数据库数据修改(增删改)

Halfup
120 阅读4分钟

增删改

INSERT DELETE UPDATE

插入数据insert

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

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

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

 INSERT INTO first_table VALUES(1, 'aaa');
图片.png

不知道填啥时候插入NULL

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

INSERT INTO first_table VALUES(2, NULL);
图片.png

自定义顺序

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

INSERT INTO first_table(second_column, first_column) VALUES ('ddd', 4);

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

图片.png

默认省略插入

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

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

我们定义的first_table表中的两个字段都允许存放NULL值，所以在插入数据的时候可以省略部分列的值。

在INSERT语句中没有显式指定的列的值将被设置为NULL，比如这样写：

 INSERT INTO first_table(first_column) VALUES(5);
图片.png

where查询的结果插入

INSERT INTO second_table(s, i) 
SELECT second_column, first_column FROM first_table WHERE first_column < 5;

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

  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表中，得到的结果就是：

图片.png

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

INSERT IGNORE

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

ALTER TABLE first_table MODIFY COLUMN first_column INT UNIQUE;

我们需要这个功能：
对于那些是主键或者具有UNIQUE约束的列或者列组合来说，如果表中已存在的记录中没有与待插入记录在这些列或者列组合上重复的值，那么就把待插入记录插到表中，否则忽略此次插入操作。

INSERT IGNORE的语法来实现这个功能：

INSERT IGNORE INTO first_table(first_column, second_column) VALUES(1, '哇哈哈') ;

图片.png

并没有插入

图片.png

INSERT ON DUPLICATE KEY UPDATE

我们想要的效果是：对于那些是主键或者具有UNIQUE约束的列或者列组合来说，如果表中已存在的记录中没有与待插入记录在这些列或者列组合上重复的值，那么就把待插入记录插到表中，否则按照规定去更新那条重复的记录中某些列的值。

INSERT ... ON DUPLICATE KEY UPDATE ...的语法来实现这个功能：

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

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

图片.png

删除数据 delete

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

DELETE FROM 表名 [WHERE 表达式];

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

 DELETE FROM first_table WHERE first_column > 4;
图片.png

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

DELETE FROM first_table ORDER BY first_column DESC LIMIT 1;

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

图片.png

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

更新数据 update set

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

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

比如我们把first_table表中first_column的值是NULL的记录的first_column的值更新为5，second_column的值更新为'乳娃娃'，可以这么写：

UPDATE first_table SET first_column = 5, second_column = '乳娃娃' WHERE first_column IS NULL;

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

UPDATE first_table SET second_column='爽歪歪' ORDER BY first_column DESC LIMIT 1;

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

图片.png
avatar
文章
阅读
粉丝