第4章 中級SQL

1.連接表達式

對于studenthetakes兩個關系：

student關系 IDnamedept_nametot_cred

00128	Zhang	Comp.Sci	102
12345	Shankar	Comp.Sci.	32
19991	Brandt	History	80
23121	Chavez	Finance	110
44553	Peltier	Physics	56
45678	Levy	Physics	46
54321	Wiliams	Comp.Sci.	54
55739	Sanchez	Music	38
70557	Snow	Physics	0
76543	Brown	Comp.Sci.	58
76653	Aoi	Elec.Eng.	60
98765	Bourikas	Elec.Eng.	98
98988	Tanaka	Biology	120

takes 關系 IDcourse.idsec_idsemesteryeargrade

00128	CS-101	1	Fall	2009	A
00128	CS-347	1	Fall	2009	A-
12345	CS-101	1	Fall	2009	c
12345	CS-190	2	Spring	2009	A
12345	CS-315	1	Spring	2010	A
12345	CS-347	1	Fall	2009	A
19991	HIS-351	1	Spring	2010	B
23121	FIN-201	1	Spring	2010	C+
44553	PHY-101	1	Fall	2009	B-
45678	CS-101	1	Fall	2009	F
45678	CS-101	1	Spring	2010	B+
45678	CS-319	1	Spring	2010	B
54321	CS-101	1	Fall	2009	A-
54321	CS-190	2	Spring	2009	B+
55739	MU-199	1	Spring	2010	A-
76543	CS-101	1	Fall	2009	A
76543	CS-319	2	Spring	2010	A
76653	EE-181	1	Spring	2009	C
98765	CS-101	1	Fall	2009	C-
98765	CS-315	1	Spring	2010	B
98988	BIO-101	1	Summer	2009	A
98988	BlO-301	1	Summer 2010	null

1.1 連接關系

SQL支持另一種形式的連接，其中可以指定任意的連接條件。

on條件允許在參與連接的關系上設置通用的謂詞。該謂詞的寫法與where子句謂詞類似，只不過使用的是關鍵詞on而不是where，與using條件一樣，on條件出現在連接表達式的末尾。

select * from student join takes on student.ID = takes.ID;

表明：如果student元組的ID與takes的元素ID上取值一樣，那么就是匹配的。

其實上述的查詢與以下的查詢是等價的：

select * from student, takes where student. ID=takes. ID;

關系名用來區分屬性名ID，這樣的ID的兩次出現分別表示為student.ID和takes.ID，只顯示一次ID值的查詢版本如下：

select student. ID as ID, name, dept_name, tol_cred, course_id, sec_id, semester, year, grade from student join takes on student. ID=takes. ID;

查詢的結果如下

IDnamedept_nametot_credcourse_idsec_idsemesteryeargrade

[00128	Zhang	Comp.Sci	102	CS-101	1	Fall	2009	A
00128	Zhang	Comp.Sci.	102	CS-347	1	Fall	2009	A-
12345	Shankar	Comp.Sci.	32	CS-101	1	Fall	2009	C
12345	Shankar	Comp.Sci.	32	CS-190	2	Spring	2009	A
12345	Shankar	Comp.Sci.	32	CS-315	1	Spring	2010	A
12345	Shankar	Comp.Sci.	32	CS-347	1	Fall	2009	A
19991	Brandt	History	80	HIS-351	1	Spring	2010	B
23121	Chavez	Finance	110	FIN-201	1	Spring	2010	C+
44553	Peltier	Physics	56	PHY-101	1	Fall	2009	B-
45678	Levy	Physics	46	CS-101	1	Fall	2009	F
45678	Levy	Physics	46	CS-101	1	Spring	2010	B+
45678	Levy	Physics	46	CS-319	1	Spring	2010	B
54321	Williams	Comp.Sci.	54	CS-101	1	Fall	2009	A-
54321	Williams	Comp.Sci.	54	CS-190	2	Spring	2009	B+
55739	Sanchez	Music	38	MU-199	1	Spring	2010	A-
76543	Brown	Comp.Sci.	58	CS-101	1	Fall	2009	A
76543	Brown	Comp.Sci.	58	CS-319	2	Spring	2010	A
76653	Aoi	Elec.Eng.	60	EE-181	1	Spring	2009	C
98765	Bourikas	Elec.Eng.	98	CS-101	1	Fall	2009	C-
98765	Bourikas	Elec.Eng.	98	CS-315	1	Spring	2010	B
98988	Tanaka	Biology	120	BIO-101	1	Summer	2009	A
98988	Tanaka	Biology	120	BIO-301	1	Summer	2010	null

on條件可以表示任何SQL謂詞，從而使用on條件的連接表達式比自然連接更加豐富的連接條件。

1.2 外連接

下面的查詢好像能夠檢索出所需的信息

select * from student natural join takes;

但是如果有一些學生，如果沒有選修任何課程，那么student關系中所對應的元素與takes關系中的任何元組配對，就不會滿足自然連接的條件。這樣我們就不能看到這些學生。

更為一般地，在參與連接的任何一個或兩個關系中的某些元組可能會以這種方式“丟失”。外連接（outer join） 運算與我們已經學過的連接運算類似，但通過在結果中創建包含空值元組的方式，保留了那些在連接中丟失的元組。

外連接有三種形式：

• 左外連接（left outer join）：只保留出現在左外連接運算之前（左邊）的關系中的元組。
• 右外連接（right outer join）：只保留出現在右外連接運算之后（右邊）的關系中的元組。
• 全外連接（full outer join）：保留出現在兩個關系中的元組。

而此前不保留未匹配的元素的方式稱為內連接（inner join）運算。

那么具體的形式左外連接運算：首先，像外面那樣計算出內連接的結果，然后，對于在內連接的左側關系中任意一個與右側關系中任何元素都不匹配的元素t，向連接結果中加入一個元組r。r的構造如下：

• 元組r從左側關系得到的屬性被賦為t中的值。
• r的其他屬性被賦值為空值。

左外連接例子

select * from student natural leftt outer join lakes; IDnamedept_nametot_credcourse_idsec_idsemesteryeargrade

[00128	Zhang	Comp.Sci.	102	CS-101	1	Fall	2009	A
00128	Zhang	Comp.Sci.	102	CS-347	1	Fall	2009	A-
12345	Shankar	Comp.Sci.	32	CS-101	1	Fall	2009	C
12345	Shankar	Comp.Sci.	32	CS-190	2	Spring	2009	A
12345	Shankar	Comp.Sci.	32	CS-315	1	Spring	2010	A
12345	Shankar	Comp.Sci.	32	CS-347	1	Fall	2009	A
19991	Brandt	History	80	HIS-351	1	Spring	2010	B
23121	Chavez	Finance	110	FIN-201	1	Spring	2010	C+
44553	Peltier	Physics	56	PHY-101	1	Fall	2009	B-
45678	Levy	Physics	46	CS-101	1	Fall	2009	F
45678	Levy	Physics	46	CS-101	1	Spring	2010	B+
45678	Levy	Physics	46	CS-319	1	Spring	2010	B
54321	Williams	Comp.Sci.	54	CS-101	1	Fall	2009	A-
54321	Williams	Comp.Sci.	54	CS-190	2	Spring	2009	B+
55739	Sanchez	Music	38	MU-199	1	Spring	2010	A-
70557	Snow	Physics	0	nmull	mull	null	null	null
76543	Brown	Comp.Sci.	58	CS-101	1	Fall	2009	A
76543	Brown	Comp.Sci.	58	CS-319	2	Spring	2010	A
76653	Aoi	Elec.Eng.	60	EE-181	1	Spring	2009	C
98765	Bourikas	Elec.Eng.	98	CS-101	1	Fall	2009	C-
98765	Bourikas	Elec.Eng.	98	CS-315	1	Spring	2010	B
98988	Tanaka	Biology	120	BIO-101	1	Summer	2009	A
98988	Tanaka	Biology	120	BIO-301	1	Summer	2010	null

on子句可以和外連接一起使用。下述查詢與我們見過的第一個使用“student natural left outer join takes"的查詢是相同的，只不過屬性ID在結果中出現兩次。

select * from student left outer join takes on student.ID=takes.ID;

on和where在外連接中的表現是不一樣的。原因是外連接之為那些對應內連接結果沒有貢獻的元組補上空值并加入結果。on條件是外連接聲明的一部分，但是where子句卻不是。

1.3 連接類型和條件

SQL中的常規連接稱作內連接。這樣連接子句就可以用inner join來替換outer join .說明使用的是常規連接，關鍵詞inner是可選的，當join子句中沒有使用outer前綴，默認的連接類型是inner join。

2. 視圖

我們一直在邏輯模型層操作，即我們假定了給定的集合的關系都是實際存儲在數據庫中。讓所有用戶都看到整個邏輯模型是不合適的。但是出于安全考慮，可能需要向用戶隱層特定的數據。

select ID,name,dept_name from instructor;

我們還可能希望創建一個比邏輯模型更加符合特定用戶直覺的個性化的關系集合。

select course. course id, sec-id, building, room_number from course, section where course. course_id =section. course_id and course. dept_name =' Physics'and section. semester=' Fall'and section. year ='2009';

如果我們將計算出的上述結果存儲下來，并傳遞給用戶，但是如果底層關系發生變化，那么結果就匹配。

在SQL中允許通過查詢定義“虛關系”，它在概念上包含查詢結果。虛關系不預先計算并存儲，而是使用虛關系的時候才通過執行查詢被計算出來。

任何想這種不是邏輯模型的一部分，但作為虛關系對用戶可見的關系稱為視圖（view）。任何給定的實際關系集合上能夠支持大量視圖。

2.1 視圖的定義

可以使用create view命令定義視圖。

create view命令的格式為

create view v as< query expression>;

其中<query expression>可以是任何合法的查詢表達式，v表示視圖名。

create view faculty as select ID,name,dept_name from instructor;

正如前面已經解釋過的，視圖關系在概念上包含查詢結果中的元組，但并不進行預計算和存儲。相反，數據庫系統存儲與視圖關系相關聯的查詢表達式。當視圖關系被訪問時，其中的元組是通過計算查詢結果而被創建出來的。從而，視圖關系是在需要的時候才被創建的。

視圖：列出Physics系在2009年秋季學期所開設的所有課程段，以及每個課程段在那棟建筑的那個房間授課的信息

create view physics_fall_2009 as select course, course_id, sec_id, building, room _number from course, section where course.course_id= section.course_id and course. dept_name=' Physics'and section. semester=' Fall'and section. year='2009';

2.2 SQL查詢中使用視圖

一旦定義了一個視圖，就可以用視圖名指代該視圖生成的虛關系。

查詢找到所有于2009年秋季學期在Watson大樓開設的Physics課程

select course_id from physics_fall_2009 where building='Watson';

在查詢中，視圖名可以出現在關系名可以出現的任何地方

視圖的屬性名可以按下述方式顯式指定：

create view departments_total_salary(dept_name,total_salary)as select dept_name,sum(salary) from instructor group by dept_name;

因為sum表達式沒有名稱，其屬性名在視圖定義中顯式指定的。

無論何時執行這個查詢，視圖關系都被重新計算

一個視圖可能被用到定義另一個視圖的表達式中。例如，我們可以如下定義視圖 physics_fall_2009_watson，它列出了于2009年秋季學期在Watson大樓開設的所有Physics 課程的標識和房間號：

create view physics_fall_2009_watson as select course_id,room_number from physics fall_2009 where building='Watson';

等價于：

create view physics_fall_2009_watson as(select course_id, room_number from(select course.course_id, building, room_numberfrom course, sectionwhere course.course_id = section.course_id and course.dept_name=' Physics'and section.semester=' Fall'and section.year='2009')where building=' Watson';

2.3 物化視圖

特定數據庫系統允許存儲視圖關系，但是它們保證：如果用于定義視圖的實際關系改變，視圖也跟著修改。這樣的視圖被稱為物化視圖（materialized view）。

保持物化視圖一直在最新狀態的過程稱為物化視圖維護（materialized view maintenance）?；蛘咄ǔ：喎Q視圖維護（view maintenance）

當構成視圖定義的任何關系被更新時，可以馬上進行視圖維護。然而某些數據庫系統在視圖被訪問時才執行視圖維護。還有一些系統僅采用周期性的物化視圖更新方式，在這種情況下，當物化視圖被使用時，其中的內容可能是陳舊的，或者說過時的。如果應用需要最新數據的話，這種方式是不適用的。某些數據庫系統允許數據庫管理員來控制在每個物化視圖上需要采取上述的哪種方式。

2.4 視圖更新

一般來說，如果定義視圖的查詢對下列條件都能滿足，我們稱SQL視圖是可更新的（updatable）

• from子句中只有一個數據庫關系
• select子句中只包含關系的屬性名，不包含任何表達式、聚集或distinct聲明。
• 任何沒有出現在select子句的屬性可以取空值，即這些屬性沒有not null約束，也不構成主碼一部分。
• 查詢不包含有group by或having子句。

在這些限制下，下面的視圖上允許執行update、insert和delete操作。

create view history_instructors as select * from instructor where dept_name='History';

在默認情況下，SQL允許執行上述更新。但是，可以通過在視圖定義的末尾包含with check option子句的方式來定義視圖。這樣，如果向視圖中插入一條不滿足視圖的where子句條件的元組，數據庫系統將拒絕該插入操作。類似地，如果新值不滿足where子句的條件，更新也會被拒絕。

3. 事務

**事務（transaction）**由查詢和（或）更新語句的序列組成。SQL標準規定當一條SQL語句被執行，就隱式地開始了一個事務。下列SQL語句之一會結束一個事務：

• Commit work：提交當前事務，也就是將該事務所做的更新在數據庫中持久存儲，事務被提交后，一個新的事務自動開始
• Rollback work：回滾當前事務，即撤銷該事務中的所有SQL語句對數據庫的更新，這樣數據庫就恢復到執行該事務第一條語句之前的狀態。

關鍵詞work在兩個語句中都是可選的。

一個事務或者在完成所有步驟后提交其行為，或者在不能成功完成其所有動作的情況下回滾其所有動作，通過這種方式數據庫提供了對事務具有原子性（atomic） 的抽象，原子性也就是不可分割性。要么事務的所有影響被反映到數據庫中，要么任何影響都沒有（在回滾之后）。

如果程序沒有執行兩條命令中的任何一條而終止了，那么更新要么被提交要么被回滾。SQL標準并沒有指出究竟執行哪一種，如何選擇依賴于具體實現。

在很多SQL實現中，默認方式下每個SQL語句自成一個事務，且一執行完就提交。如果一個事務要執行多條SQL語句，就必須關閉單獨SQL語句的自動提交。如何關閉自動提交也依賴于特定的SQL實現，盡管在諸如JDBC或ODBC那樣的應用編程接口中存在標準化方式來完成這項工作。

4. 完整性約束

完整性約束保證授權用戶對數據庫所做的修改不會破壞數據的一致性。因此，完整性約束防止的是對數據的意外破壞。

完整性約束的例子有：

• 教師姓名不能為null。
• 任意兩位教師不能有相同的教師標識。
• course 關系中的每個系名必須在department關系中有一個對應的系名。
• 一個系的預算必須大于0.00美元。

完整性約束通常被看成是數據庫模式設計過程的一部分，它作為用于創建關系的create table命令的一部分被聲明。然而，完整性約束也可以通過使用alter table table-name add constraint命令施加到已有關系上，其中constraint可以是關系上的任意約束。當執行上述命令時，系統首先保證關系滿足指定的約束。如果滿足，那么約束被施加到關系上；如果不滿足，則拒絕執行上述命令。

4.1. 單個關系上的約束

• not null
• unique
• check(<謂詞>)

4.2. not null約束

空值是所有域的成員。因此在默認的情況下是SQL中每個屬性的合法值。但是在有些情況下，我們并不希望得到的值為空值。

name varchar(20) not null budget numeric(12,2) not null

not null聲明禁止在該屬性上插入空值，任何可能導致向一個聲明為not null的屬性插入空值的數據庫都會產生錯誤的信息。

很多的情況下我們希望避免空值，尤其是SQL禁止在關系模式的主碼中出現空值。

4.3. unique約束

SQL還支持下面這種完整性約束：
$$unique(A_{j1},A_{j2},?,A_{jm})$$
unique聲明指出$A_{j1},A_{j2},?,A_{jm}$形成了一個候選碼，即在關系中沒有兩個元組能在所有列出的屬性上取值相同。

4.4. check子句

當應用于關系聲明時，**check§**子句指定一個謂詞$P$，關系中的每個的元組都必須滿足謂詞P。通常用check子句保證屬性值滿足指定的條件，實際上創建了一個強大的類型系統。

create table section(course_id varchar(8).sec_id varchar(8), semester varchar(6), year numeric(4,0),building varchar(15), room_number varchar(7), time_slot_id varchar(4), primary key(course_id, sec_id, semester, year), check(semester in(' Fall',' Winter',' Spring',' Summer')));

這里check子句模擬了一個枚舉類型。

根據SQL標準，check子句中的謂詞可以是包括子查詢在內的任意謂詞。然而，當前還沒有一個廣泛使用的數據庫產品允許包含子查詢的謂詞。

4.5. 參照完整性

我們常常希望保證在一個關系中給定屬性集上的取值也在另一關系的特定屬性集的取值中出現。這種情況稱為參照完整性（referential integrity）。

外碼可以用作為SQL中create table 語句一部分的foreign key子句來聲明。我們用大學數據庫SQL DLL定義的一部分來說明外碼聲明。course表的定義中有一個聲明“foreign key（dept_name）references department”。這個外碼聲明表示，在每個課程元組中指定的系名必須在department關系中存在。沒有這個約束，就可能會為一門課程指定一個不存在的系名。

更為一般的，令關系$r_1$和$r_2$的屬性集分別為$R_1$和$R_2$，主碼分別為$K_1$和$K_2$。如果要求對$r_2$中任意元組$t_2$，均存在$r_1$中元組$t_1$使得$t_1.K_1=t_2.\alpha$。我們稱$R_2$的子集$\alpha$為參考關系$r_1$中$K_1$的外碼(foreign key)。

這種要求稱為參照完整性約束（referential-intergrity constraint）或子集依賴（subset dependency）。后一種稱法是由于上述參照完整性可以表示為這樣一種要求：$r_2$中$\alpha$上的取值集合必須是$r_1$中$K_1$上的取值集合的子集。請注意，為使參照完整性約束有意義，$\alpha$ 和$K_1$必須是相容的屬性集；也就是說，要么$\alpha$ 等于$K_1$，要么它們必須包含相同數目的屬性，并且對應屬性的類型必須相容（這里我們假設$\alpha$ 和$K_1$是有序的）。不同于外碼約束，參照完整性約束通常不要求$K_1$是$r_1$的主碼；其結果是，$r_1$中可能有不止一個元組在屬性$K_1$上取值相同。

4.6 事務中對完整性約束的違反

例如，假設我們有一個主碼為name的 person關系，還有一個屬性是spouse，并且spouse是在person 上的一個外碼。也就是說，約束要求 spouse屬性必須包含在person表里出現的名字。假設我們希望在上述關系中插入兩個元組，一個是關于John的，另一個是關于Mary的，這兩個元組的配偶屬性分別設置為Mary和John，以此表示John和Mary彼此之間的婚姻關系。無論先插人哪個元組，插入第一個元組的時候都會違反外碼約束。在插人第二個元組后，外碼約束又會滿足了。

為了處理這樣的情況，SQL標準允許將inttially deferred子句加入到約束聲明中；這樣完整性約束不是在事務的中間步驟上檢查，而是在事務結束的時候檢查。一個約束可以被指定為可延遲的（deferrable），這意味著默認情況下它會被立即檢查，但是在需要的時候可以延遲檢查。對于聲明為可延遲的約束，執行set constraints constraint-list deferred語句作為事務的一部分，會導致對指定約束的檢查被延遲到該事務結束時執行。

4.7 復雜check條件與斷言

正如SQL標準所定義的，check子句中的謂詞可以是包含子查詢的任意謂詞。如果一個數據庫實現支持在check子句中出現子查詢，我們就可以在關系section 上聲明如下所示的參照完整性約束：

check(time_slot_id in(select time_slot_id from time_slot))

這個check條件檢測在section關系中每個元組的time_slot_id的確是在time_slot關系中某個時間段的標識。因此這個條件不僅在section中插入或修改元組時需要檢測，而且在time_slot關系改變時也需要檢測（如在time_slot關系中，當一個元組被刪除或修改的情況下）。

一個斷言（assertion） 就是一個謂詞，它表達了我們希望數據庫總能滿足的一個條件。域約束和參照完整性約束是斷言的特殊形式。我們前面用大量篇幅介紹了這幾種形式的斷言，是因為它們容易檢測并且適用于很多數據庫應用。但是，還有許多約束不能僅用這幾種特殊形式來表達。如有兩個這樣的例子

• 對于student關系中的每個元組，它在屬性tot_cred上的取值必須等于該生所成功修完課程的學分總和。
• 每位教師不能在同一個學期的同一個時間段在兩個不同的教室授課。

SQL中的斷言如下形式

create assertion <assertion-name>cheek<predicate>;

由于SQL不提供for all X , P(X)結構，所以我們通過等價的"not exists X such that not P(X)"結構來實現此約束，這一結構可以用SQL來表示。

create asvertion credits_earned_constrainu check(not exists(select ID from student where tot_cred<>(select sum(credits)from takes natural join course where student. ID=takes. ID and grade is not null and grade<>'F');

當創建斷言時，系統要檢測其有效性。如果斷言有效，則今后只有不破壞斷言的數據庫修改才被允許。如果斷言較復雜，則檢測會帶來相當大的開銷。因此，使用斷言應該特別小心。

5. SQL的數據類型與模式

5.1 SQL中的日期和時間類型

SQL標準還支持與日期和時間相關的幾種數據類型。

• date：日歷日期，包括年（四位）、月和日。
• time：一天中的時間，包括小時、分和秒?？梢杂米兞縯ime§來表示秒的小數點后數字位數，這里默認為0，通過指定time with timezone，還可以把時區信息連同時間一起存儲。
• timestamp：date和time的組合，可以用變量timestamp§來表示秒的小數點后位數（默認6位）。如果指定with timezone，則時區信息也會被存儲。

日期和時間類型的值

date '2001-04-25' time '09:30:00' timestamp '2001-04-25 10:29:01.45'

我們可以利用cast e as t形式的表達式來將一個字符串（或字符串表達式）e轉換成類型t，其中t是 date、time、timestamp中的一種。字符串必須符合正確的格式，像本段開頭說的那樣。當需要時，時區信息可以從系統設置中得到。

我們可以利用extract（field from d），從date或time值d中提取出單獨的域，這里的域可以是year、month、day、hour、minute 或者 second 中的任意一種。時區信息可以用timezone_.hour和timezone_minute 來提取。

SQL允許在上面列出的所有類型上進行比較運算，也允許在各種數字類型上進行算術運算和比較運算。SQL還支持interval數據類型，它允許在日期、時間和時間間隔上進行計算

5.2 默認值

SQL允許為屬性指定默認值，如下面的create table 語句所示：

create table student(ID varchar（5），name varchar（20）not null，dept_name varchar（20），tot_cred numeric（3，0）default0，primary key（ID))；

tot_cred屬性的默認值被聲明為0。

5.3 創建索引

在關系的屬性上所創建的索引（index）是一種數據結構，它允許數據庫系統高效地找到關系中那些在索引屬性上取給定值的元組，而不用掃描關系中的所有元組。

盡管SQL語言沒有給出創建索引的正式語法定義，但很多數據庫都支持使用如下所示的語法形式來創建索引：

create index studentID_index on student(ID);

上述語句在student關系的屬性ID上創建了一個名為studentlD_index的索引。

5.4 大對象類型

許多當前的數據庫應用需要存儲可能很大（KB級）的屬性，例如一張照片；或者非常大的屬性（MB級甚至GB級），例如高清晰度的醫學圖像或視頻片斷。因此SQL提供字符數據的大對象數據類型（clob） 和二進制數據的大對象數據類型（blob）。在這些數據類型中字符“lob”代表“Large OBject”。
例如，我們可以聲明屬性

book_reiew clob (10KB) image blob (10MB) motvie blob (2GB)

對于包含大對象（好幾個MB甚至GB）的結果元組而言，把整個大對象放人內存中是非常低效和不現實的。相反，一個應用通常用一個SQL查詢來檢索出一個大對象的“定位器”，然后在宿主語言中用這個定位器來操縱對象，應用本身也是用宿主語言書寫的。

5.5 用戶定義的類型

SQL支持兩種形式的用戶定義數據類型。第一種稱為獨特類型（distinct type），另一種稱為結構化數據類型（structured data type），允許創建具有嵌套記錄結構、數組和多重集的復雜數據類型。

例如用于學生名和教師名的name屬性就有可能有相同的域：所有人名的集合。但是如果我們從概念層而不是從屋里層來看待數據庫的話，name和dept_name應該有不同域。

更重要的是，在現實中，把一個教師的姓名賦給一個系名可能是一個程序上的錯誤；類似地，把一個以美元表示的貨幣值直接與一個以英鎊表示的貨幣值進行比較幾乎可以肯定是程序上的錯誤。一個好的類型系統應該能夠檢測出這類賦值或比較。為了支持這種檢測，SQL提供了獨特類型（distinct type） 的概念。

可以用create type子句來定義新類型：

create type Dollars as numeric(12,2) final; create type Pounds as numeric(12,2) final;

此關鍵字final并不是真的意義，是SQL:1999標準要求的。

department表定義為：

create table department(depl_name varchar(20),building varchar(15), budget Dollars);

嘗試為Pounds類型的變量賦予一個Dollars類型的值會導致一個編譯時錯誤。

由于有強類型檢查，表達式（department.budget + 20）將不會被接受。一種類型的數據可以被轉換(cast)到另一個域。

cast (department.budget to numeric(12,2))

SQL提供了drop type和 alter type子句來刪除或修改以前創建過的類型。

在把用戶定義類型加入到SQL之前，SQL有一個相似但是稍有不同的概念：域（domain）可以在基本類型上施加完整性約束。例如

create domain DDollars as numeric(12,2)not null;

DDollars域可以用作屬性類型，但是類型和域之間有兩個重大差別：

在域上可以聲明約束，然而用戶定義類型上不能聲明約束或默認值。設計用戶定義類型不僅是用它來指定屬性類型，而且還將它用在不能施加約束的地方對SQL進行過程擴展。

域不是強制類型，因此一個域類型的值可以被賦值為另一個域的類型，只要類型是相容的。

5.6 create table的擴展

SQL提供一個create table like來擴展支持這個任務。

create table temp_instructor like instructor;

上述語句創建了一個與instructor具有相同模式的新表temp_instructor。

當書寫一個復雜查詢時，把查詢的結果存儲成一個新表是很有用的。

create table tl as(select *from instructor where dept_name='Music') with data;

一條用于創建表（具有合適的列），另一條用于查詢結果插入表中。如果省略with data 子句，表會被創建，但不會被載入數據。

模式、目錄與環境

當前數據庫系統提供了三層結構的關系命名機制。最頂層由目錄（catalog）構成，每個目錄都可以包含模式（schema）。諸如關系和視圖那樣的SQL對象都包含在模式中。

要在數據庫上做任何操作，用戶（或程序）都必須先連接到數據庫。為了驗證用戶身份，用戶必須提供用戶名以及密碼（通常情況下）。每個用戶都有一個默認的目錄和模式，這個組合對用戶來說是唯一的。當一個用戶連接到數據庫系統時，將為該連接設置好默認的目錄和模式。這對應于當用戶登錄進一個操作系統時，把當前目錄設置為用戶的主（home）目錄。

為了唯一標識出一個關系，必須使用一個名字，它包含三部分，例如：

catalog5.uniy_shema.course

當名字的目錄部分被認為是連接的默認目錄時，可以省略目錄部分。這樣如果catalog5是默認目錄，我們可以用univ_schema.course來唯一標識上述關系。

如果用戶想訪問存在于另外的模式中的關系，而不是該用戶的默認模式，那就必須指定模式的名字。然而，如果一個關系存在于特定用戶的默認模式中，那么連模式的名字也可以省略。這樣，如果catalog5是默認目錄并且univ_schema是默認模式，我們可以只用course。

當有多個目錄和模式可用時，不同應用和不同用戶可以獨立工作而不必擔心命名沖突。不僅如此，一個應用的多個版本（一個產品版本，其他是測試版本）可以在同一個數據庫系統上運行。
默認目錄和模式是為每個連接建立的SQL 環境（SQL environment）的一部分。環境還包括用戶標識（也稱為授權標識符）。所有通常的SQL語句，包括DDL和DML語句，都在一個模式的環境中運行。

6. 授權

我們可能會給一個用戶在數據庫的某些部分授予幾種形式的權限。對數據的授權包括：

• 授權讀取數據。
• 授權插人新數據。
• 授權更新數據。
• 授權刪除數據。

每種類型的授權稱之為一個權限（privilege）。

當用戶提交查詢或更新時，SQL執行先基于該用戶曾獲得過的權限檢查此查詢或更新是否是授權過的。如果查詢或更新沒有經過授權，那么將被拒絕執行。

最大的授權形式是被授予數據庫管理員的。數據庫管理員可以授權新用戶、重構數據庫，等等。這種權限方式和操作系統中的超級用戶、管理員或操作員的權限是類似的。

6.1 權限的授予與收回

SQL標準包括select、insert、update和delete權限。權限所有權限（all privileges）可以用作所有允許權限的簡寫形式。一個創建了新關系的用戶將自動被授予該關系上的所有權限。
SQL數據定義語言包括授予和收回權限的命令。grant 語句用來授予權限。此語句的基本形式為：

grant <權限列表> on <關系名或視圖名> to <用戶/角色列表>；

關系上的select權限用于讀取關系中的元組。下面的grant 語句授予數據庫用戶Amit和Satoshi在department 關系上的select權限：

grant select on department to Amit,Satoshi;

值得注意的是，SQL授權機制可以對整個關系或一個關系的指定屬性授予權限。但是，它不允許對一個關系的指定元組授權。
我們使用revoke 語句來收回權限。此語句的形式與grant幾乎是一樣的：

revoke<權限列表> on <關系名或視圖名> from<用戶/角色列表>；

6.2 角色

角色（role） 的概念適用于此觀念。在數據庫中建立一個角色集，可以給角色授予權限，就和給每個用戶授權的方式完全一樣。每個數據庫用戶被授予一組他有權扮演的角色（也可能是空的）。

另一個不是很合適的方法是建立一個instructor用戶標識，允許每位教師用instructor用戶標識來連接數據庫。該方式的問題是它不可能鑒別出到底是哪位教師執行了數據庫更新，從而導致安全隱患。使用角色的好處是需要用戶用他們自己的用戶標識來連接數據庫。

在SQL中創建角色如下所示：

create role instructor;

角色可以授予給用戶，也可以授予給其他角色，如這樣的語句：

grant dean to Amit; create role dean; grant instructor to dean; grant dean to Satoshi;

一個用戶或一個角色的權限包括：

• 所有直接授予用戶/角色的權限。
• 所有授予給用戶/角色所擁有角色的權限。

值得注意的是，基于角色的授權概念并沒有在SQL中指定，但在很多的共享應用中，基于角色的授權被廣泛應用于存取控制。

6.3 視圖的授權

6.4 模式的授權

SQL標準為數據庫模式指定了一種基本的授權機制：只有模式的擁有者才能夠執行對模式的任何修改，諸如創建或刪除關系，增加或刪除關系的屬性，以及增加或刪除索引。

然而，SQL提供了一種references權限，允許用戶在創建關系時聲明外碼。SQL的references權限可以與update權限類似的方式授予到特定屬性上。

grant references (dept_name) on department to Mariano;

6.5 權限的轉移

獲得了某些形式授權的用戶可能被允許將此授權傳遞給其他用戶。在默認方式下，被授予權限的用戶/角色無權把得到的權限再授予給另外的用戶/角色。如果我們在授權時允許接受者把得到的權限再傳遞給其他用戶，我們可以在相應的grant命令后面附加 with grant option子句。

grant select on department to Amit with grant option;

6.6 權限的收回

從一個用戶/角色那里收回權限可能導致其他用戶/角色也失去該權限。這一行為稱作級聯收回。在大多數的數據庫系統中，級聯是默認行為。然而，revoke語句可以申明restrict來防止級聯收回：

revoke select on department from Amit,Satoshi restrict;

總結

以上是生活随笔為你收集整理的数据库系统概念笔记——第4章 中级SQL的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。