章節(jié)學(xué)習(xí)-第五章數(shù)據(jù)庫

計算機(jī)軟件技術(shù)基礎(chǔ)

第五章數(shù)據(jù)庫信息技術(shù)系徐家臻本課主要內(nèi)容數(shù)據(jù)庫基礎(chǔ)知識關(guān)系數(shù)據(jù)模型VisualStudio管理數(shù)據(jù)庫SqlServerSQL語言DDLDML數(shù)據(jù)庫基礎(chǔ)知識數(shù)據(jù)庫（Database,簡稱DB）是指長期存儲在計算機(jī)內(nèi)、有組織、可共享的數(shù)據(jù)集合。數(shù)據(jù)庫管理系統(tǒng)(DatabaseManagementSystem,簡稱DBMS)是指用于完成數(shù)據(jù)庫的定義、建立、運(yùn)行和維護(hù)，使用戶能方便地操縱和控制數(shù)據(jù)，并能保證數(shù)據(jù)的安全性、完整性、并發(fā)性和可恢復(fù)性的軟件系統(tǒng)。數(shù)據(jù)庫基礎(chǔ)知識數(shù)據(jù)庫系統(tǒng)在計算機(jī)系統(tǒng)中的位置用系應(yīng)統(tǒng)

發(fā)工開具用軟應(yīng)件BMDS編統(tǒng)譯系作系操統(tǒng)硬件數(shù)據(jù)庫基礎(chǔ)知識應(yīng)用程序與數(shù)據(jù)的關(guān)系DBMS應(yīng)用程序1應(yīng)用程序2數(shù)據(jù)庫…數(shù)據(jù)庫基礎(chǔ)知識ACCESSSQLiteMYSQLSQLSERVERORACLESYSBASEDB2支持操作系統(tǒng)WinWin/UnixWin/UnixWinWin/UnixWin/UnixUnix公司MS開源開源MSORACLESYSBASEIBM專業(yè)性非專業(yè)小型專業(yè)小型專業(yè)大中型專業(yè)大中型專業(yè)大型專業(yè)大型專業(yè)大型當(dāng)前主要數(shù)據(jù)庫管理系統(tǒng)數(shù)據(jù)庫基礎(chǔ)知識DBMS的主要功能數(shù)據(jù)定義功能:提供數(shù)據(jù)定義語言(DDL,DataDefinitionLanguage)，用于定義數(shù)據(jù)庫中的數(shù)據(jù)對象。數(shù)據(jù)操縱功能:提供數(shù)據(jù)操縱語言(DML,DataManipulationLanguage)，用于操縱數(shù)據(jù)實(shí)現(xiàn)對數(shù)據(jù)庫的基本操作(查詢、插入、刪除和修改)。數(shù)據(jù)庫的運(yùn)行管理:保證數(shù)據(jù)的安全性、完整性、多用戶對數(shù)據(jù)的并發(fā)使用及發(fā)生故障后的系統(tǒng)恢復(fù)。數(shù)據(jù)庫基礎(chǔ)知識DBMS的主要功能數(shù)據(jù)庫的運(yùn)行管理:保證數(shù)據(jù)的安全性、完整性、多用戶對數(shù)據(jù)的并發(fā)使用及發(fā)生故障后的系統(tǒng)恢復(fù)。數(shù)據(jù)庫的建立和維護(hù)功能:提供實(shí)用程序，完成數(shù)據(jù)庫數(shù)據(jù)批量裝載，數(shù)據(jù)庫轉(zhuǎn)儲，介質(zhì)故障恢復(fù)，數(shù)據(jù)庫的重組織和性能監(jiān)視等數(shù)據(jù)庫基礎(chǔ)知識概念模型實(shí)體（Entity）客觀存在并可相互區(qū)別的事物稱為實(shí)體?？梢允蔷唧w的人、事、物或抽象的概念。屬性（Attribute）實(shí)體所具有的某一特性稱為屬性。一個實(shí)體可以由若干個屬性來刻畫。碼/鍵（Key）唯一標(biāo)識實(shí)體的屬性集稱為碼/鍵。實(shí)體集（EntitySet）同型實(shí)體的集合稱為實(shí)體集。聯(lián)系（Relationship）數(shù)據(jù)庫基礎(chǔ)知識聯(lián)系（Relationship）現(xiàn)實(shí)世界中事物內(nèi)部以及事物之間的聯(lián)系在信息世界中反映為實(shí)體內(nèi)部的聯(lián)系和實(shí)體之間的聯(lián)系。實(shí)體內(nèi)部的聯(lián)系：組成實(shí)體的各屬性之間的聯(lián)系。實(shí)體之間的聯(lián)系：不同實(shí)體集之間的聯(lián)系。三類實(shí)體間聯(lián)系一對一聯(lián)系（1:1）一對多聯(lián)系（1:n）多對多聯(lián)系（m:n）數(shù)據(jù)庫基礎(chǔ)知識一對一聯(lián)系如果對于實(shí)體集A中的每一個實(shí)體，實(shí)體集B中至多有一個實(shí)體與之聯(lián)系，反之亦然，則稱實(shí)體集A與實(shí)體集B具有一對一聯(lián)系。記為1:1。實(shí)例班級與班長之間的聯(lián)系：一個班級只有一個正班長，而一個班長只在一個班中任職數(shù)據(jù)庫基礎(chǔ)知識一對多聯(lián)系如果對于實(shí)體集A中的每一個實(shí)體，實(shí)體集B中有n個實(shí)體（n≥0）與之聯(lián)系，反之，對于實(shí)體集B中的每一個實(shí)體，實(shí)體集A中至多只有一個實(shí)體與之聯(lián)系，則稱實(shí)體集A與實(shí)體B有一對多聯(lián)系。記為1:n實(shí)例班級與學(xué)生之間的聯(lián)系：一個班級中有若干名學(xué)生，而每個學(xué)生只在一個班級中學(xué)習(xí)數(shù)據(jù)庫基礎(chǔ)知識多對多聯(lián)系（m:n）如果對于實(shí)體集A中的每一個實(shí)體，實(shí)體集B中有n個實(shí)體（n≥0）與之聯(lián)系，反之，對于實(shí)體集B中的每一個實(shí)體，實(shí)體集A中也有m個實(shí)體（m≥0）與之聯(lián)系，則稱實(shí)體集A與實(shí)體B具有多對多聯(lián)系。記為m:n實(shí)例課程與學(xué)生之間的聯(lián)系：一門課程同時有若干個學(xué)生選修，而一個學(xué)生可以同時選修多門課程數(shù)據(jù)庫基礎(chǔ)知識班級班級-班長班長111:1聯(lián)系課程選修學(xué)生mnm:n聯(lián)系班級組成學(xué)生1n1:n聯(lián)系表示方法（E-R圖）E-R圖實(shí)體型用矩形表示，矩形框內(nèi)寫明實(shí)體名。學(xué)生教師E-R圖屬性用橢圓形表示，并用無向邊將其與相應(yīng)的實(shí)體連接起來學(xué)生學(xué)號年齡性別姓名E-R圖(續(xù))聯(lián)系用菱形表示，菱形框內(nèi)寫明聯(lián)系名，并用無向邊分別與有關(guān)實(shí)體連接起來，同時在無向邊旁標(biāo)上聯(lián)系的類型（1:1、1:n或m:n）E-R圖實(shí)體型1聯(lián)系名實(shí)體型2111:1聯(lián)系實(shí)體型1聯(lián)系名實(shí)體型2mnm:n聯(lián)系實(shí)體型1聯(lián)系名實(shí)體型21n1:n聯(lián)系E-R圖多個實(shí)體型間的一對多聯(lián)系實(shí)例對于課程、教師與參考書三個實(shí)體型，如果一門課程可以有若干個教師講授，使用若干本參考書，而每一個教師只講授一門課程，每一本參考書只供一門課程使用，則課程與教師、參考書之間的聯(lián)系是一對多的E-R圖課程講授教師1m多個實(shí)體型間的1:n聯(lián)系參考書nE-R圖聯(lián)系聯(lián)系的屬性：聯(lián)系本身也是一種實(shí)體型，也可以有屬性。如果一個聯(lián)系具有屬性，則這些屬性也要用無向邊與該聯(lián)系連接起來。聯(lián)系屬性的表示方法課程選修學(xué)生mn成績關(guān)系數(shù)據(jù)模型現(xiàn)在的絕大多數(shù)數(shù)據(jù)庫都采用關(guān)系數(shù)據(jù)模型。關(guān)系模型的基本數(shù)據(jù)結(jié)構(gòu)在用戶觀點(diǎn)下，關(guān)系模型中數(shù)據(jù)的邏輯結(jié)構(gòu)是一張二維表，它由行和列組成。關(guān)系數(shù)據(jù)模型將概念模型轉(zhuǎn)換為關(guān)系模型。實(shí)體集：直接用數(shù)據(jù)表來表示。屬性：用屬性名表示。實(shí)體名（屬性1，屬性2，…，屬性n）例如：學(xué)生信息表（學(xué)號，姓名，年齡，性別，年級）聯(lián)系一對一聯(lián)系：隱含在實(shí)體表中。一對多聯(lián)系：隱含在實(shí)體表中。多對多聯(lián)系：直接用關(guān)系表來表示。關(guān)系數(shù)據(jù)模型例1學(xué)生、系、系與學(xué)生之間的一對多聯(lián)系：學(xué)生（學(xué)號，姓名，年齡，性別，系號，年級）系(系號，系名，辦公地點(diǎn))例2學(xué)生、課程、學(xué)生與課程之間的多對多聯(lián)系：

學(xué)生信息表（學(xué)號，姓名，年齡，性別，系號，年級）課程信息表（課程號，課程名，學(xué)分）(選課)成績表（學(xué)號，課程號，成績）

學(xué)生學(xué)生選課課程關(guān)系數(shù)據(jù)模型SQL語言SQL(結(jié)構(gòu)化查詢語言，StructuredQueryLanguage)SQL語言定義了一套標(biāo)準(zhǔn)語法，集數(shù)據(jù)定義語言DDL、數(shù)據(jù)操縱語言DML、數(shù)據(jù)控制語言DCL的功能于一體。各種數(shù)據(jù)庫產(chǎn)品基本都實(shí)現(xiàn)了這些SQL語法。用戶只需提出做什么，而不必指明怎么做。SQL語言是不區(qū)分大小寫的SQL語言SQL語言基本表和視圖基本表本身獨(dú)立存在的表，一個實(shí)體集/關(guān)系對應(yīng)一個表一個（或多個）基本表對應(yīng)一個存儲文件一個表可以帶若干索引，索引也存放在存儲文件中主鍵(primarykey)是表中的一個或多個字段，它的值用于惟一地標(biāo)識表中的某一條記錄。存儲文件存儲文件的物理結(jié)構(gòu)是任意的，對用戶是透明的視圖從一個或幾個基本表或視圖導(dǎo)出的表是虛表，只存放視圖的定義而不存放對應(yīng)數(shù)據(jù)SQL語言創(chuàng)建表語句格式CREATETABLE<表名>

（<列名><數(shù)據(jù)類型>[<列級完整性約束條件>][，<列名><數(shù)據(jù)類型>[<列級完整性約束條件>]]…[，<表級完整性約束條件>]）；<表名>：所要定義的基本表的名字<列名>：組成該表的各個屬性（列）<列級完整性約束條件>：涉及相應(yīng)屬性列的完整性約束條件<表級完整性約束條件>：涉及一個或多個屬性列的完整性約束條件創(chuàng)建表常用完整性約束主碼約束：PRIMARYKEY參照完整性約束默認(rèn)值：DEFAULT非空值約束：NOTNULL取值約束：CHECK常見的數(shù)據(jù)類型char(n)最大長度為n的定長字符串，ANSI字符集存儲方式（存儲每個英文字符占用1長度，存儲每個中文字符占用2長度）。nchar(n)最大長度為n的定長字符串，Unicode字符集存儲方式（存儲每個字符占用2長度）。varchar(n)、nvarchar(n)最大長度為n的變長字符串。與char(n)、nchar(n)的區(qū)別是：若一個列被聲明為定長字符串，無論實(shí)際存儲多少個字符，都會占據(jù)n個字節(jié)存儲空間，實(shí)際長度不足n的在字符串后面補(bǔ)空格；變長字符串則只占據(jù)比實(shí)際存儲內(nèi)容稍大的存儲空間。定長字符串的好處是存儲和讀取速度更快。常見的數(shù)據(jù)類型int整數(shù)，取值范圍：–2,147,483,648~2,147,483,647float、real浮點(diǎn)數(shù)。近似值，不保證100%精確。float取值范圍：–1.79e+308~1.79e+308real取值范圍：–3.40e+38~3.40e+38decimal(d,p)/numeric(d,p)一定范圍的精確數(shù)值d–小數(shù)點(diǎn)左右兩邊的數(shù)字位數(shù)之和，d≤38p–小數(shù)點(diǎn)右邊的數(shù)字位數(shù)常見的數(shù)據(jù)類型datetime日期時間bit值為0或1，通常用來存儲trueorfalse類型數(shù)據(jù)binary(n)、varbinary(n)n個字節(jié)的二進(jìn)制數(shù)據(jù)修改表語句格式ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][MODIFY<列名><數(shù)據(jù)類型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性約束條件MODIFY子句：用于修改列名和數(shù)據(jù)類型刪除表

語句格式

DROPTABLE<表名>;系統(tǒng)刪去該表的描述和該表上的所有索引的描述系統(tǒng)從文件中刪去表中的數(shù)據(jù)表上的視圖往往仍然保留，但無法引用

建立與刪除索引

建立索引是加快查詢速度的有效手段建立索引

DBMS自動建立

PRIMARYKEYDBA或開發(fā)人員根據(jù)需要建立

使用索引

DBMS自動選擇是否使用索引以及使用哪些索引建立索引語句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)；

用<表名>指定要建索引的基本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄CLUSTER表示要建立的索引是聚簇索引建立索引唯一值索引對于已含重復(fù)值的屬性列不能建UNIQUE索引對某個列建立UNIQUE索引后，插入新記錄時DBMS會自動檢查新記錄在該列上是否取了重復(fù)值。這相當(dāng)于增加了一個UNIQUE約束。建立索引聚簇索引建立聚簇索引后，基表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項(xiàng)順序與表中記錄的物理順序一致。聚簇索引對于那些經(jīng)常要搜索范圍值的列特別有效。使用聚簇索引找到包含第一個值的行后，便可以確保包含后續(xù)索引值的行在物理相鄰。對于某些類型的查詢，可以提高查詢效率。在一個基本表上最多只能建立一個聚簇索引查詢單表查詢連接查詢嵌套查詢集合查詢查詢語句格式SELECT[DISTINCT]<目標(biāo)列表達(dá)式>[<別名>][，<目標(biāo)列表達(dá)式>[<別名>]]…FROM<表名或視圖名>[<別名>][，<表名或視圖名>[<別名>]]…[WHERE<條件表達(dá)式>][GROUPBY<列名>[,<列名>]…[HAVING<條件表達(dá)式>]][ORDERBY<列名>[,<列名>]…[ASC|DESC]]；查詢SELECT子句：指定要顯示的屬性列FROM子句：指定查詢對象(基本表或視圖)WHERE子句：指定查詢條件GROUPBY子句：對查詢結(jié)果按指定列的值分組，該屬性列值相等的記錄為一個組。通常會在每組中作用集函數(shù)。HAVING短語：篩選出滿足指定條件的組ORDERBY子句：對查詢結(jié)果表按指定列值的升序或降序排序示例數(shù)據(jù)庫學(xué)生-課程數(shù)據(jù)庫學(xué)生表：

Student(Sno，Sname，Ssex，Sage，Sdept)課程表：

Course(Cno，Cname，Cpno，Ccredit)

學(xué)生(選課)成績表：

SC(Sno，Cno，Grade)SELECT查詢?nèi)w學(xué)生的姓名、學(xué)號、所在系。SELECTSname，Sno，SdeptFROMStudent在SELECT關(guān)鍵字后面列出所有列名在FROM關(guān)鍵字后面列出表名查詢?nèi)w學(xué)生的所有屬性記錄SELECT*FROMStudentDISTINCT在SELECT子句中使用DISTINCT短語消除取值重復(fù)的行。假設(shè)SC表中有下列數(shù)據(jù)

SnoCnoGrade------------------------20110011922011001285201100138820110022902011002380DISTINCT SELECTDISTINCTSnoFROMSC; Sno----------20110012011002WHERE通過WHERE子句實(shí)現(xiàn) 比較大小 確定范圍 字符串匹配 涉及空值的查詢 多重條件查詢WHEREWHERE子句常用的查詢條件WHERE查詢計算機(jī)系全體學(xué)生的名單。SELECTSnameFROMStudentWHERESdept='CS'；查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。SELECTSname，SageFROMStudentWHERESage<20；WHERE查詢既不是歷史、數(shù)學(xué)系，也不是計算機(jī)系的學(xué)生的姓名和性別。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('HIS'，'MA'，'CS');查所有有成績的學(xué)生學(xué)號和課程號。

SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；WHERE查詢所有姓劉學(xué)生的姓名、學(xué)號和性別。

SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘劉%’；通配符%代表任意長度（長度可以為0）的字符串。

_代表任意單個字符。WHERE多重查詢用邏輯運(yùn)算符AND和OR來聯(lián)結(jié)多個查詢條件

AND的優(yōu)先級高于OR

可以用括號改變優(yōu)先級可用來實(shí)現(xiàn)多種其他謂詞

[NOT]IN[NOT]BETWEEN…AND…WHERE查詢計算機(jī)系年齡在20歲以下的學(xué)生姓名。

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；ORDERBY使用ORDERBY子句可以按一個或多個屬性列排序升序：ASC；降序：DESC；缺省值為升序當(dāng)排序列含空值時ASC：排序列為空值的記錄最后顯示DESC：排序列為空值的記錄最先顯示ORDERBY查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學(xué)生按年齡降序排列。

SELECT*FROMStudentORDERBYSdept，SageDESC；聚集函數(shù)COUNT(列名，下同) 符合條件的記錄數(shù)SUM() 求和MAX() 求最大值MIN() 求最小值A(chǔ)VG() 求平均值聚集函數(shù)查詢學(xué)生總?cè)藬?shù)。

SELECTCOUNT(*)FROMStudent；查詢選修了課程的學(xué)生人數(shù)。

SELECTCOUNT(DISTINCTSno)FROMSC；GROUPBY使用GROUPBY子句分組分組方法：按指定的一列或多列值分組，值相等的為一組使用GROUPBY子句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和聚集函數(shù)GROUPBY查詢各個課程號及相應(yīng)的選課人數(shù)。

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；查詢各個課程號，及相應(yīng)的課程成績在90分以上的學(xué)生人數(shù)。

SELECTCno，COUNT(Sno)FROMSCWHEREGrade>=90GROUPBYCno；連接查詢同時涉及多個表的查詢稱為連接查詢連接條件用來連接兩個表的條件稱為連接條件或連接謂詞。連接含義逐一查找滿足表1和表2連接條件的記錄，每找到一對這樣的記錄，就將它們拼接起來，按照選擇的列，形成結(jié)果表中一條記錄。連接查詢查詢每個學(xué)生及其選修課程的情況。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；連接查詢外連接外連接兩表時，一張為主體表，一張為非主體表。非主體表有一"萬能"的虛行，該行全部由空值組成。虛行可以和主體表中所有不滿足連接條件的記錄進(jìn)行連接。由于虛行的存在，主體表中的每條記錄至少在查詢結(jié)果中出現(xiàn)一次。由于虛行各列全部是空值，因此與虛行連接的結(jié)果中，來自非主體表的屬性值全部是空值。左外連接左表為主體表 右外連接右表為主體表連接查詢查詢每個學(xué)生及其選修課程的情況，要求即使該學(xué)生沒有選修任何課程，他的信息也會在結(jié)果中出現(xiàn)一次。

SELECTStudent.Sno，Sname，Ssex， Sage，Sdept，Cno，GradeFROMStudentLEFTOUTERJOINSCONStudent.Sno=SC.Sno；嵌套查詢嵌套查詢概述一個SELECT-FROM-WHERE語句稱為一個查詢塊將一個查詢塊嵌套在另一個查詢塊中的查詢稱為嵌套查詢查詢所有選了編號為2的課程的學(xué)生姓名。

SELECTSname FROMStudentWHERESnoIN

(SELECTSnoFROMSCWHERECno='2');嵌套查詢EXISTS謂詞存在量詞

帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生真(true)或假(false)查詢所有沒有選修1號課程的學(xué)生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoAND Cno='1')；數(shù)據(jù)更新插入數(shù)據(jù)INSERT修改數(shù)據(jù)UPDATE刪除數(shù)據(jù)DELETE插入數(shù)據(jù)語句格式

INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]VALUES(<常量1>[，<常量2>]…)DBMS在執(zhí)行插入語句時會檢查所插記錄是否破壞表上已定義的約束。對于有NOTNULL約束的屬性列是否提供了非空值對于有UNIQUE約束的屬性列是否提供了非重復(fù)值對于有值域約束的屬性列所提供的屬性值是否在值域范圍內(nèi)插入數(shù)據(jù)將一個新學(xué)生記錄（學(xué)號：2011020；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。

INSERTINTOStudentVALUES('2011020'，'陳冬'，'男'，'IS'，18)；插入一條選課記錄('2011020'，'1')。

INSERTINTOSC(Sno，Cno)VALUES('2011020'，'1')；修改數(shù)據(jù)語句格式

UPDATE<表名>SET<列名>=<表達(dá)式>[，<列名>=<表達(dá)式>]…[WHERE<條件>]；修改指定表中滿足WHERE子句條件的記錄。DBMS在執(zhí)行修改語句時會檢查修改操作是否破壞表上已定義的約束。修改數(shù)據(jù)將學(xué)生2011001的年齡改為22歲。

UPDATEStudentSETSage=22WHERESno='2011001'將所有學(xué)生的年齡增加1歲。

UPDATEStudentSETSage=Sage+1；修改數(shù)據(jù)將計算機(jī)科學(xué)系全體學(xué)生的成績置零。

UPDATESCSETGrade=0WHERESNOin(SELETESn