MYSQL 入门经典练习

本练习难度为入门级别，全部在mysql环境下运行，欢迎练习使用！

 

-- 创建学生表
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-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("08","吴菊","1990-01-20","女");

-- 创建课程表
create table course(
CID VARCHAR(10),
Cname varchar(10),
TID VARCHAR(10)
);

-- 插入课程数据
insert into course values('01','语文','02');
insert into course values('02','数学','01');
insert into course values('03','英语','03');

-- 创建教师表
create table Teacher(
TID VARCHAR(10),
Tname varchar(10)
);

-- 插入测试数据
insert into Teacher values('01','张三');
insert into Teacher values('02','李四');
insert into Teacher values('03','王五');

-- 创建成绩表
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);
insert into sc values("08","01",80);
insert into sc values("08","02",90);

-- 题目1
-- 查询01课程比02课程成绩高的学生信息及分数
-- 方法一
select a.* ,b.score
from student a join sc b on a.SID = b.SID JOIN sc c on a.SID = c.SID
WHERE b.CID = '01' AND c.CID = '02' AND b.score > c.score;
-- 方法二
select a.* ,b.score
from student a
join (select * from sc where CID = '01') b on a.SID = b.SID
JOIN (SELECT * FROM sc where CID = '02') c on b.SID = c.SID
WHERE b.score > c.score;

-- 题目2
-- 查询同时存在01课程和02课程的学生信息
-- 有没有选这门课，看成绩表
-- 方法一
select a.*
from student a
join sc b on a.SID = b.SID
JOIN sc c on a.SID = c.SID
where b.CID = '01' and c.CID = '02';
-- 方法二
select a.* 
from student a
join (select * from sc where CID = '01') b on a.SID = b.SID
JOIN (SELECT * FROM sc where CID = '02') c on b.SID = c.SID;

-- 题目3：查询存在课程1，但是不存在课程2的学生的成绩信息
select *
from sc
where CID='01' AND SID not in (select SID from sc where CID = '02');

-- 题目4：查询存在课程1但是可能不存量课程2的学生的成绩信息
select a.* 
from (select * from sc where CID = '01') a
left join (select * from sc where CID = '02') b on a.SID = b.SID;

-- 题目5：查询平均成绩大于60分的学生编号，学生姓名，平均成绩
select a.SID, b.Sname, a.平均成绩
from (select SID, AVG(score) 平均成绩 from sc group by SID having AVG(score) >60) a
left join student b on a.SID = b.SID;

-- 题目6：查询所有同学的学生编号、学生姓名、选课总数，所有课程的总成绩
select a.SID 学生编号, a.Sname 学生姓名, b.课程数量, b.总成绩
from student a left join (select sc.SID, count(sc.CID) 课程数量,sum(sc.score) 总成绩 FROM sc group by sc.SID) b on a.SID = b.SID;

-- 题目7：查询有成绩的学生信息
select * 
from student
where SID IN(SELECT DISTINCT(SID) FROM sc);

-- 题目8：查询‘李’姓老师的数量
select count(*) from teacher where Tname like '李%';

-- 题目9：查询学过张三老师教授课程的学生
select * 
from student 
where SID IN (SELECT DISTINCT(SID) FROM sc a left join course b on a.CID = b.CID left join teacher c on b.TID =c.TID WHERE c.Tname = '张三');

-- 题目10：查询没有学全所有课程的学生信息
select * 
from student
where SID IN
(select SID FROM sc group by SID having count(CID) < (select count(distinct(CID)) FROM sc)); 
-- 使用聚合函数时，包含聚合函数的表达式或字段可以不用显示

-- 题目11：查询和学号01的同学学习课程完全相同的同学
-- 方法一：由课程表可知，01同学学习了3门课程，而最多课程也只有3门，如果01同学没有同时学习所有课程则此方法无效
SELECT * FROM student where SID IN(
select SID FROM sc where CID IN(
select distinct CID from sc where SID='05') AND SID <>'05' GROUP BY SID HAVING COUNT(CID) = 2);
-- 方法二：本方法为正确方法
select a1.SID FROM 
(select a.SID ,group_CONCAT(a.CID) 所有课程 FROM (SELECT SID,CID FROM sc ORDER BY CID) a group by a.SID) a1 WHERE a1.所有课程= (select c.所有课程 from (select b.SID,group_CONCAT(b.CID) 所有课程 FROM (select SID,CID FROM sc ORDER BY CID) b GROUP BY SID) c WHERE c.SID = '01') and a1.SID <> '01';

-- 题目12：查询至少有1门课程和01同学所学课程相同的学生
select DISTINCT SID FROM SC WHERE CID IN( 
select CID FROM sc where SID='01');

-- 题目13：查询没学过张三老师课程的学生
-- 张三老师教的CID是02课程，是数学，先把上过数学的学生找出来，在从学生表中找到没有上过数学的学生
SELECT * FROM student
where SID NOT IN(
select DISTINCT a.SID
FROM sc a LEFT join course b ON a.CID = b.CID LEFT JOIN teacher c on b.TID =c.TID where c.Tname = '张三');

-- 题目13：查询两门及以上不及格的学生信息和平均成绩
select a.SID,a.Sname,b.平均成绩
from 
(select SID,AVG(score) 平均成绩 from sc where score < 60 GROUP BY SID HAVING COUNT(score) >= 2) b left join student a on a.SID = b.SID;

-- 题目14：查询01课程小于60分的学生信息，并按成绩降序
select a.* ,b.score 
from student a 
join sc b on a.SID = b.SID
where CID = '01' AND score < 60
order by score desc;

-- 题目15：按照平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select SID,
MAX(case CID when '01' then score else 0 end) '01',
MAX(case CID when '02' then score else 0 end) '02',
MAX(case CID when '03' then score else 0 end) '03',
avg(score) 平均分 from sc
group by SID order by 平均分 DESC;

-- 题目16：查询各科成绩最高分、最低分、和平均分；输出格式为CID,Cname,最高分，最低分，平均分，不及格，及格，优良率，优秀率。不及格[0,60)，及格[60,70)，中等[70,80)，良[80,90)，优秀[90,100]
select c.CID,b.Cname 课程名称, c.最高分,c.最低分,c.平均分,d.不及格,d.及格,d.中等,d.优良,d.优秀 from 
(select distinct a.CID,max(a.score) 最高分,min(a.score) 最低分,avg(a.score) 平均分 
from sc a GROUP BY a.CID) c left join course b on c.CID = b.CID
left join 
(select CID,
count(distinct(case when score <60 then SID END)) 不及格,
count(distinct(case when score >=60 and score <70 then SID END)) 及格,
count(distinct(case when score >=70 and score <80 then SID END)) 中等,
count(distinct(case when score >=80 and score <90 then SID END)) 优良,
count(distinct(case when score >=90 and score <=100 then SID END)) 优秀
FROM SC GROUP BY CID) d on c.CID = d.CID;
-- * 此处可以不使用distinct()

-- 题目17：按各科成绩排序，并显示排名,score重复时保留名词空缺
select 
    CID,
    SID,
    score,
    rank() over(
        PARTITION BY CID 
        order by score desc) 排名 
from 
    sc ;
-- *partition by CID 分区
-- *如果成绩分别为100,100,99，则输出排名为1，1,3

-- 题目18：按各科成绩排序，并显示排名,score重复时合并名次
select 
    CID,
    SID,
    score,
    dense_rank() over(
        PARTITION BY CID 
        order by score desc) 排名 
from 
    sc ;
-- *如果成绩分别为100,100,99，则输出排名为1，1,2,没有第三名

-- 题目19：查询学生的总成绩，并进行排名，总分重复时，保留名次空缺
select A.*,rank() over(order by A.总成绩 desc) 排名
from
(select SID,SUM(score) 总成绩 from sc group by SID) A;

-- 题目20：查询各科成绩前三名的记录
select * from
(select *, rank() over (partition by CID ORDER BY score desc) 名次 from sc) b where b.名次 <=3;

-- 题目21：查询每门课程选修的学生数
select CID,count(SID) 学生数 from sc group by CID;

-- 题目22：查询出之选修两门课程的学生信息
select * from student where SID IN
(select SID FROM sc group by SID HAVING COUNT(CID) =2) 

-- 题目23：查询男女生的数量
select Ssex,COUNT(SID) 人数 from student group by Ssex;

-- 题目24：查询1990年出生的学生
select * from student where year(Sage) = "1990";
-- * 此处1990是数字格式和字符串格式都可以

-- 题目25：查询没门课程的平均成绩，结果按照平均成绩降序，平均成绩相同的，按照课程编号升序排序
select CID,AVG(score) 平均成绩 from sc group by CID ORDER BY 平均成绩 desc,CID;

-- 题目26：成绩不重复的情况，张三老师课的最高成绩及学生信息
select  * 
from sc
where CID = (select CID FROM course WHERE TID = (select TID from teacher where Tname = "张三"))order by score DESC LIMIT 1
-- *TOP不适用

-- 题目27：成绩有重复的前提下，张三老师的最高成绩及学生信息
select b.* 
from (select *,dense_rank() over(order by score desc) 排名
            from sc where CID = (select CID FROM course WHERE TID = (select TID from teacher where Tname = "张三"))) b where b.排名 = 1;
-- * 借助dense_rank函数实现

-- 题目28：查询没门课程成绩最好的两名学生
select * from 
(select *,row_number() over(partition by CID ORDER BY score desc) 排名
from sc) a where a.排名 <=2;
-- * 借助row_number 排序

-----  row_number在排名时序号连续不重复，即使遇到表中的两个3时
----- 亦如此（在使用row_number实现分页时需要特别注意一点，over
----- 子句中的order by 要与Sql排序记录中的order by 保持一致，否则
----- 得到的序号可能不是连续的）
-- rank函数会把要求排序的值相同的归为一组且每组序号一样，排序不会连续
-- dense_rank排序是连续的，也会把相同的值分为一组且每组排序号一样
-- NTILE(group_num)将所有记录分成group_num个组，每组序号一样

-- 题目29： 以年计算学生的年龄
SELECT SID,TIMESTAMPDIFF(YEAR,Sage,DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')) 年龄 from student
-- * mysql中datediff只能计算天数，不能直接求出除天以外的日期差