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

計算機軟件技術基礎

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

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

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

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

（<列名><數(shù)據(jù)類型>[<列級完整性約束條件>][，<列名><數(shù)據(jù)類型>[<列級完整性約束條件>]]…[，<表級完整性約束條件>]）；<表名>：所要定義的基本表的名字<列名>：組成該表的各個屬性（列）<列級完整性約束條件>：涉及相應屬性列的完整性約束條件<表級完整性約束條件>：涉及一個或多個屬性列的完整性約束條件創(chuàng)建表常用完整性約束主碼約束：PRIMARYKEY參照完整性約束默認值：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ū)別是：若一個列被聲明為定長字符串，無論實際存儲多少個字符，都會占據(jù)n個字節(jié)存儲空間，實際長度不足n的在字符串后面補空格；變長字符串則只占據(jù)比實際存儲內(nèi)容稍大的存儲空間。定長字符串的好處是存儲和讀取速度更快。常見的數(shù)據(jù)類型int整數(shù)，取值范圍：–2,147,483,648~2,147,483,647float、real浮點數(shù)。近似值，不保證100%精確。float取值范圍：–1.79e+308~1.79e+308real取值范圍：–3.40e+38~3.40e+38decimal(d,p)/numeric(d,p)一定范圍的精確數(shù)值d–小數(shù)點左右兩邊的數(shù)字位數(shù)之和，d≤38p–小數(shù)點右邊的數(shù)字位數(shù)常見的數(shù)據(jù)類型datetime日期時間bit值為0或1，通常用來存儲trueorfalse類型數(shù)據(jù)binary(n)、varbinary(n)n個字節(jié)的二進制數(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表明此索引的每一個索引值只對應唯一的數(shù)據(jù)記錄CLUSTER表示要建立的索引是聚簇索引建立索引唯一值索引對于已含重復值的屬性列不能建UNIQUE索引對某個列建立UNIQUE索引后，插入新記錄時DBMS會自動檢查新記錄在該列上是否取了重復值。這相當于增加了一個UNIQUE約束。建立索引聚簇索引建立聚簇索引后，基表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項順序與表中記錄的物理順序一致。聚簇索引對于那些經(jīng)常要搜索范圍值的列特別有效。使用聚簇索引找到包含第一個值的行后，便可以確保包含后續(xù)索引值的行在物理相鄰。對于某些類型的查詢，可以提高查詢效率。在一個基本表上最多只能建立一個聚簇索引查詢單表查詢連接查詢嵌套查詢集合查詢查詢語句格式SELECT[DISTINCT]<目標列表達式>[<別名>][，<目標列表達式>[<別名>]]…FROM<表名或視圖名>[<別名>][，<表名或視圖名>[<別名>]]…[WHERE<條件表達式>][GROUPBY<列名>[,<列名>]…[HAVING<條件表達式>]][ORDERBY<列名>[,<列名>]…[ASC|DESC]]；查詢SELECT子句：指定要顯示的屬性列FROM子句：指定查詢對象(基本表或視圖)WHERE子句：指定查詢條件GROUPBY子句：對查詢結(jié)果按指定列的值分組，該屬性列值相等的記錄為一個組。通常會在每組中作用集函數(shù)。HAVING短語：篩選出滿足指定條件的組ORDERBY子句：對查詢結(jié)果表按指定列值的升序或降序排序示例數(shù)據(jù)庫學生-課程數(shù)據(jù)庫學生表：

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

Course(Cno，Cname，Cpno，Ccredit)

學生(選課)成績表：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECTSname FROMStudentWHERESnoIN

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

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

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約束的屬性列是否提供了非重復值對于有值域約束的屬性列所提供的屬性值是否在值域范圍內(nèi)插入數(shù)據(jù)將一個新學生記錄（學號：2011020；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。

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

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

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

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

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

UPDATESCSETGrade=0WHERESNOin(SELETESn