吐槽一下B站不支持markdown，我只管粘贴吧！如果需要外链的话，联系我，我本来写在语雀里了！

4.1 数据插入 88

基础知识

【1】 方式

方式1：VALUES的方式添加

• 情况1：为表的所有字段按默认顺序插入数据；

值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

INSERT INTO departments

VALUES (70, 'Pub', 100, 1700);

INSERT INTO departments

VALUES (100, 'Finance', NULL, NULL);

• 情况2：为表的指定字段插入数据            就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

INSERT INTO departments(department_id, department_name)

VALUES (80, 'IT');

• 情况3：同时插入多条记录

注意：字符和日期型数据应包含在单引号中。  VALUES 也可以写成VALUE ，但是VALUES是标准写法

方式2：将查询结果插入到表中

4.2 数据修改 90

可以一次更新多条数据。

如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

4.3 数据删除 91

4.3.1 使用DELETE语句删除数据 91
4.3.2 使用TRUNCATE TABLE语句删除表数据 93

使用 DELETE 语句从表中删除数据

【商业实例】 Petstore数据操作 93

4.4.1 Petstore数据表及其内容 93
4.4.2 Petstore数据录入 96
4.4.3 Petstore数据修改与删除操作 98
【单元小结】 100
【综合实训】 Library数据操作 100
【理论练习】 102
【实战演练】 SchoolDB数据操作 103

操作记录：出现问题以及解决方案的记录

1、插入数据的三个方法 具体演示过程

mysql中常用的三种插入数据的方法

原文链接：https://blog.csdn.net/weixin_44848356/article/details/119113174

insert into：正常的插入数据，插入数据的时候会检查主键或者唯一索引，如果出现重复就会报错。

replace into：替换数据。插入时，如果表中已经存在相同的primary key或者unique索引，则用新数据替换；如果没有相同的primary key或者unique索引，则直接插入。

insert ignore into：插入时，如果表中已经存在相同的primary key或unique索引，则不插入；如果没有相同的primary key或者unique索引，则直接插入。这样就不用校验是否已经存在了，有则忽略，无则添加

注：对于以上三种方法，如果表中没有设置主键或唯一索引；则效果都是一样的，即直接插入数据。

实例演示：

CREATE TABLE actor (

  actor_id  smallint(5)  NOT NULL PRIMARY KEY,

  first_name  varchar(45) NOT NULL,

  last_name  varchar(45) NOT NULL,

  last_update  DATETIME NOT NULL);

我先在DBeaver中执行上述语句，不过先创建一个数据库先

• 在输出窗口中有一套信息：Integer display width is deprecated and will be removed in a future release.，其含义是：从MySQL 8.0.17开始，对于整数数据类型，不建议使用display width属性，即不用M显示宽度，并且在将来的MySQL版本中将删除对它的支持。

• 解决方案：在定义类型的话，就integer即可。

在DBeaver中执行sql语句，ctrl+回车即可

insert into actor values('3','WD','GUINESS','2006-02-15 12:34:33'); select * from actor

（一）测试insert into

例如，现在再插入actor_id 为 3 的数据

insert into actor values ('3', 'ED', 'CHASE', '2006-02-15 12:34:33');

结果：

此时表中的数据：actor_id 为 3的数据是没有插入成功的

（二）测试insert ignore into

例如，做同样的事情，现在再插入actor_id 为 3 的数据

insert ignore into  actor values ('3', 'ED', 'CHASE', '2006-02-15 12:34:33');

结果：

insert ignore into actor values (‘3’, ‘ED’, ‘CHASE’, ‘2006-02-15 12:34:33’)

Affected rows: 0

时间: 0s

此时表中的数据：actor_id 为 3的新数据是没有插入的，表中的所有数据没有发生变化。此时并没有报错，所以当我们需要插入一条未知是否已存在的数据时，可以直接使用该语句插入，而不用提前进行校验。

（四）测试 replace into

经过上面的操作，目前的表数据并未发生变化。

此时做同样的事情，现在再插入actor_id 为 3 的数据

  replace into actor values ('3', 'ED', 'CHASE', '2006-02-15 12:34:33');

结果：

replace into actor values (‘3’, ‘ED’, ‘CHASE’, ‘2006-02-15 12:34:33’)

Affected rows: 2

时间: 0.006s

再次查询表中数据，发现actor_id为3 的数据已经被更新。

插入成功。另外说明：之所以Affected rows为2是因为replace返回的数值是由新行和旧行的变化一起决定的，在本例子中旧行消失了，增加了一条新行，所以返回值是2。当我们插入一条主键不重复的数据时，replace返回的值为1 ，因为只增加了一个新行。例子如下：

replace into actor values ('4', 'AD', 'JASON', '2006-02-15 12:34:33');

Affected rows: 1

时间: 0.005s

此时表中的数据如下：

练习：对于表中未设置主键的情况下，三种插入数据的方法，读者可自行尝试。

2  进一步提问：批量插入数据的操作

一条可以，如何实现多条数据的插入呢？

参考MySQL如何快速插入大量数据 - 风中琉璃 - 博客园

😁这里发现该文用到了事务，是较为麻烦，以后再说也行，但是对于专科的孩子，

可以使用一条语句插入多条记录的方案，以及讲解一下如何使用java来实现。

一、背景

在学习索引优化或者大数据分页的时候，需要在一张表内插入百万级的数据甚至更高，手动一条条的插入肯定是不可能，既麻烦又耗时

一开时最容易想到的是通过存储过程，可以循环的帮我们插入

#1. 准备表 create table s1( id int, name varchar(20), gender char(6), email varchar(50) ); #2. 创建存储过程，实现批量插入记录 delimiter $$ #声明存储过程的结束符号为$$ create procedure auto_insert1() BEGIN    declare i int default 1;    while(i<3000000)do        insert into s1 values(i,'bob','male',concat('bob',i,'@163.com'));        set i=i+1;    end while; END$$ #$$结束 delimiter ; #重新声明分号为结束符号 #3. 查看存储过程 show create procedure auto_insert1\G #4. 调用存储过程 call auto_insert1();

上述这种方法虽然省事，但是效率很低，，，插入300万插到天昏地暗

二、解决方案

对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有就是数据入库时间长。特别像报表系统，可能每天花费在数据导入上的时间就会长达几个小时之久。因此，优化数据库插入性能是很有意义的。

对于Myisam类型的表来说，貌似可以使用LOAD DATA的方式批量插入

对于InnoDB来说，如何优化insert语句的插入效率呢

1.一条sql语句插入多条记录

上面那种方法就是一条sql语句插入一条记录

insert into s1 values(1,'bob','male','bob@163.com');

现在改成一条sql，插入多条数据

insert into s1 values     (1,'bob','male','bob@163.com')，     (2,'tony','tony','bob@163.com')；

分析

这里第二种SQL执行效率高的主要原因是合并后日志量（MySQL的binlog和innodb的事务日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。

2.在事务中插入处理

START TRANSACTION;

insert into s1 values(1,'bob','male','bob@163.com');

insert into s1 values(1,'bob','male','bob@163.com');

COMMIT;

数据对比

分析

这是因为进行一个INSERT操作时，mysql内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作

3.数据有序插入

数据有序的插入是指插入记录在主键上是有序排列，例如id是记录的主键

#原始数据

insert into s1 values(2,'bob','male','bob@163.com');

insert into s1 values(1,'bob','male','bob@163.com');

insert into s1 values(3,'bob','male','bob@163.com');

修改后

insert into s1 values(1,'bob','male','bob@163.com');

insert into s1 values(2,'bob','male','bob@163.com');

insert into s1 values(3,'bob','male','bob@163.com');

数据对比

分析

由于数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本。我们可以参照innodb使用的B+Tree 索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。

同时使用上述三种方法

从测试结果可以看到，合并数据+事务的方法在较小数据量时，性能提高是很明显的，数据量较大时（1千万以上），性能会急剧下降，这是由于此时数据量超过了innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快。而使用合并数据+事务+有序数据的方式在数据量达到千万级以上表现依旧是良好，在数据量较大时，有序数据索引定位较为方便，不需要频繁对磁盘进行读写操作，所以可以维持较高的性能。

注意事项

1.SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packet配置可以修改，默认是1M，测试时修改为8M。

方法1：

可以编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。

max_allowed_packet = 20M

如果找不到my.cnf可以通过，寻找

mysql --help | grep my.cnf

方法2：

在mysql中输入

set global max_allowed_packet = 2*1024*1024*10

然后重启，查看

show VARIABLES like '%max_allowed_packet%';

2.事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交

所以我最后的插入数据语句改成了

创建表

创建存储过程

调用、删除存储过程

3  学会查资料-随机产生数据&插入

用到了存储过程以及函数

如何在mysql插入50万条测试数据：利用存储过程SQL编程实现_计忆芳华的博客-CSDN博客_mysql 50万数据匹配

MySQL随机数据填充工具 mysql_random_data_load - 腾讯云开发者社区-腾讯云

4  数据修改

 此处的多表关联更新比较重要。

5、数据删除

MySQL——关于删除数据的几个问题_七海健人的博客-CSDN博客

具体来说包括：

（1）MySQL如何删除一行数据？

（2）为什么删除数据后表文件大小不变？

（3）为什么建议逻辑删除数据而非物理删除？为什么建议自增主键？

（4）为什么建议删除数据的语句条件上加索引？

（5）如何删除大量数据？

（6）truncate与delete的区别？