| 45道SQL题目陆续更新 | 您所在的位置：网站首页 › sql日期范围筛选语句 › 45道SQL题目陆续更新 |
45道SQL题目陆续更新
|
文章目录
学习视频配置环境第一天内连接 + 外连接sql执行顺序
第二天group by 的用法
第三天第四天order bycase 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';补充知识点: 内连接 + 外连接从网上偷的图:
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 的用法 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; 除了出现在group by后面的字段，如果要在select后查询其他字段，必须用聚合函数进行聚合。所以这个代码运行错误，但是如果修改一下mysql的配置还是可以使用的。我这里没有修改，如果以后有需要再修改。 第三天5-1 查询所有李姓老师的数量 select count(a.Tname) as nums from teacher as a where a.Tname LIKE '李%'; like：模糊查找‘李%’：李后面可以加的文字不限定个数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) =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.score= 80 and 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 whencase when：case when score >= 60 then 1 else 0 end 先判断score是否大于60分，如果大于就返回1，否则返回0 按各科成绩进行排序，显示排名，分数重复继续排名 select *, row_number() over (partition by CId order by score desc) as 排名 from sc 把成绩安装CId进行分组，然后给每组按照降序排列（按照row_number()的排序规则）
"窗口”可以理解为记录集合，窗口函数就是在满足某种条件的记录集合上执行的特殊函数。 按照功能划分，可以把MySQL支持的窗口函数分为如下几类: 序号函数: row_number() / rank() / dense_rank()分布函数: percent_rank() / cume_dist()前后函数: lag() / lead()头尾函数: first_val() / last_val()其他函数:nth_value() / nfile()知识点:row_number()、rank()、dense_rank()的区别 row_number():依次排序,不会出现相同排名rank():出现相同排名时，跳跃排店dense_rank():出现相同排名时,连续排序窗口函数的基本用法如下: 函数名 ([expr]) over 子句over是关键字，用来指定函数执行的窗口范围。 第五天 查询学生的总成绩并进行排名，总成绩重复时保留名额空缺 select SId,sum(score) sum_score,rank() over (order by sum(score)) as Rank_ from sc group by SId;
思路：各科成绩分类进行排序列一个表，然后从这个表里取前三名的出来。（）表要起别名 select* from (select*,dense_rank() over(partition by CId order by score) as cid_rank from sc) a where cid_rank= 85 ) as b on a.Sid = b.Sid;
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
子句就是上一题 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
year：代表相隔的年份，这里也可以换为day:就是相隔的天数， hour:相隔的时间 select student.Sname, timestampdiff(year,student.Sage,now()) as 年龄 from student
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())
|
27、查询课程为数学，且分数低于60的学生姓名和分数
30、查询存在不及格的课程
32、求每门课程的学生人数
33、假设成绩不重复，查询选修"张三"老师课程的学生中，成绩最高学生信息及成绩
39、查询选修全部课程的学生信息
40、查询各学生的年龄，只按年份来算
41、按照出生日期来算（当没到生日的时候，年龄就减一）
42、查询本周过生日的同学信息
【本文地址】
公司简介
联系我们