Mysql数据库学习--多表查询

多表关系

概述

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多 (多对一)

多对多

一对一

一对多 (多对一)

案例：部门与员工的关系

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

实现：在多的一方建立外键，指向一的一方的主键

多对多

案例：学生与课程的关系

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

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键，主要通过中间表来维护多个表

#多对多的关系
create table student(id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',no varchar(10) comment '学号'
) comment '学生表';
insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊', '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');create table course(id int auto_increment primary key comment '主键ID',name varchar(10) comment '课程名称'
) comment '课程表';
insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');create table student_course(id int auto_increment comment '主键' primary key,studentid int not null comment '学生ID',courseid int not null comment '课程ID',constraint fk_courseid foreign key (courseid) references course (id),constraint fk_studentid foreign key (studentid) 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)

多表查询概述

概述：指从多张表中查询数据

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

select * from 表1 , 表2 where 条件;

多表查询分类

连接查询

内连接：相当于查询 A、B 交集部分数据

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

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

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

连接查询 - 内连接

内连接查询语法:

隐式内连接

SELECT 字段列表 FROM 表1,表2 WHERE 条件 ...;
-- 1. 查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
-- 表结构：emp ，dept
-- 连接条件：emp.dept_id = dept.idselect emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;

显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;
-- 2. 查询每一个员工的姓名，及关联的部门的名称（显式内连接实现） --- INNER JOIN ... ON ...
-- 表结构：emp ，dept
-- 连接条件：emp.dept_id = dept.idselect e.name, d.name from emp e inner join dept d  on e.dept_id = d.id;

内连接查询的是两张表交集的部分

连接查询 - 外连接

外连接查询语法:

左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;
-- 1. 查询emp表的所有数据，和对应的部门信息（左外连接）
-- 表结构：emp，dept
-- 连接条件：emp.dept_id = dept.id
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

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

右外连接

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

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

连接查询 - 自连接

自连接查询语法:

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;
-- 自连接
-- 1. 查询员工 及其 所属领导的名字
-- 表结构：emp
select a.name , b.name from emp a , emp b where a.managerid = b.id;-- 2. 查询所有员工 emp 及其领导的名字 emp ，如果员工没有领导，也需要查询出来
-- 表结构：emp a , emp b
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

自连接查询，可以是内连接查询，也可以是外连接查询。

联合查询 - union , union all

对于 union 查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...
UNION[ALL]
SELECT 字段列表 FROM 表B ...;
-- 1. 将薪资低于 5000 的员工，和 年龄大于 50 岁的员工全部查询出来。
select * from emp where salary < 5000
union all
select * from emp where age > 50;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

子查询

概念：SQL 语句中嵌套 SELECT 语句，称为嵌套查询，又称子查询。

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

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

根据子查询结果不同，分为：

标量子查询（子查询结果为单个值）

列子查询（子查询结果为一列）

行子查询（子查询结果为一行）

表子查询（子查询结果为多行多列）

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等 ），最简单的形式，这种子查询成为标量子查询。

常用的操作符: = <>> >= < <=

-- 2. 查询在 “方东白” 入职之后的员工信息
-- 需要将1个问题拆成2个问题
-- a. 查询 方东白 的入职日期
select entrydate from emp where name = '方东白';-- b. 查询指定入职日期之后入职的员工信息
select * from emp where entrydate > '2009-02-12';
-- 使用标量子查询可以直接解决
select * from emp where entrydate > (select entrydate from emp where name = '方东白');

列子查询

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

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

-- 列子查询
-- 1. 查询 “销售部” 和 “市场部” 的所有员工信息
-- 需要拆解
-- a. 查询 “销售部” 和 “市场部” 的部门ID
select id from dept where name = '销售部' or name = '市场部';-- b. 根据部门ID，查询员工信息
select * from emp where dept_id in (2,4);
-- 不需要拆解
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

行子查询

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

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

-- 行子查询
-- 1. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ；
-- a. 查询 “张无忌” 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';-- b. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ；
select * from emp where salary = 12500 and managerid = 1;
select * from emp where (salary,managerid) = (12500,1);
-- 这两种方式效果相同
-- 行子查询
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询

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

常用的操作符: IN

-- 表子查询
-- 1. 查询与 “鹿杖客” ，“宋远桥” 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );