DB2 - 表

Db2 - 表

表是关系型数据库中用于存储数据的基本逻辑结构。表表示一个实体类型（例如，“员工”、“产品”）。它由列（属性）和行（元组）组成。每行代表该实体的单个实例，每列都有特定的数据类型（例如，INTEGER、VARCHAR、TIMESTAMP）。

现代 Db2 中的表类型

Db2 提供了多种类型的表，每种都针对不同的工作负载和用例进行了优化。

行式组织（常规）表：默认且最常见的表类型。数据按行存储。适用于频繁进行单行 INSERT、UPDATE 和 DELETE 操作的联机事务处理（OLTP）工作负载。
列式组织表（‘BLU 加速’）：一种高性能表类型，数据按列存储。非常适合涉及对大型数据集进行复杂查询的联机分析处理（OLAP）和商业智能（BI）工作负载。它具有卓越的压缩和数据跳过功能。
分区表：允许根据分区键将表数据分布到多个存储对象（称为数据分区）中。对于管理超大型数据库（VLDB）至关重要，因为它能提高查询性能并简化数据生命周期管理（例如，附加新数据分区或分离旧数据分区）。
时态表：自动跟踪数据更改历史。这对于审计、合规性和历史分析至关重要。Db2 支持系统周期（跟踪数据库中数据何时更改）、业务周期（跟踪数据在现实世界中何时有效）和双时态表（结合两者）。
实体化查询表（MQTs）：这些表由查询定义，其内容是该查询的预计算结果。MQTs 用于显著提高重复出现的复杂查询的性能，例如报告仪表板中的查询。
临时表（DGTTs）：用于在会话期间存储瞬态数据。它们不被记录且不出现在系统目录中，这使得它们在复杂过程中的中间工作效率很高。
传统表类型：多维聚簇（MDC）、插入时间聚簇（ITC）和范围聚簇表（RCT）存在于特定的传统用例中，但对于新应用程序，它们通常被分区表或列式组织表等现代替代方案取代。

使用现代最佳实践创建表

一个定义良好的表不仅包括列和数据类型，还包括约束以确保数据完整性。

语法：

CREATE TABLE <schema_name>.<table_name> (
    <column1_name> <data_type> [CONSTRAINTS],
    <column2_name> <data_type> [CONSTRAINTS],
    ...
    PRIMARY KEY (<column_name>)
) IN <tablespace_name>;

示例：创建一个员工表，包括主键、自增 ID、非空姓名和默认职位角色。

db2 "CREATE TABLE professional.employee(
    id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
    employee_code VARCHAR(20) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    job_role VARCHAR(50) DEFAULT 'Analyst',
    join_date DATE,
    salary DECIMAL(10, 2),
    PRIMARY KEY(id)
) IN userspace1"

输出：

DB20000I  The SQL command completed successfully.

查询表元数据

您可以使用系统目录视图或 DESCRIBE 命令来检查表结构。

示例：列出当前数据库中用户创建的表。

db2 "SELECT SUBSTR(tabschema,1,12) AS schema, SUBSTR(tabname,1,20) AS table_name, tbspace
     FROM syscat.tables WHERE type = 'T' AND tabschema NOT LIKE 'SYS%'"

输出（示例）：

SCHEMA       TABLE_NAME           TBSPACE
------------ -------------------- ----------
PROFESSIONAL EMPLOYEE             USERSPACE1

  1 record(s) selected.

示例：描述 employee 表的列。

db2 describe table professional.employee

输出（示例）：

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- -------
ID                              SYSIBM    INTEGER                      4     0 No
EMPLOYEE_CODE                   SYSIBM    VARCHAR                     20     0 No
FULL_NAME                       SYSIBM    VARCHAR                    100     0 No
JOB_ROLE                        SYSIBM    VARCHAR                     50     0 Yes
JOIN_DATE                       SYSIBM    DATE                         4     0 Yes
SALARY                          SYSIBM    DECIMAL                     10     2 Yes

  6 record(s) selected.

隐式隐藏列

您可以将列定义为 IMPLICITLY HIDDEN（隐式隐藏）。这些列不会被 SELECT * 查询返回，并且在没有显式列列表的 INSERT 语句中会被忽略。这对于用于审计但通常不被应用程序需要的系统管理列（如 row_change_timestamp）非常有用。

示例：创建一个带有隐藏列的表，用于跟踪行上次更新的时间。

db2 "CREATE TABLE professional.customer(
    cust_id INT NOT NULL PRIMARY KEY,
    full_name VARCHAR(100),
    last_updated TIMESTAMP NOT NULL
        WITH DEFAULT CURRENT TIMESTAMP
        IMPLICITLY HIDDEN
)"

当您运行 SELECT * FROM professional.customer 时，last_updated 列将不会显示。要查看它，您必须在查询中显式指定其名称：SELECT cust_id, full_name, last_updated FROM professional.customer。

数据操作（INSERT, SELECT）

示例：向 customer 表插入数据（请注意我们没有为隐藏列提供值）。

db2 "INSERT INTO professional.customer(cust_id, full_name) VALUES (101, 'Anika Sharma')"

示例：检索数据，显式请求隐藏列。

db2 "SELECT cust_id, full_name, last_updated FROM professional.customer"

输出（示例）：

CUST_ID     FULL_NAME                 LAST_UPDATED
----------- ------------------------- --------------------------
        101 Anika Sharma              2023-10-27-10.30.00.123456

  1 record(s) selected.

更改和删除表

ALTER TABLE 语句用于修改表的结构，例如添加列、更改数据类型或重命名列。

语法：添加新列

ALTER TABLE <table_name> ADD COLUMN <column_name> <data_type>;

语法：重命名现有列

ALTER TABLE <table_name> RENAME COLUMN <old_name> TO <new_name>;

DROP TABLE 命令删除表及其所有数据。这是一个不可逆的操作，请务必谨慎使用。

语法：

DROP TABLE <table_name>;

示例：永久删除 customer 表。

db2 "DROP TABLE professional.customer"

实用指导：选择正确的表类型

对于电子商务网站、银行系统（OLTP）：从行式组织表开始。
对于数据仓库、BI 仪表板（OLAP）：使用列式组织表以获得显著的性能提升。
对于超过数千兆字节或数太字节的表：使用分区表以获得更好的管理和查询性能。
对于需要完整审计跟踪的表：实施时态表。