四、mysql 插入、更新、删除

最新推荐文章于 2022-09-15 09:37:19 发布

缘聚则合缘灭则散

最新推荐文章于 2022-09-15 09:37:19 发布

阅读量275

点赞数

分类专栏： mysql 文章标签： mysql插入、更新、删除

原文链接：https://mp.weixin,com

版权

mysql 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

本文转载至微信公众号：我们都是小青蛙

DML （Data Manipulation Language）语句：数据操纵语句,是指对数据库中表记录的操作，主要包括表记录的插入（insert）、更新（update）、删除（delete）和查询（select），是开发人员日常使用最频繁的操作。本文主要介绍插入（insert）、更新（update）、删除（delete）语句，查询语句在后续文章中介绍。

`一、准备工作`

创建first_table表，：
在这里插入图片描述
对于first_table表来说，我们保留了两个列，一个是INT类型的first_column列，另一个是VARCHAR(100)类型的second_column列。

`二、插入数据`

在关系型数据库中，数据一般都是以记录(或者说行)为单位被插入到表中的。
表创建好后，就可以往里插入记录了，插入记录的基本语法如下：
INSERT INTO tablename (field1,field2,……fieldn) VALUES(value1,value2,……valuesn);

`2.1 插入完整的记录`

在插入完整的一条记录时，完整的语法是这样：
INSERT INTO tablename (field1,field2,……fieldn) VALUES(value1,value2,……valuesn);
也可以不用指定字段名称，但是 values 后面的顺序应该和字段的排列顺序一致：
INSERT INTO 表名 VALUES(列1的值，列2的值, ..., 列n的值);
比如first_table里有两个列，分别是first_column和second_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语句看看表中的数据：

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

在这里插入图片描述
值得注意的是，虽然这种数据插入语法很简单，但是并不鼓励使用，因为这种插入语法VALUES列表中的值高度依赖表中列的顺序，也就是说一旦表的结构改变，比方把first_column和second_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列表中的值也需要改变顺序，来看一下插入效果：

在这里插入图片描述

`2.2 插入记录的一部分`

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

该列允许存储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，也表示没有数据～看一下现在表中的数据：

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

`2.3 批量插入记录`

每插入一条记录写一条语句也不是不行，但是对人来说太烦了，而且每插入一行数据提交一个请求给服务器远没有一次把所有插入的数据提交给服务器效率高，所以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后边用逗号分开就好了，我们看一下插入效果：

在这里插入图片描述

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

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

在这里插入图片描述
这个表有两个列，一个是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>

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

先执行查询语句。

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。

`2.5 插入或忽略`

对于一些是主键或者具有唯一性约束的列或者列组合来说，它们不允许重复值的出现，比如我们把first_table的first_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’)这条记录被插入成功，看插入效果：
在这里插入图片描述

`2.6 插入或更新`

对于主键或者有唯一性约束的列或列组合来说，新插入的记录如果和表中已有的记录重复的话，我们可以选择的策略不仅仅是忽略这次插入，我们也可以选择更新这条重复的记录。比如我们想在first_table表中插入一条记录，内容是(1, ‘哇哈哈’)，我们想要的效果是：如果表中没有重复的记录，则插入，如果表中有重复的记录，则按照规定更新这些重复记录中某些列的值。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表中有某些重复的记录，那就把记录的second_column列更新为’雪碧’，看一下效果：

在这里插入图片描述
如果我们在遇到记录后想更新的值就是准备插入的记录中的值，比如这样：

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>

我们想插入的记录的second_column列的值是’哇哈哈’，如果有重复记录后对second_column列想更新的值也是’哇哈哈’，那我们可以用VALUES(列名)的形式来替代显式把值写出来的形式(与上边语句达成一样效果)：

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

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

在这里插入图片描述

`三、删除数据`

如果某些记录我们不想要了，可以删掉的，语法就是这样：

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条记录，然后看一下删除效果：

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

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

mysql>

在 MySQL 中可以一次删除多个表的数据，语法如下：
DELETE t1,t2…tn FROM t1,t2…tn [WHERE CONDITION]
如果 from 后面的表名用别名，则 delete 后面的也要用相应的别名，否则会ᨀ示语法错误。
在下例中，将表 emp 和 dept 中 deptno 为 3 的记录同时都删除：

mysql> select * from emp;41
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| lisa | 2003-02-01 | 200.00 | 2 |
| bjguan | 2004-04-02 | 100.00 | 1 |
| bzshen | 2005-04-01 | 300.00 | 3 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+
5 rows in set (0.00 sec)
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 3 | hr |
| 5 | fin |
+--------+----------+
4 rows in set (0.00 sec)
mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;
Query OK, 2 rows affected (0.04 sec)
mysql>
mysql>
mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| lisa | 2003-02-01 | 200.00 | 2 |
| bjguan | 2004-04-02 | 100.00 | 1 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+
4 rows in set (0.00 sec)
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 5 | fin |42
+--------+----------+
3 rows in set (0.00 sec)

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

清空表 truncate table table_name;

清空表 delete * from table_name;

1.、truncate 是自增列的值会从1开始，而delete是从删除那条记录的ID+1开始

2、truncate是删除所有数据，而delete可以利用where条件语句删除部分数据

`四、更新数据`

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

UPDATE 表名 SET 列1=值1, 列2=值2, ..., 列n=值n [WHERE 布尔表达式];
凡是满足布尔表达式的行都会被更新，你想更新哪些列的值，就把它们写到SET单词后边，如果想更新多个列的话，它们之间用逗号,分隔开。比如我们把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条记录的内容发生了变化。我们看一下修改后的效果：

在这里插入图片描述
在使用更新语句也需要特别特别注意：虽然更新语句的WHERE条件是可选的，但是如果不加WHERE条件的话将更新所有的记录，这是玩火的行为！超级危险！十分危险！请慎重使用。

在 MySQL 中， update 命令可以同时更新多个表中数据，语法如下：
UPDATE t1,t2…tn set t1.field1=expr1,tn.fieldn=exprn [WHERE CONDITION]

mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| lisa | 2003-02-01 | 200.00 | 2 |
| bjguan | 2004-04-02 | 100.00 | 1 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+
4 rows in set (0.00 sec)
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 5 | fin |
+--------+----------+40
3 rows in set (0.00 sec)
mysql> update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where
a.deptno=b.deptno;
Query OK, 3 rows affected (0.04 sec)
Rows matched: 5 Changed: 3 Warnings: 0
mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| lisa | 2003-02-01 | 400.00 | 2 |
| bjguan | 2004-04-02 | 100.00 | 1 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+
4 rows in set (0.01 sec)
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | zzx |
| 2 | lisa |
| 5 | fin |
+--------+----------+
3 rows in set (0.00 sec)

`五、总结`

使用INSERT语句来向表中插入数据，可以使用这两种方式：

不指定需要插入数据的列：

INSERT INTO 表名 VALUES(列1的值，列2的值, …, 列n的值);
这种方式高度依赖表中列的顺序，不推荐使用。

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

INSERT INTO 表名(列1, 列2, …, 列n) VALUES(列1的值，列2的值, …, 列n的值);
推荐使用这种方式进行插入，在表结构允许的情况下还可以只对部分列进行插入。

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

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

插入或忽略

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

插入或更新

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

删除记录的语法很简单：

DELETE FROM 表名 [WHERE 布尔表达式];
更新记录的语法也很简单：

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

缘聚则合缘灭则散

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
四、mysql 插入、更新、删除

准备工作本集中要唠叨的是对表中数据的操作，首先需要确定用哪个表来演示这些操作，本着勤俭节约的精神，我们还是复用之前用过的first_table表，只不过这个表快被玩坏了，我们把它删掉重建一个，一切重新开始：对于first_table表来说，我们保留了两个列，一个是INT类型的first_column列，另一个是VARCHAR(100)类型的second_column列。插入数据在关系型数...
复制链接

扫一扫

专栏目录