SQL 高级查询技巧与预处理语句实战详解

基础查询操作

基础查询是日常开发中最频繁的操作，主要包括字段选择、别名设置及去重等场景。

查询全部或部分字段：使用 SELECT * 获取所有列，但生产环境建议明确指定字段名以提升性能。

SELECT sname, class_id FROM student; -- 只查姓名和班级 ID
SELECT * FROM student;               -- 查所有信息

字段别名：使用 AS 关键字让结果更易读，注意别名不要使用 SQL 保留字。

SELECT sname AS '姓名', class_id AS '班级 ID' FROM student;

去除重复记录：当需要统计唯一值时，配合 DISTINCT 使用。

SELECT DISTINCT class_id FROM student; -- 查询不重复的班级 ID

条件与范围筛选

利用 WHERE 子句进行数据过滤，支持单条件或多条件组合。

多条件组合：AND 表示同时满足，OR 表示满足其一。

-- 查询性别为男且班级 ID 为 2 的学生
SELECT * FROM student WHERE gender = '男' AND class_id = 2;

范围查询：BETWEEN ... AND 包含边界值。

-- 查询班级 ID 在 1 到 3 之间的学生
SELECT * FROM student WHERE class_id BETWEEN 1 AND 3;

判空处理：区分 NULL（无值）和空字符串 ''（有值但为空），前者用 IS NULL。

-- 判断非空字符串
SELECT * FROM student WHERE gender <> '';
-- 判断 NULL 值
SELECT * FROM student WHERE class_id IS NULL;

模糊匹配：LIKE 配合通配符 %（任意字符）和 _（单个字符）。

-- 第二个字是'小'的教师
SELECT * FROM teacher WHERE tname LIKE '_小%';
-- 以'谢'开头的教师
SELECT * FROM teacher WHERE tname LIKE '谢%';

排序与分页

排序：ORDER BY 支持升序（ASC）和降序（DESC），可多字段排序。

-- 先按课程 ID 降序，再按分数降序
SELECT * FROM score ORDER BY course_id DESC, num DESC;

分页：LIMIT 语法为 LIMIT 起始位置，显示条数，注意索引从 0 开始。

-- 从第 2 条记录开始，取 2 条
SELECT * FROM student LIMIT 1, 2;

聚合与分组

聚合函数用于数据统计，如 SUM, AVG, MAX, MIN, COUNT。

分组查询：GROUP BY 将数据归类，常配合聚合函数使用。HAVING 用于过滤分组后的结果。

-- 按性别分组，统计人数并筛选人数大于 6 的组
SELECT gender, COUNT(*) AS num 
FROM student 
GROUP BY gender 
HAVING num > 6;

字段拼接：GROUP_CONCAT 可将组内多个值合并为一个字符串。

SELECT gender, GROUP_CONCAT(age) AS ages FROM student GROUP BY gender;

联表查询

当数据分散在多表中时，需要通过连接操作整合信息。主要有三种方式：

• INNER JOIN：仅返回两表匹配的记录。
• LEFT JOIN：保留左表所有记录，右表无匹配则为 NULL。
• RIGHT JOIN：保留右表所有记录，左表无匹配则为 NULL。

-- 左连接：保留所有课程，即使没有对应教师
SELECT c.cid FROM course c LEFT JOIN teacher t ON c.teacher_id = t.tid;

-- 内连接：只查询有授课教师的课程
SELECT c.cid FROM course c INNER JOIN teacher t ON c.teacher_id = t.tid;

子查询与正则

子查询允许在一个查询中嵌套另一个查询，常用于复杂逻辑判断。

单行子查询：返回单个值，用于比较。

-- 查询谢小二老师所授的所有课程
SELECT * FROM course WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '谢小二老师');

多行子查询：返回多行，常用 IN 或 EXISTS。

-- 查询学过特定课程的学生的学号
SELECT * FROM student WHERE class_id IN (SELECT cid FROM course WHERE teacher_id = 2);

FROM 子句中的子查询：将子查询结果视为临时表。

SELECT s.sid, s.sname 
FROM student s 
JOIN (SELECT student_id FROM score WHERE course_id IN (1, 2)) AS temp 
ON s.sid = temp.student_id;

正则表达式：REGEXP 提供更灵活的匹配规则，如 ^ 开头，. 任意字符等。

-- 查询姓名为'谢'开头的教师
SELECT * FROM teacher WHERE tname REGEXP '^谢';

预处理语句

预处理语句将 SQL 分为'准备'和'执行'两个阶段，主要用于高频执行的场景，能有效防止 SQL 注入并提升性能。

核心流程：

1. 准备：发送 SQL 模板给数据库，解析编译生成执行计划缓存。
2. 执行：传入具体参数，直接复用缓存的执行计划。

优势：

• 减少重复解析开销。
• 自动转义参数，杜绝 SQL 注入风险。

示例：

-- ? 为占位符
SELECT * FROM table_name WHERE id = ?;

实战演练

下面通过几个典型问题来巩固上述知识点。

1. 查询平均成绩大于 60 分的同学学号和平均分

思路：按学生分组计算平均分，再用 HAVING 筛选。

SELECT student_id, AVG(num) AS avg_num 
FROM score 
GROUP BY student_id 
HAVING avg_num > 60;

2. 查询 C++ 课程比数据库课程成绩高的学生学号

思路：分别查出两门课的成绩作为临时表，然后关联对比。

SELECT A.student_id 
FROM (
    SELECT student_id, num FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = 'c++')
) AS A 
INNER JOIN (
    SELECT student_id, num FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = '数据库')
) AS B ON A.student_id = B.student_id 
WHERE A.num > B.num;

3. 查询所有同学的学号、姓名、选课数、总成绩

思路：关联学生表和成绩表，左连接确保没选课的学生也能显示。

SELECT s.sid AS '学号', s.sname AS '姓名', 
       COUNT(sc.course_id) AS '选课数', 
       SUM(sc.num) AS '总成绩'
FROM student s 
LEFT JOIN score sc ON s.sid = sc.student_id 
GROUP BY s.sid, s.sname;

4. 查询没学过'谢小二'老师课的同学

思路：排除掉学过该老师课程的学生 ID。

SELECT sid AS '学号', sname AS '姓名' 
FROM student 
WHERE sid NOT IN (
    SELECT DISTINCT sc.student_id 
    FROM score sc 
    JOIN course c ON sc.course_id = c.cid 
    JOIN teacher t ON c.teacher_id = t.tid 
    WHERE t.tname = '谢小二'
);

5. 查询学过课程编号为 1 并且也学过课程编号为 2 的同学

思路：统计每个学生选的课程数，要求同时选了这两门（即 distinct count = 2）。

SELECT s.sid AS '学号', s.sname AS '姓名'
FROM student s 
JOIN score sc ON s.sid = sc.student_id 
WHERE sc.course_id IN (1, 2)
GROUP BY s.sid, s.sname 
HAVING COUNT(DISTINCT sc.course_id) = 2;

6. 查询学过某老师所有课的同学

思路：先算出该老师教了多少门课，再筛选出学生学过的数量等于该总数的记录。

SELECT s.sid AS '学号', s.sname AS '姓名'
FROM student s 
JOIN score sc ON s.sid = sc.student_id 
JOIN course c ON sc.course_id = c.cid 
JOIN teacher t ON c.teacher_id = t.tid 
WHERE t.tname = '谢小二'
GROUP BY s.sid, s.sname 
HAVING COUNT(DISTINCT sc.course_id) = (
    SELECT COUNT(cid) FROM course WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '谢小二')
);

7. 查询有课程成绩小于 60 分的学生

SELECT DISTINCT s.sid AS '学号', s.sname AS '姓名'
FROM student s 
JOIN score sc ON s.sid = sc.student_id 
WHERE sc.num < 60;

8. 查询没有学全所有课的同学

思路：总课程数减去学生选课数，差值大于 0 即为未学全。

SELECT s.sid AS '学号', s.sname AS '姓名'
FROM student s 
LEFT JOIN score sc ON s.sid = sc.student_id 
GROUP BY s.sid, s.sname 
HAVING COUNT(DISTINCT sc.course_id) < (SELECT COUNT(cid) FROM course);

9. 查询至少有一门课与学号为 1 的同学所学相同的同学

SELECT DISTINCT s.sid AS '学号', s.sname AS '姓名'
FROM student s 
JOIN score sc ON s.sid = sc.student_id 
WHERE sc.course_id IN (SELECT course_id FROM score WHERE student_id = 1)
AND s.sid <> 1;

10. 查询至少学过学号为 1 同学所有课的其他同学

思路：类似第 6 题，对比的是学号 1 的课程总数。

SELECT s.sid AS '学号', s.sname AS '姓名'
FROM student s 
JOIN score sc ON s.sid = sc.student_id 
WHERE s.sid <> 1 
AND sc.course_id IN (SELECT course_id FROM score WHERE student_id = 1)
GROUP BY s.sid, s.sname 
HAVING COUNT(DISTINCT sc.course_id) = (
    SELECT COUNT(course_id) FROM score WHERE student_id = 1
);