5.5 表

  从接触数据库开始，就一直在和表打交道。查询数据、插入数据、更改数据和删除数据，都是在操作数据库表中的数据。从本节开始，要介绍使用数据定义语言DDL创建并修改表。

5.5.1 表概述

  • 表

  表是数据库中最重要的对象，是数据库的基础，是基本存储单元，用来存储数据，由行（记录）和列（字段）组成。

  在被授权的情况下，数据库用户可以创建表，并且创建表的用户对该表具有“增删改查”的权限。一个数据库表，可以有多达1000个字段，表名和字段名必须符合命名规则。表在创建时没有数据，只是一个表结构，之后可以通过INSERT语句单行或多行添加数据。

  如同Java语言变量有命名规则一样，Oracle数据库的表和字段的命名有如下规则：

  （1）必须以字母开始。

  （2）必须是1～30个字符长度。

  （3）只能包含字母、数字、下画线“_”、美元符“$”和井号“#”。

  （4）不能使用Oracle的关键字。

  （5）同一个用户所拥有的对象之间不能重名。

  • 用户表和数据字典表

  Oracle数据库中的表包括两种类型：一种是用户表，由用户创建和维护的表，存放用户需要存放的信息；另一种是数据字典表，由Oracle服务器创建和维护的表，包括与数据库相关的信息。

  全部数据字典表的所有者是用户SYS。数据字典表的基表很少被用户访问，因为其中的信息不容易理解，因此，用户一般是访问数据字典视图，因为视图中的信息是以容易被理解的格式表示的。存储在数据字典中的信息包括Oracle数据库用户的名字、被授予用户的权限、数据库对象名、表结构等信息。

  有四种数据字典视图，每一种都有一个特定的前缀来反映其不同的目的，如表5.2所示。

表5.2 数据字典视图

前 缀 说 明
USER_ 包含关于用户所拥有对象的信息
ALL_ 包含所有用户可访问的表 （对象表和相关的表） 的信息
DBA_ 受限制的视图，它们只能被分配有 DBA 角色的用户所访问
V$ 动态执行的视图，包含数据库服务器的性能、存储器和锁的信息
  • 查询数据字典

  要显示HR这个用户所拥有的表的名称，可以使用如下的SQL语句（以HR用户登录），其中user_tables就是记录用户表的数据字典视图。

  1. SELECT table_name FROM user_tables

  执行该SQL语句，运行结果如图5.9所示。

5.5 表 - 图1


图5.9 获取用户拥有的表的名称

  如果想了解HR这个用户所拥有的表、视图、序列等对象，可以使用如下的SQL语句：

  1. SELECT table_name,table_type FROM user_catalog

  执行结果如图5.10所示。

5.5 表 - 图2


图5.10 获取用户拥有的数据库对象

  其中第二列TABLE_TYPE中，显示为TABLE的表示前面是一个表，显示为SEQUENCE的表示前面是一个序列，显示为VIEW的表示前面是一个视图。

5.5.2 创建表

  • 语法形式

  创建表，需要使用CREATE TABLE语句，该语句是读者接触的第一个数据定义语言。为了创建表，用户必须有创建表的权限，并且有存储区域用来存储表这个数据库对象。创建表的语法形式如下：

  1. CREATE TABLE [schema.]table
  3. (col1 type1 [DEFAULT1], col2 type2 [DEFAULT2]…coln typen [DEFAULTn])

  schema（方案）是对象的集合，方案对象直接反映数据在数据库中的逻辑结构，方案对象包括表、视图、同义词、序列、存储过程、索引、集群等。默认情况下，方案名等于用户的名字。

  在Oracle中，一个用户一般对应一个方案，该用户的方案名等于用户名，并作为该用户默认的方案。如果一个表不属于本用户，那么必须通过“方案名.表名”引用这个表，也就是通过用“用户名.表名”引用这个表。

  另外，语法形式中table代表表名，coln代表第n个字段名，typen代表第n个字段的数据类型和长度，DEFAULTn代表第n个字段的默认值。默认值可以是字符、表达式或SQL函数（例如SYSDATE），主要目的是防止插入时将空值输入字段中，但默认值不能是另一个字段的名字或伪列，且默认值必须与字段的数据类型相匹配。

  • 创建表

  假设现在需要新建一个部门表depts，该部门表有四个字段，分别是部门编号dept_id、部门名称dept_name、部门经理名字manager_fname和所在地dept_loc，其SQL语句如下：

  1. CREATE TABLE depts
  3. ​     (dept_id                  NUMBER(4),
  5. ​     dept_name                       VARCHAR2(20),
  7. ​     manager_fname                   VARCHAR2(14),
  9. ​     dept_loc            VARCHAR2(30));

  执行该SQL语句（因为CREATE TABLE语句属于DDL，该语句执行时会发生一个自动提交），数据库中创建了该表。通过PL/SQL Dev查看被创建的depts表的表结构，如图5.11所示。

5.5 表 - 图3


图5.11 创建表的表结构

  以上是用SQL语句创建了depts表，这种方法比较适用于对SQL语言熟悉的数据库管理员创建表，或者软件开发人员需要在程序中动态地创建表的情况。也可以使用PL/SQL Dev这类可视化工具直接创建表，这样操作起来相对简单。在PL/SQL Dev中创建表的步骤如下。

  （1）用鼠标右键选中左侧的Tables文件夹，在弹出的菜单中选择New…，如图5.12所示。

5.5 表 - 图4


图5.12 新建表

  （2）如图5.13所示，在General选项卡中，Owner后面需要选择该表的拥有者，默认为登录用户，Name栏里需要填写表名，其他的内容暂时不填。

5.5 表 - 图5


图5.13 新建表

  （3）选择Columns选项卡，如图5.14所示，按照图5.11的内容逐行添加每个字段，添加完成后单击下面的Apply按钮创建表。需要注意的是，该窗口中的Keys、Checks、Indexes、Privileges选项卡分别是设置表的键、约束、索引和权限的，之后在涉及此类内容时，可以通过SQL语句操作，也可以通过PL/SQL Dev的可视化窗口操作。

5.5 表 - 图6


图5.14 新建表

l 用子查询创建表

  前面通过CREATE TABLE语句和PL/SQL Dev可视化工具这两种方式创建了表，此时创建的表是一个空表，里面没有任何的数据。接下来通过使用子查询的方式创建表，并同时插入数据行。用子查询创建表的语法如下：

  1. CREATE TABLE table[(col1, col2…)]
  3. AS SELECT…

  新建表后面的字段列表，如果有的话，字段的数目必须等于子查询所选字段的数目，如果没有，则新建表的字段名使用子查询的字段名。另外，定义新表的字段属性，只能定义字段名和默认值。通过子查询创建表，完整性约束不会被传递到新表中，但字段的数据类型会被引用。

  假设需要创建一个部门表depts_temp，该部门表有四个字段，分别是部门编号dept_id、部门名称dept_name、部门经理编号manager_id和所在地编号loc_id，且depts_temp表的数据来源于departments部门表（仅取部门编号为60、80和90的部门数据），其SQL语句如下：

  1. CREATE TABLE depts_temp(dept_id, dept_name, manager_id, loc_id)
  3. AS 
  5. ​    SELECT department_id, department_name, manager_id, location_id
  7. ​    FROM departments
  9. ​    WHERE department_id IN(60, 80, 90)

  执行该SQL语句，并查询新建表depts_temp里的所有数据，结果如图5.15所示。

5.5 表 - 图7


图5.15 用子查询新建表

  假设希望通过子查询创建表，但并不将数据插入新表中，那么可以让子查询的WHERE子句永远为假，这样就只创建了表，但没有插入数据。具体的SQL语句如下：

  1. CREATE TABLE depts_temp2
  3. AS 
  5. ​    SELECT department_id, department_name, manager_id, location_id
  7. ​    FROM departments
  9. ​    WHERE 1 = -1

5.5.3 修改表

  • 语法形式

  创建一个表后，可能需要改变表的结构，例如要添加或删除一个字段或修改某个字段的定义，这时可以用ALTER TABLE语句来完成这些任务。用ALTER TABLE语句添加字段、修改字段和删除字段的语法形式如下，其中，ADD、MODIFY和DROP是修改的类型。

  1. ALTER TABLE table
  3. ​    ADD(col1 type1 [DEFAULT1], col2 type2 [DEFAULT2]…)
  5. ALTER TABLE table
  7. ​    MODIFY(col1 type1 [DEFAULT1], col2 type2 [DEFAULT2]…)
  9. ALTER TABLE table
  11. ​    DROP(col)
  • 添加字段

  假设现在需要给之前创建的depts表中增加一个字段—部门编制人数num_limit，数据类型为数字型，默认值为10人，其SQL语句如下：

  1. ALTER TABLE depts
  3. ​    ADD(num_limit NUMBER(2) DEFAULT 10)

  执行该SQL语句之后，查询该表的表结构，结果如图5.16所示。

5.5 表 - 图8


图5.16 修改表添加新字段

  在图5.16中，num_limit字段被添加到该表的最后一个字段。需要注意的是，添加的新字段在该表的最后一列，不能指定字段位置。同时如果被添加的表中已包含有行，则所有行的新字段被初始化为空或默认值。

  • 修改字段

  在ALTER TABLE语句后，可以用MODIFY子句修改一个字段，字段的修改包括修改字段的数据类型（仅在列中只有空值时才可以修改）、大小和默认值，不过对默认值的修改只会影响后来插入表的数据，对之前的数据不会产生影响。

  例如发现原先设定的depts表中部门所在地字段dept_loc的字符长度为30不够使用，需要调整为50，则其SQL语句如下：

  1. ALTER TABLE depts
  3. ​    MODIFY(dept_loc VARCHAR(50))

  执行该SQL语句之后，查询该表的表结构，结果如图5.17所示。

5.5 表 - 图9


图5.17 修改表修改字段

  • 删除字段

  在ALTER TABLE语句后，可以用DROP子句删除一个字段。删除一个字段时，不论表中已有行的这个字段是否有数据，这点和修改字段数据类型时不同。另外，和添加字段、修改字段不同的是删除字段一次只能有一个字段被删除。当然，在删除字段时，被修改的表至少要保留一个字段。

  例如要删除刚刚创建的部门编制人数字段num_limit，其SQL语句如下：

  1. ALTER TABLE depts
  3. ​    DROP(num_limit)

  再次查看该表的表结构时，部门编制人数字段num_limit已被删除。

5.5.4 删除表及其他

  • 删除表

  DROP TABLE语句用于删除Oracle表，只有表的创建者或具有删除任何表权限的用户才可以删除表。在删除一个表时，数据库丢失表中所有的数据，并且所有与其相关的索引也被删除（视图和同义词被保留但已无效），未决的事务会被提交。删除表的语法形式如下：

  1. DROP TABLE table

  假设现在需要删除之前创建的depts_temp2表，其SQL语句如下：

  1. DROP TABLE depts_temp2

  执行该SQL语句，depts_temp2表被删除。

  • 重命名对象

  执行RENAME语句，可以更改一个表、视图、序列或同义词的名字，其语法形式如下：

  1. RENAME old_name TO new_name
  • 截断表

  使用TRUNCATE TABLE语句可以删除表中所有的行，并释放该表所使用的存储空间。DELETE语句也可以从表中删除所有的行，但它不能释放存储空间。相比而言，TRUNCATE TABLE语句更快一些，因为TRUNCATE语句是数据定义语句，直接提交，不会产生回滚信息，并且不会触发表的删除触发器。截断表的语法形式如下：

  1. TRUNCATE TABLE table