02. MySQL - 03. DML数据操作语言 - 《Python BI AI》

01. 添加数据
- 1.1 数据插入概述
- 1.2 向表中插入数据
  - 1.2.1 插入方式一
  - 1.2.3 后续实验数据准备
03. 筛选条件
- 3.1 筛选条件规则表
- 3.2 筛选练习
04. 删除数据
- 4.1 DELETE删除行数据
  - 4.1.1 DELETE单表删除
  - 4.1.2 删除关联数据
- 4.2 TRUNCATE清空表

01. 添加数据

1.1 数据插入概述

插入数据即为将数据按照特定的规则添加到表中。
数据插入时的注意点：
- 允许为空的字段可以插入NULL值，不允许为空的字段必须插入数据。
- 有默认约束的字段默认值不写时会自动填充设置好的默认值，无默认约束且运行为NULL的字段不写时会自动填充NULL值。
- 插入的值的类型要与列的类型一致或兼容。
- 插入字符、日期类型的数据需要用单引号引起来。
  1.2 向表中插入数据
  1.2.1 插入方式一
方式一SQL语法格式：INSERT INTO 表名 (列名1, 列名2, ……, 列名n) VALUE (值1, 值2, ……, 值n) ;
- 注意1：列与值要一一对应，否则会导致表无意义。
- 注意2：插入数据时要注意数据的类型与表结构是否匹配，否则会出现插入错误。

示例：向班级表中插入数据{cno=10, cname=高三一班}。

INSERT INTO `class`(`cno`, `cname`) VALUE (10, '高三一班');
SELECT * FROM `class`;

方式一变式1：INSERT INTO 表名 VALUE (值1, 值2, ……, 值n) ;
- 要插入的数据是所有字段的数据时，可以省略列名。
- 省略列表时默认插入的是所有列，且VALUE后的值列表的顺序必须与表的原结构保持一致。

示例：向班级表中插入数据[11, 高三二班]。

INSERT INTO `class` VALUE (11, '高三二班');
SELECT * FROM `class`;

方式一变式2：INSERT INTO 表名 VALUES (值1, 值2, ……, 值n), (值1, 值2, ……, 值n), …, (值1, 值2, ……, 值n);
- 这种方式可以一次性插入多条数据。
- 当然在插入数据时也可以只给指定的字段插入，形式与方式一的基本格式类似。
示例：向班级表中插入数据[12, 高三三班], [13, 高三四班]。 ``python INSERT INTOclass` VALUES (12, ‘高三三班’), (13, ‘高三四班’);

SELECT * FROM class;

<a name="CNGAB"></a>
#### 1.2.2 插入方式二
- 方式二SQL语法格式：`INSERT INTO 表名 SET 列名1=值1, 列名2=值2, ……, 列名n=值n ;`
- 示例：向学生表中插入数据`{姓名=孟胤, 生日=2000-03-11, 性别=男, 学号=10}`。
```python
INSERT INTO `student` SET `sname`='孟胤', `birthday`='2000-03-11', `gender`='男', `cno`=10;
SELECT * FROM `student`;

方式二与方式一的区别：
- 方式一支持一次插入多行，方式二不支持。
- 方式一支持子查询插入，且查询结果集会插入到表中，方式二不支持。
可以看出，方式一的功能比方式二更完善，所以在日常开发中用方式一居多。

1.2.3 后续实验数据准备
运行以下SQL语句，向学生表中插入数据： ``sql INSERT INTOstudent(sname,birthday,gender,cno`) VALUES (‘王慧’, ‘2001-05-17’, ‘女’, 10), (‘杨美奂’, ‘2002-09-22’, ‘女’, 11), (‘纪涵’, ‘2000-07-19’, ‘男’, 11), (‘安佰杰’, ‘2001-11-11’, ‘男’, 11), (‘刘晓慧’, ‘2002-12-16’, ‘女’, 11), (‘耿云鹏’, ‘2001-01-09’, ‘女’, 12), (‘毕士科’, ‘2001-03-26’, ‘男’, 12), (‘魏雷’, ‘2002-10-11’, ‘男’, 12), (‘许欣洋’, ‘2000-02-20’, ‘男’, 13), (‘狄大帅’, ‘2001-08-17’, ‘男’, 13), (‘马大鹏’, ‘2000-09-22’, ‘男’, 13);

SELECT * FROM student;

<a name="culKU"></a>
## 02. 修改数据
<a name="Q3RYM"></a>
### 2.1 修改整列数据
- 使用`UPDATE 表名 SET 字段名=新值;`的语句可以将指定表中指定列的所有数据全部修改成新值。
   - 可以发现，这种修改是不安全的。
   - 因此，MySQL 8.0开始执行这条语句会报错。
```sql
mysql> UPDATE `class` SET `cname`='高三1班';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

报错的原因是MySQL 8.0开始为数据的修改设置了安全等级（全局变量sql_safe_updates），安全等级共有0和1两个值，默认为1。
若就想修改整个字段的所有数据，可以使用SET sql_safe_updates = 0;语句降低安全等级，然后再修改数据即可。 ```python mysql> SET sql_safe_updates = 0; Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE class SET cname=’高三1班’; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0

mysql> SELECT * FROM class; +——-+————-+ | cno | cname | +——-+————-+ | 10 | 高三1班 | | 11 | 高三1班 | | 12 | 高三1班 | | 13 | 高三1班 | +——-+————-+ 4 rows in set (0.00 sec)

mysql> — 删除完记得改回来 mysql> SET sql_safe_updates = 1; Query OK, 0 rows affected (0.00 sec)

<a name="rEAN9"></a>
### 2.2 修改符合条件的数据
- 使用`UPDATE 表名 SET 字段名=新值 WHERE 筛选条件;`可以将符合筛选条件的数据行中指定列的数据修改成新值。
- 示例：将班级编号为11的班级名称修改成高三2班，以及编号12修改为高三3班、编号13修改为高三4班。
```sql
UPDATE `class` SET `cname`='高三2班' WHERE `cno`=11;
UPDATE `class` SET `cname`='高三3班' WHERE `cno`=12;
UPDATE `class` SET `cname`='高三4班' WHERE `cno`=13;
SELECT * FROM `class`;

03. 筛选条件

3.1 筛选条件规则表

3.2 筛选练习

向课程表、成绩表中添加数据。
- 课程表数据图：

成绩表数据图：

数据插入SQL语句： ``python INSERT INTOcourseVALUES (10001, '语文'), (10002, '数学'), (10003, '英语'); SELECT * FROMcourse`;

INSERT INTO score_tb(sno, cid, score) VALUES (1, 10001, 88), (1, 10002, 91), (1, 10003, 84), (2, 10001, 91), (2, 10003, 99), (3, 10001, 56), (3, 10002, 48), (3, 10003, 59), (4, 10001, 77), (4, 10002, 99), (5, 10002, 82), (5, 10003, 96), (6, 10001, 62), (6, 10002, 68), (6, 10003, 72), (7, 10001, 39), (7, 10002, 99), (7, 10003, 47), (8, 10001, 92), (8, 10002, 88), (8, 10003, 96), (9, 10001, 62), (9, 10002, 68), (10, 10003, 72), (10, 10001, 39), (11, 10002, 99), (11, 10003, 47), (11, 10001, 92); SELECT * FROM score_tb;


- 把学号为1、3、4的学生的所有成绩都修改成87，并把课程编号为10002的成绩改为91。
```sql
-- 所有成绩都修改成87。
UPDATE `score_tb` SET `score`=87 WHERE `sno` IN (1, 3, 4);
-- 课程编号为10002的成绩改为91。
UPDATE `score_tb` SET `score`=91
WHERE `sno` IN (1, 3, 4) AND `cid`=10002;
-- 验证修改
SELECT * FROM `score_tb` WHERE `sno` IN (1, 3, 4);

04. 删除数据

4.1 DELETE删除行数据

DELETE删除也分为单表删除和多表连接删除。

4.1.1 DELETE单表删除
单表删除格式：DELETE FROM 表名 [WHERE 筛选条件];
- 如果没有WHERE子句，则会删除表中所有的数据，但是表配置信息不会被删除。
- 如果没有WHERE子句的DELETE在MySQL 8.x中会出现2.1中同样的错误，其原因与解决方式也与2.1完全一样。
- DELETE语句删除的都是一整行数据，并非一行中某一字段中的数据。
- 若行中有数据被别的表所关联着，则DELETE删除该行数据会失败。
- 有WHERE子句的DELETE会先用WHERE筛选出符合条件的行数据，然后再将这些行数据删除。
示例1：删除成绩表的所有数据。（记得加回来） ``sql -- 删除数据 SET sql_safe_updates = 0; DELETE FROMscore_tb; SET sql_safe_updates = 1; SELECT * FROMscore_tb`;

— 恢复数据 — DELETE删除表后，配置信息不会删除，因此重新插入数据后sid会从29开始加。（29是因为原来数据到28） — 为了避免这种情况，需要有ALTER语句将自增长的起始值重置为1。 ALTER TABLE score_tb AUTO_INCREMENT 1;


- 示例2：删除成绩表中学号为1的数据。
```sql
DELETE FROM `score_tb` WHERE `sno`=1;
SELECT * FROM `score_tb`;

4.1.2 删除关联数据

当需要删除的数据被其他表关联时，删除会出现错误：

[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`school`.`score_tb`, CONSTRAINT `fk_score_student` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`))

此时，就需要先删除关联表中的关联数据，再删除被关联表中被关联的数据。

4.2 TRUNCATE清空表
TRUNCATE简单介绍：
- TRUNCATE语句又被称为清空，会删除表中所有的数据（包括配置信息也会被删除）。
- TRUNCATE语句不能跟任何的子句，也不能实现多表级联删除。
TRUNCATE语法格式：TRUNCATE TABLE 要清空的表名;
与DELETE语句的比较：
- 当要清空表时，TRUNCATE语句效率比DELETE语句的效率要高。
- 如果要删除的表中存在自增长列：
  - DELECT删除数据后再插入数据，自增长列的值从断点开始。
  - TRUNCATE删除后再插入数据，自增长列的值从1开始。
- TRUNCATE删除没有返回值，DELETE删除有返回值。
- TRUNCATE删除不能回滚，DELETE删除可以回滚。

03. DML数据操作语言

01. 添加数据

1.1 数据插入概述

1.2 向表中插入数据

1.2.1 插入方式一

1.2.3 后续实验数据准备