数据库多表连接

数据库多表的连接笔记

一、建立两张数据表

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),worktime_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');

二、分析表

1.emp表：员工表

员工编号、姓名、年龄、入职时间、工资、部门编号
截图：

独有的数据105

2.dept表：部门表

部门编号、部门名名称
截图：

独有数据104
可以发现，两行表格之间可以关联的是dept1、dept2值。

多表查询：

内连接、左连接、右连接、左独有数据、右独有数据、全外连接

三、多表详解

1、内连接（普通内连接、隐藏内连接）

定义：左边有的数据，右边也有数据，关联起来只显示两个表共有的数据
案例：左表：123 右表124 内连接：显示：左边12，右边12 ，其中3，4不显示

普通内连接：

格式：inner join
select * from 表1 INNER JOIN 2 on 表1.关联字段=表2.关联字段 ;
select * from dept INNER JOIN emp on dept.dept1=emp.dept2 ;
截图：

隐藏内连接：

格式：
select * from 表1，表2 where 表1.关联字段=表2.关联字段;
方法：
select * from dept,emp where dept.dept1=emp.dept2;
截图：

左连接：left join

以做左表为主（左表全部显示），右表右关联的关联，没有关联就不显示
案例： 左表：123 右表124 内连接：显示：左边123，右边12 ，其中4不显示
格式：
select * from 表1 left JOIN 表2 on 表1.关联字段=表2.关联字段 ;
·方法:
select * from dept left JOIN emp on dept.dept1=emp.dept2 ;
截图：

右连接：right join

以做右表为主（右表全部显示），左表有关联的关联，没有关联就不显示
案例： 左表：123 右表124 内连接：显示：左边12，右边124 ，其中3不显示
格式：
select * from 表1 right JOIN 表2 on 表1.关联字段=表2.关联字段 ;
方法:
select * from dept right JOIN emp on dept.dept1=emp.dept2 ;
截图：

左独有数据：

左表中独有的数据
是在左连接后接条件（接右表的字段为null就可以）
格式：
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;
截图：

右独有数据：

右表中独有的数据
是在右连接后接条件（接左表的字段为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;
截图：

2、全外连接：（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;

截图：

练习：

1.列出每个部门的平均收入及部门名称;

结果：avg（incoming）emp表，dept_name dept表
条件： group by dept_name
语句：

select  avg(incoming),dept_name  from  dept a left  JOIN
emp b  on  a.dept1=b.dept2 group by dept_name;

截图：

2.财务部门的收入总和；

结果：sum（incoming）
条件：dept_name=“财务”
方法1：

select sum(incoming)  from dept a
 INNER JOIN  emp  b on  a.dept1=b.dept2 where  dept_name="财务";

方法2：

select  sum(incoming) from  emp  where dept2=(
select  dept1  from dept  where  dept_name="财务"
);

截图：

3.It技术部入职员工的员工号

结果：sid
条件：dept_name=“IT技术”
方法：

select  sid  from   dept a  INNER JOIN  emp b  on a.dept1=b.dept2
where  dept_name="IT技术";

截图：