數(shù)據(jù)庫系統(tǒng)教學(xué)課件：第7講-中級SQL

1、第7講:(第4章) 中級SQL課程名稱: 數(shù)據(jù)庫系統(tǒng) -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有何不同？有何不同？有何不同？查詢結(jié)果表雖然相同，但Where允許按指定屬性(可不同名)連接，且在連接表中連接屬性都會出現(xiàn)(ID兩次) 。雖然都是按相同屬性連接，但using允許按指定屬性、而非兩表所有同名屬性連接作用及查詢結(jié)果表都相同，在連接表中連接屬性都會出現(xiàn)(ID重復(fù)出現(xiàn)兩次)。on連接示例3O

3、N連接示例 p.64一 SQL支持的表間連接方式42.外連接的不同方式3）外連接也可與on和using一起使用嗎，其作用？ 一 SQL支持的表間連接方式2）給出這兩個表左外連接、右外連接和全外連接的結(jié)果？ courseprereq可以，作用與前面join情形類似(p.67)(這4種連接類型和3種條件可任意組合)5二 視圖1）什么是視圖，如何產(chǎn)生？ 1）可以在任何QL語句中像表一樣的被使用！2）增強查詢能力且方便(用戶/程序員)使用！3）還可以提供數(shù)據(jù)訪問安全控制(隱藏數(shù)據(jù))！4）作為外模式(1級映射)有利于應(yīng)用獨立性！ 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）你認(rèn)為視圖有哪些作用(好處)？ 2）在視圖上還也可以定義新的視圖嗎？ 視圖采用create view語句定義，可以定義為任何一個SQL語句,無實際數(shù)據(jù)，虛表，有利于數(shù)據(jù)一致性！視圖上可以在定義新的視圖！4）(講解)可以通過視圖更新數(shù)據(jù)嗎？ 僅在少數(shù)簡單視圖(updatable)上可以更新數(shù)據(jù)！P.72討論2. 什么是視圖，有何作用？ 視圖關(guān)系模式1級映射2級映射6三 事務(wù)p.731）怎

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

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

8、性約束屬性(唯一)完整性約束復(fù)雜條件(來自他表select結(jié)果限定取值范圍)完整性約束復(fù)雜條件(標(biāo)量集合限定取值范圍)完整性約束(注：復(fù)雜條件甚至可以是對表上多個屬性間表達(dá)式的限制約束)3.其它數(shù)據(jù)完整性約束：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)設(shè)置為空/默認(rèn)值1)不寫時拒絕刪除三種參照約束方式：create assertion check ;例子：約束要求：student每個元組的tot_cred(學(xué)生的總學(xué)分)取值應(yīng)等于該生所修完課程的學(xué)分總和(關(guān)系takescourse的credits)3）(講解)什么是斷言，有什么作用？ p.76四 完整性約束p.73-76 當(dāng)數(shù)據(jù)更新時，保持謂詞為真。(否則拒絕更新)但作用有利有弊3.斷言9五 授權(quán) p.81討論5. 在開放環(huán)境中，如何限制用戶對數(shù)據(jù)的合法訪問？ 1）這些語句說明了什么樣的訪問權(quán)限變化？1. 表(關(guān)系)上的授權(quán)；public ？；通過授權(quán)！只有授權(quán)用戶才能

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

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

12、仍然看不到數(shù)據(jù)！grant select on geo_instructor to geo_staff(roll角色)3）geo_staff中用戶能通過該語句查看到數(shù)據(jù)嗎？ 2）視圖上也可以授權(quán)(查看/修改/刪除數(shù)據(jù))？在表instructor上創(chuàng)建一個視圖geo_instructor 將視圖上的查看權(quán)授予一個角色geo_staff注：1）視圖的創(chuàng)建者，自然擁有該視圖上的所有權(quán)限！ 2）函數(shù)與過程的創(chuàng)建者，自然擁有其上所有權(quán)限！(p.83) 用戶可以函數(shù)與過程(&5.2)，并可對其他用戶授予execute執(zhí)行權(quán)。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