Skip to main content

第 23 篇 DML 语句之插入数据

imodouAbout 4 minMySQL

《MySQL 入门教程》第 23 篇 DML 语句之插入数据

数据库的基本操作包括增、删、改、查，在前面的篇章中主要介绍了 SELECT 语句；接下来几篇我们学习一下数据的修改操作，首先是如何插入数据，也就是INSERT语句。

23.1 插入单条记录

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|

23.2 插入多条记录

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。

23.3 插入查询结果

除了手动指定插入的数据之外，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（抽取、转换和加载）或者生成测试数据。

Default footer
Copyright © 2024 imodou