测试开发实战|软件测试最常用的 SQL 命令(二) | 高级 Join 多表查询

霍格沃兹测试开发学社
1

软件测试最常用的 SQL 命令(二) | 高级 Join 多表查询

原创 月关 [霍格沃兹测试学院](javascript:void(0):wink: 3月8日

image
image900×400 46.8 KB

本文为霍格沃兹测试学院优秀学员学习笔记，想一起系统进阶测试开发技术的同学文末加群交流。

1、join 多表查询简介

先来看w3c上给出的一张解释图

在这里插入图片描述896×359 56.9 KB

解释：

  • INNER JOIN：如果表中有至少一个匹配，则返回行
  • LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
  • RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
  • FULL JOIN：只要其中一个表中存在匹配，则返回行

2、实操演示

2.1 表结构说明

现在有这样一个公司部门人员各个信息的数据库，包含了如下几个表：

departments 部门表字段：

dept_emp 雇员部门表字段：

dept_manager 领导部门表字段：

employees 雇员表字段：

salaries 薪资表字段：

titles 岗位表字段：

2.2 INNER JOIN
  • INNER JOIN-取出一个员工的员工号、生日、部门名字

分析: 员工的员工号和生日在employees表中已经存在，想要获取部门的名字dept_name就要靠部门的编号dept_no去departments表中去查找，但是employees表中只有员工的编号，这样就需要通过employees表中的员工号emp_no来找到dept_emp表中对应的部门编号dept_no

先找出员工与部门对应的编号：

select e.emp_no,e.birth_date,d.dept_no
from employees e
inner join dept_emp d
on e.emp_no=d.emp_no;

再以上述结果与dept_emp表进行链接查询：

select e.emp_no,e.birth_date,d.dept_no,n.dept_name
from employees e
inner join (dept_emp d,departments n)
on e.emp_no=d.emp_no and d.dept_no=n.dept_no
order by emp_no;

2.3 LEFT JOIN
  • LEFT JOIN -取出一个员工的员工号、生日、部门名字，员工号和生日为必展示项 。

现在在 employees 表中有一个工号为10000，名为Allen Qin的员工，此员工还未分配岗位和部门，要将此员工在多表查询中也展示出来就要使用left join

select e.emp_no,e.birth_date,d.dept_no,n.dept_name

from employees e

left join (dept_emp d,departments n)

on e.emp_no=d.emp_no and d.dept_no=n.dept_no

order by emp_no;

2.4 RIGHT LEFT
  • RIGHT LEFT -将dept_emp和departments的数据合并展示，且部门表departments信息为必展示项。

现在departments表中有一个新建的部门DQA，部门编号为d010,此部门暂时还未招聘员工，要将此部门信息也展示出来就要使用right join

select * from dept_emp d

right join departments n

on d.dept_no=n.dept_no

order by emp_no;


![](https://mmbiz.qpic.cn/mmbiz_png/ervTCibwaujHXiacX5PlPMHmW2ySfYg9Cyxx7N0fOgQm8gIkkeibnpTIlVp1zmZib7ia9Y9Va6IEtQaGiad4dsoklUyw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1 "在这里插入图片描述")

##### 2.5 FULL JOIN

* **FULL JOIN** -将dept_emp表和departments表的行全部合并返回  
注：因为 MySQL 不支持 full join，所以想要实现此功能需要使用 left join union right join 的方法：
* ```
select * from dept_emp d

left JOIN departments n

on d.dept_no=n.dept_no

union

select * from dept_emp d

right join departments n

on d.dept_no=n.dept_no

order by emp_no limit 100;

参考链接：
MySQL full join: http://www.sql-tutorial.ru/en/book_full_join_and_mysql.html
w3c: SQL FULL OUTER JOIN Keyword
w3c.cn: SQL FULL JOIN 关键字

1 个赞