MySQL 增删查改(CRUD)操作详解
一、SELECT 查询语句
概念
SELECT 语句用于从数据库表中检索数据，是 MySQL 中最常用的操作。
特性
可以从一个或多个表中检索数据
支持条件筛选、排序、分组等操作
可以计算和转换数据
支持多表连接查询
使用意义
SELECT 语句是数据库查询的核心，用于获取业务所需的数据，支持各种复杂的数据分析需求。
语句概述
基本 SELECT 语句包含以下部分：
SELECT 子句：指定要检索的列
FROM 子句：指定数据来源的表
WHERE 子句：指定筛选条件
GROUP BY 子句：指定分组依据
HAVING 子句：对分组结果进行筛选
ORDER BY 子句：指定排序方式
LIMIT 子句：限制返回的行数
语法
SELECT [DISTINCT] column1, column2, ... FROM table_name [WHERE condition] [GROUP BY column_name] [HAVING condition] [ORDER BY column_name [ASC|DESC]] [LIMIT number];
语法结构
SELECT：必需关键字，开始查询
列名或表达式：指定要检索的列
FROM：必需关键字，指定数据来源
表名：数据来源的表
可选子句：WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
语法解析
DISTINCT：可选，去除重复行
column1, column2：要检索的列名，* 表示所有列
table_name：数据来源的表名
WHERE condition：筛选条件
GROUP BY column_name：按指定列分组
HAVING condition：分组后的筛选条件
ORDER BY column_name：按指定列排序
LIMIT number：限制返回的行数
示例
SELECT employee_id, first_name, last_name, salary, salary * 1.1 AS new_salary FROM employees WHERE department_id = 10 AND salary > 5000 ORDER BY salary DESC LIMIT 5;
输出
+-------------+------------+-----------+--------+------------+ | employee_id | first_name | last_name | salary | new_salary | +-------------+------------+-----------+--------+------------+ | 101 | John | Chen | 12000 | 13200.00 | | 102 | Lex | De Haan | 11000 | 12100.00 | | 103 | Alexander | Hunold | 9000 | 9900.00 | | 104 | Bruce | Ernst | 6000 | 6600.00 | +-------------+------------+-----------+--------+------------+ 4 rows in set (0.00 sec)
代码解析
SELECT employee_id, first_name, last_name, salary, salary * 1.1 AS new_salary：选择员工ID、名、姓、工资和计算后的新工资(增加10%)FROM employees：数据来源于employees表WHERE department_id = 10 AND salary > 5000：筛选部门ID为10且工资大于5000的员工ORDER BY salary DESC：按工资降序排列LIMIT 5：最多返回5条记录
错误机制
表或列不存在：ERROR 1146 (42S02): Table 'database.table' doesn't exist
语法错误：ERROR 1064 (42000): You have an error in your SQL syntax
权限不足：ERROR 1142 (42000): SELECT command denied to user
底层原理
解析SQL语句
检查权限
查询优化器生成执行计划
存储引擎检索数据
返回结果集
生产环境
避免使用SELECT *，只查询需要的列
对大表查询使用LIMIT限制结果集
为常用查询条件创建索引
复杂查询考虑使用存储过程
注意事项
注意SQL注入风险
大数据量查询可能影响性能
使用EXPLAIN分析查询性能
考虑使用缓存机制
应用场景
数据报表生成
后台管理系统数据展示
数据分析
数据导出
二、INSERT 插入语句
概念
INSERT 语句用于向数据库表中插入新记录。
特性
可以插入单行或多行数据
支持从其他表查询数据插入
可以指定要插入的列
支持批量插入提高性能
使用意义
INSERT 语句是数据录入的基础，用于向数据库添加新数据。
语句概述
基本 INSERT 语句包含以下部分：
INSERT INTO：指定要插入的表
列名：可选，指定要插入的列
VALUES：指定要插入的值
或者使用SELECT子句从其他表查询数据插入
语法
-- 插入单行 INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); -- 插入多行 INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value1, value2, ...), ...; -- 从其他表插入 INSERT INTO table_name (column1, column2, ...) SELECT column1, column2, ... FROM another_table WHERE condition;
语法结构
INSERT INTO：必需关键字，开始插入操作
table_name：要插入数据的表名
(column1, column2, ...)：可选，要插入的列名
VALUES：必需关键字，指定要插入的值
值列表：与列对应的值
或者使用SELECT子句从其他表查询数据
语法解析
table_name：要插入数据的表名
(column1, column2, ...)：可选，指定要插入的列
VALUES (value1, value2, ...)：要插入的值，与列对应
多行插入使用多个值组，用逗号分隔
可以使用SELECT从其他表查询数据插入
示例
-- 插入单行 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES (207, 'John', 'Doe', 'jdoe@example.com', '2025-01-15', 'IT_PROG', 7500); -- 插入多行 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES (208, 'Jane', 'Smith', 'jsmith@example.com', '2025-02-20', 'FI_ACCOUNT', 6800), (209, 'Robert', 'Johnson', 'rjohnson@example.com', '2025-03-10', 'SA_REP', 6200); -- 从其他表插入 INSERT INTO employee_archive (employee_id, first_name, last_name, email, hire_date, job_id, salary) SELECT employee_id, first_name, last_name, email, hire_date, job_id, salary FROM employees WHERE hire_date < '2020-01-01';
输出
-- 单行插入 Query OK, 1 row affected (0.01 sec) -- 多行插入 Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 -- 从其他表插入 Query OK, 15 rows affected (0.03 sec) Records: 15 Duplicates: 0 Warnings: 0
代码解析
第一个INSERT：向employees表插入一条新员工记录，指定了所有字段
第二个INSERT：向employees表插入两条新员工记录，使用多行插入语法
第三个INSERT：从employees表查询2020年前雇佣的员工，插入到employee_archive表
错误机制
主键冲突：ERROR 1062 (23000): Duplicate entry '207' for key 'PRIMARY'
列数不匹配：ERROR 1136 (21S01): Column count doesn't match value count
违反约束：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
数据类型不匹配：ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'employee_id'
底层原理
解析SQL语句
检查约束和权限
分配新的行空间
写入数据到存储引擎
更新索引
写入事务日志
生产环境
使用批量插入提高性能
考虑使用LOAD DATA INFILE导入大量数据
大表插入时暂时禁用索引
使用事务保证数据一致性
注意事项
注意主键和唯一约束
确保外键约束满足
考虑自增列的处理
注意字符编码问题
应用场景
用户注册
订单创建
数据迁移
批量导入
三、UPDATE 更新语句
概念
UPDATE 语句用于修改数据库表中的现有记录。
特性
可以更新单行或多行数据
支持基于条件的更新
可以使用表达式计算新值
可以同时更新多个列
使用意义
UPDATE 语句是数据维护的关键，用于修改已有数据以反映业务变化。
语句概述
基本 UPDATE 语句包含以下部分：
UPDATE：指定要更新的表
SET：指定要更新的列和新值
WHERE：指定要更新的行条件
ORDER BY 和 LIMIT：可选，控制更新顺序和数量
语法
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition [ORDER BY column_name] [LIMIT number];
语法结构
UPDATE：必需关键字，开始更新操作
table_name：要更新的表名
SET：必需关键字，指定要更新的列和新值
column1 = value1：列和对应的新值
WHERE condition：可选，指定要更新的行条件
ORDER BY：可选，指定更新顺序
LIMIT：可选，限制更新的行数
语法解析
table_name：要更新的表名
SET column = value：设置列的新值，可以是表达式
WHERE condition：筛选要更新的行，省略则更新所有行
ORDER BY：按指定列排序后更新
LIMIT：限制更新的行数
示例
-- 更新特定员工工资 UPDATE employees SET salary = salary * 1.05, last_raise_date = CURRENT_DATE() WHERE employee_id = 207; -- 更新部门所有员工工资 UPDATE employees SET salary = salary * 1.03 WHERE department_id = 10; -- 带LIMIT的更新 UPDATE low_stock_products SET stock_alert = 1 WHERE stock_quantity < 10 ORDER BY stock_quantity ASC LIMIT 100;
输出
-- 更新特定员工 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- 更新部门所有员工 Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0 -- 带LIMIT的更新 Query OK, 15 rows affected (0.02 sec) Rows matched: 15 Changed: 15 Warnings: 0
代码解析
第一个UPDATE：更新ID为207的员工，工资增加5%，并设置最后加薪日期为当天
第二个UPDATE：更新部门ID为10的所有员工，工资增加3%
第三个UPDATE：更新库存量低于10的产品，设置库存警告标志，按库存量升序更新前100条
错误机制
更新不存在的行：Rows matched: 0 Changed: 0
违反约束：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
权限不足：ERROR 1142 (42000): UPDATE command denied to user
死锁：ERROR 1213 (40001): Deadlock found when trying to get lock
底层原理
解析SQL语句
根据WHERE条件定位要更新的行
获取行锁
修改数据
更新索引
写入事务日志
生产环境
大表更新分批进行
高并发时注意锁竞争
考虑使用事务
更新前备份重要数据
注意事项
确保WHERE条件准确，避免误更新
注意事务大小，避免长时间锁定
考虑触发器的影响
注意自增列的处理
应用场景
用户信息修改
订单状态更新
价格调整
批量数据处理
四、DELETE 删除语句
概念
DELETE 语句用于从数据库表中删除记录。
特性
可以删除单行或多行数据
支持基于条件的删除
可以控制删除顺序和数量
删除操作通常不可逆
使用意义
DELETE 语句用于清理不再需要的数据，维护数据库的整洁和效率。
语句概述
基本 DELETE 语句包含以下部分：
DELETE FROM：指定要删除数据的表
WHERE：指定要删除的行条件
ORDER BY 和 LIMIT：可选，控制删除顺序和数量
语法
DELETE FROM table_name WHERE condition [ORDER BY column_name] [LIMIT number];
语法结构
DELETE FROM：必需关键字，开始删除操作
table_name：要删除数据的表名
WHERE condition：可选，指定要删除的行条件
ORDER BY：可选，指定删除顺序
LIMIT：可选，限制删除的行数
语法解析
table_name：要删除数据的表名
WHERE condition：筛选要删除的行，省略则删除所有行
ORDER BY：按指定列排序后删除
LIMIT：限制删除的行数
示例
-- 删除特定员工 DELETE FROM employees WHERE employee_id = 209; -- 删除离职员工 DELETE FROM employees WHERE status = 'TERMINATED' AND termination_date < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR); -- 带LIMIT的删除 DELETE FROM session_logs WHERE last_activity < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) ORDER BY last_activity ASC LIMIT 1000;
输出
-- 删除特定员工 Query OK, 1 row affected (0.01 sec) -- 删除离职员工 Query OK, 3 rows affected (0.01 sec) -- 带LIMIT的删除 Query OK, 1000 rows affected (0.12 sec)
代码解析
第一个DELETE：删除ID为209的员工记录
第二个DELETE：删除状态为"TERMINATED"且离职超过1年的员工
第三个DELETE：删除30天前不活跃的会话日志，按活动时间升序删除前1000条
错误机制
删除不存在的行：Rows matched: 0 Changed: 0
违反外键约束：ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
权限不足：ERROR 1142 (42000): DELETE command denied to user
表被锁定：ERROR 1205 (HY000): Lock wait timeout exceeded
底层原理
解析SQL语句
根据WHERE条件定位要删除的行
获取行锁
从存储引擎删除数据
更新索引
写入事务日志
生产环境
大表删除分批进行
考虑使用TRUNCATE TABLE清空大表
删除前备份重要数据
高并发时注意锁竞争
注意事项
确保WHERE条件准确，避免误删除
注意外键约束的影响
考虑使用软删除(标记删除)替代物理删除
大表删除可能影响性能
应用场景
用户账号注销
订单取消
数据清理
临时数据删除
五、四种操作对比图表
六、关键区别总结
目的不同：
SELECT：读取数据
INSERT：添加数据
UPDATE：修改数据
DELETE：删除数据
对数据的影响：
SELECT是只读操作，不影响数据
INSERT/UPDATE/DELETE会改变数据
性能考虑：
SELECT关注查询效率，依赖索引
INSERT关注批量操作效率
UPDATE/DELETE关注锁竞争和事务大小
事务需求：
INSERT/UPDATE/DELETE通常需要事务保证一致性
SELECT在大多数情况下不需要事务
恢复难度：
DELETE操作通常最难恢复
UPDATE可以通过