DBMS的用戶接口

Screenshot 2022-10-11 at 18.28.10.png

數(shù)據(jù)庫管理系統(tǒng)作為一個系統(tǒng)軟件一定要讓用戶會用，讓用戶去管理數(shù)據(jù)售碳，那么它一定要提供一些訪問接口，讓用戶通過這些接口來使用數(shù)據(jù)庫礼旅。
這些接口包括：

1. Query Languages（查詢語言）：基本每種數(shù)據(jù)庫系統(tǒng)都會根據(jù)他采用的數(shù)據(jù)模型，向用戶提供某種類型的查詢語言洽洁，允許用戶通過查詢語言來訪問系統(tǒng)痘系。
   1）形式化的查詢語言（Formal Query Language）：SQL，有嚴(yán)格語法饿自。
   2）表格式的查詢語言（Tabular Query Language）：用填表格的方式來表達(dá)要查什么汰翠。
   3）圖形化查詢語言（Graphic Query Language）
   4）受限的自然語言查詢（Limited Natural Language Query Language）：我要查年齡大于24歲的學(xué)生。
2. GUI（圖形化用戶界面）：訪問并維護(hù)數(shù)據(jù)庫數(shù)據(jù)璃俗。
3. APIs：在應(yīng)用程序中訪問數(shù)據(jù)庫（ODBC）

4. Class Library：類褲（Java奴璃，C++）

   
   
   Screenshot 2022-10-11 at 18.28.25.png

關(guān)系型數(shù)據(jù)庫的查詢語言

Screenshot 2022-10-11 at 18.28.33.png

查詢語言沒有編程能力，不是圖靈完備的城豁。

Screenshot 2022-10-11 at 18.28.40.png

關(guān)系型數(shù)據(jù)庫查詢語言形式化基礎(chǔ)：
數(shù)學(xué)化查詢語言：關(guān)系代數(shù)&關(guān)系演算苟穆，SQL就是以關(guān)系演算為基礎(chǔ)開發(fā)出來的。

Screenshot 2022-10-11 at 18.28.47.png

重要的術(shù)語和概念

Screenshot 2022-10-11 at 18.50.27.png

1. Base table（基表）：一個基表就是一個關(guān)系攒盈，物理存在于磁盤上。
2. View（視圖）：虛表哎榴，不真實存儲在數(shù)據(jù)庫里的數(shù)據(jù)型豁，是通過計算基表從而得出的結(jié)論。
3. Data type supported（數(shù)據(jù)庫產(chǎn)品支持的數(shù)據(jù)類型）：短整型尚蝌，長整型數(shù)據(jù)迎变。
4. NULL（空值）：SQL中的一個關(guān)鍵字（保留字）。布爾表達(dá)式判斷：True飘言、False衣形、NULL。
5. UNIQUE：SQL中的一個關(guān)鍵字（保留字）姿鸿。在數(shù)據(jù)庫中創(chuàng)建一張表時谆吴，用UNIQUE說明，某屬性不允許在其他表里重復(fù)苛预。
6. DEFAULT：SQL中的一個關(guān)鍵字（保留字）句狼。為數(shù)據(jù)庫中某張表的某個屬性指定一個默認(rèn)值。
7. PRIMARY KEY：指定某個屬性是主鍵热某。
8. FOREIGN KEY：指定某個屬性是外鍵鲜锚。如果是外鍵的話突诬，一定是引用了另一張表的主鍵苫拍。一旦某個屬性被定義成外鍵芜繁，系統(tǒng)就會自動檢查表格之間引用完整性的約束。

9. CHECK：在表中定義約束條件绒极，比如骏令，東南大學(xué)的學(xué)生入學(xué)年齡必須大于15歲。

   
   
   Screenshot 2022-10-11 at 18.50.34.png

基本的SQL語句查詢

Screenshot 2022-10-11 at 18.50.41.png

SELECT：target-list：查詢的目標(biāo)屬性垄提。加了[DISTINCT]的話榔袋，就要求系統(tǒng)消除結(jié)果中的重復(fù)元組；不加的話铡俐，不會刪除重復(fù)元組凰兑。
FROM：relation list：這個查詢所涉及到的表。
WHERE：qualification就是一個布爾表達(dá)式审丘，表示查詢結(jié)果應(yīng)該滿足的條件吏够。

Screenshot 2022-10-11 at 20.15.17.png

在系統(tǒng)內(nèi)部一條查詢是如何執(zhí)行的

Screenshot 2022-10-11 at 18.50.47.png

1. 做FROM子句里牽扯到的所有表的笛卡爾乘積，把所有表都拼起來滩报，拼成一張大表锅知。
2. 利用WHERE子句里布爾表達(dá)式的條件，在第一部中得到的大表中去做篩選脓钾，把不滿足查詢要求的元組剔除掉售睹。
3. 根據(jù)SELECT子句中的target list中我要查的那幾個屬性，把第二步中篩選出的元組中我不要的投影掉可训，我要的留下來昌妹。
4. 如果我加了DISTINCT，系統(tǒng)還會把重復(fù)元組去掉握截；如果沒加飞崖，第三步就是最終結(jié)果了。

實例1

Screenshot 2022-10-11 at 20.30.15.png

S.sname：大寫S：給Sailor這張表起的‘別名’川蒙。
S.sid=R.sid：Sailors和Reserves兩張表要按sid做連接蚜厉。
R.bid=103：
因為Reserve這張表沒有水手name的信息，為了獲得預(yù)定了103這條船的水手的name畜眨，我必須讓Sailors這張表和Reserves這張表做一個連接操作昼牛，通過它們之間的sid相等，就可以找到S.sname康聂。

引入別名（Range Variable）

Screenshot 2022-10-11 at 20.30.22.png

Sailors表的range variable是S贰健，Reserves表的range variable是R。
sid兩張表都有恬汁，所以需要“表名.關(guān)系”伶椿，避免引起混淆；像bid和sname只存在在其中一張表中，可以不寫別名脊另。但是建議都寫导狡。

實例2

Screenshot 2022-10-11 at 20.30.28.png

查找至少預(yù)定過一條船的水手：把Reserves這張表投影到sid這個屬性上，只要是在Reserves表上出現(xiàn)偎痛，則一定預(yù)定過船只旱捧。
如果是sid加不加DISTINCT沒有影響；如果是name加不加DISTINCT有影響踩麦，因為存在重名情況枚赡。
一般SELECT S.sid, S.sname是最保險的。

實例3

Screenshot 2022-10-11 at 20.30.34.png

實例4

Screenshot 2022-10-11 at 21.11.42.png

預(yù)定過一艘紅船‘或者’一艘綠船的水手編號。
如果單純想得到sid竣贪，不需要查詢Sailors這張表军洼，但是這里有一個懸念，加上Sailors表還是有用的演怎。
等價于匕争，所有預(yù)定過紅船的水手編號 UNION 所有預(yù)定過綠船的水手編號。
sql用EXCEPT表示集合的差爷耀。如果把UNION換成EXCEPT甘桑，得到的結(jié)果就是，訂過紅船歹叮，但沒有訂過綠船的水手的編號跑杭。

實例5

Screenshot 2022-10-11 at 21.11.51.png

預(yù)定過一艘紅船‘和’一艘綠船的水手編號。
不能簡單的把(B.color='red' OR B.color='green')里的OR換成AND咆耿，這樣是錯誤的德谅，因為如果換成AND，語句意思是“一艘又綠又紅的船”萨螺，沒有這種紅配綠的船窄做。
先讓Reserves這張表做自連接愧驱，這樣每一個水手的訂船信息就會出現(xiàn)兩次。
Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2：把Boats和Reserves這兩張表分別用兩次椭盏，做他們的自連接组砚。
(B1.color='red' AND B2.color='green')：如果一個水手同時訂過紅船和綠船，那么他的訂船信息就會出現(xiàn)兩次庸汗，如果出現(xiàn)這樣一條元組的話惫确，就找到了對應(yīng)水手。（一個超長的元組中蚯舱，前面某列顯示它訂過紅船，后面某列顯示它訂過綠船）

Screenshot 2022-10-11 at 21.44.55.png

INTERSECT：集合的交掩蛤。參與運(yùn)算的關(guān)系必須滿足并兼容條件枉昏。

非關(guān)聯(lián)嵌套查詢

Screenshot 2022-10-11 at 21.11.59.png

訂過103號船的水手的姓名。
（非關(guān)聯(lián)）嵌套子查詢：對Reserves表做選擇揍鸟，把所有訂過103號水手的信息找出來做投影兄裂，投影到sid上，得到一個結(jié)果集合阳藻。與外面查詢無關(guān)聯(lián)晰奖。
外面這個查詢：對Sailors這張表做掃描，看水手編號是否在“訂過103號船的水手編號集合”里腥泥，在匾南，就對了，不在蛔外，就不理他蛆楞。

關(guān)聯(lián)嵌套查詢

Screenshot 2022-10-11 at 22.00.42.png

嵌套的子查詢條件與外層相關(guān)。
如果某個水手預(yù)定過103這條船夹厌，那么在Reserves表里一定‘存在’一套元組的bid等于103豹爹，sid等于水手sid。把Sailors表中每個水手的sid都代入子查詢循環(huán)查詢矛纹，S.sid=R.sid了就說明我查到了臂聋。
SELECT *：返回所有列，星號是通配符或南。查找所有屬性孩等。
在Reserves表中，NOT EXIST（不存在）另外一條元組迎献，他的sid和目標(biāo)水手的sid相同瞎访，bid是103，他的日期與目標(biāo)水手的訂船日期相等吁恍。

Screenshot 2022-10-11 at 22.00.49.png

查找只有一個水手預(yù)定過的船扒秸。
Reserves表自連接有R1和R2播演。
SELECT bid
FROM Reserves R2
WHERE R2.sid != R1.sid：所有除R1.sid之外的其他水手訂的船的編號集合。
WHERE bid NOT IN：如果這個水手所定的bid不在其他水手所定的bid集合中伴奥，則這條船就他一個人訂写烤。

比較運(yùn)算符

Screenshot 2022-10-11 at 22.00.55.png

op可以是大于，小于拾徙，等于洲炊，大于等于，小于等于尼啡，不等于
以大于為例：
op ANY：某屬性的值要比集合中隨便一個值大就行暂衡。
op ALL：某屬性的值要比集合中所有的值都大。

IN

Screenshot 2022-10-11 at 22.01.04.png

S.sid=R.sid AND R.bid=B.bid AND B.color='red'：把預(yù)訂了紅船的水手的sid找到崖瞭。
S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color='green'：把預(yù)訂了綠船的水手的sid找到狂巢。
S.sid IN：看訂了red的水手編號在不在訂了green的水手編號集合中。如果在书聚，說明這個水手red green兩條船都訂了唧领。
What about INTERSECT query?：不能簡單替換S.sid by S.sname，會有重名雌续。

除法

Screenshot 2022-10-11 at 22.01.09.png

查找“所有”斩个。
不存在一條船是我沒訂過的。
SELECT B.bid
FROM Boats B：所有的船驯杜。
SELECT R.bid
FROM Reserves R
WHERE R.sid=S.sid：這個水手預(yù)定過的船受啥。
所有的船 EXCEPT 這個水手預(yù)定過的船 = 這個水手沒訂過的船
如果不存在這個水手沒訂過的船，則艇肴，這個水手所有船都訂過腔呜。

如果考試不允許用EXCEPT， 那么：

Screenshot 2022-10-12 at 17.52.44.png

如果在Reserves表中查不到“這個水手沒訂的船”再悼，則這個水手訂了所有船核畴。

聚集函數(shù)運(yùn)算

Screenshot 2022-10-12 at 17.52.50.png

COUNT (*)：統(tǒng)計在一個關(guān)系中有多少條元組
COUNT ([DISTINCT] A)：統(tǒng)計屬性A中有多少個值（多少行非空元組）。加DISTINCT則統(tǒng)計屬性A有多少個不同的值冲九。
SUM ([DISTINCT] A)：把所有元組的屬性A的值做求和谤草。加DISTINCT則求和屬性A所有不同的值。
AVG ( [DISTINCT] A)：對屬性A求平均值莺奸。加DISTINCT則求屬性A所有不同的值的平均值丑孩。
MAX (A)：求屬性A中的最大值。
MIN (A)：求屬性A中的最小值灭贷。

Screenshot 2022-10-12 at 17.53.04.png

1（左上）. 問Sailors這張表中元組的個數(shù)温学。
2（右上）. 有好幾個水手都叫Bob，所有叫Bob的水手的不同的級別的個數(shù)甚疟。
3（左中）. 查找所有級別為10的水手的平均年齡仗岖。
4（右中）.查找所有級別為10的水手的不同年齡值的平均值逃延。
5（左下）.查找級別最高的水手的姓名。

Screenshot 2022-10-12 at 17.53.29.png

1. SELECT S.sname, MAX (S.age)
   FROM Sailors S：錯誤的轧拄。因為MAX (S.age)計算結(jié)果是一個單個的值揽祥，姓名與max age無法對應(yīng)。
2. SELECT S.sname, S.age
   FROM Sailors S
   WHERE S.age =
   (SELECT MAX (S2.age)
   FROM Sailors S2)：正確的檩电。
3. 換位置也是正確的拄丰。

分組聚集函數(shù)計算

Screenshot 2022-10-12 at 17.53.53.png

因為SQL不具備編程功能，所以我們不能for loop俐末。那怎么辦呢料按？

Screenshot 2022-10-12 at 17.54.11.png

GROUP BY grouping-list：把WHERE得到的符合條件的元組，按grouping-list里的那些屬性的值做分組鹅搪。屬性值相等的元組被分到一組站绪。
然后對每一個組，按照SELECT子句里規(guī)定的屬性和聚集函數(shù)做運(yùn)算丽柿，得到一條結(jié)果；即魂挂，每一個group甫题，產(chǎn)生一條結(jié)果元組。
要求SELECT中出現(xiàn)的attribute涂召，必須對每個group只有一個值坠非，否則會error。
HAVING group-qualification：與WHERE相似果正，對經(jīng)過GROUP BY分組后的每個group再做檢查炎码，把不滿足條件的group去掉。

Screenshot 2022-10-12 at 18.38.39.png

要求SELECT和HAVING中出現(xiàn)的每一個attribute對分組得到的每個group來講奕筐，它值必須是單一的舱痘。

Screenshot 2022-10-12 at 18.38.47.png

對所有年齡大于18歲的水手变骡，查每一個水手人數(shù)至少為2的年齡級別組中，年齡大于十八歲的水手中最年輕的水手衰粹。

Screenshot 2022-10-12 at 18.38.57.png

Screenshot 2022-10-12 at 18.39.05.png

Screenshot 2022-10-12 at 18.39.12.png

查每一條紅船的預(yù)定人數(shù)是多少锣光。
先連接Reserves表和Boats表，拼起來以后就得到了所有船的預(yù)定情況的詳細(xì)信息铝耻，然后做篩選誊爹，把所有紅顏色船的訂單情況篩選出來，按照bid做分組瓢捉，這樣同一條船的訂船情況就在一個group中频丘，此時對每一個group做COUNT（*），這樣我就知道泡态，每一條船有多少人訂過它搂漠。
如果在WHERE不做篩選，轉(zhuǎn)而用HAVING對group做篩選：邏輯沒問題某弦，但是數(shù)據(jù)庫會報錯桐汤。
SELECT B.bid, COUNT(*) AS scount
FROM Boats B, Reserves R
WHERE R.bid=B.bid
GROUP BY B.bid
HAVING B.color=‘red’：這就是為什么要求，SELECT和HAVING中出現(xiàn)的每個屬性靶壮，都必須是GROUP BY屬性集合的子集怔毛。（B.color不是B.bid的子集）
硬用的話怎么辦：
SELECT B.bid, COUNT(*) AS scount
FROM Boats B, Reserves R
WHERE R.bid=B.bid
GROUP BY B.bid, B.color=‘red’ (補(bǔ)上）
HAVING B.color=‘red’：在本例中，每條船跟每條船的顏色都是單一的不同的腾降，所以按編號分按顏色分其實不會改變分組情況拣度。
或者這樣寫：
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 bid
FROM Boats B
WHERE B.color=‘red’) 也是對的。

image.png

從S2重新開始的原因是S.age>18已經(jīng)把不滿足條件的元組排除掉了螃壤，S表不完整了抗果。

image.png

Screenshot 2022-10-12 at 20.47.18.png

Null Values

Screenshot 2022-10-12 at 20.52.04.png

空值就是不知道蚁滋，不是零宿接。
引入空值之后帶來的一些問題：
在查詢時，需要在WHERE和HAVING的布爾表達(dá)式中引入一些特殊的運(yùn)算符來表達(dá)對Null value的判斷辕录。

Screenshot 2022-10-12 at 21.13.20.png

Screenshot 2022-10-12 at 21.13.40.png

Screenshot 2022-10-12 at 20.52.10.png

Screenshot 2022-10-12 at 20.52.18.png

Screenshot 2022-10-12 at 20.52.28.png

Screenshot 2022-10-12 at 20.52.47.png

Screenshot 2022-10-12 at 20.52.55.png

Screenshot 2022-10-12 at 20.53.02.png

Screenshot 2022-10-12 at 20.53.10.png

擴(kuò)充的就不學(xué)了睦霎，拜拜。