多表关联

多表
一、新建两张表
已知2张基本表：
部门表：dept （部门号，部门名称）;
员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）
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/20',7500,'105');
insert into emp VALUES ('1900','老十',64,'1990/8/1',2000,'106');

drop table dept ;
drop table emp ;
select * from dept;
select * from emp ;

二、多表连接
熟悉表结构
部门表：dept （部门号（dept1），部门名称）;
员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号（dept2））

dept1=dept2 关联关系

三、多表的连接
1、内连接
（1）普通内连接
格式：
select * from 表1 INNER JOIN 表2 on 表1.关联字段1=表2.关联字段2
如：
select * from dept INNER JOIN emp on dept.dept1=emp.dept2

（2）隐藏连接
格式：select * from 表1，表2 where 表1.关联字段1=表2.关联字段2
如：select * from dept,emp where dept.dept1=emp.dept2
2、左连接
格式：
select * from 表1 left JOIN 表2 on 表1.关联字段1=表2.关联字段2
select * from dept left JOIN emp on dept.dept1=emp.dept2
3、右连接
格式：
select * from 表1 right JOIN 表2 on 表1.关联字段1=表2.关联字段2
select * from dept right JOIN emp on dept.dept1=emp.dept2
4、左独有
格式：
select * from 表1 left JOIN 表2 on 表1.关联字段1=表2.关联字段2 WHERE 右表字段 is null
如：select * from dept left JOIN emp on dept.dept1=emp.dept2 WHERE name is null
5、右独有
格式:
select * from 表1 right JOIN 表2 on 表1.关联字段1=表2.关联字段2 WHERE 左表字段 is null
如：
select * from dept right JOIN emp on dept.dept1=emp.dept2 WHERE
dept1 is null
6、全外连接（union）
（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

如:a 表： 123 b表：124
内连接：左表12，右表12 连接 ：显示12关联数据 3，4不显示
左连接：左表123 全部显示，右表12 连接， 显示 123 4不显示
右连接：右表124 全部显示，左表12 连接 ，显示124 3不显示
左独有：左边独有 3
右独有：右边独有4
四、练习题
1.列出每个部门的平均收入及部门名称;
结果：dept_name（dept表） ,avg(incoming) （emp表） 合表
条件：group by dept_name
语句：
select dept_name,avg(incoming) from dept right JOIN emp on dept.dept1=emp.dept2
group by dept_name
2.财务部门的收入总和；
结果：sum（incoming）
条件： dept_name=“财务”
语句：
select sum(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2
where dept_name="财务"
3.It技术部入职员工的员工号
结果：sid
条件： dept_name=“IT技术”
语句:
select sid from dept INNER JOIN emp on dept.dept1=emp.dept2
where dept_name="IT技术"