前言

讀《sql必知必會(huì) 第四版》隨手做的筆記，寫的比較亂，可讀性并不好净嘀，讀的是中文版，翻譯過來的感覺有點(diǎn)怪怪的侠讯。
想要pdf的話可以留郵箱給我挖藏。

1. 使用DISTINCT關(guān)鍵字，它指示數(shù)據(jù)庫只返回不同的值厢漩。

SELECT DISTINCT vend_id FROM Products;

警告:不能部分使用DISTINCT
DISTINCT關(guān)鍵字作用于所有的列熬苍，不僅僅是跟在其后的那一列。例如，你指定SELECT DISTINCT vend_id, prod_price柴底，除非指定的兩列完 全相同，否則所有的行都會(huì)被檢索出來粱胜。

2. LIMIT

如果你只想返回第一行或者一定數(shù)量的行

-- LIMIT 5指示MySQL等DBMS返回不超過5行的數(shù)據(jù)
SELECT prod_name FROM Products LIMIT 5;
-- LIMIT 5 OFFSET 5指示MySQL等DBMS返回從第5行起的5行數(shù)據(jù)柄驻。
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;

3. 排序檢索數(shù)據(jù)

1. ORDER BY，根據(jù)需要排序檢索出的數(shù)據(jù)焙压。

 SELECT prod_name FROM Products
 ORDER BY prod_name;

ORDER BY子句的位置
在指定一條ORDER BY子句時(shí)鸿脓，應(yīng)該保證它是SELECT語句中最后一條子
句。如果它不是最后的子句涯曲，將會(huì)出現(xiàn)錯(cuò)誤消息野哭。

2. 按多個(gè)列排序

要按多個(gè)列排序，簡(jiǎn)單指定列名幻件，列名之間用逗號(hào)分開即可(就像選擇多個(gè)列
時(shí)那樣)拨黔。

 --下面的代碼檢索3個(gè)列，并按其中兩個(gè)列對(duì)結(jié)果進(jìn)行排序——首先按價(jià)格绰沥，
 然后按名稱排序篱蝇。
 SELECT prod_id, prod_price, prod_name FROM Products
 ORDER BY prod_price, prod_name;

重要的是理解在按多個(gè)列排序時(shí)，排序的順序完全按規(guī)定進(jìn)行徽曲。換句話說零截，對(duì)于上述例子中的輸出，僅在多個(gè)行具有相同的prod_price值時(shí)才 對(duì)產(chǎn)品按prod_name進(jìn)行排序秃臣。如果prod_price列中所有的值都是唯一的涧衙，則不會(huì)按prod_name排序。

3. 按列位置排序

除了能用列名指出排序順序外奥此，ORDER BY還支持按相對(duì)列位置進(jìn)行排序弧哎。

```sql
-- 只對(duì) 2， 3 列進(jìn)行排序
SELECT prod_id, prod_price, prod_name FROM Products
ORDER BY 2, 3;
```

4. 指定排序方向

排序默認(rèn)升序得院，降序的話用 DESC 關(guān)鍵字

SELECT prod_id, prod_price, prod_name FROM Products
ORDER BY prod_price DESC;

如果打算用多個(gè)列排序

-- DESC關(guān)鍵字只應(yīng)用到直接位于其前面的列名傻铣。
-- 在上例中，只對(duì)prod_price列指定DESC祥绞，對(duì)prod_name列不指定非洲。
-- 因此，prod_price列以降序排 序蜕径，而prod_name列(在每個(gè)價(jià)格內(nèi))仍然按標(biāo)準(zhǔn)的升序排序两踏。
SELECT prod_id, prod_price, prod_name FROM Products
ORDER BY prod_price DESC, prod_name;

警告:在多個(gè)列上降序排序 如果想在多個(gè)列上進(jìn)行降序排序，必須對(duì)每一列指定DESC關(guān)鍵字兜喻。

DESC是DESCENDING的縮寫梦染，這兩個(gè)關(guān)鍵字都可以使用。與DESC相對(duì)的是ASC(或ASCENDING)，在升序排序時(shí)可以指定它帕识。但實(shí)際 上泛粹，ASC沒有多大用處，因?yàn)樯蚴悄J(rèn)的(如果既不指定ASC也不指定DESC肮疗，則假定為ASC)

4. 過濾數(shù)據(jù)

1. 使用WHERE子句

數(shù)據(jù)根據(jù)WHERE子句中指定的搜索條件進(jìn)行過濾晶姊。WHERE子句在表名(FROM子句)之后給出。

SELECT prod_name, prod_price FROM Products
WHERE prod_price = 3.49;

警告:WHERE子句的位置
在同時(shí)使用ORDER BY和WHERE子句時(shí)伪货，應(yīng)該讓ORDER BY位于WHERE之后们衙，否則將會(huì)產(chǎn)生錯(cuò)誤

2. WHERE子句操作符

-- 列出所有不是供應(yīng)商DLL01制造的產(chǎn)品
SELECT vend_id, prod_name FROM Products
WHERE vend_id <> 'DLL01';

--BETWEEN操作符可用來檢索價(jià)格在5美元和10美元之間的所有產(chǎn)品
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

-- 檢索出 prod_price 是空的字段
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

5. 高級(jí)數(shù)據(jù)過濾

NOT和IN

1. 組合WHERE子句

1.1. AND操作符

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

1.2. OR操作符
OR操作符與AND操作符正好相反，它指示DBMS檢索匹配任一條件的行碱呼。事實(shí)上蒙挑，許多DBMS在OR WHERE子句的第一個(gè)條件得到滿足的情況下，
就不再計(jì)算第二個(gè)條件了(在第一個(gè)條件滿足時(shí)愚臀，不管第二個(gè)條件是否滿足忆蚀，相應(yīng)的行都將被檢索出來)

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;

1.3. 求值順序
SQL(像多數(shù)語言一樣)在處理OR操作符前，優(yōu)先處理AND操作符懊悯。

SELECT prod_name, prod_price FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’ AND prod_price >= 10;

提示:在WHERE子句中使用圓括號(hào) 任何時(shí)候使用具有AND和OR操作符的WHERE子句蜓谋，都應(yīng)該使用圓括號(hào)明確地分組操作符。不要過分依賴默認(rèn)求值順序炭分，即使它確實(shí)如你希望的 那樣桃焕。使用圓括號(hào)沒有什么壞處，它能消除歧義捧毛。

2. IN操作符

IN操作符用來指定條件范圍观堂，范圍中的每個(gè)條件都可以進(jìn)行匹配。IN取一組由逗號(hào)分隔呀忧、括在圓括號(hào)中的合法值师痕。

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' ) ORDER BY prod_name;

--類似與

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name;

3. NOT操作符

WHERE子句中的NOT操作符有且只有一個(gè)功能，那就是否定其后所跟的任何條件而账。因?yàn)镹OT從不單獨(dú)使用(它總是與其他操作符一起使用)胰坟，所以
它的語法與其他操作符有所不同。NOT關(guān)鍵字可以用在要過濾的列前泞辐，而不僅是在其后笔横。

NOT WHERE子句中用來否定其后條件的關(guān)鍵字。

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;
- 等同于
SELECT prod_name
FROM Products
WHERE vend_id <> 'DLL01'
ORDER BY prod_name;

6. 用通配符進(jìn)行過濾

為在搜索子句中使用通配符咐吼，必須使用LIKE操作符吹缔。LIKE指 示DBMS，后跟的搜索模式利用通配符匹配而不是簡(jiǎn)單的相等匹配進(jìn)行比較锯茄。
通配符搜索只能用于文本字段(串)厢塘，非文本數(shù)據(jù)類型字段不能使用通配符搜索茶没。

1. 百分號(hào)(%)通配符

%表示任何字符出現(xiàn)任意次數(shù)

--找出所有以詞Fish起頭的產(chǎn)品
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

通配符可在搜索模式中的任意位置使用，并且可以使用多個(gè)通配符晚碾。下面的例子使用兩個(gè)通配符抓半，它們位于模式的兩端:

--'%bean bag%'表示匹配任何位置上包含文本bean bag的值
--不論它之前或之后出現(xiàn)什么字符
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

2. 下劃線(_)通配符

下劃線的用途與%一樣，但它只匹配單個(gè)字符迄薄，而不是多個(gè)字符

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

與%能匹配0個(gè)字符不同琅关，_總是剛好匹配一個(gè)字符，不能多也不能少讥蔽。

3. 方括號(hào)([ ])通配符

方括號(hào)([])通配符用來指定一個(gè)字符集，它必須匹配指定位置(通配符的位置)的一個(gè)字符

-- 找出所有名字以J或M起頭的聯(lián)系人
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

--查詢匹配不以J或M起頭的任意聯(lián)系人名
--如果使用的是Microsoft Access画机，需要用!而不是^來否定一個(gè)集合
--因此冶伞，使用的是[!JM]而不是[^JM]
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;

--也可以使用NOT操作符得出相同的結(jié)果
SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

7. 創(chuàng)建計(jì)算字段

1. 拼接字段

拼接(concatenate)
將值聯(lián)結(jié)到一起(將一個(gè)值附加到另一個(gè)值)構(gòu)成單個(gè)值。
Access和SQL Server使用+號(hào)步氏。
DB2响禽、Oracle、PostgreSQL荚醒、SQLite和Open Office Base使用||芋类。

SELECT vend_name || ' (' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;

/*
*再看看上述SELECT語句返回的輸出。
*結(jié)合成一個(gè)計(jì)算字段的兩個(gè)列用空格填充界阁。
*許多數(shù)據(jù)庫(不是所有)保存填充為列寬的文本值侯繁，而實(shí)際上
*你要的結(jié)果不需要這些空格。
*為正確返回格式化的數(shù)據(jù)泡躯，必須去掉這些空格贮竟。
*這可以使用SQL的RTRIM()函數(shù)來完成
*/
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;

說明:TRIM函數(shù) 大多數(shù)DBMS都支持RTRIM()(正如剛才所見，它去掉字符串右邊的空格)较剃、LTRIM()(去掉字符串左邊的空格)以及TRIM()(去掉字符串左 右兩邊的空格)咕别。

2. 使用別名

別名(alias)是一個(gè)字段或值的替換名。別名用AS關(guān)鍵字賦予

--它指示SQL創(chuàng)建一個(gè)包含指定計(jì)算結(jié)果的名 為vend_title的計(jì)算字段写穴。
--任何客戶端應(yīng)用都可以按名稱引用這個(gè)列惰拱，就像 它是一個(gè)實(shí)際的表列一樣。
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;

3. 執(zhí)行算術(shù)計(jì)算

--檢索訂單號(hào)20008中的所有物品
SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_num = 20008;

SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

SQL算術(shù)操作符 +啊送、-偿短、*、/

提示:如何測(cè)試計(jì)算
SELECT語句為測(cè)試删掀、檢驗(yàn)函數(shù)和計(jì)算提供了很好的方法翔冀。雖然SELECT通常用于從表中檢索數(shù)據(jù)，但是省略了FROM子句后就是簡(jiǎn)單地訪問和處理表達(dá)式披泪，例如SELECT 3 * 2;將返回6纤子，SELECT Trim(' abc ');將返回abc，SELECT Now();使用Now()函數(shù)返回當(dāng)前日期和時(shí)間。現(xiàn)在你明 白了控硼，可以根據(jù)需要使用SELECT語句進(jìn)行檢驗(yàn)泽论。

8. 使用數(shù)據(jù)處理函數(shù)

與SQL語句不一樣幔妨，SQL函數(shù)不是可移植的鹦赎。這表示為特定SQL實(shí)現(xiàn)編寫的代碼在其他實(shí)現(xiàn)中可能不正常.
例如：

1. 文本處理函數(shù)

-- UPPER()將文本轉(zhuǎn)換為大寫
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;

常用的文本處理函數(shù)

SOUNDEX是一個(gè)將任何文本串轉(zhuǎn)換為描述其語音表示的字母數(shù)字模式的算法锁施。SOUNDEX考慮了類似的發(fā) 音字符和音節(jié)陪踩，使得能對(duì)字符串進(jìn)行發(fā)音比較而不是字母比較。

說明:SOUNDEX支持
Microsoft Access和PostgreSQL不支持SOUNDEX()悉抵，因此以下的例子不適用于這些DBMS肩狂。 另外，如果在創(chuàng)建SQLite時(shí)使用了SQLITE_SOUNDEX編譯時(shí)選項(xiàng)姥饰，那么SOUNDEX()在SQLite中就可用傻谁。因?yàn)镾QLITE_SOUNDEX不是默認(rèn)的編譯時(shí)
選項(xiàng)，所以多數(shù)SQLite實(shí)現(xiàn)不支持SOUNDEX()媳否。

-- WHERE子句使用SOUNDEX()函數(shù)把cust_contact列值和搜索字符串轉(zhuǎn)換為它們的SOUNDEX值栅螟。
--因?yàn)镸ichael Green和Michelle Green發(fā)音相似，所以它們的SOUNDEX值匹配
--因此WHERE子句正確地過濾出了所需的數(shù)據(jù)篱竭。
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

2. 日期和時(shí)間處理函數(shù)

--SQL Server中檢索2012年的所有訂單
SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) = 2012;

-- SQLite
SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = 2012;

3. 數(shù)值處理函數(shù)

常用數(shù)值處理函數(shù)

9. 匯總數(shù)據(jù)

1. 聚集函數(shù)

聚集函數(shù)(aggregate function) 對(duì)某些行運(yùn)行的函數(shù)力图，計(jì)算并返回一個(gè)值。

SQL聚集函數(shù)

1.1. AVG()函數(shù)

AVG()通過對(duì)表中行數(shù)計(jì)數(shù)并計(jì)算其列值之和掺逼，求得該列的平均值吃媒。AVG()可用來返回所有列的平均值，也可以用來返回特定列或行的平均值吕喘。

--返回值avg_price赘那，它包含Products表中所有產(chǎn)品的平均價(jià)格
SELECT AVG(prod_price) AS avg_price
FROM Products;

SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

警告:只用于單個(gè)列
AVG()只能用來確定特定數(shù)值列的平均值，而且列名必須作為函數(shù)參數(shù)給出氯质。為了獲得多個(gè)列的平均值募舟，必須使用多個(gè)AVG()函數(shù)。

說明:NULL值
AVG()函數(shù)忽略列值為NULL的行闻察。

1.2. COUNT()函數(shù)

COUNT()函數(shù)進(jìn)行計(jì)數(shù)拱礁∽练妫可利用COUNT()確定表中行的數(shù)目或符合特定條件的行的數(shù)目。
COUNT()函數(shù)有兩種使用方式:

• 使用COUNT(*)對(duì)表中行的數(shù)目進(jìn)行計(jì)數(shù)呢灶，不管表列中包含的是空值(NULL)還是非空值吴超。
• 使用COUNT(column)對(duì)特定列中具有值的行進(jìn)行計(jì)數(shù)，忽略NULL值鸯乃。

SELECT COUNT(*) AS num_cust
FROM Customers;

--只對(duì)具有電子郵件地址的客戶計(jì)數(shù):
SELECT COUNT(cust_email) AS num_cust
FROM Customers;

1.3 MAX()函數(shù)

MAX()返回指定列中的最大值鲸阻。MAX()要求指定列名

SELECT MAX(prod_price) AS max_price
FROM Products;

1.4 MIN()函數(shù)

返回指定列的最小值，要求指定列名

SELECT MIN(prod_price) AS min_price
FROM Products;

1.5 SUM()函數(shù)

SUM()用來返回指定列值的和(總計(jì))

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;

--  SUM()也可以用來合計(jì)計(jì)算值
SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;

2. 聚集不同值

以上5個(gè)聚集函數(shù)都可以如下使用:

• 對(duì)所有行執(zhí)行計(jì)算缨睡，指定ALL參數(shù)或不指定參數(shù)(因?yàn)锳LL是默認(rèn)行為)鸟悴。
• 只包含不同的值，指定DISTINCT參數(shù)奖年。

--在使用了DISTINCT后遣臼，此例子中的avg_price比較高
--因?yàn)橛卸鄠€(gè)物品具有相同的較低價(jià)格。
--排除它們提升了平均價(jià)格
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

警告:DISTINCT不能用于COUNT()
如果指定列名拾并，則DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT()鹏浅。類似地嗅义，DISTINCT必須使用列名，不能用于計(jì)算或表達(dá)式隐砸。

提示:將DISTINCT用于MIN()和MAX()
雖然DISTINCT從技術(shù)上可用于MIN()和MAX()之碗，但這樣做實(shí)際上沒有價(jià)值。一個(gè)列中的最小值和最大值不管是否只考慮不同值季希，結(jié)果都是相同
的褪那。

說明:其他聚集參數(shù)
除了這里介紹的DISTINCT和ALL參數(shù)，有的DBMS還支持其他參數(shù)式塌，如支持對(duì)查詢結(jié)果的子集進(jìn)行計(jì)算的TOP和TOP PERCENT博敬。為了解具體的 DBMS支持哪些參數(shù)，請(qǐng)參閱相應(yīng)的文檔峰尝。

3. 組合聚集函數(shù)

-- 這里用單條SELECT語句執(zhí)行了4個(gè)聚集計(jì)算
--返回4個(gè)值(Products表中物品的數(shù)目
--產(chǎn)品價(jià)格的最高值偏窝、最低值以及平均值)
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM Products;

10. 分組數(shù)據(jù)

介紹如何分組數(shù)據(jù)，以便匯總表內(nèi)容的子集武学。這涉及兩個(gè)新SELECT語句子句:GROUP BY子句和HAVING子句

1. 創(chuàng)建分組

-- 分組是使用SELECT語句的GROUP BY子句建立的祭往。
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

在使用GROUP BY子句前，需要知道一些重要的規(guī)定火窒。

• GROUP BY子句可以包含任意數(shù)目的列硼补，因而可以對(duì)分組進(jìn)行嵌套，更細(xì)致地進(jìn)行數(shù)據(jù)分組熏矿。
• 如果在GROUP BY子句中嵌套了分組已骇，數(shù)據(jù)將在最后指定的分組上進(jìn)行匯總离钝。換句話說，在建立分組時(shí)疾捍，指定的所有列都一起計(jì)算(所以不 能從個(gè)別的列取回?cái)?shù)據(jù))奈辰。
• GROUP BY子句中列出的每一列都必須是檢索列或有效的表達(dá)式(但不能是聚集函數(shù))。如果在SELECT中使用表達(dá)式乱豆，則必須在GROUP BY子 句中指定相同的表達(dá)式奖恰。不能使用別名。
• 大多數(shù)SQL實(shí)現(xiàn)不允許GROUP BY列帶有長(zhǎng)度可變的數(shù)據(jù)類型(如文本或備注型字段)宛裕。
• 除聚集計(jì)算語句外瑟啃，SELECT語句中的每一列都必須在GROUP BY子句中給出。
• 如果分組列中包含具有NULL值的行揩尸，則NULL將作為一個(gè)分組返回蛹屿。如果列中有多行NULL值，它們將分為一組岩榆。
• GROUP BY子句必須出現(xiàn)在WHERE子句之后错负，ORDER BY子句之前。

2. 過濾分組

除了能用GROUP BY分組數(shù)據(jù)外勇边，SQL還允許過濾分組犹撒，規(guī)定包括哪些分組，排除哪些分組粒褒。
WHERE過濾行识颊，而HAVING過濾分組。

SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

說明:使用HAVING和WHERE
HAVING與WHERE非常類似奕坟，如果不指定GROUP BY祥款，則大多數(shù)DBMS會(huì)同等對(duì)待它們头谜。不過聋袋，你自己要能區(qū)分這一點(diǎn)。使用HAVING時(shí)應(yīng)該結(jié)
合GROUP BY子句纫塌，而WHERE子句用于標(biāo)準(zhǔn)的行級(jí)過濾沙合。

3. 分組和排序

GROUP BY和ORDER BY經(jīng)常完成相同的工作绊率，但它們非常不同
ORDER BY與GROUP BY

提示:不要忘記ORDER BY
一般在使用GROUP BY子句時(shí)，應(yīng)該也給出ORDER BY子句最仑。這是保證數(shù)據(jù)正確排序的唯一方法藐俺。千萬不要僅依賴GROUP BY排序數(shù)據(jù)炊甲。

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

4. SELECT子句順序

11. 使用子查詢

1. 子查詢

-- 語句1
SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';

-- 語句2
SELECT cust_id
FROM Orders
WHERE order_num IN (20007,20008);

-- 結(jié)合 變?yōu)樽硬樵?-- 在SELECT語句中，子查詢總是從內(nèi)向外處理欲芹。
-- DBMS實(shí)際上執(zhí)行了兩個(gè)操作卿啡。
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');

警告:只能是單列
作為子查詢的SELECT語句只能查詢單個(gè)列。企圖檢索多個(gè)列將返回錯(cuò)誤

警告:子查詢和性能
這里給出的代碼有效菱父，并且獲得了所需的結(jié)果颈娜。但是，使用子查詢并不總是執(zhí)行這類數(shù)據(jù)檢索的最有效方法

2. 作為計(jì)算字段使用子查詢

SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

12. 聯(lián)結(jié)表

1. 聯(lián)結(jié)

1.1. 關(guān)系表

1.2. 建立連接

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

內(nèi)聯(lián)接

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

-- 連接多個(gè)表
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;

警告:性能考慮
DBMS在運(yùn)行時(shí)關(guān)聯(lián)指定的每個(gè)表浙宜，以處理聯(lián)結(jié)官辽。這種處理可能非常耗費(fèi)資源，因此應(yīng)該注意粟瞬，不要聯(lián)結(jié)不必要的表同仆。聯(lián)結(jié)的表越多，性能下降越厲害裙品。

13. 創(chuàng)建高級(jí)聯(lián)結(jié)

使用表別名

SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

內(nèi)聯(lián)結(jié)和外聯(lián)結(jié)

INNER JOIN俗批、 OUTTER JOIN

警告:SQLite外聯(lián)結(jié)
SQLite支持LEFT OUTER JOIN，但不支持RIGHT OUTER JOIN市怎。幸好扶镀，如果你確實(shí)需要在SQLite中使用RIGHT OUTER JOIN，有一種更簡(jiǎn)單的辦 法焰轻，這將在下面的提示中介紹。

提示:外聯(lián)結(jié)的類型
要記住昆雀，總是有兩種基本的外聯(lián)結(jié)形式:左外聯(lián)結(jié)和右外聯(lián)結(jié)辱志。它們之間的唯一差別是所關(guān)聯(lián)的表的順序。換句話說狞膘，調(diào)整FROM或WHERE子
句中表的順序揩懒，左外聯(lián)結(jié)可以轉(zhuǎn)換為右外聯(lián)結(jié)。因此挽封，這兩種外聯(lián)結(jié)可以互換使用已球，哪個(gè)方便就用哪個(gè)。

3. 使用帶聚集函數(shù)的聯(lián)結(jié)

--檢索所有顧客及每個(gè)顧客所下的訂單數(shù)
SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

14. 組合查詢

利用UNION操作符將多條SELECT語句組合成一個(gè)結(jié)果集辅愿。
使用UNION很簡(jiǎn)單智亮，所要做的只是給出每條SELECT語句，在各條語句之間放上關(guān)鍵字UNION点待。

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

UNION規(guī)則

可以看到阔蛉，UNION非常容易使用，但在進(jìn)行組合時(shí)需要注意幾條規(guī)則癞埠。

• UNION必須由兩條或兩條以上的SELECT語句組成状原，語句之間用關(guān)鍵字UNION分隔(因此聋呢，如果組合四條SELECT語句，將要使用三個(gè)UNION關(guān)鍵字)颠区。
• UNION中的每個(gè)查詢必須包含相同的列削锰、表達(dá)式或聚集函數(shù)(不過，各個(gè)列不需要以相同的次序列出)毕莱。
• 列數(shù)據(jù)類型必須兼容:類型不必完全相同器贩，但必須是DBMS可以隱含轉(zhuǎn)換的類型(例如，不同的數(shù)值類型或不同的日期類型)央串。

1. 包含或取消重復(fù)的行

使用UNION時(shí)磨澡，重復(fù)的行會(huì)被自動(dòng)取消。
這是UNION的默認(rèn)行為质和，如果愿意也可以改變它稳摄。事實(shí)上，如果想返回所有的匹配行饲宿，可使用UNION ALL而不是UNION厦酬。

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

對(duì)組合查詢結(jié)果排序

SELECT語句的輸出用ORDER BY子句排序。在用UNION組合查詢時(shí)瘫想，只能使用一條ORDER BY子句仗阅，它必須位于最后一條SELECT語句之后。對(duì)于結(jié)果
集国夜，不存在用一種方式排序一部分减噪，而又用另一種方式排序另一部分的情況，因此不允許使用多條ORDER BY子句车吹。

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact;

15. 插入數(shù)據(jù)

INSERT

1. 數(shù)據(jù)插入
   INSERT用來將行插入(或添加)到數(shù)據(jù)庫表筹裕。插入有幾種方式：

• 插入完整的行;
• 插入行的一部分;
• 插入某些查詢的結(jié)果。

插入完整的行

把數(shù)據(jù)插入表中的最簡(jiǎn)單方法是使用基本的INSERT語法窄驹，它要求指定表名和插入到新行中的值朝卒。

INSERT INTO Customers
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);

提示:INTO關(guān)鍵字
在某些SQL實(shí)現(xiàn)中，跟在INSERT之后的INTO關(guān)鍵字是可選的乐埠。但是抗斤，即使不一定需要，最好還是提供這個(gè)關(guān)鍵字丈咐，這樣做將保證SQL代碼在 DBMS之間可移植瑞眼。

INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);

警告:小心使用VALUES
不管使用哪種INSERT語法，VALUES的數(shù)目都必須正確负拟。如果不提供列名，則必須給每個(gè)表列提供一個(gè)值;如果提供列名歹河，則必須給列出的每個(gè)列一個(gè)值掩浙。否則花吟，就會(huì)產(chǎn)生一條錯(cuò)誤消息，相應(yīng)的行不能成功插入厨姚。

插入部分行

INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA');

警告:省略列
如果表的定義允許衅澈，則可以在INSERT操作中省略某些列。省略的列必須滿足以下某個(gè)條件谬墙。

• 該列定義為允許NULL值(無值或空值)今布。
• 在表定義中給出默認(rèn)值。這表示如果不給出值拭抬，將使用默認(rèn)值部默。
  如果對(duì)表中不允許NULL值且沒有默認(rèn)值的列不給出值，DBMS將產(chǎn)生錯(cuò)誤消息造虎，并且相應(yīng)的行插入不成功傅蹂。

插入檢索出的數(shù)據(jù)

/**
 *使用INSERT SELECT從CustNew中將所有數(shù)據(jù)導(dǎo)入Customers。
 *SELECT語句從CustNew檢索出要插入的值算凿，而不是列出它們份蝴。
 *SELECT中列 出的每一列對(duì)應(yīng)于Customers表名后所跟的每一列。
 *這條語句將插入多少行呢?這依賴于CustNew表有多少行氓轰。
 *如果這個(gè)表為空婚夫，則沒有行被插 入(也不產(chǎn)生錯(cuò)誤，因?yàn)椴僮魅匀皇呛戏ǖ?署鸡。
 *如果這個(gè)表確實(shí)有數(shù)據(jù)案糙，則所有數(shù)據(jù)將被插入到Customers。
**/
INSERT
INTO Customers(cust_id,cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT
cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM CustNew;

提示:INSERT SELECT中的列名
為簡(jiǎn)單起見靴庆，這個(gè)例子在INSERT和SELECT語句中使用了相同的列名侍筛。但是，不一定要求列名匹配撒穷。事實(shí)上，DBMS一點(diǎn)兒也不關(guān)心SELECT返 回的列名裆熙。它使用的是列的位置端礼，因此SELECT中的第一列(不管其列名)將用來填充表列中指定的第一列，第二列將用來填充表列中指定的 第二列入录，如此等等蛤奥。

INSERT SELECT中SELECT語句可以包含WHERE子句，以過濾插入的數(shù)據(jù)僚稿。

提示:插入多行
INSERT通常只插入一行凡桥。要插入多行，必須執(zhí)行多個(gè)INSERT語句蚀同。INSERT SELECT是個(gè)例外缅刽，它可以用一條INSERT插入多行啊掏，不管SELECT語句返回多少行，都將被INSERT插入衰猛。

2. 從一個(gè)表復(fù)制到另一個(gè)表

有一種數(shù)據(jù)插入不使用INSERT語句迟蜜。要將一個(gè)表的內(nèi)容復(fù)制到一個(gè)全新的表(運(yùn)行中創(chuàng)建的表)，可以使用SELECT INTO語句啡省。

-- 這條SELECT語句創(chuàng)建一個(gè)名為CustCopy的新表娜睛，
-- 并把Customers表的整個(gè)內(nèi)容復(fù)制到新表中。
-- 因?yàn)檫@里使用的是SELECT *卦睹，
-- 所以將在CustCopy表 中創(chuàng)建(并填充)與Customers表的每一列相同的列畦戒。
-- 要想只復(fù)制部分的列，可以明確給出列名结序，而不是使用*通配符障斋。
SELECT *
INTO CustCopy
FROM Customers;

-- MariaDB、MySQL笼痹、Oracle配喳、PostgreSQL和SQLite使用的語法稍有不同
CREATE TABLE CustCopy AS
SELECT * FROM Customers;

在使用SELECT INTO時(shí)，需要知道一些事情:

• 任何SELECT選項(xiàng)和子句都可以使用凳干，包括WHERE和GROUP BY;
• 可利用聯(lián)結(jié)從多個(gè)表插入數(shù)據(jù);
• 不管從多少個(gè)表中檢索數(shù)據(jù)晴裹，數(shù)據(jù)都只能插入到一個(gè)表中。

16. 更新和刪除數(shù)據(jù)

UPDATE和DELETE

有兩種使用UPDATE的方式:

• 更新表中的特定行;
• 更新表中的所有行救赐。

基本的UPDATE語句由三部分組成涧团，分別是:

• 要更新的表;
• 列名和它們的新值;
• 確定要更新哪些行的過濾條件。

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';

-- 多個(gè)列 
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';

提示:FROM關(guān)鍵字
有的SQL實(shí)現(xiàn)支持在UPDATE語句中使用FROM子句经磅，用一個(gè)表的數(shù)據(jù)更新另一個(gè)表的行泌绣。如想知道你的DBMS是否支持這個(gè)特性，請(qǐng)參閱它的 文檔预厌。

-- 要?jiǎng)h除某個(gè)列的值阿迈，可設(shè)置它為NULL(假如表定義允許NULL值)
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

2. 刪除數(shù)據(jù)

使用DELETE語句。有兩種使用DELETE的方式:

• 從表中刪除特定的行;
• 從表中刪除所有行轧叽。

DELETE FROM Customers
WHERE cust_id = '1000000006';

DELETE不需要列名或通配符苗沧。DELETE刪除整行而不是刪除列。要?jiǎng)h除指定的列炭晒，請(qǐng)使用UPDATE語句待逞。

說明:刪除表的內(nèi)容而不是表
DELETE語句從表中刪除行，甚至是刪除表中所有行网严。但是识樱，DELETE不刪除表本身

17. 創(chuàng)建和操縱表

利用CREATE TABLE創(chuàng)建表，必須給出下列信息:

• 新表的名字，在關(guān)鍵字CREATE TABLE之后給出;
• 表列的名字和定義怜庸，用逗號(hào)分隔;
• 有的DBMS還要求指定表的位置当犯。

CREATE TABLE Products
(
prod_id        CHAR(10) NOT NULL,
vend_id       CHAR(10)  NOT NULL,
prod_name CHAR(254)     NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL
);

指定默認(rèn)值

-- SQL允許指定默認(rèn)值，在插入行時(shí)如果不給出值休雌，
-- DBMS將自動(dòng)采用默認(rèn)值灶壶。
-- 默認(rèn)值在CREATE TABLE語句的列定義中用關(guān)鍵字DEFAULT指定。
CREATE TABLE OrderItems
(
order_num  INTEGER  NOT NULL,
order_item INTEGER  NOT NULL,
prod_id    CHAR(10) NOT NULL,
quantity   INTEGER  NOT NULL DEFAULT 1,
item_price DECIMAL(8,2) NOT NULL,
);

獲得系統(tǒng)日期

2. 更新表

更新表定義杈曲，可以使用ALTER TABLE語句
以下是使用ALTERTABLE時(shí)需要考慮的事情驰凛。

• 理想情況下，不要在表中包含數(shù)據(jù)時(shí)對(duì)其進(jìn)行更新担扑。應(yīng)該在表的設(shè)計(jì)過程中充分考慮未來可能的需求恰响，避免今后對(duì)表的結(jié)構(gòu)做大改動(dòng)。
• 所有的DBMS都允許給現(xiàn)有的表增加列涌献，不過對(duì)所增加列的數(shù)據(jù)類型(以及NULL和DEFAULT的使用)有所限制胚宦。
• 許多DBMS不允許刪除或更改表中的列。
• 多數(shù)DBMS允許重新命名表中的列燕垃。
• 許多DBMS限制對(duì)已經(jīng)填有數(shù)據(jù)的列進(jìn)行更改枢劝，對(duì)未填有數(shù)據(jù)的列幾乎沒有限制。

-- 給Vendors表增加一個(gè)名為vend_phone的列卜壕，其數(shù)據(jù)類型為CHAR您旁。
ALTER TABLE Vendors
ADD vend_phone CHAR(20);

ALTER TABLE Vendors
DROP COLUMN vend_phone;

說明:ALTER TABLE和SQLite
SQLite對(duì)使用ALTER TABLE執(zhí)行的操作有所限制。最重要的一個(gè)限制是轴捎，它不支持使用ALTER TABLE定義主鍵和外鍵鹤盒，這些必須在最初創(chuàng)建表 時(shí)指定。

3 刪除表

DROP TABLE CustCopy;

4 重命名表

每個(gè)DBMS對(duì)表重命名的支持有所不同侦副。對(duì)于這個(gè)操作侦锯，不存在嚴(yán)格的標(biāo)準(zhǔn)。DB2秦驯、MariaDB尺碰、MySQL、Oracle和PostgreSQL用戶使
用RENAME語句译隘，SQL Server用戶使用sp_rename存儲(chǔ)過程亲桥，SQLite用戶使用ALTER TABLE語句。

18. 使用視圖

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';

-- 假如可以把整個(gè)查詢包裝成一個(gè)名為ProductCustomers的虛擬表细燎，
-- 則可以如下輕松地檢索出相同的數(shù)據(jù):
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

視圖創(chuàng)建和使用的一些最常見的規(guī)則和限制。

• 與表一樣皂甘，視圖必須唯一命名(不能給視圖取與別的視圖或表相同的名字)玻驻。
• 對(duì)于可以創(chuàng)建的視圖數(shù)目沒有限制。
  創(chuàng)建視圖，必須具有足夠的訪問權(quán)限璧瞬。這些權(quán)限通常由數(shù)據(jù)庫管理人員授予户辫。
• 視圖可以嵌套，即可以利用從其他視圖中檢索數(shù)據(jù)的查詢來構(gòu)造視圖嗤锉。所允許的嵌套層數(shù)在不同的DBMS中有所不同(嵌套視圖可能會(huì)嚴(yán) 重降低查詢的性能渔欢，因此在產(chǎn)品環(huán)境中使用之前，應(yīng)該對(duì)其進(jìn)行全面測(cè)試)瘟忱。
• 許多DBMS禁止在視圖查詢中使用ORDER BY子句奥额。
• 有些DBMS要求對(duì)返回的所有列進(jìn)行命名，如果列是計(jì)算字段访诱，則需要使用別名(關(guān)于列別名的更多信息垫挨，請(qǐng)參閱第7課)。
• 視圖不能索引触菜，也不能有關(guān)聯(lián)的觸發(fā)器或默認(rèn)值九榔。
• 有些DBMS把視圖作為只讀的查詢，這表示可以從視圖檢索數(shù)據(jù)涡相，但不能將數(shù)據(jù)寫回底層表哲泊。詳情請(qǐng)參閱具體的DBMS文檔。
• 有些DBMS允許創(chuàng)建這樣的視圖催蝗，它不能進(jìn)行導(dǎo)致行不再屬于視圖的插入或更新切威。例如有一個(gè)視圖，只檢索帶有電子郵件地址的顧客生逸。如 果更新某個(gè)顧客牢屋，刪除他的電子郵件地址，將使該顧客不再屬于視圖槽袄。這是默認(rèn)行為烙无，而且是允許的，但有的DBMS可能會(huì)防止這種情況 發(fā)生遍尺。

1. 創(chuàng)建視圖

視圖用CREATE VIEW語句來創(chuàng)建截酷。與CREATE TABLE一樣，CREATE VIEW只能用于創(chuàng)建不存在的視圖乾戏。

說明:視圖重命名
刪除視圖迂苛，可以使用DROP語句，其語法為DROP VIEW viewname;覆蓋(或更新)視圖鼓择，必須先刪除它三幻，然后再重新創(chuàng)建。

-- 這條語句創(chuàng)建一個(gè)名為ProductCustomers的視圖呐能，它聯(lián)結(jié)三個(gè)表念搬，返回已訂購了任意產(chǎn)品的所有顧客的列表抑堡。
-- 如果執(zhí)行SELECT * FROM ProductCustomers，將列出訂購了任意產(chǎn)品的顧客朗徊。
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

-- 檢索訂購了產(chǎn)品RGAN01的顧客
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

19. 使用存儲(chǔ)過程

什么要使用存儲(chǔ)過程首妖，如何使用存儲(chǔ)過程，以及創(chuàng)建和使用存儲(chǔ)過程的基本語法爷恳。
SqlLite 不支持有缆，而接下來又主要用到 SqlLite 故不做筆記

20. 管理事務(wù)處理

介紹什么是事務(wù)處理，如何利用COMMIT和ROLLBACK語句管理事務(wù)處理温亲。

1. 事務(wù)處理
   使用事務(wù)處理(transaction processing)棚壁，通過確保成批的SQL操作要么完全執(zhí)行，要么完全不執(zhí)行铸豁，來維護(hù)數(shù)據(jù)庫的完整性灌曙。

關(guān)于事務(wù)處理需要知道的幾個(gè)術(shù)語:

• 事務(wù)(transaction)指一組SQL語句;
• 回退(rollback)指撤銷指定SQL語句的過程;
• 提交(commit)指將未存儲(chǔ)的SQL語句結(jié)果寫入數(shù)據(jù)庫表;
• 保留點(diǎn)(savepoint)指事務(wù)處理中設(shè)置的臨時(shí)占位符(placeholder)，可以對(duì)它發(fā)布回退(與回退整個(gè)事務(wù)處理不同)节芥。

提示:可以回退哪些語句?
事務(wù)處理用來管理INSERT在刺、UPDATE和DELETE語句。不能回退SELECT語句(回退SELECT語句也沒有必要)头镊，也不能回退CREATE或DROP操作蚣驼。事 務(wù)處理中可以使用這些語句，但進(jìn)行回退時(shí)相艇，這些操作也不撤銷颖杏。

2. 控制事務(wù)處理

管理事務(wù)的關(guān)鍵在于將SQL語句組分解為邏輯塊，并明確規(guī)定數(shù)據(jù)何時(shí)應(yīng)該回退坛芽，何時(shí)不應(yīng)該回退留储。

-- SQL Server
-- BEGIN TRANSACTION和COMMIT TRANSACTION語句之間的
-- SQL必須完全執(zhí)行或者完全不執(zhí)行。
BEGIN TRANSACTION
...
COMMIT TRANSACTION

ROLLBACK

-- SQL的ROLLBACK命令用來回退(撤銷)SQL語句
-- 執(zhí)行DELETE操作咙轩，然后用ROLLBACK語句撤銷获讳。
DELETE FROM Orders;
ROLLBACK;

COMMIT
一般的SQL語句都是針對(duì)數(shù)據(jù)庫表直接執(zhí)行和編寫的。這就是所謂的隱式提交(implicit commit)活喊，即提交(寫或保存)操作是自動(dòng)進(jìn)行的丐膝。
在事務(wù)處理塊中，提交不會(huì)隱式進(jìn)行钾菊。

-- SQL Server
-- 在這個(gè)SQL Server例子中帅矗，從系統(tǒng)中完全刪除訂單12345。
-- 因?yàn)樯婕案聝蓚€(gè)數(shù)據(jù)庫表Orders和OrderItems煞烫，
-- 所以使用事務(wù)處理塊來保證訂單 不被部分刪除浑此。
-- 最后的COMMIT語句僅在不出錯(cuò)時(shí)寫出更改。
-- 如果第一條DELETE起作用滞详，
-- 但第二條失敗凛俱，則DELETE不會(huì)提交喘落。
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

使用保留點(diǎn)
使用簡(jiǎn)單的ROLLBACK和COMMIT語句，就可以寫入或撤銷整個(gè)事務(wù)最冰。但是，只對(duì)簡(jiǎn)單的事務(wù)才能這樣做稀火，復(fù)雜的事務(wù)可能需要部分提交或回退暖哨。
在MariaDB、MySQL和Oracle中創(chuàng)建占位符凰狞，可使用SAVEPOINT語句:

-- 創(chuàng)建保留點(diǎn)
SAVEPOINT delete1;

-- SQL Server 創(chuàng)建保留點(diǎn)
-- SAVE TRANSACTION delete1;

--  SQL Server例子
BEGIN TRANSACTION
INSERT INTO Customers(cust_id, cust_name)
VALUES('1000000010', 'Toys Emporium');
SAVE TRANSACTION StartOrder;                      -- 創(chuàng)建保留點(diǎn)
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20100,'2001/12/1','1000000010');
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 1, 'BR01', 100, 5.49);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 2, 'BR03', 100, 10.99);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION

21. 使用游標(biāo)

什么是游標(biāo)篇裁，如何使用游標(biāo)

1. 游標(biāo)

結(jié)果集(result set)
SQL查詢所檢索出的結(jié)果。

有時(shí)赡若，需要在檢索出來的行中前進(jìn)或后退一行或多行达布，這就是游標(biāo)的用途所在。游標(biāo)(cursor)是一個(gè)存儲(chǔ)在DBMS服務(wù)器上的數(shù)據(jù)庫查詢逾冬，
它不是一條SELECT語句黍聂，而是被該語句檢索出來的結(jié)果集。在存儲(chǔ)了游標(biāo)之后身腻，應(yīng)用程序可以根據(jù)需要滾動(dòng)或?yàn)g覽其中的數(shù)據(jù)产还。

說明:具體DBMS的支持
Microsoft Access不支持游標(biāo)，所以本課的內(nèi)容不適用于Microsoft Access嘀趟。
MySQL 5已經(jīng)支持存儲(chǔ)過程脐区。因此，本課的內(nèi)容不適用MySQL較早的版本她按。
SQLite支持的游標(biāo)稱為步驟(step)牛隅，下面講述的基本概念適用于SQLite的步驟，但語法可能完全不同酌泰。

不同的DBMS支持不同的游標(biāo)選項(xiàng)和特性媒佣。常見的一些選項(xiàng)和特性如下。

• 能夠標(biāo)記游標(biāo)為只讀宫莱，使數(shù)據(jù)能讀取丈攒，但不能更新和刪除。
• 能控制可以執(zhí)行的定向操作(向前授霸、向后巡验、第一、最后碘耳、絕對(duì)位置显设、相對(duì)位置等)。
• 能標(biāo)記某些列為可編輯的辛辨，某些列為不可編輯的捕捂。
• 規(guī)定范圍瑟枫，使游標(biāo)對(duì)創(chuàng)建它的特定請(qǐng)求(如存儲(chǔ)過程)或?qū)λ姓?qǐng)求可訪問。
• 指示DBMS對(duì)檢索出的數(shù)據(jù)(而不是指出表中活動(dòng)數(shù)據(jù))進(jìn)行復(fù)制指攒，使數(shù)據(jù)在游標(biāo)打開和訪問期間不變化慷妙。

2. 使用游標(biāo)

使用游標(biāo)涉及幾個(gè)明確的步驟:

• 在使用游標(biāo)前，必須聲明(定義)它允悦。這個(gè)過程實(shí)際上沒有檢索數(shù)據(jù)膝擂，它只是定義要使用的SELECT語句和游標(biāo)選項(xiàng)。
• 一旦聲明隙弛，就必須打開游標(biāo)以供使用架馋。這個(gè)過程用前面定義的SELECT語句把數(shù)據(jù)實(shí)際檢索出來。
• 對(duì)于填有數(shù)據(jù)的游標(biāo)全闷，根據(jù)需要取出(檢索)各行叉寂。
• 在結(jié)束游標(biāo)使用時(shí)，必須關(guān)閉游標(biāo)总珠，可能的話屏鳍，釋放游標(biāo)(有賴于具體的DBMS)。

使用DECLARE語句創(chuàng)建游標(biāo)局服，這條語句在不同的DBMS中有所不同孕蝉。DECLARE命名游標(biāo)，并定義相應(yīng)的SELECT語句腌逢，根據(jù)需要帶WHERE和其他子 句降淮。

-- DECLARE語句用來定義和命名游標(biāo)，
-- 這里為CustCursor搏讶。
-- SELECT語句定義一個(gè)包含沒有電子郵件地址(NULL值)的所有顧客的游標(biāo)佳鳖。
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL

-- 打開游標(biāo)
OPEN CURSOR CustCursor

現(xiàn)在可以用FETCH語句訪問游標(biāo)數(shù)據(jù)了。FETCH指出要檢索哪些行媒惕，從何處檢索它們以及將它們放于何處(如變量名)系吩。

DECLARE TYPE CustCursor IS REF CURSOR
RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
OPEN CustCursor;
FETCH CustCursor INTO CustRecord;
CLOSE CustCursor;
END;

22. 高級(jí)SQL特性

約束、索引和觸發(fā)器妒蔚。

約束(constraint)
管理如何插入或處理數(shù)據(jù)庫數(shù)據(jù)的規(guī)則穿挨。

1. 主鍵

主鍵是一種特殊的約束，用來保證一列(或一組列)中的值是唯一的肴盏，而且永不改動(dòng)科盛。換句話說，表中的一列(或 多個(gè)列)的值唯一標(biāo)識(shí)表中的每一行菜皂。這方便了直接或交互地處理表中的行贞绵。沒有主鍵，要安全地UPDATE或DELETE特定行而不影響其他行會(huì)非 常困難恍飘。

表中任意列只要滿足以下條件榨崩，都可以用于主鍵:

• 任意兩行的主鍵值都不相同谴垫。
• 每行都具有一個(gè)主鍵值(即列中不允許NULL值)。
• 包含主鍵值的列從不修改或更新母蛛。(大多數(shù)DBMS不允許這么做翩剪，但如果你使用的DBMS允許這樣做，好吧彩郊，千萬別!)
• 主鍵值不能重用肢专。如果從表中刪除某一行，其主鍵值不分配給新行焦辅。

CREATE TABLE Vendors
(
vend_id       CHAR(10)  NOT NULL PRIMARY KEY,   -- 主鍵
vend_name     CHAR(50)  NOT NULL,
vend_address  CHAR(50)  NULL,
vend_city     CHAR(50)  NULL,
vend_state    CHAR(5)   NULL,
vend_zip      CHAR(10)  NULL,
vend_country  CHAR(50)  NULL
);

給表的vend_id列定義添加關(guān)鍵字PRIMARY KEY，使其成為主鍵椿胯。
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

說明:SQLite中的鍵
SQLite不允許使用ALTER TABLE定義鍵筷登，要求在初始的CREATE TABLE語句中定義它們。

外鍵

外鍵是表中的一列哩盲，其值必須列在另一表的主鍵中前方。外鍵是保證引用完整性的極其重要部分。

CREATE TABLE Orders
(
order_num   INTEGER NOT NULL PRIMARY KEY,   -- 主鍵
order_date DATETIME NOT NULL,
cust_id    CHAR(10) NOT NULL REFERENCES Customers(cust_id) -- 外鍵
);

--ALTER TABLE語句中用CONSTRAINT語法來定義外鍵
ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

唯一約束

唯一約束用來保證一列(或一組列)中的數(shù)據(jù)是唯一的廉油。它們類似于主鍵惠险，但存在以下重要區(qū)別。

• 表可包含多個(gè)唯一約束抒线，但每個(gè)表只允許一個(gè)主鍵班巩。
• 唯一約束列可包含NULL值。
• 唯一約束列可修改或更新嘶炭。
• 唯一約束列的值可重復(fù)使用抱慌。
• 與主鍵不一樣，唯一約束不能用來定義外鍵眨猎。

檢查約束

檢查約束用來保證一列(或一組列)中的數(shù)據(jù)滿足一組指定的條件抑进。檢查約束的常見用途有以下幾點(diǎn)。

• 檢查最小或最大值睡陪。例如寺渗，防止0個(gè)物品的訂單(即使0是合法的數(shù))。
• 指定范圍兰迫。例如信殊，保證發(fā)貨日期大于等于今天的日期，但不超過今天起一年后的日期汁果。
• 只允許特定的值鸡号。例如，在性別字段中只允許M或F须鼎。

CREATE TABLE OrderItems
(
order_num   INTEGER  NOT NULL,
order_item  INTEGER  NOT NULL,
prod_id     CHAR(10) NOT NULL,
quantity    INTEGER  NOT NULL  CHECK (quantity > 0), -- 添加檢查約束
item_price  MONEY    NOT NULL,
);

-- 檢查名為gender的列只包含M或F鲸伴，可編寫如下的ALTER TABLE語句:
ADD CONSTRAINT CHECK (gender LIKE '[MF]')

2. 索引

索引用來排序數(shù)據(jù)以加快搜索和排序操作的速度府蔗。
數(shù)據(jù)庫索引的作用也一樣。主鍵數(shù)據(jù)總是排序的汞窗，這是DBMS的工作姓赤。因此，按主鍵檢索特定行總是一種快速有效的操作仲吏。
解決方法是使用索引不铆。可以在一個(gè)或多個(gè)列上定義索引裹唆，使DBMS保存其內(nèi)容的一個(gè)排過序的列表誓斥。在定義了索引后，DBMS以使用書的索引 類似的方法使用它许帐。DBMS搜索排過序的索引劳坑，找出匹配的位置，然后檢索這些行

在開始創(chuàng)建索引前成畦，應(yīng)該記住以下內(nèi)容:

• 索引改善檢索操作的性能距芬，但降低了數(shù)據(jù)插入、修改和刪除的性能循帐。在執(zhí)行這些操作時(shí)框仔，DBMS必須動(dòng)態(tài)地更新索引。
• 索引數(shù)據(jù)可能要占用大量的存儲(chǔ)空間拄养。
• 并非所有數(shù)據(jù)都適合做索引离斩。取值不多的數(shù)據(jù)(如州)不如具有更多可能值的數(shù)據(jù)(如姓或名)，能通過索引得到那么多的好處瘪匿。
• 索引用于數(shù)據(jù)過濾和數(shù)據(jù)排序捐腿。如果你經(jīng)常以某種特定的順序排序數(shù)據(jù)，則該數(shù)據(jù)可能適合做索引柿顶。
• 可以在索引中定義多個(gè)列(例如茄袖，州加上城市)。這樣的索引僅在以州加城市的順序排序時(shí)有用嘁锯。如果想按城市排序宪祥，則這種索引沒有用處。

索引用CREATE INDEX語句創(chuàng)建(不同DBMS創(chuàng)建索引的語句變化很大)

--索引必須唯一命名家乘。這里的索引名prod_name_ind在關(guān)鍵字CREATE INDEX之后定義蝗羊。
-- ON用來指定被索引的表，而索引中包含的列(此例中僅有一 列)在表名后的圓括號(hào)中給出仁锯。
CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);

3. 觸發(fā)器

觸發(fā)器是特殊的存儲(chǔ)過程耀找，它在特定的數(shù)據(jù)庫活動(dòng)發(fā)生時(shí)自動(dòng)執(zhí)行。觸發(fā)器可以與特定表上的INSERT、UPDATE和DELETE操作(或組合)相關(guān)聯(lián)野芒。

觸發(fā)器內(nèi)的代碼具有以下數(shù)據(jù)的訪問權(quán):

• INSERT操作中的所有新數(shù)據(jù);
• UPDATE操作中的所有新數(shù)據(jù)和舊數(shù)據(jù);
• DELETE操作中刪除的數(shù)據(jù)蓄愁。

-- SQL Server
-- 對(duì)所有INSERT和UPDATE操作，將Customers表中的cust_state列轉(zhuǎn)換為大寫
CREATE TRIGGER customer_state
ON Customers
FOR INSERT, UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id   inserted.cust_id;

提示:約束比觸發(fā)器更快
一般來說狞悲，約束的處理比觸發(fā)器快撮抓，因此在可能的時(shí)候，應(yīng)該盡量使用約束摇锋。

-----------------------------------------大部分內(nèi)容來自《sql必知必會(huì)》