數(shù)據(jù)庫(kù)習(xí)題講解課件

數(shù)據(jù)庫(kù)系統(tǒng)概論

AnIntroductiontoDatabaseSystems2012.6.7AnIntroductiontoDatabaseSystem2.2.1基本關(guān)系操作

常用的關(guān)系操作查詢：選擇、投影、連接、除、并、交、差數(shù)據(jù)更新：插入、刪除、修改查詢的表達(dá)能力是其中最主要的部分選擇、投影、并、差、笛卡爾基是5種基本操作關(guān)系操作的特點(diǎn)集合操作方式：操作的對(duì)象和結(jié)果都是集合，一次一集合的方式AnIntroductiontoDatabaseSystem集合運(yùn)算符∪-∩×并差交笛卡爾積比較運(yùn)算符＞≥＜≤＝<>大于大于等于小于小于等于等于不等于運(yùn)算符含義運(yùn)算符含義表2.4關(guān)系代數(shù)運(yùn)算符

概述AnIntroductiontoDatabaseSystem2.4關(guān)系代數(shù)

傳統(tǒng)的集合運(yùn)算二目

并、差、交、笛卡爾積R和S具有相同的目n（即兩個(gè)關(guān)系都有n個(gè)屬性）相應(yīng)的屬性取自同一個(gè)域AnIntroductiontoDatabaseSystem1.并（Union）R∪S

仍為n目關(guān)系，由屬于R或?qū)儆赟的元組組成R∪S={t|t

R∨tS}AnIntroductiontoDatabaseSystem2.差（Difference）R-S

仍為n目關(guān)系，由屬于R而不屬于S的所有元組組成

R-S={t|tR∧tS}AnIntroductiontoDatabaseSystem3.交（Intersection）R∩S仍為n目關(guān)系，由既屬于R又屬于S的元組組成R∩S={t|t

R∧tS} R∩S=R

–(R-S）AnIntroductiontoDatabaseSystem專門的關(guān)系運(yùn)算(續(xù))(a)

Student4)學(xué)生-課程數(shù)據(jù)庫(kù):學(xué)生關(guān)系Student、課程關(guān)系Course和選修關(guān)系SC學(xué)號(hào)Sno姓名Sname性別Ssex年齡Sage所在系Sdept200215121李勇男20CS200215122劉晨女19IS200215123王敏女18MA200215125張立男19ISAnIntroductiontoDatabaseSystem專門的關(guān)系運(yùn)算(續(xù))

(c)SC學(xué)號(hào)Sno課程號(hào)Cno成績(jī)Grade200215121192200215121285200215121388200215122290200215122380AnIntroductiontoDatabaseSystem選擇（續(xù)）3)選擇運(yùn)算是從關(guān)系R中選取使邏輯表達(dá)式F為真的元組，是從行的角度進(jìn)行的運(yùn)算σAnIntroductiontoDatabaseSystem選擇（續(xù)）(a)

Student學(xué)號(hào)Sno姓名Sname性別Ssex年齡Sage所在系Sdept200215121李勇男20CS200215122劉晨女19IS200215123王敏女18MA200215125張立男19IS[例1]查詢信息系（IS系）全體學(xué)生

σSdept

='IS'(Student)

或σ5='IS'(Student)

AnIntroductiontoDatabaseSystem[例1]查詢信息系（IS系）全體學(xué)生

σSdept

='IS'(Student)

或σ5='IS'(Student)

SnoSnameSsexSageSdept200215122劉晨女19IS200215125張立男19ISAnIntroductiontoDatabaseSystem選擇（續(xù)）(a)

Student學(xué)號(hào)Sno姓名Sname性別Ssex年齡Sage所在系Sdept200215121李勇男20CS200215122劉晨女19IS200215123王敏女18MA200215125張立男19IS[例2]查詢年齡小于20歲的學(xué)生 σSage<20(Student) 或σ4<20(Student)AnIntroductiontoDatabaseSystem選擇（續(xù)）[例2]查詢年齡小于20歲的學(xué)生 σSage<20(Student) 或σ4<20(Student) 結(jié)果：

SnoSnameSsexSageSdept200215122劉晨女19IS200215123王敏女18MA200215125張立男19ISAnIntroductiontoDatabaseSystem2.投影（Projection）2）投影操作主要是從列的角度進(jìn)行運(yùn)算但投影之后不僅取消了原關(guān)系中的某些列，而且還可能取消某些元組（避免重復(fù)行）πAnIntroductiontoDatabaseSystem投影（續(xù)）(a)

Student學(xué)號(hào)Sno姓名Sname性別Ssex年齡Sage所在系Sdept200215121李勇男20CS200215122劉晨女19IS200215123王敏女18MA200215125張立男19IS[例3]查詢學(xué)生的姓名和所在系即求Student關(guān)系上學(xué)生姓名和所在系兩個(gè)屬性上的投影πSname，Sdept(Student) 或π2，5(Student)AnIntroductiontoDatabaseSystem投影（續(xù)）(a)

Student學(xué)號(hào)Sno姓名Sname性別Ssex年齡Sage所在系Sdept200215121李勇男20CS200215122劉晨女19IS200215123王敏女18MA200215125張立男19IS[例3]查詢學(xué)生關(guān)系Student中都有哪些系

即求Student關(guān)系上所在系這個(gè)屬性上的投影πSdept(Student) 或π5(Student)AnIntroductiontoDatabaseSystem投影（續(xù)）[例4]查詢學(xué)生關(guān)系Student中都有哪些系

πSdept(Student) 結(jié)果：SdeptCSISMAAnIntroductiontoDatabaseSystem連接(續(xù))3）兩類常用連接運(yùn)算等值連接（equijoin）什么是等值連接θ為“＝”的連接運(yùn)算稱為等值連接

等值連接的含義從關(guān)系R與S的廣義笛卡爾積中選取A、B屬性值相等的那些元組，即等值連接為：

RS={|tr

R∧ts

S∧tr[A]=ts[B]}A=BtrtsAnIntroductiontoDatabaseSystem連接(續(xù))自然連接（Naturaljoin）

自然連接是一種特殊的等值連接兩個(gè)關(guān)系中進(jìn)行比較的分量必須是相同的屬性組在結(jié)果中把重復(fù)的屬性列去掉自然連接的含義 R和S具有相同的屬性組B

R

S={|trR∧tsS∧tr[B]=ts[B]}trtsAnIntroductiontoDatabaseSystem連接(續(xù))4）一般的連接操作是從行的角度進(jìn)行運(yùn)算。

自然連接還需要取消重復(fù)列，所以是同時(shí)從行和列的角度進(jìn)行運(yùn)算。

AθBRSAnIntroductiontoDatabaseSystem連接(續(xù))一般連接RS的結(jié)果如下：

C＜EAnIntroductiontoDatabaseSystem連接(續(xù))等值連接RS的結(jié)果如下：

R.B=S.BAnIntroductiontoDatabaseSystem連接(續(xù))

自然連接R

S的結(jié)果如下：

AnIntroductiontoDatabaseSystem綜合舉例(a)

Student學(xué)生-課程數(shù)據(jù)庫(kù):學(xué)生關(guān)系Student、課程關(guān)系Course和選修關(guān)系SC學(xué)號(hào)Sno姓名Sname性別Ssex年齡Sage所在系Sdept200215121李勇男20CS200215122劉晨女19IS200215123王敏女18MA200215125張立男19ISAnIntroductiontoDatabaseSystem綜合舉例課程號(hào)Cno課程名Cname先行課Cpno學(xué)分Ccredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語(yǔ)言64Course(b)AnIntroductiontoDatabaseSystem綜合舉例

(c)SC學(xué)號(hào)Sno課程號(hào)Cno成績(jī)Grade200215121192200215121285200215121388200215122290200215122380AnIntroductiontoDatabaseSystem綜合舉例檢索所有的學(xué)生姓名。

檢索“數(shù)據(jù)庫(kù)原理”課的課程號(hào)和學(xué)分。檢索選修了“數(shù)據(jù)庫(kù)原理”課程的學(xué)生的學(xué)號(hào)和成績(jī)。檢索選修“數(shù)據(jù)庫(kù)原理”課的學(xué)生姓名和成績(jī)。檢索沒(méi)有選修“數(shù)據(jù)庫(kù)原理”課的學(xué)生號(hào)。

AnIntroductiontoDatabaseSystem綜合舉例課后習(xí)題

AnIntroductiontoDatabaseSystem3.3.3創(chuàng)建基本表MSSQL數(shù)據(jù)類型整數(shù)數(shù)據(jù)

精確數(shù)值近似浮點(diǎn)數(shù)值日期時(shí)間數(shù)據(jù)bigint，int，smallint，tinyintnumeric和decimalfloat和realdatetime與smalldatetimeAnIntroductiontoDatabaseSystem字符串?dāng)?shù)據(jù)Unicode字符串?dāng)?shù)據(jù)二進(jìn)制數(shù)據(jù)貨幣數(shù)據(jù)char、varchar、textnchar、nvarchar與ntextbinary、varbinary、imagemoney與smallmoney標(biāo)記數(shù)據(jù)timestamp和uniqueidentifierAnIntroductiontoDatabaseSystemINT-2^31到2^31-14BYTESSMALLINT-2^15至2^15-12BYTESTINYINT0至2551BYTESNUMERIC-10^38-1至10^38-11-9位數(shù)使用5BYTES10-19位數(shù)使用9BYTES20-28位數(shù)使用13BYTES29-38位數(shù)使用17BYTESDECIMAL-10^38-1至10^38-15-17BYTES因長(zhǎng)度而異，與NUMERIC相同F(xiàn)LOAT-1.79E+306至1.79E+308,最多可表示53位數(shù)8BYTESREAL-3.40E+38到3.40E+38，最多可表示24位數(shù)4BYTESAnIntroductiontoDatabaseSystemDATETIME1753/1/1至9999/12/318BYTESSMALLDATETIME1900/1/1至2079/6/64BYTESCHAR1-8000個(gè)字符1個(gè)字符占1B，尾端空白字符保留VARCHAR1-8000個(gè)字符1個(gè)字符占1B，尾端空白字符刪除。TEXT2^31-1個(gè)字符1個(gè)字符占2B，最大可存儲(chǔ)2GBNCHAR1-4000個(gè)字符1個(gè)字符占2B，尾端空白字符保留NVARCHAR1-4000個(gè)字符1個(gè)字符占2B，尾端空白字符刪除AnIntroductiontoDatabaseSystemNTEXT2^30-1個(gè)字符1個(gè)字符占2B，最大可存儲(chǔ)2GBBINARY1-8000個(gè)字符在存儲(chǔ)時(shí)，SQLSERVER會(huì)另外增加4B，尾端空白字符會(huì)保留VARBINARY1-8000個(gè)字符在存儲(chǔ)時(shí)，SQLSERVER會(huì)另外增加4B，尾端空白字符會(huì)刪除IMAGE2^31-1個(gè)字符最大可存儲(chǔ)2GBMONEY-2^63-2^63-18BSMALLMONEY-2^31-2^31-14BTIMESTAMP16進(jìn)制8BUNIQUEIDENTIFIER全局唯一標(biāo)識(shí)符（GUID）可用NEWID()函數(shù)生成一個(gè)該種類型的字段值。AnIntroductiontoDatabaseSystem創(chuàng)建基本表

CREATETABLE<表名> (<列定義>[{，<列定義>|<表約束>}])[例]用SQL命令建立一個(gè)學(xué)生表S。

CREATETABLES ( SNoCHAR(6), SNVARCHAR(8), SexCHAR(2)DEFAULT'男', AgeINT, DeptVARCHAR(20))<列名><數(shù)據(jù)類型>[DEFAULT][{<列約束>}]

缺省值為“男”AnIntroductiontoDatabaseSystem定義數(shù)據(jù)表的約束正確性有效性相容性數(shù)據(jù)的完整性約束（Constraint）默認(rèn)（Default）規(guī)則（Rule）觸發(fā)器（Trigger）存儲(chǔ)過(guò)程（StoredProcedure）SQLServer的數(shù)據(jù)完整性機(jī)制AnIntroductiontoDatabaseSystem完整性約束的基本語(yǔ)法格式[CONSTRAINT<約束名>]<約束類型>NULL/NOTNULLUNIQUEPRIMARYKEYFOREIGNKEYCHECKAnIntroductiontoDatabaseSystemNULL/NOTNULL約束NULL表示“不知道”、“不確定”或“沒(méi)有數(shù)據(jù)”的意思主鍵列不允許出現(xiàn)空值 [CONSTRAINT<約束名>][NULL|NOTNULL][例]建立一個(gè)S表，對(duì)SNo字段進(jìn)行NOTNULL約束。 CREATETABLES (SNoCHAR(6)CONSTRAINT

S_Cons

NOTNULL, SNVARCHAR(8), SexCHAR(2), AgeINT, DeptVARCHAR(20))可省略約束名稱:SNoCHAR(6)NOTNULLAnIntroductiontoDatabaseSystemUNIQUE約束（惟一約束）指明基本表在某一列或多個(gè)列的組合上的取值必須惟一在建立UNIQUE約束時(shí)，需要考慮以下幾個(gè)因素：使用UNIQUE約束的字段允許為NULL值。一個(gè)表中可以允許有多個(gè)UNIQUE約束。可以把UNIQUE約束定義在多個(gè)字段上。UNIQUE約束用于強(qiáng)制在指定字段上創(chuàng)建一個(gè)UNIQUE索引，缺省為非聚集索引。

UNIQUE用于定義列約束[CONSTRAINT<約束名>]UNIQUE

UNIQUE用于定義表約束

[CONSTRAINT<約束名>]UNIQUE（<列名>[{,<列名>}]）AnIntroductiontoDatabaseSystem[例]建立一個(gè)S表，定義SN為惟一鍵。CREATETABLES( SNoCHAR(6),SNCHAR(8)CONSTRAINT

SN_UniqUNIQUE,SexCHAR(2),AgeINT,DeptVARCHAR(20))SN_Uniq可以省略SNCHAR(8)UNIQUEAnIntroductiontoDatabaseSystem[例]

建立一個(gè)S表，定義SN+SEX為惟一鍵，此約束為表約束。CREATETABLES( SNoCHAR(6),SNCHAR(8)UNIQUE,SexCHAR(2),AgeINT,DeptVARCHAR(20),CONSTRAINTS_UNIQUNIQUE(SN,Sex))AnIntroductiontoDatabaseSystemPRIMARYKEY約束（主鍵約束）用于定義基本表的主鍵，起惟一標(biāo)識(shí)作用PRIMARYKEY與UNIQUE的區(qū)別：一個(gè)基本表中只能有一個(gè)PRIMARYKEY，但可多個(gè)UNIQUE對(duì)于指定為PRIMARYKEY的一個(gè)列或多個(gè)列的組合，其中任何一個(gè)列都不能出現(xiàn)NULL值，而對(duì)于UNIQUE所約束的惟一鍵，則允許為NULL不能為同一個(gè)列或一組列既定義UNIQUE，又定義PRIMARYKEY約束。不能為NULL不能重復(fù)AnIntroductiontoDatabaseSystemPRIMARYKEY用于定義列約束CONSTRAINT<約束名>PRIMARYKEYPRIMARYKEY用于定義表約束[CONSTRAINT<約束名>]PRIMARYKEY(<列名>[{,<列名>}])AnIntroductiontoDatabaseSystem[例]建立一個(gè)S表，定義SNo為S的主鍵，建立另外一個(gè)數(shù)據(jù)表C，定義CNo為C的主鍵。

CREATETABLES( SNoCHAR(6)CONSTRAINTS_PrimPRIMARYKEY,SNCHAR(8),SexCHAR(2),AgeINT,DeptVARCHAR(20))CREATETABLEC( CNoCHAR(5)CONSTRAINTC_PrimPRIMARYKEY,CNCHAR(20),CTINT)AnIntroductiontoDatabaseSystem[例]建立一個(gè)SC表，定義SNo+CNo為SC的主鍵。CREATETABLESC( SNoCHAR(5)NOTNULL,CNoCHAR(5)NOTNULL,ScoreNUMERIC(4,1),CONSTRAINTSC_PrimPRIMARYKEY(SNo,CNo))

AnIntroductiontoDatabaseSystem

FOREIGNKEY約束（外鍵約束）[CONSTRAINT<約束名>]FOREIGNKEYREFERENCES

<主表名>(<列名>[{,<列名>}])外部鍵從表主鍵主表引用

AnIntroductiontoDatabaseSystem[例]建立一個(gè)SC表，定義SNo,CNo為SC的外部鍵。CREATETABLESC( SNoCHAR(5)NOTNULLCONSTRAINTS_ForeFOREIGNKEYREFERENCESS(SNo),CNoCHAR(5)NOTNULLCONSTRAINTC_ForeFOREIGNKEYREFERENCESC(CNo),ScoreNUMERIC(4,1),CONSTRAINTS_C_PrimPRIMARYKEY(SNo,CNo));

AnIntroductiontoDatabaseSystemCHECK約束CHECK約束用來(lái)檢查字段值所允許的范圍在建立CHECK約束時(shí)，需要考慮以下幾個(gè)因素：一個(gè)表中可以定義多個(gè)CHECK約束。每個(gè)字段只能定義一個(gè)CHECK約束。在多個(gè)字段上定義的CHECK約束必須為表約束。當(dāng)執(zhí)行INSERT、UNDATE語(yǔ)句時(shí)CHECK約束將驗(yàn)證數(shù)據(jù)。

[CONSTRAINT<約束名>]CHECK(<條件>)AnIntroductiontoDatabaseSystem[]建立一個(gè)SC表，定義Score的取值范圍為0～100之間。

CREATETABLESC ( SNoCHAR(5), CNoCHAR(5), ScoreNUMERIC(4,1)CONSTRAINTScore_ChkCHECK(Score>=0ANDScore<=100))AnIntroductiontoDatabaseSystem[例]建立包含完整性定義的學(xué)生表。 CREATETABLES ( SNoCHAR(6)CONSTRAINTS_PrimPRIMARYKEY, SNCHAR(8)CONSTRAINTSN_ConsNOTNULL, SexCHAR(2)DEFAULT'男', AgeINTCONSTRAINTAge_ConsNOTNULL CONSTRAINTAge_ChkCHECK(AgeBETWEEN15AND50), DeptCHAR(10)CONSTRAINTDept_ConsNOTNULL)

AnIntroductiontoDatabaseSystemAnIntroductiontoDatabaseSystem數(shù)據(jù)查詢語(yǔ)句格式SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]]；

AnIntroductiontoDatabaseSystem綜合舉例(a)

Student學(xué)生-課程數(shù)據(jù)庫(kù):學(xué)生關(guān)系Student、課程關(guān)系Course和選修關(guān)系SC學(xué)號(hào)Sno姓名Sname性別Ssex年齡Sage所在系Sdept200215121李勇男20CS200215122劉晨女19IS200215123王敏女18MA200215125張立男19ISAnIntroductiontoDatabaseSystem綜合舉例課程號(hào)Cno課程名Cname先行課Cpno學(xué)分Ccredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語(yǔ)言64Course(b)AnIntroductiontoDatabaseSystem綜合舉例

(c)SC學(xué)號(hào)Sno課程號(hào)Cno成績(jī)Grade200215121192200215121285200215121388200215122290200215122380AnIntroductiontoDatabaseSystem綜合舉例查詢“信息”系所有學(xué)生的信息。將所有學(xué)生的年齡加1歲。查詢學(xué)分在3至5之間的課程的名稱和學(xué)分。查詢所有姓'張'的學(xué)生的姓名、學(xué)號(hào)和性別。查詢選課表，查詢結(jié)果按學(xué)號(hào)升序列排列，同一學(xué)號(hào)的按成績(jī)的降序排列。查詢選修了3門及3門以上課程的學(xué)生的學(xué)號(hào)。查詢其他系中比信息系所有學(xué)生年齡都大的學(xué)生名單和所在系。AnIntroductiontoDatabaseSystem參考答案(1)SELECT*FROMSWHERESD='信息'(2)UPDATESSETSA=SA+1(3)SELECTCN,CCFROMCWHERECCBETWEEN3AND5(4)SELECTSN,SNO,SSFROMSWHERESNLIKE'張%'(5)SELECT*FROMSCORDERBYSNO,GDESC(6)SELECTSNOFROMSCGROUPBYSNOHAVINGCOUNT(SNO)>=5(7)SELECTSN,SDFROMSWHERESA>ALL

(SELECTSAFROMSWHERESD='信息')

ANDSD<>'信息'AnIntroductiontoDatabaseSystemAnIntroductiontoDatabaseSystemE-R圖向關(guān)系模型的轉(zhuǎn)換（續(xù)）E-R圖向關(guān)系模型的轉(zhuǎn)換要解決的問(wèn)題如何將實(shí)體型和實(shí)體間的聯(lián)系轉(zhuǎn)換為關(guān)系模式如何確定這些關(guān)系模式的屬性和碼轉(zhuǎn)換內(nèi)容將E-R圖轉(zhuǎn)換為關(guān)系模型：將實(shí)體、實(shí)體的屬性和實(shí)體之間的聯(lián)系轉(zhuǎn)換為關(guān)系模式。AnIntroductiontoDatabaseSystemE-R圖向關(guān)系模型的轉(zhuǎn)換轉(zhuǎn)換原則1.一個(gè)實(shí)體型轉(zhuǎn)換為一個(gè)關(guān)系模式。關(guān)系的屬性：實(shí)體型的屬性關(guān)系的碼：實(shí)體型的碼例，學(xué)生實(shí)體可以轉(zhuǎn)換為如下關(guān)系模式：

學(xué)生（學(xué)號(hào)，姓名，出生日期，所在系，年級(jí)，平均成績(jī)）

性別、宿舍、班級(jí)、檔案材料、教師、課程、教室、教科書都分別轉(zhuǎn)換為一個(gè)關(guān)系模式。AnIntroductiontoDatabaseSystemE-R圖向關(guān)系模型的轉(zhuǎn)換AnIntroductiontoDatabaseSystemE-R圖向關(guān)系模型的轉(zhuǎn)換轉(zhuǎn)換原則2.一個(gè)m:n聯(lián)系轉(zhuǎn)換為一個(gè)關(guān)系模式。關(guān)系的屬性：與該聯(lián)系相連的各實(shí)體的碼以