3.1 SQL 概述
3.1.1 SQL 的特点
- 综合统一
将数据定义语言（DML）、数据查询语言（DQL）、数据操纵语言（DML）、数据控制语言（DCL）集合在一起，语言风格统一。
- 高度非过程化
菲关系数据模型的数据操纵语言是“面向过程”的语言，“过程化”语言完成某项请求必须指定存取路径。SQL 只需要用户指出做什么，无需指明怎么做，存取路径对用户透明。
- 面向集合的操作方式
更新操作（插入、删除、修改）的对象和查找操作的对象都可以是元组的集合，而不仅仅是一条记录。
3.1.2 SQL 的基本概念
SQL 的功能动词表
| SQL 功能 | 动词 |
|---|---|
| 数据查询 | SELECT |
| 数据定义 | CREATE，DROP，ALTER |
| 数据操纵 | INSERT，UPDATE，DELETE |
| 数据控制 | GRANT，REVOKE |
支持 SQL 的关系数据库支持三级模式结构。外模式包括视图和部分基本表，数据库模式包括若干基本表，内模式包括若干存储文件。
3.2 数据定义
SQL 的数据定义语句
| 操作对象 | 创建 | 删除 | 修改 |
|---|---|---|---|
| 模式 | CREATE SCHEMA | DROP SCHEMA | |
| 表 | CREATE TABLE | DROP TABLE | ALTER TABLE |
| 视图 | CREATE VIEW | DROP VIEW | |
| 索引 | CREATE INDEX | DROP INDEX | ALTER INDEX |
SQL 标准中不提供修改模式定义和视图定义的操作，只能先删除再重建。
SQL 标准没有索引相关语句，商用关系数据库通常提供。
关系数据库管理系统可以创建多个数据库，一个数据库可以有多个模式（多个命名空间），一个模式下通常包括多个基本表、视图和索引等数据库对象。
在 MySQL 中创建的模式（schema）与数据库（database）等价，也就是可以有多个数据库，每个对应一个模式。
3.2.1 模式的定义与删除
1 定义模式
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;
如果没有指定 <模式名>，那么 <模式名> 隐含为 <用户名>。
定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的对象，如基本表、视图、索引。
SQL 语句不区分大小写。
MySQL 中的不同点：
- 不支持授权用户语句 AUTHORIZATION <用户名>。
- 可以将 SCHEMA 替换为 DATABASE。
2 删除模式
DROP SCHEMA <模式名> <CASCADE|RESTRICT>;
CASCADE 和 RESTRICT 二者必选其一。前者为级联，表示删除模式包含的所有数据库对象；后者表示限制，当包含对象。
MySQL 中的不同点：
- 删除方式默认为级联，语句中不能使用 <CASCADE|RESTRICT>。
3 切换模式(MySQL)
在 MySQL 中，可以使用 USE 切换到其他模式SCHEMA（或数据库 DATABASE）：
USE others;
3.2.2 基本表的定义、删除与修改
1 定义基本表
CREATE TABLE <表名>(<列名> <数据类型> [列级完整性约束条件]
[,<列名> <数据类型> [列级完整性约束条件]]
...
[,表级完整性约束条件]);
完整性约束条件涉及多个属性列，必须定义在表级，否则表级和列级皆可。
可以通过查询结果创建新的表：
CREATE TABLE
AS
<子查询语句>;
2 修改基本表
ALTER TABLE <表名>
[ADD [COLUMN] <新列名> <数据类型> [列级完整性约束]]
[,ADD <表级完整性约束>]
[,DROP [COLUMN] <列名> [CASCADE|RESTRICT]] # 如果有其他对象引用该列，CASCADE 会删除引用他的对象，RESTRICT 会拒绝删除操作。
[,DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE]]
[,ALTER COLUMN <列名> <数据类型>];
同一类修改语句可以同时执行多次，用逗号隔开。
MySQL 的不同点：
- 不允许修改原有的列定义。
- 修改列信息改为：CHANGE <原列名> <新列名> <数据类型>。
3 删除基本表
DROP TABLE <表名> [CASCADE|RESTRICT]; # 如果该表中列被其他表的约束引用，或存在视图、触发器等，CASCADE 会删除相关对象，RESTRICT 会拒绝删除操作。
1.2.3 索引的建立、修改与删除
1 建立索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> # UNIQUE 代表每个索引对应唯一元组。
ON <表名>(<列名> [<次序>] [,<列名> [<次序>]]...);
2 修改索引
ALTER INDEX <旧索引名> RENAME TO <新索引名>;
MySQL 的不同点：
- 不支持修改索引。
3 删除索引
DROP INDEX <索引名> ON <表名>; # SQL 语言中不加表名，MySQL 必须加。
3.2.4 数据字典
数据字典是数据库管理系统内部的一组表，记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、操作权限、统计信息等。
3.3 数据查询
SELECT [ALL|DISTINCT] <目标列表表达式>[,<目标列表表达式>]...
FROM <表明或视图名> [别名][,<表明或视图名> [别名]...]|(<SELECT语句>) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[,<列名1>] [HAVING <条件表达式>]] # HAVING 子句必须与 GROUP BY 同时出现。
[ORDER BY <列名2> [ASC|DESC][,<列名2> [ASC|DESC]...]];
根据 WHERE 子句中的条件表达式从 FROM 子句指定的基本表、视图或派生表中找出满足条件的元组，再按 SELECT 子句中的目标列表达式选出元组中的属性值形成结果表。
如果没有 WHERE 子句，则选出关系中所有元组。
如果GROUP BY 子句，则按<列名 1>的值进行分组，该属性列相等的元组为一个组。通常会对每组作用聚集函数。如果还带有 HAVING 短语，则只有满足指定条件的组予以输出。
如果有 ORDER BY 子句，则结果表按<列名 2>的值的升序或降序排序。
3.3.1 单表查询
单表查询是指仅涉及一个表的查询。
1 选择表中的若干列
选择表中的全部或部分列即投影运算。
# 查询指定列，目标列的顺序可以不与表中的顺序一致。
SELECT Sno,Sname
FROM Student;
# 查询全部列，也可以将列名全部列出。
SELECT *
FROM Student;
# 查询经过计算的值，<目标列表达式>不仅可以是属性列，还可以是表达式。
SELECT Sname,2014-Sage
FROM Student;
# <目标列表达式>不仅可以是算数表达式，还可以是字符串常量、函数等。
SELECT 'Year of Birthday',LOWER(Sdept)
FROM Student;
# 可以通过指定别名改变查询的列标题
SELECT Sname NAME
FROM Student;
2 选择结果表中的若干元组
两个本来不完全相同的元组投影到指定的某些列上后，可能变成相同的行，可以通过 DISTICT 消除重复行。不指定 DISTINCT 关键词则默认为 ALL，保留重复行。
# 消除取值重复的行。
SELECT DISTINCT Sno
FROM SC;
# 以下两种等价。
SELECT Sno
FROM SC;
SELECT ALL Sno
FROM SC;
可以通过 WHERE 子句查找满足指定条件的元组。
常用的查询条件
| 查询条件 | 谓词 |
|---|---|
| 比较 | =，>，<，>=，<=，<>（!=） |
| 确定范围 | BETWEEN AND，NOT BETWEEN AND |
| 确定集合 | IN，NOT IN |
| 字符匹配 | LIKE，NOT LIKE |
| 空值 | IS NULL，IS NOT NULL |
| 多重条件（逻辑运算） | AND，OR，NOT（置于条件表达式前部，AND优先级大于OR，可以使用括号调整优先级） |
字符匹配的语法：
-
% 代表任意长度（长度可以为0）的字符串。
-
_ 代表任意单个字符。
-
如果要查询的字符串中本来就含有通配符，需要指定一个换码字符对通配符进行转义，使用 [ESCAPE'<换码字符>'] 短语。
3 ORDER BY 子句
可以用 ORDER BY 子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认为升序，优先按照前面的列排序。
4 聚集函数
| 聚集函数 | 功能 |
|---|---|
| COUNT(*) | 统计元组个数 |
| COUNT([DISDINCT|ALL] <列名>) | 统计一列中值的个数 |
| SUM([DISDINCT|ALL] <列名>) | 计算一列值的总和（刺裂必须为数值型） |
| AVG([DISDINCT|ALL] <列名>) | 计算一列值的平均值（此列必须为数值型） |
| MAX([DISDINCT|ALL] <列名>) | 求一列值中的最大值 |
| MIN([DISDINCT|ALL] <列名>) | 求一列值中的最小值 |
使用聚集函数的注意事项：
- 如果指定 DISTINCT 短语，计算时会取消指定列中的重复值。如果不指定 DISDINCT 短语或指定 ALL 短语（ALL为默认值），则不取消重复值。
- 聚集函数遇到空值时，都会跳过空值。而COUNT()或者COUNT(任意数值或字符串)都是统计元组个数，不会跳过空值。某列或部分列取空值*不会影响 COUNT 的统计结果。
- WHERE 子句中不能使用聚集函数作为条件表达式。聚集函数只能用于 SELECT 子句和 GROUP BY 中的 HAVING 子句。
5 GROUP BY 子句
GROUP BY 子句将查询结果按某列或多列（列名之间用逗号隔开）的值分组，值相等的为一组。
注意事项：
- HAVING 短语作用于分组之后。
- 聚集函数作用在每个分组上。
WHERE 子句与 HAVING 短语的区别在于作用对象不同。WHERE 子句作用于基本表或视图，从中选择满足条件的元组。HAVING 短语作用于组，从中选择满足条件的组。
3.3.2 连接查询
1 等值与非等值连接查询
连接查询的 WHERE 子句中用来连接两个表的条件称为连接条件或连接谓词，一般格式为：
[<表名1>.] <列名1> <比较运算符> [<表名2>.] <列名2>
其中比较运算符主要=、>、<、>=、<=、!=（或<>）等。
还可以使用：
[<表名1>.] <列名1> BETWEEN [<表名2>.] <列名2> AND [<表名2>.] <列名3>
连接运算符为 = 时，称为等值连接，其余为非等值连接。
连接谓词中的列名称为连接字段。各字段类型必须是可比的，但名字不必相同。
在等值连接时把目标列中重复的属性列去掉，变为自然连接。
SELECT Student.Sno,Sname,Ssex,Sdept,Cno,Grade # 属性列名称唯一的可以省略表名前缀，在两个表中均出现时，必须加上表名前缀。
FROM Student,SC
WHERE Student.Sno=SC.Sno;
2 自身连接
连接操作不仅可以在两个表之间进行，也可以是一个表与自己进行连接。
# 查询每一门课的间接先修课
SELECT FIRST.Cno,SECOND.Cpro
FROM SC FIRST,SC SECOND
WHERE FIRST.Cpro=SECOND.Cno;
3 外连接
可以使用 <表名1> LEFT OUTER JOIN <表名2> ON (<表名1>.<列名>=<表名2>.<列名>) 实现左外连接。
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
可以使用 USING 去掉重复值，这种情况下可以省略表名前缀。
SELECT Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC USING(Sno);
同时可以使用 INNER JOIN 实现内连接。
SELECT Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student INNER JOIN SC USING(Sno);
4 多表连接
除了两表连接和自身连接，还可以通过逻辑运算符实现多表连接。
3.3.3 嵌套查询
在 SQL 语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块。将一个查询块嵌套在另一个查询块的 WHERE 或 HAVING 的条件中的查询称为嵌套查询。
SELECT Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno'2');
上层的查询块称为外层查询或父查询，下层的查询块称为内层查询或子查询。
注意事项：
- 允许子查询嵌套子查询，实现多层嵌套查询。
- 子查询的 SELECT 语句中不能使用 ORDER BY 子句，ORDER BY 子句只能对最终查询结果排序。
1 带 IN 谓词的子查询
子查询的结果通常是一个集合，可以使用 IN 谓词。
SELECT Sno,Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno IN
(SELECT Cno
FROM Course
WHERE Cname='信息系统')
);
这种嵌套查询可以使用连接运算代替。
SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND
SC.Cno=Course.Cno AND
Course.Cname='信息系统';
能够使用连接运算符表达的查询尽可能采用连接运算。
上例中子查询的查询条件不依赖父查询，称为不相关子查询。
2 带有比较运算符的子查询
带有比较运算符的子查询是指父查询与子查询之间用比较运算符连接。当确定子查询返回单个值时，可以使用比较运算符。
# 找出每个学生超过他自己选修课程平均分的课程号。
SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);
这种子查询的查询条件与父查询相关的，称为相关子查询。
3 带有 ANY（SOME）或 ALL 谓词的子查询
当子查询返回多值时，同时使用比较运算符和 ANY 或 ALL。
其语义为与返回值的任一（ANY）或所有（ALL）比较都满足则为真，否则为假。
# 查询非CS系比CS系的任意一个学生年龄小的学生姓名和年龄
SELECT Snam,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
FROM Student
WHERE Sdept='CS');
# 其语义相当于比结果中最大的小即可。
SELECT Snam,Sage
FROM Student
WHERE Sage<(SELECT MAX(Sage)
FROM Student
WHERE Sdept='CS');
用聚集函数实现子查询通常比 ANY 或 ALL 查询效率高。
4 带有 EXIST 谓词的子查询
EXIST 代表存在谓词。带有 EXIST 谓词的子查询不返回数据，只产生逻辑真 “true” 或逻辑假 “false” 。
# 查询所有选修了1号课程的学生姓名。
SELECT Sname
FROM Student
WHERE EXIST(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1');
由于 EXIST 谓词只返回真假值，列名无意义，所以通常使用 * 。
SQL 中没有全称量词，但可以使用存在谓词嵌套达到同样的效果。
# 查询选修全部课程的学生名称，相当于没有一门课没有选择。
SELECT Sname
FROM Student
WHERE NOT EXIST(SELECT *
FROM Course
WHERE NOT EXIST(SELECT *
FROM SC
WHERE Student.Sno=Sno AND Course.Cno=Cno)
);
3.3.4 集合查询
SELECT 查询的结果是元组的集合，可以对多个 SELECT 语句的结果进行集合操作。集合操作主要包括并 UNION、交 INTERSECT、差 EXCEPT。
进行集合操作的各查询结果列数和对应项的数据类型必须相同。
# 查询选修了1号或者2号课程的学生。
SELECT Sno
FROM SC
WHERE Cno='1' # 注意这里没有分号
UNION
SELECT Sno
FROM SC
WHERE Cno='2';
3.3.5 基于派生表的查询
子查询不仅可以出现在 WHERE 子句中，还可以出现在 FROM 子句中，这时子查询会生成临时派生表作为子查询的对象。
# 查询课程成绩比选课平均成绩高的学生学号和课程。
SELECT Sno,Cno
FROM SC,(SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno) AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno=avg_sno AND SC.grade>=Avg_sc.avg_grade;
3.4 数据更新
3.4.1 插入数据
插入语句 INSERT 有两种形式，一种插入元组，一种插入子查询结果，后者一次可以插入多个元组。
1 插入元组
INSERT INTO <表名> [(<属性列1>[,<属性列2>]...)]
VALUES(<常量1>[,<常量2>]...)
注意事项：
- INTO 子句中没有出现的属性列取自动取空值。
- INTO 子句中没有指明任何属性列名，则插入的新元组必须在每个属性列上均有值，且顺序与表中一致。
- 属性列名可以和表中的顺序不一致，但需要和 VALUES 中的对应，即数据类型必须满足属性列名的要求。
2 插入子查询结果
INSERT INTO <表名> [(<属性列1>[,<属性列2>]...)]
子查询;
3.4.2 修改数据
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>...]
[WHERE <条件>];
注意事项：
- 修改满足 WHERE 子句条件的元组值，未指定条件则修改所有元组值。
- WHERE 子句中同样可以嵌套子查询。
3.4.3 删除数据
DELETE
FROM <表名>
[WHERE <条件>];
注意事项：
- 如果未指定 WHERE 子句中的条件，会删除所有元组，但表的定义仍在数据字典中。
3.5 视图
视图是从一个或几个基本表（或视图）导出的表。数据库中只存放视图的定义，不存放视图的数据。
3.5.1 视图的建立与删除
要想修改视图，只能通过删除重建。
1 建立视图
CREATE VIEW <视图名> [(<列名>[,<列名>...])]
AS <子查询>
[WITH CHECK OPTION];
注意事项：
- 子查询可以为任意的 SELECT 子句。
- WHERE CHECK OPTION 表示对视图进行 UPDATE、INSERT 和 DELETE 操作时要保证被修改的行满足视图定义中的谓词条件。
- 组成视图的属性列名必须全部指定或全部省略（用 SELECT 子句中的字段名组成），如下情况必须指定列名：
- 某个目标列是聚集函数或列表达式；
- 多表连接时选出同名列作为视图字段；
- 需启用更合适的名字。
# 建立信息系学生的视图，并且修改时需保证该视图只有信息系学生。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
当视图从单个基本表导出，并且去掉了某些行和列，但保留了主码，则成为行列子集视图。
视图可以建立在一个或多个表。
# 建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）。
CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno AND Sdept='IS' AND Cno='1';
视图可以建立在一个或多个定义好的视图。
# 建立信息系选修了一号课程且成绩在90分以上的学生的视图。
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;
定义视图时需要根据应用需求设置一些派生属性列，由于这些属性列不实际存在，称为虚拟列，也称为带表达式的视图。
# 定义一个反映学生出生年份的视图。
CREATE VIEW BT_S(Sno,Sname,Sbirth) # 由于使用
AS
SELECT Sno,Sname,2020-Sage
FROM Student;
还可以用带聚集函数和 GROUP BY 子句的查询定义视图，称为分组视图。
# 按学生的学号及平时成绩定义为一个视图。
CREATE VIEW S_G(Sno,Gavg) # 由于使用聚集函数，属性列名必须明确定义。
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
2 删除视图
DROP VIEW <视图名> [CASCADE];
将视图定义从数据字典中删除。使用 CASCADE 短语可以级联删除在该视图上导出的视图。
3.5.2 查询视图
对视图查询的步骤：
- 首先进行有效性检查，检查查询中涉及的表、视图是否存在；
- 如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户查询结合起来，转换成等价的对基本表的查询，再执行修正后的查询。这一过程称为视图消解。
有些视图查询在某些关系数据库系统中是不一定能进行转换的，对于这种视图查询通常直接在基本表上进行。
# 查询平均分视图上对平均成绩在90分以上的学生学号和平均成绩。
SELECT *
FROM S_G
WHERE Gavg>=90;
# 转换后。
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;
# 由于 WHERE 子句中不能使用聚集函数，所以转换错误，正确转换为。
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
3.5.3 更新视图
更新视图是通过视图来增删改数据。
基本操作与基本表上类似。但需要注意的是，如果在定义视图时，没有添加 WITH CHECK OPTION 子句，则有可能影响到视图范围外的数据。
下面创建四种视图
# 对于包含专业项的视图，在WHERE条件表达式中可以判断专业信息为其他专业，如：WHERE Sdept='MA'
# 视图中包含专业项，但没有 WITH CHECK OPTION 子句。
# UPDATE 时可以将专业数据改成其他专业。
# INSERT 时可以插入其他专业学生的信息。
# DELETE 不受影响。
CREATE VIEW IS_CS
AS
SELECT Sname,Sdept
FROM Student
WHERE Sdept='CS';
# 视图中不包含专业项，但没有 WITH CHECK OPTION 子句。
# UPDATE、INSERT、DELETE 均不受影响。
CREATE VIEW IS_CS
AS
SELECT Sname
FROM Student
WHERE Sdept='CS';
# 视图中包含专业项，但有 WITH CHECK OPTION 子句。
# UPDATE 时拒绝将专业数据改成其他专业。
# INSERT 时只能插入该该专业的学生信息，插入其他专业时拒绝。
# DELETE 不受影响。
CREATE VIEW IS_CS
AS
SELECT Sname,Sdept
FROM Student
WHERE Sdept='CS'
WITH CHECK OPTION;
# 视图中不包含专业项，但有 WITH CHECK OPTION 子句。
# INSERT 时无法插入任何学生信息，因为Sdept缺省，自动设置为NULL。
# UPDATE 和 DELETE 不受影响。
CREATE VIEW IS_CS
AS
SELECT Sname
FROM Student
WHERE Sdept='CS'
WITH CHECK OPTION;
另外，不是所有的视图都可以更新，有些视图的更新不能唯一有意义地转化成相应基本表的更新。
# 对这种视图中的Gavg属性值进行修改时是无效的。
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
3.5.4 视图的作用
- 视图能够简化用户的操作
通过定义视图能够使数据库看起来结构简单、清晰。使用户只需要关注需要关注的数据。
- 视图使用户能以多种角度看待同一数据
如年龄信息，有些用户看到的是年龄，有些则可以看到出生年份。
- 为重构数据库提供一定的逻辑独立性
定义视图可以在基本表增加新的字段或结构变化时，不会改变视图看到的内容，从而不必更改应用程序，提高了逻辑独立性。
- 对机密数据提供安全保护
- 更简洁地表达查询
网友评论