MySQL实战四：查询再续
- 2019 年 10 月 6 日
- 笔记
MySQL学习仓库Up-Up-MySQL，这是一个学习MySQL从入门实战到理论完善，再到精通的一个仓库，后面会把MySQL的学习资料上传上去！欢迎大家star与fork起来！
仓库地址：
https://github.com/Light-City/Up-Up-MySQL
也可以点击阅读原文！
今天上手第四弹，查询再续。
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
考虑两点：
第一点：平均成绩从高到低排序;
第二点：所有学生;(要想到用join)。
mysql> select * from SC sc left join (select sc.SId, avg(sc.score) AVG from SC sc group by sc.SId order by AVG desc)r on sc.SId=r.SId; +-----+-----+-------+------+----------+ | SId | CId | score | SId | AVG | +-----+-----+-------+------+----------+ | 01 | 01 | 80.0 | 01 | 89.66667 | | 01 | 02 | 90.0 | 01 | 89.66667 | | 01 | 03 | 99.0 | 01 | 89.66667 | | 02 | 01 | 70.0 | 02 | 70.00000 | | 02 | 02 | 60.0 | 02 | 70.00000 | | 02 | 03 | 80.0 | 02 | 70.00000 | | 03 | 01 | 80.0 | 03 | 80.00000 | | 03 | 02 | 80.0 | 03 | 80.00000 | | 03 | 03 | 80.0 | 03 | 80.00000 | | 04 | 01 | 50.0 | 04 | 33.33333 | | 04 | 02 | 30.0 | 04 | 33.33333 | | 04 | 03 | 20.0 | 04 | 33.33333 | | 05 | 01 | 76.0 | 05 | 81.50000 | | 05 | 02 | 87.0 | 05 | 81.50000 | | 06 | 01 | 31.0 | 06 | 32.50000 | | 06 | 03 | 34.0 | 06 | 32.50000 | | 07 | 02 | 89.0 | 07 | 93.50000 | | 07 | 03 | 98.0 | 07 | 93.50000 | +-----+-----+-------+------+----------+ 18 rows in set (0.00 sec)
查询各科成绩最高分、最低分和平均分以如下形式显示： 课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT sc.CId '课程 ID', c.Cname '课程 name', MAX(sc.score) AS '最高分' , MIN(sc.score) AS '最低分', AVG(sc.score) AS '平均成绩' , COUNT(sc.CId) AS '选修人数' , SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) / COUNT(sc.CId) AS '及格率' , SUM(CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END) / COUNT(sc.CId) AS '中等率' , SUM(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END) / COUNT(sc.CId) AS '优良率' , SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) / COUNT(sc.CId) AS '优秀率' FROM SC sc JOIN Course c ON sc.CId = c.CId GROUP BY sc.CId ORDER BY COUNT(sc.CId) DESC, sc.CId ASC;
查询结果：
+-----------+-------------+-----------+-----------+--------------+--------------+-----------+-----------+-----------+-----------+ | 课程 ID | 课程 name | 最高分 | 最低分 | 平均成绩 | 选修人数 | 及格率 | 中等率 | 优良率 | 优秀率 | +-----------+-------------+-----------+-----------+--------------+--------------+-----------+-----------+-----------+-----------+ | 01 | 语文 | 80.0 | 31.0 | 64.50000 | 6 | 0.6667 | 0.3333 | 0.3333 | 0.0000 | | 02 | 数学 | 90.0 | 30.0 | 72.66667 | 6 | 0.8333 | 0.0000 | 0.5000 | 0.1667 | | 03 | 英语 | 99.0 | 20.0 | 68.50000 | 6 | 0.6667 | 0.0000 | 0.3333 | 0.3333 | +-----------+-------------+-----------+-----------+--------------+--------------+-----------+-----------+-----------+-----------+ 3 rows in set (0.00 sec)
统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT Course.Cname, Course.CId, SUM(CASE WHEN sc.score <= 100 AND sc.score > 85 THEN 1 ELSE 0 END) AS "[100-85]" , SUM(CASE WHEN sc.score <= 85 AND sc.score > 70 THEN 1 ELSE 0 END) AS "[85-70]", SUM(CASE WHEN sc.score <= 70 AND sc.score > 60 THEN 1 ELSE 0 END) AS "[70-60]" , SUM(CASE WHEN sc.score <= 60 AND sc.score > 0 THEN 1 ELSE 0 END) AS "[60-0]" , SUM(CASE WHEN sc.score <= 100 AND sc.score > 85 THEN 1 ELSE 0 END) / COUNT(1) AS "[100-85]百分比" , SUM(CASE WHEN sc.score <= 85 AND sc.score > 70 THEN 1 ELSE 0 END) / COUNT(1) AS "[85-70]百分比" , SUM(CASE WHEN sc.score <= 70 AND sc.score > 60 THEN 1 ELSE 0 END) / COUNT(1) AS "[70-60]百分比" , SUM(CASE WHEN sc.score <= 60 AND sc.score > 0 THEN 1 ELSE 0 END) / COUNT(1) AS "[60-0]百分比" FROM SC sc LEFT JOIN Course ON sc.CId = Course.CId GROUP BY sc.CId;
查询各科成绩前三名的记录
mysql> select * from SC sc -> where (select count(*) from SC as a -> where sc.CId= a.CId and sc.score<a.score )< 3 -> order by CId asc, sc.score desc; +-----+-----+-------+ | SId | CId | score | +-----+-----+-------+ | 01 | 01 | 80.0 | | 03 | 01 | 80.0 | | 05 | 01 | 76.0 | | 01 | 02 | 90.0 | | 07 | 02 | 89.0 | | 05 | 02 | 87.0 | | 01 | 03 | 99.0 | | 07 | 03 | 98.0 | | 02 | 03 | 80.0 | | 03 | 03 | 80.0 | +-----+-----+-------+ 10 rows in set (0.00 sec)
查询每门课程被选修的学生数
mysql> select CId,count(SId) from SC group by CId; +-----+------------+ | CId | count(SId) | +-----+------------+ | 01 | 6 | | 02 | 6 | | 03 | 6 | +-----+------------+ 3 rows in set (0.00 sec)
查询出只选修两门课程的学生学号和姓名
联合查询：join连接：
mysql> select s.SId,s.Sname from Student s join SC sc on s.SId=sc.SId group by sc.SId having count(1)=2; +-----+--------+ | SId | Sname | +-----+--------+ | 05 | 周梅 | | 06 | 吴兰 | | 07 | 郑竹 | +-----+--------+ 3 rows in set (0.00 sec)
嵌套查询：in子句
mysql> select s.SId,s.Sname from Student s where s.SId in (select sc.SId from SC sc group by sc.SId having count(1)=2); +-----+--------+ | SId | Sname | +-----+--------+ | 05 | 周梅 | | 06 | 吴兰 | | 07 | 郑竹 | +-----+--------+ 3 rows in set (0.00 sec)
查询男生、女生人数
mysql> select s.Ssex,count(s.Ssex) as '总人数' from Student s group by s.Ssex; +------+-----------+ | Ssex | 总人数 | +------+-----------+ | 男 | 4 | | 女 | 8 | +------+-----------+ 2 rows in set (0.00 sec)
查询名字中含有“风”字的学生信息
mysql> select * from Student where Sname like '%风%'; +-----+--------+---------------------+------+ | SId | Sname | Sage | Ssex | +-----+--------+---------------------+------+ | 03 | 孙风 | 1990-12-20 00:00:00 | 男 | +-----+--------+---------------------+------+ 1 row in set (0.00 sec)
查询同名学生名单，并统计同名人数
mysql> select s.Sname,count(*) as '同名人数' from Student s group by s.Sname having count(*)>1; +--------+--------------+ | Sname | 同名人数 | +--------+--------------+ | 李四 | 2 | +--------+--------------+ 1 row in set (0.00 sec)
查询1990年出生的学生名单
like通配符：
mysql> select * from Student where Sage like '1990-%'; +-----+--------+---------------------+------+ | SId | Sname | Sage | Ssex | +-----+--------+---------------------+------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | | 03 | 孙风 | 1990-12-20 00:00:00 | 男 | | 04 | 李云 | 1990-12-06 00:00:00 | 男 | +-----+--------+---------------------+------+ 4 rows in set, 1 warning (0.00 sec)
year函数：
mysql> 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-12-20 00:00:00 | 男 | | 04 | 李云 | 1990-12-06 00:00:00 | 男 | +-----+--------+---------------------+------+ 4 rows in set (0.00 sec)
查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
mysql> select sc.CId,avg(sc.score) as '平均成绩' from SC sc group by sc.CId order by 平均成绩 desc ,sc.CId asc; +-----+--------------+ | CId | 平均成绩 | +-----+--------------+ | 02 | 72.66667 | | 03 | 68.50000 | | 01 | 64.50000 | +-----+--------------+ 3 rows in set (0.00 sec)
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
mysql> select s.SId,s.Sname,avg(sc.score) as average from Student s left join SC sc on sc.SId=s.SId group by s.SId having average>=85; +-----+--------+----------+ | SId | Sname | average | +-----+--------+----------+ | 01 | 赵雷 | 89.66667 | | 07 | 郑竹 | 93.50000 | +-----+--------+----------+ 2 rows in set (0.00 sec)
查询课程名称为“数学”，且分数低于 60 的学生姓名和分数
mysql> select s.Sname,sc.score from Course c,SC sc,Student s where c.Cname='数学' and c.CId=sc.CId and sc.score<60 and sc.SId=s.SId; +--------+-------+ | Sname | score | +--------+-------+ | 李云 | 30.0 | +--------+-------+ 1 row in set (0.00 sec)
查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
mysql> select s.Sname,sc.CId,sc.score from Student s left join SC sc on s.SId=sc.SId; +--------+------+-------+ | Sname | CId | score | +--------+------+-------+ | 赵雷 | 01 | 80.0 | | 赵雷 | 02 | 90.0 | | 赵雷 | 03 | 99.0 | | 钱电 | 01 | 70.0 | | 钱电 | 02 | 60.0 | | 钱电 | 03 | 80.0 | | 孙风 | 01 | 80.0 | | 孙风 | 02 | 80.0 | | 孙风 | 03 | 80.0 | | 李云 | 01 | 50.0 | | 李云 | 02 | 30.0 | | 李云 | 03 | 20.0 | | 周梅 | 01 | 76.0 | | 周梅 | 02 | 87.0 | | 吴兰 | 01 | 31.0 | | 吴兰 | 03 | 34.0 | | 郑竹 | 02 | 89.0 | | 郑竹 | 03 | 98.0 | | 张三 | NULL | NULL | | 李四 | NULL | NULL | | 李四 | NULL | NULL | | 赵六 | NULL | NULL | | 孙七 | NULL | NULL | +--------+------+-------+ 23 rows in set (0.00 sec)
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
mysql> select s.Sname, c.Cname,sc.score from Student s,Course c,SC sc -> where sc.score>70 -> and s.SId= sc.SId -> and sc.CId= c.CId; +--------+--------+-------+ | Sname | Cname | score | +--------+--------+-------+ | 赵雷 | 语文 | 80.0 | | 赵雷 | 数学 | 90.0 | | 赵雷 | 英语 | 99.0 | | 钱电 | 英语 | 80.0 | | 孙风 | 语文 | 80.0 | | 孙风 | 数学 | 80.0 | | 孙风 | 英语 | 80.0 | | 周梅 | 语文 | 76.0 | | 周梅 | 数学 | 87.0 | | 郑竹 | 数学 | 89.0 | | 郑竹 | 英语 | 98.0 | +--------+--------+-------+ 11 rows in set (0.00 sec)
查询存在不及格的课程
group by取唯一：
mysql> select sc.CId from SC sc where sc.score<60 group by sc.CId; +-----+ | CId | +-----+ | 01 | | 02 | | 03 | +-----+ 3 rows in set (0.00 sec)
distinct取唯一：
mysql> select distinct sc.CId from SC sc where sc.score<60; +-----+ | CId | +-----+ | 01 | | 02 | | 03 | +-----+ 3 rows in set (0.00 sec)
查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
mysql> select s.SId,s.Sname from Student s,SC sc where sc.SId=s.SId and sc.score>=80 and sc.CId='01'; +-----+--------+ | SId | Sname | +-----+--------+ | 01 | 赵雷 | | 03 | 孙风 | +-----+--------+ 2 rows in set (0.00 sec)
求每门课程的学生人数
mysql> select sc.CId,count(*) from SC sc group by sc.CId; +-----+----------+ | CId | count(*) | +-----+----------+ | 01 | 6 | | 02 | 6 | | 03 | 6 | +-----+----------+ 3 rows in set (0.00 sec)
查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩
下面两种方法不管是成绩重复与否，都可以查询出来！
in子查询：
mysql> select * from Student s,SC sc where s.SId=sc.SId and sc.score =( select max(sc.score) from SC sc,Teacher t,Course c where c.TId=t.TId and c.CId=sc.CId and t.Tname='张三'); +-----+--------+---------------------+------+-----+-----+-------+ | SId | Sname | Sage | Ssex | SId | CId | score | +-----+--------+---------------------+------+-----+-----+-------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 01 | 02 | 90.0 | +-----+--------+---------------------+------+-----+-----+-------+ 1 row in set (0.00 sec)
limit查询
mysql> select s.*,sc.score from Student s,SC sc,Teacher t,Course c where c.TId=t.TId and c.CId=sc.CId and sc.SId=s.SId and t.Tname='张三' order by sc.score desc limit 1; +-----+--------+---------------------+------+-------+ | SId | Sname | Sage | Ssex | score | +-----+--------+---------------------+------+-------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 90.0 | +-----+--------+---------------------+------+-------+ 1 row in set (0.01 sec)
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
mysql> select distinct sc1.CId,sc1.SId,sc1.score from SC sc1 join SC sc2 on sc1.SId=sc2.SId and sc1.CId!=sc2.CId and sc1.score=sc2.score; +-----+-----+-------+ | CId | SId | score | +-----+-----+-------+ | 02 | 03 | 80.0 | | 03 | 03 | 80.0 | | 01 | 03 | 80.0 | +-----+-----+-------+ 3 rows in set (0.00 sec)
查询每门功成绩最好的前两名
嵌套子查询：
mysql> select * from SC sc -> where (select count(*) from SC as a -> where sc.CId= a.CId and sc.score<a.score )< 2 -> order by CId asc, sc.score desc; +-----+-----+-------+ | SId | CId | score | +-----+-----+-------+ | 01 | 01 | 80.0 | | 03 | 01 | 80.0 | | 01 | 02 | 90.0 | | 07 | 02 | 89.0 | | 01 | 03 | 99.0 | | 07 | 03 | 98.0 | +-----+-----+-------+ 6 rows in set (0.01 sec)
group by having
mysql> select a.SId,a.CId,a.score from SC as a left join SC as b on a.CId=b.CId and a.score<b.score group by a.CId asc,a.SId,a.score desc having count(b.cid)<2 order by a.CId; +-----+-----+-------+ | SId | CId | score | +-----+-----+-------+ | 01 | 01 | 80.0 | | 03 | 01 | 80.0 | | 01 | 02 | 90.0 | | 07 | 02 | 89.0 | | 01 | 03 | 99.0 | | 07 | 03 | 98.0 | +-----+-----+-------+ 6 rows in set, 2 warnings (0.01 sec)
统计每门课程的学生选修人数（超过 5 人的课程才统计）
mysql> select sc.CId,count(sc.SId) as total from SC sc group by sc.CId having total>5; +-----+-------+ | CId | total | +-----+-------+ | 01 | 6 | | 02 | 6 | | 03 | 6 | +-----+-------+ 3 rows in set (0.00 sec)
检索至少选修两门课程的学生学号
mysql> select sc.SId,count(sc.CId) as count from SC sc group by sc.SId having count(sc.CId)>=2; +-----+-------+ | SId | count | +-----+-------+ | 01 | 3 | | 02 | 3 | | 03 | 3 | | 04 | 3 | | 05 | 2 | | 06 | 2 | | 07 | 2 | +-----+-------+ 7 rows in set (0.00 sec)
查询选修了全部课程的学生信息
mysql> select s.* from Student s,SC sc where sc.SId=s.SId group by sc.SId having count(sc.CId)=(select distinct count(*) from Course); +-----+--------+---------------------+------+ | SId | Sname | Sage | Ssex | +-----+--------+---------------------+------+ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | | 02 | 钱电 | 1990-12-21 00:00:00 | 男 | | 03 | 孙风 | 1990-12-20 00:00:00 | 男 | | 04 | 李云 | 1990-12-06 00:00:00 | 男 | +-----+--------+---------------------+------+ 4 rows in set (0.00 sec)
查询各学生的年龄，只按年份来算
mysql> select s.Sname,year(curdate())-year(s.Sage) as 年龄 from Student s; +--------+--------+ | Sname | 年龄 | +--------+--------+ | 赵雷 | 29 | | 钱电 | 29 | | 孙风 | 29 | | 李云 | 29 | | 周梅 | 28 | | 吴兰 | 27 | | 郑竹 | 30 | | 张三 | 2 | | 李四 | 2 | | 李四 | 7 | | 赵六 | 6 | | 孙七 | 5 | +--------+--------+ 12 rows in set (0.00 sec)
按照出生日期来算，当前月日 < 出生年月的月日,则年龄减一
mysql> select s.SId,s.Sname,( case -> when MONTH(curdate())<MONTH(s.Sage) then year(curdate())-year(s.Sage)-1 -> when MONTH(s.Sage)=month(curdate()) and DAYOfmonth(s.Sage)<DAYOfmonth(curdate()) -> then year(curdate())-year(s.Sage)-1 -> else year(curdate())-year(s.Sage) -> end) as 年龄 from Student s; +-----+--------+--------+ | SId | Sname | 年龄 | +-----+--------+--------+ | 01 | 赵雷 | 29 | | 02 | 钱电 | 28 | | 03 | 孙风 | 28 | | 04 | 李云 | 28 | | 05 | 周梅 | 27 | | 06 | 吴兰 | 27 | | 07 | 郑竹 | 30 | | 09 | 张三 | 1 | | 10 | 李四 | 1 | | 11 | 李四 | 6 | | 12 | 赵六 | 5 | | 13 | 孙七 | 4 | +-----+--------+--------+ 12 rows in set (0.00 sec)
查询本周过生日的学生
WEEKOFYEAR(date)返回日期用数字表示的范围是从1到53的日历周。`
mysql> select * from Student s where weekofyear(s.Sage)=weekofyear(curdate()); Empty set (0.00 sec)
查询下周过生日的学生
mysql> select * -> from Student s -> where WEEKOFYEAR(s.Sage)=WEEKOFYEAR(CURDATE())+1; Empty set (0.00 sec)
查询本月过生日的学生
mysql> select * -> from Student s -> where MONTH(s.Sage)=MONTH(CURDATE()); +-----+--------+---------------------+------+ | SId | Sname | Sage | Ssex | +-----+--------+---------------------+------+ | 11 | 李四 | 2012-06-06 00:00:00 | 女 | | 12 | 赵六 | 2013-06-13 00:00:00 | 女 | | 13 | 孙七 | 2014-06-01 00:00:00 | 女 | +-----+--------+---------------------+------+ 3 rows in set (0.00 sec)
查询下月过生日的学生
mysql> select * -> from Student s -> where MONTH(s.Sage)=MONTH(CURDATE())+1; Empty set (0.01 sec)
