MySQL15道经典练习题

前言：本期博客就不标明目录了，我们直接上题目，这一期内容主要是唤醒哪些沉睡在大家心中的之前的SqlServer与oralc的知识点，以及巩固我们的MySQL，其中较难的知识点可能就是联表了，什么内联，外联，左联右联让人头皮发麻，但是不要慌，跟着小刘的步伐慢慢理清思路

首先是我们的建表，这里已经把需要的表的代码都交代上了

一共四张表,学生，教师，成绩，课程

CREATE TABLE t_student(
sid VARCHAr(10),
sname varchar(10),
sage date,
ssex CHAR
)

create table t_teacher(
tid VARCHAr(10),
tname VARCHAr(10)
)

create table t_course(
cid VARCHAr(10),
cname varchar(10),
tid VARCHAr(10)
)

create table t_score(
sid varchar(10),
cid varchar(10),
score float
)
select * from t_student
select * from t_teacher
select * from t_course
select * from t_score


-- 学生表
insert into t_student values('01' , '赵雷' , '1990-01-01' , '男');
insert into t_student values('02' , '钱电' , '1990-12-21' , '男');
insert into t_student values('03' , '孙风' , '1990-12-20' , '男');
insert into t_student values('04' , '李云' , '1990-12-06' , '男');
insert into t_student values('05' , '周梅' , '1991-12-01' , '女');
insert into t_student values('06' , '吴兰' , '1992-01-01' , '女');
insert into t_student values('07' , '郑竹' , '1989-01-01' , '女');
insert into t_student values('09' , '张三' , '2017-12-20' , '女');
insert into t_student values('10' , '李四' , '2017-12-25' , '女');
insert into t_student values('11' , '李四' , '2012-06-06' , '女');
insert into t_student values('12' , '赵六' , '2013-06-13' , '女');
insert into t_student values('13' , '孙七' , '2014-06-01' , '女');

-- 教师表
insert into t_teacher values('01' , '张三');
insert into t_teacher values('02' , '李四');
insert into t_teacher values('03' , '王五');

-- 课程表
insert into t_course values('01' , '语文' , '02');
insert into t_course values('02' , '数学' , '01');
insert into t_course values('03' , '英语' , '03');

-- 成绩表
insert into t_score values('01' , '01' , 80);
insert into t_score values('01' , '02' , 90);
insert into t_score values('01' , '03' , 99);
insert into t_score values('02' , '01' , 70);
insert into t_score values('02' , '02' , 60);
insert into t_score values('02' , '03' , 80);
insert into t_score values('03' , '01' , 80);
insert into t_score values('03' , '02' , 80);
insert into t_score values('03' , '03' , 80);
insert into t_score values('04' , '01' , 50);
insert into t_score values('04' , '02' , 30);
insert into t_score values('04' , '03' , 20);
insert into t_score values('05' , '01' , 76);
insert into t_score values('05' , '02' , 87);
insert into t_score values('06' , '01' , 31);
insert into t_score values('06' , '03' , 34);
insert into t_score values('07' , '02' , 89);
insert into t_score values('07' , '03' , 98);

 然后来看看我们的题目：

01）查询" 01 "课程比" 02 "成绩高的学生的信息及课程分数
02）查询同时存在" 01 "课程和" 02 "课程的情况
03）查询存在" 01 "课程但可能不存在" 02 "课程的情况不存在时显示为 null )
04）查询不存在" 01 "课程但存在" 02 "课程的情况
05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
06）查询在t_score表存在成绩的学生信息
07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
08）查询「李」姓老师的数量

09）查询学过「张三」老师授课的同学的信息
10）查询没有学全所有课程的同学的信息
11）查询没学过"张三"老师讲授的任一门课程的学生姓名
12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息
14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
15）查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

 接下来就是我们的解题部分：

01）查询" 01 "课程比" 02 "成绩高的学生的信息及课程分数
语句：

select a.*,b.score 语文,c.score 数学 from t_student as a
inner JOIN t_score as b
on a.sid = b.sid
inner join t_score as c
on a.sid=c.sid and b.cid='01' and c.cid='02'
where b.score>c.score

结果：

02）查询同时存在" 01 "课程和" 02 "课程的情况
语句：

select * from t_score as a inner join t_score as b on a.sid=b.sid where a.cid='01' and b.cid='02'

结果：

 03）查询存在" 01 "课程但可能不存在" 02 "课程的情况不存在时显示为 null )

语句：

select * from t_score as a inner join t_score as b on a.sid=b.sid and b.cid='02' where a.cid='01'

结果:

 04）查询不存在" 01 "课程但存在" 02 "课程的情况
语句：

select * from t_score where sid not in (select sid from t_score  where cid ='01' ) and cid ='02'

 结果：

05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
语句：

select a.sid,a.sname,avg_cj from t_student as a 
inner join(
select sid, AVG(score) As avg_cj
from t_score
group by sid
having AVG(score) >=60) as b on a.sid=b.sid;

结果：

06）查询在t_score表存在成绩的学生信息

语句：

select  a.sid,a.sname,b.ct,b.sum_score
from t_student as a
left join (select sid,count(cid) as ct,sum(score) as sum_score
from t_score
group by sid)as b on a.sid = b.sid;
)

结果：

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

select  a.sid,a.sname,b.ct,b.sum_score
from t_student as a
left join (select sid,count(cid) as ct,sum(score) as sum_score
from t_score
group by sid)as b on a.sid = b.sid;
)

结果：

08）查询「李」姓老师的数量
语句：

select count(*) from t_teacher where tname like'李%';

结果：

09）查询学过「张三」老师授课的同学的信息
语句： 

select a.*,c.tname  from t_student a , t_score b ,t_teacher c ,t_course d where a.sid=b.sid and b.cid = d.cid and d.tid = c.tid and b.cid='01'

结果：

10）查询没有学全所有课程的同学的信息
语句：

select  a.*,ct
from t_student as a
inner join (select sid,count(cid) as ct
                        from t_score
                        group by sid
                        having ct <(select count(cid) from t_course))as b on a.sid = b.sid
)

结果：

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

语句：

SELECt Sname
FROM t_student AS a
WHERe SId NOT IN (SELECt SId
                  FROM t_score AS a
                  LEFT JOIN t_course AS b
                  ON a.CId = b.CId
                  INNER JOIN t_teacher AS c
                  ON b.TId = c.TId
                  WHERe Tname = '张三');

结果：

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

语句：

SELECt a.Sid,a.Sname,AVG(c.score) AS avg_score 
FROM t_student AS a 
INNER JOIN (SELECt Sid,COUNT(Cid)AS num 
	        FROM t_score
	        WHERe score < 60 
	        GROUP BY Sid 
	        HAVINg num >= 2)AS b 
ON a.Sid = b.Sid 
INNER JOIN t_score AS c 
ON b.Sid = c.Sid 
GROUP BY a.Sid;

结果：

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

语句：

SELECt b.*,a.score
FROM(SELECt SId,score
     FROM t_score
     WHERe CId = '01' AND score < 60) AS a
LEFT JOIN t_student AS b
ON a.SId = b.SId
ORDER BY a.score DESC;

结果：

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

语句：

SELECt b.*,a.score
FROM(SELECt SId,score
     FROM t_score
     WHERe CId = '01' AND score < 60) AS a
LEFT JOIN t_student AS b
ON a.SId = b.SId
ORDER BY a.score DESC;

结果：

15）查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

语句：

SELECt a.*,b.Cname
FROM(SELECt 
     CId,
     COUNT(*)   AS 选修人数,
     MAX(score) AS 最高分,
     MIN(score) AS 最低分,
     AVG(score) AS 平均分,
     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 t_score
     GROUP BY CId
     ORDER BY COUNT(*) DESC,CId ASC) AS a
LEFT JOIN t_course AS b
ON a.CId = b.CId;

结果：

 ok,今日的练习就到此结束了，明天也要继续加油哦！