多测师拱墅校区__肖sir__数据库之多表

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’,7200,‘105’);

– drop table dept ;
– drop table emp ;
select * from dept;
select * from emp ;
员工表：

sid 员工编号
name 员工姓名
age 年龄
woektime_start 入职时间
incomming 工资
dept2 部门编号

部门表：
depr1 部门编号
dept_name 部门名称

emp：部门编号dept2 = dept 部门编号dept1

====================
二、 多表查询

1、什么是多表关联查询？
从2个表或者更多的表中查询我们需要的数据

2、多表链接的关系
（1）内连接：inner join on
（2）左连接： left join on
（3）右链接： right join on
（4）全外链接：
（5）左独有数据
（6）右独有数据

备注：
多行注释：ctrl+/
多行取消注释：shfit +ctrl+/

详解:多表

1、内连接 （普通内链接，隐藏内连接）
定义：查询两个表共有的关联的数据
（1）普通内连接
格式：select * from 表1 INNER JOIN 表2 on 表1.关联字段1=表2.关联字段2 ;
案例：select * from dept INNER JOIN emp on emp.dept2=dept.dept1 ;

（2）隐藏内链接

格式：select * from 表1，表2 where 表1.关联字段1=表2.关联字段2
案例：select * from dept,emp where emp.dept2=dept.dept1;

2、左连接
以左表为主（整个左表全部显示），右表有关联的数据就显示，没有关联的数据就以null显示
格式：SELECT * from 表1 left join 表2 on 表1.关联字段1= 表2.关联字段2;
案例：SELECT * from dept left join emp on emp.dept2=dept.dept1;

3、右连接
以右表为主（整个右表全部显示），左表右关联的数据就显示，没有的数据就以null 显示
格式：SELECT * from 表1 right join 表2 on 表1.关联字段1= 表2.关联字段2 ;
案例：SELECT * from dept right join emp on emp.dept2=dept.dept1 ;

（4）左独有数据
定义：左独有的数据在左连接的基础上，以右表显示null来取独有数据；
格式：SELECT * from 表1 left join 表2 on 表1.关联字段1= 表2.关联字段2 where 右表字段 is null ；
案例：SELECT * from dept left join emp on emp.dept2=dept.dept1 where name is null;

（5）右独有数据
定义：右独有的数据在左连接的基础上，以左表显示null来取独有数据；
格式：SELECT * from 表1 rigt join 表2 on 表1.关联字段1= 表2.关联字段2 where 左表字段 is null ；
案例：select * from dept right join emp on emp.dept2=dept.dept1 where dept_name is null ;

（6）全外连接：
union
方法1:内连接+左独有+右独有
方法2：左连接+右独有
方法3：右连接+左独有

方法一详解：内连接+左独有+右独有
SELECT * from dept inner join emp on emp.dept2=dept.dept1 #内连接
UNION
SELECT * from dept left join emp on emp.dept2=dept.dept1 where name is null #左独有
union
select * from dept right join emp on emp.dept2=dept.dept1 where dept_name is null ; #右独有

方法二：详解 左连接+右独有
SELECT * from dept left join emp on emp.dept2=dept.dept1 #左连接
UNION
select * from dept right join emp on emp.dept2=dept.dept1 where dept_name is null ; #右独有

方法三：右连接+左独有

select * from dept right join emp on emp.dept2=dept.dept1 #右连接
UNION
SELECT * from dept left join emp on emp.dept2=dept.dept1 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.求出IT技术部门所有的员工姓名和员工编号？
结果：name ，sid emp
条件：dept_name=IT技术 dept
方法：
select name,sid from dept inner join emp on dept.dept1=emp.dept2 WHERE dept_name=‘IT技术’ ;

2、求出财务部门的收入总和？
结果：sum（incomming） emp
条件: dept_name=“财务” dept
方法1：select sum(incoming) from dept inner join emp on dept.dept1=emp.dept2 where dept_name= “财务” ;
方法2：
select sum(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name= “财务”;