数据处理：增删改

之前章节介绍了如何查询数据，本章介绍数据处理里另外三个基本操作：增加、删除、更改数据库数据。

1 增加数据

使用 INSERT INTO 语句向数据表里插入数据。

1.1 VALUES 方法

  • 按照列名默认顺序插入数据

格式

1
2
INSERT INTO 表名
VALUES (val1, val2, ...);

例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 插入数据，默认顺序
INSERT INTO dept
VALUES (1, 'IT', 'Shanghai');

+---------+-----------+----------+
| dept_id | dept_name | location |
+---------+-----------+----------+
|       1 | IT        | Shanghai |
+---------+-----------+----------+
1 row in set (0.00 sec)


-- 插入空值
INSERT INTO dept
VALUES (2, NULL, NULL);

+---------+-----------+----------+
| dept_id | dept_name | location |
+---------+-----------+----------+
|       1 | IT        | Shanghai |
|       2 | NULL      | NULL     |
+---------+-----------+----------+
2 rows in set (0.01 sec)
  • 指定列名插入

格式

1
2
3
INSERT INTO 表名
	(列名1, 列名2)
VALUES (val1, val2);

指定列名进行插入，顺序默认，未插入的列按照创建表时的默认值填充。

例如：

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO dept
	(dept_name, location)
VALUES ('MKT', 'Beijing');

+---------+-----------+----------+
| dept_id | dept_name | location |
+---------+-----------+----------+
|       1 | IT        | Shanghai |
|       2 | NULL      | NULL     |
|       3 | MKT       | Beijing  |  -- dept_id 是创建时的主键，默认安装数字递增
+---------+-----------+----------+
3 rows in set (0.01 sec)
  • 插入多条数据

以上两个方法都可以通过使用 , 分隔的方式插入多条数据。

格式

1
2
3
4
5
INSERT INTO 表名
VALUES (val1, val2, ...),
	   (val1, val2, ...),
	   ...
	   (val1, val2, ...);
1
2
3
4
5
INSERT INTO 表名(列名1, 列名2)
VALUES (val1, val2),
       (val1, val2),
       ...
       (val1, val2);

例如：

1
2
3
4
5
6
7
8
9
INSERT INTO dept
VALUES (4, 'SALE', 'Guangzhou'),
       (5, 'FINA', 'Shanghai'),
       (6, 'MANA', 'Beijing');

INSERT INTO dept(dept_id, dept_name)
VALUES (7, 'IT'),
       (8, 'ACCT'),
       (9, 'LAW');

VALUES 关键字等价于 VALUE 关键字

字符和日期类型数据写在 ' ' 内

1.2 将查询结果增加到表中

使用 INSERT 语句和 SELECT 语句的组合实现先查询后增加。

格式

1
2
3
4
5
6
INSERT INTO 目标表
	(列名1, 列名2, ...)  -- 可选
SELECT *
	列名1, 列名2, ...  -- 或者选择一些列
FROM 被查询的表
WHERE 条件;  -- 可选
  • 不需要 VALUES 子句，但是需要查询结果与 INSERT 目标表列相对应

例如：

1
2
3
4
5
6
INSERT INTO dept
    (dept_name, location)
SELECT dept_name,
       location
FROM dept
WHERE dept_id = 4;

2 更新数据

UPDATE 语句对数据表数据进行修改。

格式

1
2
3
UPDATE 表名
SET 列名1=val1, 列名2=val2, ...
WHERE 条件;  -- 可选
  • 如果需要备份/回滚，提取设置
1
2
3
4
5
SET AUTOCOMMIT = FALSE;

/* UPDATE 语句 */

ROLLBACK;  -- ROLLBACK 后，更新结果被撤回

例如：更新 dept_id = 3 的 dept_name 为 IT

1
2
3
UPDATE dept
SET dept_name = 'IT'
WHERE dept_id = 3;
  • 如果不加入 WHERE 语句的条件，则会对整列进行修改。

【注意】对主键、外键更新时需要先确认是否正确。例如修改后的外键并不存在，则会报错！

  • 更新的表达式也可以使用列名
1
2
3
UPDATE 表名
SET a = a + 100  -- 例如
WHERE id = 1;

3 删除数据

使用 DELETE 语句删除一行数据。

格式

1
2
3
DELETE
FROM 表名
WHERE 条件;  -- 可选
  • 如果不加入条件，则会清空表中数据（表仍然存在）

例如：删除 id 为 10 的数据

1
2
3
DELETE
FROM dept
WHERE dept_id = 10;

【注意】同样地，删除主键和外键都会带来问题，需要先处理关联表之间的联系，再进行删除。

4 计算列

类似与 Excel 表格，由几列计算得到另一列。这样被计算得到的列称为计算列。在 MySQL 8.0 中，支持在 CREATE 和 ALTER 语句中定义计算列。

例如：创建一个新表，定义唯一主键 id ，以及用于计算的 a 和 b 两列。以及计算列 c 其中 c = a + b 即 c 列的数据由 a 列数据与 b 列数据相加得到。

1
2
3
4
5
6
7
8
CREATE TABLE calculate
(
    id INT,
    a  INT,
    b  INT,
    c  INT GENERATED ALWAYS AS (a + b) VIRTUAL,
    PRIMARY KEY (id)
);

插入数据前，需要先创建表。使用计算列的前提是，必须在表的定义中指明哪一列计算列、如何计算得到。可以在创建 CREATE 时指明，也可以在后续修改表 ALTER 时指明。

在表和计算列被定义好后，使用 INSERT 语句插入数据，MySQL 会自动进行计算填充。

1
2
3
4
5
INSERT INTO calculate
    (a, b)
VALUES (1, 2),
       (100, 200),
       (120, 310);

更新数据时，同样会自动更新计算结果

1
2
3
UPDATE calculate
SET a = -100
WHERE id = 3;

但是不能对计算列进行修改数据

1
2
3
4
5
UPDATE calculate
SET c = -100
WHERE id = 3;

ERROR 3105 (HY000): The value specified for generated column 'c' in table 'calculate' is not allowed.