## Day08 后端Web实战：员工管理1(查询)

---

##### 员工管理--查询需求

<figure><img src="/AiJavaWeb/imgs/jwai08-01.png"><figcaption>图1 员工管理--查询需求</figcaption></figure>




##### 目录

* 多表关系
* 多表查询
* 员工列表查询

### 1. 多表关系

##### 概述

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

#### 1.1 一对多（多对一）

##### 场景

* 场景：部门与员工的关系 （一个部门下有多个员工）。

```sql
create table dept(
    id int unsigned primary key auto_increment comment 'ID',
    name varchar(10) not null unique comment '部门名称',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间'
) comment '部门表' ;

create table emp(
    id int unsigned primary key auto_increment comment 'ID,主键',
    username varchar(20) not null unique comment '用户名',
    password varchar(32) not null comment '密码',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1:男, 2:女',
    phone char(11) not null unique comment '手机号',
    job tinyint unsigned comment '职位, 1:班主任,2:讲师...',
    salary int unsigned comment '薪资',
    image varchar(255) comment '头像',
    entry_date date comment '入职日期',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间',
    dept_id int unsigned comment '关联的部门ID'
) comment '员工表’;
```

<figure><img src="/AiJavaWeb/imgs/jwai08-02.png"><figcaption>图2 部门和员工之间1对多的关系</figcaption></figure>

<figure><img src="/AiJavaWeb/imgs/jwai08-03.png"><figcaption>图3 部门和员工之间1对多的关系</figcaption></figure>

<figure><img src="/AiJavaWeb/imgs/jwai08-04.png"><figcaption>图4 部门和员工之间1对多的关系</figcaption></figure>



* 一对多的关系如何实现? <mark>在数据库表中多的一方，添加字段，来关联一的一方的主键。</mark>

##### 小结

<figure><img src="/AiJavaWeb/imgs/jwai08-05.png"><figcaption>图5 1对多关系小结</figcaption></figure>

##### 多表问题分析

```sql
-- 建库、建表、添加数据
create schema web03;

use web03;

CREATE TABLE dept (
  id int unsigned PRIMARY KEY AUTO_INCREMENT COMMENT 'ID, 主键',
  name varchar(10) NOT NULL UNIQUE COMMENT '部门名称',
  create_time datetime DEFAULT NULL COMMENT '创建时间',
  update_time datetime DEFAULT NULL COMMENT '修改时间'
) COMMENT '部门表';
-- 第一条单位名称改成加上自己的姓
INSERT INTO dept VALUES
(1,'学工部_杜','2023-09-25 09:47:40','2023-09-25 09:47:40'),
(2,'教研部','2023-09-25 09:47:40','2023-10-09 15:17:04'),
(3,'咨询部2','2023-09-25 09:47:40','2023-11-30 21:26:24'),
(4,'就业部','2023-09-25 09:47:40','2023-09-25 09:47:40'),
(5,'人事部','2023-09-25 09:47:40','2023-09-25 09:47:40'),
(15,'行政部','2023-11-30 20:56:37','2023-11-30 20:56:37');


create table emp(
    id int unsigned primary key auto_increment comment 'ID,主键',
    username varchar(20) not null unique comment '用户名',
    password varchar(32) default '123456' comment '密码',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1:男, 2:女',
    phone char(11) not null unique comment '手机号',
    job tinyint unsigned comment '职位, 1 班主任, 2 讲师 , 3 学工主管, 4 教研主管, 5 咨询师',
    salary int unsigned comment '薪资',
    image varchar(255) comment '头像',
    entry_date date comment '入职日期',
    dept_id int unsigned comment '部门ID',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间' -- references dept(id)
) comment '员工表';

-- 第一条员工改成自己的名字
INSERT INTO emp VALUES 
(1,'dulaoshi','123456','杜老师',1,'13309090001',4,15000,'5.png','2000-01-01',2,'2023-10-20 16:35:33','2023-11-16 16:11:26'),
(2,'songjiang','123456','宋江',1,'13309090002',2,8600,'01.png','2015-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:37'),
(3,'lujunyi','123456','卢俊义',1,'13309090003',2,8900,'01.png','2008-05-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:39'),
(4,'wuyong','123456','吴用',1,'13309090004',2,9200,'01.png','2007-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:41'),
(5,'gongsunsheng','123456','公孙胜',1,'13309090005',2,9500,'01.png','2012-12-05',2,'2023-10-20 16:35:33','2023-10-20 16:35:43'),
(6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,'01.png','2013-09-05',1,'2023-10-20 16:35:33','2023-10-20 16:35:45'),
(7,'chaijin','123456','柴进',1,'13309090007',1,4700,'01.png','2005-08-01',1,'2023-10-20 16:35:33','2023-10-20 16:35:47'),
(8,'likui','123456','李逵',1,'13309090008',1,4800,'01.png','2014-11-09',1,'2023-10-20 16:35:33','2023-10-20 16:35:49'),
(9,'wusong','123456','武松',1,'13309090009',1,4900,'01.png','2011-03-11',1,'2023-10-20 16:35:33','2023-10-20 16:35:51'),
(10,'linchong','123456','林冲',1,'13309090010',1,5000,'01.png','2013-09-05',1,'2023-10-20 16:35:33','2023-10-20 16:35:53'),
(11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,'01.png','2007-02-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:55'),
(12,'xiaoliguang','123456','小李广',1,'13309090012',2,10000,'01.png','2008-08-18',2,'2023-10-20 16:35:33','2023-10-20 16:35:57'),
(13,'yangzhi','123456','杨志',1,'13309090013',1,5300,'01.png','2012-11-01',1,'2023-10-20 16:35:33','2023-10-20 16:35:59'),
(14,'shijin','123456','史进',1,'13309090014',2,10600,'01.png','2002-08-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:01'),
(15,'sunerniang','123456','孙二娘',2,'13309090015',2,10900,'01.png','2011-05-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:03'),
(16,'luzhishen','123456','鲁智深',1,'13309090016',2,9600,'01.png','2010-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:05'),
(17,'liying','12345678','李应',1,'13309090017',1,5800,'01.png','2015-03-21',1,'2023-10-20 16:35:33','2023-10-20 16:36:07'),
(18,'shiqian','123456','时迁',1,'13309090018',2,10200,'01.png','2015-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:09'),
(19,'gudasao','123456','顾大嫂',2,'13309090019',2,10500,'01.png','2008-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:11'),
(20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,'01.png','2018-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:13'),
(21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,'01.png','2015-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:15'),
(22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,'01.png','2016-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:17'),
(23,'ruanji','123456','阮籍',1,'13309090023',5,5800,'01.png','2012-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:19'),
(24,'tongwei','123456','童威',1,'13309090024',5,5000,'01.png','2006-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:21'),
(25,'tongmeng','123456','童猛',1,'13309090025',5,4800,'01.png','2002-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:23'),
(26,'yanshun','123456','燕顺',1,'13309090026',5,5400,'01.png','2011-01-01',3,'2023-10-20 16:35:33','2023-11-08 22:12:46'),
(27,'lijun','123456','李俊',1,'13309090027',2,6600,'8.png','2004-01-01',2,'2023-10-20 16:35:33','2023-11-16 17:56:59'),
(28,'lizhong','123456','李忠',1,'13309090028',5,5000,'6.png','2007-01-01',3,'2023-10-20 16:35:33','2023-11-17 16:34:22'),
(29,'songqing','123456','宋清',1,'13309090029',NULL,5100,'01.png','2020-01-01',NULL,'2024-10-27 16:35:33','2024-10-27 16:36:31'),
(30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'01.png','2020-03-01',NULL,'2024-10-27 16:35:33','2024-10-27 16:36:31');

```

* 现象：部门数据可以直接删除，然而还有部分员工归属于该部门下，此时就出现了数据的不完整、不一致问题 。
* 原因：目前上述的两张表，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的 。
* 解决方案：外键约束 。

<figure><img src="/AiJavaWeb/imgs/jwai08-06.png"><figcaption>图6 外键约束</figcaption></figure>

##### 外键约束

* 可以在创建表时 或 表结构创建完成后，为字段添加外键约束。 具体语法如下：

```sql
-- 创建表时指定
create table 表名(
    字段名 数据类型,
    -- ...
    [constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)	
);

-- 建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);


-- 添加外键约束（emp的dept_id--->dept的主键id）
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
```

<figure><img src="/AiJavaWeb/imgs/jwai08-07.png"><figcaption>图7 物理外键和逻辑外键</figcaption></figure>

* 一对多的关系如何实现? <mark>在数据库表中多的一方，添加字段，来关联一的一方的主键。x</mark>

##### 小结

<figure><img src="/AiJavaWeb/imgs/jwai08-08.png"><figcaption>图8 外键小结</figcaption></figure>

#### 1.2 一对一

一对一就是特殊的一对多。

* 案例: 用户 与 身份证信息 的关系

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

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

  <figure><img src="/AiJavaWeb/imgs/jwai08-09.png"><figcaption>图9 一对一关系</figcaption></figure>
  
  ```sql
  -- =============================一对一========================
  create table tb_user(
      id int unsigned  primary key auto_increment comment 'ID',
      name varchar(10) not null comment '姓名',
      gender tinyint unsigned not null comment '性别, 1 男  2 女',
      phone char(11) comment '手机号',
      degree varchar(10) comment '学历'
  ) comment '用户信息表';
  
  insert into tb_user values
  (1,'白眉鹰王',1,'18812340001','初中'),
  (2,'青翼蝠王',1,'18812340002','大专'),
  (3,'金毛狮王',1,'18812340003','初中'),
  (4,'紫衫龙王',2,'18812340004','硕士');
  
  create table tb_user_card(
      id int unsigned  primary key auto_increment comment 'ID',
      nationality varchar(10) not null comment '民族',
      birthday date not null comment '生日',
      idcard char(18) not null comment '身份证号',
      issued varchar(20) not null comment '签发机关',
      expire_begin date not null comment '有效期限-开始',
      expire_end date comment '有效期限-结束',
      user_id int unsigned not null unique comment '用户ID',
      constraint fk_user_id foreign key (user_id) references tb_user(id)
  ) comment '用户身份证信息表';
  
  insert into tb_user_card values 
  (1,'汉','1960-11-06','100000100000100001','朝阳区公安局','2000-06-10',null,1),
  (2,'汉','1971-11-06','100000100000100002','静安区公安局','2005-06-10','2025-06-10',2),
  (3,'汉','1963-11-06','100000100000100003','昌平区公安局','2006-06-10',null,3),
  (4,'回','1980-11-06','100000100000100004','海淀区公安局','2008-06-10','2028-06-10',4);
  ```
  

##### 小结

<figure><img src="/AiJavaWeb/imgs/jwai08-10.png"><figcaption>图10 一对一小结</figcaption></figure>

#### 1.3 多对多

* 案例: 学生 与 课程的关系
* 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
* 实现: <mark>建立第三张中间表，中间表至少包含两个外键，分别关联两方主键</mark>
* 注意：成绩表中为了防止同一学生、同一门课程出现两个分数，学生ID+课程ID建立unique约束

<figure><img src="/AiJavaWeb/imgs/jwai08-11.png"><figcaption>图11 多对多关系</figcaption></figure>

```sql
--  ==============================多对多=============================
create table tb_student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';
insert into tb_student(name, no) values
('黛绮丝', '2000100101'),
('谢逊', '2000100102'),
('殷天正', '2000100103'),
('韦一笑', '2000100104');


create table tb_course(
   id int auto_increment primary key comment '主键ID',
   name varchar(10) comment '课程名称'
) comment '课程表';
insert into tb_course (name) values
('Java'),
('PHP'),
('MySQL') ,
('Hadoop');


create table tb_student_score(
   id int auto_increment primary key comment 'ID',
   student_id int not null comment '学生ID',
   course_id  int not null comment '课程ID',
   score decimal(5,2) comment '分数',
   constraint uk_student_course unique (student_id, course_id),
   constraint fk_courseid foreign key (course_id) references tb_course (id),
   constraint fk_studentid foreign key (student_id) references tb_student (id)
)comment '学生分数表_中间表_杜';

insert into tb_student_score(student_id, course_id, score) values
(1,1,80),
(1,2,66),
(1,3,90),
(2,2,80),
(2,3,59),
(3,4,100);
```

<figure><img src="/AiJavaWeb/imgs/jwai08-12.png"><figcaption>图12 <mark>多对多E-R图</mark></figcaption></figure>

##### 小结

<figure><img src="/AiJavaWeb/imgs/jwai08-13.png"><figcaption>图13 多对多小结</figcaption></figure>

#### 1.4 案例

##### 根据页面原型及需求文档分析并设计表结构

* 需求：请根据资料中提供的页面原型，<mark>设计 员工模块 涉及到的表结构</mark>。
* 步骤：
  * 阅读页面原型及需求文档，分析各个模块涉及到的表结构，及表结构之间的关系。
  * 根据页面原型及需求文档，分析各个表结构中具体的字段及约束。

<figure><img src="/AiJavaWeb/imgs/jwai08-14.png"><figcaption>图14 案例需求</figcaption></figure>

```sql
-- 多表设计：案例
-- 表：dept(1）--> emp(n)   emp(1)-->emp_expr(n)
create table emp_expr(
    id      int unsigned primary key auto_increment comment 'ID，主键',
    begin   date comment '开始时间',
    end     date comment '结束时间',
    company varchar(50) comment '公司名称',
    job     varchar(50) comment '职位',
    emp_id  int unsigned comment '关联的员工ID'
) comment '工作经历表_杜';
```

<figure><img src="/AiJavaWeb/imgs/jwai08-15.png"><figcaption>图15 <mark>案例设计验证</mark></figcaption></figure>

### 2. 多表查询

##### 多表查询

* 多表查询: 指从多张表中查询数据

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

  <figure><img src="/AiJavaWeb/imgs/jwai08-16.png"><figcaption>图16 笛卡尔积</figcaption></figure>

  * 多表查询：<mark>在多表查询时，需要消除无效的笛卡尔积】</mark>

  <figure><img src="/AiJavaWeb/imgs/jwai08-17.png"><figcaption>图17 多表查询</figcaption></figure>

  * 连接查询
    * 内连接：相当于查询A、B交集部分数据
    * 外连接
      * 左外连接：查询左表所有数据(包括两张表交集部分数据)
      * 右外连接：查询右表所有数据(包括两张表交集部分数据)
      * 全外连接：查询满足连接条件的左、右两表所有数据
  
  <figure><img src="/AiJavaWeb/imgs/jwai08-18.png"><figcaption>图18 连接查询</figcaption></figure>
  
  ```sql
  use web03;
  -- 笛卡尔积、隐式内连接
  select * from emp , dept;
  select * from emp , dept where emp.dept_id = dept.id;
  ```
  
  <figure><img src="/AiJavaWeb/imgs/jwai08-19.png"><figcaption>图19 <mark>笛卡尔积、隐式内连接查询验证</mark></figcaption></figure>
  
  
  
  * 子查询


#### 2.1 内连接


* 内连接查询的是两张表交集部分的数据。具体语法为：

```sql
-- 1.隐式内连接 (常见)
select 字段列表 from 表1 , 表2 where 连接条件 ...;

-- 2.显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ... ;

-- 1.给表起别名,来简化书写
select 字段列表 from 表1 [as] 别名1, 表2 [as] 别名2 where 条件 ...;

```

* <mark>注意：一旦为表指定了别名，就要通过别名来指定字段名，而不能再使用表名了。</mark>

```sql
-- 内连接
-- A，查询所有员工的ID，姓名，及所属的部门名称（隐式、显式内连接实现）
-- 隐式
select emp.id , emp.name, dept.name
from emp , dept
where emp.dept_id = dept.id order by emp.id;
-- 显式
select emp.id , emp.name, dept.name
from emp join dept on emp.dept_id = dept.id order by emp.id;
-- B，查询性别为男，且工资高于8000的员工的ID，姓名，性别，薪资及所属的部门名称（隐式、显式内连接实现）
-- 隐式
select emp.id , emp.name, emp.gender, emp.salary, dept.name
from emp , dept
where emp.dept_id = dept.id and emp.gender = 1 and emp.salary > 8000 order by emp.id;
-- 显式
select emp.id , emp.name, emp.gender, emp.salary, dept.name
from emp join dept on emp.dept_id = dept.id
where emp.gender = 1 and emp.salary > 8000 order by emp.id;

-- 给表起别名
select e.id , e.name 姓名, e.gender 性别, e.salary 薪资, d.name 部门名称
from emp e join dept as d on e.dept_id = d.id
where e.gender = 1 and e.salary > 8000 order by e.id;
```

<figure><img src="/AiJavaWeb/imgs/jwai08-20.png"><figcaption>图20 <mark>内连接查询验证</mark></figcaption></figure>



<figure><img src="/AiJavaWeb/imgs/jwai08-21.png"><figcaption>图21 内连接小结</figcaption></figure>



#### 2.2 外连接

* 外连接分为左外连接和右外连接。具体语法为：

```sql
-- 1.左外连接 (常见)
select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ...;

-- 2.右外连接
select 字段列表 from 表1 right [outer] join 表2 on 连接条件 ...;

```

* 提示：对于外连接，常用的是左外连接，因为右外连接的SQL也可以改造成为左外连接（两张表调换个顺序）。

<figure><img src="/AiJavaWeb/imgs/jwai08-22.png"><figcaption>图22 <mark>外连接验证</mark></figcaption></figure>

##### 小结

<figure><img src="/AiJavaWeb/imgs/jwai08-23.png"><figcaption>图23 外连接小结</figcaption></figure>



#### 2.3 子查询

* 介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。
* 形式：```select * from t1 where column1 = (select column1 from t2 …);```
* 说明：子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。
* 分类：
  * 标量子查询：子查询返回的结果为单个值
  * 列子查询：子查询返回的结果为一列
  * 行子查询：子查询返回的结果为一行
  * 表子查询：子查询返回的结果为多行多列

```sql
-- ==== 子查询=======
-- 标量子查询
-- A，查询[最早入职的员工信息
-- a，获取到最早入职时间
select min(entry_date) from emp;
-- b，查询最早入职的员工信息
select * from emp where entry_date = '2000-01-01';
select * from emp where entry_date = (select min(entry_date) from emp);
-- B，查询在“阮小五"入职之后入职的员工信息
-- a，查询“阮小五"的入职时间
select entry_date from emp where name ='阮小五';
-- b，查询在该时间之后入职的员工信息
select * from emp where entry_date > '2015-01-01';
select * from emp where entry_date > (select entry_date from emp where name ='阮小五');

-- 列子查询
-- A.查询"教研部"和“咨询部”的所有员工信息
-- a.查询"教研部"和"咨询部"的部门ID
select id from dept where name='教研部'or name= '咨询部';
-- b.查询指定部门ID的员工信息
select * from emp where dept_id in (2,3);
select * from emp where dept_id in
  (select id from dept where name = '教研部' or name = '咨询部');

-- 行子查询
-- A.查询与"李忠"的薪资及职位都相同的员工信息；
-- a.查询"李忠"的薪资及职位
select salary,job from emp where name ='李忠';
-- b.查询指定薪资和职位的员工信息
select * from emp where salary = 5000 and job = 5;
select * from emp where salary = (select salary from emp where name = '李忠')
                    and job = (select job from emp where name = '李忠');
-- 优化:
select * from emp where (salary,job)=
   (select salary, job from emp where name = '李忠');

-- 表子查询
-- A.查询入职日期是“2006-01-01"之后的员工信息，及其部门信息
-- a.获取每个部门的最高薪资
select dept_id, max(salary) from emp group by dept_id;
-- b.查询每个部门中薪资最高的员工信息
select * from emp e ,(select dept_id,max(salary) max_sal from emp group by dept_id) a
where e.dept_id = a.dept_id and e.salary = a.max_sal;
```

* 提示：子查询的要点是，先对需求做拆分，明确具体的步骤，然后再逐步编写SQL语句。

  <figure><img src="/AiJavaWeb/imgs/jwai08-24.png"><figcaption>图24 <mark>子查询验证</mark></figcaption></figure>

##### 小结

<figure><img src="/AiJavaWeb/imgs/jwai08-25.png"><figcaption>图25 子查询小结</figcaption></figure>

#### 2.4 案例

##### 需求

* ①查询 "教研部" 的 "男性" 员工，且在 "2011-05-01" 之后入职的员工信息 。
* ②查询工资 低于公司平均工资的 且 性别为男 的员工信息 。
* ③查询部门人数超过 10 人的部门名称 。
* ④查询在 "2010-05-01" 后入职，且薪资高于 10000 的 "教研部" 员工信息，并根据薪资倒序排序。
* ⑤查询工资 低于本部门平均工资的员工信息 。

```sql
-- 需求:
-- 1.查询"教研部"性别为男，且在"2011-05-01"之后入职的员工信息。
select * from emp where gender = 1
                    and dept_id = (select id from dept where name = '教研部')
                    and entry_date > '2011-05-01';

select e.* from emp e, dept d where e.dept_id =d.id
                                and d.name = '教研部'
                                and e.gender = 1 and e.entry_date >'2011-05-01';
-- 2.查询工资低于公司平均工资的且性别为男的员工信息。
-- 表：emp
-- 2.1查询公司平均工资
select avg(salary) from emp;
-- 2.2查询工资低于公司平均工资的且性别为男的员工信息。
select * from emp where salary < 7548.2759 and gender = 1;
select * from emp where salary < (select avg(salary) from emp) and gender = 1;

-- 3.查询部门人数超过10人的部门名称。
select name from dept where id in
      (select dept_id from emp group by dept_id having count(*) > 10);

select d.name, count(*)
from emp e, dept d
where e.dept_id = d.id
group by d.name
having count(*) > 10;

-- 4.查询在"2010-05-01"后入职，且薪资高于10000的"教研部"员工信息，并根据薪资倒序排序。
-- 表：dept，emp
select e.* from emp e , dept d
           where e.dept_id = d.id
             and e.entry_date > '2010-05-01'
             and e.salary > 10000
             and d.name ='教研部'
           order by e.salary desc;

select * from emp where entry_date > '2010-05-01'
                    and salary > 10000
                    and dept_id = (select id from dept where name = '教研部')
order by salary desc;


-- 5.查询工资低于本部门平均工资的员工信息。
-- 5.1 查询每个部门的平均工资
select dept_id, avg(salary) avg_sal from emp group by dept_id;
-- 5.2查询工资低于本部门平均工资的员工信息。
select e.* from emp e, (select dept_id, avg(salary) avg_sal from emp group by dept_id) as a
where e.dept_id =a.dept_id and e.salary < a.avg_sal;

-- 5.3查询工资高于本部门平均工资的员工信息。
select e.*
from emp e, (select dept_id, avg(salary) avg_sal from emp group by dept_id) as a
where e.dept_id =a.dept_id and e.salary > a.avg_sal;
```

<figure><img src="/AiJavaWeb/imgs/jwai08-26.png"><figcaption>图26 <mark>多表查询案例验证</mark></figcaption></figure>



### 3.员工列表查询

##### 需求

* 需求：查询所有员工信息，并查询出部门名称。

<figure><img src="/AiJavaWeb/imgs/jwai08-27.png"><figcaption>图27 员工列表查询需求</figcaption></figure>

#### 3.1 准备工作

* 准备数据库tlias中数据表 emp、emp_expr。

```sql
use tlias;
-- 员工表
create table emp(
    id int unsigned primary key auto_increment comment 'ID,主键',
    username varchar(20) not null unique comment '用户名',
    password varchar(32) default '123456' comment '密码',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1:男, 2:女',
    phone char(11) not null unique comment '手机号',
    job tinyint unsigned comment '职位, 1 班主任, 2 讲师 , 3 学工主管, 4 教研主管, 5 咨询师',
    salary int unsigned comment '薪资',
    image varchar(255) comment '头像',
    entry_date date comment '入职日期',
    dept_id int unsigned comment '部门ID',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间'
) comment '员工表';

-- 第1条数据改成自己的名字
INSERT INTO emp VALUES 
(1,'dulaoshi','123456','杜老师',1,'13309090001',4,15000,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2000-01-01',2,'2023-10-20 16:35:33','2023-11-16 16:11:26'),
(2,'songjiang','123456','宋江',1,'13309090002',2,8600,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2015-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:37'),
(3,'lujunyi','123456','卢俊义',1,'13309090003',2,8900,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2008-05-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:39'),
(4,'wuyong','123456','吴用',1,'13309090004',2,9200,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2007-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:41'),
(5,'gongsunsheng','123456','公孙胜',1,'13309090005',2,9500,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2012-12-05',2,'2023-10-20 16:35:33','2023-10-20 16:35:43'),
(6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2013-09-05',1,'2023-10-20 16:35:33','2023-10-20 16:35:45'),
(7,'chaijin','123456','柴进',1,'13309090007',1,4700,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2005-08-01',1,'2023-10-20 16:35:33','2023-10-20 16:35:47'),
(8,'likui','123456','李逵',1,'13309090008',1,4800,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2014-11-09',1,'2023-10-20 16:35:33','2023-10-20 16:35:49'),
(9,'wusong','123456','武松',1,'13309090009',1,4900,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2011-03-11',1,'2023-10-20 16:35:33','2023-10-20 16:35:51'),
(10,'linchong','123456','林冲',1,'13309090010',1,5000,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2013-09-05',1,'2023-10-20 16:35:33','2023-10-20 16:35:53'),
(11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2007-02-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:55'),
(12,'xiaoliguang','123456','小李广',1,'13309090012',2,10000,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2008-08-18',2,'2023-10-20 16:35:33','2023-10-20 16:35:57'),
(13,'yangzhi','123456','杨志',1,'13309090013',1,5300,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2012-11-01',1,'2023-10-20 16:35:33','2023-10-20 16:35:59'),
(14,'shijin','123456','史进',1,'13309090014',2,10600,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2002-08-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:01'),
(15,'sunerniang','123456','孙二娘',2,'13309090015',2,10900,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2011-05-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:03'),
(16,'luzhishen','123456','鲁智深',1,'13309090016',2,9600,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2010-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:05'),
(17,'liying','12345678','李应',1,'13309090017',1,5800,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2015-03-21',1,'2023-10-20 16:35:33','2023-10-20 16:36:07'),
(18,'shiqian','123456','时迁',1,'13309090018',2,10200,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2015-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:09'),
(19,'gudasao','123456','顾大嫂',2,'13309090019',2,10500,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2008-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:11'),
(20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2018-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:13'),
(21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2015-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:15'),
(22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2016-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:17'),
(23,'ruanji','123456','阮籍',1,'13309090023',5,5800,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2012-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:19'),
(24,'tongwei','123456','童威',1,'13309090024',5,5000,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2006-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:21'),
(25,'tongmeng','123456','童猛',1,'13309090025',5,4800,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2002-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:23'),
(26,'yanshun','123456','燕顺',1,'13309090026',5,5400,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2011-01-01',3,'2023-10-20 16:35:33','2023-11-08 22:12:46'),
(27,'lijun','123456','李俊',1,'13309090027',2,6600,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2004-01-01',2,'2023-10-20 16:35:33','2023-11-16 17:56:59'),
(28,'lizhong','123456','李忠',1,'13309090028',5,5000,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2007-01-01',3,'2023-10-20 16:35:33','2023-11-17 16:34:22'),
(30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2020-03-01',NULL,'2023-10-20 16:35:33','2023-10-20 16:36:31'),
(36,'linghuchong','123456','令狐冲',1,'18809091212',2,6800,'https://web-framework.oss-cn-hangzhou.aliyuncs.com/2023/1.jpg','2023-10-19',2,'2023-10-20 20:44:54','2023-11-09 09:41:04');


-- 员工工作经历信息
create table emp_expr(
    id int unsigned primary key auto_increment comment 'ID, 主键',
    emp_id int unsigned comment '员工ID',
    begin date comment '开始时间',
    end  date comment '结束时间',
    company varchar(50) comment '公司名称',
    job varchar(50) comment '职位'
)comment '工作经历';
```

* 准备实体类 Emp、EmpExpr。

###### cn/dzj/pojo/Emp.java

```java
package cn.dzj.pojo;
import lombok.Data;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

@Data
public class Emp {
    private Integer id; //ID,主键
    private String username; //用户名
    private String password; //密码
    private String name; //姓名
    private Integer gender; //性别, 1:男, 2:女
    private String phone; //手机号
    private Integer job; //职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师
    private Integer salary; //薪资
    private String image; //头像
    private LocalDate entryDate; //入职日期
    private Integer deptId; //关联的部门ID
    private LocalDateTime createTime; //创建时间
    private LocalDateTime updateTime; //修改时间
    
    //封装部门名称
    private String deptName;
}
```

###### cn/dzj/pojo/EmpExpr.java

```java
package cn.dzj.pojo;
import lombok.Data;
import java.time.LocalDate;

/**
 * 工作经历
 */
@Data
public class EmpExpr {
    private Integer id; //ID
    private Integer empId; //员工ID
    private LocalDate begin; //开始时间
    private LocalDate end; //结束时间
    private String company; //公司名称
    private String job; //职位
}
```

* 准备三层架构的基本代码结构：EmpController、EmpService/EmpServiceImpl、EmpMapper。

<figure><img src="/AiJavaWeb/imgs/jwai08-28.png"><figcaption>图28 查询需求</figcaption></figure>

###### cn/dzj/controller/EmpController.java

```java
package cn.dzj.controller;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.RestController;
// 员工控制器
@Slf4j
@RestController
public class EmpController {
    
}
```

###### cn/dzj/service/EmpService.java

```java
package cn.dzj.service;
import org.springframework.stereotype.Service;
public interface EmpService {

}
```

###### cn/dzj/service/impl/EmpServiceImpl.java

```java
package cn.dzj.service.impl;
import cn.dzj.service.EmpService;
@Service
public class EmpServiceImpl implements EmpService {

}
```

###### cn/dzj/mapper/EmpMapper.java

```java
package cn.dzj.mapper;
import org.apache.ibatis.annotations.Mapper;
/**
 * 员工管理
 */
@Mapper
public interface EmpMapper {
    
}
```

<figure><img src="/AiJavaWeb/imgs/jwai08-29.png"><figcaption>图29 目录结构</figcaption></figure>

```sql
-- 查询所有的员工信息，以及该员工归属的部门名称。
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
```

#### 3.2 分页查询

##### 分页查询-分析

<figure><img src="/AiJavaWeb/imgs/jwai08-30.png"><figcaption>图30 分页查询分析</figcaption></figure>

```sql
-- 分页查询
-- limit 起始索引，每页展示记录数
-- 查询第1页，每页展示5条
select e.*, d.name from emp e left join dept d on e.dept_id = d.id limit 0,5;

-- 查询第2页，每页展示5条
select e.*, d.name from emp e left join dept d on e.dept_id = d.id limit 5,5;

-- 查询第3页，每页展示5条
select e.*, d.name from emp e left join dept d on e.dept_id = d.id limit 10,5;

-- 起始索引=（页禹-1）*每页展示记录数
```

###### cn/dzj/pojo/PageResult.java

```java
package cn.dzj.pojo;
import lombok.Data;
import java.util.List;
/**
 *分页结果封装类
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageResult<T> {
    private Long total;
    private List<T> rows;
}
```

##### 小结

<figure><img src="/AiJavaWeb/imgs/jwai08-31.png"><figcaption>图31 分页查询前后端交互小结</figcaption></figure>

##### ① 分页查询-原始方式

* 三层架构中，每一层的职责如下：

<figure><img src="/AiJavaWeb/imgs/jwai08-32.png"><figcaption>图32 三层架构每层职责</figcaption></figure>

###### cn/dzj/controller/EmpController.java

```java
package cn.dzj.controller;

import cn.dzj.pojo.Emp;
import cn.dzj.pojo.PageResult;
import cn.dzj.pojo.Result;
import cn.dzj.service.EmpService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
// 员工控制器
@Slf4j
@RequestMapping("/emps")
@RestController
public class EmpController {
    @Autowired
    private EmpService empService;
    /**
     * 分页查询
     */
    @GetMapping
    public Result page(@RequestParam(defaultValue = "1") Integer page,
                       @RequestParam (defaultValue = "10") Integer pageSize) {
      log.info("分页查询：{},{}",page,pageSize);
      PageResult<Emp> pageResult = empService.page(page, pageSize);
      return Result.success(pageResult);
    }
}
```

###### cn/dzj/service/EmpService.java

```java
package cn.dzj.service;
import cn.dzj.pojo.Emp;
import cn.dzj.pojo.PageResult;

public interface EmpService {
    PageResult<Emp> page(Integer page, Integer pageSize);
}
```

###### cn/dzj/service/impl/EmpServiceImpl.java

```java
package cn.dzj.service.impl;
import cn.dzj.mapper.EmpMapper;
import cn.dzj.pojo.Emp;
import cn.dzj.pojo.PageResult;
import cn.dzj.service.EmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class EmpServiceImpl implements EmpService {
    @Autowired
    private EmpMapper empMapper;
    @Override
    public PageResult<Emp> page(Integer page,Integer pageSize){
        //1，调用mapper接口，查询总记录数
        Long total = empMapper.count();

        //2．调用mapper接口，查询结果列表
        Integer start = (page - 1) * pageSize;
        List<Emp> rows = empMapper.list(start,pageSize);

        //3.封装结果PageResult
        return new PageResult<Emp>(total, rows);
    }
}
```

###### cn/dzj/mapper/EmpMapper.java

```java
package cn.dzj.mapper;

import cn.dzj.pojo.Emp;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;

/**
 * 员工管理
 */
@Mapper
public interface EmpMapper {
    /**
     * 查询总记录数
     */
    @Select("select count(*) from emp e left join dept d on e.dept_id = d.id")
    Long count();
    
    /**
     * 分页查询
     */
    @Select("select e.*, d.name deptName from emp e left join dept d on e.dept_id = d.id" +
            " order by e.update_time desc limit #{start},#{pageSize}")
    List<Emp> list(Integer start, Integer pageSize);

}
```

<figure><img src="/AiJavaWeb/imgs/jwai08-33.png"><figcaption>图33 <mark>分页查询-原始方式验证</mark></figcaption></figure>

<figure><img src="/AiJavaWeb/imgs/jwai08-34.png"><figcaption>图34 分页查询-原始方式小结</figcaption></figure>



##### ② PageHelper

PageHelper是第三方提供的在Mybatis框架中用来实现分页的插件，用来<mark>简化分页操作，提高开发效率</mark>。

<figure><img src="/AiJavaWeb/imgs/jwai08-35.png"><figcaption>图35 原始分页查询方式与PageHelper分页查询</figcaption></figure>



```java
//查询员工数据
@Select("select e.* from emp e ... ")
public List<Emp> list();
```



```java
public PageResult<Emp> page(Integer page, Integer pageSize) {
    //1. 设置分页参数
    PageHelper.startPage(page, pageSize);
    //2. 调用Mapper接口方法
    List<Emp> empList = empMapper.list();
    //3. 解析并封装结果
    return new PageResult(...);
}
```



##### 使用步骤：

* ①引入PageHelper的依赖

* ②定义Mapper接口的查询方法(无需考虑分页)

* ③在Service方法中实现分页查询



```xml
<!-- 分页插件PageHelper -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.4.7</version>
</dependency>
```

###### cn/dzj/controller/EmpController.java  修改page方法中empService调用page2方法

```java
    @GetMapping
    public Result page(@RequestParam(defaultValue = "1") Integer page,
                       @RequestParam (defaultValue = "10") Integer pageSize) {
      log.info("分页查询：{},{}",page,pageSize);
      //  PageResult<Emp> pageResult = empService.page(page, pageSize);
      PageResult<Emp> pageResult = empService.page2(page, pageSize);
      return Result.success(pageResult);
    }
```

###### cn/dzj/service/impl/EmpServiceImpl.java 添加下列方法

```java
    // PageHelper方式
    @Override
    public PageResult<Emp> page2(Integer page, Integer pageSize){
        //1. 设置分页参数
        PageHelper.startPage(page, pageSize);
        //2. 调用Mapper接口方法
        List<Emp> empList = empMapper.list2();
        //3. 解析并封装结果PageResult
        Page<Emp> empPage = (Page<Emp>) empList;
        return new PageResult<Emp>(empPage.getTotal(), empPage.getResult());
    }
```

###### cn/dzj/mapper/EmpMapper.java 添加下列方法

```java
    /**
     * 分页查询--PageHelper方式 不需要考虑分页参数
     */
    @Select("select e.*, d.name deptName from emp e left join dept d on e.dept_id = d.id" +
            " order by e.update_time desc")
    List<Emp> list2();
```

<figure><img src="/AiJavaWeb/imgs/jwai08-36.png"><figcaption>图36 <mark>PageHelper分页查询联调测试</mark></figcaption></figure>



<figure><img src="/AiJavaWeb/imgs/jwai08-37.png"><figcaption>图37 PageHelper分页查询</figcaption></figure>

##### PageHelper实现机制

<figure><img src="/AiJavaWeb/imgs/jwai08-38.png"><figcaption>图38 PageHelper分页查询实现机制</figcaption></figure>

##### 小结

PageHelper实现机制，对紧跟在其后的第一条SQL语句进行分页处理--分成下列两条语句：

* select count(0) from emp e ...
* select ... from emp e ... limit ?,?

注意事项

* <mark>SQL语句结尾不要加分号(;)</mark>
* <mark>PageHelper只会对紧跟在其后的第一条SQL语句进行分页处理</mark>

<figure><img src="/AiJavaWeb/imgs/jwai08-39.png"><figcaption>图39 PageHelper分页查询小结</figcaption></figure>



#### 3.3 条件分页查询

##### 条件分页查询-分析

<figure><img src="/AiJavaWeb/imgs/jwai08-40.png"><figcaption>图40 分页条件查询需求</figcaption></figure>

##### 条件分页查询-思路

<figure><img src="/AiJavaWeb/imgs/jwai08-41.png"><figcaption>图41 分页条件查询分层</figcaption></figure>

##### 条件分页查询实现

###### cn/dzj/controller/EmpController.java  修改page方法调用empService.page3(...)

```java
    /**
     * 分页查询
     */
    @GetMapping
    public Result page(@RequestParam(defaultValue = "1") Integer page,
                       @RequestParam(defaultValue = "10") Integer pageSize,
                       String name, Integer gender,
                       @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
                       @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end) {
        //  log.info("分页查询：{},{}",page,pageSize);
        //  PageResult<Emp> pageResult = empService.page(page, pageSize);
        //  PageResult<Emp> pageResult = empService.page2(page, pageSize);
        log.info("分页查询:{},{},{},{},{},{}", page, pageSize, name, gender, begin, end);
        PageResult<Emp> pageResult = empService.page3(page, pageSize, name, gender, begin, end);
        return Result.success(pageResult);
    }
```

###### cn/dzj/service/EmpService.java 添加下列方法

```java
    PageResult<Emp> page3(Integer page, Integer pageSize, String name, Integer gender, LocalDate begin, LocalDate end);
```

###### cn/dzj/service/impl/EmpServiceImpl.java 添加下列方法

```java
    // PageHelper方式
    @Override
    public PageResult<Emp> page3(Integer page, Integer pageSize, String name, Integer gender, LocalDate begin, LocalDate end){
        //1. 设置分页参数
        PageHelper.startPage(page, pageSize);
        //2. 调用Mapper接口方法
        List<Emp> empList = empMapper.list3(name, gender, begin, end);
        //3. 解析并封装结果PageResult
        Page<Emp> empPage = (Page<Emp>) empList;
        return new PageResult<Emp>(empPage.getTotal(), empPage.getResult());
    }
```



###### cn/dzj/mapper/EmpMapper.java 添加下列方法

```java
	/**
     * 分页条件查询
     */
    List<Emp> list3( String name, Integer gender, LocalDate begin, LocalDate end);
```

###### src/main/resources/cn/dzj/mapper/EmpMapper.xml

```xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.dzj.mapper.EmpMapper">
    <select id="list3" resultType="cn.dzj.pojo.Emp">
        select e.*, d.name deptName from emp e left join dept d on e.dept_id = d.id
        where
        e.name like concat('%',#{name},'%')
        and e.gender = #{gender}
        and e.entry_date between #{begin} and #{end}
        order by e.update_time desc
    </select>
</mapper>
```

<figure><img src="/AiJavaWeb/imgs/jwai08-42.png"><figcaption>图42 <mark>分页条件查询分层验证</mark></figcaption></figure>

##### 分页条件查询分层--小结

<figure><img src="/AiJavaWeb/imgs/jwai08-43.png"><figcaption>图43 分页条件查询分层小结</figcaption></figure>

##### 小结

@RequestParam注解的使用场景?

* @RequestParam 接收请求参数，参数名不一致时
* @RequestParam 设置请求参数的默认值

@DateTimeFormat的使用场景?

* 用于接收日期时间类型的参数，指定日期格式 @DateTimeFormat(pattern="yyyy-MM-dd")

<figure><img src="/AiJavaWeb/imgs/jwai08-44.png"><figcaption>图44 小结</figcaption></figure>

##### 程序优化

<figure><img src="/AiJavaWeb/imgs/jwai08-45.png"><figcaption>图45 请求参数较多存在的问题</figcaption></figure>



##### 请求参数接收优化-方案

<figure><img src="/AiJavaWeb/imgs/jwai08-46.png"><figcaption>图46 请求参数接受优化方案</figcaption></figure>

* 如果controller方法的参数较多，且未来可能继续增加，这会使得方法签名变得复杂难以维护，此时可以考虑将多个请求参数封装为一个对象。

* 请求参数：/emps?name=张&gender=1&begin=2007-09-01&end=2022-09-01&page=1&pageSize=10

###### cn/dzj/pojo/EmpQueryParam.java

```java
package cn.dzj.pojo;

import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;
import java.time.LocalDate;

@Data
public class EmpQueryParam {
    private Integer page = 1; // 当前页码
    private Integer pageSize = 10; // 每页记录数
    private String name; // 员工姓名
    private Integer gender; // 员工性别
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private LocalDate begin; // 入职日期
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private LocalDate end; // 入职日期
}
```

###### cn/dzj/controller/EmpController.java  修改page方法

```java
    /**
     * 分页查询
     */
/*    @GetMapping
    public Result page(@RequestParam(defaultValue = "1") Integer page,
                       @RequestParam(defaultValue = "10") Integer pageSize,
                       String name, Integer gender,
                       @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
                       @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end) {
        //  log.info("分页查询：{},{}",page,pageSize);
        //  PageResult<Emp> pageResult = empService.page(page, pageSize);
        //  PageResult<Emp> pageResult = empService.page2(page, pageSize);
        log.info("分页查询:{},{},{},{},{},{}", page, pageSize, name, gender, begin, end);
        PageResult<Emp> pageResult = empService.page3(page, pageSize, name, gender, begin, end);
        return Result.success(pageResult);
    }*/

    /**
     * 分页条件查询 -- 封装参数 -- 接受参数优化
     */
    @GetMapping
    public Result page(EmpQueryParam empQueryParam){
        log.info("分页查询，参数：{}",empQueryParam);
        PageResult<Emp> pageResult = empService.page4(empQueryParam);
        return Result.success(pageResult);
    }
```

###### cn/dzj/service/EmpService.java 添加page4方法

```java
    PageResult<Emp> page4(EmpQueryParam empQueryParam);
```

###### cn/dzj/service/impl/EmpServiceImpl.java 添加page4方法

```java
    @Override
    public PageResult<Emp> page4(EmpQueryParam empQueryParam) {
        //1. 设置分页参数
        PageHelper.startPage(empQueryParam.getPage(), empQueryParam.getPageSize());
        //2. 调用Mapper接口方法
        List<Emp> empList = empMapper.list4(empQueryParam);
        //3. 解析并封装结果PageResult
        Page<Emp> empPage = (Page<Emp>) empList;
        return new PageResult<Emp>(empPage.getTotal(), empPage.getResult());
    }
```

###### cn/dzj/mapper/EmpMapper.java 添加list4方法

```java
    /**
     * 分页条件查询 -- 接受参数优化
     */
    List<Emp> list4(EmpQueryParam empQueryParam);
```

###### src/main/resources/cn/dzj/mapper/EmpMapper.xml  添加list4查询

```xml
    <select id="list4" resultType="cn.dzj.pojo.Emp">
        select e.*, d.name deptName from emp e left join dept d on e.dept_id = d.id
        where
        e.name like concat('%',#{name},'%')
        and e.gender = #{gender}
        and e.entry_date between #{begin} and #{end}
        order by e.update_time desc
    </select>
```

<figure><img src="/AiJavaWeb/imgs/jwai08-47.png"><figcaption>图47 <mark>分页条件查询--接受参数优化验证</mark></figcaption></figure>

##### 小结

queryString查询参数接收方式  /emps?name=xxx&gender=xxx

* 如果参数较少，controller方法中定义一个一个形参接收
* 如果参数较多，controller方法中定义一个对象接收

<figure><img src="/AiJavaWeb/imgs/jwai08-48.png"><figcaption>图48 请求参数接受优化小结</figcaption></figure>



#### 程序优化 -- 存在动态查询的需求

<figure><img src="/AiJavaWeb/imgs/jwai08-49.png"><figcaption>图49 存在的问题：动态查询需求</figcaption></figure>



##### 动态SQL

* 随着用户的输入或外部条件的变化而变化的SQL语句，我们称为 动态SQL。
* ```<if>```：判断条件是否成立，如果条件为true，则拼接SQL。
* ```<where>```：根据查询条件，来生成where关键字，并会自动去除条件前面多余的and或or。

<figure><img src="/AiJavaWeb/imgs/jwai08-50.png"><figcaption>图50 动态SQL</figcaption></figure>

##### src/main/resources/cn/dzj/mapper/EmpMapper.xml 修改list4的查询语句

```xml
    <select id="list4" resultType="cn.dzj.pojo.Emp">
        select e.*, d.name deptName from emp e left join dept d on e.dept_id = d.id
<!--        where-->
<!--        e.name like concat('%',#{name},'%')-->
<!--        and e.gender = #{gender}-->
<!--        and e.entry_date between #{begin} and #{end}-->
        <where>
            <if test="name != null and name != ''">
                e.name like concat('%',#{name},'%')
            </if>
            <if test="gender != null">
                and e.gender = #{gender}
            </if>
            <if test="begin != null and end != null">
                and e.entry_date between #{begin} and #{end}
            </if>
        </where>
        order by e.update_time desc
    </select>
```

<figure><img src="/AiJavaWeb/imgs/jwai08-51.png"><figcaption>图51 <mark>分页条件查询--动态SQL验证</mark></figcaption></figure>

##### 小结

Mybatis中动态SQL的使用场景?

* 如果SQL语句是不固定的,是随着用户的输入或外部条件的变化而变化的

MyBatis中动态SQL的<if>及<where>标签的作用?

* ```<if>```标签：条件判断，如果条件成立，则拼接对应的sql片段
* ```<where>```：根据查询条件，来生成where关键字，并会自动去除条件前面多余的and或or。

<figure><img src="/AiJavaWeb/imgs/jwai08-52.png"><figcaption>图52 动态SQL小结</figcaption></figure>



①②③④⑤⑥⑦⑧⑨⑩