MySQL之多表查询

代码代码快快显灵

于 2024-09-16 09:44:28 发布

阅读量1.2k

点赞数 27

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/gege_0606/article/details/142253936

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

多表关系：

一对多：

案例：部门与员工的关系

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

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

#一对多关系
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;