《數(shù)據(jù)庫SQL ServeSQLite教程》 教案 第6章 視圖與索引

數(shù)據(jù)庫SQLServeSQLite教程課程教案課程名稱：數(shù)據(jù)庫SQLServeSQLite教程課程號(hào)：課程學(xué)分：2參考學(xué)時(shí)：32課程性質(zhì)：專業(yè)必修課適用專業(yè)：計(jì)算機(jī)類專業(yè)上課時(shí)間：授課教師：負(fù)責(zé)人：審核單位：年月日-1-一、課程基本信息課程名稱數(shù)據(jù)庫SQLServeSQLite教程課程號(hào)課程性質(zhì)專業(yè)必修課開課部門課程負(fù)責(zé)人課程團(tuán)隊(duì)授課學(xué)期學(xué)分/學(xué)時(shí)2/32授課語言漢語先修課程數(shù)據(jù)庫SQLServeSQLite教程課程簡介從理論實(shí)踐一體化角度出發(fā)，介紹了數(shù)據(jù)庫的基本概念、理論、模型和方法，同時(shí)從知識(shí)技能的邏輯順序出發(fā)，深入淺出地介紹了數(shù)據(jù)庫技術(shù)的相關(guān)內(nèi)容，使學(xué)生可以快速掌握數(shù)據(jù)庫系統(tǒng)的原理，應(yīng)用有關(guān)工具學(xué)習(xí)SQL，并在C#、Python等相關(guān)課程中應(yīng)用，在實(shí)際應(yīng)用中解決實(shí)際問題。授課班級(jí)

二、課程授課教案第6章，共11章講課主題視圖與索引學(xué)時(shí)2學(xué)時(shí)教學(xué)目標(biāo)從理論實(shí)踐一體化角度出發(fā)，介紹了數(shù)據(jù)庫的基本概念、理論、模型和方法，同時(shí)從知識(shí)技能的邏輯順序出發(fā)，深入淺出地介紹了數(shù)據(jù)庫技術(shù)的相關(guān)內(nèi)容，使學(xué)生可以快速掌握數(shù)據(jù)庫系統(tǒng)的原理，應(yīng)用有關(guān)工具學(xué)習(xí)SQL，并在C#、Python等相關(guān)課程中應(yīng)用，在實(shí)際應(yīng)用中解決實(shí)際問題。教學(xué)重點(diǎn)、難點(diǎn)視圖是關(guān)系數(shù)據(jù)庫系統(tǒng)為用戶提供的從多角度觀察分析數(shù)據(jù)庫中數(shù)據(jù)的一種機(jī)制，通過視圖可以看到自己感興趣的信息。而索引的建立為用戶快速查找所需信息提供了條件，合理使用索引能極大地提高數(shù)據(jù)檢索的速度，提高數(shù)據(jù)庫的性能。教學(xué)設(shè)計(jì)授課提綱及重難點(diǎn)分析教學(xué)方法及課程思政設(shè)計(jì)教學(xué)時(shí)間6.1視圖6.1.1視圖的概念和作用1.視圖的概念視圖(View)是保存在數(shù)據(jù)庫中從一個(gè)或多個(gè)表或視圖中導(dǎo)出由查詢語句定義生成的一個(gè)虛擬表。與真正的數(shù)據(jù)表類似，視圖也是由一組命名的列和數(shù)據(jù)行構(gòu)成的，其結(jié)構(gòu)和數(shù)據(jù)是建立在對(duì)表或視圖查詢的基礎(chǔ)上。數(shù)據(jù)庫只存儲(chǔ)視圖的定義，而不存儲(chǔ)對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍然存儲(chǔ)在導(dǎo)出該視圖的數(shù)據(jù)表中，當(dāng)基本表中的數(shù)據(jù)發(fā)生變化時(shí)，從視圖中查詢出來的數(shù)據(jù)也隨之改變。2.視圖的使用及注意事項(xiàng)通過視圖來訪問數(shù)據(jù)，而不必直接去訪問對(duì)應(yīng)的數(shù)據(jù)表，實(shí)現(xiàn)數(shù)據(jù)從分散到集中，簡化處理，便于共享。對(duì)視圖的一般操作與對(duì)表的操作一樣，可以對(duì)其進(jìn)行查詢、修改、刪除和更新。當(dāng)對(duì)視圖中的數(shù)據(jù)表進(jìn)行修改時(shí)，其對(duì)應(yīng)數(shù)據(jù)表的數(shù)據(jù)也會(huì)同步發(fā)生變化，同時(shí)這種變化也自動(dòng)地反映到視圖中。在創(chuàng)建視圖時(shí)，應(yīng)注意以下幾點(diǎn)：(1)只能在當(dāng)前數(shù)據(jù)庫中才能創(chuàng)建視圖。在定義視圖時(shí)，select子句中不能包含compute或computeby子句。在select子句中使用了top關(guān)鍵字，則可以使用orderby子句。(2)視圖的命名必須遵守標(biāo)識(shí)符命名規(guī)則，不能與表同名，且對(duì)每一個(gè)用戶視圖名必須是唯一的。不能把規(guī)則、默認(rèn)值或觸發(fā)器與視圖相關(guān)聯(lián)。3.視圖的作用視圖的作用有以下幾點(diǎn)：(1)視圖隱藏了底層的表結(jié)構(gòu)，簡化了數(shù)據(jù)訪問操作，客戶端不再需要知道底層表的結(jié)構(gòu)及其之間的關(guān)系。(2)視圖提供了一個(gè)統(tǒng)一訪問數(shù)據(jù)的接口，即可以允許用戶通過視圖訪問數(shù)據(jù)的安全機(jī)制，而不授予用戶直接訪問底層表的權(quán)限。(3)增強(qiáng)了安全性，使用戶只能看到視圖所顯示的數(shù)據(jù)。6.1.2視圖的創(chuàng)建、修改及刪除1.創(chuàng)建視圖1)使用菜單方式創(chuàng)建視圖2)使用代碼方式創(chuàng)建視圖3)使用別名創(chuàng)建視圖4)使用withcheckoption子句創(chuàng)建視圖2.修改、刪除視圖1)視圖的修改(1)使用菜單方式修改視圖。在“對(duì)象資源管理器”中展開“數(shù)據(jù)庫”節(jié)點(diǎn)，展開相應(yīng)的數(shù)據(jù)庫和視圖節(jié)點(diǎn)，右擊視圖，選擇“設(shè)計(jì)”菜單，進(jìn)入視圖設(shè)計(jì)器進(jìn)行必要的修改，修改完成單擊“保存”按鈕即可。(2)使用代碼方式修改視圖。使用alterview語句可以修改視圖。修改視圖與刪除并重新創(chuàng)建視圖是不同的，修改視圖會(huì)保持視圖的權(quán)限不變，但刪除并重新創(chuàng)建視圖則意味著視圖的重新定義。2)視圖的刪除視圖并不是數(shù)據(jù)庫中必需的數(shù)據(jù)庫對(duì)象，對(duì)于不需要的視圖可以使用dropview語句將其刪除，刪除視圖后，其所對(duì)應(yīng)的數(shù)據(jù)不會(huì)受到影響。如果有其他數(shù)據(jù)庫對(duì)象使用了該視圖，仍可以刪除該視圖，只是再使用那些數(shù)據(jù)庫對(duì)象時(shí)，將會(huì)發(fā)生錯(cuò)誤。35min6.2索引6.2.1SQLServer的數(shù)據(jù)存儲(chǔ)SQLServer有兩種數(shù)據(jù)存儲(chǔ)文件，分別是數(shù)據(jù)文件和日志文件，其中數(shù)據(jù)文件是以8KB(8192Byte)的頁面(Page)作為存儲(chǔ)單元，日志文件是以日志記錄作為存儲(chǔ)單元。以數(shù)據(jù)文件為例，從頁面類型、數(shù)據(jù)頁面結(jié)構(gòu)、數(shù)據(jù)頁緩存、盤區(qū)、數(shù)據(jù)訪問等方面入手，討論其存儲(chǔ)格式與方式。1.SQlServer定義的頁面類型SQlServer定義的頁面類型有8種，如表6-1所示。用戶的數(shù)據(jù)一般存儲(chǔ)在數(shù)據(jù)頁面中，在一個(gè)數(shù)據(jù)頁面中，要知道數(shù)據(jù)如何存放，根據(jù)什么來定位頁面與頁面上的數(shù)據(jù)，就要先了解數(shù)據(jù)頁面的結(jié)構(gòu)。2.數(shù)據(jù)頁面結(jié)構(gòu)在數(shù)據(jù)頁面上，數(shù)據(jù)行緊接著頁首按順序放置，在頁尾有一個(gè)行偏移表。在行偏移表中，頁上的每一行都有一個(gè)條目，每個(gè)條目記錄那一行的第一個(gè)字節(jié)與頁首的距離。頁偏移表中的條目序列與頁中行的序列相反。3.數(shù)據(jù)頁緩存SQLServer數(shù)據(jù)庫的主要用途是存儲(chǔ)和檢索數(shù)據(jù)，因此密集型磁盤I/O是數(shù)據(jù)庫引擎的一大特點(diǎn)。由于完成磁盤I/O需要消耗許多資源且耗時(shí)較長，因此SQLServer側(cè)重于提高I/O效率。緩沖區(qū)管理是實(shí)現(xiàn)高效I/O的關(guān)鍵環(huán)節(jié)，一個(gè)緩沖區(qū)就是一個(gè)8KB大小的內(nèi)存頁，其大小與一個(gè)數(shù)據(jù)頁或索引頁相當(dāng)，因此緩沖區(qū)高速緩存被劃分為多個(gè)8KB頁。緩沖區(qū)管理器負(fù)責(zé)將數(shù)據(jù)頁或索引頁從數(shù)據(jù)庫磁盤文件讀入緩沖區(qū)高速緩存中，并將修改后的頁寫回磁盤。4.盤區(qū)SQLServer默認(rèn)的存儲(chǔ)分配單位是盤區(qū)。為了避免頻繁地讀寫I/O，在表或其他對(duì)象分配存儲(chǔ)空間時(shí)，不是直接分配一個(gè)8KB的頁面，而是以一個(gè)盤區(qū)(Extent)為存儲(chǔ)分配單位，一個(gè)盤區(qū)為8個(gè)頁面(8×8KB=64KB)。5.數(shù)據(jù)訪問系統(tǒng)訪問表中的數(shù)據(jù)時(shí)，可以采用表掃描和索引查找兩種方式。如果對(duì)數(shù)據(jù)頁上的數(shù)據(jù)進(jìn)行訪問，一維升序或降序數(shù)據(jù)序列可以采用兩分檢索的方法迅速找到需要插入或刪除元素的位置。但當(dāng)采用順序存儲(chǔ)的方式時(shí)，插入一個(gè)元素，需要將其下面的數(shù)據(jù)進(jìn)行后移，反之刪除一個(gè)元素，需要將其下面的數(shù)據(jù)進(jìn)行前移。為避免大量的數(shù)據(jù)移動(dòng)，提高插入或刪除的工作效率，研究者提出了多種解決方案，其中B樹是較好的一種方案。6.2.2索引及其創(chuàng)建與刪除1.索引的概念索引(Index)是SQLServer在列上建立的一種數(shù)據(jù)庫對(duì)象。它保存著表中排序的索引列，并記錄索引列在表中的物理存儲(chǔ)位置，從而實(shí)現(xiàn)表中數(shù)據(jù)的邏輯排序。一張表的存儲(chǔ)是由數(shù)據(jù)頁面和索引頁面組成的。索引就存放在索引頁面上，當(dāng)進(jìn)行數(shù)據(jù)檢索時(shí)系統(tǒng)先搜索索引頁面，從中找到所需數(shù)據(jù)的指針，再通過指針從數(shù)據(jù)頁面中讀取數(shù)據(jù)。利用索引可以加快數(shù)據(jù)檢索的速度，提升系統(tǒng)的性能。2.索引的類型對(duì)于索引類型的劃分有多種，通常根據(jù)索引對(duì)表中記錄順序的影響分類，可以分為聚集索引和非聚集索引。此外，還有唯一索引與非唯一索引、單列索引與多列索引等分類。下面主要介紹聚集索引和非聚集索引。1)聚集索引聚集索引(ClusteredIndex)是指表中的數(shù)據(jù)記錄實(shí)際存儲(chǔ)的次序與索引中相對(duì)應(yīng)的鍵值的實(shí)際存儲(chǔ)次序完全相同的索引。也就是說，聚集索引將對(duì)表中的物理數(shù)據(jù)頁中的數(shù)據(jù)按列進(jìn)行排序，然后再存儲(chǔ)到磁盤上。聚集索引與數(shù)據(jù)是融為一體的，因此聚集索引查找數(shù)據(jù)最快。當(dāng)然，一個(gè)表只能有一個(gè)聚集索引。比如設(shè)置主鍵，系統(tǒng)自動(dòng)創(chuàng)建一個(gè)聚集索引。2)非聚集索引非聚集索引(NonclusteredIndex)是指表中的數(shù)據(jù)記錄實(shí)際存儲(chǔ)的次序與索引中相對(duì)應(yīng)的鍵值的實(shí)際存儲(chǔ)次序不相同的索引。也就是說，表中的數(shù)據(jù)不是按照索引列排序的，使用索引頁存儲(chǔ)，比聚集索引占用更多的存儲(chǔ)空間，檢索效率也較低。一個(gè)表中可以同時(shí)有聚集索引和非聚集索引，而且一個(gè)表可以有多個(gè)非聚集索引，但是一個(gè)表中最多不超過250個(gè)索引。比如學(xué)生信息表中的身份證號(hào)碼、手機(jī)號(hào)碼、電子郵箱等列可以創(chuàng)建非聚集索引。非聚集索引將行定位器按關(guān)鍵字的值用一定的方法排序，這個(gè)