MySQL学习之《多表查询》

最新推荐文章于 2024-06-04 17:40:23 发布
最新推荐文章于 2024-06-04 17:40:23 发布
阅读量1.9k 收藏 7
点赞数
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_51313763/article/details/125180675
版权

多表查询

1. 熟悉常见的几个表
DESC employees;

在这里插入图片描述

DESC departments;

在这里插入图片描述

DESC locations;

在这里插入图片描述

查询员工名为’Abel’的人在哪个城市工作

SELECT * 
FROM employees
WHERE last_name = 'Abel';

SELECT *
FROM departments
WHERE department_id = 80;


SELECT *
FROM locations 
WHERE location_id = 2500;
2. 出现笛卡尔积的错误

笛卡尔乘积是一个数学运算。
假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。
笛卡尔积的错误会在下面条件下产生：

  • 省略多个表的连接条件（或关联条件）
  • 连接条件（或关联条件）无效
  • 所有表中的所有行互相连接

错误的实现方式：每个员工都与每个部门匹配了一遍。

SELECT employee_id,department_name
FROM employees,departments;  #查询出2889条记录

错误的方式

SELECT employee_id,department_name
FROM employees CROSS JOIN departments;  #查询出2889条记录

SELECT *
FROM employees;    #107条记录

SELECT *
FROM departments; # 27条记录
3. 多表查询的正确方式：需要有连接条件

为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

加入连接条件后，查询语法：

SELECT table1.column, table2.column 
FROM table1, table2 
WHERE table1.column1 = table2.column2; #连接条件

在 WHERE子句中写入连接条件。

SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件
WHERE employees.`department_id` = departments.department_id;

在这里插入图片描述

4. 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。
SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;

建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。
在这里插入图片描述

5. 可以给表起别名，在SELECT和WHERE中使用表的别名。
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;

在这里插入图片描述
如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
如下的操作是错误的：

SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = departments.department_id;

在这里插入图片描述

结论：如果有n个表实现多表的查询，则需要至少n-1个连接条件

练习：查询员工的employee_id,last_name,department_name,city

SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_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。

等值连接 vs 非等值连接

非等值连接的例子：

SELECT *
FROM job_grades;

在这里插入图片描述练习：查询员工 姓名，工资和工资等级

SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
#where e.`salary` between j.`lowest_sal` and j.`highest_sal`;
WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= j.`highest_sal`;

在这里插入图片描述

自连接 vs 非自连接

SELECT * FROM employees;

自连接的例子：
练习：查询员工id,员工姓名及其管理者的id和姓名

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

在这里插入图片描述

内连接 vs 外连接

内连接

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id;

在这里插入图片描述
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。
右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。

  • 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
  • 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

练习：查询所有的员工的last_name,department_name信息

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id;   # 需要使用左外连接

SQL92语法实现内连接：如上
SQL92语法实现外连接：使用 + ----------MySQL不支持SQL92语法中外连接的写法！

#不支持：
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id(+);

SQL99语法中使用 JOIN …ON 的方式实现多表的查询。
这种方式也能解决外连接的问题。MySQL是支持此种方式的。
SQL99语法如何实现多表的查询。

SQL99语法实现内连接：

SELECT last_name,department_name
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

在这里插入图片描述

SELECT last_name,department_name,city
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;

在这里插入图片描述
SQL99语法实现外连接：

练习：查询所有的员工的last_name,department_name信息
左外连接：

SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

在这里插入图片描述
右外连接：

SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

在这里插入图片描述
满外连接：mysql不支持FULL OUTER JOIN

SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

在这里插入图片描述

UNION 和 UNION ALL的使用

UNION：会执行去重操作
UNION ALL:不会执行去重操作
结论：如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

7种JOIN的实现：

在这里插入图片描述

中图：内连接

SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

在这里插入图片描述
左上图：左外连接

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

在这里插入图片描述
右上图：右外连接

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

在这里插入图片描述
左中图：

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

在这里插入图片描述
右中图：

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

在这里插入图片描述
左下图：满外连接
方式1：左上图 UNION ALL 右中图

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

在这里插入图片描述
方式2：左中图 UNION ALL 右上图

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

在这里插入图片描述

右下图：左中图 UNION ALL 右中图

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

在这里插入图片描述

10. SQL99语法的新特性

1:自然连接
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

在这里插入图片描述
NATURAL JOIN : 它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

在这里插入图片描述

2:USING
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

在这里插入图片描述

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

在这里插入图片描述

张大锤锤不秃头
关注
  • 0
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL学习-基础篇-多表查询
szq207819993的博客
04-11 229
​​​​​​​id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',no varchar(10) comment '学号')comment '学生表';
MySQL多表查询练习
weixin_43239001的博客
12-12 405
MySQL多表查询练习 创建student和score表 CREATE TABLE student ( id INT(10) NOT NULL UNIQUE PRIMARY KEY , name VARCHAR(20) NOT NULL , sex VARCHAR(4) , birth YEAR, department VARCHAR(20) , address VARCHAR(50) ); 创建score表。SQL代码如下： CREATE TABLE score ( i
mysql加强（4）~多表查询：笛卡尔积、消除笛卡尔积操作(等值、非等值连接)，内连接（隐式连接、显示连接)、外连接、自连接
m0_66468899的博客
09-08 1421
数学上，有两个集合A={a,b},B={1,2,3},则两个集合的笛卡尔积={{a,1}, {a,2}, {a,3}, {b,1}, {b,2}, {b,3}} 列出所有情况,一共是2*3=6条记录在数据库中，笛卡尔积是多表查询没有连接条件时返回的表结果。通过一个列名去寻找对应的另外一个列名。
如何避免查询结果出现笛卡尔积
Gorden的博客
08-31 1万+
首先，先简单解释一下笛卡尔积。 现在，我们有两个集合A和B。 A = {0,1}     B = {2,3,4} 集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式： A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}； B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}； 以上A×B和B×A的结果就可以...
学习分享-如何避免 Apache ShardingSphere 中的笛卡尔积现象
最新发布
weixin_62079735的博客
06-04 1466
Apache ShardingSphere 是一个开源的分布式数据库中间件，旨在通过数据分片、分布式事务、分布式治理等技术，提升数据库系统的性能和可扩展性。然而，最近在使用 ShardingSphere 进行分库分表并多表查询时，出现了笛卡尔积现象。本文将和大家分享介绍我遇到的问题，并提供避免该现象的方法。笛卡尔积是指在没有适当的连接条件下，将两个或多个表中的所有行进行组合，生成一个包含所有可能行组合的结果集。
20 SQL——多表查询 （消除无效笛卡尔积）
weixin_63215361的博客
05-17 921
【代码】20 SQL——多表查询 （消除无效笛卡尔积）
外连接如何避免笛卡尔积
weixin_35752645的博客
12-19 908
在 SQL 中，要避免笛卡尔积，需要使用 JOIN 语句来显式地连接两个表。JOIN 语句有几种不同的类型，其中 INNER JOIN 和 OUTER JOIN 是最常用的。 INNER JOIN：只返回两个表中共同存在的行。 OUTER JOIN：返回两个表中所有的行，即使在另一个表中没有匹配项。有 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种。LEFT OUTER...
数据库中的笛卡尔积：定义、生成与避免策略
全栈川川
12-04 4290
数据库中的笛卡尔积：定义、生成与避免策略
MySQL 多表查询实现分析
09-11
MySQL 多表查询实现分析 MySQL 多表查询是指在一个数据库中，存在多个表，这些表都是...通过学习 MySQL 多表查询，我们可以更好地理解数据库中的数据关系，实现复杂数据查询和分析，并提高数据库应用的效率和性能。
三、MySQL查询 学习PPT
06-11
MySQL 查询学习 PPT MySQL 查询学习 PPT 是 MySQL 数据库管理系统...MySQL 查询学习 PPT 涵盖了 MySQL 查询语言的基本概念、单表查询、多表查询、视图、索引、优化等知识点，是 MySQL 数据库管理系统的重要组成部分。
MySQL中表子查询与关联子查询的基础学习教程
12-15
MySQL中的表子查询和关联子查询是SQL查询中两种重要的技术，它们在处理复杂的数据查询时发挥着关键作用。本文将深入探讨这两种子查询的概念、用法和优化策略。 首先，我们来看表子查询。表子查询指的是在一个查询中...
MySQL 联合查询案例代码
01-30
需要搭配博主【MySQL】联合查询这篇文章使用，用于初始化数据，搭配文章学习可以充分学会联合查询，建议先学习博主 MySQL 专栏前部分的基础知识，联合查询是在有一定基础后进行学习的，里面的案例非常多，...
MySQL多表查询(四).pdf
05-01
这份PDF文档深入介绍了MySQL中多表查询的高级技巧和最佳实践，是多表查询系列教程的第四部分。主要内容包括： 内连接（INNER JOIN）：使用内连接合并两个或多个表的行。 外连接（OUTER JOIN）：包括左外连接、右外...
【MySQL】MySQL中多表连接的背景、笛卡尔积的错误与如何正确地多表查询
Sihang_Xie的博客
06-28 1452
MySQL中为什么要使用多表连接？什么是笛卡尔积的错误？笛卡尔积错误是如何发生的？如何避免笛卡尔积错误的出现？如何正确地多表查询？看完本篇文章，相信你会得到答案。
面试题——sql怎么避免笛卡尔积问题
weixin_45386694的博客
10-25 394
当时我其实没想太多，分别distinct查询性别为男的子表和性别为女的子表，然后进行inner join再取一次distinct。需要补充一点，union是去重的，但是union all不去重，直接拼接。给定一个students表格，字段包括【身份证号（主键），姓名，性别】现在需要查询出男女姓名一样的名字，最后名字不得有重复，怎么进行查询？分享一下个人理解，如果有更好的方法，欢迎一起讨论～
规避笛卡尔积
weixin_30470857的博客
07-09 577
笛卡尔积形成的条件： 1.查询语句中漏掉了连接条件 2.查询语句中两个表中的所有行都满足连接条件 3.查询语句中的连接条件无效 在实际工作中应该尽量的避免出现笛卡尔积，在多表连接查询语句的WHERE字句中，必须永远是用有效而正确的连接条件。 举例说明：select * from a,b,c where a.i = c.i and c.i = b.i假设...
你的sql如何避免笛卡尔积
热门推荐
不会英语的程序员不是好开拓者
06-30 2万+
笛卡尔积数学概念        笛卡尔积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。        笛卡尔积又叫笛卡尔乘积，是一个叫笛卡尔的人提出来的。 简单的说就是两个集合相乘的结果。         假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的...
多对多连表查询消除笛卡儿积
for_champagen的博客
03-26 373
如何解决多对多连表查询时出现的笛卡尔积重复数据的现象
MySQL 多表连接及笛卡尔乘积消除
weixin_41111116的博客
08-29 3689
MySQL 多表连接及笛卡尔乘积消除连接分类笛卡尔乘积 连接分类 内连接 等值连接 在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复属性。 （区别于自然连接） 非等值连接 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的 列的列值。这些运算符包括>、>=、<=、<、!>、!<和<> 自然连接 自然连接是按照等值连接，并且展示的结果中不包含重复的属性列，保留了所有不重复的属性列，则称之
查询与维护学生信息管理数据表2.pptx
11-02
MySQL数据库

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值