3.4 《数据库系统概论》之数据查询---SELECT（从基础到进阶：单表操作、多表关联与子查询实战）

1. SELECT查询基础入门SQL查询就像在图书馆找书一样SELECT语句就是我们的图书检索系统。想象一下当你走进图书馆首先要确定想找什么书选择列在哪个区域找从哪张表以及有什么具体要求过滤条件。这就是SELECT查询的基本逻辑。SELECT语句的标准格式如下SELECT [ALL|DISTINCT] 目标列表达式 [别名] FROM 表名或视图名 [别名] [WHERE 条件表达式] [GROUP BY 列名1 [HAVING 条件表达式]] [ORDER BY 列名2 [ASC|DESC]]举个最简单的例子假设我们有一个学生表Student包含学号(Sno)、姓名(Sname)、年龄(Sage)等字段-- 查询所有学生的姓名和年龄 SELECT Sname, Sage FROM Student; -- 查询计算机系的所有学生信息 SELECT * FROM Student WHERE Sdept CS;在实际项目中我经常看到新手容易犯的几个错误忘记指定WHERE条件导致全表扫描SELECT * 查询不必要的数据列忽略DISTINCT导致结果重复2. 单表查询的进阶技巧2.1 列选择与计算字段SELECT不仅可以查询原始列还能进行各种计算和转换-- 计算学生出生年份假设当前是2023年 SELECT Sname, 2023-Sage AS BirthYear FROM Student; -- 使用函数处理数据 SELECT Sname, UPPER(Sdept) AS Department FROM Student; -- 使用CASE WHEN条件表达式 SELECT Sname, CASE WHEN Sage 20 THEN 新生 ELSE 老生 END AS StudentType FROM Student;2.2 条件过滤的艺术WHERE子句是SQL的过滤系统常用的条件运算符包括比较运算符, , , , , 范围检查BETWEEN AND集合检查IN模糊匹配LIKE空值判断IS NULL-- 查询年龄在20-23岁之间的学生 SELECT * FROM Student WHERE Sage BETWEEN 20 AND 23; -- 查询姓张且名字为两个字的学生 SELECT * FROM Student WHERE Sname LIKE 张_; -- 查询没有成绩记录的选课信息 SELECT * FROM SC WHERE Grade IS NULL;2.3 排序与聚合函数ORDER BY和聚合函数是数据分析的利器-- 按成绩降序排列 SELECT Sno, Grade FROM SC WHERE Cno3 ORDER BY Grade DESC; -- 常用聚合函数 SELECT COUNT(*) AS TotalStudents, AVG(Sage) AS AvgAge, MAX(Sage) AS MaxAge FROM Student;GROUP BY的使用需要注意SELECT中的非聚合列必须出现在GROUP BY中WHERE在分组前过滤HAVING在分组后过滤-- 统计各系学生人数 SELECT Sdept, COUNT(*) AS StudentCount FROM Student GROUP BY Sdept; -- 查询选课超过3门的学生 SELECT Sno, COUNT(*) AS CourseCount FROM SC GROUP BY Sno HAVING COUNT(*) 3;3. 多表连接查询实战3.1 连接原理与类型连接查询就像拼图游戏我们需要找到表之间的关联字段。常见的连接类型包括内连接(INNER JOIN)只返回匹配的行左外连接(LEFT JOIN)返回左表所有行右表不匹配则为NULL右外连接(RIGHT JOIN)返回右表所有行左表不匹配则为NULL全外连接(FULL JOIN)返回左右表所有行-- 等值连接查询学生及其选课信息 SELECT Student.Sno, Sname, Cno, Grade FROM Student, SC WHERE Student.Sno SC.Sno; -- 使用JOIN语法更清晰 SELECT Student.Sno, Sname, Cno, Grade FROM Student INNER JOIN SC ON Student.Sno SC.Sno;3.2 连接性能优化多表连接时数据库通常采用以下算法嵌套循环连接(Nested Loop)适合小表驱动大表哈希连接(Hash Join)适合中等规模数据排序合并连接(Sort-Merge)适合已排序数据-- 复合条件连接查询选修2号课程且成绩88的学生 SELECT Student.Sno, Sname FROM Student JOIN SC ON Student.Sno SC.Sno WHERE SC.Cno2 AND SC.Grade 88;在实际项目中连接查询最容易出现的性能问题就是笛卡尔积忘记写连接条件我曾见过一个查询因为漏写连接条件导致两个百万级表的笛卡尔积直接把数据库卡死。4. 子查询与嵌套查询4.1 子查询类型子查询就像SQL中的俄罗斯套娃主要分为不相关子查询子查询可独立执行相关子查询子查询依赖外部查询-- 不相关子查询查询与刘晨同系的学生 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname刘晨); -- 相关子查询查询超过自己平均成绩的课程 SELECT Sno, Cno FROM SC x WHERE Grade (SELECT AVG(Grade) FROM SC y WHERE y.Snox.Sno);4.2 EXISTS与NOT EXISTSEXISTS用于检查子查询是否返回结果不关心具体数据-- 查询选修了1号课程的学生 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE SnoStudent.Sno AND Cno1);4.3 集合查询集合操作包括UNION(并)、INTERSECT(交)、EXCEPT(差)-- 查询计算机系或年龄19的学生 SELECT * FROM Student WHERE SdeptCS UNION SELECT * FROM Student WHERE Sage19;5. 实战案例与性能优化5.1 典型查询模式-- 查询每个学生的总学分 SELECT Student.Sno, Sname, SUM(Ccredit) AS TotalCredit FROM Student JOIN SC ON Student.Sno SC.Sno JOIN Course ON SC.Cno Course.Cno WHERE Grade 60 -- 及格才算学分 GROUP BY Student.Sno, Sname; -- 查询各科成绩前三名 SELECT Cno, Sno, Grade FROM SC x WHERE (SELECT COUNT(*) FROM SC y WHERE y.Cnox.Cno AND y.Gradex.Grade) 3 ORDER BY Cno, Grade DESC;5.2 性能优化建议为常用连接字段创建索引避免在WHERE条件中对字段进行函数操作只查询需要的列避免SELECT *大数据量表连接时先过滤再连接复杂查询考虑使用临时表或视图-- 优化示例先过滤再连接 SELECT S.Sno, Sname, Cname, Grade FROM (SELECT * FROM Student WHERE SdeptCS) S JOIN SC ON S.Sno SC.Sno JOIN Course ON SC.Cno Course.Cno;在实际项目中我曾优化过一个需要20秒执行的查询通过添加适当的索引和重写查询逻辑最终将执行时间降到了0.5秒。关键是要理解数据库的执行计划知道它如何访问和处理数据。