mysql多表

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','刘九',64,'1971/10/20',9000,'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 ;

左链接：

格式：select * from 表1 left join 表2 on 表1.关联字段1=表2.关联字段2

案例：select  *  from  dept left JOIN  emp   on dept.dept1=emp.dept2;

 

右链接：

格式：select * from 表1 right join 表2 on 表1.关联字段1=表2.关联字段2

案例：select  *  from  dept  right    JOIN  emp   on dept.dept1=emp.dept2;

 左独有数

格式：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;

 

右独有的数据：

格式：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 dept_name
is null;

 全外链接：

（1）内链接+左独有+右独有

（2）左链接+右独有

（3）右连接+左独有

union  拼接

案例

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 dept_name
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 dept_name
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, sum(incoming)

条件：dept_name=" 财务"

语句：

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

2.列出每个部门的平均收入及部门名称;（left join）

结果：avg（incoming），dept_name

条件：group  by   

语句：

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

3. 财务部门收入超过2000元的员工姓名

语句

select  name  from  dept INNER JOIN  emp   on dept.dept1=emp.dept2  where  dept_name="财务" and  incoming>2000