一、多表查询

（一）多表关系

在项目开发中，进行数据库结构设计时，会根据业务需求和业务模块之间的关系设计表结构，由于业务之间的练习，表结构之间也会存在联系，基本分为3种：

1.一对多（多对一）

案例：部门和员工的关系

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

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

2.多对多

案例：学生和课程的关系

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

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

练习

-- 学生表 create table student( id int auto_increment primary key comment '主键id', name varchar(20) comment '姓名', no varchar(10) comment '学号' )comment '学生表'; insert into student values (null, '张三', '2026061701'),(null '李四', '2026061702'); -- 课程表 create table course( id int auto_increment primary key comment '主键id', name varchar(10) comment '课程名称' )comment '课程表'; insert into course values(null, '语文'), (null, '数学'); -- 中间表 create table studnet_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) reference course (id), constraint fk_studentid foreign key (studentd) reference student (id) )comment '学生课程中间表'; insert into student_course values (null,1,1),(null,1,2),(null,2,1),(null,2,2);

3.一对一

案例：用户和用户详情的关系

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

实现：在任意一方加入外键，关联另外一方的主键，并设置外键是唯一的（UNIQUE）。

练习

-- 用户基本信息 create table tb_user( id int auto_increment primary key comment '主键id', name varchar(10) comment '姓名', age int comment '年龄', gender char(1) comnment '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 '专业', userid int unique comment '用户id', constraint fk_userid foreign key (userid) references tb_user(id) )comment '用户教育信息表';

（二）多表查询概述

1.概述

在多表查询时，需要消除无效的笛卡尔积（笛卡尔积：集合A和集合B所有的组合情况）

select * from employ, dept with employ.id = dept.id;

不满足连接条件的数据将不会查询到

2.多表查询分类

（1）连接查询

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

外连接：

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

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

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

（2）子查询

在一个查询语句中嵌套另一个查询语句

（三）内连接

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

隐式内连接和显式内连接的区别

内连接查询语法：

1.隐式内连接

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

2.显式内连接

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

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

练习：查询每个员工的姓名和关联部门的名称

隐式内连接

-- 员工表employ，部门表dept -- 连接条件：employ.dept_id =dept.id -- 隐式内连接 select employ.name, dept.name from employ, dept where employ.dept_id =dept.id; -- 使用别名 select e.name, d.name from employ e,dept d where e.dept_id = d.id;

显式内连接

-- 显式内连接 ...INNER JOIN...ON...(INNER可省略） select e.name, d.name from employ e inner join dept d on e.dept = d.id;

注意：使用别名后，不能再用表名限定字段

拓展：Oracle表连接

（四）外连接

1.左外连接

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

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

2.右外连接

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

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

练习：

-- 左外连接 -- 查询employ表的所有数据，和对应的部门信息 select e.*, d.name from employ e left outer join dept d on e.dept_id=d.dept_id; -- 右外连接 -- 查询dept表所有数据，和对应的员工表的信息 select e.*, d.* from employ e right outer join dept d on e.dept_id=d.dept_id;

（五）自连接

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

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

练习：

查询员工的领导（员工信息中有所属领导id）

-- 查询员工及其领导的名字（内连接） select a.name '员工', b.name'领导' from employ a, employ b where a.managerid=b.id; -- 查询所有员工及其领导的名字，如果员工没有领导，也需要查询出来（外连接） select a.name '员工', b.name'领导' from employ a left join employ b on a.managerid=b.id;

（六）联合查询

把多次查询的结果合并起来，形成一个新的查询结果集

关键字：union (all)

union all：直接合并多次查询的结果

union：合并后去重

使用要求：查询语句的列的数量要保持一致，字段类型也要保持一致

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

练习

-- 将薪资低于5000的员工和年龄大于50岁的员工全部查询出来 -- 将两次查询的结果合并在一起，可能会出现重复内容 select * from employ where salary < 5000 union all select * from employ where age > 50; -- 去重（去掉all） select * from employ where salary < 5000 union select * from employ where age > 50;

（七）子查询

SQL语句中嵌套SELECT语句，成为嵌套查询，又称子查询。

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

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

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

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

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

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

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

根据子查询位置，分为：

WHERE之后

FROM之后

SELECT之后

1.标量子查询

（1）概念

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

（2）常用操作符

= <> > >= < <=

（3）练习

-- 查询销售部所有员工信息 -- 拆解 select * from dept where name = '销售部'; select * from employ where dept_id = 4; --合并 select * from employ where dept_id = (select * from dept where name = '销售部'); --查询在“张东白”入职后的员工信息 --拆解 select entrydate from employ where name = '张东白'; select * from employ where entrydate > '2009-07-01'; --合并 select * from employ where entrydate > (select entrydate from employ where name = '张东白');

2.列子查询

（1）概念

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

（2）常用的操作符

IN NOT IN ANY SOME ALL

（3）练习

--1.查询“销售部”和“市场部”所有员工信息 select * from employ where dept_id in (select id from dept where name = '销售部' or name = '市场部'); --2.查询比财务部所有人工资都高的员工的信息 select * from employ where salary > all ( select salary from employ where dept_id = (select id from dept where name = '财务部')); --3.查询比研发部任意一个人工资高的员工的信息 select * from employ where salary > any ( select salary from employ where dept_id = (select id from dept where name = '研发部')); --any也可以用some

3.行子查询

（1）概念

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

（2）常用操作符

= <> IN NOT IN

（3）练习

--1.查询与“张一”的薪资及直属领导相同的员工的信息 --拆分 select salary, managerid from employ where name = '张一'; select * from employ where (salary, managerid) = (12500, 1); --合并 select * from employ where (salary, managerid) = (select salary, managerid from employ where name = '张一');

4.表子查询

（1）概念

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

（2）常用操作符

IN

（3）练习

--1.查询与李一、李二职位和薪资都相同的员工的信息 select * from employ where (job, salary) in (select job, salary from employ where name = '李一' or name = '李二'); --2.查询入职日期在2010年之后的员工信息及部门信息 select * from (select * from employ where entrydate > '2010-01-01') e left join dept d on e.dept_id = d.id;