027、SQL语句之DML语句

INSERT 语句

INSERT 向表插⼊⾏。语法如下：

INSERT INTO table_name (column_list) VALUES (value_list) [,(value_list),…];
column_list 中的列数和 values_list 中的值的数量必须相同
值的顺序必须与语句中列的顺序相匹配
将字符串和时间数据类型（推荐）的列值⽤单引号包裹
数值数据⽆需⽤引号

UPDATE 语句

语法：

UPDATE table_name SET col_name1=expression1[,col_name2=expression2,...][WHERE condition][ORDER BY ...][LIMIT row_count];tidb> SELECT count(*) FROM universe.planets;
+----------+
| count(*) |
+----------+
| 10 |
+----------+tidb> UPDATE universe.planets SET gravity = 0.0;
Query OK, 10 rows affected (0.01 sec)
Rows matched: 10 Changed: 10 Warnings: 0

除了赋值的⼦句部分，UPDATE 的其他字句语法和 SELECT ⾮常类似，本质上是找到已存在的表⾏，然后修改它们。

在 UPDATE ⾥使⽤ LIMIT

和 SELECT 的情况⼀样, 需要注意如何确保返回表⾏的顺序:

tidb> SELECT * FROM test.r1;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | C |
+----+------+
2 rows in set (0.00 sec)tidb> UPDATE test.r1 SET name='Z' WHERE id < 10 LIMIT 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0tidb> SELECT * FROM test.r1;
+----+------+
| id | name |
+----+------+
| 1 | Z |
| 2 | C |
+----+------+
2 rows in set (0.01 sec)

警惕: 本例中的 UPDATE 语句⽆法确定它会修改哪, LIMIT 1 并不能⽤来确定哪个表⾏符合条件。

在 UPDATE ⾥使⽤ ORDER BY 和 LIMIT

tidb> SELECT * FROM test.r1;
+----+------+
| id | name |
+----+------+
| 1 | Z |
| 2 | C |
+----+------+
2 rows in set (0.01 sec)
tidb> UPDATE test.r1 SET name='Z' WHERE id < 10 ORDER BY id DESC
LIMIT 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
tidb> SELECT * FROM test.r1;
+----+------+
| id | name |
+----+------+
| 1 | Z |
| 2 | Z |
+----+------+
2 rows in set (0.00 sec)

就像 SELECT ⼀样，你必须使⽤ ORDER BY 来确保表⾏的顺序。

REPLACE 语句

找到记录的，就是更新，没找到记录的就是插入

在具有主键或唯⼀约束的表中使⽤ REPLACE 语句将现有⾏替换为新的
在没有主键或唯⼀约束的表中，它的作⽤⽅式与 INSERT 语句相同
语句返回的⾏数是删除和插⼊的⾏数的总和
- rows affected 等于 1: 插⼊了⼀⾏，未删除任何⾏
- rows affected ⼤于 1: 在插⼊新⾏之前删除了 1 或多个原有⾏。

tidb> SELECT * FROM test.r1;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
+----+------+
tidb> REPLACE INTO test.r1 (id,name) VALUES (2,'C');
Query OK, 2 rows affected (0.00 sec)
tidb> SELECT * FROM test.r1;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | C |
+----+------+

从语义上看，REPLACE 语句是 DELETE 语句和 INSERT 语句的结合，可⽤于简化应⽤程序代码。

避免重复的⾏记录

INSERT … ON DUPLICATE KEY UPDATE : 避免重复⾏记录错误
类似于 REPLACE, 但它使⽤ UPDATE ⽽不是 DELETE 来处理重复的⾏记录

tidb> select * from test.r1;
+----+------+--------+
| id | name | gender |
+----+------+--------+
| 1 | Z | F |
| 2 | Z | F |
+----+------+--------+
2 rows in set (0.00 sec)
tidb> replace into test.r1 (id, name) values (1, 'A');
Query OK, 2 rows affected (0.00 sec)# 如下语句，可看到主键为2的重复了，所以将主键为2这一行当中name列改成B
tidb> insert into test.r1 (id, name) values (2, 'B') on duplicate
key update name='B';
Query OK, 2 rows affected (0.01 sec)tidb> select * from test.r1;
+----+------+--------+
| id | name | gender |
+----+------+--------+
| 1 | A | NULL |
| 2 | B | F |
+----+------+--------+
2 rows in set (0.00 sec)

DELETE 语句

语法：

DELETE FROM table_name[WHERE condition][ORDER BY ...][LIMIT row_count];
tidb> SELECT count(*) FROM universe.planets;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
tidb> DELETE FROM universe.planets;
Query OK, 10 rows affected (0.01 sec)