05-Web后端基础(数据库)

前言

引言

在我们讲解 SpringBootWeb 基础知识(IOC、DI 等)的时候，我们讲到在 web 开发中，为了应用程序职责单一，方便维护，我们一般将 web 应用程序分为三层，即：Controller、Service、Dao 。

之前我们的案例中，是这样子的请求流程：浏览器发起请求，先请求 Controller；Controller 接收到请求之后，调用 Service 进行业务逻辑处理；Service 再调用 Dao，Dao 再解析 user.txt 中所存储的数据。

txt 文件中可以存储数据，但是在企业项目开发中一般不会使用文本文件存储项目数据，因为不便管理维护，操作难度大。

在真实的企业开发中呢，都会采用数据库来存储和管理数据，那此时，web 开发调用流程图如下所示：

那么今天我们就要来学习数据库技术。

MySQL 概述

官网：https://dev.mysql.com/

MySQL 官方提供了两个版本：

商业版本（MySQL Enterprise Edition）
- 该版本是收费的，我们可以使用 30 天。官方会提供对应的技术支持。
社区版本（MySQL Community Server）
- 该版本是免费的，但是 MySQL 不会提供任何的技术支持。

[!TIP]
本课程，采用的是 MySQL 的社区版本（8.0.34）

安装

官网下载地址：https://downloads.mysql.com/archives/community/

这个安装包呢，大家可以不用下载，因为资料中已提供现成的安装包。

[!TIP]
安装 MySQL 具体的步骤，参考 MySQL 安装文档，进行软件的安装即可。

连接

介绍

MySQL 服务器启动完毕后，然后再使用如下指令，来连接 MySQL 服务器：

1	mysql -u用户名 -p密码 [-h数据库服务器的IP地址 -P端口号]

-h 参数不加，默认连接的是本地 127.0.0.1 的 MySQL 服务器
-P 参数不加，默认连接的端口号是 3306

上述指令，可以有两种形式：

密码直接在-p 参数之后直接指定（这种方式不安全，密码直接以明文形式出现在命令行）

密码在-p 回车之后，在命令行中输入密码，然后回车

企业使用方式(了解)

上述的 MySQL 服务器我们是安装在本地的，这个仅仅是在我们学习阶段，在真实的企业开发中，MySQL 数据库服务器是不会在我们本地安装的，是在公司的服务器上安装的，而服务器还需要放置在专门的 IDC 机房中的，IDC 机房呢，就需要保证恒温、恒湿、恒压，而且还要保证网络、电源的可靠性(备用电源及网络)。

那我们要想使用服务器上的这台 MySQL 服务器，就需要在我们的电脑上去远程连接这台 MySQL 。而服务器上安装的 MySQL 数据库呢，并不是你一个人在访问，我们项目组的其他开发人员也是需要访问这台 MySQL 的。

接下来，就来演示一下，通过 MySQL 的客户端命令行，如何来连接服务器上部署的 MySQL ：

1	mysql [-h数据库服务器的IP地址 -P端口号] -u用户名 -p密码

数据模型

介绍完了 Mysql 数据库的安装配置之后，接下来我们再来聊一聊 Mysql 当中的数据模型。学完了这一小节之后，我们就能够知道在 Mysql 数据库当中到底是如何来存储和管理数据的。

在介绍 Mysql 的数据模型之前，需要先了解一个概念：关系型数据库。

关系型数据库（RDBMS）

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。而所谓二维表，指的是由行和列组成的表，如下图：

二维表的优点：

使用表存储数据，格式统一，便于维护
使用 SQL 语言操作，标准统一，使用方便，可用于复杂查询

[!TIP]
我们之前提到的 MySQL、Oracle、DB2、SQLServer 这些都是属于关系型数据库，里面都是基于二维表存储数据的。
**结论：**基于二维表存储数据的数据库就成为关系型数据库，不是基于二维表存储数据的数据库，就是非关系型数据库（比如大家后面要学习的 Redis，就属于非关系型数据库）。

数据模型

介绍完了关系型数据库之后，接下来我们再来看一看在 Mysql 数据库当中到底是如何来存储数据的，也就是 Mysql 的数据模型。

MySQL 是关系型数据库，是基于二维表进行数据存储的，具体的结构图下:

通过 MySQL 客户端连接数据库管理系统 DBMS，然后通过 DBMS 操作数据库。
使用 MySQL 客户端，向数据库管理系统发送一条 SQL 语句，由数据库管理系统根据 SQL 语句指令去操作数据库中的表结构及数据。
一个数据库服务器中可以创建多个数据库，一个数据库中也可以包含多张表，而一张表中又可以包含多行记录。

[!TIP]
在 Mysql 数据库服务器当中存储数据，你需要：

先去创建数据库（可以创建多个数据库，之间是相互独立的）
在数据库下再去创建数据表（一个数据库下可以创建多张表）
再将数据存放在数据表中（一张表可以存储多行数据）

1 2	-- 启动mysql net start mysql

SQL 语句

SQL：结构化查询语言。一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。SQL 语句根据其功能被分为四大类：DDL、DML、DQL、DCL 。

DDL 语句

数据库操作

我们在进行数据库设计，需要使用到刚才所介绍 SQL 分类中的 DDL 语句。

DDL 英文全称是 Data Definition Language(数据定义语言)，用来定义数据库对象(数据库、表)。

DDL 中数据库的常见操作：查询、创建、使用、删除。

查询数据库

查询所有数据库

1	show databases;

命令行中执行效果如下：

查询当前数据库

1	select database();

命令行中执行效果如果：

[!TIP]
我们要操作某一个数据库，必须要切换到对应的数据库中。
通过指令：select database() ，就可以查询到当前所处的数据库

创建数据库

语法：

1	create database [ if not exists ] 数据库名 [default charset utf8mb4];

创建数据库时，可以不指定字符集。因为在 MySQL8 版本之后，默认的字符集就是 utf8mb4。

案例：创建一个 itcast 数据库。

1	create database itcast;

命令行执行效果如下：

注意：在同一个数据库服务器中，不能创建两个名称相同的数据库，否则将会报错。

可以使用 if not exists 来避免这个问题

1 2	-- 数据库不存在,则创建该数据库；如果存在则不创建 create database if not exists itcast;

命令行执行效果如下：

使用数据库

语法：

1	use 数据库名 ;

我们要操作某一个数据库下的表时，就需要通过该指令，切换到对应的数据库下，否则不能操作。

案例：切换到 itcast 数据

1	use itcast;

命令执行效果如下：

删除数据库

语法:
1
2
3
4
```

drop database [ if exists ] 数据库名 ;
- 如果删除一个不存在的数据库，将会报错。
- 可以加上参数 if exists ，如果数据库存在，再执行删除，否则不执行删除。

案例：删除itcast数据库

1	drop database if exists itcast; -- itcast数据库存在时删除

命令执行效果如下：

[!TIP]
**说明：**上述语法中的 database，也可以替换成 schema

如：create schema db01;
如：show schemas;

图形化工具

介绍

前面我们讲解了 DDL 中关于数据库操作的 SQL 语句，在我们编写这些 SQL 时，都是在命令行当中完成的。大家在练习的时候应该也感受到了，在命令行当中来敲这些 SQL 语句很不方便，主要的原因有以下 3 点：

没有任何代码提示。（全靠记忆，容易敲错字母造成执行报错）
操作繁琐，影响开发效率。（所有的功能操作都是通过 SQL 语句来完成的）
编写过的 SQL 代码无法保存。

在项目开发当中，通常为了提高开发效率，都会借助于现成的图形化管理工具来操作数据库。

目前 MySQL 主流的图形化界面工具有以下几种：

DataGrip 是 JetBrains 旗下的一款数据库管理工具，是管理和开发 MySQL、Oracle、PostgreSQL 的理想解决方案。

官网： https://www.jetbrains.com/zh-cn/datagrip/

安装

安装：参考资料中提供的《DataGrip 安装手册》

[!TIP]
说明：DataGrip 这款工具可以不用安装，因为 Jetbrains 公司已经将 DataGrip 这款工具的功能已经集成到了 IDEA 当中，所以我们就可以使用 IDEA 来作为一款图形化界面工具来操作 Mysql 数据库。

连接数据库

1). 创建 Project

2). 创建连接

下载驱动, 可能会比较耗时, 耐心等待一会儿。

3). 测试连接

下载完驱动之后，可以点击 Test Connection 来测试一下是否可以正常的连接数据库。

然后点击 OK ，就已经连接上了 MySQL 数据库了。

默认情况下，连接上了 MySQL 数据库之后，数据库并没有全部展示出来。需要选择要展示哪些数据库。具体操作如下：

表操作

学习完了 DDL 语句当中关于数据库的操作之后，接下来我们继续学习 DDL 语句当中关于表结构的操作。

关于表结构的操作也是包含四个部分：创建表、查询表、修改表、删除表。

创建

语法：

  ```

create table  表名(
字段 1  字段 1 类型 [约束]  [comment  字段 1 注释 ],
字段 2  字段 2 类型 [约束]  [comment  字段 2 注释 ],
......
字段 n  字段 n 类型 [约束]  [comment  字段 n 注释 ]
) [ comment  表注释 ] ;

注意： [ ] 中的内容为可选参数；最后一个字段后面没有逗号

案例：创建tb_user表

对应的结构如下：

建表语句：

create table tb_user (
    id int comment 'ID,唯一标识',   # id是一行数据的唯一标识（不能重复）
    username varchar(20) comment '用户名',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '性别'
) comment '用户表';

1
2
3

- 数据表创建完成，接下来我们还需要测试一下是否可以往这张表结构当中来存储数据。
双击打开tb_user表结构，大家会发现里面没有数据：
{% asset_img "Va98bieYpogIF0xYPyZc4GBOnFb.png" "" %}

添加数据：

1	此时我们再插入一条数据：

我们之前提到过：id 字段是一行数据的唯一标识，不能有重复值。但是现在数据表中有两个相同的 id 值，这是为什么呢？

其实我们现在创建表结构的时候， id 这个字段我们只加了一个备注信息说明它是一个唯一标识，但是在数据库层面呢，并没有去限制字段存储的数据。所以 id 这个字段没有起到唯一标识的作用。

[!TIP]
想要限制字段所存储的数据，就需要用到数据库中的约束。

约束

概念：所谓约束就是作用在表中字段上的规则，用于限制存储在表中的数据。
作用：就是来保证数据库当中数据的正确性、有效性和完整性。（后面的学习会验证这些）
在 MySQL 数据库当中，提供了以下 5 种约束：

[!TIP]
注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

案例：创建 tb_user 表，对应的结构如下：

在上述的表结构中:

建表语句：

create table tb_user (
    id int primary key comment 'ID,唯一标识', 
    username varchar(20) not null unique comment '用户名',
    name varchar(10) not null comment '姓名',
    age int comment '年龄',
    gender char(1) default '男' comment '性别'
) comment '用户表';

数据表创建完成，接下来测试一下表中字段上的约束是否生效

大家有没有发现一个问题：id 字段下存储的值，如果由我们自己来维护会比较麻烦(必须保证值的唯一性)。MySQL 数据库为了解决这个问题，给我们提供了一个关键字：auto_increment（自动增长）

[!TIP]
主键自增：auto_increment

每次插入新的行记录时，数据库自动生成 id 字段(主键)下的值
具有 auto_increment 的数据列是一个正数序列开始增长(从 1 开始自增)

create table tb_user (
    id int primary key auto_increment comment 'ID,唯一标识', #主键自动增长
    username varchar(20) not null unique comment '用户名',
    name varchar(10) not null comment '姓名',
    age int comment '年龄',
    gender char(1) default '男' comment '性别'
) comment '用户表';

测试主键自增：

数据类型

在上面建表语句中，我们在指定字段的数据类型时，用到了 int 、varchar、char，那么在 MySQL 中除了以上的数据类型，还有哪些常见的数据类型呢？接下来,我们就来详细介绍一下 MySQL 的数据类型。

MySQL 中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

**1). **数值类型

示例:

-- 年龄字段 ---不会出现负数, 而且人的年龄不会太大
   age tinyint unsigned

-- 分数 ---总分100分, 最多出现一位小数
   score double(4,1)

**2). **字符串类型

char 与 varchar 都可以描述字符串，char 是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关。而 varchar 是变长字符串，指定的长度为最大占用长度。相对来说，char 的性能会更高些。

示例： 
    用户名 username ---长度不定, 最长不会超过50
    username varchar(50)
    
    手机号 phone ---固定长度为11
    phone char(11)

**3). **日期时间类型

示例: 
    生日字段  birthday ---生日只需要年月日  
    birthday date
    
    创建时间 createtime --- 需要精确到时分秒
    createtime  datetime

表结构设计-案例

**需求：**根据产品原型/需求创建表((设计合理的数据类型、长度、约束)

[!TIP]
参考资料中提供的《黑马-tlias 智能学习辅助系统》页面原型，设计员工管理模块的表结构。 暂不考虑所属部门及工作经历字段。

产品原型及需求如下：

1). 列表展示

2). 新增员工

3). 需求说明及字段限制

步骤：

阅读产品原型及需求文档，看看里面涉及到哪些字段。
查看需求文档说明，确认各个字段的类型以及字段存储数据的长度限制。
在页面原型中描述的基础字段的基础上，再增加额外的基础字段。

使用 SQL 创建表：

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(255) comment '头像',
    entry_date date comment '入职日期',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间'
) comment '员工表';

除了使用 SQL 语句创建表外，我们还可以借助于图形化界面来创建表结构，这种创建方式会更加直观、更加方便。

[!TIP]
设计表流程：

阅读页面原型及需求文档
基于页面原则和需求文档，确定原型字段(类型、长度限制、约束)
再增加表设计所需要的业务基础字段(id、create_time、update_time)
- create_time：记录的是当前这条数据插入的时间。
- update_time：记录当前这条数据最后更新的时间。

表操作-其他操作

上面讲解了表结构的创建、数据类型、设计表的流程，接下来，再来讲解表结构的查询、修改、删除操作。

查询数据库表的具体的语法：

-- 查询当前数据库的所有表
show tables;

-- 查看指定的表结构
desc 表名 ;   -- 可以查看指定表的字段、字段的类型、是否可以为NULL、是否存在默认值等信息

-- 查询指定表的建表语句
show create table 表名 ;

修改数据库表结构的具体语法：

添加字段

-- 添加字段
alter table 表名 add  字段名  类型(长度)  [comment 注释]  [约束];

-- 比如： 为tb_emp表添加字段qq，字段类型为 varchar(11)
alter table tb_emp add  qq  varchar(11) comment 'QQ号码';

修改字段

-- 修改字段类型
alter table 表名 modify  字段名  新数据类型(长度);

-- 比如： 修改qq字段的字段类型，将其长度由11修改为13
alter table tb_emp modify qq varchar(13) comment 'QQ号码';

-- 修改字段名，字段类型
alter table 表名 change  旧字段名  新字段名  类型(长度)  [comment 注释]  [约束];

-- 比如： 修改qq字段名为 qq_num，字段类型varchar(13)
alter table tb_emp change qq qq_num varchar(13) comment 'QQ号码';

删除字段

-- 删除字段
alter table 表名 drop 字段名;

-- 比如： 删除tb_emp表中的qq_num字段
alter table tb_emp drop qq_num;

修改表名

-- 修改表名
rename table 表名 to  新表名;

-- 比如: 将当前的emp表的表名修改为tb_emp
rename table emp to tb_emp;
或者 
alter table emp rename to tb_emp;

删除表结构

-- 删除表
drop  table [ if exists ]  表名;

-- 比如：如果tb_emp表存在，则删除tb_emp表
drop table if exists tb_emp;  -- 在删除表时，表中的全部数据也会被删除。

[!TIP]
关于表结构的查看、修改、删除操作，工作中一般都是直接基于图形化界面操作。** **

DML 语句

DML 英文全称是 Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

添加数据（INSERT）
修改数据（UPDATE）
删除数据（DELETE）

增加(insert)

语法

向指定字段添加数据

1	insert into 表名 (字段名1, 字段名2) values (值1, 值2);

全部字段添加数据

1	insert into 表名 values (值1, 值2, ...);

批量添加数据（指定字段）

1	insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);

批量添加数据（全部字段）

1	insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);

案例演示

案例 1：向 emp 表的 username, name, gender, phone, create_time, update_time 字段插入数据

1
2
3

-- 因为设计表时create_time, update_time两个字段不能为NULL，所以也做为要插入的字段
insert into emp(username, name, gender, phone, create_time, update_time)
values ('wuji', '张无忌', 1, '13309091231', now(), now());

案例 2：向 temp 表的所有字段插入数据

1
2

insert into emp2(id, username, password, name, gender, phone, job, salary, image, entry_date, create_time, update_time)
                  values (1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,'1.jpg','2000-01-01',now(),now()),

案例 3：批量向 emp 表的 username、name、gender 字段插入数据

1
2
3

insert into emp(username, name, gender, phone, create_time, update_time)
values ('Tom1', '汤姆1', 1, '13309091231', now(), now()),
       ('Tom2', '汤姆2', 1, '13309091232', now(), now());

[!TIP]
insert 操作的注意事项：

插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内。

修改(update)

语法

1	update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;

案例演示

案例 1：将 emp 表中 id 为 1 的员工，姓名 name 字段更新为’张三’

1	update emp set name='张三', update_time=now() where id=1;

案例 2：将 emp 表的所有员工入职日期更新为’2010-01-01’

1	update emp set entry_date='2010-01-01', update_time=now();

[!TIP]
注意事项:

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
在修改数据时，一般需要同时修改公共字段 update_time，将其修改为当前操作时间。

删除(delete)

语法

1	delete from 表名 [where 条件] ;

案例演示

案例 1：删除 emp 表中 id 为 1 的员工

1	delete from emp where id = 1;

案例 2：删除 emp 表中所有员工

1	delete from tb_emp;

[!TIP]
注意事项:

DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
DELETE 语句不能删除某一个字段的值(可以使用 UPDATE，将该字段值置为 NULL 即可)。
当进行删除全部数据操作时，会提示询问是否确认删除所有数据，直接点击 Execute 即可。

DQL 语句

介绍

DQL 英文全称是 Data Query Language(数据查询语言)，用来查询数据库表中的记录。

查询关键字：SELECT

查询操作是所有 SQL 语句当中最为常见，也是最为重要的操作。在一个正常的业务系统中，查询操作的使用频次是要远高于增删改操作的。当我们打开某个网站或 APP 所看到的展示信息，都是通过从数据库中查询得到的，而在这个查询过程中，还会涉及到条件、排序、分页等操作。

语法

DQL 查询语句，语法结构如下：

SELECT
        字段列表
FROM
        表名列表
WHERE
        条件列表
GROUP  BY
        分组字段列表
HAVING
        分组后条件列表
ORDER BY
        排序字段列表
LIMIT
        分页参数

我们今天会将上面的完整语法拆分为以下几个部分学习：

基本查询（不带任何条件）
条件查询（where）
分组查询（group by）
排序查询（order by）
分页查询（limit）

准备一些测试数据用于查询操作：

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 '入职日期',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间'
) comment '员工表';


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

基本查询

在基本查询的 DQL 语句中，不带任何的查询条件。

语法如下：

查询多个字段

1	select 字段1, 字段2, 字段3 from 表名;

查询所有字段（通配符）

1	select * from 表名;

设置别名

1	select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;

去除重复记录

1	select distinct 字段列表 from 表名;

案例演示:

案例 1：查询指定字段 name，entry_date 并返回

1	select name,entry_date from emp;

案例 2：查询返回所有字段

1	select * from emp;

[!TIP]

星号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）

案例 3：查询所有员工的 name, entry_date，并起别名(姓名、入职日期)

-- 方式1：
select name AS 姓名, entry_date AS 入职日期 from emp;

-- 方式2： 别名中有特殊字符时，使用''或""包含
select name AS '姓 名', entry_date AS '入职日期' from emp;

-- 方式3：
select name AS "姓名", entry_date AS "入职日期" from emp;

案例 4：查询已有的员工关联了哪几种职位(不要重复)

1	select distinct job from emp;

条件查询

语法：

1	select 字段列表 from 表名 where 条件列表 ; -- 条件列表：意味着可以有多个条件

学习条件查询就是学习条件的构建方式，而在 SQL 语句当中构造条件的运算符分为两类：

比较运算符
逻辑运算符

案例 1：查询姓名为 ‘杨逍’ 的员工

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where name = '杨逍'; -- 字符串使用''或""包含

案例 2：查询薪资小于等于 5000 的员工信息

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where salary <=5000;

案例 3：查询没有分配职位的员工信息

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where job is null ;

[!TIP]
注意：查询为 NULL 的数据时，不能使用 = null 或 ！=null 。得使用 is null 或 is not null。

案例 4：查询有职位的员工信息

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where job is not null ;

案例 5：查询密码不等于 ‘123456’ 的员工信息

-- 方式1：
select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where password <> '123456';

-- 方式2：
select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where password != '123456';

案例 6：查询入职日期在 ‘2000-01-01’ (包含) 到 ‘2010-01-01’(包含) 之间的员工信息

-- 方式1：
select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where entry_date >= '2000-01-01' and entry_date <= '2010-01-01';

-- 方式2： between...and
select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where entry_date between '2000-01-01' and '2010-01-01';

案例 7：查询入职时间在 ‘2000-01-01’ (包含) 到 ‘2010-01-01’(包含) 之间且性别为女的员工信息

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where entry_date between '2000-01-01' and '2010-01-01';
      and gender = 2;

案例 8：查询职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息

-- 方式1：使用or连接多个条件
select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where job=2 or job=3 or job=4;

-- 方式2：in关键字
select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where job in (2,3,4);

案例 9：查询姓名为两个字的员工信息

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where name like '__';  # 通配符 "_" 代表任意1个字符

案例 10：查询姓 ‘张’ 的员工信息

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where name like '张%'; # 通配符 "%" 代表任意个字符（0个 ~ 多个）

案例 11：查询姓名中包含 ‘二’ 的员工信息

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where name like '%二%'; # 通配符 "%" 代表任意个字符（0个 ~ 多个）

聚合函数

之前我们做的查询都是横向查询，就是根据条件一行一行的进行判断，而使用聚合函数查询就是纵向查询，它是对一列的值进行计算，然后返回一个结果值。（将一列数据作为一个整体，进行纵向计算）

常用聚合函数：

[!TIP]
注意 : 聚合函数会忽略空值，对 NULL 值不作为统计。

count ：按照列去统计有多少行数据。
- 在根据指定的列统计的时候，如果这一列中有 null 的行，该行不会被统计在其中。
sum ：计算指定列的数值和，如果不是数值类型，那么计算结果为 0
max ：计算指定列的最大值
min ：计算指定列的最小值
avg ：计算指定列的平均值

案例演示:

案例 1：统计该企业员工数量

-- count(字段)
select count(id) from emp;-- 结果：30
select count(job) from emp;-- 结果：29 （聚合函数对NULL值不做计算）

-- count(常量)
select count(0) from emp;
select count('A') from emp;

-- count(*)  推荐此写法（MySQL底层进行了优化）
select count(*) from emp;

案例 2：统计该企业员工的平均薪资

1	select avg(salary) from emp;

案例 3：统计该企业员工的最低薪资

1	select min(salary) from emp;

案例 4：统计该企业员工的最高薪资

1	select max(salary) from emp;

案例 5：统计该企业每月要给员工发放的薪资总额(薪资之和)

1	select sum(salary) from emp;

分组查询

分组：按照某一列或者某几列，把相同的数据进行合并输出。
- 分组其实就是按列进行分类(指定列下相同的数据归为一类)，然后可以对分类完的数据进行合并计算。
- 分组查询通常会使用聚合函数进行计算。

语法：

1	select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

案例演示：

案例 1：根据性别分组 , 统计男性和女性员工的数量

1
2
3

select gender, count(*)
from emp
group by gender; -- 按照gender字段进行分组（gender字段下相同的数据归为一组）

案例 2：查询入职时间在 ‘2015-01-01’ (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于 2 的职位

select job, count(*)
from emp
where entry_date <= '2015-01-01'   -- 分组前条件
group by job                      -- 按照job字段分组
having count(*) >= 2;             -- 分组后条件

[!TIP]
注意事项:

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
执行顺序：where > 聚合函数 > having

[!TIP]
where 与 having 区别（面试题）

执行时机不同：where 是分组之前进行过滤，不满足 where 条件，不参与分组；而 having 是分组之后对结果进行过滤。
判断条件不同：where 不能对聚合函数进行判断，而 having 可以。

排查查询

排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。

语法：

select  字段列表  
from   表名   
[where  条件列表] 
[group by  分组字段 ] 
order  by  字段1  排序方式1 , 字段2  排序方式2 … ;

排序方式：
- ASC ：升序（默认值）
- DESC：降序

案例演示：

案例 1：根据入职时间, 对员工进行升序排序

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
order by entry_date ASC; -- 按照entrydate字段下的数据进行升序排序

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
order by  entry_date; -- 默认就是ASC（升序）

[!TIP]
注意事项：如果是升序, 可以不指定排序方式 ASC

案例 2：根据入职时间，对员工进行降序排序

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
order by entry_date DESC; -- 按照entrydate字段下的数据进行降序排序

案例 3：根据入职时间对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
order by entry_date ASC , update_time DESC;

[!TIP]
注意事项：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

分页查询

分页操作在业务系统开发时，也是非常常见的一个功能，日常我们在网站中看到的各种各样的分页条，后台也都需要借助于数据库的分页操作。

分页查询语法：

1	select 字段列表 from 表名 limit 起始索引, 查询记录数 ;

案例 1：从起始索引 0 开始查询员工数据, 每页展示 5 条记录

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
limit 0 , 5; -- 从索引0开始，向后取5条记录

案例 2：查询第 1 页员工数据, 每页展示 5 条记录

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
limit 5; -- 如果查询的是第1页数据，起始索引可以省略，直接简写为：limit 条数

案例 3：查询第 2 页员工数据, 每页展示 5 条记录

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
limit 5 , 5; -- 从索引5开始，向后取5条记录

案例 4：查询第 3 页员工数据, 每页展示 5 条记录

1
2
3

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
limit 10 , 5; -- 从索引10开始，向后取5条记录

[!TIP]
注意事项:

起始索引从 0 开始。计算公式：起始索引 = （查询页码 - 1）* 每页显示记录数
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL 中是 LIMIT
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 条数

06-Web 后端基础(java 操作数据库)

前言

在前面我们学习 MySQL 数据库时，都是利用图形化客户端工具(如：idea、datagrip)，来操作数据库的。

我们做为后端程序开发人员，通常会使用 Java 程序来完成对数据库的操作。Java 程序操作数据库的技术呢，有很多啊，而最为底层、最为基础的就是 JDBC。

JDBC：（Java DataBase Connectivity），就是使用 Java 语言操作关系型数据库的一套 API。【是操作数据库最为基础、底层的技术】

但是使用 JDBC 来操作数据库，会比较繁琐，所以现在在企业项目开发中呢，一般都会使用基于 JDBC 的封装的高级框架，比如：Mybatis、MybatisPlus、Hibernate、SpringDataJPA。

而这些技术，目前的市场占有份额如下图所示：

从上图中，我们也可以看到，目前最为主流的就是 Mybatis，其次是 MybatisPlus。

所以，在我们的课程体系中呢，这两种主流的操作数据库的框架我们都要学习。而我们在学习这两个主流的框架之前，还需要学习一下操作数据库的基础基础 JDBC。然后接下来，再来学习 Mybatis。而在我们后面的课程中，我们还要学习 MybatisPlus 框架。那么今天呢，我们就先来学习 JDBC 和 Mybatis。

[!TIP]
今天课程安排：

JDBC
Mybatis
SpringBoot 配置文件

JDBC

介绍

JDBC：（Java DataBase Connectivity），就是使用Java 语言操作关系型数据库的一套 API。

本质：

sun 公司官方定义的一套操作所有关系型数据库的API规范，即接口。
各个数据库厂商去实现这套接口，提供数据库驱动 jar 包。
我们可以使用这套接口(JDBC)编程，真正执行的代码是驱动 jar 包中的实现类。

那有了 JDBC 之后，我们就可以直接在 java 代码中来操作数据库了，只需要编写这样一段 java 代码，就可以来操作数据库中的数据。示例代码如下：

查询数据

需求

**需求：**基于 JDBC 实现用户登录功能。

**本质：**其本质呢，其实就是基于 JDBC 程序，执行如下 select 语句，并将查询的结果输出到控制台。SQL 语句：

1	select * from user where username = 'linchong' and password = '123456';

准备工作

1). 创建一个 maven 项目

2). 创建一个数据库 web，并在该数据库中创建 user 表

create table user(
    id int unsigned primary key auto_increment comment 'ID,主键',
    username varchar(20) comment '用户名',
    password varchar(32) comment '密码',
    name varchar(10) comment '姓名',
    age tinyint unsigned comment '年龄'
) comment '用户表';

insert into user(id, username, password, name, age) values (1, 'daqiao', '123456', '大乔', 22),
                                                           (2, 'xiaoqiao', '123456', '小乔', 18),
                                                           (3, 'diaochan', '123456', '貂蝉', 24),
                                                           (4, 'lvbu', '123456', '吕布', 28),
                                                           (5, 'zhaoyun', '12345678', '赵云', 27);

代码实现

[!TIP]
AI 提示词（prompt）：
你是一名 java 开发工程师，帮我基于 JDBC 程序来操作数据库，执行如下 SQL 语句：
select * from user where username = ‘daqiao’ and password = ‘123456’

具体的代码为：

1). 在 pom.xml 文件中引入依赖

<dependencies>
    <!-- MySQL JDBC driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.30</version>
    </dependency>

    <dependency>
        <groupId>org.junit.jupiter</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>5.9.3</version>
        <scope>test</scope>
    </dependency>
</dependencies>

2). 在 src/main/test/java 目录下编写测试类，定义测试方法

public class JDBCTest {

    _/**_
_     * 编写JDBC程序, 查询数据_
_     */_
_    _@Test
    public void testJdbc() throws Exception {
        // 获取连接
        Connection conn = DriverManager._getConnection_("jdbc:mysql://localhost:3306/web", "root", "1234");
        // 创建预编译的PreparedStatement对象
        PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM user WHERE username = ? AND password = ?");
        // 设置参数
        pstmt.setString(1, "daqiao"); // 第一个问号对应的参数
        pstmt.setString(2, "123456"); // 第二个问号对应的参数
        // 执行查询
        ResultSet rs = pstmt.executeQuery();
        // 处理结果集
        while (rs.next()) {
            int id = rs.getInt("id");
            String uName = rs.getString("username");
            String pwd = rs.getString("password");
            String name = rs.getString("name");
            int age = rs.getInt("age");

            System._out_.println("ID: " + id + ", Username: " + uName + ", Password: " + pwd + ", Name: " + name + ", Age: " + age);
        }
        // 关闭资源
        rs.close();
        pstmt.close();
        conn.close();
    }

}

而上述的单元测试中，我们在 SQL 语句中，将将用户名和密码的值都写死了，而这两个值应该是动态的，是将来页面传递到服务端的。那么，我们可以基于前面所讲解的 JUnit 中的参数化测试进行单元测试，代码改造如下：

public class JDBCTest {

    _/**_
_     * 编写JDBC程序, 查询数据_
_     */_
_    _@ParameterizedTest
    @CsvSource({"daqiao,123456"})
    public void testJdbc(String _username, String _password) throws Exception {
        // 获取连接
        Connection conn = DriverManager._getConnection_("jdbc:mysql://localhost:3306/web", "root", "1234");
        // 创建预编译的PreparedStatement对象
        PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM user WHERE username = ? AND password = ?");
        // 设置参数
        pstmt.setString(1, _username); // 第一个问号对应的参数
        pstmt.setString(2, _password); // 第二个问号对应的参数
        // 执行查询
        ResultSet rs = pstmt.executeQuery();
        // 处理结果集
        while (rs.next()) {
            int id = rs.getInt("id");
            String uName = rs.getString("username");
            String pwd = rs.getString("password");
            String name = rs.getString("name");
            int age = rs.getInt("age");

            System._out_.println("ID: " + id + ", Username: " + uName + ", Password: " + pwd + ", Name: " + name + ", Age: " + age);
        }
        // 关闭资源
        rs.close();
        pstmt.close();
        conn.close();
    }

}

如果在测试时，需要传递一组参数，可以使用 @``CsvSource 注解。

代码剖析

ResultSet

ResultSet（结果集对象）：封装了 DQL 查询语句查询的结果。

next()：将光标从当前位置向前移动一行，并判断当前行是否为有效行，返回值为 boolean。
- true：有效行，当前行有数据
- false：无效行，当前行没有数据
getXxx(…)：获取数据，可以根据列的编号获取，也可以根据列名获取（推荐）。

结果解析步骤：

预编译 SQL

其实我们在编写 SQL 语句的时候，有两种风格：

静态 SQL（参数硬编码）

1 2	conn.prepareStatement("SELECT * FROM user WHERE username = 'daqiao' AND password = '123456'"); ResultSet resultSet = pstmt.executeQuery();

这种呢，就是参数值，直接拼接在 SQL 语句中，参数值是写死的。

预编译 SQL（参数动态传递）

conn.prepareStatement("SELECT * FROM user WHERE username = ? AND password = ?");
pstmt.setString(1, "daqiao");
pstmt.setString(2, "123456");
ResultSet resultSet = pstmt.executeQuery();

这种呢，并未将参数值在 SQL 语句中写死，而是使用？进行占位，然后再指定每一个占位符对应的值是多少，而最终在执行 SQL 语句的时候，程序会将 SQL 语句（SELECT * FROM user WHERE username = ? AND password = ?），以及参数值（“daqiao”, “123456”）都发送给数据库，然后在执行的时候，会使用参数值，将？占位符替换掉。

那这种预编译的 SQL，也是在项目开发中推荐使用的 SQL 语句。主要的作用有两个：

防止 SQL 注入
性能更高

那接下来，我们就来介绍一下这两点。

SQL 注入

SQL 注入：通过控制输入来修改事先定义好的 SQL 语句，以达到执行代码对服务器进行攻击的方法。

SQL 注入最典型的场景，就是用户登录功能。

注入演示：

1). 打开课程资料中的文件夹 资料/02. SQL注入演示，运行其中的 jar 包 sql_Injection_demo-0.0.1-SNAPSHOT.jar，进入该目录后，执行命令：

1	java -jar sql_Injection_demo-0.0.1-SNAPSHOT.jar

2). 打开浏览器访问 http://localhost:9090/ ，必须登录后才能访问到系统。我们先测试正常的用户名和密码

3). 接下来，我们再来测试一下错误的用户名和密码。

我们看到，如果用户名密码错误，是不能进入到系统中进行访问的，会提示 用户名和密码错误。

4). 那接下来，我们就要演示一下 SQL 注入现象，我们可以通过控制表单输入，来修改事先定义好的 SQL 语句的含义。从而来攻击服务器。

点击登录后，我们看到居然可以成功进入到系统中。

为什么会出现这种现象呢？

在进行登录操作时，怎么样才算登录成功呢？如果我们查询到了数据，就说明用户名密码是对的。如果没有查询到数据，就说明用户名或密码错误。

而出现上述现象，原因就是因为，我们我们编写的 SQL 语句是基于字符串进行拼接的。我们输入的用户名无所谓，比如：shfhsjfhja ，而密码呢，就是我们精心设计的，如：' or '1' = '1 。

那最终拼接的 SQL 语句，如下所示：

我们知道，or 连接的条件，是或的关系，两者满足其一就可以。所以，虽然用户名密码输入错误，也是可以查询返回结果的，而只要查询到了数据，就说明用户名和密码是正确的。

SQL 注入解决

而通过预编译 SQL（select * from user where username = ? and password = ?），就可以直接解决上述 SQL 注入的问题。接下来，我们再来演示一下，通过预编译 SQL 是否能够解决 SQL 注入问题。

1). 打开课程资料中的文件夹 资料/02. SQL注入演示，运行其中的 jar 包 sql_prepared_demo-0.0.1-SNAPSHOT.jar，进入该目录后，执行命令：

1	java -jar sql_prepared_demo-0.0.1-SNAPSHOT.jar

2). 打开浏览器访问 http://localhost:9090/ ，必须登录后才能访问到系统。我们先测试正常的用户名和密码

3). 那接下来，我们就要演示一下是否可以基于上述的密码 ' or '1' = '1，来完成 SQL 注入。

通过控制台，可以看到输入的 SQL 语句，是预编译 SQL 语句。

而在预编译 SQL 语句中，当我们执行的时候，会把整个 ' or '1'='1 作为一个完整的参数，赋值给第 2 个问号（' or '1'='1 进行了转义，只当做字符串使用）

那么此时再查询时，就查询不到对应的数据了，登录失败。

[!TIP]
**注意：在以后的项目开发中，我们使用的基本全部都是预编译 SQL 语句。 **

性能更高

增删改数据

需求

需求：基于 JDBC 程序，执行如下 update 语句。
SQL：

1	update user set password = '123456', gender = 2 where id = 1;

代码实现

[!TIP]
AI 提示词（prompt）：
你是一名 java 开发工程师，帮我基于 JDBC 程序来操作数据库，执行如下 SQL 语句：
update user set password = ‘123456’, gender = 2 where id = 1;

代码实现如下：

@ParameterizedTest
@CsvSource({"1,123456,25"})
public void testUpdate(int userId, String newPassword, int newAge) throws Exception {
    // 建立数据库连接
    Connection conn = DriverManager._getConnection_("jdbc:mysql://localhost:3306/web", "root", "1234");
    // SQL 更新语句
    String sql = "UPDATE user SET password = ?, age = ? WHERE id = ?";
    // 创建预编译的PreparedStatement对象
    PreparedStatement pstmt = conn.prepareStatement(sql);

    // 设置参数
    pstmt.setString(1, newPassword); // 第一个问号对应的参数
    pstmt.setInt(2, newAge);      // 第二个问号对应的参数
    pstmt.setInt(3, userId);         // 第三个问号对应的参数

    // 执行更新
    int rowsUpdated = pstmt.executeUpdate();

    // 输出结果
    System._out_.println(rowsUpdated + " row(s) updated.");

    // 关闭资源
    pstmt.close();
    conn.close();
}

[!TIP]

JDBC 程序执行 DML 语句：int rowsUpdated = pstmt.executeUpdate(); //返回值是影响的记录数
JDBC 程序执行 DQL 语句：ResultSet resultSet = pstmt.executeQuery(); //返回值是查询结果集

Mybatis

介绍

什么是 MyBatis?

MyBatis 是一款优秀的 持久层 框架，用于简化 JDBC 的开发。
MyBatis 本是 Apache 的一个开源项目 iBatis，2010 年这个项目由 apache 迁移到了 google code，并且改名为 MyBatis 。2013 年 11 月迁移到 Github。
官网：https://mybatis.org/mybatis-3/zh/index.html

在上面我们提到了两个词：一个是持久层，另一个是框架。

持久层：指的是就是数据访问层(dao)，是用来操作数据库的。

框架：是一个半成品软件，是一套可重用的、通用的、软件基础代码模型。在框架的基础上进行软件开发更加高效、规范、通用、可拓展。

通过 Mybatis 就可以大大简化原生的 JDBC 程序的代码编写，比如通过 select * from user 查询所有的用户数据，通过 JDBC 程序操作呢，需要大量的代码实现，而如果通过 Mybatis 实现相同的功能，只需要简单的三四行就可以搞定。

快速入门

需求：使用 Mybatis 查询所有用户数据。

1). 创建 springboot 工程，并导入 mybatis 的起步依赖、mysql 的驱动包、lombok。

项目工程创建完成后，自动在 pom.xml 文件中，导入 Mybatis 依赖和 MySQL 驱动依赖。如下所示：

2). 数据准备：创建用户表 user，并创建对应的实体类 User。

3). 配置 Mybatis

在 application.properties 中配置数据库的连接信息。

#数据库访问的url地址
spring.datasource.url=jdbc:mysql://localhost:3306/web
#数据库驱动类类名
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#访问数据库-用户名
spring.datasource.username=root
#访问数据库-密码
spring.datasource.password=root@1234

上述的配置，可以直接复制过去，不要敲错了。全部都是 spring.datasource.xxxx 开头。

4). 编写 Mybatis 程序：编写 Mybatis 的持久层接口，定义 SQL 语句（注解）

在创建出来的 springboot 工程中，在引导类所在包下，在创建一个包 mapper 。在 mapper 包下创建一个接口 UserMapper ，这是一个持久层接口（Mybatis 的持久层接口规范一般都叫 XxxMapper）。

UserMapper 接口的内容如下：

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

@Mapper
public interface UserMapper {
    /**
     * 查询全部
     */
    @Select("select * from user")
    public List<User> findAll();
}

[!TIP]
注解说明：

@Mapper 注解：表示是 mybatis 中的 Mapper 接口
程序运行时，框架会自动生成接口的实现类对象(代理对象)，并给交 Spring 的 IOC 容器管理
@Select 注解：代表的就是 select 查询，用于书写 select 查询语句

5). 单元测试

在创建出来的 SpringBoot 工程中，在 src 下的 test 目录下，已经自动帮我们创建好了测试类，并且在测试类上已经添加了注解 @SpringBootTest，代表该测试类已经与 SpringBoot 整合。

该测试类在运行时，会自动通过引导类加载 Spring 的环境（IOC 容器）。我们要测试那个 bean 对象，就可以直接通过 @Autowired 注解直接将其注入进行，然后就可以测试了。

测试类代码如下：

@SpringBootTest
class SpringbootMybatisQuickstartApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void testFindAll(){
        List<User> userList = userMapper.findAll();
        for (User user : userList) {
            System._out_.println(user);
        }
    }
}

运行结果：

注意：测试类所在包，需要与引导类所在包相同。

辅助配置

配置 SQL 提示

默认我们在 UserMapper 接口上加的 @Select 注解中编写 SQL 语句是没有提示的。如果想让 idea 给我们提示对应的 SQL 语句，我们需要在 IDEA 中配置与 MySQL 数据库的链接。

默认我们在 UserMapper 接口上的 @Select 注解中编写 SQL 语句是没有提示的。如果想让 idea 给出提示，可以做如下配置：

配置完成之后，发现 SQL 语句中的关键字有提示了，但还存在不识别表名(列名)的情况：

产生原因：Idea 和数据库没有建立连接，不识别表信息
解决方案：在 Idea 中配置 MySQL 数据库连接

按照如下方如下方式，来配置当前 IDEA 关联的 MySQL 数据库（必须要指定连接的是哪个数据库）。

在配置的时候指定连接那个数据库，如上图所示连接的就是 mybatis 数据库（自己的数据库名是什么就指定什么）。

[!TIP]
**注意：**该配置的目的，仅仅是为了在编写 SQL 语句时，有语法提示（写错了会报错），不会影响运行，即使不配置也是可以的。

配置 Mybatis 日志输出

默认情况下，在 Mybatis 中，SQL 语句执行时，我们并看不到 SQL 语句的执行日志。在 application.properties 加入如下配置，即可查看日志：

1 2	#mybatis的配置 mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

打开上述开关之后，再次运行单元测试，就可以看到控制台输出的 SQL 语句是什么样子的。

JDBC VS Mybatis

JDBC 程序的缺点：

url、username、password 等相关参数全部硬编码在 java 代码中。
查询结果的解析、封装比较繁琐。
每一次操作数据库之前，先获取连接，操作完毕之后，关闭连接。频繁的获取连接、释放连接造成资源浪费。

分析了 JDBC 的缺点之后，我们再来看一下在 mybatis 中，是如何解决这些问题的：

数据库连接四要素(驱动、链接、用户名、密码)，都配置在 springboot 默认的配置文件 application.properties 中
查询结果的解析及封装，由 mybatis 自动完成映射封装，我们无需关注
在 mybatis 中使用了数据库连接池技术，从而避免了频繁的创建连接、销毁连接而带来的资源浪费。

[!TIP]
使用 SpringBoot+Mybatis 的方式操作数据库，能够提升开发效率、降低资源浪费

而对于 Mybatis 来说，我们在开发持久层程序操作数据库时，需要重点关注以下两个方面：

application.properties

#驱动类名称
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库连接的url
spring.datasource.url=jdbc:mysql://localhost:3306/web01
#连接数据库的用户名
spring.datasource.username=root
#连接数据库的密码
spring.datasource.password=1234

Mapper 接口（编写 SQL 语句）

@Mapper
public interface UserMapper {
    @Select("select * from user")
    public List<User> list();
}

数据库连接池

在前面我们所讲解的 mybatis 中，使用了数据库连接池技术，避免频繁的创建连接、销毁连接而带来的资源浪费。

下面我们就具体的了解下数据库连接池。

介绍

1). 没有数据库连接池的情况

客户端执行 SQL 语句：要先创建一个新的连接对象，然后执行 SQL 语句，SQL 语句执行后又需要关闭连接对象从而释放资源，每次执行 SQL 时都需要创建连接、销毁链接，这种频繁的重复创建销毁的过程是比较耗费计算机的性能。

2). 有数据库连接池的情况

数据库连接池是个容器，负责分配、管理数据库连接(Connection)

程序在启动时，会在数据库连接池(容器)中，创建一定数量的 Connection 对象

允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个

客户端在执行 SQL 时，先从连接池中获取一个 Connection 对象，然后在执行 SQL 语句，SQL 语句执行完之后，释放 Connection 时就会把 Connection 对象归还给连接池（Connection 对象可以复用）

释放空闲时间超过最大空闲时间的连接，来避免因为没有释放连接而引起的数据库连接遗漏

客户端获取到 Connection 对象了，但是 Connection 对象并没有去访问数据库(处于空闲)，数据库连接池发现 Connection 对象的空闲时间 > 连接池中预设的最大空闲时间，此时数据库连接池就会自动释放掉这个连接对象

数据库连接池的好处：

资源重用
提升系统响应速度
避免数据库连接遗漏

产品

要怎么样实现数据库连接池呢？

官方(sun)提供了数据库连接池标准（javax.sql.DataSource 接口）

功能：获取连接

1	Connection getConnection() throws SQLException;

第三方组织必须按照DataSource接口实现

常见的数据库连接池：**C3P0 **、DBCP 、Druid 、Hikari (springboot默认)

现在使用更多的是：Hikari、Druid （性能更优越）

1). Hikari（追光者） [默认的连接池]

从控制台输出的日志，我们也可以看出，springboot底层默认使用的数据库连接池就是 Hikari。

2). Druid（德鲁伊）

Druid连接池是阿里巴巴开源的数据库连接池项目
功能强大，性能优秀，是Java语言最好的数据库连接池之一

如果我们想把默认的数据库连接池切换为Druid数据库连接池，只需要完成以下两步操作即可：

参考官方地址：https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter


① 在`pom.xml`文件中引入依赖

<dependency>
    <!-- Druid连接池依赖 -->
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.19</version>
</dependency>

②. 在 application.properties 中引入数据库连接配置

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.url=jdbc:mysql://localhost:3306/web
spring.datasource.druid.username=root
spring.datasource.druid.password=1234

配置完毕之后，我们再次运行单元测试，大家会看到控制台输出的日志中，已经将连接池切换为了 Druid 连接池。

增删改查操作

删除

需求：根据 ID 删除用户信息
SQL：delete from user where id = 5;

Mapper 接口方法：

方式一：

_/**_
_ * 根据 id 删除_
_ */_
@Delete("delete from user where id = 5")
public void deleteById();
- 编写单元测试方法进行测试
	在单元测试类中,增加如下测试方法.
@Test
public void testDeleteById(){
    userMapper.deleteById(36);
}

这种方式执行删除操作，调用deleteById方法只能删除id为5的用户信息，因为将id直接写死在代码中了，不可取。

方式二：

在Mybatis中，我们可以通过参数占位符号 #{...} 来占位，在调用deleteById方法时，传递的参数值，最终会替换占位符。

运行单元测试，结果如下：

运行之后，我们发现，#{...} 占位符，其实最终被替换成了？占位符，生成的是预编译的 SQL 语句。【推荐】

Mybatis的提供的符号，有两个，一个是 #{...}，另一个是 ${...}，区别如下：

那在企业项目开发中，强烈建议使用 #{…} 。

新增

如果在 SQL 语句中，我们需要传递多个参数，我们可以把多个参数封装到一个对象中。然后在 SQL 语句中，我们可以通过 #{对象``属性``名} 的方式，获取到对象中封装的属性值。

单元测试：

在测试类中添加测试方法，代码如下：

@Test
public void testInsert(){
    User user = new User();
    user.setUsername("admin");
    user.setPassword("123456");
    user.setName("管理员");
    user.setAge(30);
    userMapper.insert(user);
}

运行结果如下：

修改

需求：根据 ID 更新用户信息
SQL：

1	update user set username = 'zhouyu', password = '123456', name = '周瑜', age = 20 where id = 1；

Mapper 接口方法：

_/**_
_ * 根据id更新用户信息_
_ */_
@Update("update user set username = #{username},password = #{password},name = #{name},age = #{age} where id = #{id}")
public void update(User user);
//如果参数是一个user对象 #{} 中传入的是属性名

单元测试：

在测试类中添加测试方法，代码如下：

@Test
public void testUpdate(){
    User user = new User();
    user.setId(6);
    user.setUsername("admin666");
    user.setPassword("123456");
    user.setName("管理员");
    user.setAge(30);
    userMapper.update(user);
}

运行结果如下：

查询

需求：根据用户名和密码查询用户信息
SQL：select * fromuser whereusername = ‘zhouyu’ and password = ‘123456’
Mapper 接口方法：

_/**_
_ * 根据用户名和密码查询用户信息_
_ */_
@Select("select * from user where username = #{username} and password = #{password}")
public User findByUsernameAndPassword(@Param("username") String username, @Param("password") String password);
// 多参数时java编译成字节码文件后形参名并不会保留,意味着如果要传递多个参数要给参数起名字

@param 注解的作用是为接口的方法形参起名字的。（由于用户名唯一的，所以查询返回的结果最多只有一个，可以直接封装到一个对象中）

单元测试：

在测试类中添加测试方法，代码如下：

@Test
public void testFindByUsernameAndPassword(){
    User user = userMapper.findByUsernameAndPassword("admin666", "123456");
    System._out_.println(user);
}

运行结果如下：

[!TIP]
说明：基于官方骨架创建的 springboot 项目中，接口编译时会保留方法形参名，@Param 注解可以省略 (#{形参名})。

XML 映射配置

Mybatis 的开发有两种方式：

注解
XML

XML 配置文件规范

使用 Mybatis 的注解方式，主要是来完成一些简单的增删改查功能。如果需要实现复杂的 SQL 功能，建议使用 XML 来配置映射语句，也就是将 SQL 语句写在 XML 配置文件中。

[!TIP]
在 Mybatis 中使用 XML 映射文件方式开发，需要符合一定的规范：

XML 映射文件的名称与 Mapper 接口名称一致，并且将 XML 映射文件和 Mapper 接口放置在相同包下（同包同名）
XML 映射文件的 namespace 属性为 Mapper 接口全限定名一致
XML 映射文件中 sql 语句的 id 与 Mapper 接口中的方法名一致，并保持返回类型一致。

resultType 属性，指的是查询返回的单条记录所封装的类型。

XML 配置文件实现

第 1 步：创建 XML 映射文件

第 2 步：编写 XML 映射文件

xml 映射文件中的 dtd 约束，直接从 mybatis 官网复制即可; 或者直接 AI 生成。

<?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="">
 
</mapper>

第 3 步：配置

a. XML 映射文件的 namespace 属性为 Mapper 接口全限定名

<?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="com.itheima.mapper.UserMapper">

</mapper>

b. XML 映射文件中 sql 语句的 id 与 Mapper 接口中的方法名一致，并保持返回类型一致

<?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="com.itheima.mapper.EmpMapper">

    <!--查询操作-->
    <select id="findAll" resultType="com.itheima.pojo.User">
        select * from user
    </select>
    
</mapper>

resultType 属性的值，与查询返回的单条记录封装的类型一致。

运行测试类，执行结果：

[!TIP]
注意：一个接口方法对应的 SQL 语句，要么使用注解配置，要么使用 XML 配置，切不可同时配置。

MybatisX 的使用

MybatisX 是一款基于 IDEA 的快速开发 Mybatis 的插件，为效率而生。

MybatisX 的安装：

可以通过 MybatisX 快速定位：

MybatisX 的使用在后续学习中会继续分享。

[!TIP]

学习了 Mybatis 中 XML 配置文件的开发方式了，大家可能会存在一个疑问：到底是使用注解方式开发还是使用 XML 方式开发？

官方说明：https://mybatis.net.cn/getting-started.html。下面是官方说明:

**结论：**使用 Mybatis 的注解，主要是来完成一些简单的增删改查功能。如果需要实现复杂的 SQL 功能，建议使用 XML 来配置映射语句。

mybatis中基于注解数据封装问题[当pojo实体类中的属性与数据库的字段名不一致时,数据不会映射]

SpringBoot 配置文件

介绍

前面我们一直使用 springboot 项目创建完毕后自带的 application.properties 进行属性的配置，而如果在项目中，我们需要配置大量的属性，采用 properties 配置文件这种 key=value 的配置形式，就会显得配置文件的层级结构不清晰，也比较臃肿。

那其实呢，在 springboot 项目当中是支持多种配置方式的，除了支持 properties 配置文件以外，还支持另外一种类型的配置文件，就是我们接下来要讲解的 yml 格式的配置文件。yml 格式配置文件名字为：application.yaml , application.yml 这两个配置文件的后缀名虽然不一样，但是里面配置的内容形式都是一模一样的。

我们可以来对比一下，采用 application.properties 和 application.yml 来配置同一段信息(数据库连接信息)，两者之间的配置对比：

[!TIP]
在项目开发中，我们推荐使用 application.yml 配置文件来配置信息，简洁、明了、以数据为中心。

.yaml 和 .yml 只是 YAML 文件的两种不同文件扩展名，两者在内容和格式上完全一致。

为什么会存在两个扩展名？

.yml：早期受 DOS/Windows 系统 3 字符扩展名限制（如 config.yml），习惯沿用至今。
.yaml：更符合 YAML 全称 “YAML Ain’t Markup Language” 的拼写，现在也是主流推荐。

语法

简单的了解过 springboot 所支持的配置文件，以及不同类型配置文件之间的优缺点之后，接下来我们就来了解下 yml 配置文件的基本语法：

大小写敏感
数值前边必须有空格，作为分隔符
使用缩进表示层级关系，缩进时，不允许使用 Tab 键，只能用空格（idea 中会自动将 Tab 转换为空格）
缩进的空格数目不重要，只要相同层级的元素左侧对齐即可
# 表示注释，从这个字符一直到行尾，都会被解析器忽略

了解完 yml 格式配置文件的基本语法之后，接下来我们再来看下 yml 文件中常见的数据格式。在这里我们主要介绍最为常见的两类：

定义对象或 Map 集合
定义数组、list 或 set 集合

对象/Map 集合

user:
  name: zhangsan
  age: 18
  password: 123456

数组/List/Set 集合

hobby: 
  - java
  - game
  - sport

[!TIP]
在 yml 格式的配置文件中，如果配置项的值是以 0 开头的，值需要使用 ‘’ 引起来，因为以 0 开头在 yml 中表示 8 进制的数据。

案例

熟悉完了 yml 文件的基本语法后，我们修改下之前案例中使用的配置文件，变更为 application.yml 配置方式：

修改 application.properties 名字为：_application.properties（名字随便更换，只要加载不到即可）
创建新的配置文件： application.yml

原有的 application.properties 配置文件

新建的 application. yml 配置文件

配置文件的内容如下：

#数据源配置
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/web01
    username: root
    password: root@1234
#mybatis配置
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

05-Web后端基础(数据库)

前言

引言

相关概念

MySQL 概述

安装

连接

介绍

企业使用方式(了解)

数据模型

关系型数据库**（RDBMS）**

数据模型

SQL 语句

DDL 语句

数据库操作

查询数据库

创建数据库

使用数据库

删除数据库

图形化工具

介绍

安装

连接数据库

表操作

创建

约束

数据类型

表结构设计-案例

表操作-其他操作

DML 语句

增加(insert)

语法

案例演示

修改(update)

语法

案例演示

删除(delete)

语法

案例演示

DQL 语句

介绍

语法

基本查询

条件查询

聚合函数

分组查询

排查查询

分页查询

06-Web 后端基础(java 操作数据库)

前言

JDBC

介绍

查询数据

需求

准备工作

代码实现

代码剖析

ResultSet

预编译 SQL

SQL 注入

SQL 注入解决

性能更高

增删改数据

需求

代码实现

Mybatis

介绍

快速入门

辅助配置

配置 SQL 提示

配置 Mybatis 日志输出

JDBC VS Mybatis

数据库连接池

介绍

产品

增删改查操作

删除

新增

修改

查询

关系型数据库（RDBMS）