爆肝！一看就懂的《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
