【MySQL】数据操作

一、INSERT

1、介绍

• 在MySQL中，INSERT语句用于向表中添加新记录。它是SQL（结构化查询语言）中用于数据插入的基本命令之一。通过INSERT语句，你可以将单行或多行数据插入到指定的表中。

2、语法

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1_1, value2_1, value3_1, ...)[,(value1_2, value2_2, value3_2, ...),...];

3、语法介绍

• table_name：要插入数据的表的名称。
• (column1, column2, column3, …)：希望插入数据的列的名称，列的顺序应与VALUES子句中的值顺序相匹配。
• (value1, value2, value3, …)：与列对应的值。
• 当插入多行数据时，插入的每行数据用逗号分隔。上方语法中[]部分。
• 当插入所有列的数据时，如果列的顺序与表定义中的顺序一致，可以省略列名（column部分可以省略）。但需要为表中的所有列提供值，并且值的顺序必须与表中列的顺序一致。

4、注意事项

• 如果表中有AUTO_INCREMENT列（例如，主键列），可以在INSERT语句中省略该列，MySQL会自动为该列生成一个唯一的值。
• 如果表中有NOT NULL约束的列，并且这些列没有默认值，必须在INSERT语句中为这些列提供值。
• 如果列允许NULL值，并且希望在插入时将这些列设置为NULL，可以在VALUES子句中显式地指定NULL。
• 对于字符串和日期类型的值，应该将它们用单引号括起来。

5、示例

create table students(
id int unsigned primary key auto_increment,
sn int unique key not null comment '学号',
name varchar(20) not null
);insert into students values(1, 1234, '雪龙');
insert into students (sn, name) values(4567, '林黛玉'), (88994, '贾宝玉');select * from students;

二、插入否则更新

1、介绍

• 当想要向表中插入新记录，但如果记录已经存在（基于某些唯一性约束，如主键或唯一索引），则希望更新该记录时，可以使用INSERT … ON DUPLICATE KEY UPDATE语句。这个语句允许在尝试插入新记录时，如果发生主键或唯一索引冲突，则执行更新操作而不是插入。
• ON DUPLICATE KEY UPDATE仅当发生主键或唯一索引冲突时才会触发。即它是基于主键或唯一索引的。如果表中没有主键或唯一索引，则此语句将始终执行插入操作，即使记录已经存在。
• 可以更新多个列，只需在ON DUPLICATE KEY UPDATE子句中为每个要更新的列指定新值。
• 如果不希望更新某些列，可以省略它们。如果尝试更新的列有NOT NULL约束且没有提供非NULL值，则更新操作将失败。
• 如果表中有触发器（Triggers），则ON DUPLICATE KEY UPDATE可能会触发这些触发器。

2、语法

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)
ON DUPLICATE KEY UPDATE
column1 = VALUES(column1),
column2 = VALUES(column2),
...;

3、语法介绍

• ON DUPLICATE KEY UPDATE：当发生主键或唯一索引冲突时要执行的更新操作。
• column1 = VALUES(column1)：将冲突行的列更新为尝试插入的值。VALUES函数用于引用INSERT语句中提供的值。

4、示例

• 上方的前两条语句的执行结果分别是插入、更新操作，最后的语句因为要更新的值和插入的值（重复值）相同，不做修改。

三、ROW_COUNT

1、介绍

• ROW_COUNT是一个函数，用于返回最近执行的 SQL 语句所影响的行数。这个函数在需要获取受影响的行数时非常有用，比如在插入、更新或删除操作后，可以使用它来确认有多少行被影响。
• ROW_COUNT返回的是当前会话中最近一次执行的 DML（数据操作语言）语句所影响的行数。如果在不同的会话中执行语句，那么每个会话的 ROW_COUNT将只反映该会话中的操作。
• 在事务中，ROW_COUNT仍然有效，但在回滚事务后，之前的 ROW_COUNT结果将不再反映实际影响行数。
• 在存储过程和触发器中，ROW_COUNT也可以用来获取受影响的行数，有助于控制逻辑流。

2、示例

• 下方示例是在上方示例执行后执行的。

四、REPLACE

1、介绍

• REPLACE语句与INSERT语句的语法非常相似，但在功能上有所不同。REPLACE语句会尝试将一条新记录插入到表中，但如果表中已经存在具有相同唯一键或主键的记录，则会先删除旧记录，再插入新记录。
• REPLACE语句会改变表中的自增长ID值，并可能影响关联表的数据一致性（如果存在外键约束）。因此，在使用时需要谨慎考虑数据完整性和性能问题。

2、语法

REPLACE INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);REPLACE INTO table_name
SET column1 = value1, column2 = value2, ...;

3、示例

• 第一次为表中没有冲突数据，数据被插入。第二次为表中有冲突数据，删除后重新插入。

五、UPDATE

1、介绍

• UPDATE 语句用于修改已存在的表中的数据。通过使用 UPDATE 语句，可以根据指定的条件（通常是通过 WHERE 子句）来更新一行或多行的数据。如果未使用 WHERE 子句，则会更新表中的所有行，但这通常是不希望发生的情况。
• 在执行大规模更新操作之前，最好备份数据，以防操作错误导致数据丢失。确保 WHERE 子句正确无误，以防止意外更新大量数据。
• 如果涉及多条更新语句，可以考虑使用事务管理，以确保数据的一致性。

2、语法

UPDATE 表名
SET 列1 = 值1, 列2 = 值2, ...
WHERE 条件;

3、示例

• 本次以及下面的示例中，所用的exam_result表以及数据参见【MySQL】基本查询。因为本文与该文章拆分，所以本文示例所用的数据在该文中定义。

将贾政同学的语文成绩变更为80分

update exam_result set chinese=80 where name='贾政';
select name, chinese from exam_result where name = '贾政';

将总成绩倒数前三的 3 位同学的数学成绩加上 30分

select name, chinese+math+english total, math from exam_result order by total limit 3;

update exam_result set math=math+30 order by chinese+math+english limit 3;
select * from exam_result;

六、DELETE

1、介绍

• DELETE语句用于从数据库表中删除记录。在执行删除操作之前，最好先备份数据库，以防误删重要数据。确保WHERE子句正确无误，以防止意外删除大量数据。确保删除操作不会导致数据不一致或违反业务规则。
• 删除大量数据时，应考虑索引的使用和锁定机制的影响。对于大批量删除操作，可以考虑将删除分批进行，每批删除后手动提交事务，避免长时间锁表。

2、语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name [WHERE condition] [ORDER BY ...] [LIMIT row_count];

3、语法介绍

• LOW_PRIORITY：如果指定了LOW_PRIORITY选项，那么DELETE操作会被推迟，直到没有其他客户端正在从该表中读取数据为止。
• QUICK：仅适用于MyISAM存储引擎，删除操作不会合并删除表的索引端节点，从而加快删除速度。
• IGNORE：如果指定了IGNORE选项，那么在遇到错误时（如外键约束冲突），DELETE操作不会中断，而是会发出警告。
• table_name：要删除记录的表的名称。
• WHERE condition：可选的，用来指定应该删除哪些行。如果没有WHERE子句，那么表中的所有行都会被删除。
• ORDER BY …：可选的，用来指定删除行的顺序。
• LIMIT row_count：可选的，用来限制最多删除多少行。

七、TRUNCATE

1、介绍

• TRUNCATE语句是一种用于快速删除表中所有数据的SQL命令。它通过释放表的存储空间来快速删除所有数据，而不会逐行删除数据。这使得TRUNCATE在处理大型表时比DELETE语句更快。
• 使用TRUNCATE语句删除数据后，表结构、索引、约束等都会保持不变。删除的数据无法恢复，因为它不会记录每行的删除操作在事务日志中。因此，在执行TRUNCATE操作之前需要谨慎。
• 如果表中有自增字段，TRUNCATE会将自增字段重置为初始值。

2、语法

TRUNCATE TABLE table_name;

3、注意事项

• 外键约束：如果表有外键引用，TRUNCATE可能会失败，除非删除引用的外键关系。在某些情况下，如果外键约束被定义为级联删除，则TRUNCATE可能会成功删除所有相关表的数据。但通常建议在外键约束存在的情况下谨慎使用TRUNCATE。
• 事务处理：TRUNCATE语句是一个DDL（Data Definition Language）语句，它会自动提交事务，并且无法回滚。

4、与DELETE的比较

• 速度：TRUNCATE通常比DELETE更快，因为它不会记录每行的删除操作在事务日志中。
• 恢复性：TRUNCATE删除的数据无法恢复，而DELETE可以通过日志进行回滚操作恢复数据。
• 触发器：TRUNCATE不会触发DELETE触发器，而DELETE会。
• 自增字段：TRUNCATE会重置自增列的表的标识符，而DELETE不会。

本文到这里就结束了，如有错误或者不清楚的地方欢迎评论或者私信
本文只是在学习过程中所做的总结，不会涉及过深的概念
创作不易，如果觉得博主写得不错，请点赞、收藏加关注支持一下💕💕💕