您的位置 首页 php

《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, ...), ...;
12  

例如以下语句一次新增了 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(抽取、转换和加载)或者生成测试数据。

对了,在这里说一下,我目前是在职Java开发,如果你现在正在学习Java,了解Java,渴望成为一名合格的Java开发工程师,在入门学习Java的过程当中缺乏基础入门的视频教程,可以关注并私信我:01。获取。我这里有最新的Java基础全套视频教程。

文章来源:智云一二三科技

文章标题:《MySQL 入门教程》第 23 篇 DML 语句之插入数据

文章地址:https://www.zhihuclub.com/78998.shtml

关于作者: 智云科技

热门文章

网站地图