数据库查询秘诀：从内联到外联，掌握多表联查的艺术

在这个指南中，我们将深入探讨多表联查的各种高级技巧，包括内联、外联查询以及子查询的妙用。通过具体案例和示例代码，帮助你更高效地构建复杂的数据库查询，提升SQL技能，轻松应对各种数据分析挑战。无论你是SQL新手还是老手，这里都有你所需的一切

多表联查

内联查询

$只有完全满足关系（主外键关系）的条件才能实现$

1. 非等值 表连接条件是非等值关系

   select * from student,class;
   

2. 等值联查 表连接的条件是等值关系

   1. 查询每个学生的平均成绩 学生姓名 学生班级 平均成绩

   select student.Sname , class.classname , avg(sc.score) 
   from student,sc,class 
   where student.classid = class.classid and student.sid = sc.Sid 
   group by student.sid;
   

   这种写法 是先拿到所有数据然后根据 where 中条件进行筛选

   优点：适合表的个数多，但是表中数据量不大的情况 IO较小

   缺点：所占内存空间大

   2. 使用inner join on 查询每门课平均成绩 课名 代课老师姓名 平均成绩

   select course.Cname,teacher.Tname,avg(sc.score)from sc
   inner join course on sc.cid = course.Cid
   inner join teacher on course.tid = teacher.Tid
   group by course.cid,teacher.Tname
   

   使用 inner join on 是通过第一张表后的结果集进行on后的匹配

   优点：适合表少，但是表中数据量大的情况，并且内存占用较小

   缺点：IO较高

外联查询

一定要先确定主查表(有主键的)是那一张（尽量使用小表去连接大表 可以减少损耗）

1. left join on左外联 主查表写在左边

select * from student 
left join class on  student.classid = class.classid;

2. right join on 右外联 主表写在右边

select * from class 
right join student on  student.classid = class.classid;

案例：

• 查询所有学生学过多少们课程 学生姓名 课程数

-- 左外联
select student.Sname,count(sc.Cid) 
from student
left join sc on student.sid = sc.Sid
group by student.Sname,student.Sid;

-- 右外联
select student.Sname,count(sc.Cid) 
from sc
right join student on student.sid = sc.Sid
group by student.Sname,student.Sid;

union

union(去重 机制与distinct) 求俩个结果集的并集

union all 查询出所有数据（不去重）

特点：

• 不同类型的字段可以合并
• 不同列数量的结果集不可以合并
• 表头为第一个结果集 （起别名给第一个结果集才有用）

案例：

1. 获取没有学生的的班级和没有班级的学生

select * from student
right join class on student.classid = class.classid
where student.Sid is null
union 
select * from student
left join class on student.classid = class.classid
where class.classid is null

2. 获取没有班级的学生和班级和学生都有的还要获取没有学生的班级（为全连接）

select * from student
right join class on student.classid = class.classid
union
select * from student
left join class on student.classid = class.classid

子查询（效率低）

where型子查询

所有子查询都需写在括号（）中

案例：

1. 查询id最大的一个学生

select * from student where sid = (select max(sid) from student);

2. 查询每个班id最大的学生

select * from student
where sid in(select max(sid) from student group by classid) 

3. 查询学过张三课程的学生

select * from student where sid in
(
select sid from sc where cid =
(
select cid from course where tid =
(select tid from teacher where tname = '张三')
)
)

from 子查询

查询结果作为一张表使用

案例：

1. 查询大于5 人的班级名称和人数

select classname,人数 from class 
left join
(select classid ,count(*) 人数
 from student 
 group by classid) t1 
 on class.classid = t1.classid
where 人数 > 5;

exits

字句有结果，父句执行 反之，父句不执行

select * from teacher
where exists (select * from student where classid = 10)

any/some查询一班成绩比二班最低成绩高的学生

表示满足其中任意一个条件

select distinct student.* from sc 
left join student on sc.sid = student.Sid
where student.classid = 1 and 
score > any(select score from sc 
left join student on sc.Sid = student.Sid
where student.classid = 2)

all 满足其中所有条件

表示满足其中所有条件，ALL关键字与any关键字类似，只不过上面的or改成and。

假设any内部的查询语句返回的结果个数是三个

1. 查询一班成绩比二班最高成绩高的学生

select distinct student.* from sc 
left join student on sc.sid = student.Sid
where student.classid = 1 and score > all(
select score from sc 
left join student on sc.Sid = student.Sid
where student.classid = 2)

结果集的控制语句

if语句

格式：if(条件,条件成立 显示的数据,条件不成立显示的数据)

1. 如果tsex=1 则显示女 ；否则为男

select tid,tname,if(tsex=1,'女','男') tsex,tbirthday,Taddress from teacher

IFNULL

格式：IFNULL(expr1,expr2) 字段名 ，默认值

1. 如果birthday字段中为null则默认为’可怜‘

select sid,sname,ifnull(birthday,'可怜'),ssex from student

case/when/then/end

1. 如果tsex=1 则显示女 ；否则为男

select tid,tname,
case Tsex
	when 0 then '男'
	when 1 then '女'
	else '保密'
end tsex,tbirthday from teacher

2. 如果tsex=1 则显示女 ；否则为男

	select tid,tname,
	case 
		when tsex > 1 then  '男'
		when tsex = 1 then  '女'
		when tsex < 1 then  '未知'
end
	tsex,tbirthday from teacher

案例：

1. 查询学生的成绩，

   并将大于90分的用A显示，

   大于80分的用B显示，

   大于70分的用C显示，

   大于60分的用D显示，

   小于60分的显示不及格

   	select score,
   	case 
   		when score>90 then 'A'
   		when score>80 then 'B'
   		when score>70 then 'C'
   		when score>=60 then 'D'
   		when score<60 then '不 及格'
   		end 
   		from sc
   

2. 查询各个分数段的人数

-- 方式一
select 
case
	when score <=100 and score >=90 THEN '90-100'  
    when score <90 and score >=70 THEN '70-90'  
    when score <70 and score >=60 THEN '60-70'   
    else '不及格'  
end 分数段 ,count(*) 人数 from sc
group by 分数段
order by 分数段 desc
	
-- 方式二
select '90-100' 分数段,count(*) 人数 from sc 
where score <=100 and score >=90
union
select '70-90' 分数段,count(*) 人数 from sc 
where score <90 and score >=70
	
-- 方式三
	select '人数' 分数段,
	count(case when score <=100 and score >=90 then score end) '100-90',
	count(case when score <90 and score >=70 then score end) '90-70',
	count(case when score <70 and score >=60 then score end) '70-60',
	count(case when score <60 then score end) '不及格'
	from sc