滚雪球学Oracle[3.3讲]：数据定义语言（DDL）

全文目录：

- 前言
- 一、约束的高级使用
- - 1.1 主键（Primary Key）
  - - 案例演示：定义主键
  - 1.2 唯一性约束（Unique）
  - - 案例演示：定义唯一性约束
  - 1.3 外键（Foreign Key）
  - - 案例演示：定义外键
  - 1.4 复合主键与外键
  - - 案例演示：定义复合主键与外键
- 二、视图、同义词和序列的管理
- - 2.1 视图（View）
  - - 案例演示：创建视图
  - 2.2 同义词（Synonym）
  - - 案例演示：创建同义词
  - 2.3 序列（Sequence）
  - - 案例演示：创建序列
- 三、表分区的设计与实施
- - 3.1 表分区的类型
  - 3.2 案例演示：范围分区
  - - 创建分区表
  - 3.3 案例演示：哈希分区
- 四、延伸讨论：约束与表分区的结合
- - - 案例演示：分区表的主键和外键约束
- 结语

前言

在上期内容中，我们深入探讨了查询与数据操作基础，重点讲解了如何使用SQL语言执行数据查询、插入、更新与删除操作。通过这些基础的SQL操作，大家了解了如何与数据库交互、修改数据，并为业务应用提供支持。这些操作属于数据操作语言（DML）的范畴，帮助我们掌握了数据库操作的基础知识。

在本期，我们将更进一步，进入数据定义语言（DDL）的领域。DDL是用于定义和修改数据库结构的语言，它负责创建、修改和删除数据库对象。我们将从约束的高级使用（如主键、唯一性、外键等）入手，详细讨论DDL的核心功能。除此之外，我们还将探讨视图、同义词和序列的管理，以及如何通过表分区的设计与实施来提升数据库的性能与可维护性。本文将通过实例演示，帮助大家更深入理解DDL的实际应用。

一、约束的高级使用

在数据库设计中，约束用于限制表中的数据，以保证数据的一致性、完整性和准确性。常用的约束包括主键（Primary Key）、唯一性（Unique）、外键（Foreign Key）、非空（NOT NULL）和检查（CHECK）等。

1.1 主键（Primary Key）

主键是用于唯一标识表中每一行的列或列组合。每个表只能有一个主键，且主键列不能为空值。主键约束在创建表时定义，也可以通过ALTER TABLE命令后期添加。

案例演示：定义主键

CREATE TABLE employees (employee_id NUMBER PRIMARY KEY,first_name VARCHAR2(50),last_name VARCHAR2(50),hire_date DATE
);

在上面的例子中，employee_id是员工表的主键，用于唯一标识每个员工。

1.2 唯一性约束（Unique）

唯一性约束确保列中的值是唯一的，不会重复。与主键不同，表中可以有多个唯一性约束，且唯一性列允许为空值。

案例演示：定义唯一性约束

CREATE TABLE departments (department_id NUMBER PRIMARY KEY,department_name VARCHAR2(100) UNIQUE
);

在此例中，department_name必须是唯一的，这样可以确保每个部门名称在系统中不重复。

1.3 外键（Foreign Key）

外键用于维护表与表之间的关系，通常用来定义表与另一张表的引用关系。外键约束确保引用的值在父表中存在，保证数据的一致性和完整性。

案例演示：定义外键

CREATE TABLE orders (order_id NUMBER PRIMARY KEY,employee_id NUMBER,order_date DATE,CONSTRAINT fk_employee FOREIGN KEY (employee_id)REFERENCES employees (employee_id)
);

在这个示例中，employee_id是外键，确保每个订单中的employee_id必须是employees表中已存在的员工。

1.4 复合主键与外键

复合主键与复合外键涉及多个列的组合，它们在一些需要联合唯一性验证或关系绑定的业务场景中使用较多。

案例演示：定义复合主键与外键

CREATE TABLE project_assignments (project_id NUMBER,employee_id NUMBER,assignment_date DATE,PRIMARY KEY (project_id, employee_id),CONSTRAINT fk_employee_project FOREIGN KEY (employee_id)REFERENCES employees (employee_id)
);

此表的主键是project_id和employee_id的组合，表示一个项目可以有多个员工参与，而每个员工在项目中的记录是唯一的。

二、视图、同义词和序列的管理

在数据库中，视图、同义词和序列是非常重要的高级对象，它们为数据访问、管理和数据生成提供了极大的灵活性。

2.1 视图（View）

视图是一种虚拟表，它基于SQL查询创建，允许用户像操作表一样操作它。视图的主要优势是简化复杂查询、提供数据安全性（隐藏某些列）以及提高数据复用性。

案例演示：创建视图

CREATE VIEW employee_view AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD');

通过创建视图employee_view，我们可以快速访问自2020年以来雇佣的员工数据，而不必每次都编写复杂的查询。

2.2 同义词（Synonym）

同义词是数据库对象的别名，用于简化对象的访问，特别是在跨用户或跨模式的场景下。同义词可以是公共的，也可以是私有的。

案例演示：创建同义词

CREATE SYNONYM emp FOR employees;

这样，用户在查询时可以使用emp来代替employees表，简化访问。

2.3 序列（Sequence）

序列用于生成唯一的数值，常用于自动生成主键值。序列在插入数据时可以避免手动输入主键，确保每条记录都有唯一标识符。

案例演示：创建序列

CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1
NOCACHE;

在插入新员工时，可以通过emp_seq.NEXTVAL来获取下一个唯一值作为employee_id。

INSERT INTO employees (employee_id, first_name, last_name, hire_date)
VALUES (emp_seq.NEXTVAL, 'John', 'Doe', SYSDATE);

三、表分区的设计与实施

随着数据库数据量的增加，查询和管理大表的效率可能会大幅下降。表分区是一种非常有效的技术，允许将大表的数据根据某些条件拆分成多个部分，从而提升查询效率、管理性能和备份恢复能力。

3.1 表分区的类型

Oracle支持多种分区方式，常见的包括：

范围分区（Range Partitioning）：根据值的范围进行分区。
列表分区（List Partitioning）：根据离散的值进行分区。
哈希分区（Hash Partitioning）：通过哈希函数将数据均匀分布到不同的分区中。
组合分区（Composite Partitioning）：将多种分区方式结合起来。

3.2 案例演示：范围分区

创建分区表

我们可以创建一个根据日期范围进行分区的订单表，确保历史订单与当前订单在不同的分区中存储，从而提升查询效率。

CREATE TABLE orders_partitioned (order_id NUMBER PRIMARY KEY,order_date DATE,total_amount NUMBER
)
PARTITION BY RANGE (order_date) (PARTITION p1 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')),PARTITION p2 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),PARTITION p3 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

在该表中，orders_partitioned根据order_date进行分区，旧的订单和新的订单存储在不同的分区中。当查询历史订单时，数据库只需扫描相关分区，极大地提高了查询效率。

3.3 案例演示：哈希分区

哈希分区适用于数据分布较为均匀的场景。通过哈希函数，我们可以确保数据被均匀分布在多个分区中，从而优化负载均衡。

CREATE TABLE customer_hash_partitioned (customer_id NUMBER PRIMARY KEY,customer_name VARCHAR2(100),region_id NUMBER
)
PARTITION BY HASH (region_id) PARTITIONS 4;

这里，customer_hash_partitioned表通过region_id进行哈希分区，确保每个地区的客户均匀分布到四个分区中。

四、延伸讨论：约束与表分区的结合

在实际的数据库设计中，约束和表分区往往需要结合使用。通过在分区表中定义主键、唯一性约束和外键约束，我们可以在提高查询效率的同时，确保数据的一致性和完整性。

案例演示：分区表的主键和外键约束

CREATE TABLE sales_partitioned (sale_id NUMBER,customer_id NUMBER,sale_date DATE,amount NUMBER,PRIMARY KEY (sale_id),CONSTRAINT fk_customer FOREIGN KEY (customer_id)REFERENCES customer_hash_partitioned (customer_id)
)
PARTITION BY RANGE (sale_date) (PARTITION p1 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),PARTITION p2 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),PARTITION p3 VALUES LESS THAN (MAXVALUE)
);