数据库45题

简书
45道题:

原则

1. join的本质：字段扩充

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

分析：【原则1】

• 1处之后的的表就变为sid sname sage ssex cid score
• 推荐下面的联合查询，不推荐子查询

select 
s.*, a.score as score_01, b.score as score_02
from student a
inner join sc b
on a.sid = b.sid //1
INNER JOIN sc c
on a.sid = c.sid and b.cid = 01 and c.cid = 02

WHERE b.score>c.score

• 子查询

select s.*, a.score as score_01, b.score as score_02
from student s,
     (select sid, score from sc where cid=01) a,
     (select sid, score from sc where cid=02) b
where a.sid = b.sid and a.score > b.score and s.sid = a.sid

1.1 查询同时存在 01 课程 和 02 课程的情况

提示：左边是01课程的记录，右边是02课程的记录，sid 能关联上的就是说明 01 ，02 课程记录都有，用子查询+inner jion

1. 子查询

先筛选出cid是01和02的，再

SELECT
*
FROM (select * from sc where cid = 01) a
inner join (select * from sc where cid = 02) b
on a.sid = b.sid

2. 自关联，推荐

推荐下面这个，使用简单的select from where查询连接查询得到的中间表，过程清晰，层次分明，具有优美感

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

select
  *
from sc a 
inner join sc b 
on a.sid=b.sid and a.cid='01' and b.cid='02';//之后，字段扩充了，存在不符合on条件的，置位null

3. 左链接

select 
*
from sc a
left join sc b
on a.sid = b.sid and b.cid = 02
where a.cid = 01

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

这一题要求找出存在 01 ，但是可能不存在 02 的课程。

• 左链接的特点就是右表不满足关联条件，显示null

查出来左表等于01的值，再左连接上

select
  *
from (select * from sc where cid='01') a 
left join sc b 
on a.sid=b.sid and b.cid='02';

先左关联，再限定左表的01

select
  *
from sc a 
left join sc b 
on a.sid=b.sid  and b.cid='02'
where a.cid='01'

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

这个题目是要查询不存在 01 课程但是存在 02 课程的情况，那我们是不是可以先找到存在 01 课程的学生都有哪些，然后再关联一下存在 02 课程就好了。
(select sid from sc where cid=‘01’)

提示：找出不存在01课程的记录后，然后再进行关联找存在02课程，可以用子查询筛选+inner join

select 
  *
from (select * from sc where sid not in (select sid from sc where cid='01')) a 
inner join sc b //如果是left join的话会将所有左边的表查出来的都显示出来
on a.sid=b.sid and b.cid='02';

//直接使用条件查询也可以
select
*
from sc a 
where sid not in (select sid from sc where cid='01')
and cid='02';

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

要求查询平均成绩大于等于60分的同学信息，

• 首先确定是用成绩表里面找.
• 找到了这样的同学后，用 sid 去学生信息表里面关联.

就可以得到学习的姓名信息。那这里关键就是找 sid。

这个好:
记住聚合函数的使用套路:聚合一个字段,对另一个字段求函数

select
   a.sid
   a.sname
   b.avg_score
from student a
inner join
(select
  sid,
  avg(score) as avg_sc
from sc 
group by sid having avg(score) >= 60) b
on a.sid=b.sid;

下面的一样的
总之,从一个表中筛选数据,显示另一个表中,都需要关联查询

select
  b.*
from 
(select
  sid,
  avg(score) as avg_sc
from sc 
group by sid having avg(score) >= 60) a
inner join student b
on a.sid=b.sid;

或:

select s.sid, sname, avg(score) as avg_score
from student as s, sc
where s.sid = sc.sid
group by s.sid
having avg_score > 60

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

我的

select
  b.*
from sc a
inner join student b
on a.sid=b.sid;

首先我们知道是要查询出学生信息，要求是有成绩信息，所以我们可以确定是要用到 sc 表和 student 表。

提示：成绩表肯定都是有学生的，所以用成绩表左关联就可以得到学生信息。
那我们就可以用 sc 成绩表做左表，然后关联学生信息，我们来看一下结果.

select 
    b.*
from sc a
left join student b
on a.sid=b.sid

但是发现有很多条重复的记录，而我们就需要去重了，我们是不是可以对 sid 进行一个 groupby ，我们来试一下。

select 
    b.*
from sc a
left join student b
on a.sid = b.sid
group by a.sid;

但是这个其实是不严谨的，group by 语法中，如果你对字段分组，那你能直接查询出来字段只有分组字段，其他字段只能以聚合函数的形式被查询出来，就是我们这里对 sid 分组，那select 只能查询 sid ，其他字段只能是 sum、avg、等聚合函数的结果，为什么是这样的语法呢，其实很好理解，我们举个例子。例如全年级举行了月考，然后我要求根据班级进行分组查询，也就是我们查询出来的是每一个班级的数据情况，那又能查询出来什么班级数据情况呢，是不是首先知道是哪个班级，因为我们是根据班级分组，每一个分组的班级信息肯定是一样的，那再然后呢，我能查询这个班级的平均值，最大值，最小值吧，这些都是班级分组的一个整体情况，但是我能查询具体一条数吗，不能把，为什么不能呢，那我们想如果能，这条数据是什么呢，是最大值还是最小值还是什么特殊值，你这条数据没有特殊的我凭什么查询你出来呢，那如果你是最大值最小值的话那不就是用了聚合函数吗，所以分组语法是不能查询除分组字段外其他字段的信息的，其他字段一定是经过聚合函数处理得到一个群体的值才行。

即:

• group只能查出来分组字段,其他的需要使用聚合函数,所以不能查出来类似 b.*的东西(mysql中select查出来返回第一条)

那我们这里为什么可以呢，这里其实是有一个优化，他这里查询默认就是排名第一的记录，这个记录不可控，但是由于我们说相同 sid 学生信息都一样，所以也无所谓。
那推荐写法是什么呢，我们可以先查询成绩表有哪些 sid ，然后和学生表做一个关联

select
  b.*
from 
(select 
    sid
from sc 
group by sid) a
left join student b
on a.sid=b.sid;

下面这句话起到了一个去重的作用

(select 
    sid
from sc 
group by sid)

当然我们这里的前提是有成绩的都有学习信息，有学生的不一定有成绩，所以用 left join ，但是如果有成绩的也不一定有学生信息，那就应该是用 inner join 会更合适。

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

我的

select 
a.sid,
a.sname
count(b.sid)
sum(b.score)
from student a
left join sc b;
on a.sid = b.sid;
group by a.sid;

提示：所有同学存在于学生表，课程数、成绩情况在成绩表，那就是要学生表左关联成绩汇总表得到记录，关联不上的说明就是没成绩。

那我们可以先来获取每个学生的选课总数及所有课程总成绩，在SC表中需要对SID进行聚合

select sid,count(cid) as cons,sum(score) as sum_score 
from sc
 group by sid;

第三步：与学生表拼接，获取学生信息，由于null也显示，则Student做主表，左连SC表。

select a.sid,a.sname,b.cons,b.sum_score 
from student a 
left join (select sid,count(cid) as cons,sum(score) as sum_score from sc group by sid) b
on a.sid = b.sid;

另外我们还可以先关联再统计，我们可以先给学生表关联上成绩信息，然后来分组统计，这样也可以把学习信息和汇总成绩查询出来。

select 
a.sid,
a.sname,
count(1) as cons,
sum(b.score) as cours
from student a
left join sc b
on a.sid=b.sid
group by a.sid;

group by a.sid;

下面的也可以

或

select s.sid, s.sname, count(cid) as 选课总数, sum(score) as 总成绩
from student as s left join sc
on s.sid = sc.sid
group by s.sid

4.1 查有成绩的学生信息

不用distinct去重，distinct去重只返回需要去重的字段，其他的不返回
用groupby去重

我的

//就是一个连接查询,但是成绩表中的数据可能有多个成绩对应一个sid,

select
*
from student a
inner join 
	(select sid 
	from score 
	group by sid) b
on a.sid = b.sid;

或

select
*
distinct from student a
inner join score b
on a.sid = b.sid;

别人的
提示：取出学生表中在成绩表的 sid 信息记录

那我们是不是判断 sid 是否存在就可以了，我们就可以先找出成绩表有多少个 sid ，然后用学生表的 sid 进行 in 判断就可以了。
即不用连接查询,用个子查询

select
 * 
from student a 
where a.sid in (select sid from sc group by sid);

另外求是否存在的，我们还可以用 exists 方法，这个是

select * from student 
where EXISTS(select 1)

即

select * from student 
where EXISTS(select * from sc where student.SId=sc.SId);

另外求这个并集，其实我们也可以用 inner join

select
  *
from student a
inner join (select sid from sc group by sid) b 
on a.sid=b.sid;

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

提示：like 语法的使用

select * from teacher where tname like ‘李%’;

select count(1) as cons from teacher where tname like '李%';

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

难点:四表关联

疯狂inner join
看下面的答案,每一层的别名可以重复使用

select 
*
from student e
inner join
	(select *
	from sc d
	inner join
		(select * from teacher a inner join course b on a.tid = b.tid) c
	on d.cid = c.cid) f
on e.sid = f.sid
where a.tname = "张三"

提示：关联教师表、课程表、成绩表、学生表，得到每个同学学习每门课程的老师是谁，然后筛选张三老师的记录。

教师表：teacher tid
课程表：course tid + cid
成绩表：sc cid + sid
学生表：student sid

注意每次取结果集把tname取上
1、得到教师和课程的关系

select * from course a inner join teacher b on a.tid = b.tid;

2、得到教师和成绩的关系

select * from sc a inner join (select a.*,b.Tname from course a inner join teacher b on a.tid = b.tid) b on a.cid = b.cid;

3、得到学生和教师的关系

select * from student a inner join ( select a.*,b.Tname from sc a inner join (select a.*,b.Tname from course a inner join teacher b on a.tid = b.tid) b on a.cid = b.cid) b on a.sid = b.sid;

4、筛选张三老师的记录

select a.*,Tname from student a inner join ( select a.*,b.Tname from sc a inner join (select a.*,b.Tname from course a inner join teacher b on a.tid = b.tid) b on a.cid = b.cid) b on a.sid = b.sid where Tname = "张三"

其他方法:

• 注意where和in的使用,where和=的使用 情况:看右边是有一个还是多个

select b.* from sc a left join student b on a.sid=b.sid where cid in (select cid from course where tid=(select tid from teacher where tname='张三') ) group by sid;

多表查询 https://www.liaoxuefeng.com/wiki/1177760294764384/1179664013849760
注意where进行多表查询不加限定返回的是笛卡尔积,慎用

​```sql
select student.* from teacher  ,course  ,student,sc where teacher.Tname='张三' and   teacher.TId=course.TId and   course.CId=sc.CId and   sc.SId=student.SId;

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

我的

只需要用学习信息关联到成绩sc 表得到每个人的课程信息，然后分组计数就知道是不是满足全部学习了。这里需要成绩表一门功课只有一条记录

select 
    * 
from student a 
left join sc b 
    on a.sid=b.sid 
group by a.sid 
    having count(b.cid)<(select count(cid) from course);

8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息(先录制)

select distinct b.* 
from sc a 
left join student b 
on a.sid=b.sid 
where cid in (select cid from sc where sid='01');

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

难

可以从两个方面来保证，第一没有01课程外的课程记录，第二课程数量一致

select b.*
from (select * from sc where sid not in (select sid from sc where cid not in (select cid from sc where sid='01')) and sid!='01') a
left join student b
on a.sid=b.sid
group by a.sid
having count(cid)=(select count(cid) from sc where sid='01');

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

提示：没有学习过，那找出学习过的，再用 not in 来判断一下就好了

教师表：teacher tid
课程表：course tid + cid
成绩表：sc cid + sid
学生表：student sid

1、找出张三老师的记录

select 
sid 
from sc a 
left join course b 
on a.cid = b.cid 
inner join teacher c 
on b.tid = c.tid 
and c.tname ="张三";

2、使用 not in

select
 * from student 
   where sid not in 
   (select 
     sid 
   from sc a 
   left join course b 
   on a.cid = b.cid 
   inner join teacher c 
   on b.tid = c.tid 
   and c.tname ="张三");

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

提示：平均成绩肯定要用到分组聚合

1、首先可以先求平均值
select
sid,
avg(score)
from sc
group by sid;

2、再求出有两门以上课程是不及格的学生学号
select
sid
from sc
where score<60
group by sid
having count(1)>1;

3、求平均值的时候筛选要的学生，再进行聚合求均值，并且可以关联上学生信息
select
a.sid,
b.sname,
avg(score)
from sc a
left join student b
on a.sid=b.sid
inner join (select
sid
from sc
where score<60
group by sid
having count(1)>1) c
on a.sid=c.sid
group by a.sid;

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

提示：这里用到了课程分数，学生信息，确认是用成绩表和学生表

1、筛选01课程分数小于60分的学生sid

select
sid
from sc where cid=‘01’ and score<60;

2、关联上学生信息，按照01课程分数排序

select
a.*,
b.cid,
b.score
from student a
inner join(
select
sid,
cid,
score
from sc where cid=‘01’ and score<60) b
on a.sid=b.sid
order by b.score desc;

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

第二步：获取每个学生的成绩，没有成绩显示null，所有需要学生表左连成绩表
select
a.sid,
b.cid,
b.score
from student a
left join sc b
on a.sid=b.sid;

第三步：求学生的平均成绩，只需用到成绩表
select
sid,
avg(score) as avg_sco
from sc group by sid;

第四步：将第二步与第三步左连，实现每个记录都有平均成绩，并按照成绩排序
select
c.sid,
c.cid,
c.score,
d.avg_sco
from (select
a.sid,
b.cid,
b.score
from student a
left join sc b
on a.sid=b.sid ) c
left join (select
sid,
avg(a.score) as avg_sco
from sc a group by a.sid) d
on c.sid=d.sid
order by avg_sco desc

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

提示：分组聚合，这里考察一个条件计数的技巧

1、计算各科的平均分，最高分，及格率等
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;

2、进行排序

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 选修人数 DESC,sc.CId asc;

15、按各科成绩进行排序，并显示排名， Score 重复时继续排序
select
sid,cid,score,
@rank:=@rank+1 as rn
from sc ,(select @rank:=0) as t order by score desc;

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select
*,
case when (@sco=score) then @rank
else @rank:=@rank+1 end as rn,
@sco:=score – 保存上一次的分数
from sc ,(select @rank:=0,@sco:=null) as t order by score desc

或者
select
sc.CId ,
case when @fontscore=score then @curRank
when @fontscore:=score then @curRank:=@curRank+1
end as rank,sc.score
from (select @curRank:=0 ,@fontage:=null) as t ,sc
ORDER BY sc.score desc

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

提示：排名使用用户变量

1、先求学生总成绩
select
sid,
sum(score) as scos
from sc
group by sid
order by scos desc;

2、使用自定义变量来记录排名值
select
s.*,
@rank:=if(@sco=scos,’’,@rank+1) as rn ,
@sco:=scos
from (select
sid,
sum(score) as scos
from sc group by sid order by scos desc) s
, (select @rank:=0,@sco:=null) as t;

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

提示：在上一题的基础上保留名次即可

select
s.*,
@rank:=if(@sco=scos,@rank,@rank+1) as rn ,
@sco:=scos
from (select
sid,
sum(score) as scos
from sc group by sid order by scos desc) s
, (select @rank:=0,@sco:=null) as t;

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

提示：分组统计数据，判断数据进行累加统计

1、直接进行分组统计
select
cid,
sum(case when 0<=score and score <=60 then 1 else 0 end )/count(1) as ‘[0,60]’,
sum(case when 60<score and score <=70 then 1 else 0 end )/count(1) as ‘[60,70]’,
sum(case when 70<score and score <=85 then 1 else 0 end )/count(1) as ‘[70,85]’,
sum(case when 85<score and score <=100 then 1 else 0 end )/count(1) as ‘[85,100]’
from sc group by cid

2、加上一个百分号
select course.CId,course.Cname,t1.*
from course LEFT JOIN (
select sc.CId,CONCAT(sum(case when sc.score>=85 and sc.score<=100 then 1 else 0 end )/count(*)100,’%’) as ‘[85-100]’,
CONCAT(sum(case when sc.score>=70 and sc.score<85 then 1 else 0 end )/count()100,’%’) as ‘[70-85)’,
CONCAT(sum(case when sc.score>=60 and sc.score<70 then 1 else 0 end )/count()100,’%’) as ‘[60-70)’,
CONCAT(sum(case when sc.score>=0 and sc.score<60 then 1 else 0 end )/count()*100,’%’) as ‘[0-60)’
from sc
GROUP BY sc.CId) as t1 on course.CId=t1.CId

18、查询各科成绩前三名的记录（无水印）
思路：前三名转化为若大于此成绩的数量少于3即为前三名。

这种其实就是笛卡儿积的写法，只不过灵活好用很多，但是不好查看名次

select
*
from sc a
where (select count(1) from sc b where a.cid=b.cid and b.score>a.score)❤️;

开始讲解
19、查询每门课程被选修的学生数

提示：按照课程id分组统计数据量

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

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

提示：分组查询，根据分组情况进行筛选
select
a.sid,
b.sname,
count(*) as cnum
from sc a
inner join student b
on a.sid = b.sid
group by a.sid,b.sname
having cnum = 2;

21.查询男生、女生人数

提示:根据性别分组就好

select
count(1) as cons,
ssex
from student
group by ssex;

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

提示：使用 like 语法

select
*
from student
where sname like ‘%风%’;

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

是不是需要一条记录和学生表的其他记录都去关联然后判断是不是同名同姓
提示：自关联筛选出符合条件的记录，要求名字，性别相等但是sid不同

select
a.*
from student a
inner join student b
on a.sname=b.sname and a.ssex=b.ssex and a.sid!=b.sid;

select
a.sname,
a.ssex,
count(1)
from student a
inner join student b
on a.sname=b.sname and a.ssex=b.ssex and a.sid!=b.sid
group by a.sname,a.ssex;

24.查询 1990 年出生的学生名单

分析题目：关键字段“出生日期Sage”

select

• from student
  where year(sage)=‘1990’;

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

提示：分组聚合然后排序

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

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

提示：需要分组聚合求平均值，然后筛选学生信息

select
sid,
avg(score) as avg_sco
from sc
group by sid
having avg_sco>=85;

select
a.sid,
a.avg_sco,
b.sname,
b.ssex
from (select
sid,
avg(score) as avg_sco
from sc
group by sid
having avg_sco>=85) a
left join student b
on a.sid=b.sid ;

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

提示：条件关联查询

1、先查询数学的课程id
select
cid from course where cname = “数学”;

2、求分数低于60分的成绩记录
select
a.sid,
cid,
score
from sc a
left join student b
on a.sid=b.sid
where cid = (select cid from course where cname = “数学”) and score <60;

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

提示：给学生信息加上课程情况

select

• from student a
  left join sc b
  on a.sid=b.sid;

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

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

提示：

1、先找出成绩大于70分的成绩记录，这里面的sid就是要找的学生

select

• from sc a
  left join course c
  on a.cid=c.cid
  where a.score>70;

2、和学生表进行关联得到学生信息记录

select
a.score,
b.sname,
c.cname
from sc a
left join student b
on a.sid=b.sid
left join course c
on a.cid=c.cid
where a.score>70;

30.查询存在不及格的课程

提示：条件查询

1、筛选出小于60分的成绩记录
select * from sc where score<60;

2、去重得到cid，如果需要课程信息还可以和 course 表做关联

select
a.*
from course a
where a.cid in (select cid from sc where score<60);

–从这里开始–
31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

提示：关联筛选

select
a.SId,
a.Sname
from student a
inner Join sc b
on a.sid=b.sid
where b.cid=‘01’
and b.score>80;

32、求每门课程的学生人数
提示：分组统计计数

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

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

提示：条件筛选查询，只需要一个人

1、关联学生表、成绩表、教师表

2、筛选出张三老师，然后排序选出一个来就是要找的同学

select
*
from sc a
left join student b
on a.sid=b.sid
left join course c
on a.cid=c.cid
left join teacher d
on c.tid=d.tid and d.tname=‘张三’
order by a.score desc limit 1;

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

成绩重复，也就是有可能第一名是同分数的，也都要查询出来，这种也可以通过 count(*)<1 来实

修改为得到数据后，对子查询表进行排名编号，这样排名是对的

提示：使用分组排序

select
a.*,
case when @score=score then @rank
when @score:=score then @rank:=@rank+1 end as rn
from
(select
a.sid,
a.score,
c.cid,
d.tname
from sc a
left join course c
on a.cid=c.cid
left join teacher d
on c.tid=d.tid
where d.tname=‘张三’) a
,(select @score:=null,@rank:=0) as t
order by a.score desc;

这题的答案感觉也是有问题的，因为下面答案括号里面的语句，并不是先 order by 后排序，而是先排序后 order by ，那么就会有一个一开始编号时是没有排序但是却b编号了，有可能第一个就不是最大值了，这里得到正确答案可能就是凑巧。

答案：这里成绩有重复，也就是有可能查出几条同分得最高分，也就是要进行排名了

select student.*,t1.score
from student INNER JOIN (
select
sc.SId,sc.score,
case when @fontage=sc.score then @rank
when @fontage:=sc.score then @rank:=@rank+1 end as rank
from course ,teacher ,sc,(select @fontage:=null,@rank:=0) as t
where course.CId=sc.CId
and course.TId=teacher.TId
and teacher.Tname=‘张三’
ORDER BY sc.score DESC) as t1 on student.SId=t1.SId
where t1.rank=1

35、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
–有一个错误，没有重录

提示：条件关联，条件是 cid 不同，sid 相同，但是成绩相同

1、先条件关联，sid 相同，cid 不同，成绩相同的记录

2、取出sid 、cid 的记录即可

或者使用 exists ，如果能查询出来则这条记录属于我们要找的

select
*
from sc a
inner join sc b
on a.sid=b.sid
where a.cid!=b.cid and a.score=b.score
group by a.sid ,a.cid

或者

select *
from sc as t1
where exists(select * from sc as t2
where t1.SId=t2.SId
and t1.CId!=t2.CId
and t1.score =t2.score )

36. 查询每门科目成绩最好的前两名

提示：用户变量记录排名

1、先排名
select
*,
@rank:=if(@c_cid=cid,if(@sco=score,@rank,@rank+1),1) as rank,
@sco:=score, @c_cid:=cid
from sc, (select @sco:=null,@rank:=0,@c_cid:=null) b
order by cid,score desc;

2、筛选前两名
select
sid,
cid,
score,
rank
from ( select
*,
@rank:=if(@c_cid=cid,if(@sco=score,@rank,@rank+1),1) as rank,
@sco:=score,
@c_cid:=cid
from sc, (select @sco:=null,@rank:=0,@c_cid:=null) b
order by cid,score desc ) a
where rank<=2;

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

提示：分组统计
select
,
count()
from sc
group by cid
having count(*)>5;

38.检索至少选修两门课程的学生学号

提示：分组查询

select sid from sc group by sid having count(*)>=2;

39.查询选修了全部课程的学生信息

提示：条件筛选

1、求出课程总数

select count(1)from course;

2、求每个同学的选修课程数
select sid,count(1) from sc group by sid;

3、筛选出符合条件的 sid
select ,count()
from sc
group by sid
having count()=(select count() from course);

4、获取学生信息
select * from student where sid in (select sid from sc group by sid having count()=(select count() from course));40.查询各学生的年龄，只按年份来

select
*
from student a
where (select count(1) from sc b where a.sid=b.sid)
=(select count(1) from course)

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

提示：使用 year函数

select *, year(now())-year(sage) as age from student;

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

提示：使用日期函数相减

–timestampdiff 日期相减函数
select timestampdiff(year,‘2002-05-01’,‘2001-01-01 10:00:03’); – -1
select timestampdiff(day ,‘2002-05-01’,‘2001-01-01’); – -485
select timestampdiff(hour,‘2008-08-08 12:00:00’,‘2008-08-08 00:00:00’); – -12

–获取当前时间
select now();
select curdate();

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

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

提示：使用 week 函数

s

select *, substr(YEARWEEK(student.Sage),5,2) as birth_week, substr(YEARWEEK(CURDATE()),5,2) as now_week from student having substr(YEARWEEK(student.Sage),5,2)=substr(YEARWEEK(CURDATE()),5,2);

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

select *, substr(YEARWEEK(student.Sage),5,2) as birth_week, substr(YEARWEEK(CURDATE()),5,2) as now_week from student having substr(YEARWEEK(student.Sage),5,2)= substr(YEARWEEK(CURDATE()),5,2)+1;

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

提示：使用month函数

select *, month(sage) as birth_month, month(now()) as now_month from student where month(sage)=month(now());

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

提示：使用 month 函数

select *, month(sage) as birth_month, month(now()) as now_month from student where month(sage)=month(now())+1