多表讲解
1、什么是多表关联查询？
定义：查询数据来源于多张表
在实际工作中我们数据需要去2个表或者更多的表中提取，需要使用多表关联查询
2、多表查询类型？
（1）内连接
(2)左连接（左外连接）
（3）右连接
（4）全连接
（5）左独有连接
（6）右独有连接
==========================================
建表语句：
cREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(20)) default charset=utf8;
INSERT into dept VALUES ('101','财务');
INSERT into dept VALUES ('102','销售');
INSERT into dept VALUES ('103','IT技术');
INSERT into dept VALUES ('104','行政');
CREATE table emp (sid VARCHAR(6),name VARCHAR(20),age TINYINT(2),woektime_start VARCHAR(10),incoming SMALLINT(10),dept2 VARCHAR(6))default charset=utf8;
insert into emp VALUES ('1789','张三',35,'1980/1/1',4000,'101');
insert into emp VALUES ('1674','李四',32,'1983/4/1',3500,'101');
insert into emp VALUES ('1776','王五',24,'1990/7/1',2000,'101');
insert into emp VALUES ('1568','赵六',57,'1970/10/11',7500,'102');
insert into emp VALUES ('1564','荣七',64,'1963/10/11',8500,'102');
insert into emp VALUES ('1879','牛八',55,'1971/10/20',7300,'103');
insert into emp VALUES ('1880','刘十',55,'1971/10/21',7000,'105');
drop table dept ;
drop table emp ;
select * from dept;
select * from emp ;
dept部门表：
dept1 部门编号
dept_name 部门名称
emp 表
sid 员工编号
name 员工姓名
age 员工年龄
woektime_start 开始工作时间
incoming 工资
dept2 部门编号
============================================================
1、内连接
定义：查询两个表共有的关联数据
A、普通内连接
B、隐藏内连接
============================
A、普通内连接
格式：select * from 表1 inner join 表2 on 表1.关联字段1=表2.关联字段2 ;
案例1：select * from dept inner join emp on dept.dept1=emp.dept2 ;
案例2：select * from dept s inner join emp c on s.dept1=c.dept2 ; 将表名取了别名
案例3：select * from dept s inner join emp c on dept1=dept2 ;
B、隐藏内连接
格式：select * from 表1,表2 where 表1.关联字段1=表2.关联字段2;
案例：select * from dept,emp where dept.dept1=emp.dept2 ;
============================
左连接：
定义：以左表为主，（显示整个左表），右表关联的数据就显示，不关联的数据就以null形式显示
格式：select * from 表1 left join 表2 on 表1.关联字段=表2.关联字 ;
案例：select * from dept left join emp on dept.dept1=emp.dept2 ;
============================
右连接
定义：以右表为主，（显示整个右表），左表关联的数据就显示，不关联的数据就以null形式显示
格式：select * from 表1 right join 表2 on 表1.关联字段=表2.关联字 ;
案例：select * from dept RIGHT join emp on dept.dept1=emp.dept2 ;
============================
左独有数据：
左连接的基础上，在判断右表为null 字段来判断
格式：select * from 表1 left join 表2 on 表1.关联字段=表2.关联字 where 右表字段 is null;
案例：select * from dept left join emp on dept.dept1=emp.dept2 where name is null;
============================
右独有数据：
右连接的基础上，在判断左表为null 字段来判断
格式：select * from 表1 right join 表2 on 表1.关联字段=表2.关联字 where 左表字段 is null;
案例：select * from dept right join emp on dept.dept1=emp.dept2 where dept1 is null;
============================
左独有+右独有 union
格式：select * from 表1 left join 表2 on 表1.关联字段=表2.关联字 where 右表字段 is null union
select * from 表1 right join 表2 on 表1.关联字段=表2.关联字 where 左表字段 is null;
案例：select * from dept left join emp on dept.dept1=emp.dept2 where name is null
union
select * from dept RIGHT join emp on dept.dept1=emp.dept2 where dept1 is null;
============================
全 连接 ：
（1）内连接+左独有+右独有
（2）左连接+右独有
（3）右连接+左独有
案例1:内连接+左独有+右独有
select * from dept inner join emp on dept.dept1=emp.dept2
UNION
select * from dept left join emp on dept.dept1=emp.dept2 where name is null
union
select * from dept RIGHT join emp on dept.dept1=emp.dept2 where dept1 is null;
案例2：左连接+右独有
select * from dept left join emp on dept.dept1=emp.dept2
UNION
select * from dept RIGHT join emp on dept.dept1=emp.dept2 where dept1 is null;
案例3：右连接+左独有
select * from dept RIGHT join emp on dept.dept1=emp.dept2
UNION
select * from dept left join emp on dept.dept1=emp.dept2 where name is null;
总结：
多表：
普通内连接：select * from 表1 inner join 表2 on 表 1.关联字段=表2.关联字段
隐藏内连接：select * from 表1，表2 where 表 1.关联字段=表2.关联字段
左连接：select * from 表1 left join 表2 on 表 1.关联字段=表2.关联字段
右连接 ：select * from 表1 right join 表2 on 表 1.关联字段=表2.关联字段
左独有数据：select * from 表1 left join 表2 on 表 1.关联字段=表2.关联字段 where 表2中的字段 is null
右独有数据：select * from 表1 right join 表2 on 表 1.关联字段=表2.关联字段 where 表1 中的字段 isnull
全外连接：union
（1）左独有+右独有+内连接
（2）左连接+右独有
（3）右连接+左独有
==========================================
1.列出每个部门的平均收入及部门名称;
条件：group by dept_name
结果：dept 表 ：dept_name emp 表：avg(incoming)
方法一：SELECT dept_name,avg(incoming) from dept left JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name;
方法二：
SELECT dept_name,avg(incoming) from (
SELECT * FROM dept RIGHT JOIN emp on dept1=dept2
UNION
SELECT * FROM dept LEFT JOIN emp on dept1=dept2 WHERE NAME IS NULL
) b1
GROUP BY b1.dept_name;
2.财务部门的收入总和；
条件： dept 表 ：dept_name="财务" emp表：sum（incoming）
结果：收入总和
方法1：select sum(incoming) from dept left JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务"
方法2：
SELECT dept_name,sum(incoming) from (
SELECT * FROM dept RIGHT JOIN emp on dept1=dept2
UNION
SELECT * FROM dept LEFT JOIN emp on dept1=dept2 WHERE NAME IS NULL
) b1
WHERE b1.dept_name='财务';
方法3：
SELECT SUM(incoming) AS '收入总和' from emp where dept2=(SELECT dept1 from dept where dept_name='财务')
3.It技术部入职员工的员工号
条件： dept_name=it技术部门
结果： sid
方法1：select sid from dept left JOIN emp on dept.dept1=emp.dept2 where dept_name="IT技术";
方法2：SELECT name,sid from (
SELECT * FROM dept RIGHT JOIN emp on dept1=dept2
UNION
SELECT * FROM dept LEFT JOIN emp on dept1=dept2 WHERE NAME IS NULL
) b1
WHERE b1.dept_name='IT技术';
方法3：
SELECT sid from emp where dept2=(SELECT dept1 from dept where dept_name='It技术')