MySQL03-数据操作之增删改

1. insert 数据插入-DML

insert 语句用于向数据库中插入新的数据。

1.1 单行数据插入

用 insert 语句进行单行数据插入时，每个字段与其值是严格一一对应的，即每个值、值的顺序、值的类型必须与对应的字段相匹配。其语法如下：

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

# 示例，在 student 表中插入数据
insert into student (id,name,age,sex) 
values (1,'张三',16,'男');

注意：

1. 需要插入空值或碰到自增字段时，用 NULL 表示数据值；
2. 插入语句中的各字段与其在表中定义的顺序、数量无需一致，只要与 VALUES 中值的顺序、数量相互匹配即可，也即可以让字段缺省实现部分数据插入；
3. 当插入数据的顺序、数量与数据表中字段的顺序、数量一致时，参数 (字段1,字段2,...) 可以缺省不写；
4. 使用 insert 插入语句时，要求插入的值与相应字段在数量和类型上完全匹配，否则插入操作失败；
5. 使用 insert 插入语句时，除了需要正确的语法，还要求插入操作与数据表上的约束不冲突，否则正确的语法也可能插入失败；
6. 字符串和日期型数据应该包含在引号中；
7. 插入的数据大小，应该在字段的规定范围内。

1.2 多行数据插入

用 insert 语法进行多行数据插入，语法规则和单行数据一样，各数据值用 , 连接。其语法如下：

insert into 表名 [(字段1,字段2,字段3 ...)]
values (值11,值12,值13 ...),
(值21,值22,值23 ...),
(值31,值32,值33 ...);

# 示例，在 student 表中插入数据
insert into student (id,name,age,sex)
values (0001,'张三',16,'男'),
(0002,'李四',17,'男'),
(0003,'王二麻子',18,'女');

2. update 数据更新-DML

update 语句用于更新数据表中已经存在的数据记录。

2.1 简单更新

用 insert 语句进行数据插入的语法如下：

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

# 示例，在 student 表中 name 字段为 张三 的 数据行中，更改 age 字段为 20 ，sex 字段为 男
update student
set age=20,sex='男'
where name='张三';

其中，参数 字段名1 = 新值1/条件表达式 表示将要更新的数据，其中的新值既可以是具体值，也可以是表达式，参数 where 条件表达式 表示将要更新的数据所在位置。

注意：

1. 用 insert 语句进行简单更新时，参数 where 条件表达式 是可选项，该参数省略时则说明更新指定字段的所有行的值；
2. 使用 update 语句时，即使语法正确，如果操作的数据与其他表之间存在外键关系，更新操作也可能受限，或者需要设置级联更新，在实际操作中，一条存在外键的主键记录是不能被删除或修改的；

2.2 级联更新

级联更新是指更新父表主键值时，所有子表外键值自动与父表主键值匹配更新的现象，外键约束中，update 语句有如下四种属性：

1. CASCADE，级联操作属性：如果从父表中更新某个记录，首先检查该记录主键是否有对应外键，如果有则一并更新外键所在子表中的记录；
2. NO ACTION / RESTRICT，限制操作属性：在 MySQL 中两者等价，如果在父表中更新某个记录，首先检查该记录主键是否有对应外键，如果有则不允许更新，NO ACTION 来自标准的 SQL；
3. SET NULL，设置空值属性：如果在父表中更新某个记录，首先检查该记录主键是否有对应外键，如果有则设置子表中该外键值为空值(NULL)，这要求定义子表时该外键允许取空值（不为非空约束）；

要想设置级联更新，应该设置外键约束 update 属性为 CASCADE。

实现级联更新操作一般分为三个步骤：

1. 检查子表的外键约束，并将其设置为级联更新属性；
2. 检查主表及子表是否有相同信息记录；
3. 如果有则从主表更新目标记录，子表即可由系统自动更新相应记录；

3. delete 数据删除-DML

delete 语句用于删除数据表的一行数据或者多行数据，甚至所有数据。

3.1 简单删除

用 delete 语句进行数据删除的语法如下：

delete from 表名
[where 条件表达式]
[order by 字段名 [asc / desc]];
[limit 行数]

# 示例，删除 student 表中 age 字段值为 14 的所有数据行
delete from student
where age=14;

其中，参数 where 条件表达式 表示将要更新的数据所在位置；参数 order by 字段名 [asc / desc] 表示按照指定的顺序进行删除操作，asc 为升序（数值从小到大），desc 为降序（数值从大到小）；参数 limit 行数 用于告知服务器所限制删除的行数；order by 子句要和 limit 子句配合使用。

注意：

1. 用 delete 语句进行简单删除时，参数 where 条件表达式 是可选项，该参数省略时则说明删除指定字段的所有数据行；
2. 删除带有空值的数据行，where语句应写为 where 字段名 is null，如删除score字段为空值的数据行：where score is null ；
3. delete 语句不能删除某一个字段的值（可以使用 update 将数据更新为空值变相实现）；
4. 使用 delete 语句时，即使语法正确，如果操作的数据与其他表之间存在外键关系，更新操作也可能受限，或者需要设置级联更新，在实际操作中，一条存在外键的主键记录是不能被删除或修改的；

3.2 级联删除

级联删除是指删除父表主键值时，所有子表外键值自动与父表主键值匹配删除的现象，外键约束中，delete 语句有如下四种属性：

1. CASCADE，级联操作属性：如果从父表中删除某个记录，首先检查该记录主键是否有对应外键，如果有则一并删除外键所在子表中的记录；
2. NO ACTION / RESTRICT，限制操作属性：在 MySQL 中两者等价，如果在父表中删除某个记录，首先检查该记录主键是否有对应外键，如果有则不允许删除，NO ACTION 来自标准的 SQL；
3. SET NULL，设置空值属性：如果在父表中删除某个记录，首先检查该记录主键是否有对应外键，如果有则设置子表中该外键值为空值(NULL)，这要求定义子表时该外键允许取空值（不为非空约束）；

要想设置级联删除，应该设置外键约束 delete 属性为 CASCADE。

实现级联删除操作一般分为三个步骤：

1. 检查子表的外键约束，并将其设置为级联删除属性；
2. 检查主表及子表是否有相同信息记录；
3. 如果有则从主表删除目标记录，子表即可由系统自动删除相应记录；

3.3 删除风险等级

DELETE 数据删除操作从语法来看比较简单，但因为删除操作会从当前数据库中清除数据，所以说是一种有风险的操作。删除操作有三个风险等级，具体如下：

1. 删除数据表

DROP TABLE 表名;

该语句用于从数据库中删除已存在的数据表，包括数据表结构，且不可恢复，因此在使用时需特别小心。这是危险级别最高的删除命令，属于 DDL（数据定义语言）。

2. 不可恢复的删除操作

TRUNCATE TABLE 表名;

该语句只能用于将数据表内全部数据删除，但空表结构不会被删除，因 TRUNCATE 删除操作后不记录 MySQL 日志，同样不可以恢复数据。这是危险级别次之的删除命令。

3. 有条件恢复的删除操作

DELETE FROM table 表名
WHERE condition;

这是在特定条件下可恢复的删除操作，DELETE 语句会根据用户需求删除数据表内部分或全部数据记录，与 TRUNCATE 操作不同的是DELETE 操作记录 MySQL 日志，能有条件地实现数据回滚恢复。但从数据删除效率来说TRUNCATE 操作优于 DELETE 操作。

注意：

1. DELETE 语句后可跟 WHERE 子句，可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录，TRUNCATE 语句则不能跟 WHERE 子句，因此只能用于删除表中的所有记录；
2. 使用 TRUNCATE 语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用 DELETE 语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1；
3. DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句；
4. 无论哪种删除操作都存在数据丢失的风险，所以在实际应用场景中执行删除命令是需要相应权限的；