【MySQL】---经典测试题一、考试题带答案

清疚 2024-04-01 16:59 70阅读 0赞

> ### 分享第十七条励志语句 ###
> 
> #### 总把一些事情想得过于严重，就像第一次上台演讲，第一次考试失败，第一次失恋。回头看看，那些自己走过的路，遇到的人，摊上的事，我们总能坚强的走过来。 ####

做数据库的测试题目，首先就是要创建数据库，然后添加数据，进行增删改查

![891f0675dbb74ec997e7674e3dc02a2a.png][]

\#创建数据库exam  
CREATE DATABASE IF NOT EXISTS \`exam\`;

\#使用数据库exam  
USE exam;

\#创建学生表Student  
CREATE TABLE IF NOT EXISTS \`Student\`(  
\`s\_id\` VARCHAR(20),  
\`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\`)  
);

\#创建课程表Course  
CREATE TABLE IF NOT EXISTS \`Course\`(  
\`c\_id\` VARCHAR(20),  
\`c\_name\` VARCHAR(20) NOT NULL DEFAULT '',  
\`t\_id\` VARCHAR(20) NOT NULL,  
PRIMARY KEY(\`c\_id\`)  
);

\#创建教师表Teacher  
CREATE TABLE IF NOT EXISTS \`Teacher\`(  
\`t\_id\` VARCHAR(20),  
\`t\_name\` VARCHAR(20) NOT NULL DEFAULT '',  
PRIMARY KEY(\`t\_id\`)  
);

\#创建成绩表Score  
CREATE TABLE IF NOT EXISTS \`Score\`(  
\`s\_id\` VARCHAR(20),  
\`c\_id\` VARCHAR(20),  
\`s\_score\` INT(3),  
PRIMARY KEY(\`s\_id\`,\`c\_id\`)  
);  
\#--------------------------------------------------------------------------------

#创建数据库exam
    CREATE DATABASE IF NOT EXISTS `exam`;
    
    #使用数据库exam
    USE exam;
    
    #创建学生表Student
    CREATE TABLE IF NOT EXISTS `Student`(
    `s_id` VARCHAR(20),
    `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`)
    );
    
    #创建课程表Course
    CREATE TABLE IF NOT EXISTS `Course`(
    `c_id` VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`c_id`)
    );
    
    #创建教师表Teacher
    CREATE TABLE IF NOT EXISTS `Teacher`(
    `t_id` VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(`t_id`)
    );
    
    #创建成绩表Score
    CREATE TABLE IF NOT EXISTS `Score`(
    `s_id` VARCHAR(20),
    `c_id` VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
    );
    #--------------------------------------------------------------------------------

添加数据

\#向Student表中插入数据  
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','女');

\#向Course表中插入数据  
INSERT INTO Course VALUES('01' , '语文' , '02');  
INSERT INTO Course VALUES('02' , '数学' , '01');  
INSERT INTO Course VALUES('03' , '英语' , '03');

\#向Teacher表中插入数据  
INSERT INTO Teacher VALUES('01' , '张三');  
INSERT INTO Teacher VALUES('02' , '李四');  
INSERT INTO Teacher VALUES('03' , '王五');

\#向Score表中插入数据  
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);  
\#--------------------------------------------------------------------------------

#向Student表中插入数据
    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','女');
    
    #向Course表中插入数据
    INSERT INTO Course VALUES('01' , '语文' , '02'); 
    INSERT INTO Course VALUES('02' , '数学' , '01'); 
    INSERT INTO Course VALUES('03' , '英语' , '03');
    
    #向Teacher表中插入数据
    INSERT INTO Teacher VALUES('01' , '张三'); 
    INSERT INTO Teacher VALUES('02' , '李四'); 
    INSERT INTO Teacher VALUES('03' , '王五');
    
    #向Score表中插入数据
    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);
    #--------------------------------------------------------------------------------

进行查询，这次的测试题目比较难，需要深刻理解

\#创建数据库exam  
CREATE DATABASE IF NOT EXISTS \`exam\`;

\#使用数据库exam  
USE exam;

\#创建教师表Teacher  
CREATE TABLE IF NOT EXISTS \`Teacher\`(  
\`t\_id\` VARCHAR(20),  
\`t\_name\` VARCHAR(20) NOT NULL DEFAULT '',  
PRIMARY KEY(\`t\_id\`)  
);

\#向Course表中插入数据  
INSERT INTO Course VALUES('01' , '语文' , '02');  
INSERT INTO Course VALUES('02' , '数学' , '01');  
INSERT INTO Course VALUES('03' , '英语' , '03');

\#向Teacher表中插入数据  
INSERT INTO Teacher VALUES('01' , '张三');  
INSERT INTO Teacher VALUES('02' , '李四');  
INSERT INTO Teacher VALUES('03' , '王五');

\#01 查询“01”课程比“02”课程成绩高的学生的信息及课程分数  
\#本质 行运算：比较运算  
\#方法 1、直接比较：子查询 2、弄成两列：自关联（注意显式连接，提升查询速度）  
\#备注 任一科成绩缺失不予比较

\# 1、直接比较：子查询  
SELECT s.\*,sc.s\_score  
FROM (  
SELECT sc1.s\_id,sc1.s\_score  
FROM score sc1  
WHERE sc1.c\_id='01'  
AND sc1.s\_score>(SELECT s\_score FROM score WHERE s\_id=sc1.s\_id AND c\_id='02')) sc JOIN student s ON sc.s\_id=s.s\_id

\# 2.弄成两列：自关联（注意显式连接，提升查询速度）  
SELECT s.\*,sc3.s\_score  
FROM  
(  
SELECT sc1.s\_id,sc1.s\_score  
FROM  
( SELECT s\_id, s\_score FROM score WHERE c\_id = '01' ) sc1  
JOIN ( SELECT s\_id, s\_score FROM score WHERE c\_id = '02' ) sc2 ON sc1.s\_id = sc2.s\_id AND sc1.s\_score > sc2.s\_score  
) sc3  
JOIN student s ON sc3.s\_id = s.s\_id

\# 2.2 直接自关联  
SELECT s.\*,sc1.s\_score  
FROM student s  
JOIN score sc1 ON s.s\_id=sc1.s\_id AND sc1.c\_id='01'  
JOIN score sc2 ON s.s\_id=sc2.s\_id AND sc2.c\_id='02' AND sc1.s\_score>sc2.s\_score

\#02 查询“01”课程比“02”课程成绩低的学生的信息及课程分数(题目 1 是成绩高)  
\#本质 行运算：比较运算  
\#方法 1、直接比较：子查询 2、弄成两列：自关联（注意显式连接，提升查询速度）  
\#备注 任一科成绩缺失不予比较

\# 1、直接比较：子查询  
SELECT s.\*,sc.s\_score  
FROM (  
SELECT sc1.s\_id,sc1.s\_score  
FROM score sc1  
WHERE sc1.c\_id='01'  
AND sc1.s\_score<(SELECT s\_score FROM score WHERE s\_id=sc1.s\_id AND c\_id='02')) sc JOIN student s ON sc.s\_id=s.s\_id

\# 2.弄成两列：自关联（注意显式连接，提升查询速度）  
SELECT s.\*,sc3.s\_score  
FROM  
(  
SELECT sc1.s\_id,sc1.s\_score  
FROM  
( SELECT s\_id, s\_score FROM score WHERE c\_id = '01' ) sc1  
JOIN ( SELECT s\_id, s\_score FROM score WHERE c\_id = '02' ) sc2 ON sc1.s\_id = sc2.s\_id AND sc1.s\_score < sc2.s\_score  
) sc3  
JOIN student s ON sc3.s\_id = s.s\_id

\#03 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩  
\#本质 行聚合，后having筛选  
\#方法 直接聚合，后having对聚合函数进行筛选  
SELECT sc.s\_id,s.s\_name,ROUND(AVG(sc.s\_score),0) AS score  
FROM score sc JOIN student s ON sc.s\_id=s.s\_id  
GROUP BY sc.s\_id  
HAVING score>=60

\# 附加题:总分超过200分的同学  
SELECT sc.s\_id,s.s\_name,SUM(sc.s\_score) AS score  
FROM score sc JOIN student s ON sc.s\_id=s.s\_id  
GROUP BY sc.s\_id  
HAVING score>200

\#04 查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)  
\#本质 行聚合，后having筛选  
\#方法 直接聚合，聚合后用having筛选，注意主表  
\#备注 1、主表是学生表，没有成绩的需要包含在内，故left join 2、having可看作聚合函数的where：为空判断，比较运算，子查询 3、用ifnull处理空/left join情况  
\# null 判断  
SELECT s.s\_id,s.s\_name,ROUND(AVG(sc.s\_score),0) AS score  
FROM student s  
LEFT JOIN score sc ON s.s\_id=sc.s\_id  
GROUP BY s.s\_id  
HAVING score IS NULL OR score < 60

\# ifnull 函数：更兼容  
SELECT s.s\_id,s.s\_name,ROUND(AVG(IFNULL(sc.s\_score,0)),0) AS score  
FROM student s  
LEFT JOIN score sc ON s.s\_id=sc.s\_id  
GROUP BY s.s\_id  
HAVING score < 60

\#05 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩  
\#本质 行聚合  
\#方法 直接聚合，注意对象范围  
\#备注 主表是学生表，需考虑没有选课or没有成绩的情况  
\#本题sql  
SELECT s.s\_id,s.s\_name,  
COUNT(DISTINCT sc.c\_id) AS c\_num,  
SUM(sc.s\_score) AS score  
FROM student s LEFT JOIN score sc ON s.s\_id=sc.s\_id  
GROUP BY s.s\_id

\#如果需要聚合后筛选，则使用if和ifnull函数  
\#if和ifnull是好函数，处理null情况非常方便。注：ifnull搭配值运算函数使用，if函数搭配count函数  
SELECT s.s\_id,s.s\_name,  
IF(sc.s\_id IS NULL,0,COUNT(DISTINCT sc.c\_id)) AS c\_num,  
SUM(IFNULL(sc.s\_score,0)) AS score  
FROM student s LEFT JOIN score sc ON s.s\_id=sc.s\_id  
GROUP BY s.s\_id

\#06 查询“李”姓老师的数量  
\#本质 行筛选聚合  
\#方法 筛选计数  
\#备注  
\#问题：like是最优的吗  
\#like通配符  
SELECT COUNT(DISTINCT t\_id) AS t\_num  
FROM Teacher  
WHERE t\_name LIKE '李%'

\#截取姓氏，然后判断  
SELECT COUNT(DISTINCT t\_id) AS t\_num  
FROM Teacher  
WHERE SUBSTR(t\_name,1,1)='李'

\#07 查询学过张三老师授课的同学的信息  
\#本质 行筛选：逐级  
\#方法 表关联or子查询  
\#备注  
\# 逐级子查询，这样更好理解  
SELECT s.\*  
FROM Score sc  
JOIN Student s ON sc.s\_id=s.s\_id  
WHERE c\_id IN(SELECT c\_id FROM Course WHERE t\_id IN(SELECT t\_id FROM Teacher WHERE t\_name='张三'))

\# 表关联  
SELECT s.\*  
FROM score sc  
JOIN course c ON c.c\_id=sc.c\_id  
JOIN teacher t ON t.t\_id=c.t\_id AND t.t\_name='张三'  
JOIN student s ON sc.s\_id=s.s\_id

\#08 找出没有学过张三老师课程的学生  
\#本质 反向行筛选  
\#方法 按照学过筛选，最后取相反情况left join 或right join，且is null  
\#备注  
\# 表关联  
SELECT s.\*  
FROM score sc  
JOIN course c ON c.c\_id=sc.c\_id  
JOIN teacher t ON t.t\_id=c.t\_id AND t.t\_name='张三'  
RIGHT JOIN student s ON sc.s\_id=s.s\_id  
WHERE sc.s\_id IS NULL

\# not in，这个好理解  
SELECT \*  
FROM student  
WHERE s\_id NOT IN(  
SELECT sc.s\_id  
FROM score sc  
JOIN course c ON c.c\_id=sc.c\_id  
JOIN teacher t ON t.t\_id=c.t\_id AND t.t\_name='张三'  
)

\#09 查询学过编号为 01，并且学过编号为 02 课程的学生信息  
\#本质 行筛选：取交集  
\#方法 自关联 or 子查询  
\#备注  
\# 自关联  
SELECT s.\*  
FROM Score sc1  
JOIN Score sc2 ON sc1.s\_id=sc2.s\_id AND sc1.c\_id='01' AND sc2.c\_id='02'  
JOIN Student s ON sc1.s\_id=s.s\_id

\# 子查询  
SELECT s.\*  
FROM Score sc1  
JOIN Student s ON sc1.s\_id=s.s\_id  
WHERE sc1.c\_id='01'  
AND sc1.s\_id IN(SELECT s\_id FROM Score sc2 WHERE sc1.s\_id=sc2.s\_id AND sc2.c\_id='02')

\#10 查询学过 01 课程，但是没有学过 02 课程的学生信息(注意和上面9题目的区别)  
\#本质 行筛选：取交集  
\#方法 子查询，不能使用自关联，因为一条cid一条记录，不能用否判断  
\#备注  
\# 子查询  
SELECT s.\*  
FROM Score sc1  
JOIN Student s ON sc1.s\_id=s.s\_id  
WHERE sc1.c\_id='01'  
AND sc1.s\_id NOT IN(SELECT s\_id FROM Score sc2 WHERE sc1.s\_id=sc2.s\_id AND sc2.c\_id='02')

\#11 查询没有学完全部课程的同学的信息  
\#本质 行聚合，后having筛选  
\#方法 直接聚合，注意表对象范围  
\#备注 having可看作聚合函数的where：为空判断，比较运算，子查询  
SELECT s.\*  
FROM student s  
LEFT JOIN score sc ON sc.s\_id=s.s\_id  
GROUP BY s.s\_id  
HAVING COUNT(DISTINCT sc.c\_id)<(SELECT COUNT(\*) FROM course)

\#发现筛选，先筛选出学完全部课程的同学  
SELECT \*  
FROM Student  
WHERE s\_id NOT IN(SELECT s\_id FROM Score GROUP BY s\_id HAVING COUNT(c\_id)=(SELECT COUNT(\*) FROM Course))

\#12 查询至少有一门课与学号为 01 的同学所学相同的同学的信息  
\#本质 行筛选：子查询  
\#方法 1、学号01所学课程c\_id01 2、学号不等于01且c\_id in c\_id01  
\#备注  
SELECT s.\*  
FROM score sc  
JOIN student s ON sc.s\_id=s.s\_id AND sc.s\_id<>'01'  
WHERE sc.c\_id IN(SELECT c\_id FROM score WHERE s\_id='01')  
GROUP BY s.s\_id

\#13 查询和 01 同学学习的课程完全相同的同学的信息  
\#本质 行筛选：子查询  
\#方法 1、学号01所学课程 2、和01所学相同课程且课程数等于01课程数  
\#备注 关键是思考方法  
\#group\_concat()函数用法  
\#group\_concat(\[DISTINCT\] 字 段 \[Order BY ASC/DESC 排序字段\] \[Separator '分隔符'\])  
\# 方法1 课程相同的数量相同  
SELECT s.\*  
FROM score sc  
JOIN student s ON sc.s\_id=s.s\_id  
\#课程数量相等  
WHERE sc.s\_id IN(SELECT s\_id FROM Score WHERE s\_id <>'01' GROUP BY s\_id HAVING COUNT(DISTINCT c\_id)=(SELECT COUNT(DISTINCT c\_id) FROM score WHERE s\_id='01'))  
\#课程相同的数量相同  
AND c\_id IN(SELECT c\_id FROM score WHERE s\_id='01')  
GROUP BY s.s\_id  
HAVING COUNT(DISTINCT sc.c\_id)=(SELECT COUNT(DISTINCT c\_id) FROM score WHERE s\_id='01')

\# 方法2 使用group\_concat()函数，如果记录数比较大，建议采用方法1  
SELECT s.\*  
FROM student s  
JOIN score sc ON s.s\_id=sc.s\_id AND sc.s\_id<>'01'  
GROUP BY s.s\_id  
HAVING GROUP\_CONCAT(sc.c\_id ORDER BY sc.c\_id)=  
(SELECT GROUP\_CONCAT(c\_id ORDER BY c\_id) FROM score WHERE s\_id='01' GROUP BY s\_id)

\#14 查询没有修过张三老师讲授的任何一门课程的学生姓名  
\#本质 反向行筛选  
\#方法 修过张三老师任一门课即不符合要求  
\#备注 注意表对象是全体学生，主表是学生表，因对s\_id筛选，故不用再和score关联  
SELECT s\_name  
FROM student  
WHERE s\_id NOT IN(  
SELECT s\_id FROM score  
WHERE c\_id IN(SELECT c\_id FROM course WHERE t\_id=(SELECT t\_id FROM teacher WHERE t\_name='张三')))

\#15 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩  
\#本质 行筛选并聚合  
\#方法 1、行筛选：及格与否 2、求不及格课程数 3、having筛选  
\#备注 修过才会有及格与否之说，故score为主表  
\# 方式1  
SELECT sc.s\_id,s.s\_name,ROUND(AVG(sc.s\_score),0) AS avg\_score  
FROM score sc  
JOIN student s ON sc.s\_id=s.s\_id  
GROUP BY sc.s\_id  
HAVING SUM(IF(sc.s\_score<60,1,0))>=2

\# 方式2 查询表更小  
SELECT sc.s\_id,s.s\_name,ROUND(AVG(sc.s\_score),0) AS avg\_score  
FROM score sc  
JOIN student s ON sc.s\_id=s.s\_id AND sc.s\_score<60  
GROUP BY sc.s\_id  
HAVING COUNT(\*)>=2

\#16 检索 01 课程分数小于 60，按分数降序排列的学生信息  
\#本质 行筛选并排序  
\#方法 分数<60、分数降序  
\#备注 排序函数区别 1、row\_number()over：123 2、rank()over()：113 3、dense\_rank()over()：112  
SELECT s.\*  
FROM Score sc  
JOIN Student s ON sc.s\_id=s.s\_id  
WHERE c\_id='01'  
AND s\_score<60  
ORDER BY s\_score DESC

\#17 按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩  
\#本质 表关联并排序  
\#方法 找到主表，找到排序字段  
\#备注 如何将排序字段和其他字段关联是关键点,这里的平均成绩是所选课程的平均成绩  
\# 纵表  
SELECT s.s\_name,c.c\_name,sc.s\_score,sc2.avg\_score  
FROM Student s  
JOIN Course c  
LEFT JOIN Score sc ON s.s\_id=sc.s\_id AND c.c\_id=sc.c\_id  
LEFT JOIN (SELECT s\_id,ROUND(AVG(s\_score),0) AS avg\_score FROM Score GROUP BY s\_id) sc2 ON s.s\_id=sc2.s\_id  
ORDER BY sc2.avg\_score DESC

\#横表  
SELECT s.s\_name,  
MAX(CASE sc.c\_id WHEN '01' THEN sc.s\_score END) AS '语文',  
MAX(CASE sc.c\_id WHEN '02' THEN sc.s\_score END) AS '数学',  
MAX(CASE sc.c\_id WHEN '03' THEN sc.s\_score END) AS '英语',  
sc2.avg\_score AS '平均成绩'  
FROM Student s  
JOIN Course c  
LEFT JOIN Score sc ON s.s\_id=sc.s\_id AND c.c\_id=sc.c\_id  
LEFT JOIN (SELECT s\_id,ROUND(AVG(s\_score),0) AS avg\_score FROM Score GROUP BY s\_id) sc2 ON s.s\_id=sc2.s\_id  
GROUP BY s.s\_name,sc2.avg\_score  
ORDER BY sc2.avg\_score DESC

\#18 查询各科成绩最高分、最低分和平均分:以如下形式显示:课程 ID，课程 name，最高分，最低分，平均分，及格 率，中等率，优良率，优秀率;及格:>=60，中等为:70-80，优良为:80-90，优秀为:>=90  
\#本质 表关联并聚合  
\#方法 主表course，left join 成绩表score，求最值、均值及分组  
\#备注 注意小数位数及百分率符号  
SELECT c.c\_id AS '课程id',c.c\_name AS '课程name',  
MAX(sc.s\_score) AS '最高分',  
MIN(sc.s\_score) AS '最低分',  
ROUND(AVG(sc.s\_score),2) AS '平均分',  
CONCAT(ROUND(SUM(IF(sc.s\_score>=60,1,0))/COUNT(sc.s\_id)\*100,2),'%') AS '及格率',  
CONCAT(ROUND(SUM(IF(sc.s\_score>=70 AND sc.s\_score<80,1,0))/COUNT(sc.s\_id)\*100,2),'%') AS '中等率',  
CONCAT(ROUND(SUM(IF(sc.s\_score>=80 AND sc.s\_score<90,1,0))/COUNT(sc.s\_id)\*100,2),'%') AS '优良率',  
CONCAT(ROUND(SUM(IF(sc.s\_score>=90,1,0))/COUNT(sc.s\_id)\*100,2),'%') AS '优秀率'  
FROM Course c  
LEFT JOIN Score sc ON c.c\_id=sc.c\_id  
GROUP BY c.c\_id

\#19 按照各科成绩进行排序，并且显示排名  
\#本质 分组排序  
\#方法 选择排序依据并排名  
\#备注  
SELECT c.c\_name,s.s\_name,sc.s\_score,  
ROW\_NUMBER() OVER(PARTITION BY c.c\_name ORDER BY sc.s\_score DESC) AS '排名'  
FROM Score sc  
JOIN Student s ON sc.s\_id=s.s\_id  
JOIN Course c ON sc.c\_id=c.c\_id

\#20 查询学生的总成绩，并进行排名  
\#本质 行聚合后排序  
\#方法 sum、排序  
\#备注  
SELECT s.s\_name,SUM(sc.s\_score) AS '总成绩',  
ROW\_NUMBER()OVER(ORDER BY SUM(sc.s\_score) DESC) AS '排名'  
FROM Student s  
LEFT JOIN Score sc ON s.s\_id=sc.s\_id  
GROUP BY s.s\_name

\#21 查询不同老师所教不同课程平均分从高到低显示  
\#本质 表关联后聚合再排序  
\#方法 主表是教师表teacher和课程表course笛卡尔积，left join 成绩表score，求均值后order by  
\#备注  
SELECT t.t\_name,c.c\_name,ROUND(AVG(sc.s\_score),2) AS '平均分'  
FROM Teacher t  
JOIN Course c ON t.t\_id=c.t\_id  
LEFT JOIN Score sc ON c.c\_id=sc.c\_id  
GROUP BY t.t\_name,c.c\_name  
ORDER BY AVG(sc.s\_score) DESC

\#22 查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩  
\#本质 行排序后筛选  
\#方法 所有课程成绩排名，再取2和3名  
\#备注  
SELECT r.c\_name,r.rank\_num,s.s\_name,r.s\_score  
FROM  
(SELECT c.c\_name,sc.s\_id,sc.s\_score,  
ROW\_NUMBER()OVER(PARTITION BY c.c\_name ORDER BY sc.s\_score DESC) AS rank\_num  
FROM Course c  
LEFT JOIN Score sc ON c.c\_id=sc.c\_id)r  
JOIN Student s ON r.s\_id=s.s\_id AND r.rank\_num IN(2,3)

\#23 统计各科成绩各分数段人数:课程编号，课程名称，\[100-85\]，\[85-70\]，\[70-60\]，\[0-60\] 及所占百分比  
\#本质 行分组聚合  
\#方法 先列基本：科目、成绩，后分组计数  
\#备注  
SELECT sc.c\_id,c.c\_name,  
SUM(IF(sc.s\_score>=85,1,0)) AS '\[100-85\]人数',  
SUM(IF(sc.s\_score>=70 AND sc.s\_score<85,1,0)) AS '\[85-70\]人数',  
SUM(IF(sc.s\_score>=60 AND sc.s\_score<70,1,0)) AS '\[70-60\]人数',  
SUM(IF(sc.s\_score<60,1,0)) AS '\[0-60\]人数',  
CONCAT(ROUND(SUM(IF(sc.s\_score>=85,1,0))/COUNT(\*)\*100,2),'%') AS '\[100-85\]百分比',  
CONCAT(ROUND(SUM(IF(sc.s\_score>=70 AND sc.s\_score<85,1,0))/COUNT(\*)\*100,2),'%') AS '\[85-70\]百分比',  
CONCAT(ROUND(SUM(IF(sc.s\_score>=60 AND sc.s\_score<70,1,0))/COUNT(\*)\*100,2),'%') AS '\[70-60\]百分比',  
CONCAT(ROUND(SUM(IF(sc.s\_score<60,1,0))/COUNT(\*)\*100,2),'%') AS '\[0-60\]百分比'  
FROM Score sc  
JOIN Course c ON sc.c\_id=c.c\_id  
GROUP BY sc.c\_id

\#24 查询学生的平均成绩及名次  
\#本质 行聚合后排序  
\#方法 求平均成绩，后排序  
\#备注  
SELECT s.s\_name,ROUND(AVG(sc.s\_score),2) AS '总成绩',  
ROW\_NUMBER()OVER(ORDER BY AVG(sc.s\_score) DESC) AS '排名'  
FROM Student s  
LEFT JOIN Score sc ON s.s\_id=sc.s\_id  
GROUP BY s.s\_name

\#25 查询各科成绩前三名的记录  
\#本质 行排序后筛选  
\#方法 各科成绩排序，取前3  
\#备注  
SELECT r.c\_name,r.rank\_num,s.s\_name,r.s\_score  
FROM  
(SELECT c.c\_name,sc.s\_id,sc.s\_score,  
ROW\_NUMBER()OVER(PARTITION BY c.c\_name ORDER BY sc.s\_score DESC) AS rank\_num  
FROM Course c  
LEFT JOIN Score sc ON c.c\_id=sc.c\_id)r  
JOIN Student s ON r.s\_id=s.s\_id AND r.rank\_num<=3

\#26 查询每门课被选修的学生数  
\#本质 行聚合  
\#方法 成绩表按科目对s\_id计数  
\#备注  
SELECT c.c\_name,COUNT(DISTINCT sc.s\_id) AS s\_num  
FROM Score sc  
JOIN Course c ON sc.c\_id=c.c\_id  
GROUP BY c.c\_name

\#27 查询出只有两门课程的全部学生的学号和姓名  
\#本质 行聚合  
\#方法 成绩表按学生对c\_id计数  
\#备注  
SELECT s.s\_id,s.s\_name  
FROM Score sc  
JOIN Student s ON sc.s\_id=s.s\_id  
GROUP BY s.s\_id  
HAVING COUNT(DISTINCT sc.c\_id)=2

\#28 查询男女生人数  
\#本质 行筛选聚合  
\#方法 学生表student按性别分组计数  
\#备注  
SELECT s\_sex,COUNT(DISTINCT s\_id) AS '人数'  
FROM Student  
GROUP BY s\_sex

\#29 查询名字中含有 风 字的学生信息  
\#本质 模糊筛选  
\#方法  
\#备注  
SELECT \*  
FROM Student  
WHERE s\_name LIKE '%风%'

\#30 查询同名同性的学生名单，并统计同名人数  
\#本质 分组聚合  
\#方法 分组计数后having筛选  
\#备注  
SELECT s1.s\_name,s1.s\_sex,s2.num AS '同名人数'  
FROM Student s1  
JOIN (SELECT s\_name,COUNT(\*) AS num FROM Student GROUP BY s\_name HAVING COUNT(\*)>=2)s2 ON s1.s\_name=s2.s\_name  
GROUP BY s1.s\_name,s1.s\_sex  
HAVING COUNT(\*)>=2

\#31 查询 1990 年出生的学生信息  
\#本质 行筛选  
\#方法 获取出生年份，并筛选  
\#备注 DATE()可以将varchar转成日期型 YEAR()函数返回一个指定日期or时间的年份值，范围为1000到9999，如果日期为零，YEAR()函数返回0  
SELECT \*  
FROM Student  
WHERE YEAR(DATE(s\_birth))=1990

\#32 查询每门课程的平均成绩，结果按平均成绩降序排列;平均成绩相同时，按课程编号 c\_id 升序排列  
\#本质 行聚合后排序  
\#方法 对课程求均值，后排序  
\#备注 order by后可跟聚合函数  
SELECT c.c\_name,ROUND(AVG(sc.s\_score),2) AS '平均分'  
FROM Score sc  
JOIN Course c ON sc.c\_id=c.c\_id  
GROUP BY c.c\_id  
ORDER BY AVG(sc.s\_score) DESC,c.c\_id ASC

\#33 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩  
\#本质 行聚合后筛选  
\#方法 对学生求成绩均值，后筛选  
\#备注  
SELECT s.s\_id,s.s\_name,ROUND(AVG(sc.s\_score),2) AS '平均成绩'  
FROM Score sc  
JOIN Student s ON sc.s\_id=s.s\_id  
GROUP BY s.s\_id  
HAVING AVG(sc.s\_score)>=85

\#34 查询课程名称为数学，且分数低于 60 的学生姓名和分数  
\#本质 行筛选  
\#方法 按要求筛选，用到子查询  
\#备注  
SELECT s.s\_name,sc.s\_score  
FROM Score sc  
JOIN Student s ON sc.s\_id=s.s\_id  
WHERE c\_id=(SELECT c\_id FROM Course WHERE c\_name='数学')  
AND s\_score<60

\#35 查询所有学生的课程及分数情况  
\#本质 表关联后聚合  
\#方法 学生表与课程表笛卡尔积，获取所有学生及所有课程，然后关联成绩表获取成绩，最后聚合 注：成绩表为窄表，故外层需要聚合  
\#备注  
SELECT s.s\_name,  
SUM(CASE c.c\_name WHEN '语文' THEN sc.s\_score ELSE 0 END) AS '语文',  
SUM(CASE c.c\_name WHEN '数学' THEN sc.s\_score ELSE 0 END) AS '数学',  
SUM(CASE c.c\_name WHEN '英语' THEN sc.s\_score ELSE 0 END) AS '英语',  
SUM(sc.s\_score) AS '总分'  
FROM Student s  
JOIN Course c  
LEFT JOIN Score sc ON s.s\_id=sc.s\_id AND c.c\_id=sc.c\_id  
GROUP BY s.s\_name

\#36 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数  
\#本质 行筛选  
\#方法 成绩表筛选>70  
\#备注  
SELECT s.s\_name,c.c\_name,sc.s\_score  
FROM Score sc  
JOIN Student s ON sc.s\_id=s.s\_id  
JOIN Course c ON sc.s\_id=c.c\_id  
WHERE s\_score>70

\#37 查询不及格的课程  
\#本质 行筛选  
\#方法  
\#备注  
SELECT sc.c\_id,c.c\_name,sc.s\_score  
FROM Score sc  
JOIN Course c ON sc.c\_id=c.c\_id  
WHERE sc.s\_score<60

\#38 查询课程编号为 01 且课程成绩大于等于 80 的学生的学号和姓名  
\#本质 行筛选  
\#方法  
\#备注  
SELECT s.s\_id,s.s\_name  
FROM Score sc  
JOIN Student s ON sc.s\_id=s.s\_id  
WHERE c\_id='01'  
AND s\_score>=80

\#39 每门课程的学生人数  
\#本质 行聚合  
\#方法  
\#备注  
SELECT c.c\_name,COUNT(DISTINCT sc.s\_id) AS '人数'  
FROM Score sc  
JOIN Course c ON sc.c\_id=c.c\_id  
GROUP BY c.c\_name

\#40 查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩  
\#本质 行筛选后聚合  
\#方法 张三老师所授课程中，成绩最高（max、order by、row\_number()over）的学生 1、max需要关联表匹配 2、order by和limit 1配合使用，推荐 3、row\_number()over需要外层表限制rank\_num=1  
备注  
SELECT s.\*,s\_score  
FROM Score sc  
JOIN Student s ON sc.s\_id=s.s\_id  
WHERE c\_id=(SELECT c\_id FROM Course WHERE t\_id=(SELECT t\_id FROM Teacher WHERE t\_name='张三'))  
ORDER BY s\_score DESC  
LIMIT 1

\#41 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩  
\#本质 行筛选  
\#方法 符合条件的成绩：按成绩分组，对课程id计数且>2  
\#备注  
SELECT \*  
FROM Score  
WHERE s\_score IN(SELECT s\_score FROM Score GROUP BY s\_score HAVING COUNT(DISTINCT c\_id)>=2)

\#42 查询每门功成绩最好的前两名  
\#本质 行排序后筛选  
\#方法 这里分组取前两名，需窗口函数row\_number()over  
\#备注  
SELECT r.\*  
FROM(  
SELECT c\_name,s\_id,s\_score,  
ROW\_NUMBER()OVER(PARTITION BY c\_name ORDER BY s\_score DESC) AS rank\_num  
FROM Score sc  
JOIN Course c ON sc.c\_id=c.c\_id)r  
WHERE r.rank\_num<=2

\#43 统计每门课程的学生选修人数(超过 5 人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排列， 若人数相同，按课程号升序排列  
\#本质 行聚合后排序  
\#方法  
\#备注  
SELECT c\_id,COUNT(DISTINCT s\_id) AS '人数'  
FROM Score  
GROUP BY c\_id  
ORDER BY '人数' DESC,c\_id ASC

\#44 检索至少选修两门课程的学生学号  
\#本质 行聚合后筛选  
\#方法  
\#备注  
SELECT s\_id  
FROM Score  
GROUP BY s\_id  
HAVING COUNT(DISTINCT c\_id)>=2

\#45 查询选修了全部课程的学生信息  
\#本质 行聚合后筛选  
\#方法  
\#备注  
SELECT s.\*  
FROM Score sc  
JOIN Student s ON sc.s\_id=s.s\_id  
GROUP BY s.s\_id  
HAVING COUNT(DISTINCT c\_id)=(SELECT COUNT(\*) FROM Course)

\#46 查询各学生的年龄:按照出生日期来算，当前月日 < 出生年月的月日则，年龄减 1  
\#本质 行筛选：日期-年月日  
\#方法 年月日获取year()、month()、day()  
\#备注  
SELECT \*,  
CASE  
WHEN MONTH(NOW())<MONTH(DATE(s\_birth)) THEN  
YEAR(NOW())-YEAR(DATE(s\_birth))-1  
WHEN MONTH(NOW())=MONTH(DATE(s\_birth)) AND DAY(NOW())<DAY(DATE(s\_birth)) THEN  
YEAR(NOW())-YEAR(DATE(s\_birth))-1  
ELSE  
YEAR(NOW())-YEAR(DATE(s\_birth))  
END AS age  
FROM Student

\#47 查询本周过生日的学生  
\#本质 行筛选：日期-周  
\#方法 周获取week()  
\#备注  
SELECT \*  
FROM Student  
WHERE WEEK(DATE(s\_birth))=WEEK(NOW())

\#48 查询下周过生日的学生  
\#本质 行筛选：日期-周  
\#方法 周获取week()  
\#备注  
SELECT \*  
FROM Student  
WHERE WEEK(DATE(s\_birth))=WEEK(NOW())+1

\#49 查询本月过生的同学  
\#本质 行筛选：日期-月  
\#方法  
\#备注  
SELECT \*  
FROM Student  
WHERE MONTH(DATE(s\_birth))=MONTH(NOW())

\#50 查询下月过生的同学  
\#本质 行筛选：日期-月  
\#方法  
\#备注  
SELECT \*  
FROM Student  
WHERE MONTH(DATE(s\_birth))=MONTH(NOW())+1

![bdd2505b95e94343a5d3580cf8466aa9.png][]

停更了半个月，国庆之后疫情爆发，在家待着，颓废的不成样子，连博客都停更了，希望大家谅解

自制力不好，就换一个可以让自己静下心学习的环境，我们都不是天才，要想改变自己的困境，就要逼迫自己改变，虽然说，每天改变一点点，不足为奇，但是坚持下去，我们一定会跳出自己的舒适圈，

#### 今天的分享就到此结束了 ####

#### **创作不易点赞评论互关三连** ####

![c7a579659984427c951f24021bdb4be8.gif][]

[891f0675dbb74ec997e7674e3dc02a2a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/2d2df601ef0445ebb548a6cdb53fb1a2.png
[bdd2505b95e94343a5d3580cf8466aa9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/88e2d2c93fd04e2f833d5a99a2e6a239.png
[c7a579659984427c951f24021bdb4be8.gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/d8e9dcdb596f4a5ca5c48454ff85d836.gif