- 发布日期
MySQL基础-1
- 作者
- 姓名
- 缘
- 社交账号
文章目录
概述与数据模型
MySQL 是一种关系型数据库管理系统（RDBMS）。
关系型数据库
关系型数据库建立在关系模型的基础上，由多张相互关联的二维表组成。
主要特点：
- 使用表存储数据，数据结构清晰，便于维护。
- 使用 SQL 语言操作数据，标准统一，使用方便。
- 表与表之间可以通过字段建立关联。
数据模型
数据库系统中的基本层级关系：
用户端
↓
数据库管理系统（DBMS）
↓
数据库（Database）
↓
数据表（Table）
↓
字段（Column）和记录（Row）
其中：
- DBMS：数据库管理系统，例如 MySQL。
- Database：数据库，用于组织和管理多张表。
- Table：数据表，由行和列组成。
- Column：字段，表示某一类数据。
- Row：记录，表示一条完整的数据。
SQL
SQL 全称为 Structured Query Language，即结构化查询语言。
SQL 是操作关系型数据库的标准语言，可以用于：
- 创建数据库和数据表
- 添加、修改和删除数据
- 查询数据
- 管理数据库用户和权限
SQL 通用语法
- SQL 语句可以单行或多行书写，以分号
;结尾。 - SQL 语句可以使用空格和缩进增强可读性。
- MySQL 中的 SQL 关键字不区分大小写，建议使用大写。
- 字符串和日期数据通常使用单引号包裹。
例如：
SELECT *
FROM emp
WHERE age >= 18;
SQL 注释
单行注释：
-- 注释内容
-- 后面需要保留一个空格。
也可以使用：
# 注释内容
多行注释：
/*
多行注释内容
*/
SQL 分类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用于定义数据库、表、字段等数据库对象 |
| DML | Data Manipulation Language | 数据操作语言，用于对表中数据进行增加、修改和删除 |
| DQL | Data Query Language | 数据查询语言，用于查询表中的数据 |
| DCL | Data Control Language | 数据控制语言，用于管理用户和数据库访问权限 |
DDL
DDL 全称为 Data Definition Language，即数据定义语言。
主要用于定义和管理数据库对象，例如数据库、数据表和字段。
数据库操作
查询所有数据库
SHOW DATABASES;
查询当前数据库
SELECT DATABASE();
如果当前没有选择数据库，返回结果为 NULL。
创建数据库
基本语法：
CREATE DATABASE [IF NOT EXISTS] 数据库名
[DEFAULT CHARACTER SET 字符集]
[COLLATE 排序规则];
简单创建：
CREATE DATABASE test;
避免数据库已经存在时报错：
CREATE DATABASE IF NOT EXISTS test;
指定字符集和排序规则：
CREATE DATABASE IF NOT EXISTS test
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
其中：
IF NOT EXISTS：数据库不存在时才创建。CHARACTER SET：指定默认字符集。COLLATE：指定字符集对应的排序规则。
删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
示例：
DROP DATABASE IF EXISTS test;
删除数据库后，其中的数据表和数据也会一起被删除。
使用数据库
USE 数据库名;
示例：
USE test;
表操作
查询当前数据库中的所有表
SHOW TABLES;
查询表结构
DESC 表名;
也可以写成：
DESCRIBE 表名;
示例：
DESC emp;
可以查看：
- 字段名称
- 字段类型
- 是否允许为
NULL - 键信息
- 默认值
- 额外属性
查询指定表的建表语句
SHOW CREATE TABLE 表名;
示例：
SHOW CREATE TABLE emp;
创建表
基本语法：
CREATE TABLE 表名 (
字段名1 数据类型 [COMMENT '字段注释'],
字段名2 数据类型 [COMMENT '字段注释'],
字段名3 数据类型 [COMMENT '字段注释'],
...
字段名n 数据类型 [COMMENT '字段注释']
) COMMENT '表注释';
示例：
CREATE TABLE emp (
id INT COMMENT '编号',
workno VARCHAR(10) COMMENT '工号',
name VARCHAR(10) COMMENT '姓名',
gender CHAR(1) COMMENT '性别',
age TINYINT UNSIGNED COMMENT '年龄',
idcard CHAR(18) COMMENT '身份证号',
entrydate DATE COMMENT '入职时间'
) COMMENT '员工表';
注意事项：
- 字段之间使用英文逗号
,分隔。 - 最后一个字段后面不能添加逗号。
- 注释内容需要使用单引号包裹。
- SQL 中不能混入中文逗号
，。 - 数据类型应根据实际数据范围选择。
添加字段
ALTER TABLE 表名
ADD [COLUMN] 字段名 数据类型
[COMMENT '字段注释']
[约束];
示例：
ALTER TABLE emp
ADD nickname VARCHAR(20) COMMENT '昵称';
添加到表的第一个位置：
ALTER TABLE emp
ADD nickname VARCHAR(20) FIRST;
添加到指定字段后面：
ALTER TABLE emp
ADD email VARCHAR(100) AFTER name;
修改字段的数据类型
ALTER TABLE 表名
MODIFY [COLUMN] 字段名 新数据类型;
示例：
ALTER TABLE emp
MODIFY name VARCHAR(30);
同时重新设置字段注释：
ALTER TABLE emp
MODIFY name VARCHAR(30) COMMENT '员工姓名';
MODIFY可以修改字段定义，但不能修改字段名称。
修改字段名和字段类型
ALTER TABLE 表名
CHANGE [COLUMN] 旧字段名 新字段名 新数据类型
[COMMENT '字段注释']
[约束];
示例：
ALTER TABLE emp
CHANGE nickname user_name VARCHAR(30) COMMENT '用户名';
即使只修改字段名，也需要重新写出字段的数据类型：
ALTER TABLE emp
CHANGE name employee_name VARCHAR(30);
删除字段
ALTER TABLE 表名
DROP [COLUMN] 字段名;
示例：
ALTER TABLE emp
DROP nickname;
删除字段后，该字段中原有的数据也会被删除。
修改表名
ALTER TABLE 旧表名
RENAME TO 新表名;
示例：
ALTER TABLE emp
RENAME TO employee;
删除表
DROP TABLE [IF EXISTS] 表名;
示例：
DROP TABLE IF EXISTS emp;
同时删除多张表：
DROP TABLE IF EXISTS emp, department;
执行后会删除表结构和表中的全部数据。
清空表
TRUNCATE TABLE 表名;
示例：
TRUNCATE TABLE emp;
TRUNCATE TABLE 的特点：
- 清空表中的全部数据。
- 保留数据表结构。
- 不能添加
WHERE条件。 - 通常比逐行执行
DELETE更快。 - 通常会重置
AUTO_INCREMENT计数值。 - 属于 DDL 语句。
常用数据类型
MySQL 常用的数据类型可以分为：
- 数值类型
- 字符串类型
- 日期和时间类型
数值类型
| 类型 | 大小 | 有符号范围 | 无符号范围 | 描述 |
|---|---|---|---|---|
TINYINT | 1 byte | -128 ～ 127 | 0 ～ 255 | 小整数 |
SMALLINT | 2 bytes | -32768 ～ 32767 | 0 ～ 65535 | 较小整数 |
MEDIUMINT | 3 bytes | -8388608 ～ 8388607 | 0 ～ 16777215 | 中等整数 |
INT / INTEGER | 4 bytes | -2147483648 ～ 2147483647 | 0 ～ 4294967295 | 常用整数 |
BIGINT | 8 bytes | −2^63 ～ 2^63−1 | 0 ～ 2^64−1 | 极大整数 |
FLOAT | 4 bytes | 约 ±3.402823466E+38 | 不建议使用 UNSIGNED | 单精度浮点数 |
DOUBLE | 8 bytes | 约 ±1.7976931348623157E+308 | 不建议使用 UNSIGNED | 双精度浮点数 |
DECIMAL(M,D) | 取决于 M 和 D | 取决于 M 和 D | 不建议使用 UNSIGNED | 精确小数 |
整数类型可以使用 UNSIGNED 修饰，表示不允许存储负数。
例如：
age TINYINT UNSIGNED
普通 TINYINT 的范围：
-128 ～ 127
添加 UNSIGNED 后：
0 ～ 255
年龄、数量等不可能为负数的数据可以使用 UNSIGNED。
FLOAT 和 DOUBLE 属于近似数值类型，计算时可能出现精度误差。
score FLOAT
distance DOUBLE
一般来说：
FLOAT精度较低，占用 4 bytes。DOUBLE精度更高，占用 8 bytes。- 金额、价格等要求精确的数据应使用
DECIMAL。
DECIMAL 用于存储精确小数。
DECIMAL(M, D)
其中：
M：数字总位数。D：小数部分位数。- 整数部分最多为
M-D位。
例如：
price DECIMAL(10, 2)
表示：
- 总共最多 10 位数字。
- 小数部分最多 2 位。
- 整数部分最多 8 位。
可以存储：
12345678.99
字符串类型
| 类型 | 最大长度 | 描述 |
|---|---|---|
CHAR(M) | 0～255 个字符 | 定长字符串 |
VARCHAR(M) | M 表示字符数，实际受字符集和单行大小限制 | 变长字符串 |
TINYBLOB | 255 bytes | 较小的二进制数据 |
TINYTEXT | 255 bytes | 短文本数据 |
BLOB | 65535 bytes | 二进制长数据 |
TEXT | 65535 bytes | 长文本数据 |
MEDIUMBLOB | 16777215 bytes | 中等长度二进制数据 |
MEDIUMTEXT | 16777215 bytes | 中等长度文本数据 |
LONGBLOB | 4294967295 bytes | 极大二进制数据 |
LONGTEXT | 4294967295 bytes | 极大文本数据 |
CHAR(M) 和 VARCHAR(M) 中的 M 表示字符数量，不是固定的字节数量。
实际占用空间会受到以下因素影响：
- 字符集
- 实际存储内容
- 单行最大存储空间
- 字符是否属于多字节字符
例如，在 utf8mb4 字符集中，一个字符最多可能占用 4 bytes。
CHAR 和 VARCHAR 的区别：
| 对比项 | CHAR | VARCHAR |
|---|---|---|
| 长度 | 定长 | 变长 |
| 存储方式 | 按声明的固定长度处理 | 根据实际内容长度处理 |
| 适合数据 | 长度固定的数据 | 长度变化较大的数据 |
| 常见场景 | 性别、状态、身份证号 | 姓名、地址、标题 |
例如：
gender CHAR(1)
idcard CHAR(18)
name VARCHAR(20)
address VARCHAR(255)
TEXT 用于存储文本数据：
article_content TEXT
BLOB 用于存储二进制数据：
file_data BLOB
日期和时间类型
| 类型 | 大小 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
DATE | 3 bytes | 1000-01-01 ～ 9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 bytes | -838:59:59 ～ 838:59:59 | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 byte | 1901 ～ 2155，也可以存储 0000 | YYYY | 年份值 |
DATETIME | 5 bytes | 1000-01-01 00:00:00 ～ 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 日期和时间 |
TIMESTAMP | 4 bytes | 1970-01-01 00:00:01 ～ 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 时间戳 |
表中的大小指没有设置小数秒时的基础存储大小。
TIME、DATETIME 和 TIMESTAMP 可以设置 0～6 位小数秒精度：
created_at DATETIME(3)
表示保留 3 位毫秒：
2026-08-02 17:30:20.123
DATE 只保存日期：
birthday DATE
2006-08-02
TIME 可以表示一天中的时间，也可以表示持续时间：
duration TIME
100:00:00
DATETIME 和 TIMESTAMP 的区别：
| 对比项 | DATETIME | TIMESTAMP |
|---|---|---|
| 基础存储大小 | 5 bytes | 4 bytes |
| 时间范围 | 1000～9999 年 | 1970～2038 年 |
| 时区转换 | 通常不自动转换 | 会根据连接时区进行转换 |
| 常见用途 | 生日、预约时间、业务时间 | 创建时间、更新时间 |
DML
DML 全称为 Data Manipulation Language，即数据操作语言。
主要用于对数据库表中的数据进行增加、修改和删除。
| 操作 | SQL 关键字 |
|---|---|
| 添加数据 | INSERT |
| 修改数据 | UPDATE |
| 删除数据 | DELETE |
添加数据
给指定字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...)
VALUES (值1, 值2, ...);
示例：
INSERT INTO emp (id, workno, name, gender, age, entrydate)
VALUES (1, '00001', '张三', '男', 20, '2026-08-02');
字段和值需要一一对应：
字段名1 ← 值1
字段名2 ← 值2
字段名3 ← 值3
给全部字段添加数据
INSERT INTO 表名
VALUES (值1, 值2, ...);
示例：
INSERT INTO emp
VALUES (
1,
'00001',
'张三',
'男',
20,
'500000200608020001',
'2026-08-02'
);
使用这种方式时：
- 值的数量必须与表中的字段数量一致。
- 值的顺序必须与字段排列顺序一致。
- 表结构发生变化后，SQL 可能需要跟着修改。
实际开发中通常建议明确写出字段名。
批量添加数据
指定字段批量添加：
INSERT INTO emp (id, workno, name, gender, age)
VALUES
(1, '00001', '张三', '男', 20),
(2, '00002', '李四', '女', 21),
(3, '00003', '王五', '男', 22);
给全部字段批量添加：
INSERT INTO 表名
VALUES
(值1, 值2, ...),
(值1, 值2, ...),
(值1, 值2, ...);
添加数据的注意事项：
- 字段数量和值的数量必须一致。
- 字段和值的顺序必须一一对应。
- 字符串和日期数据需要使用单引号包裹。
- 数值类型通常不需要使用引号。
NULL表示空值，不能写成字符串'NULL'。- 插入的数据必须符合字段的数据类型和长度要求。
- 批量插入通常比逐条插入效率更高。
正确示例：
INSERT INTO emp (id, name, age, entrydate)
VALUES (1, '张三', 20, '2026-08-02');
错误示例：
INSERT INTO emp (id, name, age)
VALUES (1, '张三');
字段数量和值数量不一致，因此会执行失败。
修改数据
基本语法：
UPDATE 表名
SET 字段名1 = 值1,
字段名2 = 值2,
...
[WHERE 条件];
修改指定记录
UPDATE emp
SET name = '张三'
WHERE id = 1;
同时修改多个字段：
UPDATE emp
SET name = '张三',
age = 21,
gender = '男'
WHERE id = 1;
根据原值进行计算：
UPDATE emp
SET age = age + 1
WHERE id = 1;
修改全部记录
UPDATE emp
SET entrydate = '2026-08-02';
UPDATE没有设置WHERE条件时，会修改表中的所有记录。
执行修改前，可以先查询需要修改的数据：
SELECT *
FROM emp
WHERE id = 1;
确认无误后再执行：
UPDATE emp
SET age = 21
WHERE id = 1;
删除数据
基本语法：
DELETE FROM 表名
[WHERE 条件];
删除指定记录
DELETE FROM emp
WHERE id = 1;
删除年龄小于 18 岁的员工：
DELETE FROM emp
WHERE age < 18;
删除全部记录
DELETE FROM emp;
执行后表结构仍然保留，只删除表中的数据。
DELETE没有设置WHERE条件时，会删除表中的全部记录。
DELETE 删除的是整条记录，不能只删除某一个字段。
将字段设置为空应使用 UPDATE：
UPDATE emp
SET idcard = NULL
WHERE id = 1;
DELETE 和 TRUNCATE 的区别：
| 对比项 | DELETE | TRUNCATE |
|---|---|---|
| 类型 | DML | DDL |
是否支持 WHERE | 支持 | 不支持 |
| 是否保留表结构 | 保留 | 保留 |
| 是否可以删除部分数据 | 可以 | 不可以 |
| 清空整表速度 | 通常较慢 | 通常较快 |
| 自增计数 | 通常继续原值 | 通常重新开始 |
DQL
DQL 全称为 Data Query Language，即数据查询语言。
DQL 主要用于查询数据库表中的数据，核心关键字为：
SELECT
完整语法结构：
SELECT [DISTINCT] 字段列表
FROM 表名
[WHERE 条件]
[GROUP BY 分组字段]
[HAVING 分组后条件]
[ORDER BY 排序字段 ASC | DESC]
[LIMIT 起始索引, 查询记录数];
| 关键字 | 作用 |
|---|---|
SELECT | 指定需要查询的字段 |
FROM | 指定查询的数据表 |
WHERE | 对原始数据进行条件筛选 |
GROUP BY | 对查询结果进行分组 |
HAVING | 对分组后的结果进行筛选 |
ORDER BY | 对查询结果进行排序 |
LIMIT | 限制查询结果数量 |
基本查询
查询多个字段
SELECT name, gender, age
FROM emp;
查询所有字段
SELECT *
FROM emp;
* 表示查询表中的全部字段。
实际开发中，如果只需要部分数据，建议明确写出字段名：
SELECT id, name, age
FROM emp;
这样可以减少不需要的数据传输，并提高 SQL 的可读性。
设置字段别名
SELECT 字段名 [AS] 别名
FROM 表名;
示例：
SELECT name AS `姓名`,
age AS `年龄`
FROM emp;
AS 可以省略：
SELECT name `姓名`,
age `年龄`
FROM emp;
当别名包含空格、特殊字符或者中文时，可以使用反引号包裹。
去除重复记录
SELECT DISTINCT 字段列表
FROM 表名;
查询所有不重复的性别：
SELECT DISTINCT gender
FROM emp;
查询多个字段时，只有这些字段的组合完全相同，才会被视为重复：
SELECT DISTINCT gender, age
FROM emp;
条件查询
基本语法：
SELECT 字段列表
FROM 表名
WHERE 条件;
查询年龄为 20 岁的员工：
SELECT *
FROM emp
WHERE age = 20;
比较运算符
| 运算符 | 说明 |
|---|---|
= | 等于 |
<> 或 != | 不等于 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
BETWEEN ... AND ... | 在指定范围内，包含边界 |
IN (...) | 在指定的值列表中 |
LIKE | 模糊匹配 |
IS NULL | 判断是否为 NULL |
IS NOT NULL | 判断是否不为 NULL |
查询年龄大于 20 岁的员工：
SELECT *
FROM emp
WHERE age > 20;
查询年龄在 18～25 岁之间的员工：
SELECT *
FROM emp
WHERE age BETWEEN 18 AND 25;
等价写法：
SELECT *
FROM emp
WHERE age >= 18
AND age <= 25;
BETWEEN 包含两边的边界值。
查询年龄为 18、20 或 22 岁的员工：
SELECT *
FROM emp
WHERE age IN (18, 20, 22);
查询没有填写身份证号的员工：
SELECT *
FROM emp
WHERE idcard IS NULL;
查询已经填写身份证号的员工：
SELECT *
FROM emp
WHERE idcard IS NOT NULL;
判断 NULL 不能写成：
idcard = NULL
应该写成：
idcard IS NULL
逻辑运算符
| 运算符 | 说明 |
|---|---|
AND | 多个条件同时成立 |
OR | 多个条件满足任意一个 |
NOT | 对条件取反 |
查询年龄大于等于 18 岁，并且性别为女的员工：
SELECT *
FROM emp
WHERE age >= 18
AND gender = '女';
查询年龄小于 18 岁或者大于 60 岁的员工：
SELECT *
FROM emp
WHERE age < 18
OR age > 60;
查询年龄不在 18～25 岁之间的员工：
SELECT *
FROM emp
WHERE age NOT BETWEEN 18 AND 25;
查询年龄不是 18、20、22 岁的员工：
SELECT *
FROM emp
WHERE age NOT IN (18, 20, 22);
模糊查询
模糊查询使用 LIKE。
| 通配符 | 说明 |
|---|---|
% | 匹配任意数量的字符，也可以是零个字符 |
_ | 匹配任意一个字符 |
查询姓名以“张”开头的员工：
SELECT *
FROM emp
WHERE name LIKE '张%';
查询姓名以“三”结尾的员工：
SELECT *
FROM emp
WHERE name LIKE '%三';
查询姓名中包含“明”的员工：
SELECT *
FROM emp
WHERE name LIKE '%明%';
查询姓名正好是两个字符的员工：
SELECT *
FROM emp
WHERE name LIKE '__';
查询身份证号最后一位为 X 的员工：
SELECT *
FROM emp
WHERE idcard LIKE '%X';
聚合与分组
聚合函数会将一列数据作为一个整体进行计算，并返回一个结果。
常用聚合函数
| 函数 | 说明 |
|---|---|
COUNT() | 统计数量 |
MAX() | 查询最大值 |
MIN() | 查询最小值 |
AVG() | 计算平均值 |
SUM() | 计算总和 |
统计表中的记录数量：
SELECT COUNT(*)
FROM emp;
设置别名：
SELECT COUNT(*) AS total
FROM emp;
统计 idcard 不为 NULL 的记录数量：
SELECT COUNT(idcard)
FROM emp;
区别：
COUNT(*)：统计所有记录。COUNT(字段名)：统计该字段不为NULL的记录。
查询最大年龄：
SELECT MAX(age)
FROM emp;
查询最小年龄：
SELECT MIN(age)
FROM emp;
查询平均年龄：
SELECT AVG(age)
FROM emp;
查询年龄总和：
SELECT SUM(age)
FROM emp;
除 COUNT(*) 外，聚合函数计算时通常会忽略值为 NULL 的数据。
分组查询
基本语法：
SELECT 字段列表
FROM 表名
[WHERE 分组前条件]
GROUP BY 分组字段
[HAVING 分组后条件];
统计不同性别的员工数量：
SELECT gender,
COUNT(*) AS total
FROM emp
GROUP BY gender;
查询不同性别员工的平均年龄：
SELECT gender,
AVG(age) AS average_age
FROM emp
GROUP BY gender;
先筛选年龄小于 30 岁的员工，再按照性别分组：
SELECT gender,
COUNT(*) AS total
FROM emp
WHERE age < 30
GROUP BY gender;
处理过程：
WHERE 筛选原始数据
↓
GROUP BY 对数据分组
↓
聚合函数计算每组结果
查询员工数量不少于 2 人的性别分组：
SELECT gender,
COUNT(*) AS total
FROM emp
GROUP BY gender
HAVING COUNT(*) >= 2;
同时使用 WHERE 和 HAVING：
SELECT gender,
COUNT(*) AS total,
AVG(age) AS average_age
FROM emp
WHERE age >= 18
GROUP BY gender
HAVING COUNT(*) >= 2;
WHERE 和 HAVING 的区别：
| 对比项 | WHERE | HAVING |
|---|---|---|
| 执行时机 | 分组之前 | 分组之后 |
| 过滤对象 | 原始记录 | 分组后的结果 |
| 聚合函数 | 通常不能直接使用 | 可以使用 |
| 所在位置 | GROUP BY 之前 | GROUP BY 之后 |
使用 GROUP BY 后，查询字段通常应该是：
- 分组字段
- 聚合函数
推荐写法：
SELECT gender,
COUNT(*)
FROM emp
GROUP BY gender;
不推荐写法：
SELECT name,
gender,
COUNT(*)
FROM emp
GROUP BY gender;
同一个性别分组中可能存在多个姓名，数据库无法明确应该返回哪一个姓名。
排序与分页
排序查询
基本语法：
SELECT 字段列表
FROM 表名
ORDER BY 排序字段1 排序方式1,
排序字段2 排序方式2;
| 排序方式 | 说明 |
|---|---|
ASC | 升序排列 |
DESC | 降序排列 |
默认排序方式为 ASC。
按照年龄从小到大排序：
SELECT *
FROM emp
ORDER BY age ASC;
ASC 可以省略：
SELECT *
FROM emp
ORDER BY age;
按照年龄从大到小排序：
SELECT *
FROM emp
ORDER BY age DESC;
多字段排序：
SELECT *
FROM emp
ORDER BY age ASC,
entrydate DESC;
执行规则：
- 首先按照年龄升序排列。
- 年龄相同时，再按照入职时间降序排列。
分页查询
基本语法：
SELECT 字段列表
FROM 表名
LIMIT 起始索引, 查询记录数;
其中：
- 起始索引从
0开始。 - 查询记录数表示本次最多返回多少条数据。
每页显示 10 条，查询第一页：
SELECT *
FROM emp
LIMIT 0, 10;
起始索引为 0 时可以简写：
SELECT *
FROM emp
LIMIT 10;
查询第二页：
SELECT *
FROM emp
LIMIT 10, 10;
查询第三页：
SELECT *
FROM emp
LIMIT 20, 10;
起始索引计算公式：
起始索引 = （当前页码 - 1）× 每页显示数量
每页显示 10 条，查询第 5 页：
起始索引 = （5 - 1）× 10 = 40
SELECT *
FROM emp
LIMIT 40, 10;
另一种写法：
SELECT *
FROM emp
LIMIT 10 OFFSET 40;
为了保证分页结果顺序稳定，通常需要配合 ORDER BY：
SELECT *
FROM emp
ORDER BY id ASC
LIMIT 0, 10;
综合查询与执行顺序
查询年龄在 18～30 岁之间的员工，按照年龄降序排列，只显示前 5 条：
SELECT id,
name,
gender,
age
FROM emp
WHERE age BETWEEN 18 AND 30
ORDER BY age DESC
LIMIT 5;
查询成年员工中不同性别的员工数量，只显示人数不少于 2 人的分组，并按照人数降序排列：
SELECT gender,
COUNT(*) AS total
FROM emp
WHERE age >= 18
GROUP BY gender
HAVING COUNT(*) >= 2
ORDER BY total DESC;
SQL 书写顺序
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
便于理解的逻辑处理顺序
FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BY
↓
LIMIT
例如：
SELECT gender,
COUNT(*) AS total
FROM emp
WHERE age >= 18
GROUP BY gender
HAVING COUNT(*) >= 2
ORDER BY total DESC
LIMIT 2;
大致处理过程：
- 从
emp表中获取数据。 - 筛选年龄大于等于 18 岁的员工。
- 按照性别进行分组。
- 筛选员工数量不少于 2 人的分组。
- 查询性别和每组人数。
- 按照人数降序排列。
- 返回前 2 条结果。
DCL
DCL 全称为 Data Control Language，即数据控制语言。
主要用于：
- 管理数据库用户
- 修改用户密码
- 授予用户权限
- 查询用户权限
- 撤销用户权限
MySQL 账户由用户名和允许连接的主机共同确定：
'用户名'@'主机名'
例如：
'yuan'@'localhost'
表示用户 yuan 只能从 MySQL 服务器本机连接。
'yuan'@'%'
表示用户 yuan 可以从任意主机连接。
这两个属于不同的 MySQL 账户。
用户管理
查询当前用户
SELECT CURRENT_USER();
查询 MySQL 中的用户
SELECT User, Host
FROM mysql.user;
执行该语句需要拥有相应的查询权限。
创建用户
基本语法：
CREATE USER [IF NOT EXISTS]
'用户名'@'主机名'
IDENTIFIED BY '密码';
创建只能从本机连接的用户：
CREATE USER IF NOT EXISTS
'yuan'@'localhost'
IDENTIFIED BY 'Yuan@2026#MySQL';
创建允许从任意主机连接的用户：
CREATE USER IF NOT EXISTS
'yuan'@'%'
IDENTIFIED BY 'Yuan@2026#MySQL';
创建用户后，该用户默认没有业务数据库的操作权限，需要使用 GRANT 单独授权。
修改用户密码
ALTER USER
'用户名'@'主机名'
IDENTIFIED BY '新密码';
示例：
ALTER USER
'yuan'@'localhost'
IDENTIFIED BY 'New@2026#MySQL';
锁定用户
ALTER USER
'yuan'@'localhost'
ACCOUNT LOCK;
用户被锁定后无法登录，但用户和原有权限不会被删除。
解锁用户
ALTER USER
'yuan'@'localhost'
ACCOUNT UNLOCK;
删除用户
DROP USER [IF EXISTS]
'用户名'@'主机名';
示例：
DROP USER IF EXISTS
'yuan'@'localhost';
删除用户时，用户名和主机名必须与创建用户时保持一致。
权限控制
常用权限
| 权限 | 说明 |
|---|---|
SELECT | 查询数据 |
INSERT | 添加数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
CREATE | 创建数据库或数据表 |
DROP | 删除数据库或数据表 |
ALTER | 修改数据表 |
INDEX | 创建或删除索引 |
EXECUTE | 执行存储过程或函数 |
CREATE VIEW | 创建视图 |
SHOW VIEW | 查看视图定义 |
ALL PRIVILEGES | 指定范围内的全部常规权限 |
权限作用范围
| 权限范围 | 说明 |
|---|---|
*.* | 所有数据库中的所有对象 |
数据库名.* | 指定数据库中的所有对象 |
数据库名.表名 | 指定数据库中的指定表 |
例如：
*.* 所有数据库中的所有对象
test.* test 数据库中的所有对象
test.emp test 数据库中的 emp 表
查询用户权限
查询指定用户的权限：
SHOW GRANTS FOR
'yuan'@'localhost';
查询当前用户的权限：
SHOW GRANTS;
授予权限
基本语法：
GRANT 权限列表
ON 权限范围
TO '用户名'@'主机名';
授予用户指定表的查询权限：
GRANT SELECT
ON test.emp
TO 'yuan'@'localhost';
授予用户多种权限：
GRANT SELECT, INSERT, UPDATE
ON test.*
TO 'yuan'@'localhost';
授予指定数据库中的全部常规权限：
GRANT ALL PRIVILEGES
ON test.*
TO 'yuan'@'localhost';
授予所有数据库中的全部常规权限：
GRANT ALL PRIVILEGES
ON *.*
TO 'yuan'@'localhost';
普通业务账户不建议授予：
ALL PRIVILEGES ON *.*
允许用户继续授权
GRANT SELECT, INSERT
ON test.*
TO 'yuan'@'localhost'
WITH GRANT OPTION;
WITH GRANT OPTION 表示该用户可以将自己拥有的这些权限继续授予其他用户。
该权限能力较强，应谨慎使用。
撤销权限
基本语法：
REVOKE 权限列表
ON 权限范围
FROM '用户名'@'主机名';
撤销修改权限：
REVOKE UPDATE
ON test.*
FROM 'yuan'@'localhost';
同时撤销多个权限：
REVOKE INSERT, UPDATE, DELETE
ON test.*
FROM 'yuan'@'localhost';
撤销指定范围内的全部常规权限：
REVOKE ALL PRIVILEGES
ON test.*
FROM 'yuan'@'localhost';
用户权限管理完整流程
创建用户：
CREATE USER
'yuan'@'localhost'
IDENTIFIED BY 'Yuan@2026#MySQL';
授予权限：
GRANT SELECT, INSERT, UPDATE, DELETE
ON test.*
TO 'yuan'@'localhost';
查询权限：
SHOW GRANTS FOR
'yuan'@'localhost';
撤销部分权限：
REVOKE DELETE
ON test.*
FROM 'yuan'@'localhost';
删除用户：
DROP USER
'yuan'@'localhost';
DCL 注意事项
- MySQL 账户由用户名和主机名共同确定。
- 创建用户后，需要根据实际需求单独授权。
- 用户只应获得完成工作所必需的权限。
- 普通应用程序不应该直接使用
root用户。 - 不要随意授予
ALL PRIVILEGES ON *.*。 - 不要随意使用
WITH GRANT OPTION。 - 远程用户尽量限制具体 IP，不要直接使用
%。 - 密码应包含大小写字母、数字和特殊字符。
- 删除用户前，应确认该用户是否仍被应用程序使用。
- 使用
CREATE USER、GRANT、REVOKE等账户管理语句后，不需要再手动执行FLUSH PRIVILEGES。 - 不建议通过
INSERT、UPDATE或DELETE直接修改 MySQL 权限表。