SQL语句整理（3）｜青训营笔记这是我参与「第五届青训营」伴学笔记创作活动的第 11 天以实例分析（三）CRUD

这是我参与「第五届青训营」伴学笔记创作活动的第 11 天

以实例分析（三）CRUD

sql语句练习50题(Mysql版)_启明星的指引的博客-CSDN博客_sql 练习

学生表 / 课程表 / 成绩表 / 教师表

学生表： s_id， s_name， s_birth， s_sex
课程表： c_id， c_name， t_id
- 01
- 02
教师表： t_id， t_name
成绩表： s_id， c_id， s_score

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

select a.* ,b.s_score as 01_score,c.s_score as 02_score 
from 
student a join score b 
on a.s_id=b.s_id 
and
b.c_id='01'left join score c 
on a.s_id=c.s_id 
and c.c_id='02' 
or c.c_id = NULL 
where b.s_score>c.s_score
--也可以这样写
select a.*,b.s_score as 01_score,c.s_score as 02_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

查询"01"课程比"02"课程成绩高的学生的信息及课程分数 - 学生信息 —— 来自表 a

课程分数 01>02
- 01 —— 来自表 b
- 02 —— 来自表 c

前一种方案，用 join 和 left join。后一种选择方案都写在 where 里面

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

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,b.s_name 
HAVING avg_score >=60;

Join 和 On 搭配，Join 写对应表/内容，on 写选择条件

3.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

select b.s_id, b.s_name, ROUND(AVG(a.s_score),2) as avg_score
from student b left join score a
on b.s_id = a.s_id
group by b.s_id, b.s_name
having avg_score < 60

union

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);

查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

有成绩 union 无成绩
小于60分 having ...<60

having 后面可以添加“聚合函数”，搭配 union where 后面是返回结果

4.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select a.* 
from student a 
where a.s_id in 
(select s_id from score where c_id='01' ) 
and a.s_id 
not in(select s_id from score where c_id='02')

查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

套用子查询的流程
- 套用在 where 的 in 中
- 子查询和父查询有“外键”
学过编号为"01" in 子查询找外键
没有学过编号为"02" not in 子查询找外键

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

select a.s_name 
from student a
where a.s_id
not in (
	-- 找到c_id的表，返回s_id
	select s_id 
	from score
	where c_id =(
		-- 找到t_id的表，返回c_id
		select c_id 
		from corse
		where t_id =(
			-- 找到'张三'的表，返回t_id
			select t_id
			from teacher
			where t_name = '张三'
		)
	)
)

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

总查询是“学生姓名”
- 学生姓名表需要学生 id
  - 学生 id 需要课程 id
    - 课程 id 需要老师 id
      - 老师 id 对应老师姓名

6.查询不同老师所教不同课程平均分从高到低显示

select a.t_id, c.t_name, a.c_id, ROUND(avg(s_score),2) as avg_score
from course a
left join score b on a.c_id = b.c_id
left join teacher c on a.t_id = c.t_id
group by a.c_id, a.t_id, c.t_name
order by avg_score desc;

查询不同老师所教不同课程平均分从高到低显示

Desc 是降序
Asc 是升序

SQL 的连接

SQL语句整理（3）｜ 青训营笔记