MySQL多表查询讲解( 三 ) _多表查询

文章插图

更直观的展现上述关系

mysql> select * from employee left join dept on employee.dept_id = dept.did and dept.did = 1;+----+-----------+-----+-----+----------+------------+---------+------+-----------+| id | name| age | sex | salary| hire_date| dept_id | did| dname|+----+-----------+-----+-----+----------+------------+---------+------+-----------+|1 | 菜虚鲲 |20 |2 | 10000.00 | 2020-01-10 |1 |1 | 研发部 ||2 | 奥力给 |30 |1 | 18000.00 | 2020-01-08 |1 |1 | 研发部 ||3 | 老八|28 |1 | 7000.00| 2020-01-07 |1 |1 | 研发部 ||4 | 小张|25 |1 | 8000.00| 2020-01-10 |1 |1 | 研发部 ||5 | 小红|20 |2 | 6000.00| 2020-01-05 |2 | NULL | NULL||6 | 小丽|23 |2 | 6500.00| 2020-01-05 |2 | NULL | NULL||7 | 小花|21 |2 | 5500.00| 2020-01-10 |2 | NULL | NULL||8 | 马小跳 |25 |1 | 7000.00| 2020-01-01 |3 | NULL | NULL||9 | 张大骚 |30 |1 | 9000.00| 2020-01-07 |3 | NULL | NULL|| 10 | 马冬梅 |31 |2 | 5000.00| 2020-01-07 |4 | NULL | NULL|| 11 | 川坚果 |60 |1 | 100.00| 2020-01-08 | NULL| NULL | NULL|+----+-----------+-----+-----+----------+------------+---------+------+-----------+11 rows in set (0.01 sec)

5. Right右外连接查询右外连接查询，与左外连接查询正好相反，即右表的数据全部显示。
5.1 语法select * from 表1 right join 表2 on [条件];5.2 实战查询员工和部门信息
select * from employee right join dept on employee.dept_id = dept.did;输出结果

mysql> select * from employee right join dept on employee.dept_id = dept.did;+------+-----------+------+------+----------+------------+---------+-----+-----------+| id| name| age| sex| salary| hire_date| dept_id | did | dname|+------+-----------+------+------+----------+------------+---------+-----+-----------+|1 | 菜虚鲲 |20 |2 | 10000.00 | 2020-01-10 |1 |1 | 研发部 ||2 | 奥力给 |30 |1 | 18000.00 | 2020-01-08 |1 |1 | 研发部 ||3 | 老八|28 |1 | 7000.00| 2020-01-07 |1 |1 | 研发部 ||4 | 小张|25 |1 | 8000.00| 2020-01-10 |1 |1 | 研发部 ||5 | 小红|20 |2 | 6000.00| 2020-01-05 |2 |2 | 人事部 ||6 | 小丽|23 |2 | 6500.00| 2020-01-05 |2 |2 | 人事部 ||7 | 小花|21 |2 | 5500.00| 2020-01-10 |2 |2 | 人事部 ||8 | 马小跳 |25 |1 | 7000.00| 2020-01-01 |3 |3 | 测试部 ||9 | 张大骚 |30 |1 | 9000.00| 2020-01-07 |3 |3 | 测试部 ||10 | 马冬梅 |31 |2 | 5000.00| 2020-01-07 |4 |4 | 销售部 || NULL | NULL| NULL | NULL | NULL| NULL| NULL|5 | 生产部 |+------+-----------+------+------+----------+------------+---------+-----+-----------+11 rows in set (0.00 sec)

6. UNION全连接查询全连接查询：其结果是在内连接查询的基础上显示左右两边没有的数据。
写法：左连接查询 UNION 右连接查询
6.1 语法select * from 表1 left join 表2 on [条件]unionselect * from 表1 right join 表2 on [条件]6.2 实战以全连接的形式查询部门和员工数据

select * from employee left join dept on employee.dept_id = dept.did unionselect * from employee right join dept on employee.dept_id = dept.did;

输出结果

mysql> select * from employee left join dept on employee.dept_id = dept.did-> union-> select * from employee right join dept on employee.dept_id = dept.did;+------+-----------+------+------+----------+------------+---------+------+-----------+| id| name| age| sex| salary| hire_date| dept_id | did| dname|+------+-----------+------+------+----------+------------+---------+------+-----------+|1 | 菜虚鲲 |20 |2 | 10000.00 | 2020-01-10 |1 |1 | 研发部 ||2 | 奥力给 |30 |1 | 18000.00 | 2020-01-08 |1 |1 | 研发部 ||3 | 老八|28 |1 | 7000.00| 2020-01-07 |1 |1 | 研发部 ||4 | 小张|25 |1 | 8000.00| 2020-01-10 |1 |1 | 研发部 ||5 | 小红|20 |2 | 6000.00| 2020-01-05 |2 |2 | 人事部 ||6 | 小丽|23 |2 | 6500.00| 2020-01-05 |2 |2 | 人事部 ||7 | 小花|21 |2 | 5500.00| 2020-01-10 |2 |2 | 人事部 ||8 | 马小跳 |25 |1 | 7000.00| 2020-01-01 |3 |3 | 测试部 ||9 | 张大骚 |30 |1 | 9000.00| 2020-01-07 |3 |3 | 测试部 ||10 | 马冬梅 |31 |2 | 5000.00| 2020-01-07 |4 |4 | 销售部 ||11 | 川坚果 |60 |1 | 100.00| 2020-01-08 | NULL| NULL | NULL|| NULL | NULL| NULL | NULL | NULL| NULL| NULL|5 | 生产部 |+------+-----------+------+------+----------+------------+---------+------+-----------+12 rows in set (0.00 sec)

7. 嵌套查询嵌套查询：在一个sql语句中使用多个select，第一次的查询结果可作为第二次查询结果的条件/表名使用。
7.1 作为表名使用select * from (select id,name,age from employee) as em where em.id = 1;查询结果

mysql> select * from (select id,name,age from employee) as em where em.id = 1;+----+-----------+-----+| id | name| age |+----+-----------+-----+|1 | 菜虚鲲 |20 |+----+-----------+-----+1 row in set (0.00 sec)

解释：把（select id,name,age from employee）语句的返回结果当作一个临时表，临时表的表名为em 。