【数据库概论】第四章 SQL查询相关操作

4.1 数据查询

SQL提供SELECT语句用于查询，一般格式为：

根据WHERE子句条件表达式从FROM子句指定的基本表、视图中找出满足条件的元组。GROUP BY语句用于将结果按照 <列名1> 的值进行分组，该属性列值相等的元组为一个组；ORDER BY用作将结果表按照 <列名2> 的值升序或者降序排序输出。

4.4.1 单表查询

SELECT子句中的目标列表达式不仅可以是表中属性列，也可以是表达式，比如：

SELECT Sname, 2014 - Sage
FROM Student

可以使用DISTINCT消除取值重复的行，比如查询全体学生的年龄集合（不允许有重复值）：

SELECT DISTINCT Sage FROM Student

使用通配符 * 可以查询全体记录，例如查询全部学生的详细记录：

SELECT * FROM STUDENT

使用WHERE子句实现条件查找，常见的查询条件如表所示：

1. 确定范围

确定范围使用 BETWEEN ... AND ... 和 NOT BETWEEN ... AND ...，前者是范围下限，后者是范围上限。

2. 集合查找

提供集合，查询符合集合元素的项，比如:

SELECT Sname, Ssex
FROM Student
WHERE Sdept NOT IN ('CS', 'MA')

NOT IN：不在集合中；IN：在集合中。

3. 字符匹配（模糊查询）

使用 LIKE 可以用来实现模糊查询，语法如下：

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

其含义是查找指定属性列与 <匹配串> 匹配的元组。其中有两种通配符：

• % 代表任意长度的字符串，比如 a%b 表示以 'a' 开头、'b' 结尾的字符串，包括 'ab'、'asfqb' 等。
• _ 代表任意单个字符，比如 a_b 表示以 'a' 开头、'b' 结尾的三元素字符串。

另外，如果用户查询的字段本来就含有通配符，则需要使用 ESCAPE 字段对通配符进行转义，比如：

SELECT Cno FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\'

上述例子指的是，'' 是换码字符，在 \ 后的 _ 不算作通配符。

4. 多重条件查询

在 WHERE 后的判断语句，可以使用 AND 或者 OR 来连接多个查询条件。括号的优先级最高，其次是 AND，最后是 OR。

5. ORDER BY 子句

用户可以使用 ORDER BY 子句对查询结果按照一个或者多个属性升序或者降序排序。比如：

-- 查询选修了三号课程的学生学号及其成绩，分数降序排列
SELECT Sno, Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC

6. 聚集函数

为了进一步方便用户，增强检索功能，SQL提供了许多聚集函数，如下：

如果指定了 DISTINCT 短语，那么将会跳过重复值。当聚集函数遇到空值的时候，除了 COUNT(*) 之外，都跳过空值而非处理。

TIP: WHERE 子句中不能使用聚集函数，只能用于 SELECT 和 GROUP BY 中的 HAVING 子句中。使用例子如下：

1. 查询学生总人数

SELECT COUNT(*) FROM Student

2. 查询选修了课程的学生人数

SELECT COUNT(DISTINCT Sno) FROM SC

3. 计算选修一号课的学生成绩

SELECT AVG(Grade) FROM SC WHERE Cno='1'

7. GROUP BY 子句

GROUP BY 子句将查询结果按某一列或者多列的值分组，值相等的为一组。对于查询结果分组的目的是细化聚集函数的作用对象。如果没有对查询结果分组，则聚集函数会作用于整个查询结果，分组后聚集函数将会作用于每一个组。而 HAVING 语句则是用于对组进行筛选，比如：

1. 查询选修了三门课以上的学生学号

SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) > 3

SC表是学生-课程表，里面存储着学生的选课信息，由于一个学生可能会选择多门课，那么SC表中，同一个学生会有多个记录。上述的SQL语句中 GROUP BY 指明了组单元为学生学号 Sno，他会将SC表中同一学号的所有记录都是作为一个组。HAVING 语句指明了，只有在SC表中存在3条记录以上的学生才会在最终结果中输出。

我们再看多点例子，比如：

2. 求各个课程号以及相应的选课人数

SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno

4.4.2 连接查询

前

面的查询都是针对一个表进行的，如果一个查询同时涉及两个及以上的表，则称之为连接查询。

1. 等值与非等值连接

连接查询到 WHERE 子句中用来连接两个表的条件称之为连接条件或者连接谓词，一般格式是：

[<TABLE NAME1>.]<COL NAME1> <比较运算符> [<TABLE NAME2>.]<COL NAME>

也可以使用 BETWEEN...AND… 字段。其中当连接运算符为 = 的时候为等值连接，使用 > 或者 < 的时候则是非等值连接。

连接谓词中的列名称称为连接字段，连接条件中各个连接字段类型必须是可比的，但是名字不需要相同。

比如：

-- 查询每个学生以及其选修课程的状况
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno=SC.Sno

在上述例子中，关系型数据库管理系统执行连接操作会首先在表Student中找到第一个元组，然后从头扫描SC表，逐一比较两者的Sno，这其实和嵌套循环连接算法思想相似，而这样的效率是比较低下的。

如果建立索引，可以缩短连接查询的时间。还是上述的例子，如果在SC表Sno上建立了索引的话，就不用每次全表扫描SC了，而是根据Student表中元组的Sno值通过索引找到相应的SC元组。这是索引加快查找速度的实例。

在第二章中我们提到了等值连接和自然连接，在语句中将目标列中重复的属性列去掉则为自然连接。

2. 自身连接

连接操作不仅可以在两个表之间执行，也可以是一个表与自己进行连接，称为表的自身连接。它和单表查询的区别在于单表查询是单个表查询的，而自身连接是两个相同的表比较。示例如下：

3. 外连接

在一般的连接操作中，只有满足连接条件的数组才能输出，如果有的行没有数据的话，会不出现在连接结果中（被舍弃），这时就是悬浮数组。但是有时候我们会希望将悬浮数组保存在结果关系内，而在对应的空属性上填上 NULL。外连接示例如下：

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno)

左外连接列出左边关系中所有的元组，右外连接列出右边关系所有的元组。在上述例子中，Student中所有的行都会被查出，即使其在SC中是空值，也不会被置为悬浮数组，而是使用 NULL 进行填充。

4. 多表连接

连接操作支持多个表的连接，称为多表连接，示例如下：

SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno=SC.Sno AND SC.Sno=Course.Sno

4.4.3 嵌套查询

在SQL中，一个 SELECT-FROM-WHERE 语句称之为一个查询块，将一个查询块嵌套在另一个查询块的 WHERE 子句或者 HAVING 短语的条件中的查询称为嵌套查询，示例如下：

SELECT Sname FROM Student
WHERE Sno IN (
SELECT Sno FROM SC
WHERE Cno='2');

上例中，查询了选了课程号为2的学生的姓名。上层查询块称为外层查询或者父查询，下层查询块称之为内层查询或者子查询。在子查询中禁止使用 ORDER BY，排序只能对最终输出结果排序。

嵌套查询使得用户可以用多个简单查询构成复杂查询，从而增强SQL的查询能力。以层层嵌套的方式构造程序是SQL中结构化的含义所在。

1. 带有 IN 谓词的子查询

在这种情况中，子查询的结果往往是一个集合，因此谓词 IN 是嵌套查询中最常用的谓词。比如：

-- 查询和刘晨在同一个系学习的学生
SELECT * FROM Student
WHERE Sdept IN (
    SELECT Sdept
    FROM Student
    WHERE Sname='刘晨'
);

有一些嵌套查询可以用连接运算代替，有些是不可以代替的。如果子查询的查询条件不依赖父查询，这类子查询称为不相关子查询；如果子查询的查询条件依赖于父查询，则称为相关子查询。如本例中，可以改写为等值连接写法：

SELECT S1.Sno, S1.Sname, S1.Sdept
FROM Student S1, Student S2
WHERE S1.Sdept = S2.dept AND S2.Sname='刘晨';

因此是不相关子查询。

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

带有比较运算符是指父查询和子查询之间使用比较运算符进行连接，使用比较运算符意味着子查询返回值只可以是单个值，不可以是多个值的集合。比如上述的例子“查询和刘晨在同一个系学习的学生”可以改造成比较运算符子查询：

SELECT * FROM Student
WHERE Sdept = (
    SELECT Sdept
    FROM Student
    WHERE Sname="刘晨")

3. 带有ANY（SOME）或者ALL谓词的子查询
   子查询返回单值的时候可以使用比较运算符，但是返回多值的时候可以使用ANY/SOME或者ALL，其语义如下：
   

比如，查询非计算机系中比计算机系中任一学生年龄都小的学生

SELECT Sname, Sage FROM Student
WHERE Sage<ANY(
SELECT Sage FROM Student
WHERE Sdept='CS')

SELECT Sname, Sage FROM Student
WHERE Sage < ANY (
	SELECT Sage FROM Student
	WHERE Sdept='CS')

4.带有EXISTS谓词的子查询

EXISTS谓词代表∃，表示存在。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑值true或者false。例子如下：

SELECT Sname FROM Student
WHERE EXISTS (
	SELECT * FROM SC
	WHERE Sno = Student.Sno AND Cno = '1' )

使用了EXISTS后，如果内层查询为空，则外层的WHERE子句返回真，否则返回假

EXISTS的子查询目标列表达式一般为*，因为EXISTS只做存在性判断，选择哪个列进行查询没有意义。除了EXISTS外还有NOT EXISTS谓词，作用相反。

4.4.4 集合查询

SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可以进行集合操作。集合操作主要包括并操作UNION、交操作INTERSECT、差操作EXCEPT

-- 示例：UNION并操作查询计算机系统年龄小于19的学生
SELECT * 
FROM Student
WHERE Sdept='CS'
UNION
SELECT * 
FROM Student
WHERE Sage<=19;

4.4.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_sc.avg_sno AND SC.Grade >= Avg_sc.avg_grade;

其中的子句的子查询生成一个派生表Avg_sc。该表有两个属性，主查询将SC表和Avg_sc按照学号进行连接，选出课程成绩大于平均成绩的课程号

4.5 数据更新

1. 插入数据
   SQL的数据插入语句INSERT可以插入一个元组（单个插入）或者插入一个子查询（一次插入多个）

元组插入示例：

INSERT INTO Student(Sno, Sname, Ssex, Sdept, Sage)
VALUES ('114514', 'John', 'm', 'CS', 18);

此外，子查询可以嵌套在insert语句中以生成要插入的批量数据，其格式如下：

-- 将高于平均年龄的学生批量插入到Dept_age表中
INSERT INTO Dept_age(Sdept, Avg_age)
SELECT Sdept, AVG(Sage) 
FROM Student
GROUP BY Sdept;

2. 修改数据
   修改操作又称为更新操作，使用UPDATE关键字，示例：

UPDATE Student 
SET Sage = 22 
WHERE Sno = '114514';

3. 删除数据
   删除某一元组

DELETE FROM Student
WHERE Sno = '114514';

将某个表内容全部删除

DELETE FROM SC;

此时表会变为空表，但是表仍然存在

4.6 空值的处理

空值指的是“不知道”或“不存在”或“无意义”的值。一般使用NULL指定。空值的判断一般在WHERE中使用IS NULL和IS NOT NULL。同时也可以在定义列的时候指定是否可以取空值，在列约束条件处使用not null对列加以限制。空值和其他值的算数运算结果也为空，然后空值与其他值的比较运算结果为UNKNOWN。