MySQL讲义第6讲——数据更新之 INSERT
文章目录
MySQL 数据库和其它的关系型数据库一样,支持数据的增(插入:insert)、删(删除:delete)、改(更新:update)、查(查询:select)操作。
一、数据准备
创建两张表:部门(dept)和员工(emp),表结构如下:
mysql> desc dept;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| dept_id | int(11) | NO | PRI | NULL | |
| dept_name | char(20) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc emp;
+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| emp_id | int(11) | NO | PRI | NULL | auto_increment |
| emp_name | char(20) | NO | | NULL | |
| gender | char(2) | YES | | 男 | |
| birth | datetime | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
| dept_id | int(11) | YES | MUL | NULL | |
+----------+----------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
二、数据的插入操作
插入数据使用 Insert 命令,Insert 命令有三种用法:
1、插入一条或多条数据记录
语法如下:
-- 下面的命令一次可以插入一条或多条数据记录
INSERT [INTO] 表名 [(列名,...)]
VALUES ({表达式 | DEFAULT}, ...), (...),...
说明:
(1)每条数据记录包含在一对括号中。
(2)如果省略表名后面的列名,则 values 后面的数据必须和表中字段的数量与顺序对应。
(3)使用 DEFAULT 可以把字段的默认值插入表中。
(4)使用一条 INSERT 命令插入多条数据记录时,多条记录之间用逗号分开。
(1)用 insert 命令一次插入一条记录
mysql> insert into dept values(11,'人事部');
Query OK, 1 row affected (0.01 sec)
mysql> insert into dept(dept_id,dept_name) values(12,'财务部');
Query OK, 1 row affected (0.02 sec)
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 11 | 人事部 |
| 12 | 财务部 |
+---------+-----------+
2 rows in set (0.00 sec)
mysql> insert into emp(emp_name,gender,birth,phone,dept_id)
values('Jack',default,'1995-2-3','15937321555',11);
Query OK, 1 row affected (0.01 sec)
mysql> insert into emp values(1101,'Mark','男','1997-12-15','15903732155',11);
Query OK, 1 row affected (0.02 sec)
mysql> insert into emp(emp_name,gender,birth,phone,dept_id)
values('Jack','女',null,'15937321666',12);
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp(emp_name,gender,dept_id)
values('Jack',default,12);
Query OK, 1 row affected (0.00 sec)
mysql> mysql> select * from emp;
+--------+----------+--------+---------------------+-------------+---------+
| emp_id | emp_name | gender | birth | phone | dept_id |
+--------+----------+--------+---------------------+-------------+---------+
| 1 | Jack | 男 | 1995-02-03 00:00:00 | 15937321555 | 11 |
| 1101 | Mark | 男 | 1997-12-15 00:00:00 | 15903732155 | 11 |
| 1102 | Jack | 女 | NULL | 15937321666 | 12 |
| 1103 | Jack | 男 | NULL | NULL | 12 |
+--------+----------+--------+---------------------+-------------+---------+
4 rows in set (0.00 sec)
(2)用 insert 命令一次插入多条记录
mysql> insert into dept values(13,'生产制造部'),(14,'销售部'),(15,'公关部');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from dept;
+---------+-----------------+
| dept_id | dept_name |
+---------+-----------------+
| 11 | 人事部 |
| 12 | 财务部 |
| 13 | 生产制造部 |
| 14 | 销售部 |
| 15 | 公关部 |
+---------+-----------------+
5 rows in set (0.00 sec)
2、使用 set 参数插入数据
格式如下:
INSERT [INTO] 表名
SET col_name={表达式 | DEFAULT}, ...
说明:使用这种形式的 INSERT 语句不能插入多行。
举例:
mysql> insert into dept set dept_id=16,dept_name='信息部';
Query OK, 1 row affected (0.01 sec)
mysql> select * from dept;
+---------+-----------------+
| dept_id | dept_name |
+---------+-----------------+
| 11 | 人事部 |
| 12 | 财务部 |
| 13 | 生产制造部 |
| 14 | 销售部 |
| 15 | 公关部 |
| 16 | 信息部 |
+---------+-----------------+
6 rows in set (0.00 sec)
3、把一个查询的结果插入到数据表中
格式如下:
INSERT [INTO] 表名 [(列名,...)]
SELECT ...
说明:查询命令不能包含 ORDER BY子句,而且INSERT语句的目的表不能出现在查询命令的 FROM 子句中。
举例:
(1)创建一张数据表
表结构如下:
mysql> create table emp001 select emp_id,emp_name,phone from emp where 2=3;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp001;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| emp_id | int(11) | NO | | 0 | |
| emp_name | char(20) | NO | | NULL | |
| phone | char(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from emp001;
Empty set (0.00 sec)
(2)从 emp 表中查询中男性员工的信息插入 emp001 表中
mysql> insert into emp001 select emp_id,emp_name,phone from emp where gender='男';
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from emp001;
+--------+----------+-------------+
| emp_id | emp_name | phone |
+--------+----------+-------------+
| 1 | Jack | 15937321555 |
| 1101 | Mark | 15903732155 |
| 1103 | Jack | NULL |
+--------+----------+-------------+
3 rows in set (0.00 sec)
4、replace into 命令
replace into 命令的格式与 insert into 命令基本相同。使用 insert into 命令插入数据时,如果主键重复,则插入失败,使用replace into 命令插入数据时,如果主键重复,则替换该行的所有数据,相当于将主键这条记录彻底删除,再插入新的记录。也就是说,将所有的字段都更新了。
语法如下:
REPLACE [INTO] 表名 [(列名, ...)]
VALUES (expr, ...),(...),...
REPLACE [INTO] 表名 [(列名, ...)]
SELECT ...
举例:在 dept 表中插入一个新部门:技术部
mysql> select * from dept;
+---------+-----------------+
| dept_id | dept_name |
+---------+-----------------+
| 11 | 人事部 |
| 12 | 财务部 |
| 13 | 生产制造部 |
| 14 | 销售部 |
| 15 | 公关部 |
| 16 | 信息部 |
+---------+-----------------+
6 rows in set (0.00 sec)
mysql> replace into dept values(16,'技术部');
Query OK, 2 rows affected (0.01 sec)
mysql> select * from dept;
+---------+-----------------+
| dept_id | dept_name |
+---------+-----------------+
| 11 | 人事部 |
| 12 | 财务部 |
| 13 | 生产制造部 |
| 14 | 销售部 |
| 15 | 公关部 |
| 16 | 技术部 |
+---------+-----------------+
6 rows in set (0.00 sec)
-- 可以看到,当命令执行成功后,“信息部”被替换为了“技术部”。