JavaWeb 笔记 05 (50 - 72)

MySQL, JDBC, MyBatis 相关 (50 - 72)

提前声明：本文章仅作个人学习记录，不涉及商业用途。

内容记录于学习途中, 没有按分类进行总结, 可能存在错误或不完整之处。

视频地址

在线讲义地址

第一章 MySQL

一、连接 MySQL

1. 显式密码方式进行连接

-- 连接 MySQL
> mysql -u<username> -p<password> [-h<IP Address> -P<port>]
-- `-h` 默认 127.0.0.1
-- `-P` 默认 3306

2. 隐式密码方式进行连接

-- 隐式密码方式
> mysql -u<username> -p
Enter password: <password>

3. 命令分类

分类 全称 说明
DDL Data Definition Language 定义数据库对象
DML Data Manipulation Language 对数据库表中的数据进行增删改
DQL Data Query Language 查询数据库中表的记录
DCL Data Control Language 创建数据库对象、控制数据库的访问权限

二、DDL 语句

1. 数据库操作

-- 查询所有数据库
SHOW databases;

-- 查询当前数据库
SELECT database(); -- NULL 表示没有使用任何数据库

-- 创建数据库
CREATE DATABASE [ if not exists ] '<database_name>'
[default charset utf8mb4];

-- 使用数据库
USE '<database_name>';

-- 删除数据库
DROP DATABASE [ if exists ] '<database_name>';

2. 用户管理

-- 创建用户
CREATE USER '<username>'@'<host>'
IDENTIFIED BY '<password>';

-- 删除用户
DROP USER '<username>'@'<host>';

-- 修改密码
ALTER USER '<username>'@'<host>'
IDENTIFIED BY '<password>';

-- 重命名用户
RENAME USER '<username>'@'<host>'
TO '<new_username>'@'<host>';

-- 授予权限
GRANT '<privilege>'
ON '<database_name>'.'<table_name>'
TO '<username>'@'<host>';

-- 撤销权限
REVOKE '<privilege>'
ON '<database_name>'.'<table_name>'
FROM '<username>'@'<host>';
常用语句
-- CI 不区分大小写
CREATE DATABASE tlias CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER 'fallw'@'localhost' IDENTIFIED BY 'real_password';

GRANT ALL PRIVILEGES ON tlias.* TO 'fallw'@'localhost';

USE tlias;

3. 表操作

创建表
-- 创建表
CREATE TABLE table_name (
  column1_name column1_type [constraints] [COMMENT 'column1_comment'],
  ......
) [COMMENT 'table_comment'];

-- 示例：创建用户表
CREATE TABLE tb_user (
  id int comment 'ID,唯一标识',
  username varchar(20) comment '用户名',
  name varchar(10) comment '姓名',
  age int comment '年龄',
  gender char(1) comment '性别'
) comment '用户表';
MySQL 数据类型
类别 数据类型 大小 (字节) 描述/范围 样例
数值型 TINYINT \(1\) 有符号: \([-128, 127]\); 无符号: \([0, 255]\) age TINYINT UNSIGNED
SMALLINT \(2\) 有符号: \([-32768, 32767]\); 无符号: \([0, 65535]\) status SMALLINT
MEDIUMINT \(3\) 有符号: \([-8388608, 8388607]\); 无符号: \([0, 16777215]\) count MEDIUMINT
INT / INTEGER \(4\) 有符号: \([-2^{31}, 2^{31}-1]\); 无符号: \([0, 2^{32}-1]\) id INT AUTO_INCREMENT
BIGINT \(8\) 有符号: \([-2^{63}, 2^{63}-1]\); 无符号: \([0, 2^{64}-1]\) user_id BIGINT
FLOAT \(4\) 单精度浮点数，约 \(7\) 位小数精度 score FLOAT(5,2)
DOUBLE \(8\) 双精度浮点数，约 \(15\) 位小数精度 salary DOUBLE(10,2)
DECIMAL (M,D) \(M+2\) 精确固定点数，M 为总位数，D 为小数位数 amount DECIMAL(10,2)
BIT \(1 \sim 8\) 位字段类型，用于存储位值 is_deleted BIT(1)
日期/时间型 DATE \(3\) 日期值 YYYY-MM-DD (\([\)1000-01-01, 9999-12-31\(]\)) birthday DATE
TIME \(3\) 时间值 HH:MM:SS (\([\)-838:59:59, 838:59:59\(]\)) work_time TIME
DATETIME \(8\) 日期和时间组合 YYYY-MM-DD HH:MM:SS created_at DATETIME
TIMESTAMP \(4\) 时间戳 YYYY-MM-DD HH:MM:SS (\([\)1970-01-01, 2038\(]\)) update_time TIMESTAMP
YEAR \(1\) 年份值 YYYY (\([\)1901, 2155\(]\)) birth_year YEAR
字符串型 CHAR (M) \(M\) 固定长度字符串，M 为 \([0, 255]\) 之间的整数 id_card CHAR(18)
VARCHAR (M) \(L+1\) 可变长度字符串，L 为实际长度 \(+1\) 或 \(2\) 字节开销 name VARCHAR(50)
TINYTEXT \(L+1\) 最大长度 \(255\) (\(2^8-1\)) 字节 tag TINYTEXT
TEXT \(L+2\) 最大长度 \(65,535\) (\(2^{16}-1\)) 字节 content TEXT
MEDIUMTEXT \(L+3\) 最大长度 \(16,777,215\) (\(2^{24}-1\)) 字节 blog_body MEDIUMTEXT
LONGTEXT \(L+4\) 最大长度 \(4,294,967,295\) (\(2^{32}-1\)) 字节 html_source LONGTEXT
BINARY (M) \(M\) 固定长度二进制字符串 file_hash BINARY(16)
VARBINARY (M) \(L+1\) 可变长度二进制字符串 signature VARBINARY(256)
TINYBLOB \(L+1\) 最大长度 \(255\) 字节 small_icon TINYBLOB
BLOB \(L+2\) 最大长度 \(65,535\) 字节 avatar_img BLOB
MEDIUMBLOB \(L+3\) 最大长度 \(16,777,215\) 字节 video_clip MEDIUMBLOB
LONGBLOB \(L+4\) 最大长度 \(4,294,967,295\) 字节 large_file LONGBLOB
枚举/集合 ENUM \(1\) 或 \(2\) 枚举类型，最多包含 \(65535\) 个成员 sex ENUM('M','F')
SET \(1, 2, 3, 4\) 或 \(8\) 集合类型，最多包含 \(64\) 个成员 hobbies SET('A','B','C')
JSON JSON 可变 存储 JSON 文档，MySQL 5.7+ 支持 extra_info JSON
空间数据 GEOMETRY 等 可变 空间数据类型，如 POINT, LINESTRING, POLYGON 等 location GEOMETRY
约束 Constraint
关键字 约束 描述
NOT NULL 非空约束 限制该字段值不能为 NULL
UNIQUE 唯一约束 保证字段的所有数据都是唯一、不重复的
PRIMARY KEY 主键约束 主键是一行数据的唯一标识，要求非空且唯一
DEFAULT 默认约束 保存数据时，如果未指定该字段值，则采用默认值
FOREIGN KEY 外键约束 让两张表的数据建立连接，保证数据的一致性和完整性
查询表结构
-- 查看当前数据库的所有表
SHOW tables;

-- 查看指定的表结构
DESC '<table_name>';

-- 查询指定表的建表语句
SHOW CREATE TABLE '<table_name>';
添加字段
-- 添加字段
ALTER TABLE '<table_name>'
ADD '<column_name>' '<column_type>(<column_length>)'
[comment] [constraint];

-- 示例：添加 QQ 号码字段
ALTER TABLE tb_user
ADD qq varchar(11)
COMMENT 'QQ 号码';
修改字段类型
-- 修改字段类型
ALTER TABLE '<table_name>'
MODIFY '<column_name>' '<new_column_type>(<column_length>)'
[comment] [constraint];

-- 示例：修改 QQ 号码字段类型
ALTER TABLE tb_user
MODIFY qq varchar(13)
COMMENT 'QQ 号码';
修改字段名
-- 修改字段名
ALTER TABLE '<table_name>'
CHANGE '<old_column_name>' '<new_column_name>' '<new_column_type>(<column_length>)'
[comment] [constraint];

-- 示例：修改 QQ 号字段名称
ALTER TABLE tb_user
CHANGE qq qq_number varchar(13)
COMMENT 'QQ 号码';
删除字段
-- 删除字段
ALTER TABLE '<table_name>'
DROP '<column_name>';

-- 示例：删除 QQ 号码字段
ALTER TABLE tb_user DROP qq_number;
修改表名
-- 修改表名
RENAME TABLE '<old_table_name>'
TO '<new_table_name>';

-- 示例：修改用户表名
RENAME TABLE tb_user TO tb_users;
删除表
-- 删除表
DROP TABLE [ if exists ] '<table_name>';

三、DML 语句

1. 增加 Insert

-- 向指定字段添加数据
INSERT INTO '<table_name>' ('<column1>', '<column2>')
VALUES ('<value_1>', '<value_2>');

-- 向全部字段添加数据
INSERT INTO '<table_name>'
VALUES ('<value_1>', '<value_2>', ...);

-- 批量插入数据（指定字段）
INSERT INTO '<table_name>' ('<column1>', '<column2>')
VALUES ('<value_1>', '<value_2>'), ('<value_1>', '<value_2>');

-- 批量插入数据（全部字段）
INSERT INTO '<table_name>'
VALUES ('<value_1>', '<value_2>', ...), ('<value_1>', '<value_2>', ...);

-- 示例：插入员工信息
INSERT INTO employees (name, age, sex, salary)
VALUES ('fallw', 22, 'M', 2000), ('lily', 18, 'F', 3000);

2.修改 Update

-- 修改数据
UPDATE '<table_name>'
SET '<column1>' = '<value_1>', '<column2>' = '<value_2>'
[where '<condition>'];

-- 示例：修改 ID 为 1 的员工信息
UPDATE employees
SET name = 'fallw', age = 22
WHERE id = 1;

3.删除 Delete

DELETE FROM '<table_name>'
[where '<condition>'];

-- 示例：删除 ID 为 1 的员工信息
DELETE FROM employees WHERE id = 1;

四、DQL 语句

1. DQL 查询语法结构

SELECT '<column_list>'
FROM '<table_list>'
WHERE '<condition_list>'
GROUP BY '<group_column_list>'
HAVING '<post_group_condition_list>'
ORDER BY '<sort_column_list>'
LIMIT '<pagination_params>'

2. 基本查询

-- 查询多个字段
SELECT '<column1>', '<column2>'...
FROM '<table_name>';

-- 查询所有字段（通配符）
SELECT *
FROM '<table_name>';

-- 设置别名
SELECT '<column1>' [AS '<alias1>'], column2 [AS '<alias2>']
FROM '<table_name>';

-- 去除重复记录
SELECT DISTINCT '<column_list>'
FROM '<table_name>';

3. 条件查询

SELECT '<column_list>'
FROM '<table_name>'
where '<condition_list>';

-- 示例：查询 ID 为 1 的员工姓名
SELECT name FROM employees WHERE id = 1;
条件运算符
条件运算符 描述
> 大于
>= 大于等于
< 小于
<= 小于等于
= 等于
<> 或 != 不等于
between... and... 在某个范围之间（含最大、最小值）
in(...) in 之后列表中的值多选一
like 模糊匹配（_ 匹配单个字符，% 匹配任意个字符）
is null 为 NULL
逻辑运算符
逻辑运算符 描述
and 或 && 与
or 或 || 或
not 或 ! 非

4. 聚合查询

聚合函数 描述
count() 计数
sum() 求和
avg() 平均值
max() 最大值
min() 最小值 
-- 示例：统计用户数量
SELECT count(*) FROM users;

-- 示例：统计用户数量（去重）
SELECT count(distinct user_id) FROM orders;

-- 示例：统计员工平均工资
SELECT avg(salary) FROM employees;

5. 分组查询

SELECT '<column_list>'
FROM '<table_name>'
GROUP BY '<group_column_list>';
[HAVING '<post_group_condition_list>']

-- 示例：按性别统计员工数量
SELECT gender, count(*) FROM emp GROUP BY gender;

-- 示例：查询入职时间在 2015 年之前的员工数量大于 2 的职位
SELECT job, count(*)
FROM emp
WHERE entry_date <= '2015-01-01'
GROUP BY job
HAVING count(*) >= 2;

执行顺序：WHERE > 聚合函数 > HAVING

6. 排序查询

SELECT '<column_list>'
FROM '<table_name>'
[WHERE '<condition_list>']
[GROUP BY '<group_column_list>']
ORDER BY '<sort_column_list>';
排序方式 描述
ASC 升序（默认值）
DESC 降序 
-- 示例：按员工入职时间升序排序，入职时间相同时，按照更新时间进行降序排列
SELECT * FROM employees
ORDER BY entry_date ASC, update_time DESC;

7. 分页查询

SELECT '<column_list>'
FROM '<table_name>'
LIMIT '<pagination_params>';

-- 示例：查询员工信息，每页显示 10 条数据，第 2 页
SELECT * FROM employees LIMIT 10 OFFSET 10;
-- 或者
SELECT * FROM employees LIMIT 10, 10;

第二章 JDBC

一、引入依赖

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.33</version>
</dependency>

二、测试类

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

三、MyBatis

1. Mapper 接口示例

// 原 com.fallw.mapper.DepartmentMapper
@Mapper
public interface DepartmentMapper {
  @Select("select * from department")
  List<Department> getDepartments();

  @Delete("delete from department where id = #{deptId}")
  void deleteDepartmentById(Integer deptId);

  @Insert("insert into department (name, create_time, update_time) values (#{name}, #{createTime}, #{updateTime})")
  void insertDepartment(Department department);

  @Select("select * from department where id = #{deptId}")
  Department getDepartmentById(Integer deptId);

  @Update("update department set name = #{name}, update_time = #{updateTime} where id = #{id}")
  void updateDepartment(Department department);
}

2. xml 文件示例

<!-- com/fallw/mapper/StudentMapper.xml -->
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fallw.mapper.StudentMapper">
  <resultMap id="StudentVOResultMap" type="com.fallw.entity.Student">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="no" property="no"/>
    <result column="gender" property="gender"/>
    <result column="phone" property="phone"/>
    <result column="id_card" property="idCard"/>
    <result column="is_college" property="isCollege"/>
    <result column="address" property="address"/>
    <result column="degree" property="degree"/>
    <result column="graduation_date" property="graduationDate"/>
    <result column="course_id" property="courseId"/>
    <result column="violation_count" property="violationCount"/>
    <result column="violation_score" property="violationScore"/>
    <result column="create_time" property="createTime"/>
    <result column="update_time" property="updateTime"/>
    <result column="is_deleted" property="isDeleted"/>
    <result column="courseName" property="courseName"/>
  </resultMap>

  <select id="list" resultMap="StudentVOResultMap">
    select s.*, c.name as courseName
    from student s
    left join course c on s.course_id = c.id
    <where>
      <if test="studentQueryParam.name != null and studentQueryParam.name != ''">
        and s.name like concat('%',#{studentQueryParam.name},'%')
      </if>
      <if test="studentQueryParam.degree != null and studentQueryParam.degree != ''">
        and s.degree = #{studentQueryParam.degree}
      </if>
      <if test="studentQueryParam.courseId != null and studentQueryParam.courseId != ''">
        and s.course_id = #{studentQueryParam.courseId}
      </if>
      and s.is_deleted = 0
    </where>
    order by s.update_time desc
  </select>

  <select id="countStudentDegreeData" resultType="com.fallw.pojo.dto.ChartData">
    select
    case degree
    when 1 then '初中'
    when 2 then '高中'
    when 3 then '大专'
    when 4 then '本科'
    when 5 then '硕士'
    when 6 then '博士'
    else '其他'
    end as name,
    count(*) as value
    from student
    group by degree
  </select>

  <!-- 统计各个班级的人数 -->
  <select id="countStudentCourseData" resultType="com.fallw.pojo.dto.ChartData">
    SELECT
    c.name AS name,
    COUNT(s.id) AS value
    FROM student s
    INNER JOIN course c ON s.course_id = c.id
    GROUP BY c.id, c.name
  </select>
</mapper>

三、MyBatis Plus

1. Warpper

方法分类 方法头 功能说明
比较运算 eq(R column, Object val) 等于 =
ne(R column, Object val) 不等于 <>
gt(R column, Object val) 大于 >
ge(R column, Object val) 大于等于 >=
lt(R column, Object val) 小于 <
le(R column, Object val) 小于等于 <=
模糊查询 like(R column, Object val) 模糊查询 %值%
likeLeft(R column, Object val) 左模糊 %值
likeRight(R column, Object val) 右模糊 值%
范围查询 between(R column, Object v1, Object v2) 在 v1 和 v2 之间
in(R column, Collection<?> coll) 字段 IN (值1, 值2, ...)
逻辑运算 and(Consumer<Param> consumer) AND 嵌套 (用于复杂逻辑)
or() 拼接 OR
排序分组 orderByDesc(R... columns) 倒序排序 ORDER BY ... DESC
groupBy(R... columns) 分组 GROUP BY
更新专用 set(R column, Object val) UpdateWrapper 专用，设置 SET 部分

2. BaseMapper<T>

方法分类 方法名 (及典型函数头) 功能说明
插入 insert(T entity) 插入一条记录
删除 deleteById(Serializable id) 根据 ID 删除
deleteByMap(Map<String, Object> map) 根据 Map 条件删除
delete(Wrapper<T> wrapper) 根据条件构造器删除
更新 updateById(T entity) 根据 ID 修改记录
update(T entity, Wrapper<T> wrapper) 根据条件修改记录
查询 selectById(Serializable id) 根据 ID 查询
selectBatchIds(Collection<? extends Serializable> idList) 根据 ID 集合批量查询
selectOne(Wrapper<T> wrapper) 根据条件查询一条（多于一条报错）
selectList(Wrapper<T> wrapper) 根据条件查询全部列表
selectPage(IPage<T> page, Wrapper<T> wrapper) 分页查询
selectCount(Wrapper<T> wrapper) 获取满足条件的记录数

3. IService / ServiceImpl<M, T>

方法分类 方法名 (及典型函数头) 功能说明
保存/更新 save(T entity) 插入一条记录
saveOrUpdate(T entity) 根据 ID 判断，存在则更新，不存在则插入
saveBatch(Collection<T> entityList) 批量插入（默认分批 1000 条）
删除 removeById(Serializable id) 根据 ID 删除
removeByMap(Map<String, Object> columnMap) 根据 Map 条件删除
remove(Wrapper<T> queryWrapper) 根据条件删除
查询 getById(Serializable id) 根据 ID 查询
getOne(Wrapper<T> queryWrapper) 查询一条记录
list(Wrapper<T> queryWrapper) 查询集合
listByIds(Collection<? extends Serializable> idList) 批量 ID 查询
page(IPage<T> page, Wrapper<T> queryWrapper) 分页查询
count(Wrapper<T> queryWrapper) 查询总记录数
链式调用 query() 返回 LambdaQueryChainWrapper
update() 返回 LambdaUpdateChainWrapper

4. 分页插件

组件名 类型 功能说明
Page<T> 实体类 实现 IPage 接口。用于封装分页参数（当前页 current、每页大小 size）和返回的查询结果。
MybatisPlusInterceptor 插件类 插件主体容器。需要向其添加 PaginationInnerInterceptor 才能开启物理分页。

5. 静态工具类

类名 常用方法 功能说明
Db Db.getById(id, Entity.class) 静态 CRUD 工具。直接根据实体类类型执行操作，底层自动查找对应的 Mapper。
Db.saveBatch(Collection<T>) 静态批量保存，逻辑与 ServiceImpl 中的批量操作一致。
Wrappers Wrappers.<T>lambdaQuery() 构造器工厂。快速创建 LambdaQueryWrapper 对象，支持链式编程且代码更简洁。

6. 核心模型基类

基类/注解 类型 功能说明
Model<T> 基类 实体类继承此基类后，可直接调用 entity.insert()、entity.updateById() 等方法，无需通过 Service。
@TableId 注解 指定主键属性。支持多种主键策略：AUTO (自增)、ASSIGN_ID (雪花算法)、INPUT (手动输入) 等。
@InterceptorIgnore 注解 用于方法或 Mapper 上。标记后可忽略全局插件（如忽略多租户权限过滤、忽略全表更新校验）。
posted @ 2026-04-08 17:00  fallw  阅读(1)  评论(0)    收藏  举报