[MySQL]数据的增加(插入)、修改、删除与计算列

2023-08-06 271

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介： [MySQL]数据的增加(插入)、修改、删除与计算列

1. 插入数据

插入数据，即向已有的表中增加一条记录。

1.1 表的准备

# 使用 atguigudb
USE atguigudb;
# 新建表格
CREATE TABLE IF NOT EXISTS emp1 (
    id INT,
    `name` VARCHAR(15),
    hire_data DATE,
    salary DOUBLE(10, 2)
);
# 查看表的结构
DESC emp1;

1.2 一次添加一条记录

1.2.1 为表的所有字段按默认顺序插入数据

语法：

INSERT INTO 表名
VALUES (value1, value2, ...);

注意：值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

# 向表 emp1 中插入一条记录
INSERT INTO emp1
VALUES (1, 'ZS', '2000-11-11', 3333);
SELECT * FROM emp1;

1.2.2 为表的指定字段插入数据

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

语法：

INSERT INTO 表名(column1 [column2, column3, ...])
VALUES (value1, [value2, value3, ...]);

注意：在 INSERT 子句中列出的列名顺序以及个数可以与定义表格时的顺序和个数不同，但是一旦列出，VALUES中要插入的value值需要与column列一一对应。如果不对应，将无法插入，并且MySQL会产生错误。

INSERT INTO emp1(id, hire_data, `name`, salary)
VALUES (2, '2001-12-12', 'LS', 4444);
SELECT * FROM emp1;

INSERT INTO emp1(id, `name`, salary)
VALUES (3, 'WW', 5555);
SELECT * FROM emp1;

1.3 一次添加多条记录

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式如下：

INSERT INTO 表名
VALUES
(value1 [,value2, ..., valuen]),
(value1 [,value2, ..., valuen]),
......
(value1 [,value2, ..., valuen]);

或者

INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, ..., valuen]),
(value1 [,value2, ..., valuen]),
......
(value1 [,value2, ..., valuen]);

INSERT INTO emp1(id, `name`, salary)
VALUES (4, 'ZL', 6555),
       (5, 'LL', 6767);
SELECT * FROM emp1;

一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中效率更高。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

1.4 将查询结果插入到表中

INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

语法：

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

将查询结果插入到表中，即在 INSERT 语句中加入子查询，不必书写 VALUES 子句，子查询中的字段应与 INSERT 子句中的字段对应。

INSERT INTO emp1(id, `name`, salary, hire_data)
SELECT employee_id, last_name, salary, hire_date
FROM employees
WHERE department_id IN (70, 60);
SELECT * FROM emp1;

注意：被插入数据的表中要添加数据的字段的长度不能低于查询表中查询的字段的长度。如果被插入数据的表中要添加数据的字段的长度低于查询表中查询的字段的长度的话，就有添加不成功的风险。

2. 更新(修改)数据

更新(修改)数据，对表中已有的记录进行修改。

2.1 语法

UPDATE 表名
SET 字段名 = 值, [字段名 = 值, ...]
[WHERE 条件];

使用 WHERE 子句指定需要更新指定数据的记录；如果省略 WHERE 子句，则表中的所有记录的指定数据都将被更新。

2.2 修改指定记录的指定数据

UPDATE emp1
SET hire_data = '2020-12-11'
WHERE id = 5;
SELECT * FROM emp1;

2.3 修改所有记录的指定数据

UPDATE emp1
SET hire_data = '2021-11-11';
# WHERE id = 5;
SELECT * FROM emp1;

2.4 修改一条记录的多个字段

UPDATE emp1
SET hire_data = '2000-01-01', salary = 9999
WHERE id = 1;
SELECT * FROM emp1;

3. 删除数据

3.1 语法

DELETE FROM 表名
[WHERE 条件];

使用 WHERE 子句删除指定的记录；如果没有WHERE子句，DELETE语句将删除表中的所有记录。

3.2 删除指定记录

DELETE FROM emp1
WHERE id > 2;
SELECT * FROM emp1;

3.3 删除所有数据

DELETE FROM emp1;
# WHERE id > 2;
SELECT * FROM emp1;

4. 注意点

在插入、删除、修改数据时，有可能因为约束的影响，导致插入、删除、修改数据失败。
DML操作默认情况下，执行完以后都会自动提交数据。如果希望执行完以后不自动提交数据，则需要使用 SET autocommit = FALSE。

5. MySQL8新特性：计算列

计算列：简单来说就是某一列的值是通过别的列计算得来的。

例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。当a或b列的值发生修改，c列的值也会对应修改。

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。

计算可以提升查询数据时，需要查询出记录的某些列数据的计算结果的效率。使用计算列，不用在每次查询时每条记录都进行一次运算。

定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的值。

CREATE TABLE tb1
(
    id INT,
    a  INT,
    b  INT,
    # 计算列
    c  INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

插入数据

INSERT INTO tb1(a,b) VALUES (100,200);
SELECT * FROM tb1;

更新数据

UPDATE tb1
SET a = 200
WHERE id IS NULL ;
SELECT * FROM tb1;