SQL联合查询 join

问题说明：
图片审核功能：要求mac与图片绑定, 图片状态需要审核，管理员修改图片状态，现在要求同时显示绑定信息与图片状态

有两种表格：

一张是mac-图片绑定表格mac_config ，包含文件名，所属用户信息
filename， email

一张是图片状态表格files ，包含审核状态
filename，status

现在有个查询， 需要返回 mac-图片绑定表格，并且包含图片的审核状态：

使用联合查询：

select m.filename,m.email,f.status
from mac_config as m left join files as f
on m.filename = f.filename;

执行查询后，返回的表格如下， 可以看到表格中多了一个status的字段：

join查询不会产生临时表，所以这张所谓的新表是不存在的，节省性能。

本次查询使用了left join，保留了左边完整表格

各种连接：

-- 交叉连接 不带where，相当于笛卡尔积
select *
from course cross join teacher;

-- 交叉连接 带where
select *
from course as c cross join teacher as t
where c.教师号 = t.教师号;

-- 内连接，交集部分 inner可省略
select s.学号,姓名,课程号
from student as s inner join score as s1
on s.学号 = s1.学号;

-- 左连接，保留左表全记录
select s.学号,姓名,课程号
from student as s left join score as s1
on s.学号 = s1.学号;

-- 右连接，保留右表全记录
select 课程号,课程名称,教师名称,t.教师号
from course as c right join teacher as t
on c.教师号 = t.教师号;

-- 左外连接，保留只在左表有的记录
select s.学号,姓名,课程号
from student as s left join score as s1
on s.学号 = s1.学号
where s1.学号 is null;

-- 右外连接，保留只在右表有的记录
select 课程号,课程名称,教师名称,t.教师号
from course as c right join teacher as t
on c.教师号 = t.教师号
where c.教师号 is null;

/* 全连接，MySQL不支持全连接 full outer join 但oracle支持
   可以借助union合并两表左连接和右连接结果集实现
*/
select 课程号,课程名称,教师名称,t.教师号
from course2 as c left join teacher as t
on c.教师号 = t.教师号
union
select 课程号,课程名称,教师名称,t.教师号
from course2 as c right join teacher as t
on c.教师号 = t.教师号;

-- 全外连接(mysql也不支持)，A、B除去相交部分的集合
select 课程号,课程名称,教师名称,t.教师号
from course2 as c left join teacher as t
on c.教师号 = t.教师号 
where t.教师号 is null
union
select 课程号,课程名称,教师名称,t.教师号
from course2 as c right join teacher as t
on c.教师号 = t.教师号
where c.教师号 is null;

-- 查询所有学生的学号、姓名、选课数、总成绩
select s1.学号,姓名,count(课程号) as 选课数,sum(成绩) as 总成绩
from student as s1
left join score as s2 on s1.学号 = s2.学号
group by s1.学号;

-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select s1.学号,姓名,avg(成绩) as 平均成绩
from student as s1
left join score as s2 on s1.学号 = s2.学号
group by s1.学号
having 平均成绩 > 85;

-- 查询学生的选课情况：学号，姓名，课程号，课程名称
select s1.学号,姓名,s2.课程号,课程名称
from student as s1 
join score as s2 on s1.学号 = s2.学号
join course as c on s2.课程号 = c.课程号;

另外还有一个多表查询关键字UNION，当由于有大量的数据，导致一个表被切分为多个表后，使用UNION查询
如：

select student，学号 from class1
union
select student，学号 from class2

联合查询去重复: 使用GROUP BY

SELECT f.*
          FROM files as f right join mac_config as m
          on m.filename = f.filename
          WHERE f.filename<>'null'
          GROUP BY m.filename
          ORDER BY create_time DESC

另外还有union all关键字
union和union all的区别：union会进行数据的排序和去重，查询效率较低；union all没有进行排序和去重，查询效率较高。