06.MySQL数据库-多表查询和子查询
渐写到别来,此情深处,红笺为无色。
——晏几道《思远人》
MySQL数据库-多表查询和子查询
1. 多表查询
数据库在单个表里操作其实很简单,但是涉及在多张表里寻找数据的时候,难度会大大增加,这里解释一些多表联合查询常用的操作。
1 | -- 创建部门表 |
多表查询:我们想查询孙悟空的名字和他所在的部门的名字,则需要使用多表查询。
如果一条 SQL 语句查询多张表,因为查询结果在多张不同的表中。每张表取 1 列或多列。
1.1 多表查询分类
1 | graph TD; |
1.2 笛卡尔积现象
1.2.1 什么是笛卡尔积现象
1 | -- 需求:查询所有的员工和所有的部门 |
结果分析
1.2.2 清除笛卡尔积现象的影响
我们发现不是所有的数据组合都是有用的,只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。
1 | -- 设置过滤条件 Column 'id' in where clause is ambiguous select * from emp,dept where id=5; |
1.3 内连接
用左边表的记录去匹配右边表的记录,如果符合条件的则显示。如:从表.外键=主表.主键
1.3.1 显式内连接
使用 inner join… on语句, 可以省略 inner
1 | select 字段名 from 左表 [inner] join 右表 on 条件; |
1.3.2 隐式内连接
看不到 join关键字,条件使用 where指定
1 | select 字段名 from 左表,右表 where 条件; |
1 | -- 创建一个dept表,设置id字段为主键,并且设置主键自增 |
1.3.3 内连接总结
- 确定查询哪些表
- 确定表连接的条件 e.dept_id = d.id
- 确定查询的条件 where e.name=’唐僧‘
- 确定查询的字段 ** ,e.name,e.gender,e.salary,d.name**
1.4 外连接
1.4.1 左外连接
left outer join … on ,outer可以省略
用左边表的记录去匹配右边表的记录,
如果符合条件的则显示;
否则,显示 NULL
可以理解为:在内连接的基础上保证左表的数据全部显示(左表是部门,右表员工)
1 | select 字段名 from 左表 left [outer] join 右表 on 条件; |
1.4.2 右外连接
right outer join … on ,outer 可以省略
用右边表的记录去匹配左边表的记录,
如果符合条件的则显示;
否则,显示 NULL 可以理解为:在内连接的基础上保证右表的数据全部显示
1 | -- 在员工表中增加一个员工 |
2. 子查询
子查询的概念:
- 一个查询的结果做为另一个查询的条件
- 有查询的嵌套,内部的查询称为子查询
- 子查询要使用括号
1 | -- 查询开发部中有哪些员工 |
2.1 子查询结果的三种情况
2.1.1 子查询的结果是单行单列
子查询结果只要是单行单列,肯定在 WHERE 后面作为条件,
父查询使用:比较运算符,如:> 、<、<>、= 等
==select 查询字段 from 表 where 字段=(子查询);==
2.1.2 子查询的结果是多行单列
子查询结果是单例多行,结果集类似于一个数组,父查询使用 IN 运算符
==select 查询字段 from 表 where 字段 in (子查询);==
2.1.3 子查询的结果是多行多列
子查询结果只要是多列,肯定在 from 后面作为表
==select 查询字段 from (子查询) 表别名 where 条件;==
子查询作为表需要取别名,否则这张表没有名称则无法访问表中的字段
2.1.4 子查询总结
- 子查询结果只要是单列,则在 where后面作为条件
- 子查询结果只要是多列,则在 from 后面作为表进行二次查询