多表讲解
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技术')