第二章 關(guān)系模型與SQL

主流數(shù)據(jù)庫(kù)技術(shù)與實(shí)踐第二章關(guān)系模型與SQL

2.1關(guān)系數(shù)據(jù)庫(kù)的結(jié)構(gòu)2.2關(guān)系代數(shù)運(yùn)算2.3SQL2.1關(guān)系數(shù)據(jù)庫(kù)的結(jié)構(gòu)基本結(jié)構(gòu)表：唯一的表名（關(guān)系）列：屬性，列名唯一。每個(gè)屬性有一組可能的取值（域）。行：記錄，元組。是一個(gè)實(shí)體（各屬性有具體的值）。null：是所有可能域的成員，表明值未知或不存在。數(shù)據(jù)庫(kù)模式數(shù)據(jù)庫(kù)模式（schema）：數(shù)據(jù)庫(kù)的邏輯設(shè)計(jì)（表結(jié)構(gòu)的集合），相當(dāng)與程序設(shè)計(jì)語(yǔ)言中變量的定義。

instructor(ID,name,dept_name,salary)數(shù)據(jù)庫(kù)實(shí)例(instance)：給定時(shí)刻數(shù)據(jù)庫(kù)中數(shù)據(jù)的一個(gè)快照（表記錄的集合），相當(dāng)與程序設(shè)計(jì)語(yǔ)言中變量的值。碼（key，鍵）屬性值唯一標(biāo)識(shí)元組：沒(méi)有兩個(gè)元組（行/記錄）的所有屬性值相同超碼（superkey）：一個(gè)或多個(gè)屬性的集合，可以唯一標(biāo)識(shí)元組。它的任意超集也是超碼。候選碼：任意真子集都不是超碼的最小超碼。主碼：設(shè)計(jì)者選中的用來(lái)區(qū)分元組的候選碼。外碼、參照關(guān)系、被參照關(guān)系、模式圖

圖中表account是參照關(guān)系,其中的branch_name是外碼,表branch是被參照關(guān)系。模式圖可于表示多個(gè)有相互關(guān)系的表的數(shù)據(jù)庫(kù)模式。教師表(instructor)、系部表(department)每個(gè)系有課程列表(course)，包括課程號(hào)(course_id)、課程名(title)、系名(dept_name)和學(xué)分(credits),可能還有先修要求(prerequisites)。學(xué)生列表(student)包括表識(shí)(ID)、姓名(name)、主修的系(dept_name)和已修的學(xué)分(tot_cred)。教室列表(classroom)包括樓名(building)、房間號(hào)(room_number)和容量(capacity)。開(kāi)課列表(section)包括課程號(hào)(course_id)、開(kāi)課號(hào)(sec_id)、年(year)、學(xué)期(semester)、樓名(building)、房間號(hào)(room_number)和時(shí)間段號(hào)(time_slot_id)。系部有教學(xué)任務(wù)列表(teaches)，說(shuō)明每位教師的授課情況。學(xué)生課程注冊(cè)表(takes)，說(shuō)明每位學(xué)生在哪些課程的哪次開(kāi)課中注冊(cè)了。大學(xué)案例關(guān)系表大學(xué)案例關(guān)系表(模式圖)大學(xué)案例關(guān)系表(表數(shù)據(jù)1)course:課程表section:開(kāi)課表大學(xué)案例關(guān)系表(表數(shù)據(jù)2)teaches:教學(xué)任務(wù)表2.2關(guān)系代數(shù)運(yùn)算選擇運(yùn)算(行選擇)例1:顯示物理系(Physics)的所有教師dept_name=“Physics”

(instructor)SQL語(yǔ)句:select*frominstructorwheredept_name=‘Physics’例2:顯示物理系(Physics)中工資大于90000的所有教師dept_name=“Physics”

^salary>90000

(instructor)SQL語(yǔ)句:select*fromloanwheredept_name=‘Physics‘a(chǎn)ndsalar>90000IDnamedept_namesalary22222EinsteinPhysics9500033456GoldPhysics87000IDnamedept_namesalary22222EinsteinPhysics95000投影運(yùn)算(列選擇)例:顯示所有系部的教師號(hào)、姓名和工資ID,name,salary(instructor)SQL語(yǔ)句:selectID,name,salaryfrominstructor關(guān)系運(yùn)算的組合關(guān)系運(yùn)算的結(jié)果還是一個(gè)關(guān)系,可以對(duì)其進(jìn)行其他運(yùn)算。關(guān)系代數(shù)表達(dá)式：多個(gè)關(guān)系代數(shù)運(yùn)算組合成的運(yùn)算式。例:顯示物理系(Physics)的所有教師的姓名name(

dept_name=“Physics”

(instructor))SQL語(yǔ)句:selectnamefrominstructorwheredept_name=‘Physics‘并運(yùn)算(聯(lián)合,結(jié)合)rs：把兩個(gè)關(guān)系的元組合并在一起（去除重復(fù)元組）。條件：兩關(guān)系相容，即要同元（屬性數(shù)相同），并且對(duì)應(yīng)屬性的域相同。例:顯示2009年秋季或2010年春季（或兩者都有）的所有課程號(hào)的集合。

course_id

(

semester=“Fall”Λyear=2009(section))

course_id

(

semester=“Spring”Λyear=2010(section))SQL語(yǔ)句:（selectcourse_idfromsectionwheresemester=‘Fall‘a(chǎn)ndyear=2009）union（selectcourse_idfromsectionwheresemester=‘Spring‘

andyear=2010）集合差運(yùn)算r－s：得到在關(guān)系ｒ中但不在關(guān)系ｓ中的元組。條件：兩關(guān)系相容。例:顯示2009年秋季開(kāi)設(shè)但2010年春季沒(méi)有開(kāi)設(shè)的所有課程號(hào)的集合course_id

(

semester=“Fall”Λyear=2009(section))?

course_id

(

semester=“Spring”Λyear=2010(section))SQL語(yǔ)句:（selectcourse_idfromsectionwheresemester=‘Fall‘

andyear=2009）except（selectcourse_idfromsectionwheresemester=‘Spring‘

andyear=2010）笛卡兒積運(yùn)算r×s：得到關(guān)系ｒ與關(guān)系ｓ中所有元組的交叉組合。結(jié)果中可能重復(fù)的列名要加表名前綴。例1:顯示教師表與教學(xué)任務(wù)表的組合列表。instructor×techesSQL語(yǔ)句:selecti.ID,name,dept_name,salary,t.ID,course_id,sec_id,semester,yearfrominstructorasi，

teches

ast

結(jié)果見(jiàn)P126圖6-8。不同教師的任務(wù)信息組合在一起沒(méi)有實(shí)際意義。例2:查找物理系教師所教授的課程列表，顯示姓名和課程號(hào)。instructor.ID,course_id

(dept_name=“Physics”

(instructor.ID=teaches.ID

(instructorxteaches)))SQL語(yǔ)句:selectinstructor.ID,teches.course_idfrominstructor，techeswhereinstructor.ID=teches.IDanddept_name=‘Physics

‘

另一種方法：instructor.ID,course_id

(instructor.ID=teaches.ID

(dept_name=“Physics”(instructor)xteaches))更名運(yùn)算

x(E)：將關(guān)系代數(shù)表達(dá)式E的結(jié)果命名為x（又稱別名）?？梢詫?duì)表名或結(jié)果列名賦予別名。

：將多元表達(dá)式E的結(jié)果命名為x，各個(gè)屬性分別命名為A1，A2，…，An。作用：避免表名或列名重復(fù)?？梢詫?duì)同一個(gè)表多次引用。例:顯示教師信息表中的最高工資。

步驟1：從教師信息表中找出所有非最大工資的集合；

instructor.salary(

instructor.salary<d,salary(instructorxd

(instructor)))步驟2：求教師信息表與所有非最大工資集合的集合差。salary(instructor)–

instructor.salary(

instructor.salary<d,salary

(instructorxd

(instructor)))SQL語(yǔ)句:（selectinstructor.salaryfrominstructor）except（selectinstructor.salaryfrominstructor,instructorasdwhereinstructor.salary<d.salary）步驟1結(jié)果步驟2結(jié)果集合交運(yùn)算rs

：得到關(guān)系ｒ與關(guān)系ｓ中所有共同的元組集合。等同于r

s=r–(r–s)條件：兩關(guān)系相容。例:顯示2009年秋季和2010年春季都開(kāi)設(shè)的所有課程號(hào)的集合course_id

(

semester=“Fall”Λyear=2009(section))

course_id

(

semester=“Spring”Λyear=2010(section))SQL語(yǔ)句:(selectcourse_idfromsectionwheresemester=‘Fall‘

andyear=2009)

intersect

(selectcourse_idfromsectionwheresemester=‘Spring‘

andyear=2010)自然連接運(yùn)算rs

：二元運(yùn)算，先得到ｒ與ｓ的笛卡兒積，然后基于共同屬性上的相等性選擇，最后去除重復(fù)屬性。形式化定義：r=(A,B,C,D)，s=(E,B,D)，則結(jié)果集合=(A,B,C,D,E)rs=r.A,r.B,r.C,r.D,s.E

(r.B=s.Br.D=s.D

(rxs))例:顯示所有計(jì)算機(jī)系教師名及所教課程的列表。

name,title(

dept_name=“Comp.Sci.”(instructor

teaches

course))SQL:

selectname,titlefrom(courseinnerjoin

(instructorinnerjointeaches

oninstructor.ID=teaches.ID

asitb(name,dept_name,course_id)

oncourse.course_id

=itb.course_id

ascitb(name,dept_name,

title))

wheredept_name=‘Comp.Sci‘;自然連接運(yùn)算instructor

teaches的結(jié)果自然連接運(yùn)算

name,title(

dept_name=“Comp.Sci.”(instructor

teaches

course))的結(jié)果外連接是連接運(yùn)算的擴(kuò)展，當(dāng)一個(gè)關(guān)系中連接數(shù)據(jù)缺失時(shí)可以在結(jié)果集合中用空值（null）填補(bǔ)。左外連接（leftoutjoin）（rs）：將左側(cè)關(guān)系中在右側(cè)關(guān)系沒(méi)有匹配的所有元組加入自然聯(lián)結(jié)結(jié)果中，用空值填補(bǔ)來(lái)自右側(cè)關(guān)系的所有屬性。右外連接（rightoutjoin）（rs）：將右側(cè)關(guān)系中在左側(cè)關(guān)系沒(méi)有匹配的所有元組加入自然聯(lián)結(jié)結(jié)果中，用空值填補(bǔ)來(lái)自左側(cè)關(guān)系的所有屬性。全外連接（fulloutjoin）（rs）：將兩側(cè)關(guān)系中在對(duì)方關(guān)系沒(méi)有匹配的所有元組加入自然聯(lián)結(jié)結(jié)果中，用空值填補(bǔ)來(lái)自對(duì)方關(guān)系的所有屬性。例1:自然連接instructorteaches

外連接例2:左連接

instructorteaches

例3:右連接

teaches

instructor聚集函數(shù)

含義：輸入一個(gè)集合，返回單一值。sum：返回集合中各元素值的和。avg(average)：返回集合中各元素值的平均值。count：返回集合中元素的個(gè)數(shù)。min：返回集合中的最小值。max：返回集合中的最大值。如果需要去除重復(fù)，需要加說(shuō)明，如：count-distinct形式化定義：Gi：分組屬性名Fi：聚集函數(shù)Ai：聚集函數(shù)所計(jì)算的屬性名例:dept_nameg

average(salary)(instructor)SQL:selectdept_name,avg(salary)frominstructor

groupbydept_name2.3SQL背景誕生:SQL讀做Sequel,1970年代由IBM開(kāi)發(fā)，隨關(guān)系數(shù)據(jù)庫(kù)的創(chuàng)建而出現(xiàn)。標(biāo)準(zhǔn)：ANSI和ISO發(fā)布了多個(gè)SQL標(biāo)準(zhǔn)，有SQL-86、SQL-89、SQL-92、SQL:1999和SQL:2003。組成：數(shù)據(jù)定義語(yǔ)言DDL:定義關(guān)系模式，刪除關(guān)系和修改關(guān)系模式。交互式數(shù)據(jù)操縱語(yǔ)言DML：查詢，插入，刪除和修改元組。完整性：定義數(shù)據(jù)完整性約束條件（違背條件的更新將被禁止）。視圖定義：定義視圖。事務(wù)控制：定義事務(wù)的開(kāi)始和結(jié)束。嵌入式SQL和動(dòng)態(tài)SQL：嵌入到通用編程語(yǔ)言中操縱數(shù)據(jù)庫(kù)。授權(quán)：設(shè)定對(duì)關(guān)系和視圖的訪問(wèn)權(quán)限。標(biāo)準(zhǔn)與產(chǎn)品實(shí)現(xiàn)的不同：目前不同的關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)產(chǎn)品對(duì)SQL語(yǔ)言標(biāo)準(zhǔn)的支持程度不同。多數(shù)產(chǎn)品提供標(biāo)準(zhǔn)里所沒(méi)有的部分功能。功能相同的SQL語(yǔ)句的語(yǔ)法也不完全相同，要以產(chǎn)品手冊(cè)為準(zhǔn)。數(shù)據(jù)定義基本類型char(n)：固定長(zhǎng)度為n的字符串。全稱character。varchar(n)：最大長(zhǎng)度為n的可變長(zhǎng)度字符串。全稱(n)：整數(shù)類型（機(jī)器相關(guān)整數(shù)的有限子集)。全稱integer。smallint：小整數(shù)類型（機(jī)器相關(guān)整數(shù)域類型的子集)。numeric(p,d)：定點(diǎn)數(shù)，為小數(shù)d位的p位十進(jìn)制數(shù)字。real，doubleprecision：浮點(diǎn)數(shù)與雙精度浮點(diǎn)數(shù)（精度與機(jī)器相關(guān)）。float(n)：精度至少為n位的浮點(diǎn)數(shù)。SQL內(nèi)建數(shù)據(jù)類型date：日歷日期,包括年（四位）、月、日。time，time(p)：時(shí)間,p為秒后小數(shù)位(默認(rèn)為0)。也可同時(shí)存儲(chǔ)時(shí)區(qū)。timestamp，timestamp(p)：date和time的組合，p為秒后小數(shù)位(默認(rèn)為6)。指定withtimezone時(shí)也可同時(shí)存儲(chǔ)時(shí)區(qū)。例：date‘2001-04-25’time‘09:30:00’timestamp‘2001-04-2510:29:01.45’數(shù)據(jù)定義SQL內(nèi)建數(shù)據(jù)類型類型轉(zhuǎn)換casteast:將字符串e轉(zhuǎn)換為類型t的時(shí)間變量。cast‘2001-04-25’asdate時(shí)間提取extract(fieldfromd)：從時(shí)間變量d中提取域field的值。域field可以取year、month、day、hour、minute或second中的任意一種。時(shí)區(qū)可以用timezone_hour和timezone_minute提取。其他時(shí)間函數(shù)current_date返回當(dāng)前日期,current_time返回帶時(shí)區(qū)的當(dāng)前時(shí)間,localtime返回不帶時(shí)區(qū)的本地時(shí)間,current_timestamp返回帶時(shí)區(qū)的當(dāng)前日期時(shí)間,localtimestamp返回不帶時(shí)區(qū)的當(dāng)前日期時(shí)間。間隔類型interval：代表兩個(gè)日期時(shí)間數(shù)據(jù)的差。如日期X-日期Y的值為X與Y間的間隔天數(shù)（整型）。用戶自定義類型：用戶可以根據(jù)業(yè)務(wù)需要定義特殊的數(shù)據(jù)類型。

例:createtypeDollarsasnumeric(12,2)finalSQL還提供droptype和altertype子句來(lái)刪除和修改以前定義的用戶類型。大對(duì)象類型：用于存儲(chǔ)圖像視頻等大容量數(shù)據(jù)。clob用于字符數(shù)據(jù)，blob用于二進(jìn)制數(shù)據(jù)。

例:book_reviewclob(10KB)imageblob(10MB)movieblob(2GB)關(guān)系的基本操作建立關(guān)系createtabler(A1D1,A2D2,…,AnDn,<完整性約束1>,<完整性約束2>,…,<完整性約束n>)。Ai：屬性名，Di：域類型。完整性約束：用戶根據(jù)需要建立的規(guī)則，更新數(shù)據(jù)時(shí)系統(tǒng)檢測(cè)約束防止破壞數(shù)據(jù)的一致性。域約束

primarykey：主鍵約束，非空且唯一（非重復(fù)）

notnull：非空約束

unique：唯一約束（非重復(fù)）check：條件約束參照完整性約束:外碼參照約束（子集依賴）

foreignkey(屬性名)

references<參照關(guān)系名> ondelete<cascade/setnull> onupdate<cascade/setnull>關(guān)系的基本操作建立關(guān)系例：大學(xué)數(shù)據(jù)表createtable

department

(

dept_namevarchar(20),

buildingvarchar(15),

budget

numeric(8,2))primarykey(dept_name));createtable

course

(

course_idvarchar(7),titlevarchar(50),dept_namevarchar(20),credits

numeric(2,0),primarykey(course_id),foreignkey(dept_name)referencesdepartment));createtable

instructor(

ID

char(5),

namevarchar(20)notnull,

dept_namevarchar(20),

salary

numeric(8,2),primarykey(ID),

foreignkey(dept_name)referencesdepartment));

createtable

section

(

course_idvarchar(7),

sec_id

varchar(8),

semester

varchar(20)notnull,

year

numeric(4,0),

buildingvarchar(15),

room_numbervarchar(7),

time_slot_idvarchar(4),primarykey(course_id,sec_id,

semester,year

),

foreignkey(course_id)referencescourse));

createtable

teaches

(

ID

char(5),

course_idvarchar(7),sec_id

varchar(8),

semestervarchar(20)notnull,

year

numeric(4,0),

primarykey(course_id,sec_id,semester,year

),

foreignkey(course_id,sec_id,semester,year)references

section

)

foreignkey(ID)referencesinstructor

);

createtable

student(

ID

varchar(5),

name

varchar(20)notnull,

dept_name

varchar(20),

tot_cred

numeric(3,0),

primarykey(ID),

foreignkey(dept_name)referencesdepartment));createtable

takes(

ID

varchar(5),

course_id

varchar(8),

sec_id

varchar(8),

semester

varchar(6),

year

numeric(4,0),

grade

varchar(2),

primarykey(ID,course_id,sec_id,semester,year),

foreignkey(ID)references

student,

foreignkey(course_id,sec_id,semester,year)

referencessection);關(guān)系的基本操作建立關(guān)系參照完整性:當(dāng)刪除或修改被參照元組而破壞參照完整性時(shí)，操作被拒絕事務(wù)回滾。例:當(dāng)試圖刪除department表中dept_name為Comp.Sci的行時(shí),因instructor中有dept_name為Comp.Sci的行存在，操作被拒絕事務(wù)回滾。級(jí)聯(lián)刪除

createtable

instructor……foreignkey(dept_name)referencesdepartment

ondeletecascade

onupdatesetnull,……)當(dāng)刪除department表中branch_name為Comp.Sci的行時(shí),

instructor中dept_name為Comp.Sci的行同時(shí)被刪除。而當(dāng)被參照數(shù)據(jù)被修改時(shí)，參照數(shù)據(jù)被設(shè)為空（null）。關(guān)系的基本操作斷言斷言就是將復(fù)雜的約束條件(謂詞)單獨(dú)定義存儲(chǔ)，在建立關(guān)系時(shí)可以直接使用。 createassertion<斷言名>check<謂詞>例:每個(gè)學(xué)生的學(xué)分?jǐn)?shù)值必須等于該學(xué)生成功修完各課程的學(xué)分總和。目前大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)不支持?jǐn)嘌?，?duì)check的支持也有限。斷言可以用觸發(fā)器代替，多數(shù)系統(tǒng)支持觸發(fā)器。刪除關(guān)系：droptabler修改關(guān)系：altertableraddAD在關(guān)系r中增加域?yàn)镈的屬性A

altertablerdropA從關(guān)系r中去除屬性A創(chuàng)建索引createindexstudentID_indexonstudent(ID);主鍵一般不需要建索引（數(shù)據(jù)按主鍵順序存放），對(duì)主鍵以外的列建索引可以加快數(shù)據(jù)檢索的速度。B+樹(shù)是最常用的索引結(jié)構(gòu)。SQL查詢基本查詢selectA1,A2,...,Anfrom

r1,r2,...,rmwherePselect子句select*frominstructor；

得到所有屬性selectname,salary*1.1frominstructor;得到部分屬性,可有算術(shù)表達(dá)式

selectdistinctdept_namefrominstructor;去除重復(fù)的元組（行）

where子句

selectname,salaryfrominstructorwheresalarybetween

50000and100000;可用notbetweenselectname,salaryfrominstructor

wheredept_name=

‘Comp.Sci.'

andsalary>70000;可以有比較運(yùn)算符和邏輯運(yùn)算符（and、or、not）SQL查詢基本查詢

from子句（多關(guān)系查詢）例：查詢所有教師名以及所在系部名和樓名。selectname,instructor.dept_name,building

frominstructor,department

where

instructor.dept_name=department.dept_name

可以用<表名.屬性名>的方式引用指定特定表的屬性，以區(qū)分同名屬性。SQL查詢更名運(yùn)算與元組變量selectID,name,salary/12asmonthly_salaryas在此是運(yùn)算符不能省

frominstructor；查詢比計(jì)算機(jī)系某一個(gè)教師工資高的所有教師。selectdistinctT.name

frominstructorasT,instructorasSas在此是定義變量可以省

whereT.salary>S.salaryandS.dept_name=‘Comp.Sci.’SQL查詢字符串運(yùn)算用單引號(hào)表示字符串。串中的單引號(hào)可用雙引號(hào)?！甀t’sright’->‘It”sright’模式匹配運(yùn)算符like

用百分號(hào)(%)匹配任意子串,下劃線(_)匹配任意一個(gè)字符。

例：‘Perr%’匹配所有以“Perr”開(kāi)頭的字符串。

‘%idge%’匹配包含“idge”的任何字符串，如‘Perridge’、

‘RockRidge’、‘Ridgeway’等。selectdept_namefromdepartment

where

buildinglike'%Walson%’,

用escape定義轉(zhuǎn)義字符表示字符串中的百分號(hào)或下劃線。like‘a(chǎn)b\%cd%’

escape‘\‘匹配所有以“ab%cd”開(kāi)頭的字符串其他字符串函數(shù)

串聯(lián)、提取子串、計(jì)算長(zhǎng)度、大小寫(xiě)轉(zhuǎn)換等。不同產(chǎn)品的字符串函數(shù)集差別較大。SQL查詢排序子句orderbyA1desc，A2asc，A3

升序的asc可省略select*frominstructor

orderbysalary

desc，nameasc;集合運(yùn)算union（集合并）、intersect（集合交）、except（集合差）

自動(dòng)去除重復(fù)。用unionall可以保留重復(fù)。聚集函數(shù)

共5個(gè)sum：返回集合中各元素值的和。avg：返回集合中各元素值的平均值。count：返回集合中元素的個(gè)數(shù)。min：返回集合中的最小值。max：返回集合中的最大值。SQL查詢聚集函數(shù)having子句

指定分組后的選擇條件(必須與groupby同時(shí)用)例:查找顯示平均工資大于42000美元的系部名和平均工資。selectdept_name,avg(salary)frominstructorgroupbydept_namehavingavg(salary)>42000;

條件只對(duì)分組內(nèi)的行測(cè)試SQL查詢聚集函數(shù)having子句

where與having同時(shí)存在時(shí),先找出滿足where條件的所有行，然后分組，再對(duì)各分組測(cè)試having條件，得到滿足條件的分組并輸出。例:找出2009年講授的有2名以上學(xué)生選修的課程，顯示學(xué)生總學(xué)分的平均值。select

course_id,semester,year,sec_id,avg(tot_cred)

fromtasknaturaljoinstudentwhereyear=2009

groupbycourse_id,semester,year,sec_id

havingcount(ID)>=2;

SQL查詢course_idsemesteryearsec_idavg(tot_cred)CS-101Fall20091SQL：P48，表數(shù)據(jù)：P63-64作業(yè)：將SQL結(jié)果填入此表SQL查詢空值null

表示值為空

selectIDfrominstructor

where

salary

isnull也可以用isnotnullunknown表示未知(即不是null

也不是isnull)嵌套子查詢連接詞in:測(cè)試元組是否是集合中的成員。notin是否不是集合成員。例:顯示2009年秋季和2010年春季都開(kāi)設(shè)的所有課程號(hào)的集合。

selectdistinctcourse_idfromsectionwheresemester=’Fall’andyear=2009and

course_idin(selectcourse_id

fromsection

wheresemester=’Spring’andyear=2010);也可以從常量中選擇selectdistinctname

fromsalary

wherenamenotin(‘Mozart’,’Einstein’);嵌套子查詢集合的比較any:集合中的任一個(gè)。早期SQL中使用，現(xiàn)在等同于some。some:集合中的某一個(gè)。all:集合中的全部。例1:查詢比生物系某一教師工資高的所有教師。selectnamefrominstructorwheresalary>some(selectsalaryfrominstructorwheredept_name=’Biology’);例2:查詢平均工資最高的系部。selectdept_amefrominstructorgroupbydept_amehavingavg(salary)>=all

(selectavg(salary)frominstructorgroupbydept_ame);

嵌套子查詢測(cè)試是否為空關(guān)系(記錄存在測(cè)試)exists:存在測(cè)試?yán)?顯示2009年秋季和2010年春季都開(kāi)設(shè)的所有課程號(hào)的集合。selectcourse_id

fromsectionasS

wheresemester=’Fall’andyear=2009and

exists(select*

fromsectionasT

wheresemester=’Spring’andyear=2010

andS.course_id=T.course_id);notexists:不存在測(cè)試?yán)?找出選修了生物系所有課程的學(xué)生。selectdistinctS.ID,S.namefromstudentasSwherenotexists((selectcourse_id當(dāng)生物系所有課程被全部減掉時(shí)fromcoursewheredept_name=’Biology’)生物系開(kāi)設(shè)的課程集

except減去(selectT.course_idfromtakesasTwhereS.ID=T.ID));學(xué)生S.ID所修的所有課程集嵌套子查詢測(cè)試是否存在重復(fù)元組unique:沒(méi)有重復(fù)測(cè)試(唯一)例:找出所有在2009年最多開(kāi)設(shè)一次的課程。selectT.course_id

fromcourseasT

whereunique(selectR.course_id

fromsectionasR

whereT.course_id=R.course_id

andR.year=2009);

notunique:有重復(fù)測(cè)試(不唯一)例:找出所有在2009年最少開(kāi)設(shè)二次的課程。selectT.course_id

fromcourseasT

wherenotunique(selectR.course_id

fromsectionasR

whereT.course_id=R.course_id

andR.year=2009);

復(fù)雜查詢from子句中子查詢（派生關(guān)系）將from中子查詢產(chǎn)生的集合命名為臨時(shí)的關(guān)系例1:查找顯示平均工資大于42000美元的系部名和平均工資。不用having。

selectdept_name,avg_salary

from(selectdept_name,avg(salary)asavg_salary

frominstructor

groupbydept_name)

whereavg_salary>42000;例2:找出工資總額最大的系部的工資總額。selectmax(tot_salary)

from(selectdept_name,sum(salary)

from

instructor

groupby

dept_name)

asdept_total(dept_name,tot_salary);

復(fù)雜查詢with子句定義臨時(shí)關(guān)系（視圖）的子查詢。例1:找出預(yù)算額最大的系部。withmax_budget(value)as

(selectmax(budget)

fromdepartment)

selectbudget

fromdepartment,max_budget

wheredepartment.budget=max_budget.value;部分?jǐn)?shù)據(jù)庫(kù)系統(tǒng)不支持with子句。

例3:找出工資總額大于所有系部平均工資總額的系部。withdept_total(dept_name,value)as各系部工資總額(selectdept_name,sum(salary)

frominstructorgroupbydept_name),dept_total_avg(value)as第二個(gè)with,平均工資總額(selectavg(value)fromdept_total)selectdept_name大于平均工資總額的系部fromdept_total,dept_total_avgwheredept_total.value>=dept_total_avg.value;視圖視圖：在實(shí)際關(guān)系基礎(chǔ)上產(chǎn)生的對(duì)用戶可見(jiàn)的虛關(guān)系。主要作為特定查詢數(shù)據(jù)的臨時(shí)表結(jié)構(gòu)（執(zhí)行時(shí)檢索，不存儲(chǔ)）。視圖定義createviewvas<查詢表達(dá)式>。例:建立物理系2009年秋季所有課程及上課地點(diǎn)的視圖。createviewphysics_fall_2009

as

selectcourse.course_id,sec_id,building,room_number

fromcourse,section

wherecourse.course_id=section.course_id

andcourse.dept_name=’Physics’

andsection.semester=’Fall’

andsection.year=’2009’;視圖使用和普通表一樣。例：查詢物理系2009年秋季在Walson樓里上課的所有課程。

selectcourse_id

fromphysics_fall_2009

wherebuilding=‘Walson’;視圖物化視圖一旦視圖引用的關(guān)系的數(shù)據(jù)發(fā)出變化，視圖的數(shù)據(jù)也應(yīng)該隨之更新。一般情況下數(shù)據(jù)庫(kù)系統(tǒng)只存儲(chǔ)視圖定義，在被引用時(shí)用定義替換。特定數(shù)據(jù)庫(kù)系統(tǒng)允許視圖關(guān)系被存儲(chǔ)，稱為物化視圖。當(dāng)實(shí)際關(guān)系發(fā)生變化時(shí)，數(shù)據(jù)庫(kù)系統(tǒng)負(fù)責(zé)進(jìn)行物化視圖的及時(shí)更新，稱為視圖維護(hù)。視圖更新視圖是關(guān)系的映射，更新會(huì)使原關(guān)系中的數(shù)據(jù)出現(xiàn)問(wèn)題，所稱一般情況下不允許更新。如果滿足下列條件的情況下可以更新：form字句中只有一個(gè)關(guān)系；select

子句中只包含屬性名，沒(méi)有表達(dá)式、聚集或distinct聲明；select

子句中的屬性都可以取空值，即屬性沒(méi)有notnull約束，也不是主碼的一部分；查詢中不包含groupby或having子句。

數(shù)據(jù)庫(kù)的修改插入

按關(guān)系定義時(shí)的屬性順序插入

insertintocourse

values(’CS-437’,’DatabaseSystems’,’Comp.Sci.’,4);insertintostudent

values(’3003’,’Green’,’Finance’,null);

不違反約束時(shí)可以有空值按指定的屬性順序插入insertintocourse(course_id,title,dept_name,credits)

values(’CS-437’,’DatabaseSystems’,’Comp.Sci.’,4);從其他表檢索數(shù)據(jù)插入insertintostudent

selectID,name,dept_name,0

from

instructor刪除

deletefrominstructor;

刪除全部數(shù)據(jù)

deletefrominstructor

wheredept_name=’Finance’;

刪除滿足條件的數(shù)據(jù)

deletefrominstructor

wheredept_namein(selectdept_name

fromdepartment

wherebuilding=’Watson’);數(shù)據(jù)庫(kù)的修改更新例：年度工資增長(zhǎng)，10萬(wàn)元以上的教師漲3%，10萬(wàn)元及以下的教師漲5%。

updateinstructor

setsalary=salary*1.03

wheresalary>100000;

updateinstructor

setsalary=salary*1.05

wheresalary<=100000;順序很關(guān)鍵，順序不同結(jié)果不同。

updateinstructor

setsalary=case

whensalary<=100000thensalary*1.05

elsesalary*1.03

end事務(wù)

事務(wù)：一段查詢和（或）更新語(yǔ)句（一條或多條SQL語(yǔ)句）的整體。commitwork：提交當(dāng)前事務(wù)。rollbackwork：回滾當(dāng)前事務(wù)。連接關(guān)系連接類型與條件

開(kāi)設(shè)課程表course

預(yù)備課程表prereq

course中”CS-315”在prereq中沒(méi)有對(duì)應(yīng)的數(shù)據(jù)，

prereq中”CS-347”在course中沒(méi)有對(duì)應(yīng)的數(shù)據(jù)。連接類型內(nèi)連接innerjoin左外連接leftouterjoin右外連接rightouterjoin全外連接fullouterjoin連接類型自然連接(以同名屬性相等為條件)natural條件連接on<謂詞>using(A1,A2,…,An)連接關(guān)系

例1:on條件內(nèi)連接courseinnerjoinprereq

oncourse.course_id=prereq.course_id

例2:on條件左外連接

courseleftouterjoin

prereq

oncourse.course_id=prereq.course_id

例3:on條件右外連接

courserightouterjoin

prereq

oncourse.course_id=prereq.course_id連接關(guān)系

例4:自然連接coursenaturaljoinprereq注意與

innerjoin

on的不同

例5:自然左外連接

coursenaturalleftouterjoin

prereq

例6:自然右外連接

coursenaturalrightouterjoin

prereq連接關(guān)系

例7:自然全外連接coursenaturalfullouterjoin