【MySQL】 表的增删操作

目录

1.Create（增）

1.1.单行数据 + 全列插入 

1.2.多行数据 + 指定列插入 

1.3.插入否则更新

1.4.替换数据（REPLACE）

2.Delete（删）

2.1.删除表中的某个条目

2.2.删除整张表数据

2.3.截断表

---

1.Create（增）

在SQL中，INSERT INTO语句用于向数据库表中添加新记录。

INSERT [INTO] table_name 
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...// 解释： value_list: value, [, value] ...

说明：

• SQL中大写的表示关键字，[ ]中代表的是可选项。
• SQL中的每个value_list都表示插入的一条记录，每个value_list都由若干待插入的列值组成。
• SQL中的column列表，用于指定每个value_list中的各个列值应该插入到表中的哪一列。 

1. INSERT [INTO] table_name：INSERT INTO是固定的SQL关键字，用于指示要执行的操作是插入新记录。table_name是您要插入数据的表的名称。在某些数据库系统中，INTO关键字是可选的，但在大多数情况下都会使用。
2. [(column [, column] ...)]：这是一个可选的部分，用于指定要插入数据的列。如果您提供了列名，那么VALUES子句中的值列表必须按照相同的顺序匹配这些列。如果您省略了列名，那么VALUES子句中的值必须按照表中列的顺序提供，并且必须包含所有非空列的值。
3. VALUES (value_list) [, (value_list)] ...：VALUES子句用于指定要插入的值。value_list是一个逗号分隔的值列表，这些值将插入到指定的列中。如果您要插入多行数据，可以在VALUES子句中使用多个value_list，每个value_list用逗号分隔，并且整个列表用括号括起来。每个value_list中的值数量必须与指定的列数相匹配。

注释部分：// 解释： value_list: value, [, value] ...

• 这里的注释说明了value_list的结构。每个value_list由一个或多个值组成，这些值之间用逗号分隔。这些值对应于您要插入的列的值。如果您有多个列要插入值，那么每个value_list中就会有多个值，这些值按照您在INSERT INTO语句中指定的列的顺序排列。

为了方便进行演示，下面创建一个学生表，表当中包含自增长的主键id、学号、姓名和QQ号。如下：

create table students(id int unsigned primary key auto_increment,sn int unsigned not null unique comment '学号',name varchar(20) not null comment '姓名',qq varchar(15) unique comment 'qq号码'
);desc students;

1.1.单行数据 + 全列插入 

当我们在使用insert语句向学生表中插入记录，每次向表中插入一条记录，并且插入记录时不指定column列表，表示按照表中默认的列顺序进行全列插入，因此插入的每条记录中的列值需要按表列顺序依次列出。如下：

insert into students values(100, 1000, '唐玄奘', null);
insert into students values(101, 1023, '孙悟空', null);
select * from students;

1.2.多行数据 + 指定列插入 

使用insert语句也可以一次向表中插入多条记录，插入的多条记录之间使用逗号隔开，并且插入记录时可以只指定某些列进行插入。如下：

insert into students(sn, name) values (123, '曹操'), (187,'刘备');
select * from students;

1.3.插入否则更新

有时候我们在向表中插入新记录时，由于 主键 或者 唯一键 对应的值已经存在而导致我们插入失败。 

desc students;
select * from students;
insert into students (id,sn,name) values (100,1000,'唐僧');

 这时如果我们还想插入我们的数据就可以选择性的进行同步更新操作：

您提供的INSERT ... ON DUPLICATE KEY UPDATE语法描述是正确的。这是MySQL特有的一个扩展语法，用于处理在插入记录时可能发生的键冲突（通常是主键冲突或唯一键冲突）。这个语法允许你在尝试插入一条记录时，如果因为键冲突而失败，则自动更新表中已存在的记录。
下面是该语法的详细解释：

INSERT ... ON DUPLICATE KEY UPDATE column1=value1 [, column2=value2] ...; 

• INSERT ...：这部分与标准的INSERT INTO语句相同，用于指定要插入的表和值。
• ON DUPLICATE KEY UPDATE：这是一个条件子句，它告诉MySQL在发生键冲突时应该执行什么操作。具体来说，如果尝试插入的记录与表中已存在的记录在主键或唯一键上冲突，则不执行插入操作，而是执行UPDATE子句指定的更新操作。
• column1=value1 [, column2=value2] ...：这部分指定了在发生键冲突时需要更新的列和对应的值。你可以指定一个或多个列进行更新，每个列和值的对之间用逗号分隔。

这个语法的作用如下：

1. 如果表中没有冲突数据：则直接插入指定的记录。
2. 如果表中有冲突数据：则不插入新记录，而是将表中已存在的、与插入记录冲突的记录的指定列更新为新的值。

需要注意的是，这个语法依赖于表中的主键或唯一键约束。如果没有这些约束，MySQL将无法检测到冲突，因此也就无法执行更新操作。此外，这个语法是MySQL特有的，不是SQL标准的一部分，因此在其他数据库管理系统中可能无法使用。

        举个例子，假设我们有一个名为students的表，包含id（主键）、name和score三列。我们可以使用以下语句来插入新记录，或者在发生冲突时更新已有记录的score列：

INSERT INTO students (id, name, score)  
VALUES (1, 'Alice', 90)  
ON DUPLICATE KEY UPDATE score=90; 

在这个例子中，如果id为1的记录已经存在，那么它的score列将被更新为90（尽管这里看起来像是没有变化，但在实际应用中，你可能会根据某些条件来动态地设置这个值）。如果id为1的记录不存在，那么将插入一条新记录。

例如：这次我们继续向学生表中刚才的插入记录。如下：

select * from students;insert into students (id,sn,name) values(100, 1000, '唐僧') on duplicate key update name= '唐僧', sn= 1010;select * from students;

 

 可以看到在冲突的情况下我们也确实更新了数据。

说明：执行插入否则更新的SQL后，可以通过受影响的数据行数来判断本次数据的插入情况：

• 0 rows affected：表中有冲突数据，但冲突数据的值和指定更新的值相同。
• 1 row affected：表中没有冲突数据，数据直接被插入。
• 2 rows affected：表中有冲突数据，并且数据已经被更新。

1.4.替换数据（REPLACE）

REPLACE INTO语句是MySQL特有的一个语法，它用于处理数据插入时的键冲突。

与INSERT ... ON DUPLICATE KEY UPDATE不同，REPLACE INTO在遇到键冲突时不是更新已有记录，而是先删除冲突的记录，然后插入新记录。

下面是REPLACE INTO语句的详细解释：

REPLACE INTO table_name (column1, column2, ...)  VALUES (value1, value2, ...); 

1. REPLACE INTO：这是指示MySQL执行替换操作的关键字。
2. table_name：要插入或替换数据的表的名称。
3. (column1, column2, ...)：要插入或替换数据的列的名称。
4. (value1, value2, ...)：与列名称相对应的值，这些值将被插入或用于替换。

当您执行REPLACE INTO语句时，MySQL会尝试将新记录插入到表中。

• 如果表中已经存在具有相同主键或唯一键值的记录，MySQL会先删除那条记录，然后插入新记录。
• 如果表中不存在冲突的记录，MySQL则直接插入新记录。

需要注意的是，REPLACE INTO可能会导致数据的丢失，因为它会删除冲突的记录。因此，在使用REPLACE INTO之前，请确保您了解这一行为，并且确实希望删除冲突的记录。

        此外，REPLACE INTO语法与INSERT INTO非常相似，只需要将INSERT替换为REPLACE即可。但是，由于REPLACE INTO的潜在破坏性，建议在使用时格外小心。

举个例子，我们使用replace into来替换掉我们的孙悟空

我们可以使用以下语句来插入新记录，或者在发生冲突时替换已有记录：

REPLACE INTO students (sn, name) VALUES (1023, '孙行者'); 

在这个例子中，如果sn为1023的记录已经存在，那么那条记录将被删除，然后插入一条新的记录，其sn为1023，name为'孙行者'。如果sn为1023的记录不存在，那么将插入一条新记录。

执行替换数据的SQL后，也可以通过受影响的数据行数来判断本次数据的插入情况：

• 1 row affected：表中没有冲突数据，数据直接被插入。
• 2 rows affected：表中有冲突数据，冲突数据被删除后重新插入。

2.Delete（删）

删除数据的SQL如下：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]; 

• SQL中大写的表示关键字，[ ]中代表的是可选项。
• 在删除数据之前需要先找到待删除的记录，delete语句中的where、order by和limit就是用来定位数据的。 

1. DELETE FROM table_name：这是删除操作的基本形式，指示要从哪个表中删除记录。table_name是您要删除数据的表的名称。
2. [WHERE ...]：WHERE子句是可选的，但非常重要，因为它用于指定删除哪些记录。如果不使用WHERE子句，表中的所有记录都将被删除，这通常是不希望发生的情况。WHERE子句后面跟着一个条件表达式，只有满足该条件的记录才会被删除。
3. [ORDER BY ...]：ORDER BY子句也是可选的，它用于指定删除记录的顺序。这在某些情况下很有用，比如当您希望按照特定的顺序删除记录时（尽管数据库引擎在删除记录时可能不会严格遵循这个顺序，因为删除操作本身通常不保证顺序）。ORDER BY子句后面跟着一个或多个列名，以及可选的排序方向（ASC升序或DESC降序）。
4. [LIMIT ...]：LIMIT子句同样是可选的，它用于限制删除操作影响的记录数。这对于批量删除记录时避免一次性删除过多记录导致性能问题或事务日志过大非常有用。LIMIT子句后面跟着一个数字，表示要删除的记录的最大数量。

需要注意的是，DELETE语句是一个危险的操作，因为它会永久删除表中的记录（除非您使用了事务，并且在删除后执行了回滚操作）。因此，在执行DELETE语句之前，请务必确保您已经备份了数据，并且确实希望删除这些记录。

此外，DELETE语句通常会触发与表相关联的删除触发器（如果有的话），并且会影响与该表相关联的索引和约束。

举个例子，假设我们有一个名为students的表，包含id、name和score三列。我们可以使用以下语句来删除score小于60的记录：

DELETE FROM students WHERE score < 60;

如果我们想进一步限制删除操作，只删除满足条件的记录中的前10条，我们可以使用LIMIT子句：

 DELETE FROM students WHERE score < 60 LIMIT 10; 

在某些情况下，你可能需要按照特定的顺序删除记录，或者限制删除的记录数量。MySQL 8.0支持在DELETE语句中使用ORDER BY和LIMIT子句。

DELETE FROM students WHERE score < 60 ORDER BY score DESC LIMIT 5; 

上面的语句会删除students表中score字段值小于60的记录中，按score降序排列的前5条记录。

         注意：虽然MySQL允许在DELETE语句中使用ORDER BY，但并非所有数据库管理系统都支持这一用法。此外，ORDER BY在DELETE语句中的行为可能因具体的数据库实现而异，有时可能不会严格按照指定的顺序删除记录。 

2.1.删除表中的某个条目

我们先来准备一张测试表，表中包含一个自增长的主键id和姓名。如下：

create table test( id int primary key auto_increment, name varchar(10));desc test;insert into test(name) values ('A'),('B'),('C');select * from test;

接下来我们要删除B，删除B首先要确定B是存在的！！

select * from test where name = 'B';

好的，B确实存在，这个时候我们才可以删除。

delete from test where name = 'B';
select * from test;

\

2.2.删除整张表数据

删除整表的操作很简单，我们只要不在delete后面加where子句，这样我们就能够删除整个表了

注意：删除整表操作要慎用！

我们先来准备一张测试表，表中包含一个自增长的主键id和姓名。如下：

create table test2( id int primary key auto_increment, name varchar(10));desc test2;insert into test2(name) values ('A'),('B'),('C');select * from test2;

这里我们顺便看一下test2表的主键值的相关信息，方便我们解释下面的截断表问题：

show create table test2 \G

 

这个4就是下次的增长值。即下次插入数据的id值 

delete from test2;
select * from test2;

这里我们再看一下test2表的自增长值的相关信息

show create table test2 \G

我们发现啊，这个auto_increment的值怎么没变啊！！！ 

我们在插入一些数据进行验证：

insert into test2(name) values ('D'), ('E'), ('F');select * from test2;

 

再次查看主键值的相关信息：

show create table test2 \G

到这里我们也就得出结论了：

 在MySQL中，当你使用DELETE语句删除表中的所有记录时，表的AUTO_INCREMENT计数器不会被重置。这意味着，如果你随后插入一条新记录，该记录将使用被删除记录之后的下一个AUTO_INCREMENT值。

要重置AUTO_INCREMENT值，你可以使用ALTER TABLE语句。

例如，要将AUTO_INCREMENT值重置为1（或者任何你希望的起始值，只要它大于当前表中任何记录的id值，以避免冲突），你可以执行以下语句：

ALTER TABLE test2 AUTO_INCREMENT = 1; 

但是，请注意，在生产环境中重置AUTO_INCREMENT值可能会导致数据完整性问题，特别是如果你有其他依赖于这些AUTO_INCREMENT值的外部系统或数据。因此，在重置之前，请确保你了解这一操作的潜在影响。

        另外，如果你只是想清空表中的数据而不关心AUTO_INCREMENT值，那么使用TRUNCATE TABLE语句可能是一个更好的选择。TRUNCATE TABLE不仅会删除表中的所有记录，还会重置AUTO_INCREMENT值，并且通常比DELETE语句执行得更快，因为它不会逐行删除记录，而是直接释放整个表的数据页。但是，请注意，TRUNCATE TABLE也会删除表上的所有触发器，并且不能用于有外键约束的表（除非先删除或禁用这些约束）。

2.3.截断表

• TRUNCATE 是 SQL 中的一个命令，用于快速删除表中的所有记录，并通常重置表的自增（AUTO_INCREMENT）计数器（如果表中有这样的字段）。
• 与 DELETE 命令不同，TRUNCATE 不会逐行删除数据，而是直接释放表的数据页和索引页，这使得它在处理大表时通常比 DELETE 更快。

以下是 TRUNCATE 命令的一些关键特性和注意事项：

• 快速删除：
• TRUNCATE 通过释放整个表的数据页和索引页来删除数据，而不是像 DELETE 那样逐行删除。这使得 TRUNCATE 在处理包含大量记录的表时非常高效。
• 重置自增计数器：
• 在大多数数据库系统中，TRUNCATE 会将表的自增（AUTO_INCREMENT）计数器重置为初始值（通常是1，除非之前已经设置了其他起始值）。这意味着在 TRUNCATE 之后插入的第一条新记录将获得自增字段的下一个可用值。
• 不激活触发器：
• TRUNCATE 不会激活与表关联的 DELETE 触发器。如果你依赖于触发器来执行某些操作（如记录删除日志），那么 TRUNCATE 可能不是最佳选择。
• 外键约束：
• 如果表被其他表的外键约束所引用，那么通常不能对该表执行 TRUNCATE 操作。在这种情况下，你需要先删除或禁用这些外键约束，或者改用 DELETE 语句来删除记录。
• 权限要求：
• 执行 TRUNCATE 操作通常需要具有对表的 DROP 权限，这通常比对表执行 DELETE 操作所需的权限更高。
• 无法回滚：
• TRUNCATE 操作通常是一个 DDL（数据定义语言）命令，而不是 DML（数据操作语言）命令。因此，它通常不经过事务日志记录（或者记录方式与 DELETE 不同），并且一旦执行，就无法通过事务回滚来恢复数据。这意味着在使用 TRUNCATE 之前，必须确保你真的想要删除所有数据，并且已经做好了备份。
• 表空间：
• 在某些数据库系统中（如 MySQL 的 InnoDB 存储引擎），TRUNCATE 不会立即释放表所占用的物理存储空间，而是将其标记为可重用。要真正释放表空间，可能需要执行额外的操作，如 OPTIMIZE TABLE。然而，在其他数据库系统中（如 PostgreSQL），TRUNCATE 通常会释放表所占用的存储空间。
• 日志记录：
• 由于 TRUNCATE 通常是一个 DDL 命令，它可能会记录在数据库的DDL日志中，而不是DML日志中。这意味着它可能不会像 DELETE 那样详细地记录每行数据的删除操作。

总之，TRUNCATE 是一个强大的工具，可以快速清空表并重置自增计数器，但它也有一些重要的限制和注意事项。在使用之前，请确保你了解这些限制，并且已经做好了适当的备份和计划。如果你需要保留触发器激活、事务回滚能力或精细的日志记录，那么 DELETE 可能是更合适的选择。

截断表的SQL语法如下：

TRUNCATE [TABLE] table_name;

说明：

1. SQL中大写的表示关键字，[ ]中代表的是可选项。
2. truncate只能对整表操作，不能像delete一样针对部分数据操作。
3. truncate实际上不对数据操作，所以比delete更快。
4. truncate在删除数据时不经过真正的事务，所以无法回滚。
5. truncate会重置AUTO_INCREMENT=n字段。

为了演示truncate的效果，我们创建一张测试和原来一样表，表中包含一个自增长的主键id和姓名。如下：

create table for_truncate(id int primary key auto_increment,name varchar(10));insert into for_truncate(name) values ('A'),('B'),('C');select * from for_truncate;

我们查看一下表for_truncate自增长值的相关信息：

show create table for_truncate \G

然后我们就要通过truncate删除这张表了，在truncate语句中只指明要删除数据的表名，这时便会删除整张表的数据，但由于truncate实际不对数据操作，因此执行truncate语句后看到影响行数为0。如下：

truncate for_truncate;

 

再次查看表for_truncate自增长值的相关信息

show create table for_truncate \G

我们发现auto_increment不见了

再向表中插入一些数据，在插入数据时不指明自增长字段的值，这时会发现插入数据对应的自增长id值是重新从1开始增长的。如下：

insert into for_truncate(name) values ('D'), ('E'), ('F');select * from for_truncate;

 

 再次查看表for_truncate自增长值的相关信息

show create table for_truncate \G

注意： truncate在删除数据时不经过真正的事务，无法回滚，所以截断表操作要慎用！