本篇内容介绍了“MySQL多表查询案例分析”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
笛卡尔积的理解
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 ;#非等值
非自链接:表1和表2连接
自链接:表1和自己连接
#显示员工(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...
左外连接:LEFT OUTER JOIN
右外连接:RIGHT OUTER JOIN
满外连接:FULL OUTER JOIN(MySQL不支持)
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
UNION操作符
两个查询结果的并集,去重(效率低)
UNION ALL操作符(推荐)
两个查询结果的并集,不去重(效率高)
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多表查询案例分析”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。