MySQL - 多表查询
创始人
2024-06-01 00:20:29
0

目录

  • 1. 多表查询示例
  • 2. 多表查询分类
    • 2.1 等/非等值连接
      • 2.1.1 等值连接
      • 2.1.2非等值连接
    • 2.2 自然/非自然连接
    • 2.3 内/外连接
      • 2.3.1 内连接
      • 2.3.2 外连接
  • 3.UNION的使用
    • 3.1 合并查询结果
      • 3.1.1 UNION操作符
      • 3.1.2 UNION ALL操作符
  • 4. 7种JOIN操作
  • 5. join 多张表

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

  • 如果查询语句中出现了多个表中都存在的字段,则必须指明此字段所在的表。
    • 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_idAND 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 eINNER JOIN departments dON e.department_id = d.department_idINNER JOIN locations lON d.location_id = l.location_id;-- 返回 employees_id和department_id都不为NULL的数据

2.3.2 外连接

-- 左外连接
SELECT e.employee_id, d.department_id
FROM employees eLEFT OUTER JOIN departments dON e.department_id = d.department_id;-- 右外连接SELECT e.employee_id, d.department_id
FROM employees eRIGHT OUTER JOIN departments dON 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 allselect '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 eJOIN departments dON e.department_id = d.department_id;

左上图:左外连接

SELECT employee_id, last_name, department_name
FROM employees eLEFT JOIN departments dON e.department_id = d.department_id;

右上图:右外连接

SELECT employee_id,last_name,department_name
FROM employees eRIGHT JOIN departments dON e.department_id = d.department_id;

左中图

SELECT employee_id, last_name, department_name
FROM employees eLEFT JOIN departments dON e.department_id = d.department_id
WHERE d.department_id IS NULL;

右中图

SELECT employee_id, last_name, department_name
FROM employees eRIGHT JOIN departments dON e.department_id = d.department_id
WHERE e.department_id IS NULL;

左下图: 左中图 + 右上图

SELECT employee_id,last_name,department_name
FROM employees eLEFT JOIN departments dON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL #没有去重操作,效率高
SELECT employee_id,last_name,department_name
FROM employees eRIGHT JOIN departments dON e.department_id = d.department_id;

右下图:左中图 + 右中图

SELECT employee_id,last_name,department_name
FROM employees eLEFT JOIN departments dON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees eRIGHT JOIN departments dON 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 ARIGHT JOIN db_name.table_name_b B ON A.id = B.idRIGHT JOIN db_name.table_name_c C ON  B.channel_id  = C.channel_idRIGHT JOIN db_name.table_name_d D ON A.id = D.oid
WHERE A.code = 16AND A.order_status != 4AND 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开发手册》

相关内容

热门资讯

开源电脑安卓系统排行,探索自由... 亲爱的电脑爱好者们,你是否曾想过,在电脑的世界里,也能体验到安卓系统的便捷与乐趣?没错,这就是今天我...
如何清空相册安卓系统,轻松恢复... 手机里的相册是不是越来越满,看着那些堆积如山的照片,是不是有点头疼呢?别急,今天就来教你怎么在安卓系...
安卓系统要停止更新,拥抱新变革 你知道吗?最近有个大消息在安卓圈里炸开了锅!安卓系统,这个陪伴我们多年的老朋友,竟然要停止更新了!这...
安卓系统怎样强行关机,安卓系统... 手机突然卡壳了,是不是又想强行关机了?别急,今天就来教你安卓系统怎样强行关机,让你轻松应对各种突发状...
安卓系统如何删除桌面,轻松删除... 手机桌面乱糟糟的,是不是感觉像你的房间一样,东西堆得有点多?别急,今天就来教你怎么给安卓系统的桌面来...
安卓系统怎么发英语,Andro... 你有没有想过,在安卓系统上发送英语信息竟然也能变得如此简单有趣?没错,就是那种轻松自如,仿佛英语是你...
最早期的安卓系统,揭秘最早期安... 亲爱的读者,你是否曾好奇过,那个陪伴我们手机成长的安卓系统,它的起源究竟是怎样的呢?今天,就让我们一...
安卓双系统添加应用,轻松实现多... 你有没有想过,你的安卓手机里可以同时运行两个系统呢?听起来是不是很酷?想象一边是熟悉的安卓系统,一边...
pipo安卓进系统慢,探究pi... 最近是不是发现你的Pipo安卓系统更新或者运行起来特别慢?别急,今天就来给你好好分析分析这个问题,让...
怎样使用安卓手机系统,安卓手机... 你有没有发现,安卓手机已经成为我们生活中不可或缺的一部分呢?从早晨闹钟响起,到晚上睡前刷剧,安卓手机...
双系统安卓安装caj,轻松实现... 你有没有想过,你的安卓手机里装上双系统,是不是就能同时享受安卓和Windows系统的乐趣呢?没错,这...
安卓使用ios系统教程,安卓用... 你是不是也和我一样,对安卓手机上的iOS系统充满了好奇?想要体验一下苹果的优雅和流畅?别急,今天我就...
安卓系统gps快速定位,畅享便... 你有没有遇到过这样的情况:手机里装了各种地图导航软件,但每次出门前都要等上好几分钟才能定位成功,急得...
安卓手机系统更新原理,原理与流... 你有没有发现,你的安卓手机最近是不是总在提醒你更新系统呢?别急,别急,让我来给你揭秘一下安卓手机系统...
安卓系统通知管理,全面解析与优... 你有没有发现,手机里的通知就像是一群调皮的小精灵,时不时地跳出来和你互动?没错,说的就是安卓系统的通...
安卓系统手机哪买,揭秘哪里购买... 你有没有想过,拥有一部安卓系统手机是多么酷的事情呢?想象你可以自由安装各种应用,不受限制地探索各种功...
安卓系统 ipv4,基于安卓系... 你知道吗?在智能手机的世界里,有一个系统可是无人不知、无人不晓,那就是安卓系统。而在这个庞大的安卓家...
目前安卓是什么系统,探索安卓系... 亲爱的读者,你是否曾好奇过,如今安卓系统究竟是什么模样?在这个科技飞速发展的时代,操作系统如同人体的...
安卓6.0系统比5.0,从5.... 你有没有发现,自从手机更新了安卓6.0系统,感觉整个人都清爽了不少呢?没错,今天咱们就来聊聊这个话题...
安卓2.36系统升级,功能革新... 你知道吗?最近安卓系统又来了一次大变身,那就是安卓2.36系统升级!这可不是一个小打小闹的更新,而是...