一、INSERT
mysql> truncate table t1;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(2),(3),(-1); //插入多个值，MySQL独有
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into t1 select 8;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> create table t3 (a int, b int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t3 select 8; //没有指定列，报错
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into t3(a) select 8; //指定列，正常插入
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+------+------+
| a | b |
+------+------+
| 8 | NULL |
+------+------+
1 row in set (0.00 sec)
mysql> insert into t3 select 8, 9; //不指定列，但是插入值匹配列的个数和类型
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+------+------+
| a | b |
+------+------+
| 8 | NULL |
| 8 | 9 |
+------+------+
2 rows in set (0.00 sec)
mysql> insert into t3(b) select a from t2; //从t2表中查询数据并插入到t3(a)中，注意指定列
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+------+------+
| a | b |
+------+------+
| 8 | NULL |
| 8 | 9 |
| NULL | 5 |
| NULL | 4 |
| NULL | 3 |
+------+------+
5 rows in set (0.00 sec)
二、DELETE
没啥好说的。。
三、UPDATE
1.单表更新
mysql> truncate table t3;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t3 select 1,2;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql> update t3 set a=10 where a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t3;
+------+------+
| a | b |
+------+------+
| 10 | 2 |
+------+------+
1 row in set (0.00 sec)
2.级联更新
mysql> select * from t1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| -1 |
| 8 |
+------+
5 rows in set (0.00 sec)
mysql> select * from t2;
+------+
| a |
+------+
| 5 |
| 4 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> select * from t1 join t2 on t1.a = t2.a ;
+------+------+
| a | a |
+------+------+
| 3 | 3 |
+------+------+
1 row in set (0.00 sec)
级联更新 先得到t1.a=t2.a的结果集，然后将结果集中的t1.a设置为100
mysql> update t1 join t2 on t1.a = t2.a set t1.a=100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+------+
| a |
+------+
| 1 |
| 2 |
| 100 | //该行被update为100
| -1 |
| 8 |
+------+
5 rows in set (0.00 sec)
四、REPLACE
replace的原理是：先delete，在insert
mysql> create table t4(a int primary key auto_increment, b int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t4 values(NULL, 10);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t4 values(NULL, 11);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t4 values(NULL, 12);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t4;
+---+------+
| a | b |
+---+------+
| 1 | 10 |
| 2 | 11 |
| 3 | 12 |
+---+------+
3 rows in set (0.00 sec)
//报错，说存在重复的主键记录 "1"
mysql> insert into t4 values(1, 100);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
//使用replace成功！替换该主键对应的值，两行记录受到影响
mysql> replace into t4 values(1, 100);
Query OK, 2 rows affected (0.01 sec)
mysql> select * from t4;
+---+------+
| a | b |
+---+------+
| 1 | 100 | //replace的原理是：先delete，在insert
| 2 | 11 |
| 3 | 12 |
+---+------+
3 rows in set (0.00 sec)
//没有替换对象时，类似插入效果
mysql> replace into t4 values(5, 50);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t4;
+---+------+
| a | b |
+---+------+
| 1 | 100 |
| 2 | 11 |
| 3 | 12 |
| 5 | 50 |
+---+------+
4 rows in set (0.00 sec)
测试示例2
mysql> create table t6 (a int primary key,
-> b int auto_increment, // b是auto_increment的int型数据
-> c int, key(b));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t6 values(10, NULL, 100),(20,NULL,200);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t6;
+----+---+------+
| a | b | c |
+----+---+------+
| 10 | 1 | 100 |
| 20 | 2 | 200 |
+----+---+------+
2 rows in set (0.00 sec)
//替换后b从1变成了3，说明是先删除，再插入
mysql> replace into t6 values(10,NULL,150);
Query OK, 2 rows affected (0.01 sec)
mysql> SELECT * FROM t6;
+----+---+------+
| a | b | c |
+----+---+------+
| 10 | 3 | 150 |
| 20 | 2 | 200 |
+----+---+------+
2 rows in set (0.00 sec)