MYSQL数据库详解（4）-- 高级数据查询语句

多表联查

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

非等值联查() 笛卡尔积

select * from student,class

等值联查（交集）

内联查询方式1

select * from student,class,sc
where student.classid=class.classid and student.sid = sc.sid

内联查询方式2

select * from student
inner join class on student.classid = class.classid
inner join sc on student.sid = sc.sid

找到学过张三老师课程的学生信息（案例）

方式1
select student.sid,sname,birthday,ssex,Cname,score,tname from teacher 
inner join course on course.Tid = teacher.Tid
inner join sc on sc.cid = course.Cid
inner join student on student.sid = sc.Sid 
where tname = '张三'

方式2
select * from student 
inner join sc on student.sid = sc.Sid 
inner join course on sc.cid = course.Cid
inner join teacher on course.Tid = teacher.Tid
where tname = '张三'

外联查询

所有学生的班级信息和成绩 （案例）

左外联

select * from student 
left join class on student.classid = class.classid
left join sc on student.sid = sc.sid

右外联

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

没有班级的学生 （案例）

select * from student
left join class on class.classid = student.classid
where student.classid is NULL

没有学生的班级（案例）

select * from class
left join student on class.classid = student.classid
where student.classid is NULL

union 合并多个无关的表 （并集）

1.字段类型不影响
2.字段数量要一致
3.自动去重（union all 不去重）
4.起别名在第一个select中

注意：
1列名不一致时，会以第一张表的表头为准，并对其栏目。
2会将重复的行过滤掉。
3如果查询的表的列数量不相等时，会报错。
4在每个子句中的排序是没有意义的，mysql在进行合并的时候会忽略掉。
5如果子句中的排序和limit进行结合是有意义的。
6可以对合并后的整表进行排序

**union all **：是求两个查询的并集，但是不会把重复的过滤掉，而是全部显示出来

没有班级的学生 和 没有学生的班级（案例）

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

全都要 （案例）

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

子查询

where 子查询

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

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

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

select * from student
where sid in (select max(sid) from student GROUP BY classid) 

– 没学过张三课程的

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

from 子查询

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

select * from class 
left join student on class.classid = student.classid 
group by class.classid

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

select * from class
left join (select classid,count(sid) c from student group by classid) t
on class.classid = t.classid
where c > 5

exists 子查询

– 从学生表中有男同学的信息，查询老师信息

select *  from teacher
where exists (select * from student where ssex = '男')

– 从学生表中有外星人的信息，查询老师信息

select *  from teacher
where exists (select * from student where ssex = '外星人')

any/some ,all 子查询

any子查询：表示满足其中任意一个条件

all子查询：表示满足其中所有条件条件

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

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

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

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

特殊的语句格式

1.IF(expr1,expr2,expr3)
expr1：表达式
expr2：成立结果
expr3：不成立结果

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

2.IFNULL(expr1,expr2)
expr1：字段
expr2：如果字段为null时显示

select sid,sname,IFNULL(birthday,'没出生日期') birthday,ssex from student

3.case （字段） when （条件） then （结果） end (都要出现)
– 简单case
/*
查询学生的成绩，
并将大于90分的用A显示，
大于80分的用B显示，
大于70分的用C显示，
大于60分的用D显示，
小于60分的显示不及格
*/

select student.sid,sname,ssex, 
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 'score'
from student 
inner join sc on student.sid = sc.sid