MySQL表记录的更新操作（增删改）

表记录的插入

使用insert语句插入新记录

insert into 表名[(字段列表)] value(值列表)

• 字段列表：可选项。有若干个要插入的数据的字段名组成，各字段使用逗号“,”隔开。若省略了字段列表，则表示需要为表的所有字段插入数据。
• 值列表：必选项。值列表给出了待插入的若干个字段值，各个字段值使用逗号“,”隔开，并与字段列表形成一一对应关系。
• 向char、varchar、text以及日期型的字段插入数据时，字段值要用单引号括起来。
• 向自增型auto_increment字段插入数据时，建议插入NULL值，此时将项自增型字段插入下一个编号。
• 向默认值约束字段插入数据时，字段值可以使用default关键字，表示插入的是该字段的默认值。
• 插入新记录时，需要注意表之间的外键约束关系，原则上先给父表插入数据，然后再给子表插入数据。
  • 如果字段存在唯一性约束，插入的字段值不能重复；
  • 如果字段存在外键约束，子表字段值要么是null，要么是父表中相应字段值，如果指定的值不在此范围内，插入将失败；
  • 即使insert语句执行失败，表的自增型字段仍会递增。

insert into classes(class_no,class_name,department_name) values(null,'2012自动化1班','机电工程');

批量插入多条记录

insert into 表名[(字段列表)] value
(值列表1),
(值列表2),
...,
(值列表n);

使用insert…select插入结果集

将源表的目标结果插入到目标表中。

insert into 目标表名[(字段列表1)];
select (字段列表2) from 源表 where 条件表达式;

• 字段列表1与字段列表2的字段个数必须相同，且对应字段的数据类型尽量保持一致。如果源表与目标表的表结构完全相同，则字段列表1可省略。

示例：

use choose;
create table new_student like student;
insert into new_student select - from student;

使用replace插入新记录

• 语法格式1：

replcae into 表名[(字段列表)] value(值列表)

• 语法格式2：

replace [into]目标表名[(字段列表1)]

• 语法格式3：

replace [into]表名
set 字段1=值1,字段2=值2

使用replace语句向表中插入新记录时，如果新记录的主键值或唯一性约束的字段值与旧记录先删除（注意：旧记录删除时也不能未被外键约束条件），然后再插入新记录。

表记录的修改

update 表名;
set 字段名1=值1,字段名2=值2,...,字段名n=值n;
[where 条件表达式];

• update语句可用于对表中的一行、多行，甚至所有记录进行修改；
• where子句指定了表中哪些记录需要修改，若省略了where子句，则表示修改表中的所有记录；
• set子句指定了要修改的字段以及该字段修改后的值
• 修改表记录时，需要注意表的唯一性约束、表之间的外键约束关系以及级联选项的设置。

示例：将班级表中class_no<=3的院系名修改为“机电工程学院”

use choose;
update classes set deparment_name='机电工程学院' where class_no<=3;

表记录的删除

使用delete删除表记录

delete from 表名[ where 条件表达式]

• 如果没有指定where子句，则该表的所有记录都将被删除，但表结构依然存在。
• 删除表记录时，需要注意表之间的外键约束关系及级联选项的设置，否则没有办法成功删除。

使用truncate清空表记录

truncate [table]表名

• 如果清空的表是父表，那么truncate命令将永远执行失败。

delete与truncate的区别

• 使用delete清除表中所有记录后，表的自增型字段的起点并不会改变；
• 使用truncate清空表记录后，表的自增型字段的起点将被重置为1.

题目实例

删除重复的电子邮箱

来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

DELETE p1 FROM Person p1,Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id

delete p1 from Person p1 join Person p2 on p1.Email = p2.Email where p1.Id > p2.Id;

交换工资

给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。
注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。

UPDATE salary
SET
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;

---

《MySQL数据库基础与实例教程》——孔祥盛