MySQL综合练习

数据准备

数据表介绍

--1.学生表
Student(SId,Sname,Sage,Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
 
--2.课程表
Course(CId,Cname,TId)
--CId 课程编号,Cname 课程名称,TId 教师编号
 
--3.教师表
Teacher(TId,Tname)
--TId 教师编号,Tname 教师姓名
 
--4.成绩表
SC(SId,CId,score)
--SId 学生编号,CId 课程编号,score 分数

数据表创建

-- 学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
-- 科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 成绩表 SC
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);

开始做题

-- 1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT
    Sname,t1.score,s.SId
FROM
    (SELECT * FROM sc WHERE CId=1) t1,
    (SELECT * FROM sc WHERE CId=2) t2,
    student s
WHERE
    s.SId=t1.SId AND
    t1.SId=t2.SId AND
    t1.score>t2.score;
-- 这里主要是从sc中筛选出课程为01和课程为02的两张表,然后通过join的方式得到后续结果,有这种将筛选后的表作为查询表的思想很重要
-- 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
SELECT
    Sname,
    t1.SId,
    t1.score as '01成绩',
    t2.score as '02成绩'
FROM
    (SELECT * FROM sc WHERE CId=1) t1,
    (SELECT * FROM sc WHERE CId=2) t2,
    student
WHERE
    t1.SId=t2.SId AND
    student.SId=t2.SId;
-- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT
    *
FROM
    (SELECT * FROM sc WHERE CId='01') t1 LEFT JOIN
    (SELECT * FROM sc WHERE CId='02') t2 on t1.SId=t2.SId
    INNER JOIN student s on s.SId=t1.SId;

-- 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT
    *
FROM
    (SELECT * FROM sc WHERE CId='01') t1 RIGHT JOIN
    (SELECT * FROM sc WHERE CId='02') t2 on t1.SId=t2.SId
    INNER JOIN student s ON s.SId=t2.SId;

-- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT
    s.SId,
    Sname,
    round(avg(score),0) as '平均成绩'
FROM sc s1
    INNER JOIN student s ON s1.SId = s.SId
GROUP BY s.SId,Sname HAVING avg(score)>=60;

-- 3.查询在 SC 表存在成绩的学生信息
SELECT
    s.SId,s.Sname
FROM student s
    RIGHT JOIN sc s2 ON s.SId = s2.SId
GROUP BY s.SId,s.Sname;  -- 这是我写的,用两个表做join

SELECT * FROM student WHERE SId IN (SELECT SId FROM sc);  -- 思路一(子查询)
select * from student s, (select DISTINCT sid from sc) t1 where s.SId = t1.SId;-- 思路2: 用连接查询做, 先筛选, 后连接.

-- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
SELECT
    s.SId,
    Sname,
    count(CId),
    sum(score)
FROM sc s1 RIGHT JOIN student s ON s1.SId = s.SId
GROUP BY s.SId, Sname;

-- 4.1显示没选课的学生(显示为NULL)
-- 我写的
SELECT
    *
FROM student LEFT JOIN sc s ON student.SId = s.SId;
-- 改进后的写法
SELECT
    s.SId,
    Sname,
    count(CId),
    sum(score)
FROM sc s1 RIGHT JOIN student s ON s1.SId = s.SId
GROUP BY s.SId, Sname;

-- 4.2查有成绩的学生信息
SELECT
    s.SId,
    s.Sname,
    count(CId),
    sum(score)
FROM sc s1 LEFT JOIN student s ON s1.SId = s.SId
GROUP BY s.SId,s.Sname;

-- 5.查询「李」姓老师的数量
SELECT
    count(TId)
FROM teacher WHERE Tname LIKE '李%';

-- 6.查询学过「张三」老师授课的同学的信息
SELECT
    s2.*
FROM teacher t INNER JOIN course c ON t.TId = c.TId
INNER JOIN sc s ON c.CId = s.CId
INNER JOIN student s2 ON s.SId = s2.SId
WHERE Tname='张三';

-- 7.查询没有学全所有课程的同学的信息
SELECT
    s.SId,
    Sname,
    Sage,
    count(CId)
FROM sc s INNER JOIN student s2 ON s.SId = s2.SId
GROUP BY s.SId,Sname,Sage HAVING count(CId)<(SELECT count(cid) FROM course);

-- *8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-- 子查询,还是思想,首先找出学号为01同学所学的课程
SELECT CId FROM sc WHERE SId='01';  -- 先查询01号同学选的课程
SELECT DISTINCT SId FROM sc WHERE CId IN (SELECT CId FROM sc WHERE SId='01') AND SId!='01'; -- 找出与至少一门课程与01号同学相同同学的sid
SELECT
    s.*
FROM student s,
    (SELECT DISTINCT SId FROM sc WHERE CId IN (SELECT CId FROM sc WHERE SId='01') AND SId!='01') t1
WHERE s.SId=t1.SId;  -- 根据sid找出几位同学的具体信息

-- *9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
-- 解法一(通过选课数量来解决问题,有瑕疵,这里就不写了

-- 解法二(通过选课的cid来匹配group_contact
SELECT group_concat(CId) FROM sc GROUP BY SId HAVING SId='01';  -- 查看01同学选课内容
SELECT
    *
FROM
    (SELECT group_concat(CId) c1 FROM sc GROUP BY SId HAVING SId='01') t1, -- 查看01同学选课内容
    (SELECT SId,group_concat(CId) c2 FROM sc GROUP BY SId HAVING SId!='01') t2,  -- 查看非01同学选课内容,id
    student s
WHERE t1.c1=t2.c2 AND t2.SId=s.SId;  -- 非01同学选课内容和01同学选课内容相同,再和student表建立连接

-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT
    *
FROM student WHERE SId NOT IN (
    SELECT
        SId
    FROM teacher INNER JOIN course c ON teacher.TId = c.TId
    INNER JOIN sc s ON c.CId = s.CId
    WHERE Tname='张三'
);
-- 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT
    s.SId,
    Sname,
    round(avg(score),0) '平均成绩',
    count(CASE WHEN score <60 THEN 1 END) as unreach_60
FROM sc INNER JOIN student s ON sc.SId = s.SId
GROUP BY SId,Sname HAVING unreach_60>=2;

-- 12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT
    s2.*
FROM sc s1,student s2 WHERE s1.SId=s2.SId AND s1.CId='01' AND s1.score<60 ORDER BY score DESC ;

-- *13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
    *,
    avg(score) OVER (PARTITION BY SId) avg_score
FROM sc ORDER BY avg_score DESC ;

-- *14.查询各科成绩最高分、最低分和平均分
-- 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT
    sc.CId,
    Cname,
    max(score),
    min(score),
    avg(score),
    count(CASE WHEN score>=90 THEN 1 END) AS '优秀',
    count(CASE WHEN score>=80 AND score<90 THEN 1 END) AS '优良',
    count(CASE WHEN score>=70 AND score<80 THEN 1 END) AS '中等',
    count(CASE WHEN score>=60 AND score<70 THEN 1 END) AS '及格',
    count(CASE WHEN score>=90 THEN 1 END)/sum(c.CId) as '优秀率',
    count(CASE WHEN score>=80 AND score<90 THEN 1 END)/sum(c.CId) as '优良率',
    count(CASE WHEN score>=70 AND score<80 THEN 1 END)/sum(c.CId) as '中等率',
    count(CASE WHEN score>=60 AND score<70 THEN 1 END)/sum(c.CId) as '及格率'
FROM sc INNER JOIN course c ON sc.CId = c.CId GROUP BY CId,Cname;

-- * 15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
SELECT
    *,
    rank() OVER (PARTITION BY CId ORDER BY score DESC ) as drk
FROM sc;
-- 16.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT SId,sum(score) FROM sc GROUP BY SId;  -- 先将总成绩和学号拎出来

SELECT
    t1.Sid,
    dense_rank() OVER (ORDER BY t1.sum DESC ) as '名次'
FROM (SELECT SId,sum(score) sum FROM sc GROUP BY SId) t1;  -- 再将总成绩进行排序

-- 17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT
    count(CASE WHEN score<=100 AND score>85 THEN 1 END ) as A,
    count(CASE WHEN score<=85 AND score>70 THEN 1 END ) as B,
    count(CASE WHEN score<=70 AND score>60 THEN 1 END ) as C,
    count(CASE WHEN score<=60 AND score>0 THEN 1 END ) as D,
    count(CASE WHEN score<=100 AND score>85 THEN 1 END )/count(1) as 'A占比',
    count(CASE WHEN score<=85 AND score>70 THEN 1 END )/count(1) as 'B占比',
    count(CASE WHEN score<=70 AND score>60 THEN 1 END )/count(1) as 'C占比',
    count(CASE WHEN score<=60 AND score>0 THEN 1 END )/count(1) as 'D占比'
FROM sc GROUP BY CId;

-- 18. 查询各科成绩前三名的记录 (这个应该是分组求Top是窗口函数的典型应用)
-- 需要用到CET表达式,
-- select dense_rank() over (partition by Cid order by score desc) drk from sc where drk <= 4;
-- 报错,因为where只能筛选表中已经存在的列
WITH a AS (  SELECT CId, dense_rank() OVER (PARTITION BY CId ORDER BY score DESC ) drk FROM sc)
SELECT * FROM a WHERE drk<=3;

-- 19.查询每门课程被选修的学生数
SELECT
    CId,
    COUNT(SId)
FROM sc GROUP BY CId;

-- 20.查询出只选修两门课程的学生学号和姓名
SELECT
    SId,
    count(CId)
FROM sc GROUP BY SId HAVING count(CId)=2;

-- 21.查询男生、女生人数
SELECT
    Ssex,
    count(SId)
FROM student GROUP BY Ssex;

-- 22.查询名字中含有「风」字的学生信息
SELECT
    *
FROM student WHERE Sname LIKE '%风%';

-- 23.查询同名学生名单，并统计同名人数
 -- 初步设想
SELECT
    s1.SId,
    s1.Sname,
    count(s1.Sname)
FROM student s1,student s2 WHERE s1.Sname=s2.Sname AND s1.SId!=s2.SId GROUP BY s1.SId, s1.Sname;
-- 另一种想法
SELECT
    Sname
FROM student GROUP BY Sname HAVING count(Sname)>1;
-- 最终版
SELECT *
FROM student s,(SELECT Sname,count(*) FROM student GROUP BY Sname HAVING count(Sname)>1) t1
WHERE s.Sname=t1.Sname;

-- 24.查询 1990 年出生的学生名单
SELECT
    *
FROM student WHERE year(Sage)='1990';

-- 25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
-- 初步设想,麻烦了
SELECT
    CId,
    dense_rank() OVER (ORDER BY avg DESC) as '平均成绩排序'
FROM (SELECT
    CId,
    avg(score) avg
FROM sc GROUP BY CId) t1;
-- 不用子查询的方法,简单
SELECT
    CId,
    round(avg(score),2) avg
FROM sc GROUP BY CId ORDER BY avg DESC ,CId;

-- 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT
    s.SId,
    s.Sname,
    avg(score)
FROM sc INNER JOIN student s ON sc.SId = s.SId GROUP BY s.SId,s.Sname HAVING avg(score)>=85;

-- 27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT
    Sname,
    score
FROM course INNER JOIN sc s ON course.CId = s.CId
INNER JOIN student s2 ON s.SId = s2.SId WHERE Cname='数学' AND score<60;

-- 28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT
    Sname,
    s.SId,
    CId,
    score
FROM student LEFT JOIN sc s ON student.SId = s.SId;

-- 29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
-- 理解一是任意一门成绩均在70分以上
SELECT
    SId,
    min(score) min
FROM sc GROUP BY SId HAVING min>70;
-- 理解二是存在一门成绩在70分以上即可满足条件
SELECT DISTINCT
    Sname,
    Cname,
    score
FROM sc INNER JOIN student s ON sc.SId = s.SId
INNER JOIN course c ON sc.CId = c.CId
WHERE score>70;

-- 30.查询存在不及格的课程
SELECT
    CId,
    min(score) min
FROM sc GROUP BY CId HAVING min<60;

-- 31.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
SELECT
    s1.SId,
    Sname
FROM sc s1 INNER JOIN student s2 ON s1.SId=s2.SId WHERE CId='01' AND score>=80;

-- 32.求每门课程的学生人数
SELECT
    CId,
    count(SId) as '学生人数'
FROM sc GROUP BY CId;
-- 33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT
    *
FROM (SELECT
    Sname,
    score,
    Sage,
    Ssex,
    s2.SId,
    dense_rank() OVER (PARTITION BY c.CId ORDER BY score DESC ) drk
FROM teacher INNER JOIN course c ON teacher.TId = c.TId
INNER JOIN sc s ON c.CId = s.CId
INNER JOIN student s2 ON s.SId = s2.SId
WHERE Tname='张三') t1 WHERE drk=1;

-- 34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT
    *
FROM (SELECT
    Sname,
    score,
    Sage,
    Ssex,
    s2.SId,
    rank() OVER (PARTITION BY c.CId ORDER BY score DESC ) rk
FROM teacher INNER JOIN course c ON teacher.TId = c.TId
INNER JOIN sc s ON c.CId = s.CId
INNER JOIN student s2 ON s.SId = s2.SId
WHERE Tname='张三') t1 WHERE rk=1;

-- 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-- 这个问题其实一开始没太明白啥意思，后来理解为某个人的几科分数是一样的，需要把这个人找出来
SELECT DISTINCT
    s1.SId
FROM sc s1, sc s2 WHERE s1.score=s2.score AND s1.CId!=s2.CId AND s1.SId=s2.SId;

-- 36.查询每门功成绩最好的前两名(又是分组求topN
SELECT
    t1.SId,
    t1.CId,
    t1.drk
FROM (
    SELECT CId,SId,dense_rank() OVER (PARTITION BY CId ORDER BY score DESC ) drk FROM sc) t1 WHERE drk<=2;

-- 37.统计每门课程的学生选修人数（超过 5 人的课程才统计）
SELECT
    CId,
    count(SId) as '选课人数'
FROM sc GROUP BY CId HAVING count(SId)>=5;

-- 38.检索至少选修两门课程的学生学号
SELECT
    SId,
    count(CId) '选课数量'
FROM sc GROUP BY SId HAVING count(CId)>=2;

-- 39.查询选修了全部课程的学生信息
SELECT
    s.sid,Sname
FROM sc INNER JOIN student s ON sc.SId = s.SId
GROUP BY SId,Sname HAVING count(CId)=(SELECT count(CId) FROM course );

-- 40.查询各学生的年龄，只按年份来算
SELECT
    S.*,
    year(now())-year(Sage) as '年龄'
FROM student S;

-- 41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
-- timestampdiff(单位, 开始时间, 结束时间)
select *, timestampdiff(year, Sage, now()) age from student;

-- 42.查询本周过生日的学生
-- 有点复杂，需要拼接出本周的起止日期
SELECT
    Sname
FROM student WHERE week(Sage)=week(now());
-- 老师给的答案感觉有点麻烦
-- 细节1: 把学生的出生年月日, 拼接成 今年的年份-学生出生的月份-学生出生的天, 判断这个时间是否在本周即可.
-- 细节2: 记得把你获取到的各种时间都用 date()函数 转成日期对象即可.
select * from student where date(concat(year(now()), '-', month(Sage), '-', day(sage)))
    BETWEEN
        date(date_sub(now(), INTERVAL weekday(now()) day))
    and
        date(date_add(now(), INTERVAL 6 - weekday(now()) day));
-- 43. 查询下周过生日的学生
-- 同42
SELECT
    Sname,Sage
FROM student WHERE week(Sage)=week(now())+1;

-- 44.查询本月过生日的学生
SELECT
    Sname,Sage
FROM student WHERE month(Sage)=month(now());

-- 45.查询下月过生日的学生
-- 注意本月是12月的话，下一个月份是1即可
SELECT
    Sname,Sage
FROM student WHERE month(Sage)=month(now())+1;