爆肝！一看就懂的《SQL 语言知识体系》【建议收藏】（中）

爆肝！一看就懂的《SQL 语言知识体系》【建议收藏】（上）：https://developer.aliyun.com/article/1529601

5. 数据操作

SQL 的数据操作包括 SELECT(查询)、INSERT(插入)、DELETE(删除)和 UPDATE(修改)四条语句。

5.1 SELECT 基本结构

数据库查询是数据库的核心操作，语法格式如下：

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

SQL 查询中的子句顺序为SELECT、FROM、WHERE、GROUP BY、HAVING 和 ORDER BY。其中，SELECT、FROM 是必须的，HAVING 条件子句只能与 GROUP BY 搭配起来使用。

（1）SELECT 子句对应的是关系代数中的投影运算，用来列出查询结果中的属性。其输出可以是列名、表达式、函数集（AVG、COUNT、MAX、MIN、SUM），DISTINCT 选项可以保证查询的结果集不存在重复元组。

（2）FROM 子句对应的是关系代数中的笛卡尔积，它列出的表达式求值过程中需扫描的关系，即在 FROM 子句中出现多个基本表或视图时，系统首先执行笛卡尔积操作。

（3）WHERE 子句对应的是关系代数中的选择谓词。WHERE 子句的条件表达式可以使用的运算符如下：

5.2 简单查询

SQL 最简单的查询是找出关系中满足特定条件的元组，这些查询与关系代数中的选择操作类似，通常只需要使用三个关键字 SELECT、FROM 和 WHERE。

示例10. 查询所有员工信息；查询员工号为1088的员工的姓名和参加工作时间。

SELECT *
FROM 员工;

SELECT 姓名,参加工作时间
FROM 员工
WHERE 员工号 = 1088;

5.3 连接查询

如果查询涉及两个以上的表，则称为连接查询。

示例11. 关系模式：员工（员工号，姓名，性别，参加工作时间，部门号）；部门（部门号，名称，电话，负责人）；

# 查询部门名为“企业信息部”，且在2010年1月1日后入职的员工工号、姓名和性别
SELECT 员工号,姓名,性别
FROM 员工,部门
WHERE 员工.部门号 = 部门.部门号
AND 部门.名称 = '企业信息部'
AND 参加工作时间 > '2010-01-01';

连接查询相同字段需要带上表名点前缀，字符类型，日期类型的数据需要使用单引号。

5.4 子查询

子查询也称嵌套查询。嵌套查询是指一个 SELECT-FROM-WHERE 查询块可以嵌入另一个查询块之中。在 SQL 中允许多重嵌套。

示例12. 查询部门名为“企业信息部和行政部”，所有员工的工号、姓名和性别。

SELECT 员工号,姓名,性别
FROM 员工
WHERE 部门号 IN (SELECT 部门号
                FROM 部门
                WHERE 名称 IN ('企业信息部','行政部'));

5.5 聚集函数和更名操作

聚集函数是一个值的集合输入，返回单个值的函数。

SQL提供了5个预定义集函数:平均值AVG、最小值MIN、最大值MAX、求和SUM以及计数COUNT；

使用ANY和ALL谓词必须同时使用比较运算符，其含义及等价的转换关系如下：

使用聚合函数实现子查询比直接用 ALL 或 ANY 查询效率要高。

更名操作：SQL提供可为关系和属性重新命名的机制，可以使用 AS 来操作。as 子句可以出现在SELECT 子句中，也可出现在FROM子句中

示例13. 查询课程C1的最高分和最低分以及高低分之间的差距。

SELECT MAX(Grade) AS MAX_Grade
      ,MIN(Grade) AS MIN_Grade
      ，MAX(Grade)-MIN(Grade) AS GAP
FROM SC AS X
WHERE Cno='C1';

示例14. 查询其他系比计算机系CS所有学生年龄都要小的学生姓名及年龄。

## 方法一
SELECT Sname,Sage
FROM S 
WHERE Sage < ALL(SELECT Sage
                 FROM S
                 WHERE SD='CS')
      AND SD <> 'CS';
## 方法二    
SELECT Sname,Sage
FROM S
WHERE Sage < (SELECT MIN(Sage)
              FROM S 
              WHERE SD = 'CS')
      AND SD <> 'CS';

5.6 分组查询

在 WHERE 子句后面加上 GROUP BY 子句可以对元组进行分组，关键字 GROUP BY 后面跟着一个分组属性列表。如元组在分组后需要过滤，可以在后面加 HAVING 子句即可。

当元组含有空值时，应该记住以下两点：

（1）空值在任何聚集操作中都会被忽视。它对求和、求平均值和计数都没有影响。它也不能是某列的最大值或最小值。例如，COUNT(*)是某个关系中所有元组数目之和，但 COUNT(A) 却是A属性非空的元组个数之和。

（2）NULL 值又可以在分组属性中看作是一个一般的值。例如，SELECT A,AVG(B) FORM R 中，当A的属性值为空时，就会统计A=NULL的所有元组中B的均值。

示例15. 供应商数据库中的S、P、J、SPJ关系，查询某工程至少用了三家供应商(包含三家)供应的零件的平均数量，并按工程号的降序排列

SELECT Jno,AVG(QTY)
FROM SPJ 
GROUP BY Jno
HAVING COUNT(DISTINCT(Sno)) > 2
ORDER BY Jno DESC

5.7 字符串操作

谓词 LIKE 可以用来进行字符串匹配，通常也称模糊查询，语法格式如下：

[NOT] LIKE '<匹配串>'[ESCAPE'<换码字符>']

可以使用通配符%和_，其中：%匹配任意字符串；_匹配任意一个字符。例如，_ _匹配只含两个字符的字符串；_ _ %匹配至少包含两个字符的字符串。

NULL 操作，通常使用 IS NULL、IS NOT NULL。

示例16. 学生关系模式为(Sno,Sname,Sex,SD,Sage,Addr)，其中，Sno为学号，Same为姓名，Sex为性别，SD为所在系，Sage为年龄，Addr为家庭地址。请查询:

(1) 家庭地址包含“科技路”的学生姓名。

(2)名字为“晓军”的学生姓名、年龄和所在系。

# (1)
SELECT Sname FROM S WHERE Addr LIKE '%科技路%';
# (2)
SELECT Sname,Sage,SD FROM S WHERE Sname LIKE '_ _晓军';

为了使模式中包含特殊模式字符(即%和_)，在SQL中允许使用 ESCAPE 关键词来定义转义符。转义字符紧靠着特殊字符，并放在它的前面，表示该特殊字符被当成普通字符。例如，在LIKE比较中使用ESCAPE关键词来定义转义符，例如使用反斜杠\作转义符。

LIKE 'ab\%cd%'ESCAPE '\',匹配所有以阿宝%cd开头的字符串。
LIKE 'ab\\cd%'ESCAPE '\',匹配所有以ab\cd开头的字符串。

5.8 集合操作

在关系代数中可以用集合的并、交和差来组合关系。SQL也提供了对应的操作，但是查询的结果必须具有相同的属性和类型列表。保留字UNION、INTERSECT和EXCEPT分别对应 ∪、∩和—。保留字用于两个查询时，应该将每个查询分别用括号括起来。

UNION 运算

示例17. 假设查询所有客户的集合的语句1为:SELECT Customer-no FROM depositor;查询有贷款客户的集合的语句2为:SELECT Customer-no FROM borrower。查询在银行有账户、有贷款或两者都有的所有客户身份证号。

# 1和2 取并集,使用 UNION 运算会自动去除重复
SELECT Customer-no FROM depositor
UNION 
SELECT Customer-no FROM borrower;
# 1和2 取并集,使用 UNION ALL 运算会保留重复
SELECT Customer-no FROM depositor
UNION ALL
SELECT Customer-no FROM borrower;

INTERSECT 运算

示例18. 学生关系模式为Students (Name, Sno, SEX, SD, Type, Address)，教师关系模式为Teachers (Name, Eno, SEX, Salary, Address)，查询是女研究生，又是教师且工资大于等于2600元的名字和地址。

(SELECT Name, Address 
 FROM Students
 WHERE SEX='女' 
       AND rype='研究生') 
INTERSECT
(SELECT Name, Address 
 FROM Teachers
 WHERE Salary>=2600);

EXCEPT 运算

示例19.查询不是教师的学生姓名。

(SELECT Name, Address FROM Students) 
 ЕХСЕРТ
(SELECT Name, Address FROM Teachers);

5.9 视图查询和更新

查询视图表时，系统先从数据字典中取出该视图的定义，然后将定义中的查询语句和对该视图的查询语句结合起来，形成一个修正的查询语句。

# 创建视图
CREATE VIEW V_ITEmp
AS SELECT 员工号,姓名,性别
   FROM 员工,部门
   WHERE 员工.部门号 = 部门.部门号
   AND 部门.名称 = 'IT部';
# 查询IT部性别为‘女’员工的姓名工号
SELECT 员工号,姓名
FROm V_ITEmp
WHERE 性别 = '女';

视图更新

SQL 对视图更新必须遵循以下规则:

(1) 从多个基本表通过连结操作导出的视图不允许更新。

(2) 对使用了分组、聚集函数操作的视图则不允许进行更新操作。

(3) 如果视图是从单个基本表通过投影、选取操作导出的则允许进行更新操作，且语法同基本表。

5.10 WITH 子句

With 子句是在SQL99中引入的，目前只有部分数据库支持这一子句。如果我们将一个复杂查询分解成一些小视图，然后将它们组合起来，就像将一个程序按其任务分解成一些过程一样，使得复杂查询的编写和理解都会简单得多。

示例20. 假定教师关系模式为Teachers(TName,Eno,Tdept,SEX,Salary,Address)，利用With子句查询工资最高的教师姓名。此时，如果具有同样工资最高的教师有多个，他们都会被选择。

with max-Salary (value) AS (SELECT max (Salary)
FROM Teachers)
SELECT Tname
FROM Teachers, max-Salary
WHERE Teachers.Salary = max-Salary.value;

5.11 其他语句

插入语句语法如下：

INSERT INTO 表名(列名[,...n])
VALUES (常量[,...n]);

INSERT INTO 表名(列名[,...n])
SELECT 查询语句;

删除语句语法如下：

DELETE 
FROM 表名
WHERE 条件表达式;

更新语句语法如下：

UPDATE 表名
SET 列名=值表达式(,列名=值表达式)
[WHERE 条件表达式];

6. 数据控制

数据控制是控制用户对数据的操作和存储权力，是由 DBA 来决定的。DBMS 数据控制应具有如下功能：

（1）通过 GRANT 和 REVOKE 将授权通知系统，并存入数据字典。

（1）通过 GRANT 和 REVOKE 将授权通知系统，并存入数据字典。

（2）当用户提出请求时，根据授权情况检查是否执行操作请求。

SQL标准包括 DELETE、INSERT、SELECT 和 UPDATE 权限。SELECT 权限对应于 READ 权限，SQL 还包括了 REFERENCES 权限，用来限制用户在创建关系时定义外码的能力。

6.1 授权（GRANT)

授权语法格式：

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

注意：若指定了 WITH GRANT OPTION 子句，那么获得了权限的用户还可以将权限赋给其他用户；接受权限的用户可以是单个或多个具体的用户，PUBLIC 参数可将权限赋给全体用户。不同类型的操作对象有不同的操作权限，常见的操作权限如下所示：

示例21. 如果用户要求把数据库SPJ中供应商S、零件P、项目J表赋予各种权限。各种授权要求如下:

(1) 将对供应商S、零件P、项目J的所有操作权限赋给用户 USER1 及 USER2。

(2) 将对供应商S的插入权限赋给用户 USER1，并允许将此权限赋给其他用户。

(3) DBA 把数据库 SPJ 中建立表的权限赋给用户 USER1。

参考答案:

(1)GRANT ALL PRIVILEGES ON TABLE S,P,J TO USER1,USER2;

(2)GRANT INSERT ON TABLE S TO USER1 WITH GRANT OPTION;

(3)GRANT CREATE TABLE ON DATABASE SPJ TO USER1;

6.2 销权（REVOKE）

销权语法格式：

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

其中：RESTRICT 表示只收回指定用户的权限，默认；CASCADE 表示收回指定用户及其授予的其他用户的该权限。

示例22. 要求回收用户对数据库SPJ中供应商S、零件P、 项目J表的操作权限。各种收回权限的要求如下:

(1) 将用户USER1 及USER2对供应商S、零件P、项目J的所有操作权限收回。

(2) 将所有用户对供应商S的所有查询权限收回 。

(3)将USER1 用户对供应商S的供应商编号Sno的修改权限收回。

参考答案:

(1) REVOKE AL PRIVILEGES ON TABLE S,P,J FROM USER1, USER2;

(2) REVOKE SELECT ON TABLE S FROM PUBLIC;

(3) REVOKE UPDATE(Sno) ON TABLE S FROM USER1;

示例23. 收回用户LI对表EMPLOYEE的查询权限，同时级联收回LI授予其他用户的该权限，SQL语句为: (1) SELECT ON TABLE EMPLOYEE FROM LI (2) ;

(1) A.GRANT    B.GIVE    C.CALL BACK    D. REVOKE

(2) A.RESTRICT    B.CASCADE    C.WITH GRANT OPTION    D. WITH CHECK OPTION

答案：(1)的正确选项为D，(2)的正确选项为B。

7. 存储过程和触发器

存储过程，类似于高阶语言的函数或者方法，包含SQL语句序列，是可复用的语句，保存在数据库中，在服务器中执行。

特点：复用，提高了效率，安全性。

触发器是一种特殊类型的存储过程，是通过事件触发而执行的，而存储过程可以通过存储过程名称而被直接调用。

触发器主要有如下特点：

(1) 当数据库程序员声明的事件发生时，触发器被激活。事件可以是对某个特定关系的插入(INSERT)、删除(DELETE)或更新(UPDATE)。

(2)当触发器被事件激活时，不是立即执行，而是首先由触发器测试触发条件，若条件不成立，响应该事件的触发器什么事情都不做。

(3)如果触发器声明的条件满足，则与该触发器相连的动作由DBMS执行。动作可以阻止事件发生，可以撤销事件。

触发器数据库对象，当创建一个触发器时必须指定：1> 名称；2> 在其上定义触发器的表；3> 触发器将何时激发；4> 指明触发器执行时应做的动作。其名称必须遵循标识符的命名规则，数据库像存储普通数据那样存储触发器。触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。尽管不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。

触发动作实际上是一系列SQL语句，可以有两种方式:

(1) 对被事件影响的每一行(FOR EACH ROW)，每一元组执行触发过程，称为行级触发器。

(2) 对整个事件只执行一次触发过程(FOR EACH STATEMENT)，称为语句级触发器。该方式是触发器的默认方式。

7.1 创建触发器

触发器主要包括两个方面：指明触发器的触发事件，指明触发器执行的动作。

触发事件包括表中行的插入、删除和修改，即执行 INSERT、DELETE、UPDATE 语句。 在修改操作 (UPDATE)中，还可以指定特定的属性或属性组的修改为触发条件。事件的触发还有两个相关的时间：BEFORE 和 AFTER。BEFORE 触发器是在事件发生之前触发，AFTER 触发器是在事件发生之后触发。创建触发器语句格式如下:

CREATE TRIGGER <触发器名> [{BEFORE|AFTER}]
{[DELETE|INSERT|UPDATE OF[列名清单]]}
ON 
[REFERENCING <临时视图名>]
[FOR EACH ROW|FOR EACH STATEMENT]
[WHEN <触发条件>]
BEGIN
<触发动作>
END [触发器名];

参数说明：

BEFORE：指示 DBMS 在执行触发语句之前激发触发器。

AFTER：指示 DBMS在执行触发语句之后激发触发器。

DELETE：指明是 DELETE 触发器，每当一个DELETE 语句从表中删除一行时激发触发器。

INSERT：指明是 INSERT 触发器，每当一个INSERT语句向表中插入一行时激发触发器

UPDATE：指明是 UPDATE 触发器，每当 UPDATE 语句修改由 OF 子句指定的列值时，激发触发器。如果忽略 OF 子句，每当 UDPATE 语句修改表的任何列值时，DBMS 都将激发触发器。

REFERENCING <临时视图名>：指定临时视图的别名 。在触发器运行过程中，系统会生成两个临时视图，分别存放被更新值(旧值)和更新后的值(新值)。对于行级触发器， 默认临时视图名分别是 OLD 和 NEW；对于语句级触发器，默认临时视图名分别是 OLD-TABLE 和 NEW-TABLE。一旦触发器运行结束，临时视图就不在。

WHEN <触发条件>：指定触发器的触发条件。当满足触发条件时，DBMS 才激发触发器。触发条件中必须包含临时视图名，不包含查询。

示例24. 银行数据库关系模式如下：

            Account (Account-no, branch-name, balance)

            Loan (Loan-no, branch-name, amount)

            Depositor (customer-name, Account-no)

账户关系模式 Account 中的属性 Account-no 表示账号 ，branch-name 表示支行名称，balance 表示余额。贷款关系模式 Loan 中的属性 Loan-no 表示贷款号，branch-name 表示支行名称，amount 表示金额。存款关系模式 Depositor 中的属性 customer-name 表示存款人姓名。SQL-99 创建触发器如下所示:

CREATE TRIGGER overdraft_trigger AFTER UPDATE ON Account
REFERENCING NEW ROW AS nrow
FOR EACH ROW 
WHEN nrow.balance < 0 
BEGIN ATOMIC
INSERT INTO borrower
(SELECT customer-name,Account-no
FROM Depositor
WHERE nrow.account-no = Depositor.account-no);
INSERT INTO loan VALUES (nrow.account-no,branch-name,-nrow.balance);
UPDATE account SET balance = 0
WHERE account.account-no = nrow.account-no;
END;

CREATE TRIGGER reorder_trigger AFTER UPDATE OF amount on inventory
REFERENCING OLD ROW AS orow,NEW ROW AS nrow
FOR EACH ROW 
WHEN nrow.level <= (SELECT level
                    FROM minlevel
                    WHERE minlevel.item = orow.item)
AND orow.level > (SELECT level
                  FROM minlevel
                  WHERE minlevel.item = orow.item)
BEGIN 
INSERT INTO orders
(SELECT item,amount
FROM reorder
WHERE reorder.item = orow.item)
END;

示例26. 若修改某商品的库存时，使得库存值小于或等于其最小库存值，则向采购表插入一条记录，要求采购的数量是该商品最小库存值的两倍再加上10。

CREATE TRIGGER 采购-trigger AFTER UPDATE ON 商品 [AFTER UPDATE OF 库存 ON 商品]
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN nrow.库存<=nrow.最小库存
BEGIN
INSERT INTO 采购(商品号,采购数量)
VALUES（nrow.商品号,nrow.最小库存*2+10)
END;

爆肝！一看就懂的《SQL 语言知识体系》【建议收藏】（下）：https://developer.aliyun.com/article/1529607