07-07 13:09 中南大学 Java 发布于湖北

关注

史上最细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全题系列题解：绝对通俗易懂。会慢慢涨价，欢迎订阅！

全部评论

推荐最新楼层

Java抽象带篮子

楼主

中南大学 Java

天不生我带篮子，牛客万古如长夜！

3 回复分享

发布于 07-08 08:37 湖北

Java抽象带篮子

楼主

中南大学 Java

求🌸😍

1 回复分享

发布于 07-07 13:20 湖北

爱吃烤肠的茶叶蛋在干饭

门头沟学院 Java

求

点赞回复分享

发布于 07-08 23:09 四川

Fyindins

天津仁爱学院客户端其它

mark收藏

点赞回复分享

发布于 07-08 07:01 江西

不愿透露姓名的神秘牛友

07-08 14:14

恋爱四年没想到过自己会出轨

主包是男生，最近和女友之间发生了很多事情，和女朋友谈了4年，之前都是我包容他，无论是大事小事都是我道歉我来包容她，导致她变成了现在这样，非常的优越，吵架从来不会低头，不会承认错误，我一直在北京实习+工作，压力很大，今年上半年她没在北京，我就通过运动认识了一些其他朋友，长大半年的时间我都是自己在这边工作，她在老家没有工作，之前三年的感情我都是包容她，我慢慢的没有了自我，今年开始运动，我慢慢的想起来了自己没谈恋爱之前，我是多么阳光爱运动的男生，朋友很多！但是现在我变成了什么？于是这半年我每天都会出去和这些运动搭子一起出去玩，女朋友总是会因为小事和我吵架，但她从不安慰我，也不会道歉，都是我主动去哄她，5月她来了北京，她说我变了不围着她转了，我开始慢慢的更在乎自己的感受，因为感情的事情我不会在主动低头，我们爆发过几次大规模的争吵，这个时候我认识了另外一个比我年纪大几岁的姐姐，她出现在了我的生活中。有一次和我的女朋友开玩笑，她突然很生气，开始用各种东西扔我，拖鞋、手机、甚至水瓶子，这些东西激起了我不好的回忆，（小时候爸妈吵架就会经常摔东西，给小小的我留下了很多的阴影）当时我脑子里这些回忆突然开始闪动，我非常的害怕，同时也很生气，我就沉默的做到了沙发上，女朋友这时候见我没有去哄她，她拿上了钥匙就离开了，我在原地呆了很久，这时候比我年纪大的姐姐约我出去，我答应了，见面后她看我情绪不对，她安慰我，我情绪一下就绷不住了，我哭的很伤心，我心里开始慢慢的依赖她，晚上我女朋友找我谈话问我为什么没去哄她。我说了我的原因，还有这不是我的错，四年的恋爱，她第一次对我说出了对不起，我不知道她是觉得自己做错了，还是觉得我这次会真的分手，但这个时候我心里想的是那个姐姐，我也清楚那个姐姐给我展现出的都是她想让我看到的。人初见都是最好的。我和那个姐姐接触了大概两周了，我感觉和她在一起很舒服，哪怕是不见面，就仅仅是聊天，但我又很对不起我女朋友，我知道这是不对的，我也想过断了和姐姐的联系，但是我做不到，我很需要被爱。整件事情中，错误在我，我不该包容一个女孩儿三年后选择放弃，我也不该对一个刚刚认识一周的姐姐动心。大家当个乐子看就好了，我没有人可以分享这件事，又不能发在其他地方，发在牛客，大家可以谴责我，我都会看的。以至于为什么会说第三者是我的第一课，是因为我学到了我也能够被爱。

牛客刘北：挺难评价的，你肯定是错的，但是你没在女友哪里得到过爱，这也挺难评的，谈了4年，出了问题就该好好交流让双方意识到问题错了，你也不能包容她一辈子，有一天爆发了，这样的一个姐姐出现了，她就会直接走进你的心里，你想既要又要，所以出了问题会第一时间想着跟“姐姐”诉苦，想跟她呆在一起，她会对你好，你自己也清楚她更多的是伪装，没猜错的话你女朋友应该是发现你这种出轨行为了，但是你的感情到底在哪儿，只有你自己知道，原则性问题发生了，就是不对的。趁着还不晚，还能和解，好好珍惜自己的恋人

社会教会你的第一课

点赞评论收藏