回到顶部暗色模式

数据库系统（6）：SQL数据定义

1. 数据类型

1.1 标识符

SQL标识符 ( $identifier$ ) 用于表示数据库中的对象，如表名、列名、视图名等。用户定义标识符所用的字符必须是字符集 ( $character\ \ set$ ) 中存在的。默认的字符集包括 $A-Z$, $a-z$ 和 $_$ ，也可以另外指定其他字符集。标识符的限制有：

不能长于 $128$ 个字符（在一些方言中会更短）
必须以字母开头
不能有空格

1.2 数据类型

数据类型	声明
布尔型	$BOOLEAN$
字符型	$CHAR$, $VARCHAR$
位类型	$BIT$, $BIT\ \ VARYING$
定点数型	$NUMERIC$, $DECIMAL$, $INTEGER$, $SMALLINT$, $BIGINT$
浮点数型	$FLOAT$, $REAL$, $DOUBLE\ \ PRECISION$
日期时间型	$DATE$, $TIME$, $TIMESTAMP$
间隔型	$INTERVAL$
大对象型	$CHARACTER\ \ LARGE\ \ OBJECT$, $BINARY\ \ LARGE\ \ OBJECT$

1.2.1 布尔型

布尔型包括 $TRUE$ / $FALSE$ 两个值，如果没有 $NOT\ \ NULL$ 约束，也可以为 $NULL$ 。$TRUE$ 大于 $FALSE$ ，对于涉及 $NULL$ 的比较，结果都是 $UNKNOWN$ 。

1.2.2 字符型

字符型数据是由字符集中的字符组成的序列，由方言供应商提供。$CHAR$ 为固定长度，当输入字符串小于指定长度时会使用空格补足。$VARCHAR$ 为变化长度，只会存储输入的字符，不会补足。

1.2.3 位类型

位类型用于定义位字符串，即二进制序列。为了操作方便，有时候会将字符型和位类型合称为字符串型。

1.2.4 定点数型

定点数据类型用于定义准确表示的数值。对于 $NUMERIC$ 和 $DECIMAL$ ，应指定精度 ( $precision$ ) 和 小数位 ( $scale$ ) 。精度为全部位数，小数位为小数部分的位数。

1.2.5 浮点数型

浮点数据类型用于定义非精确的数字，如实数，采用类似于科学计数法的表示方式。为了操作方便，有时会将定点数型和浮点数型合称为数值数据类型。对于 $FLOAT$ ，需要指定精度。而 $REAL$ 和 $DOUBLE\ \ PRECISION$ 的精度则依赖于具体实现。

1.2.6 日期时间型

日期时间类型用于定义一定精度的时间。ISO标准将日期时间类型分为 $YEAR$, $MONTH$, $DAY$, $HOUR$, $MINUTE$, $SECOND$, $TIMEZONE_HOUR$ 和 $TIMEZONE_MINUTE$ 。后面两个字段指定从GMT时区偏移的小时数和分钟数。
$DATE$ 用于存储 $YEAR$, $MONTH$, $DAY$ 。$TIME$ 用于存储 $HOUR$, $MINUTE$, $SECOND$ 。$TIMESTAMP$ 用于存储上述全部。对于 $TIME$ 和 $TIMESTAMP$ ，需要指定时间精度，即秒的精度。在 $TIME$ 和 $TIMESTAMP$ 最后可以声明 $WITH\ \ TIME\ \ ZONE$ ，用于指定 $TIMEZONE_HOUR$ 和 $TIMEZONE_MINUTE$ 。

1.2.7 间隔型

间隔数据类型表示一段时间。每个间隔类型由下列字段的一个连续子集构成：$YEAR$, $MONTH$, $DAY$, $HOUR$, $MINUTE$, $SECOND$ 。间隔数据类型分为年-月间隔和天-时间间隔，前者只能包括 $YEAR$ 和 $MONTH$ ，后者可以从 $DAY$, $HOUR$, $MINUTE$, $SECOND$ 中选择连续的一段。声明格式如下

INTERVAL {{startField TO endField} singleDatetimeField}

startField = YEAR | MONTH | DAY | HOUR | MINUTE [(intervalLeadingFieldPrecision)]
endField = YEAR | MONTH | DAY | HOUR | MINUTE | SECOND [(fractionalSecondsPrecision)]
singleDatetimeField = startField | SECOND [(intervalLeadingFieldPrecision[, fractionalSecondsPrecision])]

1.2.8 大对象型

大对象数据类型用于表示大量数据。SQL可定义三类大对象数据类型：二进制大对象 ( $BLOB$ )、字符大对象 ( $CLOB$ ) 和自然字符大对象 ( $NCLOB$ )，前者为二进制串，后两者都是字符串。

2. 完整性增强特性

完整性约束有五种类型：

必须有值的数据
域约束
实体完整性
引用完整性
一般性约束

2.1 必须有值的数据

某些的列的值不允许为空，可以通过 $NOT\ \ NULL$ 指定。若不指定或者指定为 $NULL$ ，则接受空。ISO的默认值为 $NULL$ 。

2.2 域约束

每一列都有一个域，即合法值的集合。ISO标准在 $CREATE$ 和 $ALTER\ \ TABLE$ 中可以使用 $CHECK$ 子句对列或者整个表进行约束。

sex CHAR NOT NULL CHECK(sex IN('M', 'F'))

也可以使用 $CREATE\ \ DOMAIN$ 定义域。

CREATE DOMAIN DomainName [AS] dateType
[DEFAULT defaultOption]
[CHECK (searchCondition)]

DROP DOMAIN DomainName [RESTRICT|CASCADE]

$DROP\ \ DOMAIN$ 用于删除。$RESTRICT$ 或 $CASCADE$ 指出如果域正在被使用时应采取的行为，$RESTRICTION$ 是只有当域未被使用时才能删除，$CASCADE$ 则将列的域变为基类型定义。

如下所示：

CREATE DOMAIN SexType AS CHAR
    DEFAULT 'M'
    CHECK(VALUE IN ('M','F'));

在创建表时进行使用：

sex SexType NOT NULL

2.3 实体完整性

表中每一行的主键必须是唯一非空值。ISO标准在 $CREATE$ / $ALTER\ \ TABLE$ 中使用 $PRIMARY\ \ KEY$ 支持实体完整性。对于非主键的列，如果要支持唯一性，可以使用 $UNIQUE$ 指定。

2.4 引用完整性

外键是某个列或者列集合，把包含外键的子表中的每个元组与父表中包含候选键的元组关联起来。外键必须是父表中已存在的有效元组。ISO标准可以在 $CREATE$ / $ALTER\ \ TABLE$ 中使用 $FOREIGN\ \ KEY$ 定义外键。

FOREIGN KEY(columnList)
REFERENCES tableName [ON {DELETE | UPDATE} {CASCADE | SET NULL | SET DEFAULT | NO ACTION}]

在子表中，如果试图使用 $INSERT$ / $UPDATE$ 创建与父表的候选键不匹配的外键，则会被拒绝。$ON\ \ DELETE$ / $UPDATE$ 子句决定在父表中进行 $DELETE$ / $UPDATE$ 操作时子表的行为：

$CASCADE$ ：删除/更新父表中的行时会自动删除/更新子表中的列。如果列中的候选键是其他的表的外键，那么会触发级联删除/更新；
$SET\ \ NULL$ ：删除/更新父表中的行时会自动设置子表中外键的值为 $NULL$ ，但要求外键未指定 $NOT\ \ NULL$ ；
$SET\ \ DEFAULT$ ：删除/更新父表中的行时会自动设置子表中外键的值为默认值，但要求外键指定 $DEFAULT$ ；
$NO\ \ ACTION$ ：拒绝对父表进行删除/更新操作，是默认规则。

2.5 一般性约束

对表的更新可能受到的企业规则的约束。ISO允许使用 $CREATE$ / $ALTER\ \ TABLE$ 中的 $CHECK$ / $UNIQUE$ 以及 $CREATE\ \ ASSERTION$ 指定一般性约束。

CREATE ASSERTION assertionName
CHECK(searchCondition)

3. 数据定义

DDL允许创建和删除数据库对象。主要的数据定义语句为：

	$CREATE$	$ALTER$	$DROP$
$SCHEMA$	$\surd$		$\surd$
$DOMAIN$	$\surd$	$\surd$	$\surd$
$TABLE$	$\surd$	$\surd$	$\surd$
$VIEW$	$\surd$		$\surd$

此外虽然许多DBMS支持 $CREATE$ / $DROP\ \ INDEX$ ，但是SQL标准并不支持。

3.1 数据库

根据ISO标准，关系和其他的数据库对象都存在于某个环境 ( $environment$ ) 中，每个环境含有一个或多个目录 ( $catalogs$ )，每个目录包括一组模式 ( $schemas$ )。模式是一组数据库对象的明明集合，该集合中的对象以某种方式相互关连。对象可以是表、视图、域、字符集等，具有相同的所有者并且共享若干的默认值。

CREATE SCHEMA [Name | AUTHORIZATION creatorIdentifier]

DROP SCHEMA Name [RESTRICT | CASCADE]

$AUTHORIZATION$ 指定模式的创建者。$RESTRICT$ 限制只有模式为空时才能删除，$CASCADE$ 则会级联删除所有与该模式相关的对象。

3.2 表

3.2.1 创建

CREATE TABLE tableName
    {(columnName dateType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK(searchCondition)]
    [,...])}
    [PRIMARY KEY(columnList)]
    {[UNIQUE(columnList)[,...]]}
    {[FOREIGN KEY(columnList)
    REFERENCES tableName[(columnList)]]
        [MATCH {PARTIAL | FULL}
        [ON UPDATE referentialAction]
        [ON DELETE referentialAction]][,...]}
    {[CHECK(searchCondition)][,...]}

$PRIMARY\ \ KEY$ 及之后的子句统称为表约束，可以选择下列句子作为前缀：

CONSTRAINT constraintName

通过这种方式，之后可以使用 $ALTER\ \ TABLE$ 删除约束。外键中的 $MATCH$ 选项对外键中存在空的情况施加另外限制。如果指定为 $FULL$ ，则外键全为空或全不为空。如果指定为 $PARTIAL$ ，则外键各列或者全为空，或者说当为空的列被代入时，父表中至少有有一行能满足约束。

3.2.2 修改

ALTER TABLE tableName
[ADD [COLUMN] columnName dataType [NOT NULL] [UNIQUE]
[DEFAULT defaultOption]
[CHECK(searchCondition)]]
[DROP [COLUMN] columnName [RESTRICT | CASCADE]]
[ADD [CONSTRAINT [constraintName]] tableConstraintDefinition]
[DROP CONSTRAINT constraintName [RESTRICT | CASCADE]]
[ALTER [COLUMN] SET DEFAULT defaultOption]
[ALTER [COLUMN] DROP DEFAULT]

3.2.3 删除

DROP TABLE tableName [RESTRICT | CASCADE]

3.3 索引

索引是一种结构，提供了一个或多个列值得快速访问表中元组的方法。索引极大地提高了查询的性能。然而，由于每更新一次基本关系时都可能会更新索引，所以可能会导致额外的开销。SQL并未创建索引的标准，但大多数实现可以支持下列语句：

CREATE [UNIQUE] INDEX indexName
ON tableName(columnName [ASC | DESC][,...])

指定的列构成索引关键字，按照从主到次的顺序排列。索引只能基于基表建立，不能基于视图。

4. 视图

视图是为了得到另一个关系而对基关系进行一次或多次关系操作所得到的动态结果。视图是虚关系，只在需要时根据特定用户的要求临时生成。对于用户来说，视图像真实表一样。DBMS只存储视图的定义，当遇到视图引用时，再将请求转换为对源表的等价请求。这个转换过程称为视图分解 ( $view\ \ resolution$ )。也可以通过视图物化 ( $view\ \ materialization$ ) 实现，即把视图存储在数据库的临时表中，并在基表变化时更新临时表。

4.1 创建

CREATE VIEW viewName [(newColumnName[,...])]
AS subselect [WITH [CASCADE | LOCAL] CHECK OPTION]

$subselect$ 称为定义查询 ( $defining\ \ query$ ) 。在定义查询中使用 $ORDER\ \ BY$ 子句的视图称为分组视图，如果使用了 $JOIN$ 子句的视图称为连接视图。如果指定 $WITH\ \ CHECK\ \ OPTION$ ，那么哪些不满足定义查询中 $WHERE$ 子句的行不会添加到视图的基表中。

4.2 删除

DROP VIEW viewName [RESTRICT | CASCADE]

4.3 视图的局限性

如果视图中的某个列基于聚合函数，那么在访问它语句中，该列只能出现在 $SELECT$ / $ORDER\ \ BY$ 子句中
分组视图不能与基表或者视图连接

4.4 视图的可更新性

没有指定 $DISTINCT$ ，即未删除重复元组
定义查询中 $SELECT$ 列表的每个元素都是列名，即非常量、表达式或者聚合函数，且每个列名最多出现一次
$FROM$ 只指定一个表，并且用户对该表具有一定的权限
$WHERE$ 子句不能包括任何引用了 $FROM$ 子句中的表的嵌套 $SELECT$ 操作
定义查询中不能有 $GROUP\ \ BY$ 或者 $HAVING$ 子句

这些限制的蕴含为：对于任何一个行或者列，DBMS必须能追溯到其源表中相应的行或者列。

4.5 WITH CHECK OPTION

视图中的行均满足定义查询中 $WHERE$ 子句的条件。如果某行经过修改后不满足，则应该将其从视图中移除。相应的，如果某行在修改后满足，则应移入视图中。对于发生这种改变的行，我们称之为迁移行 ( $migrating\ \ rows$ )。$WITH\ \ CHECK\ \ OPTION$ 子句用于禁止行迁移出视图。$LOCAL$ 修饰词在该视图或者其导出视图上进行插入或者更新操作时，禁止行迁移出视图，除非该行也从底层视图或者表中迁移出。$CASCADED$ 修饰词是默认设置，在该视图或者其导出视图上进行插入或者更新操作时，禁止所有行迁移出视图。

4.6 视图的优缺点

4.6.1 优点

数据独立性。即使底层源发生了变化，只要不影响到视图所需的列，那么视图的定义就不需要改变；
实时性。对基表的任何改变都会立即反映到视图上；
提高安全性。用户只能访问可以访问的视图；
降低复杂性。将多表查询转换为单表查询；
方便。只需要提供用户想看到的内容；
用户化。同一个表可以被不同用户以不同方式查看；
数据完整性。通过 $WITH\ \ CHECK\ \ OPTION$ ，任何不满足定义查询中 $WHERE$ 子句的行不会通过视图加入基表，确保了视图的完整性。

4.6.2 缺点

更新局限性。在某些情况下视图无法更新；
结构局限性。一旦视图被定义，那么后续无法修改其查询语句；
性能开销。使用视图会带来一定的性能开销。

5. 事务

事务是由一个或者多个SQL语句组成的逻辑工作单元，通过恢复机制可以保证其原子性。当用户或者程序执行事务初始化语句，如 $SELECT$, $INSERT$, $UPDATE$ 等，SQL事务自动开始。结束事务有四种方式：

$COMMIT$ ，提交事务并持久化数据库
$ROLLBACK$ ，撤销事务并回退至事务执行之前
终止程序。对于程序式SQL，这意味着提交事务。对于非程序式SQL，这意味着撤销事务。

$SET\ \ TRANSACTION$ 语句允许用户配置事务的某些特性：

SET TRANSACTION
[READ ONLY | READ WRITE] |
[ISOLATION LEVEL READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE]

$READ\ \ ONLY/WRITE$ 表示事务的读写型，默认为可读写。隔离级表示事务执行过程中允许的与其他事务的交互程度。

6. 自主访问控制

        DBMS通常提供一种或两种授权机制：自主访问控制 ( $discretionary\ \ access\ \ control$ ) 和强制访问控制 ( $mandatory\ \ access\ \ control$ ) 。在自主访问控制中，每个用户会被授予对特定数据库对象的适当的访问权限。在强制访问控制中，每个数据库对象会被分级。SQL通过 $GRANT$ 和 $REVOKE$ 语句来支持自主访问控制，基于授权标识符 ( $authorization\ \ identifiers$ ) ，所有权 ( $ownership$ ) 和权限 ( $privileges$ ) 。
        授权标识符是SQL用于辨别用户的一般标识符，每个用户都由数据库管理员分配一个授权标识符。通常这个标识符都与一个密码相关联。可以用授权标识符确定用户可以访问哪些对象，能对对象进行哪些操作。
        SQL中的每个对象都有一个所有者，即创建该对象所属模式时 $AUTHORIZATION$ 指定的授权标识符。在最初，只有所有者知道对象存在，并能对其进行任何操作。
        权限是指允许用户对指定基表或视图进行的操作，包括：

$SELECT$ ：查询表中数据
$INSERT$ ：向表中插入数据，可以限制到指定列
$UPDATE$ ：更新表中数据，可以限制到指定列
$DELETE$ ：删除表中数据
$REFERENCES$ ：完整性约束中引用指定表中列
$USAGE$ ：使用域、序列、字符集和转变规则

6.1 授予权限

GRANT {privilegeList | ALL PRIVILEGES}
ON objectName
TO {authorizationIdList | PUBLIC}
[WITH GRANT OPTION]

6.2 撤销权限

REVOKE [GRANT OPTION FOR] {privilegeList | ALL PRIVILEGES}
ON objectName
FROM {authorizationIdList | PUBLIC} [RESTRICT | CASCADE]

数据库系统（6）：SQL数据定义

回到顶部