day12 员工管理实战

day12

计划

1. 多表关系设计（一对一、一对多、多对多）
2. 多表查询SQL（内连接、外连接、子查询）
3. 员工列表查询功能实现
4. 分页查询（原始方式 vs PageHelper插件）
5. 动态SQL实现条件查询

笔记

1.多表关系设计

三种表关系

关系	说明	实现方式	示例
一对多	一个部门下有多个员工	多的一方添加外键字段	dept(1) → emp(N)
一对一	一个用户对应一张身份证	任意一方加外键+UNIQUE	user(1) ↔ user_card(1)
多对多	一个学生可选多门课程	建立中间表，两个外键	student ↔ course

一对多（部门与员工）

部门表（父表/一方）：

CREATE TABLE dept (
  id int unsigned PRIMARY KEY AUTO_INCREMENT,
  name varchar(10) NOT NULL UNIQUE,
  create_time datetime,
  update_time datetime
);

员工表（子表/多方）：

create table emp(
    id int unsigned primary key auto_increment,
    username varchar(20) not null unique,
    password varchar(50) default '123456',
    name varchar(10) not null,
    gender tinyint unsigned not null,
    phone char(11) not null unique,
    job tinyint unsigned,
    salary int unsigned,
    image varchar(300),
    entry_date date,
    dept_id int unsigned,  -- 外键，关联dept表的id
    create_time datetime,
    update_time datetime
);

一对一（用户基本信息与身份信息）

-- 用户基本信息表
create table tb_user(id int, name varchar(10), ...);

-- 用户身份信息表
create table tb_user_card(
    id int primary key,
    user_id int unsigned not null unique,  -- UNIQUE保证一对一
    ...
    constraint fk_user_id foreign key (user_id) references tb_user(id)
);

要点： 外键设置为 UNIQUE 即可实现一对一。

多对多（学生与课程）

-- 中间表：学生选课关系
create table tb_student_course(
   id int auto_increment primary key,
   student_id int not null,
   course_id int not null,
   constraint fk_courseid foreign key (course_id) references tb_course(id),
   constraint fk_studentid foreign key (student_id) references tb_student(id)
);

要点： 中间表至少包含两个外键字段，分别关联两方主键。

物理外键 vs 逻辑外键

对比项	物理外键（foreign key）	逻辑外键
概念	使用foreign key定义外键关联	在业务层逻辑中解决关联
优点	保证数据完整性和一致性	不影响增删改效率，适合分布式
缺点	影响增删改效率，不适合分布式/集群	需要业务层保证数据一致性
使用	学习阶段使用	企业开发首选

结论： 实际开发中基本都使用逻辑外键，物理外键很少使用。

---

2.多表查询

笛卡尔积

多表查询时，不加条件会产生笛卡尔积（两表所有记录的组合），需要通过连接条件消除无效数据。

-- 笛卡尔积（180条 = 30员工 × 6部门）
select * from emp, dept;

-- 加连接条件消除笛卡尔积
select * from emp, dept where emp.dept_id = dept.id;

内连接

隐式内连接：

select 字段列表 from 表1, 表2 where 连接条件;

-- 示例
select e.id, e.name, d.name 
from emp e, dept d 
where e.dept_id = d.id;

显式内连接：

select 字段列表 from 表1 inner join 表2 on 连接条件;

-- 示例
select e.id, e.name, d.name 
from emp e inner join dept d on e.dept_id = d.id;

要点：

• 内连接只查询两表交集部分的数据
• 没有部门的员工（dept_id为NULL）不会被查询出来
• inner 关键字可省略

外连接

类型	语法	说明
左外连接	left join ... on ...	查询左表所有数据 + 右表匹配数据
右外连接	right join ... on ...	查询右表所有数据 + 左表匹配数据

-- 左外连接：查询所有员工（包括无部门的）及其部门名称
select e.name, d.name 
from emp e left join dept d on e.dept_id = d.id;

-- 右外连接：查询所有部门及其员工
select e.name, d.name 
from emp e right join dept d on e.dept_id = d.id;

要点：

• 外连接保留某一方所有数据，即使不满足连接条件
• 左外连接和右外连接可通过交换表的位置互相转换
• 员工列表查询应使用左外连接（保留所有员工）

子查询

SQL中嵌套select语句，称为子查询。

类型	结果	常用操作符	示例场景
标量子查询	一行一列（单值）	=, >, <, >=, <=	查询最早入职的员工
列子查询	一列多行	in, not in	查询某部门的所有员工
行子查询	一行多列	=, in	查询与某人薪资和职位相同的员工
表子查询	多行多列	作为临时表	查询每部门最高薪资的员工

标量子查询示例：

-- 查询最早入职的员工
select * from emp 
where entry_date = (select min(entry_date) from emp);

列子查询示例：

-- 查询"教研部"和"咨询部"的所有员工
select * from emp 
where dept_id in (
    select id from dept where name = '教研部' or name = '咨询部'
);

行子查询示例：

-- 查询与"李忠"的薪资及职位都相同的员工
select * from emp 
where (salary, job) = (
    select salary, job from emp where name = '李忠'
);

表子查询示例：

-- 获取每个部门中薪资最高的员工
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;

---

3.员工列表查询实现

3.1 实体类

Emp员工实体类：

@Data
public class Emp {
    private Integer 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;     // 部门名称（多表查询结果封装）
}

PageResult分页结果封装类：

@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageResult<T> {
    private Long total;    // 总记录数
    private List<T> rows; // 当前页数据列表
}

3.2 基础实现（左外连接多表查询）

Mapper接口：

@Mapper
public interface EmpMapper {
    /** 查询所有员工及其部门名称 */
    @Select("select e.*, d.name as deptName 
             from emp e left join dept d on e.dept_id = d.id")
    List<Emp> list();
}

要点：

• 使用 left join 保留所有员工（包括dept_id为NULL的）
• SQL中给部门名称起别名 deptName，与Java属性名对应
• 开启驼峰映射后，dept_name → deptName 自动转换

---

4.分页查询

4.1 原始方式

Mapper接口：

@Mapper
public interface EmpMapper {
    // 查询总记录数
    @Select("select count(*) from emp e left join dept d on e.dept_id = d.id")
    Long count();
    
    // 分页查询（limit 起始索引, 每页条数）
    @Select("select e.*, d.name deptName from emp e left join dept d on e.dept_id = d.id 
             limit #{start}, #{pageSize}")
    List<Emp> list(Integer start, Integer pageSize);
}

Service实现：

public PageResult page(Integer page, Integer pageSize) {
    Long total = empMapper.count();           // 1. 查总数
    Integer start = (page - 1) * pageSize;     // 2. 计算起始索引
    List<Emp> empList = empMapper.list(start, pageSize); // 3. 查列表
    return new PageResult(total, empList);     // 4. 封装结果
}

分页公式： 开始索引 = (当前页码 - 1) × 每页条数

4.2 PageHelper分页插件（推荐）

1. 引入依赖：

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.4.7</version>
</dependency>

2. application.yml配置：

pagehelper:
  reasonable: true          # 分页合理化（负数页码查第1页，超范围查最后一页）
  helper-dialect: mysql      # 指定数据库方言

3. Mapper接口（只需一条SQL）：

@Mapper
public interface EmpMapper {
    @Select("select e.*, d.name deptName 
             from emp e left join dept d on e.dept_id = d.id")
    List<Emp> list();  // 无需传分页参数
}

4. Service实现：

public PageResult page(Integer page, Integer pageSize) {
    // 1. 设置分页参数（紧跟着的第一条SQL会被分页）
    PageHelper.startPage(page, pageSize);
    
    // 2. 执行查询（PageHelper自动追加count和limit）
    List<Emp> empList = empMapper.list();
    Page<Emp> p = (Page<Emp>) empList;
    
    // 3. 封装结果
    return new PageResult(p.getTotal(), p.getResult());
}

PageHelper核心要点：

• PageHelper.startPage(page, pageSize) 之后的第一条SQL会被自动分页
• PageHelper自动执行两条SQL：① count(0) 查总数 ② limit 查分页数据
• SQL结尾不要加分号（;），否则可能导致分页失效
• Page 对象继承自 List，可用 getTotal() 和 getResult() 获取分页信息

---

5.条件分页查询

5.1 多参数接收

Controller：

@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) {  // 结束日期
    
    PageResult pageResult = empService.page(page, pageSize, name, gender, begin, end);
    return Result.success(pageResult);
}

注意： @DateTimeFormat 用于将字符串日期转换为 LocalDate 对象。

5.2 动态SQL（XML映射文件）

EmpMapper.xml：

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhang.mapper.EmpMapper">
    <select id="list" resultType="com.zhang.pojo.Emp">
        select e.*, d.name deptName 
        from emp e left join dept d on e.dept_id = d.id
        <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>
</mapper>

Mapper接口：

@Mapper
public interface EmpMapper {
    List<Emp> list(String name, Integer gender, LocalDate begin, LocalDate end);
}

5.3 动态SQL标签说明

标签	作用	示例
<if>	条件成立时拼接SQL	<if test="name != null">
<where>	自动添加where关键字，去除多余的and/or	包裹多个<if>条件
<set>	UPDATE语句中去除尾部多余逗号	-
<foreach>	遍历集合/数组，常用于in查询	in (1,2,3)

<where> 标签的作用：

• 自动在SQL后追加 where 关键字
• 自动去除第一个条件前多余的 and 或 or
• 如果没有任何条件，不追加 where

5.4 参数封装优化

当请求参数较多时，可以封装为实体类：

EmpQueryParam参数类：

@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;
}

Controller简化为：

@GetMapping
public Result page(EmpQueryParam param) {  // 一个对象接收所有参数
    PageResult pageResult = empService.page(param);
    return Result.success(pageResult);
}

Mapper接口简化为：

List<Emp> list(EmpQueryParam param);  // 传递整个参数对象

---

6.核心知识点总结

知识点	说明
一对多	多方加外键，关联一方主键（如emp.dept_id → dept.id）
一对一	任意一方加外键+UNIQUE约束
多对多	建立中间表，两个外键分别关联
物理外键	foreign key，保证完整性但影响性能，少用
逻辑外键	业务层处理关联，企业开发首选
笛卡尔积	多表不加条件的组合，需通过连接条件消除
内连接	只查两表交集，inner join
左外连接	保留左表所有数据，left join
右外连接	保留右表所有数据，right join
标量子查询	结果为单值，用 = 比较
列子查询	结果为一列多行，用 in 比较
行子查询	结果为一行多列，用 = 比较
表子查询	结果为多行多列，作为临时表
LIMIT分页	limit 起始索引, 每页条数
PageHelper	MyBatis分页插件，自动实现count+limit
PageHelper.startPage	紧跟其后的第一条SQL自动分页
动态SQL	<if> 条件拼接、<where> 智能处理
@DateTimeFormat	字符串日期 → LocalDate
参数封装	多参数封装为实体类，简化方法签名