管理表（三）

远歌已逝

已于 2024-12-03 14:04:44 修改

阅读量1.3k

点赞数 14

分类专栏：数据库文章标签： oracle 数据库

于 2024-12-03 08:03:27 首次发布

本文链接：https://blog.youkuaiyun.com/yuanymnl/article/details/144197165

版权

数据库专栏收录该内容

32 篇文章

订阅专栏

创建表

在 Oracle 中，CREATE TABLE 命令用于创建数据库表。表是存储数据的基本结构，由行和列组成。创建表时，可以指定表的列、数据类型、约束、存储选项等一系列参数。

`CREATE TABLE` 基本语法

CREATE TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...
    [table_constraint]
) [TABLESPACE tablespace_name] [STORAGE storage_options] [tablespace_options];

其中：

table_name：要创建的表的名称。
column_name：列的名称。
datatype：列的数据类型，如 NUMBER, VARCHAR2, DATE 等。
constraint：对列或表施加的约束，例如 PRIMARY KEY, NOT NULL 等。
table_constraint：表级约束，如 PRIMARY KEY, FOREIGN KEY 等。
TABLESPACE tablespace_name：指定表所在的表空间。
STORAGE：指定存储选项，如初始空间、空间增长策略等。

`CREATE TABLE` 命令详细选项

下面将详细解释 CREATE TABLE 命令中各个常用选项。

1. 列定义

每个列在创建表时都需要定义其名称和数据类型。可选地，可以为列指定约束（如 NOT NULL，DEFAULT 等）。

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25) NOT NULL,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(8,2)
);

解释：

employee_id NUMBER(6)：employee_id 列是一个数字类型，最多 6 位。
first_name VARCHAR2(20)：first_name 列是一个可变长度的字符串，最大长度 20 个字符。
last_name VARCHAR2(25) NOT NULL：last_name 列是一个可变长度字符串，最大长度 25 个字符，并且该列不允许为空。
hire_date DATE DEFAULT SYSDATE：hire_date 列是日期类型，默认值为当前日期。
salary NUMBER(8,2)：salary 列是一个数字类型，最多 8 位数字，其中有 2 位小数。

2. 列约束（Column Constraints）

列约束用于确保列数据的完整性和一致性。常见的列约束包括：

NOT NULL：确保列中的数据不能为空。
UNIQUE：确保列中的数据唯一。
CHECK：确保列中的数据满足特定条件。
DEFAULT：为列指定默认值。
PRIMARY KEY：确保列的值唯一，并且不为空。
FOREIGN KEY：定义外键约束，确保列的值必须在另一个表的列中存在。

CREATE TABLE orders (
    order_id NUMBER(6) PRIMARY KEY,
    customer_id NUMBER(6) NOT NULL,
    order_date DATE DEFAULT SYSDATE,
    total_amount NUMBER(10, 2) CHECK (total_amount >= 0),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

解释：

order_id NUMBER(6) PRIMARY KEY：order_id 列是主键，保证该列中的值唯一且不为空。
customer_id NUMBER(6) NOT NULL：customer_id 列不能为空。
order_date DATE DEFAULT SYSDATE：order_date 列的默认值是当前日期。
total_amount NUMBER(10,2) CHECK (total_amount >= 0)：total_amount 列必须大于等于 0，确保总金额不为负数。
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)：customer_id 列是一个外键，引用了 customers 表中的 customer_id 列。

3. 表级约束（Table Constraints）

表级约束适用于多列，并且定义在表的最后。例如：

PRIMARY KEY：指定一个主键约束，可以包含多个列。
FOREIGN KEY：指定一个外键约束，确保列值在另一个表中存在。
CHECK：指定一个检查约束，保证多个列满足某个条件。

CREATE TABLE departments (
    department_id NUMBER(4),
    department_name VARCHAR2(30),
    manager_id NUMBER(6),
    location_id NUMBER(4),
    CONSTRAINT pk_department PRIMARY KEY (department_id),
    CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);

解释：

PRIMARY KEY (department_id)：department_id 列是主键约束。
FOREIGN KEY (manager_id) REFERENCES employees (employee_id)：manager_id 列是外键约束，引用 employees 表的 employee_id 列。

4. 表空间（TABLESPACE）

表空间是数据库中的物理存储位置。通过 TABLESPACE 选项，可以指定表的存储位置。默认情况下，表会创建在数据库的默认表空间中。

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25)
) TABLESPACE users;

解释：

TABLESPACE users：指定该表创建在 users 表空间中。

5. 存储选项（STORAGE）

通过 STORAGE 子句，可以定义表的存储属性，包括初始存储空间、空间增长方式等。常见的存储选项有：

INITIAL：指定表的初始存储空间大小。
NEXT：指定下一个扩展空间的大小。
PCTINCREASE：指定每次扩展的空间增量百分比。
MINEXTENTS：指定表的最小扩展数。
MAXEXTENTS：指定表的最大扩展数。

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25)
) STORAGE (
    INITIAL 100K 
    NEXT 100K 
    PCTINCREASE 10 
    MINEXTENTS 1 
    MAXEXTENTS 5
);

解释：

INITIAL 100K：指定表的初始存储空间为 100KB。
NEXT 100K：指定每次扩展时分配 100KB 的空间。
PCTINCREASE 10：指定空间扩展时，增长 10%。
MINEXTENTS 1：指定表至少有 1 个扩展。
MAXEXTENTS 5：指定表最多有 5 个扩展。

6. 块利用率（Block Utilization）

通过以下选项，可以管理表数据的存储块利用率：

PCTFREE：指定每个数据块中保留的最小空闲空间百分比。
PCTUSED：指定每个数据块中可用的最小空间百分比。
INITRANS：指定每个数据块初始的事务条目数。
MAXTRANS：指定每个数据块允许的最大事务条目数。

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255;

解释：

PCTFREE 10：每个数据块保留 10% 空间，用于未来的更新。
PCTUSED 40：当数据块使用空间低于 40% 时，新的行可以插入该块。
INITRANS 1：为每个数据块分配 1 个初始事务条目。
MAXTRANS 255：每个数据块最多允许 255 个事务。

好的，接下来继续讲解 分区表 以及其他 CREATE TABLE 的选项。

7. 分区表（Partitioned Tables）（继续）

在分区表中，数据根据指定的列进行划分，这有助于提高查询性能，特别是在大数据集的情况下。Oracle 支持多种分区类型，包括 范围分区（Range Partitioning）、列表分区（List Partitioning）、哈希分区（Hash Partitioning） 和 复合分区（Composite Partitioning）。

继续解释：

CREATE TABLE sales (
    sale_id NUMBER(6),
    sale_date DATE,
    amount NUMBER(8,2)
) PARTITION BY RANGE (sale_date) (
    PARTITION q1 VALUES LESS THAN (TO_DATE('01-APR-2024', 'DD-MON-YYYY')),
    PARTITION q2 VALUES LESS THAN (TO_DATE('01-JUL-2024', 'DD-MON-YYYY')),
    PARTITION q3 VALUES LESS THAN (TO_DATE('01-OCT-2024', 'DD-MON-YYYY')),
    PARTITION q4 VALUES LESS THAN (TO_DATE('01-JAN-2025', 'DD-MON-YYYY'))
);

解释：

PARTITION BY RANGE (sale_date)：表示根据 sale_date 列的值进行范围分区。
PARTITION q1 VALUES LESS THAN (TO_DATE(‘01-APR-2024’, ‘DD-MON-YYYY’))：分区 q1 包含 sale_date 列值小于 2024年4月1日 的数据。
PARTITION q2 VALUES LESS THAN (TO_DATE(‘01-JUL-2024’, ‘DD-MON-YYYY’))：分区 q2 包含 sale_date 列值小于 2024年7月1日 的数据，以此类推。

8. 表的外键约束（FOREIGN KEY Constraint）

FOREIGN KEY 约束用于确保表之间的数据一致性。它确保一个表的列中的值必须出现在另一个表的对应列中。外键约束有助于维持数据的完整性，防止无效数据的插入。

CREATE TABLE orders (
    order_id NUMBER(6) PRIMARY KEY,
    customer_id NUMBER(6) NOT NULL,
    order_date DATE DEFAULT SYSDATE,
    total_amount NUMBER(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

解释：

FOREIGN KEY (customer_id)：这是一个外键，确保 orders 表中的 customer_id 列的值存在于 customers 表中的 customer_id 列中。

9. 表级约束（Table Constraints）

表级约束是在表的定义中设置的约束，通常用于处理多个列之间的关系。常见的表级约束包括 PRIMARY KEY、UNIQUE、CHECK、FOREIGN KEY。

示例：

CREATE TABLE orders (
    order_id NUMBER(6),
    customer_id NUMBER(6),
    order_date DATE DEFAULT SYSDATE,
    total_amount NUMBER(10, 2),
    CONSTRAINT pk_order PRIMARY KEY (order_id),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    CONSTRAINT ck_total_amount CHECK (total_amount >= 0)
);

解释：

PRIMARY KEY (order_id)：定义了 order_id 列作为主键约束。
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)：定义了 customer_id 列作为外键，引用了 customers 表中的 customer_id 列。
CHECK (total_amount >= 0)：确保 total_amount 列的值不小于零。

10. 使用默认表空间（TABLESPACE）

表空间是 Oracle 中用于存储数据的逻辑容器。通过 TABLESPACE 子句，可以指定表存储的表空间。如果不指定，表将存储在用户的默认表空间中。

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25)
) TABLESPACE users;

解释：

TABLESPACE users：指定表将存储在 users 表空间中。

11. 存储选项（STORAGE）

通过 STORAGE 子句，您可以定义表的存储属性。这些属性影响表的扩展方式、空间使用效率以及表的数据块分配等。常见的存储选项包括：

INITIAL：指定表的初始空间大小。
NEXT：指定下一次扩展时分配的空间大小。
PCTINCREASE：指定扩展时的百分比增量。
MINEXTENTS：指定表的最小扩展数。
MAXEXTENTS：指定表的最大扩展数。

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25)
) STORAGE (
    INITIAL 100K 
    NEXT 100K 
    PCTINCREASE 10 
    MINEXTENTS 1 
    MAXEXTENTS 5
);

解释：

INITIAL 100K：指定表的初始空间为 100KB。
NEXT 100K：指定每次扩展时分配 100KB 的空间。
PCTINCREASE 10：每次扩展时，空间大小增加 10%。
MINEXTENTS 1：指定表至少有 1 个扩展。
MAXEXTENTS 5：指定表最多有 5 个扩展。

12. 块利用率（Block Utilization）

在 Oracle 数据库中，每个表数据被存储在数据块中。通过块利用率的设置，您可以影响表的空间分配和数据块的管理策略。

PCTFREE：指定每个数据块中保留的空闲空间百分比。
PCTUSED：指定数据块中可以使用的最小空间百分比，只有在数据块使用空间低于该值时，新行才会被插入该块。
INITRANS：指定每个数据块初始的事务条目数。
MAXTRANS：指定每个数据块允许的最大事务条目数。

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255;

解释：

PCTFREE 10：每个数据块保留 10% 空间，用于将来的更新。
PCTUSED 40：当数据块的使用空间低于 40% 时，新的行可以插入该块。
INITRANS 1：每个数据块初始分配 1 个事务条目。
MAXTRANS 255：每个数据块最多允许 255 个事务。

13. 分区表的更多示例：

除了按范围分区，还可以使用其他类型的分区。

哈希分区（Hash Partitioning）示例：

CREATE TABLE orders (
    order_id NUMBER(6),
    customer_id NUMBER(6),
    order_date DATE DEFAULT SYSDATE
) PARTITION BY HASH (order_id) 
PARTITIONS 4;

列表分区（List Partitioning）示例：

CREATE TABLE regions (
    region_id NUMBER(4),
    region_name VARCHAR2(25)
) PARTITION BY LIST (region_id) (
    PARTITION p1 VALUES (1, 2),
    PARTITION p2 VALUES (3, 4),
    PARTITION p3 VALUES (5, 6)
);

复合分区（Composite Partitioning）示例：

CREATE TABLE sales (
    sale_id NUMBER(6),
    sale_date DATE,
    amount NUMBER(8,2)
) PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (sale_id)
PARTITIONS 4;