Java基础27--mysql-联合查询7种结果和联合查询分类

Java基础27–mysql-联合查询7种结果和联合查询分类（多张表查询）

关联查询七种结果：

（1）A∩B

（2）A

（3）A - A∩B

（4）B

（5）B - A∩B

（6）A ∪ B

（7）A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）

如何实现？

（1）内连接

（2）外连接：左外连接、右外连接、全外连接（mysql使用union代替全外连接）

• 1、内连接：inner join
  （1）A∩B
• 2、外连接
  （1）左外连：left join
  （2）A
  （3）A - A ∩ B
  （2）右外连接：right join
  （4）B
  （5）B - A ∩ B
  （3）全外连接：full join
  但是不直接支持全外连接，但是可以使用union（合并）结果来实现以下两种结果（mysql中没办法直接实现，迂回实现）
  （6）A ∪ B 用 （2）A union（合并） （4）B
  （7）A ∪ B - A ∩ B 用 （3）A - A ∩ B union（合并）（5）B - A ∩ B

1、内连接：实现A∩B

内连接：A∩B
1、官方写法
两张表内连接关联查询
select 字段列表
from A表名 inner join B表名
on 1个关联条件
where 其他条件等

三张表内连接关联查询
select 字段列表
from A表名 inner join B表名 inner join C表名
on 2个关联条件
where 其他条件等

关联条件的个数 = 表数 - 1
如果少了关联条件，就会笛卡尔积的

2、另一种写法

两张表内连接关联查询
select 字段列表
from A表名 , B表名
where 1个关联条件 and 其他条件等
*/

//两张表
#查询所有员工的编号，姓名，部门编号和他所在部门的名称
/*
select eid,ename, did,dname 
from t_employee inner join t_department;

错误
错误代码： 1052
Column 'did' in field list is ambiguous
//ambiguous：模糊的，两张表出现相同名字的列，需要指定是哪张表的
*/
/*
select eid,ename,t_employee.did,dname 
from t_employee inner join t_department;

出现了笛卡尔积  A表的数量 * B表的数量，因为没有关联条件
*/
SELECT eid,ename,t_employee.did,dname 
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did;

#给表名取别名
/*取别名原则：
  列的别名可以使用双引号""，给表名取别名不要使用双引号。取别名时as可以省略
   如果列的别名没有包含空格，可以省略双引号，如果有空格双引号不能省略。*/
SELECT eid,ename,emp.did,dname 
FROM t_employee AS emp INNER JOIN t_department AS dept
ON emp.did = dept.did;

SELECT eid,ename,emp.did,dname 
FROM t_employee AS emp , t_department AS dept
WHERE emp.did = dept.did;

//三张表
#查询员工编号，员工姓名，员工的职位编号，职位名称，部门编号，部门名称
SELECT eid,ename,t_employee.`job_id`,job_name,t_employee.`did`,dname
FROM t_employee INNER JOIN t_job INNER JOIN t_department
ON t_employee.`did` = t_department.did AND t_employee.`job_id` = t_job.`job_id`;

#查询薪资高于15000的男员工编号，员工姓名，员工的职位编号，职位名称，部门编号，部门名称
SELECT eid,ename,t_employee.`job_id`,job_name,t_employee.`did`,dname
FROM t_employee INNER JOIN t_job INNER JOIN t_department
ON t_employee.`did` = t_department.did AND t_employee.`job_id` = t_job.`job_id`
WHERE salary > 15000 AND gender = '男'

示例2：

#查询员工的姓名和他所在的部门的名称
#员工的姓名在t_employee
#部门的名称在t_department
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did
//两个表有相同名字的列需要指定是哪个表

SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee , t_department
WHERE t_employee.did = t_department.did


#查询薪资高于20000的男员工的姓名和他所在的部门的名称
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did
WHERE salary>20000 AND gender = '男'

2、左外连接，两个表

左连接：
（1）A
select 字段列表
from A left join B
on 关联条件
where 其他条件 等

（2）A - A∩B

select 字段列表
from A left join B
on 关联条件
where 关联字段 is null and 其他条件 等

#查询员工的编号，姓名，部门编号，部门名称
#包括那些没有分配部分的员工
SELECT eid,ename,t_employee.did,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did;

#查询那些没有分配部门的员工
SELECT eid,ename,t_employee.did,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL;

#实现查询结果是A
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句;

#实现A -  A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 关联字段 is null and 等其他子句;

代码示例：

#查询所有员工的姓名和他所在的部门的名称
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did


#查询所有没有部门的员工
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

3、右外连接，两张表

右连接：
（1）B
select 字段列表
from A right join B
on 关联条件
where 其他条件 等

（2）B - A∩B

select 字段列表
from A right join B
on 关联条件
where 关联字段 is null and 其他条件 等

#查询员工的编号，姓名，部门编号，部门名称
#包括那些没有分配部分的员工
SELECT eid,ename,t_employee.did,dname
FROM t_department RIGHT JOIN t_employee
ON t_employee.did = t_department.did;

#查询那些没有分配部门的员工
SELECT eid,ename,t_employee.did,dname
FROM t_department RIGHT JOIN t_employee
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL;

#实现查询结果是B
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;

#实现B -  A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 关联字段 is null and 等其他子句;

代码示例：

#查询所有部门，以及所有部门下的员工信息
SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did

#查询那些没有员工属于它的部门
SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

4、用union代替全外连接

使用UNION来实现全外连接的查询结果：
（1）A ∪ B
（2）A ∪ B - A ∩ B
迂回成
（1）A UNION B
（2）A - A ∩ B UNION B - A ∩ B

#查询所有员工和所有部门的信息，包括那些没有分配部门的员工和没有安排员工的部门
SELECT * 
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.`did`

UNION

SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.`did`

#查询那些没有分配部门的员工和没有安排员工的部门
SELECT * 
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.`did`
WHERE t_employee.did IS NULL

UNION

SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.`did`
WHERE t_employee.did IS NULL

#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句

union 

select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;


#实现A∪B -  A∩B  或   (A -  A∩B) ∪ （B - A∩B）
#使用左外的 (A -  A∩B)  union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 关联字段 is null and 等其他子句

union

select 字段列表
from A表 right join B表
on 关联条件
where 关联字段 is null and 等其他子句

代码示例：

#查询所有员工，所有部门，包括没有员工的部门，和没有部门的员工
SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did

UNION

SELECT *
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did


#查询那些没有部门的员工和所有没有员工的部门

#没有部门的员工
SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

UNION 

#所有没有员工的部门
SELECT *
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

5、自连接

两个关联查询的表是同一张表，通过取别名的方式来虚拟成两张表

select 字段列表
from 表名 别名1 inner/left/right join 表名 别名2
on 别名1.关联字段 = 别名2的关联字段
where 其他条件

代码示例：

#查询员工的编号，姓名，薪资和他领导的编号，姓名，薪资
#这些数据全部在员工表中
#把t_employee表，即当做员工表，又当做领导表
#领导表是虚拟的概念，我们可以通过取别名的方式虚拟
SELECT emp.eid "员工的编号",emp.ename "员工的姓名" ,emp.salary "员工的薪资",
	mgr.eid "领导的编号" ,mgr.ename "领导的姓名",mgr.salary "领导的薪资"
FROM t_employee emp INNER JOIN t_employee mgr
#t_employee emp：如果用emp.，表示的是员工表的
#t_employee mgr：如果用mgr.，表示的是领导表的
ON emp.mid = mgr.eid

#表的别名不要加""，给列取别名，可以用""，列的别名不使用""也可以，但是要避免包含空格等特殊符号。