Sqlsever 基礎(chǔ)培訓(xùn)資料 第一部分

1、第一部分?jǐn)?shù)據(jù)庫(kù)的設(shè)計(jì)1數(shù)據(jù)庫(kù)和應(yīng)用程序數(shù)據(jù)庫(kù)服務(wù)器響應(yīng)和提供數(shù)據(jù)應(yīng)用程序數(shù)據(jù)庫(kù)操作和查詢2數(shù)據(jù)庫(kù)和應(yīng)用程序應(yīng)用程序作用：響應(yīng)操作并顯示結(jié)果、向數(shù)據(jù)庫(kù)請(qǐng)求數(shù)據(jù)要求：美觀、操作簡(jiǎn)單方便數(shù)據(jù)庫(kù)作用：存儲(chǔ)數(shù)據(jù)、檢索數(shù)據(jù)、生成新的數(shù)據(jù)要求：統(tǒng)一、安全、性能等3數(shù)據(jù)庫(kù)能夠做什么存儲(chǔ)大量數(shù)據(jù)，方便檢索和訪問(wèn)保持?jǐn)?shù)據(jù)信息的一致、完整共享和安全通過(guò)組合分析，產(chǎn)生新的有用信息4時(shí)下流行的數(shù)據(jù)庫(kù)OracleOracle公司的產(chǎn)品“關(guān)系-對(duì)象”型數(shù)據(jù)庫(kù)產(chǎn)品免費(fèi)、服務(wù)收費(fèi)SQL Server針對(duì)不同用戶群體的五個(gè)特殊的版本易用性好DB2IBM公司的產(chǎn)品支持多操作系統(tǒng)、多種類(lèi)型的硬件和設(shè)備5數(shù)據(jù)庫(kù)的基本概念 6-1這些客

2、觀存在的、可以被描述事物都是“實(shí)體”6數(shù)據(jù)庫(kù)的基本概念 6-2行(記錄)列7數(shù)據(jù)庫(kù)的基本概念 6-3數(shù) 據(jù) 表存儲(chǔ)過(guò)程視 圖.訂單客戶產(chǎn)品產(chǎn)品數(shù)據(jù)庫(kù)表數(shù)據(jù)庫(kù)就是“數(shù)據(jù)”的“倉(cāng)庫(kù)”數(shù)據(jù)庫(kù)由表、關(guān)系以及操作對(duì)象組成數(shù)據(jù)存放在表中8數(shù)據(jù)庫(kù)的基本概念 6-4馬蘭A004楊可A003劉開(kāi)泰B008李新愛(ài)B007數(shù)據(jù)庫(kù)檢索數(shù)據(jù)插入數(shù)據(jù)更新數(shù)據(jù)刪除數(shù)據(jù)數(shù)據(jù)庫(kù)管理系統(tǒng)DBMS9數(shù)據(jù)庫(kù)的基本概念 6-5編號(hào)姓名年齡民族部門(mén)1王濤33漢族人事管理部2李梅27漢族人事管理部1127李梅21133王濤1部門(mén)編碼民族編碼年齡姓名編號(hào)回族2漢族1民族民族編碼市場(chǎng)營(yíng)銷(xiāo)部2人事管理部1部門(mén)部門(mén)編碼存在冗余為減少數(shù)據(jù)查找的麻煩

3、，允許數(shù)據(jù)有一定的冗余10數(shù)據(jù)庫(kù)的基本概念 6-6Roll NumberNameAddressBookTaken12Alicia Ruth12, Temple StreetAC09114Jason Darren123, Sunset Blvd.AC04315Mary Beth32, Golden AvenueAC02112Alicia Ruth12, Temple StreetAC04312Alicia Ruth12, Tmple StreetAC01115Mary Beth33, Golden AvenueAC011不同的地址!存在不正確、不準(zhǔn)確的數(shù)據(jù)，數(shù)據(jù)庫(kù)“失去了完整性”姓名11知識(shí)點(diǎn)數(shù)

4、據(jù)庫(kù)有哪些基本操作？建庫(kù)建表加約束創(chuàng)建登錄帳戶 12目標(biāo)了解設(shè)計(jì)數(shù)據(jù)庫(kù)的基本步驟熟練實(shí)現(xiàn)建庫(kù)、建表、加約束13建表14SQL Server的數(shù)據(jù)類(lèi)型15什么是主鍵關(guān)系數(shù)據(jù)庫(kù)說(shuō)，為了唯一區(qū)分表的每一行記錄，必須為表確定一個(gè)主鍵。主鍵可以是一個(gè)或多個(gè)列組成，這些主鍵列的值不能重復(fù)。主鍵是兩個(gè)表進(jìn)行關(guān)聯(lián)的基礎(chǔ)，所謂“關(guān)系”體現(xiàn)的是一個(gè)表的字段與另一個(gè)表的主鍵的關(guān)聯(lián) 16選擇主鍵的原則最少性盡量選擇單個(gè)鍵作為主鍵穩(wěn)定性盡量選擇數(shù)值更新少的列作為主鍵17再論數(shù)據(jù)完整性數(shù)據(jù)完整性+=可靠性準(zhǔn)確性18再論數(shù)據(jù)完整性數(shù)據(jù)存放在表中“數(shù)據(jù)完整性的問(wèn)題大多是由于設(shè)計(jì)引起的”創(chuàng)建表的時(shí)候，就應(yīng)當(dāng)保證以后數(shù)據(jù)輸入是正

5、確的錯(cuò)誤的數(shù)據(jù)、不符合要求的數(shù)據(jù)不允許輸入創(chuàng)建表：保證數(shù)據(jù)的完整性 = 實(shí)施完整性約束19完整性包括2-1輸入的類(lèi)型是否正確？年齡必須是數(shù)字輸入的格式是否正確？身份證號(hào)碼必須是18位是否在允許的范圍內(nèi)？性別只能是”男”或者”女”是否存在重復(fù)輸入？學(xué)員信息輸入了兩次是否符合其他特定要求？信譽(yù)值大于5的用戶才能夠加入會(huì)員列表列值要求（約束）整行要求（約束）20實(shí)體完整性河南新鄉(xiāng)趙可以河南新鄉(xiāng)張麗鵑江西南昌雷銅湖南新田吳蘭山東定陶李山.地址姓名學(xué)號(hào)江西南昌雷銅約束方法：唯一約束、主鍵約束、標(biāo)識(shí)列21域完整性河南新鄉(xiāng)趙可以河南新鄉(xiāng)張麗鵑江西南昌雷銅湖南新田吳蘭山東定陶李山.地址姓名學(xué)號(hào)湖北江門(mén)李亮87

6、00000000約束方法：限制數(shù)據(jù)類(lèi)型、檢查約束、外鍵約束、默認(rèn)值、非空約束22為什么需要設(shè)計(jì)數(shù)據(jù)庫(kù)修建茅屋需要設(shè)計(jì)嗎？修建大廈需要設(shè)計(jì)嗎？結(jié)論：當(dāng)數(shù)據(jù)庫(kù)比較復(fù)雜時(shí)我們需要設(shè)計(jì)數(shù)據(jù)庫(kù)23為什么需要設(shè)計(jì)數(shù)據(jù)庫(kù)良好的數(shù)據(jù)庫(kù)設(shè)計(jì)：節(jié)省數(shù)據(jù)的存儲(chǔ)空間能夠保證數(shù)據(jù)的完整性方便進(jìn)行數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的開(kāi)發(fā)糟糕的數(shù)據(jù)庫(kù)設(shè)計(jì)：數(shù)據(jù)冗余、存儲(chǔ)空間浪費(fèi)內(nèi)存空間浪費(fèi)數(shù)據(jù)更新和插入的異常24軟件項(xiàng)目開(kāi)發(fā)周期需求分析階段：分析客戶的業(yè)務(wù)和數(shù)據(jù)處理需求;概要設(shè)計(jì)階段：設(shè)計(jì)數(shù)據(jù)庫(kù)的E-R模型圖，確認(rèn)需求信息 的正確和完整;詳細(xì)設(shè)計(jì)階段：將E-R圖轉(zhuǎn)換為多張表，進(jìn)行邏輯設(shè)計(jì)， 并應(yīng)用數(shù)據(jù)庫(kù)設(shè)計(jì)的三大范式進(jìn)行審核;代碼編寫(xiě)階段：選

7、擇具體數(shù)據(jù)庫(kù)進(jìn)行物理實(shí)現(xiàn)，并編寫(xiě) 代碼實(shí)現(xiàn)前端應(yīng)用現(xiàn)實(shí)世界建模信息世界數(shù)據(jù)庫(kù)世界模型轉(zhuǎn)換 規(guī)范化25設(shè)計(jì)數(shù)據(jù)庫(kù)的步驟-1收集信息： 與該系統(tǒng)有關(guān)人員進(jìn)行交流、坐談，充分理解數(shù)據(jù)庫(kù)需要完成的任務(wù)BBS論壇的基本功能：用戶注冊(cè)和登錄，后臺(tái)數(shù)據(jù)庫(kù)需要存放用戶的注冊(cè)信息和在線狀態(tài)信息；用戶發(fā)貼，后臺(tái)數(shù)據(jù)庫(kù)需要存放貼子相關(guān)信息，如貼子內(nèi)容、標(biāo)題等；論壇版塊管理：后臺(tái)數(shù)據(jù)庫(kù)需要存放各個(gè)版塊信息，如版主、版塊名稱(chēng)、貼子數(shù)等；26設(shè)計(jì)數(shù)據(jù)庫(kù)的步驟-2標(biāo)識(shí)對(duì)象（實(shí)體Entity） 標(biāo)識(shí)數(shù)據(jù)庫(kù)要管理的關(guān)鍵對(duì)象或?qū)嶓w 實(shí)體一般是名詞：用戶：論壇普通用戶、各版塊的版主。用戶發(fā)的主貼用戶發(fā)的跟貼（回貼）版塊：論壇的各個(gè)

8、版塊信息27設(shè)計(jì)數(shù)據(jù)庫(kù)的步驟-3論壇用戶：呢稱(chēng)密碼電子郵件生日性別用戶的等級(jí)備注信息注冊(cè)日期狀態(tài)積分主貼發(fā)貼人發(fā)貼表情回復(fù)數(shù)量標(biāo)題正文發(fā)貼時(shí)間點(diǎn)擊數(shù)狀態(tài)：最后回復(fù)時(shí)間回貼貼子編號(hào)回貼人,回貼表情標(biāo)題正文回貼時(shí)間點(diǎn)擊數(shù)版塊版塊名稱(chēng)版主本版格言點(diǎn)擊率發(fā)貼數(shù)標(biāo)識(shí)每個(gè)實(shí)體的屬性（Attribute）28設(shè)計(jì)數(shù)據(jù)庫(kù)的步驟-4標(biāo)識(shí)對(duì)象之間的關(guān)系（Relationship）跟貼和主貼有主從關(guān)系：我們需要在跟貼對(duì)象中表明它是誰(shuí)的跟貼；版塊和用戶有關(guān)系：從用戶對(duì)象中可以根據(jù)版塊對(duì)象查出對(duì)應(yīng)的版主用戶的情況；主貼和版塊有主從關(guān)系：需要表明發(fā)貼是屬于哪個(gè)版塊的；跟貼和版塊有主從關(guān)系：需要表明跟貼是屬于哪個(gè)版塊的；2

9、9繪制E-R圖1111M1MMMM用戶積分性別用戶等級(jí)備注信息注冊(cè)日期版塊名稱(chēng)本版留言發(fā)貼數(shù)狀態(tài)密碼昵稱(chēng)電子郵件生日論壇用戶（BBSUser）管理發(fā)表發(fā)表跟隨屬于屬于點(diǎn)擊率版主標(biāo)題發(fā)貼人貼子編號(hào)正文點(diǎn)擊率版塊（BBSSection）發(fā)貼（BBSTopic）發(fā)貼人正文狀態(tài)貼子編號(hào)所在版塊最后回復(fù)時(shí)間發(fā)貼表情回復(fù)數(shù)量點(diǎn)擊率發(fā)貼時(shí)間標(biāo)題M跟貼（BBSReply）所在版塊發(fā)貼時(shí)間最后回復(fù)時(shí)間發(fā)貼表情1論壇E-R圖 30 如何將E-R圖轉(zhuǎn)換為表 3-2UID主鍵TID主鍵RID主鍵SID主鍵31如何將E-R圖轉(zhuǎn)換為表添加各表之間的關(guān)系32數(shù)據(jù)規(guī)范化 僅有好的RDBMS并不足以避免數(shù)據(jù)冗余，必須在數(shù)據(jù)庫(kù)的

10、設(shè)計(jì)中創(chuàng)建好的表結(jié)構(gòu) 最初定義了規(guī)范化的三個(gè)級(jí)別，范式是具有最小冗余的表結(jié)構(gòu)。這些范式是：第一范式(1st NF First Normal Fromate)第二范式(2nd NFSecond Normal Fromate)第三范式(3rd NF Third Normal Fromate)33第一范式 (1st NF)BuyerIDCountryCity1142中國(guó)中國(guó)日本美國(guó)北京北京東京紐約BuyerIDAddress1234中國(guó)北京市 美國(guó)紐約市英國(guó)利物浦日本東京市 第一范式的目標(biāo)是確保每列的原子性如果每列都是不可再分的最小數(shù)據(jù)單元（也稱(chēng)為最小的原子單元），則滿足第一范式（1NF）34第二范

11、式 (2nd NF)如果一個(gè)關(guān)系滿足1NF，并且除了主鍵以外的其他列，都依賴與該主鍵，則滿足第二范式（2NF）(一是表必須有一個(gè)主鍵；二是沒(méi)有包含在主鍵中的列必須完全依賴于主鍵，而不能只依賴于主鍵的一部分 ) 第二范式要求每個(gè)表只描述一件事情Orders字段例子訂單編號(hào)產(chǎn)品編號(hào)訂購(gòu)日期價(jià) 格001A0012000-2-3$29.00 Orders字段例子訂單編號(hào)訂購(gòu)日期0012000-2-3Products字段例子產(chǎn)品編號(hào)價(jià) 格A001$29.0035例如(學(xué)生選課表):學(xué)生課程教師教師職稱(chēng)教材教室上課時(shí)間李四Spring張老師java講師Spring深入淺出 08:00張三Struts楊老師

12、 java講師Struts in Action 13:30 修改后，選課表：學(xué)生課程教師教師職稱(chēng)教室上課時(shí)間李四Spring張老師java講師08:00張三Struts楊老師java講師13:30課程表：課程教材SpringSpring深入淺出StrutsStruts in Action 36第三范式 (3rd NF)如果一個(gè)關(guān)系滿足2NF，并且除了主鍵以外的其他列都不傳遞依賴于主鍵列，則滿足第三范式（3NF） (首先是 2NF，另外非主鍵列必須直接依賴于主鍵，不能存在傳遞依賴。即不能存在：非主鍵列 A 依賴于非主鍵列 B，非主鍵列 B 依賴于主鍵的情況 )Orders字段例子訂單編號(hào)訂購(gòu)日期

13、顧客編號(hào)0012000-2-3AB001顧客姓名Tony Orders字段例子訂單編號(hào)訂購(gòu)日期顧客編號(hào)0012000-2-3AB001 37選課表：學(xué)生課程教師教師職稱(chēng)教室上課時(shí)間李四Spring張老師java講師08:00張三Struts楊老師java講師 13:30課程表：課程教材SpringSpring深入淺出StrutsStruts in Action 學(xué)生課程教師教室上課時(shí)間李四Spring張老師08:00張三Struts楊老師13:30教師表：教師教師職稱(chēng)張老師java講師楊老師java講師 課程表：課程教材SpringSpring深入淺出StrutsStruts in Actio

14、n38總結(jié) 在需求分析階段，設(shè)計(jì)數(shù)據(jù)庫(kù)的一般步驟為：收集信息標(biāo)識(shí)對(duì)象標(biāo)識(shí)每個(gè)對(duì)象的屬性標(biāo)識(shí)對(duì)象之間的關(guān)系在概要設(shè)計(jì)階段和詳細(xì)設(shè)計(jì)階段，設(shè)計(jì)數(shù)據(jù)庫(kù)的步驟為：繪制E-R圖將E-R圖轉(zhuǎn)換為表格應(yīng)用三大范式規(guī)范化表格39總結(jié)為了設(shè)計(jì)結(jié)構(gòu)良好的數(shù)據(jù)庫(kù)，需要遵守一些專(zhuān)門(mén)的規(guī)則，稱(chēng)為數(shù)據(jù)庫(kù)的設(shè)計(jì)范式 。第一范式（1NF）的目標(biāo)：確保每列的原子性。第二范式（2NF）的目標(biāo)：確保表中的每列，都和主鍵相關(guān) 第三范式（3NF）的目標(biāo)：確保每列都和主鍵列直接相關(guān)，而不是間接相關(guān) 40關(guān)于范式范式可以避免數(shù)據(jù)冗余，減少數(shù)據(jù)庫(kù)的空間，減輕維護(hù)數(shù)據(jù)完整性的麻煩，但是操作困難，因?yàn)樾枰?lián)系多個(gè)表才能得到所需要數(shù)據(jù)，而且范式越

15、高性能就會(huì)越差。要權(quán)衡是否使用更高范式是比較麻煩的，一般在項(xiàng)目中，用得最多的也就是第三范式，我認(rèn)為使用到第三范式也就足夠了，性能好而且方便管理數(shù)據(jù) 41規(guī)范化實(shí)例假設(shè)某建筑公司要設(shè)計(jì)一個(gè)數(shù)據(jù)庫(kù)。公司的業(yè)務(wù)規(guī)則概括說(shuō)明如下：公司承擔(dān)多個(gè)工程項(xiàng)目，每一項(xiàng)工程有：工程號(hào)、工程名稱(chēng)、施工人員等公司有多名職工，每一名職工有：職工號(hào)、姓名、性別、職務(wù)（工程師、技術(shù)員）等公司按照工時(shí)和小時(shí)工資率支付工資，小時(shí)工資率由職工的職務(wù)決定（例如，技術(shù)員的小時(shí)工資率與工程師不同）公司定期制定一個(gè)工資報(bào)表，如圖-1所示42規(guī)范化實(shí)例 -2圖-1 某公司的工資表43規(guī)范化實(shí)例 -3工程號(hào)工程名稱(chēng)職工號(hào)姓名職務(wù)小時(shí)工資率工

16、時(shí)A1花園大廈1001齊光明工程師6513A1花園大廈1002李思岐技術(shù)員6016A1花園大廈1001齊光明工程師6513A1花園大廈1003鞠明亮工人5517A3臨江飯店1002李思岐技術(shù)員6018A3臨江飯店1004葛宇洪技術(shù)員6014圖-2 某公司的項(xiàng)目工時(shí)表44規(guī)范化實(shí)例 -41.表中包含大量的冗余，可能會(huì)導(dǎo)致數(shù)據(jù)異常：更新異常 例如，修改職工號(hào)=1001的職務(wù)，則必須修改所有職工號(hào)=1001的行添加異常 若要增加一個(gè)新的職工時(shí)，首先必須給這名職工分配一個(gè)工程。或者為了添加一名新職工的數(shù)據(jù)，先給這名職工分配一個(gè)虛擬的工程。（因?yàn)橹麝P(guān)鍵字不能為空）刪除異常 例如，1001號(hào)職工要辭職，則必須刪除所有職工號(hào)1001的數(shù)據(jù)行。這樣的刪除操作，很可能丟失了其它有用的數(shù)據(jù)45規(guī)范化實(shí)例 -52采用這種方法設(shè)計(jì)表的結(jié)構(gòu)，雖然很容易產(chǎn)生工資報(bào)表，但是每當(dāng)一名職工分配一個(gè)工程時(shí)，都要重復(fù)輸入大量的數(shù)據(jù)。這種重復(fù)的輸入操作，很可能導(dǎo)致數(shù)據(jù)的不一致性。46一張表描述了多件事情，如圖-3所示。應(yīng)用范式規(guī)范化設(shè)計(jì)工程號(hào)工程名稱(chēng)職工號(hào)姓名職務(wù)小時(shí)工資率工時(shí)圖-3