黑马Tlias JavaWeb后台管理系统 08 后端实战 员工管理 查询

于 2024-12-31 18:28:37 发布
阅读量145 收藏
点赞数
分类专栏： 黑马 JavaWeb Tlias后台管理系统 文章标签： 数据库 前端
原文链接：https://heuqqdmbyk.feishu.cn/wiki/XXQTwBpWLiFDzjkpXShcXt8lnYg
版权

从原型可知，查询员工信息时，除了要展示 姓名、性别、头像、职位、入职日期、最后操作时间这些员工信息外，还要展示出所属部门，这就会涉及到多表操作。

多表关系

  • 一对多
  • 多对多
  • 一对一

一对多

  • 场景：部门与员工的关系（一个部门下有多个员工）
  • 部门管理的页面原型：

  • 员工管理的页面原型：

由于一个部门下会关联多个员工。而一个员工是归属于某一个部门的。那么此时我们就需要在员工表emp中增加一个字段dept_id来标识这个员工属于哪一个部门，dept_id关联的是dept的id。如下所示：

上述的emp员工表的dept_id字段，关联的是dept部门表的id。部门表是一的一方，也被称为父表，员工表是多的一方，称之为子表。

逻辑是在数据表中多的一方添加字段，来关联一的一方的主键。

我们将上述的两张表创建出来

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,'学工部','2024-09-25 09:47:40','2023-04-25 09:47:40'),
                      (2,'教研部','2024-09-25 09:47:40','2024-10-09 15:17:04'),
                      (3,'咨询部','2024-09-25 09:47:40','2024-11-30 21:26:24'),
                      (4,'就业部','2024-09-25 09:47:40','2024-09-25 09:47:40'),
                      (5,'人事部','2024-09-25 09:47:40','2024-09-25 09:47:40'),
                      (6,'行政部','2024-11-30 20:56:37','2024-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(50) 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(300) comment '头像',
  entry_date date comment '入职日期',
  dept_id int unsigned comment '部门ID',
  create_time datetime comment '创建时间',
  update_time datetime comment '修改时间'
) comment '员工表';


INSERT INTO emp VALUES 
    (1,'shinaian','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'),
    (30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'01.png','2020-03-01',NULL,'2023-10-20 16:35:33','2023-10-20 16:36:31'),
    (36,'linghuchong','123456','令狐冲',1,'18809091212',2,6800,'1.png','2023-10-19',2,'2023-10-20 20:44:54','2023-11-09 09:41:04');

多表问题分析

问题

表结构创建完毕后，我们看到两张表的数据分别为：

我们看到，在3号部门下，是关联的有7个员工。 当删除了3号部门后，数据变为：

3号部门被删除了，但是依然还有7个员工是属于3号部门的。 此时：就出现数据的不完整、不一致了。

分析

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

  • 外键约束：让两张表的数据建立连接，保证数据的一致性和完整性。
  • 关键字：foreign key

语法：

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


-- 建完表后，添加外键
alter table  表名  add constraint  外键名称  foreign key(外键字段名) references 主表(主表列名);
  1. SQL语句操作
-- 修改表： 添加外键约束
alter table emp  add  constraint  fk_dept_id  foreign key (dept_id)  references  dept(id);
  1. 图形化界面操作

右键emp表，选择Modify Table

右键dept_id，新建Foreign Key

  • Name：外键约束名
  • Target Table：关联的目标表名
  • Column Name：外键字段
  • Target Name：关联的目标表字段
  • Preview：自动生成的SQL语句

此时数据库报错，不允许删除

物理外键与逻辑外键

  • 物理外键
    • 概念：使用foreign key定义外键关联另外一张表
    • 缺点：
      • 影响增、删、改的效率（需要检查外键关系）
      • 仅用于单节点数据库，不适用于分布式、集群场景
      • 容易引发数据库的死锁问题，消耗性能
  • 逻辑外键
    • 概念：在业务层逻辑中，解决外键关联
    • 不容易引发物理外键的问题

:::info
现在企业开发中很少使用物理外键，甚至在一些数据库开发规范中会明确指出禁止使用物理外键foreign key

:::

一对一

一对一关系表通常是用来做单表的拆分，也就是将一张大表拆分成两张小表，将大表中的一些基础字段放在一张表中，将其他字段放在另外一张表中，以此来提高数据的操作效率。

应用场景：用户表（基本信息+身份信息）

  • 基本信息：用户的ID、姓名、性别、手机号、学历
  • 身份信息：民族、生日、身份证号、身份证签发机关、身份证有效期

如果在业务系统当中，对用户的基本信息查询频率特别高，但是对用户的身份信息查询频率很低，此时出于提高查询效率的考虑，我就可以将这张大表拆分。

其实一对一可以看成特殊的一对多，在任意一方添加外键就可以了。

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);

在任意一方加入外键，关键另一方的主键，并且设置外键为唯一的（UNIQUE）

多对多

案例：学生与课程的关系

  • 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
  • 实现关系：建立第三张中间表，中间表至少包含两个外键，分别关联两方的主键

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_course(
   id int auto_increment comment '主键' primary key,
   student_id int not null comment '学生ID',
   course_id  int not null comment '课程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_course(student_id, course_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);

多对多：需要建立一张中间表，中间表中有两个外键字段，分别关联两方的主键。

案例

需求：根据页面原型设计员工管理模块的表结构

步骤：

  • 根据原型及需求文档，分析各个模块涉及到的表结构 以及表结构之间的关系
  • 分析各个表结构中具体的字段及约束

分析：

  • 部门管理

涉及到一张部门表，已经设计过了

  • 员工管理

上述在员工列表查询的页面原型，当我们点击 “新增员工” 按钮时，会弹出一个新增员工的表单，表单展示形式如下：

部门与员工之间的关系是一对多关系。

员工还有工作经历，而每个员工可以添加多个工作经历。所以工作经历可以再设计一张表，员工与工作经历是一对多关系。

最终的表结构如下：

我们使用了逻辑外键，而非物理外键foreign key

-- 部门表
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(50) 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(300) comment '头像',
  entry_date date comment '入职日期',
  dept_id int unsigned comment '部门ID',  -- 关联的是dept部门表的ID
  create_time datetime comment '创建时间',
  update_time datetime comment '修改时间'
) comment '员工表';

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

多表查询

概述

数据准备

-- 部门管理
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 '部门表' ;

insert into dept (id, name, create_time, update_time) values
        (1,'学工部',now(),now()),
        (2,'教研部',now(),now()),
        (3,'咨询部',now(),now()),
        (4,'就业部',now(),now()),
        (5,'人事部',now(),now());


-- 员工管理
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:讲师,3:学工主管,4:教研主管,5:咨询师',
    salary int unsigned comment '薪资',
    image varchar(300) comment '头像',
    entry_date date comment '入职日期',
    dept_id int unsigned COMMENT '关联的部门ID',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间'
) comment '员工表';


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

介绍

多表查询：查询时从多张表中获取所需数据

单表查询的SQL语句：select 字段列表 from 表名;

那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;

查询用户表和部门表中的数据：

select * from  emp , dept;

查询的到一个150条记录的结果集，这是员工表所有记录（30行）和部门表所有记录（5行）的所有组合情况，这种现象称之为笛卡尔积

**笛卡尔积：**笛卡尔乘积是指在数学中，两个集合(A集合和B集合)的所有组合情况。

在多表查询时，需要消除无效的笛卡尔积，只保留表关联部分的数据。

那么在SQL语句中我们要消除无效的笛卡尔积，只需要给多表查询加上连接查询的条件即可。

select * from emp , dept where emp.dept_id = dept.id ;

由于id为29、30的员工没有dept_id字段值，所以在多表查询时根据连接查询的条件没有查询到。

分类

多表查询可以分为：

  1. 连接查询
  • 内连接：相当于查询A、B交集部分数据
  • 外连接：
    • 左外连接：查询左表的所有数据（包括两张表交集部分数据）
    • 右外连接：查询右表的所有数据（包括两张表交集部分数据）
  1. 子查询

内连接

从语法上可以分为：

  • 隐式内连接
select  字段列表   from   表1 , 表2   where  条件 ... ;
  • 显式内连接
select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;
  1. 案例一：查询所有员工的ID，姓名，及所属的部门名称

隐式

select emp.id, emp.name, dept.name from emp , dept 
where emp.dept_id = dept.id;

显式

select emp.id, emp.name, dept.name from emp inner join dept 
on emp.dept_id = dept.id;
  1. 案例二：查询 性别为男, 且工资 高于8000 的员工的ID, 姓名, 及所属的部门名称

隐式

select emp.id, emp.name, dept.name from emp , dept 
where emp.dept_id = dept.id and emp.gender = 1 and emp.salary > 8000;

显式

select emp.id, emp.name, dept.name from emp inner join dept 
on emp.dept_id = dept.id where emp.gender = 1 and emp.salary > 8000;

:::info
在多表联查时，我们指定字段时需要再字段名前面加上表名，来指定具体是哪一张的字段

例如：emp.dept_id

:::

给表起别名简化书写：

select  字段列表 from 表1 as 别名1 , 表2 as  别名2  where  条件 ... ;

select  字段列表 from 表1 别名1 , 表2  别名2  where  条件 ... ;  -- as 可以省略

select e.id, e.name, d.name from emp as e , dept as d 
where e.dept_id = d.id and e.gender = 1 and e.salary > 8000;

:::info
一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

:::

外连接

左外连接

select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

右外连接

select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;
  1. 查询员工表所有员工姓名和对应的部门名称（左外）
-- 左外连接：以left join关键字左边的表为主表，查询主表中所有数据，以及和主表匹配的右边表中的数据
select e.name , d.name  
from emp as e left join dept as d 
on e.dept_id = d.id ;

  1. 查询部门表所有部门名称，和对应的员工名称（右外）
-- 右外连接：以right join关键字右边的表为主表，查询主表中所有数据，以及和主表匹配的左边表中的数据
select e.name , d.name 
from emp as e right join dept as d 
on e.dept_id = d.id;

  1. 查询工资高于8000的所有员工的姓名和对应的部门名称（左外）
select e.name , d.name 
from emp as e left join dept as d 
on e.dept_id = d.id where e.salary > 8000;

:::info
左外和右外是可以互相转换的，但是我们再日常开发时更偏向于左外连接

:::

子查询

介绍

嵌套select语句，称为嵌套查询，又称为子查询

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

最外层语句可以是insert/update/delete/select

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

  1. 标量子查询（结果为单个值[一行一列]）
  2. 列子查询（一列多行）
  3. 行子查询（一行多列）
  4. 表子查询（多行多列[相当于一张表]）

可以书写的位置：

  1. where
  2. from
  3. select

:::info
先对需求做拆分，明确具体的步骤，然后再逐条编写SQL语句，最终将多条SQL语句合并为一条。

:::

标量子查询

单个值（数字、字符串、日期等）

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

  1. 查询 最早入职 的员工信息
-- 1. 查询最早的入职时间
select min(entry_date) from emp;  -- 结果: 2000-01-01

-- 2. 查询入职时间 = 最早入职时间的员工信息
select * from emp where entry_date = '2000-01-01';

-- 3. 合并为一条SQL
select * from emp where entry_date = (select min(entry_date) from emp);

从本表中通过聚合函数查询到单个值，为所有员工中最早的入职时间。再查询所有的员工信息，通过where语句进行条件匹配。

  1. 查询在 阮小五 之后入职的员工信息
-- 1. 查询 "阮小五" 的入职日期
select entry_date from emp where name = '阮小五'; -- 结果: 2015-01-01

-- 2. 根据上述查询到的这个入职日期, 查询在该日期之后入职的员工信息
select * from emp where entry_date > '2015-01-01';

-- 3. 合并SQL为一条SQL
select * from emp where entry_date > (select entry_date from emp where name = '阮小五');

通过where匹配到 阮小五 所在的行，select查询entry_date这一列，得到单个值为 阮小五的入职日期。外层查询用where 和> 进行条件匹配。

列子查询

一列，可以是多行

操作符描述
in在指定的集合范围之内，多选一
not in不在指定的集合范围之内
  1. 查询 教研部 和 咨询部 的所有员工信息
-- 1. 查询 "教研部" 和 "咨询部" 的部门ID
select id from dept where name = '教研部' or name = '咨询部'; -- 结果: 3,2

-- 2. 根据上面查询出来的部门ID, 查询员工信息
select * from emp where dept_id in(3,2);

-- 3. 合并SQL为一条SQL语句
select * from emp where dept_id in (select id from dept where name = '教研部' or name = '咨询部');

因为要查询的是部门的名称，只在部门表里有；而想要得到的员工信息在员工表里。所以我们要找到员工表和部门表的一对多关系的键，也就是dept.id 和 emp.dept_id。首先查询部门id，通过where语句进行名称的匹配，or语句是有一个满足条件就可以匹配上，and语句是两个条件都要满足，而一个员工不可能对应两个部门（并且使用and需要条件变为”同时在教研部和咨询部工作的员工信息“，这显然不成立），所以这里使用or表示”在教研部或者咨询部工作的员工“。那么我们得到了两个id（3,2），再查询所有的员工信息，通过where语句条件限制，再通过in关键词进行是否存在的判断，判断员工表的dept_id是否存在于子查询得到的两个id中。

行子查询

一行，可以是多列

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

  1. 查询与 李忠 的薪资 和 职位都相同的员工信息
-- 1. 查询 "李忠" 的薪资和职位
select salary , job from emp where name = '李忠'; -- 结果: 5000, 5

-- 2. 根据上述查询到的薪资和职位 , 查询对应员工的信息
select * from emp where (salary, job) = (5000,5);

-- 3. 将两条SQL合并为一条SQL
select * from emp where (salary, job) = (select salary , job from emp where name = '李忠');

查询用where语句条件限制到 name为 李忠的员工的那一行，再select限制到salary和job这两列。外层查询用 where限制，where的内容为（salary, job），表示这两列的内容一起匹配，匹配到子查询的一行两列的内容（也就是两个单元格）。

表子查询

返回多行多列，常作为临时表

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

“每个部门”，也就是需要将部门由id分组，对每个分组的薪资salary作聚合函数max()处理。由于员工表里也有dept_id，所以我们只需要在员工表里查询，group by depy_id，表示由部门id进行分组，而想要由dept_id 进行分组，select语句后也必须跟上dept_id。

但这时候只select dept_id max(salary)是远远不够的，我们得到的是一份两列四行的表。两列为dept_id和聚合函数根据dept_id分组后得到的每个部门的最高薪资max(salary)；四行是因为我们的员工表数据只有dept_id为1、2、3和null（未分部门）的员工。我们需要根据得到的这张新表与原来的表进行匹配。那么怎么匹配呢？

如果只匹配max(salary)，那么就会把null也给匹配上，并且可能在不同部门的员工之间匹配。所以我们需要同时匹配dept_id和max(salary)。为了更加简洁，我们给emp取别名e，给临时表取别名a；a表中的max(salary)取别名 max_sal。联表查询e和a，用where条件限制原表和临时表的dept_id相同，并且原表的salary等于临时表的max_sal。

案例

根据需求，完成对多表查询SQL语句的编写

  1. 查询 教研部 性别为 男，且在 2011-05-01 之后入职的员工信息。

我的

select * from emp where dept_id = (select id from dept where name = '教研部')
                    and gender = 1 and entry_date > '2011-05-01';

答案

select e.* from emp as e , dept as d
           where e.dept_id = d.id and d.name = '教研部'
             and e.gender = 1 and e.entry_date > '2011-05-01';

结果相同。我使用了子查询匹配部门表的id，答案使用了联表查询。答案同时使用了别名，更加简洁。答案使用三个and相连，分别直接判断了 部门id是否匹配、部门名称是否为 教研部、性别是否为男、入职日期，这四个条件。

注意日期的匹配要加上单引号。

  1. 查询 工资 低于 公司平均工资 且 性别为男 的员工信息。

我的

select * from emp where salary < (select avg(salary) from emp) and gender = 1;

答案

select e.* from emp as e , dept as d where e.dept_id = d.id 
  and e.salary < (select avg(salary) from emp) and e.gender = 1;

答案作了联表查询，用部门id作了匹配。我的结果为15行，答案的结果为14行，多的那一行是因为有个符合条件的员工的dept_id为null。

  1. 查询 部门人数 超过 10人 的部门名称。

我的

select d.name from dept d ,
(select dept_id, count(id) c_id from emp group by dept_id) a
where a.c_id > 10 and d.id = a.dept_id;

答案

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

答案多select了一个count(*)数据，也就是部门人数。答案多使用了group by … having …的语句，简化了一条where后的大于条件。直接联表查询减少了子查询的查询返回值，更简化了。

where用于select或join子句，用于对原始数据进行筛选，满足条件的数据才会被进一步处理；

而having用于group by子句之后的聚合函数如sum()、avg()等，用于在对数据进行分组之后对分组结果进行筛选。

  1. 查询在 2010-05-01后入职，且薪资高于10000的 教研部 员工信息，并根据薪资 倒序排序。

我的

select * from dept d, emp e where e.entry_date > '2010-05-01' and e.salary > 10000
    and d.name = '教研部' order by salary DESC ;

答案

select * 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;

联表查询还是要对主键进行匹配。

  1. 查询 工资 低于 本部门平均工资 的员工信息
-- 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;

子查询每个部门的的平均工资，建临时表为a。用部门id匹配，原表中salary小于临时表a中avg_sal。

员工列表查询

在查询员工列表数据时，既需要查询 员工的基本信息，还需要查询员工所属的部门名称，涉及到多表查询的操作。

在查询员工列表数据时，既要考虑到搜索栏中的查询条件，还要考虑对查询的结果进行分页处理。

  • 准备工作
  • 分页查询
  • 条件分页查询

准备工作

需求：查询所有员工信息，并查询出部门名称。（涉及到的表：emp、dept）

基础代码准备

  1. 创建员工管理相关表结构
-- 员工表
create table emp(
    id int unsigned primary key auto_increment comment 'ID,主键',
    username varchar(20) not null unique comment '用户名',
    password varchar(50) 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(300) comment '头像',
    entry_date date comment '入职日期',
    dept_id int unsigned comment '部门ID',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间'
) comment '员工表';


INSERT INTO emp VALUES 
    (1,'shinaian','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 '工作经历';
  1. 准备emp表对应的实体类Emp、EmpExpr
package com.itheima.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; //部门名称
}


package com.itheima.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; //职位
}
  1. 准备Emp员工管理的基础结构，包括Controller、Service、Mapper

Mapper

package com.itheima.mapper;

import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;

@Mapper
public interface EmpMapper {

}

Service

package com.itheima.service;

public interface EmpService {
}

package com.itheima.service.impl;

import com.itheima.mapper.EmpMapper;
import com.itheima.service.EmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * 员工管理
 */
@Service
public class EmpServiceImpl implements EmpService {

    @Autowired
    private EmpMapper empMapper;

}

Controller

package com.itheima.controller;

import com.itheima.service.EmpService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RestController;

/**
 * 员工管理
 */
@Slf4j
@RestController
public class EmpController {

    @Autowired
    private EmpService empService;

}

SQL&Mapper接口

先考虑查询所有的员工数据 及其关联的部门名称。

左外连接实现。

  1. SQL语句
-- 查询所有的员工信息，如果员工关联了部门，也要查询出部门名称
select e.*, d.name as dept_name 
from emp e left join dept d on e.dept_id = d.id;
  1. Mapper接口方法定义

EmpMapper

@Mapper
public interface EmpMapper {

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

}

在上述SQL语句中我们给部门名称起了别名deptName，是因为接口文档中规定返回给前端的部门名称数据 必须叫 deptName。而我们需要将查询返回的每一条记录都封装到Emp对象中，那么就必须保证查询返回的字段名与属性名是一一对应的。

所以Emp类中也需要定义一个属性deptName用来封装部门名称。

我们可以编写一个单元测试，对上述的程序进行测试：

@SpringBootTest
class TliasWebManagementApplicationTests {

    @Autowired
    private EmpMapper empMapper;

    @Test
    public void testList(){
        List<Emp> empList = empMapper.list();
        empList.forEach(System.out::println);
    }
}

可以看到员工信息、员工关联的部门名称都查询出来了。

分页查询

分析

使用LIMIT关键字 格式为： limit 开始索引 每页显示的条数

select * from emp  limit 0,10;	-- 第一页
select * from emp  limit 10,10;	-- 第二页
select * from emp  limit 20,10;	-- 第三页

开始索引一直在改变，每页显示条数是固定的。

开始索引 = （当前页码 - 1） * 每页显示条数

我们基于页面原型，得出以下结论：

  1. 前端在请求服务器时传递的参数
  • 当前页码 page
  • 每页显示条数 pageSize
  1. 后端需要响应什么数据给前端
  • 所查询到的数据列表（存储到List集合中）
  • 总记录数

后台给前端返回的数据包含：List集合（数据列表）、total（总记录数）

而这两部分我们通常封装到PageResult对象中，并将该对象转换为json格式的数据响应回服务器。

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

接口描述

  1. 基本信息

请求路径：/emps

请求方式：GET

接口描述：该接口用于员工列表数据的条件分页查询

  1. 请求参数
参数名称是否必须示例备注
name否张姓名
gender否1性别 , 1 男 , 2 女
begin否2010/1/1范围匹配的开始时间(入职日期)
end否2020/1/1范围匹配的结束时间(入职日期)
page是1分页查询的页码，如果未指定，默认为1
pageSize是10分页查询的每页记录数，如果未指定，默认为10

请求数据样例：

/emps?name=张&gender=1&begin=2007-09-01&end=2022-09-01&page=1&pageSize=10
  1. 响应数据

参数格式：application/json

参数说明：

名称类型是否必须备注
codenumber必须响应码, 1 成功 , 0 失败
msgstring非必须提示信息
dataobject必须返回的数据
- totalnumber必须
- rowsobject []必须
- idnumber非必须
- usernamestring非必须
- namestring非必须
- passwordstring非必须
- gendernumber非必须
- imagestring非必须
- jobnumber非必须
- salarynumber非必须
- entryDatestring非必须
- deptIdnumber非必须
- deptNamestring非必须
- createTimestring非必须
- updateTimestring非必须
{
  "code": 1,
  "msg": "success",
  "data": {
    "total": 2,
    "rows": [
      {
        "id": 1,
        "username": "jinyong",
        "password": "123456",
        "name": "金庸",
        "gender": 1,
        "image": "https://web-framework.oss-cn-hangzhou.aliyuncs.com/2022-09-02-00-27-53B.jpg",
        "job": 2,
        "salary": 8000,
        "entryDate": "2015-01-01",
        "deptId": 2,
        "deptName": "教研部",
        "createTime": "2022-09-01T23:06:30",
        "updateTime": "2022-09-02T00:29:04"
      },
      {
        "id": 2,
        "username": "zhangwuji",
        "password": "123456",
        "name": "张无忌",
        "gender": 1,
        "image": "https://web-framework.oss-cn-hangzhou.aliyuncs.com/2022-09-02-00-27-53B.jpg",
        "job": 2,
        "salary": 6000,
        "entryDate": "2015-01-01",
        "deptId": 2,
        "deptName": "教研部",
        "createTime": "2022-09-01T23:06:30",
        "updateTime": "2022-09-02T00:29:04"
      }
    ]
  }
}

目前我们只考虑分页查询，不考虑查询条件，而上述接口文档中与分页查询相关的参数就两个，page和pageSize。

原始方式

代码实现

通过查看接口文档：员工列表查询

请求路径：/emps

请求方式：GET

请求参数：跟随在请求路径后的参数字符串。 例：/emps?page=1&pageSize=10

响应数据：json格式

  1. EmpController
@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={}", page, pageSize);
        PageResult pageResult = empService.page(page, pageSize);
        return Result.success(pageResult);
    }

}

@RequestParam(defaultValue = “默认值” //设置请求参数默认值

  1. EmpService
public interface EmpService {
    /**
     * 分页查询
     * @param page 页码
     * @param pageSize 每页记录数
     */
    PageResult page(Integer page, Integer pageSize);
}
  1. EmpServiceImpl
@Service
public class EmpServiceImpl implements EmpService {

    @Autowired
    private EmpMapper empMapper;

    @Override
    public PageResult page(Integer page, Integer pageSize) {
        //1. 获取总记录数
        Long total = empMapper.count();

        //2. 获取结果列表
        Integer start = (page - 1) * pageSize;
        List<Emp> empList = empMapper.list(start, pageSize);

        //3. 封装结果
        return new PageResult(total, empList);
    }
}
  1. EmpMapper
@Mapper
public interface EmpMapper {

    /**
     * 查询总记录数
     */
    @Select("select count(*) from emp e left join dept d on e.dept_id = d.id ")
    public Long count();
    
    /**
     * 查询所有的员工及其对应的部门名称
     */
    @Select("select e.*, d.name deptName from emp as e left join dept as d on e.dept_id = d.id limit #{start}, #{pageSize}")
    public List<Emp> list(Integer start , Integer pageSize);

}
功能测试

功能开发完成后，重新启动项目，使用Apifox，发送GET请求：

http://localhost:8080/emps?

前后端联调

启动nginX，打开浏览器，测试后端功能接口：

http://localhost:90/emp

测试改变每页记录数和页码

PageHelper分页插件

介绍

我们发现分页查询功能编写起来比较繁琐，但是分页查询的功能非常常见，而且思路和步骤都比较固定。

  • 在Mapper接口中定义两个方法执行两条SQL查询语句（1. 查询总记录数 2. 指定页码的数据列表）
  • Service中调用Mapper接口的两个方法，获取总记录数和查询结果列表，然后再将获取的数据结果封装到PageResult（PageBean）对象中。

可以使用现成的插件来解决，对于MyBatis来说现在最主流的就是PageHelper。

PageHelper是第三方提供的Mybatis框架中的一款功能强大、方便易用的分页插件，支持任何形式的单标、多表的分页查询。

https://pagehelper.github.io/

  • Mapper接口层：
    • 原始的分页查询中，我们需要在Mapper接口中定义两条SQL语句。
    • PageHelper实现分页查询之后，只需要编写一条正常的查询语句，不需要考虑分页操作。
  • Service层：
    • 需要根据页码、每页展示记录数，手动计算起始索引。
    • 无须手动计算起始索引，直接告诉PageHelper需要查询哪一页的数据和每页展示多少条记录即可。
代码实现
  1. pom.xml中引入依赖
<!--分页插件PageHelper-->
<dependency>
  <groupId>com.github.pagehelper</groupId>
  <artifactId>pagehelper-spring-boot-starter</artifactId>
  <version>1.4.7</version>
</dependency>
  1. EmpMapper
/**
 * 查询所有的员工及其对应的部门名称
 */
@Select("select e.*, d.name deptName from emp as e left join dept as d on e.dept_id = d.id")
public List<Emp> list();
  1. EmpServiceImpl
@Override
public PageResult page(Integer page, Integer pageSize) {
    //1. 设置分页参数
    PageHelper.startPage(page,pageSize);

    //2. 执行查询
    List<Emp> empList = empMapper.list();
    Page<Emp> p = (Page<Emp>) empList;

    //3. 封装结果
    return new PageResult(p.getTotal(), p.getResult());
}
功能测试

http://localhost:8080/emps?page=1&pageSize=5

实现机制

IDEA控制台

可以看到执行了两条sql语句，其实是我们再Mapper接口中定义的SQL演变而来的

  1. 查询总记录数

将查询返回的字段列表替换成了count(0)来统计总记录数。

  1. 分页查询，查询指定页码对应的数据列表

在SQL语句之后拼接上了limit进行分页查询。而由于测试时查询的是第一页，起始索引是0，所以简写为limit ?

PageHelper在进行分页查询时，会执行上述两条SQL语句，并将查询到的总记录数与数据列表封装到了Page<Emp>对象中，我们再获取查询结果时，只需要调用Page对象的方法就可以获取。

:::info

  • 使用PageHelper实现分页查询时，SQL语句的结尾一定不要加分号，否则limit拼接不上
  • PageHelper只会对紧跟在其后的第一条SQL语句进行分页处理

:::

条件分页查询

需求

不仅需要考虑分页，还需要考虑查询条件。

  • 姓名：模糊匹配
  • 性别：精确匹配
  • 入职日期：范围匹配

接口描述

员工管理 -> 员工列表查询

思路分析

功能开发

通过查看接口文档：员工列表查询

请求路径：/emps

请求方式：GET

请求参数：

参数名称是否必须示例备注
name否张姓名
gender否1性别 , 1 男 , 2 女
begin否2010/1/1范围匹配的开始时间(入职日期)
end否2020/1/1范围匹配的结束时间(入职日期)
page是1分页查询的页码，如果未指定，默认为1
pageSize是10分页查询的每页记录数，如果未指定，默认为10
  1. 在EmpController方法中通过多个方法形参，依次接收这几个参数
@Slf4j
@RestController
@RequestMapping("/emps")
public class EmpController {

    @Autowired
    private EmpService empService;

    @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, name, gender, begin, end);
        PageResult pageResult = empService.page(page,pageSize,name,gender,begin,end);
        return Result.success(pageResult);
    }
}
  1. 修改EmpService以及EmpServiceImpl中的代码逻辑
public interface EmpService {
    /**
     * 分页查询
     */
    PageResult page(Integer page, Integer pageSize, String name, Integer gender, LocalDate begin, LocalDate end);
}

/**
 * 员工管理
 */
@Service
public class EmpServiceImpl implements EmpService {

    @Autowired
    private EmpMapper empMapper;

    @Override
    public PageResult page(Integer page, Integer pageSize, String name, Integer gender, LocalDate begin, LocalDate end) {
        //1. 设置PageHelper分页参数
        PageHelper.startPage(page, pageSize);
        //2. 执行查询
        List<Emp> empList = empMapper.list(name, gender, begin, end);
        //3. 封装分页结果
        Page<Emp> p = (Page<Emp>) empList;
        return new PageResult(p.getTotal(), p.getResult());
    }
}
  1. 调整EmpMapper接口方法
@Mapper
public interface EmpMapper {
    
    /**
     * 查询所有的员工及其对应的部门名称
     */
    public List<Emp> list(String name, Integer gender, LocalDate begin, LocalDate end);
    
}

由于SQL语句比较复杂，建议将SQL语句配置在XML映射文件中。

  1. 新增Mapper映射文件 EmpMapper.xml
<!--定义Mapper映射文件的约束和基本结构-->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper">
  <select id="list" resultType="com.itheima.pojo.Emp">
    select e.*, d.name deptName from emp as e left join dept as 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}
  </select>
</mapper>

concat是SQL的函数，将字符串拼接起来。这里它将<font style="color:rgb(6, 6, 7);">%</font>（表示任意字符的通配符）与参数值拼接起来，形成一个LIKE查询的模式。

测试：

程序优化1

上述分页条件查询中请求参数过多

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

那我们在controller层方法中，接收请求参数的时候，直接在controller方法中声明这样6个参数即可，这样做，功能可以实现，但是不方便维护和管理。

那接下来呢，我们就可以通过 通义零码 AI辅助工具，对这一块儿的代码进行优化。 具体操作如下：

优化思路：定义一个实体类，来封装这几个请求参数。 【需要保证，前端传递的请求参数和实体类的属性名是一样的】

  1. 定义实体类 EmpQueryParam
package com.itheima.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; //入职结束时间
    
}
  1. EmpController接收请求参数
@GetMapping
public Result page(EmpQueryParam empQueryParam) {
    log.info("查询请求参数： {}", empQueryParam);
    PageResult pageResult = empService.page(empQueryParam);
    return Result.success(pageResult);
}
  1. 修改EmpService接口方法
public interface EmpService {
    /**
     * 分页查询
     */
    //PageResult page(Integer page, Integer pageSize, String name, Integer gender, LocalDate begin, LocalDate end);
    PageResult page(EmpQueryParam empQueryParam);
}
  1. 修改EmpServiceImpl中的page方法
@Service
public class EmpServiceImpl implements EmpService {

    @Autowired
    private EmpMapper empMapper;

    /*@Override
    public PageResult page(Integer page, Integer pageSize, String name, Integer gender, LocalDate begin, LocalDate end) {
        //1. 设置PageHelper分页参数
        PageHelper.startPage(page, pageSize);
        //2. 执行查询
        List<Emp> empList = empMapper.list(name, gender, begin, end);
        //3. 封装分页结果
        Page<Emp> p = (Page<Emp>) empList;
        return new PageResult(p.getTotal(), p.getResult());
    }*/

    public PageResult page(EmpQueryParam empQueryParam) {
        //1. 设置PageHelper分页参数
        PageHelper.startPage(empQueryParam.getPage(), empQueryParam.getPageSize());
        //2. 执行查询
        List<Emp> empList = empMapper.list(empQueryParam);
        //3. 封装分页结果
        Page<Emp> p = (Page<Emp>)empList;
        return new PageResult(p.getTotal(), p.getResult());
    }
}
  1. 修改EmpMapper接口方法
@Mapper
public interface EmpMapper {

    /**
     * 查询所有的员工及其对应的部门名称
     */
//    @Select("select e.*, d.name as deptName from emp e left join dept d on e.dept_id = d.id")
//    public List<Emp> list(String name, Integer gender, LocalDate begin, LocalDate end);
    
    /**
     * 根据查询条件查询员工
     */
    List<Emp> list(EmpQueryParam empQueryParam);
}

配置文件无需修改

测试：

当页码输入负数，则无法查询到数据

我们在application.yml中引入如下配置即可：

pagehelper:
  reasonable: true
  helper-dialect: mysql

reasonable:分页合理化参数，默认值为false。当该参数设置为true时，pageNum<=0时会查询第一页，pageNum>pages（超过总数时），会查询最后一页。默认false时，直接根据参数查询。

此时传递的页码是负数，但是能查询到第一页

程序优化2

当前我们的查询条件是写死的，但是我们希望查询条件是可选的。

  • 如果只输入 姓名 这个查询条件，则SQL语句中只根据name字段查询，SQL如下：
select e.*, d.name deptName from emp as e left join dept as d on e.dept_id = d.id where e.name like concat('%',#{name},'%')；
  • 如果只输入 性别 这个查询条件，则SQL语句中只根据gender字段查询，SQL如下：
select e.*, d.name deptName from emp as e left join dept as d on e.dept_id = d.id where e.gender = #{gender}；
  • 如果输入 姓名 和 **性别 **这两个查询条件，则SQL语句中要根据name、gender两个字段查询，SQL如下：
select e.*, d.name deptName from emp as e left join dept as d on e.dept_id = d.id
where e.name like concat('%',#{name},'%') and e.gender = #{gender}；

这里可以通过Mybatis中的动态SQL来实现

<!--定义Mapper映射文件的约束和基本结构-->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper">
  <select id="list" resultType="com.itheima.pojo.Emp">
    select e.*, d.name deptName from emp as e left join dept as 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>
  </select>
</mapper>

在这里呢，我们用到了两个动态SQL的标签：<if>``<where>。 这两个标签的具体作用如下：

  • <if>：判断条件是否成立，如果条件为true，则拼接SQL。
  • <where>：根据查询条件，来生成where关键字，并会自动去除条件前面多余的and或or。

代码优化完毕后，重启服务，测试如下：

没有输入任何条件时，运行日志

输入名字和性别时，运行日志

此时当我们输入了不同的搜索条件时，会动态地根据查询条件，动态拼接SQL语句。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值