第14章、数据的插入、删除和更新

标签： MySQL是怎样使用的新版

前边介绍了让人眼花缭乱的查询方式，包括简单查询、子查询、连接查询、组合查询以及各种查询细节，可别忘了表里先得有数据之后查询才能有意义啊！之前我们只是简单介绍了数据的插入语句，本章我们将详细唠叨各种对表中数据的操作，包括插入数据、删除数据和更新数据。

准备工作

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

  1. mysql> DROP TABLE first_table;
  2. Query OK, 0 rows affected (0.02 sec)
  4. mysql>
  6. mysql> CREATE TABLE first_table (
  7.     ->     first_column INT,
  8.     ->     second_column VARCHAR(100)
  9.     -> );
  10. Query OK, 0 rows affected (0.03 sec)
  12. mysql>

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

插入数据

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

插入完整的记录

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

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

比如first_table里有两个列，分别是first_column和second_column，如果我们想要插入完整的记录的话，VAULES()中必须依次填入first_column列和second_column列的值，比如这样：

  1. mysql> INSERT INTO first_table VALUES(1, 'aaa');
  2. Query OK, 1 row affected (0.00 sec)
  4. mysql>

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

  1. mysql> SELECT * FROM first_table;
  2. +--------------+---------------+
  3. | first_column | second_column |
  4. +--------------+---------------+
  5. |            1 | aaa           |
  6. +--------------+---------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

  1. mysql> INSERT INTO first_table VALUES(2, NULL);
  2. Query OK, 1 row affected (0.01 sec)
  4. mysql>
  6. mysql> SELECT * FROM first_table;
  7. +--------------+---------------+
  8. | first_column | second_column |
  9. +--------------+---------------+
  10. |            1 | aaa           |
  11. |            2 | NULL          |
  12. +--------------+---------------+
  13. 2 rows in set (0.00 sec)
  15. mysql>

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

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

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

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

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

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

插入记录的一部分

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

  • 该列允许存储NULL值
  • 该列有DEFAULT属性，给出了默认值

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

  1. mysql> INSERT INTO first_table(first_column) VALUES(5);
  2. Query OK, 1 row affected (0.00 sec)
  4. mysql> INSERT INTO first_table(second_column) VALUES('fff');
  5. Query OK, 1 row affected (0.00 sec)
  7. mysql>

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

  1. mysql> SELECT * FROM first_table;
  2. +--------------+---------------+
  3. | first_column | second_column |
  4. +--------------+---------------+
  5. |            1 | aaa           |
  6. |            2 | NULL          |
  7. |            3 | ccc           |
  8. |            4 | ddd           |
  9. |            5 | NULL          |
  10. |         NULL | fff           |
  11. +--------------+---------------+
  12. 6 rows in set (0.00 sec)
  14. mysql>

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

批量插入记录

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

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

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

  1. mysql> SELECT * FROM first_table;
  2. +--------------+---------------+
  3. | first_column | second_column |
  4. +--------------+---------------+
  5. |            1 | aaa           |
  6. |            2 | NULL          |
  7. |            3 | ccc           |
  8. |            4 | ddd           |
  9. |            5 | NULL          |
  10. |         NULL | fff           |
  11. |            7 | ggg           |
  12. |            8 | hhh           |
  13. +--------------+---------------+
  14. 8 rows in set (0.00 sec)
  16. mysql>

将某个查询的结果集插入表中

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

  1. mysql> CREATE TABLE second_table (
  2.     ->     s VARCHAR(200),
  3.     ->     i INT
  4.     -> );
  5. Query OK, 0 rows affected (0.03 sec)
  7. mysql>

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

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

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

  1. 先执行查询语句。

    1. SELECT second_column, first_column FROM first_table WHERE first_column < 5;
  1. 这条语句的结果集是
  3.     +---------------+--------------+
  4.     | second_column | first_column |
  5.     +---------------+--------------+
  6.     | aaa           |            1 |
  7.     | NULL          |            2 |
  8.     | ccc           |            3 |
  9.     | ddd           |            4 |
  10.     +---------------+--------------+

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

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

    1. mysql> SELECT * FROM second_table;
    2. +------+------+
    3. | s    | i    |
    4. +------+------+
    5. | aaa  |    1 |
    6. | NULL |    2 |
    7. | ccc  |    3 |
    8. | ddd  |    4 |
    9. +------+------+
    10. 4 rows in set (0.00 sec)
    12. mysql>

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

INSERT IGNORE

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

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

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

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

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

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

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

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

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

  1. mysql> SELECT * FROM first_table;
  2. +--------------+---------------+
  3. | first_column | second_column |
  4. +--------------+---------------+
  5. |            1 | aaa           |
  6. |            2 | NULL          |
  7. |            3 | ccc           |
  8. |            4 | ddd           |
  9. |            5 | NULL          |
  10. |         NULL | fff           |
  11. |            7 | ggg           |
  12. |            8 | hhh           |
  13. |            9 | iii           |
  14. +--------------+---------------+
  15. 9 rows in set (0.01 sec)
  17. mysql>

INSERT ON DUPLICATE KEY UPDATE

对于主键或者有唯一性约束的列或列组合来说，新插入的记录如果和表中已存在的记录重复的话，我们可以选择的策略不仅仅是忽略该条记录的插入，也可以选择更新这条重复的旧记录。比如我们想在first_table表中插入一条记录，内容是(1, '哇哈哈')，我们想要的效果是：对于那些是主键或者具有UNIQUE约束的列或者列组合来说，如果表中已存在的记录中没有与待插入记录在这些列或者列组合上重复的值，那么就把待插入记录插到表中，否则按照规定去更新那条重复的记录中某些列的值。设计MySQL的大叔给我们提供了INSERT ... ON DUPLICATE KEY UPDATE ...的语法来实现这个功能：

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

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

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

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

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

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

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

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

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

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

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

删除数据

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

  1. DELETE FROM 表名 [WHERE 表达式];

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

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

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

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

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

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

不过在使用删除语句需要特别特别注意：虽然删除语句的WHERE条件是可选的，但是如果不加WHERE条件的话将删除所有的记录，这是玩火的行为！超级危险！十分危险！请慎重使用。

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

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

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

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

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

更新数据

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

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

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

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

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

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

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

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

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

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

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

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