4.2　關(guān)系數(shù)據(jù)庫編程

Web數(shù)據(jù)庫開發(fā)的基礎(chǔ)是熟練應(yīng)用各種SQL語句，本節(jié)將講解常用的SQL增、刪、改、查語句，并演示Python的數(shù)據(jù)庫編程方法。

4.2.1　常用的SQL語句

SQL的英文全稱是Structured Query Language，即結(jié)構(gòu)化查詢語言，該語言于1986年經(jīng)過美國國家標準協(xié)會（ANSI）的規(guī)范成為關(guān)系數(shù)據(jù)庫的標準語言，其后ANSI又進行了若干次更新，但至今該查詢語言的主體結(jié)構(gòu)未發(fā)生變化。SQL由以下6類內(nèi)容組成。

? 數(shù)據(jù)定義語言（DDL）：創(chuàng)建、刪除表結(jié)構(gòu)的語言，包括Create、Drop。

? 數(shù)據(jù)控制語言（DCL）：為定義數(shù)據(jù)訪問及修改權(quán)限而實現(xiàn)的語句，包括Grant、Revoke。

? 數(shù)據(jù)查詢語言（DQL）：定義從數(shù)據(jù)表中查詢已有數(shù)據(jù)的方法，如Select。

? 數(shù)據(jù)操作語言（DML）：定義對數(shù)據(jù)表中的數(shù)據(jù)進行增、刪、改的方法，包括Insert、Delete、Update。

? 事務(wù)處理語言（TPL）：為保證多條SQL語句的數(shù)據(jù)一致性而定義的語句，如Commit、Rollback。

? 指針控制語言（CCL）：定義對查詢到的多條記錄進行逐行控制的方法及與Cursor相關(guān)的語句。

在以上6類語句中，DDL和DCL是數(shù)據(jù)庫管理員常用的語句，CCL是數(shù)據(jù)庫存儲過程中開發(fā)者需要的技能，本節(jié)不做講解，我們重點講解以下語句。

1. INSERT語句

INSERT語句用于向數(shù)據(jù)表中插入數(shù)據(jù)，其語法為：

如果INSERT語句中的列名序列與表定義中的位置相同，則可以省略不寫。例如，對于表4.1所定義的課程表，在其中插入數(shù)據(jù)的SQL例子為：

上述3條語句的效果完全相同。第1條語句按正規(guī)語法編寫；第2條語句省略了列名；第3條語句顛倒了列名的順序，相應(yīng)值的順序也要顛倒。如果在INSERT語句中不指明某列的值且在表定義時沒有指定默認值，則數(shù)據(jù)庫將其設(shè)置為默認值NULL：

上述INSERT語句中省略了對description列的賦值，在新插入的記錄中該列將被置為NULL。

注意：SQL語句本身的關(guān)鍵字不區(qū)分大小寫，如INSERT INTO、DELETE等。

2. DELETE語句

DELETE語句用于從數(shù)據(jù)表中刪除已有的行，其語法為：

該語義為刪除table_name中所有滿足條件表達式（即條件表達式結(jié)果為True）的記錄。條件表達式由條件操作符和操作數(shù)組成，常用的SQL條件表達式如表4.6所示。

應(yīng)用多條件表達式時，應(yīng)注意AND和OR操作符同時出現(xiàn)時的優(yōu)先順序：AND運算的優(yōu)先級高于OR，即先運算AND再運算OR。如果需要指定不按照該優(yōu)先級執(zhí)行，則可以通過小括號表示先后順序。DELETE及WHERE條件表達式的應(yīng)用舉例如下：

3. UPDATE語句

UPDATE語句用于修改數(shù)據(jù)表中已有記錄的列數(shù)據(jù)，其語法為：

該語義為將table_name表中所有滿足條件表達式的記錄的指定列設(shè)置為新值。其中的條件表達式已經(jīng)在表4.6中總結(jié)過，對表4.3定義的學(xué)生表應(yīng)用UPDATE語句的示例如下：

4. SELECT語句

SELECT用于從數(shù)據(jù)表中選取數(shù)據(jù)，是SQL中最常用的語句，語法結(jié)構(gòu)如下：

其中方括號中的內(nèi)容為可選項目。語法中的第1行用于指定查詢結(jié)果所需要返回的列：可以逐個列出所有列名，也可以用通配符星號“*”表示返回所有列。而可選項top用于指定返回的最大行數(shù)；distinct只用于在只返回一列時指明排除重復(fù)項。WHERE條件表達式的用法同UPDATE/DELETE語句相同。對于表4.1定義的課程表舉例如下：

GROUP BY用于對數(shù)據(jù)進行分組以便于匯總計算；HAVING是GROUP BY的可選項，用于對匯總結(jié)果進行篩選。匯總計算是指統(tǒng)計記錄的個數(shù)、計算某列的平均值等。比如：

技巧：GROUP BY語句可以同時指定多個列進行分組。

上例中的AVG(period)、COUNT(*)是SQL的匯總計算聚集函數(shù)。常用的SQL聚集函數(shù)如表4.7所示。

ORDER BY用于指定返回結(jié)果的記錄按某個或某幾列的大小排序，ASC用于指定從小到大排列（ASC是默認值），DESC用于指定從大到小排列。比如：

5. 多表連接的SELECT語句

因為整個系統(tǒng)的數(shù)據(jù)分布在不同的表中，所以很多時候為了得到完整的結(jié)果，開發(fā)者需要從兩個或更多的表中查詢數(shù)據(jù)，這時需要在FROM子語句中用JOIN關(guān)鍵字連接多個表。JOIN相關(guān)的語法為：

其語義為按照連接條件表達式連接兩個表，使兩個表的列都可以被用于SELECT、WHERE、ORDER BY等子語句。JOIN關(guān)鍵字本身有多種類型，如表4.8所示。

雖然一個JOIN關(guān)鍵字只能連接兩個表，但是可以同時使用多個JOIN關(guān)鍵字以達到連接多個表的目的，對于表4.1～表4.5的數(shù)據(jù)庫進行連接查詢，舉例如下：

上面的代碼分別演示了一次連接查詢和兩次連接查詢，其中都用了關(guān)鍵字INNER JOIN。INNER JOIN是最常用的一種JOIN類型，其含義為只獲取兩個表中滿足查詢關(guān)鍵字的連接記錄。

6. 事務(wù)控制語句

SQL中的事務(wù)控制語句能確保被DML語句影響的表的所有行及時得以更新，當必須以原子方式執(zhí)行的多條語句中一旦有一條失敗時，能夠取消之前成功的語句。事務(wù)是SQL中將一組DML語句賦予原子執(zhí)行方式的方法。

注意：原子方式執(zhí)行是指在一組語句中，要么所有語句都執(zhí)行成功，要么所有語句都不執(zhí)行。

事務(wù)控制語句包含以下3條不可分割的語句。

? BEGIN TRANSACTION：啟動一個新事務(wù)，即其后的所有語句被封裝為一個原子性事務(wù)，直到有ROLLBACK或COMMIT被執(zhí)行。

? ROLLBACK：回滾事務(wù)，結(jié)束當前事務(wù)，并取消（UNDO）在本次事務(wù)中已經(jīng)執(zhí)行成功的語句。

? COMMIT：提交事務(wù)，當前事務(wù)正式完成，其中DML語句對數(shù)據(jù)庫做的更新正式生效。

4.2.2　實戰(zhàn)演練：在Python中應(yīng)用SQL

雖然SQL標準統(tǒng)一了數(shù)據(jù)庫語言，但是通過Python、Java、C++等高級語言操作數(shù)據(jù)庫時需要連接每個數(shù)據(jù)庫獨特的數(shù)據(jù)庫引擎，之后才能用SQL語言對數(shù)據(jù)庫進行操作。所以，在Python中操作不同的數(shù)據(jù)庫需要引入不同的數(shù)據(jù)庫包，常用數(shù)據(jù)庫引擎的Python包如表4.9所示。

雖然每種數(shù)據(jù)庫引擎的Python包不同，但是所有Python的數(shù)據(jù)庫引擎都遵守DB-API規(guī)范，該規(guī)范使得引用數(shù)據(jù)庫引擎后的編程方法大體相當，Python數(shù)據(jù)庫編程的步驟如下。

（1）引入Python引擎包：例如，import PsyCopg語句用于為PostgreSQL操作做準備。

（2）連接數(shù)據(jù)庫：使用引擎包的connect方法連接物理數(shù)據(jù)庫，通常在本步驟中需要輸入數(shù)據(jù)庫的IP地址、端口、數(shù)據(jù)庫名、數(shù)據(jù)庫用戶和密碼等。對于SQLite和Excel等文件數(shù)據(jù)庫，本步驟中需要給出文件名。

（3）獲取游標：在DB-API規(guī)范中，游標（cursor）用于執(zhí)行SQL命令并且管理查詢到的數(shù)據(jù)集。

（4）執(zhí)行SQL命令：將SQL命令傳給游標執(zhí)行，并解析返回的結(jié)果。本步驟可以多次進行。

（5）提交或回滾事務(wù)：在執(zhí)行DML類的SQL語句時，數(shù)據(jù)庫引擎會自動啟動新事務(wù)，在一系列的操作完成之后，可以提交或回滾當前事務(wù)。

（6）關(guān)閉游標：完成SQL操作后關(guān)閉游標。

（7）關(guān)閉數(shù)據(jù)庫連接：關(guān)閉Python客戶端和數(shù)據(jù)庫服務(wù)器的連接。

【示例4-1】下面演示用SQLite3包操作SQLite數(shù)據(jù)庫：

本例中演示了連接數(shù)據(jù)庫、新增數(shù)據(jù)、修改數(shù)據(jù)、讀取數(shù)據(jù)、關(guān)閉數(shù)據(jù)連接的一系列操作。將代碼保存為db.py，執(zhí)行效果如下：

由于所有Python數(shù)據(jù)庫引擎都遵守DB-API開發(fā)接口，因此這里不再演示其他數(shù)據(jù)庫的編程代碼，讀者可以自行嘗試。