DML语句

主要是数据操作语句（增删改查）

添加数据

-- 格式：
insert into 表名称(列名,...列名) values(值列表1,...,值列表n);

-- 不带字段名的方式，VALUES 的字段顺序，必须和建表时的字段定义 完全一致
INSERT INTO b VALUES (1, 100, 1, '张三', 88.50, '2000-01-01', '男', '唱,跳', 1);

-- 带字段名的方式，VALUES 的字段顺序，和列名的顺序一致。
INSERT INTO b (sid, vid, id, name, score, birthday, sex, hobby, status)
VALUES (1, 100, 1, '张三', 88.50, '2000-01-01', '男', '唱,跳', 1);

-- 一次性添加多条数据
INSERT INTO b (sid, vid, id, name, score, birthday, sex, hobby, status)
VALUES (1, 100, 1, '张三', 88.50, '2000-01-01', '男', '唱,跳', 1),
VALUES (1, 100, 1, '赵四', 88.50, '2000-01-01', '男', '唱,跳', 1),
VALUES (1, 100, 1, '王五', 88.50, '2000-01-01', '男', '唱,跳', 1);

嵌套子查询

使用INSERT语句+子查询，复制数据，此时INSERT不用在写values

-- 把t_dep表中的数据复制到dep表中，表结构得一致。
insert into dep (select * from t_dep where id < 3)

-- 同时创建表结构和复制数据
create table d_dep as (select * from t_dep);

修改数据

update 表名称 set 字段名1 = 值1,字段名2 = 值2 [where 条件];

UPDATE b SET score = 95.00, hobby = '唱,rap' WHERE id = 1;

如果没有加where条件，表示修改所有行，这个字段的值；

嵌套子查询

子查询就是 在一个 SQL 语句里嵌套另一个查询，常用于更新或筛选。

📌 使用子查询更新字段

-- 把分数改成某个学生的分数
-- 意思是：把 id=2 这行的分数改为表里最高分。
UPDATE b 
SET score = (SELECT MAX(score) FROM b) 
WHERE id = 2;

📌 使用子查询筛选条件

-- 把分数更新为 60，条件是分数小于平均分的学生
-- 意思是：把所有低于平均分的同学的分数改成 60
UPDATE b 
SET score = 60 
WHERE score < (SELECT AVG(score) FROM b);

📌 结合 IN 使用

-- hobby 中包含 'rap' 的学生，分数加 5
UPDATE b 
SET score = score + 5
WHERE id IN (SELECT id FROM b WHERE FIND_IN_SET('rap', hobby));

临时表

在 MySQL 里，如果 UPDATE 的目标表和子查询中的查询表是同一个表，就会报错。

-- 原因 ：MySQL 不允许在同一条语句里 一边更新表，又一边从这个表里取数据。
ERROR 1093 (HY000): You can't specify target table 'b' for update in FROM clause

解决办法：临时表（子查询结果先存储）

✅ 方法一：嵌套子查询 + 别名（最常用）

UPDATE employee 
SET salary = (
  SELECT salary FROM (
    SELECT salary FROM employee WHERE name = '张三'
  ) AS tmp
)
WHERE name = '李四';

📌 原理：给子查询套一层，让它变成派生表 tmp。
👉 适合：更新单个字段。

✅ 方法二：JOIN 派生表

UPDATE employee e
JOIN (
  SELECT name, MAX(salary) AS max_salary 
  FROM employee 
  WHERE department_id = 10
) t ON e.name = '李四'
SET e.salary = t.max_salary;

📌 原理：把子查询结果当作临时表 t，用 JOIN 更新。
👉 适合：批量更新。

✅ 方法三：用 变量方式

SET @sal = (SELECT salary FROM employee WHERE name = '张三');

UPDATE employee 
SET salary = @sal 
WHERE name = '李四';

📌 原理：先用变量存子查询结果，再更新。
👉 适合：更新单条记录。

✅ 方法四：临时表

CREATE TEMPORARY TABLE tmp AS
SELECT salary FROM employee WHERE name = '张三';

UPDATE employee 
SET salary = (SELECT salary FROM tmp) 
WHERE name = '李四';

DROP TEMPORARY TABLE tmp;

📌 原理：先存到临时表，再更新。
👉 适合：需要多次使用子查询结果时。
临时表，断开连接之后会自动消失

✅ 方法五：CTE（MySQL 8.0+）

WITH tmp AS (
  SELECT salary FROM employee WHERE name = '张三'
)
UPDATE employee 
SET salary = (SELECT salary FROM tmp)
WHERE name = '李四';

📌 原理：用公用表表达式 WITH，避免直接引用同一表。
👉 适合：结构更清晰（MySQL 8.0 以上才支持）。

删除数据

delete from 表名称 [where 条件];
DELETE FROM b WHERE id = 2;

如果没有where条件，表示删除整张表的数据;

可以使用 truncate来删除整张表的数据，效率更高，但是不支持回滚。

truncate 表名称;

⚡️ 用delete删除整张表和用truncate删除整张表的数据的区别

（1）truncate速度快

（2）truncate无法回滚

truncate因为底层是把表drop掉，然后新建了一张空表。

delete因为底层是一行一行删数据。

嵌套子查询

📌 基于子查询的条件删除

-- 删除分数低于平均分的学生
DELETE FROM b 
WHERE score < (SELECT AVG(score) FROM (SELECT AVG(score) FROM b) AS tmp);

注意：

这里必须套一层 (SELECT ... FROM ( ... ) tmp)，
因为 MySQL 不允许直接在 DELETE 时引用同一张表。

📌 用 IN + 子查询

-- 删除 hobby 中包含 'rap' 的学生
-- 👉 子查询查出要删除的 id，外层再用 IN 删除。
DELETE FROM b 
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM b WHERE FIND_IN_SET('rap', hobby)
  ) AS tmp
);

📌 用 EXISTS 子查询

-- 删除有重复姓名的学生（只保留 id 最小的）
-- 📌 用 EXISTS 判断外层记录是否满足子查询条件。
DELETE FROM b 
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT name, MIN(id) AS min_id
    FROM b
    GROUP BY name
  ) AS tmp
  WHERE b.name = tmp.name AND b.id > tmp.min_id
);

📌 JOIN 派生表删除（推荐用于批量删除）

-- 删除分数不是最高分的学生（每个名字只保留最高分）
-- JOIN 子查询结果（派生表），直接定位要删的行。
DELETE b
FROM b
JOIN (
  SELECT name, MAX(score) AS max_score
  FROM b
  GROUP BY name
) t ON b.name = t.name AND b.score < t.max_score;

🔑 总结

单值判断：用子查询 + 临时表别名 tmp
多行条件：用 IN 或 EXISTS
批量复杂删除：用 JOIN 派生表
MySQL 8.0+ 还可以用 CTE WITH，写法更清晰

查询数据

查询数据详见DQL章节。

添加数据​

嵌套子查询​

修改数据​

嵌套子查询​

临时表​

删除数据​

嵌套子查询​

查询数据​

添加数据

嵌套子查询

修改数据

嵌套子查询

临时表

删除数据

嵌套子查询

查询数据