SQL动词

SQL功能	动词
数据定义	CREATE,DROP,ALTER
数据查询	SELECT
数据操纵	INSERT,UPDATE,DELETE
数据控制	GRANT,REVOKE

模式

-- 创建
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>

-- 删除
DROP SCHEMA <模式名><CASCADE|RESTRICT>

定义模式实际是定义一个命名空间，在这个空间上可以进一步定义该模式包含的数据库对象，如基本表、视图、索引等

• CASCADE: 级联删除，删除模式同时删除下属所有数据库对象
• RESTRICT: 如果定义了下属数据库对象，则拒绝该删除语句的执行

表

-- 创建表
CREATE TABLE <表名> (
  <列名> <列的数据类型> [<列级完整性约束条件>]
  [, <列名> <列的数据类型> [<列级完整性约束条件>]]
  ...
  [, <表级完整性约束条件>]
)

-- 修改表
ALETER TABLE <表名>
[ADD <新列名> <数据类型> [完整性约束名]]
[DROP <完整性约束名>]
[MODIFY <列名> <数据类型>]

-- 删除表
DROP TABLE <表名>

完整性

• 实体完整性：列级约束，主键（码）约束(PRIMARY KKEY)
• 参照完整性：表级约束，外键（FOREIGN KEY）
• 业务规则完整性: 是否满足布尔表达式(CHECK)、非空约束(NOT NULL)、唯一性约束(UNIQUE)、自定义业务(如：性别只能是男或女)等等。

唯一性可以为空，主键唯一且不能为空。

索引

建立索引是加快查询速度的有效手段，有些DBMS自动建立以下列上的索引

• PRIMARY KEY
• UNIQUE

并且由DBMS自动维护和使用。

-- 创建索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名> [<次序>] [,<列名> [<次序>]])

-- 删除索引
DROP INDEX <索引名>

次序指定索引值的排列次序，升序ASC，降序DESC，缺省值ASC。

UNIQUE: 唯一值索引，表明每一索引值只对应唯一的数据，有重复值的不能建唯一值索引。

CLUSTER：聚族索引，在某列上建立聚族索引想当于以该列内容项排序，每个表内只能存在一个聚族索引。比如在学生名字上建立聚族索引，会把姓名为张三的数据项放到一起，李四放到一起。

查询

-- 查询
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] ...
FROM <表名或视图名>[,<表名或视图名>]...
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];

-- SELECT 子句得目标列可以是已有列名、字符串常量、算术表达式、函数。
SELECT sname, 'year of birth:', 2020-sage, ISLOWER(sdept);

• DISTINCT子句消除取值重复得行。
• WHERE子句指定查询条件。
• GROUP BY子句对查询结果按指定列的值分组，该属性列值相等的元组为一个组。
• HAVING短语：筛选出只有满足指定条件的组，即作用于GROUP BY生成的分组。
• ORDER BY子句：对查询结果表按指定列值得升序或者降序排序。排序时，空值认为是无限大，升序时最后，降序时最先。

常用查询条件

• 比较： =, >, <, >=, <=, !=或<>, !>, !<；用 NOT + 上述运算符。
• 确定范围：BETWEEN AND, NOT BETWEEN AND
• 确定集合：IN, NOT IN
• 字符匹配：LIKE, NOT LIKE
• 空值：IS NULL, IS NOT NULL
• 多重条件：AND, OR

字符串匹配

通配符：

• % 匹配任意长度字符
• _ 匹配单个字符
• ESCAPE 短语跳过转义字符''，字符串本身含有%或_的需要用转义字符。如：

-- 查询 db_design 相关课程的课程号
SELECT c_no, c_credit FROM course WHERE c_name LIKE 'db\_design' ESCAPE '\';

常用集函数

• 计数 COUNT ([DISTINCT|ALL] *|<列名>)
• 计算总和 SUM ([DISTINCT|ALL] <列名>)
• 计算平均值 AVG ([DISTINCT|ALL] <列名>)
• 查询最大值 MAX ([DISTINCT|ALL] <列名>)

注意: 上述集函数会排除空值（NULL）；未对查询结果分组，集函数作用于整个查询结果，分组后，集函数分别作用于每个分组。

多表查询

连接查询

定义：同时涉及多个表的查询称为连接查询

三种连接查询方式：

1. 嵌套循环法，表A中的数据依次和表B中的数据比较，若相关条件符合，则连接。表A中有m条数据，表B中有n条数据，共需要m*n次比较
2. 排序合并法，若表A、B要比较的字段同为no，先将表A、B中的no字段分别排序，表A中的第一条元组分别和表B中的分别比较，若相同（记为i）则连接，然后用A中第二条元组从B中第i+1依次向后，重复上述过程直到完成；需要m+n次比较。
3. 索引连接，A表无要求，B表相关字段建立索引，比较A表和索引表对应字段，得益于索引，速度会高于嵌套循环法。

速度：排序合并法 > 索引连接 > 嵌套循环法

几种连接方式

1. 等值与非等值连接：连接运算符为等号（=）的称为等值连接。
2. 自然连接：等值连接去掉重复的属性列。
3. 自身连接：与本表自身连接，用于类似递归的操作，即将一张表假象成两张，每张需要一个别名。如：

-- 查询每门课的间接先修课（即先修课的先修课）
SELECT first.c_no, second.c_pno FROM course first, course second WHERE first.c_pno = second.cno;

4. 外连接

普通连接操作只输出满足连接条件的元组，外连接操作以指定表为连接主体，将主体中不满足连接条件的元组也一并输出。比如连接查询学生选课情况，自然连接输出选课表中的存在的元组，而外连接会将选课表中不存在（未选课的学生）的连接结果一并输出，只是非主体表结果为空（万能的空行）。

左外连接：外连接符出现在连接条件的左边。

右外连接：外连接符出现在连接条件的右边。

5. 复合条件连接

WHERE子句中含有多个连接条件时，称为复合条件连接。

6. 多表连接（三个及以上的表）

嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或者HAVING短语的条件中的查询称为嵌套查询。

嵌套查询一般可以和连接查询相互转换。

分类

• 不相关子查询：子查询的查询条件不依赖于父查询
• 相关子查询：子查询的查询条件依赖于父查询

不同谓词引出的子查询

1. 带有IN谓词的子查询

-- 查询和学号1001选了相同课程的学生的学号

SELECT s_no FROM course WHERE c_no IN (
  SELECT c_no FROM course WHERE s_no = '1001'
)

2. 带有比较运算符的子查询

  -- 查询与学号为1001的学生的同系同学，注意子查询只能写在比较运算符后面
  SELECT s_no, s_name FROM student WHERE s_dept = (
    SELECT s_dept FROM student WHERE s_no = '1001' 
  );

3. 带有ANY或ALL谓词的子查询

-- 查询其他系中比信息系任意一个年龄小的学生的姓名和年龄
SELECT s_name, s_age FROM student WHERE s_age < ANY (
  SELECT s_age FROM student WHERE s_dept = 'IS'
) AND s_dept <> 'IS';

-- 通常可以优化以上查询，比任意小就是比最小的小
SELECT s_name, s_age FROM student WHERE s_age < (
  SELECT MIN(s_age) FROM student WHERE s_dept = 'IS'
) AND s_dept <> 'IS';

4. 带有EXISTS谓词的子查询

-- 查询选修了一号课程的学生姓名
SELECT s_name FROM student WHERE EXISTS (
  SELECT * FROM sc WHERE s_no = student.s_no AND c_no = '1'
);

-- 同连接查询的转换<!>
SELECT s_name FROM student, sc WHERE student.s_no = sc.s_no AND sc.c_no = '1';

-- 查询没有选修一号课程的学生姓名，连接查询无法实现
SELECT s_name FROM student WHERE NOT EXISTS (
  SELECT * FROM sc WHERE s_no = student.s_no AND c_no = '1'
);

一些带EXISTS或NOT EXISTS的子查询无法用其他形式的子查询替换，所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询替换。

集合查询

集合查询是对查询结果的集合进行操作。

标准SQL直接支持的集合操作种类：并操作（UNION）

一般商用数据库支持：并（UNION）、交（INTERSECT）、差（NIMUS）

注意：OEDER BY子句只能用于对最终查询结果排序，不能对中间结果排序，任何时候。ORDER BY子句只能出现在查询语句最后。

-- 将两个子查询结果取并
SELECT ... FROM ... WHERE 
UNION
SELECT ... FROM ... WHERE

数据的更新

插入

-- 插入单个元组
INSERT INTO <表名> [(<属性列1>[, <属性列2>...])]
VALUES (<常量1>[, <常量2>]...)

-- 插入子查询结果
INSERT INTO <表名> [(<属性列1>[, <属性列2>...])]
子查询

-- 或者
SELECT <属性列1>[, <属性列2>...] INTO <表名> ... FROM ...

修改

-- 更新数据，where子句中的条件可以包含子查询的结果
UPDATE <表名> SET <列名> = <表达式>[, <列名> = <表达式>...]
[WHERE <条件>];

不允许破坏完整性约束：

1. 实体完整性：主码不允许修改
2. 用户定义的完整性：NOT NULL约束，UNIQUE约束，值域的约束。

删除

-- 删除数据，不选where子句会删除所有数据，同样，where子句中的条件可以包含子查询的结果
DELETE FROM <表名> [WHERE <条件>];

不允许破坏完整性约束：参照完整性，不允许删除，级联删除

视图

定义：视图是一个或几个基本表（或视图）导出的表。

它与基本表不同，是一个虚表。数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍然存在原来的基本表中。所以一旦基本表中的数据发生变化，从视图中查询到的数据也就随之改变了。

_{《数据库系统概论》 王珊}

-- 创建视图
CREATE VIEW <视图名> [(<列名> [, <列名>]...)]
  AS <子查询>
[WITH CHECK OPTION];

-- 删除视图

DROP VIEW <视图名>;

WITH CHECK OPTION：透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）。

-- 创建信息系学生视图，并要求透过该视图进行的更新操作只涉及信息系的学生。
CREATE VIEW v_is_student
  AS SELECT * FROM student WHERE s_dept = 'IS'
WITH CHECK OPTION;

对于视图v_is_student，插入、修改、删除化工系的同学则不允许执行，若无WITH CHECK OPTION则可以插入、修改，事实上删除操作时，视图中查不到相关数据，不会影响数据，不会直接报错。

某些情况需要明确指定视图所有列名：

1. 某个目标列是集函数或列表达式
2. 目标列为 * （实测mysql不需要）
3. 多表连接时选出几个同名列作为字段
4. 需要在视图中为某个列启用新的更为合适的名字

更新和查询同普通表，但是要根据定义注意操作的有效性。比如用集函数AVG创建的视图则不能更新，因为无法将插入的数据合理地转化为实体表的数据。

视图查询和更新方法

1. 实体化视图（View Materialization）：进行有效性检查，执行视图定义查询出临时表，再从临时表中筛选结果（对临时表对应的数据进行更新）。
2. 视图消解法（View Resolution）：进行有效性检查，将视图定义和用户查询等价转化成对基本表的查询（更新）。

授权

-- 创建用户
CREATE USER <username> [WITH] [DBA|RESOURCE|CONNECT];

-- 授权
GRANT <权限>[,权限...]
  ON <对象类型> <对象名> [,<对象类型><对象名>...]
  TO <用户>[,<用户>]
[WITH GRANT OPTION];

-- 回收
REVOKE <权限>[,权限...]
  ON <对象类型><对象名>[,<对象类型><对象名>...]
FROM <用户>[,<用户>...[CASCADE|RESTRICT]];

• DBA|RESOURCE|CONNECT

  -	创建用户	创建模式	创建表	登录数据库，操作数据
  DBA	可以	可以	可以	可以
  RESOURCE	不可以	不可以	可以	可以
  CONNECT	不可以	不可以	不可以	可以，但必须拥有相应权限

• WITH GRANT OPTION子句：获得该权限的用户还可以把这种权限再授予其他用户，如果没有该子句，则获得权限的用户不能传播该权限
• CASCADE|RESTRICT：CASCADE 就是收回WITH GRANT OPTION级联授与的权限，RESTRICT就是不允许收回。

通过角色控制权限

-- 创建角色
CREATE ROLE <角色名>;

-- 给角色授权
GRANT <权限> [, <权限>...]
  ON <对象类型> 对象名 
  TO <角色> [, <角色>...];
  
-- 将一个角色属于其他的角色或用户
GRANT <角色1> [, <角色2>...]
  TO <角色3> [,<用户1>...]
[WITH ADMIN OPTION];

-- 角色权限的收回
REVOKE <权限> [, <权限>...]
  ON <对象类型> <对象名>
  FROM <角色> [, <角色>...]
  
-- 如：
REVOKE SELECT ON TABLE student FROM role1;

WITH ADMIN OPTION子句：如果指定了该子句，则获得某种权限的角色或用户还可以把这种权限再授予其他角色。

完整性约束

定义完整性约束的三种方式：

-- 列级约束
CREATE TABLE student (
  no varchar(64) PRIMARY KEY,
  name varchar(8)
)
-- 表级约束
CREATE TABLE student (
  no varchar(64),
  name varchar(8),
  PRIMARY KEY(no)
);
-- 完整性约束命名子句
CONSTRAINT <完整性约束条件名> [ PRIMARY KEY 短语 | FOREIGN KEY 短语 | CHECK 短语]
-- 如：
CREATE TABLE student (
  no varchar(64),
  name varchar(8),
  CONSTRAINT PK_NO PRIMARY KEY(no)
);

触发器

触发器（trigger）是用户定义在关系表上的一类由事件驱动的特殊过程。比如定义触发器t1为当删除学生记录时向日志表中插入一条日志记录。

-- 创建
CREATE TRIGGER <触发器名>
{BEFORE|AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS <变量>
FOR EACH {ROW|STATEMENT}
[WHEN <触发条件>]<触发动作体>;

-- 删除
DROP TRIGGER <触发器名> ON <表名>;

• FOR EACH {ROW|STATEMENT}：定义触发器类型时行级(ROW)触发器还是语句级(STATEMENT)触发器。行级触发器是触发的事件影响几行数据触发几次，语句级触发器是一个语句只触发一次。
• REFERENCING NEW|OLD ROW AS <变量>：对于行级触发器，NEWROW和OLDROW分别指代操作前后行的内容；对于语句级触发器，NEWTABLE和OLDTABLE分别指代操作前后表的内容。AS后是给NEWROW,OLDROW,NEWTABLE,OLDTABLE起别名。
• [WHEN <触发条件>]<触发动作体>：只有当触发条件为真时才会执行触发动作体。

断言

通过声明断言(ASSERTION)来指定更具一般性的约束。比如定义断言a1为商品的库存因该维持在5-100之间，则无论是增加库存超过100还是出售商品后小于5，都会触发断言为假，从而拒绝操作。

所以相对于触发器，断言更具一般性，以断言条件(CHECK子句)，不以事件驱动。

-- 创建
CREATE ASSERTION <断言名> <CHECK 子句>;

-- 限制没门课程最多60名学生选修
CREATE ASSERTION ass_sc_cnum
CHECK (
  60 >= ALL(SELECT COUNT(*) FROM sc GROUP BY cno)
);

-- 删除
DROP ASSERTION <断言名>;