MySQL - 多表查询

已于 2023-05-23 15:05:36 修改
阅读量1.5k 收藏 4
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 sql
于 2023-03-10 17:55:48 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44988085/article/details/129448438
版权

目录

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

  • 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。
    • SELECT tb_a.id, tb_b.id FROM tb_a,tb_b WHERE tb_a.id=tb_b.id;
  • 可以给表起别名，在SELECT和WHERE中使用表的别名
    • SELECT t1.id, t2.id FROM tb_a as t1,tb_b as t2 WHERE t1.id=t2.id;
  • 如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不是使用表的原名。
  • 如果有n个表实现多表的查询，则需要至少n-1个连接条件

1. 多表查询示例

新建表：

老街头的猫。

SELECT e.employee_id as '员工id',
       e.last_name as '员工名称',
       d.department_name as '员工所在部门',
       l.city as '部门所在城市'
FROM employees e, departments d, locations l
WHERE e.department_id=d.department_id
    AND d.location_id=l.location_id;

结果：

员工id员工名称员工所在部门部门所在城市
200WhalenAdministrationSeattle
201HartsteinMarketingToronto
202FayMarketingToronto
114RaphaelyPurchasingSeattle
115KhooPurchasingSeattle

2. 多表查询分类

2.1 等/非等值连接

2.1.1 等值连接

老街头的猫。 
SELECT employees.employee_id as '员工id',
       employees.last_name as '员工名称',
       employees.department_id as '部门id',
       departments.department_id as '部门id',
       departments.department_name as '部门名称',
       departments.location_id as '位置id'
FROM employees, departments
WHERE employees.department_id = departments.department_id;
员工id员工名称部门id部门id部门名称位置id
200Whalen1010Administration1700
201Hartstein2020Marketing1800
202Fay2020Marketing1800
114Raphaely3030Purchasing1700
…

2.1.2非等值连接

SELECT e.last_name as '员工名称',
       e.salary as '员工薪资',
       j.grade_level as '员工级别'
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;
员工名称员工工资员工级别
King24000E
Kochhar17000E
De Haan17000E
Hunold9000C
Ernst6000C
Austin4800B

2.2 自然/非自然连接

SELECT emp.employee_id as '员工id', 
       emp.last_name as '员工名称', 
       mgr.employee_id as '管理者id', 
       mgr.last_name as '管理者名称'
FROM employees emp, employees mgr
WHERE emp.manager_id = mgr.employee_id;
或者
SELECT CONCAT(worker.last_name ,' works for ' , manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
员工id员工名称管理者id管理者名称
101Kochhar100King
102De Haan100King
103Hunold102De Haan
104Ernst103Hunold
105Austin103Hunold
106Pataballa103Hunold

2.3 内/外连接

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

2.3.1 内连接

SELECT e.employee_id, d.department_id, l.city
FROM employees e
    INNER JOIN departments d
        ON e.department_id = d.department_id
    INNER JOIN locations l
        ON d.location_id = l.location_id;
        
-- 返回 employees_id和department_id都不为NULL的数据

2.3.2 外连接

-- 左外连接
SELECT e.employee_id, d.department_id
FROM employees e
    LEFT OUTER JOIN departments d
        ON e.department_id = d.department_id;
        
-- 右外连接

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

3.UNION的使用

3.1 合并查询结果

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

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

3.1.1 UNION操作符

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

3.1.2 UNION ALL操作符

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

老街头的猫。 
select 'newMember' as "memberType", 
    count(distinct full_union_id) as "memberCount" 
from XXX.XXX  
where XX_id = cast(66666666 as varchar) 
    and XX_time >= '2021-06-16 00:00:00' 
    and XX_time <= '2022-06-16 00:00:00' 

union all

select 'oldMember' as "memberType", 
    count(distinct full_union_id) as "memberCount" 
from XXX.XXX  
where XX_id = cast(66666666 as varchar) 
    and XX_time <'2021-06-16 00:00:00'
memberTypememberCount
oldMember2,666,888
newMember1,888,666

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

4. 7种JOIN操作

老街头的猫。

中图：内连接

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;

左中图

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;

右中图

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;

右下图：左中图 + 右中图

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

5. join 多张表

SELECT B.id, A.sid, C.c_type, C.c_name, D.sku_id
FROM db_name.table_name_a A
	RIGHT JOIN db_name.table_name_b B ON A.id = B.id
    RIGHT JOIN db_name.table_name_c C ON  B.channel_id  = C.channel_id
    RIGHT JOIN db_name.table_name_d D ON A.id = D.oid
WHERE A.code = 16
    AND A.order_status != 4
    AND A.create_time > '2023-03-08 21:00:00'
    AND A.tid = '1234567890'
ORDER BY A.create_time DESC 
LIMIT 200;

注意：

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

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、SQL 性能。

来源：阿里巴巴《Java开发手册》

老街头的猫。
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
mysql 多表联接查询练习
10-31
mysql,多表联接查询练习,mysql,多表联接内联,外联练习。 mysql,多表联接查询练习,mysql,多表联接内联,外联练习。
MySQL多表查询
Owen Guo
08-28 1万+
MySQL的多表查询
MySQL：多表查询（全面详解）
最新发布
时间如瑾 代码如诗
06-18 2185
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。
MySQL --- 多表查询 & 驱动表
weixin_53622554的博客
05-12 9266
指从多张表中查询数据，就是从多张表当中进行查询单表查询的SQL语句：select 字段列表 from 表名;那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;-- 多表查询:直接在from之后跟上多张表就可以了,多张表之间使用逗号分隔可以看到，在多表查询的结果当中，每个员工都展示了5次，将每个员工分别和五个部门进行了一次匹配， 也就是将17个员工和5个部门挨个组合了一次，就是17 * 5 = 85。
Mysql多表查询
oxh499780424的博客
09-05 4597
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。11.查找出部门 10 和部门 20 中，工资最高第 3 名到工资第 5 名的员工的员工名字，部门名字，部门位置。左外连接：先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。
Mysql-单表查询 教学.pdf
06-17
mysql -单表查询 教学.pdf
MySQL多表查询练习题原数据
08-18
练习题答案解决思路请查看本人原文章MySQL数据库多表查询练习题
mysql-connector-java-8.0.20.jar
08-13
`mysql-connector-java-8.0.20.jar`是MySQL Connector/J的特定版本，它实现了这些JDBC接口，使得Java开发者可以方便地在代码中执行SQL查询、操作数据以及管理数据库连接。 在MySQL 8.0版本中，引入了许多新特性、...
mysql-connector-java-8.0.15
05-17
这个驱动包使得开发者能够使用Java编程语言轻松地连接到MySQL服务器，执行SQL查询，处理结果集，以及执行其他数据库管理任务。MySQL Connector/J遵循JDBC（Java Database Connectivity）标准，它是Java平台上的...
mysql-server-mysql-8.3.0.tar.gz
03-28
5. **改进的查询优化器**：MySQL 8.3.0可能有更智能的查询优化器，能做出更好的执行计划选择，提高查询速度。 6. **分区表改进**：分区表是大型数据库中常见的优化手段，新的版本可能扩展了分区策略，提升了处理大...
MySQL多表查询 （超详细）
weixin_55772633的博客
08-05 6795
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）（开发中一般取消外键的设定，即在多的一方建立字段指向一的一方的主键即可）（开发中一般取消外键的设定，即在任意一方加入字段关联另外一方的主键即可）（开发中一般取消外键的设定，即在中间表建立两个字段指向两方的主键即可）关系：一个部门对应多个员工，一个员工对应一个部门。实现：在多的一方建立外键，指向一的一方的主键。
MySQL中的多表查询详解
qq_57492774的博客
07-09 5311
在实际的项目开发中，在进行数据表结构设计的时候，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间很多都是相关联的，所以各个表结构之间也存在着一定的关系。id INT PRIMARY KEY COMMENT '部门编号',name VARCHAR(20) UNIQUE COMMENT '部门名称'
MySQL之多表查询
小月月的博客
05-26 5882
一、介绍 二、多表关系 三、多表查询简述 四、多表查询操作 ：1.连接查询--内连接 2.连接查询--外连接 3.连接查询--自连接
MySQL多表查询语句
weixin_45513161的博客
03-23 697
MySQL数据库多表查询语句的学习。
MySQL的多表查询
weixin_55734444的博客
07-05 3912
MySQL的多表查询
【MySQL】多表查询
m0_51064412的博客
07-09 560
结果显示：发现MySQL从其中一张表中选出一条记录去和另外一张表的所有记录进行组合，重复这个过程直到一开始那张表的所有记录全部被组合完成，这样子不加筛选条件的直接组合在一起显示出来的结果都叫做笛卡尔积。另外，emp员工表中有一个字段叫做deptno表示该员工所在部门的部门号，这个字段是一个外键，其主表是dept部门表：// 1、查看dept部门表的数据 mysql > select * from dept;
【MySQL】多表查询策略（多表联查&子查询）
热门推荐
尹煜的博客
05-01 1万+
MySQL语句学习的难点和重点就在于多表查询，同时MySQL也有诸多方法供大家选择，不论是多表联查（联结表、左连接、右连接……）还是子查询（SELECT子查询、WHERE子查询、FROM子查询），掌握一种方法达到目的即可，当然其他方法也需要理解，本文将阐述完整的多表查询方法。 首先分享下学习链接，大家可以在看完本文后选择以下链接巩固学习成果，我相信如果都给掌握的话，MySQL语句中90%的问题都将迎刃而解！！ 视频学习链接：SQL学习之道_哔哩哔哩_bilibili
mysql数据库-单表查询一
05-23
对于 MySQL 数据库的单表查询，我们可以使用 SELECT 语句来实现。下面是一个示例： ``` SELECT * FROM table_name; ``` 其中，`table_name` 表示要查询的表名，`*` 表示查询所有列，也可以指定要查询的列名。例如： ``` SELECT column1, column2 FROM table_name; ``` 如果我们想要对查询结果进行排序，可以使用 ORDER BY 子句。例如： ``` SELECT * FROM table_name ORDER BY column_name ASC; -- 按升序排序 ``` 如果我们只想查询满足某些条件的行，可以使用 WHERE 子句。例如： ``` SELECT * FROM table_name WHERE column_name = 'value'; ``` 以上是 MySQL 数据库单表查询的一些基本语法和示例。更多详细的用法可以参考 MySQL 官方文档。
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值