1.連接表達(dá)式

基本分類

• 連接成分
  • 包括兩個(gè)輸入關(guān)系胚鸯、連接條件、連接模型
• 連接條件
  • 決定兩個(gè)關(guān)系中哪些元組相互匹配熔任，以及連接結(jié)果中出現(xiàn)哪些屬性
• 外連接
  • 決定如何處理與連接條件不匹配的元組

連接條件

• 自然連接（natural join）
  • 出現(xiàn)在結(jié)果關(guān)系中的兩個(gè)連接關(guān)系的元組在全部公共屬性上取值相等褒链，且公共屬性只出現(xiàn)一次。
• join…using（A~1~疑苔，A~2~,...,A~n~）
  • （A₁甫匹，A₂,...,A_n）是兩個(gè)連接關(guān)系的公共屬性的子集，元組在（A₁惦费，A₂,...,A_n）上取值相等兵迅，且（A₁，A₂,...,A_n）只出現(xiàn)一次薪贫。
• join...on<P>
  • 出現(xiàn)在結(jié)果關(guān)系中的兩個(gè)連接關(guān)系的元組在公共屬性上取值滿足謂詞條件P恍箭，且公共屬性出現(xiàn)兩次。
  • on子句特點(diǎn)：
    • on條件可表示任何謂詞瞧省，可表示比自然連接更豐富的連接條件扯夭。
    • 對于外連接，on條件的表現(xiàn)與where不同鞍匾。
    • 在on子句中指定連接條件交洗，在where中指定其他條件，更清晰易懂橡淑。
    • on條件是外連接聲明的一部分构拳，where子句不是。

內(nèi)連接

• 舍棄不匹配的元組

• 是默認(rèn)連接類型梁棠，關(guān)鍵詞inner可選

  select * from student natural join takes
  

外連接

• 通過在結(jié)果中創(chuàng)建包含空值元組的方式置森，保留哪些在連接中丟失的元組
  • 左外連接（left outer join）
    • 內(nèi)連接加上左側(cè)適配的元組，缺少右側(cè)關(guān)系屬性用null
  • 右外連接
    • 內(nèi)連接加上右側(cè)失配的元組符糊，缺少左側(cè)關(guān)系屬性用null
  • 全外連接（full outer join）
    • 內(nèi)連接+左側(cè)失配元組+右側(cè)失配元組凫海，缺少的關(guān)系屬性用null

關(guān)系的連接

• cross join
  • 兩個(gè)關(guān)系的笛卡爾積
• union join
  • 左邊關(guān)系中失配的元組+右邊關(guān)系中失配的元組
• 對于外連接，連接條件是必須的
• 對于內(nèi)連接男娄，盐碱；連接條件是可選的，沒有連接條件等價(jià)于兩個(gè)關(guān)系的笛卡爾積

2.視圖

• 視圖：作為虛關(guān)系沪伙，對用戶可見的關(guān)系

• 定義視圖

  create view view_name[(col_name[,col_name]...)] as (select...)
  

  • 視圖的屬性名缺省為子查詢結(jié)果中的屬性名，也可以顯式指明县好。
  • 視圖名可以出現(xiàn)在任何關(guān)系名可以出現(xiàn)的地方围橡。

• 撤銷視圖

  drop view view_name
  

• 視圖關(guān)系在概念上包含查詢結(jié)果的元組，但并不進(jìn)行預(yù)計(jì)算和存儲結(jié)果元組缕贡，只存儲與視圖關(guān)系相關(guān)聯(lián)的查詢表達(dá)式翁授。當(dāng)視圖關(guān)系被訪問時(shí)拣播，才計(jì)算產(chǎn)生查詢結(jié)果元組。

物化視圖（materialized view）

• 物化視圖：某些DBS允許存儲視圖收擦，但保證如果定義視圖的實(shí)際關(guān)系改變贮配，視圖也跟著修改。
• 物化視圖維護(hù)：保持物化視圖一直在最新狀態(tài)塞赂。
  • 各數(shù)據(jù)庫產(chǎn)品采用的維護(hù)方式不同泪勒。
• 頻繁使用視圖的應(yīng)用將會從視圖的物化獲益。
• SQL沒有定義指定物化視圖的標(biāo)準(zhǔn)方式宴猾。

視圖更新

• 視圖更新最終是對所設(shè)計(jì)的實(shí)表的更新圆存。由視圖更新導(dǎo)致實(shí)表更新可能帶來問題。

• 如果下面條件都滿足仇哆，稱視圖時(shí)可更新的：

  • from子句中只有一個(gè)數(shù)據(jù)庫關(guān)系
  • select中只包含關(guān)系屬性名沦辙，不包含表達(dá)式、聚集讹剔、distinct聲明
  • 出現(xiàn)在select子句中的屬性限制null
  • 查詢中不包含group by或having子句

• 即便視圖可更新油讯，依然存在問題

  • with check option

    • 視圖定義時(shí)，指定with check option延欠，檢查通過視圖進(jìn)行修改時(shí)陌兑，結(jié)果必須在視圖中，否則拒絕更新衫冻。

    • 無with check option：

      可以更新诀紊，更新后元組不再出現(xiàn)在視圖中；

    • 有with check option：

      不可以更新隅俘，update語句將被dbms拒絕邻奠；

3.事務(wù)

• 事務(wù)由查詢或更新語句的序列組成。
• sql中事務(wù)的開始：
  • 一個(gè)sql執(zhí)行为居，隱含開始了一個(gè)事務(wù)
• 結(jié)束事務(wù)：
  • commit work：完成所有事務(wù)碌宴，提交事務(wù)
  • rollback worl：不能完成所有任務(wù)，回滾事務(wù)
  • commit/rollback：結(jié)束事務(wù)蒙畴，其后的sql屬于新的事務(wù)

4.完整性約束

• 完整性：
  • 數(shù)據(jù)的正確性和相容性
• 完整性檢查：
  • DBMS必須提供一種機(jī)制來檢查數(shù)據(jù)庫中的數(shù)據(jù)是否滿足規(guī)定的條件贰镣，以保證數(shù)據(jù)庫中數(shù)據(jù)是正確的，避免非法的不合語義的錯誤數(shù)據(jù)的輸入和輸出膳凝，即所謂的‘Garbage in garbage out’所造成的無效操作和錯誤結(jié)果碑隆。
• 完整性子系統(tǒng)：
  • 數(shù)據(jù)庫的非法更新情況：
    • 數(shù)據(jù)本身是錯誤的
    • 數(shù)據(jù)原來是正確的，操作或程序錯誤導(dǎo)致輸入數(shù)據(jù)錯誤
    • 由于系統(tǒng)故障蹬音，導(dǎo)致數(shù)據(jù)錯誤
    • 事務(wù)的并發(fā)執(zhí)行產(chǎn)生不正確的結(jié)果
    • 認(rèn)為故意破壞
  • 功能：
    • 監(jiān)督事務(wù)執(zhí)行上煤，檢查是否違反完整性規(guī)則
    • 如有違反，采取相應(yīng)措施（拒絕著淆、報(bào)告劫狠、改正）

完整性控制

• 完整性規(guī)則集
  • 由DBA或程序員事先提供的有關(guān)數(shù)據(jù)約束的一組規(guī)則
• 規(guī)則的組成
  • 約束條件：要檢查什么樣的錯誤（性別為男女）
  • 觸發(fā)條件：什么時(shí)候使用規(guī)則進(jìn)行檢查（插入元組）
  • ELSE子句：若檢查出錯誤拴疤，該怎樣進(jìn)行處理（顯示錯誤）
• 規(guī)則的分類：
  • 域完整性規(guī)則：屬性取值范圍
  • 域聯(lián)系規(guī)則：在一個(gè)或多個(gè)關(guān)系中，屬性間的聯(lián)系影響約束
  • 關(guān)系完整性規(guī)則：更新操作對數(shù)據(jù)庫中值的影響和限制

完整性約束

• 完整性約束通常被看成是數(shù)據(jù)庫模式設(shè)計(jì)過程的一部分

  • create table命令中的約束

    • not null
    • unique
    • check（<P>）

  • alter table table_name add constraint
    

• check子句

  • check子句模擬枚舉

    create table table_name 
    (
        ...
        check(semester in ('Fall','Winter','Spring','Summer'))
    );
    

• 域約束子句

  • 用create domain定義域時(shí)独泞，可以出現(xiàn)check

    create domian age_domian smallint
        check((value>=15)and(value<=25));
    

• 主碼約束

  • 主碼值不允許空呐矾，也不允許重復(fù)出現(xiàn)
  • 意義：關(guān)系對應(yīng)到現(xiàn)實(shí)世界中的實(shí)體集，元組對應(yīng)到實(shí)體懦砂，實(shí)體是相互可區(qū)分的蜒犯，通過主碼來唯一標(biāo)識，若主碼為空孕惜，則出現(xiàn)不可標(biāo)識的實(shí)體愧薛，這是不容許的

• 參照完整性（referential integrity）

  • 一個(gè)關(guān)系中給定屬性集上的取值也在另一關(guān)系的特定屬性集中出現(xiàn)

    • 如果關(guān)系R2的某個(gè)元組t2參照了關(guān)系R1的某個(gè)元組t1，則t1必須存在衫画。

  • 外碼約束：

    • 關(guān)系R包含一個(gè)屬性組毫炉，與另一個(gè)關(guān)系S的主碼相對應(yīng)， 則稱這個(gè)屬性組為R的外碼削罩。
    • 如果關(guān)系R2的外部碼Fk與關(guān)系R1的主碼Pk相對應(yīng)瞄勾，則R2 中的每一個(gè)元組的Fk值或者等于R1 中某個(gè)元組的Pk 值， 或者為空值弥激。（對R2的檢查）

  • 外碼約束定義形式

    foreign key (sno) references s(sno)
    

  • 參照完整性約束與外碼約束不同进陡，通常不要 求被參照關(guān)系上的參照屬性一定是主碼或候 選碼，但這種形式SQL還不能直接聲明微服。

  • references子句與foreign key子句

    • references子句作為屬性定義的一部分趾疚，聲明外碼

      當(dāng)違反約束時(shí)，通常是拒絕執(zhí)行導(dǎo)致破壞完整性的操作以蕴。

    • foreign key 子句可以指明：如果被參照關(guān)系上的刪改或更新動作違反了約束糙麦，系統(tǒng)必須采取一些步驟通過修改參照關(guān)系中的元組來恢復(fù)完整性，而不是簡單拒絕丛肮。

      foreign key (dept_name) references department
        [on <delete|update> cascade
           on <delete|update> set null
           on <delete|update> set default]
      

      • 刪除基本關(guān)系（被參照關(guān)系）元組
        • RESTRICT方式 : 只有當(dāng)依賴關(guān)系中沒有一個(gè)外碼值與要刪除的基本關(guān) 系的主碼值相對應(yīng)時(shí)赡磅，才可以刪除該元組，否則系統(tǒng) 拒絕此刪除操作
        • CASCADE方式 : 將依賴關(guān)系中所有外碼值與基本關(guān)系中要刪除的主碼 值所對應(yīng)的元組一起刪除
        • SET NULL方式 : 刪除基本關(guān)系中元組時(shí)宝与，將依賴關(guān)系中與基本關(guān)系中 被刪主碼值相對應(yīng)的外碼值置為空值
      • 修改基本關(guān)系主碼
        • RESTRICT方式 ：只有當(dāng)依賴關(guān)系中沒有一個(gè)外碼值與要修改的基本關(guān) 系的主碼值相對應(yīng)時(shí)焚廊，才可以修改該元組主碼，否則 系統(tǒng)拒絕此次修改
        • CASCADE方式 ： 將依賴關(guān)系中所有與基本關(guān)系中要修改的主碼值所對 應(yīng)的外碼值一起修改為新值
        • SET NULL方式： 修改基本關(guān)系中元組主碼時(shí)习劫，將依賴關(guān)系中與基本關(guān) 系中被修改主碼值相對應(yīng)的外碼值置為空值

約束

約束的命名

constraint 約束名 <約束條件>   

• Sno char(4) constraint s_pk primary key
  age smallint constraint age_val
    check(age>=15 and age<=25)
  

約束的撤銷和添加

• alter...drop...

  alter...add...

域約束的創(chuàng)建咆瘟、添加與撤銷

• create domain age_domain smallint
    constraint dc_age check(value<=25 and value >=15)
  

• alter domain age_domain
    add constraint dc_age check(value<=35 and value>=15)
  

• alter domain age_domain
    drop constrain dc_age
  

事務(wù)中對完整性約束的違反

• 事務(wù)中某一部會暫時(shí)違反完整性約束
  • 默認(rèn)約束時(shí)立即檢查
• 延遲約束檢查
  • 約束聲明中加入initially deffered子句，在事務(wù)結(jié)束時(shí)檢查诽里。
  • 一個(gè)約束可以被指定位deferrable可延遲的搞疗。對于可延遲的約束,執(zhí)行 set constraints constrain-list deferred 命令作為事 務(wù)一部分,延遲到事務(wù)結(jié)束時(shí)檢查。
• 許多數(shù)據(jù)庫不支持延遲約束

復(fù)雜check條件與斷言

• 全局約束（復(fù)雜check條件，check子句中使用子查詢）

  • 全局約束設(shè)計(jì)多個(gè)屬性間的或多個(gè)關(guān)系間的聯(lián)系

    create table sc
    (
        sno char(4),
        cno char(4),
        grade smallint,
        primary key(sno,cno),
        check(sno in (select sno from s)),
        check(cno in (select cno from c))
    )
    

  • 復(fù)雜的check'檢測開銷可能很大

• 斷言（assertion）是一個(gè)謂詞匿乃，表達(dá)一個(gè)條件。域約束和參照完整性約束就是斷言的特殊形式豌汇。

• 大多數(shù)數(shù)據(jù)庫實(shí)現(xiàn)不支持以上兩種用法

斷言

• 定義：create assertion <斷言名> check <條件>

  斷言是謂詞幢炸，表達(dá)數(shù)據(jù)庫總應(yīng)該滿足的條件

• 一旦定義了斷言，系統(tǒng)驗(yàn)證其有效性拒贱，并且對每個(gè)可能違反該斷言的更新操作都進(jìn)行檢查宛徊。

• 這種檢查會帶來巨大的系統(tǒng)負(fù)載，因此應(yīng)該謹(jǐn)慎使用斷言

• 對斷言“所有X, P(X)”逻澳，是通過檢查“not exists X闸天， ?P(X)”來實(shí)現(xiàn)的

• create assertion ASSE2 check (not exists (select * from SC where Cno in (select Cno fromC where TEACHER = ‘張’)
  and Sno in (select Sno from S where SEX = ‘M’)))
  

5.sql的數(shù)據(jù)類型與模式

默認(rèn)值

• name varchar(20) default ‘張三’

sql中的日期和時(shí)間類型

• date:’2001-04-25’

• time:’09:30:00’

• timestamp:’2001-04-25 09:29:01.45’

• 字符串與日期時(shí)間類型轉(zhuǎn)換：

  cast string to t

• 從日期，時(shí)間中提取單獨(dú)的域

  extract(field from d);

  field可以是year,month,day,hour,minute,second

  時(shí)區(qū)中的信息用timezone_hour,timezone_minute

• 獲取當(dāng)前日期斜做、時(shí)間函數(shù)：

  • current_date
  • current_time
  • local_time
  • current_timestamp,local_timestamp

• 運(yùn)算結(jié)果：interval類型

大對象類型

• SQL提供字符數(shù)據(jù)的大對象數(shù)據(jù)類型clob和二進(jìn)制數(shù)據(jù)的大對象數(shù)據(jù)類型blob
  • bookview clob(10KB)
  • image blob(10MB)
  • movie blob(2GB)

用戶定義類型

• SQL提供獨(dú)特類型（distinct type）SQL:1999
  • create type Dollars as numeric(12,2) final
  • 一些數(shù)據(jù)庫實(shí)現(xiàn)忽略final
• create type和create domain還沒有被大多數(shù)數(shù)據(jù)庫實(shí)現(xiàn)完全支持

創(chuàng)建索引

• 在關(guān)系的屬性上創(chuàng)建索引苞氮，可以提高查詢速度。

• sql沒有給出創(chuàng)建索引的正式語法定義

• 很多數(shù)據(jù)庫支持以下語法

  create index studentID_indexon student(ID)
  

模式瓤逼、目錄與環(huán)境

• 當(dāng)代數(shù)據(jù)庫系統(tǒng)提供三層結(jié)構(gòu)的關(guān)系命名機(jī)制
  • 目錄catalog
  • 模式schema
  • 關(guān)系笼吟、視圖對象 catalog5.univ_schema.course
• 用戶連接到數(shù)據(jù)庫（驗(yàn)證身份）后，有一個(gè)默認(rèn) 的目錄和模式霸旗。默認(rèn)目錄和默認(rèn)模式可以省略
• 每個(gè)數(shù)據(jù)庫連接會建立SQL環(huán)境:包括目錄贷帮、模式 和用戶授權(quán)標(biāo)識。
• 大多數(shù)數(shù)據(jù)庫系統(tǒng)诱告，環(huán)境隨用戶賬戶創(chuàng)建而自動創(chuàng)建撵枢， 此時(shí)模式名被置為用戶賬戶名
• 用create schema和drop schema語句創(chuàng)建、刪除模式

6.授權(quán)

• 安全性控制定義
  • 安全性控制保護(hù)數(shù)據(jù)庫以防止不合法的使用所造成的數(shù)據(jù)泄露和破壞精居。其基本措施是存取控制锄禽。
• 安全性措施
  • 物理級
  • 人際級
  • 操作系統(tǒng)級
  • 網(wǎng)絡(luò)級
  • 數(shù)據(jù)庫系統(tǒng)級

數(shù)據(jù)控制功能

• 數(shù)據(jù)權(quán)限：數(shù)據(jù)的讀取、插入箱蟆、更新沟绪、刪除
• 模式權(quán)限：創(chuàng)建、修改或刪除關(guān)系
• 權(quán)限的授權(quán)空猜、轉(zhuǎn)授和回收
  • 允許用戶把已獲得的權(quán)限轉(zhuǎn)授給其他用戶绽慈，也可以把已授給其他用戶的權(quán)限再回收上來

權(quán)限的授予

• 授權(quán)命令：

  grant <權(quán)限列表> on <表名|視圖名> to <用戶/角色列表|public> [with grant option]

  • 權(quán)限列表包括：select, update, insert, delete, index, alter, drop, resource以及它們的總和all，其中對select辈毯，update坝疼，insert可指定屬性列表

  • with grant option表示獲得權(quán)限的用戶可以把權(quán)限再授予其它用戶。

  • 例：

    grant update(budget) on department to Amy;
    

權(quán)限的收回

• 回收命令：

  revoke 表級權(quán)限 on {表名|視圖名} from {用戶,...|public}

• 收回權(quán)限時(shí)谆沃，若該用戶已將權(quán)限授予其他用戶钝凶，則也一并收回。授權(quán)路徑的起點(diǎn)一定是DBA

• 例：

  revoke insert on S from Amy
  

角色

• 創(chuàng)建角色

  create role Instructor;

• 給角色授權(quán)

  grant select on takes to Instructor;

• 角色可以授予用戶或其他角色：

  grant Instructor to Amy

  create role Dean; 
  grant Instructor to Dean; 
  grant Dean to Alice;
  

• 基于角色的授權(quán)概念并沒有在SQL中指定唁影。

視圖的授權(quán)

• 視圖的訪問會轉(zhuǎn)換成實(shí)際關(guān)系的訪問耕陷，所以創(chuàng)建視圖的用戶在視圖上的權(quán)限掂名，不會超越在實(shí)際關(guān)系上的權(quán)限。
• SQL2003：如果函數(shù)定義有sql security invoker子句哟沫，那么它就在調(diào)用該函數(shù)的用戶權(quán)限下執(zhí)行饺蔑，而不是在函數(shù)定義者的權(quán)限下運(yùn)行。

模式的授權(quán)

• 模式的基本授權(quán)：模式的擁有者才擁有對模式的修改權(quán)限嗜诀。如：增猾警、刪關(guān)系，增隆敢、刪關(guān)系的屬性发皿， 以及增、刪索引拂蝎。

• references權(quán)限穴墅，允許用戶在創(chuàng)建關(guān)系時(shí)聲明外碼。此權(quán)限可以授予到指定屬性上

  grant references(dept_name) on department to Amy

  允許Amy創(chuàng)建關(guān)系能參照department的dept_name

• 因?yàn)橥獯a會限制其他用戶對被參照關(guān)系將來的行為匣屡，所以需要授權(quán)封救。

權(quán)限的轉(zhuǎn)移

• 獲得了某些形式授權(quán)的用戶可能被允許傳遞給其他用戶

授權(quán)圖

• authorization graph
• 結(jié)點(diǎn)時(shí)用戶，根結(jié)點(diǎn)是DBA捣作，有向邊U_i→U_j,表示用戶U_i把某權(quán)限授予給用戶U_j

• 用戶具有權(quán)限的充分必要條件是：當(dāng)且僅當(dāng)存在從根結(jié)點(diǎn)到該用戶結(jié)點(diǎn)的路徑

  
  
  授權(quán)圖.png

• 級聯(lián)收回（默認(rèn)）：收回權(quán)限時(shí)誉结，若該用戶已將權(quán)限授予其他用戶，則也一并收回

• 防止權(quán)限級聯(lián)收回：

  revoke select on department from Amy restrict ;

  如果存在任何級聯(lián)收回券躁，返回錯誤惩坑，不執(zhí)行任何收權(quán)動作。

  • 僅僅收回grant oprtion 也拜，保留其他權(quán)限

    revoke grant option for select on department from Amy;

    一些數(shù)據(jù)庫權(quán)限不支持上述語法以舒。變通方法：收回權(quán)限；然后不帶grant option重新授權(quán)慢哈。

  • 通過角色授權(quán)蔓钟，避免用戶授權(quán)的級聯(lián)收回

    • 設(shè)置會話的當(dāng)前角色為已定義角色
    • 由當(dāng)前角色授權(quán)，授權(quán)語句后加：grant by current_role

數(shù)據(jù)庫級權(quán)限授權(quán)

• 支持多庫的數(shù)據(jù)庫系統(tǒng)中授權(quán)對象可以是數(shù)據(jù)庫

  grant 數(shù)據(jù)庫權(quán)限 to {用戶,...|public}

  • 數(shù)據(jù)庫權(quán)限包括：

    • connect：允許用戶在database語句中指定數(shù)據(jù)庫

    • resource：connect權(quán)限+建表卵贱、刪除表及索引權(quán)限

    • dba：resource權(quán)限+授予或撤銷其他用戶的connect滥沫，resource，dba權(quán)限

      不允許dba撤銷自己的dba權(quán)限键俱。