第11课—数据库之多表运用（内连接、左、右连接...）
数据库之多表运用
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',8000,'105');
drop table dept ;
drop table emp ;
select * from dept;
select * from emp ;
====================
部门表：部门编号 ，部门名称
员工表：
员工编号，姓名，年龄、工作时间、工资、部门编号
================
一、多表查询
1、什么是多表关联查询？
答：从2个表或者更多的表中查询我们需要的数据
2、多表连接的关系？
（1）内连接
（2）左连接
（3）右连接
（4）左独有数据
（5）右独有数据
（6）全外连接
比如： a 表：1，2，3 b 表：1，2，4
内连接：显示左边12和右边12关联 12
左连接：显示左边1，2，3，右 边12 关联 123 4不显示
右连接： 显示右边1，2，4全部显示，左 边12关联 124， 3不显示
左独有数据：显示3
右独有数据：显示4
全外连接：显示1，2，3，4
三、内连接
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 wehre name is null;
五、右独有数据
格式：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 wehre dept1 is null;
六、全外连接 （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 ;
==========================
总结：
多表：
普通内连接：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.列出每个部门的平均收入及部门名称;
结果：部门名称（dept_name）,avg（incong）
条件：group by dept_name
方法：
select avg(incoming) ,dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept_name ;
2.财务部门的收入总和；
emp: sum(incoming)
dept: dept_name="财务"
方法1:select sum(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name="财务" ;
方法2：select a.dept_name,sum(incoming) from dept a join emp b on a.dept1=b.dept2 group by dept_name HAVING dept_name='销售';
3.It技术部入职员工的员工号
方法1：select sid from dept inner join emp on dept.dept1=emp.dept2 where dept_name="IT技术"
