MySQL多表查询2

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL多表查询2

非等值连接


image.png


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;


image.png


分类2:自连接 vs 非自连接


image.png


  • 当table1和table2本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接,外连接等查询。


题目:查询employees表,返回“Xxx  works for Xxx”


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


image.png


练习:查询出last_name为 ‘Chen’ 的员工的 manager 的信息。


分类3:内连接 vs 外连接


除了查询满足条件的记录以外,外连接还可以查询某一方不满足条件的记录。


image.png


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


  • 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。


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


SQL92:使用(+)创建连接


  • 在 SQL92 中采用(+)代表从表所在的位置。即左或右外连接中,(+) 表示哪个是从表。


  • Oracle 对 SQL92 支持较好,而 MySQL 则不支持 SQL92 的外连接。
#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;


  • 而且在 SQL92 中,只有左外连接和右外连接,没有满(或全)外连接。


三、 SQL99语法实现多表查询


3.1 基本语法


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


SELECT table1.column, table2.column,table3.column
FROM table1
    JOIN table2 ON table1 和 table2 的连接条件
        JOIN table3 ON table2 和 table3 的连接条件


  • 它的嵌套逻辑类似我们使用的 FOR 循环:


for t1 in table1:
    for t2 in table2:
       if condition1:
           for t3 in table3:
              if condition2:
                  output t1 + t2 + t3


  • SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强,即使再多的表进行连接也都清晰可见。如果你采用 SQL92,可读性就会大打折扣。


  • 语法说明:


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


3.2 内连接(INNER JOIN)的实现


  • 语法:


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


题目1:


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


image.png


题目2:


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


image.png


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


image.png


  • 语法:


#实现查询结果是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) ;


image.png


需要注意的是,LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中,在 SQL92 中不存在,只能用 (+) 表示。


3.3.3 满外连接(FULL OUTER JOIN)


  • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。


  • SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。


  • 需要注意的是,MySQL不支持FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT join代替。


四、 UNION的使用


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


语法格式:


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


UNION操作符


image.png


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


UNION ALL操作符


image.png


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


注意:执行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';



相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6月前
|
SQL 存储 关系型数据库
轻松入门MySQL:数据库关联与多表查询,构建高效的业务决策引擎(6)
轻松入门MySQL:数据库关联与多表查询,构建高效的业务决策引擎(6)
162 0
|
6月前
|
关系型数据库 MySQL
3. Mysql 如何实现多表查询
MySQL多表查询主要包括内连接和外连接。内连接有隐式和显式:隐式是通过`From 表A, 表B where 连接条件`,显式是`From 表A inner join 表B on 连接条件`。外连接包括左外连接(`left join`)、右外连接(`right join`)和全外连接(较少使用)。此外,还有交叉连接(`cross join`),但也较少使用。
66 0
|
5月前
|
关系型数据库 MySQL 数据库
关系型数据库MySQL开发要点之多表查询2024详解
关系型数据库MySQL开发要点之多表查询2024详解
34 2
|
5月前
|
关系型数据库 MySQL 数据库
MySQL数据库开发之多表查询数据准备及案例实操
MySQL数据库开发之多表查询数据准备及案例实操
47 1
|
5月前
|
关系型数据库 MySQL 数据库
MySQL数据库基础第四篇(多表查询与事务)
MySQL数据库基础第四篇(多表查询与事务)
|
5月前
|
SQL 关系型数据库 MySQL
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)一
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)一
45 5
|
5月前
|
关系型数据库 MySQL 数据库
MySQL数据库——多表查询(4)-实例练习、多表查询总结
MySQL数据库——多表查询(4)-实例练习、多表查询总结
141 1
|
5月前
|
SQL 关系型数据库 MySQL
MySQL数据库——多表查询(3)-自连接、联合查询、子查询
MySQL数据库——多表查询(3)-自连接、联合查询、子查询
237 1
|
5月前
|
关系型数据库 MySQL 数据库
MySQL数据库——多表查询(2)-内连接、外连接
MySQL数据库——多表查询(2)-内连接、外连接
40 1
|
6月前
|
SQL 关系型数据库 MySQL
【MySQL进阶之路 | 基础篇】MySQL之多表查询
【MySQL进阶之路 | 基础篇】MySQL之多表查询