高级数据查询语句-多表联查

一、多表联查

        多表联查可以通过连接运算实现，即将多张表通过主外键关系关联在一起进行查询。下图提供了多表联查 时用到的数据库表之间的关系。 

1.内联查询 

        只有完全满足条件(主外键关系)的数据才能出现的结果

 1.1 非等值联查  

        语法：非等值查询：SELECT * FROM 表1，表2

        注意：非等值联查：笛卡尔积 逻辑上有错误，表中标红的一行数据是有错误的，班级编号为1，但是显示数据在二班

-- 非等值联查   -- 笛卡尔积 逻辑上有错误
select * from  student,class;

 

1.2  等值联查

        语法：等值查询：SELECT * FROM 表1，表2 WHERE 表1.字段1 = 表2.字段2...

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

 

1.3 五张表全部联查出来

-- 5张表全部联查出来
select * from student,class,sc,course,teacher
where student.classid=class.classid and student.Sid=sc.Sid and sc.Cid=course.Cid and course.Tid=teacher.Tid;

 

1.4 面试题！！！ 

-- 查询出学生学过张三老师课程的学生信息(面试！！！)
select * from student;
select * from teacher;
select student. * from student,sc,course,teacher 
where  student.Sid=sc.Sid and sc.Cid=course.Cid and course.Tid=teacher.Tid and teacher.Tname='张三';

 

-- 查询每个学生的平均成绩 学生姓名 班级名称 平均成绩
select student.Sname ,class.classname ,avg(score) from student,sc,class
where student.Sid=sc.Sid 
and student.classid=class.classid
group by student.Sid;

 

1.5 inner join  on 

select * from student 
inner join class on student.classid=class.classid
inner join sc on student.Sid=sc.Sid
where ssex='男' 

 

1.6 等值联查与inner join on 的区别

等值联查：

• 先拿出所有的结果再筛选
• 适合表的个数多，但是每个表的数据量不大，吃内存但是 IO小

inner join on：

• 通过第一张表的结果集进行on后面的结果匹配，符合条件的放到结果集里面
• 适合表少，但是每张表的数据大，内存占用小，IO高

-- 笛卡尔积
-- 先拿出所有的结果再筛选
-- 适合表的个数多，但是每个表的数据量不大，吃内存但是 IO小
select * from student,class
where student.classid=class.classid andssex='男';     -- 结果一样


-- 通过第一张表的结果集进行on后面的结果匹配，符合条件的放到结果集里面
-- 适合表少，但是每张表的数据大，内存占用小，IO高
select * from student 
inner join class on student.classid=class.classid
inner join sc on student.Sid=sc.Sid
where ssex='男'       

 

-- 3表联查
select * from student
inner join class on student.classid=class.classid
inner join sc on student.Sid=sc.Sid

 

-- 5表联查
select * from student
inner join class on student.classid=class.classid
inner join sc on student.Sid=sc.Sid
inner join course on student.classid=course.Cid
inner join teacher on course.Tid=teacher.Tid

 

-- 没门课程的平均成绩 课程名称 代课老师姓名 平均成绩
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

 

2.外联查询 

• 找到主查表
• 以小表驱动大表

 

2.1  left join on 左外联  主查表在join的左边

-- 获取所有学生的数据和对象的班级信息
-- left join on 左外联  主查表在join的左边
select * from student
left join  class on student.classid=class.classid

2.2  right join on 右外联  主查表在join的右边

-- right join on 右外联  主查表在join的右边
select * from class
right join student on student.classid=class.classid

         查询出所有的学生都学过多少门课程 显示学生姓名  课程数 

-- 查询出所有的学生都学过多少门课程 显示学生姓名  课程数
select sname ,COUNT(Cid) from student 
left join sc on student.Sid=sc.Cid
group by student.Sid

select sname ,COUNT(DISTINCT Cid) from student 
left join sc on student.Sid=sc.Cid
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

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

2.3 union

注意：

•  union 两个结果集的并集
•  union 去除重复与distinct 一样
•  不同类型的字段是可以合并的
•  不同列数量的结果集不允许合并
•   起别名给第一个结果集才有用

        查询库中的有名字的人 

-- 查询库中的有名字的人
select sname 姓名,ssex,classid from student
union
select tname ,tsex 性别,temail from teacher

        获取没有班级的学生和没有学生的班级的数据 

-- 获取没有班级的学生和没有学生的班级的数据
select * from student
left join class on student.classid=class.classid
where class.classid is null 
union 
select * from student
right join class on student.classid=class.classid
where student.Sid is null

        获取没有班级的学生和班级和学生都有的还要获取没有学生的班级 (全连接)

-- 获取没有班级的学生和班级和学生都有的还要获取没有学生的班级
-- 全连接
select * from student
left join class on student.classid=class.classid
union 
select * from student
right join class on student.classid=class.classid

         获取没有班级的学生和班级和学生都有的还要获取没有学生的班级(不去重的并集) 

-- 不去重的并集
select * from student
left join class on student.classid=class.classid
union all
select * from student
right join class on student.classid=class.classid

3.子查询 

         子查询，又叫内部查询

3.1  where 型子查询

        查询id最大的一个学生(使用排序+分页实现)

select * from student order by sid desc limit 1

 

        查询id最大的一个学生(子查询)

•  所有的查询必须用小括号括起来
•  效率极低 

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

 

        查询每个班下id最大的学生(使用where子查询实现)

select * from student
left join class on student.classid=class.classid
where 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人的班级名称和人数(不使用子查询)

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

 

        查询大于5人的班级名称和人数(使用from型子查询) 

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

 

3.3 exists 子查询 

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

-- exists 子查询 子句有结果，父句执行 ，子句没结果 ，父句不执行
select * from teacher
where exists (select * from student where classid=10)

 

3.4 any(推荐新版本的any) \ some 子查询

        题：查询出一班成绩比二班最低成绩高的学生

-- any(推荐新版本的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 sc
left join student on sc.Sid=student.Sid
where student.classid=2)

-- some
select  DISTINCT student.* from sc
left join student on sc.Sid=student.Sid
where student.classid=1 and score>some(
	select score from sc
left join student on sc.Sid=student.Sid
where student.classid=2)

 

3.5 all 子查询 

         题：查询出一班成绩比二班最高成绩高的学生

-- all 
-- 题：查询出一班成绩比二班最高成绩高的学生
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)

 

4.结果集的控制语句

4.1 IF(expr1,expr2,expr3) 

IF(expr1,expr2,expr3)
expr1 条件
expr2 条件成立 显示数据
expr3 条件不成立 显示数据

select * from teacher
-- 1 女
-- 0 男
select tid,tname ,if(tsex=1,'女','男')tsex,tbirthday,taddress from teacher

 

4.2  IFNULL(expr1,expr2)

IFNULL(expr1,expr2)
expr1 字段
expr2 当字段为null写的默认值

IFNULL(expr1,expr2)
-- expr1 字段
-- expr2 当字段为null写的默认值
select sid,sname,IFNULL(birthday,'这个学生没有生日,可怜宝宝！')bir,ssex from student

 

4.3 case when then end (必须同时出现) 

-- case when then end (必须同时出现)
select tid,tname,
case tsex
	when 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 tsex,tbirthday from teacher

-- 查询学生的成绩，
-- 大于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

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

 

(面试题！！！)行专列  列转行
        统计各个分数段的人数 

分数段    人数
100-90     5
90-70      10
70-60      2
不及格     3 

select '100-90' 分数段 ,count(*) 人数 from sc where score <=100 and score >=90
union 
select '90-70' ,count(*) from sc where score <=90 and score >=70
union 
select '70-60' ,count(*) from sc where score <=70 and score >=60
union 
select '不及格' ,count(*) from sc where score <60 

 

 

 分数段   100-90    90-70   70-60   不及格
人数        3        10      2        3             5

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

         

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

         

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 bioabioa