请使用支持现代 CSS 与 JavaScript 的浏览器播放课件
DBPA · 3.3

数据查询

把自然语言问题组合成结果关系

VER. 2608.2 Built with impress.js
DBPA | CH03 | 3.3

学习目标

一个 SQL 查询先从输入关系形成结果关系，再用连接、嵌套、集合和派生表组合更复杂的条件

  1. 01读懂 SELECT 查询块各子句的作用
  2. 02完成单表筛选、排序、聚集与分组
  3. 03根据关系联系选择合适的连接方式
  4. 04用子查询表达单值、集合与存在性条件
  5. 05判断集合运算和派生表的使用前提
  6. 06区分查询结果语义与实际执行计划
2/20
LEARNING OBJECTIVES
DBPA | CH03 | 3.3

一个 SELECT 查询块从关系生成结果

FROM 提供输入关系，WHERE 筛选元组，SELECT 形成结果列

FROM、WHERE、SELECT 从输入关系形成结果关系的语义路径
SQL
SELECT Sname, Smajor
FROM Student
WHERE Smajor = '信息安全';
3/20
QUERY BLOCK
DBPA | CH03 | 3.3 | 3.3.1

选择目标列，再决定是否消除重复

投影改变列，DISTINCT 决定结果中是否消除重复

写法结果语义适用判断
SELECT Sno FROM SC选出每条选课记录的学号同一学生可出现多次
SELECT DISTINCT Sno FROM SC只保留不同学号需要学生集合
SELECT Sname AS name FROM Student选择列并改结果列标题结果需要更清楚的列名
SELECT * FROM Student选择全部列临时查看或探索数据
Takeaway

关系代数常按集合理解，SQL 默认保留重复行

4/20
SINGLE TABLE
DBPA | CH03 | 3.3 | 3.3.1

WHERE 把自然语言条件变成元组筛选

WHERE 先筛选单个元组，聚集结果由 HAVING 筛选

比较与范围

=、>、BETWEEN AND

集合与匹配

IN、NOT IN、LIKE

逻辑组合

AND、OR、NOT

空值入口

IS NULL、IS NOT NULL；三值逻辑留到 3.5

Takeaway

WHERE 先于分组作用于元组

5/20
WHERE PREDICATES
DBPA | CH03 | 3.3 | 3.3.1

排序与分页改变结果呈现，不改变基本表

ORDER BY 作用于查询结果，LIMIT 控制返回行数

SQL
-- MySQL 8.4 常见分页语法
SELECT Sno, Grade
FROM SC
WHERE Cno = '81003'
ORDER BY Grade DESC, Sno ASC
LIMIT 10 OFFSET 0;
子句作用
ORDER BY Grade DESC按成绩降序排列结果
LIMIT 10只返回前 10 行
LIMIT 5 OFFSET 2跳过 2 行后返回 5 行
Takeaway

先得到满足条件的结果，再决定显示顺序与窗口；LIMIT/OFFSET 是 MySQL 常见语法，分页要有稳定的次排序列

6/20
ORDER AND LIMIT
DBPA | CH03 | 3.3 | 3.3.1

聚集把多行压缩成统计结果

没有分组时，聚集函数作用于整个满足条件的结果

SQL
SELECT COUNT(*) AS enrollment_count
FROM SC
WHERE Semester = '20202';

COUNT

COUNT(*) 统计行；COUNT(column) 统计非空值；COUNT(DISTINCT column) 统计不同值

SUM 与 AVG

计算数值列的总和与平均值

MAX 与 MIN

寻找一列中的极值

7/20
AGGREGATION
DBPA | CH03 | 3.3 | 3.3.1

WHERE 筛选行，HAVING 筛选组

WHERE 在分组前筛选元组，HAVING 在分组后筛选组

SQL
SELECT Cno, COUNT(*) AS enrollment_count
FROM SC
WHERE Semester = '20202'
GROUP BY Cno
HAVING COUNT(*) > 10;
条件位置作用对象例子
WHERE单个元组学期为 20202
GROUP BY形成分组按课程号分组
HAVING一个分组人数大于 10
8/20
GROUP AND HAVING
DBPA | CH03 | 3.3 | 3.3.2

连接用键把多个关系组合成一个结果

结果列来自多个表，连接条件决定哪些元组能够配对

Student、SC、Course 按键连接并形成学号、学生姓名、课程名和成绩结果
SQL
-- Student.Sno = SC.Sno；SC.Cno = Course.Cno
SELECT s.Sno, s.Sname, c.Cname, sc.Grade
FROM Student AS s
JOIN SC AS sc ON s.Sno = sc.Sno
JOIN Course AS c ON sc.Cno = c.Cno;
9/20
JOIN QUERY
DBPA | CH03 | 3.3 | 3.3.2

连接要分别判断匹配、保留和拓扑

连接形式同时涉及匹配条件、未匹配元组的保留规则和关系拓扑

判断维度常见选择先问什么
连接谓词等值、非等值、复合条件哪些元组能够配对？
保留规则内连接、左／右外连接未匹配元组是否保留？
关系拓扑两表、多表、自身连接需要几个关系副本？
Takeaway

连接判断包括匹配条件、未匹配元组的保留规则和关系拓扑。FULL OUTER JOIN 属于标准 SQL，MySQL 8.4 没有直接语法；MySQL 8.4 可直接使用左／右外连接

10/20
JOIN VARIANTS
DBPA | CH03 | 3.3 | 3.3.2

外连接保留没有匹配项的主体记录

左外连接可以列出 Student 中所有学生，包括尚未选课的学生

SQL
SELECT s.Sno, s.Sname, sc.Cno, sc.Grade
FROM Student AS s
LEFT OUTER JOIN SC AS sc ON s.Sno = sc.Sno;
结果部分内连接左外连接
已选课学生保留保留
未选课学生舍弃保留并补空值
业务含义只看匹配记录以左表为完整主体
11/20
OUTER JOIN
DBPA | CH03 | 3.3 | 3.3.3

子查询把复杂条件拆成内外两层

外层查询把内层查询返回的集合用于条件判断；本例是不相关子查询

SQL
SELECT Sno, Sname
FROM Student
WHERE Smajor IN
      (SELECT Smajor
       FROM Student
       WHERE Sname = '刘晨');
12/20
NESTED QUERY
DBPA | CH03 | 3.3 | 3.3.3

子查询返回什么，决定外层条件怎么写

子查询结果若为单值，可用 = 或 > 比较；若为集合，可用 IN、ANY 或 ALL；若只判断是否存在，可用 EXISTS 或 NOT EXISTS

SQL
-- EXISTS：只判断是否有匹配行
SELECT s.Sname
FROM Student AS s
WHERE EXISTS (
  SELECT 1
  FROM SC AS sc
  WHERE sc.Sno = s.Sno
    AND sc.Cno = '81001'
);
13/20
SUBQUERY SHAPE
DBPA | CH03 | 3.3 | 3.3.3

相关子查询让内层条件随外层元组变化

先标出父查询列，再判断每一行对应的内层结果

SQL
SELECT x.Sno, x.Cno
FROM SC x
WHERE x.Grade >=
      (SELECT AVG(y.Grade)
       FROM SC y
       WHERE y.Sno = x.Sno);
Takeaway

y.Sno = x.Sno 是相关条件：x.Sno 来自父查询，y.Sno 来自内层查询。Grade 为 NULL 时的比较留到 3.5。相同问题也可以用连接、聚集或派生表表达，应先验证结果是否正确，再比较写法

14/20
CORRELATED QUERY
DBPA | CH03 | 3.3 | 3.3.4

集合运算组合结构相同的查询结果

先检查列数、对应列类型、列顺序与业务含义，再决定并、交或差

SQL
SELECT Sno
FROM SC
WHERE Cno = '81001'
UNION
SELECT Sno
FROM SC
WHERE Cno = '81002';
运算结果业务提问
UNION两个结果的并集满足任一条件
INTERSECT两个结果的交集同时满足两个条件
EXCEPT左结果减去右结果满足左条件且排除右条件
Takeaway

不加 ALL 的集合运算默认按集合语义去重；UNION ALL 明确保留重复行。UNION、INTERSECT、EXCEPT 属于标准 SQL，MySQL 对这些运算的支持范围需要按授课时版本核对

15/20
SET QUERY
DBPA | CH03 | 3.3 | 3.3.5

FROM 中的子查询形成派生表

派生表让主查询继续处理一个已经整理过的中间关系

SQL
SELECT SC.Sno, SC.Cno
FROM SC
JOIN (SELECT Sno, AVG(Grade) AS Avg_grade
      FROM SC
      GROUP BY Sno) AS Avg_SC
  ON SC.Sno = Avg_SC.Sno
WHERE SC.Grade >= Avg_SC.Avg_grade;
Takeaway

派生表必须有别名，例如 Avg_SC(Sno, Avg_grade)；它只在当前查询中有效，不作为持久化对象保存，也不是视图

16/20
DERIVED TABLE
DBPA | CH03 | 3.3

同一查询语义可以对应不同执行路径

SQL 描述结果条件，数据库管理系统（DBMS）再选择具体访问方式

同一查询语义可以对应全表扫描、索引查找或不同连接次序
SQL
EXPLAIN
SELECT s.Sname, c.Cname, sc.Grade
FROM Student s JOIN SC sc ON s.Sno = sc.Sno
JOIN Course c ON sc.Cno = c.Cno;
Takeaway

用户先写结果语义，DBMS 再选择执行计划；具体产品决定语法与边界，EXPLAIN 可以观察当前产品的执行路径

17/20
SEMANTICS AND PLAN
DBPA | CH03 | 3.3

用结果关系组织数据查询

查询先确定输入关系，再筛选元组、形成结果列，并按需组合中间结果

单表

单表查询决定目标列、行条件、去重、排序、聚集和分组

多表

多表查询用连接条件和别名组合关系，并明确外连接的保留规则

组合

嵌套、集合和派生表可以表达复杂条件；它们产生的中间结果在形状和作用域上不同

Takeaway

SQL 写法应先满足结果关系，再比较可读性和产品边界

18/20
RECAP
DBPA | CH03 | 3.3

本节知识地图

19/20
KNOWLEDGE MAP
DBPA | CH03 | 3.3

本节问题

  1. 01SELECT、DISTINCT、WHERE、GROUP BY 与 HAVING 分别处理什么？WHERE 与 HAVING 的先后如何？
  2. 02连接如何区分匹配条件、保留规则和关系拓扑？左外连接中的 SQL NULL 表示什么？
  3. 03子查询返回单值、集合或存在性结果时，如何选择 =、IN、EXISTS？y.Sno = x.Sno 为什么表示相关？
  4. 04集合运算的兼容、重复和版本边界如何判断？派生表为何需别名，与基本表/视图有何异同？如何用 EXPLAIN 观察执行计划？
20/20
CHECK YOUR UNDERSTANDING