史上最细SQL实战系列：sql笔试例题总结篇（建议收藏）

这是我的史上最细SQL实战系列的sql笔试例题总结篇。本文章例题所用知识点可回顾之前发的文章：sql基础知识篇和基础知识篇补充本系列文章核心不在于总结Mysql相关的八股（Mysql超全八股可见此文：Mysql超全八股笔记贴），本系列旨在：1.快速掌握sql知识，会写面试时常见的sql题目；2.了解在实际工作中需要注意的超多数据库和表设计相关的知识和坑点。

本系列文章分为3个部分：1基础知识篇；2sql笔试篇；3公司级数据库与表设计相关知识与坑点总结。

另外：

想要学习Java冲实习或冲春招的，我能助你一臂之力，我之前整理了高质量可速成的魔改外卖项目话术和7000字轮子项目话术，还有超全超精品八股大全专栏，怎么写简历，怎么包装实习经历，怎么0基础速成冲春招和实习等等等等精品帖子，大家可以去看看我的精品文章汇总帖子：往期精品帖子汇总

我的八股大全、算法、项目话术全专栏（20w人学习，超千人订阅，牛客最受欢迎最高质量java八股专栏，内容包含： 1.八股大全：多一句没有少一句不行的最精简八股整理，完全可以应付校招社招的八股拷打！ 2.速成项目话术：目前有魔改苍穹外卖项目话术（额外扩展了很多技术亮点），能速成拿去面试，后面会更新魔改黑马点评、商城项目等等热门高质量项目话术 3.智力题超详细题解汇总； 4.面试时非技术问题话术整理，绝对震惊面试官一年； 5.算法lc hot100全题系列题解：绝对通俗易懂：快速冲刺秋招专栏

---------------------------------------------------------------------

1.建表

总共4张表，包括学生、成绩、科目和教师，建表SQL如下：

-- 学生表

create table student(
	s_id varchar(20) not null,
	s_name varchar(20) not null default '',
	s_birth varchar(20) not null default '',
	s_sex varchar(10) not null default '男',
	primary key(s_id)
);
-- 成绩表

CREATE TABLE score(
	s_id VARCHAR(20),
	c_id  VARCHAR(20),
	s_score INT,
	PRIMARY KEY(s_id,c_id)
);
-- 教师表

CREATE TABLE teacher(
	t_id VARCHAR(20),
	t_name VARCHAR(20) NOT NULL DEFAULT '',
	PRIMARY KEY(t_id)
);
-- 课程表

CREATE TABLE course(
	c_id  VARCHAR(20),
	c_name VARCHAR(20) NOT NULL DEFAULT '',
	t_id VARCHAR(20) NOT NULL,
	PRIMARY KEY(c_id)
);

2.插入数据

-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 教师表测试数据
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');
-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

3.SQL查询语句练习

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

方法1：子连接 
SELECT 
    s.*, 
    s01.s_score AS score_01, 
    s02.s_score AS score_02
FROM 
    student s
JOIN score s01 ON s.s_id = s01.s_id AND s01.c_id = '01'
JOIN score s02 ON s.s_id = s02.s_id AND s02.c_id = '02'
WHERE 
    s01.s_score > s02.s_score;

方法2：子查询
SELECT 
    s.*, 
    (SELECT s_score FROM score WHERE s_id = s.s_id AND c_id = '01') AS score_01,
    (SELECT s_score FROM score WHERE s_id = s.s_id AND c_id = '02') AS score_02
FROM 
    student s
WHERE 
    (SELECT s_score FROM score WHERE s_id = s.s_id AND c_id = '01') > 
    (SELECT s_score FROM score WHERE s_id = s.s_id AND c_id = '02');

方法3：CTE
WITH course_01 AS (
    SELECT s_id, s_score FROM score WHERE c_id = '01'
),
course_02 AS (
    SELECT s_id, s_score FROM score WHERE c_id = '02'
)
SELECT 
    st.*, 
    c01.s_score AS score_01, 
    c02.s_score AS score_02
FROM 
    student st
JOIN course_01 c01 ON st.s_id = c01.s_id
JOIN course_02 c02 ON st.s_id = c02.s_id
WHERE 
    c01.s_score > c02.s_score;

方法一（自连接） 是最推荐的：

执行效率最高，只需要扫描一次 score 表
结构清晰，易于理解和维护
索引优化友好（建议在 score 表上创建复合索引 (s_id, c_id, s_score)）

子查询（方法二）

优点：语法简洁，直观表达查询逻辑
缺点：对每个学生执行两次子查询，大数据量下性能较差

CTE（方法三）

优点：逻辑分离，提高可读性，适合复杂查询
缺点：在简单查询中略显冗余

2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

方法1：分组
SELECT 
    s.s_id, 
    s.s_name, 
    AVG(sc.s_score) AS avg_score
FROM 
    student s
JOIN score sc ON s.s_id = sc.s_id
GROUP BY 
    s.s_id, s.s_name
HAVING 
    AVG(sc.s_score) >= 60;
    
记住：当使用聚合函数（如SUM、COUNT等）时，非聚合列必须出现在GROUP BY子句中或通过某种方式被聚合。所以GROUP BY后面还要加个s_name

方法2：子查询
SELECT 
    s.s_id, 
    s.s_name, 
    (SELECT AVG(s_score) FROM score WHERE s_id = s.s_id) AS avg_score
FROM 
    student s
WHERE 
    (SELECT AVG(s_score) FROM score WHERE s_id = s.s_id) >= 60;

方法三：CTE
WITH student_avg AS (
    SELECT 
        s_id, 
        AVG(s_score) AS avg_score
    FROM 
        score
    GROUP BY 
        s_id
)
SELECT 
    s.s_id, 
    s.s_name, 
    sa.avg_score
FROM 
    student s
JOIN student_avg sa ON s.s_id = sa.s_id
WHERE 
    sa.avg_score >= 60;

方法一（GROUP BY 和 HAVING） 是最推荐的：

执行效率最高，直接在分组阶段过滤数据
结构简单，符合 SQL 标准
索引优化友好（建议在 score 表上创建复合索引 (s_id, s_score)）

子查询（方法2）

优点：语法简洁，直观表达查询意图
缺点：对每个学生执行一次子查询，大数据量下性能较差

CTE（方法3）

优点：逻辑清晰，先计算平均分再关联学生表
缺点：可能产生临时表，对复杂查询更有优势

3.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

方法1：使用 LEFT JOIN + COALESCE 处理 NULL
SELECT 
    s.s_id, 
    s.s_name, 
    COALESCE(AVG(sc.s_score), 0) AS avg_score
FROM 
    student s
LEFT JOIN score sc ON s.s_id = sc.s_id
GROUP BY 
    s.s_id, s.s_name
HAVING 
    COALESCE(AVG(sc.s_score), 0) < 60;

方法二：使用 CASE WHEN 自定义 NULL 处理
SELECT 
    s.s_id, 
    s.s_name, 
    AVG(CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END) AS avg_score
FROM 
    student s
LEFT JOIN score sc ON s.s_id = sc.s_id
GROUP BY 
    s.s_id, s.s_name
HAVING 
    AVG(CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END) < 60;

为什么用 LEFT JOIN？普通 JOIN 会过滤掉无成绩的学生，而 LEFT JOIN 确保所有学生被保留，无成绩的学生对应分数为 NULL。
NULL 值处理COALESCE(AVG(sc.s_score), 0)：将 NULL 转换为 0（无成绩的学生视为平均分 0）。AVG(CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END)：手动替换 NULL 为 0 后计算平均分。

4.查询学过"张三"老师授课的同学的信息

方法1：多层join
SELECT DISTINCT 
    s.*
FROM 
    student s
JOIN score sc ON s.s_id = sc.s_id
JOIN course c ON sc.c_id = c.c_id
JOIN teacher t ON c.t_id = t.t_id
WHERE 
    t.t_name = '张三';

方法2：EXISTS
SELECT * 
FROM 
    student s
WHERE 
    EXISTS (
        SELECT *
        FROM score sc
        JOIN course c ON sc.c_id = c.c_id
        JOIN teacher t ON c.t_id = t.t_id
        WHERE 
            s.s_id = sc.s_id 
            AND t.t_name = '张三'
    );

方法一（多层 JOIN） 是最简洁直观的：

直接通过表关联获取结果，执行效率较高。
索引优化建议：在 teacher(t_name) 上创建索引，加速教师名称查询。在 course(t_id) 和 score(c_id) 上创建复合索引，加速连接。

若数据量较大且可能存在大量不匹配记录，方法二（EXISTS） 性能更优，因为它提前终止不必要的扫描。

5.查询没学过"张三"老师授课的同学的信息

SELECT * 
FROM 
    student s
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM score sc
        JOIN course c ON sc.c_id = c.c_id
        JOIN teacher t ON c.t_id = t.t_id
        WHERE 
            s.s_id = sc.s_id 
            AND t.t_name = '张三'
    );

6.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

方法1：自连接
SELECT s.*
FROM 
    student s
JOIN score sc1 ON s.s_id = sc1.s_id AND sc1.c_id = '01'
JOIN score sc2

剩余60%内容，订阅专栏后可继续查看/也可单篇购买

八股大全、算法、项目话术全专栏文章被收录于专栏

内容包含： 1.八股大全：多一句没有少一句不行的最精简八股整理，完全可以应付校招八股拷打！ 2.速成项目话术：目前有魔改苍穹外卖项目话术（额外扩展了很多技术亮点），能速成拿去面试，后面会更新魔改黑马点评、商城项目等等热门高质量项目话术 3.智力题超详细题解汇总； 4.面试时非技术问题话术整理，绝对震惊面试官一年； 5.算法lc hot100全题系列题解：绝对通俗易懂。会慢慢涨价，欢迎订阅！