.Net程序员学用Oracle系列(6)：表、字段、注释、约束、索引

2022-11-16 15:13:41

本文大纲

1、表
- 1.1、创建表
- 1.2、修改表 & 删除表
2、字段
- 2.1、添加字段
- 2.2、修改字段 & 删除字段
3、注释
- 3.1、表注释
- 3.2、字段注释
4、约束
- 4.1、添加主键约束
- 4.2、添加外键约束
- 4.3、添加唯一约束
- 4.4、添加 CHECK 约束
- 4.5、非空约束
- 4.6、禁用约束 & 启用约束 & 删除约束
5、索引
- 5.1、创建索引
- 5.2、修改索引 & 删除索引
6、总结

据说 Oracle 中表的类型有 9 种，但实际应用中 99% 以上的情况下用的可能都是堆组织表，也就是我们常说的“普通”表。执行普通 CREATE TABLE 语句时，默认得到的表就是堆组织表。表是数据库中一个非常重要的对象，是其他对象的基础。数据库只是一个框架，表才是其实质内容。本文内容将围绕表及表相关的 SQL 操作展开。

表

创建表

如要创建一张课程表，字段包含：课程ID、课程名称、课程描述。示例：

CREATE TABLE t_course(
  course_id NUMBER(10) PRIMARY KEY,
  course_name VARCHAR2(50),
  course_desc VARCHAR2(2000)
);

如要创建一张只包含职员姓名和生日的表，示例：

CREATE TABLE t_staff_mini AS
SELECT t1.staff_id, t1.birthday FROM t_staff t1;

如要创建一张只包含 90 后员工数据的职员表，示例：

CREATE TABLE t_staff90 AS
SELECT * FROM t_staff t1 WHERE t1.birthday>=TO_DATE('1990-01-01','yyyy-mm-dd');

上面的示例已经两次用到根据已有表创建一张新表的语法——“CREATE TABLE ... AS SELECT * FROM ... WHERE ...”（T-SQL 中类似的语法是select field1,field2 into tableA from tableB），关于这个语法有两点需要注意：

只创建表结构并拷贝表数据，不拷贝任何约束。
当 WHERE 条件不成立时，只创建表结构，不拷贝表数据。

修改表 & 删除表

将表名从 t_staff_mini 改成 t_staff2，示例：

ALTER TABLE t_staff_mini RENAME TO t_staff2;

删除 t_staff90 表，示例：

DROP TABLE t_staff90;

字段

添加字段

向 t_staff2 表中添加 is_disabled 字段，默认值 0，表示职员未被禁用，即启用状态，示例：

ALTER TABLE t_staff2 ADD(is_disabled NUMBER(1) DEFAULT(0)); -- DEFAULT 的括号可以省略

向 t_staff 表中添加性别、身高和体重 3 个字段，写三条语句分别来添加当然是可以的，但 Oracle 提供了更简洁的语法，示例：

ALTER TABLE t_staff2 ADD(gender NUMBER(1), height NUMBER(4,1), weight NUMBER(4,1));

修改字段 & 删除字段

把 t_staff 表中的 is_disabled 字段的字段名改为 staff_status，示例：

ALTER TABLE t_staff2 RENAME COLUMN is_disabled TO staff_status;

把 t_staff 表中的 gender 字段的数据类型改为 VARCHAR2，示例：

ALTER TABLE t_staff2 MODIFY(gender VARCHAR2(10)); -- MODIFY 的括号可以省略

把 t_staff 表中的 gender 字段的数据类型长度扩大 5 倍，示例：

ALTER TABLE t_staff2 MODIFY(gender VARCHAR2(50));

把 t_staff 表中的 staff_status 字段的默认值改为字符串 1，示例：

ALTER TABLE t_staff2 MODIFY(staff_status DEFAULT('1'));

删除 t_staff 表中的 gender 字段，示例：

ALTER TABLE t_staff2 DROP(gender);

删除 t_staff 表中的身高和体重两个字段，与添加多个字段同理，示例：

ALTER TABLE t_staff2 DROP(height,weight);

注释

表注释

表注释的语法有且只有一种写法，示例：

COMMENT ON TABLE table_name IS '注释内容';

如要给 t_staff 表添加注释，示例：

COMMENT ON TABLE t_staff IS '职员表';
COMMENT ON TABLE t_staff IS ''; -- 清空 t_staff 表的注释，可利用这个思路实现删改表注释的效果

字段注释

字段注释的语法有且只有一种写法，示例：

COMMENT ON COLUMN t_staff.staff_name IS '职员姓名';
COMMENT ON COLUMN t_staff.staff_name IS ''; -- 清空 staff_name 字段的注释，可利用这个思路实现删改字段注释的效果

约束

数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在 Oracle 中数据完整性可以通过约束、触发器、存储过程、函数等方式来实现，而约束易于维护，并且具有最好的性能，一般作为确保数据完整性的首选。

添加主键约束

要求给 t_staff_copy 表添加一个主键，主键字段为 staff_id，示例：

ALTER TABLE t_staff_copy ADD CONSTRAINT pk_staff_copy PRIMARY KEY(staff_id);

如要根据职员姓名和性别创建一个联合主键，示例：

ALTER TABLE t_staff_copy DROP CONSTRAINT pk_staff_copy; -- 表只能有一个主键，所以要先把刚才建的主键删除掉
ALTER TABLE t_staff_copy ADD CONSTRAINT pk_staff_copy PRIMARY KEY(staff_name,gender);

实际开发中一般都会给表设置主键，而且大部分都是在创建表的时候一块儿创建，示例：

-- 语法一
CREATE TABLE t_staff3(
  staff_id NUMBER(10) PRIMARY KEY,
  staff_name VARCHAR2(20)
);
-- 语法二
CREATE TABLE t_staff4(
  staff_id NUMBER(10),
  staff_name VARCHAR2(20),
  CONSTRAINT pk_staff4 PRIMARY KEY(staff_id)
);

添加外键约束

关于外键约束有两个需要注意的概念，分别是级联更新和级联删除，简单的说就是当主表中数据删除后，会同步更新或删除从表中的数据。在 Oracle 里删除有外键约束的主表数据，会有以下三种情况之一发生：

1、禁止删除，也是 Oracle 默认行为。
2、参照主表把从表中对应的数据一同删除。适合强耦合关系。
3、参照主表把从表中对应的数据设置为空。适合弱耦合关系。

第 1 种情况示例：

ALTER TABLE t_field_enum ADD CONSTRAINT fk_field_code FOREIGN KEY(field_code) REFERENCES t_field(field_code);

第 2 种情况示例：

ALTER TABLE t_field_enum DROP CONSTRAINT fk_field_code; -- 对同一个字段只能建一个外键，所以要先把刚才建的外键删除掉
ALTER TABLE t_field_enum ADD CONSTRAINT fk_field_code FOREIGN KEY(field_code) REFERENCES t_field(field_code) ON DELETE CASCADE;

第 3 种情况示例：

ALTER TABLE t_field_enum DROP CONSTRAINT fk_field_code;
ALTER TABLE t_field_enum ADD CONSTRAINT fk_field_code FOREIGN KEY(field_code) REFERENCES t_field(field_code) ON DELETE SET NULL;

添加唯一约束

唯一约束的作用就是确保表字段的值不会重复，示例：

ALTER TABLE t_staff ADD CONSTRAINT uk_staff_name UNIQUE(staff_name);

添加 CHECK 约束

CHECK 约束允许对表字段做更个性化的约束以满足业务需要，比如限定性别只能是 1（男）和 0（女），示例：

ALTER TABLE t_staff ADD CONSTRAINT ck_gender CHECK(gender IN(0,1));

非空约束

非空约束比较特殊，其作用就是限制表字段必填，添加非空约束的示例：

ALTER TABLE t_staff2 MODIFY(staff_status NOT NULL); -- 如要去掉非空约束，去掉 NOT 再执行一次即可

禁用约束 & 启用约束 & 删除约束

除非空约束比较特殊以外，其它约束的启用、禁用和删除语法都以一样的。示例：

ALTER TABLE t_staff DISABLE CONSTRAINT uk_staff_name;           -- 禁用 uk_staff_name 约束
ALTER TABLE t_staff ENABLE CONSTRAINT uk_staff_name;            -- 启用 uk_staff_name 约束
ALTER TABLE t_staff RENAME CONSTRAINT uk_staff_name TO uk_test; -- 将约束名从 uk_staff_name 改成 uk_test
ALTER TABLE t_staff DROP CONSTRAINT uk_test;                    -- 删除 uk_staff_name 约束

索引

索引其实是一个很大的话题，大型应用这块儿工作主要由 DBA 来完成，还有些索引 Oracle 会自动创建（比如创建主键时会自动创建基于主键字段的唯一索引，创建唯一约束时会创建基于约束字段的唯一索引），这里仅介绍创建、禁用和删除索引的语法，作为普通开发人员知道这些已经够了。

创建索引

索引一般是根据业务查询条件来创建的，常见的 Oracle 索引创建语法示例：

CREATE INDEX idx_manager_id ON t_staff(manager_id);       -- 创建标准/普通索引
CREATE UNIQUE INDEX uk_staff_name ON t_staff(staff_name); -- 创建唯一索引
CREATE BITMAP INDEX idx_gender ON t_staff(gender);        -- 创建位图索引
CREATE INDEX idx_dept_code ON t_staff(UPPER(dept_code));  -- 创建基于函数的索引
CREATE INDEX idx_birthday ON t_staff(birthday) REVERSE;   -- 创建反向键索引

索引有单列索引和组合索引之分，上面 5 个索引都属于单列索引，如果要创建组合索引，即在多个列上创建索引，只要在上面创建索引的语句中括号里加入更多字段并用逗号隔开即可。

修改索引 & 删除索引

修改索引：创建索引时还可以指定表空间，修改时也能调整表空间等索引属性，下面只示例几个本人用过的语法：

ALTER INDEX idx_manager_id UNUSABLE;           -- 禁用索引
ALTER INDEX idx_manager_id REBUILD REVERSE;    -- 将普通索引修改为反向键索引
ALTER INDEX idx_manager_id REBUILD NOREVERSE;  -- 将反向键索引修改为普通索引
ALTER INDEX idx_manager_id RENAME TO idx_test; -- 将索引名从 idx_staff_name 改成 idx_test

删除索引：如果发现索引建错了也可以直接删除掉，示例：

DROP INDEX idx_test;

总结

本文主要介绍了关于表的常见 SQL 语法，不知道大家有没有发现如下两个规律：

规律一：当成功创建一个主键约束之后，Oracle 会自动基于相同的字段列表再创建一个同名的唯一索引，反之不会。

规律二：当成功创建一个唯一约束之后，Oracle 会自动基于相同的字段列表再创建一个同名的唯一索引，反之不会。

《.Net程序员学用Oracle系列：导航目录》

本文声明：如果您认为这篇文章还可以或对您有帮助，请点击文章末尾的“推荐”按钮。欢迎转载、演绎或用于商业目的，但必须保留本文的署名韩宗泽，并且要在明显位置给出原文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！

码农公寓

表

创建表