DML 🔥

# DML

DML 需要 commit；

# 准备工作

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

mysql>

1
2
3
4
5
6
7

# 插入数据—INSERT INTO 🔥

# 插入完整记录 ☠️

-- PostgreSQL中使用 BEGIN TRANSACTION; Oracle和DB2种不需要该语句
START TRANSACTION;

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

COMMIT;

1
2
3
4
5
6

列名和表名要一一对应，即值的顺序必须与表当前的列的顺序相同
没有指定的列等同于插入 null 值，插入记录总是插入一行
要插入的值为 null，则输入 null
总的来说不是特别推荐该写法！！！

# 插入指定列 & 批量插入 🔥

-- PostgreSQL中使用 BEGIN TRANSACTION; Oracle和DB2种不需要该语句
START TRANSACTION;

INSERT INTO 表名(
    列名1,列名2, ...
) VALUES(列值1, 列值2, ...),(列值1, 列值2, ...);-- 直接插入多行数据

COMMIT;

1
2
3
4
5
6
7
8

-- Oracle中的多行INSERT
INSERT ALL INTO 表名 VALUES (1, '测试1')
INTO 表名 VALUES (2, '测试2')

1
2
3

# 将某个查询的结果集插入表中—蠕虫复制 🔥

将一张已经存在的表中的数据复制到另一张表中。SELECT 子句可以使用任何 SQL 语法，但 ORDER BY 无效

INSERT INTO 表名1 SELECT * FROM 表名2; -- 复制所有
INSERT INTO 表名1(列1, 列2) SELECT 列1, 列2 FROM 表名2; -- 复制部分列

1
2

先执行 SELECT 查询，然后把查询得到的结果集插入到指定的表中。

# INSERT IGNORE INTO 🔥

对于一些是主键或者具有UNIQUE约束的列或者列组合来说，它们不允许重复值的出现，所以如果待插入记录的列值与已有的值重复的话就会报错，比如这样：

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

1
2
3

可是这里有一个问题：我们在插入记录之前又不知道表里边有没有主键或者具有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>

1
2
3
4

批量插入同样适用！

# ON DUPLICATE KEY UPDATE ...

对于主键或者有唯一性约束的列或列组合来说，新插入的记录如果和表中已存在的记录重复的话，我们可以选择的策略不仅仅是忽略该条记录的插入，也可以选择更新这条重复的旧记录。比如我们想在first_table表中插入一条记录，内容是(1, '哇哈哈')，我们想要的效果是：对于那些是主键或者具有 UNIQUE 约束的列或者列组合来说，如果表中已存在的记录中没有与待插入记录在这些列或者列组合上重复的值，那么就把待插入记录插到表中，否则按照规定去更新那条重复的记录中某些列的值。设计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
2
3
4

这个语句的意思就是，对于要插入的数据(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>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

对于那些是主键或者具有 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>

1
2
3
4

其中的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>

1
2
3
4
5

我们准备批量插入两条记录(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>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 删除数据—DELETE FROM 🔥

DELETE 语句中不能使用 GROUP BY、HAVING，只能使用 WHERE（基本是必须使用！！！），ORDER BY，LIMIT。

我们可以使用LIMIT子句来限制想要删除的记录数量，使用ORDER BY子句来指定符合条件的记录的删除顺序

DELETE FROM 表名 [WHERE 条件];-- 若不加条件，则删除所有记录
DELETE FROM first_table ORDER BY first_column DESC LIMIT 1;

1
2

DELETE每次从表中删除一行，并将该操作作为事务记录在日志中以便回滚，不清空 AUTO_INCREMENT 记录数

# 删除表中所有数据—TRUNCATE 🔥

TRUNCATE [TABLE] 表名;

TRUNCATE直接删除表并重新创建一张相同结构的新表，不能回滚， AUTO_INCREMENT 置为 0，效率比 DELETE 高

其实就是执行了 DROP TABLE 和 CREATE TABLE 语句

# 修改数据—UPDATE…SET 🔥

使用 UPDATE 语句可以将值清空为 NULL(但只限于未设置 NOT NULL 约束的列)。

UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ... [WHERE 条件] -- 不加条件则将表中所有记录修改
UPDATE 表名 SET (列名1,列名2) = (列值1,列值2) [WHERE 条件] -- PostgreSQL和DB2中使用

1
2

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

编辑

上次更新: 2021/01/17, 16:35:51

← DDL—表&列 🔥 DQL 🔥→