MySQL中的DML（数据操作语言）操作

插入

插入单条记录

MySQL 主要使用 INSERT 语句插入数据，基本的语法如下：

INSERT INTO table_name(col1, col2, ...)
VALUES (val1, val2, ...);

其中，table_name 是表名；VALUES子句中提供的值与INSERT INTO中的字段数量必须相同，并且数据类型能够兼容。例如：

INSERT INTO employee(emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email)
VALUES ( 26, '张三', '男', 5, 18, '2019-12-25', 10, 6000, NULL, 'zhangsan@shuguo.com');

以上语句为 employee 表增加了一名新员工。如果 VALUES 值列表与表中的字段顺序完全一致，可以省略字段列表；因此上面的语句也可以简写成：

INSERT INTO employee
VALUES ( 26, '张三', '男', 5, 18, '2019-12-25', 10, 6000, NULL, 'zhangsan@shuguo.com');

如果同时执行了上面的两个插入语句，第二次执行时将会产生主键冲突的错误。因为数据库在插入数据之前将会执行完整性检查，对于违反约束的数据提示错误，而不会生成新的数据。

INSERT INTO employee(emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email)
VALUES ( 27, '李四', '男', 5, 18, '2019-12-25', 0, 6000, NULL, 'lisi@shuguo.com');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`hrdb`.`employee`, CONSTRAINT `fk_emp_job` FOREIGN KEY (`job_id`) REFERENCES `job` (`job_id`))

INSERT INTO employee(emp_id)
VALUES ( 28 );
ERROR 1364 (HY000): Field 'emp_name' doesn't have a default value

MySQL 为 INSERT 语句提供了一个IGNORE选项，可以忽略插入数据时的错误，当然也不会创建新的数据行。例如：

INSERT IGNORE INTO employee
VALUES ( 26, '张三', '男', 5, 18, '2019-12-25', 10, 6000, NULL, 'zhangsan@shuguo.com');
Query OK, 0 rows affected, 1 warning (0.00 sec)

show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1062 | Duplicate entry '26' for key 'employee.PRIMARY' |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

如果某个字段可空或者定义了默认值，插入数据时可以不指定该字段，或者指定 default，表示使用默认值。例如：

CREATE TABLE t(id int NOT NULL DEFAULT 100);
INSERT INTO t VALUES ();
INSERT INTO t VALUES (default);

SELECT * FROM t;
id |
---|
100|
100|

MySQL 还提供了另一种形式的 INSERT 语法，通过SET子句设置字段的值：

INSERT INTO table_name
   SET col1 = val1, 
       col2 = val2, 
       ...;

例如：

INSERT INTO t 
   SET id = 1;

如果表中存在 AUTO_INCREMENT 自增字段，每次执行插入操作之后可以使用 LAST_INSERT_ID() 函数返回最后一条记录的 id。例如：

DROP TABLE IF EXISTS t;
CREATE TABLE t(id int AUTO_INCREMENT NOT NULL PRIMARY KEY, c1 int);

INSERT INTO t(c1) VALUES (123);
SELECT last_insert_id();
last_insert_id()|
----------------|
               1|

插入多条记录

MySQL 中的 INSERT 语句支持一次插入多条记录，只需要在 VALUES 子句后指定多个数据行即可。

INSERT INTO table_name(col1, col2, ...)
VALUES (val11, val12, ...), (val21, val22, ...), ...;

例如以下语句一次新增了 3 名员工：

INSERT INTO employee(emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email)
VALUES ( 27, '李四', '女', 5, 18, current_date, 10, 6000, NULL, 'lisi@shuguo.com'),
       ( 28, '王五', '男', 5, 18, current_date, 10, 6500, NULL, 'wangwu@shuguo.com'),
       ( 29, '赵六', '女', 5, 18, current_date, 10, 6700, NULL, 'zhaoliu@shuguo.com');

理论上来说，可以一次插入任意多行数据，但是每个语句的长度不能超过系统变量 max_allowed_packet 的大小。改变量的值可以使用以下语句查看：

SHOW VARIABLES LIKE 'max_allowed_packet';
Variable_name     |Value  |
------------------|-------|
max_allowed_packet|4194304|

如果有必要，可以使用 SET 命令修改该参数的值。

插入多行数据时，LAST_INSERT_ID() 函数将会返回插入的第一个 AUTO_INCREMENT 值，而不是最后一行数据对应的 id。

插入查询结果

除了手动指定插入的数据之外，MySQL 也支持插入一个 SELECT 语句的查询结果。

INSERT INTO table_name(col1, col2, ...)
SELECT ...;

其中，SELECT 子句可以包含任何操作，例如分组聚合、排序、连接查询、子查询等。

我们先创建一个新表 emp_devp：

CREATE TABLE emp_devp
    ( emp_id    INTEGER NOT NULL PRIMARY KEY
    , emp_name  VARCHAR(50) NOT NULL
    , sex       VARCHAR(10) NOT NULL
    , dept_id   INTEGER NOT NULL
    , manager   INTEGER
    , hire_date DATE NOT NULL
    , job_id    INTEGER NOT NULL
    , salary    NUMERIC(8,2) NOT NULL
    , bonus     NUMERIC(8,2)
    , email     VARCHAR(100) NOT NULL
    ) ;

然后通过一个查询语句，将研发部的员工信息复制到 emp_devp 表中：

INSERT INTO emp_devp(emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email)
SELECT *
  FROM employee
 WHERE dept_id = (SELECT dept_id FROM department WHERE dept_name = '研发部');

SELECT count(*) FROM emp_devp;
count(*)|
--------|
       9|

这种形式的插入语法可以实现表的数据复制，通常用于数据仓库中的 ETL（抽取、转换和加载）或者生成测试数据。

更新

单表更新

MySQL 使用 UPDATE 语句更新表中的数据，基本的语法如下：

UPDATE table_name
   SET col1 = expr1,
       col2 = expr2,
       ...
[WHERE conditions]
[ORDER BY ...]
[LIMIT row_count];

其中，table_name 是表名；SET子句指定了需要更新的列和更新后的值（expr1、expr2 或者 DEFAULT），多个字段使用逗号进行分隔；只有满足 WHERE 条件的数据行才会被更新，如果没有指定条件将会更新表中所有行；如果指定了 ORDER BY 子句，按照顺序更新数据行；如果指定了 LIMIT 子句，最多会更新 row_count 行数据。

例如，以下语句为 emp_devp 表中“关平”增加 10% 的月薪和 2000 奖金：

UPDATE emp_devp
SET salary = salary * 1.1,
    bonus = 2000
WHERE emp_name = '关平';

SELECT emp_name, salary, bonus
FROM emp_devp
WHERE emp_name = '关平';

emp_name|salary |bonus  |
--------|-------|-------|
关平     |7480.00|2000.00|

从查询结果可以看出，“关平”的月薪增加到了 7480，奖金被修改为 2000。

和插入数据一样，更新数据时也会执行约束校验，确保不会产生违反约束的数据。例如，以下更新语句违反了外键约束：

mysql> UPDATE IGNORE employee
    -> SET dept_id = 10
    -> WHERE emp_id = 1;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`hrdb`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`))

由于不存在编号为 10 的部门，所以无法将员工的 dept_id 字段设置为 10。此时，如果在 UPDATE 语句中使用了IGNORE选项，将会忽略执行过程的中错误，当然也不会更新成功。例如：

mysql> UPDATE IGNORE employee
    -> SET dept_id = 10
    -> WHERE emp_id = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                    |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1452 | Cannot add or update a child row: a foreign key constraint fails (`hrdb`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`)) |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果指定了 ORDER BY 子句，MySQL 将会按照指定顺序更新数据。这种方式有一些特殊用途，例如：

DROP TABLE IF EXISTS t;
CREATE TABLE t(id int NOT NULL PRIMARY KEY);
INSERT INTO t VALUES (1),(2),(3);

UPDATE t
SET id = id + 1;
ERROR 1062 (23000): Duplicate entry '2' for key 't.PRIMARY'

由于 id 字段是主键，更新时会产生唯一键冲突。为了避免这个问题，可以将 id 的值按照从大到小的顺序进行更新：

UPDATE t
SET id = id + 1
ORDER BY id DESC;

SELECT * FROM t;
id|
--|
 2|
 3|
 4|

对于多个字段的更新，计算的时候按照从左到右的顺序赋值。例如：

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

其中，col2 的值等于 col1 更新后的数据；也就是说，以上语句的执行结果是 col2 和 col1 的值相同。

跨表更新

连接查询（JOIN ）可以用于从多个表中返回关联的数据。同样，UPDATE JOIN语句可以通过关联其他表中的数据进行更新。

UPDATE table_name t
[INNER JOIN | LEFT JOIN] other_table ON conditions
SET t.col1 = expr1,
    t.col2 = expr2
WHERE conditions
[ORDER BY ...]
[LIMIT row_count];

其中，table_name 是需要更新数据的表名；INNER JOIN或者LEFT JOIN用于连接其他的表；SET子句指定了需要更新的列和更新后的值，表达式 expr1、expr2 等可以引用 other_table 中的字段；只有满足 WHERE 条件的数据行才会被更新，如果没有指定条件将会更新表中所有行；如果指定了 ORDER BY 子句，按照顺序更新数据行；如果指定了 LIMIT 子句，最多会更新 row_count 行数据。

例如，以下语句通过关联 employee 中的数据更新 emp_devp 中的月薪：

UPDATE emp_devp ed
JOIN employee d ON (d.emp_id = ed.emp_id)
SET ed.salary = d.salary;

以上关联更新语句也可以通过一个子查询实现：

UPDATE emp_devp ed
   SET salary = (SELECT e.salary
                 FROM employee e
                 WHERE e.emp_id = ed.emp_id);

以上语句在 SET 子句中使用了一个关联子查询，将 employee 表中开发部门所有员工的月薪更新到 emp_devp 表对应的记录中。

多表更新

MySQL 中的 UPDATE 语句支持同时更新多个表中的数据。例如：

UPDATE emp_devp ed
JOIN employee e ON (ed.emp_id = e.emp_id)
SET ed.salary = e.salary,
    e.bonus = 0;

该语句通过连接操作同时更新了 emp_devp 和 employee 中的数据。

对于多表更新操作，每个匹配的数据行只会更新一次，即使多次匹配连接条件。另外，多表更新语句不支持 ORDER BY 和 LIMIT 子句。

与单表更新不同的是，多表更新语句中多个字段的赋值不是从左至右顺序执行，而是顺序不确定。

删除

单表删除

MySQL 使用 DELETE 语句删除表中的数据，基本的语法如下：

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

其中，DELETE 表示删除数据；table_name 是表名；只有满足 WHERE 条件的数据行才会被删除，如果没有指定条件将会删除表中的全部数据；如果指定了 ORDER BY 子句，按照顺序删除数据行；如果指定了 LIMIT 子句，最多会删除 row_count 行数据。

例如，以下语句删除了 emp_devp 表中姓名为“关平”的员工：

DELETE
FROM emp_devp
WHERE emp_name = '关平';

以下语句使用 ORDER BY 和 LIMIT 子句删除了 emp_devp 表中月薪最高的员工：

DELETE
FROM emp_devp
ORDER BY salary DESC
LIMIT 1;

LIMIT 子句可以实现大数据量的多批次删除。

如果删除了外键约束中的父表数据，可能会导致违反外键约束。例如，以下删除语句违反了外键约束：

DELETE
FROM department 
WHERE dept_id = 1;
ERROR 1452 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ds`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`))

由于员工表中存在部门编号为 1 的数据，所以无法删除部门表中的父记录。此时，如果在 DELETE 语句中使用了IGNORE选项，将会忽略执行过程的中错误，当然也不会删除数据。例如：

mysql> DELETE IGNORE
    -> FROM department
    -> WHERE dept_id = 1;
Query OK, 1 row affected, 1 warning (0.09 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                      |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1451 | Cannot delete or update a parent row: a foreign key constraint fails (`ds`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`)) |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果想要在删除父表数据的同时删除子表中相关的数据，可以使用外键的级联删除（ON DELETE CASCADE）功能。例如：

CREATE TABLE t_parent(id int NOT NULL PRIMARY KEY, c1 int);
INSERT INTO t_parent VALUES (1, 1), (2, 2);

CREATE TABLE t_child(
    id int NOT NULL PRIMARY KEY,
    pid int NOT NULL,
    FOREIGN KEY (pid)
      REFERENCES t_parent (id)
      ON DELETE CASCADE 
      ON UPDATE CASCADE
);
INSERT INTO t_child VALUES (1, 1), (2, 1), (3, 2);

SELECT * FROM t_child;
id|pid|
--|---|
 1|  1|
 2|  1|
 3|  2|

其中，t_child 的 pid 字段引用了 t_parent 字段的主键；外键约束中的 ON DELETE CASCADE 表示删除父表记录时级联删除子表中的数据，ON UPDATE CASCADE 表示更新父表记录时级联更新子表中的数据。以下语句演示了级联删除的效果：

DELETE 
FROM t_parent
WHERE id = 1;

SELECT * FROM t_child;
id|pid|
--|---|
 3|  2|

删除 t_parent 中 id 等于 1 的记录之后，t_child 中相应的记录也被删除。

如果 DELETE 语句中没有指定 WHERE 条件，将会删除表中的全部数据。此时，我们可以使用TRUNCATE语句快速删除所有数据：

TRUNCATE [TABLE] table_name;

例如，以下语句删除了 emp_devp 中的所有数据：

TRUNCATE TABLE emp_devp;

TRUNCATE 语句相当于 DROP TABLE 加上 CREATE TABLE。虽然 TRUNCATE 和 DELETE 的逻辑效果类似，但是它属于 DDL 语句，而且它们之间存在一些区别：

TRUNCATE 删除并重建表，通常比 DELETE 快很多，尤其对于大表而言；
TRUNCATE 会进行隐式提交，所以无法回滚；
TRUNCATE 语句需要获取表上的独占锁；
如果存在外键约束，父表无法使用 TRUNCATE 语句；除非是自引用的外键约束；
TRUNCATE 语句通常返回“0 rows affected”，不代表被删除的数据行数；
TRUNCATE 语句会将表中的 AUTO_INCREMENT 重置为初始值；
TRUNCATE 语句不会触发 ON DELETE 触发器。

跨表删除

与 UPDATE JOIN 语句实现跨表更新类似，DELETE JOIN语句也可以通过关联其他表中的数据进行删除操作。

DELETE table_name
FROM table_name
[INNER JOIN | LEFT JOIN] other_table ON conditions
WHERE conditions;

DELETE
FROM table_name
USING table_name
[INNER JOIN | LEFT JOIN] other_table ON conditions
WHERE conditions;

其中，table_name 是需要删除数据的表名；第一种语法只删除 DELETE 后面表中的数据，第二种语法只删除 FROM 后面表中的数据；INNER JOIN或者LEFT JOIN用于连接其他的表；跨表删除不支持 ORDER BY 和 LIMIT 子句。

例如，以下语句通过关联 employee 中的数据删除 emp_devp 中的数据：

DELETE ed
FROM emp_devp ed
LEFT JOIN employee e ON (e.emp_id = ed.emp_id)
WHERE e.emp_id IS NULL;

DELETE
FROM ed
USING emp_devp ed
LEFT JOIN employee e ON (e.emp_id = ed.emp_id)
WHERE e.emp_id IS NULL;

该语句可以用于删除 emp_devp 中不属于 employee 表的数据。以上关联删除语句也可以通过一个子查询实现：

DELETE
FROM emp_devp
WHERE emp_id NOT IN (SELECT emp_id FROM employee);

多表删除

MySQL 中的DELETE JOIN语句也可以用于同时删除多个表中的数据。

DELETE t1, t2
FROM t1
[INNER JOIN | LEFT JOIN] t2 ON conditions
[INNER JOIN | LEFT JOIN] other_table ON conditions
WHERE conditions;

DELETE
FROM t1, t2
USING t1
[INNER JOIN | LEFT JOIN] t2 ON conditions
[INNER JOIN | LEFT JOIN] other_table ON conditions
WHERE conditions;

与上面的跨表更新类似，第一种语法只删除 DELETE 后面表中的数据，第二种语法只删除 FROM 后面表中的数据；连接其他的表 other_table 是可选项，也可以连接多个表；多表删除也不支持 ORDER BY 和 LIMIT 子句。

例如：

DELETE
FROM p, c
USING t_parent p
INNER JOIN t_child c ON (c.pid = p.id)
WHERE p.c1 = 2;

该语句通过连接操作同时删除了 t_parent 和 t_child 中的数据。

合并数据

数据合并操作在 SQL 标准中使用 MERGE 语句实现，MySQL 使用专有的语法，有些数据库使用 UPSERT 语句。

INSERT ON DUPLICATE KEY

MySQL 中的 INSERT 语句提供了一个额外的子句：ON DUPLICATE KEY UPDATE，可以用于同时实现插入和更新操作。当插入的数据违反主键或者唯一约束时，执行更新操作替换原表中的数据。

INSERT INTO table_name(col1, col2, ...)
VALUES (val1, val2, ...)
    ON DUPLICATE KEY UPDATE
       col1 = expr1,
       col2 = expr2,
       ...;

INSERT INTO table_name(col1, col2, ...)
SELECT ...
    ON DUPLICATE KEY UPDATE
       col1 = expr1,
       col2 = expr2,
       ...;

其中，ON DUPLICATE KEY UPDATE 表示存在唯一值冲突时更新相应的字段数据；否则插入数据。

首先，创建一个测试表：

DROP TABLE IF EXISTS t;
CREATE TABLE t(id int NOT NULL PRIMARY KEY, val varchar(10));
INSERT INTO t VALUES (1, '一'),(2, '二'),(3, '三');

再次插入一条数据：

INSERT INTO t VALUES (1, '壹');
ERROR 1062 (23000): Duplicate entry '1' for key 't.PRIMARY'

由于 id = 1 已经存在，该语句违反了主键约束。此时，可以使用以下语句实现更新操作：

INSERT INTO t VALUES (1, '壹')
ON DUPLICATE KEY UPDATE
       val = '壹';

id|val|
--|---|
 1|壹  |
 2|二  |
 3|三  |

ON DUPLICATE KEY UPDATE 子句中的赋值表达式可以使用别名引用插入的数据值，例如：

INSERT INTO t VALUES (1, '壹'), (4, '肆') AS new(id, val)
ON DUPLICATE KEY UPDATE
       val = new.val;

SELECT * FROM t;
id|val|
--|---|
 1|壹  |
 2|二  |
 3|三  |
 4|肆  |

对于第二种语法形式，如果使用 UNION 操作指定源数据，需要将其定义为派生表。例如：

-- 错误语法
INSERT INTO t 
(SELECT 1 AS id, '壹' AS v
   UNION ALL
   SELECT 4, '肆')
ON DUPLICATE KEY UPDATE
val = v;

-- 正确语法
INSERT INTO t 
SELECT * FROM
  (SELECT 1 AS id, '壹' AS v
   UNION ALL
   SELECT 4, '肆') AS s
ON DUPLICATE KEY UPDATE
val = v;

如果表中存在多个唯一约束，可能会匹配到多行数据，但只会更新一条数据。因此，应该避免针对这种表使用 ON DUPLICATE KEY UPDATE 子句。

如果表中存在 AUTO_INCREMENT 字段，INSERT ... ON DUPLICATE KEY UPDATE 语句插入或者更新一行数据后，LAST_INSERT_ID() 函数将会返回 AUTO_INCREMENT 值。

REPLACE

MySQL 提供的另一种实现数据合并操作的语句是 REPLACE。REPLACE 语句会尝试插入数据，如果违反了主键或者唯一约束就删除原表中的数据并插入一条新记录。REPLACE 语法和 INSERT 非常类似：

REPLACE INTO table_name(col1, col2, ...)
 VALUES (val1, val2, ...);

REPLACE INTO table_name
    SET col1 = val1,
        col2 = val2,
        ...;

REPLACE INTO table_name(col1, col2, ...)
 SELECT ...;

如果表中不存在主键或者唯一约束，REPLACE 等价于 INSERT。上一节中的示例也可以用 REPLACE 语句改写如下：

REPLACE INTO t(id, val)
VALUES (1, '壹'), (4, '肆');

REPLACE INTO t
SET id = 1,
    val = '壹';

REPLACE INTO t 
SELECT * FROM
  (SELECT 1 AS id, '壹' AS v
   UNION ALL
   SELECT 4, '肆') AS s;

如果表中的主键或者唯一索引包含多个字段，需要所有索引字段的值都相同时 REPLACE 才会更新数据。例如：

CREATE TABLE test (
  id INT NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id, ts)
);
REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');

SELECT * FROM test;
id|data|ts                 |
--|----|-------------------|
 1|Old |2014-08-20 18:47:00|
 1|New |2014-08-20 18:47:42|

第二个 REPLACE 语句没有更新数据，而是插入了一条新的记录。

最后，如果想要实现数据插入，同时在违反唯一约束时不做任何操作，可以使用前面介绍的INSERT IGNORE语句。

参考：

posted @ 2022-02-19 20:03 残城碎梦阅读(123) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

残城碎梦