多表查询 案列说明 笛卡尔积的理解 select id,department_name from employees,departments;#错的 select
笛卡尔积的理解
select id,department_name
from employees,departments;#错的
select id,department_id,department_name
from employees CROSS JOIN departments;#错的
每个员工和每个部门匹配了一遍(查出的条目数=id数*department数)
错误原因:缺少连接条件
编写连接条件: 表1.列 = 表二.列(若多个表连接,至少要用n-1个连接条件)
select id,employees.name,department_name
from employees,departments
WHERE employees.name = departments.name;
注:如果要显示的列在要查询的表中名字一样,则要表明,是出自哪个表, eg: employees.name
建议在多表查询时,标明显示的是哪个表的信息 (优化)
优化:可以在FROM后使用表的别名,但是一旦使用别名,后续就一定要都用别名
select t1.id,t1.name,t2.grade
from employees t1,departments t2
WHERE ti.salary BETWEEN t2.lowest_salary AND t2.highest_salary ;#非等值
#显示员工(t1)和其管理者(t2)的基本信息
select t1.id,t1.name,t2.id,t2.name
from employees t1,employees t2#一个表看作两个表
WHERE t1.manage_id = t2.id ;#自连接
外连接的分类:左外连接(左表多,补右边),右外连接(右表多,补左边),满外连接
内连接:见上
外连接:左表有数据不匹配,在右表加(+);反之,在左表加(+),但是Mysql不支持
WHERE t1.department_id = t2.department_id(+)#左连接
select t1.id,t1.name,t2.department_name,t3.environment
from employees t1 JOIN departments t2
ON t1.department_id = t2.department_id
JOIN locations t3#加入第二个人表
ON t2.department_location = t3.department_location;
使用OUTER JOIN...ON...
select t1.name,t2.department_name#左外连接
from employees t1 LEFT OUTER(可省略) JOIN departments t2
ON t1.department_id = t2.department_id;
合并查询结果
SELECT colum... FROM table1
UNION (ALL)
SELECT colum... FROM table2
两个查询结果的并集,去重(效率低)
两个查询结果的并集,不去重(效率高)
7种SQL JOINS的实现
中图(内连接):
select t1.name,t2.department_name
from employees t1 JOIN departments t2
ON t1.department_id = t2.department_id;
左上图(左外连接):
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id;
右上图(右外连接):
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id;
左中图:
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t2.department_id IS NULL;
右中图:
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t1.department_id IS NULL;
左下图(满外连接):
#方式一:左上图 UNION ALL 右中图
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id
UNION ALL
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t1.department_id IS NULL;
#方式二:左中图 UNION ALL 右上图
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t2.department_id IS NULL
UNION ALL
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id;
右下图:
#左中图 UNION ALL 右中图
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t2.department_id IS NULL
UNION ALL
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t1.department_id IS NULL;
使用关键字:NATURAL JOIN(不灵活),自动查询表中所有相同字段,然后进行等值连接
使用关键字:USING(同名字段),将表中相同名字的字段自动等值连接
select t1.name,t2.department_name
from employees t1 JOIN departments t2
ON t1.department_id = t2.department_id;
等价于
select t1.name,t2.department_name
from employees t1 JOIN departments t2
USING(department_id);
到此这篇关于MySQL多表查询的文章就介绍到这了,更多相关MySQL多表查询内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!
--结束END--
本文标题: MySQL多表查询的案例详解
本文链接: https://www.lsjlt.com/news/141444.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0