增删改查操作

CRUD

C : Create(创建)
R : Retrieve(读取)
U : Update(更新)
D : Delete(删除)

Create 新增

语法

1

INSERT INTO 表名 (字段1,字段2,字段3,...) VALUES (值1,值2,值3,...);

单行数据全列插入

1

INSERT INTO 表名 VALUES (值1,值2,值3,...);

单行数据指定列插入

1

INSERT INTO 表名 (字段1,字段2,字段3,...) VALUES (值1,值2,值3,...);

多行数据指定列插入

1
2


INSERT INTO 表名 (字段1,字段2,字段3,...) 
VALUES (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...);

Retrieve 检索

语法

1
2
3
4
5
6


SELECT [DISTINCT] 列名1, 列名2, ...
FROM 表名
[WHERE 条件]
[GROUP BY 列名 [HAVING 条件]]
[ORDER BY 列名 [ASC|DESC]]
[LIMIT 数量];

SELECT：指定要查询的列。
- *表示查询所有列
- DISTINCT 用于去除重复的行
FROM：指定要查询的表。
WHERE：用于筛选满足条件的行。
GROUP BY：用于对结果进行分组。
HAVING：用于筛选分组后的结果。
ORDER BY：用于对结果进行排序。
LIMIT：用于限制返回的行数。

全列查询

1

SELECT * FROM 表名;

指定列查询

1

SELECT 字段1,字段2,字段3,... FROM 表名;

查询字段为表达式

1

SELECT 字段1 + 字段2 (表达式) FROM 表名;

查询结果指定别名

1

SELECT 字段1 AS 别名1,字段2 AS 别名2,... FROM 表名;

查询结果去重

1

SELECT DISTINCT 字段1,字段2,... FROM 表名;

使⽤DISCTINCT去重时，只有查询列表中所有列的值都相同才会判定为重复

Where 条件查询

语法

1

SELECT 列名1,列名2,... FROM 表名 WHERE 条件;

比较运算符

运算符	说明
`>`, `>=`, `<`, `<=`	大于、大于等于、小于、小于等于
`=`	等于，对 `NULL` 比较不安全，如 `NULL = NULL` 结果为 `NULL`
`<=>`	等于，对 `NULL` 比较安全，如 `NULL <=> NULL` 结果为 `TRUE(1)`
`!=`, `<>`	不等于
`value BETWEEN a0 AND a1`	范围匹配 `[a0, a1]`，若 `a0 <= value <= a1`，返回 `TRUE` 或 `1`；`NOT BETWEEN` 取反
`value IN (option, ...)`	若 `value` 在 `option` 列表中，返回 `TRUE(1)`；`NOT IN` 取反
`IS NULL`	判断是否为 `NULL`
`IS NOT NULL`	判断是否不为 `NULL`
`LIKE`	模糊匹配，`%` 表任意多个（含0个）字符；`_` 表任意一个字符；`NOT LIKE` 取反

逻辑运算符

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR	任意一个条件为 TRUE(1)，结果为 TRUE(1)
NOT	条件为 TRUE(1)，结果为 FALSE(0)

注意

WHERE条件中可以使用表达式，但不能使用别名
AND的优先级高于OR，在同时使用时，建议使用小括号()包裹优先执行的部分
过滤NULL时不要使用等于号(=)与不等于号(!= , <>)
NULL与任何值运算结果都为NULL

Order by 排序

语法

1

SELECT 列名1,列名2,... FROM 表名 ORDER BY 列名 [ASC|DESC];

ASC：升序，默认值
DESC：降序

注意

查询中没有ORDER BY 子句，返回的顺序是未定义的，永远不要依赖这个顺序
ORDER BY 子句中可以使用列的别名进⾏排序
NULL 进行排序时，视为比任何值都小，升序出现在最上面，降序出现在最下面

分页查询

语法

1
2
3
4
5
6


-- 从 0 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num;
-- 从 start 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT start, num;
-- 从 start 开始，筛选 num 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num OFFSET start;

Update 修改

语法

1

UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2,... [WHERE 条件] [ORDER BY ...] [LIMIT ...]

注意

以原值的基础上做变更时，不能使⽤math += 30这样的语法
不加where条件时，会导致全表数据被列新

Delete 删除

语法

1

DELETE FROM 表名 [WHERE 条件] [ORDER BY ...] [LIMIT ...]

注意

不加where条件时，会导致全表数据被删除

截断表

语法

1

TRUNCATE TABLE 表名;

注意

只能对整表操作，不能像 DELETE 一样针对部分数据
不对数据操作所以比DELETE更快，TRUNCATE在删除数据的时候，不经过真正的事物，所以无法回滚
会重置 AUTO_INCREMENT 项

插入查询结果

语法

1
2


INSERT INTO 表名 (字段1,字段2,字段3,...)
SELECT 字段1,字段2,字段3,... FROM 表名 WHERE 条件;

聚合函数

函数	说明
`COUNT([DISTINCT] expr)`	返回查询到的数据的数量
`SUM([DISTINCT] expr)`	返回查询到的数据的总和，非数字无意义
`AVG([DISTINCT] expr)`	返回查询到的数据的平均值，非数字无意义
`MAX([DISTINCT] expr)`	返回查询到的数据的最大值，非数字无意义
`MIN([DISTINCT] expr)`	返回查询到的数据的最小值，非数字无意义

Group by 分组查询

语法

1
2
3
4
5


SELECT 分组字段, 聚合函数(字段)
FROM 表名
[WHERE 条件]
GROUP BY 分组字段1, 分组字段2, ...
[HAVING 分组条件];

核心作用

将表中数据按照指定字段进行分组
通常与聚合函数配合使用（COUNT、SUM、AVG 等）
用于统计各组数据的汇总信息

常用示例

基础分组统计

1
2
3
4
5
6


-- 按部门分组，统计每个部门的员工数量
SELECT 
  department, 
  COUNT(*) AS employee_count
FROM employees
GROUP BY department;

多字段分组

1
2
3
4
5
6
7


-- 按部门和职位分组，统计每个部门各职位的平均工资
SELECT 
  department, 
  position,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department, position;

分组前筛选（WHERE）

1
2
3
4
5
6
7


-- 先筛选出2023年后入职的员工，再按部门分组统计
SELECT 
  department,
  COUNT(*) AS new_employees
FROM employees
WHERE hire_date >= '2023-01-01'  -- 分组前筛选
GROUP BY department;

分组后筛选（HAVING）

1
2
3
4
5
6
7


-- 按部门分组，只显示员工数量大于10人的部门
SELECT 
  department,
  COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING emp_count > 10;  -- 分组后筛选

与排序结合

1
2
3
4
5
6
7


-- 按类别分组统计商品数量，并按数量降序排列
SELECT 
  category_id,
  COUNT(*) AS product_count
FROM products
GROUP BY category_id
ORDER BY product_count DESC;

注意事项

SELECT 字段限制：SELECT 后只能出现 GROUP BY 中指定的分组字段或聚合函数
NULL 值处理：NULL 值会被视为一个独立的分组
分组顺序：多字段分组时，先按第一个字段分组，再按第二个字段在每个分组内细分
效率考虑：对大数据集分组时，建议在 GROUP BY 字段上建立索引

执行顺序
FROM/JOIN->WHERE->GROUP BY->HAVING->SELECT->ORDER BY->LIMIT

内置函数

日期函数

函数	说明
`CURDATE()`	返回当前日期，同义词 `CURRENT_DATE`，`CURRENT_DATE()`
`CURTIME()`	返回当前时间，同义词 `CURRENT_TIME`，`CURRENT_TIME([fsp])`
`NOW()`	返回当前日期和时间，同义词 `CURRENT_TIMESTAMP`，`CURRENT_TIMESTAMP`
`DATE(data)`	提取 `date` 或 `datetime` 表达式的日期部分
`ADDDATE(date, INTERVAL expr unit)`	向日期值添加时间值（间隔），同义词 `DATE_ADD()`
`SUBDATE(date, INTERVAL expr unit)`	向日期值减去时间值（间隔），同义词 `DATE_SUB()`
`DATEDIFF(expr1, expr2)`	两个日期的差，以天为单位，`expr1 - expr2`

函数	返回格式示例	说明
`CURDATE()`	`2023-10-05`	仅日期（年-月-日）
`CURTIME()`	`14:30:25` 或 `14:30:25.123`	仅时间（时:分:秒），可带微秒
`NOW()`	`2023-10-05 14:30:25`	日期+时间（年-月-日时:分:秒）
`DATE(datetime)`	`2023-10-05`	从 datetime 中提取日期部分
`ADDDATE('2023-10-05', INTERVAL 1 DAY)`	`2023-10-06`	计算后的日期，格式同原日期参数
`SUBDATE('2023-10-05', INTERVAL 1 MONTH)`	`2023-09-05`	计算后的日期，格式同原日期参数
`DATEDIFF('2023-10-05', '2023-10-01')`	`4`	整数（两个日期相差的天数）

字符串处理函数

函数	说明
`CHAR_LENGTH(str)`	返回给定字符串的长度，同义词 `CHARACTER_LENGTH()`
`LENGTH(str)`	返回给定字符串的字节数，与当前使用的字符编码集有关
`CONCAT(str1, str2, ...)`	返回拼接后的字符串
`CONCAT_WS(separator, str1, str2, ...)`	返回拼接后带分隔符的字符串
`LCASE(str)`	将给定字符串转换成小写，同义词 `LOWER()`
`UCASE(str)`	将给定字符串转换成大写，同义词 `UPPER()`
`HEX(str)`, `HEX(N)`	对于字符串参数 `str`，`HEX()` 返回 `str` 的十六进制字符串表示形式；对于数字参数 `N`，`HEX()` 返回一个十六进制字符串表示形式
`INSTR(str, substr)`	返回 `substr` 第一次出现的索引
`INSERT(str, pos, len, newstr)`	在指定位置插入子字符串，最多不超过指定的字符数
`SUBSTR(str, pos)` `SUBSTR(str FROM pos FOR len)`	返回指定的子字符串，同义词 `SUBSTRING(str, pos)`，`SUBSTRING(str FROM pos FOR len)`
`REPLACE(str, from_str, to_str)`	把字符串 `str` 中所有的 `from_str` 替换为 `to_str`，区分大小写
`STRCMP(expr1, expr2)`	逐个字符比较两个字符串，返回 -1、0、1
`LEFT(str, len)`, `RIGHT(str, len)`	返回字符串 `str` 中最左/最右边的 `len` 个字符
`LTRIM(str)`, `RTRIM(str)`, `TRIM(str)`	删除给定字符串的前导、末尾、前导和末尾的空格

数学函数

函数	说明
`ABS(X)`	返回 X 的绝对值
`CEIL(X)`	返回不小于 X 的最小整数值，同义词是 `CEILING(X)`
`FLOOR(X)`	返回不大于 X 的最大整数值
`CONV(N, from_base, to_base)`	不同进制之间的转换
`FORMAT(X, D)`	将数字 X 格式化为 “#,###,###” 的格式，四舍五入到小数点后 D 位，并以字符串形式返回
`RAND([N])`	返回一个随机浮点值，取值范围 [0.0, 1.0)
`ROUND(X)`, `ROUND(X, D)`	将参数 X 舍入到小数点后 D 位（`ROUND(X)` 等同于 `ROUND(X, 0)`）
`CRC32(expr)`	计算指定字符串的循环冗余校验值并返回一个 32 位无符号整数

其他常用函数

函数	说明
`version()`	显示当前数据库版本
`database()`	显示当前正在使用的数据库
`user()`	显示当前用户
`md5(str)`	对一个字符串进行 MD5 摘要，摘要后得到一个 32 位字符串
`ifnull(val1, val2)`	如果 `val1` 为 `NULL`，返回 `val2`，否则返回 `val1`