MySQL之多表查询和子查询

标签：查询 dept emp 和子之多表 where id select

一、多表查询

1、概述

MySQL 中的多表查询允许用户在单个查询语句中联合两个或多个表，依据表之间的关联关系来提取所需数据，这种技术在处理复杂数据关系时尤为常见和重要。

简单讲就是多表查询就是从两个或多个表中检索数据，当需要结合不同表中的信息时，通常会使用 JOIN 子句来实现表的连接，从而完成数据的整合。

2、表与表的关系

在数据库设计中，表与表之间的关系可以分为三种基本类型：

一对一关系（One-to-One）：表示两个表中的记录是唯一匹配的。例如，“用户”表和“身份证信息”表之间的一对一关系意味着每个用户都对应着一条唯一的身份证信息记录。

一对多关系（One-to-Many）：指的是一个表中的每条记录可以与另一个表中的多条记录相联系。比如，“班级”表和“学生”表之间的关系，一个班级可以有多个学生，但每个学生只属于一个班级。

多对多关系（Many-to-Many）：在这种情况下，两个表中的记录都可以与对方的多条记录关联。例如，“学生”表和“课程”表之间的关系，一个学生可以选修多门课程，同时一门课程也可以被多个学生选修。为了有效地处理多对多关系，通常需要创建一个关联表或者叫做桥梁表来存储这两个表之间的关系。

3、多表查询

3.1、准备数据

-- 创建dept表

create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,'销售部'), (5, '总经办'), (6, '人事部');

-- 创建emp表，并插入数据
create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
)comment '员工表';

-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

3.2、笛卡尔积

select * from emp,dept;

查询结果中出现的大量结果集（共102条记录）实际上是员工表 emp （共有17条记录）与部门表 dept （共有6条记录）之间的一种现象，称为笛卡尔积。

笛卡尔积发生在一个表中的每一行与另一个表中的每一行都进行了配对，生成的结果集是两个表行数的乘积，即 17 * 6 = 102。这种结果通常表明查询中没有正确地定义表之间的连接条件或者说 JOIN 条件缺失，导致了不想要的全部组合输出。为了避免这种情况，应该在查询中明确指定表之间的关联条件。

3.3、多表查询的分类

3.3.1内连接

在内连接查询中，只有那些满足连接条件的记录才会出现在结果集中。换句话说，内连接会找出两个表中相对应字段值相等的记录，并将这些记录组合成新的结果集返回。

select 字段... from 表1 inner join 表2 on 条件

inner可以省略

select * from emp join dept on emp.sept_id = dept.id;

只有 on 后面的条件成立的数据才会被查出来

3.3.2外连接

外连接（Outer Join）是一种数据库连接操作，它返回不仅是两个表中匹配的行，还包括某个表或两个表中的非匹配行。外连接可以分为左外连接（Left Outer Join）、右外连接（Right Outer Join）。

左外连接（Left Outer Join）：返回所有左表（即出现在连接关键字左边的表）的记录，以及右表中与之匹配的记录。如果右表中没有匹配项，则结果集中的右表部分显示为NULL。
右外连接（Right Outer Join）：与左外连接相反，返回所有右表的记录以及左表中与之匹配的记录。如果左表中没有匹配项，则左表部分显示为NULL。

左外连接：select 字段... from 表1 left outer join 表2 on 条件;

右外连接：select 字段... from 表1 right outer join 表2 on 条件

outer可省略。

这就是左外连接，左表的数据全部显示，右表能关联上的显示，关联不上的显示为null。上面内连接时只有16行是因为陈友谅的dept_id是空，关联不上。

3.3.3字段显示

如果是想要两个表中的某些字段，该字段必须非常的明确：

select id,name,dept_id from emp e left join dept d on e.dept_id = d.id ;

如果不明确他就不知道你需要的到底是哪个表里的该字段，就会报错

如果是想要两个表中的某些字段，该字段必须非常的明确：

select emp.id,dept.name,dept_id from emp left join dept on emp.dept_id = dept.id ;

多表关联的时候，如果一个字段非常的明确，可以不在前面加表名，如果有多个表都有该字段，必须在字段前面指明是哪个表的字段，否则报错！！！

3.3.4使用别名

如果表名特别⻓，每次写关联关系就特别的费劲，可以使⽤别名代替

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

mysql中 group by、 order by、 having 后面可以使用别名，where后面不能使用别名

二、子查询

子查询（Subquery）是在另一个查询语句内部执行的SELECT语句。子查询可以嵌套在主查询的WHERE子句、FROM子句或其他适当的位置中，用于过滤或生成主查询所需的中间结果。子查询通常用于处理更复杂的查询逻辑，如比较多个表中的数据、聚合计算或是生成临时表等。

1、分类

标量子查询：返回单一值的标量，最简单的形式。
列子查询：返回的结果集是 N 行一列。
行子查询：返回的结果集是一行 N 列。
表子查询：返回的结果集是 N 行 N 列。

2、实例

2.1标量子查询

标量子查询是指子查询的结果是一个单独的值，如一个数字、字符串或日期。这种类型的子查询通常在比较操作中使用，作为主查询的一个条件。
常用的操作符：= <> > >= < <=

需求：查询 "销售部" 的所有员工信息

首先要查出销售部的id ：select id from dept where name = '销售部';

再根据销售部的id去查询所有员工的id：select * from emp where dept_id = 4;

但有时他们会让你只能使用一条sql去查，此时，子查询的作用就凸显出来了

select * from emp where dept_id = (select id from dept where name = '销售部');

轻松惬意解决问题

2.2列子查询

列子查询是指子查询的结果是一列或多行一列的数据。这种类型的子查询通常用于在主查询中进行集合比较或者条件筛选。

常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

操作符	描述
IN	在指定的集合范围内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有一个满足即可
SOME	与any等同，使用some的地方搜可以使用any
ALL	子查询返回列表的所有值都必须满足

需求：查询 "销售部" 和 "市场部" 的所有员工信息

先查询"销售部" 和 "市场部"的id：

select id from dept where name = '销售部' or name = '市场部';

在根据id查询员工信息：
select * from emp where dept_id = 2 or dept_id = 4;

但使用子查询可以一下成功

select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

2.3行子查询

行子查询是指子查询的结果是一行或多列一行的数据。这种类型的子查询通常用于在主查询中查找与子查询结果完全匹配的记录。

常用的操作符：= 、<> 、IN 、NOT IN

需求：查询与 "张无忌" 的薪资及直属领导相同的员工信息

先查询"张无忌" 的薪资及直属领导：

select salary,managerid from emp where name = '张无忌';

在查询和他一样的员工

select * from emp
where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

2.4表子查询

表子查询是指子查询的结果是多行多列的数据，相当于一个小型的表格。这种类型的子查询通常用于在主查询中作为数据源或者进行集合操作。

需求：查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

先查询与 "鹿杖客" , "宋远桥" 的职位和薪资

select job,salary from emp where name = '鹿杖客' or name = '宋远桥'

再查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

select * from emp where (job,salary) in (
select job,salary from emp where name = '鹿杖客' or name = '宋远桥') ;

若是更进一步，则是去掉"鹿杖客" , "宋远桥"这两个人，各位不妨思考一下怎么搞。

3、出现位置

子查询的各个分类说白了也就是子查询的出现位置

- 在where⼦句中: ⼦查询的结果可⽤作条件筛选时使⽤的值。
- 在from⼦句中: ⼦查询的结果可充当⼀张表或视图，需要使⽤表别名。
- 在having⼦句中: ⼦查询的结果可⽤作分组查询再次条件过滤时使⽤的值
- 在select⼦句中: ⼦查询的结果可充当⼀个字段。仅限⼦查询返回单⾏单列的情况。

上面的是出现在where中，下面讲讲出现在其他三个地方

3.1出现在from子句中

需求：查询员⼯的姓名，⼯资，及其部⻔的平均⼯资

部门平均工资： select dept_id,avg(salary) from emp group by dept_id;

此时只要把上面查出来的结果当成一张表和emp表关联即可得到最后的答案

select name,salary,avgMoney from
emp ,(select dept_id,avg(salary) avgMoney from emp group by dept_id) avgTable
where emp.dept_id=avgTable.dept_id;

3.2出现在having子句中

需求：查询平均⼯资⼤于1号部⻔的平均⼯资的部⻔号，和平均⼯资

1号部门的平均工资

select avg(salary) avg1 from emp where dept_id=1;