SQL-mysql环境中的练习题及答案与分析

一，建表、插入数据

首先，我们在test2库中新建一些表并插入数据：

--建表
--学生表
CREATE TABLE `student`(
	`s_id` VARCHAR(20),
	`s_name` VARCHAR(20) NOT NULL DEFAULT '',
	`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
	`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
	PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `course`(
	`c_id`  VARCHAR(20),
	`c_name` VARCHAR(20) NOT NULL DEFAULT '',
	`t_id` VARCHAR(20) NOT NULL,
	PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `teacher`(
	`t_id` VARCHAR(20),
	`t_name` VARCHAR(20) NOT NULL DEFAULT '',
	PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `score`(
	`s_id` VARCHAR(20),
	`c_id`  VARCHAR(20),
	`s_score` INT(3),
	PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
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' , '女');
--课程表测试数据
insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');

--教师表测试数据
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');

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

这里注意到表明和字段名用了``符号，注意这里的``是tab键上面的英文符号，不是单引号！

``在mysql中一般加与不加是一样的，但是``的作用是为了避免与关键字冲突，例如你想给一个表取名from（当然，一般不提倡这么做），加了``以后就不会报语法错误了。mysql会将from认为是一个列名。如下：

当然，为表明和字段名加上``，一定程度上也可以增加sql语句的可读性。

NOT NULL DEFAULT ''或NOT NULL DEFAULT 0的作用会将没有的值设为''或者0，避免出现NULL。NULL会给比较或者与其它数据进行运算时造成麻烦，而且NOT NULL DEFAULT会禁止插入NULL。

数据不多，我们可以直接打印一下完整的表看一看。

1，表student

2，表teacher

3，表course

4，表score

二，练习题、答案，运行效果

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

比较常规，student连接score，过滤出课程01比02高的就可以了。

select a.* ,b.s_score as 01_score,c.s_score as 02_score from 
student a 
join score b on a.s_id=b.s_id and b.c_id='01'
left join score c on a.s_id=c.s_id and c.c_id='02' or c.c_id = NULL 
where b.s_score>c.s_score;

--也可以这样写
select a.*,b.s_score as 01_score,c.s_score as 02_score 
from student a,score b,score c 
where a.s_id=b.s_id 
and a.s_id=c.s_id 
and b.c_id='01' 
and c.c_id='02' 
and b.s_score>c.s_score;

这里左连接score时加了一句c.c_id=NULL，这是因为左连接时，右表中没有的会置为NULL，但对于我们输入的表来说，有没有这句没有区别。

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

跟上一题几乎一样

select a.* ,b.s_score as 01_score,c.s_score as 02_score from
student a left join score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL 
join score c on a.s_id=c.s_id and c.c_id='02' where b.s_score<c.s_score;

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

典型的分组查询

select a.s_id,a.s_name,round(AVG(b.s_score),2) as avg_score from 
student a 
join score b on a.s_id = b.s_id
group by a.s_id,a.s_name having avg_score >=60;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

根据需要的select出的信息，选择各自表中的对应部分，然后通过过滤，得到结果。

这里唯一的难点是成绩不存在的情况，后面有进行分析。

select b.s_id,b.s_name,round(avg(a.s_score),2) as avg_score from
student b 
left join score a on b.s_id = a.s_id
group by b.s_id,b.s_name having avg_score <60
union
select a.s_id,a.s_name,0 as avg_score from 
student a 
where a.s_id not in (select distinct s_id from score);

这部分我第一次写就做错了，没有考虑到成绩不存在的情况！

直接left join，不会有王菊的成绩，这是因为王菊的成绩为NULL，不是0！

我们尝试把人和成绩都打印出来看看：

select a.s_id,a.s_name,group_concat(b.c_id,':',b.s_score) as score from 
student a 
left join score b on a.s_id=b.s_id group by a.s_id;

原来如此，王菊其实是缺考的，没有成绩，所以需要union，把成绩不在score表上的合并上来。

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

这题比较简单，只需要从对应的表取对应的数据就行了。

select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from 
student a 
left join score b on a.s_id=b.s_id
group by a.s_id,a.s_name;

如果不想结果中出现NULL，可以用IFNULL，将NULL替换：

select a.s_id,a.s_name,count(b.c_id) as sum_course,ifnull(sum(b.s_score),0) as sum_score from 
student a 
left join score b on a.s_id=b.s_id
group by a.s_id,a.s_name;

6、查询"李"姓老师的数量

“李”姓老师，说明要用到通配符%，count李开头的教师名就可以了。

select count(t_id) from teacher where t_name like '李%';

7、查询学过"张三"老师授课的同学的信息

select a.* from student a 
join score b on a.s_id=b.s_id where b.c_id in(
select c_id from course 
where t_id =(select t_id from teacher where t_name = '张三'));

这个问题的难点在于找到‘张三’老师带的所有课。需要通过score表，将学生和老师对应起来。

8、查询没学过"张三"老师授课的同学的信息

这题逻辑主要和上一题相反，需要通过score表，将与‘张三’相关的学生id给剔除掉。

select c.* from student c 
where c.s_id not in(select a.s_id from student a 
join score b on a.s_id=b.s_id where b.c_id in 
(select a.c_id from course a 
join teacher b on a.t_id = b.t_id 
where t_name ='张三'));

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select a.* from student a,score b,score c 
where a.s_id = b.s_id  
and a.s_id = c.s_id 
and b.c_id='01' and c.c_id='02';

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

学生的课程信息在score，只需要从score中选出对应的有01而没有02的s_id就可以了。

select a.* from student a 
where a.s_id in 
(select s_id from score where c_id='01') 
and a.s_id not in
(select s_id from score where c_id='02');

我第一次写错的错误写法是这样的：

select a.* from student a  
where a.s_id in  
(select s_id from score where c_id='01' and c_id!='02');

这样的结果是这样的：

显然，select s_id from score where c_id='01' and c_id!='02'并不能把含有02的过滤掉，选出的是s_id对应的c_id中有01，且有c_id不是02的，所有只要“有01”或者“有01和03”就能通过，不能把02过滤掉。

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

“没有学全”意味着有些课没有成绩，所需需要从学生有成绩的课程数和总课程数进行比较，那么可以这么写：

select a.* from student a
left join score b on a.s_id=b.s_id
group by a.s_id having count(b.c_id)<(select count(*) from course);

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

 “至少有一门”则需要选出学号01的同学学的所有课程的集合，然后选出选课在这个集合中的学生就行了。

select a.* from student a 
where a.s_id != '01' and a.s_id in 
(select b.s_id from score b where b.c_id in 
(select c_id from student c left join score d on c.s_id=d.s_id where c.s_id='01'));

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

"完全相同"则不同是in了，而是=，而多条结果无法直接=，那么我的思路就比较直接，将学生的课程信息group_concat成字符串，然后就可以来比较了。缺点是sql语句会比较长。

select a.* from student a right join 
(select b.s_id,group_concat(b.c_id) as b_c_id from score b group by b.s_id having b_c_id=
(select group_concat(d.c_id) as c_c_id from student c left join 
score d on c.s_id=d.s_id  group by c.s_id having c.s_id='01')) e 
on a.s_id=e.s_id where a.s_id<>'01';

网上有别的方法，有一种是通过两个条件：1，课程数量一样；2，没有选01同学没选的课的

select  student.* from  student where  
s_id in (select s_id from score group by s_id having count(s_id) = 
(select count(c_id) from score where s_id = '01')) and 
s_id not in(select s_id from score  where c_id in
(select distinct c_id from score where c_id not in 
(select c_id from score where s_id = '01')) 
group by s_id )
and s_id not in ('01');

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

这一题可以反过来想，先从course中选出张三带的课，然后从score中选出有张三课的成绩的学生，然后从student中选出不在这些学生中的s_name

select b.s_name from student b where 
b.s_id not in 
(select d.s_id from score d where d.c_id=
(select a.t_id from teacher a where a.t_name='张三'));

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

难点在于加入了平均成绩，则需要用聚合函数求平均数。网上的答案其实都是有问题的，这是因为他们都忽略了王菊！因为王菊的成绩是NULL，用count（1）是统计不到王菊的，因为王菊的成绩在score里不存在。

我的写法如下：

select a.s_id,a.s_name,round(avg(b.s_score),3) from 
student a 
left join score b on a.s_id=b.s_id
where a.s_id in(select s_id from score where s_score<'60' group by s_id having count(1)>=2)
group by a.s_id
union select c.s_id,c.s_name,ifnull(round(avg(d.s_score),3),0) from
student c left join score d on c.s_id=d.s_id
group by c.s_id;

这里面有一个count(1)，这里count(1)是统计满足条件的个数。根据sql的执行顺序可知，这里的count(1)过滤掉了小于2的where。（顺便一提，count(1)在效果上约等于count(*)，唯一的区别是没有主键时，count(1)略快于count(*)）

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

网上的答案同样是有问题的，又忽略了王菊（王菊已经哭晕在厕所）。这题比较简单，就是两个条件。

select a.*,b.s_score,b.c_id 
from student a left join score b on a.s_id=b.s_id where 
(b.s_score<'60' or b.s_score is NULL) and (b.c_id='01' or b.c_id is NULL) 
order by b.s_score desc

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

这题的难点在于所有课程的成绩，我采用的方法是一个一个select，如果课程很多怎么办呢？。。。。。。

select b.*,ifnull((select s_score from score where s_id=a.s_id and c_id='01'),'0') as 语文,
ifnull((select s_score from score where s_id=a.s_id and c_id='02'),'0') as 数学,
ifnull((select s_score from score where s_id=a.s_id and c_id='03'),'0') as 英语,
round(avg(s_score),3) as 平均分 from score a right join student b on a.s_id=b.s_id group by  a.s_id,b.s_id order by 平均分 desc;

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

这个题目难点主要在计算，整个提取数据的逻辑并不复杂

select a.c_id,b.c_name,max(s_score),min(s_score),round(avg(s_score),2),
round(100*(sum(case when a.s_score>=60 then 1 else 0 end)/sum(case when a.s_score then 1 else 0 end)),2) as 及格率,
round(100*(sum(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/sum(case when a.s_score then 1 else 0 end)),2) as 中等率,
round(100*(sum(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/sum(case when a.s_score then 1 else 0 end)),2) as 优良率,
round(100*(sum(case when a.s_score>=90 then 1 else 0 end)/sum(case when a.s_score then 1 else 0 end)),2) as 优秀率
from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name;

19、按各科成绩进行排序，并显示排名

这里涉及到sql如何进行排名的问题，可以使用@来构造一个自增序号来实现。我们现在实验一下：

select @i:=@i+1 as 序号,s_name from (select @i:=0) i,student;

这样便构造了一列自增的序号，同理，可以用这个方法来进行排名！

(select * from (select (select @i:=@i+1) as 排名,a.* from 
(select @i:=0)  i,score a where a.c_id='01' order by a.s_score desc) a1 
order by a1.s_score desc)
union (select * from (select (select @j:=@j+1) as 排名,b.*  from 
(select @j:=0)  j,score b where b.c_id='02' order by b.s_score desc) b1 
order by b1.s_score desc)
union (select * from (select (select @k:=@k+1) as 排名,c.*  from 
(select @k:=0)  k,score c where c.c_id='03' order by c.s_score desc) c1 
order by c1.s_score desc)
;

20、查询学生的总成绩并进行排名

求总成绩比较简单，排名的方法还是用上一题的方法

select (select @i:=@i+1) as 排名,j.* from
(select a.s_name,a.s_id,sum(b.s_score) as 总成绩 from 
student a left join score b on a.s_id=b.s_id
group by a.s_id order by 总成绩 desc) as j,
(select @i:=0) as i;

21、查询不同老师所教不同课程平均分从高到低显示

这道题主要需要解决的就一个问题，就是老师和成绩的连接。

select avg(a.s_score) as 平均成绩,b.t_name from 
score a left join
(select d.c_id,d.c_name,e.t_id,e.t_name from 
course d left join teacher e on d.t_id=e.t_id) b on a.c_id=b.c_id 
group by b.t_id order by 平均成绩 desc ; 

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

这一题的难点在于排名，排名可以用之前的方法，但值得注意的是，排名在sql嵌套中的位置。就这题而言，如果排名在最外面，会报错，说为定义‘排名’，这是为什么呢？因为where的执行顺序是在select之前的，这里‘排名’还不是（select @i=@i+1）的值。

如果非要放在外层，可以这样：

这里还有一个点，是between and，为什么不用limit呢？网上有人做过测试，数据量大时，between and速度要limit快很多。

然后，我们只需要把三个课程union起来就成了题目要求的完整答案。

select c.*  from
(select (select @i:=@i+1) as 排名,a.*,b.c_id,b.s_score from student a 
left join score b on a.s_id=b.s_id,(select @i:=0) as i
where b.c_id='01' order by b.s_score desc) c 
where c.排名 between 2 and 3
union
select d.*  from
(select (select @j:=@j+1) as 排名,a.*,b.c_id,b.s_score from student a 
left join score b on a.s_id=b.s_id,(select @j:=0) as j
where b.c_id='02' order by b.s_score desc) d 
where d.排名 between 2 and 3
union
select e.*  from
(select (select @k:=@k+1) as 排名,a.*,b.c_id,b.s_score from student a 
left join score b on a.s_id=b.s_id,(select @k:=0) as k
where b.c_id='03' order by b.s_score desc) e 
where e.排名 between 2 and 3;

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

这道题看上去有点复杂。。。。难点在于统计百分比，我们先一步步来，我们尝试将100-85的统计出来：

select c_id,sum(case when s_score >85 and s_score <=100 
then 1 else 0 end) as `85-100人数`,
round(100*(sum(case when s_score >85 and s_score <=100 
then 1 else 0 end)/count(1)),2) as `百分比`
from score group by c_id;

这里我们还缺少别的分段以及课程名字，因为课程名字和ID是一样的，我们可以构造一个宽表，课程名字最后再加上。

这里在字段名`85-100人数`这，不加``会报错。。。。

select b.c_id,b.`85-100人数`,b.`百分比`,c.`70-85人数`,c.`百分比`,d.`60-70人数`,d.`百分比`,e.`0-60人数`,e.`百分比` 
from
(select c_id,sum(case when s_score >85 and s_score <=100 then 1 else 0 end) as `85-100人数`,
round(100*(sum(case when s_score >85 and s_score <=100 then 1 else 0 end)/count(1)),2) as `百分比`
from score group by c_id) b 
left join 
(select c_id,sum(case when s_score >70 and s_score <=85 then 1 else 0 end) as `70-85人数`,
round(100*(sum(case when s_score >70 and s_score <=85 then 1 else 0 end)/count(1)),2) as `百分比`
from score group by c_id) c 
on b.c_id=c.c_id
left join 
(select c_id,sum(case when s_score >60 and s_score <=70 then 1 else 0 end) as `60-70人数`,
round(100*(sum(case when s_score >60 and s_score <=70 then 1 else 0 end)/count(1)),2) as `百分比`
from 
score group by c_id) d 
on b.c_id=d.c_id
left join 
(select c_id,sum(case when s_score >=0 and s_score <=60 then 1 else 0 end) as `0-60人数`,
round(100*(sum(case when s_score >=0 and s_score <=60 then 1 else 0 end)/count(1)),2) as 百分比
from score group by c_id)e 
on b.c_id=e.c_id

 

最后，再left join一个course表，得到课程名

select distinct f.c_name,a.c_id,b.`85-100人数`,b.百分比,c.`70-85人数`,
c.百分比,d.`60-70人数`,d.百分比,e.`0-60人数`,e.百分比 
from score a
left join 
(select c_id,sum(case when s_score >85 and s_score <=100 then 1 else 0 end) as 
`85-100人数`,
round(100*(sum(case when s_score >85 and s_score <=100 then 1 else 0 end)/count(1)),2) as 
`百分比`
from score group by c_id)b 
on a.c_id=b.c_id
left join 
(select c_id,sum(case when s_score >70 and s_score <=85 then 1 else 0 end) as 
`70-85人数`,
round(100*(sum(case when s_score >70 and s_score <=85 then 1 else 0 end)/count(1)),2) as 
`百分比`
from score group by c_id)c 
on a.c_id=c.c_id
left join 
(select c_id,sum(case when s_score >60 and s_score <=70 then 1 else 0 end) as 
`60-70人数`,
round(100*(sum(case when s_score >60 and s_score <=70 then 1 else 0 end)/count(1)),2) as 
`百分比`
from score group by c_id)d 
on a.c_id=d.c_id
left join 
(select c_id,sum(case when s_score >=0 and s_score <=60 then 1 else 0 end) as 
`0-60人数`,
round(100*(sum(case when s_score >=0 and s_score <=60 then 1 else 0 end)/count(1)),2) as 
`百分比`
from score group by c_id)e 
on a.c_id=e.c_id
left join course f on a.c_id = f.c_id;

24、查询学生平均成绩及其名次

这个跟之前的一个题比较类似，只是多了一个名次

select (select @i:=@i+1) as 排名,c.* from
(select b.s_name as 学生姓名,avg(a.s_score) as 平均成绩 
from score a 
left join student b on a.s_id=b.s_id group by a.s_id order by 平均成绩 desc) c,
(select @i:=0) as i;

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

我一开始的