【MySQL】06-多表查询

最新推荐文章于 2023-12-22 20:51:59 发布
最新推荐文章于 2023-12-22 20:51:59 发布
阅读量1.1k 收藏 5
点赞数 1
分类专栏： MySQL 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44922493/article/details/127024040
版权

6.多表查询

多表查询：也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

1.多表查询的一个案例

SELECT COUNT(employee_id) FROM employees;
#输出107行
SELECT COUNT(department_id)FROM departments;
#输出27行

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;
#2889 rows in set (0.01 sec)

首先，last_name属于员工表employees的字段，department_name属于部门表departments的字段，如果想查询员工名字及该员工所属的部门，并且使用了上述的查询语句，将得到2889条数据，显然这不是我们想要的。上述多表查询中出现的问题称为：笛卡尔积的错误。

2.笛卡尔积（或称 交叉连接）的理解

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。如下图所示：

image-20220923121838349

SQL92中，笛卡尔积也称为交叉连接，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：

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

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

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

按照上面规则，改写上面的例子：

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
#在表中有相同列时，在列名之前加上表名前缀。

3.多表查询分类讲解

3.1 等值连接vs非等值连接

等值连接

上一节中查询员工的姓名及其部门名称的例子中where子句的判断条件就是在做等值判断，属于等值连接

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
#在表中有相同列时，在列名之前加上表名前缀。

扩展：表的别名

  • 使用别名可以简化查询
  • 列名前使用表名前缀可以提高查询效率
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;

需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，
不能使用原有的表名，否则就会报错。

扩展：连接多个表

连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件。

非等值连接

只要多表连接查询时，where子句的判断条件不是取等判断的，就是非等值连接

3.2 自连接vs非自连接

自连接

下图是员工表的数据，这里在逻辑上，每个人都是员工，但不是所有员工都属于管理者（上级）。比如第一条数据的King就没有上级，第二条数据的Kochhar的上级id是100，即King。

image-20220923123417740

要怎么查询出员工和该员工上级的名字呢？可以使用自连接，如下：

SELECT CONCAT(worker.last_name ,' works for '
, manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

查询结果：

image-20220923123911670

自连接本质上查询的是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。

非自连接

只要多表查询的不是出自同一张表，就认为是非自连接

3.3 内连接vs外连接

内连接

现有员工表数据107条，其中有一条部门id为null，如下图：

image-20220923125034540

现在查询员工id和对应部门名字，需要多表查询，语句如下：

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

结果如下：

image-20220923125203979

一共查询出了106条数据，明显地，那条部门为null地员工数据不在结果集中。因此得出内连接特点：根据where子句的判断条件，将满足条件的多表中的行数据进行合并，在结果集中不包含不满足条件的行数据。

外连接

需要说明的是，内连接和外连接是平常工作中使用最多的多表查询类型。上面我们知道了内连接的特点，不难推理出外连接的特点：==根据where子句的判断条件，将满足条件的多表中的行数据进行合并，在结果集中还包含不满足条件的行数据。==我们以两个表查询为例，形式可以分为左表和右表，根据这个不满足条件的行数据是在左表中，还是右表中，还是左右都有，外连接又细分为左外连接、右外连接和满外连接。

比如，还是上面员工和部门的例子，形式上认为员工表是左表，部门表为右表（左表还是右表看编写查询语句时，哪张表先出现那张表就是左表）。

员工数据中有一条数据部门id为null，用左外连接能查出107条数据，包括那条特殊员工数据（内连接能查出106条数据）。

有时，部门下并没有员工，用右外连接能查出107条数据，包括那条特殊部门数据。

满外连接即，内连接结果集加上左右两表中特殊的数据。

注意，外连接的语法在SQL92和SQL99中有不同的写法，在MySQL中不支持SQL92的写法，具体的外连接写法将在后续内容展开。

4.SQL99语法实现多表查询

4.1.基本语法

使用JOIN…ON子句创建连接的语法结构：

SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
  • 可以使用 ON 子句指定额外的连接条件。
  • 这个连接条件是与其它条件分开的。
  • ON 子句使语句具有更高的易读性。
  • 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接。

4.2内连接 INNER JOIN

语法：

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

例如：

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);

注意：一般使用内连接时，省略INNER，只写JOIN即表示内连接

4.3外连接 OUTER JOIN

4.3.1左外连接 LEFT OUTER JOIN

语法：

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

例子：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
#结果集中将包含左表（employees）不满足匹配条件的数据

注意：一般使用左连接时，省略OUTER，只写LEFT JOIN即表示内连接

4.3.2右外连接 RIGHT OUTER JOIN

语法：

FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

例子：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
# 结果集中将包含右表（departments）不满足匹配条件的数据

注意：一般使用右连接时，省略OUTER，只写RIGHT JOIN即表示内连接

4.3.3满外连接 FULL OUTER JOIN
  • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
  • SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
  • 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN代替。

4.4UNION的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNIONALL关键字分隔。

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
UNION vs UNIONALL

UNION

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

image-20220924125420455

UNIONALL

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

image-20220924125819001

注意

执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据
不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效
率。

举例：查询部门编号>90或邮箱包含a的员工信息

#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

#方式2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

5. 7种JOIN的实现

image-20220924130439978

5.1代码实现

#中图：内连接 A∩B
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 LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

#左下图 = 左上图 + 右中图  或者 （左下图 = 左中图 + 右上图）
SELECT employee_id,last_name,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,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右下图 = 左中图 + 右中图
# A ∪ B- A ∩ B 或者 (A - A ∩ B) ∪ （B - A ∩ B）
SELECT employee_id,last_name,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,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

者 (A - A ∩ B) ∪ （B - A ∩ B）
SELECT employee_id,last_name,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,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
Eneru_7
关注
  • 1
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
06mysql分组查询
01-16
这是我自己学习mysql时的学习笔记，每一个知识点都是自己动手写的，涵盖了mysql中的几乎全部的基础知识点，查询，子查询，分组，排序，常用函数，多表连接，视图，变量，存储过程，函数，分支结构，循环结构等等
第06章_多表查询.docx
最新发布
01-17
此系列资源适用于MySQL数据库的学习，以及高级SQL语句
『MySQL』行号关联查询
12-14
前言 永远相信美好的事情即将发生 背景 前段时间碰到过这样一个问题，查询出一家店铺在2020年和2019年活动类型为’xxx’的日期，因历史原因，2019年的活动和2020年的活动分别存储在两张表中，需要通过外连接将查询结果放在一起展示，最终要的结果大概长这样： 2019年日期 2019年活动 2020年日期 2020年活动 2019/01/05 xxx节 2020/02/01 xxx节 2019/03/12 xxx节 2020/04/10 xxx节 2019/07/19 xxx节 2020/06/05 xxx节 2020/09/09 xxx节 2020
大型门户网站核心技术-Mysql优化
07-23
 教程名称：大型门户网站核心技术-Mysql优化  课程目录：【】Mysql优化 资料【】Mysql优化01关键技术【】Mysql优化02表的设计【】Mysql优化03慢查询(一)【】Mysql优化04慢查询(二)【】Mysql优化05慢查询(三)【】Mysql优化06索引(一)【】Mysql优化07索引(二)【IT教 资源太大，传百度网盘了，链接在附件中，有需要的同学自取。
2023-06-05 stonedb-在派生表的场景查询为空无法传递默认值-mysql/sql-innodb的trace日志
06-05
mysqld.trace
SQL多表查询的注意点,以及 join on 、where 执行的顺序
u013400314的博客
01-06 5490
1、多表查询 where 、 join①where 实现多表查询缺点：在上语句中，实际先查询 表a，b 两个表的cross join 【笛卡儿积】，然后通过正确a.ID=b.ID 过滤正常的数据，这是一种低效利用数据库资源。为了避免创建笛卡儿积，应该使用inner join② join优点：如上面语句，使用inner join 这样数据库就只产生等于ID 的条目标结果。增加了查询效率。有些数据库系统会识别出 WHERE连接并自动转换为 INNER JOIN。
数据库基础——7.多表查询
m0_52096593的博客
05-28 4511
连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件。
什么是回表查询
热门推荐
我真的睡不够
09-11 2万+
什么是回表查询？ 这先要从InnoDB的索引实现说起，InnoDB有两大类索引： 聚集索引(clustered index) 普通索引(secondary index) InnoDB聚集索引和普通索引有什么差异？ InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引： （1）如果表定义了PK，则PK就是聚集索引； （2）如果表没有定义PK，则第一个not NULL unique列是聚集索引； （3）否则，InnoDB会创建一个隐..
MySQL联合查询（多表查询）
sfwqwfew的博客
02-15 708
多表联查
数据库19:带 WHERE 子句的多表查询(头歌云课)
weixin_60530224的博客
01-07 1507
注意到，class 表中的 class_id = 308 的内容行，并没有出现在我们的关联后的新表中。我们可以理解为，当 student 表与 class 表中的 class_id 字段内容相同时，我们可以将两张表中 class_id 字段对应的内容行拼接成一张表。我们推荐把不同种类的信息放在不同的数据表中，但是有关系的两张表，可以被同一种数据所关联。有两种选择，一种是把厂家的信息和产品信息存储在一张数据表里，另一种是建立新的数据表来存储厂家的信息。在你使用连接查询之前，一定要搞清楚数据表之间的关系。
mysql三表where查询_mysql三表查询sql语句
weixin_32287433的博客
01-19 670
表结构：Student学生表(学号、姓名、性别、年龄、编辑)Course课程表(编号、课程名称)sc选课表(选课编号、学号、课程编号、成绩)(1)写一个SQL语句，查询选修了“计算机原理”的学生学号和姓名(2)写一个SQL语句，查询“小明”同学选修的课程名称(3)写一个SQL语句，查询选修了5门课程的学生学号和姓名答案：(1)select student.stu_no,student.stu_na...
MySQL多表连接查询
weixin_63946209的博客
05-11 1419
多表连接查询实际上是通过各个表之间的共同列的关联性来查询数据的，它是关系数据库查询最重要的特征。#一、多表连接查询（1）内连接查询内连接查询是最典型、最常用的连接查询，它是根据表中的共同列来进行匹配。特别是两个表中存在主外键关系时通常会使用内连接查询。（2）外连接查询外连接查询至少返回一个表中的所有记录，根据匹配条件有选择性的返回另一张表的记录。外连接可以是在左外连接、右外连接。内连接和外连接都是用来联合两个表的查询结果，但是它们之间的区别在于内连接只返回满足条件的行，而外连接则会返回所有的行。
mysql表连接，多表查询
diamond219的博客
10-18 1173
MySQL数据库表连接 两张表通过where连接 格式： select 表名1.列名1，表名1.列名2，表名2.列名1，表名2.列名2 from 表名1，表名2 where 表名1.列名1=表名2.列名1 【是两个表之间建立关系】 *表1的列名和表2的列的列名可以不一致，单数数据类型和内容得保持一致。 内连接查询：只关联与表中能够匹配的数据。比如表1的学号从1-10，表2的学号从1-9，那么最终只能查询到1-9的信息。 * 示例： – 查询学生的学号、姓名、性别以及所在的班级名称和年级 select
MySQL-多表查询
qq_56930747的博客
04-03 1162
如果要查询多个表中的数据，就需要使用多表查询。多表查询有多种方法，隐式查询、链接查询（包括外连接查询和内连接查询）、交叉查询。 例如： result表 student表 在如上两张表中，如果要查询 result 表中 studentResult 字段值大于80的学生姓名 studentName ，此时就要使用多表查询。 SELECT student.studentName,result.studentResult FROM student,result WHERE stud...
MySQL的where条件不起作用？？来看看是不是这个问题
Royi的学习博客
12-22 551
解决了MySQL中使用了where判断条件但是查询结果发现不正确的情况
MySQL数据库——MySQL WHERE：条件查询数据
Itmastergo的博客
04-25 2066
在 MySQL 中，如果需要有条件的从数据表中查询数据，可以使用WHERE关键字来指定查询条件。
mysql中多个where,MySQL中如何用WHERE子句联结多个表
weixin_34681072的博客
03-17 1834
WHERE子句的重要性利用 WHERE 子句建立联结关系似乎有点奇怪，但实际上，有一个很充分的理由。请记住，在一条 SELECT 语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西。你必须自己做这件事情。在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。 WHERE 子句作为过滤条件，它只包含那些匹配给定条件(这...
Linux:MYSQL（四）多表操作
杜达达的博客
02-23 308
多表查询 union 纵向合并 两张表合并，需要对应关系,union有去重功能 select stuid,name from students union select tid,name from teachers; 多表查询 横向合并 一、cross join 交叉连接 笛卡尔乘积 横向乘积，横向合并 缺点：无效项太多 select s.name as student_name,s.ag...
MYSQL-在图书表和学生表基础上进行子查询
06-07
假设有两个表，一个是...4. 查询学生表中不在专业为“计算机科学与技术”和“软件工程”的学生的姓名和专业 ```mysql SELECT name, major FROM students WHERE major NOT IN ('计算机科学与技术', '软件工程'); ```

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值