mysql练习:经典50道基础题
阿里云国内75折 回扣 微信号:monov8 |
阿里云国际,腾讯云国际,低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号:monov8 飞机:@monov6 |
目录
- 一、环境准备
- 50道题目练习
- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
- 2、查询学生选课存在" 01 "课程但可能不存在" 02 "课程的情况不存在时显示为 null
- 3、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
- 4、查询在 SC 表存在成绩的学生信息
- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
- 6、查询「李」姓老师的数量
- 7、查询学过「张三」老师授课的同学的信息
- 8、查询没有学全所有课程的同学的信息
- 9、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
- 10、查询和"01"号的同学学习的课程完全相同的其他同学的信息
- 11、查询没学过"张三"老师讲授的任一门课程的学生姓名
- 12、查询两门及其以上不及格课程的同学的学号姓名及其平均成绩
- 13、查询"01"课程分数小于 60按分数降序排列的学生信息
- 14、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
- 15、查询各科成绩最高分、最低分和平均分
- 16、按各科成绩进行排序并显示排名 Score 重复时保留名次空缺
- 17、查询学生的总成绩并进行排名总分重复时保留名次空缺
- 18、查询学生的总成绩并进行排名总分重复时不保留名次空缺
- 19、统计各科成绩各分数段人数课程编号[100-85)[85-70)[70-60)[60-0] 及所占百分比
- 20、查询各科成绩前三名的记录
- 21、查询每门课程被选修的学生数
- 22、查询出只选修两门课程的学生学号和姓名
- 23、查询男生、女生人数
- 24、查询名字中含有「风」字的学生信息
- 25、查询同名同性学生名单并统计同名人数
- 26、查询 1990 年出生的学生名单
- 27、查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列
- 28、 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
- 29、查询课程名称为「数学」且分数低于 60 的学生姓名和分数
- 30、查询所有学生的课程及分数情况存在学生没成绩没选课的情况
- 31、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
- 32、查询不及格的课程
- 33、查询课程编号为 01 且课程成绩在 60 分以上的学生的学号和姓名
- 34、求每门课程的学生人数
- 35、成绩没有重复的情况下查询选修「张三」老师所授课程的学生中成绩最高的学生信息及其成绩
- 36、成绩有重复的情况下查询选修「张三」老师所授课程的学生中成绩最高的学生信息及其成绩
- 37、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
- 38、查询每门课程成绩最好的前两名
- 39、统计每门课程的学生选修人数超过 5 人的课程才统计
- 40、检索至少选修两门课程的学生学号
- 41、查询选修了全部课程的学生信息
- 42、查询各学生的年龄只按年份来算
- 43、按照出生日期来算当前月日 < 出生年月的月日则年龄减一
- 44、查询本周过生日的学生
- 45、查询下周过生日的学生
- 46、查询本月过生日的学生
- 47、查询下月过生日的学生
- 48、查询下周过生日的学生
- 49、查询本月过生日的学生
- 50、查询下月过生日的学生
- 参考文章
一、环境准备
建表共4张表分别对应学生信息Student、课程信息Course、教师信息Teacher以及成绩信息SC
-- 学生表
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
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' , '女');
-- 课程表
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 教师表
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 成绩表
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
50道题目练习
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT student.*,t3.sid FROM
(SELECT t1.sid,t1.score FROM
(SELECT sid,score FROM sc WHERE cid = "01") as t1
JOIN
(SELECT sid,score FROM sc WHERE cid = "02") as t2
ON
t1.sid = t2.sid WHERE t1.score > t2.score) as t3
JOIN student
ON t3.sid = student.sid;
结果
+-----+-------+---------------------+------+-----+
| sid | sname | sage | ssex | sid |
+-----+-------+---------------------+------+-----+
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 02 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 04 |
+-----+-------+---------------------+------+-----+
2 rows in set
解析
先将课程为01和02的课程及对应分数筛选出来再joinon为01.sid = 02.sid条件为01.score >02.score结果’存’为新表t3再将Student表和t3表join
2、查询学生选课存在" 01 “课程但可能不存在” 02 "课程的情况不存在时显示为 null
SELECT * FROM
(SELECT * FROM sc WHERE cid = "01") as t1
LEFT JOIN
(SELECT * FROM sc WHERE cid = "02") as t2
ON t1.sid = t2.sid;
结果
+-----+-----+-------+------+------+-------+
| sid | cid | score | sid | cid | score |
+-----+-----+-------+------+------+-------+
| 01 | 01 | 80.0 | 01 | 02 | 90.0 |
| 02 | 01 | 70.0 | 02 | 02 | 60.0 |
| 03 | 01 | 80.0 | 03 | 02 | 80.0 |
| 04 | 01 | 50.0 | 04 | 02 | 30.0 |
| 05 | 01 | 76.0 | 05 | 02 | 87.0 |
| 06 | 01 | 31.0 | NULL | NULL | NULL |
+-----+-----+-------+------+------+-------+
6 rows in set
解析
即找出学生选了01课程没有选02课程的情况用left join即可
3、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
#多表联合查询
SELECT sc.sid,student.sname,avg(sc.score) FROM sc ,student WHERE sc.sid = student.sid GROUP BY sc.sid HAVING avg(sc.score) > 60;
#多表连接查询
SELECT sc.sid,student.sname,avg(sc.score) FROM sc JOIN student on sc.sid = student.sid GROUP BY sc.sid HAVING avg(sc.score) > 60;
结果
+-----+-------+---------------+
| sid | sname | avg(sc.score) |
+-----+-------+---------------+
| 01 | 赵雷 | 89.66667 |
| 02 | 钱电 | 70.00000 |
| 03 | 孙风 | 80.00000 |
| 05 | 周梅 | 81.50000 |
| 07 | 郑竹 | 93.50000 |
+-----+-------+---------------+
5 rows in set
解析
首先确定的是两张表student和sc这里使用多表联合查询和多表连接查的方式都可以关联条件是sid然后分组最后加一个having函数条件是平均成绩大于60即可查询出来
4、查询在 SC 表存在成绩的学生信息
#多表联合查询方式
SELECT t1.*,t2.score FROM student t1, sc t2 WHERE t1.sid = t2.sid GROUP BY t1.sid;
#多表连接查询方式
SELECT a.*,b.score FROM student as a
JOIN sc AS b
ON a.sid = b.sid
GROUP BY a.sid;
结果
+-----+-------+---------------------+------+-------+
| sid | sname | sage | ssex | score |
+-----+-------+---------------------+------+-------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 80.0 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 80.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 76.0 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 31.0 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | 89.0 |
+-----+-------+---------------------+------+-------+
7 rows in set
解析
确定是两个表,student和sc关联条件还是sid消除笛卡尔积然后再group by最后select 取需要的信息
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
#多表联合查询方式
SELECT t1.sid as 学生编号,t1.sname as 学生姓名,COUNT(t2.cid) as 选课总数,SUM(t2.score) as 课程成绩总和 FROM student t1, sc t2 WHERE t1.sid = t2.sid GROUP BY t1.sid;
#多表连接查询
SELECT t1.sid as 学生编号,t1.sname as 学生姓名,COUNT(t2.cid) as 选课总数,SUM(t2.score) as 课程成绩总和 FROM student t1 JOIN sc t2 ON t1.sid = t2.sid GROUP BY t1.sid;
结果
+----------+----------+----------+--------------+
| 学生编号 | 学生姓名 | 选课总数 | 课程成绩总和 |
+----------+----------+----------+--------------+
| 01 | 赵雷 | 3 | 269.0 |
| 02 | 钱电 | 3 | 210.0 |
| 03 | 孙风 | 3 | 240.0 |
| 04 | 李云 | 3 | 100.0 |
| 05 | 周梅 | 2 | 163.0 |
| 06 | 吴兰 | 2 | 65.0 |
| 07 | 郑竹 | 2 | 187.0 |
+----------+----------+----------+--------------+
7 rows in set
解析
两个聚合函数统计函数一个count(cid),一个sum(score),同样join student表和sc表再group by sid即可
6、查询「李」姓老师的数量
SELECT COUNT(t.tid) FROM teacher t WHERE t.tname like "%李%";
结果
+--------------+
| COUNT(t.tid) |
+--------------+
| 1 |
+--------------+
1 row in set
解析
count加条件函数加通配符即可
7、查询学过「张三」老师授课的同学的信息
SELECT s.*,y.tname FROM
(SELECT sc.sid,x.tname FROM
(SELECT t.tname,c.cid FROM teacher AS t
JOIN course AS c
ON t.tid = c.tid
WHERE t.tname = '张三') AS x
JOIN sc
ON x.cid = sc.cid) AS y
JOIN student AS s
ON y.sid = s.sid;
结果
+-----+-------+---------------------+------+-------+
| sid | sname | sage | ssex | tname |
+-----+-------+---------------------+------+-------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 张三 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 张三 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 张三 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 张三 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 张三 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | 张三 |
+-----+-------+---------------------+------+-------+
6 rows in set
解析
四表连接teacher表里的tid与course表里的tid条件为tname=‘张三’再course表里的cid与sc表里的cid最后sc表里的sid与student里的sid
8、查询没有学全所有课程的同学的信息
SELECT a.*,count(b.cid) AS 所学课程数
FROM student AS a
LEFT JOIN sc AS b
ON a.sid = b.sid
GROUP BY a.sid
HAVING COUNT(b.cid) < (SELECT COUNT(c.cid) FROM course as c);
结果
+-----+-------+---------------------+------+------------+
| sid | sname | sage | ssex | 所学课程数 |
+-----+-------+---------------------+------+------------+
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 2 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 2 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | 2 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 | 0 |
+-----+-------+---------------------+------+------------+
解析
先查询总课程数再查询所有同学的信息筛选条件为其所学课程数小于总课程数
9、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT s.* FROM student AS s
JOIN sc
ON s.sid = sc.sid
WHERE sc.cid in
(SELECT sc.cid FROM sc AS sc WHERE sc.sid = '01')
GROUP bY s.sid
HAVING s.sid != '01';
结果
+-----+-------+---------------------+------+
| sid | sname | sage | ssex |
+-----+-------+---------------------+------+
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
+-----+-------+---------------------+------+
6 rows in set
解析
先从成绩表里查询学号为01的同学所学的课程编号筛选条件为sc.cid in 01同学所学编号再使用学生表和成绩表两表关联关联字段为sid并且把课程编号作为子查询的条件刷选然后再group by sid 最后通过having筛选sid 不等于01
10、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select * from student t1 where t1.sid not in
(select p.sid fro
(select t.*,sc.sid sc_sid ,sc.cid sc_cid from
(select * from student ,
(select cid from sc where sid = "01") s ) t
left join sc on t.sid = sc.sid and t.cid = sc.cid) p where sc_sid is null)
and t1.sid != "01"
and (SELECT count(t3.cid) from sc t3 where t3.sid = t1.sid) = (SELECT COUNT(*) FROM sc t2 WHERE t2.sid = "01");
结果
+-----+-------+---------------------+------+---------------+
| sid | sname | sage | ssex | count(t3.cid) |
+-----+-------+---------------------+------+---------------+
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 3 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 3 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 3 |
+-----+-------+---------------------+------+---------------+
3 rows in set
解析
先从成绩表中查询学号为01的总课程数然后使用学生表和成绩表关联查询关联字段为sid消除笛卡尔积where条件语句过滤学号01并且用学号字段分组并且使用having函数统计课程总数=学号为1的课程总数
11、查询没学过"张三"老师讲授的任一门课程的学生姓名
#多表联合查询方式
SELECT student.sname FROM student
WHERE student.sid NOT IN
(SELECT sc.sid FROM sc
JOIN course
ON sc.cid=course.cid
JOIN teacher
ON course.tid=teacher.tid
WHERE tname='张三' );
#多表连接查询方式
SELECT student.sname FROM student
WHERE student.sid NOT IN
(SELECT sc.sid FROM sc,course,teacher where sc.cid = course.cid and course.tid=teacher.tid and tname='张三');
结果
+-------+
| sname |
+-------+
| 吴兰 |
| 王菊 |
+-------+
2 rows in set
解析
先找出所有学生选课信息及sid再找出张三老师授课课程将其连接再用student里的sid not in 前面的sid
12、查询两门及其以上不及格课程的同学的学号姓名及其平均成绩
SELECT c.sname, b.*
FROM student c
JOIN ((
SELECT sid, COUNT(cid)
FROM sc
WHERE score < 60
GROUP BY sid
HAVING COUNT(cid) >= 2
) a
JOIN (
SELECT sid, avg(score)
FROM sc
GROUP BY sid
) b
ON a.sid = b.sid) ON c.sid = b.sid;
结果
+-------+-----+------------+
| sname | sid | avg(score) |
+-------+-----+------------+
| 李云 | 04 | 33.33333 |
| 吴兰 | 06 | 32.50000 |
+-------+-----+------------+
2 rows in set
解析
先查询出不及格两门或两门以上的数据再查询出不及格的平均成绩再三张表嵌套关联
13、查询"01"课程分数小于 60按分数降序排列的学生信息
#多表联合查询方式
SELECT b.*, a.score
FROM student b
JOIN (
SELECT *
FROM sc
WHERE cid = '01'
AND score < 60
ORDER BY score DESC
) a
ON a.sid = b.sid;
#多表连接查询方式
SELECT b.*, a.score
FROM student b,
(
SELECT *
FROM sc
WHERE cid = '01'
AND score < 60
ORDER BY score DESC
) a
where a.sid = b.sid;
结果
+-----+-------+---------------------+------+-------+
| sid | sname | sage | ssex | score |
+-----+-------+---------------------+------+-------+
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 31.0 |
+-----+-------+---------------------+------+-------+
2 rows in set
解析
先查询出01课程分数小于60的sid 按照分数降序然后和学生表关联
14、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT a.sid, a.score, a.cid, b.`平均成绩`
FROM sc a
JOIN (
SELECT sid, avg(score) AS 平均成绩
FROM sc
GROUP BY sid
) b
ON a.sid = b.sid
ORDER BY b.`平均成绩` DESC;
结果
+-----+-------+-----+----------+
| sid | score | cid | 平均成绩 |
+-----+-------+-----+----------+
| 07 | 89.0 | 02 | 93.50000 |
| 07 | 98.0 | 03 | 93.50000 |
| 01 | 80.0 | 01 | 89.66667 |
| 01 | 90.0 | 02 | 89.66667 |
| 01 | 99.0 | 03 | 89.66667 |
| 05 | 76.0 | 01 | 81.50000 |
| 05 | 87.0 | 02 | 81.50000 |
| 03 | 80.0 | 01 | 80.00000 |
| 03 | 80.0 | 02 | 80.00000 |
| 03 | 80.0 | 03 | 80.00000 |
| 02 | 70.0 | 01 | 70.00000 |
| 02 | 60.0 | 02 | 70.00000 |
| 02 | 80.0 | 03 | 70.00000 |
| 04 | 50.0 | 01 | 33.33333 |
| 04 | 30.0 | 02 | 33.33333 |
| 04 | 20.0 | 03 | 33.33333 |
| 06 | 31.0 | 01 | 32.50000 |
| 06 | 34.0 | 03 | 32.50000 |
+-----+-------+-----+----------+
18 rows in set
解析
先求平均成绩注意这里的平均成绩一定要取别名然后取所有人的成绩再关联然后按照平均成绩降序排列
15、查询各科成绩最高分、最低分和平均分
以如下形式显示
课程 id最高分最低分平均分及格率中等率优良率优秀率
及格为>=60中等为[70,80)优良为[80-90)优秀为>=90
要求输出课程号和选修人数查询结果按人数降序排列若人数相同按课程号升序
SELECT cid AS 课程id, MAX(score) AS 最高分, MIN(score) AS 最低分, AVG(score) AS 平均分
, SUM(CASE
WHEN score >= 60 THEN 1
ELSE 0
END) / COUNT(sid) AS 及格率
, SUM(CASE
WHEN score >= 70
AND score < 80
THEN 1
ELSE 0
END) / count(sid) AS 中等率
, SUM(CASE
WHEN score >= 80
AND score < 90
THEN 1
ELSE 0
END) / count(sid) AS 优良率
, SUM(CASE
WHEN score >= 90 THEN 1
ELSE 0
END) / count(sid) AS 优秀率
FROM sc
GROUP BY cid
ORDER BY cid ASC;
结果
+--------+--------+--------+----------+--------+--------+--------+--------+
| 课程id | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 优良率 | 优秀率 |
+--------+--------+--------+----------+--------+--------+--------+--------+
| 01 | 80.0 | 31.0 | 64.50000 | 0.6667 | 0.3333 | 0.3333 | 0.0000 |
| 02 | 90.0 | 30.0 | 72.66667 | 0.8333 | 0.0000 | 0.5000 | 0.1667 |
| 03 | 99.0 | 20.0 | 68.50000 | 0.6667 | 0.0000 | 0.3333 | 0.3333 |
+--------+--------+--------+----------+--------+--------+--------+--------+
3 rows in set
解析
重点在case when语句的用法其实case when 就类似于 if函数 if x>某个值then 1 else 0。就只用一个表只是对表头需要做修改用聚合函数+AS
16、按各科成绩进行排序并显示排名 Score 重复时保留名次空缺
-- MySql8.0以上
select *, rank() over(partition by cid order by score desc) AS ranked from sc;
-- MySql5.7为实现分组组内排名
select s.*, @rank:=@rank+1 as ranked from sc as s,(SELECT @rank:=0) as p ORDER BY score desc;
结果
+-----+-----+-------+--------+
| sid | cid | score | ranked |
+-----+-----+-------+--------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 1 |
| 05 | 01 | 76.0 | 3 |
| 02 | 01 | 70.0 | 4 |
| 04 | 01 | 50.0 | 5 |
| 06 | 01 | 31.0 | 6 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 03 | 02 | 80.0 | 4 |
| 02 | 02 | 60.0 | 5 |
| 04 | 02 | 30.0 | 6 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
| 02 | 03 | 80.0 | 3 |
| 03 | 03 | 80.0 | 3 |
| 06 | 03 | 34.0 | 5 |
| 04 | 03 | 20.0 | 6 |
+-----+-----+-------+--------+
18 rows in set
解析
注意mysql8.0之前 是没有rank函数
MySQL可以实现Oracle中的排名公式一共有三种
- rank() over(order by col_name desc
- dense_rank() over()
- row_number() over()
第一个是如果出现了相同排名都为同一排名下个排名跳过例如1,1,3,4
第二个是如果出现了相同排名都为同一排名下个排名不跳过例如1,1,2,3
第三个是直接对行进行排名不分是否有相同值
此题目要按照各科成绩进行排序 over()中要填partition by col_name order by col_name
第一个colname 为分组的内容第二个是按什么值排的内容
17、查询学生的总成绩并进行排名总分重复时保留名次空缺
-- MySql8.0以上
SELECT a.*, rank() OVER (ORDER BY a.总成绩 DESC) AS Ranked
FROM (
SELECT *, SUM(score) AS 总成绩
FROM sc
GROUP BY sid
) a;
-- MySql5.7
SELECT a.*, @rank := @rank + 1 AS ranked
FROM (
SELECT s.*, SUM(score) AS 总成绩
FROM sc s
GROUP BY sid
) a, (
SELECT @rank := 0
) p
ORDER BY a.总成绩 DESC;
结果
+-----+-----+-------+--------+--------+
| sid | cid | score | 总成绩 | Ranked |
+-----+-----+-------+--------+--------+
| 01 | 01 | 80.0 | 269.0 | 1 |
| 03 | 01 | 80.0 | 240.0 | 2 |
| 02 | 01 | 70.0 | 210.0 | 3 |
| 07 | 02 | 89.0 | 187.0 | 4 |
| 05 | 01 | 76.0 | 163.0 | 5 |
| 04 | 01 | 50.0 | 100.0 | 6 |
| 06 | 01 | 31.0 | 65.0 | 7 |
+-----+-----+-------+--------+--------+
7 rows in set
解析
跟上题一样用rankover只是多了层嵌套
18、查询学生的总成绩并进行排名总分重复时不保留名次空缺
SELECT a.*, dense_rank() OVER (ORDER BY a.total_socre DESC) AS Ranked
FROM (
SELECT *, SUM(score) AS total_socre
FROM sc
GROUP BY sid
) a;
结果
+-----+-----+-------+-------------+--------+
| sid | cid | score | total_socre | Ranked |
+-----+-----+-------+-------------+--------+
| 01 | 01 | 80.0 | 269.0 | 1 |
| 03 | 01 | 80.0 | 240.0 | 2 |
| 02 | 01 | 70.0 | 210.0 | 3 |
| 07 | 02 | 89.0 | 187.0 | 4 |
| 05 | 01 | 76.0 | 163.0 | 5 |
| 04 | 01 | 50.0 | 100.0 | 6 |
| 06 | 01 | 31.0 | 65.0 | 7 |
+-----+-----+-------+-------------+--------+
7 rows in set
解析
和上面一样只是换成dense_rank () over()只是总分没有重复无法看出区别
19、统计各科成绩各分数段人数课程编号[100-85)[85-70)[70-60)[60-0] 及所占百分比
SELECT cid AS 课程ID,
SUM(CASE WHEN score <= 60 THEN 1 ELSE 0 END)/count(sid) AS 百分比1,
SUM(CASE WHEN score >60 AND score <=70 THEN 1 ELSE 0 END)/count(sid) AS 百分比2,
SUM(CASE WHEN score >70 AND score <=85 THEN 1 ELSE 0 END)/count(sid) AS 百分比3,
SUM(CASE WHEN score >85 THEN 1 ELSE 0 END)/count(sid) AS 百分比4
FROM sc GROUP BY cid ORDER BY cid;
结果
+--------+---------+---------+---------+---------+
| 课程ID | 百分比1 | 百分比2 | 百分比3 | 百分比4 |
+--------+---------+---------+---------+---------+
| 01 | 0.3333 | 0.1667 | 0.5000 | 0.0000 |
| 02 | 0.3333 | 0.0000 | 0.1667 | 0.5000 |
| 03 | 0.3333 | 0.0000 | 0.3333 | 0.3333 |
+--------+---------+---------+---------+---------+
3 rows in set
解析
使用case when
20、查询各科成绩前三名的记录
-- MySql8.0以上
SELECT * FROM
(SELECT *,rank() over(PARTITION by cid ORDER BY score desc) as ranked FROM sc) as a
WHERE a.ranked <=3;
-- MySql5.7
SELECT *
FROM sc
WHERE (
SELECT count(*)
FROM sc a
WHERE sc.CId = a.CId
AND sc.score < a.score
) < 3
ORDER BY CId ASC, sc.score DESC
结果
+-----+-----+-------+--------+
| sid | cid | score | ranked |
+-----+-----+-------+--------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 1 |
| 05 | 01 | 76.0 | 3 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
| 02 | 03 | 80.0 | 3 |
| 03 | 03 | 80.0 | 3 |
+-----+-----+-------+--------+
10 rows in set
解析
与上面rank一样用rankoverwhere ranked <=3
注意where 的执行顺序在select前嵌套一个select 语句就好
MySql5.7版本使用嵌套循环找出cid相同并且比自己score大但不超过三条的数据前三名
21、查询每门课程被选修的学生数
SELECT cid AS 课程id, COUNT(sid) AS 选修的学生数
FROM sc
GROUP BY cid
ORDER BY 课程id;
结果
+--------+--------------+
| 课程id | 选修的学生数 |
+--------+--------------+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+--------+--------------+
3 rows in set
解析
单表 查询使用group by order by
22、查询出只选修两门课程的学生学号和姓名
SELECT student.sname, a.*
FROM student
JOIN (
SELECT sid, count(cid) AS 选修课程数
FROM sc
GROUP BY sid
HAVING 选修课程数 = 2
) a
ON student.sid = a.sid;
结果
+-------+-----+------------+
| sname | sid | 选修课程数 |
+-------+-----+------------+
| 周梅 | 05 | 2 |
| 吴兰 | 06 | 2 |
| 郑竹 | 07 | 2 |
+-------+-----+------------+
3 rows in set
解析
先从成绩表中查询出只选修两门课程的学生id和课程数再和学生表进行关联查询
23、查询男生、女生人数
SELECT ssex,COUNT(sid) FROM student GROUP BY ssex;
结果
+------+------------+
| ssex | COUNT(sid) |
+------+------------+
| 男 | 4 |
| 女 | 4 |
+------+------------+
2 rows in set
解析
根据ssex group by后再count()
24、查询名字中含有「风」字的学生信息
SELECT * FROM student WHERE sname like "%风%";
结果
+-----+-------+---------------------+------+
| sid | sname | sage | ssex |
+-----+-------+---------------------+------+
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
+-----+-------+---------------------+------+
1 row in set
解析
通配符%‘%a’a结尾‘a%’a开头‘%a%’含有a
25、查询同名同性学生名单并统计同名人数
SELECT *, COUNT(sid) AS 同名人数
FROM (
SELECT a.*
FROM student a
JOIN student b
WHERE a.sname = b.sname
AND a.ssex = b.ssex
) c
GROUP BY sid
HAVING 同名人数 >= 2;
结果
解析
连接表student和student on ssname and ssex 在group by sid因为id唯一name可能重名count sid
26、查询 1990 年出生的学生名单
SELECT * FROM student WHERE YEAR(sage) = 1990;
结果
+-----+-------+---------------------+------+
| sid | sname | sage | ssex |
+-----+-------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
+-----+-------+---------------------+------+
5 rows in set
解析
sage一列为datetime类型用时间函数。MySQL里面能够对datetime类型函数截取年、月、周、日等等 用YEAR()来表示年以此类推
27、查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列
SELECT cid,avg(score) AS 平均成绩 FROM sc GROUP BY cid ORDER BY 平均成绩 DESC,cid ASC;
结果
+-----+----------+
| cid | 平均成绩 |
+-----+----------+
| 02 | 72.66667 |
| 03 | 68.50000 |
| 01 | 64.50000 |
+-----+----------+
3 rows in set
解析
order by x desc,y,z,… 先根据x排序再根据y然后z…
28、 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT student.sname, a.*
FROM student
JOIN (
SELECT sid AS 学号, avg(score) AS 平均成绩
FROM sc
GROUP BY sid
HAVING 平均成绩 > 85
) a
ON student.sid = a.学号;
结果
+-------+------+----------+
| sname | 学号 | 平均成绩 |
+-------+------+----------+
| 赵雷 | 01 | 89.66667 |
| 郑竹 | 07 | 93.50000 |
+-------+------+----------+
2 rows in set
解析
先从成绩表中查询出平均成绩大于85的学生好和平均成绩记住这里需要取别名然后再和学生表关联关联字段为sid获取到学生名字
29、查询课程名称为「数学」且分数低于 60 的学生姓名和分数
SELECT student.sname, c.*
FROM student
JOIN (
SELECT t1.cname, t2.score, t2.sid
FROM course t1
JOIN sc t2 ON t1.cid = t2.cid
WHERE t2.score < 60
AND t1.cname = '数学'
) c
ON student.sid = c.sid;
结果
+-------+-------+-------+-----+
| sname | cname | score | sid |
+-------+-------+-------+-----+
| 李云 | 数学 | 30.0 | 04 |
+-------+-------+-------+-----+
1 row in set
解析
先把课程表和成绩表关联获取到低于60分的学生号、分数和课程名称作为临时表然后再和学生表关联获取到最后一个字段学生姓名
30、查询所有学生的课程及分数情况存在学生没成绩没选课的情况
SELECT student.sname, c.*
FROM student
JOIN (
SELECT a.cname, b.sid, b.score
FROM course a
LEFT JOIN sc b ON a.cid = b.cid
) c
ON student.sid = c.sid;
结果
+-------+-------+-----+-------+
| sname | cname | sid | score |
+-------+-------+-----+-------+
| 赵雷 | 语文 | 01 | 80.0 |
| 赵雷 | 数学 | 01 | 90.0 |
| 赵雷 | 英语 | 01 | 99.0 |
| 钱电 | 语文 | 02 | 70.0 |
| 钱电 | 数学 | 02 | 60.0 |
| 钱电 | 英语 | 02 | 80.0 |
| 孙风 | 语文 | 03 | 80.0 |
| 孙风 | 数学 | 03 | 80.0 |
| 孙风 | 英语 | 03 | 80.0 |
| 李云 | 语文 | 04 | 50.0 |
| 李云 | 数学 | 04 | 30.0 |
| 李云 | 英语 | 04 | 20.0 |
| 周梅 | 语文 | 05 | 76.0 |
| 周梅 | 数学 | 05 | 87.0 |
| 吴兰 | 语文 | 06 | 31.0 |
| 吴兰 | 英语 | 06 | 34.0 |
| 郑竹 | 数学 | 07 | 89.0 |
| 郑竹 | 英语 | 07 | 98.0 |
+-------+-------+-----+-------+
18 rows in set
解析
先把课程表和成绩表关联关联字段为cid获取到课程名称学生号和学科成绩作为临时表然后再和学生表关联关联字段为sid获取到学生名字
31、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT student.sname, c.*
FROM student
JOIN (
SELECT a.cname, b.sid, b.score
FROM course a
LEFT JOIN sc b ON a.cid = b.cid
) c
ON student.sid = c.sid
WHERE c.score > 70;
结果
+-------+-------+-----+-------+
| sname | cname | sid | score |
+-------+-------+-----+-------+
| 赵雷 | 语文 | 01 | 80.0 |
| 赵雷 | 数学 | 01 | 90.0 |
| 赵雷 | 英语 | 01 | 99.0 |
| 钱电 | 英语 | 02 | 80.0 |
| 孙风 | 语文 | 03 | 80.0 |
| 孙风 | 数学 | 03 | 80.0 |
| 孙风 | 英语 | 03 | 80.0 |
| 周梅 | 语文 | 05 | 76.0 |
| 周梅 | 数学 | 05 | 87.0 |
| 郑竹 | 数学 | 07 | 89.0 |
| 郑竹 | 英语 | 07 | 98.0 |
+-------+-------+-----+-------+
11 rows in set
解析
在上一题的基础上增加score > 70,使用where 或and都可以
32、查询不及格的课程
SELECT cname, a.*
FROM course
JOIN (
SELECT score, cid
FROM sc
WHERE score < 60
) a
ON course.cid = a.cid;
结果
+-------+-------+-----+
| cname | score | cid |
+-------+-------+-----+
| 语文 | 50.0 | 01 |
| 数学 | 30.0 | 02 |
| 英语 | 20.0 | 03 |
| 语文 | 31.0 | 01 |
| 英语 | 34.0 | 03 |
+-------+-------+-----+
5 rows in set
解析
先从成绩表中获取到不及格的课程id和成绩然后再和课程表关联关联字典为课程id获取到课程名称
33、查询课程编号为 01 且课程成绩在 60 分以上的学生的学号和姓名
SELECT student.sname,c.* FROM student
JOIN
(SELECT b.sid ,b.score,a.cid ,a.cname FROM course as a
JOIN
sc as b
ON a.cid = b.cid WHERE a.cid = "01" AND b.score > 60) as c ON student.sid = c.sid;
结果
+-------+-----+-------+-----+-------+
| sname | sid | score | cid | cname |
+-------+-----+-------+-----+-------+
| 赵雷 | 01 | 80.0 | 01 | 语文 |
| 钱电 | 02 | 70.0 | 01 | 语文 |
| 孙风 | 03 | 80.0 | 01 | 语文 |
| 周梅 | 05 | 76.0 | 01 | 语文 |
+-------+-----+-------+-----+-------+
4 rows in set
解析
先从课程表和成绩表中获取到学生号、成绩、课程号和课程名称关联字段为课程号作为临时表然后再和学生表关联关联字段为学生号获取到学生名字
34、求每门课程的学生人数
SELECT course.cname, a.*
FROM course
JOIN (
SELECT count(sid), cid
FROM sc
GROUP BY cid
) a
ON course.cid = a.cid;
结果
+-------+------------+-----+
| cname | count(sid) | cid |
+-------+------------+-----+
| 语文 | 6 | 01 |
| 数学 | 6 | 02 |
| 英语 | 6 | 03 |
+-------+------------+-----+
3 rows in set
解析
先从成绩表中统计出每门课程的人数再和课程表关联关联字段为课程号获取到课程名称
35、成绩没有重复的情况下查询选修「张三」老师所授课程的学生中成绩最高的学生信息及其成绩
SELECT student.sname, e.*
FROM student
JOIN (
SELECT MAX(d.score), c.*, d.sid
FROM sc d
JOIN (
SELECT a.tid, a.tname, b.cid, b.cname
FROM teacher a
JOIN course b ON a.tid = b.tid
WHERE a.tname = '张三'
) c
ON d.cid = c.cid
) e
ON student.sid = e.sid;
结果
+-------+--------------+-----+-------+-----+-------+-----+
| sname | MAX(d.score) | tid | tname | cid | cname | sid |
+-------+--------------+-----+-------+-----+-------+-----+
| 赵雷 | 90.0 | 01 | 张三 | 02 | 数学 | 01 |
+-------+--------------+-----+-------+-----+-------+-----+
1 row in set
解析
教师表和课程表关联获取到教师编号、教师名称和课程编号和课程名称关联字段为教师编号
作为临时表再和成绩表关联关联字段为课程编号
作为临时表再和学生表关联关联字段为学生号
36、成绩有重复的情况下查询选修「张三」老师所授课程的学生中成绩最高的学生信息及其成绩
SELECT student.sname, e.*
FROM student
JOIN (
SELECT MAX(d.score), c.*, d.sid
, rank() OVER (ORDER BY MAX(d.score)) AS Ranked
FROM sc d
JOIN (
SELECT a.tid, a.tname, b.cid, b.cname
FROM teacher a
JOIN course b ON a.tid = b.tid
WHERE a.tname = '张三'
) c
ON d.cid = c.cid
) e
ON student.sid = e.sid
WHERE e.Ranked;
结果
+-------+--------------+-----+-------+-----+-------+-----+--------+
| sname | MAX(d.score) | tid | tname | cid | cname | sid | Ranked |
+-------+--------------+-----+-------+-----+-------+-----+--------+
| 赵雷 | 90.0 | 01 | 张三 | 02 | 数学 | 01 | 1 |
+-------+--------------+-----+-------+-----+-------+-----+--------+
1 row in set
解析
用rank函数然后再嵌套一个selectwhere rank = 1
37、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT a.*
FROM sc a
JOIN sc b
ON a.score = b.score
AND a.cid != b.cid;
结果
+-----+-----+-------+
| sid | cid | score |
+-----+-----+-------+
| 02 | 03 | 80.0 |
| 03 | 02 | 80.0 |
| 03 | 03 | 80.0 |
| 01 | 01 | 80.0 |
| 03 | 01 | 80.0 |
+-----+-----+-------+
5 rows in set
解析
sc表自连distinct去重cid 不同score相同
38、查询每门课程成绩最好的前两名
-- MySql8.0以上
SELECT * FROM
(SELECT *,dense_rank()over(PARTITION BY cid ORDER BY score DESC) AS ranked FROM sc ) a
WHERE a.ranked <=2;
-- MySql5.7
SELECT *
FROM sc
WHERE (
SELECT count(*)
FROM sc a
WHERE sc.CId = a.CId
AND sc.score < a.score
) < 2
ORDER BY CId ASC, sc.score DESC;
结果
+-----+-----+-------+--------+
| sid | cid | score | ranked |
+-----+-----+-------+--------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 1 |
| 05 | 01 | 76.0 | 2 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
+-----+-----+-------+--------+
7 rows in set
解析
我认为最好的前两名是排名的前2个即第一个排名1 和第二个排名2如果有两个并列第一一个第二那么前两名应该是3个人用dense_rank排名不跳过如果说是最好的前两个人就用rank排名跳过
39、统计每门课程的学生选修人数超过 5 人的课程才统计
SELECT course.cname,a.* FROM course
JOIN
(SELECT cid,COUNT(sid) as 选修人数 FROM sc GROUP BY cid HAVING COUNT(sid) >5) as a
ON course.cid = a.cid;
结果
+-------+-----+----------+
| cname | cid | 选修人数 |
+-------+-----+----------+
| 语文 | 01 | 6 |
| 数学 | 02 | 6 |
| 英语 | 03 | 6 |
+-------+-----+----------+
3 rows in set
解析
group byhaving聚合
40、检索至少选修两门课程的学生学号
SELECT student.sname, a.*
FROM student
JOIN (
SELECT sid, COUNT(cid) AS 选修课程总数
FROM sc
GROUP BY sid
HAVING 选修课程总数 >= 2
) a
ON student.sid = a.sid;
结果
+-------+-----+--------------+
| sname | sid | 选修课程总数 |
+-------+-----+--------------+
| 赵雷 | 01 | 3 |
| 钱电 | 02 | 3 |
| 孙风 | 03 | 3 |
| 李云 | 04 | 3 |
| 周梅 | 05 | 2 |
| 吴兰 | 06 | 2 |
| 郑竹 | 07 | 2 |
+-------+-----+--------------+
7 rows in set
41、查询选修了全部课程的学生信息
SELECT student.*, c.`选修课程总数`
FROM student
JOIN (
SELECT b.sid, COUNT(a.cid) AS 选修课程总数
FROM course a
JOIN sc b ON a.cid = b.cid
GROUP BY b.sid
HAVING COUNT(a.cid) = (
SELECT COUNT(cid)
FROM course
)
) c
ON student.sid = c.sid;
结果
+-----+-------+---------------------+------+--------------+
| sid | sname | sage | ssex | 选修课程总数 |
+-----+-------+---------------------+------+--------------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 3 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 3 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 3 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 3 |
+-----+-------+---------------------+------+--------------+
4 rows in set
解析
从课程表中查询出总的课程数作为后面子查询的条件
从成绩表中查询出选修了全部课程数的的学生号和选修的课程总数
作为临时表和学生表关联关联字段为学生号获取到全部的学生信息
42、查询各学生的年龄只按年份来算
SELECT sname,YEAR(NOW()) - YEAR(sage) as 年龄 FROM student;
结果
+-------+------+
| sname | 年龄 |
+-------+------+
| 赵雷 | 31 |
| 钱电 | 31 |
| 孙风 | 31 |
| 李云 | 31 |
| 周梅 | 30 |
| 吴兰 | 29 |
| 郑竹 | 32 |
| 王菊 | 31 |
+-------+------+
8 rows in set
解析
使用year函数
43、按照出生日期来算当前月日 < 出生年月的月日则年龄减一
SELECT sname
, CASE
WHEN DATE_FORMAT(NOW(), '%m-%d') - DATE_FORMAT(sage, '%m-%d') < 0 THEN YEAR(NOW()) - YEAR(sage) - 1
ELSE YEAR(NOW()) - YEAR(sage)
END AS age
FROM student;
结果
+-------+-----+
| sname | age |
+-------+-----+
| 赵雷 | 31 |
| 钱电 | 30 |
| 孙风 | 31 |
| 李云 | 31 |
| 周梅 | 29 |
| 吴兰 | 29 |
| 郑竹 | 32 |
| 王菊 | 31 |
+-------+-----+
8 rows in set
解析
有两种方法一种是利用date_format直接截取时间类型中的月日直接比大小
另外一种是用month()先比大小相等再用day()比大小
44、查询本周过生日的学生
SELECT sname FROM student WHERE week(NOW()) = WEEK(sage);
结果
Empty set
解析
week() 返回的是今年的第几周即如果本周过生返回数字相等
45、查询下周过生日的学生
SELECT sname FROM student WHERE week(NOW()) + 1 = WEEK(sage);
结果
Empty set
解析
加一就行
46、查询本月过生日的学生
SELECT sname FROM student WHERE month(NOW()) = month(sage);
结果
Empty set
解析
使用month函数
47、查询下月过生日的学生
SELECT sname FROM student WHERE month(NOW()) + 1 = month(sage);
结果
Empty set
48、查询下周过生日的学生
SELECT st.*
FROM student st
WHERE week(now()) + 1 = week(date_format(st.s_birth, ‘ % Y % m % d’))
49、查询本月过生日的学生
SELECT st.*
FROM student st
WHERE month(now()) = month(date_format(st.s_birth, ‘ % Y % m % d’))
50、查询下月过生日的学生
SELECT st.*
FROM student st
WHERE month(timestampadd(month, 1, now())) = month(date_format(st.s_birth, ‘ % Y % m % d’));
-- 或者
SELECT st.*
FROM student st
WHERE (month(now()) + 1) % 12 = month(date_format(st.s_birth, ‘ % Y % m % d’));
注意:当当前月为12时,用month(now())+1为13而不是1,可用timestampadd()函数或mod取模
参考文章
MYSQL窗口函数https://blog.csdn.net/Annabel_CM/article/details/125840831
MYSQL基础常见常用语句200条https://blog.csdn.net/c361604199/article/details/79479398