JB2-3-MySQL（一）

Java道经第2卷 - 第3阶 - MySQL（一）


传送门：JB2-3-MySQL（一）
传送门：JB2-3-MySQL（二）

心法：本章使用 Maven 父子结构项目进行练习。

练习项目结构如下：

|_ v2-3-web-mysql
	|_ test

武技：搭建练习项目结构。

1. 创建父项目 v2-3-web-mysql，删除 src 目录。
2. 创建子项目 test，不需要添加任何依赖。

S01. 关系型数据库

E01. MySQL基础概念

心法：关系型数据库适用于数据结构固定、需要强事务一致性（如金融交易、订单管理）、复杂关联查询的场景，而非关系型数据库更适合数据结构灵活（如 JSON 文档）、高并发读写、无需复杂关联（如日志记录、用户行为分析、实时数据缓存）的场景。

数据库 DataBase, DB：一个可以持久化存储数据以及简单分析数据的应用软件。

关系型数据库 Relational Database Management System, RDMS：由二维表组成，格式一致，易于维护，支持事务，不灵活，如 MySQL，Oracle 等。

非关系型数据库 Non-Relational Database Management System, NRDMS：由文档，键值对，图片等组成，使用灵活，但不支持事务，如 Redis，MongoDB 等。

1. MySQL基础概念

心法：MySQL 是一款优秀的 RDMS 关系型数据库，由瑞典 mysql-ab 公司开发，目前属于 Oracle 旗下产品，采用双授权政策，分为社区版（开源免费，支持定制）和商业版，体积小，速度快，使用标准的 SQL 数据语言形式，跨平台，支持多种语言，如 C，C++，Python，Java，PHP 等。

仓库大小：MySQL 支持 5000 万条记录的数据仓库：

• 32 位系统表文件最大可支持 4GB。
• 64 位系统支持最大的表文件为 8TB。

2. MySQL存储引擎

心法：MySQL 存储引擎是负责数据存储，读取和管理的核心组件，不同引擎基于不同的存储机制，事务支持和锁策略，适配不同的业务场景。

MyISAM 引擎：MySQL 在 5 版本之前默认使用：

• 不支持事务和行级锁，只支持表级锁，查询效率较高，但在并发写入时性能较差。
• 适用于以读操作为主、对数据一致性要求不高的应用，如博客系统、新闻网站等。

InnoDB 引擎：MySQL 在 5 版本之后默认使用：

• 支持事务处理、行级锁、外键约束等特性，提供了较好的数据一致性和并发支持。
• 适用于对数据一致性要求高、有大量并发读写操作的应用，如电商系统、银行系统等。

Memory 引擎：MySQL 全版本支持切换使用，但不默认使用：

• 数据存储在内存中，读写速度非常快，但数据在服务器重启后会丢失。
• 支持表级锁，不支持事务。
• 使用哈希索引，适合于对速度要求极高、数据量较小且不需要持久化存储的数据。
• 常用于临时表、缓存表或用于存储一些实时性要求高但不需要长期保存的数据，如在线游戏中的临时数据、统计信息等。

3. MySQL组织架构

心法：一个 OS 中可以同时安装多个不同版本的 MySQL Server，一个 Server 中可以同时创建多个 MySQL Instance（用户是共享一个 Server 中的所有 Instance 的），一个 Instance 中可以同时创建多张 Table 表，一个 Table 中可以存放多条 Record 记录。

E02. MySQL服务搭建

武技：在 Docker 中搭建 MySQL 单机容器。

1. 安装MySQL容器

1. 准备相关目录：

# 创建 MySQL 相关目录
mkdir -p /opt/mysql/single/conf;
mkdir -p /opt/mysql/single/data;
mkdir -p /opt/mysql/single/log;
chmod -R 777 /opt/mysql;

2. 开发配置文件：my.cnf 是 MySQL 数据库的核心配置文件，用于定义服务器的运行参数和行为：

touch /opt/mysql/single/conf/my.cnf;
echo '[mysqld]' >> /opt/mysql/single/conf/my.cnf;
echo 'character-set-client-handshake = FALSE' >> /opt/mysql/single/conf/my.cnf;
echo 'character-set-server = utf8mb4' >> /opt/mysql/single/conf/my.cnf;
echo 'collation-server = utf8mb4_unicode_ci' >> /opt/mysql/single/conf/my.cnf;
echo 'default-time-zone = Asia/Shanghai' >> /opt/mysql/single/conf/my.cnf;
echo 'default_authentication_plugin = mysql_native_password' >> /opt/mysql/single/conf/my.cnf;

3. 降低配置文件 my.cnf 的权限：否则系统上任何用户都可以修改该文件，存在安全风险：

# 降低 my.cnf 文件的权限
chmod 644 /opt/mysql/single/conf/my.cnf;

4. 搭建 MySQL 单机容器：

# 拉取镜像（二选一）
docker pull mysql:8;
docker pull registry.cn-hangzhou.aliyuncs.com/joezhou/mysql:8;

# 创建并运行容器
# 参数 `-e MYSQL_ROOT_PASSWORD=root`: 设置 root 用户的密码为 root
# 参数 `-e TZ=Asia/Shanghai`: 设置时区
docker run --name mysql \
  --network my-net \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=root \
  -e TZ=Asia/Shanghai \
  -v /opt/mysql/single/conf:/etc/mysql/conf.d \
  -v /opt/mysql/single/data:/var/lib/mysql \
  -v /opt/mysql/single/log:/var/log/mysql \
  -itd registry.cn-hangzhou.aliyuncs.com/joezhou/mysql:8;

# 查看容器
docker ps --format "table {{.ID}}\t{{.Names}}\t{{.Ports}}"
docker logs mysql --tail 30

# 永久开放 3306 端口
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

5. 查看 MySQL 版本：

# 进入 MySQL 容器
docker exec -it mysql bash

# 在 MySQL 容器中：查看 MySQL 版本
mysqladmin --version

# 进入 MySQL 命令行（盲敲密码后回车进入）
mysql -uroot -p

# 在 MySQL 命令行中：查看当前登录的用户名
select user();

# 退出 MySQL 命令行
exit;

# 退出容器
exit;

2. 安装可视化工具

心法：MySQL 可视化工具是一类通过图形化界面（GUI）替代传统命令行操作，帮助用户更便捷地管理、操作和监控 MySQL 数据库的软件工具，它的核心价值在于降低技术门槛、提升操作效率，尤其适合非技术人员或需要频繁进行数据库管理的场景。

MySQL 常用可视化工具如下：

• MySQL GUI Tools：MySQL 官方提供的图形化管理工具，功能强大，可惜没有中文界面。
• MySQL Workbench：MySQL 官方推出的一个开源的可视化工具，支持数据库设计，SQL 开发。服务器管理等功能。
• Navicat for MySQL：一款商业化的 MySQL 可视化工具，提供了完整的数据库管理功能，推荐使用。
• phpMyAdmin：一款基于 Web 的 MySQL 可视化工具，可以通过浏览器访问和管理 MySQL 数据库。
• HeidiSQL：一款免费开源的 MySQL 可视化工具，支持多种数据库管理操作。
• DBeaver：一款跨平台的数据库管理工具，支持多种数据库管理操作。
• DataGrip：JetBrains 发布的多引擎数据库环境，支持多种数据库，包括 MySQL、PostgreSQL、Microsoft SQL Server、Oracle 等。它提供了强大的 SQL 编辑和调试功能，以及数据库结构和数据的可视化展示。
• SQLyog：一款易于使用、快速而简洁的图形化管理 MySQL 数据库的工具，能够在任何地点有效地管理数据库。它提供了直观的用户界面，支持数据库设计、查询执行、数据备份和恢复等功能。
• MySQL ODBC Connector：MySQL 官方提供的 ODBC 接口程序，系统安装该程序后，就可以通过 ODBC 来访问 MySQL，这样就可以实现 SQLServer、Access 和 MySQL 之间的数据转换，还可以支持 ASP 访问 MySQL 数据库。

武技：使用 IDEA 作为 MySQL 可视工具。

1. 点击 Database - 加号 - Data Source - MySQL 进入 MySQL 配置页面：

2. 在 General 选项卡中添加 MySQL 本地驱动 jar 包，该 jar 可以直接在你的 Maven 仓库中寻找：

3. 在 General 选项卡中填写 MySQL 连接的基本信息：

3. 导入表数据

武技：利用 IDEA 导入表结构以及表数据。

1. 在 D 盘根目录新建一个 测试表.sql 文件，并写入如下内容：

-- 创建部门表
DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
    deptno INT(2) NOT NULL COMMENT '部门编号',
    dname VARCHAR(14) COMMENT '部门名称',
    loc VARCHAR(13) COMMENT '部门所在地',
    PRIMARY KEY (deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

-- 插入测试数据
INSERT INTO dept (deptno, dname, loc) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH',   'DALLAS'),
(30, 'SALES',      'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

-- 创建员工表
DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
    empno INT(4) NOT NULL COMMENT '员工编号',
    ename VARCHAR(10) COMMENT '员工姓名',
    job VARCHAR(9) COMMENT '职位',
    mgr INT(4) COMMENT '直属领导编号',
    hiredate DATE COMMENT '入职日期',
    sal DECIMAL(7,2) COMMENT '月薪',
    comm DECIMAL(7,2) COMMENT '奖金',
    deptno INT(2) COMMENT '所属部门编号',
    PRIMARY KEY (empno),
    FOREIGN KEY (deptno) REFERENCES dept(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';

-- 插入测试数据
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, 'SMITH',  'CLERK',     7902, '1980-12-17', 800.00,  NULL,    20),
(7499, 'ALLEN',  'SALESMAN',  7698, '1981-02-20', 1600.00, 300.00,  30),
(7521, 'WARD',   'SALESMAN',  7698, '1981-02-22', 1250.00, 500.00,  30),
(7566, 'JONES',  'MANAGER',   7839, '1981-04-02', 2975.00, NULL,    20),
(7654, 'MARTIN', 'SALESMAN',  7698, '1981-09-28', 1250.00, 1400.00, 30),
(7698, 'BLAKE',  'MANAGER',   7839, '1981-05-01', 2850.00, NULL,    30),
(7782, 'CLARK',  'MANAGER',   7839, '1981-06-09', 2450.00, NULL,    10),
(7788, 'SCOTT',  'ANALYST',   7566, '1987-04-19', 3000.00, NULL,    20),
(7839, 'KING',   'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL,    10),
(7844, 'TURNER', 'SALESMAN',  7698, '1981-09-08', 1500.00, 0.00,    30),
(7876, 'ADAMS',  'CLERK',     7788, '1987-05-23', 1100.00, NULL,    20),
(7900, 'JAMES',  'CLERK',     7698, '1981-12-03', 950.00,  NULL,    30),
(7902, 'FORD',   'ANALYST',   7566, '1981-12-03', 3000.00, NULL,    20),
(7934, 'MILLER', 'CLERK',     7782, '1982-01-23', 1300.00, NULL,    10);

2. 在指定的数据库目录上右键，选择 SQL Scripts → Run SQL Script 项。
3. 找到你要导入的 SQL 文件，点击 OK，即可完成导入。

如图：

4. 导出表数据

武技：利用 IDEA 导出表结构以及表数据。

1. 在 1 或 N 张表上右键并选择 Import/Export → Export Data to Files 项（按住 shift 可以多选）。
2. 选择 SQL Insert 并勾选 Add table definition（DDL），否则只导出了数据，没有表结构。
3. 在 Output directory 栏选择路径，导出 SQL 文件。

如图：

S02. 结构化查询语言

心法：结构化查询语言 Structured Query Language 简称 SQL，用于操作关系型数据库的标准语言，目前数据库厂商实现的都是 SQL92 或 SQL99 标准，按功能分为 DDL，DCL，DML 和 DQL 四种。

SQL语言分类	简称	全称	说明
数据定义语言	DDL	Data Definition Language	用于对 DB 元数据如表，用户，列，索引等进行 create，drop，set 等操作
数据控制语言	DCL	Data Control Language	用于对 DB 用户进行 grant 赋予权限和 revoke 回收权限操作
数据操作语言	DML	Data Manipulation Language	用于对 DB 表中的数据进行 update，insert，delete 等操作
数据查询语言	DQL	Data Query Language	用于对 DB 表中的数据进行 select 操作

E01. DDL用户操作

1. 创建新用户

心法：MySQL 用户共享一个 MySQL 服务中的全部 MySQL 数据库。

常用语句如下：

• 查看用户：
  • select user()：查看当前用户。
  • select user, host from mysql.user：查看全部用户以及 host 地址。
• 创建用户：
  • create user '账号'@'localhost'：仅允许该用户访问本地的数据库实例，无密码。
  • create user '账号'@'localhost' identified by '密码'：仅允许该用户访问本地的数据库实例。
  • create user '账号'@'192.168.40.77' identified by '密码'：仅允许该用户访问指定 IP 地址的数据库实例。
  • create user '账号'@'%' identified by '密码'：允许该用户访问所有主机中的数据库实例（包括本地 + 远程）。
• 修改密码：
  • alter user '账号'@'地址' identified with mysql_native_password by '新密码'
• 删除用户：
  • drop user '账号'@'地址'：删除指定用户，用户不存在会报错。
  • drop user if exists '账号'@'地址'：删除指定用户，仅当用户存在时执行。

武技：使用 root 登录，并创建 joezhou用户，密码设置为 joezhou 即可，要求允许该用户访问所有主机中的数据库实例。

-- 创建新用户
create user 'joezhou'@'%' identified by 'joezhou';

-- 查看全部用户
select user, host
from mysql.user;

2. 给用户赋权（DCL）

心法：新创建的用户没有任何权限，需要使用 grant 语句进行赋权，赋权操作属于 DCL 范畴。

常用语句如下：

• 查询权限：
  • show grants for '账号'@'地址'：查询指定用户的权限（USAGE 表示无权限）。
• 赋予权限：
  • grant all privileges on *.* to '账号'@'地址'：为指定用户赋予全部权限（针对全部库的全部表）。
  • grant all privileges on a.* to '账号'@'地址'：为指定用户赋予全部权限（针对 a 库的全部表）。
  • grant all privileges on a.b to '账号'@'地址'：为指定用户赋予全部权限（针对 a 库的 b表）。
  • grant insert,delete on *.* to '账号'@'地址'：为指定用户赋予 insert 和 delete 权限（针对全部库的全部表）。
  • grant all privileges on *.* to '账号'@'地址' with grant option：为指定用户赋予 insert 和 delete 权限（针对全部库的全部表），且该用户可以为其他用户授权。
• 刷新权限：
  • flush privileges：用户赋权之后，要立刻执行一次刷新权限命令，否则可能导致赋权失败。
• 回收权限：
  • revoke all on *.* from '账号'@'地址'：回收指定账号的全部权限。
  • revoke insert,delete on *.* from '账号'@'地址'：回收指定账号的 insert 和 delete 权限。

常用权限列表如下：权限范围按粒度从小到大排列（如列 < 表 < 库 < 服务器）：

权限名称	作用范围	具体权限描述
all	整个服务	拥有对整个服务的所有操作权限
select	表 / 列	允许对表 / 列执行查询（读取）操作
insert	表 / 列	允许向表中插入新行数据
update	表 / 列	允许更新表中的行数据
delete	表	允许删除表中的行数据
create	库 / 表 / 索引	允许创建数据库、表或索引
drop	库 / 表 / 视图	允许删除数据库、表或视图
reload	整个服务	允许使用 flush 语句重新加载配置或刷新日志等
grant option	库 / 表 / 存储过程	允许将自身权限授予其他用户（授权权限）
references	库 / 表	允许操作外键约束的父表（与表关联关系相关）
index	表	允许创建或删除表的索引
alter	表	允许修改表结构（如添加 / 删除字段、修改字段类型等）
show databases	服务器	允许查看数据库列表（显示所有数据库名称）
super	服务器	拥有超级权限（如终止其他用户进程、修改系统级参数等）
create temporary tables	表	允许创建临时表
lock tables	库	允许对库中的表执行锁定操作（阻塞其他用户的读写）
execute	存储过程	允许执行存储过程
replication client	服务器	允许查看主 / 从服务器状态及二进制日志信息
replication slave	服务器	允许作为从服务器参与主从复制（获取主服务器数据）
create view	视图	允许创建视图
show view	视图	允许查看视图的定义和数据
create routine	存储过程	允许创建存储过程或函数
alter routine	存储过程	允许修改或删除已有的存储过程或函数（需注意权限依赖）
create user	服务器	允许创建、修改或删除用户账号
event	库	允许创建、修改、删除或查看数据库中的事件（定时任务）
create tablespace	服务器	允许创建、修改或删除表空间及日志文件（需谨慎操作，涉及物理存储）
proxy	服务器	允许代理其他用户执行操作（模拟其他用户身份）
usage	服务器	无实际权限（默认权限，表示用户仅能连接服务器，但无法执行任何操作）

武技：使用 root 登录，并为 joezhou 用户赋予全部权限。

-- 查询用户权限: USAGE 表示无权限
show grants for 'joezhou'@'%';

-- 为用户赋权
grant all privileges on *.* to 'joezhou'@'%' with grant option;

-- 查询用户权限
show grants for 'joezhou'@'%';

-- 刷新权限: 用户赋权之后，要立刻执行一次刷新权限命令，否则可能导致赋权失败
flush privileges;

E02. DDL实例操作

1. 创建数据库实例

心法：一个 MySQL 服务中可以创建多个 MySQL 数据库实例。

常用语句如下：

• 查看数据库：
  • show databases：展示 MySQL 服务端中的全部数据库实例。
  • select version(), database()：查看当前数据库版本和名称。
  • show variables like '%time_zone%'：查看当前数据库的全局变量中的时区。
  • select @@character_set_database：查看当前数据库的编码。
• 创建数据库：
  • create database 库名 character set 编码
  • use 库名：切换数据库。
• 修改数据库：
  • set time_zone = '时区'：修改指定时区（临时）。
  • alter database 库名 character set 编码：修改指定数据库的编码。
• 删除数据库：
  • drop database 库名：数据库不存在会报错。
  • drop database if exists 库名：删除指定用户，仅当用户存在时执行。

武技：创建数据库实例，命名为 mysql8，字符编码为 utf8mb4 即可。

-- 展示 MySQL 服务端中的全部数据库实例
show databases;

-- 创建数据库实例: 数据库实例名为 mysql8
create database mysql8 character set utf8mb4;

-- 切换当前数据库: 切换为 mysql8
use mysql8;

E03. DDL表格操作

1. 数据库三范式

心法：数据库设计三范式（3NF）是帮助我们设计更好的数据库表的规范，不一定非要严格执行这个标准（有时候为了效率，数据库表的设计可能会违反数据库三范式），但它对你设计数据库来说，无疑是一个很好的建议和帮助。

第一范式 - 1NF：表中的每一列都保持了原子性，不能再拆分，则满足 1NF：

• 如：用户表｛姓名，性别，电话｝
• 其中｛电话｝可以再拆成｛家庭电话，公司电话｝
• 所以不满足 1NF。

第二范式 - 2NF：满足 1NF 基础上，表有主键，且每一列都与主键相关，则满足 2NF：

• 如：
  • 订单表｛订单ID，商品ID，下单日期｝
  • 商品表｛商品ID，商品名称｝
• 订单表中的｛商品ID｝和｛订单ID｝无关，所以不满足 2NF，应该删除，再使用中间表如下：
  • 订单表｛订单ID，下单日期｝
  • 商品表｛商品ID，商品名称｝
  • 中间表｛订单ID，商品ID｝

第三范式 - 3NF：满足 2NF 基础上，每一列都与主键直接相关，而不是间接相关，则满足 3NF：

• 如：
  • 订单表｛订单ID，用户ID，用户姓名｝
  • 用户表｛用户ID，用户姓名｝
• 订单表中的所有字段都和订单相关，满足 2NF，但实际上是｛用户姓名｝和｛用户ID｝直接相关，｛用户ID｝和｛订单ID｝直接相关，导致｛用户姓名｝和｛订单ID｝不是直接相关，而是间接相关，所以不满足 3NF。
• 应该将｛用户姓名｝移动到用户表中，而不应该出现在订单表中，如下：
  • 订单表｛订单ID，用户ID｝
  • 用户表｛用户ID，用户姓名｝

2. 表数据类型

心法：数据库表字段的数据类型，一般能用小的就别用大的，能用固定的就别用变化的。

常用的数字类型如下：小数类型统一使用 decimal，禁止使用 float 和 double，规避丢失精度的问题：

数据类型	中文	类型大小	描述
tinyint	极小整数	1 字节	有符号范围 -128 ~ 127 无符号范围 0 ~ 255
smallint	小整数	2 字节	有符号范围：-32768 ~ 32767 无符号范围：0 ~ 65535
mediumint	中等整数	3 字节	有符号范围：-8388608 ~ 8388607 无符号范围：0 ~ 16777215
integer(int)	整数	4 字节	有符号范围 -232 ~ 232-1 无符号范围 0 ~ 约 42.9 亿
bigint	大整数	8 字节	有符号范围 -263 ~ 263-1 无符号范围 0 ~ 约 1019
float	单精度浮点数	4 字节	精确到小数点后 6 - 7 位 范围约 ±1.175494351×10-38 ~ ±3.402823466×1038
double	双精度浮点数	8 字节	精确到小数点后 15 - 17 位 范围约 ±2.2250738585072014×10-308 ~ ±1.7976931348623157×10308
decimal(n, m)	浮点数	取决于精度和规模	m 表示小数位数，小数位数超出 m 则四舍五入，不足 m 则补 0 m + 整数位数 + 1(小数点) 的总和必须不超出 n，超出报错

常用的时间类型如下：日期类型推荐统一使用 datetime 类型：

数据类型	中文	类型大小	描述
time	时间	3 字节	范围：-838:59:59 ~ 838:59:59（可表示负数，常用于时间间隔计算）
date	日期	3 字节	范围 1000-01-01 ~ 9999-12-31
timestamp	时间戳	4 字节	范围：‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC 自动更新为当前时间（需配合 ON UPDATE CURRENT_TIMESTAMP）
datetime	日期 + 时间	8 字节	范围 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

常用的文本类型如下：前缀字节用于记录数据实际长度，例如 text 的 2 字节前缀可表示最大 2^16 - 1 = 65535 字节的数据长度：

数据类型	中文	类型大小	描述
char(n)	定长字符串	一个 U8 中文占 3 个字节（无字节前缀）	固定长度为 n，实际内容不足时使用空格补充至 n
varchar(n)	变长字符串	一个 U8 中文占 3 个字节（1 ~ 2 字节前缀）	最大长度为 n，相比 char，空间利用率提高但查询效率降低 长度尽量保持在 5000 字节以内。
tinytext	微小文本	最大 255 字节（1 字节前缀）	存储短文本，如状态描述、标签等
text	文本	最大 65535 字节（2 字节前缀）	存储中等长度文本，如文章段落、评论内容
mediumtext	中等文本	最大 16777215 字节（3 字节前缀）	存储较长文本，如小说章节、详细说明
longtext	长文本	最大 4294967295 字节（4 字节前缀）	存储超大文本，如日志文件、长篇内容

常用的二进制类型如下：

数据类型	中文	类型大小	描述
tinyblob	微小二进制	最大 255 字节（1 字节前缀）	存储小二进制数据，如缩略图、小文件
blob	二进制	最大 65535 字节（2 字节前缀）	存储中等二进制数据，如普通图片、音频片段
mediumblob	中等二进制	最大 16777215 字节（3 字节前缀）	存储较大二进制数据，如高清图片、视频片段
longblob	长二进制	最大 4294967295 字节（4 字节前缀）	存储超大二进制数据，如大型文件、数据库备份

3. 创建数据库表

心法：MySQL 的数据最终都存放在表中，每个表最多存储 4096 列数据，每个表的大小不能超过 65535 字节，每个表的记录行数建议控制在 500 万以内。

数据库表设计规范推荐如下：

• 表名规范：
  • MySQL 表名和字段名可由字母，数字，下划线组成。
  • MySQL 表名建议使用 “全小写 + 下划线” 格式。
  • MySQL 表名且禁止使用复数单词和缩写。
• 表名前缀：表名允许使用以下前缀作为业务说明：
  • ums_XXX：用户管理模块，如用户表，角色表，权限表，菜单表等。
  • pms_XXX：商品管理模块，如商品表，商品分类表等。
  • oms_XXX：订单管理模块，如订单表，订单明细表，购物车表等。
  • cms_XXX：内容管理模块，如评论表，举报表等。
  • sms_XXX：营销管理模块，如广告轮播图表，活动表等。
• 必设字段：MySQL 每张表都强烈建议设置以下三个字段：
  • id bigint auto_increment：主键，对应 Long 型属性。
  • created datetime：首次创建时间，对应 LocalDateTime 型属性。
  • updated datetime：最后修改时间，对应 LocalDateTime 型属性。

常用语句如下（表相关）：若当前库清晰，直接使用 表名 操作即可，若当前库不清晰，建议使用 库名.表名 的格式操作：

• 查看表：
  • show tables：查看当前数据库中的表（此命令无法看到临时表）。
  • show create table 表名：查看指定表的建表语句（此命令可以看到临时表）。
  • desc 表名：查看指定表的表结构（此命令可以看到临时表）。
  • select table_name, column_name, column_type, character_maximum_length, is_nullable, column_default, column_comment from information_schema.columns where table_schema = '库名' and table_name = '表名'： 查看指定表的详细表结构（此命令无法看到临时表）。
• 创建表：
  • create table 表名( 字段 类型, 字段 类型 .. )：创建指定表，表已存在则报错。
  • create table if not exists 表名( 字段 类型, 字段 类型 .. )：创建指定表，仅不存在时创建。
  • 优化01：每张表都建议添加主键，并设置 auto_increment 自增（只能用于数字列）以提高查询效率。
  • 优化02：每张表及字段都建议使用 comment '注释' 指定注释，以提高可读性。
  • 优化03：字段列表末尾使用 primary key (主键字段) 来指定主键。
  • 优化04：表的末尾支持使用 engine 引擎名称 指定引擎，默认追随数据库实例的配置。
  • 优化05：表的末尾支持使用 default charset 编码 指定字符集，默认追随数据库实例的配置。
  • 优化06：每个字段都尽可能使用 not null 添加非空约束以提高查询效率。
  • 优化06：每个字段都尽可能使用 default 默认值 添加默认值以提高查询效率。
• 复制表：
  • create table 表名B (select * from 表名A)：快速复制表 A 为表 B，包括表结构和表数据。
• 重命名表：
  • rename table 表名A to 表名B：将表 A 重命名为表 B。
• 删除表：
  • drop table 表名：删除指定库中的指定表，表不存在会报错。
  • drop table if exists 表名：删除指定库中的指定表，仅存在时删除。

常用语句如下（表字段相关）：

• 新增字段：
  • alter table 表名 add 字段名 字段类型 [非空约束] [默认值] [注释]：为指定表增加指定列。
• 修改字段：
  • alter table 表名 modify 字段名 [新字段类型] [非空约束] [默认值] [注释]：为指定表修改指定列，无法修改字段名。
  • alter table 表名 change 字段名 新字段名 [新字段类型] [非空约束] [默认值] [注释]：为指定表修改指定列，必须指定新字段名（可以不变）。
• 删除字段：
  • alter table 表名 drop column 字段名：为指定表删除指定列。

武技：在 mysql8 数据库中创建 user 表。

-- 切换当前库为 mysql8
use mysql8;

-- 查看当前数据库中的表: 此命令无法看到临时表
show tables;

-- 创建表 user
create table `user`
(
    `id`       bigint auto_increment comment '主键',
    `username` varchar(64)   not null default '' comment '登录账号',
    `password` varchar(64)   not null default '' comment '登录密码',
    `realname` char(4)       not null default '' comment '真实姓名',
    `age`      tinyint       not null default 0 comment '用户年龄',
    `gender`   tinyint       not null default 0 comment '用户性别：0女1男2保密',
    `weight`   decimal(6, 2) not null default 0.00 comment '用户体重，单位斤',
    `created`  datetime      not null default current_timestamp comment '创建日期',
    `updated`  datetime      not null default current_timestamp comment '修改日期',
    primary key (id)
)
    engine = innodb
    default character set utf8mb4
    comment '用户表';

-- 查看 user 表的详细表结构
select table_name               '表名',
       column_name              '字段名称',
       column_type              '字段类型',
       character_maximum_length '字段长度',
       is_nullable              '是否必填',
       column_default           '默认值',
       column_comment           '注释'
from information_schema.columns
where table_schema = 'mysql8'
  and table_name = 'user';

4. 创建临时表

心法：临时表用于保存临时数据，仅当前连接可用，关闭连接时自动销毁并释放资源，支持正常的 CRUD 和 drop 操作，但不支持重命名等操作，且在 Idea 的 Database 面板中不可见。

常用语句如下：

• 查看临时表：
  • show create table 表名：查看临时表的建表语句。
  • desc 表名：查看指定临时表的表结构。
• 创建临时表：
  • create temporary table 表名( 字段 类型, 字段 类型 .. )：创建临时表，表已存在则报错，表名建议以 tmp_ 为前缀并以日期为后缀。
  • create temporary table if not exists 表名( 字段 类型, 字段 类型 .. )：创建临时表，仅不存在时创建。
• 删除临时表：
  • drop table 表名：删除指定库中的指定临时表，表不存在会报错。
  • drop table if exists 表名：删除指定库中的指临时定表，仅存在时删除。

武技：测试临时表操作。

-- 切换当前库为 mysql8
use mysql8;

-- 创建临时表
create temporary table tmp_user_20240225
(
    id bigint auto_increment comment '临时表主键',
    primary key (id)
) comment '用户临时表';

-- 查看临时表结构
desc tmp_user_20240225;

-- 查看临时表的建表语句
show create table tmp_user_20240225;

-- 删除临时表
drop table if exists tmp_user_20240225;

5. 操作表数据（DML）

心法：表中数据的新增，修改和删除操作，统称为数据的 DML 操作。

常用语句如下：

• 新增记录：
  • insert into 表名 (字段列表) values (值列表)：值列表必须和字段列表的个数，顺序，类型一一对应。
  • insert into 表名 (字段列表) values (值列表), (值列表) ..：支持批量添加。
  • insert into 表名 values (值列表)：全字段有序添加时，可以省略字段列表部分。
• 修改记录：
  • update 表名 set 字段 = 新值, 字段 = 新值 .. where 条件：必须设置修改内容和修改条件，否则报错。
• 删除记录：
  • truncate table 表名：直接清空表中的全部记录。
  • delete from 表名 where 条件：必须设置删除条件，否则报错。

武技：测试数据的增删改操作。

-- 切换当前库为 mysql8
use mysql8;

-- 单条增加记录
insert into user (username, password, realname, age, gender, weight, created, updated)
values ('zhaosi', 'zhaosi', '赵国强', 58, 1, 150.20, '2008-08-08: 08:08:08', '2009-09-09 09:09:09');

-- 批量增加记录
insert into user (username, password, realname, age, gender, weight, created, updated)
values ('liuneng', 'liuneng', '刘能', 59, 1, 152.20, '2006-06-06: 06:06:06', '2007-07-07 07:07:07'),
       ('guangkun', 'guangkun', '谢广坤', 60, 1, 158.20, '2004-04-04: 04:04:04', '2005-05-05 05:05:05');

-- 全字段添加时的简写形式
insert into user
values (4, 'wangyun', 'wangyun', '王云', 18, 2, 100.20, '2002-02-02: 02:02:02', '2003-03-03 03:03:03');

-- 修改记录: 必须设置修改内容和修改条件，否则SQL语句报错
update user
set realname = '赵四',
    age      = 25
where id = 1;

-- 删除记录: 必须设置删除条件
delete
from user
where id = 3;

-- 清空表记录
truncate table user;

E04. DDL约束操作

心法：约束就是对 column 添加一些强制校验规则以保证数据的正确性，添加约束前尽量保持表中无数据或者无错误数据。

除非空约束外，均可以使用以下语句查看某张表的约束情况：

-- 查看约束情况
select table_schema    '库名',
       table_name      '表名',
       constraint_name '约束名',
       constraint_type '约束类型'
from information_schema.table_constraints
where table_schema = '库名'
  and table_name = '表名';

1. 非空约束

心法：非空约束 not null 规定在字段插入 null 值时报错，非空列可以提高查询效率，建议配合 default 一起使用（默认值在缺省时生效），但不要对外键字段设置非空约束。

添加非空约束：

• 造表时：在某个字段末尾添加 not null default 默认值
• 造表后：alter table 表名 modify 字段 类型 not null default 默认值

删除非空约束：

• alter table 表名 modify 字段 类型 null

武技：测试非空约束的操作。

-- 切换当前库为 mysql8
use mysql8;

-- 创建非空约束测试表（造表时，直接添加非空约束）
create table null_test
(
    id   bigint,
    name varchar(50) not null default ''
) comment '非空约束测试表';

-- 查看表结构：发现 id 列允许为空，name 列不允许为空
desc null_test;

-- 造表后，为指定字段添加非空约束
alter table null_test
    modify id bigint not null default 0;

-- 再次查看表结构：发现 id 列不允许为空
desc null_test;

-- 删除非空约束
alter table null_test
    modify name varchar(50) null;

-- 再次查看表结构：发现 name 列允许为空
desc null_test;

2. 唯一约束

心法：唯一约束 unique key 规定在字段插入重复值时报错。

添加唯一约束：

• 造表时：
  • 方式一：在某个字段末尾添加 unique 关键字，约束名使用 字段名。
  • 方式二：在字段列表整体末尾添加 unique (字段) 语句，约束名使用 字段名。
• 造表后：
  • alter table 表名 add unique (字段)：为指定表的指定字段添加唯一约束，约束名使用 字段名。
  • alter table 表名 add constraint 约束名 unique (字段)：为指定表的指定字段添加 指定约束名的 唯一约束。

删除唯一约束：

• alter table 表名 drop constraint 唯一约束名

武技：测试唯一约束的操作。

-- 切换当前库为 mysql8
use mysql8;

-- 创建唯一约束测试表
create table unique_test
(
    -- 造表时，直接添加唯一约束（方式一）
    id     bigint unique,
    name   varchar(50),
    gender tinyint,
    age    int,
    -- 造表时，直接添加唯一约束（方式二）
    unique (name)
) comment '唯一约束测试表';

-- 查看 unique_test 表约束情况
select table_schema    '库名',
       table_name      '表名',
       constraint_name '约束名',
       constraint_type '约束类型'
from information_schema.table_constraints
where table_schema = 'mysql8'
  and table_name = 'unique_test';

-- 造表后，为指定字段添加唯一约束，指定约束名为 uk_gender
alter table unique_test
    add constraint uk_gender unique (gender);

-- 造表后，为指定字段添加唯一约束，默认唯一约束名为字段名
alter table unique_test
    add unique (age);

-- 删除指定字段上的唯一约束，通过约束名称删除
alter table unique_test
    drop constraint uk_gender;

3. 主键约束

心法：主键约束 primary key 规定该列唯一且非空，一张表只能存在一个主键约束列（必须为唯一的自增列指定为主键），默认固定使用 PRIMARY 作为约束名称。

添加主键约束：

• 造表时：
  • 方式一：在某个字段末尾添加 primary key 关键字。
  • 方式二：在字段列表整体末尾添加 primary key (字段) 语句。
• 造表后：
  • alter table 表名 add primary key (字段)：为指定表的指定字段添加唯一约束。
  • alter table 表名 add constraint 约束名 primary key (字段)：为指定表的指定字段添加 指定约束名的 唯一约束。

删除主键约束：

• alter table 表名 drop primary key

武技：测试主键约束的操作。

-- 切换当前库为 mysql8
use mysql8;

-- 创建主键约束测试表
create table primary_test_01
(
    -- 造表时，直接添加主键约束（方式一）
    id bigint primary key
) comment '主键约束测试表01';

-- 创建主键约束测试表
create table primary_test_02
(
    id bigint,
    -- 造表时，直接添加主键约束（方式二）
    primary key (id)
) comment '主键约束测试表02';

-- 创建主键约束测试表
create table primary_test_03
(
    id bigint
) comment '主键约束测试表03';

-- 造表后，添加主键约束
alter table primary_test_03
    add primary key (id);

-- 查看 primary_test_01 和 primary_test_02 表约束情况
select table_schema    '库名',
       table_name      '表名',
       constraint_name '约束名',
       constraint_type '约束类型'
from information_schema.table_constraints
where table_schema = 'mysql8'
  and table_name like 'primary\_test\_%';

-- 删除主键约束
alter table primary_test_03
    drop primary key;

4. 外键约束

心法：外键约束 foreign key 就是用 主表某字段 连接 从表主键（或唯一约束字段），双方必须同类型，同长度，同符号，外键约束虽然提高了安全性，但开发中建议尽量避免使用外键约束（但一定要在关联字段上建立索引），因为它影响效率。

添加外键约束：

• 造表时：在字段列表整体末尾添加 foreign key (外键字段) references 从表(从表主键) 语句。
• 造表后：
  • alter table 表名 add foreign key (外键字段)：为指定表的指定字段添加外键约束，约束名使用 随机名。
  • alter table 表名 add constraint 约束名 foreign key (外键字段) references 从表(从表主键)：为指定表的指定字段添加 指定约束名的 外键约束。

删除外键约束：

• alter table 表名 drop foreign key 约束名：根据指定的约束名删除外键约束。

武技：测试外键约束的操作。

-- 切换当前库为 mysql8
use mysql8;

-- 创建班级表
create table clazz
(
    id    bigint auto_increment,
    title varchar(20),
    primary key (id)
) comment '班级表';

-- 添加两条部门信息
insert into clazz values (1, '三年一班'), (2, '三年二班');

-- 创建学生表
create table student_01
(
    id          bigint auto_increment,
    name        varchar(20),
    fk_clazz_id bigint,
    primary key (id),
    -- 造表时，添加外键约束，约束名随机
    foreign key (fk_clazz_id) references clazz (id)
) comment '学生表';

-- 创建学生表
create table student_02
(
    id          bigint auto_increment,
    name        varchar(20),
    fk_clazz_id bigint,
    primary key (id)
) comment '学生表';

-- 主表的某字段，连接从表的主键（唯一约束字段），约束名随机
alter table student_02
    add foreign key (fk_clazz_id) references clazz (id);

-- 创建学生表
create table student_03
(
    id          bigint auto_increment,
    name        varchar(20),
    fk_clazz_id bigint,
    primary key (id)
) comment '学生表';

-- 主表的某字段，连接从表的主键（唯一约束字段），指定约束名
alter table student_03
    add constraint fk_clazz_id foreign key (fk_clazz_id) references clazz (id);

-- 查看 student_01，student_02 和 student_03 表的约束情况
select table_schema    '库名',
       table_name      '表名',
       constraint_name '约束名',
       constraint_type '约束类型'
from information_schema.table_constraints
where table_schema = 'mysql8'
  and table_name like 'student\_%';

E05. DQL基本查询

心法：select 字段列表 from 查询范围

基础规则：

1. 字符类型数据用单引号或双引号标注，建议使用单引号。
2. 数字类型数据可以直接进行计算。
3. null 值和任何值进行计算结果都为 null。
4. 判断字段是否为 null 时，不能使用等号，应该使用 is null 或 is not null 关键字。
5. 查询结果可以使用 distinct(字段) 去重，只能对单独一列使用。

优化建议：

对比项	学习写法	工作写法
星号	使用星号表示全部字段列表，更简单	不允许使用星号，减少解析时间
语句单词大小写	全小写，可读性高	关键字大写，字段或表名小写，减少解析时间
记录单词大小写	和记录保持一致	和记录保持一致
字段别名	非必要不使用，更简单	尽量起别名，减少解析时间和重名歧义，更严谨
表别名	非必要不使用，更简单	尽量起别名，减少解析时间和重名歧义，更严谨
as 关键字	能省则省	能省则省

一条查询语句的执行顺序如下：

执行顺序	关键字	描述
1	from	确定要查询的数据来自哪些表
2	on	多表联合时，使用 ON 关键字定义连接条件
3	where	在执行查询之前，逐行进行条件判断
4	group by	按一个或多个列对结果集进行分组
5	having	在分组后，使用 HAVING 子句对分组后的结果集进行过滤
6	select	根据 SELECT 子句中选择的项目来检索数据
7	distinct	去除结果集中的重复行
8	order by	对结果集进行排序
9	limit	对结果集进行分页

武技：测试 DQL 基本规则。

use mysql8;

-- 基本查询格式: `select 字段列表 from 查询范围`。
select empno, ename from emp;

-- 字符类型数据用单/双引号标注，建议使用单引号。
select * from emp where ename = 'SMITH';

-- 数字类型数据可以直接进行计算。
select sal, sal + 1000 result from emp;

-- null值和任何值进行计算结果都为null
select comm, comm + 1000 result from emp;

-- 查询所有拥有奖金的员工
-- 判断时使用 `is null/is not null`。
select * from emp where comm != 0;
select * from emp where comm is not null;

-- 使用 `distinct(字段)` 可以去重，但只能对单独的一列使用。
select job from emp;
select distinct(job) from emp;

-- 字段列表不建议使用 `*`，更不建议将 `*` 和其它字段一起查询。
select * from emp;

-- 关键字大写，字段或表名小写，减少解析时间
SELECT * FROM emp WHERE ename = 'WARD';
SeLeCt * FrOM emp WHerE ENAME = 'WARD';

-- 记录中的单词尽量和记录保持一致，减少解析时间
SELECT * FROM emp WHERE ename = 'ward';
SELECT * FROM emp WHERE ename = 'WARD';

-- 字段建议起别名，别名不建议使用中文。
select empno id, ename as empName from emp;

-- 表名建议起别名，别名不建议使用中文。
select e.empno, e.ename from emp e;

1. 查询排序

心法：select 字段列表 from 查询范围 order by 排序字段 排序方式, 排序字段 排序方式 ..

排序方式:

• order by 字段 asc：根据指定字段 升序 排序，asc 关键字可以省略，即默认升序。
• order by 字段 desc：根据指定字段 降序 排序，desc 关键字不可以省略。

排序特点：查询排序不参与任何计算，也不会改变任何一个值。

支持别名：order by 在 select 之后执行，因此在 order by 后，可以使用字段的别名。

武技：测试 DQL 查询排序。

use mysql8;

-- 查询全部员工，要求按工资升排（asc 可以省略）
select * from emp order by sal;
select * from emp order by sal asc;

-- 查询全部员工，要求按工资降排（desc 不可以省略）
select * from emp order by sal desc;

-- 支持别名
select * from emp e order by e.sal desc;

-- 查询全部员工，要求按工资升排，若工资相同，再按主键降排
select * from emp order by sal, empno desc;

2. 条件查询

心法：select 字段列表 from 查询范围 where 条件 用于在查询的过程中，逐行 进行条件判断，只有满足条件的记录才会被保留。

条件符号：多个条件允许使用 and，or，not，<>，!= 等符号，且 and 优先级高于 or，但要注意，即使多个条件，where 关键字也只能有一个。

条件查询相关语句如下：

• ifnull(字段, a)：若字段为 null 则返回 a，否则返回原字段。
• if(表达式, a, b)：若表达式结果为真（非 0、非 null），则返回 a，否则返回 b，支持嵌套。
• if(条件, a, b)：若条件成立，返回 a，否则返回 b，支持嵌套。
• case when 条件 then 返回值 end：相当于 Java 中的多分支 if 结构。

优化建议：为了提高效率，筛选力度大（能过滤掉更多数据）的条件尽量往前放，筛选效率低（如模糊查询）的条件尽量往后放。

武技：测试 DQL 条件查询。

use mysql8;

-- 查询20部门中，除了smith之外的所有员工
select * from emp where deptno = 20 and ename != 'SMITH';

-- 查询员工月总工资（工资 + 提成）
select sal, comm, sal + ifnull(comm, 0) total from emp;

-- 列出所有员工是否拥有额外收入
select comm, if(comm, '有额外收入', '无额外收入') result from emp;

-- 列出所有员工是否是有钱人（工资大于1000就是有钱人）
select sal, if(sal > 1000, '有钱人', '没钱人') result from emp;

-- 月薪在0~1000的视为码农，月薪在1001~3000的视为程序员，月薪超过3000的视为工程师
select sal, (
    case
    when sal > 0 and sal <= 1000 then '码农'
    when sal > 1000 and sal <= 3000 then '程序员'
    when sal > 3000 then '工程师'
    end ) result
from emp;

3. 模糊查询

心法：select 字段列表 from 查询范围 where 字段 like 表达式

模糊查询常用占位符如下：

• %：表示随意个数占位：
  • 如 .. where ename like 'M%'
• _：表示只占一位：
  • 如 .. where ename like '_M'
• \：转义字符，支持使用 escape 单独指定符号：
  • 如 .. where ename like '%\%%'
  • 如 .. where ename like '%a%%' escape a

注意事项：

1. 数据类型限制：like 仅对字符串类型字段（如 char, varchar, text 等）有效，对数值型字段使用会隐式转换，性能差，如 empno like '73%' 不如 empno >= 7300 and empno < 7400 性能高。
2. 避免前模糊：如 “%赵” 的写法会让索引失效，可通过全文索引、业务拆分优化。
3. 避免大量数据使用模糊查询：小数据量模糊查询直接用 like，大数据量优先用 regexp 或全文检索。
4. 空值处理：like 不会匹配 null 值，若需查询 null，需单独加 or ename is null 进行过滤。

武技：测试 DQL 模糊查询。

use mysql8;

-- 查询姓名以 `M` 开头的员工
select ename from emp where ename like 'M%';

-- 查询姓名第 2 位是 `U` 的员工
select ename from emp where ename like '_U%';

-- 查询姓名包含 `E` 的员工
select ename from emp where ename like '%E%';

-- 查询姓名中带 单引号 的员工
select ename from emp where ename like '%\'%';

-- 查询姓名中带 百分号 的员工
select ename from emp where ename like '%a%%' escape 'a';

4. 正则查询

心法：select 字段列表 from 查询范围 where 字段 regexp 表达式：其中 regexp 关键字等价于 rlike 关键字（为了兼容）。

正则查询常用表达式如下：

• ^S：查询姓名以 S 开头的记录。
• H$：查询姓名以 H 结尾的记录。
• ^S.*H$：查询姓名以 S 开头并且以 H 结尾的记录。
• A：查询姓名包含 A 的记录。
• ^.{2}R：查询姓名第 3 位是 R 的记录。
• ^[aeiou]|H$：查询姓名以元音字母开头或以 H 结尾的记录。

注意事项：

1. 大小写敏感：MySQL 默认的 regexp 匹配区分大小写。
2. 空值处理：若匹配字段为 null，regexp 会返回 null（不会匹配成功），可结合 is not null 过滤。
3. 性能建议：正则查询对大数据量表性能较低，若仅需简单的开头 / 结尾 / 包含匹配，优先使用 like 模糊查询。

武技：测试 DQL 正则查询。

use mysql8;

-- 查询姓名以 `S` 开头的员工
select ename from emp where ename regexp '^S';

-- 查询姓名以 `H` 结尾的员工
select ename from emp where ename regexp 'H$';

-- 查询姓名以 `S` 开头并且以 `H` 结尾的员工
select ename from emp where ename regexp '^S.*H$';

-- 查询姓名包含 `S` 的员工
select ename from emp where ename regexp 'S';

-- 查询姓名第 3 位是 `R` 的员工
select ename from emp where ename regexp '^.{2}R';

-- 查询姓名以元音字母开头，或者以 `H` 结尾的员工
select ename from emp where ename regexp '^[aeiou]|H$';

5. 范围查询

心法：MySQL 中范围查询用于筛选字段值在指定集合或区间内（或外）的记录。

范围件查询相关语句如下：

• 离散集合：
  • 字段 in(值列表)：匹配字段值出现在列表中的记录，替代多个 or 拼接，简化语法。
  • 字段 not in(值列表)：匹配字段值 未 出现在列表中的记录，替代多个 and 拼接，简化语法。
• 连续区间：仅适用于可比较的数值或日期类型：
  • 字段 between x and y：匹配字段值在 x 到 y 区间内的记录（包含两端值），等价于 字段 >= x and 字段 <= y 写法。
  • 字段 not between x and y：匹配字段值 不在 x 到 y 区间内的记录（不含两端值），等价于 字段 < x or 字段 > y 写法。

优化建议：

1. 对于少量离散值（小于 10 个），in 效率与 or 接近。
2. 对于大量离散值，优先用 in，因为 MySQL 优化器对 in 的处理更高效。
3. 区间查询优先用 between and 写法，比 >= + <= 更易读，二者性能一致。

武技：测试 DQL 范围查询。

use mysql8;

-- 查询主键为7369，7499，7521的员工
select * from emp where empno = 7369 or empno = 7499 or empno = 7521;
select * from emp where empno in (7369, 7499, 7521);

-- 查询主键不为7369，7499，7521的员工
select * from emp where empno != 7369 and empno != 7499 and empno != 7521;
select * from emp where empno not in (7369, 7499, 7521);

-- 查询主键在 7654 到 7900 之间的所有员工，包括两端值
select * from emp where empno between 7654 and 7900;

-- 查询主键不在 7654 到 7900 之间的所有员工，不包括两端值
select * from emp where empno not between 7654 and 7900;

6. 分页查询

心法：select 字段列表 from 查询范围 limit 起始偏移量M, 截取条数N：分页的核心是 “先确定完整数据集，再截取部分”，分页前必须加 order by，否则每次分页结果可能无序。

分页格式：假设当前显示第 page 页，每页显示 size 条：

• M = (page - 1) * size：表示跳过前 M 条记录，从第 M + 1 条开始（M 从 0 开始），为 0 时可省略。
• N = size：表示截取 N 条数据，不能省略。

分页优化：MySQL 分页的原理是从数据集的第 1 条记录（0 号记录）开始全量扫描，跳过前 M 条记录，然后截取后续 N 条记录返回：

• limit 5, 2：先从 0 号记录开始全量扫描，跳过 5 条记录，然后取出后续 2 条记录返回，扫描成本 5 次。
• limit 50, 2：先从 0 号记录开始全量扫描，跳过 50 条记录，然后取出后续 2 条记录返回，扫描成本 50 次。
• limit 500, 2：先从 0 号记录开始全量扫描，跳过 500 条记录，然后取出后续 2 条记录返回，扫描成本 500 次。

所以 M 越大，需要跳过的记录越多，全量扫描的成本越高 ，此时推荐先用 where 将记录起始位置限定到 M，然后取出 N 条记录，以提升分页查询效率，如下：

• order by id limit 500, 2：效率低。
• where id >= 500 order by id limit 2：效率高。

武技：测试 DQL 分页查询。

use mysql8;

-- 分页查询员工记录，查询第 1 页（page=1），每页显示 5 条（size = 5）
-- M = (page-1) * size => 0，M 为 0 时可以省略
-- N = size => 5
select empno, ename from emp order by empno limit 0, 5;
select empno, ename from emp order by empno limit 5;

-- 分页查询员工记录，查询第 2 页（page=2），每页显示 5 条（size = 5）
-- M = (page-1) * size => 5
-- N = size => 5
select empno, ename from emp order by empno limit 5, 5;

-- MySQL分页优化测试 - 从第1条记录开始扫描到第10W条数据，然后取出100条记录，效率低
select empno, ename from emp order by empno limit 100000, 100;

-- MySQL分页优化测试 - 直接从10W条数据开始取出100条记录，效率高
select empno, ename from emp where empno >= 100000 order by empno limit 100

7. 分组查询

心法：select 字段列表 from 查询范围 [where 分组前筛选条件] group by 字段列表 [having 分组后筛选条件]

分组核心规则：

• MySQL 支持对 1 个或多个字段分组，分组后默认返回每组的第一条数据，默认仅返回一列数据。
• 分组过程中 null 值会被视为独立分组，若需排除需通过 where deptno is not null 语句主动处理。
• MySQL 5.7+ 严格要求 select 字段必须是 group by 字段或聚合函数，否则报错。
• 分组前支持使用 where 进行基本条件筛选，分组后支持使用 having 进行聚合条件筛选。

聚合函数：分组查询一般配合聚合函数一起使用，分组查询常用聚合函数如下：

函数	精准描述	效率 / 注意事项
count(字段)	统计每组中指定字段非 null 的记录数	效率低（逐行检查字段值是否为 null 值）
count(*)	统计每组的总记录数（包含 null 值）	效率高（直接读取存储引擎的行数元数据），推荐
count(1)	为每行生成常量 1 并统计数量（包含 null 值）	效率与 count(*) 基本一致
max(字段)	取每组中指定字段的最大值	字段为字符串时按字符编码排序
min(字段)	取每组中指定字段的最小值	字段为字符串时按字符编码排序
sum(字段)	计算每组中指定字段的数值总和	null 值自动忽略，字段为非数值类型时返回 0
avg(字段)	计算每组中指定字段的平均值	null 值自动忽略
group_concat(字段)	将每组中指定字段的值拼接为字符串，逗号分隔	有长度限制，默认1024字符
group_concat(字段 separator ';')	将每组中指定字段的值拼接为字符串	可自定义分隔符

优化建议：

1. 优先用 where 过滤无效数据：分组前通过 where 排除不需要的记录（如 null、无效值），减少分组计算的数据量，提升效率。
2. 索引优化：对 group by 的分组字段（如 deptno、job）建立索引，可避免全表扫描，大幅提升分组效率。
3. 避免分组后排序：若需排序，优先在 group by 后加 order by，且对排序字段建立索引。

武技：测试 DQL 分组查询。

use mysql8;

-- 查询员工，按照职位分组（where 在 group by 之前执行过滤，可以提高查询效率）
select job
from emp
where job is not null
group by job;

-- 查询员工，先按照部门分组，再按姓名分组
select deptno, ename
from emp
where deptno is not null
  and ename is not null
group by deptno, ename;

-- 查询每个部门中的员工数量，最高工资，最低工资，平均工资和总工资
select deptno,
       count(empno) number,
       max(sal)     maxSal,
       min(sal)     minSal,
       avg(sal)     avgSal,
       sum(sal)     sumSal
from emp
where deptno is not null
group by deptno;

-- 查询平均工资超过2000的部门
-- where 在 group by 之前执行过滤，having 在 group by 之后执行过滤
select deptno, avg(sal) avgSal
from emp
where deptno is not null
group by deptno
having avgSal > 2000;

-- 查询每个职位中都有哪些员工
select job, group_concat(ename) names
from emp
where job is not null
group by job;

E06. DQL复杂查询

1. 单行子查询

心法：select 字段列表 from 查询范围 where 字段 操作符 (单行子查询结果)：单行子查询是返回单行单列结果的子查询，需搭配单行操作符（= / > / < / >= / <= 等）使用，若子查询返回多行 / 多列会直接报错。

关键特性：

1. 执行逻辑：子查询先独立执行一次，生成单行结果后，代入主查询作为筛选条件。
2. 性能注意：子查询结果集无法利用索引（尤其是嵌套多层时），数据量大时建议替换为 join 关联查询。
3. 空值处理：若子查询返回 null，主查询结果会为空（如 sal > (null) 结果为 unknown，无匹配记录）。

武技：测试单行子查询。

use mysql8;

-- 查询比CLARK工资值高的所有员工
select *
from emp
where sal > (select sal from emp where ename = 'CLARK');

-- 优化示例：将单行子查询改写为JOIN（JOIN可利用sal字段索引，数据量大时效率远高于子查询）
-- 需求：查询比CLARK工资高的员工（JOIN替代子查询）
select e1.empno, e1.ename, e1.sal 
from emp e1
join (select sal from emp where ename = 'CLARK') e2
on e1.sal > e2.sal;

2. 多行子查询

心法：select 字段列表 from 查询范围 where 字段 操作符 any/some/all(多行子查询结果)：多行子查询返回多行单列结果，需搭配多行操作符（any/some/all）或集合操作符（in/not inf）使用，不能直接用单行操作符（= / > 等）。

关键特性：

1. 执行逻辑：子查询先独立执行一次，生成多行结果后，主查询逐行匹配条件。
2. 性能注意：同单行子查询，结果集无索引，大数据量建议改写为 join。

多行子查询操作符：

操作符	含义	等价逻辑	适用场景
any	主查询字段与子查询结果中任意一个满足条件即可（存在至少一个）	or 关系	不等式专用
some	与 any 完全等价（MySQL 中别名），仅语义上更强调 “部分”	or 关系	等式专用
all	主查询字段与子查询结果中所有都满足条件才返回	and 关系	不等式专用

武技：测试多行子查询。

use mysql8;

-- 查询比CLARK或ADAMS工资高的所有员工
select *
from emp
where sal > any (select sal from emp where ename = 'CLARK' or ename = 'ADAMS');

-- 查询和WARD或MILLER职位相同的所有员工
select *
from emp
where job = some (select job from emp where ename = 'WARD' or ename = 'MILLER');

-- 查询比CLARK和ADAMS工资值都高的所有员工
select *
from emp
where sal > all (select sal from emp where ename = 'CLARK' or ename = 'ADAMS');

3. 关联子查询

心法：select 字段列表 from 查询范围 where exists/not exists(子查询结果)：关联子查询不独立执行，父查询每遍历一条记录，子查询就执行一次（子查询依赖父查询的字段），exists 仅判断子查询是否有结果（不在乎结果内容）。

关联子查询特性：

1. 执行逻辑：父查询驱动子查询，子查询中需通过表别名关联主查询字段（如 子.deptno = 父.deptno）。
2. 性能关键：exists 子查询中只需返回 “是否存在”，推荐用 select 1 作为查询字段，最大程度减少数据传输。
3. 效率提升：关联子查询可利用关联字段（如 deptno）的索引，合理建索引可大幅提升效率。

关联子查询语法格式：

• ..where exists(子查询)：当子查询有结果时（不在乎结果是什么），输出父查询结果，否则跳过。
• ..where not exists(子查询)：当子查询无结果时（不在乎结果是什么），输出父查询结果，否则跳过。

武技：测试关联子查询。

use mysql8;

-- 查询所有存在员工的部门和编号
select deptno, dname
from dept d
where exists(select 1 from emp e where e.deptno = d.deptno);

-- 查询所有不存在员工的部门和编号
select deptno, dname
from dept d
where not exists(select 1 from emp e where e.deptno = d.deptno);

4. 联表查询

心法：select 字段列表 from 表A [inner/left/right] join 表B on 关联条件：联表查询用于整合多个表的关联数据，核心是通过 on 指定表之间的关联条件（而非 where），联表时强烈推荐起别名，解决重复列名冲突的问题，生产环境中联表数建议不超过 5 个，以避免性能急剧下降。

联表格式：

联表类型	核心逻辑	结果集特征
inner join（内连接）	仅返回同时满足关联条件的记录（两表交集），inner 可省略	无 null 填充，仅保留匹配记录
left join（左连接）	优先返回左表（表 A）所有记录，右表（表 B）无匹配时填充 null	左表全量，右表不匹配则为 null
right join（右连接）	优先返回右表（表 B）所有记录，左表（表 A）无匹配时填充 null	右表全量，左表不匹配则为 null

优化技巧：

1. 索引优化：必须为关联字段（如 deptno）建立索引，避免联表时的全表扫描。
2. 字段精简：select 只查需要的字段，避免 select * 写法，减少数据传输和内存占用。
3. 联表顺序：将数据量小的表作为驱动表（左表），大数据量表作为被驱动表（右表）。
4. 提前筛选：用 WHERE 先过滤掉无关数据，再联表（如 WHERE d.deptno IN (10,20)）。

武技：测试联表查询。

use mysql8;

-- 查询所有员工名和部门名
-- 内联：不存在员工的部门和没分配部门的员工都不显示（inner 关键字可以省略）
select e.ename, d.dname
from emp e
         inner join dept d on e.deptno = d.deptno;

-- 查询所有员工名和部门名，ACCOUNTING 部门除外
select e.ename, d.dname
from emp e
         inner join dept d on e.deptno = d.deptno
where d.dname != 'ACCOUNTING';

-- 查询所有员工名和部门名，不存在员工的部门也显示
-- 右联：着重显示右表（即使不满足连接条件）
select e.ename, d.dname
from emp e
         right join dept d on d.deptno = e.deptno;

-- 查询所有员工名和部门名，没分配部门的员工也显示
-- 左联：着重显示左表（即使不满足连接条件）
select e.ename, d.dname
from emp e
         left join dept d on d.deptno = e.deptno;

-- 查询所有员工名和他的上级领导的名字
select xiao.ename, da.ename
from emp xiao
         join emp da on xiao.mgr = da.empno;

5. 并集查询

心法：结果集A union [all] 结果集B：并集查询用于将多个 select 语句的结果集纵向合并，若需对最终并集结果排序，order by 需写在最后一个结果集之后，且只能引用第一个结果集的列名或别名。

列匹配规则：

1. 列数一致：结果集 A 有 3 列，结果集 B 也必须有 3 列，否则直接报错。
2. 类型兼容：对应列的类型需可隐式转换（如 int(10) 和 decimal(10,2) 兼容，varchar 和 date 不兼容）。
3. 列名规则：最终结果的列名由第一个结果集的列名决定，后续结果集的列名无效。
4. NULL 补位：若两个结果集的业务字段不一致（如 A 有 工资 列、B 无），需用 null as 工资 补位，保证列数一致。

去重规则：

1. union：自动去重（包括 null 值，两个 null 也会被判定为重复），需额外执行排序去重操作，效率较低。
2. union all：不做去重、不排序，直接合并结果，效率远高于 union，生产环境优先使用，除非明确需要去重。

武技：测试 DQL 并集查询。

use mysql8;

# union求并集的时候会去重，且结果集中不忽略 null 值
select e.deptno from emp e
union
select d.deptno from dept d;

# union all求并集的时候不去重，且结果集中不忽略 null 值
select e.deptno from emp e
union all
select d.deptno from dept d;

E07. DQL查询函数

心法：MySQL 是单点核心，一旦 CPU 打满，整个系统直接卡死，而 Java 服务可以无限集群、横向扩容（加机器就行），所以计算型任务，天生就该交给 Java 来而不是数据库，而且大量不规范的使用 MySQL 的函数还可能会导致索引失效等问题，所以要根据实际场景判断，不要滥用 MySQL 的内置函数。

虚拟表：dual 是一张虚拟表，可用于一些函数的基本测试，在 SQL 中可以省略不写。

1. 内置数学函数

心法：MySQL 数学函数。

常用数学函数：表格中不推荐的函数，均意味着你应该在 Java 端完成对应的工作：

函数表达式（字母均表示数值）	简述	功能说明	生产推荐
abs(N)	绝对值	返回 N 的绝对值	不推荐
ceil(N)	上取整	对 N 执行向上取整操作，返回不小于该数的最小整数	不推荐
floor(N)	下取整	对 N 执行向下取整操作，返回不大于该数的最大整数	不推荐
power(N, M)	平方	计算 N 的 M 次幂	不推荐
sqrt(N)	开平方	计算 N 的平方根	不推荐
mod(N, M)	取模	计算 N 取余 M	不推荐
round(N, M)	四舍五入	先对 N 的绝对值四舍五入，再拼接 N 的原符号，并保留 M 位小数	不推荐
rand()	随机数	返回一个 0（包含）到 1（不包含）之间的随机浮点数	推荐
greatest(N, M ..)	最大值	从参数列表中返回最大的值	推荐
least(N, M ..)	最小值	从参数列表中返回最小的值	推荐

武技：测试数学函数。

-- 计算 -10 的绝对值
select abs(-10) from dual;

-- 计算 1.1 向上取整，1.9 向下取整
select ceil(1.1), floor(1.9);

-- 计算 2 的 3 次方，4 的平方根
select power(2, 3), sqrt(4);

-- 计算 5 对 3 取余
select mod(5, 3);

-- 计算随机数，范围在0到1之间
select rand();

-- 返回-11.5555的四舍五入，保留1为小数
select round(-11.5555, 1);

-- 从列表中返回一个最大/最小的
select greatest(10, 20, 30, 40), least(10, 20, 30, 40);

2. 内置日期函数

心法：MySQL 日期字符串可以自动转为日期类型数据（比如 MySQL 可以自动将 ‘2026-01-14’ 隐式转换为日期格式），但会牺牲一些效率。

常用时间函数：表格中不推荐的函数，均意味着你应该在 Java 端完成对应的工作：

说明：时间函数中涉及的单位可选值为：year、month、day、week、hour、minute、second、microsecond 等。

函数表达式（D 表示日期，N 表示数值）	简述	功能说明	生产推荐
now()	本地日期 + 时间	查询当前系统本地 日期 + 时间 格式为 “年-月-日 时:分: 秒” 等价于 current_timestamp 关键字	推荐
curdate()	本地日期	查询当前系统本地 日期，格式为 “年-月-日”	推荐
curtime()	本地时间	查询当前系统本地 时间，格式为 “时:分:秒”	推荐
date_add(D, interval N 单位)	日期增加	对指定日期 D 添加 指定时长后返回新日期	推荐
date_sub(D, interval N 单位)	日期减少	对指定日期 D 减去 指定时长后返回新日期	推荐
datediff(D01, D02)	日期相减	计算 D01 - D02 的结果（相差的 天数）	推荐
last_day(D)	最后一天	返回指定日期 D 所在月份的 最后一天 常用于判断大小月	推荐
year(D)	提取年	从指定日期 D 中提取 “年” 部分	大数据量不推荐
month(D)	提取月	从指定日期 D 中提取 “月” 部分	大数据量不推荐
day(D)	提取日	从指定日期 D 中提取 “日” 部分	大数据量不推荐
hour(D)	提取时	从指定日期/时间 D 中提取 “时” 部分	大数据量不推荐
minute(D)	提取分	从指定日期/时间 D 中提取 “分” 部分	大数据量不推荐
second(D)	提取秒	从指定日期/时间 D 中提取 “秒” 部分	大数据量不推荐
weekday(D)	提取星期	提取指定日期 D 对应的 星期几 注意：周日 = 0，其它以此类推	大数据量不推荐
date_format(D, 模板)	格式化	按指定模板 格式化 日期 D，常用模板符号见下表	大数据量不推荐

常用模板符号：

符号	描述	符号	描述
%Y	年份，4位	%y	年份，2位
%M	月份，英文名称	%m	月份，数值（00-12）
%D	日期，英文后缀	%d	日期，数值（00-31）
%W	星期，英文名称	%w	星期，数值（1-7）
%T	24小时制时间	%r	12小时制时间
%H	小时，数值（00-23）	%h	小时，数值（01-12）
%i	分钟，数值（00-59）	%p	AM或者PM
%S	秒钟，数值（00-59）	%j	一年中的第几天

武技：测试时间函数。

-- 查询当前系统日期 + 时间，格式：yyyy-MM-dd HH:mm:ss
select now(), current_timestamp;

-- 查询当前系统日期，格式：yyyy-MM-dd
select curdate();

-- 查询当前系统时间，格式：HH:mm:ss
select curtime();

-- 查询2018年8月8日，10天后的日期
select date_add('2018-08-08', interval 10 day) result;

-- 查询2018年8月8日，2月前的日期
select date_sub('2018-08-08', interval 2 month) result;

-- 查询2018-08-08和2008-08-08差了多少天
select datediff('2018-08-08', '2008-08-08') result;

-- 查询指定日期中的年，月，日，时，分，秒和星期
select year(current_timestamp)          yyyy,
       month(current_timestamp)         mm,
       day(current_timestamp)           dd,
       hour(current_timestamp)          hh,
       minute(current_timestamp)        mm,
       second(current_timestamp)        ss,
       weekday(current_timestamp) + 1   week;

-- 查询本月的最后一天
select last_day(current_timestamp) result;

-- 对日期进行格式化
select current_timestamp, date_format(current_timestamp, '%Y年%m月%d日 %T') result;

3. 内置字符函数

心法：MySQL 字符函数用于对字符串类型数据进行各类处理操作。

常用字符函数：表格中不推荐的函数，均意味着你应该在 Java 端完成对应的工作：

函数表达式（字母均表示字符串）	简述	功能说明	生产推荐
length(A)	字节长度	返回 A 的 字节数，一个 UTF-8 编码的中文字符占 3 个字节	不推荐
lower(A) upper(A)	大小写	返回 A 的 全小写 形式 返回 A 的 全大写 形式	不推荐
trim(A) ltrim(A) rtrim(A) trim(A from B)	两端去值	对 A 的 两端 去除空格后返回 对 A 的 左端 去除空格后返回 对 A 的 右端 去除空格后返回 对 B 的两端 删除 A 后返回	不推荐
lpad(A, 5, B) rpad(A, 5, B)	两端补齐	对 A 左补 B，直到 5 个字符，若 A 长度超 5 则直接截取前 5 个 对 A 右补 B，直到 5 个字符，若 A 长度超 5 则直接截取前 5 个	不推荐
replace(A, B, C)	替换	将 A 中所有的子串 B 替换 为 C 后返回	不推荐
concat(A, B) concat_ws(A, B, C)	拼接	将 A 和 B 拼接 后返回，若任一参数为 null 则整体返回 null 使用 A 作为 连接符，拼接 B 和 C 后返回，其余同上	不推荐
repeat(A, 5)	重复	将 A 重复 拼接 5 次后返回，若 A 为 null 则返回 null	不推荐
position(A in B)	首现位置	返回 A 在 B 中的 首次出现的位置（从 1 开始），不存在则返回 0	不推荐
left(A, 2) right(A, 2)	寻找字符	返回字符串 A 最 左侧 的 2 个字符 返回字符串 A 最 右侧 的 2 个字符	不推荐
substring(A, 2) substring(A, 2, 5)	截取	从字符串 A 的第 2 个位置开始，截取到 末尾 并返回 从字符串 A 的第 2 个位置开始，截取 5 个字符并返回	不推荐
reverse(A)	反转	返回 A 的 反转 形式	不推荐
insert(A, 5, 0, B) insert(A, 5, 1, B) insert(A, 5, 5, '')	插入 修改 删除	从 A 的 5 号位置开，删除 0 个字符，再插入 B 后返回 从 A 的 5 号位置开，删除 1 个字符，再插入 B 后返回 从 A 的 5 号位置开，删除 0 个字符，再插入空串后返回	不推荐
ascii(A) char(97, 98, 99)	ASCII转换	返回 A 首个字符 的 ASCII 码值，若 A 为 null 则返回 null 返回多个 ASCII 码值对应的字符（自动拼接），null 值会被忽略	大数据量不推荐
space(5)	补充空格	返回由 5 个空格 组成的字符串	大数据量不推荐
elt(1, 字符列表) field(A, 字符列表)	快速查找	返回参数列表中的 1 号位元素，若位置不存在则返回 null 返回 A 在参数列表中的位置（从 1 开始），不存在则返回 0	大数据量不推荐

武技：测试字符函数。

-- 返回字符串的字节数，一个U8中文占3个字节
select length('hello'), length('你好');

-- 返回字符串的全小写/全大写形式
select lower('HELLO'), upper('hello');

-- 对字符串两端/左端/右端去空格后返回
select trim(' hi world '), ltrim(' hi world '), rtrim(' hi world ');

-- 对字符串两端删除字符串 'h' 后返回
select trim('h' from 'hi world');

-- 对字符串A左补/右补字符串 '-'，直到4个字符，超出截取
select lpad('hi', 4, '-'), rpad('hi', 4, '-');

-- 查询字符串首字母的ascii码，null值的ascii码为null
select ascii('a'), ascii('banana'), ascii(null);

-- 查询ascii码值对应的字符，null会被忽略
select char(97, null, 98, 99);

-- 将字符串 'hello' 中的 'l' 替换为 'o'
select replace('hello', 'l', 'o');

-- 将两个字符串拼接在一起后返回，null拼接任何值都为null
select concat('a', 22), concat('a', null), concat('a', 'b', 'c');

-- 将字符串列表按照 `-` 分隔符拼接在一起，null拼接任何值都为null
select concat_ws('-', 'a', 'b', 'c'), concat_ws(null, 'a', 'b', 'c');

-- 将字符串重复拼接5遍后返回，null拼接任何值都为null
select repeat('love', 5), repeat(null, 5);

-- 返回 "o" 在 "com.joezhou" 中的位置，不存在返回0
select position('o' in 'com.joezhou'), position('k' in 'com.joezhou');

-- 返回字符串左边/右边5个字符
select left('perfect', 5), right('perfect', 5);

-- 从3号位置截取字符串，一直截取到最后
select substring('perfect', 3);

-- 从3号位置截取字符串，截取两个
select substring('perfect', 3, 2);

-- 返回6个空格
select space(6);

-- 将字符串反转
select reverse('banana');

-- 在字符串的3号位置，删除0个字符，并插入另一个字符串
select insert('hiva', 3, 0, 'ja');
select insert('hijava', 3, 4, 'mysql');
select insert('himysql', 3, 5, '');

-- 返回字符串列表中1号位上的元素，没找到返回null
select elt(1, 'a', 'b', 'c', 'd');

-- 返回字符串 'a' 在字符串列表中的位置，不存在返回0
select field('a', 'a', 'b', 'c', 'd');

4. 自定义函数function

心法：MySQL 使用 function 关键字声明自定义函数，核心用于封装可复用的 SQL 计算逻辑，必须返回且仅返回一个结果值，需作为 SQL 语句的一部分调用（如 SELECT 中），无法独立执行。

使用场景：不管你写的是简单逻辑、复杂逻辑，MySQL 自定义函数都意味着 “高危、慢、难维护、高耦合、坑巨多”，一律推荐写到 Java 里，不要写在 MySQL 里。只有像财务报表，数据仓库统计，DBA 自己跑数据，离线批量计算等没有 Java 服务、只靠 SQL 完成的场景，自定义函数才能派上用场。

自定义函数格式：

-- 1. 先删除已存在的同名函数（必填：避免重复创建报错）
DROP FUNCTION IF EXISTS 函数名;

-- 2. 临时修改结束符（必填：避免函数内分号提前终止创建）
DELIMITER //
-- 3. 创建函数核心语法
CREATE FUNCTION 函数名(参数1 类型, 参数2 类型, ...)
    RETURNS 返回值类型  -- 必填：声明返回值类型（如INT/VARCHAR(50)/DATE）
    [DETERMINISTIC]    -- 可选：声明确定性函数（相同输入必返回相同输出，符合规范）
BEGIN
    -- 函数体：核心业务逻辑（必填，单语句可省略BEGIN...END）
    RETURN 结果值;  -- 必填：返回值类型必须与RETURNS声明一致
END //
-- 4. 恢复默认结束符（必填）
DELIMITER ;

-- 5. 调用函数（必填：必须作为SQL一部分调用）
SELECT 函数名(参数值1, 参数值2);

自定义函数核心规则

1. 函数名建议以 f_ 为前缀，避免与内置函数重名。
2. 参数默认是 IN 类型（可省略），不支持 OUT/INOUT 类型（存储过程支持）。
3. 函数体必须用 begin .. end 包裹（单语句可省略），返回值类型需与 returns 声明严格一致。
4. 若 MySQL 报错 This function has none of DETERMINISTIC…，需临时设置：SET GLOBAL log_bin_trust_function_creators = 1 以解决日志权限限制。

武技：测试自定义函数。

-- 如果函数存在，则删除: 函数名称不带小括号
drop function if exists f1;

-- 创建函数: 参数中的in可以省略
create function f1(a int, b int)
    -- 函数中必须在第一行声明返回值类型
    returns varchar(50)
begin
    if a > b then
        -- 返回类型必须和声明的返回类型一致
        return '第1个参数大';
    elseif a < b then
        return '第2个参数大';
    else
        return '两个参数一样大';
    end if;
end

-- 调用这个函数: IDEA爆红不影响
select f1(1, 2), f1(2, 2), f1(3, 2);

Java道经第2卷 - 第3阶 - MySQL（一）


传送门：JB2-3-MySQL（一）
传送门：JB2-3-MySQL（二）