資料庫系統概念 第4章 中級SQL

第四章 中級SQL

4.1 連線表示式

SQL提供了連線運算的其他形式，通過這些形式能在結果中包含被自然連線排除在外的元組。

4.1.1 連線條件

SQL使用on來在參與連線的關係上設定通用的謂詞，和where相似，但在外連線運算中表現結果與where不同。

on的用法如下。

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

上面的on條件表明，若來自student的元組的ID屬性和來自takes的元組的ID屬性相同，則把它們連線起來，作為結果的元組。

上述結果雖然和下列語句在邏輯上相同

select * from student natural join takes;

但是on語句並不取出重複的元組，也就是說結果關係中會出現兩個ID屬性，student.ID和takes.ID

實際上，等價語句是

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

4.1.2 外連線

外連線的引入

考慮查詢“找出所有學生的選課資訊”，如果使用如下查詢語句。

select * from student natural join takes;

此時，對於那些沒有選課的學生來說，即在takes中沒有對應的ID，那麼這個學生的選課結果不會被顯示，而我們希望即使學生沒有選課就設其選課資訊為null。此時，我們便引入了外連線。

總的來說，外連線在內連線的結果上通過在一些屬性上設定null來保留原來在內連線的結果上不匹配的元組。

外連線有三種，左外連線、右外連線和全外連線。

• 左外連線。先做內連線，然後把左邊關係中未被包含的元組包含進去，對於這些元組在右邊關係的項被置為null。
• 右外連線。先做內連線，然後把右邊關係中未被包含進去的元組包含進去，對於這些元組的左邊關係中的項被置為null。
• 全外連線。左外連線和右外連線的並集。

使用外連線後，查詢“找出所有學生的選課資訊”可以被如下表示。

select *
from student left outer join takes;

使用左外連線後，即使存在一些不選課的學生，其也被包含在結果中，只不過其在選課資訊的值被置為null。

相似的，右外連線和全外連線表示為right outer join和full outer join

注意，on和where在外連線上表現得結果是不一樣的。在外連線中，on作用於內連線的結果，也就是對被補上null然後插入的元組不起作用。但where是在內連線和補上null插入後得到的結果上發生作用。例如，上面的查詢等價於

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

但下面的查詢

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

不存在包含了空值屬性的元組。

4.2 檢視

4.2.1 檢視的定義

很多時候，我們並不希望讓所有使用者看到整個關係的邏輯模型。出於安全考慮，需要向使用者隱藏特定資料。因此，SQL中引入了檢視。檢視是通過查詢語句定義的，但是，其中並不預先包含資料，而是在使用過程中才通過定義它的查詢語句計算出來。

SQL使用create view來定義檢視

create view view_name as <query expression>

view_name是檢視名稱，<query expression>是任何合法的查詢語句。

例如，可以定義一個檢視，“列出Physics系在2009年秋季學期所開設的所有課程段”

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

4.2.2 SQL查詢中使用檢視

檢視可以被當成一個關係來使用，例如，“找出所有在2009年秋季學期在Waston大樓開設的Physics課程”

select course_id
from physics_fall_2009
where building = 'Watson';

4.2.3 物化檢視

特定資料庫允許儲存檢視關係，但是它們保證，如果用於定義檢視的實際關係被改變，檢視也跟著修改，這樣的檢視被稱為物化檢視。

4.2.4 檢視更新

儘管對查詢而言，檢視是一個有用的工具，但如果我們用它們來表達更新、插入或刪除，它們可能帶來嚴重的問題。困難在於，用檢視表達的資料庫修改必須被翻譯為對資料庫邏輯模型中實際關係的修改。所以，檢視如果被稱為可更新的，需要滿足如下限制

• from子句中只有一個資料庫關係。
• select子句中只包含關係的屬性名，不包含任何表示式、聚集函式和distinct宣告。
• 在任何沒有出現在select子句中的屬性可以取空值。
• 查詢部分不包含group by和having子句。

4.3 事務

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

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

如果一個事務還未完成commit work，其影響將被回滾。在執行事務過程中，若發生斷點和系統崩潰，在資料庫重啟時回滾將被自動執行。若事務已經完成commit work，在其影響將不能通過回滾來撤銷。

事務的完成是具有原子性的。即一個事務或者在完成所有步驟後提交其行為，或者在不能完成其所有動作的情況下回滾其所有動作。

在很多SQL語句中，預設方式下每個SQL語句自成一個事務，執行完後便自動提交。如果一個事務要完成多個SQL語句，則需要關閉自動提交。

4.4 完整性約束

完整性約束保證授權使用者對資料所作的修改不會破壞資料的一致性。

4.4.1 單個關係上的約束

單個關係上的約束包括

• not null。例如，name varchar(20) not null
• unique。例如，unique(Aj1, Aj2, ..., Ajn)，unique定義的屬性元組構成候選碼。
• check(<謂詞>)。例如，check(semester in ('Fall', 'Winter', 'Spring', 'Summer'))。check子句來保證屬性值滿足的條件。

上面3條語句是在create table上加入的。

4.4.2 斷言

一個斷言就是一個謂詞，它表達了我們希望資料庫總能滿足的一個條件。域約束和參照完整性約束是斷言的特殊形式。斷言的定義一般如下形式

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

注意，還沒有一個廣泛使用的資料庫系統支援斷言結構。

4.5 SQL的資料型別與模式

4.5.1 SQL的日期和時間型別

SQL支援日期型別的資料，如下所示

date # 日曆日期，包括年、月和日，如'2001-04-25'
time # 一天中的時間，包括時、分和秒，如'09:30:00'，可以用time(p)來指定秒的小數點後的數字數，用time with time zone來將時區和時間一起儲存
timestamp # date和time的組合，如'2001-04-25 10:29:01.45'。可以用變數timestamp(p)來指定秒的小數點後的數字數，用timestamp with time zone來將時區和時間一起儲存

我們可以利用case e as t形式的表示式來將一個字串e轉換成型別t，其中t是data, time, timestamp中的一種，而且字串也必須符合正確的格式。

我們可以利用extract (field from d)，從date或time的值d中提取出單獨的域，這裡的域可以是year, month, day, hour, minute, second中的任意一種。時區資訊可以用timezone_hour和timezone_minute來提取。

還有其他的函式可以獲取當前的日期和時間。current_date返回當前日期，current_time返回當前時間（帶有時區），local_time返回當前的本地時間（不帶時區）。timestamp由current_timestamp和local_timestamp返回。

4.5.2 預設值

SQL允許在建立表中使用default來指定預設值，如下所示

create table student(
	...
    tot_cred numeric(3, 0) default 0,
    ...
);

4.5.3 建立索引

索引（index）可以幫助資料庫高效地找到關係中那些在索引屬性上取給定的元組。例如，如果我們在student關係的屬性ID上建立了索引，資料庫系統就不用掃描關係中的其他元組，就可以直接找到任何像22201或44553那樣具有指定ID值的記錄。

常用資料庫一般使用如下語句來定義索引

create index studentID_index on student(ID);

上述語句在student關係的屬性ID上建立了一個名為studentID_index的索引。

4.5.4 大物件型別

SQL提供字元資料的大物件型別clob和二進位制資料的大物件資料型別blob，如下所示

book_revoew clob (10kb)
image blob (10MB)

4.5.5 使用者定義的型別

SQL標準定義了可使用create type和create domain來分別定義型別和域，但這些結構形式還沒有被大多數資料庫實現完全支援。

4.5.6 create table的擴充套件

SQL提供了create table like來支援基於某一個表的結構建立一個新表，如下所示。

create table temp like student;

但上述語句只是建立了一個和student結構完全相同的新表temp，其內容為空。

我們也可以基於查詢結果來建立一個新表，如下所示。

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

如果不加data將不會插入資料。新表的屬性是根據查詢結果的屬性來推匯出來的，當然也可以在新表的名稱後指定屬性名。

4.6 授權

我們可以會限制使用者在資料庫上的許可權，並通過授權的方式來提供許可權。許可權的授權有兩種，對資料的授權（select, update, insert, delete）和對資料庫模式的授權（允許使用者建立、修改和刪除關係）。當使用者執行了未被授權的許可權時，系統將拒絕執行。

4.6.1 許可權的授予與收回

許可權的授予使用grant，如下所示

grant <許可權列表>
on <關係名或檢視>
to <使用者或角色>

例如

grant select on department to Amit, Satashi;

該語句授予了使用者Amit和Satashi在department關係上的select許可權。

有時我們希望許可權可以具體到部分屬性，如下所示

grant update(budget)
on department
to Amit, Satashi;

該語句授予了使用者Amit和Satashi在department關係上的update許可權，但只能update budget。我們也可以在授予insert許可權中指定屬性，其餘未被指定的屬性要麼被指定為null，要麼被指定為default值。

將許可權授予給pulic相當於對當前所有使用者和將來使用者的授權。

我們可以使用revoke收回許可權，用法語句和grant時完全相同，如下所示

revoke update(budget)
on department
to Amit, Satashi;

授予使用者的許可權也可以被使用者在授予其他使用者，此時許可權的收回將會更加複雜。

4.6.2 角色

在SQL中，我們可以將許可權授予角色。此時，當新使用者建立時，我們直接將角色授予該使用者，使得新使用者直接獲得角色擁有的許可權，省去了單獨為新使用者授權的麻煩。

因此，一個使用者或角色擁有的許可權包括兩部分

• 直接授予的許可權。
• 被授予的角色帶來的許可權。

建立角色使用create role，如下所示

create role instructor;

然後我們可以將許可權授予角色

grant select
on department
to instructor;

然後角色可以被授予到使用者或角色，如下所示

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

4.6.5 許可權的轉移

我們可以在執行grant命令時附加with grant option來允許被授權使用者將該授權再次授予到其他使用者，如下所示。

grant select on department to Amit with grant option;

許可權從一個使用者到另一個使用者的傳遞可以表示為授權圖，但是，相互授權的行為可以導致授權圖中存在迴路。

使用者存在許可權的充要條件是存在從授權圖的根節點到代表該使用者頂點的路徑。

4.6.6 許可權的收回

當收回一個使用者的許可權時，其授予其他使用者的許可權也會被預設級聯收回，但可以在revoke後加上restrict來防止級聯收回。