MySQL Insert语句的12种用法

Tags: mysql

本教程通过几个实用和有用的例子介绍MySQL的插入语句。 下面的例子将使用用户名:devuser和密码:mysecretpwd连接到devdb数据库。

# mysql -u devuser -pmysecretpwd devdb
mysql>

在这篇教程中,我们将向员工表(Employee)插入一些数据,下面是员工表的表结构:

mysql> desc employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
| dept   | varchar(10) | YES  |     | NULL    |                |
| salary | int(10)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

如果你第一次接入MySQL,请先学习如何创建MySQL数据库和表结构这篇文章

1. 基础的Insert语句示例

下面的语句向员工表插入一条新记录。在这个例子中,后的“values”指定要插入到表中的所有字段对应的值。

INSERT INTO employee VALUES(100,'Thomas','Sales',5000);

SELECT语句来验证数据是否插入成功。

SELECT * FROM employee;

2. 只针对选定的字段插入值

如果你只想向选定的字段插入值,你需要在INSERT语句中明确指定要插入的字段名字。 下面的代码仅插入id和name列的数据

INSERT INTO employee(id,name) VALUES(200,'Jason');

以上代码中,我们未对dept和salary列指定任何值。所以,以上两个字段的值为NULL,当我们用SELECT语句检索数据时。需要注意的是,NULL不是指"NULL"字符串,SELECT语句使用"NULL"来表示这个字段的值是空的而已。

mysql> SELECT * FROM employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5000 |
| 200 | Jason  | NULL  |   NULL |
+-----+--------+-------+--------+
2 rows in set (0.00 sec)

3. INSERT SET示例

INSERT语句不仅仅可以使用"VALUE"关键字,也可以使用"SET"关键字。下面的例子与上面的例子结果一样,但是使用的是SET关键字。

mysql> INSERT INTO employee SET id=300, name='Mayla';

mysql> select * from employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5000 |
| 200 | Jason  | NULL  |   NULL |
| 300 | Mayla  | NULL  |   NULL |
+-----+--------+-------+--------+

4.从另一个表中检索数据插入

下面的例子中,我们会使用INSERT ... SELECT语句,这个语句可以从其他表中检索数据,并插入到目标表。 下面的代码从Contractor表中检索所有数据并插入到Employee表。

INSERT INTO employee SELECT * FROM contractor;

SELECT语句中可以根据需要使用WHERE子句,用来检索需要的从Contractor表插入到Employee表的数据。

INSERT INTO employee SELECT * FROM contractor WHERE salary >= 7000;

注意:如果你使用oracle数据库,你的SQL应该这样写"INSERT INTO employee AS SELECT * FROM contractor"。需要注意在MySQL中不适用AS关键字。

5.从其他表中选择部分字段插入

当然,你也可以从其他表中选择部分字段的数据插入到你的表中。 下面的例子将从contractor表中选择id,name字段的所有数据插入到employee表。

INSERT INTO employee(id,name) SELECT id,name FROM contractor;

跟前面的例子一样,可以使用WHERE子句过滤数据。

INSERT INTO employee(id,name) SELECT id,name FROM contractor WHERE salary >= 7000;

注意:如果employee表中已存在相同主键(这里的主键是id)的记录,你会得到一条错误信息。下面是一个示例错误信息,指出employee表中已经存在id为100的记录。

ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

6. 插入数据到指定分区

如果创建了分区表(分区类型为range)并希望将输入插入到指定分区,可以参考下面代码。 将数据插入到分区p1中的employee表。

INSERT INTO employee PARTITION (p1) VALUES(100,'Thomas','Sales',5000);

注意:如果该分区的employee表中已包含相应的数据(这里是id=100的employee数据),你会得到如下错误信息。

ERROR 1729 (HY000): Found a row not matching the given partition set

注意: 分区仅在MySQL5.6及以上版本可用。

7. 插入数据到多个分区

可以使用单条SQL语句将数据插入到多个分区。下面的INSERT语句将id为100的记录插入到p1分区,将id为200的记录插入到p2分区。

INSERT INTO employee PARTITION (p1, p2) VALUES(100,'Thomas','Sales',5000), (200,'Jason','Technology',5500);

注意: 在上例中,如果MySQL往任意分区插入数据失败,那么整个INSERT语句将失败。当然,这同样仅能在MySQL5.6及以上版本使用。

8. 在插入过程中忽略错误

在一些场景中(比如:批处理),你可能希望忽略插入过程中MySQL产生的错误信息,你可以使用INSERT IGNORE语句。 比如:下面的例子会抛出错误信息,提示数据已经存在。

mysql> INSERT INTO employee VALUES(100,'Thomas','Sales',5000);
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

要忽略上面的错误信息,可以使用INSERT IGNORE来替换INSERT语句(执行前请确保这条对应的主键已经存在)。

mysql> INSERT IGNORE INTO employee VALUES(100,'Thomas','Sales',5000);
Query OK, 0 rows affected (0.00 sec)

INSERT IGNORE仅仅只是忽略错误信息,不会进行其他任何处理。

9. INSERT语句的默认值

如果MySQL运行在strict模式下,并且在我们的INSERT语句中并未指定默认值,那么MySQL将抛出错误信息。 不过,如果MySQL并未启用strict模式(这个是默认启用的)时,同时INSERT语句没有为字段指定值,那么MySQL会为该字段使用字段类型的默认值。 比如,bonus表的所有字段都不允许为空(not null)。

mysql> DESC bonus;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | NO   |     | NULL    |       |
| amount | int(11) | NO   |     | NULL    |       |
+--------+---------+------+-----+---------+-------+

我们先执行以下语句,插入一条数据(id=100)

INSERT INTO bonus(id) VALUES(100);

查看刚插入的数据,可以看到amount字段被设置为默认值0

SELECT * FROM bonus;
+-----+--------+
| id  | amount |
+-----+--------+
| 100 |      0 |
+-----+--------+

如果,SQL语句中同时不指定id和amount字段的值,那么他们都会被自动设置为默认值0。参考下面例子。

INSERT INTO bonus VALUES();

mysql> select * from bonus;
+-----+--------+
| id  | amount |
+-----+--------+
|   0 |      0 |
+-----+--------+

注意: 你可以在VALUES中使用DEFAULT关键字,效果跟上面例子一样。

INSERT INTO bonus VALUES(DEFAULT,DEFAULT);

对于字符类型字段,默认值是空字符。需要注意的是,如果是自增长(AUTO_INCREMENT)的数字类型字段,那么默认值将是该字段的下一个序列值。

10. VALUES中的表达式

在VALUE子句中,可以使用"+","-","*"或者任何有效的MySQL运算操作符,当然也可以在VALUE子句中使用其他字段的值。 下面的例子中,SQL语句会先取得id字段的值(100)然后将将salary字段的值设为'5000+id',因此salary字段的值为:5100。

mysql> INSERT INTO employee VALUES(50*2,'Thomas','Sales',5000+id);

mysql> select * from employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5100 |
+-----+--------+-------+--------+

11.设定INSERT语句的优先级

对于支持表锁的MySQL存储引擎(例如:MyISAM),我们可以指定INSERT语句的优先级。 例如下面的语句设定INSERT语句为低优先级,INSERT语句将会在没有其他读(read)的情况下执行。

INSERT LOW_PRIORITY INTO employee VALUES(100,'Thomas','Sales',5000);

当然,也可以指定INSERT语句为高优先级。

INSERT HIGH_PRIORITY INTO employee VALUES(100,'Thomas','Sales',5000);

需要注意的是,如果业务数据库是读密集型的数据库并且设定INSERT语句为低优先级(low_priority),那么INSERT可能会很长时间不会被执行。注意: 这个语句与'INSERT ... DELAYED'是有区别的,"INSERT .. DELAYED"在MySQL5.6.6中已经不建议使用。

12. 当数据已存在时,更新列值

在插入数据过程中,如果发现数据已存在(根据主键)数据库将会报错,如下例:

mysql>  INSERT INTO employee VALUES(100,'Thomas','Sales',5000);
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

mysql> select * from employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5000 |
+-----+--------+-------+--------+

然而,在冲突发生时,你可以针对某些特定的已存在的记录执行更新操作,如下例:我们对已存在的记录的salary字段执行更新操作,将它的值增加500.

mysql> INSERT INTO employee VALUES(100,'Thomas','Sales',5000) on DUPLICATE KEY UPDATE salary=salary+500;

mysql> select * from employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5500 |
+-----+--------+-------+--------+

注意: 上例中会提示"2 rows affected",尽管操作只更新了一条数据。


文章来源:12 Essential MySQL Insert Command Examples

翻译:www.4byte.cn

本文链接:http://www.4byte.cn/learning/119996/mysql-insert-yu-ju-de-12-zhong-yong-fa.html