1 數(shù)據(jù)庫語言

DBMS提供操作命令和語言，使用戶能夠?qū)?shù)據(jù)庫進(jìn)行各式各樣的操作，例如查詢、增、刪、改數(shù)據(jù)，定義、修改數(shù)據(jù)模式等

——— >這就構(gòu)成了用戶和數(shù)據(jù)庫的接口。

DBMS所提供的語言一般局限于對數(shù)據(jù)庫的操作，有別于計算完備的程序設(shè)計語言，稱為數(shù)據(jù)庫語言（database language）。

1.1 數(shù)據(jù)庫語言分類

數(shù)據(jù)庫語言一般分為這幾種

1.1.1 形式化查詢語言（Query Language ）

本章的重點SQL就是這一類語言，它有嚴(yán)格的語法和文法

1.1.2?表格查詢語言（Tabular Query Language ）

用填表的方式描述查詢需求

我們給一個關(guān)系的名字（student），DBMS自動把屬性填到表里面去。然后用戶往表里面填需求。

在上圖中，比如用戶填的P，表示打印（顯示）操作；填的IS，表示找尋所有學(xué)院是IS學(xué)院的學(xué)生

1.1.3? 圖像查詢語言（Graphic Query Language）

每一個方框代表一種數(shù)據(jù)結(jié)構(gòu)

找出數(shù)據(jù)庫中教師的名字和他們的年齡（青色表中段）。其中教師的職位是教授（左綠色表）、年齡大于45歲（青色表下段）、學(xué)院是計算機(jī)學(xué)院（右綠色表）

1.2?對于關(guān)系數(shù)據(jù)模型的查詢語言

關(guān)系數(shù)據(jù)模型的查詢語言是基于關(guān)系代數(shù)或關(guān)系演算的理論基礎(chǔ)，進(jìn)行開發(fā)的。

SQL基于關(guān)系演算，是一種非過程化的查詢語言（回憶一下關(guān)系演算：用布爾公式表述結(jié)果應(yīng)該滿足的條件）【數(shù)據(jù)庫筆記——數(shù)據(jù)模型_劉文巾的博客-CSDN博客】

***需要注意一點，查詢語言不是編程語言。因為它不是圖靈完備的，沒有邏輯編程能力。

QL（query language）只支持?jǐn)?shù)據(jù)的查詢，不能編程（如果要編程，就需要和別的語言結(jié)合、嵌入。目前常用方法是將數(shù)據(jù)庫語言嵌入到一種高級程序設(shè)計語言中【如C，后文會講】。這種高級程序設(shè)計語言【C語言】稱為數(shù)據(jù)庫語言的宿主語言。）

1.3 SQL的四個子語言

數(shù)據(jù)定義語言 Data Definition Language (DDL)

????????用來定義、刪除、修改數(shù)據(jù)模式（表、視圖。。。）

查詢語言 Query Language

????????重中之重，就是select語句及其各種子句

數(shù)據(jù)操縱語言Data Manipulation Language (DML)

????????插入、刪除、更新數(shù)據(jù)庫中的數(shù)據(jù)

數(shù)據(jù)控制語言Data Control Language (DCL)

????????控制、授權(quán)用戶對數(shù)據(jù)的訪問權(quán)限

2 SQL

2.1 SQL中的基本概念

基表：一個基表就是真實存在磁盤上的一個關(guān)系數(shù)據(jù)結(jié)構(gòu)

視圖：又叫作虛表，根據(jù)基表通過映射和計算得到的虛表。不是真實存儲在磁盤上的數(shù)據(jù)，邏輯意義上的表

主鍵primary key? 外鍵 foreign key： 同數(shù)據(jù)模型

2.2 SQL中的保留字

NULL——空值，由于引入了NULL，所以SQL是三值邏輯而不是二值邏輯（真、假、不知道）

UNIQUE——創(chuàng)建表的時候，說明一個屬性值是否允許重復(fù)值

DEFAULT——為數(shù)據(jù)庫某一張表的某一個屬性值指定缺省值

CHECK——定義一張表的時候，我們會定義一些完整性約束。之后向數(shù)據(jù)庫插入數(shù)據(jù)的時候，系統(tǒng)會check這個要插入的數(shù)據(jù)是否符合約束條件

2.3 后文大部分SQL案例使用的數(shù)據(jù)表格

依舊是水手信息S1 S2、船只信息B1 、船只預(yù)定信息R1

2.4 SQL基本架構(gòu)

target——目標(biāo)列表

distinct——可缺省，加了之后，要求系統(tǒng)對查詢結(jié)果消除重復(fù)元素，如果不加這個關(guān)鍵字的話，系統(tǒng)不會主動消除重復(fù)元素【這個類似于上一章的投影，做投影的時候不會主動刪除重復(fù)元素，用戶要求（比如加distinct關(guān)鍵字）后，才會刪除】

from——查詢所涉及的表

qualification——查詢結(jié)果需要滿足的布爾表達(dá)式

聯(lián)想到關(guān)系演算（數(shù)據(jù)庫筆記——數(shù)據(jù)模型_劉文巾的博客-CSDN博客）：

target-list就是關(guān)系演算定義中 '|'之前的部分.

relation-list就是這個元組應(yīng)該要屬于的某個關(guān)系

qualifications就是關(guān)系演算定義中 '|'后面的部分

2.5 系統(tǒng)執(zhí)行一條SQL語句的概念性方法

這是一種naive的實現(xiàn)方法，實際的數(shù)據(jù)庫會根據(jù)所存儲和所操作的數(shù)據(jù)，以及不同的應(yīng)用而改變一些流程

1 ) 計算所有在relation-list中，也就是本次查詢涉及到的表的笛卡爾乘積

2 ) 刪去所有不滿足qualifications的元組

3 ) 刪去所有不在target-list中的屬性

4 )? 如果有distinct關(guān)鍵字，那么就刪除所有重復(fù)的元組

3 簡單的SQL例子

3.1 兩張表聯(lián)立

比如我們要找定了103號船的水手的名字

這時候需要水手信息的表格S和預(yù)定船信息的表格R聯(lián)立。

讓兩個表聯(lián)系起來的條件是S.sid=R.sid

這里的S和R是數(shù)據(jù)庫的別名。

SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103

按照前面naive的思路。我們進(jìn)行這個SQL查詢的時候，會對R和S先進(jìn)性笛卡爾乘積。得到一張大表，然后找滿足條件的元組。截取需要的屬性。

3.1.1 別名

在不引起混淆的情況下屬性對應(yīng)的表格的名字可以不出現(xiàn)

！！！但是出于規(guī)范的考慮，還是建議寫上！！！

SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND bid=103 SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103

以上兩個在語法和語義上也是可以的。但是為了規(guī)范起見，還是建議使用3.1中的SQL編程習(xí)慣。

3.2 distinct的使用

我們現(xiàn)在需要完成一個這樣的查詢：

我們現(xiàn)在要找一個至少預(yù)定了一艘船的水手。

SQL實現(xiàn)如下：

SELECT S.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid

如果我們只是要水手的id的話，那么完全可以不用引入S這個表格，這里加上S表格，是和后面的SQL和查詢要求相對應(yīng)的。

此時，我們在SELECT后面加或者不加DISTINCT關(guān)鍵字，在語義上不會有什么異同（因為預(yù)定了水手的數(shù)量不會因為加了distinct語句而發(fā)生什么改變）。

如果我們是要水手的名字呢？直接S.sid改成S.sname就可以了。

此時我們加或者不加DISTINCT關(guān)鍵字，有什么區(qū)別嗎？有的，如果水手出現(xiàn)重名的話，加了DISTINCT關(guān)鍵字之后可能水手的數(shù)量就變少了（幾個重名的水手算成了一個人）

3.3 新屬性的命名

SELECT S.age, age1=S.age-5, 2*S.age AS age2 FROM Sailors S WHERE S.sname LIKE ‘B_%B’

第一行的后面兩個表達(dá)式（“as”和“=”）表示我們查詢的結(jié)果所需要填入的屬性名（給計算得到的新屬性賦予名字的兩種方法）

SQL用LIKE關(guān)鍵詞進(jìn)行模糊匹配，_代表匹配一個字符，%代表匹配0個或者更多字符（這里的意思是首位和末尾都是大寫B(tài)，且整個字符串至少長度為三個字符的水手名字）

3.4 連接詞的使用

如果我們要找一個水手，預(yù)定了一艘紅船或者一艘綠船：

SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’)

如果我們的查詢只是需要水手的id的話，在這里我們只需要Boats和Reserves兩張表就可以了，這里我們加Sailors這張表，是為了和后面的對照SQL相對應(yīng)。

當(dāng)然，上面這個查詢我們也可以用Union集合并的方式寫這個查詢

SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ UNION SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’

但如果我們要找的是，一個定了一條紅船以及一條綠船的水手呢？

此時我們不能簡單地把上面的"OR"直接換成"AND"。因為一條船只可能為一個顏色，不可能即使紅色又是綠色。

我們可以用自聯(lián)結(jié)的方式表達(dá)這個查詢，Boats和Reserves出現(xiàn)兩次，類似于一個自聯(lián)結(jié)（Boats和對應(yīng)的Reserves是靠bid聯(lián)系起來的，Reserves之間靠sid相關(guān)聯(lián)）

SELECT S.sid FROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2 WHERE S.sid=R1.sid AND R1.bid=B1.bid AND S.sid=R2.sid AND R2.bid=B2.bidAND B1.color=‘red’ AND B2.color=‘green’

幾張表之間的關(guān)聯(lián)如下

B1——(bid)——R1——(sid)——R2——(bid)——B2

? ? ? ? ? ? ? ? ? ? ? ? ? ?|-----（sid）----->S

當(dāng)然，我們也可以用INTERSECT集合交來表示這個查詢（這個就是簡單地把前面的UNION換成INTERSECT就可以了）

SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ INTERSECT SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’

3.5 嵌套查詢

3.5.1 互相無關(guān)聯(lián)的嵌套查詢

在3.1 節(jié)中，我們用兩個表之間的相關(guān)聯(lián)來實現(xiàn)了“找預(yù)定了103號船的水手的名字”這一個查詢。

當(dāng)時我們使用的SQL是這樣的：兩張表格用sid相連接

SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103

我們現(xiàn)在也可以用嵌套來完成這個查詢

SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sidFROM Reserves RWHERE R.bid=103)

嵌套子查詢中的SELECT語句在外層查詢處理之前求解。

我們先在Reserves表內(nèi)找到預(yù)定了103號船的所有水手的id（內(nèi)層查詢）。

然后在S表查詢這些id對應(yīng)的水手的名字。

當(dāng)查詢涉及多個關(guān)系時，用嵌套查詢逐次求解層次分明，具有結(jié)構(gòu)程序設(shè)計特點。

同時，嵌套查詢的執(zhí)行效率也比聯(lián)接查詢的效率高。（因為連接查詢需要笛卡爾乘積這一步操作）

如果我們要找的是沒有預(yù)定103號船的水手，那么我們用NOT IN代替IN即可：

SELECT S.sname FROM Sailors S WHERE S.sid NOT IN (SELECT R.sidFROM Reserves RWHERE R.bid=103)

3.5.2 互相關(guān)聯(lián)的嵌套

下面這個例子是互相關(guān)聯(lián)的嵌套

SELECT S.sname FROM Sailors S WHERE EXISTS (SELECT *FROM Reserves RWHERE R.bid=103 AND S.sid=R.sid)

此時，外循環(huán)S的每一個sid，都需要帶入內(nèi)循環(huán)去進(jìn)行一次查詢，如果滿足內(nèi)循環(huán)的條件，那么這個sid就是一個我們要查詢的內(nèi)容。

這條嵌套查詢語句和上面無關(guān)聯(lián)嵌套查詢語句在語義上是等價的。但是上面那個無關(guān)聯(lián)嵌套查詢語句，內(nèi)層循環(huán)只需要執(zhí)行一次。而這里外層查詢的S有幾個sid，內(nèi)層循環(huán)就需要進(jìn)行幾次。效率是很低的。

所以同樣的SQL邏輯，不同的設(shè)計方法，效率會大大不同。

3.5.3 嵌套的一個比較繞的例子

怎么表達(dá)一個只預(yù)定了一次103號船的水手呢？（預(yù)定一次的意思是只預(yù)定了一天的船）

第一種思路是使用NOT IN 關(guān)鍵字

SELECT S.name FROM Sailors S Reserve R WHERE R.sid=S.sid AND R.bid=103 AND S.sid NOT IN ( SELECT R1.sidFROM Reserve R1 WHERE R1.bid=103 AND R1.day <>R.day)

外層循環(huán)時先找哪些定了103號船的水手

內(nèi)層循環(huán)是什么意思呢？

字面理解是這樣的：從另一張Reserve表中找水手的id，這一條元組中的這個水手也定了103號船。同時預(yù)定這條船的日期和外層循環(huán)預(yù)定了這條船的日期不一樣。

翻譯一下，內(nèi)層循環(huán)就是找至少定了兩次103號船的水手的id集合。

那么外層循環(huán)where里面說的是sid NOT IN 內(nèi)存循環(huán)，不在內(nèi)存循環(huán)查詢到的集合中。

也就是說。滿足條件的水手不在“至少定了兩次103號船的水手”集合中，同時這個水手又預(yù)定了103號船。

兩個取一個交際，就是只預(yù)定了一次103號船的水手的id

第二種思路是使用EXISTS 和NOT EXISTS關(guān)鍵字

SELECT S.name FROM Sailors S WHERE EXISTS (SELECT *FROM Resreves R1WHERE R1.bid=103 AND S.sid=R2.sid )ANDNOT EXISTS (SELECT *FROM Resreves R2WHERE R2.bid=103 AND S.sid=R2.sid AND R1.day <> R2.day )

思路和第一種思路是一樣的 “預(yù)定了103號船”+不在”至少預(yù)定了兩次103號船“。這兩個條件相疊加。得到我們要的查詢結(jié)果。

3.5.4 關(guān)系嵌套+自聯(lián)結(jié)

比如我們要找只被一個水手預(yù)定過的船：

SELECT bid FROM Reserves R1 WHERE bid NOT IN (SELECT bidFROM Reserves R2WHERE R2.sid <> R1.sid)

對R1里面的每條記錄，我們把它的sid帶進(jìn)去

子查詢的意思是 R2中所有 不被這個R1.sid而被其他sid預(yù)定的船的編號

不在子查詢中的船，就是只被這個R1.sid預(yù)定的船

3.5.5 用嵌套的方法解決 3.4 “同時定紅船以及綠船”的問題

我們再回來看找一個同時選了紅船和綠船的水手這個查詢

之前我們是用自聯(lián)結(jié)、INTERSECT來表示的

我們現(xiàn)在用嵌套的方式來表達(dá)：

SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’AND S.sid IN (SELECT S2.sidFROM Sailors S2, Boats B2, Reserves R2WHERE S2.sid=R2.sid AND R2.bid=B2.bidAND B2.color=‘green’)

外循環(huán)的意思是找到所有預(yù)約了紅船的水手的id，同時這個id是滿足內(nèi)循環(huán)條件的元組里面的id。

內(nèi)循環(huán)的意思是找到所有預(yù)約了綠船的水手的id

3.5.4?第3.4節(jié) “同時定紅船以及綠船”的問題 進(jìn)一步的思考

在3.4和3.5.5中，我們查找的是滿足條件的水手的id。如果我們需要查的是水手的姓名呢？

首先，自聯(lián)結(jié)的方法，直接把sid換成sname就可以了，別的都不動

SELECT S.sname FROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2 WHERE S.sid=R1.sid AND R1.bid=B1.bid AND S.sid=R2.sid AND R2.bid=B2.bidAND B1.color=‘red’ AND B2.color=‘green’

然后嵌套的方法，也是直接把sid換成sname就可以了

SELECT S.sname FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’AND S.sid IN (SELECT S2.sidFROM Sailors S2, Boats B2, Reserves R2WHERE S2.sid=R2.sid AND R2.bid=B2.bidAND B2.color=‘green’)

但是對于INTERSECT的實現(xiàn)方法，可以直接將sid換成sname嘛？

這種方法是有一定的風(fēng)險的。和3.2節(jié)加不加DISTINCT關(guān)鍵字一樣。如果水手用重名的話，就有可能出現(xiàn)問題（比如一個叫Bob的只訂了紅船，另一個叫Bob的只訂了綠船。但是兩個查詢分別返回了Bob，INTERSECT之后Bob是算在最終的結(jié)果里面的）

如果一定要用INTERSECT呢？我想到的辦法就是再加一層INTERSECT：

SELECT SX.sname FROM Sailors SX WHERE SX.sid IN(SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bidAND B.color=‘red’INTERSECTSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bidAND B.color=‘green’)

內(nèi)層循環(huán)就是原來3.4節(jié)中的INTERSECT部分

3.5.5 FROM語句的嵌套

這里會用到一丟丟第四節(jié)的東西。

我們現(xiàn)在需要找找平均年齡最小的rating。

首先 ，聚集函數(shù)不能嵌套，所以以下寫法是錯誤的：

SELECT S.rating FROM Sailors S WHERE S.age = (SELECT MIN (AVG (S2.age))FROM Sailors S2)

那么我們應(yīng)該怎么辦呢？這時候我們可以對FROM語句使用嵌套：

這里用到了第四節(jié)GROUPBY的內(nèi)容

SELECT Temp.rating FROM (SELECT S.rating, AVG (S.age) AS avgageFROM Sailors SGROUP BY S.rating) AS Temp WHERE Temp.avgage = (SELECT MIN (Temp.avgage)FROM Temp)

Temp是一個新的表，表的內(nèi)容是每一個rating和rating對應(yīng)的平均年齡的一一對應(yīng)

WHERE中就是找等于最小平均年齡的那一組的rating

3.6 SQL 連接詞

除了 NOT IN, NOT EXISTS， NOT UNIQUE之外，還可以有 op ANY（比某些op） op ALL （比所有op） （op是＜，＞，＝，≤，≥，≠中的一個）

比如我們要找比某些叫Horatio等級高的水手：

SELECT * FROM Sailors S WHERE S.rating > ANY (SELECT S2.ratingFROM Sailors S2WHERE S2.sname=‘Horatio’)

子查詢的意思是所有叫Horatio的水手的等級。

如果要找的是比所有叫Horatio等級高的水手，那么就把ANY替換成ALL就可以了，即：

SELECT * FROM Sailors S WHERE S.rating > ALL (SELECT S2.ratingFROM Sailors S2WHERE S2.sname=‘Horatio’)

3.7 SQL除法

3.7.1 關(guān)系代數(shù)除法回憶

回顧一下關(guān)系代數(shù)中的除法（數(shù)據(jù)庫筆記——數(shù)據(jù)模型_劉文巾的博客-CSDN博客）

和關(guān)系代數(shù)里面用基礎(chǔ)運(yùn)算符表達(dá)除法一樣，我們也可以用”否定之否定“來表達(dá)除法

回顧一下關(guān)系代數(shù)中對于除法的表述（數(shù)據(jù)庫筆記——數(shù)據(jù)模型_劉文巾的博客-CSDN博客）

3.7.2 SQL除法舉例

找到預(yù)定了所有船的水手

SELECT S.sname FROM Sailors S WHERE NOT EXISTS ((SELECT B.bidFROM Boats B)EXCEPT(SELECT R.bidFROM Reserves RWHERE R.sid=S.sid))

子查詢里面 第一個子句：找到Boat里面所有的船的編號

子查詢里面第二個字句? 找到所有當(dāng)前外查詢遍歷到的sid 預(yù)定的船的編號

兩個子句一減，就是 所有當(dāng)前外查詢遍歷到的sid 沒有預(yù)定的船的編號

然后外查詢的條件是NOT EXIST，即沒有 沒有預(yù)定的船，也就是所有的船都被預(yù)定了。

如果我們不用EXCEPT呢？

SELECT S.sname FROM Sailors S WHERE NOT EXISTS (SELECT B.bidFROM Boats B WHERE NOT EXISTS (SELECT R.bidFROM Reserves RWHERE R.bid=B.bidAND R.sid=S.sid))

這個比較復(fù)雜，我們一層一層看：

最外層：我們從Sailors數(shù)據(jù)庫中尋找水手的名字，這些水手不滿足一些條件

中間層：水手不滿足什么條件呢？ 從Boats數(shù)據(jù)庫中找船的id，這些船不滿足一些條件

最里層：這些船不滿足什么條件呢？條件是： 在預(yù)約數(shù)據(jù)集Reserves里面，這個水手定了這艘船

然后我們從里向外分析：

船不滿足”水手定了這艘船“——水手沒有定這艘船

水手不滿足”水手沒有定這艘船“——水手定了所有船

3.8 SQL 排序

ORDER BY 關(guān)鍵字用于對結(jié)果集按照一個列或者多個列進(jìn)行排序。

ORDER BY 關(guān)鍵字默認(rèn)按照升序?qū)τ涗涍M(jìn)行排序。如果需要按照降序?qū)τ涗涍M(jìn)行排序，可以使用 DESC 關(guān)鍵字。

以上面這個表為例

SELECT * FROM Websites ORDER BY alexa

?對結(jié)果按照alexa排序，結(jié)果為：

3.8.2 倒排序

倒排序就是在上面的基礎(chǔ)上，加一個DESC即可

SELECT * FROM Websites ORDER BY alexa DESC

?結(jié)果為：

3.8.3 多列排序

SELECT * FROM Websites ORDER BY country,alexa

?

?上述SQL結(jié)果為：

DESC或者ASC只對它緊跟著的第一個列名有效，其他的不受影響

3.9?其他SQL命令

3.9.1? LIMIT

選取頭幾條記錄

SELECT * FROM Students LIMIT 2

上面一條語句是從Students這個表中選取頭兩條記錄

LIMIT y 分句表示——?讀取 y 條數(shù)據(jù)
LIMIT x, y 分句表示——跳過 x 條數(shù)據(jù)，讀取 y 條數(shù)據(jù)
LIMIT y offset x 分句表示——?跳過 x 條數(shù)據(jù)，讀取 y 條數(shù)據(jù)
LIMIT n?等價于?limit 0,n

3.9.2 TOP PERCENT

選取頭百分之多少的記錄

SELECT TOP 50 PERCENT * FROM Students

?上面一條語句是從Students這個表中選取前百分之五十的記錄

3.9.3 MINUS

使用方式和前面的UNION，INTERSECTION一樣，只不過這邊是集合差的意思

3.9.4 IFNULL

IFNULL() 函數(shù)用于判斷第一個表達(dá)式是否為 NULL，如果為 NULL 則返回第二個參數(shù)的值，如果不為 NULL 則返回第一個參數(shù)的值。

IFNULL(expression, alt_value) 參數(shù)描述

expression	必須，要測試是否為NULL的值
alt_value	必須，expression 表達(dá)式為 NULL 時返回的值

3.10 SQL連接

本小節(jié)需要用到的表：

? ??

3.10.1 （INNER） JOIN

內(nèi)連接，或等值連接

獲取兩個表中字段匹配關(guān)系的記錄

SELECT a.role_id, a.occupation, a.camp, b.mount_name FROM roles a INNER JOIN mount_info b ON a.role_id = b.role_id;

對于inner join 這兩個語句是一樣的

SELECT a.role_id, a.occupation, a.camp, b.mount_name FROM roles a, mount_info b WHERE a.role_id = b.role_id;

查詢結(jié)果為：

3.10.2 LEFT JOIN

?? ??

LEFT JOIN 會讀取左側(cè)數(shù)據(jù)表的全部數(shù)據(jù)，即使右側(cè)表中無對應(yīng)數(shù)據(jù)。

SELECT a.role_id, a.occupation, a.camp, b.mount_name FROM roles a LEFT JOIN mount_info b ON a.role_id = b.role_id;

?查詢結(jié)果為：

3.10.3?RIGHT JOIN

?? ??

RIGHT JOIN 會讀取右側(cè)數(shù)據(jù)表的全部數(shù)據(jù)，即便左側(cè)表無對應(yīng)數(shù)據(jù)。

SELECT a.role_id, a.occupation, a.camp, b.mount_name FROM roles a RIGHT JOIN mount_info b ON a.role_id = b.role_id;

查詢結(jié)果為：

3.10.4 連接語句 ON 和WHERE的區(qū)別

參考SQL中的ON和WHERE的區(qū)別_liitdar的博客-CSDN博客

以 LEFT JOIN 為例：在使用 LEFT JOIN 時，ON 和 WHERE 過濾條件的區(qū)別如下：

——ON 條件是在生成臨時表時使用的條件，它不管 ON 中的條件是否為真，都會返回左邊表中的記錄；
——WHERE 條件是在臨時表已經(jīng)生成后，對臨時表進(jìn)行的過濾條件。因為此時已經(jīng)沒有 LEFT JOIN 的含義（必須返回左側(cè)表的記錄）了，所以如果 WHERE 條件不為真的記錄就會被過濾掉。
?

?? ??

以下面的查詢?yōu)槔?/p> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id) WHERE mount_info.mount_name="sheep";

查詢結(jié)果為

分析一下上述SQL語句的執(zhí)行過程：

1. 首先，根據(jù)ON過濾條件“roles.role_id = mount_info.role_id”（相當(dāng)于去掉后面的WHERE過濾條件），生成中間表，如下：

2. 然后，針對上面生成的中間表，再根據(jù)WHERE過濾條件“mount_info.mount_name="sheep"”，產(chǎn)生最終查詢結(jié)果，如下：

?3.10.5 JOIN總結(jié)

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ????????????????? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??

3.11 IF

IF(sex = 'f', 'm', 'f')

這條語句表示：如果sex是'f'的話，那么返回'm'，否則返回'f'

4 聚集計算

聚集計算有以上幾個，分別對應(yīng)了元組的個數(shù)、某一個屬性（不同）值數(shù)量、某一個屬性（不同）值的和、某一個屬性（不同）值的平均數(shù)、某一個屬性的最大值、某一個屬性的最小值。

注：以count為例，count(*)會去計算有NULL的行。count+字段只考慮 非NULL的部分

4.1 聚集計算舉例說明

4.1.1 水手個數(shù)

SELECT COUNT (*) FROM Sailors S

4.1.2??叫bob的水手有幾個不同的等級

SELECT COUNT (DISTINCT S.rating) FROM Sailors S WHERE S.sname=‘Bob’

4.1.3 級別為10的水手的平均年齡

SELECT AVG (S.age) FROM Sailors S WHERE S.rating=10

4.1.4 級別為10的水手的不同年齡的平均值

SELECT AVG (DISTINCT S.age) FROM Sailors S WHERE S.rating=10

4.1.5 級別最高的那些水手的名字

SELECT S.sname FROM Sailors S WHERE S.rating= (SELECT MAX(S2.rating)FROM Sailors S2)

內(nèi)循環(huán)是求水手的最高等級

4.2 GROUPING

4.2.1 grouping語句的引出

我們?nèi)绻枰獙?shù)據(jù)庫每一個分組計算出一個值，那么我們就需要用到grouping.

GROUP BY子句按列值分組。列值相同的分為一組。當(dāng)其后有多個列名時，則先按第一列名分組，再按第二列名在組中分組，直到GROUP子句指名的列都具有相同值的基本組。

比如，我們要找每個等級最年輕的水手

首先，我們不知道水手有幾個等級、這幾個等級的值都是多少

其次，就算我們知道了水手的等級，我們也不太好用我們已經(jīng)介紹了的這些sql語句實現(xiàn)

比如我們知道了rating是從1到10遞增的，我們可以這么寫嗎？

答案是不行的，因為我們在1.2節(jié)說過“QL（query language）只支持?jǐn)?shù)據(jù)的查詢，不能編程”。SQL語言無法實現(xiàn)for操作。

4.2.2?group by 和having語句格式

其中，規(guī)定target-list 中的屬性必須是grouping-list中的子集。

sql要求 在selct字句和having出現(xiàn)的屬性必須是分組屬性集grouping-list中的子集

where語句篩選元組，having語句篩選組

4.2.3 GROUPING語句的邏輯流程

1）先對 from語句里面的relation-list做笛卡爾乘積

2）?where 子句對笛卡爾乘積得到的大表做篩選

3） group by 分組，將group by語句屬性值相等的元組放到同一分組

4） 按照having語句的條件對組做篩選

5）合格的組，按照select做相應(yīng)的計算，每一個組得到一個結(jié)果

4.2.4 grouping 語句舉例

“Find age of the youngest sailor with age ≥ 18, for each rating with at least 2 such sailors”

SELECT S.rating, MIN (S.age) AS minage FROM Sailors S WHERE S.age >= 18 GROUP BY S.rating HAVING COUNT (*) > 1

邏輯步驟：

1） 先篩選年齡大于18的水手（where條件）

2）然后對rating排序，然后相同rating一組

3）最后就是having的條件 count(*)＞1

Find age of the youngest sailor with age > 18, for each rating with at least 2 sailors (of any age)

剛才的要求是：

“Find age of the youngest sailor with age ≥ 18, for each rating with at least 2 such sailors”

和前一個的區(qū)別在于，前一個是18+的人數(shù)大于等于2作為組別的篩選條件。現(xiàn)在是組別所有人的人數(shù)大于等于2。

這里我們不能像之前那樣直接 having 語句里面count(*) 。因為where已經(jīng)把小宇等于18的人刪掉了。

這時候我們需要having的嵌套語句：

SELECT S.rating, MIN (S.age) FROM Sailors S WHERE S.age > 18 GROUP BY S.rating HAVING 1 < (SELECT COUNT (*)FROM Sailors S2WHERE S2.rating = S.rating)

4.2.4 “是grouping-list的子集”約束條件舉例

? ? ? ? 關(guān)系型數(shù)據(jù)庫不能從語義上檢查判斷selct和having 每一個屬性在每一個分組中值是否單一，所以才規(guī)定了“在selct字句和having出現(xiàn)的屬性必須是分組屬性集grouping-list中的子集”這個約束條件。

對每艘紅船，找他的預(yù)定次數(shù)

For each red boat, find the number of reservations for this boat

SELECT B.bid, COUNT (*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’ GROUP BY B.bid

上面這個肯定是對的。但如果B.color='red'不在where語句，二是在Having里面，那么可不可以呢？就是以下這樣的：

SELECT B.bid, COUNT (*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid GROUP BY B.bid HAVING B.color=‘red’

首先，邏輯上是沒有問題的，但是SQL會報錯

為什么呢？就是前面說的”“在selct字句和having出現(xiàn)的屬性必須是分組屬性集grouping-list中的子集"..HAVING語句中的屬性color不在分組屬性集bid中，所以SQL會自動報錯。哪怕語義邏輯上是正確的。

如果我們一定不能在WHERE中使用color='red'呢？

第一種方法是HAVING中使用嵌套，然后HAVING語句中的屬性值也只是bid

SELECT B.bid, COUNT(*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid GROUP BY B.bid HAVING B.bid in SELECT B2.bid FROM BOATS B2WHERE B2.color='red'

第二種方法是在GROUP BY中使用color='red'

SELECT B.bid, COUNT(*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid GROUP BY B.bid AND B.color='red'

5 CAST語句舉例

類似于強(qiáng)制類型轉(zhuǎn)換

5.1 CAST語句的幾個作用

5.1.1 匹配函數(shù)的參數(shù)

比如截取字符串，設(shè)置首末位置的下標(biāo)為整數(shù)

5.1.2 轉(zhuǎn)換精度

Decimal (5,0) 表示長度為5位 小數(shù)點后0位

5.1.3 對空值賦以類型

假設(shè)我們有這樣兩個表：

我們想到一張students和soldier的外并，但我們只能用并操作來完成。

那我們就需要考慮并兼容的問題，并兼容的話就需要解決NULL空值的情況

CREATE VIEW prospects (name, school, service) ASSELECT name, school, CAST(NULL AS Varchar(20))FROM Students UNIONSELECT name, CAST(NULL AS Varchar(20)), serviceFROM Soldiers ;

VIEW，視圖，也就是虛表。我們根據(jù)查詢結(jié)果臨時算出來的。

prospects 括號里面的是視圖的模式。

students和solders各補(bǔ)了一列，以滿足并兼容。

CAST 語句的意思是，把NULL轉(zhuǎn)換成最長20的字符串。

6?CASE語句

很多數(shù)據(jù)庫為了節(jié)省空間，對屬性進(jìn)行了編碼。但是用戶使用的時候，看編碼肯定不方便。于是我們需要把編碼再還原成語義信息。這時候就可以用到CASE語句。

我們現(xiàn)在有這樣一個數(shù)據(jù)庫：

其中status是officer現(xiàn)在的狀態(tài)。但是數(shù)據(jù)庫中為了節(jié)省空間，并沒有存儲狀態(tài)實際的字符串，而是用數(shù)字表示的。

但是用戶看的時候，officer的狀態(tài)是數(shù)字肯定是不方便的。于是我們需要再用戶使用端再把數(shù)字轉(zhuǎn)換回字符串：

SELECT name, CASE statusWHEN 1 THEN ‘Active Duty’WHEN 2 THEN ‘Reserve’WHEN 3 THEN ‘Special Assignment’WHEN 4 THEN ‘Retired’ELSE ‘Unknown’END AS status FROM Officers ;

我們提取name 和status兩個屬性。

其中status屬性我們進(jìn)行轉(zhuǎn)換，如果是1，那么在我們新的表中，status為‘a(chǎn)ctive duty’；如果是2，那么在我們新的表中，status為‘Reserve’。。。以此類推。那么此時存在Officers里面的status屬性就不是數(shù)字，是字符串了。

我們比較一下以下兩條SQL語句：

SELECT type, CASEWHEN sum(hours_used)>0 THEN sum(accidents)/sum(hours_used)ELSE NULLEND AS accident_rate FROM Machines GROUP BY type;SELECT type, sum(accidents)/sum(hours_used) FROM Machines GROUP BY type HAVING sum(hours_used)>0;

表達(dá)的語義都是每一組的平均出故障時間比例。

但是不同的是，如果我們的數(shù)據(jù)里面有沒有使用過的機(jī)器（也就是他們sum(hours_used)為0的機(jī)器），在第一個SQL查詢結(jié)果里面也會出現(xiàn)，值是NULL。

但在第二個SQL查詢里面，就會被HAVING語句篩掉。結(jié)果只出現(xiàn)sum(hours_used)＞0的部分）

7 子查詢

之前的無關(guān)聯(lián)嵌套和有關(guān)聯(lián)嵌套都是子查詢，我們這里系統(tǒng)的整理一下

子查詢一共有三種：

7.1?標(biāo)量子查詢

凡是可以出現(xiàn)一個值的地方，都能是標(biāo)量子查詢

比如我們要查詢平均獎金大于平均薪水的部門：

SELECT d.deptname, d.location FROM dept AS d WHERE (SELECT avg(bonus)FORM empWHERE deptno=d.deptno)> (SELECT avg(salary)FORM empWHERE deptno=d.deptno)

當(dāng)然這里也可以grouping來表示

SELECT d.deptname, d.location FROM dept AS d GROUP BY d.deptno HAVING (SELECT avg(bonus)FROM emp WHERE deptnp =d.deptno)>(SELECT avg(salary)FROM emp WHERE deptnp =d.deptno)

上面的寫法是正確的，下面是錯誤的

SELECT d.deptname, d.location FROM dept AS d GROUP BY d.deptno HAVING avg(bonus) > avg(salary)

錯誤的原因和4.2.4中是一樣的。就是前面說的”“在selct字句和having出現(xiàn)的屬性必須是分組屬性集grouping-list中的子集"..HAVING語句中的兩個屬性不在分組屬性集bid中，所以SQL會自動報錯。哪怕語義邏輯上是正確的。

7.2?表格表達(dá)式

表格表達(dá)式實際上得到的都是視圖（虛表）

eg?每一年入職的員工平均拿到的錢

SELECT startyear, avg(pay) FROM (SELECT name, salay+bonus AS pay, year(startdate) AS startyearFROM emp) AS emp2 GROUP BY startyear;

7.3?公共表表達(dá)式

在特別復(fù)雜的查詢里面，可能某一種查詢不止出現(xiàn)一次。如果我們每次用到這個表的時候，在SQL里面都寫一次這個表的表達(dá)式，也不是不可以，但是得重復(fù)計算，效率偏低。

為了提高效率，我們定義一個公共表表達(dá)式，在select之前定義、計算一次，得到一張視圖。然后在之后的查詢中直接使用即可。

比如我們要去找拿錢最多的部門：

這時候我們要對表格進(jìn)行兩次查詢：第一次查詢拿錢最多的這個值，第二次查詢哪個部門拿的錢數(shù)的等于這個值

WITH payroll (deptno, totalpay) AS(SELECT deptno, sum(salary)+sum(bonus)FROM empGROUP BY deptno) SELECT deptno FROM payroll WHERE totalpay = (SELECT max(totalpay)FROM payroll);

公共表表達(dá)式用WITH進(jìn)行定義

這個公共表表達(dá)式的名字叫payroll，定義是AS之后的部分

比如我們要找，平均薪資一組比另一組多一倍以上的部門對

WITH deptavg (deptno, avgsal) AS(SELECT deptno, avg(salary)FROM empGROUP BY deptno) SELECT d1.deptno, d1.avgsal, d2.deptno, d2.avgsal FROM deptavg AS d1, deptavg AS d2 WHERE d1.avgsal>2*d2.avgsal;

7.3.1?綜合使用公共表達(dá)式和cast語句實現(xiàn)外連接

我們現(xiàn)在有這么兩張表。我們希望能同時找到 這學(xué)期所有開的課和對應(yīng)的老師、這學(xué)期不開的課，這學(xué)期沒有課的老師這三種信息，也就是兩個表的外連接

WITHinnerjoin(name, rank, subject, enrollment) AS(SELECT t.name, t.rank, c.subject, c.enrollmentFROM teachers AS t, courses AS cWHERE t.name=c.teacher AND c.quarter=‘Fall 96’) ,teacher-only(name, rank) AS(SELECT name, rankFROM teachersEXCEPT ALLSELECT name, rankFROM innerjoin) ,course-only(subject, enrollment) AS(SELECT subject, enrollmentFROM coursesEXCEPT ALLSELECT subject, enrollmentFROM innerjoin)SELECT name, rank, subject, enrollment FROM innerjoinUNION ALLSELECT name, rank,CAST (NULL AS Varchar(20)) AS subject,CAST (NULL AS Integer) AS enrollment FROM teacher-onlyUNION ALLSELECT CAST (NULL AS Varchar(20)) AS name,CAST (NULL AS Varchar(20)) AS rank,subject, enrollment FROM course-only ;

interjoin 表示這學(xué)期有老師上課的情況

teacher-only 所有老師減去interjoin里面的老師，就是這學(xué)期沒有開課的老師?

這里使用except all會比except效率高（如果我們的查詢結(jié)果沒有重復(fù)，或者重復(fù)沒有影響，我們可以使用except all；因為except 會先做一次排序，效率不如except all）

course-only 所有課程減去interjoin里面的課程，就是這學(xué)期不開的課

外連接回顧：（數(shù)據(jù)庫筆記——數(shù)據(jù)模型_劉文巾的博客-CSDN博客）

8 遞歸查詢

自己的查詢里面使用了自己的定義

8.1 有結(jié)束條件的遞歸查詢

比如我們有這樣一個表格，manager表示自己的直接上司。

我們希望找到hoover所管轄的員工中薪資大于100000的人

我們不能直接 where manager='HOOVER’ 來判斷這個人是不是hoover的下屬。因為這樣只能判斷hoover的直接管轄的員工，但是直接管轄的員工還有他們管轄的員工，那些人也是算hoover的下屬的。。。。

這時候我們就需要使用遞歸實現(xiàn)了

WITH agents (name, salary) AS((SELECT name, salary --- initial queryFROM FedEmpWHERE manager=?Hoover?)UNION ALL(SELECT f.name, f.salary --- recursive queryFROM agents AS a, FedEmp AS fWHERE f.manager = a.name))SELECT name --- final query FROM agents WHERE salary>100000 ;

agents表格最終達(dá)到的效果是hoover所有下屬以及對應(yīng)的薪水。

initial query指的是hoover的直接下屬。

recursive query的話，就是找現(xiàn)在agents中的員工各自的直接下屬。然后把這些直接下屬放到agents中，再找他們的直接下屬。。。以此循環(huán)。直到最底層員工，然后跳出循環(huán)。

得到表格后，我們再query就很方便了。

8.2 沒有結(jié)束條件的遞歸查詢

我們現(xiàn)在看這樣一個問題

我們有這么幾個機(jī)場，幾個機(jī)場之間有航班相連。我們現(xiàn)在要找從SFO到JFK 開銷最小的航班坐法

數(shù)據(jù)可視化之后的結(jié)果

SQL中是沒有辦法表達(dá)中轉(zhuǎn)的?

我們需要建立一個臨時表。這個臨時表記錄了從SFO中轉(zhuǎn)一次、兩次、三次。。。可能到達(dá)的目的地。（如下表，記錄了我們做一次、兩次、三次航班后的目的地、路徑、開銷）

WITH trips (destination, route, nsegs, totalcost) AS((SELECT destination, CAST(destination AS varchar(20)), 1, costFROM flights --- initial queryWHERE origin=‘SFO’)UNION ALL(SELECT f.destination, --- recursive queryCAST(t.route||’,’||f.destination AS varchar(20)), t.nsegs+1, t.totalcost+f.costFROM trips t, flights fWHERE t.destination=f.originAND f.destination<>’SFO’ --- stopping rule 1AND f.origin<>’JFK’ --- stopping rule 2AND t.nsegs<=3)) --- stopping rule 3SELECT route, totalcost --- final query FROM trips WHERE destination=‘JFK’ AND totalcost= --- lowest cost rule(SELECT min(totalcost)FROM tripsWHERE destination=‘JFK’) ;

||表示字符串連接

我們重點看一下遞歸查找中的結(jié)束條件。

首先'<>'表示不等于 也就終點不能是SFO；

然后起點不能是JFK,

以及最多可以坐三次航班（這可以看作“兜底”。哪怕前面思考的不周到，我們也最多遞歸三次）

最終的查詢結(jié)果為：

9 數(shù)據(jù)操縱語言

主要是三個操作

9.1 insert

將一條元組插入數(shù)據(jù)庫中

VALUE后面是要插入的元組值，其次序 和域應(yīng)與STUDENT的模式定義一致。

INSERT INTO EMPLOYEES VALUES ('Smith', 'John', '1980-06-10', 'Los Angles', 16, 45000);

?把VALUES 后面括號里面的元組插入到INTO后面的數(shù)據(jù)庫中

9.2 delete

把滿足條件（條件為WHERE后面的語句）的元組刪除

DELETE FROM Person WHERE LastName = 'Rasmussen' ;

如果沒有WHERE子句，則刪除指定表中的所有元組，使該表為一空表。（刪除整個表要用 DROP TABLE語句）

9.3 update

把滿足條件（條件為WHERE后面的語句）的元組中的某一些屬性更新（更新方式為SET后面的語句）

UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson';

9.4 創(chuàng)建新表

比如我們要生成一個學(xué)生成績臨時表GRADE，表中包括SNAME，CNO,GRADE三個屬性。

首先定義一個臨時表GRADE：

CREATE TABLE GRADE(SNAME VARCHAR(8) NOT NULL,CNO CHAR(6) NOT NULL,GRADE DEC(4,1) DEFAULT NULL);

其次插入有關(guān)的數(shù)據(jù)：

INSERT INTO GRADE SELECT SNAME, CNO, GRADE FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO

10 視圖總結(jié)

10.1 普通視圖

是一個虛表，用create view 創(chuàng)建的虛表。

它區(qū)別于基表，后者是以某種形式存在磁盤里面的。

普通視圖是在基表的基礎(chǔ)上利用查詢得到的，數(shù)據(jù)庫中只會記錄他們的定義。

普通視圖可以保證數(shù)據(jù)的邏輯獨(dú)立性（按照功能創(chuàng)建普通視圖）。

普通視圖也能保證數(shù)據(jù)的安全性（比如用戶可以看到的數(shù)據(jù)是總數(shù)居的一部分，那么給用戶的數(shù)據(jù)可以是所有屬性的一部分、所有元組的一部分、甚至是加工后的結(jié)果。此時可以保證數(shù)據(jù)的安全性

視圖對應(yīng)的內(nèi)容總是實時、最新的內(nèi)容，并不是視圖定義時對應(yīng)內(nèi)容。這是由于基表隨著更新操作其內(nèi)容在不斷變化，所以視圖對應(yīng)的內(nèi)容也在不斷變化。

10.1.1 普通視圖的更新問題。

早期的SQL中是不允許視圖來更新屬性的，當(dāng)時的視圖是只讀。

但現(xiàn)在的SQL中，只要視圖的屬性可以唯一對應(yīng)基表中的屬性，那么視圖也是可以更新的。相當(dāng)于更新的基表中的屬性.

比如上面這樣兩個普通視圖，第一個就可以更新視圖里面的屬性值，而第二個就不行。

10.1.2 普通視圖的撤銷

使用DROP VIEW 來撤銷

DROP VIEW YoungSailor

10.2 臨時視圖和遞歸查詢

臨時視圖的定義方式with和普通視圖的create view很像，他們的實現(xiàn)也是類似的。

唯一不同的是，臨時視圖的定義也是臨時的，數(shù)據(jù)庫并不會存儲他。當(dāng)查詢語句完成，臨時試圖就被遺棄。

11?嵌入式SQL

我們之前進(jìn)行的，都是一條查詢語句。而SQL不是編程語言，本身不具備程序設(shè)計能力。

那么如果我們要基于數(shù)據(jù)庫進(jìn)行應(yīng)用程序開發(fā)，我們就需要SQL和某些編程語言相結(jié)合。

在這里，我們主要介紹在C語言中使用嵌入式SQL。

11.1 嵌入式SQL的特點

1）所有嵌入在ｃ里面的SQL命令，都以“EXEC SQL"這個開始，以分號結(jié)束。編譯可以用這個來識別這段語句是SQL語言還是C語言

2）使用宿主變量(host variables)在C和數(shù)據(jù)庫之間傳遞數(shù)據(jù)和消息。宿主變量需要以”EXEC SQL“為開頭進(jìn)行定義。

3）在SQL語句中，如果是C語言中的變量，我們需要加上冒號以示區(qū)分SQL（數(shù)據(jù)庫）中的變量屬性

4）在宿主語言（也就是這里的C語言）中，宿主變量正常使用就ok了（和普通變量一樣使用）

5）不能將宿主變量成定義數(shù)組或者結(jié)構(gòu)

6）SQL中有一個通訊區(qū)SQLCA，可以利用這個數(shù)組變量實現(xiàn)C和SQL語言之間信息的交換

7）SQLCA.SQLCODE判斷返回的狀態(tài)（查詢結(jié)果正常與否）

8）使用短整型 indicator（說明符）表示C里面沒有空值（０還是１）　

11.2?宿主變量的定義

EXEC SQL BEGIN DECLARE SECTION; char SNO[7]; char GIVENSNO[7]; char CNO[6]; char GIVENCNO[6]; float GRADE; short GRADEI; /*indicator of GRADE*/ EXEC SQL END DECLARE SECTION;

這里面的GRADEI就是前面11.1第8條說的短整型說明符

11.3 連接數(shù)據(jù)庫

首先要用connect和數(shù)據(jù)庫建立連接

連接數(shù)據(jù)庫需要uid用戶標(biāo)識符和pwd用戶輸入的密碼

連接的時候，用這個用戶名和密碼來訪問數(shù)據(jù)庫

EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;

11.4 將C語言中的值插入到數(shù)據(jù)庫

EXEC SQL INSERT INTO SC(SNO,CNO,GRADE)VALUES(:SNO, :CNO, :GRADE);

將C語言程序中的SNO,CNO,GRADE宿主變量（加冒號的部分）插入到SC數(shù)據(jù)庫中

11.5?查詢語句

EXEC SQL SELECT GRADEINTO :GRADE :GRADEIFROM SCWHERE SNO=:GIVENSNO AND CNO=:GIVENCNO;

查詢的返回結(jié)果放到ｃ變量里面去（INTO里面的部分）

11.6 游標(biāo)

在11.5中，{SNO，CNO}是SC的主鍵。所以查詢的結(jié)果只有一條語句。如果查詢的結(jié)果是一組元組呢？

這時候我們需要使用游標(biāo)cursor來處理一般的查詢語句返回的元組集合　

定義一個游標(biāo)，類似于C中執(zhí)行的SQL語句一樣來定義，for后面的就是查詢語句。

我們把游標(biāo)看成一個文件，那么對游標(biāo)的操作就和對文件的是一樣的了（會有一個讀寫指針），需要open，close。

讀寫指針一開始指向第一個元組　

fetch語句逐條元組地取每一個值，按照順序賦給宿主變量。每一個宿主變量得到了一個值。

我們用一個循環(huán)再去fetch，直到集合中所有的元組的信息都被提取出來了。

那么循環(huán)什么時候結(jié)束呢？

SQLCA.SQLCODE的值是100的時候，表明查詢結(jié)果的結(jié)果集處理完了。

下面是一個完整的嵌入式游標(biāo)操作：（聲明+打開+一條一條讀取+關(guān)閉）

EXEC SQL DECLARE C1 CURSOR FORSELECT SNO, GRADEFROM SCWHERE CNO = :GIVENCNO;EXEC SQL OPEN C1;if (SQLCA.SQLCODE<0) exit(1); /* There is error in query*/while (1) { EXEC SQL FETCH C1 INTO :SNO, :GRADE :GRADEIif (SQLCA.SQLCODE==100) break; /* treat data fetched from cursor, omitted*/ ∶ }EXEC SQL CLOSE C1;

12 動態(tài)嵌入式sql

在之前11小節(jié)的嵌入式SQL中，SQL語句都是在編譯之前就已經(jīng)寫好了。

但是在一些應(yīng)用中。SQL語句并不能提前寫好（根據(jù)用戶的輸入的信息構(gòu)建sql；換句話說，程序執(zhí)行之前需要執(zhí)行什么SQL用戶是不知道的）。他需要在程序運(yùn)行過程中動態(tài)地建立。

12.1?非查詢動態(tài)SQL

EXEC SQL BEGIN DECLARE SECTION; char sqlstring[200]; EXEC SQL END DECLARE SECTION;char cond[150]; strcpy( sqlstring, ”DELETE FROM STUDENT WHERE ”);printf(“ Enter search condition :”); scanf(“%s”, cond);strcat( sqlstring, cond);EXEC SQL EXECUTE IMMEDIATE :sqlstring;

上面的嵌入式SQL作用是將滿足條件的學(xué)生刪除，其中條件是使用者輸入進(jìn)去的

sqlstring是拼接之后的SQL語句

cond是用戶需要輸入的條件。條件是程序運(yùn)行的時候，由用戶來決定。

IMMEDIATE表示數(shù)據(jù)庫系統(tǒng)動態(tài)地立即執(zhí)行sqlstring里面的語句

12.2 有動態(tài)參數(shù)的嵌入式SQL

先用占位符（place holder）在事先寫好的SQL語句中占一個位置，然后以后填進(jìn)去

EXEC SQL BEGIN DECLARE SECTION; char sqlstring[200]; int birth_year; EXEC SQL END DECLARE SECTION;strcpy( sqlstring, ”DELETE FROM STUDENT WHERE YEAR(BDATE) <= :y; ”);printf(“ Enter birth year for delete :”); scanf(“%d”, &birth_year);EXEC SQL PREPARE purge FROM :sqlstring;EXEC SQL EXECUTE purge USING :birth_year;

這里的：y就是占位符。

PREPARE語句先準(zhǔn)備一下要執(zhí)行的sql語句，此時還是占位符：y。

真正執(zhí)行的時候EXECUTE，用宿主變量的值替換占位符位置的值。

13 嵌入式SQL存儲過程

允許用戶把公用的SQL語句段定義成一個過程，系統(tǒng)事先經(jīng)過編譯優(yōu)化后存儲在數(shù)據(jù)庫系統(tǒng)里面。將來用戶要用的時候直接調(diào)用它。（有點類似于程序語言中的函數(shù)）。

這樣可以改善性能，方便開發(fā)

對于頻繁同時使用這幾個SQL語句段的用戶來說，下次要用的時候，直接調(diào)用存儲過程，不用重新寫語句了。

如果需要修改需求，只要改定義SQL語句段成為過程的那一個地方就可以了，要不然SQL語句段在程序中使用過的地方都需要改。

不用對存儲的語句再編譯（SQL語句段都寫在C程序里面的話，每次進(jìn)行都要進(jìn)行預(yù)編譯優(yōu)化，如果存成一個過程的話就只需要在存入DBMS的第一次過程中優(yōu)化）。

EXEC SQLCREATE PROCEDURE drop_student(IN student_no CHAR(7),OUT message CHAR(30))BEGIN ATOMICDELETE FROM STUDENTWHERE SNO=student_no;DELETE FROM SCWHERE SNO=student_no;SET message=student_no || ’droped’;END; EXEC SQL ∶ CALL drop_student(…); /* call this stored procedure later*/ ∶

放在一起連續(xù)做的事情構(gòu)建成一個存儲過程，定義成一個模塊

在上面例子中，drop_student就是這個存儲過程。之后在C語言程序中直接調(diào)用這個，就代表了它定義里面的幾條SQL語句了。

存儲過程和函數(shù)類似，可以有輸入輸出　　

in——　學(xué)生的學(xué)號

out——返回值，告訴用戶查詢過程是成功還是失敗

ATOMIC表示里面操作為原子操作——要么全部成功，要么一個不做

總結(jié)

以上是生活随笔為你收集整理的数据库笔记： SQL的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。