阅读 568

MySQL入门系列:数据的插入、删除和更新

标签: MySQL入门


上集回顾

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

准备工作

本集中要唠叨的是对表中数据的操作,首先需要确定用哪个表来演示这些操作,本着勤俭节约的精神,我们还是复用之前用过的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的值);
复制代码

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

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

mysql>
复制代码

可以看到执行结果是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>
复制代码

值得注意的是,虽然这种数据插入语法很简单,但是并不鼓励使用,因为这种插入语法VALUES列表中的值高度依赖表中列的顺序,也就是说一旦表的结构改变,比方把first_columnsecond_column列的顺序换一下,这种插入语句就不适用了。所以我们可以在书写插入语句的时候就把列的顺序规定好,就像这样:

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值,所以在插入数据的时候可以省略部分列的值。没有显式指定的列的值将被设置为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>
复制代码

在使用这种方式插入数据时需要注意,指定的列顺序可以改变,但是一定要和VALUES列表中的值一一对应起来。其实如果把所有列都给出来的话,其实就相当于插入完整的记录了。

批量插入记录

每插入一条记录写一条语句也不是不行,但是对人来说太烦了,而且每插入一行数据提交一个请求给服务器远没有一次把所有插入的数据提交给服务器效率高,所以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>
复制代码

我们在这个插入语句中插入了(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后边,其实我们也可以将某个查询结果作为数据源插入到表中。我们先新建一个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>
复制代码

这条语句可以分成两条语句理解:

  1. 先执行查询语句。

    SELECT second_column, first_column FROM first_table WHERE first_column < 5;
    复制代码

    这条语句的结果集是

    +---------------+--------------+
    | second_column | first_column |
    +---------------+--------------+
    | aaa           |            1 |
    | NULL          |            2 |
    | ccc           |            3 |
    | ddd           |            4 |
    +---------------+--------------+
    复制代码
  2. 把查询语句得到的结果集插入到指定的表中。

    把第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

插入或忽略

对于一些是主键或者具有唯一性约束的列或者列组合来说,它们不允许重复值的出现,比如我们把first_tablefirst_column列添加一个唯一性约束:

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列的值与已有的值重复的话就会报错,比如这样:

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

我们在插入数据的时候又不知道表里有没有重复的记录,所以我们现在想要一个这样的效果:如果表中没有重复的记录,则插入,如果表中有重复的记录,则忽略本次插入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>
复制代码

插入或更新

对于主键或者有唯一性约束的列或列组合来说,新插入的记录如果和表中已有的记录重复的话,我们可以选择的策略不仅仅是忽略这次插入,我们也可以选择更新这条重复的记录。比如我们想在first_table表中插入一条记录,内容是(1, '哇哈哈'),我们想要的效果是:如果表中没有重复的记录,则插入,如果表中有重复的记录,则按照规定更新这些重复记录中某些列的值。MySQL给我们提供了INSERT ... ON DUPLICATE KEY UPDATE ...的语法来实现这个功能:

INSERT INTO first_table (first_column, second_column) VALUES(1, '哇哈哈') ON DUPLICATE KEY UPDATE second_column = '雪碧';
复制代码

这个语句的意思就是,对于要插入的数据(1, '哇哈哈')来说,如果first_table表中有某些重复的记录,那就把记录的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>
复制代码

如果我们在遇到记录后想更新的值就是准备插入的记录中的值,比如这样:

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

我们想插入的记录的second_column列的值是'哇哈哈',如果有重复记录后对second_column列想更新的值也是'哇哈哈',那我们可以用VALUES(列名)的形式来替代显式把值写出来的形式:

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的值,本例中就是'哇哈哈'。这种形式在批量插入或更新中更重要:

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列更新成准备插入的记录中的值就好了,所以效果是这样:

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_tablefirst_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_column大于4的记录就都不见了哈~ 当然删除语句的WHERE子句是可选的,如果不加WHERE子句的话,意味着删除表中所有数据,比如我们想清除second_table表中的所有数据,可以这么写:

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

mysql>
复制代码

不过在使用删除语句需要特别特别注意:记录被删除之后就非常难恢复了,所以使用删除语句时需要十分慎重!另外,虽然删除语句的WHERE条件是可选的,但是如果不加WHERE条件的话将删除所有的记录,这是玩火的行为!超级危险!十分危险!请慎重使用

更新数据

有时候对于某些记录的某些列的值不满意,我们可以去修改它们,我们需要指定更改哪张表中的哪些记录以及具体的修改内容,语法就是这样:

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

凡是满足布尔表达式的行都会被更新,你想更新哪些列的值,就把它们写到SET单词后边,如果想更新多个列的话,它们之间用逗号,分隔开。比如我们把first_table表中first_column的值是NULL的记录的first_column的值更新为5second_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 | 橙汁儿        |
|            4 | ddd           |
|            5 | 乳娃娃        |
+--------------+---------------+
5 rows in set (0.00 sec)

mysql>
复制代码

在使用更新语句也需要特别特别注意:虽然更新语句的WHERE条件是可选的,但是如果不加WHERE条件的话将更新所有的记录,这是玩火的行为!超级危险!十分危险!请慎重使用

总结

  1. 使用INSERT语句来向表中插入数据,可以使用这两种方式:

    • 不指定需要插入数据的列:

      INSERT INTO 表名 VALUES(列1的值,列2的值, ..., 列n的值);
      复制代码

      这种方式高度依赖表中列的顺序,不推荐使用。

    • 显式指定需要插入数据的列

      INSERT INTO 表名(列1, 列2, ..., 列n) VALUES(列1的值,列2的值, ..., 列n的值);
      复制代码

      推荐使用这种方式进行插入,在表结构允许的情况下还可以只对部分列进行插入。

  2. 直接在VALUES后多加几组值,每组值用小括号()扩起来并用逗号分隔就好了就是批量插入语句。

  3. 对于主键或者有唯一性约束的列或列组合来说,新插入的记录如果和表中已有的记录重复的话,我们可以选择下边这两种插入策略:

    • 插入或忽略

      使用INSERT IGNORE语法,如果表中没有重复的记录,则插入,如果表中有重复的记录,则忽略本次插入。

    • 插入或更新

      使用INSERT ... ON DUPLICATE KEY UPDATE ...语法,如果表中没有重复的记录,则插入,如果表中有重复的记录,则按照规定更新这些重复记录中某些列的值。

  4. 删除记录的语法很简单:

    DELETE FROM 表名 [WHERE 布尔表达式];
    复制代码
  5. 更新记录的语法也很简单:

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

小册

本系列专栏都是MySQL入门知识,想看进阶知识可以到小册中查看:《MySQL是怎样运行的:从根儿上理解MySQL》的链接 。小册的内容主要是从小白的角度出发,用比较通俗的语言讲解关于MySQL进阶的一些核心概念,比如记录、索引、页面、表空间、查询优化、事务和锁等,总共的字数大约是三四十万字,配有上百幅原创插图。主要是想降低普通程序员学习MySQL进阶的难度,让学习曲线更平滑一点~

关注下面的标签,发现更多相似文章
评论