5.4 创建简单的表

可用多种方法将表保存在数据库中，最简单的是堆表。堆表包含了随机排列的可变长度的行。输入行的顺序和排列行的顺序之间可能有某种关联，但这只是巧合而已。更高级的表结构，如下所示，可能对行强制顺序和分组，或者要求随机分布：

● 索引组织的表 按索引键的顺序存储行。

● 索引群集 可以反规范化父子关系表，这样来自不同表的相关行就可以存储在一起。

● 哈希群集 要求随机分布行，这样可以打破依据条目序列的排序。

● 分区表 以单独的物理结构(分区)存储行，依据列的值分配行。

使用更高级的表结构对SQL没有影响。对用这些选项定义的表执行的每个SQL语句都会返回相同的结果，就像表是标准的堆表一样，因此使用这些特性不会影响代码。但当它们的使用对于编程人员而言是透明时，它们的确大大提高了性能。

5.4.1 使用列规范创建表

要创建标准的堆表，可以使用下面的语法：

        CREATE TABLE [schema.]table [ORGANIZATION HEAP]
        (column datatype [DEFAULT expression]
        [, column datatype [DEFAULT expression]...);

至少指定表名(如果没有指定其他人的，就会在自己的模式中创建它)和具有某个数据类型的一列。很少有开发人员指定ORGANIZATION HEAP，因为这是默认的，是行业标准的SQL。列定义中的DEFAULT关键字要求提供一个表达式，如果INSERT语句没有提供值，那么在插入行时，这个表达式会生成列的值。

分析下面的语句：

        create table scott.emp
        (empno number(4),
        ename varchar2(10),
        hiredate date default trunc(sysdate),
        sal number(7,2),
        comm number(7,2) default 0.03);

这会在SCOTT模式中创建一个名为EMP的表。用户SCOTT本身必须发出语句(这种情况下，就没必要命名模式)，或者另一个用户可以发出它(如果允许他们在SCOTT模式中创建表)。下面逐列进行考察：

● EMPNO可以是4位数长，没有小数部分。如果在INSERT语句中包含小数，就会将它们舍入(向上舍入或者向下舍入)为最接近的整数。

● ENAME可以最多存储10个字符。

● HIREDATE可以接受任何日期，可能包含时间，但如果没有提供值，就会输入今天午夜的日期。

● SAL表示员工的薪水，可以接受数值，最多可达到7位。小数点左边最多可以有5位数字。如果超过两位的数字出现在小数点右边，就会舍入它们。

● COMM(表示佣金率)的默认值是0.03，如果INSERT语句没有包含这一列的值，就会输入它。

创建表之后，下面这些语句插入行和选择结果：

        insert into scott.emp (empno, ename, sal)
        values (1000, 'John', 1000.789);
        1 row created.

       select *
        from scott.emp;

           EMPNO    ENAME       HIREDATE         SAL        COMM
        ----------   ----------  --------- ---------- ----------
              1000   John        19-NOV-13    1000.79         .03

注意，INSERT语句中没有提到的列值由DEFAULT子句生成。如果表定义中没有定义这些子句，列就为NULL。还要注意对SAL值的舍入。

5.4.2 使用子查询创建表

除了创建一个空表，然后插入行之外(如前所述)，还可以使用子查询通过其他表来创建表。这种方法可以创建表定义，并使用一条语句给表填充行。所有查询都可用作表结构和行的来源。其语法如下所示：

CREATE TABLE [schema.]table AS subquery;

所有查询都返回行的二维集合；这个结果存储为新的表。使用子查询创建表的简单示例是：

        create table employees_copy as
        select *
        from employees;

这条语句会创建表EMPLOYEES_COPY，它完全是EMPLOYEES表的副本，它们的定义和包含的行完全相同。列上的所有NOT NULL(非空)和CHECK(检查)约束也将应用于新的表，但所有PRIMARY KEY(主键)、UNIQUE(唯一值)或者FOREIGN KEY(外键)约束则不适用(稍后将讨论约束的概念)。这是因为这三类约束需要索引，这些索引可能不是必需的。

下面这个示例更复杂：

        create table emp_dept as
        select last_name ename, department_name dname, round(sysdate -hire_date)
            service
        from employees
        natural join departments
        order by dname, ename;

新表中的行是联接这两个源表的结果，两个选中的列已经改变名称。计算员工被雇佣天数的算术运算的结果将填充新的SERVICE列，并将会按指定的顺序插入这些行。后面的DML不会保持这个顺序，但假设是标准的HR模式数据，新表会如下所示：

        select *
        from emp_dept
        where rownum < 10;

       ENAME            DNAME               SERVICE
        --------------- --------------- ----------
        Gietz            Accounting             4203
        De Haan          Executive              4713
        Kochhar          Executive              3001
        Chen             Finance                2994
        Faviet           Finance                4133
        Popp             Finance                2194
        Sciarra          Finance                2992
        Urman            Finance                2834
        Austin           IT                     3089
        9 rows selected.

子查询当然可以包含WHERE子句来限制插入新表的行。要创建一个没有行的表，可以使用排除所有行的WHERE子句：

        create table no_emps as
        select *
        from employees
        where 1=2;

WHERE子句1=2绝不会返回TRUE，因此会创建表结构以备用，但在创建时没有插入行。

5.4.3 在创建之后更改表定义

创建表后，可以对表进行许多更改。影响物理存储的那些更改属于数据库管理员领域，但许多更改只是逻辑的，由SQL开发人员实施。下面就是示例(它们大部分都很简单)：

● 添加列：

        alter table emp
        add (job_id number);

● 修改列：

        alter table emp
        modify (comm number(4,2) default 0.05);

● 删除列：

        alter table emp
        drop column comm;

● 将列标记为未使用：

        alter table emp
        set unused column job_id;

● 重命名列：

        alter table emp
        rename column hiredate to recruited;

● 将表标记为只读：

        alter table emp
        read only;

所有这些变更都是包含内置COMMIT的DDL命令。因此它们是不可逆的，如果表上有活动事务，它们就会失败。其实它们也是瞬间完成的(删除列除外)。删除列可能是一个耗时的操作，因为删除各列之后，必须重新构造各行来删除列的数据。SET UNUSED命令——对于SQL而言，它让这些列不存在——通常是更好的选择，如果方便的话，后面采用下列命令，一次删除表中所有未使用的列：

        ALTER TABLE tablename
        DROP UNUSED COLUMNS;

对于DML命令而言，将表标记为只读会产生错误。但还是能够删除表。这可能令人困惑，但理解之后，就会发现它完全符合逻辑。DROP命令实际上不影响表：它影响数据字典中的表，这些表定义表，它们不是只读的。

5.4.4 删除和截断表

TRUNCATE TABLE命令能够删除表的所有行，同时保持表定义不变。DROP TABLE更极端，因为同时会删除表定义。其语法如下所示：

DROP TABLE [schema.]tablename ;

如果没有指定模式，那么当前登录的模式中名为tablename的表就会被删除。

和TRUNCATE一样，SQL在删除表之前不会生成警告，而且，与任何DDL命令一样，它包含COMMIT。因此DROP一般不可逆。但也有一些限制：如果会话(甚至是你自己的)有正在处理的事务，该事务包含表中的行，那么DROP就会失败；另外，也不可以删除在为其他表而定义的外键约束中引用的表。首先必须删除这个表(或者这个约束)。

练习5-3 创建表

在本练习中，使用SQL Developer创建一个堆表，使用子查询来插入一些行，并修改表。使用SQL*Plus进行更多修改，然后删除表。

(1) 使用SQL Developer，以用户HR身份连接数据库。

(2) 右击导航树中的Tables分支，单击New Table选项。

(3) 将新表命名为EMPS，使用Add Column按钮，将它设置为如图5-6所示。

(4) 单击DDL标签，看看是不是已经构造了语句。它应该如下所示：

        CREATE TABLE EMPS
        (
        EMPNO NUMBER
        ,ENAME VARCHAR2(25)
        ,SALARY NUMBER
        ,DEPTNO NUMBER(4, 0)
        );

返回Table选项卡(如图5-6所示)，单击OK按钮创建表。

(5) 运行下列语句：

        insert into emps
        select employee_id, last_name, salary, department_id
        from employees;

并提交插入：

        commit;

(6) 右击SQL Developer导航器中的EMPS表，单击Column和Add按钮。

(7) 定义一个新的列EMAIL，数据类型为DATE，如图5-7所示；单击Apply按钮创建列。

(8) 使用SQL*Plus作为HR用户连接到数据库。

(9) 为EMPS表中的HIRED列定义一个默认值：

        alter table emps
        modify (hired default sysdate);

(10) 在没有指定HIRED值的情况下插入一行，检查新的行有HIRED日期，而其他行没有：

        insert into emps (empno, ename)
        values (99, 'Newman');
        select hired, count(1)
        from emps
        group by hired;

(11) 删除新表，进行整理。

        drop table emps;

5.5 创建和使用临时表

临时表包含所有会话都可以访问的定义，但其中的行是插入行的会话专用的。编程人员可将它们作为私有存储区，用于操纵大量数据。语法如下：

        CREATE GLOBAL TEMPORARY TABLE temp_tab_name
        (column datatype [, column datatype] )
        [ON COMMIT {DELETE | PRESERVE} ROWS] ;

列定义与普通表没有差别，但确实可以通过子查询来提供。末尾的可选子句确定插入的任何行的生命周期。默认方式是：在插入行的事务完成之时，删除相应的行，但也可以更改此行为，以便将它们保留到插入行的会话结束为止。无论选择哪个选项，数据都专门用于每个会话：不同用户可将自己的行插入自己的表的副本中，谁都看不到其他人的行。

在很多方面，临时表都与永久表类似。可以针对其执行任何DML或SELECT命令。可以为临时表定义索引、约束以及触发器。可以在视图和同义词中引用它，或将它与其他表联接在一起。二者的区别在于：临时表的数据是临时的，专用于相应的会话，针对其执行的所有SQL命令的速度远比针对永久表的命令快。

速度快的第一个原因是，临时表不是永久表空间中的段。表将写出到用户临时表空间的临时段中。临时表空间上的I/O远比永久表空间上的I/O速度快，原因是前者不使用数据库缓冲区缓存，而全部由会话的服务器进程直接在磁盘上执行。

速度快的第二个原因在于：针对临时表的DML不生成重做数据。由于数据仅在会话生存期间(可能仅是事务期间)保留，就没必要生成重做数据。这带来了双重好处：针对表的会话的DML更快，且取消了重做生成系统的负担(在忙碌的多用户数据库中，这是一个糟糕的争用点)。

图5-8显示了使用SQL*Plus来创建和使用临时表的过程。Database Control的Table Creation Wizard也可以创建临时表。

练习5-4 创建和使用临时表

本练习创建用于报告当前员工的临时表。使用两个SQL*Plus 会话演示数据是每个会话专用的。

(1) 使用SQL*Plus，以用户HR的身份连接到数据库。

(2) 按如下方式创建临时表：

        create global temporary table tmp_emps on commit preserve rows as
        select * from employees where 1=2

(3) 插入一些行，并将其提交：

        insert into tmp_emps select * from employees where department_id=30;
        commit;

(4) 以HR的身份启动第二个SQL*Plus会话。

(5) 在第二个会话中，确认第一个会话中的第一个插入虽已提交但不可见，并插入一些不同的行：

        select count(*) from tmp_emps;
        insert into tmp_emps select * from employees where department_id=50;
        commit;

(6) 在第一个会话中，将表截断：

        truncate table tmp_emps;

(7) 在第二个会话中，确认表的会话副本仍然包含行：

        select count(*) from tmp_emps;

(8) 在第二个会话中，演示会话的终止并不会清除行。这需要断开连接并再次连接：

        disconnect;
        connect hr/hr
        select count(*) from tmp_emps;

(9) 在两个会话中通过删除表来清理环境。