MySQL多表查询案例分析

MySQL多表查询案例分析

多表查询

案列说明

笛卡尔积的理解

select id,department_namefrom employees,departments;#错的select id,department_id,department_namefrom employees CROSS JOIN departments;#错的登录后复制

每个员工和每个部门匹配了一遍（查出的条目数=id数*department数）

错误原因：缺少连接条件

笛卡尔积的解决

编写连接条件: 表1.列 = 表二.列（若多个表连接，至少要用n-1个连接条件）

select id,employees.name,department_name from employees,departmentsWHERE employees.name = departments.name;登录后复制

注：如果要显示的列在要查询的表中名字一样，则要表明，是出自哪个表， eg: employees.name

建议在多表查询时，标明显示的是哪个表的信息 （优化）

优化：可以在FROM后使用表的别名，但是一旦使用别名，后续就一定要都用别名

多表查询的分类

等值连接和非等值连接

等值连接：上述的带有=的非等值连接：没有=的

select t1.id,t1.name,t2.gradefrom employees t1,departments t2WHERE ti.salary BETWEEN t2.lowest_salary AND t2.highest_salary ;#非等值登录后复制

自连接和非自连接

非自链接：表1和表2连接自链接：表1和自己连接

#显示员工（t1）和其管理者（t2）的基本信息select t1.id,t1.name,t2.id，t2.namefrom employees t1,employees t2#一个表看作两个表WHERE t1.manage_id = t2.id ;#自连接登录后复制

内连接和外连接

内连接：合并含有同一列的表，结果不包括一个表与另一个表不匹配打的行外连接：合并含有同一列的表，结果除了内连接的结果还查询不匹配的行

外连接的分类：左外连接（左表多，补右边），右外连接（右表多，补左边），满外连接

SQL92:使用（+）创建连接

内连接：见上

外连接：左表有数据不匹配，在右表加（+）；反之，在左表加（+），但是MySQL不支持

WHERE t1.department_id = t2.department_id(+)#左连接登录后复制

SQL99：使用JOIN...ON的方式

内连接

select t1.id,t1.name,t2.department_name,t3.environmentfrom employees t1 JOIN departments t2ON t1.department_id = t2.department_idJOIN locations t3#加入第二个人表ON t2.department_location = t3.department_location;登录后复制

外连接

使用OUTER JOIN...ON...

左外连接：LEFT OUTER JOIN右外连接：RIGHT OUTER JOIN满外连接：FULL OUTER JOIN（MySQL不支持）

select t1.name,t2.department_name#左外连接from employees t1 LEFT OUTER（可省略） JOIN departments t2ON t1.department_id = t2.department_id；登录后复制

UMION的使用

合并查询结果

SELECT colum... FROM table1UNION (ALL)SELECT colum... FROM table2登录后复制

UNION操作符

两个查询结果的并集，去重（效率低）

UNION ALL操作符（推荐）

两个查询结果的并集，不去重（效率高）

7种SQL JOINS的实现

中图（内连接）：

select t1.name,t2.department_namefrom employees t1 JOIN departments t2ON t1.department_id = t2.department_id；登录后复制

左上图(左外连接)：

select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_id；登录后复制

右上图(右外连接)：

select t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_id；登录后复制

左中图：

select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULL;登录后复制

右中图：

select t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL;登录后复制

左下图(满外连接)：

#方式一：左上图 UNION ALL 右中图select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idUNION ALL select t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL;#方式二：左中图 UNION ALL 右上图select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULLUNION ALLselect t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_id；登录后复制

右下图：

#左中图 UNION ALL 右中图select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULLUNION ALLselect t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL;登录后复制

SQL语法新特性

自然连接

使用关键字：NATURAL JOIN（不灵活），自动查询表中所有相同字段，然后进行等值连接

USING连接(不适用于自连接)

使用关键字：USING（同名字段），将表中相同名字的字段自动等值连接

select t1.name,t2.department_namefrom employees t1 JOIN departments t2ON t1.department_id = t2.department_id;等价于select t1.name,t2.department_namefrom employees t1 JOIN departments t2USING(department_id);登录后复制

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。