高级数据查询语句

多表联查

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

# 多表联查

内联查询

-- 只有完全满足条件（主外键关系）的数据才能显示结果

-- 非等值联查 -- 笛卡儿积 逻辑上有错误

select * from student,class

等值联查

where

-- 查询出学生和班级信息 student class

select * from student ,class

where student.classid=class.classid;

-- 查询学过张三老师课程的学生信息

select student.sname from student,teacher,course,sc

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 ,class,sc

where student.Sid=sc.Sid -- 关系

and student.classid=class.classid

group by student.Sid;

 inner join on

-- 在表中至少一个匹配时，则返回记录。

-- 笛卡儿积 筛选

-- 适用表多数据量不大 占内存大，io小只读一次，效率高

select * from student,class

where student.classid=class.classid and ssex='男';

-- 通过第一张表的结果进行on条件匹配 后筛选

-- 适用表小，数据大 占内存小，io高

select *from student

inner join class on student.classid=class.classid

where ssex='男';

-- 5表联查

select *from student

inner join class on student.classid=class.classid

inner join sc on student.Sid=sc.Sid

inner join course on sc.Cid=course.Cid

inner join teacher on course.Tid=teacher.Tid;

-- 每门课的平均成绩 课程名称 代课老师姓名

select cname,tname,avg(score) from sc

inner join course on sc.Cid=course.Cid

inner join teacher on course.Tid=teacher.Tid

group by course.Cid;

外联查询

-- 找到主查表

-- 所有学生的数据对应的班级信息

left join on 左外联

从左表（表1）中返回所有的记录，即便在右 （表2）中没有匹配的行。

select *from student

left join class on student.classid=class.classid

right join on 右外联

从右表（table_2）中返回所有的记录，即便在左（table_1）中没有匹配的行。

select * from class -- 尽量小表在前，减少循环次数

right join student 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 class.classid=student.classid

where student.Sid is null;

union 两个结果的并集

-- 有去重机制 distinct 完全一样去重

-- 不同类型的字段可以合并

-- 不同列数量的结果集不允许合并

-- 表头为第一个结果集的，起别名给第一个结果集才有用

-- 库中所有人的姓名

select sname from student

union

select tname 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

union all (不去重)

select * from student

left join class on student.classid=class.classid

union all

select * from student

right join class on student.classid=class.classid

# 子查询 

-- 所有子查询必须用（）

where 型子查询：

-- 查询id最大的学生

select * from student order by sid desc limit 1;

select max(sid) from student

select *from student where sid=21 -- 魔数

--子查询，效率极低，工作中尽量不要用

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

-- 每个班id最大的学生

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  in

              (select cid from course where tid in

                     (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='张三')))

from 子查询

--把内层的查询结果当成临时表，供外层sql再次查询。查询结果集可以当成表看待。临时表要使用一个别名。

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

-- 1.不用子查询

select classname,count(*)from  class -- 列要相同

left join student on class.classid=student.classid

group by class.classid having count(*)>5


-- 2.子查询

select classname,人数 from class left join

(select classid,count(*) 人数

from student group by classid)

t1 on class.classid=t1.classid

where 人数>5

exists 子查询

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

select *from teacher

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

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)

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)

# 结果集的控制语句

if(expr1,expr2,expr3)

-- expr1 条件

-- expr2 条件成立 显示数据

-- expr3 条件不成立 显示数据

select tid,tname,if(tsex=1,'女','男')tsex,

tbirthday,taddress from teacher

ifnull(expr1,expr2)

-- expr1 字段

-- expr2 当字段为null 默认值

select sid ,sname,ifnull(birthday,'ooo')bir,ssex

from student

case when then end

-- when 后跟常量

select tid,tname,
case tsex
       when 0 then '男'
       when 1 then '女'-- 没有匹配到输出null
       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 sid,score,
case
       when score>=90 then'A'
       when score>=80 and score<90 then'B'
       when score>=70 and score<80 then'C'
       when score>=60 and score<70 then'D'
       when score<60  then'不及格'
end  from sc

-- 统计各个分数段的人数

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

-- 行转列，列转行

select '人数' 分数段,

       count(case when score<=100 and score>=90 then score end) '100-90',

       count(case when score<90 and score>=80 then score end) '90-80',

       count(case when score<80 and score>=70 then score end) '80-70',

       count(case when score<70 and score>=60 then score end) '70-60',

       count(case when score<60 then score end) '不及格'

from sc

sql执行顺序

Sql语句在数据库中的执行流程

        1.系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接。

        2. Caches & Buffers： 查询缓存组件

        3. SQL Interface: SQL接口 接收用户的SQL命令，并且返回用户需要查询的结果。比如

        SELECT ... FROM就是调用SQL Interface

        MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口

        4. Parser: 解析器:在解析器中对 SQL 语句进行语法分析、语义分析。

        5. Optimizer: 查询优化器

        6.存储引擎

        7.文件系统

        8.日志系统

Sql查询语句的执行顺序