sql经典45题

冷不防 2022-10-07 14:49 112阅读 0赞

1.创建表  
代码：

# 学生表 Student：
    
    CREATE DATABASE IF NOT EXISTS sss;
    CREATE TABLE Student(
    SId VARCHAR(10) ,
    Sname VARCHAR(10),
    Sage DATETIME,
    Ssex VARCHAR(10));
    
    # 教师表 Teacher
    CREATE TABLE Teacher(
    TId VARCHAR(10),
    Tname VARCHAR(10)); 
    
    # 科目表 Course
    CREATE TABLE Course(
    CId VARCHAR(10),
    Cname NVARCHAR(10),
    TId VARCHAR(10)); 
    
    # 成绩表 SC
    CREATE TABLE SC(
    SId VARCHAR(10),
    CId VARCHAR(10),
    score DECIMAL(18,1)); 
    SHOW TABLES;
    
    # 学生表 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('09' , '张三' , '2017-12-20' , '女');
    INSERT INTO Student VALUES('10' , '李四' , '2017-12-25' , '女');
    INSERT INTO Student VALUES('11' , '李四' , '2017-12-30' , '女');
    INSERT INTO Student VALUES('12' , '赵六' , '2017-01-01' , '女');
    INSERT INTO Student VALUES('13' , '孙七' , '2018-01-01' , '女');
    
    # 科目表 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' , '王五'); 
    
    # 成绩表 SC
    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);

创建结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][20210618162015999.png]  
![在这里插入图片描述][20210618162031466.png]  
![在这里插入图片描述][20210618162044217.png]

# 1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
    SELECT *
    FROM student a
    JOIN sc b
    ON a.sid=b.sid
    JOIN sc c
    ON a.sid=c.sid AND b.cid=01 AND c.cid=02
    WHERE b.score > c.score;
    # 两个分数在同一表格内且列名一样，无法提取，需要分开表写
    # 或者说 查询同一表中的同一列的信息时就需要自联结。
    
    # 1.1 .查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
    
    SELECT * 
    FROM (SELECT * FROM sc WHERE cid='01') s1
    LEFT JOIN (SELECT * FROM sc WHERE cid='02') s2
    ON  s1.sid=s2.sid;
    
    SELECT * 
    FROM sc s1
    LEFT JOIN sc s2
    ON  s1.sid=s2.sid AND s2.cid='02'
    WHERE s1.cid='01';
    
    # 要保留2表的null，可以把2的筛选条件放在on中。
    
    #	1.2 .查询同时存在01和02课程的情况
    
    SELECT * 
    FROM (SELECT * FROM sc WHERE cid='01') s1
    JOIN (SELECT * FROM sc WHERE cid='02') s2
    ON  s1.sid=s2.sid;
    
    SELECT * 
    FROM sc s1
    JOIN sc s2
    ON  s1.sid=s2.sid
    WHERE s1.cid='01' AND s2.cid='02';
    
    #	1.3 .查询选择了02课程但没有01课程的情况
    SELECT * 
    FROM (SELECT * FROM sc WHERE sid NOT IN (SELECT sid FROM sc WHERE cid='01')) s1
    INNER JOIN sc s2
    ON  s1.sid=s2.sid AND s2.cid='02';
    # 表连接，必须是个表，所以要再加一层子连接
    
    SELECT *
    FROM sc
    WHERE sid NOT IN (SELECT sid FROM sc WHERE cid='01')
    AND cid='02';
    
    #2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
    
    SELECT s.sid,s.`Sname`,AVG(c.score) avg_score
    FROM student s
    JOIN sc c
    ON s.sid=c.sid
    GROUP BY s.sid
    HAVING avg_score>60;
    
    SELECT s.sid,s.`Sname`,b.avg_score
    FROM student s
    JOIN
    (SELECT sid,AVG(score) avg_score
    FROM sc 
    GROUP BY sid
    HAVING avg_score>60) b
    ON s.sid=b.sid;
    
    #3.查询在 SC 表存在成绩的学生信息
    SELECT s.*
    FROM student s
    RIGHT JOIN (SELECT sid FROM sc GROUP BY sid) a
    ON s.`SId`=a.sid;
    
    --4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
    
    # 查询所有学生
    
    -- 提示:所有同学存在于学生表，课程数、成绩情况在成绩表，
    -- 就是要学生表左关联成绩汇总表得到记录，关联不上的说明就是没成绩。
    
    SELECT s.sid,s.sname,c.`选课总数`,c.`总成绩`
    FROM student s
    LEFT JOIN 
    (SELECT sid,COUNT(1) '选课总数',SUM(score) '总成绩'
    FROM sc
    GROUP BY sid) c
    ON s.sid=c.sid;
    
    
    SELECT s.sid,s.sname,COUNT(s.sid) AS cons,SUM(c.score) AS sum_score
    FROM student s
    LEFT JOIN sc c
    ON s.sid=c.sid
    GROUP BY s.sid;
    
    
    -- 	5.查询「李」姓老师的数量
    SELECT COUNT(1)
    FROM teacher
    WHERE tname LIKE '李%';

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

/*
    教师表: teacher   tid+tname
    课程表:course   tid+cid
    戍绩表:sc  cid + sid
    学生表: student  sid
    */
    # 查询张三老师教的课
    SELECT c.cid,t.tname
    FROM teacher t
    JOIN course c
    ON t.tid=c.tid
    WHERE t.tname='张三';
    
    # 查询有这门课的同学
    SELECT sid
    FROM sc
    WHERE cid IN (SELECT c.cid
    FROM teacher t
    JOIN course c
    ON t.tid=c.tid
    WHERE t.tname='张三');
    
    # 查询信息
    SELECT * 
    FROM student
    WHERE sid IN (SELECT sid
    FROM sc
    WHERE cid IN (SELECT c.cid
    FROM teacher t
    JOIN course c
    ON t.tid=c.tid
    WHERE t.tname='张三'));

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

# 所有课程在course中,查询全部课程的数量
    SELECT COUNT(1) FROM course;
    # 查询学生的课程数量，查询数量小于count的学生的信息
    SELECT s.*,COUNT(s.sid) cons
    FROM student s
    LEFT JOIN sc c
    ON s.sid = c.sid
    GROUP BY s.sid
    HAVING cons<(SELECT COUNT(1) FROM course);

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

# '01'号同学的课程
    SELECT cid FROM sc
    WHERE sid='01';
    # 查询其他学生的课程
    SELECT DISTINCT s.*
    FROM student s
    INNER JOIN sc c
    ON s.`SId`=c.`SId`
    WHERE c.cid IN (
    SELECT cid FROM sc
    WHERE sid='01'
    );
    
    # 第二种
    SELECT * FROM student s
    INNER JOIN (
    SELECT  c.sid
    FROM sc c
    WHERE c.cid IN (SELECT cid FROM sc
    WHERE sid='01')
    GROUP BY c.sid
    ) d
    ON s.`SId`=d.sid;

1.  查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

# 没有学习其他课程且和‘01’ 学习的课程数量一致
    # ① 学习其他课程的同学
    SELECT sid
    FROM sc
    WHERE cid NOT IN (SELECT cid FROM sc
    WHERE sid='01');
    
    # ② 没有学习其他课程的同学
    SELECT * 
    FROM student
    WHERE sid NOT IN
    (SELECT sid
    FROM sc
    WHERE cid NOT IN (SELECT cid FROM sc
    WHERE sid='01'));
    
    #③ ‘01’号学习的课程数量
    SELECT COUNT(1) FROM sc
    WHERE sid='01'
    
    # ④ 
    SELECT s.* 
    FROM student s
    INNER JOIN sc c
    ON s.sid=c.sid
    WHERE s.sid NOT IN
    (SELECT sid
    FROM sc
    WHERE cid NOT IN (SELECT cid FROM sc
    WHERE sid='01') )AND s.sid!='01'
    GROUP BY s.sid
    HAVING COUNT(s.sid)=(SELECT COUNT(1) FROM sc
    WHERE sid='01');

1.  查询没学过"张三"老师讲授的任一门课程的学生姓名

# 张三老师教的课
    SELECT c.cid
    FROM course c
    INNER JOIN teacher t
    ON c.tid = t.`TId`
    WHERE t.`Tname`='张三';
    
    # 学过张三老师的课的同学，即学的课在老师教的课中
    SELECT sid FROM sc
    WHERE `CId` IN (SELECT c.cid
    FROM course c
    INNER JOIN teacher t
    ON c.tid = t.`TId`
    WHERE t.`Tname`='张三');
    
    # 没学过的同学
    SELECT * FROM student 
    WHERE sid NOT IN (SELECT sid FROM sc
    WHERE `CId` IN (SELECT c.cid
    FROM course c
    INNER JOIN teacher t
    ON c.tid = t.`TId`
    WHERE t.`Tname`='张三'));
    
    
    # 第二种
    SELECT 
     d.sid
    FROM sc a
    LEFT JOIN course b
    ON a.cid = b.`CId`
    LEFT JOIN teacher c
    ON  b.tid=c.`TId`
    LEFT JOIN student d
    ON a.sid=d.`SId`
    WHERE c.`Tname`='张三';
    
    
    SELECT * FROM student 
    WHERE sid NOT IN (SELECT 
     d.sid
    FROM sc a
    LEFT JOIN course b
    ON a.cid = b.`CId`
    LEFT JOIN teacher c
    ON  b.tid=c.`TId`
    LEFT JOIN student d
    ON a.sid=d.`SId`
    WHERE c.`Tname`='张三');

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

# 成绩平均值
    SELECT sid,AVG(score)
    FROM sc
    GROUP BY sid;
    
    # 课程不及格的数量超过2
    SELECT sid
    FROM sc
    WHERE score<60
    GROUP BY sid
    HAVING COUNT(*)>=2;
    
    # 学生信息
    SELECT s.sid,s.sname,a.avg_score
    FROM student s
    INNER JOIN (SELECT sid,AVG(score) avg_score
    FROM sc
    GROUP BY sid) a
    ON s.`SId`=a.sid
    WHERE s.sid IN (SELECT sid
    FROM sc
    WHERE score<60
    GROUP BY sid
    HAVING COUNT(*)>=2)

1.  检索" 01 "课程分数小于 60，按分数降序排列的学生信息

# 当限制条件量表中都有时，可以直接在on中添加
    SELECT s.*
    FROM student s
    JOIN sc c
    ON s.`SId`=c.sid AND c.cid='01'
    WHERE c.score<60
    ORDER BY c.score DESC;
    
    # 第二种
    SELECT a.*,b.*
    FROM (SELECT sid,score
    FROM sc
    WHERE cid='01' AND score<60) a
    LEFT JOIN student b
    ON a.sid=b.`SId`
    ORDER BY a.score DESC;

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

# 平均成绩
    SELECT sid,AVG(score) avg_score
    FROM sc
    GROUP BY sid;
    # 关联排序
    SELECT s.*,c.avg_score
    FROM sc s
    INNER JOIN (SELECT sid,AVG(score) avg_score
    FROM sc
    GROUP BY sid) c
    ON s.sid = c.sid
    ORDER BY avg_score DESC;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuYWguRg_size_10_color_FFFFFF_t_70_g_se_x_16]

SELECT sc.sid,
    SUM(CASE WHEN cid=01 THEN score ELSE NULL END) '01课程',
    SUM(CASE WHEN cid=02 THEN score ELSE NULL END) '02课程',
    SUM(CASE WHEN cid=03 THEN score ELSE NULL END) '03课程',
    ss.avg_score
    FROM sc 
    INNER JOIN (SELECT sid,AVG(score) avg_score
    FROM sc
    GROUP BY sid) ss
    ON sc.`SId`=ss.sid
    GROUP BY sid

![在这里插入图片描述][c461ed38ef2a4b0a843372be27bf3903.png]

# 我的sql没有这个关键字，出不来，呜呜
    SELECT * FROM sc
    pivot
    (
    SUM(score)
    FOR cid IN ([01],[02],[03])
    ) AS d

14.查询各科成绩最高分、最低分和平均分，及格率等等

SELECT c.cid '课程ID',c.cname '课程name',MAX(score) '最高分',
    MIN(score) '最低分',AVG(score) '平均分',COUNT(*) '选修人数',
    SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(1) AS '及格率',
    SUM(CASE WHEN score>=70 AND score<80 THEN 1 ELSE 0 END)/COUNT(1) AS '中等率',
    SUM(CASE WHEN score>=80 AND score<90 THEN 1 ELSE 0 END)/COUNT(1) AS '优良率',
    SUM(CASE WHEN score>=90 THEN 1 ELSE 0 END)/COUNT(1) AS '优秀率'
    FROM sc s
    INNER JOIN course c
    ON s.cid=c.cid
    GROUP BY s.cid
    ORDER BY  '选修人数' DESC,s.cid ASC;

![在这里插入图片描述][f2abdebbcc364daa8be5acf1df5b6082.png]  
15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

SELECT b.cid,a.sname,b.score,rank() over(PARTITION BY b.cid ORDER BY b.score DESC) cnt
    FROM student a
    RIGHT JOIN sc b
    ON a.`SId`=b.`SId`

![在这里插入图片描述][c37536f3775c44aa9e4241d810a88cd4.png]  
16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

SELECT sid,a.sum_score,rank() over(ORDER BY a.sum_score DESC) cnt
    FROM( SELECT sid,SUM(score) sum_score
    FROM sc
    GROUP BY sid
    ) a

![在这里插入图片描述][8e62e4c4b9314dec8e55b0be41463126.png]

-- 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
    SELECT sid,a.sum_score,dense_rank() over(ORDER BY a.sum_score DESC) cnt
    FROM( SELECT sid,SUM(score) sum_score
    FROM sc
    GROUP BY sid
    ) a

![在这里插入图片描述][df8e547739dd48d7b429eb19c166227a.png]  
17. 统计各科成绩各分数段人数：课程编号，课程名称，\[100-85\]，\[85-70\]，\[70-60\]，\[60-0\] 及所占百分比

SELECT s.cid,c.cname,
    SUM(CASE WHEN s.score >85 THEN 1 ELSE 0 END)/COUNT(1) AS '(85-100]',
    SUM(CASE WHEN s.score >70 AND s.score<=85 THEN 1 ELSE 0 END)/COUNT(1) AS '(70-85]',
    SUM(CASE WHEN s.score >60 AND s.score<=70 THEN 1 ELSE 0 END)/COUNT(1) AS '(60-70]',
    SUM(CASE WHEN s.score <=60 THEN 1 ELSE 0 END)/COUNT(1) AS '[0-60]'
    FROM sc s
    INNER JOIN course c 
    ON s.cid = c.cid
    GROUP BY s.cid

![在这里插入图片描述][b18d4903032a4451a2cdf950bfd1354e.png]  
18. 查询各科成绩前三名的记录

SELECT ss.cid,ss.sid,ss.score,ss.cnt
    FROM(
    SELECT *,rank() over(PARTITION BY cid ORDER BY score DESC) cnt
    FROM sc) ss
    WHERE cnt<4;

![在这里插入图片描述][113b722534a440539dffaecb6c648287.png]

# 前三名转化为大于此成绩的数量少于3
    # 其实就是笛卡尔积的写法，只不过灵活好用,但是不好查看排名
    SELECT a.*
    FROM sc a
    WHERE (SELECT COUNT(1) FROM sc b WHERE a.cid = b.cid AND b.score>a.score)<3
    ORDER BY cid

![在这里插入图片描述][184e9d0c62ec4b268715a4586c17ae9a.png]  
19. 查询每门课程被学习的学生数

# 按课程id分组统计数据量
    SELECT cid,COUNT(1) cnt
    FROM sc
    GROUP BY cid

![在这里插入图片描述][0cb4c52393dd4a3f9b2b7f2ddbe338ca.png]  
20. 查询出只选修两门课程的学生学号和姓名

SELECT a.sid,a.sname,COUNT(1) cnt
    FROM student a
    INNER JOIN sc b
    ON a.sid=b.sid
    GROUP BY sid
    HAVING cnt=2

![在这里插入图片描述][5df90d5725ce41ab89671462d3f5c81a.png]  
21. 查询男生、女生人数

SELECT ssex,COUNT(1) cnt
    FROM student
    GROUP BY ssex

![在这里插入图片描述][0361e233e67049ea861552c36235fdfc.png]  
22. 查询名字中含有「风」字的学生信息

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

![在这里插入图片描述][dd3d457b9d744d7eb6abb0ab7c476b32.png]  
23.查询同名同性学生名单，并统计同名人数

# 查询同名同姓学生名单，并统计同名人数
    SELECT sname,COUNT(1) cnt
    FROM student
    GROUP BY sname
    HAVING cnt>1

![在这里插入图片描述][9de569eb3394418a9127311ccce1dc08.png]

# 查询同名同性学生名单，并统计同名人数
    SELECT a.sname,COUNT(1) cnt
    FROM student a
    INNER JOIN student b
    ON a.sname = b.sname AND a.ssex = b.ssex AND a.sid != b.sid
    GROUP BY a.sname

![在这里插入图片描述][050b295e66ad4e51a7e9e959fc0512f4.png]  
24.查询1990年出生的学生名单

SELECT sname,sage
    FROM student
    WHERE YEAR(sage)=1990

![在这里插入图片描述][be9dfab5f0a94c2680c5e278e1be6444.png]  
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT cid,AVG(score) avg_score
    FROM sc
    GROUP BY cid
    ORDER BY avg_score DESC,cid ASC

![在这里插入图片描述][28d25307ffa94def9d09d284a82362fb.png]  
26.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT a.sid,a.sname,AVG(b.score) avg_score
    FROM student a
    INNER JOIN sc b
    ON a.sid = b.sid
    GROUP BY a.sid
    HAVING avg_score>85

![在这里插入图片描述][c477a11058514150a9d0b64824b303f7.png]  
27.查询课程名称为「数学」，且分数低于60的学生姓名和分数

SELECT
    a.sname,b.score
    FROM student a
    INNER JOIN sc b
    ON a.sid=b.sid
    INNER JOIN course c
    ON b.cid=c.cid AND c.cname='数学'
    WHERE b.score<60

![在这里插入图片描述][1004e6f14d9a4f9981b8073cd05618d3.png]

# 第二种
    SELECT a.sname,b.score
    FROM student a
    INNER JOIN sc b
    ON a.sid=b.sid
    WHERE cid IN (SELECT cid FROM course WHERE cname='数学')
    AND b.score <60

![在这里插入图片描述][779c53d71e044527933cc0cdf5a0fceb.png]  
28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况)

SELECT a.*,b.cid,b.score
    FROM student a
    LEFT JOIN sc b
    ON a.sid = b.sid

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuYWguRg_size_18_color_FFFFFF_t_70_g_se_x_16]  
29. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT a.sname,c.cname,b.score
    FROM student a
    INNER JOIN (
    SELECT sid,cid,score
    FROM sc
    WHERE score>70) b
    ON a.sid = b.sid
    INNER JOIN course c
    ON b.cid=c.cid

![在这里插入图片描述][7b08f9d2c44b49d5806796e3fcf969f3.png]  
30. 查询存在不及格的课程

# 筛选出小于60分的成绩
    SELECT cid
    FROM sc
    WHERE score<60
    # 和课程表关联
    SELECT a.cid,b.cname
    FROM (SELECT cid
    FROM sc
    WHERE score<60) a
    INNER JOIN course b
    ON a.`CId`=b.cid
    GROUP BY a.cid

![在这里插入图片描述][09d166e810dd45a7a7da7baec7695075.png]  
31. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

SELECT sid,sname
    FROM student
    WHERE sid IN(
    SELECT sid
    FROM sc
    WHERE cid=01 AND score>=80)

![在这里插入图片描述][2cfa1452885a4a8d8b1a88b363176b6a.png]

1.  求每门课程的学生人数

SELECT cid,COUNT(1)
    FROM sc
    GROUP BY cid

![在这里插入图片描述][718c4e042f064bdb887702378cbde67c.png]  
33.假设成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

# 连接一个表，in里面两个表，可根据思路分开写
    SELECT a.*,b.score
    FROM student a
    INNER JOIN (
    SELECT sid,score,MAX(score)
    FROM sc
    WHERE cid IN (SELECT cid
    FROM course c
    INNER JOIN teacher t
    ON c.tid=t.tid
    WHERE t.tname='张三')) b
    ON a.sid = b.sid

![在这里插入图片描述][3ee22d8f043d4881891e1c810a61c19e.png]

# 表全部关联再选择
    SELECT b.*,a.score
    FROM sc a
    LEFT JOIN student b
    ON a.sid = b.sid
    LEFT JOIN course c
    ON a.cid=c.cid
    LEFT JOIN teacher d
    ON c.tid=d.tid
    WHERE d.tname = '张三'
    ORDER BY a.score DESC
    LIMIT 1

![在这里插入图片描述][bf2e12f5d0d44411ab72ae5fe5fc0679.png]  
34.假设成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

# 先找出最高成绩，然后找成绩等于这个成绩的学生
    
    SELECT c.sid,MAX(score)
    FROM sc a
    LEFT JOIN course c
    ON a.cid=c.cid
    LEFT JOIN teacher d
    ON c.tid=d.tid
    WHERE d.tname = '张三'
    
    SELECT s.*,ss.score
    FROM student s
    INNER JOIN sc ss
    ON s.sid = ss.sid
    WHERE ss.score =(SELECT MAX(score)
    FROM sc a
    LEFT JOIN course c
    ON a.cid=c.cid
    LEFT JOIN teacher d
    ON c.tid=d.tid
    WHERE d.tname = '张三')

![在这里插入图片描述][d173696acc0b4f79af62e0a1a7bf9f8a.png]  
35.查询同一个学生、不同课程、成绩相同的学生的学生编号、课程编号、学生成绩

SELECT a.sid,a.cid,b.cid,a.score,b.score
    FROM sc a
    INNER JOIN sc b
    ON a.sid = b.sid
    WHERE a.`score`=b.score AND a.cid != b.cid
    GROUP BY a.sid,a.cid

![在这里插入图片描述][7c352b6b8a784fa09c24ccc4a06422ca.png]

36.查询每门功成绩最好的前两名

SELECT a.cid,a.sid,a.cnt
    FROM(
    SELECT cid,sid,rank() over(PARTITION BY cid  ORDER BY score DESC) cnt
    FROM sc) a
    WHERE a.cnt<3

![在这里插入图片描述][422f38ad761b49d5afd822ac5c17de09.png]  
37.统计每门课程的学生选修人数（超过5人的课程才统计)

SELECT cid,COUNT(1) cnt
    FROM sc
    GROUP BY cid
    HAVING cnt>5

![在这里插入图片描述][ab10d3ddace14fb0b830604d0946d8bc.png]

1.  检索至少选修两门课程的学生学号

SELECT sid,COUNT(1) cnt
    FROM sc
    GROUP BY sid
    HAVING cnt>1

![在这里插入图片描述][4b55244282774dbdb6521debb257b657.png]  
39.查询选修了全部课程的学生信息

SELECT a.*
    FROM student a
    INNER JOIN (
    SELECT sid,COUNT(1) cnt
    FROM sc
    GROUP BY sid
    HAVING cnt = (SELECT COUNT(1)
    FROM course
    )) b
    ON a.`SId`=b.sid

![在这里插入图片描述][3e4f435fb9e8401992e90f4e53899b14.png]

SELECT a.*
    FROM student a
    WHERE (SELECT COUNT(1) FROM sc b WHERE a.sid=b.sid)
    =(SELECT COUNT(1) FROM course)

40.查询各学生的年龄，只按年份来算

SELECT sid,sname,YEAR(NOW())-YEAR(sage) age
    FROM student

![在这里插入图片描述][83ae22c2139e4d619e9c20c476657373.png]

41.按照出生日期来算，当前月日<出生年月的月日则，年龄减一

# SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2003-04-01')  #0
    # SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2003-06-01')  #1
    # SELECT TIMESTAMPDIFF(DAY,'2002-05-01','2003-06-01')  # 天数
    SELECT *,TIMESTAMPDIFF(YEAR,sage,NOW())
    FROM student

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuYWguRg_size_20_color_FFFFFF_t_70_g_se_x_16]  
42.查询本月过生日的学生

SELECT sname
    FROM student
    WHERE MONTH(sage) = MONTH(NOW())

43.查询下月过生日的学生

SELECT sname
    FROM student
    WHERE MONTH(sage) = MONTH(NOW())

分组函数和窗口函数

SELECT sid,cid,rank() over(PARTITION BY sid)
    FROM sc

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuYWguRg_size_13_color_FFFFFF_t_70_g_se_x_16]

SELECT sid,cid
    FROM sc
    GROUP BY sid,cid

![在这里插入图片描述][60c970a09c6f4658bf587e1fd066df59.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MjIzNDQ0_size_16_color_FFFFFF_t_70]: /images/20221005/66e2236980fc4098b2521b4dad5469df.png
[20210618162015999.png]: /images/20221005/914cd738247a41bcaf6c467b5c864686.png
[20210618162031466.png]: /images/20221005/fd635329bb034a5490fc8714b25a19ff.png
[20210618162044217.png]: /images/20221005/d112e1250e2247d7acf40431070fc7ae.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuYWguRg_size_10_color_FFFFFF_t_70_g_se_x_16]: /images/20221005/dd11098cb5dc4163b40ea5a182da2a1a.png
[c461ed38ef2a4b0a843372be27bf3903.png]: /images/20221005/477900a84a1e49309bdb24ec14c36c5a.png
[f2abdebbcc364daa8be5acf1df5b6082.png]: /images/20221005/2594fe8954cf4b84ac628bc4710c1d69.png
[c37536f3775c44aa9e4241d810a88cd4.png]: /images/20221005/279d35fb76a34a119aecd81ae1541865.png
[8e62e4c4b9314dec8e55b0be41463126.png]: /images/20221005/195b655da8ab4cfd85a0baec3502a979.png
[df8e547739dd48d7b429eb19c166227a.png]: /images/20221005/ba8395f1c2494d2aad533531ff87d87a.png
[b18d4903032a4451a2cdf950bfd1354e.png]: /images/20221005/0c62df49ceca42f7b6c14e6ada4840f7.png
[113b722534a440539dffaecb6c648287.png]: /images/20221005/ad5f7cb69512466f82a2d642d3d4a3d0.png
[184e9d0c62ec4b268715a4586c17ae9a.png]: /images/20221005/b9bfddc5bbfa4f1485b2dea88984cb19.png
[0cb4c52393dd4a3f9b2b7f2ddbe338ca.png]: /images/20221005/0369154d5f2e4ce4b98c67b50cc6faeb.png
[5df90d5725ce41ab89671462d3f5c81a.png]: /images/20221005/52f095992e3047f48083a7c18cacf445.png
[0361e233e67049ea861552c36235fdfc.png]: /images/20221005/ee8681e667bb48cd8aa8d215f02396ec.png
[dd3d457b9d744d7eb6abb0ab7c476b32.png]: /images/20221005/bab9d9f7189748448134eb480babb7f5.png
[9de569eb3394418a9127311ccce1dc08.png]: /images/20221005/63dc0374c6cd4132a417c8d9a193e1ab.png
[050b295e66ad4e51a7e9e959fc0512f4.png]: /images/20221005/d322f59dc3934d0b8f7386a7fc661040.png
[be9dfab5f0a94c2680c5e278e1be6444.png]: /images/20221005/718607666a2f4388b76efc4c36433f02.png
[28d25307ffa94def9d09d284a82362fb.png]: /images/20221005/7b05bda5e1b7476ea17bf0c205243720.png
[c477a11058514150a9d0b64824b303f7.png]: /images/20221005/bafd9c8e88f546a39ca72fca4d26f623.png
[1004e6f14d9a4f9981b8073cd05618d3.png]: /images/20221005/cbf11186cf964f8884bf3ae8117c0e6c.png
[779c53d71e044527933cc0cdf5a0fceb.png]: /images/20221005/3174d4a51aa046d3998f20bc2e0daa32.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuYWguRg_size_18_color_FFFFFF_t_70_g_se_x_16]: /images/20221005/edea8c45702046ebba88841c72aef5a0.png
[7b08f9d2c44b49d5806796e3fcf969f3.png]: /images/20221005/9d9be848f71a463f8f1680706b532c5b.png
[09d166e810dd45a7a7da7baec7695075.png]: /images/20221005/846ae31e432f4911a6f9ede10341ed45.png
[2cfa1452885a4a8d8b1a88b363176b6a.png]: /images/20221005/66aada41d65546a2896bd2a43f4eb9b9.png
[718c4e042f064bdb887702378cbde67c.png]: /images/20221005/df5e6e1469d047b09f3a106fb43fadb9.png
[3ee22d8f043d4881891e1c810a61c19e.png]: /images/20221005/54a1476df42f43ccaf246a7e02fc6c53.png
[bf2e12f5d0d44411ab72ae5fe5fc0679.png]: /images/20221005/114e646b735d4e9caafedce3738a04d3.png
[d173696acc0b4f79af62e0a1a7bf9f8a.png]: /images/20221005/b10a35395f9e419ca1a77c0086e76589.png
[7c352b6b8a784fa09c24ccc4a06422ca.png]: /images/20221005/51fa8f3a8e0a400eb1c7988ef241ad31.png
[422f38ad761b49d5afd822ac5c17de09.png]: /images/20221005/3b70a4f6738945debcc3c507630e65b5.png
[ab10d3ddace14fb0b830604d0946d8bc.png]: /images/20221005/cbf42be505ea423e9b4f91e5992cc93d.png
[4b55244282774dbdb6521debb257b657.png]: /images/20221005/36fd0da6d01846929d567334a4adc5ae.png
[3e4f435fb9e8401992e90f4e53899b14.png]: /images/20221005/98d28be3fa374607a5d66f19421e4298.png
[83ae22c2139e4d619e9c20c476657373.png]: /images/20221005/73b63a6ff65f45e487925da75bfca521.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuYWguRg_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20221005/110144af713740b98f7f78bf06fda0ff.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGFuYWguRg_size_13_color_FFFFFF_t_70_g_se_x_16]: /images/20221005/211c36ec56084f2da2a1eddaace44411.png
[60c970a09c6f4658bf587e1fd066df59.png]: /images/20221005/3957bea3f4174ad8974c440a85ec0553.png