MySQL：CRUD（增删改查）

ZachOn1y

于 2024-08-01 23:21:45 发布

阅读量389

点赞数 13

分类专栏： MySQL 文章标签： mysql 数据库数据库系统后端 sql

本文链接：https://blog.csdn.net/Zachyy/article/details/140859606

版权

MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

MySQL CRUD（增删改查）是数据库操作中的基础且核心的功能，涵盖了数据的创建（Create）、读取（Retrieve）、更新（Update）和删除（Delete）四个基本动作。

插入数据 INSERT INTO

在 MySQL 中可以使用INSERT语句向数据库已有的表中插入一行或者多行元组数据。INSERT语句分别有两种形式，分别为INSERT...VALUES和 INSERT...SET

当使用单条 INSERT 语句插入多行数据的时候，只需要将每行数据用圆括号括起来即可。

max_allowed_packet

在 MySQL 中，用单条 INSERT 语句处理多个插入要比使用多条 INSERT 语句更快。当插入大量数据时，若 INSERT 的占用大于max_allowed_packet时，会发生错误

使用下列语句获取、设置 max_allowed_packet 的值:

#获取 （单位为bytes）
SHOW VARIABLES LIKE 'max_allowed_packet';

#设置
SET GLOBAL max_allowed_packet = Size;

max_allowed_packet 对于 INSERT INTO … SELECT 语句无影响，该语句能添加任意数量的数据。

INSERT INTO … VALUES 语句

语法格式：

INSERT INTO table_name
[column1, column2, ...] VALUES
(value1, value2, ...)

若向表中每个列都插入数据，列名可省略。
value 与 column 一一对应插入
非空的字段必须在插入数据时提供值
每执行一次 INSERT 语句，被[[主键约束#AUTO_INCREMENT|AUTO_INCREMENT]]修饰的字段值就会自动加一

插入默认值

在插入数据时，若需要将默认值插入到字段中，可以使用 DEFAULT 关键字

INSERT INTO table_name 
VALUES ('value1', DEFAULT);

插入日期

向字段插入日期数据时，可以使用 YYYY-MM-DD 的形式，也可以使用
CURRENT _DATE() 函数来显示当前的日期

INSERT INTO table_name
VALUES ("2023-09-30", CURRENT_DATE());

INSERT INTO … SET 语句

语法格式：

INSERT INTO table_name
SET <column1> = <value1>,
	<column2> = <value2>,
	...;

此语句用于直接给表中的某些列指定对应的列值，即要插入的数据的列名在 SET 子句中指定，对于未指定的列，列值会指定为该列的默认值。

INSERT INTO … SELECT 语句

向表中插入数据，不仅可以使用 VALUES 、SET 关键字添加数据，还可以
将SELECT 语句的查询结果，将一个或多个其他表的数据，添加到表中：

INSERT INTO table_name [column_list]
SELECT select_list 
FROM another_table
[WHERE condition];

在 VALUES 子句中使用 SELECT 语句

SELECT 语句也可以在 VALUES 语句中使用，向表中添加其他表的数据：

INSERT INTO table_name
VALUES 
		(SELECT column_lists FROM table1),
		(SELECT column_lists FROM table2),
		...;

INSERT ON DUPLICATE KEY UPDATE 声明

INSERT ON DUPLICATE KEY UPDATE 是 MySQL 对 SQL标准语句 INSERT 的扩展。

向表中插入新记录时，若字段具有 UNIQUE 或 PRIMARY KEY 属性，就会导致索引重复，引发报错。

在 MySQL 中，可以在 SELECT 语句中使用 ON DUPLICATE KEY UPDATE 关键字，它会将现有记录中的数据进行替换。

INSERT INTO column_list
VALUES (value_list)
ON DUPLICATE KEY UPDATE
	c1 = v1,
	c2 = v2,
	...;

语句会首先尝试向表中插入一条新记录，若出现了重复错误，则会按照语句中的“字段-值”对的形式对现有记录进行更新。

MySQL根据执行的操作返回受影响的行数:

如果插入了新行，则受影响的行数为1。
如果更新的是现有行，则受影响的行数为2。
如果使用当前值更新现有行，则受影响的行数为0。

INSERT IGNORE INTO 语句

INSERT IGNORE INTO 语句是 MySQL 对 SQL 标准的拓展。

当使用插入大量数据时，若在运行过程中出现了 Error，MySQL 会立即停止运行并返回 Error，导致没有任何数据被插入。

使用 INSERT IGNORE INTO 语句，在插入数据的过程中，非法的数据会被忽略，而合法的数据会正常插入到表中。

INSERT IGNORE INTO table_name
(column_list) VALUES (value_list);

语句执行后，MySQL 会返回被成功插入的记录和其他被忽略的记录：

-- 示例
1 row(s) affected, 1 warning(s): 1062 Duplicate entry 'john.doe@gmail.com' for key 'email' Records: 2  Duplicates: 1  Warnings: 1

使用 SHOW WARINGS 指令可以查看 warnings 的详细信息。

INSERT IGNORE 与严格模式

在严格模式下，当向表中插入无效（非法）数据时，MySQL会中止 INSERT 语句并返回一个error。如果使用 INSERT IGNORE 语句，MySQL只会发出warning 而不是返回 error，并且会在值插入到表中之前，将插入值调整使之有效（合法）

INSERT LOW_PRIORITY INTO 语句

在 MySQL 中，LOW_PRIORITY 是一种用于指定查询的优先级的关键字。

当在查询中使用 LOW_PRIORITY 关键字时，MySQL 会将该查询的执行优先级设置为较低。

当系统存在更高优先级的查询正在执行时，MySQL 会优先执行高优先级的查询，而将低优先级的查询推迟到系统空闲时再执行。这个特性主要用于减少对系统资源的竞争，以保证高优先级任务的顺利进行。

INSERT LOW_PRIORITY INTO table_name
(column_list) VALUES (value_list);

INSERT 查询被设置为低优先级，如果系统中有更高优先级的查询正在执行，这个 INSERT 查询可能会被推迟执行。

需要注意的是，LOW_PRIORITY 仅仅是一个指示，在实际情况下，MySQL 是否真正推迟执行低优先级的查询也取决于系统的负载和资源状况。

更新数据 UPDATE

在 MySQL 中，可以使用 UPDATE 语句来修改、更新一个或多个表的数据。

UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET 
	column_name1 = value1,
	column_name2 = value2,
	...
[WHERE condition],
[ORDER BY column_list],
[LIMIT ...];

SET 用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字
DEFAULT 表示列值。
WHERE 语句十分重要，若不使用WHERE子句规定修改范围，忽略 WHERE 子句将所有 column_name 字段的值改为 value
ORDER BY 限定表中的行被修改的次序, LIMIT 用于限定被修改的行数
可选项LOW_PRIORITY会延迟执行当前UPDATE语句。 LOW_PRIORITY 对只使用表级锁的存储引擎生效，如 MyISAM，MERGE 和 MEMORY。
可选项 IGNORE 类似于 INSERT IGNORE 语句，非法数据不会更新，只更新有效数据。

UPDATE 结合 REPLACE () 函数

REPLACE()

REPLACE函数可以将记录中的一个字符串替换为新的字符串：

REPLACE(str, old_string, new_string)

REPLACE() 函数用于搜索和替换表中的文本非常方便，例如更新过时的URL、纠正拼写错误等。

REPLACE() 函数并不支持正则表达式，若需要替换正则表达式形式的字符串，需要使用 MySQL 外部库中的user-defined function(UDF)

注意 REPLACE函数与在MySQL中的 REPLACE语句进行区分

REPLACE 在 UPDATE 子句中

借助 REPLACE() 函数在 UPDATE 语句中对字符串进行更新：

UPDATE table_name
SET
	column_name = REPLACE(column_name, old_stirng, new_string);
WHERE condition;

在 REPLACE 中，column_name 不需要加引号，否则会将该字段的内容改为"column_name"，引发数据损失。

在检索需要更新的string时，MySQL进行区分大小写（case sensitive）的匹配模式。

'''纠正表中数据的拼写错误'''
UPDATE products
SET productDescrip = REPLACE(produceDescrip, 'tols', 'tools');

UPDATE SELECT… 语句

使用UPDATE...SELECT语句，将在另一个表中的查询结果用于更新该表中的数据:

UPDATE table1
SET column1 = (
		SELECT column2 FROM table2
		WHERE condition;
)
WHERE condition;

使用 UPDATE...SELECT 可以方便地将一个表的数据与另一个表进行关联，并将符合条件的数据更新到目标表中。
需要确保子查询返回的结果集和目标表的字段类型匹配，且只返回一个列作为更新值。

UPDATE JOIN 语句

跨表更新（Cross-table update）是一种数据操作技术，用于使用一个表的数据来更新另一个表中的数据，跨表更新可能会影响大量数据和性能，因此在进行跨表更新操作时，建议先进行备份并谨慎验证更新条件。

在MySQL中，可以使用 UPDATE JOIN 实现:

UPDATE table1 [,table2]
[INNER | LEFT] JOIN table2 ON table1.column1 = table2.column2
SET table1.column2 = table2.column2
	[,table2.column3 = expr]
WHERE condition;
[LIMIT ...]

使用 UPDATE JOIN 时，需要确保 JOIN 条件准确匹配两个表，并且更新语句设置了正确的列更新规则

删除数据 DELETE

在 MySQL 中 ,使用 DELETE 从单个表中删除数据:

DELETE FROM table_name
WHERE condition
[ORDER BY column1,column2,...]
[LIMIT row_count];

ORDER BY 子句：表中各行将按照子句中指定的顺序进行删除。
WHERE 子句：为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。
LIMIT 子句：用于告知服务器在控制命令被返回到客户端前被删除行的最大值。一般情况下, LIMIT 子句配合 ORDER BY 子句使用
DELETE 语句会返回被删除的行数

DELETE JOIN

在 DELETE 语句中借助 INNER JOIN 和 LEFT JOIN 子句，可以更加灵活地在多个表中删除数据：

DELETE t1 t2 FROM table1 t1
[INNER | LEFT] JOIN table2 t2 ON t1.column1 = t2.column2
WHERE condition;

在 DELETE 和 FROM 之间添加了 t1 和 t2，若忽略了t1，则只会删除table2中的数据；若忽略了t2，则只会删除 table1 中的数据。
DELETE INNER JOIN 会同时删除左表和右表中匹配的数据
使用 LEFT JOIN 的目的是为了删除目标表的数据，并且在关联表中，只删除与目标表关联的数据。这意味着如果关联表中没有对应的数据，它的数据将保留。

ON DELETE CASCADE

MySQL提供了一种更为高效的操作 ON DELETE CASCADE 若有俩个关联表，当父表中的主键被删除时，其关联表中相应的外键也会被自动删除。

ON DELETE CASCADE 只适用于支持外键约束的存储引擎，例如 InnoDB（MyISAM不支持外键约束，所有无法使用该操作）

该操作在创建表时使用：

CREATE TABLE table2(
	column1 dataType PRIMARY KEY,
	column2,
	...
	CONSTRAINT fk_table1_table2
	FOREIGN KEY (column1)
		REFERENCES table1 (column);
		ON DELETE CASCADE
);

ON DELETE CASCADE 语句位于外键约束语句的最后

可以通过如下操作获取受到 ON DELETE CASCADE 作用的表：

USE information_schema;
SELECT table_name
FROM referential_constraints
WHERE
	constraint_schema = 'database_name'
	AND reference_table_name = 'parent_table'
	AND delete_rule = 'CASCADE'

REPLACE

REPLACE 语句是 MySQL 对 SQL 的拓展，该语句与REPLACE()函数不同
它的作用是替换表中的数据或插入新的数据

REPLACE INTO table_name
(column_list) VALUES (value_list);

其原理为：

系统会检查要插入或替换的数据是否存在主键（PRIMARY KEY）或唯一索引（UNIQUE KEY）列的重复值。
如果存在重复值，将执行替换（UPDATE）操作（将重复的值所在行删除，重新在该行所在位置，插入一条新记录）；
如果没有重复值，将直接插入（INSERT）新的数据。
如果要插入的数据在目标表中已存在，系统会根据主键或唯一索引列的值找到要替换的行，并将新的数据逐一替换原有数据。
除了主键或唯一索引列，其他列的值也会被更新为新的值。
如果要插入的数据在目标表中不存在，系统会将数据直接插入为一条新的行，并分配一个新的主键或唯一索引值。

REPLACE 语句会将整行数据替换或插入，而不是仅仅更新特定的列，在REPLACE后，部分列的数据可能为 Null。
因此，在使用 REPLACE 语句时，需要确保目标表的主键或唯一索引能够清晰地标识每一行数据。

要使用REPLACE语句，需要同时拥有对表的 INSERT 和 DELETE 的权利

REPLACE SET…

借助 REPLACE SET 语句对数据进行替换，与 UPDATE 语句不同的是，其没有WHERE子句，若在SET子句中未指定column的值，系统会为其自动指定DEFAULT值：

REPLACE INTO table_name
SET column1 = value1,
	column2 = value2,
	...;

REPLACE SELECT…

类似于 INSERT INTO SELECT ， REPLACE SELECT 语句能够将其他表中的查询结果插入到表中：

REPLACE INTO table1 
(column_list)
SELECT column_list FROM table2
WHERE condition;