标题 | MySQL经典50题解析及答案 |
---|---|
作者 | Peter |
微信 | 756803877 |
公众号 | 尤而小屋 |
时间 | 2021-09-02 |
MySQL经典50题解析及答案
下面是网传经典的MySQL50题的习题及参考答案💪,供参考和学习,有更好的方法或者不恰当的地方,欢迎提出来
题目1
题目要求
查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SQL实现
-- 方法1
select
a.*
,b.s_score as 1_score
,c.s_score as 2_score
from Student a
left join Score b on a.s_id = b.s_id and b.c_id = '01' -- 两个表通过学号连接,指定01
left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02,或者c中的c_id直接不存在
-- 为NULL的条件可以不存在,因为左连接中会直接排除c表中不存在的数据,包含NULL
where b.s_score > c.s_score; -- 判断条件
-- 方法2:直接使用where语句
select
a.*
,b.s_score as 1_score
,c.s_score as 2_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; -- 前者成绩高
第二种方法实现:
题目2
题目要求
查询"01"课程比"02"课程成绩低的学生的信息及课程分数(题目1是成绩高)
SQL实现
类比题目1的实现过程
-- 方法1:通过连接方式实现
select
a.*
,b.s_score as 1_score
,c.s_score as 2_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 -- 包含NULL的数据
join score c on a.s_id=c.s_id and c.c_id='02'
where b.s_score < c.s_score;
-- 通过where子句实现
select
a.*
,b.s_score as 1_score
,c.s_score as 2_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; -- 前者比较小
题目3
题目需求
查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SQL实现
-- 执行顺序:先执行分组,再执行avg平均操作
select
b.s_id
,b.s_name
,round(avg(a.s_score), 2) as avg_score
from Student b
join Score a
on b.s_id = a.s_id
group by b.s_id -- 分组之后查询每个人的平均成绩
having avg_score >= 60;
-- 附加题:总分超过200分的同学
select
b.s_id
,b.s_name
,round(sum(a.s_score),2) as sum_score -- sum求和
from Student b
join Score a
on b.s_id=a.s_id
group by b.s_id
having sum_score > 200;
附加题:总分超过200分的同学
题目4
题目要求
查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
SQL实现1-两种情况连接
平均分小于60
select
b.s_id
,b.s_name
,round(avg(a.s_score), 2) as avg_score -- round四舍五入函数
from Student b
join Score a
on b.s_id = a.s_id
group by b.s_id -- 分组之后查询每个人的平均成绩
having avg_score < 60;
结果为:
没有成绩的同学:
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
);
最后将两个部分的结果连起来即可:通过union
方法
SQL实现2-ifnull函数判断
使用ifnull函数
select
S.s_id
,S.s_name
,round(avg(ifnull(C.s_score,0)), 2) as avg_score -- ifnull 函数:第一个参数存在则取它本身,不存在取第二个值0
from Student S
left join Score C
on S.s_id = C.s_id
group by s_id
having avg_score < 60;
使用null判断
select
a.s_id
,a.s_name
,ROUND(AVG(b.s_score), 2) as avg_score
from Student a
left join Score b on a.s_id = b.s_id
GROUP BY a.s_id
HAVING avg_score < 60 or avg_score is null; -- 最后的NULL判断
题目5
题目需求
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SQL实现
select
a.s_id
,a.s_name
,count(b.c_id) as course_number -- 课程个数
,sum(b.s_score) as scores_sum -- 成绩总和
from Student a
left join Score b
on a.s_id = b.s_id
group by a.s_id,a.s_name;
题目6
题目需求
查询“李”姓老师的数量
SQL实现
select count(t_name) from Teacher where t_name like "李%"; -- 通配符
这题怕是最简单的吧😭
题目7
题目需求
查询学过张三老师授课的同学的信息
SQL实现
-- 方法1:通过张三老师的课程的学生来查找;自己的方法
select * -- 3. 通过学号找出全部学生信息
from Student
where s_id in (
select s_id -- 2.通过课程找出对应的学号
from Score S
join Course C
on S.c_id = C.c_id -- 课程表和成绩表
where C.t_id=(select t_id from Teacher where t_name="张三") -- 1.查询张三老师的课程
);
-- 方法2:通过张三老师的课程来查询
select s1.*
from Student s1
join Score s2
on s1.s_id=s2.s_id
where s2.c_id in (
select c_id from Course c where t_id=( -- 1. 通过老师找出其对应的课程
select t_id from Teacher t where t_name="张三"
)
)
-- 方法3
select s.* from Teacher t
left join Course c on t.t_id=c.t_id -- 教师表和课程表
left join Score sc on c.c_id=sc.c_id -- 课程表和成绩表
left join Student s on s.s_id=sc.s_id -- 成绩表和学生信息表
where t.t_name='张三';
自己的方法:
方法2来实现:
方法3实现:
题目8
题目需求
找出没有学过张三老师课程的学生
SQL实现
select * -- 3. 通过学号找出全部学生信息
from Student
where s_id not in ( -- 2.通过学号取反:学号不在张三老师授课的学生的学号中
select s_id
from Score S
join Course C
on S.c_id = C.c_id
where C.t_id=(select t_id from Teacher where t_name ="张三") -- 1.查询张三老师的课程
);
-- 方法2:
select *
from Student s1
where s1.s_id not in (
select s2.s_id from Student s2 join Score s3 on s2.s_id=s3.s_id where s3.c_id in(
select c.c_id from Course c join Teacher t on c.t_id=t.t_id where t_name="张三"
)
);
-- 方法3
select s1.*
from Student s1
join Score s2
on s1.s_id=s2.s_id
where s2.c_id not in (
select c_id from Course c where t_id=( -- 1. 通过老师找出其对应的课程
select t_id from Teacher t where t_name="张三"
)
);
方法2:
题目9
题目需求
查询学过编号为01,并且学过编号为02课程的学生信息
SQL实现
-- 自己的方法:通过自连接实现
select s1.*
from Student s1
where s_id in (
select s2.s_id from Score s2
join Score s3
on s2.s_id=s3.s_id
where s2.c_id='01' and s3.c_id='02'
);
-- 方法2:直接通过where语句实现
select s1.*
from Student s1, Score s2, Score s3
where s1.s_id=s2.s_id
and s1.s_id=s3.s_id
and s2.c_id=01 and s3.c_id=02;
-- 方法3:两个子查询
-- 1. 先查出学号
select sc1.s_id
from (select * from Score s1 where s1.c_id='01') sc1,
(select * from Score s1 where s1.c_id='02') sc2
where sc1.s_id=sc2.s_id;
-- 2.找出学生信息
select *
from Student
where s_id in (select sc1.s_id -- 指定学号是符合要求的
from (select * from Score s1 where s1.c_id='01') sc1,
(select * from Score s1 where s1.c_id='02') sc2
where sc1.s_id=sc2.s_id);
- 先从Score表中看看哪些人是满足要求的:01-05同学是满足的
通过自连接查询的语句如下:
查询出学号后再匹配出学生信息:
通过where语句实现:
方法3的实现:
题目10
题目需求
查询学过01课程,但是没有学过02课程的学生信息(注意和上面👆题目的区别)
SQL实现
首先看看哪些同学是满足要求的:只有06号同学是满足的
错误思路1
直接将上面一题的结果全部排出,导致那些没有学过01课程的学生也出现了:07,08
select s1.*
from Student s1
where s_id not in ( -- 直接将上面一题的结果全部排出,导致那些没有学过01课程的学生也出现了:07,08
select s2.s_id from Score s2
join Score s3
on s2.s_id=s3.s_id
where s2.c_id='01' and s3.c_id ='02'
);
错误思路2
将上面题目中的02课程直接取反,导致同时修过01,02,03或者只修01,03的同学也会出现
select s1.*
from Student s1
where s_id in (
select s2.s_id from Score s2
join Score s3
on s2.s_id=s3.s_id
where s2.c_id='01' and s3.c_id !='02' -- 直接取反是不行的,因为修改(01,02,03)的同学也会出现
);
正确思路
https://www.jianshu.com/p/9abffdd334fa
-- 方法1:根据两种修课情况来判断
select s1.*
from Student s1
where s1.s_id in (select s_id from Score where c_id='01') -- 修过01课程,要保留
and s1.s_id not in (select s_id from Score where c_id='02'); -- 哪些人修过02,需要排除
!!!!!方法2:先把06号学生找出来
select * from Student where s_id in (
select s_id
from Score
where c_id='01' -- 修过01课程的学号
and s_id not in (select s_id -- 同时学号不能在修过02课程中出现
from Score
where c_id='02')
);
题目11
题目需求
查询没有学完全部课程的同学的信息
SQL实现
-- 自己的方法
select * -- 排除学号后得到的结果
from Student
where s_id not in (select s_id from (select s_id, count(s_id) as number -- 3.最大课程数所在的学号需要排除
from Score
group by s_id) s -- 取别名
where number=(select max(number) -- 2.保证最大的课程数
from( select s_id, count(s_id) as number -- 1.学号和个数统计(即修了几门课)
from Score group by s_id)t)); -- 别名
自己的方法一开始在课程的最大数中没有使用Course表,导致多使用了一个临时表的结果,现在改成使用Course表的统计值(3)作为课程的总数:
select s.*
from Student s
where s_id not in (
select s_id
from Score s1
group by s_id
having count(*) = (select count(*) from Course)
);
-- 方法2:having
select s.*
from Student s -- 学生表
left join Score s1 -- 成绩表
on s1.s_id = s.s_id
group by s.s_id -- 学号分组
having count(s1.c_id) < ( -- 分组后学生的课程数<3
select count(*) from Course -- 全部课程数=3
)
题目12
题目需求
查询至少有一门课与学号为01的同学所学相同的同学的信息
SQL实现
首先看看结果的:因为01号同学修了全部课程,所以其他的同学都是满足要求,除了08号同学没有任何成绩,不符合。
具体实现过程为:
select * -- 3、求出学生信息
from Student
where s_id in (
select distinct s_id -- 2、找出满足课程在01学生课程中的全部学号(学生),学号去重,同时将01自己排除
from Score
where c_id in (
select c_id
from Score
where s_id=01 -- 1、找出学号01同学的全部课程
)
and s_id != 01);
-- 方法2
select s1.*
from Student s1
join Score s2
on s1.s_id = s2.s_id -- 学生表和成绩表的关联
and c_id in (select c_id from Score where s_id=01) -- 对课程进行限制,只在01学生的课程内
group by s1.s_id; -- 根据学号分组
题目13
题目需求
查询和01同学学习的课程完全相同的同学的信息
SQL实现
- 自己的方法
select *
from Student
where s_id in (select s_id -- 3、步骤2中得到的学号是满足要求的
from(select distinct(s_id), count(c_id) number
from Score
group by s_id)t1 -- 1、学号和所修课程分组的结果t1
where number=3 -- 2、投机:选择出所修课程数是3(01修了3门)的学号
and s_id !=01 -- 01 本身排除
);
我们在上面的步骤2中不考虑直接指定3(where number=3),而是用01学生所修的课程数(虽然也是3)来代替:
select *
from Student
where s_id in(
select s_id -- 3、步骤2中得到的学号是满足要求的
from(select distinct(s_id), count(c_id) number
from Score
group by s_id)t1 -- 1、学号和所修课程分组的结果t1
where number=(select count(c_id) number
from Score
group by s_id having s_id=01) -- 2、改变的地方:使用学号01的课程数3来代替
and s_id !=01 -- 01 本身排除
);
- 使用
group_concat
函数
group_concat
的使用方法为:
group_concat([DISTINCT] 字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
我们将Score
表中每个s_id
的c_id
进行分组合并,实际的效果如下:
select
s_id
,group_concat(c_id order by c_id) -- 分组合并,同时排序
from Score
group by s_id;
需要进行排序的原因是防止出现这种情况:01修的课程顺序是:01,02,03;如果有同学修课的顺序是02,03,01,虽然顺序不同,但是本质上他们修的课程是相同的
使用排序后都会变成:01,02,03,保证结果相同
那么之后,我们只需要判断合并后和01号同学相同的结果即可,取出学号:
select * -- 3、查询信息
from Student
where s_id in(
select s_id
from Score
group by s_id
having group_concat(c_id order by c_id)=( -- 2、找出和01号学生分组合并结果相同的学号s_id;也需要排序
select group_concat(c_id order by c_id) -- 1、找出01号学生分组合并的结果,同时排序;排序很重要
from Score
group by s_id
having s_id=01)
and s_id != 01 -- 将自身排除
);
题目14
题目需求
查询没有修过张三老师讲授的任何一门课程的学生姓名
SQL实现
自己的方法,具体过程如下:
select s_name -- 4、学号取反找到学生姓名
from Student
where s_id not in(
select distinct(s_id) -- 3、课程号找到对应的学号
from Score
where c_id=(
select c_id -- 2、教师编号找到对应的课程号
from Course
where t_id=(
select t_id -- 1、姓名找到教师编号
from Teacher
where t_name='张三')
));
-- 修过张老师课程的学生的学号
select distinct(s_id)
from Score
where c_id=(
select c_id
from Course
where t_id=(
select t_id
from Teacher
where t_name='张三')); -- 修过张三老师课的学生
题目15
题目需求
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SQL实现
首先看看哪些同学是满足两门或者两门以上是及格的
-- 2门及以上不及格的
select
s_id
,round(avg(s_score)) avg_score
from Score
where s_score < 60 -- 小于60分,不及格
group by s_id
having count(s_score) >= 2; -- 不及格的有2门以上
说明04,06是我们最终想要的结果
-- 自己的方法
select
s.s_id
,s.s_name
,t.avg_score
from Student s
join (select
s_id
,round(avg(s_score)) avg_score
from Score
where s_score < 60
group by s_id
having count(s_score) >= 2)t
on s.s_id=t.s_id
-- 参考方法1
select
a.s_id,
a.s_name,
ROUND(AVG(b.s_score))
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,a.s_name
-- 参考方法2
select
s.s_id
,s_name
,round(avg(s_score), 2) avg_score
from Student s
join Score sc
on s.s_id=sc.s_id
and sc.s_score < 60 -- 不及格
group by s.s_id -- 学号分组
having count(sc.c_id )>= 2; -- 2门课
改进点
上面的两种方法都没有考虑都08学生,3门都没有成绩,这个本题需要改进的地方。
题目16
题目需求
检索01课程分数小于60,按分数降序排列的学生信息
SQL实现
自己的方法如下:
首先从Score表中找出哪些学生是满足这个要求:
select
s_id
,s_score
from Score
where s_score < 60
and c_id = 01;
然后直接将上面的结果和Student表查询:
select s.*
from Student s
where s.s_id in (
select
s_id
,s_score
from Score
where s_score < 60
and c_id = 01
);
select
s.*
,t.s_score
from Student s
join (select s_id,s_score -- 2、Student和t的连接查询
from Score
where s_score < 60
and c_id = 01 )t -- 1、将第一步结果作为中间表t
on s.s_id=t.s_id;
-- 自己的方法2:两个表的直接连接查询+where条件
select
s.*
,sc.s_score
from Student s
join Score sc
on s.s_id=sc.s_id
where sc.c_id=01 and sc.s_score < 60
order by sc.s_score desc; -- 默认就是降序desc
题目17
题目需求
按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩
SQL实现
下面是自己的解法:
1、先求出每个同学的平均分,并降序排列
select
s_id
,round(avg(s_score),2) avg_score
from Score
group by s_id
order by 2 desc;
-- 自己的方法
select
s.s_id
,s.c_id
,s.s_score
,t.avg_score
from Score s
join (select
s_id
,round(avg(s_score),2) avg_score
from Score
group by s_id)t
on s.s_id = t.s_id
order by 4 desc; -- 指的是第4个字段
-- 参考方法1
select
s.s_id
,(select s_score from Score where s_id=s.s_id and c_id='01')
as 语文
,(select s_score from Score where s_id=s.s_id and c_id='02')
as 数学
,(select s_score from Score where s_id=s.s_id and c_id='03')
as 英语
,round(avg(s_score),2) 平均分
from Score s
group by s.s_id
order by 5 desc;
select
s.s_id
,max(case s.c_id when '01' then s.s_score end) 语文
,max(case s.c_id when '02' then s.s_score end) 数学
,max(case s.c_id when '03' then s.s_score end) 英语
,avg(s.s_score)
,b.s_name -- 没有出现在group by子句中,导致报错
join Student b
on s.s_id = b.s_id
group by s.s_id
order by 5 desc;
严格模式的报错:
ERROR 1055 (42000): Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.b.s_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中,也就是说查出来的列必须在group by后面出现否则就会报错,或者这个字段出现在聚合函数里面。
-- 参考方法2:将上面的b.s_name去掉
select
s.s_id
,max(case s.c_id when '01' then s.s_score end) 语文
,max(case s.c_id when '02' then s.s_score end) 数学
,max(case s.c_id when '03' then s.s_score end) 英语
,round(avg(s.s_score),2) avg_score
from Score s
join Student b
on s.s_id = b.s_id
group by s.s_id
order by 5 desc;
题目18
题目需求
查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率;及格:>=60,中等为:70-80,优良为:80-90,优秀为:>=90
SQL实现
思路清晰:统计每个阶段的总人数,再除以总共的人数即可
将成绩表和课程表联合起来进行查询:
- case 语句用于对每个分数贴标签
- sum 语句对相应的语句中的1进行求和
select
s.c_id
,c.c_name
,max(s.s_score)
,min(s.s_score)
,round(avg(s.s_score), 2)
,round(100 * (sum(case when s.s_score >= 60 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 及格率
,round(100 * (sum(case when s.s_score >= 70 and s.s_score <= 80 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 中等率
,round(100 * (sum(case when s.s_score >= 80 and s.s_score <= 90 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 优良率
,round(100 * (sum(case when s.s_score >= 90 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 优秀率
from Score s
left join Course c
on s.c_id = c.c_id
group by s.c_id, c.c_name;
题目19
题目需求
按照各科成绩进行排序,并且显示排名
分析过程
题目的意思是:将每科的成绩单独进行排名,类似如下的效果:
课程名 | 分数 | 排名 |
---|---|---|
英语 | 99 | 1 |
英语 | 92 | 2 |
英语 | 89 | 3 |
数学 | 88 | 1 |
数学 | 85 | 2 |
…… | …… | …… |
SQL实现
第一步:我们对Score表中的一门课程进行排名,比如01课程
select * from(
select
t1.c_id -- 课程号
,t1.s_score -- 分数
,(select count(distinct t2.s_score) -- 课程去重
from Score t2
where t2.s_score >= t1.s_score -- SQL实现排序
and t2.c_id = '01') rank
from Score t1 -- 通过相同的表实现自连接
where t1.c_id = '01'
order by t1.s_score desc
)t1
上面是针对01课程,结果为:
第二步:我们将01、02、03课程全部连接起来,通过union实现
- 表的自连接
- SQL实现排序
-- 自己的方法
select * from(
select
t1.c_id -- 课程号
,t1.s_score -- 分数
,(select count(distinct t2.s_score) -- 课程去重
from Score t2
where t2.s_score >= t1.s_score -- SQL实现排序
and t2.c_id = '01') rank
from Score t1 -- 通过相同的表实现自连接
where t1.c_id = '01'
order by t1.s_score desc
)t1
union
select * from(
select
t1.c_id -- 课程号
,t1.s_score -- 分数
,(select count(distinct t2.s_score) -- 课程去重
from Score t2
where t2.s_score >= t1.s_score -- SQL实现排序
and t2.c_id = '02') rank
from Score t1 -- 通过相同的表实现自连接
where t1.c_id = '02'
order by t1.s_score desc
)t2
union
select * from(
select
t1.c_id -- 课程号
,t1.s_score -- 分数
,(select count(distinct t2.s_score)
from Score t2
where t2.s_score >= t1.s_score
and t2.c_id = '03') rank
from Score t1
where t1.c_id = '03'
order by t1.s_score desc
)t3;
-- 参考代码
select * from (select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score)
from Score t2
where t2.s_score>=t1.s_score and t2.c_id='01') rank
from Score t1 where t1.c_id='01'
order by t1.s_score desc) t1
union
select * from (select
t1.c_id
,t1.s_score
,(select count(distinct t2.s_score)
from Score t2
where t2.s_score>=t1.s_score and t2.c_id='02') rank
from Score t1 where t1.c_id='02'
order by t1.s_score desc) t2
union
select * from (select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score) from Score t2 where t2.s_score>=t1.s_score and t2.c_id='03') rank
from Score t1 where t1.c_id='03'
order by t1.s_score desc) t3
题目20
题目需求
查询学生的总成绩,并进行排名
SQL实现
1、先查询每个学生的总成绩
select
s_id
,sum(s_score)
from Score
group by s_id
order by 2 desc;
将上面的结果和学生信息表进行关联查询:
--
select
s.s_name
,s.s_id
,t.score
from Student s
join(select
s_id
,sum(s_score) score
from Score
group by s_id
order by 2 desc
)t
on s.s_id = t.s_id;
-- 不使用中间表查询
select
s.s_id
,s.s_name
,sum(sc.s_score) score
from Student s
join Score sc
on s.s_id = sc.s_id
group by s.s_id
order by 3 desc;
如果想给排名加上一个排序号,参考之前的文章
-- 加上排序号
select
t1.s_id ,t1.s_name, t1.score
,(select count(t2.score)
from(select s.s_id, s.s_name, sum(sc.s_score) score
from Student s
join Score sc
on s.s_id = sc.s_id
group by s.s_id
order by 3 desc)t2 -- t2和t1相同
where t2.score > t1.score) + 1 as rank
from(
select s.s_id ,s.s_name ,sum(sc.s_score) score
from Student s
join Score sc
on s.s_id = sc.s_id
group by s.s_id
order by 3 desc)t1 -- t1
order by 3 desc;
题目21
题目需求
查询不同老师所教不同课程平均分从高到低显示
SQL实现
先找出每个老师教授了哪些课程:
select
c.c_name
,t.t_name
from Course c
left join Teacher t
on c.t_id = t.t_id;
将上面的结果和成绩表连接起来:
select
c.c_name
,t.t_name
,round(avg(s.s_score),2) score -- 课程分组后再求均值
from Course c -- 主表,通过两次连接
left join Teacher t
on c.t_id = t.t_id
left join Score s
on c.c_id = s.c_id
group by c.c_id -- 课程分组
order by 3 desc; -- 降序
题目22
题目需求
查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SQL实现
自己的方法
1、课程表和成绩表连接起来,显示所有的课程和成绩信息
select
s.s_id
,s.c_id
,s.s_score
,c.c_name
from Score s
join Course c
on s.c_id = c.c_id
2、查出全部的语文成绩
select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc;
3、我们找出语文的第2、3的学生
select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc
limit 1, 2;
4、同时求出语文、数学、英语的分数,并且通过union
拼接
-- union连接
(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc
limit 1, 2)
union
(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '数学'
order by s.s_score desc
limit 1, 2)
union
((select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '英语'
order by s.s_score desc
limit 1, 2))
5、将上面的结果学生信息表进行连接即可
好歹是实现了😭
-- 最终脚本
-- !!!!真的需要好好优化下
select
s.s_id
,s.s_name
,t.c_name
,t.s_score
from Student s
join (-- union连接
(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc
limit 1, 2)
union
(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '数学'
order by s.s_score desc
limit 1, 2)
union
((select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '英语'
order by s.s_score desc
limit 1, 2)))t -- 临时表t
on s.s_id = t.s_id
和第25题相同的方法
1、以语文为例,首先我们找出前3名的成绩(包含相同的成绩)
-- 语文
select
a.s_id
,a.c_id
,a.s_score -- 3、此时a表的成绩就是我们找的
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数,要带上等号
and a.c_id="01"
group by 1,2
having count(b.s_id) <= 3 -- 2、b中的个数至少有3个,应对分数相同的情形
order by 3 desc
limit 1,2
-- 语文
select
a.s_id
,a.c_id
,a.s_score -- 3、此时a表的成绩就是我们找的
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数,要带上等号
and a.c_id="01"
group by 1,2
having count(b.s_id) <= 3 -- 2、b中的个数至少有3个,应对分数相同的情形
order by 3 desc
limit 1,2; -- 取得第2、3名
在通过数学和英语的类似操作得到2、3名的成绩,再进行拼接即可
题目23
题目需求
统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SQL实现
自己的方法
1、如何对每个成绩进行分组展示:ABCD代表相应的等级
select
c_id
,s_score
,case when s_score >= 85 and s_score<= 100 then 'A' -- 大小关系必须分两次写,一次写的话MySQL无法识别
when 70 <= s_score and s_score < 85 then 'B'
when 60 <= s_score and s_score < 70 then 'C'
when 0 <= s_score and s_score < 60 then 'D'
else '其他' end as 'category'
from Score s;
2、将两个表关联起来展示数据
-- 1、查看全部课程和成绩信息
select
s.c_id
,c.c_name
,s.s_score
,case when s.s_score >= 85 and s.s_score<= 100 then 'A' -- 大小关系必须分两次写,一次写的话MySQL无法识别
when 70 <= s.s_score and s.s_score < 85 then 'B'
when 60 <= s.s_score and s.s_score < 70 then 'C'
when 0 <= s.s_score and s.s_score < 60 then 'D'
else '其他' end as 'category'
from Score s
join Course c
on s.c_id = c.c_id;
3、完整代码
select
s.c_id 编号
,c.c_name 科目
,sum(case when s.s_score >= 85 and s.s_score<= 100 then 1 else 0 end) "[85,100]人数"
,round(100 * (sum(case when s.s_score >= 85 and s.s_score<= 100 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[85,100]百分比'
,sum(case when s.s_score >= 70 and s.s_score<= 85 then 1 else 0 end) "[70,85]人数"
,round(100 * (sum(case when s.s_score >= 70 and s.s_score<= 85 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[70,85]百分比'
,sum(case when s.s_score >= 60 and s.s_score<= 70 then 1 else 0 end) "[60,70]人数"
,round(100 * (sum(case when s.s_score >= 60 and s.s_score<= 70 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[60,70]百分比'
,sum(case when s.s_score >= 0 and s.s_score<= 60 then 1 else 0 end) "[0,60]人数"
,round(100 * (sum(case when s.s_score >= 0 and s.s_score<= 60 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[0,60]百分比'
from Score s
left join Course c
on s.c_id = c.c_id
group by s.c_id, c.c_name
参考方法
1、先统计每个阶段的人数和占比
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(*)), 2) '占比'
from Score
group by c_id; -- 分课程统计总数和占比
-- 方式2
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(case when s_score then 1 else 0 end)), 2) '占比' -- 不同count(*)
from Score
group by c_id;
注意对比:
2、我们将4种情况同时查出来
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(*)), 2) '[85,100]占比'
,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(*)), 2) '[70,85]占比'
,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(*)), 2) '[60,70]占比'
,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(*)), 2) '[0,60]占比'
from Score
group by c_id; -- 分课程统计总数和占比
3、将科目名称连接起来
-- 整体和自己的方法是类似的
select
s.c_id
,c.c_name
,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(*)), 2) '[85,100]占比'
,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(*)), 2) '[70,85]占比'
,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(*)), 2) '[60,70]占比'
,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(*)), 2) '[0,60]占比'
from Score s
left join Course c
on s.c_id = c.c_id
group by s.c_id, c.c_name; -- 分课程统计总数和占比
题目24
题目需求
查询学生的平均成绩及名次
SQL实现
自己的方法
1、先求出每个人的平均分
-- 自己的方法
select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id=s.s_id
group by sc.s_id,s.s_name
2、我们对上面的结果进行排序
!!!MySQL5中是没有rank函数的,需要自己实现排序功能
-- MYSQL5.7中没有rank函数,所以通过自连接实现
select
t1.s_id
,t1.s_name
,t1.avg_score
,(select count(distinct t2.avg_score)
from (select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id=s.s_id
group by sc.s_id,s.s_name)t2 -- 临时表t2也是上面的结果
where t2.avg_score >= t1.avg_score
) rank
from (select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id=s.s_id
group by sc.s_id,s.s_name)t1 -- 临时表t1就是上面的结果
order by t1.avg_score desc;
参考方法
select
a.s_id -- 学号
,@i:=@i+1 as '不保留空缺排名' -- 直接i的自加
,@k:=(case when @avg_score=a.avg_s then @k else @i end) as '保留空缺排名'
,@avg_score:=avg_s as '平均分' -- 表a中的值
from (select
s_id
,round(avg(s_score), 2) as avg_s
from Score
group by s_id
order by 2 desc)a -- 表a:平均成绩的排序和学号
,(select @avg_score:=0, @i:=0, @k:=0)b -- 表b:通过变量设置初始值
实现rank函数
select
s.s_name -- 姓名
,s.s_score -- 成绩
,(select count(distinct t2.s_score)
from Score t2
where t2.s_score >= t1.s_score) rank -- 在t2分数大的情况下,统计t2的去重个数
from Score t1
order by t1.s_score desc; -- 分数降序排列
举例子来说明这个脚本:
姓名 | 成绩 |
---|---|
张三 | 89 |
李四 | 90 |
王五 | 78 |
小明 | 98 |
小红 | 60 |
- 当t1.s_score=89,满足t2.s_score > = t1.s_score的有98,90和89,此时count(distinct t2.s_score) 的个数就是3
- 当t1.s_score=90,满足t2.s_score > = t1.s_score的有98和90,此时count(distinct t2.s_score) 的个数就是2
- 当t1.s_score=78,满足t2.s_score > = t1.s_score的有98、90、89和78,此时count(distinct t2.s_score) 的个数就是4
- 当t1.s_score=98,满足t2.s_score > = t1.s_score的只有98,此时count(distinct t2.s_score) 的个数就是1
- 当t1.s_score=60,满足t2.s_score > = t1.s_score的有89、90、78、98、60,此时count(distinct t2.s_score) 的个数就是5
通过上面的步骤,我们发现:t1中每个分数对应的个数就是它的排名
题目25
题目需求
查询各科成绩前三名的记录
SQL实现
自己的方法
1、首先我们找出语文的前3名
select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc -- 降序之后取出前3条记录
limit 3;
2、通过同样的方法我们可以求出数学和英语的前3条记录,然后通过union进行联结,有待优化😭
-- 自己的脚本
(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc -- 降序之后取出前3条记录
limit 3)
union
(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '数学'
order by s.s_score desc
limit 3)
union
(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '英语'
order by s.s_score desc
limit 3)
参考方法
通过Score表的自连接,表a中的值小于表b中的值,排序之后我们取前3
select
a.s_id
,a.c_id
,a.s_score -- 取出a中的成绩
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score -- 表b中的成绩大
group by 1,2,3
having count(b.s_id) = 3
order by 2, 3 desc;
我们通过语文
这个科目来理解上面的代码:前3名是80,80,76
-- 语文
select
a.s_id
,a.c_id
,a.s_score -- 3、此时a表的成绩就是我们找的
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数,要带上等号
and a.c_id="01"
group by 1,2
having count(b.s_id) <= 3 -- 2、b中的个数至少有3个,应对分数相同的情形
order by 3 desc;
-- 语文
select
a.s_id
,a.c_id
,a.s_score -- a表的成绩
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数,要带上等号
group by 1,2,3
having count(b.s_id) <= 3 -- 2、b中的个数至少有3个,应对分数相同的情形
order by 2, 3 desc; -- 课程(2)的升序,成绩()3的降序
题目26
题目需求
查询每门课被选修的学生数
SQL实现
select
c.c_id
,c.c_name
,count(s.s_id)
from Course c
join Score s
on c.c_id = s.c_id
group by c.c_id;
题目27
题目需求
查询出只有两门课程的全部学生的学号和姓名
SQL实现
having条件是分组之后在执行的,where语句是分组前先执行的
select
s.s_id
,s.s_name
from Student s
join Score sc
on s.s_id = sc.s_id
group by 1,2
having count(sc.c_id) = 2;
题目28
题目需求
查询男女生人数
SQL实现
先看看数据:男女人数都是4
-- 自己的方法
select
count(case when s_sex='男' then 1 end) as '男'
,count(case when s_sex='女' then 1 end) as '女'
from Student;
-- 参考方法
select
s_sex
,count(s_sex) as `人数`
from Student
group by s_sex;
题目29
题目需求
查询名字中含有风
字的学生信息
SQL实现
先看看哪些同学的名字中有风
-- 模糊匹配:我们在两边都加上了%,考虑的是姓或者名字含有风,虽然风姓很少见
select * from Student where s_name like "%风%";
题目30
题目需求
查询同名同性的学生名单,并统计同名人数
SQL实现
1、先看看班级的学生信息
现有的数据中没有同名的学生,但是当班级人数增多的时候很有可能在班级上出现同名的学生
2、假设有同名同性的学生
select
a.s_name
,a.s_sex
,count(*)
from Student a -- 同一个表的自连接
join Student b
on a.s_id != b.s_id -- 连接的时候不能是同一个人:学号保证,每个人的学号是唯一的,其他字段都可能重复
and a.s_sex = b.s_sex -- 性别相同
and a.s_name = b.s_name -- 名字相同
group by 1,2;
题目31
题目需求
查询1990年出生的学生信息
SQL实现
select *
from Student
where s_birth like '1990%'; -- 模糊匹配
题目32
题目需求
查询每门课程的平均成绩,结果按平均成绩降序排列;平均成绩相同时,按课程编号c_id升序排列
SQL实现
-- 自己的方法
select
c_id
,round(avg(s_score),2) avg_score
from Score
group by 1
order by 2 desc, c_id; -- 指定字段和排序方法
如果想带上课程的名称,需要和Course表进行联结
-- 自己的方法
select
c.c_id
,c.c_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Course c
on sc.c_id = c.c_id
group by 1,2
order by 3 desc, c.c_id; -- 指定字段和排序方法
题目33
题目需求
查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SQL实现
-- 自己的方法
select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id = s.s_id
group by sc.s_id,s.s_name
having avg_score >= 85;
题目34
题目需求
查询课程名称为数学,且分数低于60的学生姓名和分数
SQL实现
select
s.s_name
,sc.s_score
from Score sc -- 成绩表
join Student s -- 学生信息表
on sc.s_id = s.s_id
join Course c -- 课程表,指定数学
on sc.c_id = c.c_id
where c.c_name = '数学'
and sc.s_score < 60; -- 指定成绩
看看真正的数据,的确只有一个人满足
题目35
题目需求
查询所有学生的课程及分数情况
SQL实现
select
s.s_id
,s.s_name
,sum(case c.c_name when '语文' then sc.s_score else 0 end) as '语文' -- 语文分数
,sum(case c.c_name when '数学' then sc.s_score else 0 end) as '数学'
,sum(case c.c_name when '英语' then sc.s_score else 0 end) as '英语'
,sum(sc.s_score) as '总分' -- 每个人的总分
from Student s
left join Score sc
on s.s_id = sc.s_id
left join Course c
on sc.c_id = c.c_id
group by s.s_id, s.s_name; -- 学号和姓名的分组
题目36
题目需求
查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SQL实现
select
s.s_name
,c.c_name
,sc.s_score
from Score sc -- 成绩表
join Student s -- 学生信息表
on sc.s_id = s.s_id
join Course c -- 课程表
on sc.c_id = c.c_id
where sc.s_score > 70
group by s.s_name, c.c_name, sc.s_score;
题目37
题目需求
查询不及格的课程
SQL实现
select
sc.c_id
,c.c_name
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where sc.s_score < 60;
题目38
题目需求
查询课程编号为01且课程成绩大于等于80的学生的学号和姓名
SQL实现
select
sc.s_id
,s.s_name
,sc.s_score
from Score sc -- 成绩表
join Student s -- 学生信息表
on sc.s_id = s.s_id
join Course c -- 课程表
on sc.c_id = c.c_id
where c.c_id = 01
and sc.s_score >= 80;
题目39
题目需求
每门课程的学生人数
SQL实现
select
c_id
,count(s_id)
from Score
group by c_id;
如果想连接到课程名称:
-- 报错!!!
select
sc.c_id
,c.c_name
,count(sc.s_id)
from Score sc
join Course c
group by sc.c_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.c.c_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决的方法是将我们之前的结果作为临时表和Course表来连接查询:
select
c.c_name 课程名称
,c.c_id 课程编号
,t.num 人数
from Course c
join(select
c_id
,count(s_id) num
from Score
group by c_id)t -- 上面结果的临时表
on c.c_id = t.c_id;
题目40
题目需求
查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
SQL实现
1、我们先找出张三老师教了哪些课程
select
c.c_id
,c.c_name
from Course c
join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三';
2、找出哪些人修了数学
select
sc.s_id
,sc.s_score
from Score sc
left join Course c
on sc.c_id = c.c_id
left join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三';
3、通过max函数找出成绩的最高分
select
max(sc.s_score)
from Score sc
left join Course c
on sc.c_id = c.c_id
left join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三';
4、连接Student表,找出学生信息
select
s.*
,sc.s_score
,sc.c_id
,c.c_name
from Student s
left join Score sc
on s.s_id = sc.s_id
left join Course c
on sc.c_id = c.c_id
where sc.s_score in (select max(sc.s_score) -- 最大值90分的确定
from Score sc
left join Course c
on sc.c_id = c.c_id
left join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三');
题目41
题目需求
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SQL实现
select
a.s_id
,a.c_id
,a.s_score
from Score a
join Score b
on a.c_id != b.c_id
and a.s_score = b.s_score
and a.s_id != b.s_id;
我们对学号还需要去重下:
select
distinct a.s_id
,a.c_id
,a.s_score
from Score a
join Score b
on a.c_id != b.c_id
and a.s_score = b.s_score
and a.s_id != b.s_id;
再看看原始的数据中是否符合要求:
题目42
题目需求
查询每门功成绩最好的前两名
SQL实现
自己的方法
还需要好好优化的😭
-- 先找出语文的前2名同学
select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '语文' -- 改成数学和英语即可求出相应的信息
order by sc.s_score desc
limit 2;
将3门学科的信息进行拼接即可求出答案:
-- 最终脚本
(select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '语文'
order by sc.s_score desc
limit 2)
union
(select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '数学'
order by sc.s_score desc
limit 2)
union
(select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '英语'
order by sc.s_score desc
limit 2)
参考方法(好方法)
如何解决前几名排序的问题🐂🍺🚗太牛了
select
a.c_id
,a.s_id
,a.s_score
from Score a
where (select count(1) -- count(1)类似count(*):统计表b中分数大的数量
from Score b
where b.c_id=a.c_id -- 课程相同
and b.s_score >= a.s_score) <= 2 -- 前2名
order by a.c_id;
首先我们看看真实的数据,我们以01课程来进行解释上面的代码:
符合要求count(1)<=2
的只有两种情况
还需要好好理解下😭
题目43
题目需求
统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SQL实现
select
c_id
,count(s_score) num
from Score
group by c_id
having num > 5
order by num desc, c_id;
题目44
题目需求
检索至少选修两门课程的学生学号
SQL实现
结果显示全部满足要求
select
s_id
,count(*) num
from Score
group by s_id
having num >= 2;
题目45
题目需求
查询选修了全部课程的学生信息
SQL实现
自己的方法
1、全部的课程数目num
select count(*) from Course; -- 总共3门
2、从Score表分组统计每个人的课程数目,满足是3的学生信息
select
s_id
,count(c_id) num -- 课程数目
from Score
group by s_id
having num in (select count(*)
from Course); --满足全部课程
3、我们找出上面结果中的学生信息即可
select
s.*
,count(c_id) num -- 课程数目
from Score sc
join Student s
on sc.s_id = s.s_id
group by s.s_id
having num in (select count(*)
from Course); --满足全部课程
参考方法
select * -- 3、s_id对应的学生信息
from Student
where s_id in(select s_id -- 2、最大课程数对应的s_id
from Score
group by s_id
having count(*)=(select count(*) from Course) -- 1、全部课程数
)
题目46
题目需求
查询各学生的年龄:按照出生日期来算,当前月日 < 出生年月的月日则,年龄减1
SQL实现
自己的方法
-- 自己的方法
select *
,case when dayofyear(now()) >= dayofyear(s_birth) then year(now()) - year(s_birth)
when dayofyear(now()) < dayofyear(s_birth) then year(now()) - year(s_birth) - 1
else 'other' end as 'age'
from Student;
参考方法
select
s_name
,s_birth
,date_format(now(), '%Y') - date_format(s_birth, '%Y') - (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end) as age -- 当前日期大,说明已经过生了,年龄正常;反之说明今年还没有到年龄-1
from Student;
如何返回年份/日期
通过date_format
函数能够指定返回的数据
-- 两个方法
select year(now());
select date_format(now(), '%Y');
返回具体的日期:
题目47
题目需求
查询本周过生日的学生
select week(now()); -- 47
- DAYOFWEEK(date)
返回日期date的星期索引(1=星期天,2=星期一, ……7=星期六),符合国内标准
- WEEKDAY(date)
返回date的星期索引(0=星期一,1=星期二, ……6= 星期天),国外标准
SQL实现
自己的方法
select *
from Student
where week(s_birth) = week(now());
参考方法
select * from Student where week(date_format(now(),'%Y%m%d')) = week(s_birth); -- 方式1
select * from student where yearweek(s_birth) = yearweek(date_format(now(),'%Y%m%d')); -- 方式2
题目48
题目需求
查询下周过生日的学生
SQL实现
-- 自己的方法
select *
from Student
where week(s_birth) = week(now()) + 1; -- 往前推1周
-- 参考方法
select * from Student where week(date_format(now(),'%Y%m%d')) + 1= week(s_birth);
边界问题
如果现在刚好的是今年的最后一个周,那么下周就是明年的第一个周,我们如何解决这个问题呢??改进后的脚本:
-- 自己的方法
select * from Student
where mod(week(now()), 52) + 1 = week(s_birth);
当现在刚好是第52周,那么mod函数的结果是0,则说明出生的月份刚好是明年的第一周
题目49
题目需求
查询本月过生的同学
SQL实现
-- 自己的方法
select *
from Student
where month(s_birth) = month(now());
-- 参考方法
select * from Student where month(date_format(now(), '%Y%m%d')) = month(s_birth);
返回的是空值,是因为数据本身就没有在11月份出生的同学
题目50
题目需求
查询下月过生日的同学
SQL实现
-- 自己的方法
select * from Student
where month(s_birth) = month(now()) + 1; -- 推迟一个月
-- 参考方法
select * from Student
where month(date_format(now(), '%Y%m%d')) + 1= month(s_birth);
边界问题
假设现在是12月份,那么下个月就是明年的1月份,我们如何解决???将上面的代码进行改进:
select * from Student
where mod(month(now()),12) + 1 = month(s_birth);
如果现在是12月份,则mod函数的结果是0,说明生日刚好是1月份