数据库多表联查

一、内联查询

内联查询只有完全满足条件的数据才能出现的结果

1.1 非等值联查

笛卡尔积，查到的结果具有不一致性
示例：	select * from student,class

1.2 等值查询

-- 查询出学生表和班级信息select * from student,class where student.classid=class.classid

-- 5张表全部联查select * from student,class,course,sc,teacher where                    	      student.classid=class.classid and student.sid=sc.sidand sc.cid=course.cid and course.tid=teacher.tid

-- 查询学过张三老师课程的学生信息select student.* from student,class,course,sc,teacher where student.sid=sc.sidand sc.cid=course.cid and course.tid=teacher.tid and teacher.tname='张三'

-- 多表最终跟单表一致
-- 查询每个学生的 学生信息，班级名称，平均成绩select student.Sname,class.classid,avg(score) from student,class,sc where student.sid=sc.sid and student.classid=class.classid group by student.sid

-- 通过第一张表的结果进行on条件匹配
-- 表少 每张表的数据大，内存占用小， IO高select * from student,class where student.classid=class.classid ssex='男'

1.3 inner join on

表的个数多，每个表的数据量不大,吃内存  IO小，笛卡尔积

-- 通过第一张表的结果进行on条件匹配select * from student inner join class on student.classid=class.classid
where ssex='男'select * from student inner join class on student.classid=class.classid
inner join sc on student.sid=sc.sid -- 五表联查
select * from class inner join student on class.classid=student.classid
inner join sc on sc.sid=

-- 每门课程的平均成绩  课程名称  代课老师姓名 平均成绩select cname,tname,avg(score) from sc inner join course on sc.cid=course.cidinner join teacher on course.tid=teacher.tidgroup by course.cid

二、外联查询

找到主查表

2.1 left join on 左外联

主表在join左边

-- 所有学生的数据和对应的班级信息
-- 主表是studentselect * from student left join class on student.classid=class.classid

-- 查询出所有的学生学过多少门课程 学生姓名 课程数select sname,count(cid) from student left join sc on student.sid=sc.sid
group by student.sid-- 没有班级 的学生select * from student left join class on student.classid=class.classid
where class.classid is null-- 没有学生的班级select * from class left join student on student.classid=class.classid
where student.sid is null

2.2 right join on 右外联

主表在join右边

-- 没有学生的班级select * from student right join class on student.classid = class.classidwhere student.sid is null

2.3 union

两个结果集的并集
（1） 去除重复 distinct
（2）不同类型的字段是可以合并
（3）起别名给第一个结果集才有用
（4）不同列数量的结果集不能合并

-- 库中的所有人的名字select sname,ssex,classid from student unionselect tname,tsex,temail from teacher-- 获取没有学生的班级和没有班级的学生的数据select * from student left join class on student.classid=class.classidwhere class.classid is nullunion select * from student right join class on student.classid=class.classidwhere student.sid is null-- 获取没有班级的学生和、班级和学生都有、
-- 全连接select * from student left join class on student.classid=class.classidunion select * from student right join class on student.classid=class.classid-- 不去重的并集select * from student left join class on student.classid=class.classidunion allselect * from student right join class on student.classid=class.classid

三、子查询

子查询，又称内部查询

3.1 where 子句

--查询id最大的一个学生select * from student order by sid desc limit 1-- 查询id最大的一个学生（子查询）
-- 效率差select max(sid) from studentselect * from student where sid=(select max(sid) from student)  -- 魔数-- 查询每个班id最大的学生（子查询）select * from student left join class on student.classid=class.classidwhere sid in(select max(sid) from student group by classid)-- 查询学过张三老师课程的学生select * from student where sid in(select sid from sc where cid=(select cid from course where tid=(select tid from teacher where tname='张三'))	)-- 查询没学过张三老师课程的学生select * from student where sid not in(select sid from sc where cid=(select cid from course where tid=(select tid from teacher where tname='张三'))	)

3.2 from 子查询

查询结果将作为一张表使用

-- 查询大于5人的班级名称和人数（不使用子查询）select classname,count(*) from class left join student on       class.classid=student.classidgroup by class.classid having count(*)>5-- 查询大于5人的班级名称和人数（使用子查询）select classname,人数 from class left join (select classid,count(*) 人数from student group by classid)t1on class.classid = t1.classidwhere 人数>5

3.3 exists 子查询

子句有结果，父句执行，子句没结果，父句不执行

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

3.4 any /some,all

子查询-- 查询一班成绩比二班最低分高的学生SELECT DISTINCT student.* FROM sc  LEFT JOIN student ON sc.sid=student.sid WHERE student.classid=1 AND score > ANY(SELECT score FROM scLEFT JOIN student  ON sc.sid=student.`Sid`WHERE student.classid=2)

-- allSELECT DISTINCT student.* FROM scLEFT JOIN student ON student.sid=sc.sid WHERE student.classid=1 AND score >ALL(SELECT score FROM scLEFT JOIN student ON sc.sid=student.`Sid`WHERE student.`classid`=2)

3.5 IF(expr1,expr2,expr3)

（1）expr1 条件
（2）expr2 条件成立，显示数据
（3）expr 条件不成立，显示数据

 -- 1女-- 0男SELECT *FROM teacher ;SELECT tid,tname,IF(tsex=1,'女','男')tsex,tbirthday,taddress FROM teacher;

3.6 IFNULL(expr1,expr2)

 -- expr1 字段-- expr2 当字段为null时，默认值

select sid,sname,ifnull(birthday,'这个学生没有生日'),ssex from student

3.7 case when then end

同时出现

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

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

select score,sid,
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