sql持续学习

前几天一个面试官，给我出了一个sql题，我没有答出来， 面试官很善意对我提点并告诉我数据库是我们吃饭的碗。谨记此话。当时的题目:

id	class
01	A
01	B
02	A
02	B
02	C
03	A

请用一个查询，查出01/02/03之间class的包含关系

 select * from                   
(select * from test where id = 01 ) a full join                   
(select * from test where id = 02 ) b on  a.class = b.class   full join                       
(select * from test where id = 03 ) c on c.class = b.class

结果

---

废话不多说开始下面的训练：

50题训练

建表

• 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 分数

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

 select * from  Student s  ,
 (select s1.sid from 
 (select * from SC where cid = 01 ) s1,
( select * from SC where cid = 02 ) s2   
 where s1.sid = s2.sid and s1.score>s2.score ) 
 t where s.sid(+) = t.sid

思路：
先查出同时又01和02课程的学生，再对比其分数

1.1、 查询同时存在" 01 “课程和” 02 "课程的情况

 select * from 
 (select * from sc where cid = 01 ) s1 , 
 (select * from sc where cid = 02） s2  
  where s1.sid= s2.sid

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

 select * from 
 (select * from sc where cid = 01 ) s1 , 
 (select * from sc where cid = 02） s2  
  where s1.sid= s2.sid(+)

使用 where s1.sid= s2.sid(+) 与 左连接是一个效果

1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

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

not in 表示不在其范围内，与之对应功能的是not exsits

 select * from sc  s2 where   not exists  (select * from sc s1 where cid = 01 and s1.sid=s2.sid )  and cid =02

相对于<>和not in 使得索引失效，not exsits效率更高。

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

 select avg(score),sid  as ss from SC    
 group by sid   having (avg(score) >= 60) 

having和where的区别是，where更快，但是分组前查询，having是分组后查询
having后面可以直接使用聚合函数，SUM, COUNT, MAX, AVG，而where后面补能使用

3、 查询在 SC 表存在成绩的学生信息

select t.* from 
(select sid from sc group by sid) s,
student t 
where  s.sid=t.sid(+) 

select t.* from   (select distinct sid  from sc) s,
student t  where  s.sid=t.sid(+) 

注意distinct的用法，去除重复数据

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

   select s.*,t.coursenumber ,t.scoresum from   
   ( select sid ,sname from student  )s,
  (select sid, count(cid) coursenumber ,sum(score) scoresum from sc group by sid   )t 
  where s.sid=t.sid(+) order by s.sid  asc

4.1、 查有成绩的学生信息

select s.* from student  s ,
( select distinct sid from sc ) t where s.sid = t.sid 

或者使用in/exsits

select * from student 
where exists (select sc.sid from sc where student.sid = sc.sid);

5查询「李」姓老师的数量

select count (tname) from teacher where tname like '李%'

6查询学过「张三」老师授课的同学的信息

select student.* from sc ,student ,
( select c.cid from (select tid from teacher where tname='张三') t,
 course c  where t.tid=c.tid ) tt  
 where sc.cid = tt.cid  and sc.sid=student.sid

对上面的sql进行修改
先查询 张三老师的cid

select course.cid from teacher,course 
where course.tid = teacher.tid and tname='张三'

再做联合查询

select student.* from student,teacher,course,sc
where 
    student.sid = sc.sid 
    and course.cid=sc.cid 
    and course.tid = teacher.tid 
    and tname = '张三';

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

select * from student 
where  sid not in (
select sid from 
(select  sid,count(cid) sumcourse from sc group by sid ) s ,
(select count (*) as sumcourse from course )c where  s.sumcourse= c.sumcourse )

这题需要写全都选课的补集
除了上面的sql还可以使用having

 select * from student
 where student.sid not in (
  select sc.sid from sc
  group by sc.sid
  having count(sc.cid)= (select count(cid) from course)
);

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

select student.* from student ,
(select distinct sid  from sc where sid <> 01 and cid  in 
(select cid from sc where sid =01) ) s 
where s.sid = student .sid  order by student.sid asc

我们对上面的sql进行修改

select * from student  where sid in 
(  select  sid  from sc where  cid  in 
	(select cid from sc where sid =01) 
	)  
and sid <> 01

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

select sid from SC where cid in (select cid from SC where sid=01)  
group by sid having count(*)=(select count(*) from SC where sid=01); 

这个不是我写的，是百度的点这里

我们来分析下，这个sql

//首先我们要获得01学生的所有课程
select cid from SC where sid=01;
//我们对sc表格进行筛选，所选的行的cid必须在 01的课程值内
select sid from SC where cid in (select cid from SC where sid=01)  ；
//我们再对这个选出来的值进行去重，且去重后的行数跟01的课程数相等
select sid from SC where cid in (select cid from SC where sid=01)  
group by sid having count(*)=(select count(*) from SC where sid=01); 

但是这个sql有一个问题，如果是一个学生选了两门一样的课，就会出现错误的结果。

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

select * from student 
        where student .sid  not in (
                select sid from sc where sc.cid in
                ( select cid from teacher,course where teacher.tname= '张三'  and  teacher.tid= course .tid))

或者

select * from student 
        where student .sid  not in (
                select sid from sc,teacher ,course 
                where sc.cid= course.cid and teacher.tname= '张三'  and  teacher.tid= course .tid)

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

select student.sid ,student.sname , avg(sc.score) 
from student ,sc ,(
select  sid  from sc where score < 60 
group by sid having count(cid)>=2 ) c 
where c.sid= sc.sid and student.sid=c.sid 
group by student.sid ,student.sname 

首先我们来分析下这个题目，查询两门及其以上不及格课程的同学的学号

select  sid  from sc where score < 60 
group by sid having count(cid)>=2 

问题是 获取 姓名及其平均成绩，这个平均成绩是指不及格的课程的平均成绩还是这个学生的所有课程的平均成绩。
不及格课程的平均成绩：
做一个两表联合查询就可以了

select  s.sid  ,t.sname,avg(s.score)  from sc s, student t  
where s.score < 60 and s.sid= t.sid 
group by s.sid,t.sname 
having count(s.cid)>=2   

这个学生的所有课程的平均成绩

将<60的条件放到联合查询的条件内部，做一个三表联合查询

select student.sid ,student.sname , avg(sc.score) from
 student ,sc ,(
select  sid  from sc where score < 60 
group by sid having count(cid)>=2 ) c 
where c.sid= sc.sid and student.sid=c.sid 
group by student.sid ,student.sname 

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

select t.* ,s.score from sc s ,student t  
where s.cid =01 and s.score<60  and s.sid= t.sid order by s.score desc

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

select *  from sc 
left join (
    select sid,avg(score) as avscore from sc 
    group by sid
    )r 
on sc.sid = r.sid
order by avscore desc;

14、查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select 
sc.CId ,
max(sc.score)as 最高分,
min(sc.score)as 最低分,
AVG(sc.score)as 平均分,
count(*)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率 
from sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC

题目很长，但是不难，最重要的使用 case when then else

15、按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
看到这题我们需要理清楚 自然连接和内连接的区别
如表

自然连接， 是将两个表相同的数据连接到一起，不需要指定列

select * from test t1 natural join test t2 

内连接则会根据指定列的匹配条件，将所有满足条件的列都列出来

select * from test t1,test t2 where t1.class=t2.class 

好，我们来列出分数的排名
我们选用内连接形式，以cid作为条件，即所有相同课程的同学都会显示，如 01 学生选了 01 课程， 02 学生也选了01 课程，查询的结果：

sid	cid	score	sid	cid	score
01	01	90	02	01	80
02	01	80	01	01	90

我们将左边表和右边表的分数进行对比，并做一个排名，即如果左边分数大于右边,则会出现在结果中，左边出现的次数即排名的反面，而有边出现的次数即排名，如查询01课程右边表格中01同学出现3次，02同学出现1次，03同学出现2次，排名即使01/03/02

select  * from test s1, test s2 where s1.class= s2.class  and s1.score >=s2.score 

进化 成本题

select a.cid, a.sid, a.score, count(b.sid)+1 as rank
from sc  a ,sc  b 
where a.score<b.score(+) and a.cid = b.cid(+)
group by a.cid, a.sid,a.score
order by a.cid, rank ASC;

也可以这样写

select s2.sid,s2.cid ,
(select  count(1) from sc s1 where s1.cid= s2.cid  and s1.score >s2.score ) +1 "排名" 
from sc s2 order by s2.cid  ,"排名" asc

结果：

15.1、 按各科成绩进行排序，并显示排名， Score 重复时合并名次

select s2.sid,s2.cid ,
(select  count(1) from sc s1 where s1.cid= s2.cid  and s1.score >s2.score ) +1 "排名" 
from sc s2 order by s2.cid  ,"排名" asc

16、查询学生的总成绩，并进行排名，总分重复时保留名次空缺
如果不需要显示排名，很简单

select sum(score) sumscore,sid  from sc  group by sid order by sumscore desc

如果需要排名列，参考上面的 15
参考

select s1.sid,s1.总成绩,s1.sName ,count(s2.sid)+1 排名 from 
(select t.sid ,nvl(sum(score),0)  总成绩 ,t.sname sName from  sc,student t where sc.sid(+)= t.sid group by t.sid,t.sname) s1,

(select t.sid ,nvl(sum(sc.score),0)  总成绩 ,t.sname sName from  sc,student t where sc.sid(+)= t.sid group by t.sid,t.sname ) s2 where s1.总成绩< s2.总成绩(+) group by s1.sid,s1.总成绩,s1.sName

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

如果不保留， 即对 计数时对总成绩进行去除重复 distinct

select s1.sid,s1.总成绩,s1.sName ,count(distinct (s2.总成绩) )+1 排名 from 
(select t.sid ,nvl(sum(score),0)  总成绩 ,t.sname sName from  sc,student t where sc.sid(+)= t.sid group by t.sid,t.sname) s1,

(select t.sid ,nvl(sum(sc.score),0)  总成绩 ,t.sname sName from  sc,student t where sc.sid(+)= t.sid group by t.sid,t.sname ) s2 where s1.总成绩< s2.总成绩(+) group by s1.sid,s1.总成绩,s1.sName

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

select s.cid,course.cname, sum (case when s.score <100 and s.score >= 85  then 1 else 0 end )/ count(*) "100-85",
sum (case when s.score <85 and s.score >= 70  then 1 else 0 end )/ count(*) "85-70",
sum (case when s.score <70 and s.score >= 60  then 1 else 0 end )/ count(*) "70-60",
sum (case when s.score <60 and s.score >= 0  then 1 else 0 end )/ count(*) "60-0"

 from sc s ,course where s.cid = course.cid  group by s.cid,course.cname

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

select a.cid, a.sid, a.score, count(b.sid)+1 as rank
from sc  a ,sc  b 
where a.score<b.score(+) and a.cid = b.cid(+)  
group by a.cid, a.sid,a.score having count(b.sid)+1 <=3
order by a.cid, rank ASC;

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

select count（sid）,cid  from sc  group by cid

20、查询出只选修两门课程的学生学号和姓名

select  t.* from student t,
(select s.sid from sc s having count (s.cid) =2 group by s.sid ) ss where t.sid = ss.sid

或者

select s.sid, t.sname from sc s,student t where t.sid = s.sid 
having count (s.cid) =2 group by s.sid , t.sname

21、查询男生、女生人数

  select count(1) ,ssex from student group by ssex

22、查询名字中含有「风」字的学生信息

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

考察模糊查询

23、查询同名同性学生名单，并统计同名人数

 select count(1) ,sname from student group by  sname having count(1)>1

24、查询 1990 年出生的学生名单
如果年龄是按varch2存储 的

  select  * from student where sage like '1990%'

如果年龄 存储是datetime

select *
from student
where YEAR(student.Sage)=1990;

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

select avg (score), cid from sc    group by  cid  
order by cid asc ,avg (score) desc

26、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

select avg(score), sc.sid,student.sname from sc ,student  where sc.sid = student.sid group by sc.sid,student.sname  having avg(score)>85;

27、查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

select sc.score,sc.sid,student.sname  from sc,student , (select cid from course where course.cname= '数学') cc 
where sc.cid = cc.cid  and sc.sid = student .sid  and sc.score<60

28、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

select sc.cid,sc.score,student.sid,student.sname  from sc,student where sc.sid(+) = student.sid

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

select sc.sid,sc.score,student.sname,course.cname from sc,student,course where sc.score >70  and sc.sid = student.sid and sc.cid = course.cid

30、查询有不及格的课程

select cid from sc where sc.score<60 group by cid

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

select sc.sid ,student.sname from sc,student  where cid = 01 and sc.sid = student.sid and sc.score>=80;

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

select count(1),cid from sc group by cid ;

33、成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select rownum,sid,score ,sname from (   select sc.score score,sc.sid sid,student.sname sname  from sc,teacher,course ,student  where teacher .tname= '张三' and course.cid = sc.cid and sc.sid = student.sid and teacher.tid = course.tid  
group by sc.score ,sc.sid ,student.sname order by sc.score desc ) where  rownum =1;

这里注意，rownum 是在 oder by 之前就生成的，所以如果你是排序完再根据结果取 rownum的话，需要 先获取的oder by的结果集。

34、成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select student.*, sc.score, sc.cid from student, teacher, course,sc 
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = '张三'
and sc.score = (
    select Max(sc.score) 
    from sc,student, teacher, course
    where teacher.tid = course.tid
    and sc.sid = student.sid
    and sc.cid = course.cid
    and teacher.tname = '张三'
);

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

select  a.cid, a.sid,  a.score from sc  a
,
sc  b
where a.sid = b.sid
and a.cid != b.cid
and a.score = b.score
group by a.cid, a.sid,a.score;

36、查询每门功成绩最好的前两名

select s1.sid ,s1.cid , s1.score ,count(s1.sid ) from sc s1 , sc s2 where   s1.score < s2.score(+) and s1.cid = s2.cid(+)  group by s1.sid ,s1.cid,s1.score having  count(s1.sid )<=2

37、统计每门课程的学生选修人数（超过 5 人的课程才统计）。

select cid ,count(sid) 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 sid ,count(cid) from sc  group by sid  
having  count(cid) = (select count(1) from course )  

下面几题都是照抄答案的：

40、查询各学生的年龄，只按年份来算

在这里插入代码片

41、按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select student.SId as 学生编号,student.Sname  as  学生姓名,
TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 学生年龄
from student

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

select *
from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());

43、查询下周过生日的学生

select *
from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;

44、查询本月过生日的学生

select *
from student 
where MONTH(student.Sage)=MONTH(CURDATE());

45、查询下月过生日的学生

select *
from student 
where MONTH(student.Sage)=MONTH(CURDATE())+1;

---

接着做：
sql