【MySQL】 多表查询 的初体验
目录
- 一 、 S Q L 99 语 法 实 现 多 表 查 询 一、SQL99语法实现多表查询 一、SQL99语法实现多表查询
-
- 🔥基本语法
- 🔥语法说明
- 🔥内连接的实现
- 🔥外连接的实现
-
- 👌左外连接
- 👌右外连接
- 👌满外连接
- 二 、 多 表 查 询 分 类 二、多表查询分类 二、多表查询分类
-
- 🔥分类1:等值连接 vs 非等值连接
- 🔥分类2:自连接 vs 非自连接
- 🔥分类3:内连接 vs 外连接
- 三 、 U N I O N 的 使 用 三、UNION的使用 三、UNION的使用
- 四 、 S Q L 99 语 法 新 特 性 四、SQL99语法新特性 四、SQL99语法新特性
-
- 🔥自然连接
- 🔥USING连接
- 五 、 笛 卡 尔 积 五、笛卡尔积 五、笛卡尔积
- 六 、 7 种 S Q L J O I N S 的 实 现 六、7种SQL JOINS的实现 六、7种SQLJOINS的实现
老规矩先上 表 :
一 、 S Q L 99 语 法 实 现 多 表 查 询 一、SQL99语法实现多表查询 一、SQL99语法实现多表查询
多表查询,也称为关联查询,指两个或更多个表一起完成查询操作
🔥基本语法
使用JOIN...ON
子句创建连接的语法结构:
SELECT table1.column, table2.column,table3.columnFROM table1JOIN table2 ON table1 和 table2 的连接条件JOIN table3 ON table2 和 table3 的连接条件
🔥语法说明
- 可以使用 ON 子句指定额外的连接条件
- 这个连接条件是与其它条件分开的
- ON 子句使语句具有更高的易读性
- 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的,表示内连接
🔥内连接的实现
INNER JOIN
语法:
SELECT 字段列表FROM A表 INNER JOIN B表ON 关联条件WHERE 等其他子句;
🔥外连接的实现
OUTER JOIN
👌左外连接
LEFT OUTER JOIN
语法:
#实现查询结果是ASELECT 字段列表FROM A表 LEFT JOIN B表ON 关联条件WHERE 等其他子句;
👌右外连接
RIGHT OUTER JOIN
语法:
#实现查询结果是BSELECT 字段列表FROM A表 RIGHT JOIN B表ON 关联条件WHERE 等其他子句;
👌满外连接
FULL OUTER JOIN
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
SQL99是支持满外连接的,使用FULL JOIN 或 FULL OUTER JOIN来实现。但MySQL不支持FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT JOIN代替
二 、 多 表 查 询 分 类 二、多表查询分类 二、多表查询分类
多表查询,也称为关联查询,指两个或更多个表一起完成查询操作
前提条件:多表一起查询的表之间是有关系的(一对一、一对多)
🔥分类1:等值连接 vs 非等值连接
等值连接
一个表的外键与另一个表的主键之间建立的 “=” 连接
SELECT e.last_name, e.salary, j.grade_levelFROM employees e, job_grades jWHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
非等值连接
多表中指使用除等号…=…外的其他运算符号作为连接查询。其他符号指…、=、、between…and
SELECT e.last_name, e.salary, j.grade_levelFROM employees e, job_grades jWHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
🔥分类2:自连接 vs 非自连接
查询employees表,返回“Xxx works for Xxx”
SELECT CONCAT(worker.last_name ,' works for ', manager.last_name)FROM employees worker, employees managerWHERE worker.manager_id = manager.employee_id ;
🔥分类3:内连接 vs 外连接
内连接
: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接
: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)
如果是左外连接,则连接条件中左边的表也称为 主表 ,右边的表称为 从表
如果是右外连接,则连接条件中右边的表也称为 主表,左边的表称为 从表
#左外连接SELECT last_name,department_nameFROM employees ,departmentsWHERE employees.department_id = departments.department_id(+);#右外连接SELECT last_name,department_nameFROM employees ,departmentsWHERE employees.department_id(+) = departments.department_id;
三 、 U N I O N 的 使 用 三、UNION的使用 三、UNION的使用
合并查询结果
利用UNION关键字
,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并时,两个表对应的列数和数据类型必须相同,并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔
SELECT column,... FROM table1UNION [ALL]SELECT column,... FROM table2
UNION 操作符返回两个查询的结果集的并集,去除重复记录
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重
🎁注:执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据
不存在重复数据,或不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效率
四 、 S Q L 99 语 法 新 特 性 四、SQL99语法新特性 四、SQL99语法新特性
select查询列表from表1别名[连接类型]join表2别名on连接条件where[筛选条件]group by [分组]having[筛选条件]order by [排序列表]
🔥自然连接
可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ,然后进行 等值连接
SQL92:
SELECT employee_id,last_name,department_nameFROM employees e JOIN departments dON e.`department_id` = d.`department_id`AND e.`manager_id` = d.`manager_id`;
SQL99:
SELECT employee_id,last_name,department_nameFROM employees e NATURAL JOIN departments d;
🔥USING连接
SQL99还支持使用 USING
指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用
SELECT employee_id,last_name,department_nameFROM employees e JOIN departments dUSING (department_id);
USING 指定了具体的相同的字段名称,你需要在 USING的括号 () 中填入要指定的同名字段
五 、 笛 卡 尔 积 五、笛卡尔积 五、笛卡尔积
交叉连接
CROSS JOIN
可以把任意表进行连接,即使这两张表不相关
笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数
六 、 7 种 S Q L J O I N S 的 实 现 六、7种SQL JOINS的实现 六、7种SQLJOINS的实现
代码实现:
#中图:内连接 A∩BSELECT employee_id,last_name,department_nameFROM employees e JOIN departments dON e.`department_id` = d.`department_id`;
#左上图:左外连接SELECT employee_id,last_name,department_nameFROM employees e LEFT JOIN departments dON e.`department_id` = d.`department_id`;
#右上图:右外连接SELECT employee_id,last_name,department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`;
#左中图:A - A∩BSELECT employee_id,last_name,department_nameFROM employees e LEFT JOIN departments dON e.`department_id` = d.`department_id`WHERE d.`department_id` IS NULL
#右中图:B-A∩BSELECT employee_id,last_name,department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`WHERE e.`department_id` IS NULL
#左下图:满外连接# 左中图 + 右上图 A∪BSELECT employee_id,last_name,department_nameFROM employees e LEFT JOIN departments dON e.`department_id` = d.`department_id`WHERE d.`department_id` IS NULLUNION ALL #没有去重操作,效率高SELECT employee_id,last_name,department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`;
#右下图#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ (B - A∩B)SELECT employee_id,last_name,department_nameFROM employees e LEFT JOIN departments dON e.`department_id` = d.`department_id`WHERE d.`department_id` IS NULLUNION ALLSELECT employee_id,last_name,department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`WHERE e.`department_id` IS NULL