45道SQL题目陆续更新 学习视频 配置环境 45、查询下月过生日的学生信息

Posted on by hackdl

文章目录

  • 学习视频
  • 配置环境
    • 第一天
      • 内连接 + 外连接
      • sql执行顺序
    • 第二天
      • group by 的用法
    • 第三天
    • 第四天
      • order by
      • case when
      • 窗口函数
    • 第五天
    • 第六天
    • 第七天
      • limit
      • 第八天
  • 45、查询下月过生日的学生信息

学习视频

学习视频

配置环境

  • 四张表
  • 配置四张表的sql语句
#创建发据库
create database frogdata charset=utf8；

use frogdata;

# 学生表 Student 
create table Student(
SId varchar(10), 
Sname varchar(10), 
Sage datetime, 
Ssex varchar(10));

# 教师表 Teacher
create table Teacher(
TId varchar(10), 
Tname varchar(10));

# 科目表 Course
create table Course(
CID varchar(10),
Cname nvarchar(10),
TId varchar(10)
);

# 成绩表 SC
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1));




# 学生表
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','郑竹','1999-07-01','女');
insert into Student values('09','张三','2017-12-20','女');
insert into Student values('10','李四','2017-12-25','女');
insert into Student values('11','李四','2017-12-30','女');
insert into Student values('12','赵六','2017-01-01','女');
insert into Student values('13','孙七','2018-01-01','女');

# 教师表
insert into Teacher values('01','张三');
insert into Teacher values('02','李四');
insert into Teacher values('03','王五');

# 科目表Course
insert into Course values('01','语文','02');
insert into Course values('02','数学','01');
insert into Course values('03','英语','03');


# 成绩表SC
insert into SC values
    ('01', '01', 80),
    ('01', '02', 90),
    ('01', '03', 99),
    ('02', '01', 70),
    ('02', '02', 60),
    ('02', '03', 80),
    ('03', '01', 80),
    ('03', '02', 80),
    ('03', '03', 80),
    ('04', '01', 50),
    ('04', '02', 30),
    ('04', '03', 20),
    ('05', '01', 76),
    ('05', '02', 87),
    ('06', '01', 31),
    ('06', '03', 34),
    ('07', '02', 89),
    ('07', '03', 98);
  • 配置好的环境



第一天

  • from 后面紧接的基础表会放在开头
  • on 后面放与基础表的条件

1-1 查询01课程比02课程成绩高的学生信息和课程分数

# 第一步:通过Sid主键连接学生表和成绩表
select* from Student a inner join SC b on a.SId = b.SId;

# 第二步:进行同一学生不同成绩比较,利用Sid相同,Cid不同进行关联
select* from Student a 
inner join SC b on a.SId = b.SId inner join SC c on a.SId = c.SId 
and b.CId = '01' and c.CId = '02';

# 最后:回到题目,利用where比较分数即可
select* from Student a
inner join SC b on a.SId = b.SId inner join SC c on a.SId = c.SId
and b.CId = '01' and c.CId = '02'
where b.score > c.score;

1-2 查询存在 01 课程但可能不存在 02 课程的情况(不存在的时候显示为null)

# 第一步:实现1名学生的2门课程在同一行,左边的Cid等于 01 课程, 右边Cid等于 02 课程
select * from SC a
inner join SC b on a.SId = b.SId
where a.cid = '01' and b.CId = '02';

# 最后:需要用到left JOIN,实现1个学生两个课程在同一行,左边 01 课程,右边 02 课程
# and b.CId = '02';  表示 a 这个表只显示01课程若无则显示null
# where a.CId = '01';  这是一个显示条件，若无就不显示 
select * from SC a
left join SC b on a.SId = b.SId
and a.CId = '01'and b.CId = '02' 
where a.CId = '01';

1-3 查询不存在 01 课程但存在 02 课程的情况

# 第一步:筛选出存在 01 课程的学生
select sid from SC where cid = '01';
# 最后:使用not in 筛选出不存在 01 课程,但是存在 02 课程的学生
select* from SC
where sid not in(select sid from SC where cid = '01') and cid = '02';

补充知识点:

内连接 + 外连接

从网上偷的图:



sql执行顺序

from->on->join->where->group by->having+聚合函数->select->order by->limit
  • on的优先级高于join，那就说明在联表前，会对表提前进行过滤，形成一张更小的临时表，然后再进行join联表，接着就对连表的结果进行where过滤
  • 在使用内连接的情况下on和where差别不大，
  • 但是使用外连接的时候就会有差距了，那么来看看join是怎么执行的，我们的优化器首先会选中一个表作为驱动表，然后我们的执行器从存储引擎中取出这个表中的所有数据，这个表中的每一行数据会去另一个表进行连表操作，如果我们能提前用on条件去缩小我们的这张驱动表，这样连表的速度就会更快。

第二天

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

提示：要求查询平均成绩大于等于60分的同学信息，首先确定是在成绩表里面找，找到了这样的同学后，是不是用sid去学生信息表里面关联就可以得到学生的姓名信息，在这里的关键就是找sid。

select a.sid,a.Sname,b.avg_score
from student as a
inner join
(select sid,avg(score) as avg_score from SC
group by SId having avg_score>=60) as b
on a.sid = b.sid;
  • 子语句可以查询成绩表中平均成绩大于60的sid

group by 的用法

  1. group by 的用法
select 
	聚合函数，
	列（要求出现在group by的后面）
from 
	表
where 
	筛选条件
group by 
	分组的列表
order by 
	子句

注意：除了出现在group by后面的字段，如果要在select后查询其他字段，必须用聚合函数进行聚合

  • 特点：分组查询中的筛选条件分为两类：

    • where：对分组前的表进行筛选，所以放在group by前面
    • having：对分组后的表进行筛选，所以放在group by后面

  • 因为执行顺序：from->on->join->where->group by->having+聚合函数->select->order by->limit

3-1、查询在sc表存在成绩的学生信息

提示：成绩表肯定都是有学生的，所以用左关联就可以得到学生的信息。

select b.*from (
select SId
from sc 
group by SId) as a

left join student as b
on a.SId = b.SId
  • 子语句可以查询所有成绩表中的SId，结果作为主表。

4-1：查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（没成绩的显示为null）
解法一：子查询

select a.SId,a.sname,b.counts,b.sums
from student as a
left join
(SELECT SId,count(CId) as counts,sum(score) as sums
from sc
group by SId) as b
on a.SId = b.SId;
  • 子语句使用聚合函数，算出：选课总数、所有课程的总成绩，根据SId分组

解法二：

select a.SId,a.sname,count(b.CId) as counts,sum(b.score) as sums
from student as a
left join sc as b
on a.SId = b.SId
group by a.SId;
  1. 除了出现在group by后面的字段，如果要在select后查询其他字段，必须用聚合函数进行聚合。
  2. 所以这个代码运行错误，但是如果修改一下mysql的配置还是可以使用的。
  3. 我这里没有修改，如果以后有需要再修改。

第三天

5-1 查询所有李姓老师的数量

select count(a.Tname) as nums
from teacher as a
where a.Tname LIKE  '李%';
  1. like：模糊查找
  2. ‘李%’：李后面可以加的文字不限定个数

6-1 查询学过【张三】老师授课的同学的信息

select a.* from student a
inner join SC b on a.sid = b.SId
inner join course c on b.CId = c.CID
inner join teacher d on c.TId = d.TId
where d.Tname = '张三';

子句主要作用是从sc表中找出张三学生的SId，然后通过SId关联到学生表，找到学生信息

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

select b.*
from(select sc.SId from sc
group by sc.SId
having count(sc.CId)  (select count(CId)from course )) AS a

inner join student as b
on a.SId = b.SId;
  1. count(sc.CId)
  2. 子句主要是找出没有学全所有课程的SId

8-1：查询至少一门课与学号为01的同学所学相同的同学的信息

  • 关键点：先查询到01同学所学的课程CId，IN DISTINCT 也可以用group by 代替DISTINCT
select distinct a.*
from student a
inner join sc b on a.SId = b.SId

where b.CId IN (select sc.CId from sc where sc.SId = '01');

子句主要是查询01学生所学的所有课程

9-1：查询和01号同学学习课程完全相同的其他同学信息

  • 关键点：1、要没有学习01号同学以外的其他课程 （1）01号同学学习了哪些课程，（2）找到学习1号同学以外课程的同学
  • SELECT cid from sc where sid = ‘01’ ：01号同学学习课程
  • SELECT sid FROM sc WHERE cid NOT IN (SELECT cid from sc where sid = ‘01’ )：筛选出与01课程同学所学不一样的同学的信息。
  • 两次用NOT IN 第一次筛选出与01课程同学所学不一样的同学的信息，第二次把这些不一样的同学筛选掉留下一样的，然后再用group by 保证课程个数一样

2、课程的数量要保持一致

select a.*
from student a
inner join sc b
on a.SId = b.SId
where b.SId not in (select sc.SId from sc where sc.CId not in 
(select CId from sc  where SId = '01')) and b.SId != '01'

group by a.SId
having COUNT(*) = (select COUNT(*) from sc where SId = '01');

10-1：查询没有学过“张三”老师讲授任意一门课程的学生姓名

select student.* from student
where student.SId not in
(
select c.Sid from teacher as a
inner join course as b on a.TId = b.TId
inner join sc as c on b.CId = c.CId
where a.Tname = '张三'
);
  1. 子句是查询张三老师教的学生Sid
  2. inner join course as b on a.TId = b.TId：找出与老师相匹配的课程 形成一个表
  3. inner join sc as c on b.CId = c.CId；再刚刚形成表的基础上找到相应的学生

第四天

  1. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.sid,sname,avg from student,
(
select sid,avg(score) avg from sc 
where score60 
group by sid having count(cid)>=2
) t 
where student.sid=t.sid;
  1. 子句的作用是查询两门课程不及格的sid
  2. 子句先where形成一个表，然后再用having进行筛选形成一个表
  1. 检索” 01 “课程分数小于 60，按分数降序排列的学生信息
select student.* from student,
(select sid,score from sc where cid='01' and score60) t 
where student.sid=t.sid order by t.score desc;

子句的作用是：筛选 01 课程分数小于60的sid

order by

order by 字段名 desc;
desc:降序排列
asc:升序排列（默认值）
  1. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select sc.*,avg from sc,
(select sid,avg(score) avg from sc group by sid) t 
where sc.sid=t.sid order by t.avg desc;
  1. 查询各科成绩最高分、最低分和平均分：

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

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

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

select a.CId,b.Cname,count(*) as 选修人数,
max(a.score) as 最高分, min(a.score) as 最低分, avg(a.score) as 平均分,
# 把大于等于60分的人数进行求和/总数 = 及格率
(sum(case when a.score>= 60 then 1 else 0 end)/count(*)) as 及格率,
(sum(case when a.score>= 70 and a.score80 then 1 else 0 end)/COUNT(*)) as 中等率,
(sum(case when a.score>= 80 and a.score90 then 1 else 0 end)/COUNT(*)) as 优良率,
(sum(case when a.score>= 90 then 1 else 0 end)/COUNT(*)) as 优秀率
from sc as a
inner join course as b
on a.CId = b.CId
group by a.CId
# 查询结果按人数降序排列，若人数相同，按课程号升序排列
order by 选修人数 desc,CId asc;

case when

case when：case when score >= 60 then 1 else 0 end
先判断score是否大于60分，如果大于就返回1，否则返回0

  1. 按各科成绩进行排序，显示排名，分数重复继续排名
select *,
row_number() over (partition by CId order by score desc) as 排名 
from sc
  1. 把成绩安装CId进行分组，然后给每组按照降序排列（按照row_number()的排序规则）

窗口函数

  • MySQI从8.0开始支持窗口函数

“窗口”可以理解为记录集合，窗口函数就是在满足某种条件的记录集合上执行的特殊函数。

按照功能划分，可以把MySQL支持的窗口函数分为如下几类:

  1. 序号函数: row_number() / rank() / dense_rank()
  2. 分布函数: percent_rank() / cume_dist()
  3. 前后函数: lag() / lead()
  4. 头尾函数: first_val() / last_val()
  5. 其他函数:nth_value() / nfile()

知识点:row_number()、rank()、dense_rank()的区别

  1. row_number():依次排序,不会出现相同排名
  2. rank():出现相同排名时，跳跃排店
  3. dense_rank():出现相同排名时,连续排序

窗口函数的基本用法如下:
函数名 ([expr]) over 子句over是关键字，用来指定函数执行的窗口范围。

第五天

  1. 查询学生的总成绩并进行排名，总成绩重复时保留名额空缺
select SId,sum(score) sum_score,rank() over (order by sum(score)) as Rank_
from sc
group by SId;

  1. 查询各科成绩前三名的记录

思路：各科成绩分类进行排序列一个表，然后从这个表里取前三名的出来。（）表要起别名

select*
from
(select*,dense_rank() over(partition by CId order by score) as cid_rank
from sc) a
where cid_rank3


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

思路：先拿出各个科目总人数（count（） group by），然后进行case when分类，然后再取百分数

select CId,
concat(sum(case when score60 then 1 else 0 end)/count(SId)*100,'%') as '[0-60]所占百分比',
concat(sum(case when score>60 and score70 then 1 else 0 end)/count(SId)*100,'%') as '(60-70]所占百分比',
concat(sum(case when score>70 and score85 then 1 else 0 end)/count(SId)*100,'%') as '(70-85]所占百分比',
concat(sum(case when score>85 then 1 else 0 end)/count(SId)*100,'%') as '(85-100]所占百分比'
from sc
group by CId

  1. 查询每门课程被选修的学生数
select CId,count(CId) as '选修的学生数'
from sc
group by CId


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

select s.Sname,sc.SId
from student s
inner join sc
on s.SId=sc.SId
group by SId
having count(CId)=2

第六天

21、查询男生、女生人数

select Ssex,count(*)
from student 
group by Ssex


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

select *
from student 
where Sname like "%风%"


23、查询同名同性学生名单，并统计同名同姓人数
思路：重复一个表，过滤ID不同但名字相同的人，使用自连接

select *
from student a 
inner join student b 
on a.SId!=b.SId 
and a.Sname=b.Sname 
and a.Ssex=b.Ssex


24、查询1990年出生的学生信息

select *
from student
where year(Sage)=1990

# 取左侧4个字符
select
*
from student
where left(student.Sage,4) = '1990'


25、查询每门课程平均成绩，并降序排列，平均成绩相同，则按课程编号升序排列

select CId,avg(score)
from sc
group by CId
# 按照平均成绩降序，成绩相同时课程编号升序
order by AVG(score) desc, cid asc;

第七天

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

select a.Sname, b.sid, b.avg_score
from student a
inner join
(
select sid,avg(score) as avg_score
from SC 
group by sid
having avg_score >= 85
) as b
on a.Sid = b.Sid;


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

select a.Sname,b.score
from student a
inner join sc b on a.SId = b.SId
inner join course c on b.CId = c.CID
where c.Cname = '数学' and b.score  60


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

select a.Sname, c.Cname, b.score
from student as a
left join sc as b on a.SId = b.SId
left join course as c on b.CId = c.CID

29、查询任意一门课程成绩在70分以上的学生姓名、所选课程名称和分数

select a.Sname, c.Cname, b.score
from student as a
inner join sc as b on a.SId = b.SId
inner join course as c on b.CId = c.CID
where b.score > 70


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

select distinct b.Cname
from sc as a
inner join course as b on a.CId = b.CId
where a.score  60


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

select a.sid,a.Sname
from student as a
inner join sc as b on a.SId = b.SId
where b.score >= 80 and b.CId = '01'


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

select b.Cname,count(*)
from sc as a
inner join course as b on a.CId = b.CId
group by b.Cname


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

select *
from student as a
inner join sc as b on a.SId = b.SId
inner join course as c on b.CId = c.CId
inner join teacher as d on c.TId = d.TId
where d.Tname = '张三'
order by b.score desc
limit 1;

limit

limit子句用于限制查询结果返回的数量

34、假设成绩重复，查询选修”张三”老师课程的学生中，成绩最高学生信息及成绩

  • 可能会出现多个并列最高分
# 使用窗口函数,会显示排名
# 排好名后只取第一名,
# 因为使用的是dense_rank()所以第一名可以有多个,只要满足a.score_rank = 1 都可以显示

select * from
(
select a.*, b.score, c.Cname,d.Tname, dense_rank() over (order by b.score desc) as score_rank
from student as a
inner join sc as b on a.SId = b.SId
inner join course as c on b.CId = c.CId
inner join teacher as d on c.TId = d.TId
# 如果直接在where后面写 score_rank = 1会报错,说没有这行,所以作为子表,然后再筛选一遍
where d.Tname = '张三'
) as a
where a.score_rank = 1

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

#自连接
select distinct a.*
from sc as a
inner join sc as b on a.SId = b.SId
where a.CId != b.CId and a.score = b.score

第八天

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

  • 使用窗口函数进行分组和排序
select *
from 
(
select a.SId, a.Sname, b.CId, b.score, row_number() over (partition by b.cid order by b.score desc) as _Rank

from student as a
inner join sc as b on a.SId = b.SId
) as c
where _Rank  2


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

子句作用：把每门课进行分组，并且进行排序

select b.CId,a.Sname,b.score,b._rank
from student as a
inner join
(
select* ,  rank() over(partition by sc.CId order by sc.score desc) as _rank
from sc
) as b on a.SId = b.SId
where b._rank  2
order by b.CId


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

select sc.CId , count(*) as CId_count
from sc
group by sc.CId
having count(*) >= 5


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

select sc.SId, count(*) as SId_count
from sc
group by sc.SId
having count(*) >= 2


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

子句就是上一题

select * 
from student as a
inner join
(
select sc.SId, count(*) as SId_count
from sc
group by sc.SId
having count(*) >= 2
) as b on a.SId = b.SId
where b.SId_count = 3


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

select student.Sname, (year(now()) - year(student.Sage)) as 年龄
from student


41、按照出生日期来算（当没到生日的时候，年龄就减一）

year：代表相隔的年份，这里也可以换为day:就是相隔的天数， hour:相隔的时间

select student.Sname, timestampdiff(year,student.Sage,now()) as 年龄
from student


42、查询本周过生日的同学信息

week(NOW())：返回此时在当年的周数

select s.SId,s.Sname,s.Sage
from student s 
where week(s.Sage) = week(NOW())

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

select s.SId,s.Sname,s.Sage
from student s 
where week(s.Sage) = week(now()) + 1

44、查询本月过生日的同学信息

month(NOW())：返回此时在当年的月数

select s.*
from student s 
where month(s.Sage) = month(NOW())

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

select s.*
from student s 
where month(s.Sage) = month(NOW()) + 1

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: