SQL --插入语句

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)