MySQL從零開始 8-表資料的增删查改：insert, update, delete, truncate

表示資料庫的主要操作對象，今天介紹關于表的增删改。

• 增加：insert
• 修改：update
• 删除：delete/truncate

1. insert增加

 insert文法如下

insert into tableName(colName name1, colName name2, ...) values(value1, value2, ...);
           

 在插入資料時，我們可以指定插入的列，在tableName後括号内指定要插入列的列名，然後在values後的括号内指定相應的值。

 注意，指定列和之後的資料順序要一一對應。

 如果不指定要插入的列，則隐式表明全部插入。

 看下面的例子。

-- 建立表
mysql> create table t1(id int, name  varchar(32));
Query OK, 0 rows affected (0.52 sec)

-- 指定name列進行插入
mysql> insert into t1(name) values('xucc');
Query OK, 1 row affected (0.53 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
| NULL | xucc |
+------+------+
1 row in set (0.00 sec)

-- 指定id列進行插入
mysql> insert into t1(id) values(1);
Query OK, 1 row affected (0.28 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
| NULL | xucc |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

-- 向id列插入name，報錯
mysql> insert into t1(id) values('zhangsan');
ERROR 1366 (HY000): Incorrect integer value: 'zhangsan' for column 'id' at row 1

-- 不指定列，全部插入
mysql> insert into t1 values(3, 'lisi');
Query OK, 1 row affected (0.06 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
| NULL | xucc |
|    1 | NULL |
|    3 | lisi |
+------+------+
3 rows in set (0.00 sec)
           

 在插入資料的時候，有時候我們會對主鍵所在列進行插入，假設逐漸對應的值已經存在，就會插入失敗。

-- id列為主鍵
mysql> create table t2(id int primary key, name varchar(), age int);
Query OK,  rows affected ( sec)

mysql> insert into t2 values(, 'xucc', );
Query OK,  row affected ( sec)

-- 插入主鍵值存在的列，報錯
mysql> insert into t2 values(, 'zhangsan', );
ERROR  (): Duplicate entry '1' for key 'PRIMARY'
           

 這是就要使用如下方法：

• a. 更新操作

 文法如下：

insert into tableName(colName1, ...) values(value1, ...) on duplicate key update colName1=value1, ... ;
           

 當發生主鍵沖突時，就會将update關鍵字後的操作進行執行。

mysql> insert into t2 values(1, 'zhangsan', 11) on duplicate key update id=1, name='zahngsan';
Query OK, 2 rows affected (0.09 sec)

mysql> select * from t2;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zahngsan |   10 |
+----+----------+------+
1 row in set (0.00 sec)
           

• b. 替換操作

 使用replace進行資料插入，如果發生主鍵沖突，直接替換，如果沒發生主鍵沖突，正常資料插入。

replace into tableName(colName1, ...) values(value1, ...);
           

 使用如下：

mysql> replace into t2 values(1, 'lisi', 12);
Query OK, 2 rows affected (0.26 sec)

mysql> select * from t2;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lisi |   12 |
+----+------+------+
1 row in set (0.00 sec)
           

2. update修改

 update又稱更新，文法如下：

update tableName set colName1=value1[, ...] [where condition];
           

 uodate将set關鍵字後的列修改成等号後面的值，可以有多列，where後面跟的是update的條件，即修改的列的限制條件，如果不加限制條件，就會将表中該列所有資料進行修改。

 在進行update的使用之前，我們想創造一張表。

mysql> create table t3(
    ->   id int,
    ->   name varchar(32),
    ->   price float
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql> insert into t3 values(1, '蘋果', 5.3),(2, '香蕉', 4.1), (3, '橘子', 7.12), (4, '芒果', 2.2);
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|    1 | 蘋果 |   5.3 |
|    2 | 香蕉 |   4.1 |
|    3 | 橘子 |  7.12 |
|    4 | 芒果 |   2.2 |
+------+------+-------+
4 rows in set (0.01 sec)
           

 update使用如下：

-- 将所有水果價錢加10
mysql> update t3 set price=price+10;
Query OK, 4 rows affected (0.09 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from t3;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|    1 | 蘋果 |  15.3 |
|    2 | 香蕉 |  14.1 |
|    3 | 橘子 | 17.12 |
|    4 | 芒果 |  12.2 |
+------+------+-------+
4 rows in set (0.00 sec)

-- 将所有id為偶數的水果價格定為20
mysql> update t3 set price=20 where id%2=0;
Query OK, 2 rows affected (0.58 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t3;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|    1 | 蘋果 |  15.3 |
|    2 | 香蕉 |    20 |
|    3 | 橘子 | 17.12 |
|    4 | 芒果 |    20 |
+------+------+-------+
4 rows in set (0.00 sec)
           

 還可以将limit與update配合使用表示限制更新數量，文法如下：

update tableName set colName=value [...] [where condition] limit 更新數量;
           

 使用如下：

-- 将前兩行資料id加10
mysql> update t3 set id=id+10 limit 2;
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t3;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|   11 | 蘋果 |  15.3 |
|   12 | 香蕉 |    20 |
|    3 | 橘子 | 17.12 |
|    4 | 芒果 |    20 |
+------+------+-------+
4 rows in set (0.00 sec)
           

3. delete/truncate删除

 與update相似，如果不指定條件，delete會删除整張表，反之，會删除指定條件的行。

 在進行示範之前，我們先應該對t3表做一個拷貝，以防資料丢失，畢竟，删除操作在資料庫裡是一個高風險操作。

-- 建立副本tmp，結構與t3一樣
mysql> create table tmp like t3;
Query OK, 0 rows affected (0.53 sec)

-- 将t3資料全部插入tmp中
mysql> insert into tmp select * from t3;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|   11 | 蘋果 |  15.3 |
|   12 | 香蕉 |    20 |
|    3 | 橘子 | 17.12 |
|    4 | 芒果 |    20 |
+------+------+-------+
4 rows in set (0.02 sec)
           

 接下來進行删除操作。

mysql> delete from tmp where id=11;
Query OK, 1 row affected (0.18 sec)

mysql> select * from tmp;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|   12 | 香蕉 |    20 |
|    3 | 橘子 | 17.12 |
|    4 | 芒果 |    20 |
+------+------+-------+
3 rows in set (0.00 sec)

-- 不指定條件，删除整張表
mysql> delete from tmp;
Query OK, 3 rows affected (0.09 sec)

mysql> select * from tmp;
Empty set (0.00 sec)
           

 MySQL提供了零一個關鍵字truncate也用于删除，它的文法如下：

 truncate删除不能指定條件，一般用于表的整體删除，速度比delete整體删除要快。

 truncate會将表記錄也删除掉，例如自增長資料。使用delete删除自增長資料，下次自增長資料添加還會從删除的值開始增加，而truncate卻将其徹底删除，下次添加從預設值開始。

 truncate删除完畢不會傳回删除的資料數，而delete會傳回删除的資料數。

 要注意，不管是delete還是truncate隻是删除表的資料，并沒有删除表結構，删除表結構使用drop。