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

时间：2024-10-03 20:55:16

全文目录：

    • 前言
    • 一、约束的高级使用
      • 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)
);

该表定义了主键和外键约束，同时将sale_date用于分区。通过这种方式，我们既能确保数据一致性，又能享受分区带来的性能优化。

结语

在本期内容中，我们详细探讨了数据定义语言（DDL）的各个方面，涵盖了约束的高级使用、视图和同义词的管理、序列的生成，以及表分区的设计与实施。通过这些知识，大家能够更好地定义和管理数据库结构，为后续的数据操作和性能优化奠定坚实基础。

下期内容将重点讨论事务控制与锁管理，深入理解如何管理并发操作，确保数据库事务的完整性与一致性。敬请期待！

参考文献：

  • Oracle数据库官方文档
  • 数据库设计与优化实战

相关文章