MySQL之多表查询

多表关系：

一对多：

案例：部门与员工的关系

关系:一个部门对应多个员工，一个员工对应一个部门

实现:在多的一方建立外键（员工），指向一的一方的主键（部门）

#一对多关系
create table dept1(id int primary key comment  'ID',name varchar(50) not null comment '部门名称'
) comment '部门';
INSERT INTo dept1(id, name) VALUES(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');create table emp2(id int primary key auto_increment comment '主键',name varchar(10) not null unique comment '姓名',age int check ( age >= 18 && age <= 80 ) comment '年龄',status char(1) default '1' comment '状态',gender char(1) comment '性别',dept_id int comment '部门ID'
)comment '员工表';insert into emp2(name, age,gender,dept_id) values
('Tom1',18,'男',3),('Tom4',20,'男',1),
('Tom2',28,'女',1),('Tom5',25,'男',5),
('Tom3',19,'男',4),('Tom6',40,'女',1),
('小李子',18,'男',1),('小智障',30,'女',2);alter table emp2 add constraint fk_emp2_dept_id foreign key (dept_id) references dept(id) on update cascade on delete  cascade;

多对多：

案例：学生和课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

# 多对多
create table curse(id int auto_increment primary key comment '主键',name varchar(10) comment '课程名称'
) comment '课程';
insert into curse values (null, 'Java'), (null, 'PHP'), (null, 'MySQL'), (null, 'Hadoop');create table student(id int auto_increment primary key comment 'ID',name varchar(10) comment '姓名',no varchar(10) comment '学号'
)comment '学生';INSERT INTO student (name, no) VALUES
('鳄绵丝', '2000100101'),
('谢逊', '2000100102'),
('殷天正', '2000100103'),
('韦一笑', '2000100104');#多对多在数据库层面的体现：通过中间表
create table student_course (id int auto_increment comment '主键' primary key,student_id int not null comment '学生ID',course_id int not null comment '课程ID',constraint fk_courseid foreign key (course_id) references curse (id),constraint fk_studentid foreign key (student_id) references student (id)
) comment '学生课程中间表';insert into student_course values (null, 1, 1), (null, 1, 2), (null, 1, 3), (null, 2, 2), (null, 2, 3), (null, 3, 4);

可视化图像：

一对一：

案例: 用户与用户详情的关系

关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现:在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

create table tb_user (id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',age int comment '年龄',gender char(1) comment '1: 男, 2: 女',phone char(11) comment '手机号'
) comment '用户基本信息表';create table tb_user_edu (id int auto_increment primary key comment '主键ID',degree varchar(20) comment '学历',major varchar(50) comment '专业',primaryschool varchar(50) comment '小学',middleschool varchar(50) comment '中学',university varchar(50) comment '大学',userid int unique comment '用户ID',constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';

多表查询的概念：

概述：

指从多张表中查询数据

笛卡尔积：

笛卡尔乘积是指在数学中，两个集合A集合和 B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)

如何避免笛卡尔积：

使用 WHERE 子句进行连接条件过滤

可以通过 WHERE 子句来过滤掉不相关的记录：

SELECT *
FROM emp2 e, dept d
WHERE e.dept_id = d.id;

多表查询的分类：

连接查询：

内连接:

相当于查询A、B交集部分数据

1. 隐式内连接

这种写法通过 WHERE 子句来指定连接条件，查询两张表中满足条件的记录：

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

SELECT e.name, d.name 
FROM emp2 e, dept d 
WHERE e.dept_id = d.id;

解释：这种写法在 FROM 中列出多个表，然后在 WHERE 子句中指定表之间的连接条件。它实现的效果与 INNER JOIN 是一样的，只不过连接条件隐含在 WHERE 中。

2. 显式内连接（推荐的写法）

使用 INNER JOIN 语句显式指定两张表的连接条件。这种方式语法更加清晰，也更易读。

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

SELECT e.name, d.name 
FROM emp2 e 
INNER JOIN dept d 
ON e.dept_id = d.id;

解释：通过 INNER JOIN 明确表示两张表之间的连接关系，ON 子句用于指定连接条件。这种写法比隐式连接更直观，尤其是在多表连接时，能更清楚地看到每一张表的连接方式。

外连接:

左外连接:

查询左表所有数据，以及两张表交集部分数据

左外连接查询返回左表中的所有记录，即使右表中没有匹配的记录，右表没有匹配的记录时返回 NULL。

语法：

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

解释：

表1 是左表，表2 是右表。
左外连接会返回左表中的所有记录，如果右表中有匹配的记录，则一起返回；如果右表中没有匹配的记录，右表对应的字段会返回 NULL。

例子：

select e.*,d.id from emp2 e left join  dept d on e.dept_id = d.id;

解释：这个查询会返回 emp2 表中所有员工的记录。如果某个员工没有对应的部门（dept 表中没有匹配的 dept_id），则部门信息为 NULL。

右外连接:

查询右表所有数据，以及两张表交集部分数据

右外连接与左外连接类似，只不过返回的是右表中的所有记录，如果左表中没有匹配的记录，则返回 NULL。

语法：

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

解释：

表1 是左表，表2 是右表。
右外连接会返回右表中的所有记录，如果左表中有匹配的记录，则一起返回；如果左表中没有匹配的记录，左表对应的字段会返回 NULL。

例子：

select d.*,e.name from emp2 e right join  dept d on e.dept_id = d.id;

解释：这个查询会返回 dept 表中所有部门的记录。如果某个部门没有对应的员工（emp2 表中没有匹配的 dept_id），则员工信息为 NULL。

自连接:

当前表与自身的连接查询，自连接必须使用表别名

1. 自连接的场景和应用

自连接通常用于以下场景：

查找某个表中具有父子关系的记录：例如，公司员工表中，每个员工有一个上级，通过自连接可以查找员工与其上级之间的关系。
比较表中的某些记录：比如查找工资比某个员工高的员工。
查找重复的记录：通过自连接可以找到表中重复的数据。

2. 自连接的语法

自连接其实与普通连接（JOIN）语法相同，只不过是表名相同。为了区分连接中的表，自连接时通常会为表设置别名。

语法格式：

SELECT a.字段1, b.字段2
FROM 表名 a
JOIN 表名 b
ON a.连接条件 = b.连接条件;

1. 自连接的内连接

内连接（INNER JOIN）是返回两个表（在自连接情况下，是同一个表）中匹配条件的行。自连接的内连接会返回满足条件的记录，如果没有匹配的记录，不会返回。

例子：查找员工和他们的上级

假设有一个 employees 表，其中 id 表示员工编号，manager_id 表示上级员工编号。通过内连接可以找到有上级的员工和他们上级的名字。

SELECT e.name AS Employee, m.name AS Manager
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.id;

解释：

这里 e 是员工，m 是他们的上级。通过 INNER JOIN 连接 employees 表的两个实例，只返回有上级的员工。
这个查询不会返回没有上级的员工，因为内连接只返回匹配的记录。

2. 自连接的左外连接

左外连接（LEFT JOIN）返回左表中的所有行，右表中匹配的行。如果右表中没有匹配的行，则返回 NULL。在自连接的情况下，左外连接可以用来找出那些没有匹配关系的记录（例如没有上级的员工）。

例子：查找所有员工和他们的上级（包括没有上级的员工）

SELECT e.name AS Employee, m.name AS Manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;

解释：

LEFT JOIN 返回 e 表中的所有员工。如果 m 表中没有匹配的上级，m.name 将会是 NULL。
这允许我们找到没有上级的员工，因为 LEFT JOIN 保证返回左表的所有记录，即使右表中没有匹配的行。

3. 自连接的右外连接

右外连接（RIGHT JOIN）返回右表中的所有行，左表中匹配的行。如果左表中没有匹配的行，则返回 NULL。自连接中的右外连接相对较少使用，但可以用于确保右表中的所有行都会出现在结果中，即使左表中没有匹配。

例子：查找所有上级和他们的员工（包括没有下属的上级）

SELECT e.name AS Employee, m.name AS Manager
FROM employees e
RIGHT JOIN employees m
ON e.manager_id = m.id;

解释：

RIGHT JOIN 会确保返回 m 表中的所有记录（即上级）。如果没有匹配的 e 表中的员工，则 e.name 会是 NULL。
这样可以找出那些没有下属的上级。

选择内连接还是外连接

内连接：如果你只想返回那些确实有匹配关系的记录（例如有上级的员工），那么使用内连接（INNER JOIN）。
左外连接：如果你想返回所有的左表记录，并包含右表中的匹配（如果有的话），使用左外连接（LEFT JOIN）。例如，返回所有员工，即使他们没有上级。
右外连接：如果你想确保返回右表中的所有记录，使用右外连接（RIGHT JOIN）。例如，返回所有的上级，即使他们没有下属。

联合查询：

UNION 查询的关键点：

UNION 用于合并多个 SELECT 查询的结果，并自动去除重复的记录。
如果你想保留所有的结果，包括重复的记录，可以使用 UNION ALL。

语法解释：

SELECT 字段列表 FROM 表A [where 条件]
UNION [ALL]
SELECT 字段列表 FROM 表B [where 条件];

SELECT 字段列表 FROM 表A：这是第一个查询，选择了 表A 中的某些字段。
UNION 或 UNION ALL：UNION 关键字用于合并查询结果。UNION 默认会去重，而 UNION ALL 不会去重，会保留所有重复的行。
SELECT 字段列表 FROM 表B：这是第二个查询，选择了 表B 中的某些字段。（表一和表二可以相同）

使用 `UNION` 的规则：

相同的列数：每个 SELECT 查询中的列数必须相同。
相同的数据类型：对应位置的列的数据类型必须兼容（例如，整数和小数，字符类型等）。
列名称：合并后的结果集会使用第一个查询中的列名称。

子查询：

1. 子查询的定义：

子查询（Subquery）是在一个 SQL 查询中嵌套的另一个查询，它可以出现在 SELECT、INSERT、UPDATE、DELETE 语句的内部，或者用于 WHERE、FROM、HAVING 等子句中。

2. 子查询的基本语法：

SELECT * 
FROM 表1
WHERE column1 = (SELECT column1 FROM 表2);

外查询：指最外层的查询，它依赖子查询的结果。
子查询：被嵌套在外查询中的查询，返回给外查询所需的值。

3. 子查询的类型：

根据子查询返回的结果形式，子查询可以分为以下几种：

常用操作符：

IN 在指定的集合范围内，返回 TRUE 或 FALSE。 WHERE column_name IN (子查询) 查询所有属于某些部门的员工。
SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

NOT IN 不在指定的集合范围内，返回 TRUE 或 FALSE。 WHERE column_name NOT IN (子查询) 查询不属于某些部门的员工。
SELECT name FROM employees WHERE department_id NOT IN (SELECT id FROM departments WHERE location = 'New York');

ANY 如果满足子查询返回的任意一个值，返回 TRUE。 WHERE column_name > ANY (子查询) 查询工资大于某部门中任意一个员工工资的员工。
SELECT name FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 2);

SOME 与 ANY 功能相同，检查是否满足子查询返回结果中的任意一个值。 WHERE column_name > SOME (子查询) 查询工资大于某部门中任意一个员工工资的员工。
SELECT name FROM employees WHERE salary > SOME (SELECT salary FROM employees WHERE department_id = 2);

ALL 如果满足子查询返回的所有值，返回 TRUE。 WHERE column_name > ALL (子查询) 查询工资大于某部门中所有员工工资的员工。
SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 2);

1. 标量子查询（Scalar Subquery）

子查询的结果为单个值。
常用于 WHERE 子句，用来比较单个值。
```
select MAX(salary) from employees;SELECT name 
FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees);
```
解释：这个查询查找工资等于所有员工最高工资的员工姓名。子查询返回单个值（最高工资）。

2. 列子查询（Column Subquery）

子查询的结果为一列，用于与外查询的某个列进行匹配或比较。
通常用于 IN 或者 ANY 子句。

/*列子查询
--1.查询“销售部”和“市场部”的所有员工信息
--a.查询“销售部”和“市场部”的部门ID
*/select id from dept where name ='销售部'or name ='市场部';
# 根据部门ID，查询员工信息
select *from emp2 where dept_id in(2,4);
# 即select *from emp2 where dept_id in(select id from dept where name ='销售部'or name ='市场部');# 查询比 财务部 所有人工资都高的员工信息# a.查询所有 财务部 人员工资
select id from dept where name ='财务部';
select salary from emp2 where dept_id =(select id from dept where name ='财务部');# b:比财务部 所有人工资都高的员工信息
select * from emp2 where salary > all ( select salary from emp2 where dept_id = (select id from dept where name = '财务部'));

3. 行子查询（Row Subquery）

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询

子查询的结果为一行，用于与外查询的一行进行比较。
一般使用 = 来比较外查询和子查询的结果。

# 行子查询
# 1.查询与“张无忌”的薪资及直属领导相同的员工信息
# a.查询“张无忌”的薪资及直属领导select salary,managerid from emp2 where name ='张无忌';# b.查询与"张无忌”的薪资及直属领导相同的员工信息select *from emp where(salary,managerid) = (12500,1);
# 即select *from emp where(salary,managerid) = (select salary,managerid from emp2 where name ='张无忌');

4. 表子查询（Table Subquery）

查询返回的结果是多行多列，这种子查询称为表子查询。

子查询的结果为多行多列，返回一个完整的结果集。
常用于 IN、EXISTS 子句或 FROM 子句中。

# 表子查询
# 查询与“鹿杖客"或者与“宋远桥”的职位和薪资相同的员工信息
# a.查询“鹿杖客”"宋远桥”的职位和薪资
select job,salary from emp where name ='鹿杖客'or name ='宋远桥';# b.查询与“鹿杖客""宋远桥”的职位和薪资相同的员工信息
select * from emp2 where (job,salary) in ( select job, salary from emp2 where name ='鹿杖客'or name = '宋远桥');#2.查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
# a.入职日期是“2006-01-01”之后的员工信息
select * from emp2 where entrydate > '2006-01-01';# b.查询这部分员工，对应的部门信息;
select e.*,d.* from (select * from emp2 where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

MySQL之多表查询

多表关系：

一对多：

多对多：

可视化图像：

一对一：

多表查询的概念：

概述：

笛卡尔积：

如何避免笛卡尔积：

多表查询的分类：

连接查询：

内连接:

外连接:

自连接:

联合查询：

UNION 查询的关键点：

使用 `UNION` 的规则：

子查询：

1. 子查询的定义：

2. 子查询的基本语法：

3. 子查询的类型：

相关资讯

热文排行

最新新闻

推荐新闻

热搜词

MySQL之多表查询

多表关系：

一对多：

多对多：

可视化图像：

一对一：

多表查询的概念：

概述：

笛卡尔积：

如何避免笛卡尔积：

多表查询的分类：

连接查询：

内连接:

外连接:

自连接:

联合查询：

UNION 查询的关键点：

使用 UNION 的规则：

子查询：

1. 子查询的定义：

2. 子查询的基本语法：

3. 子查询的类型：

相关资讯

热文排行

最新新闻

推荐新闻

热搜词

使用 `UNION` 的规则：