中級SQL(Chapter 4)
Join
普通連接的條件
A join B on A.id=B.courseid;
根據(jù)on后面的條件將表A與B進行連接
Outer Join
與inner join和natural join不同狭归，減少了在連接中信息的丟失
- left outer join
保留左邊的table中的所有tuple夭坪，沒有對應(yīng)的就設(shè)為null
- right outer join
保留右邊的table中的所有tuple戈二，沒有對應(yīng)的就設(shè)為null
- full outer join
保留兩邊的table中的所有tuple立莉，沒有對應(yīng)的就設(shè)為null
Inner Join
如果沒有說明是outer join臀蛛，默認就是inner join
視圖（View）
出于數(shù)據(jù)安全及隱私的考慮选泻，需要建立視圖
定義
v表示視圖名
create view v as <query expression>;
如果要指定視圖的屬性名，可以如下顯式指定
CREATE VIEW faculty(ID,Name,Department,Total_salary) AS
SELECT
Id, name, dept_name,sum(salary)
FROM
instructor;
使用
視圖使用起來和表沒有什么差別
可以用于select from
可以用于創(chuàng)建其他的視圖
物化視圖（materialized view）
如果用于定義視圖的實際關(guān)系發(fā)生改變籽腕，視圖也得隨之修改
保持物化視圖一直在最新狀態(tài)的過程稱為物化視圖的維護
不同的數(shù)據(jù)庫的更新頻率不一致
有的只要實際關(guān)系一發(fā)生改變丑勤，就進行更新
有的允許物化視圖過時稳强，周期性進行更新
有的在視圖被訪問時進行更新
更新視圖
可以通過對某些符合要求的視圖進行插入刪除更新來影響到原表敷待，但是一般不建議
能進行操作的視圖需要滿足以下條件：
from子句中只有一個table
select中只有屬性名间涵，不包含任何表達式，聚集函數(shù)和distinct等聲明
沒有被select屬性沒有not null約束榜揖，也不構(gòu)成主碼
query expression中不包含group by或having語句
事務(wù)（Transaction）
由查詢或/和更新語句的序列組成勾哩。當(dāng)一條sql語句被執(zhí)行時抗蠢，就隱式地開啟了事務(wù)
結(jié)束事務(wù)
- commit work
提交當(dāng)前事務(wù)，當(dāng)前事務(wù)所做的更新在數(shù)據(jù)庫中永久保存思劳。事務(wù)被提交后迅矛，自動開始新的事務(wù)
- rollback work
回滾當(dāng)前事務(wù)，撤銷該事物對數(shù)據(jù)庫所做的更新
由于有的操作不能彼此分離敢艰，要么都做要么都沒做，所以引入原子事務(wù)
格式如下
begin atomic
<transaction>
end;
其間的transaction所包含的多條語句全部完成才會提交册赛，否則回滾
完整性約束
單關(guān)系約束
- not null 約束
在create table時
name varchar(20) not null
- unique約束
unique(A1,A2,...)
相當(dāng)于指出屬性A1,A2,...構(gòu)成了一組候選碼钠导，如果未約束not null，那么null是可以的
- check約束
check(P)
check(semester in ('Spring','Summer','Fall','Winer'))
或者
check(budget>0)
經(jīng)實測森瘪，MySQL不支持check語句
- 斷言（Assertion）
域約束和參照完整性約束都只是斷言的特殊形式
create assertion <assertion name> check <predicate>;
其他數(shù)據(jù)結(jié)構(gòu)
默認值
在create table的時候可以為屬性設(shè)置默認值牡属，如果一個元組被插入到table中但是沒有被給出該屬性值的時候，就自動填充為默認值
dept_name varchar(20) default 'CS'
索引（Index）
查詢文件中的少量記錄扼睬，利用索引可以不用掃描關(guān)系中的所有元組逮栅，進行高效查詢
create index studentID_index on student(ID);
在student關(guān)系的屬性ID上創(chuàng)建了一個名為studentID_index的索引
大對象數(shù)據(jù)類型
用法和varchar，int一致
clob 字符數(shù)據(jù)的大對象數(shù)據(jù)類型
blob 二進制數(shù)據(jù)的大對象數(shù)據(jù)類型
用戶定義數(shù)據(jù)類型（類似C中的typedef）
定義type
create type Dollars as numeric(12,2) final;
create type Pounds as numeric(12,2) final;
final無實際意義
雖然Dollars和Pounds都是numeric(12,2)窗宇，但是如果把Dollars的值賦給Pounds也會出錯
強制類型轉(zhuǎn)換形式如下
cast(dept.budget to numeric(12,2));
可以用drop type或alter type修改以前創(chuàng)造過的類型
定義domain
create domain DDollars as numeric(12,2) not null;
DDollars域可以像Dollars屬性一樣使用
type與domain之間的差別
domain上可以聲明約束而type上不行
domain沒有強制類型檢查措伐，只要基本類型相同就可以相互賦值
用戶權(quán)限
權(quán)限種類：
select
insert
update
delete
授權(quán)與撤銷
- 通過grant語句授予權(quán)限
grant <權(quán)限列表>
on <關(guān)系名或視圖名>
to <用戶/角色列表>;
可用all privileges代指所有權(quán)限
- 在授予update/insert權(quán)限時，可以指定屬性名军俊，如果不指定侥加，就默認是關(guān)系中所有屬性的update/insert權(quán)限
如果只授予部分屬性的insert權(quán)限，那么insert時粪躬，其余屬性要么是默認值担败，要么是null
grant update(budget)
on departments
to Amit,Chen;
用戶名如果是public，代指系統(tǒng)所有的當(dāng)前用戶和將來用戶
通過revoke語句撤銷權(quán)限
revoke <權(quán)限列表>
on <關(guān)系名或視圖名>
from <用戶/角色列表>;
用戶角色（role）
- 創(chuàng)建角色
create role instructor;
- 角色可以像用戶一樣被授予權(quán)限
grant select on dept to instructor;
角色也可以被授予給用戶镰官，也可以被授予給其他角色
一個用戶/角色的權(quán)限包括：所有直接授予的權(quán)限提前；授予給用戶/角色的角色所擁有的權(quán)限
視圖的授權(quán)
創(chuàng)建視圖的用戶在原關(guān)系上擁有的權(quán)限不會增多，如果原來沒有update權(quán)限泳唠，創(chuàng)建視圖后對視圖也不會有update權(quán)限
默認函數(shù)所具有的權(quán)限是創(chuàng)建者的權(quán)限狈网，但是如果函數(shù)定義有一個額外的sql security invoker子句，那么就在調(diào)用該函數(shù)的用戶的權(quán)限下進行
模式的授權(quán)
原本只有模式的擁有者才能夠執(zhí)行對模式的任何操作
references權(quán)限笨腥，可以像update權(quán)限那樣授予與收回孙援，允許用戶在創(chuàng)建關(guān)系時聲明foreign key
grant references(dept_name) on department to Amit;
意味著用戶Amit可以在創(chuàng)建關(guān)系時聲明外碼參照department中的dept_name
Amit定義的foreign key可以限制其他用戶對department將來的行為
- 如果想要新建關(guān)系r上的check約束，而約束里面有參照department的子查詢扇雕，那也需要references權(quán)限
權(quán)限轉(zhuǎn)移
- 使用以下形式進行
grant select on department to Amit with grant option;
意味著在給用戶Amit授權(quán)department上的select時拓售，還賦予其給其他用戶授予select權(quán)的權(quán)限
即被賦予給Amit的department上的select可以被Amit轉(zhuǎn)移給其他用戶
以此種方式授權(quán)的在revoke時默認級聯(lián)刪除，如果Amit的權(quán)限被revoke了镶奉，那么它給予別的用戶的權(quán)限也將失效
想要防止級聯(lián)刪除權(quán)限础淤，可以加關(guān)鍵字restrict
revoke select on department from Amit restrict;
- 想要收回Amit的權(quán)限轉(zhuǎn)移能力（一些數(shù)據(jù)庫不支持）
revoke grant option for select on department from Amit;
- 某些時候不希望這樣的級聯(lián)崭放，所以就允許權(quán)限通過角色而非用戶被授予（4.6.6）
