MySQL多表查询

已于 2024-11-29 16:02:52 修改
阅读量1.2k 收藏 27
点赞数 11
CC 4.0 BY-SA版权
分类专栏： 数据库基础学习 文章标签： mysql 数据库 sql
于 2024-11-29 14:10:08 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2403_87725694/article/details/144133838

目录

引入（笛卡尔积的错误/交叉连接）

错误

 正确

1.等值连接/非等值连接·

等值连接（即查询的数目相同）

区分重复的列名

 如：

表的别名

如

 注意

总结：

 非等值连接（即查询数目不相同）

2.自连接 /非自连接（即是不是自己链接自己）

3.内连接  /外连接

内连接

外连接

外连接的分类

3. 基本语法

3.1使用JOIN...ON子句创建连接的语法结构

语法说明

3.2 内连接(INNER JOIN)的实现

语法

如：

3.3 外连接(OUTER JOIN)的实现 

3.3.1 左外连接(LEFT OUTER JOIN)

语法

3.3.2 右外连接(RIGHT OUTER JOIN)

语法

3.3.3 满外连接(FULL OUTER JOIN)

4. UNION的使用

 语法格式：

UNION操作符

UNION ALL操作符

注意

 总结

5. 7种SQL JOINS的实现

5.1语法格式小结

左中图

右中图

左下图

右下图

5.2代码演示

6. SQL99语法新特性

6.1 自然连接

6.2 USING连接

自然连接（NATURAL JOIN）与USING连接对比

7.总结

注意

引入（笛卡尔积的错误/交叉连接）

错误

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

 多表查询中出现的问题称笛卡尔积的错误

        笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN 表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关
笛卡尔积的错误会在下面条件下产生 ：
  1. 省略多个表的连接条件（或关联条件）
  2. 连接条件（或关联条件）无效
  3. 所有表中的所有行互相连接
为了避免笛卡尔积， 可以 在 WHERE 加入有效的连接条件。

 正确

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

1.等值连接/非等值连接·

如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表

SELECT ...,....,....
FROM ....
WHERE .... AND / OR / NOT....
ORDER BY .... (ASC/DESC),....,...
LIMIT ...,...

等值连接（即查询的数目相同）

SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

区分重复的列名

多个表中有相同列时，必须在列名之前加上表名前缀。
在不同表中具有相同列名的列可以用 表名 加以区分。

 如：

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

表的别名

  1. 使用别名可以简化查询。
  2. 列名前使用表名前缀可以提高查询效率
  3. 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。

如

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个连接条件。比如，连接三个表，至少需要两个连接条件

 非等值连接（即查询数目不相同）

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`;

2.自连接 /非自连接（即是不是自己链接自己）

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`;

3.内连接  /外连接

内连接

        合并具有同一列的两个以上的表的行, 结果集表中不包含一个表与另一个表不匹配的行(只包含他们相同的表内容)

外连接

合并具有同一列的两个以上的表的行, 结果集表中除了包含一个表与另一个表匹配的行之外，还查询到了左表 或 右表中不匹配的行。(查到他们不相同的表内容)

外连接的分类

左外连接、右外连接、满外连接

         左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。


        右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。

  1. 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
  2. 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
    1. 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
    2. 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

3. 基本语法

3.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 的含义是一样的，都表示内连接

3.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);

3.3 外连接(OUTER JOIN)的实现 

3.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) ;

3.3.2 右外连接(RIGHT OUTER JOIN)

语法
#实现查询结果是B
SELECT 字段列表
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) ;

3.3.3 满外连接(FULL OUTER JOIN)

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

4. UNION的使用

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

 语法格式：

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

UNION操作符

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

UNION ALL操作符

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

注意

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

 总结

 UNION：会执行去重操作
 UNION ALL:不会执行去重操作
        1.UNION(只有一份相交（即相同的数据）的数据)
         2.UNION ALL (多了一份重复的数据)


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

5. 7种SQL JOINS的实现

5.1语法格式小结

左中图

#实现A - A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

右中图

#实现B - A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

左下图

#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;

右下图

#实现A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B）
#使用左外的 (A - A∩B) union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

5.2代码演示

# 中图：内连接
select e.employee_id, d.department_name
from departments d
         join employees e
              on e.`department_id` = d.`department_id`;

# 左上图：左外连接(left)
SELECT employee_id, department_name
FROM employees e
         LEFT JOIN departments d
                   ON e.`department_id` = d.`department_id`;

# 右上图：右外连接(right)
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 右中图
# UNION:相当于并集
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;

6. SQL99语法新特性

6.1 自然连接

NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接 。

NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`，然后进行`等值连接`。

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;

6.2 USING连接

使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN 一起使用。 
#11. SQL99语法的新特性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);


#拓展：建议使用两个 join... on(超过3个表不建议使用join...on)
SELECT last_name, job_title, department_name
FROM employees
         INNER JOIN departments
         INNER JOIN jobs
                    ON employees.department_id = departments.department_id
                        AND employees.job_id = jobs.job_id;

自然连接（NATURAL JOIN）与USING连接对比

USING 指定了具体的相同的字段名称，你需要在 USING的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接

7.总结

表连接的约束条件可以有三种方式 WHERE, ON, USING
  1. WHERE：适用于所有关联查询
  2. ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
  3. USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

注意

        我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

MySQL 如何同时查询多个相同分表的数据
省赚客开发者博客
08-14 832
在大型系统中，随着数据量的增加，单表存储数据会变得非常慢，查询效率也会下降。本文将详细介绍在MySQL中如何同时查询多个相同分表的数据，并且会通过一些具体的例子来演示。通过本文的介绍，我们了解了在MySQL中如何同时查询多个相同分表的数据，并且演示了几种常用的方法。在上述 Java 代码中，我们动态构建了查询语句，将所有分表的查询结果合并，然后按。如果分表的结构固定，可以创建一个视图，将多个分表的数据合并在一起。，因为订单量非常大，所以我们将其按月份进行了分表，形成了多个表，如。将多个查询结果合并在一起。
MySQL基础-多表查询
~
03-22 515
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接，没有匹配的行时, 结果表中相应的列为空(NULL)如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。说明：上述多表查询中出现的问题称为：笛卡尔积的错误，结果是将每个员工分配了所有的部门所产生的。满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。同时使用 JOIN…
MySQL多表查询从入门到精通
最新发布
m0_75107803的博客
06-06 916
usersordersproducts：遵循数据库规范化设计原则（第三范式3NF）：通过主键(PK)-外键(FK)关系连接数据user_iduser_idcourse_iddept_id。
mysql 多表查询语句
qq_65924224的博客
07-08 4664
多表查询语句
MySQL中的多表查询(超级详细)
haust_允谦的博客
01-30 4359
MySQL中的多表查询 多表查询的分类 等值连接—— 非等值连接 自连接 —— 非自连接 内连接 —— 外连接 # 等值连接 vs 非等值连接 # 非等值连接的例子 SELECT* FROM job_grades; SELECT last_name, salary,grade_level FROM employees e, job_grades j WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` # 自连接 vs 非
【MySQL】一学就会：多表查询
小鳴人的博客
04-11 1313
【包含：SQL99语法实现多表查询，内连接，外连接及其分类，等值连接vs非等值连接，自连接vs非自连接，内连接vs外连接，union的使用，SQL99语法新特性，笛卡尔积，7种SQLJOINS的实现】
MySQL多表查询练习题原数据
08-18
练习题答案解决思路请查看本人原文章MySQL数据库多表查询练习题
MySQL多表查询大全（超精确）
qq_53518630的博客
05-02 7479
目录 1、等值查询 2、内连接 3、外连接 3.1、左外连接 3.2、右外连接 3.3、完全外连接 4、交叉连接 5、自连接 6、联合查询 如果我们要查询的数据分布在不同的表时，那么需要连接多张表进行多表查询。 --而这些表当中，必须存在主外键关系（直接关联） --如果不存在直接关系，则必定存在间接关系，否则业务上是没有意义的 1、等值查询 select 字段集合 from 表1,表2,...,表n where 条件 (连接条件，一般使用主外键) #注意： 条件..
Mysql 多表查询详解
热门推荐
Jintao_Ma的博客
04-27 12万+
纸上得来终觉浅 1.前言：上篇讲到Mysql中关键字执行的顺序，只涉及了一张表；实际应用大部分情况下，查询语句都会涉及到多张表格 : 1)多表连接有哪些分类； 2)针对这些分类有哪些连接方法？ 3)这些连接方法分别作用于哪些应用场景？ 这篇针对这三个点通过实例来讲述，目的是穷尽所有的场景和所有的方法，并且对每个方法的使用做实例。 2.假设有两张表格A和B，把表格当作一个集合，那么表格中
mysql多表查询，强制使用特定索引
gaoshan12345678910的博客
05-16 1464
mysql多表查询，强制使用特定索引
mysql多数据库查询语句_mysql数据库常用的查询语句(多表查询)
weixin_35698190的博客
01-21 451
例如：原表countrymysql> select * from country;+----+----------+------------+------+----------+| id | name | population | area | language |+----+----------+------------+------+----------+|1 | mySQL...
MySQL多表查询语句
孙正_大正
05-30 9265
MySQL多表查询语句 1.内连接查询 1.隐式内连接：使用where条件消除无用数据 例子： --查询所有员工信息和对应的部门信息 SELECT * FROM emp,dept WHERE emp.dept_id=dept.id; --查询员工表的姓名，性别，部门表的名称 SELECT t1.name, -- 员工表姓名 t1.gender, -- 员工表性别 t2.name -- 部门表名称 FROM emp t1, dept t2
MySQL基础之多表查询
一切总会归于平淡
04-15 1743
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
MySQL中的多表查询语句
qq_58626489的博客
01-29 1884
在了解多表查询之前，我们先看一下什么是单表查询。那什么是多表查询呢？selectfromA,B这是初学者比较容易理解的多表查询，就是直接查询两张表的字段，当然，星号可以改为对应的字段名。但是这样直接查询两张表，展示的结果会出现笛卡尔积。笛卡尔积：有A，B两个集合，取A，B所有的组合情况。比如A表有6条数据，B表有4条数据，那么查询出来就是24条数据。
MYSQL语句和多表查询
weixin_30814329的博客
07-25 482
数据库 什么是数据库 存储数据的仓库,本质上是一套CS结构的软件程序,分为客户端和服务器, 我们通常说安装数据,装的其实是服务器 库的语句 create database drop database alter database show databases show create database 表的语句 create tabl...
Mysql多表查询语句整理
定格空间
09-25 461
在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型： INNER JOIN：取交集，如果表中有至少一个匹配，则返回行 LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行（在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN） RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行 FULL JOIN：只要其中一个表中存在匹配，则返回行...
MySql多表查询
qq_61916210的博客
06-13 209
MySql 多表查询
mysql:多表查询
gaokuiyuan的博客
02-24 305
mysql多表查询，包括连接查询和子查询，并且有两个练习案例
MySQL多表查询详解：实例演示与解决笛卡尔积现象
MySQL多表查询详解上深入讲解了在MySQL数据库中进行多表查询的各种情况。在实际的数据处理和分析中，多表查询...无论是初学者还是经验丰富的开发者，通过本文提供的实例和理论，都能在实践中提升自己的MySQL查询能力。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值