【数据库原理】（11）SQL数据查询功能

基本格式

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

• SELECT: 指定要从表中检索的列。
• ALL | DISTINCT: ALL 显示所有行，DISTINCT 去除重复行。
• FROM: 指定要从中检索数据的表或视图。
• WHERE: 指定行的过滤条件。
• GROUP BY: 将结果集分组。
• HAVING: 对 GROUP BY 生成的组应用过滤条件。
• ORDER BY: 按一个或多个列对结果集进行排序。

一.单表查询

1.选择表中的若干列

(1) 查询指定列

• 目的: 从表中选择特定的列。
• 示例: 查询所有课程的编号和名称。

SELECT Cno,Cname FROM C;

• 列显示顺序: 可以根据需要更改查询结果中列的显示顺序。

(2)查询全部列

• 目的: 选择表中的所有列。
• 示例: 查询所有课程的详细信息。

SELECT * FROM C;

注意: 使用 * 符号可以选择表中的所有列，但在有大量列的大型表中，这可能会影响性能。

(3)查询经过计算的值

• 目的: 查询包含计算结果的列。
• 示例: 查全体学生的姓名及其出生年份。

SELECT Sname,2011-Sage FROM S;

查询结果为:

<目标列表达式>还可以是字符串常量、函数等,而且可以通过指定别名来改变查询结果的列标题。

• 别名: 可以为列指定别名，以在结果集中更清晰地展示。
• 函数使用: 在 SELECT 语句中可以使用各种函数，如 LOWER() 将文本转换为小写。
• 示例: 查询全体学生的姓名、出生年份和所在系，用小写字母表示系名。

SELECT Sname NAME,2011-Sage BIRTHDATE,LOWER(Sclass) CLASS FROM S;

查询结果为:

2.选择表中的若干元组

(1) 消除取值重复的行

• 使用 DISTINCT: 去除查询结果中的重复行。
• 示例: 查询选修了课程的所有不重复的学生学号。

SELECT DISTINCT Sno
FROM SC;

如果未指定 DISTINCT 短语,则缺省为 ALL,即保留结果表中取值重复的行。

(2)查询满足条件的元组

查询满足指定条件的元组可以通过 WHERE 子句来实现。WHERE 子句常用的查询条件,如表所示。

比较大小: 如 =、<、>。

• 示例: 查询全体男生的名单。

SELECT Sname FROM S WHERE Ssex ='M';

确定范围: 使用 BETWEEN。

• 示例: 查询年龄在 19 到 22 岁之间的学生。

SELECT Sname,Sage FROM S WHERE Sage BETWEEN 19 AND 22;

确定集合: 使用 IN 或 NOT IN。

• 示例: 查询特定课程之外的课程编号和名称。

SELECT Cno, Cname FROM C WHERE Cname NOT IN ('数据结构', '计算机原理', '数据库原理');

字符匹配:使用 LIKE。

谓词 LIKE 可以用来进行字符串的匹配，其格式为 :

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

其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以为完整的字符串,也可以含有通配符%和_。

其中:%(百分号)代表任意长度(长度可以为 0)的字符串;代表任意单个字符。例如 a%b 表示以 a 开头,以 b 结尾的任意长度的字符串,asb、asdb、aefegb 均满足该匹配串; a_b 表示以 a 开头,以 b 结尾的长度为 3 的任意字符串,acb、asb、azb 满足该匹配串，而 asdb、aefegb 不满足该匹配串。

• 示例：查询所有姓张的学生的姓名、学号和性别。

SELECT Sname, Sno,Ssex
FROM S
WHERE Sname LIKE '张%':

• 示例：查询姓名中第二个字为“娜”的学生的姓名和学号：

SELECT Sname,Sno
FROM S
WHERE Sname LIKE '_娜%';

涉及空值的查询：使用 IS NULL 或 IS NOT NULL。

• 示例: 查询有成绩的学生的学号和相应的课程号、成绩。

SELECT *
FROM SC
WHERE GRADE IS NOT NULL;

多重条件：使用 AND、OR 组合多个条件。

• 示例: 查询所有年龄在 20 岁以上(包括 20 岁)的男学生姓名及其年龄。

SELECT Sname，Sage FROM S WHERE Sage < = 20 AND Ssex = 'M';

3.对查询结果排序

使用 ORDER BY 子句可以根据一个或多个列对 SQL 查询结果进行排序。可以指定升序（ASC）或降序（DESC）排列。如果未明确指定，排序默认为升序。

注意事项:

• 当使用 ORDER BY 对多个列进行排序时，排序的优先级按列在 ORDER BY 子句中的出现顺序决定。
• 在一些复杂的查询中，ORDER BY 可以结合聚合函数和分组（GROUP BY）使用来生成更加详细的报告或数据分析结果。

示例: 按单一列降序排序
查询选修了 03 号课程的学生的选课记录,查询结果按分数的降序排列。

SELECT *
FROM SC
WHERE Cno ='03'
ORDER BY Grade DESC;

示例: 按多列排序

查询全体学生情况,查询结果按所在班级的升序排列,同一班的学生按年龄降序排列。

SELECT *
FROM S
ORDER BY Sclass,Sage DESC;

4.使用集函数

集函数（也称为聚合函数）用于执行对一组值的计算，并返回单个值。常用的集函数包括 COUNT, SUM, AVG, MAX, 和 MIN 等。

注意事项：

• GROUP BY 和 HAVING 子句通常与集函数一起使用，以对特定组进行聚合计算。
• WHERE 和 HAVING 的主要区别在于：WHERE 用于过滤行，而 HAVING 用于过滤分组。
• GROUP BY 后可以跟一个或多个列名，用于指定分组依据。
• HAVING 子句通常用于在执行聚合计算后筛选分组，而 WHERE 子句则在聚合计算之前对行进行筛选。

示例：统计课程总数。

统计课程表中课程的总数。

这个查询使用 COUNT(*) 函数统计课程表 C 中的总行数，即课程总数。

COUNT(*) 计算表中的行数，而 COUNT(列名) 计算指定列中非空值的数量。

SELECT COUNT (*)
FROM C;

示例 2: 统计唯一值的数量

统计被学生选修的不同课程的数量。

使用 COUNT(DISTINCT Cno) 统计选课表 SC 中不同课程编号的数量。

SELECT COUNT(DISTINCT SNO) FROM SC;

示例 3: 计算平均值

计算选修 01 号课程的学生的平均成绩。

这个查询使用 AVG(Grade) 函数计算课程编号为 ‘01’ 的所有学生的平均成绩。

SELECT AVG(Grade)
FROM SC
WHERE Cno ='01';

示例 4: 查询最大值

查询选修 01 号课程学生的最高分数。

使用 MAX(Grade) 函数找出课程编号为 ‘01’ 的学生中的最高成绩。

SELECT MAX(Grade)
FROM SC
WHERE Cno ='01':

5.对查询结果分组（GROUP BY）

GROUP BY 子句用于将查询结果的行根据一列或多列的值进行分组。它通常与集函数（如 COUNT, SUM, AVG, MAX, MIN 等）结合使用，以对每个分组执行聚合计算。

示例:按列分组并计算平均值

计算每门课程的平均分。

这个查询首先根据课程编号 (Cno) 对选课表 (SC) 进行分组，然后计算每个课程的平均成绩。

SELECT Cno,AVG(Grade)
FROM SC
GROUP BY Cno;

示例 2: 使用 HAVING 过滤分组

查询学生选修课程超过 3 人的课程编号。

这个查询先按课程编号 (Cno) 分组，然后使用 HAVING 子句筛选出选修学生数大于或等于 3 的课程。

SELECT Cno
FROM SC
GROUP BY Cno
HAVING COUNT( * )> = 3;

二.连接查询

若一个查询同时涉及两个以上的表,则称之为连接查询。连接查询实际上是关系数据库中最主要的查询,主要包括等值连接、非等值连接、自然连接、自身连接、外连接和复合条件连接查询。

连接条件写在 WHERE 子句中,格式为:

[表名 1.]<列名 1><比较运算符>[表名 2.] <列名 2>

比较运算符包括=、!=、>、>=、<、<=。如果比较运算符为=，则称为等值连接否则称为非等值连接。

1.等值与非等值连接查询

等值连接

等值连接是最常见的连接类型，用于连接两个表中相等的列。

查询每个学生及其选修课程的情况：

SELECT S.*,SC.*
FROM S,SC
WHERE S.Sno = SC.Sno;

该查询的执行结果为:

这个查询展示了等值连接的典型用途：从两个表中（在本例中是学生表 S 和选课表 SC）选择相关联的记录。WHERE 子句中的条件 S.Sno = SC.Sno 确保只有当学生表中的学号与选课表中的学号相等时，记录才会被选中。

非等值连接

非等值连接用于基于不等式条件（如 >、< 等）连接两个表。虽然在实践中不如等值连接常用，但在某些情况下（如范围查询或分段查询）非常有用。

连接运算有两种特殊情况: 广义笛卡儿积连接和自然连接。

广义笛卡儿积

广义笛卡儿积，也就是没有连接条件的连接，会产生两个表所有可能组合的结果。这通常不是期望的结果，因为它会生成大量无意义的组合。

自然连接

自然连接是一种特殊的等值连接，其中排除了重复的列。在 SQL 中，可以通过显式指定要连接的列来模拟自然连接。

可用自然连接完成如下:

SELECT S.Sno,Sname,Ssex,Sage,Sclass,Cno,Grade
FROM S,SC
WHERE S.Sno = SC.Sno;

提示:

• 当处理大型表或多表连接时，使用明确的 JOIN 语法（如 INNER JOIN、LEFT JOIN）而不是旧式的逗号分隔方法可能更清晰易懂。
• 在使用连接查询时，为避免混淆，最好使用表别名，并在列名前明确指定别名（如 S.Sno、SC.Cno）。

2.自身连接

自身连接是一种特殊类型的连接，其中一个表在查询中被用作两个不同的实体。这在查询表内的数据需要相互比较时非常有用。

查询每一门课的间接先行课(即先行课的先行课)

为 C 表取两个别名，FIRST、SECOND,完成该查询的 SQL 语句为:

SELECT FIRST.Cno,SECOND.Cpno
FROM C FIRST,C SECOND
WHERE FIRST.Cpno = SECOND.CNO AND SECOND.CPNO IS NOT NULL;

这个查询用于查找每一门课程的间接先行课程。通过为同一表 C 创建两个别名 FIRST 和 SECOND，并在 WHERE 子句中建立连接条件，此查询有效地将一门课程的先行课程与另一门课程的课程编号进行比较。结果显示了课程之间的这种间接关联。

该查询的执行结果为:

3.外连接

外连接是连接查询的一个变体，它包括了那些即使在另一表中没有匹配项也会被包含在结果集中的行。外连接分为左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。

SELECT S.Sno,Sname,Ssex,Sage,Sclass,Cno,Grade 
FROM S LEFT OUTER JOIN SC
ON S.Sno = Sc.Sno;

这个查询展示了左外连接的应用。通过使用 LEFT OUTER JOIN，查询包含了 S 表（左表）的所有行，即使在 SC 表（右表）中没有匹配的行。如果右表中没有匹配的行，则相关列会以空值（NULL）填充，从而确保左表的每一行都会出现在结果集中。

外连接的表示方法为:加入关键字表 1 LEFT/RIGHT OUTER JOIN 表 2 ON 谓词表达式。

外连接就好像是为符号 * 所在边的表增加一个“万能”的行,这个行全部由空值组成,它可以和另一边的表中所有不满足连接条件的元组进行连接。

执行结果如下:

提示:

• 自身连接通常用于查询表内部的关联，如层级关系、前后关系等。
• 外连接特别适用于需要展示一个表的所有行，同时显示与另一个表的关联（如果存在）的情况。
• 在使用外连接时，明确选择 LEFT 或 RIGHT JOIN 可以确保你正确地理解和展示了数据的关联

4.复合条件连接

复合条件连接允许您在 WHERE 子句中使用多个条件来精确地定义连接的要求。

示例：查询选修 02 号课程且成绩在 90 分以上的所有学生。

SELECT S.Sno,Sname
FROM S,SC
WHERE S.Sno = SC.Sno AND SC.Cno ='02' AND SC.Grade > 90;

连接操作除了可以是两表连接或一个表自身连接外,还可以是两个以上的表进行连接,即多表连接。

示例：查询每个学生的学号、姓名、选修的课程及成绩

SELECT S.Sno,Sname,Cname,Grade
FROM S,SC,C
WHERE S.Sno = SC.SnO AND SC.Cno = C.Cno;

三.嵌套查询

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

查询学号为“2006111121”的学生选修的课程名称。

SELECT Cname
FROM C
WHERE CnO IN (SELECT Cno
FROM SC
WHERE Sno ='2006111121');

上层的查询块称为外层查询或父查询,下层查询块称为内层查询或子查询。SELECT Cno FROM SC WHERE Sno='2006111121’为子查询。

特点和注意事项：

1. 层级结构：嵌套查询可以多层嵌套，形成层级结构。
2. 执行顺序：通常，子查询先于父查询执行，其结果用于父查询的条件判断。
3. 子查询限制：在子查询中通常不能使用 ORDER BY 子句，因为子查询的主要目的是提供条件结果集，而非最终排序结果。
4. 结果集的使用：子查询的结果可以作为父查询的条件、值列表或作为列使用。
5. 查询的强大性：嵌套查询大大增强了 SQL 的查询能力，允许进行更复杂和动态的数据检索。

1.带有 IN 谓词的子查询

查询以“程序设计基础”为先行课的课程。

使用IN 的嵌套查询:

内层查询首先找出课程名为“程序设计基础”的课程编号，然后外层查询使用这些编号来找出所有以这些课程为先行课的课程。

SELECT *
FROM C
WHERE Cpno IN
(SELECT Cno
FROM C
WHERE Cname ='程序设计基础');

结果为:

DBMS 求解嵌套时也是分步执行的,类似于分步查询,即首先确定“程序设计基础”的课程编号,然后求解父查询,查找所有以’03‘为先行课的课程。

本例中的查询也可以用自身连接来完成：

在这个例子中，表 C 被分为两个别名 FIRST 和 SECOND，然后通过连接这两个别名来完成查询。

SELECT FIRST.*
FROM C FIRST,C SECOND
WHERE SECOND.Cname ='程序设计基础'AND FIRST.Cpno = SECOND.Cno;

本例中父查询和子查询均引用了 C 表,可以像自身连接那样用别名将父查询中的 C表与子查询中的 C 表区分开:

SELECT *
FROM C C1
WHERE C1.Cpno IN
(SELECT Cno FROM C C2 WHERE C2.Cname ='程序设计基础');

查询选修了课程名为“程序设计基础”的学生。

SELECT *
FROM S
WHERE Sno IN
	(SELECT Sno
	FROM SC
	WHERE Cno IN
		(SELECT CnO FROM C WHERE Cname ='程序设计基础')
	);

结果为:

本查询同样可以用连接查询来实现:

SELECT S.*
FROM S,SC,C
WHERE S.Sno = SC.Sno AND SC.Cno = C.Cno AND
C.Cname ='程序设计基础';

查询涉及多个关系时,用嵌套查询逐步求解,层次清楚,易于构造,具有结构化程序设计的优点。

子查询的查询条件不依赖于父查询,这类子查询称为不相关子查询。不相关子查询是最简单的一类子查询

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

当我们需要基于另一个查询的结果来过滤或比较数据时，带有比较运算符的子查询是一个非常有用的工具。这种类型的子查询通常出现在 SQL 语句的 WHERE 子句中，并用于与主查询中的某个字段进行比较。

基本概念

• 子查询：一个嵌套在另一个查询中的 SQL 查询。
• 比较运算符：可以是 =, <>, !=, >, <, >=, <= 等。
• 单值子查询：子查询返回单个值，适用于与外部查询的一个字段进行比较。

示例说明

假设我们有一个学生表（S），我们想找出与特定学生（例如“王昕”）处于同一班级的所有学生。这里，我们的子查询首先找出“王昕”的班级，然后外层查询使用这个信息来选择同一班级的其他学生。

SELECT Sno，Sname，Sclass
FROM S
WHERE Sclass =
	(SELECT Sclass
	FROM S
	WHERE Sname ='王昕'
	);

注意:子查询一定要跟在比较运算符之后，下面的 SQL 语句是错误的。

SELECT Sno,Sname,Sclass
FROM S
WHERE (SELECT Sclass
	FROM S
	WHERE Sname ='王昕' )
	= Sclass;

注意点

• 确保子查询可以返回单个值，因为比较运算符不能直接用于比较多个值。
• 子查询的结果应与外部查询的相关字段兼容，例如，不能将数字与字符串进行比较。
• 子查询应避免返回 NULL 值，因为这可能导致外部查询的逻辑出错。

四.集合查询

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

1.并集（UNION）

并集操作 UNION 用于合并两个或多个 SELECT 语句的结果集，去除重复的行。

示例： 查询计科 0701 班的学生及年龄小于 20 岁的学生。

SELECT *
FROM S
WHERE Sclass ='CS0701'
UNION
SELECT *
FROM S
WHERE Sage < 20;

注意:参加 UNION 操作的各结果表的列数必须相同; 对应项的数据类型也必须相同。

2.交集（INTERSECT）

交集操作 INTERSECT 用于返回两个查询结果集的共同部分。

标准 SQL 中没有直接提供集合交操作和集合差操作,但可以用其他方法来实现。

示例： 查询选修课程 03 的学生集合与选修课程 05 的学生集合的交集：

SELECT Sno
FROM SC
WHERE Cno ='03'
INTERSECT
SELECT Sno
FROM SC
WHERE Cno ='05':

可以用下面的 SELECT 语句实现:

SELECT Sno
FROM SC
WHERE Cno ='03' AND Sno IN
	(SELECT Sno
	FROM SC
	WHERE Cno='05');

3.差集（MINUS）

差集操作 MINUS 用于返回第一个查询结果集中独有的部分。

示例： 查询计科 0701 班的学生与年龄小于 20 岁的学生的差集：

SELECT *
FROM S
WHERE Sclass ='CS0701'AND Sae> 20;

注意事项:

• 参与集合操作的各查询结果的列数和列的数据类型必须相同。
• UNION 默认去除重复行，如果想保留重复行，可以使用 UNION ALL。
• 并集、交集和差集操作通常用于复杂的数据分析和报告。