《數(shù)據(jù)庫原理》教案

1、數(shù)據(jù)庫原理與應用項目化教程教案計算機網(wǎng)絡教研室2014年9月1日第1章 數(shù)據(jù)庫系統(tǒng)概述項目1.1 引 言一、有關概念1數(shù)據(jù)2數(shù)據(jù)庫（DB）3數(shù)據(jù)庫管理系統(tǒng)（DBMS） Access桌面DBMS VFP SQL Server Oracle客戶機/服務器型DBMS MySQL DB24數(shù)據(jù)庫系統(tǒng)（DBS） 數(shù)據(jù)庫（DB） 數(shù)據(jù)庫管理系統(tǒng)（DBMS） 開發(fā)工具 應用系統(tǒng)二、數(shù)據(jù)管理技術的發(fā)展1數(shù)據(jù)管理的三個階段人工管理文件系統(tǒng)數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)能否保存不能保存可以保存可以保存數(shù)據(jù)面向的對象某一應用程序某一應用程序整個應用系統(tǒng)數(shù)據(jù)的共享程度無共享，一組數(shù)據(jù)只能對應一個應用程序。共享性差，一個數(shù)據(jù)文件只能對

2、應一個應用程序。共享性高數(shù)據(jù)的獨立性不獨立，它是應用程序的一部分。獨立性差數(shù)據(jù)庫與應用系統(tǒng)完全分開項目1.2 數(shù) 據(jù) 模 型1.2.1 概念模型一、模型的三個世界1現(xiàn)實世界2信息世界：即根據(jù)需求分析畫概念模型（即E-R圖），E-R圖與DBMS無關。3機器世界：將E-R圖轉換為某一種數(shù)據(jù)模型，數(shù)據(jù)模型與DBMS相關。注意：信息世界又稱概念模型，機器世界又稱數(shù)據(jù)模型二、實體及屬性1實體：客觀存在并可相互區(qū)別的事物。2屬性：3關鍵詞（碼、key）：能唯一標識每個實體又不含多余屬性的屬性組合。一個表的碼可以有多個，但主碼只能有一個。例：借書表（學號，姓名，書號，書名，作者，定價，借期，還期）規(guī)定：學生

3、一次可以借多本書，同一種書只能借一本，但可以多次續(xù)借。4實體型：即二維表的結構例 student(no，name，sex，age，dept)5實體集：即整個二維表三、實體間的聯(lián)系：1兩實體集間實體之間的聯(lián)系 1：1聯(lián)系 1：n聯(lián)系 m：n聯(lián)系2同一實體集內(nèi)實體之間的聯(lián)系1：1聯(lián)系 1：n聯(lián)系 m：n聯(lián)系四、概念模型（常用E-R圖表示）實體型：屬性：聯(lián)系：說明： E-R圖作為用戶與開發(fā)人員的中間語言。 E-R圖可以等價轉換為層次、網(wǎng)狀、關系模型。舉例：學校有若干個系，每個系有若干班級和教研室，每個教研室有若干教員，其中有的教授和副教授每人各帶若干研究生。每個班有若干學生，每個學生選修若干課程，每

4、門課程有若干學生選修。用E-R圖畫出概念模型。1.2.2 數(shù)據(jù)模型一、層次模型：用樹型結構表示實體之間的聯(lián)系。 每個結點代表一個實體型。 只能直接處理一對多（含一對一）的實體關系。 查找層次數(shù)據(jù)庫中的記錄，速度較慢。二、網(wǎng)狀模型：用圖結構表示實體之間的聯(lián)系。 每個結點代表一個實體型。 可以處理多對多的實體關系。 查找網(wǎng)狀數(shù)據(jù)庫中的記錄，速度最快。三、關系模型：用二維表表示實體之間的聯(lián)系。1重要術語：關系：一個關系就是一個二維表；元組：二維表的一行，即實體；關系模式：在實體型的基礎上，注明主碼。關系模型：指一個數(shù)據(jù)庫中全部二維表結構的集合。2特點： 關系模型是建立在嚴格的數(shù)學理論的基礎上的； 關

5、系模型的存取路徑對用戶透明； 查找關系數(shù)據(jù)庫中的記錄，速度最慢。小結：數(shù)據(jù)有三種類型，DBMS就有三種類型，DB亦有三種類型。項目1.3 數(shù)據(jù)庫系統(tǒng)結構一、數(shù)據(jù)庫系統(tǒng)的體系結構 單機結構：DBMS、數(shù)據(jù)庫、開發(fā)工具、應用系統(tǒng)安裝在一臺計算機上。 C/S結構：局域網(wǎng)結構客戶機：裝開發(fā)工具、應用系統(tǒng)服務器：裝DBMS、數(shù)據(jù)庫 B/S結構：Internet 結構服務器：裝DBMS、數(shù)據(jù)庫、開發(fā)工具、應用系統(tǒng)客戶機：裝IE即可三、 數(shù)據(jù)庫系統(tǒng)的模式結構1三級模式 模式：是數(shù)據(jù)庫中全體數(shù)據(jù)的邏輯結構和特征的描述。Ü 模式只涉及數(shù)據(jù)庫的結構；Ü 模式既不涉及應用程序，又不涉及數(shù)據(jù)庫結構

6、的存儲； 外模式：是模式的一個子集，是與某一個應用程序有關的邏輯表示。特點：一個應用程序只能使用一個外模式，但同一個外模式可為多個應用程序使用。 內(nèi)模式：描述數(shù)據(jù)庫結構的存儲，但不涉及物理記錄。2兩級映象 外模式/模式映象：保證數(shù)據(jù)庫的邏輯獨立性； 模式/內(nèi)模式映象：保證數(shù)據(jù)庫的物理獨立性；3兩級映象的意義 使數(shù)據(jù)庫與應用系統(tǒng)完全分開，數(shù)據(jù)庫改變時，應用系統(tǒng)不必改變。 數(shù)據(jù)的存取完全由DBMS管理，用戶不必考慮存取路徑。項目1.4 數(shù)據(jù)庫管理系統(tǒng)1 DBMS的功能：負責對數(shù)據(jù)庫進行統(tǒng)一的管理與控制。 數(shù)據(jù)定義：即定義數(shù)據(jù)庫中各對象的結構 數(shù)據(jù)操縱：包括對數(shù)據(jù)庫進行查詢、插入、刪除、修改等操作。

7、 數(shù)據(jù)控制：包括安全性控制、完整性控制、并發(fā)控制、數(shù)據(jù)庫恢復。2DBMS的組成：DDL語言 DML語言DCL語言實用程序注意： SQL集DDL，DML，DCL功能于一體； 所有應用程序通過SQL語句才能訪問數(shù)據(jù)庫第2章 關系數(shù)據(jù)庫項目2.1 關系的數(shù)學定義 2.1.1 基本概念1碼2候選碼：一個關系模式可以有多個候選碼。3主碼：任選候選碼中的一個。4主屬性：主碼中包含的各個屬性。5非主屬性：不包含在主碼中的各個屬性。6外碼：設F是關系R的一個屬性，不是R的主碼，但卻是另一個關系S的主碼，則稱F是關系R的外碼。例：student ( sno, sname, ssex, sage, sdept)

8、Sc ( sno, cno, grade)Sc的主碼為：（sno,cno）；外碼為：sno2.1.2 關系的數(shù)學定義一、域( domain)1定義：域是一組具有相同類型的值的集合。2域的基數(shù)：域中所含數(shù)據(jù)的個數(shù)。二、笛卡爾積1定義：給定一組域D1,D2,D3，則D1×D2×D3稱為笛卡爾積。2笛卡爾積D1×D2×D3對應一個二維表，所含元組的個數(shù)等于各個域的基數(shù)之積。三、關系1定義：笛卡兒積的一部分元組稱為關系。2關系的目（或度）：一個關系所含屬性的個數(shù)。3關系的性質(zhì)任意兩個元組不能完全相同，但屬性名允許重復。四、關系的完整性1實體完整性：指關系的所有主

9、屬性都不能取空值。注意：實體完整性不僅僅是主碼整體不能取空值。2參照完整性：指一個關系外碼的取值必須是相關關系中主碼的有效值或空值。例：班級( 班名,人數(shù))學生(學號,姓名,性別,密碼,班名)在學生表中，班名的取值必須是班級表班名的值或空值。項目2.2 關系代數(shù)一、傳統(tǒng)的集合運算設關系R、S的結構完全相同，則：RS：由屬于R或屬于S的元組組成。RS：由既屬于R又屬于S的元組組成。RS：由屬于R而不屬于S的元組組成。思考：（RS）（RS）=？R×S：設R有m個屬性，K1個元組；S有n個屬性，K2個元組，則R×S含有(m+n)個屬性，(K1×K2)個元組。二、專門的關

10、系運算1選擇：從關系R中選擇滿足條件的元組。記為： 2投影：從關系R中選擇若干屬性組成新的關系，并把新關系的重復元組去掉。記為： 3條件連接：將兩關系按一定條件連接成一個新關系，記為： 說明：條件連接：兩關系可以沒有公共屬性，若有公共屬性，則新關系含有重復屬性。4自然連接：將兩關系按公共屬性連接成一個新的關系，并把新關系的重復屬性去掉。記為： 說明： 自然連接：兩關系至少有一個公共屬性。 對于R的每個元組，S都從第一個元組開始判斷，若兩元組的公共屬性值相同，則產(chǎn)生一個新元組添加到新關系中，最后把新關系中的重復屬性去掉。 5除：給定關系R（x，y）和S（y，z），則R÷S=P（x），其

11、中x，y，z為屬性組。求解過程：求R中x可以取哪些值，并求各值的象集。求S在屬性組y上的投影K。檢查每個象集是否包含K注：除不是一個必須的運算，可以由其它運算符代替。例：設有關系R，S如下圖，求R÷S。RABCSBCDa1b1c2b1c2d1a2b3c7b2c1d1a3b4c6b2c3d2a1b2c3a4b6c6a2b2c3a1b2c1解：在關系R中，A可以取四個值，a1，a2，a3，a4。a1的象集為（b1，c2），（b2，c3），（b2，c1）a2的象集為（b3，c7），（b2，c3）a3的象集為（b4，c6）a4的象集為（b6，c6）S在（B，C）上的投影K為（b1，c2），（

12、b2，c3），（b2，c1）顯然只有a1的象集包含K，故R÷S=a1結論：如何寫關系代數(shù)表達式？答： 查詢涉及多個關系時，一般使用 。 查詢涉及“否定”時，一般用差運算。 查詢涉及“全部”時，一般用除運算。 查詢涉及“至少”時，一般用×第3章 關系數(shù)據(jù)庫規(guī)范化理論項目3.1 函數(shù)依賴一、有關概念：R表XHKHKMXMDZCJ961C1OS高明D170962C2DBS高飛D272962C4AI高飛D280962C1OS高明D175963C1OS高明D1901函數(shù)依賴：任給R（U），U為屬性集，x、y為U的子集，如果對于x的每個值，y有唯一確定的值與之對應，則稱x決定y，或y函

13、數(shù)依賴于x。記為：xy。例：KHKMXMDZ(XH,KH) CJKH(KM,XM)2. 完全函數(shù)依賴：若xy，且對于x的所有真子集x,都有x y，則稱x完全決定y，或y完全函數(shù)依賴于x。記為：。例1：(XH,KH) CJ但 XH CJ KH CJ故 例2：KHKM則 結論：若xy，且x只包含一個屬性，則。3部分函數(shù)依賴：若xy，且存在x的一個真子集x,滿足xy，則稱x部分決定y，或y部分函數(shù)依賴于x。記為：。例1：(KH,KM)XM但 KMXM則 例2：(XH,KH)DZ但 KHDZ則 4傳遞函數(shù)依賴：若xy,yz,但 y x,則例：KMXM,XMDZ,但XM KM二、平凡函數(shù)依賴與非平凡函數(shù)

14、依賴設xy，如果y是x的子集，則該依賴是平凡的。如：Sno,snamesno 如果y中至少有一個屬性不在x中，則該依賴是非平凡的。如：Sno,snamesname,sdept如果y中沒有一個屬性在x中，則該依賴為完全非平凡的。三、函數(shù)依賴的推理規(guī)則設有關系R，x、y、z為R的一個屬性集，則有：自反律：若，則xy。增廣律：若xy，則xzyz。傳遞律：若xy，yz，則xz。注意傳遞律與傳遞函數(shù)依賴的區(qū)別。合并律：若xy，xz,則xyz。分解律：若xyz，則xy，xz。項目3.2 關系模式的規(guī)范化一、問題提出R表XHKHKMXMDZCJ961C1OS高明D170962C2DBS高飛D272962C4

15、AI高飛D280962C1OS高明D175963C1OS高明D190答：存在問題 數(shù)據(jù)冗余大； 修改麻煩； 插入異常：應該插入到DB中的數(shù)據(jù)插不進去。如：新開課程沒有學生選修時，新開課程的課程號、課程名插不進去。 刪除異常：不應該刪除的數(shù)據(jù)被刪掉。如選修某門課的學生畢業(yè)了，在刪除學生信息的同時，把課程信息也刪除掉。結論：一個好的關系模式應滿足： 冗余應盡可能少； 應盡可能避免插入、刪除異常； 消去關系中不合適的屬性依賴關系。二、范式 什么叫范式？指一個關系的非主屬性函數(shù)依賴于主碼的程度。 什么叫關系規(guī)范化？指一個關系從低級范式向高級范式的轉換過程。 應用：關系規(guī)范化理論應用在邏輯結構設計階段。

16、三、關系模式的規(guī)范化1第一范式（1NF） 定義：若關系R的所有屬性不能再分，則R1NF 存在問題 原因：存在非主屬性對主碼的部分依賴。 解決辦法：消除非主屬性對主碼的部分依賴，將關系R一分為二，將滿足完全依賴的屬性集組成一個關系；將滿足部分依賴的屬性集組成另一個關系；R1表 R2表XHKHCJKHKMXMDZ961C170C1OS高明D1962C272C2DBS高飛D2962C480C4AI高飛D2962C175963C190R1主碼：（XH,KH）R2主碼：KH2第二范式（2NF） 定義：若關系R1NF，且它的每個非主屬性都完全依賴于主碼，則稱R2NF。 存在問題：l 冗余大： R1必要冗余

17、，R2冗余可以修改。l 修改麻煩l 插入異常：如新來的教師沒有上課，則該教師的信息就沒辦法插入R2表中。l 刪除異常：若某位教師只授一門課，當該門課不開時，該教師的信息亦被刪除。 原因：存在非主屬性對主碼的傳遞依賴。KHXM，XMDZ，但XM KH傳遞依賴必須有兩個非主屬性 解決辦法：將R2 一分為二R21表 R22表KHKMXMXMDZC1OS高明高明D1C2DBS高飛高飛D2C4AI高飛R21主碼：KHR22主碼：XM3第三范式（3NF） 定義：若關系R2NF，且它的每個非主屬性都不傳遞依賴于主碼，則稱R3NF。 規(guī)范化過程非規(guī)范關系使每個屬性都不能再分1NF 消去非主屬性對主碼的部分依賴

18、2NF 消去非主屬性對主碼的傳遞依賴3NF4結論 若R1NF，且主碼只含一個屬性，則R一定為2NF。 若R2NF，且只有01個非主屬性，則R一定為3NF。 3NF一般控制了數(shù)據(jù)冗余，一般避免了操作異常。 范式并非越高越好，適可而止。第4章 數(shù)據(jù)庫設計項目4.1 需求分析一、數(shù)據(jù)庫設計的步驟 需求分析：了解分析用戶的需要、要求。 概念結構設計：根據(jù)需求分析的結果畫概念模型（即E-R圖）。 邏輯結構設計：將E-R圖轉換為某一種數(shù)據(jù)模型，并優(yōu)化。 物理結構設計 數(shù)據(jù)庫實施 數(shù)據(jù)庫運行與恢復二、有關概念1弱實體如果一個實體型的存在依賴于另一個實體型的存在，則稱該實體型為弱實體。例：一個職工有多個親屬，

19、沒有職工就沒有親屬。職工有親屬1n職工（職工號，職工名，密碼）親屬（職工號，親屬姓名，親屬關系）注：弱實體的主碼由父表的主碼與弱實體本身的碼組成。2 超類與子類教師如：助師講師副教授教授 職工工人管理員飛行員 超類：包含子類的公共屬性；子類：包含超類的主碼以及子類本身特有的屬性。職工（職工號，姓名，性別，密碼，出生年月，身份證號）飛行員（職工號，飛行小時，健康檢查，飛機型號）管理員（職工號，職務，職稱）工人（職工號，技術等級）項目4.2 概念結構設計一、局部E-R圖設計 1確定局部范圍 通常把系統(tǒng)涉及的各個部門或各個主要功能作為局部。2確定實體與屬性 屬性是不能再分的數(shù)據(jù)項； 聯(lián)系只發(fā)生在兩實

20、體之間； 原則上，能夠作為屬性，就不要作為實體。二、合并成總體E-R圖1消除各局部E-R圖的沖突問題。2按公共實體名合并，生成初步E-R圖。3消除冗余的屬性和冗余的聯(lián)系，生成總體E-R圖。項目4.3 邏輯結構設計一、聯(lián)系的屬性和主碼（1）聯(lián)系的屬性：必須包含相關聯(lián)的各實體型的主碼。（2）聯(lián)系的主碼1：1聯(lián)系：可以是相關聯(lián)的任一實體型的主碼。1：n聯(lián)系：必須是n方實體型的主碼。m：n聯(lián)系：必須是相關聯(lián)的各實體型的主碼之和。二、E-R圖向關系模型的轉換（1）把每個實體型轉換為一個關系模式。（2）1：1聯(lián)系：可以消化到相關聯(lián)的任一實體型對應的關系模式中。NLXMXHBHXHBHRS班級管理班長 11

21、班長( XH， XM， NL，BH)班級（BH，RS）（3）1：n聯(lián)系：可以消化到n方實體名對應的關系模式中。例：一個班級有多名學生，每名學生只能屬于一個班級。每一個班級有一名班長，他是學生中的一員。XHXMNL 學生 1n班長組成XH組成BH11班級BHRS學生（XH，XM，NL，BH）班級（BH，RS，XH） 班長的學號（4）m：n聯(lián)系：必須轉換為一個關系模式，并且不能消化。sagessexsname例：sdeptcnogradesno選修理工creditcnamecno課程sno學生 mn學生(sno，sname， ssex， sage， sdept)課程（cno， cname，cred

22、it）選修（sno， cno， grade）（5）多元聯(lián)系：不能消化例：供應商m供應 nmk零件產(chǎn)品項目4.4 物理結構設計與數(shù)據(jù)庫實施1物理結構設計在邏輯設計的基礎上，為每個關系模式選擇合適的存儲結構與存儲方式。選擇存儲結構：即決定每個表的記錄順序。選擇存取方式：即決定為哪些屬性建立非聚集索引，以便加快查找速度。一般把經(jīng)常查詢的屬性名指定為非聚集索引。2數(shù)據(jù)庫實施主要工作：定義數(shù)據(jù)庫結構；組織數(shù)據(jù)入庫；編寫應用程序；數(shù)據(jù)庫試運行；第5章 SQL Server 2005系統(tǒng)入門一、運行環(huán)境最低處理器速度：600MHZ（推薦1GB或者更快）最小內(nèi)存：512MB（推薦1GB或者更大）可用硬盤空間：

23、1.6GB二、SQL Server 2005的主要組件1服務：用于啟動SQL Server 2005服務器單擊“開始”菜單，選擇“控制面板”|“管理工具”|“服務”選項，將“SQL Server （MSSQLSERVER）”選項置為“自動”，即隨操作系統(tǒng)的啟動而自動啟動。安裝時默認為“自動”。2配置管理器（Configuration Manager）：用于啟動SQL Server 2005服務器注意：“服務”窗口中顯示的是操作系統(tǒng)中全部的服務程序，而“配置管理器”窗口中顯示的僅僅是與SQL Server 2005有關的服務程序。3SQL Server Management Studio（簡稱S

24、SMS，管理工作室）組合了對象資源管理器、查詢編輯器的功能。對象資源管理器用于管理數(shù)據(jù)庫服務器中的所有數(shù)據(jù)庫對象；4SSMS查詢編輯器：主要用于輸入、執(zhí)行和保存Transact-SQL命令第6章 創(chuàng)建和使用數(shù)據(jù)庫一、數(shù)據(jù)庫文件1一個數(shù)據(jù)庫至少有一個主要數(shù)據(jù)文件和一個事務日志文件。如果數(shù)據(jù)庫很大，可以使用一個主要數(shù)據(jù)文件、多個次要數(shù)據(jù)文件和多個事務日志文件。用于存放數(shù)據(jù)庫的各類對象主數(shù)據(jù)文件(.mdf )次數(shù)據(jù)文件(.ndf ) 事務日志文件(.ldf )：用來記錄對數(shù)據(jù)庫對象的所有更新操作。2系統(tǒng)數(shù)據(jù)庫Master數(shù)據(jù)庫、Model數(shù)據(jù)庫二、創(chuàng)建數(shù)據(jù)庫1打開數(shù)據(jù)庫：Use 數(shù)據(jù)庫名2刪除數(shù)據(jù)庫

25、：Drop database數(shù)據(jù)庫名三、修改數(shù)據(jù)庫1分離和附加數(shù)據(jù)庫2備份和還原數(shù)據(jù)庫3數(shù)據(jù)的導入和導出第7章 創(chuàng)建和使用表項目7.1 數(shù)據(jù)類型1字符型char(n)： 定長字符型，n表示字符數(shù)，取值為18000。若缺省n，則默認為1。varchar(n)：變長字符型Text：可以存儲任意長的字符串說明：標準字符型：每個英文字母、數(shù)字算1個字符，每個漢字算2個字符，每個字符占1個字節(jié)。2統(tǒng)一碼字符型每個英文字母、數(shù)字、漢字算1個字符，每個字符占2個字節(jié)。nchar(n)： n表示字符數(shù)，取值為14000。nvarchar(n)ntext：可以存儲任意長的統(tǒng)一碼字符串。例：設某表的結構如下：no

26、 char(6), name nchar(6)則no可以賦予3個漢字，name可以賦予6個漢字3 整型取值范圍所占字節(jié)數(shù)Bigint8Int4Smallint-32768 +327672Tinyint02551bit0,1即邏輯型4實型 精確數(shù)值型Decimal(p,s)Numeric(p,s)精度P：表示全部數(shù)字的位數(shù)（不計小數(shù)點和正負號）S：表示小數(shù)位數(shù)，若缺省S，則默認為0P-S：表示整數(shù)位數(shù) 近似數(shù)值型Real 精確到7位有效數(shù)字Float 精確到15位有效數(shù)字5貸幣型Money：占8個字節(jié)，保留4位小數(shù)Smallmoney：占4個字節(jié)，保留4位小數(shù)字符串常量：由單引號括住的字符序列貸

27、幣型常量：可以是一個實型常量6日期時間型Datetime、Smalldatetime 若省略日期部分，則默認為：1900-1-1若省略時間部分，則默認為：00:00:00 日期格式：yyyy-mm-dd時間格式：hh:mm:ss項目7.2 使用對象資源管理器創(chuàng)建和管理表一、創(chuàng)建表1在一個表中只能定義一個標識字段。2只有整型和精確數(shù)值型（decimal、numeric）字段才能指定為標識字段。3把某個字段指定為公式字段。二、設置約束1主鍵約束：每個表中只能有一列或一個組合被指定為主鍵，主鍵中的各個列不能為空值。2外鍵約束：創(chuàng)建外鍵約束，就是定義兩個表的永久關聯(lián)，這兩個表分別稱為主鍵表、外鍵表。外

28、鍵表中外鍵的值只能是主鍵表中主鍵的有效值或空值。3唯一性約束：Ü 主鍵約束與唯一性約束的區(qū)別是： 在一個表中只能定義一個主鍵約束，但可定義多個唯一性約束； 指定為主鍵約束的列不能取空值，但指定為唯一性約束的列允許取空值。Ü 創(chuàng)建主鍵約束、唯一性約束時可以指定聚集或非聚集。主鍵約束默認為聚集的，唯一性約束默認為非聚集的。4檢查約束：用于限制輸入到一列或多列的值的范圍，保證數(shù)據(jù)庫的數(shù)據(jù)完整性。5默認值約束：三、建立數(shù)據(jù)庫關系圖要實現(xiàn)兩表的永久關聯(lián)，可以創(chuàng)建外鍵約束，也可以創(chuàng)建數(shù)據(jù)庫關系圖。第8章 SQL語言項目8。1 定義表一、SQL語言的特點SQL語言集數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)

29、據(jù)操縱、數(shù)據(jù)控制的功能于一體。動詞數(shù)據(jù)定義Create、drop數(shù)據(jù)查詢select數(shù)據(jù)操縱Insert、delete、update數(shù)據(jù)控制Grant、revoke 二、 定義表1創(chuàng)建表create table 表名（屬性名 類型，屬性名 類型） 指定標識字段：identity(標識種子，標識增量) 指定公式字段：屬性名 as 表達式例：create table student (no int identity(1,1),name char(6),chi smallint,mat smallint,score as chi+mat)2刪除表drop table表名, 表名項目82查詢表一、se

30、lect語句select */表達式表into 新表名from 表名,表名where 條件group by 屬性名having 條件order by屬性名Asc/Desc1Select 子句 *代表所有屬性名 若一個屬性名來自多個表，則屬性名前須冠以表名，格式為：表名. 屬性名 設置表達式的別名： 表達式 As 別名 限制查詢結果的記錄行數(shù)： all 返回全部記錄 top n 返回前面n號記錄 distinct 表示取消重復行 說明：top n只能放在關鍵字select的后面； all、distinct只能放在關鍵字select或聚合函數(shù)的后面。2Where 子句 in的格式：屬性名 in (

31、常量，常量) like的格式：屬性名 like 通配字符串通配符有： % 表示0個或多個字符 - 表示1個字符 在Where 子句中指定連接： Where 表名1. 屬性名=表名2. 屬性名3Order by子句 order by屬性名1 Asc/Desc, 屬性名2 Asc/Desc4聚合函數(shù) sum(屬性名)：縱向求數(shù)值型屬性之和。 avg(屬性名) count(*) 返回表的記錄行數(shù)（含重復行）。count(屬性名) 返回指定列中取非NULL值的單元格數(shù)目。 count(distinct 屬性名) 返回指定列中取非NULL值、非重復的單元格數(shù)目。 max(屬性名) min(屬性名)5Gr

32、oup by子句 使用Group by子句時，Select 子句只能使用分組項字段和聚合函數(shù) 6Having子句 Having子句只能跟在Group by子句之后，且只能使用聚合函數(shù)和分組項字段。 where子句放在Group by子句之前，甚至可以沒有Group by子句；且不能包含聚合函數(shù)。 例：以系別為分組項，查詢學生平均年齡大于19歲的系的系名和平均年齡。Select sdept,avg(sage) as平均年齡From studentGroup by sdeptHaving avg(sdept)>197into子句 功能：將查詢結果保存到新的基表中。二、查詢的分類 單表查詢連接

33、查詢嵌套查詢?nèi)粢粋€查詢問題涉及到多個表，必定可以寫成連接查詢；若一個查詢問題涉及到一個表，必定可以寫成單表查詢或嵌套查詢。1連接查詢：在where子句中指定連接where 表名1.屬性名=表名2.屬性名2嵌套查詢 嵌套查詢的特點Ü 每級查詢的from子句一般只包含一個表名。Ü 若一個查詢問題的中心詞來自一個表，才可以寫成嵌套查詢。Ü 子查詢不能使用order by子句，order by只能用于最頂層的查詢。 在where子句中指定子查詢where 屬性名 not in（子查詢）：子查詢返回一列多行。where 屬性名=（子查詢）：子查詢返回一列一行。where n

34、ot exists（子查詢）：子查詢返回多列多行。項目83 操縱表一、insert語句（1）每次插入一條記錄insert into 表名(屬性名表) values(表達式表)（2）插入子查詢的結果insert into 表名(屬性名表)子查詢 例：insert into student select * from student1二、update語句update 表名 set 屬性名=值，屬性名=值 where 條件 缺省where子句，默認為更新全部記錄。三、delete語句delete from 表名 where 條件第9章 T-SQL程序設計項目91 常量、變量、表達式一、常量 字符型：

35、由單引號括住，例：'china' 整型實型日期型：由單引號括住的具有日期或時間意義的序列，格式為： 'yyyy-mm-dd'， 'hh:mm:ss'二、變量 局部變量：由用戶定義和賦值，以 開頭。 全局變量：由系統(tǒng)定義和賦值，以 開頭。1.聲明局部變量Declare 變量名 類型例：declare a int,b char(5)注：不要把局部變量聲明為text、ntext、image2.給局部變量賦值 使用set語句： Set 變量名=表達式 使用select 語句 Select 變量名=表達式,，變量名=表達式 from 表名 若表達式中含有屬

36、性名，則必須使用from子句。例：declare a char(5),b char(6)Select a='95001' ,b='王名'或：select a=sno,b=snameFrom student3.輸出表達式的值： 使用print 語句 Print 表達式 使用select語句 Select 表達式，表達式 from 表名4局部變量的作用域： 只能在聲明它的批處理中使用。三、函數(shù)1創(chuàng)建函數(shù)create function 函數(shù)名(形參名 類型) returns 類型asbegin函數(shù)體 End注： 函數(shù)體最后一條語句必須是return語句。2執(zhí)行函數(shù)se

37、lect 變量名1=用戶名.函數(shù)名(常量| 變量名) Print 用戶名.函數(shù)名 (常量| 變量名)3刪除函數(shù)Drop function 函數(shù)名四、運算符算術運算符（*、/、% +、-） 關系運算符（>、>=、<、<=、=、<>） 邏輯運算符（not and or）項目92 使用批處理1什么叫批處理？一個腳本由一個或多個批處理組成，批處理以GO作為結束標志。2批處理是腳本的編譯單位，當一個批處理中的某個語句出現(xiàn)編譯錯誤，則批處理中的任何語句均無法執(zhí)行。3當一個批處理中的某個語句出現(xiàn)運行錯誤，則批處理中當前語句和它之后的語句將無法執(zhí)行。項目93 流程控制語句一

38、、beginend語句Begin語句1 ：語句nEnd二、if-else語句1格式：if 邏輯表達式 語句1 else 語句22當邏輯表達式包含子查詢時，子查詢必須用括號括住。 屬性名 in (子查詢) 子查詢返回一列多行 屬性名 = (子查詢) 子查詢返回一列一行 Exists(子查詢) 子查詢返回多列多行三、case表達式Case When 邏輯表達式1 then 結果1When 邏輯表達式2 then 結果2else結果nEnd說明：case表達式不是語句，不能單獨執(zhí)行。例：use studSelect 姓名=sname,系別=CaseWhen sdept= 'CS' t

39、hen '計算機科學系'When sdept= 'IS' then '信息系統(tǒng)系'When sdept= 'MA' then '數(shù)學系'End四、while語句1格式： While邏輯表達式 Begin 語句組 End2專用于循環(huán)體的語句： Break 強制退出while語句，執(zhí)行其后續(xù)語句。 Continue 返回while語句的入口。 Break、Continue必須放在循環(huán)體內(nèi)，并常與if-else語句結合使用。五、 其它語句 Return語句 格式：Return 整數(shù)值功能：用于存儲過程或批處理中，功能是退

40、出所在的存儲過程或批處理。說明：Return放在函數(shù)中，后面可跟算術表達式和字符串表達式；Return放在存儲過程中，后面只跟整型表達式；Return放在批處理中，后面不跟表達式。 Waitfor 語句項目94 使用游標一、游標的概念1每個表均有一個游標，它可以指向表的任意一條記錄。2移動游標的方法： 在觸發(fā)器或存儲過程中，使用SQL語句定義和使用游標。 在前臺應用程序中，使用主語言實現(xiàn)對游標的移動。二、Transact-SQL游標的使用1. 聲明游標Declare 游標名 cursorforward_only / scroll global /localread_only for selec

41、t 語句 forward_only(只進游標)：只能進行next操作，缺省為forward_only。 scroll （滾動游標）global /local：缺省為：global2打開游標open 游標名打開游標時，游標指向查詢結果集的第一條記錄之前。3提取游標 fetch next / prior / first /last from游標名 into 局部變量名表 缺省into子句：移動游標，并顯示當前記錄的內(nèi)容。 含into子句：移動游標，并將當前記錄各屬性值依次賦給局部變量。 缺省游標移動方式，則為next。4關閉游標 close游標名5釋放游標deallocate游標名例：-打印stu

42、dent表中全體男生的平均年齡。（ 不得使用聚合函數(shù)。）set nocount ondeclare x int, s int,n decimal(4,1)select n=0, s=0declare pm cursorscrollfor select sage from student where ssex='男'open pmfetch next from pm into xwhile FETCH_STATUS=0beginselect n=n+1, s=s+xfetch next from pm into xendprint '全體男生的平均年齡為:'+st

43、r(s/n,4,1)close pmdeallocate pm三、有關全局變量1FETCH_STATUS：返回上一個Fetch語句的執(zhí)行狀態(tài)，若Fetch語句成功執(zhí)行，則返回0，否則返回-1。2rowcount：返回受上一個語句（包括select、insert、delete、update）所影響的行數(shù)。3set nocount on：關閉影響行數(shù)信息。項目95 并發(fā)控制一、什么是事務？1事務是用戶定義的一組操作序列。 事務是并發(fā)控制的基本單位。 一個事務包含的諸操作要么都執(zhí)行，要么都不執(zhí)行。2事務的屬性原子性：指事務中包含的諸操作要么都執(zhí)行，要么都不執(zhí)行。一致性：事務必須使數(shù)據(jù)庫從一個一致性狀

44、態(tài)變到另一個一致性狀態(tài)。隔離性：一個事務的執(zhí)行不能被其他事務干擾。持續(xù)性3顯式定義事務begin transaction ：commit/rollback當未顯式指定事務，則一個SQL語句就是一個事務。二、并發(fā)問題1并發(fā)操作造成的數(shù)據(jù)不一致性問題丟失修改：指事務1與事務2從數(shù)據(jù)庫中讀入同一數(shù)據(jù)并修改，事務2的提交結果破壞事務1提交的結果，導致事務1的修改被丟失。不可重復讀：指事務1讀取數(shù)據(jù)后，事務2執(zhí)行更新操作，使事務1無法再現(xiàn)前一次讀取結果。讀臟數(shù)據(jù)：指事務1修改某一數(shù)據(jù)后，事務2讀取該數(shù)據(jù)，事務1由于某種原因被撤銷，這時數(shù)據(jù)又恢復到原值，事務2讀到的數(shù)據(jù)與數(shù)據(jù)庫中的數(shù)據(jù)不一致，稱為“臟”數(shù)

45、據(jù)。2產(chǎn)生數(shù)據(jù)不一致性的原因 并發(fā)操作破壞了事務的隔離性。3并發(fā)事務正確性的原則幾個事務的并發(fā)執(zhí)行是正確的，當且僅當其結果與任何一個串行執(zhí)行的結果相同。4并發(fā)控制的方法DBMS一般采用“封鎖”技術，保證并發(fā)操作的可串行化。三、封鎖（Locking）1什么叫封鎖？SQL Server自動強制封鎖，并且會將封鎖粒度控制在合適的級別，用戶不必考慮封鎖問題。2封鎖類型排它鎖（X鎖）：事務T對數(shù)據(jù)A加X鎖，其它事務不能再對A加鎖，即其它事務不能讀取和修改A。共享鎖（S鎖）：事務T對數(shù)據(jù)A加S鎖，其它事務只能再對A加S鎖，即其它事務只能讀A，不能修改A。3封鎖粒度封鎖對象可以是屬性列、元組、關系、整個數(shù)據(jù)

46、庫。封鎖對象的大小稱為封鎖粒度。封鎖粒度越小，并發(fā)度越高，但并發(fā)控制的開銷越大。4封鎖協(xié)議 事務T在修改數(shù)據(jù)A之前，必須對其加X鎖，直到事務結束才釋放。 事務T在讀取數(shù)據(jù)A之前，必須對其加S鎖，直到事務結束才釋放。遵循封鎖協(xié)議，可以解決三種數(shù)據(jù)不一致性問題： 丟失修改 不可重復讀 讀“臟”數(shù)據(jù)四、死鎖和活鎖 封鎖技術可以解決并發(fā)操作的不一致性問題，但也帶來新的問題，即死鎖和活鎖。1死鎖： 定義：兩個事務已經(jīng)各自鎖定一個數(shù)據(jù)，但是又要訪問被對方鎖定的數(shù)據(jù)，造成了循環(huán)等待，稱為死鎖。 避免死鎖的方法：順序封鎖法：若規(guī)定封鎖順序為A，B，則T1，T2只能先封鎖A，再封鎖B。2活鎖： 定義：若多個事務

47、請求封鎖同一個數(shù)據(jù)時，其中的某個事務總處于等待狀態(tài)，則稱為活鎖。 避免活鎖的方法：先來先服務第10章 視圖項目10.1 在FROM子句中指定連接1內(nèi)連接：from 表名1 inner join 表名2 on 條件例：select * from student,sc where student.sno=sc.sno等價于 select * from student inner join sc on student.sno=sc.sno2左外連接：from 表名1 left outer join 表名2 on 條件3右外連接：from 表名1 right outer join 表名2 on 條件4

48、完全外連接：from 表名1 full outer join 表名2 on 條件5交叉連接項目10.2 創(chuàng)建和管理視圖一、視圖的特點 視圖只有結構，沒有記錄，是虛表； 一個視圖總對應著一個select語句； 對視圖的查詢、更新，實際上是對基本表的查詢、更新。二、創(chuàng)建視圖Create view 視圖名 (屬性名，屬性名)As 子查詢with check option說明：視圖的屬性個數(shù)必須與子查詢中select子句的表達式個數(shù)相同。三、刪除視圖Drop view 視圖名，視圖名項目10.4 使用視圖一、查詢視圖select */表達式表from 視圖名,視圖名where 條件group by 屬性名order by屬性名Asc/Desc二、操縱視圖1向視圖插入一條記錄insert into 視圖名(屬性名表) values(表達式表)2修改視圖中的數(shù)據(jù)update視圖名set 屬性名=值，屬性名=值 where 條件缺省where子句，