首先来首歌曲来放松一下吧！
关系数据库的基本操作就是增删改查，即CRUD：Create、Retrieve(检索)、Update、Delete。其中，对于查询，我们已经详细讲述了SELECT语句的详细用法。
而对于增、删、改，对应的SQL语句分别是：
- INSERT：插入新记录；
- UPDATE：更新已有记录；
- DELETE：删除已有记录。
一、INSERT
1、插入一条记录
语法：INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
id字段是一个自增主键，它的值可以由数据库自己推算出来。
如果一个字段有默认值或设置为允许NULL值，那么在INSERT语句中也可以不出现。
要注意，字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。
可以不指定插入列，但必须和表的列完全匹配，这样并不安全，高度依赖于表中的定义顺序，表结构变动后，就不一定匹配了！一般不这样使用！
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| INSERT INTO Customers
VALUES (NULL,
'Pep E. LaPew'，
'100 Main Street',
'LoS Ange les',
'CA',
'90046'，
'USA',
NULL,
NULL);
mysql> INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
Query OK, 1 row affected (0.03 sec)
$ mysql> SELECT * FROM students;
+
| id | class_id | name | gender | score |
+
| 1 | 1 | 小明 | M | 90 |
| 2 | 1 | 小红 | F | 95 |
| 3 | 1 | 小军 | M | 88 |
| 4 | 1 | 小米 | F | 73 |
| 5 | 2 | 小白 | F | 81 |
| 6 | 2 | 小兵 | M | 55 |
| 7 | 2 | 小林 | M | 85 |
| 8 | 3 | 小新 | F | 91 |
| 9 | 3 | 小王 | M | 89 |
| 10 | 3 | 小丽 | F | 85 |
| 11 | 5 | 新生 | M | 88 |
| 12 | 2 | 大牛 | M | 80 |
+
12 rows in set (0.00 sec)
|
2、插入多条记录
语法：INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...), VALUES (值1, 值2, ...).....;
多条语句，逗号隔开！
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
| $ mysql> INSERT INTO students (class_id, name, gender, score) VALUES
-> (1, '大宝', 'M', 87),
-> (2, '二宝', 'M', 81);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
$ mysql> SELECT * FROM students;
+
| id | class_id | name | gender | score |
+
| 1 | 1 | 小明 | M | 90 |
| 2 | 1 | 小红 | F | 95 |
| 3 | 1 | 小军 | M | 88 |
| 4 | 1 | 小米 | F | 73 |
| 5 | 2 | 小白 | F | 81 |
| 6 | 2 | 小兵 | M | 55 |
| 7 | 2 | 小林 | M | 85 |
| 8 | 3 | 小新 | F | 91 |
| 9 | 3 | 小王 | M | 89 |
| 10 | 3 | 小丽 | F | 85 |
| 11 | 5 | 新生 | M | 88 |
| 12 | 2 | 大牛 | M | 80 |
| 13 | 1 | 大宝 | M | 87 |
| 14 | 2 | 二宝 | M | 81 |
+
14 rows in set (0.00 sec)
|
3、插入检索出的数据
即使用INSERT INTO ... SELECT ... FROM ...
为简单起见，这个例子在 NSERT 和SELECT 语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，MySQL甚至不关心 SELECT 返回的列名。它使用的是列的位置，因此 SELECT 中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。这对于从使用不同列名的表中导入数据是非常有用的。
这个例子导入了 cust_id （假设你能够确保 cust_id 的值不重复）。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| INSERT INTO customers(cust_id,
cust_contact ,
cust_emai 1，
cust_name ，
cust_address,
cust_city ，
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact ，
cust_emai 1，
cust_name , .
cust_address,
cust_city ，
cust_state,
cust_zip,
cust_country
FROM custnew;
|
4、提高整体性能
INSERT 操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的 SELECT 语句的性能。
如果数据检索是最重要的（通常是这样），则你可以通过在INSERT 和 INTO 之间添加关键字 LOW_PRIORITY ，指示MySQL降低 INSERT 语句的优先级，如下所示：
1
| INSERT LOW_PRIORITY INTO
|
5、IGNORE
如果用 INSERT 语句插入多行，并且在插入这些行中的一行或多行时出一个现错误，则整个 INSERT 操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。
使用IGNORE即使是发生错误，也继续进行更新，如下所示：
INSERT IGNORE INTO customers....
二、UPDATE
在 UPDATE 语句中使用子查询 ，UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据更新列数据。
1、更新一条记录
语法：UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
1
2
3
4
5
6
7
8
9
10
11
| $ mysql> UPDATE students SET name='大牛', score=66 WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
$ mysql> SELECT * FROM students WHERE id=1;
+
| id | class_id | name | gender | score |
+
| 1 | 1 | 大牛 | M | 66 |
+
1 row in set (0.00 sec)
|
2、更新多条记录
语法：UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
WHERE条件可与逻辑运算符结合，更新指定条件的记录。
如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| $ mysql> UPDATE students SET score=score+10 WHERE score<80;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
$ mysql> SELECT * FROM students;
+
| id | class_id | name | gender | score |
+
| 1 | 1 | 大牛 | M | 76 |
| 2 | 1 | 小红 | F | 95 |
| 3 | 1 | 小军 | M | 88 |
| 4 | 1 | 小米 | F | 83 |
| 5 | 2 | 小白 | F | 81 |
| 6 | 2 | 小兵 | M | 65 |
| 7 | 2 | 小林 | M | 85 |
| 8 | 3 | 小新 | F | 91 |
| 9 | 3 | 小王 | M | 89 |
| 10 | 3 | 小丽 | F | 85 |
| 11 | 5 | 新生 | M | 88 |
| 12 | 2 | 大牛 | M | 80 |
| 13 | 1 | 大宝 | M | 87 |
| 14 | 2 | 二宝 | M | 81 |
+
14 rows in set (0.00 sec)
|
3、不使用WHERE条件
此举动要小心，整个表的所有记录都会被更新。
eg：UPDATE students SET score=60;
效果：整个表的学生分数都变为了60.
注意：所以，在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。
4、IGNORE
如果用 UPDATE 语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个 UPDATE 操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。
使用IGNORE即使是发生错误，也继续进行更新，如下所示：
UPDATE IGNORE customers... SET...
5、MySQL提示信息
使用MySQL这类真正的关系数据库时，UPDATE语句会返回更新的行数以及WHERE条件匹配的行数。
1
2
3
| $ mysql> UPDATE students SET name='大牛', score=66 WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
|
三、DELETE
1、删除记录
语法：DELETE FROM <表名> WHERE ...;
同样：可以配合逻辑运算符实现多条删除！
如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| $ mysql> DELETE FROM students WHERE id=1;
Query OK, 1 row affected (0.01 sec)
$ mysql> SELECT * FROM students;
+
| id | class_id | name | gender | score |
+
| 2 | 1 | 小红 | F | 95 |
| 3 | 1 | 小军 | M | 88 |
| 4 | 1 | 小米 | F | 83 |
| 5 | 2 | 小白 | F | 81 |
| 6 | 2 | 小兵 | M | 65 |
| 7 | 2 | 小林 | M | 85 |
| 8 | 3 | 小新 | F | 91 |
| 9 | 3 | 小王 | M | 89 |
| 10 | 3 | 小丽 | F | 85 |
| 11 | 5 | 新生 | M | 88 |
| 12 | 2 | 大牛 | M | 80 |
| 13 | 1 | 大宝 | M | 87 |
| 14 | 2 | 二宝 | M | 81 |
+
13 rows in set (0.00 sec)
|
2、不使用WHERE条件
此举动要小心，整个表的所有记录都会被删除。
eg：DELETE FROM students;
效果：变成了一张空表。
注意：所以，在执行DELETE语句时也要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用DELETE删除。
4、删除指定列
将某列更新为NULL值，如果表定义允许NULL值。
1
2
3
| UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
|
5、删除所有行
普通方法：
更快的方法：
使用TRUNCATE TABLE，它完成相同的工作，但速度更快（ TRUNCATE 实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。
6、MySQL提示信息
使用MySQL这类真正的关系数据库时，DELETE语句也会返回删除的行数以及WHERE条件匹配的行数。
1
2
| $ mysql> DELETE FROM students WHERE id=1;
Query OK, 1 row affected (0.01 sec)
|
四、实用SQL语句
1、插入或替换
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用REPLACE语句。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| $ mysql> REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
Query OK, 1 row affected (0.01 sec)
$ mysql> SELECT * FROM students;
+
| id | class_id | name | gender | score |
+
| 1 | 1 | 小明 | F | 99 |
| 2 | 1 | 小红 | F | 95 |
| 3 | 1 | 小军 | M | 88 |
| 4 | 1 | 小米 | F | 83 |
| 5 | 2 | 小白 | F | 81 |
| 6 | 2 | 小兵 | M | 65 |
| 7 | 2 | 小林 | M | 85 |
| 8 | 3 | 小新 | F | 91 |
| 9 | 3 | 小王 | M | 89 |
| 10 | 3 | 小丽 | F | 85 |
| 11 | 5 | 新生 | M | 88 |
| 12 | 2 | 大牛 | M | 80 |
| 13 | 1 | 大宝 | M | 87 |
| 14 | 2 | 二宝 | M | 81 |
+
14 rows in set (0.00 sec)
|
2、插入或更新
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| $ mysql> INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, ' 小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=98;
Query OK, 2 rows affected (0.00 sec)
$ mysql> SELECT * FROM students;
+
| id | class_id | name | gender | score |
+
| 1 | 1 | 小明 | F | 98 |
| 2 | 1 | 小红 | F | 95 |
| 3 | 1 | 小军 | M | 88 |
| 4 | 1 | 小米 | F | 83 |
| 5 | 2 | 小白 | F | 81 |
| 6 | 2 | 小兵 | M | 65 |
| 7 | 2 | 小林 | M | 85 |
| 8 | 3 | 小新 | F | 91 |
| 9 | 3 | 小王 | M | 89 |
| 10 | 3 | 小丽 | F | 85 |
| 11 | 5 | 新生 | M | 88 |
| 12 | 2 | 大牛 | M | 80 |
| 13 | 1 | 大宝 | M | 87 |
| 14 | 2 | 二宝 | M | 81 |
+
14 rows in set (0.00 sec)
|
3、插入或忽略
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| $ mysql> INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
Query OK, 0 rows affected, 1 warning (0.00 sec)
$ mysql> SELECT * FROM students;
+
| id | class_id | name | gender | score |
+
| 1 | 1 | 小明 | F | 98 |
| 2 | 1 | 小红 | F | 95 |
| 3 | 1 | 小军 | M | 88 |
| 4 | 1 | 小米 | F | 83 |
| 5 | 2 | 小白 | F | 81 |
| 6 | 2 | 小兵 | M | 65 |
| 7 | 2 | 小林 | M | 85 |
| 8 | 3 | 小新 | F | 91 |
| 9 | 3 | 小王 | M | 89 |
| 10 | 3 | 小丽 | F | 85 |
| 11 | 5 | 新生 | M | 88 |
| 12 | 2 | 大牛 | M | 80 |
| 13 | 1 | 大宝 | M | 87 |
| 14 | 2 | 二宝 | M | 81 |
+
14 rows in set (0.00 sec)
|
4、快照
如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT。
可选的复制复制了一份，和该表结构一致。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| $ mysql> CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0
$ mysql> SHOW TABLES;
+
| Tables_in_test |
+
| classes |
| students |
| students_of_class1 |
+
3 rows in set (0.01 sec)
$ mysql> SELECT * FROM students_of_class1;
+
| id | class_id | name | gender | score |
+
| 1 | 1 | 小明 | F | 98 |
| 2 | 1 | 小红 | F | 95 |
| 3 | 1 | 小军 | M | 88 |
| 4 | 1 | 小米 | F | 83 |
| 13 | 1 | 大宝 | M | 87 |
+
5 rows in set (0.00 sec)
|
5、写入查询结果集
如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。
- 先创建满足需求的表
1
2
3
4
5
6
7
| $ mysql> CREATE TABLE result (
-> id BIGINT NOT NULL AUTO_INCREMENT,
-> class_id BIGINT NOT NULL,
-> average DOUBLE NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.03 sec)
|
- 使用
INSERT INTO ... SELECT ... FROM...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| $ mysql> INSERT INTO result (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
$ mysql> SELECT * FROM result;
+
| id | class_id | average |
+
| 1 | 1 | 90.2 |
| 2 | 2 | 78.4 |
| 3 | 3 | 88.333333333 |
| 4 | 5 | 88 |
+
4 rows in set (0.00 sec)
|
6、强制使用指定索引
在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。
指定索引前提是索引（idx_class_id）必须存在！
1
| $ SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
|
微信
支付宝