MySQL 多表查询

avatar
作者
筋斗云
阅读量:0

🌹作者主页:青花锁 🌹简介:Java领域优质创作者🏆、Java微服务架构公号作者😄

🌹简历模板、学习资料、面试题库、技术互助

🌹文末获取联系方式 📝

在这里插入图片描述


往期热门专栏回顾

专栏描述
Java项目实战介绍Java组件安装、使用;手写框架等
Aws服务器实战Aws Linux服务器上操作nginx、git、JDK、Vue
Java微服务实战Java 微服务实战,Spring Cloud Netflix套件、Spring Cloud Alibaba套件、Seata、gateway、shadingjdbc等实战操作
Java基础篇Java基础闲聊,已出HashMap、String、StringBuffer等源码分析,JVM分析,持续更新中
Springboot篇从创建Springboot项目,到加载数据库、静态资源、输出RestFul接口、跨越问题解决到统一返回、全局异常处理、Swagger文档
Spring MVC篇从创建Spring MVC项目,到加载数据库、静态资源、输出RestFul接口、跨越问题解决到统一返回
华为云服务器实战华为云Linux服务器上操作nginx、git、JDK、Vue等,以及使用宝塔运维操作添加Html网页、部署Springboot项目/Vue项目等
Java爬虫通过Java+Selenium+GoogleWebDriver 模拟真人网页操作爬取花瓣网图片、bing搜索图片等
Vue实战讲解Vue3的安装、环境配置,基本语法、循环语句、生命周期、路由设置、组件、axios交互、Element-ui的使用等
Spring讲解Spring(Bean)概念、IOC、AOP、集成jdbcTemplate/redis/事务等

MySQL专栏回顾

专栏导航描述
MySQL- -MySQL DDL通用语法
MySQL- -MySQL DML通用语法
MySQL- -MySQL 约束篇
MySQL- -MySQL 多表查询

前言

此为MySQL专栏文章之一,讲解MySQL多表查询。

多表查询,也称为关联查询,指两个或更多个表一起完成查询操作。
前提条件:这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段,这个关联字段可能建立了外键,也可能没有建立外键。


1. 多表关系

● 一对多(多对一)
● 多对多
● 一对一

1.1 一对多

案例:部门与员工
关系:一个部门对应多个员工,一个员工对应一个部门
实现:在多的一方建立外键,指向一的一方的主键

1.2 多对多

案例:学生与课程
关系:一个学生可以选多门课程,一门课程也可以供多个学生选修
实现:必须建立第三张中间表,中间表至少包含两个外键,分别关联两方主键

1.3 一对一

案例:用户与用户详情
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)


2. 查询

2.1 合并查询(笛卡尔积,会展示所有组合结果)

select * from employee, dept; 

笛卡尔积:两个集合A集合和B集合的所有组合情况(在多表查询时,需要消除无效的笛卡尔积)

我们要避免笛卡尔积查询的出现。
通过指定关联关系来消除无效笛卡尔积:

select * from employee, dept where employee.dept = dept.id; 

2.2 内连接查询

内连接:合并具有同一列的多个表的行, 结果集中不包含一个表与另一个表不匹配的行。内连接查询的是两张表交集的部分。

● 隐式内连接:

SELECT 字段列表 FROM 表1, 表2 WHERE 条件 …;

● 显式内连接:

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 …;

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

显式性能比隐式高:在 MySQL 中,可以使用 连接(JOIN)代替子查询。连接查询不需要建临时表,其速度比子查询快,如果查询中使用索引的话,性能会更好。

例子:

-- 查询员工姓名,及关联的部门的名称 -- 隐式 select e.name, d.name from employee as e, dept as d where e.dept = d.id; -- 显式 select e.name, d.name from employee as e [inner] join dept as d on e.dept = d.id; 

2.3 连接查询

● 外连接:
除了查询满足条件的记录以外,外连接还可以查询某一方不满足条件的记录。
两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
– 如果是左外连接,则连接条件中左边的表也称为 主表 ,右边的表称为 从表 。
– 如果是右外连接,则连接条件中右边的表也称为 主表 ,左边的表称为 从表 。

左外连接:
查询左表所有数据,以及两张表交集部分数据

SELECT 字段列表 FROM1 LEFT [ OUTER ] JOIN2 ON 条件 ...; 

相当于查询表1的所有数据,包含表1和表2交集部分数据

右外连接:
查询右表所有数据,以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 …;

例子:

-- 左 select e.*, d.name from employee as e left outer join dept as d on e.dept = d.id;  -- 这条语句与下面的语句效果一样 select d.name, e.* from dept d left outer join emp e on e.dept = d.id;   -- 右 select d.name, e.* from employee as e right outer join dept as d on e.dept = d.id; 

左连接可以查询到没有 dept 的 employee,右连接可以查询到没有 employee 的 dept

2.4 自连接查询

当 table1 和 table2 本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义。
语法:

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...; 

自连接查询,可以是内连接查询,也可以是外连接查询
例子:

-- 查询员工及其所属领导的名字 select a.name, b.name from employee a, employee b where a.manager = b.id; -- 没有领导的也查询出来 select a.name, b.name from employee a left join employee b on a.manager = b.id; 

2.5 联合查询 union, union all

合并查询结果 利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并时,两个表 对应的列数和数据类型必须相同,并且相互对应。各个 SELECT 语句之间使用 UNION 或UNION ALL 关键字分隔。
把多次查询的结果合并,形成一个新的查询集。
区别:
● UNION 操作符返回两个查询的结果集的并集,去除重复记录。
● UNION ALL 操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。
语法:

SELECT 字段列表 FROM 表A ... UNION [ALL] SELECT 字段列表 FROM 表B ... 

注意事项
● UNION ALL 会有重复结果,UNION 不会
● 联合查询比使用or效率高,不会使索引失效

2.6 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询,SQL 中子查询的使用大大增强了 SELECT 查询的能力,因为很多时候查询需要从结果集中获取数据,或者需要从同一个表中先计算得出一个数据结果,然后与这个数据结果(可能是某个标量,也可能是某个集合)进行比较。
子查询会在主查询之前一次执行完成,也就是说包含子查询的语句真正的执行次数至少是2次。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2); 

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果可以分为:

● 单行子查询(子查询结果为单个值)
● 列子查询(子查询结果为一列)
● 行子查询(子查询结果为一行)
● 表子查询(子查询结果为多行多列)

根据子查询位置可分为:

● WHERE 之后
● FROM 之后
● SELECT 之后

2.6.1 单行子查询

单行子查询返回的结果是单个值(数字、字符串、日期等)。
常用操作符:- < > > >= < <=

例子:

# 题目:查询工资大于 2 号员工工资的员工的信息 SELECT * FROM emp  WHERE salary > (SELECT salary FROM emp WHERE eid = 2); 

2.6.2 列子查询

返回的结果是一列(可以是多行)。

常用操作符:

例子:

-- 查询销售部和市场部的所有员工信息 select * from employee where dept in (select id from dept where name = '销售部' or name = '市场部'); -- 查询比财务部所有人工资都高的员工信息 select * from employee where salary > all(select salary from employee where dept = (select id from dept where name = '财务部')); -- 查询比研发部任意一人工资高的员工信息 select * from employee where salary > any (select salary from employee where dept = (select id from dept where name = '研发部')); 

2.6.3 行子查询

返回的结果是一行(可以是多列)。
常用操作符:=, <, >, IN, NOT IN

例子:

-- 查询与xxx的薪资及直属领导相同的员工信息 select * from employee where (salary, manager) = (12500, 1); select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx'); 

2.6.4 表子查询

返回的结果是多行多列
常用操作符:IN

例子:

-- 查询与xxx1,xxx2的职位和薪资相同的员工 select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2'); -- 查询入职日期是2006-01-01之后的员工,及其部门信息 select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id; 

资料获取,更多粉丝福利,关注下方公众号获取

在这里插入图片描述

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!