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 字段的值改为 valueORDER 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;