达梦数据库学习-表的管理

一、前言

表是数据库中数据存储的基本单元，是对用户数据进行读和操纵的逻辑实体。表由列和行组成，每一行代表一个单独的记录。表中包含一组固定的列，表中的列描述该表所跟踪的实体的属性，每个列都有一个名字及各自的特性。

列的特性由两部分组成：数据类型（dataType）和长度（length）。对于 NUMERIC、DECIMAL 以及那些包含秒的时间间隔类型来说，可以指定列的小数位及精度特性。在 DM 系统中，CHAR、CHARACTER、VARCHAR 数据类型的最大长度由数据库页面大小决定，数据库页面大小在初始化数据库时指定。DM 系统具有 SQL-92 的绝大部分数据类型，以及部分SQL-99、Oracle 和 SQL Server 的数据类型。

为了确保数据库中数据的一致性和完整性，在创建表时可以定义表的实体完整性、域完整性和参考完整性。实体完整性定义表中的所有行能唯一地标识，一般用主键、唯一索引、UNIQUE 关键字及 IDENTITY 属性来定义；域完整性通常指数据的有效性，限制数据类型、缺省值、规则、约束、是否可以为空等条件，域完整性可以确保不会输入无效的值；参考完整性维护表间数据的有效性、完整性，通常通过建立外键联系另一表的主键来实现。

如果用户在创建表时没有定义表的完整性和一致性约束条件，那么用户可以利用 DM 所提供的基表修改语句来进行补充或修改。DM 系统提供基表修改语句，可对基表的结构进行全面的修改，包括修改基表名、列名、增加列、删除列、修改列类型、增加表级约束、删除表级约束、设置列缺省值、设置触发器状态等一系列修改功能。

二、管理表的准则

1.设计表

表是数据库设计过程中的基本构件，基于来自应用开发者的有关应用如何运作和所期望的数据类型，数据库管理员应与应用开发者一起工作，并认真规划每个表，具体需要做到以下几点：

1. 规范化表，估算并校正表结构，使数据冗余达到最小；
2. 为每个列选择合适的数据类型，是否允许为空等，并根据实际情况判断是否需要对列进行加密或压缩处理；
3. 建立合适的完整性约束，管理约束可查看 15 章管理完整性约束的内容；
4. 建立合适的聚集索引。每个表（列存储表，堆表除外）都含一个聚集索引，默认以ROWID 建立，而建立合适的聚集索引，可以有效加快表的检索效率；
5. 根据实际需要，建立合适类型的表。DM 支持的表类型包括普通表、临时表、水平分区表、垂直分区表、堆表和列存储表。

2. 指定表的存储空间上限

在创建表时指定 SPACE LIMIT 子句，可以对表的存储空间指定上限。DM 支持对表的存储空间指定大小，单位是 MB，即表的大小可由管理员指定，便于表的规模管理。当表的所有索引所占用的存储空间超过指定大小时，表将不能再新增数据。

3.指定表的存储位置

创建表时，在 STORAGE 子句中，可对表指定存储的表空间。如果没有指定，则该表将创建在用户的默认表空间中。在创建表时，通过指定合适的表空间，有以下优点：

1. 提高数据库系统的性能，因为不同的数据库表可能对应不同的数据文件，可减少对相同文件的竞争；
2. 减少数据库管理的时间，数据库表分布在不同的表空间中，即使一个表空间损坏，也不影响其他表空间上数据库表的正常访问。

三、创建表

如果要在所属模式中创建新表，需要有 CREATE TABLE 数据库权限；而要在其他用户的模式中创建新表，则需要有 CREATE ANY TABLE 数据库权限。创建表时，应当为表指定一个表空间，否则，表将在 MAIN 创建。下面给出一个创建一个简单表的例子。

CREATE TABLE EMPLOYEE ( 
EMPNO INT PRIMARY KEY, 
ENAME VARCHAR(15) NOT NULL, 
JOB VARCHAR(10), 
MGR INT
CONSTRAINT EMP_FKEY REFERENCES EMPLOYEE(EMPNO), 
HIREDATE DATE DEFAULT (CURDATE), 
SALARY FLOAT, 
DEPTNO TINYINT NOT NULL 
CONSTRAINT DEPT_FKEY REFERENCES DEPT(DEPTNO))
STORAGE (
INITIAL 50, 
NEXT 50, 
MINEXTENTS 10,
FILLFACTOR 80,
ON USERS);

在上述 CREATE TABLE 语句中，在 users 表空间上建立了 employee 表，并有几个完整性约束，其中包含定义在不同列上的一个主键和外键。

创建表之后，可以使用 INSERT 命令插入数据或使用达梦数据导入导出工具装载数据，还可以直接使用 CREATE TABLE AS SELECT 创建一个表。

四、更改表

想更改的表如果在所属的模式中，用户必须具有 ALTER TABLE 数据库权限；若在其他模式中，用户必须有 ALTER ANY TABLE 的数据库权限。通过更改表，用户可以对数据库中的表作如下修改：

1. 添加或删除列，或修改现有的列的定义（列名、数据类型、长度、默认值）。其中，对于添加列，当设置 INI 参数 ALTER_TABLE_OPT 为 1 时，添加列采用查询插入实现，可能会导致 ROWID 的改变；ALTER_TABLE_OPT 为 2 时，系统开启快速加列功能，对于没有默认值或者默认值为 NULL 的新列，系统内部会标记为附加列，能够达到瞬间加列的效果，此时 ROWID 不会改变，若有默认值且默认值不为 NULL，则仍旧采取查询插入实现。
2. 添加、修改或删除与表相关的完整性约束；
3. 重命名一个表；
4. 启动或停用与表相关的完整性约束；
5. 启动或停用与表相关的触发器；
6. 修改表的 SPACE LIMIT；
7. 增删自增列。

五、删除表

当一个表不再使用时，可以将其删除。删除表时，将产生以下结果：

1. 表的结构信息从数据字典中删除，表中的数据不可访问；
2. 表上的所有索引和触发器被一起清除；
3. 所有建立在该表上的同义词、视图和存储过程变为无效；
4. 所有分配给表的簇标记为空闲，可被分配给其他的数据库对象。
   一般情况下，普通用户只能删除自己模式下的表。若要删除其他模式下的表，则必须具有 DROP ANY TABLE 数据库权限。
   以下语句可删除 employee 表：

DROP TABLE employee;

如果要删除的表被其他表引用，即其他表的外键引用了表的任何主键或唯一键，则需要
在 DROP TABLE 语句中包含 CASCADE 选项，如：

DROP TABLE employee CASCADE;

六、清空表

有些情况下，当表的数据不再使用时，需要删除表的所有行，即清空该表。DM7 支持以下方式来删除表中的所有的行：

1. 使用 DELETE 语句；
2. 使用 DROP 和 CREATE 语句；
3. 使用 TRUNCATE 语句。

DELETE FROM employee;

DROP TABLE EMPLOYEE;
CREATE TABLE EMPLOYEE(…);

TRUNCATE TABLE EMPLOYEE;

七、查看表信息

1.查看表定义

创建表后，可以通过 SP_TABLEDEF 系统过程查看表的定义。

CALL SP_TABLEDEF('SYSDBA', 'EMPLOYEE');

DM 通过提供的 TABLEDEF 函数来显示当前表的定义。当表多次进行 ALTER TABLE 后，
显示的表定义将是最后一次修改后的建表语句。

2.查看自增列信息

DM 支持 INT 和 BIGINT 两种数据类型的自增列，并提供以下函数查看表上自增列的当
前值、种子和增量等信息：

1. IDENT_CURRENT：获得表上自增列的当前值；
2. IDENT_SEED：获得表上自增列的种子信息；
3. IDENT_INCR：获得表上自增列的增量信息。

CREATE TABLE IDENT_TABLE (
C1 INT IDENTITY(100, 100),
C2 INT
);
SELECT IDENT_CURRENT('SYSDBA.IDENT_TABLE');
SELECT IDENT_SEED('SYSDBA.IDENT_TABLE');
SELECT IDENT_INCR('SYSDBA.IDENT_TABLE');

3.查看表的空间使用情况

DM 使用段、簇和页实现数据的物理组织。DM 支持查看表的空间使用情况，包括：

1. TABLE_USED_SPACE：已分配给表的页面数；
2. TABLE_USED_PAGES：表已使用的页面数。

CREATE TABLE SPACE_TABLE ( C1 INT,
C2 INT
);
SELECT TABLE_USED_SPACE('SYSDBA','SPACE_TABLE');
SELECT TABLE_USED_PAGES('SYSDBA','SPACE_TABLE');