数据的插入,删除,更新(DML)

准备工作

本集中要唠叨的是对表中数据的操作，首先需要确定用哪个表来演示这些操作，本着勤俭节约的精神，我们还是复用之前用过的 first_table 表，只不过这个表快被玩坏了，我们把它删掉重建一个，一切重新开始：

mysql> DROP TABLE first_table;
Query OK, 0 rows affected (0.02 sec)

mysql>

mysql> CREATE TABLE first_table (
    ->     first_column INT,
    ->     second_column VARCHAR(100)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>

对于 first_table 表来说，我们保留了两个列，一个是 INT 类型的 first_column 列，另一个是 VARCHAR(100)类型的 second_column 列。

插入数据

在关系型数据库中，数据一般都是以记录(或者说行)为单位被插入到表中的，具体的插入形式且看我们慢慢道来。

插入完整的数据

在插入完整的一条记录时，需要我们指定要插入表的名称和该条记录中全部列的具体数据，完整的语法是这样：

INSERT INTO 表名 VALUES(列1的值，列2的值, ..., 列n的值);

可以看到执行结果是 Query OK, 1 row affected (0.01 sec)，表明成功的插入了一行。然后再用 SELECT 语句看看表中的数据：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
+--------------+---------------+
1 row in set (0.00 sec)

mysql>

现在的 first_table 中就有了一条记录了。在使用这种插入一条完整记录的语法时必须注意，VALUES 语句中必须给出表中所有列的值，缺一个都不行，如果我们不知道向某个列填什么值，可以使用填入 NULL（前提是该列没有声明 NOT NULL 属性），就像这样：

mysql> INSERT INTO first_table VALUES(2, NULL);
Query OK, 1 row affected (0.01 sec)

mysql>

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
+--------------+---------------+
2 rows in set (0.00 sec)

mysql>

上述的这种插入方式 VALUE 列表中参数的顺序与表中各个列的顺序是一一对应的，其实我们也可以在书写插入语句时自定义一下列的顺序，就像这样：

mysql> INSERT INTO first_table(first_column, second_column) VALUES (3, 'ccc');
Query OK, 1 row affected (0.00 sec)

mysql>

在这个语句中，我们显式的指定了列的插入顺序是(first_column, second_column)，对应于 VALUES 列表中的值的顺序，也就是说 first_column 与值 3 对应，second_column 与值'ccc'对应。之后即使 first_table 表中列的结构改变了，这个语句仍然能继续使用。我们也可以随意指定列的插入顺序，比如这样：

mysql> INSERT INTO first_table(second_column, first_column) VALUES ('ddd', 4);
Query OK, 1 row affected (0.01 sec)

mysql>

我们把 second_column 放在了 first_column 之前，所以 VALUES 列表中的值也需要改变顺序，来看一下插入效果：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|            3 | ccc           |
|            4 | ddd           |
+--------------+---------------+
4 rows in set (0.00 sec)

mysql>

插入记录的一部分

我们在插入记录的时候，某些列的值可以被省略，但是这个列必须满足下边列出的某个条件之一

• 该列允许存储 NULL 值

• 该列有 DEFAULT 属性，给出了默认值

我们定义的 first_table 表中的两个字段都允许存放 NULL 值，所以在插入数据的时候可以省略部分列的值。在 INSERT 语句中没有显式指定的列的值将被设置为 NULL，比如这样写：

mysql> INSERT INTO first_table(first_column) VALUES(5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO first_table(second_column) VALUES('fff');
Query OK, 1 row affected (0.00 sec)

mysql>

第一条插入语句我们只指定了 first_column 列的值是 5，而没有指定 second_column 的值，所以 second_column 的值就是 NULL；第二条插入语句我们只指定了 second_column 的值是'ddd'，而没有指定 first_column 的值，所以 first_column 的值就是 NULL，也表示没有数据～ 看一下现在表中的数据：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|            3 | ccc           |
|            4 | ddd           |
|            5 | NULL          |
|         NULL | fff           |
+--------------+---------------+
6 rows in set (0.00 sec)

mysql>

我们再次强调一下，INSERT 语句中指定的列顺序可以改变，但是一定要和 VALUES 列表中的值一一对应起来。

批量插入记录

每插入一条记录就写一条 INSERT 语句也不是不行，但是对人来说太烦了，而且每插入一条记录就提交一个请求给服务器远没有一次把所有待插入的记录全部提交给服务器效率高，所以 MySQL 为我们提供了批量插入的语句，就是直接在 VALUES 后多加几组值，每组值用小括号()扩起来，各个组之间用逗号分隔就好了，就像这样：

mysql> INSERT INTO first_table(first_column, second_column) VALUES(7, 'ggg'), (8, 'hhh');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

我们在这个 INSERT 语句中插入了(7, 'ggg')、(8, 'hhh')这么两条记录，直接把它们放到 VALUES 后边用逗号分开就好了，我们看一下插入效果：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|            3 | ccc           |
|            4 | ddd           |
|            5 | NULL          |
|         NULL | fff           |
|            7 | ggg           |
|            8 | hhh           |
+--------------+---------------+
8 rows in set (0.00 sec)

mysql>

将某个查询结果插入表中(没有 values)

上边的插入语句都是我们显式的将记录的值放在 VALUES 后边，其实我们也可以将某个查询的结果集作为数据源插入到表中。我们先新建一个 second_table 表：

mysql> CREATE TABLE second_table (
    ->     s VARCHAR(200),
    ->     i INT
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>

这个表有两个列，一个是 VARCHAR 类型的 s 列，另一个是 INT 类型的 i 列。现在这个 second_table 表中是没有数据的，我们想把 first_column 表中的一些数据插入到 second_table 表的话可以这么写：

mysql> INSERT INTO second_table(s, i) SELECT second_column, first_column FROM first_table WHERE first_column < 5;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>

我们可以把这条 我们可以把这条 INSERT 语句分成两部分来理解：

• 先执行查询语句。

SELECT second_column, first_column FROM first_table WHERE first_column < 5;

这条语句的执行结果如下：

+---------------+--------------+
| second_column | first_column |
+---------------+--------------+
| aaa           |            1 |
| NULL          |            2 |
| ccc           |            3 |
| ddd           |            4 |
+---------------+--------------+

• 把查询语句得到的结果集插入到指定的表中。

把第 1 步中的到的结果集中的记录批量插入到 second_table 表中，得到的结果就是：

mysql> SELECT * FROM second_table;
+------+------+
| s    | i    |
+------+------+
| aaa  |    1 |
| NULL |    2 |
| ccc  |    3 |
| ddd  |    4 |
+------+------+
4 rows in set (0.00 sec)

mysql>

在将某个查询的结果集插入到表中时需要注意，INSERT 语句指定的列要和查询列表中的表达式一一对应。比方说上边的 INSERT 语句指定的列是 s, i，对应于查询语句中的 second_column, first_column。

INSERT IGNORE (慎用)

如果插入的记录的主键或者具有 UNIQUE 约束的列或者列组合的值在表中已经存在，那么就忽略此次插入操作，否则就把这条记录插入到表中。

对于一些是主键或者具有 UNIQUE 约束的列或者列组合来说，它们不允许重复值的出现，比如我们把 first_table 的 first_column 列添加一个 UNIQUE 约束：

mysql> ALTER TABLE first_table MODIFY COLUMN first_column INT UNIQUE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

因为 first_column 列有了 UNIQUE 约束，所以如果待插入记录的 first_column 列值与已有的值重复的话就会报错，比如这样：

mysql> INSERT INTO first_table(first_column, second_column) VALUES(1, '哇哈哈');
ERROR 1062 (23000): Duplicate entry '1' for key 'first_column'
mysql>

可是这里有一个问题：我们在插入记录之前又不知道表里边有没有主键或者具有 UNIQUE 约束的列或者列组合重复的记录，所以我们迫切的需要这样的一个功能：对于那些是主键或者具有 UNIQUE 约束的列或者列组合来说，如果表中已存在的记录中没有与待插入记录在这些列或者列组合上重复的值，那么就把待插入记录插到表中，否则忽略此次插入操作。设计 MySQL 的大叔给我们提供了 INSERT IGNORE 的语法来实现这个功能

mysql> INSERT IGNORE INTO first_table(first_column, second_column) VALUES(1, '哇哈哈') ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

我们只是简单的在 INSERT 后边加了个 IGNORE 单词便不再报错了！对于批量插入的情况，INSERT IGNORE 同样适用，比如这样：

mysql> INSERT IGNORE INTO first_table(first_column, second_column) VALUES(1, '哇哈哈'), (9, 'iii');
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 1

mysql>

这个批量插入的语句中我们想插入(1, '哇哈哈')和(9, 'iii')这两条记录，因为 first_column 列值为 1 的记录已经在表中存在，所以这个记录会被忽略，而(9, 'iii')这条记录被插入成功，看插入效果：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|            3 | ccc           |
|            4 | ddd           |
|            5 | NULL          |
|         NULL | fff           |
|            7 | ggg           |
|            8 | hhh           |
|            9 | iii           |
+--------------+---------------+
9 rows in set (0.01 sec)

mysql>

INSERT ON DUPLICATE KEY UPDATE

还有一种 就是选择不忽略 而是更新这条重复的数据，比如这样：

mysql> INSERT INTO first_table (first_column, second_column) VALUES(1, '哇哈哈') ON DUPLICATE KEY UPDATE second_column = '雪碧';
Query OK, 2 rows affected (0.00 sec)

mysql>

这个语句的意思就是，对于要插入的数据(1, '哇哈哈')来说，如果 first_table 表中已经存在 first_column 的列值为 1 的记录（因为 first_column 列具有 UNIQUE 约束），那么就把该记录的 second_column 列更新为'雪碧'，看一下效果：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | 雪碧          |
|            2 | NULL          |
|            3 | ccc           |
|            4 | ddd           |
|            5 | NULL          |
|         NULL | fff           |
|            7 | ggg           |
|            8 | hhh           |
|            9 | iii           |
+--------------+---------------+
9 rows in set (0.00 sec)

mysql>

对于那些是主键或者具有 UNIQUE 约束的列或者列组合来说，如果表中已存在的记录中有与待插入记录在这些列或者列组合上重复的值，我们可以使用 VALUES(列名)的形式来引用待插入记录中对应列的值，比方说下边这个 INSERT 语句：

mysql> INSERT INTO first_table (first_column, second_column) VALUES(1, '哇哈哈') ON DUPLICATE KEY UPDATE second_column = VALUES(second_column);
Query OK, 2 rows affected (0.00 sec)

mysql>

其中的 VALUES(second_column)就代表着待插入记录中 second_column 的值，本例中就是'哇哈哈'。有的同学就呵呵了，我直接写成下边这种形式不好么：

INSERT INTO first_table (first_column, second_column) VALUES(1, '哇哈哈') ON DUPLICATE KEY UPDATE second_column = '哇哈哈';

是的，没有任何问题，但是在批量插入大量记录的时候该咋办呢？此时 VALUES(second_column)就 派上了大用场：

mysql> INSERT INTO first_table (first_column, second_column) VALUES(2, '红牛'), (3, '橙汁儿') ON DUPLICATE KEY UPDATE second_column = VALUES(second_column);
Query OK, 4 rows affected (0.00 sec)
Records: 2  Duplicates: 2  Warnings: 0

mysql>

我们准备批量插入两条记录(2, '红牛')和(3, '橙汁儿')，在遇到重复记录时把该重复记录的 second_column 列更新成待插入记录中 second_column 列的值就好了，所以效果是这样：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | 哇哈哈        |
|            2 | 红牛          |
|            3 | 橙汁儿        |
|            4 | ddd           |
|            5 | NULL          |
|         NULL | fff           |
|            7 | ggg           |
|            8 | hhh           |
|            9 | iii           |
+--------------+---------------+
9 rows in set (0.00 sec)

mysql>

删除数据

如果某些记录我们不想要了，那可以使用下边的语句把它们给删除掉：

DELETE FROM 表名 [WHERE 表达式];

我们把 first_table 中 first_column 的值大于 4 的记录都删掉看看：

mysql> DELETE FROM first_table WHERE first_column > 4;
Query OK, 4 rows affected (0.00 sec)

mysql>

其中的 Query OK, 4 rows affected (0.00 sec)表明成功的删除了 4 条记录，然后看一下删除效果：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | 哇哈哈        |
|            2 | 红牛          |
|            3 | 橙汁儿        |
|            4 | ddd           |
|         NULL | fff           |
+--------------+---------------+
5 rows in set (0.00 sec)

mysql>

first_table 表中 first_column 列大于 4 的记录就都不见了哈～ 当然删除语句的 WHERE 子句是可选的，如果不加 WHERE 子句的话，意味着删除表中所有数据，比如我们想清除 second_table 表中的所有数据，可以这么写：

mysql> DELETE FROM second_table;
Query OK, 4 rows affected (0.01 sec)

mysql>

另外，我们也可以使用 LIMIT 子句来限制想要删除掉的记录数量，使用 ORDER BY 子句来指定符合条件的记录的删除顺序，比方说这样：

mysql> DELETE FROM first_table ORDER BY first_column DESC LIMIT 1;
Query OK, 1 row affected (0.00 sec)

mysql>

上述语句就是想删除掉 first_column 列值最大的那条记录，我们看一下删除后的效果：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | 哇哈哈        |
|            2 | 红牛          |
|            3 | 橙汁儿        |
|         NULL | fff           |
+--------------+---------------+
4 rows in set (0.00 sec)

mysql>

可以看到 first_column 列值最大的那条记录，也就是 first_column 列值为 4 的那条记录已经被删除掉了。

更新数据

我们有时候对于某些记录的某些列的值不满意，需要去修改它们，修改记录的语法就是这样：

UPDATE 表名 SET 列1=值1, 列2=值2, ...,  列n=值n [WHERE 布尔表达式];

我们在 UPDATE 单词后边指定要更新的表，然后把你想更新的列的名称和该列更新后的值写到 SET 单词后边，如果想更新多个列的话，它们之间用逗号,分隔开。如果我们不指定 WHERE 子句，那么表中所有的记录都会被更新，否则的话只有符合 WHERE 子句中的条件的记录才可以被更新。比如我们把 first_table 表中 first_column 的值是 NULL 的记录的 first_column 的值更新为 5，second_column 的值更新为'乳娃娃'，可以这么写：

mysql> UPDATE first_table SET first_column = 5, second_column = '乳娃娃' WHERE first_column IS NULL;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Query OK, 1 row affected (0.01 sec)就表明成功更新了 1 行数据。Rows matched: 1 表示符合 WHERE 条件的记录一共有 1 条，Changed: 1 表示有 1 条记录的内容发生了变化。我们看一下修改后的效果：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | 哇哈哈        |
|            2 | 红牛          |
|            3 | 橙汁儿        |
|            5 | 乳娃娃        |
+--------------+---------------+
4 rows in set (0.00 sec)

mysql>

这里再次强调一下：虽然更新语句的 WHERE 子句是可选的，但是如果不加 WHERE 子句的话将更新表中所有的记录，这是玩火的行为！超级危险！十分危险！请慎重使用。

另外，我们也可以使用 LIMIT 子句来限制想要更新的记录数量，使用 ORDER BY 子句来指定符合条件的记录的更新顺序，比方说这样：

mysql> UPDATE first_table SET second_column='爽歪歪' ORDER BY first_column DESC LIMIT 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

上述语句就是想删更新 first_column 列值最大的那条记录，我们看一下更新后的效果：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | 哇哈哈        |
|            2 | 红牛          |
|            3 | 橙汁儿        |
|            5 | 爽歪歪        |
+--------------+---------------+
4 rows in set (0.00 sec)

mysql>

可以看到 first_column 列值最大的那条记录，也就是 first_column 列值为 5 的那条记录的 second_column 列的值已经被更新为'爽歪歪'了。