MySQL資料庫表數據的增刪查改學習: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(32), age int);
Query OK, 0 rows affected (0.35 sec)

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

-- 插入主鍵值存在的列,報錯
mysql> insert into t2 values(1, 'zhangsan', 11);
ERROR 1062 (23000): 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刪除

delete from tableName [where condition];

與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 table tableName

truncate刪除不能指定條件,一般用於表的整體刪除,速度比delete整體刪除要快。

truncate會將表記錄也刪除掉,例如自增長數據。使用delete刪除自增長數據,下次自增長數據添加還會從刪除的值開始增加,而truncate卻將其徹底刪除,下次添加從默認值開始。

truncate刪除完畢不會返回刪除的數據數,而delete會返回刪除的數據數。

要註意,不管是delete還是truncate隻是刪除表的數據,並沒有刪除表結構,刪除表結構使用drop。

發佈留言