45道SQL题目陆续更新

学习视频

学习视频

配置环境

• 四张表
  
• 配置四张表的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

知识点补充：

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 aleft 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 
b.*
from( select SId from sc 
inner join course on sc.CId = course.CId
inner join teacher on course.TId = teacher.TId
where teacher.Tname = '张三') as ainner join student as b
on a.SId = b.Sid;

子句主要作用是从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 ainner join student as b
on a.SId = b.SId;

1. count(sc.CId) < (select count(CId)from course )：课程总数大于学生选择数
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.SIdwhere 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；再刚刚形成表的基础上找到相应的学生