MySQL 数据库数据操作 (DML/DQL) 命令大全

说明：DML（Data Manipulation Language）用于对表中的数据进行增、删、改操作；DQL（Data Query Language）主要指数据查询（SELECT）。本指南涵盖所有常用的数据操作命令，并提供详细的语法和实用示例。

---

一、数据插入（INSERT）

1.1 插入单行数据（完整列）

语法：

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

值的顺序必须与表定义的列顺序完全一致。

示例：

-- 假设 users 表结构为 (id, name, age, email)
INSERT INTO users VALUES (1, '张三', 25, 'zhang@example.com');

1.2 插入单行数据（指定列）

语法：

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

未列出的列会取默认值（如果有 DEFAULT）或允许 NULL。

示例：

-- 只插入 name 和 email，id 为自增列，age 取默认值 0
INSERT INTO users (name, email) VALUES ('李四', 'li@example.com');

1.3 插入多行数据（批量插入）

语法：

INSERT INTO 表名 (列名1, 列名2, ...) VALUES 
    (值1a, 值2a, ...),
    (值1b, 值2b, ...),
    (值1c, 值2c, ...);

示例：

INSERT INTO users (name, age, email) VALUES 
    ('王五', 28, 'wang@example.com'),
    ('赵六', 35, 'zhao@example.com'),
    ('小明', 22, 'ming@example.com');

1.4 插入查询结果（INSERT…SELECT）

语法：

INSERT INTO 目标表 (列1, 列2, ...)
SELECT 列1, 列2, ... FROM 源表 WHERE 条件;

示例：

-- 将 active 用户复制到备份表
INSERT INTO users_backup (id, name, email)
SELECT id, name, email FROM users WHERE status = 'active';

-- 插入聚合结果
INSERT INTO sales_summary (year, total)
SELECT YEAR(order_date), SUM(amount) FROM orders GROUP BY YEAR(order_date);

1.5 插入或更新（ON DUPLICATE KEY UPDATE）

当插入时遇到主键或唯一索引冲突，则执行更新操作。

语法：

INSERT INTO 表名 (主键列, 其他列, ...) VALUES (值, ...)
ON DUPLICATE KEY UPDATE 列1 = 值1, 列2 = 值2, ...;

示例：

-- 如果 id=5 已存在，则更新 name 和 age
INSERT INTO users (id, name, age) VALUES (5, '刘七', 30)
ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);

-- 使用 VALUES() 函数引用插入时的值，也可以直接写值或表达式
INSERT INTO counter (page, visits) VALUES ('home', 1)
ON DUPLICATE KEY UPDATE visits = visits + 1;

1.6 替换插入（REPLACE）

REPLACE 的工作原理：如果存在主键或唯一索引冲突，则先删除旧行，再插入新行（相当于 DELETE + INSERT）。

语法：

REPLACE INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);
REPLACE INTO 表名 SET 列1 = 值1, 列2 = 值2, ...;
REPLACE INTO 表名 (列1, 列2, ...) SELECT ...;

示例：

REPLACE INTO users (id, name, age) VALUES (5, '周八', 28);
-- 若 id=5 存在，先删除原行再插入；若不存在则直接插入

⚠️ 注意：REPLACE 会导致自增列值变化，且会触发删除触发器而非更新触发器，使用时需谨慎。

1.7 INSERT 使用 SET 形式（适合列较少时）

语法：

INSERT INTO 表名 SET 列1 = 值1, 列2 = 值2, ...;

示例：

INSERT INTO users SET name = '吴九', age = 32, email = 'wu@example.com';

1.8 从文件导入数据（LOAD DATA INFILE）

语法：

LOAD DATA INFILE '文件路径' INTO TABLE 表名
    [FIELDS TERMINATED BY '分隔符']
    [LINES TERMINATED BY '换行符']
    (列1, 列2, ...);

示例：

LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, name, age, email);

需要确保 secure_file_priv 变量允许访问文件路径。

---

二、数据更新（UPDATE）

2.1 单表更新

语法：

UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... WHERE 条件;

如果不加 WHERE 条件，将更新表中所有行。

示例：

-- 更新指定用户邮箱
UPDATE users SET email = 'newemail@example.com' WHERE name = '张三';

-- 年龄加一
UPDATE users SET age = age + 1 WHERE age < 30;

-- 同时更新多列
UPDATE products SET price = price * 0.9, updated_at = NOW() WHERE category = '电子';

2.2 多表更新（MySQL 特有）

根据其他表中的值更新当前表，或同时更新多张表。

语法：

UPDATE 表1, 表2, ... SET 表1.列 = 值, 表2.列 = 值, ... WHERE 连接条件;
-- 或使用 JOIN 语法
UPDATE 表1 JOIN 表2 ON 连接条件 SET 表1.列 = 值, 表2.列 = 值, ... WHERE 额外条件;

示例：

-- 根据订单表更新用户最后下单时间
UPDATE users u 
JOIN (SELECT user_id, MAX(order_date) AS last_date FROM orders GROUP BY user_id) o 
ON u.id = o.user_id 
SET u.last_order_date = o.last_date;

-- 同时更新两张表（将同名的用户标记为VIP且订单优先）
UPDATE users u, orders o
SET u.vip = 1, o.priority = 1
WHERE u.id = o.user_id AND u.name = '张三';

2.3 带子查询的更新

示例：

UPDATE products 
SET price = price * 0.8 
WHERE category_id = (SELECT id FROM categories WHERE name = '清仓');

⚠️ 注意：在 MySQL 中，如果子查询中引用了正在更新的表，有时会受到限制，可通过派生表绕过。

---

三、数据删除（DELETE）

3.1 单表删除

语法：

DELETE FROM 表名 WHERE 条件;

如果不加 WHERE 条件，将删除表中所有行（但保留表结构）。

示例：

-- 删除特定用户
DELETE FROM users WHERE id = 10;

-- 删除年龄大于60的用户
DELETE FROM users WHERE age > 60;

-- 删除所有数据（保留表结构）
DELETE FROM logs;

3.2 多表删除（MySQL 特有）

可以一次性从一张或多张表中删除满足条件的行。

语法：

-- 从多张表中删除（使用逗号分隔表）
DELETE 表1, 表2 FROM 表1, 表2 WHERE 连接条件 AND 筛选条件;

-- 使用 JOIN 语法
DELETE 表1, 表2 FROM 表1 JOIN 表2 ON 连接条件 WHERE 筛选条件;

-- 仅删除某一张表中的行
DELETE 表1 FROM 表1 JOIN 表2 ON 条件 WHERE 筛选;

示例：

-- 删除 orders 表和对应的 order_items 表中的相关记录
DELETE o, oi FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.order_date < '2020-01-01';

-- 仅删除 order_items 中的记录，保留 orders 记录
DELETE oi FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'cancelled';

3.3 带子查询的删除

示例：

DELETE FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE is_deprecated = 1);

3.4 使用 ORDER BY 和 LIMIT 删除

MySQL 允许在 DELETE 中使用 ORDER BY 和 LIMIT 来限制删除的行数。

语法：

DELETE FROM 表名 WHERE 条件 ORDER BY 列名 LIMIT 行数;

示例：

-- 删除最早注册的5个用户
DELETE FROM users ORDER BY created_at LIMIT 5;

-- 删除年龄最大的前10名（如果有相同年龄，按id排序）
DELETE FROM users ORDER BY age DESC, id LIMIT 10;

---

四、数据查询（SELECT）- 基础

4.1 简单查询

-- 查询所有列
SELECT * FROM 表名;

-- 查询指定列
SELECT 列1, 列2 FROM 表名;

-- 查询时使用别名
SELECT name AS 姓名, age AS 年龄 FROM users;

-- 查询常量值
SELECT 'Hello', 100, NOW();

-- 去重查询
SELECT DISTINCT city FROM users;

4.2 WHERE 条件筛选

比较运算符：=, <> 或 !=, >, <, >=, <=

逻辑运算符：AND, OR, NOT

示例：

SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM products WHERE price > 100 AND category = '手机';
SELECT * FROM orders WHERE user_id IN (1, 2, 3);
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE name LIKE '张%';   -- 以“张”开头
SELECT * FROM users WHERE name LIKE '_三';   -- 第二个字为“三”

4.3 排序（ORDER BY）

-- 升序（ASC可省略）
SELECT * FROM users ORDER BY age ASC;

-- 降序
SELECT * FROM users ORDER BY age DESC;

-- 多列排序（先按年龄降序，再按id升序）
SELECT * FROM users ORDER BY age DESC, id ASC;

4.4 限制行数（LIMIT）

-- 返回前10行
SELECT * FROM users LIMIT 10;

-- 分页：跳过20行，取10行（第21-30行）
SELECT * FROM users LIMIT 20, 10;

-- 另一种写法（MySQL 8.0推荐）
SELECT * FROM users LIMIT 10 OFFSET 20;

4.5 分组与聚合（GROUP BY）

常用聚合函数：COUNT(), SUM(), AVG(), MAX(), MIN(), GROUP_CONCAT()

-- 统计每个年龄的人数
SELECT age, COUNT(*) AS cnt FROM users GROUP BY age;

-- 计算每个部门的平均薪资
SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id;

-- 拼接字符串：将每个部门下的员工姓名用逗号连接
SELECT dept_id, GROUP_CONCAT(name) AS members FROM employees GROUP BY dept_id;

4.6 分组后过滤（HAVING）

HAVING 用于对分组后的结果进行筛选，而 WHERE 用于分组前筛选原始数据。

示例：

-- 查找平均薪资大于50000的部门
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING avg_salary > 50000;

-- 查找订单数超过10的用户
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING order_count > 10;

---

五、高级查询

5.1 连接查询（JOIN）

内连接（INNER JOIN）

只返回两个表中连接条件匹配的行。

SELECT u.name, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

左外连接（LEFT JOIN / LEFT OUTER JOIN）

返回左表所有行，右表没有匹配则填充 NULL。

SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

右外连接（RIGHT JOIN）

与左连接对称，返回右表所有行。

SELECT u.name, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

全外连接（FULL OUTER JOIN）

MySQL 不直接支持全外连接，可以使用 UNION 模拟：

SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.order_id FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

交叉连接（CROSS JOIN）

返回两个表的笛卡尔积。

SELECT * FROM users CROSS JOIN products;
-- 等价于 SELECT * FROM users, products;

自连接（同一张表连接自身）

示例：查找员工及其经理（员工表中 manager_id 引用 id）

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

5.2 子查询

标量子查询（返回单值）

SELECT name, age 
FROM users 
WHERE age > (SELECT AVG(age) FROM users);

行子查询（返回一行多列）

SELECT * FROM users 
WHERE (name, age) = (SELECT name, age FROM users WHERE id = 1);

表子查询（返回多行多列）

SELECT * FROM (SELECT id, name FROM users WHERE age > 18) AS adult_users;

使用 IN / NOT IN

SELECT name FROM users 
WHERE id IN (SELECT DISTINCT user_id FROM orders);

使用 EXISTS / NOT EXISTS

SELECT name FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

5.3 联合查询（UNION / UNION ALL）

• UNION：合并两个查询结果，并自动去重。
• UNION ALL：合并结果，不去重，效率更高。

SELECT name FROM users WHERE age < 18
UNION
SELECT name FROM users WHERE age > 60;

-- 要求两个查询的列数和数据类型一致

5.4 条件表达式（CASE）

简单 CASE：

SELECT name,
    CASE age
        WHEN 18 THEN '成年'
        WHEN 60 THEN '老年'
        ELSE '其他'
    END AS age_group
FROM users;

搜索 CASE：

SELECT name,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 60 THEN '中青年'
        ELSE '老年'
    END AS age_segment
FROM users;

5.5 常用内置函数

字符串函数

• CONCAT(str1, str2, ...)：连接字符串
• LENGTH(str)：返回字节长度（注意字符集）
• CHAR_LENGTH(str)：返回字符长度
• SUBSTRING(str, pos, len)：截取子串
• UPPER(str), LOWER(str)：大小写转换
• TRIM(str)：去除两端空格
• REPLACE(str, from, to)：替换
• INSTR(str, substr)：返回子串位置

日期时间函数

• NOW()：当前日期时间
• CURDATE()：当前日期
• CURTIME()：当前时间
• DATE_ADD(date, INTERVAL expr unit)：日期增加
• DATEDIFF(date1, date2)：相差天数
• YEAR(date), MONTH(date), DAY(date)：提取部分
• DATE_FORMAT(date, format)：格式化

数值函数

• ABS(x)：绝对值
• ROUND(x, d)：四舍五入保留 d 位小数
• CEIL(x), FLOOR(x)：向上/向下取整
• RAND()：随机数 0~1

聚合函数（常用于分组）

• COUNT(*)：行数
• SUM(列)：求和
• AVG(列)：平均值
• MAX(列) / MIN(列)

5.6 窗口函数（MySQL 8.0+）

-- ROW_NUMBER：为每行分配一个唯一序号
SELECT name, age, ROW_NUMBER() OVER (ORDER BY age DESC) AS rn FROM users;

-- RANK / DENSE_RANK
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM scores;

-- 分组内的聚合
SELECT 
    dept_id, 
    name, 
    salary,
    AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

-- LAG / LEAD 获取前一行或后一行
SELECT 
    date, 
    amount,
    LAG(amount, 1) OVER (ORDER BY date) AS prev_amount
FROM sales;

5.7 公用表表达式（CTE，MySQL 8.0+）

WITH high_salary AS (
    SELECT id, name, salary FROM employees WHERE salary > 50000
)
SELECT * FROM high_salary WHERE name LIKE '张%';

-- 递归 CTE（用于树形结构查询）
WITH RECURSIVE subcategories (id, name, parent_id) AS (
    SELECT id, name, parent_id FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id, c.name, c.parent_id 
    FROM categories c
    INNER JOIN subcategories s ON c.parent_id = s.id
)
SELECT * FROM subcategories;

5.8 查询结果导出到文件（SELECT … INTO OUTFILE）

语法：

SELECT 列... INTO OUTFILE '文件路径'
    [FIELDS TERMINATED BY ',' ENCLOSED BY '"']
    [LINES TERMINATED BY '\n']
FROM 表名 WHERE 条件;

示例：

SELECT id, name, email INTO OUTFILE '/tmp/users_export.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM users WHERE status = 'active';

同样受 secure_file_priv 变量限制。

---

六、其他 DML 操作

6.1 清空表（TRUNCATE）

虽然通常归类为 DDL，但因其直接操作数据且无法回滚，有时放在这里说明。

TRUNCATE TABLE 表名;

删除表中所有行，重置自增计数器，速度快于 DELETE 且不记录每行删除日志。

6.2 锁定与解锁表（辅助数据操作）

-- 读锁（共享锁）
LOCK TABLES users READ;

-- 写锁（排他锁）
LOCK TABLES users WRITE;

-- 解锁
UNLOCK TABLES;

6.3 执行 SQL 脚本文件（SOURCE）

mysql> SOURCE /path/to/script.sql;

---

七、完整综合示例

-- 创建测试表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    dept VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

-- 1. 插入数据
INSERT INTO employees (name, dept, salary, hire_date) VALUES 
    ('张三', '技术部', 8000, '2020-01-15'),
    ('李四', '市场部', 6000, '2019-06-20'),
    ('王五', '技术部', 9500, '2021-03-10'),
    ('赵六', '市场部', 5500, '2022-07-01'),
    ('孙七', '技术部', 7200, '2018-11-30');

-- 2. 更新数据：技术部所有人涨薪 10%
UPDATE employees SET salary = salary * 1.1 WHERE dept = '技术部';

-- 3. 删除市场部工资低于6000的员工
DELETE FROM employees WHERE dept = '市场部' AND salary < 6000;

-- 4. 查询每个部门的平均工资、最高工资、人数
SELECT 
    dept,
    ROUND(AVG(salary), 2) AS avg_sal,
    MAX(salary) AS max_sal,
    COUNT(*) AS headcount
FROM employees
GROUP BY dept
HAVING headcount >= 2;

-- 5. 使用窗口函数：按工资排名（MySQL 8.0）
SELECT name, dept, salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- 6. 导出查询结果为文件
SELECT * INTO OUTFILE '/tmp/employees_backup.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM employees;

---

八、注意事项与最佳实践

操作	注意事项
INSERT	批量插入比逐行插入效率高；ON DUPLICATE KEY UPDATE 可避免先查询再更新；使用 REPLACE 时注意触发器影响。
UPDATE	务必带 WHERE 条件，否则全表更新；多表更新时先备份或事务测试。
DELETE	不带 WHERE 会删除所有行，建议 BEGIN 后用 ROLLBACK 验证；删除大量数据时可分批删除，避免长锁。
SELECT	避免 SELECT * 以提高性能和可维护性；大表查询使用 LIMIT；合理使用索引；连接查询时确保连接列有索引。
事务	对 INSERT / UPDATE / DELETE 可使用事务（START TRANSACTION; ... COMMIT;）确保数据一致性。
性能	UNION 会去重带来额外开销，确定无需去重时用 UNION ALL；子查询很多情况下可改写为 JOIN 提升性能。

---

以上涵盖了 MySQL 中几乎所有的 DML 和 DQL 操作，从基础增删改查到高级功能（窗口函数、CTE、多表更新、导出文件等）。根据实际场景灵活运用即可。