数据定义

SQL数据类型

SQL标识符

用户自定义SQL标识符包含a-z、A-Z、0-9、_

限制：标识符<=128个字符，以字母开头、不能有空格

SQL标量数据类型

SQL语句包含保留字（reserved words）和用户自定义字

完整性（integrity）增强特性

必须有值的数据

某些列的值必须为有效值，不允许为空（空格和0可以）——NOT NULL

域约束（Domain Constraint）

域：合法值的集合，如性别为男女

约束方式：

1. CHECK子句，允许对列或整个表定义约束：CHECK(searchCondition)

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

ISO可以使用CREATE DOMAIN显式定义域

CREATE DOMAIN SexType AS CHAR
DEFAULT 'M'
CHECK (VALUE IN('M','F'));
sex SexType NOT NULL    --定义列sex可以用域名SexType代替数据类型CHAR

还可以查表确保输入的值均为表中存在的

CREATE DOMAIN BranchNumber AS CHAR(4)
CHECK (VALUE IN (SLECT branchNo FROM Branch));

撤销域约束：

DROP DOMAIN DomainName [RESTRICT | CASCADE]

实体完整性（entity integrity）

表中每一行的主关键字必须是唯一的非空值

PRIMARY KEY()指定关键字组合（已同时满足唯一且非空），每个表中只能使用一个该子句，可用UNIQUE保证列的唯一性

引用完整性（Referential Integrity）

外部关键字：某个列或列集合把包含外部关键字的子表中的每个元组与父表中包含匹配候选关键字值的元组关联起来

引用完整性：外部关键字必须是父表中已存在的有效元组

使用FOREIGN KEY子句定义外部关键字

FOREIGN KEY(branchNo) REFERENCES Branch

定义外部关键字后试图在子表中使用UPDATE/INSERT创建与 父表中候选关键字不匹配的外部关键字，SQL拒绝操作；试图在父表中UPDATE/DELETE更新或删除与子表有匹配行的候选关键字时，SQL 将根据 FOREIGN KEY ⼦句中的 ON UPDATE 或 ON DELETE ⼦句来决定如何执⾏该操作

• CASCADE：删除⽗表中的⾏并且自动删除⼦表中匹配的行。由于删除的行可能有侯选关键字

是另⼀个表的外部关键字，所以这些表的外部关键字规则就会以级联的方式相继触发。

• SET NULL：删除⽗表中的元组，且设置⼦表中的外部关键字为 NULL。只有当外部关键字列

没有指定为 NOT NULL 时，这样做才是有效的

• SET DEFUALT：删除⽗表中的元组，且设⼦表中的外部关键字为默认值。只有当外部关键字

列指定了 DEFAULT 值时，这样做才是有效的

• NO ACTION：拒绝对⽗表进⾏删除操作；ON DELETE规则默认设置为NO ACTION

FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL
-- Staff表中员工纪录被删除，对应表PropertyForRent中相应的staffNo列值设为NULL

FOREIGN KEY (ownerNo) REFERENCES PrivateOwner ON UPDATE CASCADE
-- PrivateOwner中的业主编号更新，表PropertyForRent中的相应列更新值

一般性约束（General Constraints）

表的更新可能受企业规则的约束，如房屋租赁公司禁⽌员⼯同时管理100以上房产

CREATE ASSERTION AssertionName
CHECK (searchCondition)
-- 一般性约束格式
-- 禁止员工同时管理100以上的房产
CREATE ASSERTION StaffNotHandlingTooMuch
CHECK ( NOT EXISTS (SELECT staffNo
                    FROM PropertyForRent
                    GROUP BY staffNo
                    HAVING COUNT(*) > 100))

数据定义

创建

创建数据库

create database 数据库名;

创建表【"[]"表示可以省略，"|"表示隔开的几项可取其一】

create table 表名(列名 数据类型 [Primary key | Unique][Not null][,列明 数据类型 [Not null],……]);

Primary key：主键约束，每个表只能一个

Unique：唯一性约束（候选键），每个表可以多个

Not null：非空约束，不允许有空值

一个完整的加了约束创建表

CREATE TABLE PropertyForRent (
    propertyNo VARCHAR(5) NOT NULL, 
    street VARCHAR(25) NOT NULL,
    city VARCHAR(15) NOT NULL,
    postcode VARCHAR(8) NOT NULL,
    type VARCHAR(15) NOT NULL,
    rooms SMALLINT NOT NULL DEFAULT 4, 
    rent DECIMAL(6,2) NOT NULL DEFAULT 600, 
    ownerNo VARCHAR(5) NOT NULL, 
    staffNo VARCHAR(5),
    branchNo CHAR(4) NOT NULL,
    PRIMARY KEY (propertyNo),
    FOREIGN KEY (staffNo) REFERENCES Staff 
    ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (ownerNo) REFERENCES PrivateOwner
    ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY (branchNo) REFERENCES Branch
    ON DELETE NO ACTION ON UPDATE CASCADE
);

修改表（ALTER TABLE）

• 在表中添加一个新列
• 从表中删除一个列
• 添加一项新的表约束
• 删除一项表约束
• 设置列默认值
• 删除列默认值

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]

删除表

DROP TABLE TableName [RESTRICT | CASCADE]

DELETE是删除表中的行，仍保留表的结构

创建索引

索引：一种结构，提供了基于一个或多个列值快速访问表中元组的方法。极大的提高了查询的性能

但是每一次更新基本关系时系统都可能更新索引，所以将导致额外的开销；通常建立索引是在表使用一段时间后，规模有所增加而又需满足特殊的查找要求

CREATE [UNIQUE] INDEX IndexName
ON TableName (columnName [ASC | DESC][,…])

CREATE INDEX RentInd ON PropertyNoInd ON PropertyForRent (propertyNo);

根据表PropertyForRent创建了名为RentInd的索引文件，记录将按照city列值的字母顺序排序，若city值相同则按rent列排序

删除索引

DROP INDEX IndexName;

视图（view）

视图 | 为了得到另一个关系而对基关系进行一次或多次关系操作所得到的动态结果。视图是虚关系，即在数据库中不存在，需要时根据特定用户的要求临时生成

创建视图

CREATE VIEW ViewName [(newColumnName [,…])]
AS subselect [WITH [CASCADED | LOCAL] CHECK OPTION]

subselect是定义查询（查询子句）；若省略列名则视图中的列名即采用subselect子句中相对应的列的名字

水平视图：一个或多个表中选定的元组

垂直视图：一个或多个表中选定的列

删除视图

DROP VIEW ViewName [RESTRICT | CASCADE]

视图局限性

• 若视图的某个列基于聚集函数得到的，在访问该视图的查询语句中，该列只能出现在SELECT和ORDER BY子句中，不能再作为任何聚集函数的参数
• 分组视图（GROUP BY子句查询产生的视图）不能与基表或视图连接

视图的优缺点

自主访问控制

每个用户被授予对特定的数据库对象的适当的访问权力

ISO标准定义的权限：

• SELECT：检索表中数据的权限
• INSERT：向表中插入新行的权限
• UPDATE：更新表中的行的权限
• DELETE：删除表中的行的权限
• REFERENCES：完整性约束中引用指定表中列的权限
• USAGE：使用域、序列、字符集和转变规则

授予其他用户权限

GRANT {PrivilegeList | ALL PRIVILEGES}
ON ObjectName
TO {AuthorizationIdList | PUBLIC}
[WITH GRANT OPTION]

PrivilegeList：逗号隔开的一个或多个权限组成

ALL PRIVILEGES：授予用户所有权限

PUBLIC：允许现在或未来授权用户访问数据

WITH GRANT OPTION：AuthorizationIdList的用户可以传递权限给其他用户

eg：

-- 授予部分用户全部权限
GRANT ALL PRIVILEGES
ON Staff
TO Manager WITH GRANT OPTION;

-- 授予部分用户查询和更新salary列的权限
GRANT SELECT, UPDATE (salary)
ON Staff
TO Personnel, Director;

-- 授予所有用户查询权限
GRANT SELECT
ON Branch
TO PUBLIC;

撤销权限

REVOKE [GRANT OPTION FOR] {PrivilegeList | ALL PRIVILEGES}
ON ObjectName
FROM {AuthorizationIdList | PUBLIC} [RESTRICT | CASCADE]

GRANT OPTION FOR：通过GRANT中的WITH GRANT OPTION传递的权限独立地被撤销

但是用户从其他用户那里得到的权限并不受这个REVOKE影响