sql语句练习及答案

先把表建好,然后给大家讲解写sql的思路即拆分步骤 

--准备工作：建表、新增测试数据
create table Student(Sid varchar(10),Sname varchar(10),Sage date,Ssex varchar(10));
insert into Student values('01' , '赵雷' , str_to_date('1990-01-01','%Y-%m-%d') , '男');
insert into Student values('02' , '钱电' , str_to_date('1990-12-21','%Y-%m-%d') , '男');
insert into Student values('03' , '孙风' , str_to_date('1990-05-20','%Y-%m-%d') , '男');
insert into Student values('04' , '李云' , str_to_date('1990-08-06','%Y-%m-%d') , '男');
insert into Student values('05' , '周梅' , str_to_date('1991-12-01','%Y-%m-%d') , '女');
insert into Student values('06' , '吴兰' , str_to_date('1992-03-01','%Y-%m-%d') , '女');
insert into Student values('07' , '郑竹' , str_to_date('1989-07-01','%Y-%m-%d') , '女');
insert into Student values('08' , '王菊' , str_to_date('1990-01-20','%Y-%m-%d') , '女');

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(SCid varchar(10),Cid varchar(10),score int);
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 t3.* FROM (SELECT t1.*,t2.score FROM Student t1 LEFT JOIN sc t2 ON t1.`Sid`=t2.`SCid` WHERE t2.`Cid`=01) t3
LEFT JOIN (SELECT t1.*,t2.score FROM Student t1 LEFT JOIN sc t2 ON t1.`Sid`=t2.`SCid` WHERE t2.`Cid`=02) t4
ON t3.sid = t4.sid WHERE t3.score > t4.score

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

SELECT t3.* FROM (SELECT t1.*,t2.score FROM Student t1 LEFT JOIN sc t2 ON t1.`Sid`=t2.`SCid` WHERE t2.`Cid`=01) t3
LEFT JOIN (SELECT t1.*,t2.score FROM Student t1 LEFT JOIN sc t2 ON t1.`Sid`=t2.`SCid` WHERE t2.`Cid`=02) t4
ON t3.sid = t4.sid WHERE t3.score < t4.score

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

一看这种平均成绩的肯定得分组来进行查询

select AVG(score),SCid from sc GROUP BY SCid

然后一步就可以查出来了,把上面这个查询结果作为表进行接下来的查询

答案
select s1.Sname,s2.avgscore,s2.SCid from student s1,
(select AVG(score) avgscore,SCid from sc GROUP BY SCid) s2 
where s1.Sid=s2.SCid and s2.avgscore>=70

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

select s1.Sname,s2.avgscore,s2.SCid from student s1,
(select AVG(score) avgscore,SCid from sc GROUP BY SCid) s2 
where s1.Sid=s2.SCid and s2.avgscore<70

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

这个比较简单我就直接写答案了

select s1.Sid,s1.Sname,SUM(s2.score),COUNT(s2.SCid) from student s1,sc s2 
where s1.Sid=s2.SCid
GROUP BY s2.SCid

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

这个单表查询,更简单

select COUNT(Tid)
from teacher
where Tname like '李%'

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

首先得整明白表和表的关系,这个最终查的是同学的信息,但是关键点在课程表,但是课程表中只有课程的id,所以先得获取到张三老师教的那门课的id

select c.Cid cid
from course c,teacher t
where c.Tid=t.Tid and t.Tname='张三'

然后再查询含有这个课程的学生id

select s.SCid
from sc s,
(select c.Cid cid
from course c,teacher t
where c.Tid=t.Tid and t.Tname='张三') s2
where s.Cid=s2.cid 
GROUP BY s.SCid

接下来得到学生信息就很简单了

答案
select * from student s5
where s5.Sid in 
(select s.SCid
from sc s,
(select c.Cid cid
from course c,teacher t
where c.Tid=t.Tid and t.Tname='张三') s2
where s.Cid=s2.cid 
GROUP BY s.SCid)

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

select * from student s5
where s5.Sid not in 
(select s.SCid
from sc s,
(select c.Cid cid
from course c,teacher t
where c.Tid=t.Tid and t.Tname='张三') s2
where s.Cid=s2.cid 
GROUP BY s.SCid)

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

先查学过01的学生信息,和学过02的信息单独都查出来

select * from sc 
where Cid=01

select * from sc 
where Cid=02

然后查它俩都学过的

select s1.SCid
from 
(select SCid from sc 
where Cid=01) s1,
(select SCid from sc 
where Cid=02) s2
where s1.SCid=s2.SCid

答案

select *
from student
where Sid in
(select s1.SCid
from 
(select SCid from sc 
where Cid=01) s1,
(select SCid from sc 
where Cid=02) s2
where s1.SCid=s2.SCid)

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

在上面的基础上查询,学过01但没学过02的

select SCid
from sc 
where SCid not in
(select SCid from sc 
where Cid=02) and SCid in
(select SCid from sc 
where Cid=01)
GROUP BY SCid

答案

select * from student
where Sid in
(select SCid
from sc 
where SCid not in
(select SCid from sc 
where Cid=02) and SCid in
(select SCid from sc 
where Cid=01)
GROUP BY SCid)

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

这个首先从所有课程入手,查看一共有多少课程

然后按照学生分组查询每个学生共学了多少课程

然后查询不满3门课程的学生

最后联表查询查出最后结果

答案
select s.* 
from student s,
(select SCid,COUNT(Cid) c
from sc
GROUP BY SCid
HAVING c!=(select COUNT(Cid)
from course)) s2
where s.Sid = s2.SCid

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

从01学生学的哪些课程入手

然后查询在分数表中学过这些课程的学生id并且把01学生排除掉

答案

select *
from student
where Sid in
(select SCid 
from sc 
where Cid in
(select Cid
from sc
where SCid=01) and SCid!=01
GROUP BY SCid)

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

先查01学了哪些课程,和他学的课程的个数是多少个

然后查询跟01学习课程相同的并且个数也相同的

答案
select * from student
where Sid in
(select SCid
from sc s1,(select Cid
from sc
where SCid=01) s2
where s1.Cid=s2.Cid
GROUP BY SCid
HAVING COUNT(s1.Cid)=(select COUNT(Cid)
from sc
where SCid=01
))

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

先查张三的id

然后查他教过哪些课程的id

然后查学过张三课程的学生有哪些,因为要是直接查没学过他的课程的不好查

最后查不在那些学生id里的人

答案
select Sname
from student
where Sid not in (select SCid 
from sc
where Cid in (select Cid from course
where Tid in (select Tid
from teacher
where Tname = '张三')))

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

先查询哪些学生有大于等于两门课不及格的

然后把上面查询的结果当作表进行查询

答案

select s1.Sid,s1.Sname,s2.avgScore
from student s1,
(select COUNT(SCid) countSCid,SCid,AVG(score) avgScore
from sc 
where score<60
GROUP BY SCid
HAVING countSCid>=2) s2
where s1.Sid=s2.SCid 

 

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

答案
select * from student 
where Sid in (select SCid
from sc
where score<60 and Cid=01 
ORDER by score desc)

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

case cid WHEN '01' THEN score ELSE 0 end 这句话的意思是当cid为01的时候如果它有值就显示原有01的值,如果没值显示0.

答案
select scid,
max(case cid WHEN '01' THEN score ELSE 0 end) '语文',
max(case cid WHEN '02' THEN score ELSE 0 end) '数学',
max(case cid WHEN '03' THEN score ELSE 0 end) '英语',
AVG(score) '平均成绩'
from sc
GROUP BY scid
ORDER BY AVG(score) desc

 

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

先查询最简单的两个显示出来

然后查询这三个显示出来

再把它们合并一下,后面的都是这么做

答案
select a.cid,a.cname,b.最高分,b.最低分,b.平均分,b.及格率,b.中等率,b.优良率,b.优秀率
from course A
LEFT JOIN (select cid,MAX(score) '最高分',MIN(score) '最低分',AVG(score) '平均分',
CONCAT(ROUND(sum(case when score>=60 then 1 else 0 end)/COUNT(*)*100,2),'%') '及格率',
CONCAT(ROUND(sum(case when score>=60 AND score<80 then 1 else 0 end)/COUNT(*)*100,2),'%') '中等率',
CONCAT(ROUND(sum(case when score>=80 AND score<90 then 1 else 0 end)/COUNT(*)*100,2),'%') '优良率',
CONCAT(ROUND(sum(case when score>=90 then 1 else 0 end)/COUNT(*)*100,2),'%') '优秀率'
 FROM sc
GROUP BY cid) B
on a.cid=b.cid

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

(SELECT *,@row1:=@row1+1 rank FROM sc,(SELECT @row1:=0) r WHERE cid=01 ORDER BY score DESC) UNION ALL
(SELECT *,@row2:=@row2+1 rank FROM sc,(SELECT @row2:=0) r WHERE cid=02 ORDER BY score DESC) UNION ALL
(SELECT *,@row3:=@row3+1 rank FROM sc,(SELECT @row3:=0) r WHERE cid=03 ORDER BY score DESC)

20.查询学生的总成绩并进行排名,(select @rownu:=0) r这个就是声明了个变量,然后当作一个字表每条数据进行加一

select *,sum(score),@rownu:=@rownu+1 as rank from sc ,
(select @rownu:=0) r
GROUP BY SCid 
ORDER BY sum(score) desc

26、查询每门课程被选修的学生数

答案
select course.cname,a.个数
from course
LEFT JOIN (select cid,COUNT(cid) '个数'
from sc
GROUP BY cid ) A on course.cid=a.cid

27、查询出只有两门课程的全部学生的学号和姓名

答案

select sid,sname
from student
where sid in (select scid
from sc 
GROUP BY scid
HAVING COUNT(*)=2)

28、查询男生、女生人数

答案

select ssex,COUNT(*) from student GROUP BY ssex 

29.查询名字中含有"风"字的学生信息

答案
select * from student where sname like '%风%'

30.查询同名同性学生名单，并统计同名人数

答案
select sname,COUNT(*) from student GROUP BY sname,ssex HAVING COUNT(*)>1

31.查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime) 

答案
select sname from student WHERE YEAR(sage)=1990

32.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

答案
select * from sc GROUP BY cid ORDER BY AVG(score) desc ,cid

33.查询平均成绩大于等于60的所有学生的学号、姓名和平均成绩

先查出大于等于60的学生id和平均成绩

然后再查学生信息

答案
select s1.sid ,sname ,avg
from student s1,(select scid,AVG(score) avg from sc GROUP BY scid HAVING AVG(score)>=60) s2
where s1.sid=s2.scid

34.查询课程名称为"数学"，且分数低于60的学生姓名和分数

先查数学的课程id

然后查数学低于60的学生id和分数

然后就可以查出结果了

答案
select sname,score from student s1,
(select score ,scid from sc 
where cid = (select cid from course where cname = '数学') and score<60) s2
where s1.sid=s2.scid

35.查询所有学生的课程及分数情况；

答案

select scid,
max(case cid WHEN '01' THEN score else 0 end) 语文, 
max(case cid WHEN '02' THEN score else 0 end) 数学, 
max(case cid WHEN '03' THEN score else 0 end) 英语
from sc GROUP BY scid

36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数

先查70分以上的同学信息

然后结合课程表把课程名称并进来

然后再把学生名字并进来

答案
select sname,cname,scid,score from student s2,(select cname,scid,score from course c,(select scid,score,cid from sc where score>70) s
where c.cid=s.cid) A
where s2.sid=a.scid

37、查询不及格的课程

select * from sc where score<60

38、查询课程编号为01且课程成绩在70分以上的学生的学号和姓名；

答案
select sid,sname from student
where sid in (select scid from sc where cid = 01 and score >70)

39、求每门课程的学生人数

答案

select cid ,COUNT(*) from sc
GROUP BY cid

40.查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

先查张三的tid

然后查他对应课程的cid

再查最高成绩学生id和成绩

答案
select * from student s1,(
select scid,MAX(score) from sc where cid=(select cid from course where tid = (select tid from teacher where tname = '张三'))) s2
where s1.sid=s2.scid

41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

 

 

47、查询本周过生日的学生

 

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

DATE_ADD(NOW(),INTERVAL 1 MONTH)这个函数是把当前月加1月,也就是下个月

外面套个month就是获取它是几月

答案
select * from student where MONTH(sage)=MONTH(DATE_ADD(NOW(),INTERVAL 1 MONTH))

select 
    * 
from scores t
where 
    (select count(1)+1 from scores where class_id=t.class_id and score>t.score)
    <= 3

select * from a 
where fenshu>80
GROUP BY name HAVING count(*) = 
(select count(*) from (select * from a GROUP BY kecheng) a)

51. 

select * from (select s.*,@r:=(case when @c=class_id and @d=`subject`  then @r+1 else 1 end) as rank,
@c:=class_id c,@d:=`subject`
from scores s
ORDER BY class_id,`subject`,score desc) totle 
where totle.rank<=2

在where后面使用case when语句

select * from syscolumns where (case colid when 0 then 1 end) = 1 

说一个特别的东西,关于删除的,如果让你删除id小于8的数据,你该怎么做,你会感觉很简单,但是你会发现它会报错,但是看着sql感觉很正常,这是为什么呢,原因是mysql在做修改的时候,不能进行对这个表的查询,不知道MySQL为什么不允许这样操作，猜，可能是担心更新的表与查询的表为同一表会存在嵌套递归？还是担心效率的问题呢？

解决办法,在子语句里再嵌套一层就不会报错了,修改语句也是一样,也得嵌套一层,只有查询语句不用.