學習SQL【5】-數據更新

05-20

學習SQL【5】-數據更新

來自專欄 Python愛好者

數據的更新處理大體可以分為插入（INSERT）、刪除（DELETE）和更新（UPDATE）三類，此外，還會給大家介紹資料庫中用來管理數據更新的重要概念—事務。

一：數據的插入（INSERT語句）

1：要學習INSERT語句，我們得首先創建一個名為ProductIns的表。

創建表ProductIns的語句如下：

CREATE TABLE ProductIns ( product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER DEFAULT 0, purchase_price INTEGER , regist_date DATE , PRIMARY KEY(product_id));

如上所示，這裡僅僅是創建了一個表，並沒有插入數據。

INSERT語句的基本語法：

INSERT INTO <表名> （列名1, 列名2,...） VALUES (值1, 值2,...);

例，向表ProductIns中插入一行數據：

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES (0001, T衫, 衣服, 1000, 500, 2017-09-20);

將列名和值用逗號分開，分別括在()內，這種形式成為清單。上面的INSERT語句包含如下兩個清單：

列清單：

(product_id, product_name, product_type, sale_price, purchase_price, regist_date)

值清單：

(0001, T衫, 衣服, 1000, 500, 2017-09-20)

原則上，執行一次INSERT 語句會插入一行數據。

當然很多RDBMS都支持一次插入多行數據，這樣的功能稱為多行INSERT。

例，多行INSERT(Oracle除外)：

INSERT INTO ProductIns VALUES (0002, 打孔器, 辦公用品, 500, 320, 2017-09-11), (0003, 運動T衫, 衣服, 4000, 2800, NULL), (0004, 菜刀, 廚房用具, 3000, 2800, 2017-09-20);

其次，多行INSERT的語法並不適用於所有的RDBMS，該語法適用於的DB2，SQL,SQL Server,PostgreSQL和MySQL，但不適用於Oracle.

2：列清單的省略

對錶進行全列INSERT時，可以省略表名後的列清單。

INSERT INTO ProductIns VALUES (0005, 高壓鍋, 廚房用具, 6800, 5000, 2017-01-15);

3：插入NULL INSERT語句中想要給某一列賦予NULL值時，可以直接在VALUES子句的值清單中寫入NULL。

例，向purchase列插入中插入NULL：

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES (0006, 叉子, 廚房用具, 500, NULL, 2017-09-20);

但是，想要插入NULL的列一定不能有NOT NULL約束。

4：插入默認值

我們還可以向表中插入默認值。可以通過在創建表的CREATE TABLE語句中設置DEFAULT約束來設置默認值。

我們可以通過「DEFAULT <默認值>」的形式來設定默認值。

例，創建表ProductIns的語句：

```SQL CREATE TABLE ProductIns ( product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER DEFAULT 0, -- 銷售單價的默認值設定為0 purchase_price INTEGER , regist_date DATE , PRIMARY KEY(product_id));

默認值的使用方法通常有顯示和隱式兩種。

通過顯示方式設定默認值，在VALUES子句中指定DEFAULT關鍵字：

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES (0007, 擦菜板, 廚房用具, DEFAULT, 790, 2017-04-28);

確認插入的數據行

SELECT * FROM ProductIns WHERE product_id = 0007;

執行結果：

通過隱式方法設定默認值，在清單中省略相應的列和值：

INSERT INTO ProductIns (product_id, product_name, product_type, purchase_price, regist_date) VALUES (0007, 擦菜板, 廚房用具, 790, 2017-09-28);

省略INSERT語句中的列名，就會自動設定為該列的默認值（沒有默認值的會設定為NULL）。

5：從其他表中複製數據

要學習該方法，我們首先得創建一張表。

創建ProductCopy表的CREATE TABLE語句：

--用來插入數據的複製表 CREATE TABLE ProductCopy ( product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER , purchase_price INTEGER , regist_date DATE , PRIMARY KEY (product_id));

下面我們就嘗試一下將Product表中的數據插入到ProductCopy表中吧。

--將商品表中的數據複製到商品複製表中 INSERT INTO ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date) SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date FROM Product;

使用INSERT…SELECT語句可以在關聯的表之間傳遞數據。

下面我們嘗試一下使用包含GROUP BY 子句的SELECT語句進行插入：

創建ProductType表的CREATE TABLE語句

--根據商品種類進行匯總的表 CREATE TABLE ProductType ( product_type VARCHAR(32) NOT NULL, sun_sale_price INTEGER , sum_purchase_price INTEGER , PRIMARY KEY (product_type));

下面我們使用INSERT…SELECT語句，從Product表中選取數據插入到這張表中吧：

--插入其他表中數據合計值的INSERT...SELECT語句 INSERT INTO ProductType (product_type, sun_sale_price, sum_purchase_price) SELECT product_type, SUM(sale_price), SUM(purchase_price) FROM Product GROUP BY product_type;

確認插入的數據：

SELECT * FROM ProductType;

執行結果

product_type | sun_sale_price | sum_purchase_price--------------+----------------+-------------------- 衣服 | 5000 | 3300 辦公用品 | 600 | 320 廚房用具 | 11180 | 8590(3 行記錄)

INSERT 語句的SELECT語句中，可以使用WHERE子句或者GROUP BY子句等任何SQL語法（但使用ORDER BY子句不會產生任何效果）。

二：數據的刪除（DELETE語句）

1：DROP TABLE語句和DELETE語句

刪除數據的方法大體可以分為以下兩種：

① DROP TABLE語句：可以將表完全刪除② DELETE語句：刪除表中的全部數據，會留下表（容器）

2：DELETE語句的基本語法

保留數據表，僅刪除全部數據行的DELETE語句：

DELETE FROM <表名>;

例如，清空Product表：

DELETE FROM Product;

DELETE語句刪除的對象不是表或者列，而是記錄（行）。

3：指定刪除對象的DELETE語句

刪除部分數據行的DELETE語句如下：

DELETE FROM <表名> WHERE <條件>;

例如，刪除銷售單價大於等於4000日元的數據：

DELETE FROM Product WHERE sale_price >= 4000;

確認刪除後的結果:

SELECT * FROM Product;

執行結果：

product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+------------- 0001 | T衫 | 衣服 | 1000 | 500 | 2017-09-20 0002 | 打孔器 | 辦公用品 | 500 | 320 | 2017-09-11 0004 | 菜刀 | 廚房用具 | 3000 | 2800 | 2017-09-20 0006 | 叉子 | 廚房用具 | 500 | | 2017-09-20 0007 | 擦菜板 | 廚房用具 | 880 | 790 | 2016-04-28 0008 | 圓珠筆 | 辦公用品 | 100 | | 2017-11-11(6 行記錄)

可以通過WHERE子句指定對象條件來刪除部分數據。

與SELECT語句不同的是，DELETE語句中不能使用GROUP BY、HAVING和ORDER BY三類子句，而只能使用WHERE 子句。

三：數據的更新（UPDATE語句）

1：UPDATE語句的基本用法

改變表中數據的UPDATE語句：

UPDATE <表名> SET <列名> = <表達式>;

例如，將登記日期全部更新為「2017-10-10」：

UPDATE Product SET regist_date = 2017-10-10;

通過SELECT語句來確認一下更新內容：

--確認更新內容 SELECT * FROM Product;

執行結果：

product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+------------- 0001 | T衫 | 衣服 | 1000 | 500 | 2017-10-10 0002 | 打孔器 | 辦公用品 | 500 | 320 | 2017-10-10 0004 | 菜刀 | 廚房用具 | 3000 | 2800 | 2017-10-10 0006 | 叉子 | 廚房用具 | 500 | | 2017-10-10 0007 | 擦菜板 | 廚房用具 | 880 | 790 | 2017-10-10 0008 | 圓珠筆 | 辦公用品 | 100 | | 2017-10-10(6 行記錄)

2：指定條件的UPDATE語句

更新部分數據行的UPDATE語法如下：

UPDATE <表名> SET <列名> = <表達式> WHERE <條件>;

例如，將商品種類為廚房用具的記錄的銷售單價更新為原來的十倍：

UPDATE Product SET sale_price = sale_price * 10 WHERE product_type = 廚房用具;

確認更新內容:

SELECT * FROM Product ORDER BY product_id;

執行結果：

product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+------------- 0001 | T衫 | 衣服 | 1000 | 500 | 2017-10-10 0002 | 打孔器 | 辦公用品 | 500 | 320 | 2017-10-10 0004 | 菜刀 | 廚房用具 | 30000 | 2800 | 2017-10-10 0006 | 叉子 | 廚房用具 | 5000 | | 2017-10-10 0007 | 擦菜板 | 廚房用具 | 8800 | 790 | 2017-10-10 0008 | 圓珠筆 | 辦公用品 | 100 | | 2017-10-10(6 行記錄)

3：使用NULL進行更新

使用UPDATE也可以將列更新為NULL（該更新俗稱為NULL清空）。

例如，將商品編號為0008的數據（圓珠筆）的登記日期更新為NULL：

UPDATE Product SET regist_date = NULL WHERE product_id = 0008;

確認更新內容:

SELECT * FROM Product ORDER BY product_id;

執行結果：

product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+------------- 0001 | T衫 | 衣服 | 1000 | 500 | 2017-10-10 0002 | 打孔器 | 辦公用品 | 500 | 320 | 2017-10-10 0004 | 菜刀 | 廚房用具 | 30000 | 2800 | 2017-10-10 0006 | 叉子 | 廚房用具 | 5000 | | 2017-10-10 0007 | 擦菜板 | 廚房用具 | 8800 | 790 | 2017-10-10 0008 | 圓珠筆 | 辦公用品 | 100 | |(6 行記錄)

如上所示，圓珠筆的登記日期被更新為NULL。

使用UPDATE語句可以將值清空為NULL（但只限於未設置NOT NULL約束的列）。

4：多列更新

UPDATE語句的SET 子句支持同時將多個列作為更新對象。

使用逗號對列進行分隔：

UPDATE Product SET sale_price = sale_price * 10, purchase_price = purchase_price / 2 WHERE product_type = 廚房用具;

確認更新內容:

SELECT * FROM Product ORDER BY product_id;

執行結果：

product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+------------- 0001 | T衫 | 衣服 | 1000 | 500 | 2017-10-10 0002 | 打孔器 | 辦公用品 | 500 | 320 | 2017-10-10 0004 | 菜刀 | 廚房用具 | 300000 | 1400 | 2017-10-10 0006 | 叉子 | 廚房用具 | 50000 | | 2017-10-10 0007 | 擦菜板 | 廚房用具 | 88000 | 395 | 2017-10-10 0008 | 圓珠筆 | 辦公用品 | 100 | |(6 行記錄)

如上所示，廚房用具的銷售單價更新為原來的10倍，進貨單價更新為原來一半。

四：事務

1：什麼是事務

在RDBMS中，事務是對表中數據進行更新的單位。

例如，更新商品信息的事務：

① 將叉子的銷售單價降低1000元

PDATE Product SET sale_price = sale_price - 1000 WHERE product_name = 叉子;

② 將T衫的銷售單價上浮1000元

UPDATE Product SET sale_price = sale_price + 1000 WHERE product_name = T衫;

上述兩個操作一定要作為同一個處理單元執行。遇到這種需要在同一個處理單元中執行一系列更新操作的情況，一定要使用事務來進行處理。所以，事務是需要在同一個處理單元中執行一系列更新處理的集合。

2：創建事務

在RDBMS中創建事務的語法：

事務開始語句; DML語句1; DML語句2; DML語句3;...事務結束語句（COMMIT或者ROLLBACK）;

使用事務開始語句和事務結束語句，將一系列的DML語句括起來，就實現了一個事務。

不同的DBMS的事務開始語句各不相同。

● SQL Server PostgreSQL

BEGIN TRANSACTION

● MySQL

START TRANSACTION

● Oracle、DB2

無

例如，更新商品信息的事務：

● SQL Server PostgreSQL

BEGIN TRANSACTION; --將叉子的銷售單價降低1000元 UPDATE Product SET sale_price = sale_price - 1000 WHERE product_name = 叉子;UPDATE 1 --將T衫的銷售單價上浮1000元 UPDATE Product SET sale_price = sale_price + 1000 WHERE product_name = T衫;UPDATE 1 COMMIT;

● MySQL

START TRANSACTION; --將叉子的銷售單價降低1000元 UPDATE Product SET sale_price = sale_price - 1000 WHERE product_name = 叉子;UPDATE 1 --將T衫的銷售單價上浮1000元 UPDATE Product SET sale_price = sale_price + 1000 WHERE product_name = T衫;UPDATE 1 COMMIT;

● Oracle、DB2

--將叉子的銷售單價降低1000元 UPDATE Product SET sale_price = sale_price - 1000 WHERE product_name = 叉子;UPDATE 1 --將T衫的銷售單價上浮1000元 UPDATE Product SET sale_price = sale_price + 1000 WHERE product_name = T衫;UPDATE 1 COMMIT;

● COMMIT—提交處理

COMMIT是提交事務包含的全部更新處理的結束指令，一旦提交，就無法恢復到事務開始前的狀態了。所以在事務結束時一定要仔細確認。

● ROLLBACK—取消處理

ROLLBACK是取消事務包含的全部更新處理的結束指令，相當於文件處理中的放棄保存。一旦回滾，資料庫就會恢復到事務開始前的狀態。

事務回滾的例子（SQL Server PostgreSQL）：

3：ACID特性

DBMS的事務都遵循四種特性，將這四種特性的首字母結合起來統稱為ACID特性。這是所有DBMS都必須遵守的規則。

● 原子性（Atomicity）

原子性是指在事務結束後，其中所包含的更新處理要麼全部執行，要麼完全不執行。

● 一致性（Consistency）

一致性指的是事務中包含的處理要滿足資料庫提前設置的約束，如主鍵約束或者NOT NULL約束等。

一致性也稱為完整性。

● 隔離性（Isolation）

隔離性指的是保證不同事務之間互不干擾的特性。

● 持久性（Durability）

持久性也可以成為耐久性，指的是在事務結束後，DBMS能夠保證該時間點的數據狀態會被保存的特性。

保證持久性的方法根據實現的不同而不同，其中最常見的就是講事務的執行記錄保存到硬碟燈存儲介質中（該執行記錄稱為日誌）。當發生故障時，可以通過日誌恢復到故障發生前的狀態。

每天學習一點點，每天進步一點點。