Mysql:多表查询 前言附录:常用的 SQL 标准有哪些一、一个案例引发的多表连接1、案例说明2、笛卡尔积(或交叉连接)的理解3、案例分析与问题解决 二、多表查询分类讲解1、等值连接 vs 非等值连接1.1 等值连接1.2 非
新星计划,等你来造,一起学习进步!
7月3日-7月15日期间,完成计划任务,完成打卡赢好礼,活动报名链接如下:点击跳转
活动奖励:
❀【新秀奖】新注册用户发布第一篇文章(500字以上)获得电子【新秀勋章】;
❀【基础奖】完成任务挑战用户可获专属电子勋章(潜力新星)+抽奖机会(百分百中奖)
❀【特别奖】各导师评选团队综合top5,获实力新星实体证书/实体奖牌红色款二选一+APP作者推荐关注+实力
接下来我们进入正文!
多表查询,也称为关联查询,指两个或更多个表一起完成查询操作。
前提条件:这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段,这个关联字段可能建立了外键,也可能没有建立外键。比如:员工表和部门表,这两个表依靠“部门编号”进行关联。
从多个表中获取数据:
#案例:查询员工的姓名及其部门名称SELECT last_name, department_nameFROM employees, departments;
查询结果:
+-----------+----------------------+| last_name | department_name |+-----------+----------------------+| King | Administration || King | Marketing || King | Purchasing || King | Human Resources || King | Shipping || King | IT || King | Public Relations || King | Sales || King | Executive || King | Finance || King | Accounting || King | Treasury |...| Gietz | IT Support || Gietz | NOC || Gietz | IT Helpdesk || Gietz | Government Sales || Gietz | Retail Sales || Gietz | Recruiting || Gietz | Payroll |+-----------+----------------------+2889 rows in set (0.01 sec)
分析错误情况:
SELECT COUNT(employee_id) FROM employees;#输出107行SELECT COUNT(department_id)FROM departments;#输出27行SELECT 107*27 FROM dual;
我们把上述多表查询中出现的问题称为:笛卡尔积的错误。
笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。
SQL92中,笛卡尔积也称为交叉连接,英文是 CROSS JOIN
。在 SQL99 中也是使用 CROSS JOIN
表示交叉连接。它的作用就是可以把任意表进行连接,即使这两张表不相关。在mysql中如下情况会出现笛卡尔积:
#查询员工姓名和所在部门名称SELECT last_name,department_name FROM employees,departments;SELECT last_name,department_name FROM employees CROSS JOIN departments;SELECT last_name,department_name FROM employees INNER JOIN departments;SELECT last_name,department_name FROM employees JOIN departments;
笛卡尔积的错误会在下面条件下产生:
为了避免笛卡尔积, 可以在 WHERE
加入有效的连接条件。
加入连接条件后,查询语法:
SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column1 = table2.column2; #连接条件
- 在 WHERE子句中写入连接条件。
#案例:查询员工的姓名及其部门名称SELECT last_name, department_nameFROM employees, departmentsWHERE employees.department_id = departments.department_id;
- 在表中有相同列时,在列名之前加上表名前缀。
SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_idFROM employees, departmentsWHERE employees.department_id = departments.department_id;
拓展1:多个连接条件与 AND 操作符
拓展2:区分重复的列名
SELECT employees.last_name, departments.department_name,employees.department_idFROM employees, departmentsWHERE employees.department_id = departments.department_id;
拓展3:表的别名
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e , departments dWHERE e.department_id = d.department_id;
需要注意的是,如果我们使用了表的别名,在查询字段中、过滤条件中就只能使用别名进行代替,不能使用原有的表名,否则就会报错。
阿里开发规范:
【强制】对于数据库中表记录的查询和变更,只要涉及多个表,都需要在列名前加表的别名(或 表名)进行限定。
说明:对多表进行查询记录、更新记录、删除记录时,如果对操作列没有限定表的别名(或表名),并且操作列在多个表中存在时,就会抛异常。
正例:select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;
反例:在某业务中,由于多表关联查询语句没有加表的别名(或表名)的限制,正常运行两年后,最近在 某个表中增加一个同名字段,在预发布环境做数据库变更后,线上查询语句出现出 1052 异常:Column 'name' in field list is ambiguous
拓展4:连接多个表
总结:连接 n个表,至少需要n-1个连接条件。比如,连接三个表,至少需要两个连接条件。
SELECT e.last_name, e.salary, j.grade_levelFROM employees e, job_grades jWHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
当table1
和table2
本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接,外连接等查询。
题目:查询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 ;
#左外连接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;
SELECT table1.column, table2.column,table3.columnFROM table1 JOIN table2 ON table1 和 table2 的连接条件 JOIN table3 ON table2 和 table3 的连接条件
它的嵌套逻辑类似我们使用的 FOR 循环:
for t1 in table1: for t2 in table2: if condition1: for t3 in table3: if condition2: output t1 + t2 + t3
SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强,即使再多的表进行连接也都清晰可见。如果你采用 SQL92,可读性就会大打折扣。
SELECT 字段列表FROM A表 INNER JOIN B表ON 关联条件WHERE 等其他子句;
题目1:
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id);
题目2:
SELECT employee_id, city, department_nameFROM employees e JOIN departments dON d.department_id = e.department_id JOIN locations lON d.location_id = l.location_id;![
#实现查询结果是ASELECT 字段列表FROM A表 LEFT JOIN B表ON 关联条件WHERE 等其他子句;
SELECT e.last_name, e.department_id, d.department_nameFROM employees eLEFT OUTER JOIN departments dON (e.department_id = d.department_id) ;
#实现查询结果是BSELECT 字段列表FROM A表 RIGHT JOIN B表ON 关联条件WHERE 等其他子句;
SELECT e.last_name, e.department_id, d.department_nameFROM employees eRIGHT OUTER JOIN departments dON (e.department_id = d.department_id) ;
需要注意的是,LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中,在 SQL92 中不存在,只能用 (+) 表示。
来源地址:https://blog.csdn.net/weixin_52533007/article/details/131239554
--结束END--
本文标题: MySQL:多表查询(全面详解)
本文链接: https://www.lsjlt.com/news/371737.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