1、第7講:(第4章) 中級SQL課程名稱: 數據庫系統 -2一 SQL支持的表間連接方式 P.63（方式4）(P.63)select *from student join takes on student.id=takes.id; select *from student, takeswhere student.ID = takes.ID;（方式1） (p.39)select name, course_idfrom instructor, teacheswhere instructor.ID= teaches.ID;（方式2） (p.39)select name, course_idfrom i

2、nstructor natural join teaches;（方式3）(p.40)select name, titlefrom (instructor natural join teaches) join course using(course_id);1.(自然/等值)連接的不同方式 P.63有何不同？有何不同？有何不同？查詢結果表雖然相同，但Where允許按指定屬性(可不同名)連接，且在連接表中連接屬性都會出現(ID兩次) 。雖然都是按相同屬性連接，但using允許按指定屬性、而非兩表所有同名屬性連接作用及查詢結果表都相同，在連接表中連接屬性都會出現(ID重復出現兩次)。on連接示例3O

3、N連接示例 p.64一 SQL支持的表間連接方式42.外連接的不同方式3）外連接也可與on和using一起使用嗎，其作用？ 一 SQL支持的表間連接方式2）給出這兩個表左外連接、右外連接和全外連接的結果？ courseprereq可以，作用與前面join情形類似(p.67)(這4種連接類型和3種條件可任意組合)5二 視圖1）什么是視圖，如何產生？ 1）可以在任何QL語句中像表一樣的被使用！2）增強查詢能力且方便(用戶/程序員)使用！3）還可以提供數據訪問安全控制(隱藏數據)！4）作為外模式(1級映射)有利于應用獨立性！ create view physics_fall_2009 as (P.6

4、8) 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; create view physics_fall_2009_watson as select course_id, room_number from physics_fall_2009 w

5、here building= Watson; (P.68) create view faculty as select ID, name, dept_name from instructor ; (P.68)3）你認為視圖有哪些作用(好處)？ 2）在視圖上還也可以定義新的視圖嗎？ 視圖采用create view語句定義，可以定義為任何一個SQL語句,無實際數據，虛表，有利于數據一致性！視圖上可以在定義新的視圖！4）(講解)可以通過視圖更新數據嗎？ 僅在少數簡單視圖(updatable)上可以更新數據！P.72討論2. 什么是視圖，有何作用？ 視圖關系模式1級映射2級映射6三 事務p.731）怎

6、么解決上述問題？ 討論3：什么是事務，有何作用？ 通過銀行卡為校園一卡通充值的處理過程：1）輸入充值金額100元2）銀行卡下賬100元3）一卡通上賬100元異常現象:若此時操作失敗(因停電、網斷等)，導致銀行卡少100元，而一卡通錢仍未增加！事務： SQL允許將多個數據操作捆綁為一個邏輯單元 要么這些操作全部完成，要么一個也未執行！例子：(銀行卡下賬100元，一卡通上賬100元)兩事務操作-1個事務SQL提供的相關命令為：Commit work-提交事務；Rollback work-撤銷事務；Begin atomic-事務(操作語句)開始； End-事務(操作語句)結束；7四 完整性約束p.7

7、2-73 check(time_slot_id in(select time_slot_id from time_slot);Check (salary10000)，unique，討論4. SQL提供哪些類型的完整性約束，作用？ 1. 鍵完整性約束（主碼/主鍵） 關系(模式)必需有一個主碼，來區分不同元組！ SQL采用primary key來定義！2.參照完整性約束（外碼/外鍵） 用另一關系的主碼，來約束屬性取值的有效性！ SQL采用foreign key references 來定義！1）SQL還提供哪些方式的數據完整性約束？ 鍵完整性約束參照完整性約束屬性(非空)完整性約束屬性(范圍)完整

8、性約束屬性(唯一)完整性約束復雜條件(來自他表select結果限定取值范圍)完整性約束復雜條件(標量集合限定取值范圍)完整性約束(注：復雜條件甚至可以是對表上多個屬性間表達式的限制約束)3.其它數據完整性約束：1. 完整性約束82.外鍵約束方式create table course ( (P.73-74)dept_name varchar(20),foreign key (dept_name) references department on delete cascade on update cascade,);2）這里的on約束起到什么作用？ -2)連帶刪除(修改)set nul/set d

9、efualt -3)設置為空/默認值1)不寫時拒絕刪除三種參照約束方式：create assertion check ;例子：約束要求：student每個元組的tot_cred(學生的總學分)取值應等于該生所修完課程的學分總和(關系takescourse的credits)3）(講解)什么是斷言，有什么作用？ p.76四 完整性約束p.73-76 當數據更新時，保持謂詞為真。(否則拒絕更新)但作用有利有弊3.斷言9五 授權 p.81討論5. 在開放環境中，如何限制用戶對數據的合法訪問？ 1）這些語句說明了什么樣的訪問權限變化？1. 表(關系)上的授權；public ？；通過授權！只有授權用戶才能

10、查看(/插入/修改/刪除)相關表中的數據.注:表的創建者, 自然擁有表上的一切權限.用戶在branch表上的查看()權被收回。作用及好處？還可以創建子角色；將instructor表上的查看(插入/)權授予用戶。所有用戶可以建立角色roll(用戶群)。當將某權限授予角色時，該用戶群均有該使用權限。角色名用戶名簡化權限管理103.授權轉移 圖4-10示例：表teaches上的update更新權作用：1）描述在一張表上某種授權的當前狀態，便于系統動態管理授權；2）當DBA或具有權限的用戶(樹上節點)進行授權時，樹擴展(生長)； 3）當DBA或具有權限的用戶(樹上節點)回收權限時，樹收縮(枯萎)；變化

11、示例1：U2執行語句:grant update on teaches To U6;U6變化示例2：U1執行語句：revoke update on teaches from U5;DBA執行語句：revoke update on teaches from U2;112. 視圖(虛關系)上的授權P.82五 授權 p.81create view geo_instructor as (select * from instructor where dept_name=Geology);select *from geo_instructor;如果該用戶在instructor上沒有獲得select授權，則他

12、仍然看不到數據！grant select on geo_instructor to geo_staff(roll角色)3）geo_staff中用戶能通過該語句查看到數據嗎？ 2）視圖上也可以授權(查看/修改/刪除數據)？在表instructor上創建一個視圖geo_instructor 將視圖上的查看權授予一個角色geo_staff注：1）視圖的創建者，自然擁有該視圖上的所有權限！ 2）函數與過程的創建者，自然擁有其上所有權限！(p.83) 用戶可以函數與過程(&5.2)，并可對其他用戶授予execute執行權。12UDT(User-Defined Types)&Domian六 *(略講)用戶自定義的類型 p.78-79create domain person_name char(20) not null;create domain degree_level varchar(10)constraint degree_level_testcheck (value in (Bachelors, Masters, Doctorate);create type Dollars as numeric (12,2) f