SQL经典案例45讲

说明：
1、数据库字段表名应当使用小写字母，此处案例参考来源其它地方，未做规范化。
2、涉及到知识点的会做相应拓展补充

数据库表

数据库表SQL：practice.sql
示例数据库：Mysql 8.0

1、查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数

-- 1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT
    * 
FROM
    student a
    INNER JOIN score b ON a.SId = b.SId
    INNER JOIN score c ON a.SId = c.SId 
    AND (b.CId = "01" AND c.CId = "02")
WHERE
    b.score > c.score
-- 查询同时存在”01“课程和"02“课程的情况
SELECT * FROM
 (SELECT * FROM score WHERE CId = '01') a
INNER JOIN 
(SELECT * FROM score WHERE CId = '02') b
ON a.SId = b.SId;
-- 简化代码
SELECT * FROM
score a
INNER JOIN score b
on a.SId = b.SId
WHERE a.CId = '01' AND b.SId = '02'
-- 1.2查询存在”01“课程但可能不存在"02"课程的情况(不存在时显示为 null )
SELECT * FROM
(SELECT * FROM score WHERE CId = '01') a
LEFT JOIN score b
ON a.SId = b.SId AND b.CId = '02';
-- 简化
SELECT * FROM score a
LEFT JOIN score b
ON a.SId = b.SId AND b.CId = '02'
WHERE a.CId = '01';
-- 1.3查询不存在"01"课程但存在"02“课程的情况
SELECT
    * 
FROM
    score 
WHERE
    SId NOT IN ( SELECT SId FROM score WHERE CId = '01' ) 
    AND CId = '02'

2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT a.SId, b.Sname, a.score FROM
(SELECT SId,AVG(score) score FROM score GROUP BY SId HAVING 60) a
LEFT JOIN student b ON a.SId = b.SId

3、查询在SC表存在成绩的学生信息

SELECT DISTINCT
    a.SId,
    b.Sname,
    b.Sage,
    b.Ssex 
FROM
    score a
    LEFT JOIN student b ON a.SId = b.SId;

-- 另外一种方法
SELECT
    b.* 
FROM
    ( SELECT SId FROM score GROUP BY SId ) a
    LEFT JOIN student b ON a.SId = b.SId

4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)

SELECT * FROM
(SELECT * FROM student) a LEFT JOIN
(SELECT
    SId,
    SUM(score),
    COUNT(DISTINCT CId) 
FROM
    score GROUP BY SId) b ON a.SId = b.SId

-- 查询有成绩的学生信息
SELECT
    * 
FROM
    student 
WHERE
    SId IN (SELECT SId FROM score GROUP BY SId)

5、查询李姓老师的数量

-- 5、查询李姓老师的数量
SELECT COUNT(TId) FROM teacher WHERE Tname LIKE '李%';
-- 查询字符列中的任何单一字符为 四的
SELECT * FROM teacher WHERE Tname REGEXP '[四]';

6、查询学过「张三」老师授课的同学的信息

SELECT
    d.* 
FROM
    ( SELECT TId FROM teacher WHERE Tname = '张三' ) a
    LEFT JOIN course b ON a.TId = b.TId
    LEFT JOIN score c ON b.CId = c.CId
    LEFT JOIN student d ON c.SId = d.SId

7、查询没有学全所有课程的同学的信息

SELECT b.*,a.count_c FROM
(SELECT
    SId,
    count( DISTINCT CId ) count_c 
FROM
    score 
GROUP BY
    SId 
HAVING
    count_c <(
    SELECT
        count( CId ) 
    FROM
        course)
) a
LEFT JOIN student b ON a.SId = b.SId

8、查询至少有一门课与学号为”01“的同学所学相同的同学的信息

SELECT
    d.* 
FROM
    (
    SELECT DISTINCT
        b.SId 
    FROM
        ( SELECT CId FROM score WHERE SId = '01' ) a
        LEFT JOIN score b ON a.CId = b.CId 
    ) c
    LEFT JOIN student d ON c.SId = d.SId

-- 第二种
SELECT 
DISTINCT a.* 
FROM
    student a
    INNER JOIN (
    SELECT
        * 
    FROM
        score 
WHERE
    CId IN ( SELECT CId FROM score WHERE SId = '01' )) b ON a.SId = b.SId

9、查询和”01”号的同学学习的课程完全相同的其他同学的信息

SELECT
    b.* 
FROM
    (
    SELECT
        SId 
    FROM
        score 
    WHERE
        SId NOT IN (
        SELECT
            SId 
        FROM
            score 
        WHERE
        CId NOT IN ( SELECT CId FROM score WHERE SId = '01' )) 
        AND SId != '01' 
    GROUP BY
        SId 
    HAVING
    COUNT( CId ) = ( SELECT COUNT( CId ) FROM score WHERE SId = '01' )) a
    LEFT JOIN student b ON a.SId = b.SId

10、查询没学过”张三”老师讲授的任一门课程的学生姓名

SELECT
    DISTINCT c.Sname
FROM
    (
    SELECT
        SId 
    FROM
        score 
    WHERE
    CId NOT IN ( SELECT a.CId FROM course a LEFT JOIN teacher b ON a.TId = b.TId WHERE Tname = '张三' )) b
    LEFT JOIN student c ON b.SId = c.SId

11、查询两门及以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
    a.SId,
    b.Sname,
    avg_score 
FROM
    (SELECT
        a.SId,
        AVG(score) AS avg_score 
    FROM
        score a
        INNER JOIN (SELECT SId FROM score WHERE score < 60 GROUP BY SId HAVING count( CId ) >= 2 ) b ON a.SId = b.SId 
    GROUP BY
        a.SId 
    ) a
    LEFT JOIN student b ON a.SId = b.SId

12、检索”01”课程分数小于60，按分数降序排列的学生信息

SELECT
    b.*,
    a.score 
FROM
    ( SELECT * FROM score WHERE CId = '01' AND score < 60 ) a
    LEFT JOIN student b ON a.SId = b.SId 
ORDER BY
    a.score DESC

13、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT
    a.SId,a.Sname,b.avg_score
FROM
    student a
    LEFT JOIN 
  ( SELECT SId,avg( score ) AS avg_score FROM score 
   GROUP BY SId ORDER BY avg_score DESC ) b 
  ON a.SId = b.SId

14、查询各科成绩最高分、最低分和平均分

SELECT CId, count(*) AS elective, MAX( score ), MIN( score ), AVG( score ), 
sum( CASE WHEN score >= 60 THEN 1 ELSE 0 END )/ count(*) AS 及格率,
sum( CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END )/ count(*) AS 中等率,
sum( CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END )/ count(*) AS 优良率,
sum( CASE WHEN score >= 90 THEN 1 ELSE 0 END )/ count(*) AS 优秀率
    FROM
        score 
GROUP BY
    CId

15、按各科成绩进行排序，并显示排名，Score重复时也继续排名

（具体在 SQL/SQL基础/窗口函数查看详细介绍）

SELECT
    *,
    rank() over ( PARTITION BY cid ORDER BY score DESC ) AS ranking,
    dense_rank() over ( PARTITION BY cid ORDER BY score DESC ) AS dese_rank,
    row_number() over ( PARTITION BY cid ORDER BY score DESC ) AS row_num 
FROM
    score;

16、查询学生的总成绩，并进行排名，总分重复时保留名次空缺

-- 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT
    SId,
    sum_score,
    RANK() over ( ORDER BY sum_score DESC ) AS sort 
FROM
    ( SELECT SId, sum( score ) AS sum_score FROM score GROUP BY SId ) a


-- 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT
    SId,
    sum_score,
    DENSE_RANK() over ( ORDER BY sum_score DESC ) AS sort 
FROM
    ( SELECT SId, sum( score ) AS sum_score FROM score GROUP BY SId ) a

17、CASE WHEN 统计各科成绩各分数段人数

-- 分段求和
SELECT CId,
SUM(CASE WHEN score >= 0 AND score < 60 THEN 1 ELSE 0 END) AS '[60-0]',
SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) AS '[60-70]',
SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) AS '[70-85]',
SUM(CASE WHEN score >= 85 AND score < 100 THEN 1 ELSE 0 END) AS '[60-0]'
FROM score
GROUP BY CId;

-- 两位小数的百分比
SELECT b.Cname,a.* FROM (
SELECT CId,
CONCAT(ROUND(SUM(CASE WHEN score >= 0 AND score < 60 THEN 1 ELSE 0 END) /COUNT(*),2) *100,'%') AS '[60-0]',
CONCAT(ROUND(SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END)/COUNT(*),2) *100,'%') AS '[60-70]',
CONCAT(ROUND(SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END)/COUNT(*),2) *100,'%') AS '[70-85]',
CONCAT(ROUND(SUM(CASE WHEN score >= 85 AND score < 100 THEN 1 ELSE 0 END)/COUNT(*),2) *100,'%') AS '[80-100]'
FROM score
GROUP BY CId
) AS a LEFT JOIN course b ON a.CId  = b.CId

18、查询各科成绩前三名的记录

SELECT * FROM(
SELECT *,
    DENSE_RANK() over(PARTITION by CId ORDER BY score DESC) AS score_rank 
FROM score) AS a 
WHERE score_rank <= 3

19、查询每门课程被选修的学生数

SELECT CId,count(*) AS counter FROM score GROUP BY CId

20、查询出只选修两门课程的学生学号和姓名

SELECT b.Sname,a.* FROM (
SELECT SId,COUNT(CId) AS cunter FROM score GROUP BY SId HAVING cunter = 2 ) AS a 
LEFT JOIN student AS b ON a.SId = b.SId

21、统计性别

-- 统计性别
SELECT Ssex,COUNT(*) AS sex_counter FROM student GROUP BY ssex

22、姓名 中含有“风”的学生信息

SELECT * FROM student WHERE Sname LIKE '%风%'

23、统计同名同性别的学生

-- 
SELECT Sname, Ssex, COUNT(*) AS counter FROM student GROUP BY Sname, Ssex HAVING counter >=2

24、查询1990年出生的学生

SELECT * FROM student WHERE YEAR(Sage) = '1990'

25、查询成绩并排序

-- 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT
    cid,
    AVG( score ) AS avg_score 
FROM
    score 
GROUP BY
    CId 
ORDER BY
    avg_score DESC,
    CId ASC

26、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT b.*,a.avg_score FROM(
SELECT SId,avg(score) AS avg_score FROM score GROUP BY SId HAVING avg_score>= 85) AS a 
LEFT JOIN student b ON a.SId = b.SId

27、查询课程名称为「数学」，且分数低于60的学生姓名和分数

SELECT
    b.SId,b.Sname,a.CId,a.score 
FROM
    score AS a
    LEFT JOIN student AS b ON a.SId = b.SId 
WHERE
    CId = (SELECT CId FROM course WHERE Cname = '数学')

28、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况)

SELECT * FROM student AS a LEFT JOIN score b ON a.SId = b.SId

29、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT * FROM score WHERE score >= '70'
SELECT Sname,Cname,score FROM score a 
LEFT JOIN course b ON a.CId = b.CId 
LEFT JOIN student AS c ON a.SId = c.SId 
WHERE a.score > 70

30、查询不及格的课程

SELECT b.CId,b.Cname FROM(
SELECT DISTINCT CId FROM score WHERE score <60) AS a 
LEFT JOIN course AS b ON a.CId = b.CId

31、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

SELECT a.*,b.Sname FROM score AS a LEFT JOIN student AS b 
ON a.SId = b.SId WHERE a.CId = '01' AND a.score >= 80

34、假设成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

-- 同等环境0.019
SELECT * FROM (
SELECT d.SId,e.Sname,d.score,DENSE_RANK() over(ORDER BY d.score DESC) AS score_rank FROM 
(SELECT b.CId FROM
(SELECT * FROM teacher WHERE Tname = '张三') AS a LEFT JOIN course b ON a.TId = b.TId) AS c
 LEFT JOIN score AS d ON c.CId = d.CId LEFT JOIN student AS e
    ON d.SId = e.SId) AS t WHERE score_rank = 1

-- 同等环境0.022
SELECT * FROM(
SELECT b.*,a.score,d.Tname,DENSE_RANK() over (ORDER BY score DESC) as score_rank 
FROM score AS a LEFT JOIN student AS b ON a.SId = b.SId LEFT JOIN course AS c
 ON a.CId = c.CId LEFT JOIN teacher AS d ON c.TId = d.TId WHERE Tname = '张三') AS a
    WHERE score_rank = 1;

35、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT DISTINCT a.* FROM score AS a INNER JOIN score b
ON a.SId = b.SId AND a.CId !=b.CId AND a.score = b.score

36、查询每门科日成绩最好的前两名

-- 36、查询每门科日成绩最好的前两名
SELECT * FROM(
SELECT CId,SId,score,DENSE_RANK() over(PARTITION BY CId ORDER BY score DESC) AS rank_score FROM score) 
AS t LEFT JOIN student AS b ON t.SId = b.SId where t.rank_score <=2

38、检索至少选修两门课程的学生学号

SELECT SId,COUNT(*) AS counter FROM score GROUP BY SId HAVING counter >=2;

39、查询选修了全部课程的学生信息

SELECT b.*,a.counter FROM(
SELECT SId,COUNT(*) AS counter FROM score GROUP BY SId 
HAVING counter =(SELECT COUNT(*) FROM course)) AS a LEFT JOIN student b ON a.SId = b.SId

40、查询年龄，只按年份计算

SELECT SId,Sname,YEAR(NOW()) - YEAR(sage) FROM student

41、查询年龄，按日期计算

select timestampdiff(year , '1990-02-01 00:00:00' , '2020-01-01 00:00:00');
select timestampdiff(year , '1990-02-01 00:00:00' , '2020-04-01 00:00:00');
select timestampdiff(day , '1990-02-01 00:00:00' , '2020-03-01 00:00:00');
select timestampdiff(hour , '1990-02-01 00:00:00' , '2020-02-03 00:00:00');

SELECT *,TIMESTAMPDIFF(year,Sage,NOW()) AS time_diff FROM student

42、查询本周过生日的

SELECT week(NOW());

SELECT *,WEEK(Sage),WEEK('2020-05-20 00:00:00') FROM student
WHERE week(Sage) = WEEK('2020-05-20 00:00:00');

43、查询下周过生日的

SELECT week(NOW());
-- 当前时间是第25周

SELECT *,WEEK(Sage),WEEK(NOW()) + 1 FROM student
WHERE week(Sage) = WEEK(NOW()) + 1;

如果是查询本月或下月过生日的同样的方法，用函数MONTH()。