MySQL join连接和子查询

emp表：员工表

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

独有的数据103

dept表：部门表

部门编号、部门名名称

多表查询：

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

二、多表详解

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;

全外连接：（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技术"

子查询：

在一个查询中以另一个查询为条件，将后者查询结果作为一张表操作。

子查询：

（一）

定义：一个查询嵌套另一查询

（二）分类

1、标量子查询（一行一列）

2、列子查询（一列值）

3、行子查询（一行值）

4、表子查询（一个表）

（三）实操

1、标量子查询（一行一列）

案例：.财务部门的收入总和； 

步骤：

a、将财务部门的编号求出在dept中，

b、根据编号在到emp找通过收入总和

方法：

select  sum(incoming)  from  emp  where  dept2=(

select  dept1  from dept  where  dept_name="财务")

2、列子查询（一列值）

求出销售和财务的收入总和？

方法

select  sum(incoming)  from  emp  where  dept2 in(

select  dept1  from dept  where  dept_name="财务" or 

dept_name="销售" 

)

条件是一列值

截图

3、行子查询（一行值）

条件是一行值：（一行多列）

语句：

select  age,incoming,dept2  from  emp where  name="张三";

select  *  from emp where (age,incoming,dept2)in (

select  age,incoming,dept2  from  emp where  name="张三"

)

截图：

以上三种都是接在where  后面

4、表子查询（一个表)

一般接from后面

求出来的条件是一个表

案例：

select  SUM(s.incoming) from (

select   *  from  dept  INNER JOIN emp  on  dept.dept1=emp.dept2 where  dept_name="财务"

) as s

求出每个部门最大年龄的姓名和部门编号？

select  name,dept_name  from dept  INNER JOIN emp  on dept.dept1=emp.dept2 where (age,dept_name) in(

select   max(age),dept_name  from  dept  INNER JOIN emp  on  dept.dept1=emp.dept2 group  by   dept_name

)

1、求财务部门最低工资的员工姓名；

方法：

select  min(incoming)  from  emp where dept2=

(select dept1 from dept  where  dept_name="财务");

 2、找出销售部门中年纪最大的员工的姓名

select  name  from  dept INNER JOIN  emp on dept.dept1=

emp.dept2 where  age=(

select  max(age)  from  emp where dept2=

(select dept1 from dept  where  dept_name="销售"))

and dept_name="销售"

;

3.查找张三所在的部门名称；

结果：depe_name

条件：name=张三

SELECT   dept_name  from dept  where dept1=(

select  dept2  from  emp  where  NAMe="张三"

)