目录
3, 插入或更新(on duplicate key update)
1, 插入数据(insert into)
在插入数据时, 字段与值需要一一对应
1-1, 单条数据插入
语法:insert into 表名 (字段1, ..., 字段n) values (值1, ..., 值2);
mysql> insert into dept (deptno, dname, loc) values (50, 'haha', 'hehe');
Query OK, 1 row affected (0.41 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | haha | hehe |
+--------+------------+----------+
5 rows in set (0.00 sec)
1-2, 多条数据插入
语法:insert into 表名 (字段1, ..., 字段n) values (值1, ..., 值n), ..., (值1, ..., 值n);
mysql> insert into dept (deptno, dname, loc)
-> values
-> (60, '1', '2'),
-> (70, '1', '2');
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | haha | hehe |
| 60 | 1 | 2 |
| 70 | 1 | 2 |
+--------+------------+----------+
7 rows in set (0.00 sec)
2, 插入或替换(replace into)
语法:replace into 表名 (字段1, ..., 字段n) values (值1, ..., 值n);
指当插入的数据存在时,先删除该条记录;若插入的数据不存在,则直接插入该条记录
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | fdsf | fdsaf |
| 60 | fdsf | fdsaf |
| 70 | fdsf | fdsaf |
+--------+------------+----------+
7 rows in set (0.00 sec)
# 因为id=70的数据已经存在,所以这里的操作是先删除id=70的记录,再插入新的数据
mysql> replace into dept (deptno, dname, loc) values (70, 'aaa', 'bbb');
Query OK, 2 rows affected (0.17 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | fdsf | fdsaf |
| 60 | fdsf | fdsaf |
| 70 | aaa | bbb |
+--------+------------+----------+
7 rows in set (0.00 sec)
# 因为id=80的数据不存在,所以这里的操作是直接插入id=80的记录
mysql> replace into dept (deptno, dname, loc) values (80, 'aaa', 'bbb');
Query OK, 1 row affected (0.15 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | fdsf | fdsaf |
| 60 | fdsf | fdsaf |
| 70 | aaa | bbb |
| 80 | aaa | bbb |
+--------+------------+----------+
8 rows in set (0.00 sec)
3, 插入或更新(on duplicate key update)
指的是若插入的记录不存在,则直接插入该条数据;若插入的数据存在,则根据指定的字段名称进行更新
语法:insert into 表名 (字段1,...,字段n) values (值1, ..., 值n) on duplicate key update 字段x='xxx', 字段y=‘yyy', 字段z='zzz';
mysql> insert into dept (deptno, dname, loc) values(80, 'fdsaaa', 'fdsasaaaa') on duplicate key update dname='fdabb', loc='bbb';
Query OK, 2 rows affected (0.15 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | fdsf | fdsaf |
| 60 | fdsf | fdsaf |
| 70 | aaa | bbb |
| 80 | fdabb | bbb |
+--------+------------+----------+
8 rows in set (0.00 sec)
mysql>
4, 插入或忽略(insert ignore into)
指的是若插入的数据存在,则忽略该条插入;若不存在则插入该条记录
语法:insert ignore into 表名 (字段1,...,字段n) values (值1, ..., 值n);
mysql> insert ignore into dept (deptno, dname, loc) values (80, 'ccc', 'cccc');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | fdsf | fdsaf |
| 60 | fdsf | fdsaf |
| 70 | aaa | bbb |
| 80 | fdabb | bbb |
+--------+------------+----------+
8 rows in set (0.00 sec)
5, 写入查询结果集
将查询的结果保存到一个表中
语法:insert into 结果表 (字段1, ..., 字段n) select 字段1, ..., 字段n from 表名;
注意:结果表中的字段需要与select中的字段一一对应
mysql> create table result_table(
-> id bigint not null auto_increment,
-> deptno bigint not null,
-> deptno_avg_sal double not null,
-> primary key (id));
Query OK, 0 rows affected (0.39 sec)
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| aaa |
| dept |
| emp |
| result_table |
| salgrade |
+-----------------+
5 rows in set (0.00 sec)
# 将select deptno, avg(sal) from emp group by deptno查询结果保存到表result_table中
mysql> insert into result_table (deptno, deptno_avg_sal) select deptno, avg(sal) from emp group by deptno;
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from result_table;
+----+--------+----------------+
| id | deptno | deptno_avg_sal |
+----+--------+----------------+
| 1 | 20 | 2175 |
| 2 | 30 | 1566.666666666 |
| 3 | 10 | 2916.666666666 |
+----+--------+----------------+
3 rows in set (0.00 sec)