MySQL实战四：查询再续

公众号guangcity

发布于 2019-09-20 17:30:39

1K0

发布于 2019-09-20 17:30:39

文章被收录于专栏：光城(guangcity)光城(guangcity)

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)

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-06-25，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server

数据库

sql

本文分享自光城微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

云数据库 SQL Server

数据库

sql

登录后参与评论

0 条评论

热度

MySQL实战四：查询再续

MySQL实战四：查询再续

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐