SQLServer基礎(chǔ)教程.ppt

2020年3月13日 第1頁 本章概述 業(yè)務(wù)數(shù)據(jù)存儲(chǔ)在什么地方 數(shù)據(jù)庫對(duì)象存儲(chǔ)在哪里 對(duì)于業(yè)務(wù)數(shù)據(jù)而言 什么樣的存儲(chǔ)方式是合理的 如果數(shù)據(jù)量劇增之后 數(shù)據(jù)庫能否適應(yīng)這些變化 回答諸如此類的問題 首先需要明白到底什么是數(shù)據(jù)庫 數(shù)據(jù)庫是數(shù)據(jù)庫管理系統(tǒng)的核心 是存放數(shù)據(jù)庫對(duì)象的容器 是使用數(shù)據(jù)庫系統(tǒng)時(shí)首先面對(duì)的對(duì)象 管理數(shù)據(jù)庫就是設(shè)計(jì)數(shù)據(jù)庫 定義數(shù)據(jù)庫和維護(hù)數(shù)據(jù)庫的過程 數(shù)據(jù)庫的效率和性能在很大程度上取決于數(shù)據(jù)庫的設(shè)計(jì)和優(yōu)化 本章將對(duì)MicrosoftSQLServer系統(tǒng)的數(shù)據(jù)庫管理進(jìn)行全面的研究和分析 2020年3月13日 第2頁 本章要點(diǎn) 數(shù)據(jù)庫的管理問題數(shù)據(jù)庫文件和文件組的基本特征數(shù)據(jù)庫的物理存儲(chǔ)方式和大小估算方法使用CREATEDATABASE語句定義數(shù)據(jù)庫數(shù)據(jù)庫選項(xiàng)的作用和設(shè)置方式擴(kuò)大數(shù)據(jù)庫的原因和方法收縮數(shù)據(jù)庫的原因和方法文件組的管理方式數(shù)據(jù)庫快照的作用和特點(diǎn)優(yōu)化數(shù)據(jù)庫設(shè)計(jì)的方法 2020年3月13日 第3頁 本章內(nèi)容 4 1概述4 2數(shù)據(jù)庫文件和文件組的基本特征4 3定義數(shù)據(jù)庫4 4修改數(shù)據(jù)庫4 5管理數(shù)據(jù)庫快照4 6其他相關(guān)操作4 7優(yōu)化數(shù)據(jù)庫4 8本章小結(jié) 4 1概述 為了有效地實(shí)現(xiàn)數(shù)據(jù)庫的管理工作 必須至少解決8個(gè)方面的問題 包括數(shù)據(jù)庫文件的存儲(chǔ)問題 數(shù)據(jù)庫的大小問題 確定數(shù)據(jù)庫運(yùn)行時(shí)的行為特征 數(shù)據(jù)庫的更改問題 數(shù)據(jù)庫的擴(kuò)大問題 數(shù)據(jù)庫的收縮問題 如何兼顧數(shù)據(jù)庫的事務(wù)處理效率和決策支持效率問題和數(shù)據(jù)庫的性能優(yōu)化問題等 2020年3月13日 第4頁 4 2數(shù)據(jù)庫文件和文件組的基本特征 本節(jié)主要講述數(shù)據(jù)庫文件的類型 事務(wù)的概念 文件組的作用 估算數(shù)據(jù)庫文件大小的方法等內(nèi)容 2020年3月13日 第5頁 數(shù)據(jù)庫文件 在MicrosoftSQLServer2008系統(tǒng)中 一個(gè)數(shù)據(jù)庫至少有一個(gè)數(shù)據(jù)文件和一個(gè)事務(wù)日志文件 當(dāng)然 一個(gè)數(shù)據(jù)庫也可以有多個(gè)數(shù)據(jù)文件和多個(gè)日志文件 數(shù)據(jù)文件用于存放數(shù)據(jù)庫的數(shù)據(jù)和各種對(duì)象 而事務(wù)日志文件用于存放事務(wù)日志 一個(gè)數(shù)據(jù)庫最多可以擁有32767個(gè)數(shù)據(jù)文件和32767個(gè)日志文件 2020年3月13日 第6頁 文件組 文件組就是文件的邏輯集合 為了方便數(shù)據(jù)的管理和分配 文件組可以把一些指定的文件組合在一起 使用文件和文件組時(shí) 應(yīng)該考慮下列因素 1 一個(gè)文件或者文件組只能用于一個(gè)數(shù)據(jù)庫 不能用于多個(gè)數(shù)據(jù)庫 2 一個(gè)文件只能是某一個(gè)文件組的成員 不能是多個(gè)文件組的成員 3 數(shù)據(jù)庫的數(shù)據(jù)信息和日志信息不能放在同一個(gè)文件或文件組中 數(shù)據(jù)文件和日志文件總是分開的 4 日志文件永遠(yuǎn)也不能是任何文件組的一部分 2020年3月13日 第7頁 估算大小 通過理解數(shù)據(jù)庫的空間管理 可以估算數(shù)據(jù)庫的設(shè)計(jì)尺寸 數(shù)據(jù)庫的大小等于數(shù)據(jù)庫中的表大小 索引大小以及其他占據(jù)物理空間的數(shù)據(jù)庫對(duì)象大小之和 假設(shè)某個(gè)數(shù)據(jù)庫中只有一個(gè)表 該表的數(shù)據(jù)行字節(jié)是800B 這時(shí) 一個(gè)數(shù)據(jù)頁上最多只能存放10行數(shù)據(jù) 如果該表大約有100萬行的數(shù)據(jù) 那么該表將占用10萬個(gè)數(shù)據(jù)頁的空間 因此 該數(shù)據(jù)庫的大小估計(jì)為 100000 8KB 800000KB 781 25MB 根據(jù)數(shù)據(jù)庫大小的估計(jì)值 再考慮其他因素 就可以得到數(shù)據(jù)庫的設(shè)計(jì)值 2020年3月13日 第8頁 4 3定義數(shù)據(jù)庫 定義數(shù)據(jù)庫就是創(chuàng)建數(shù)據(jù)庫和設(shè)置數(shù)據(jù)庫選項(xiàng) 本節(jié)從3個(gè)方面講述定義數(shù)據(jù)庫 創(chuàng)建數(shù)據(jù)庫 設(shè)置數(shù)據(jù)庫選項(xiàng)和查看數(shù)據(jù)庫信息 2020年3月13日 第9頁 創(chuàng)建數(shù)據(jù)庫 創(chuàng)建數(shù)據(jù)庫就是確定數(shù)據(jù)庫名稱 文件名稱 數(shù)據(jù)文件大小 數(shù)據(jù)庫的字符集 是否自動(dòng)增長(zhǎng)以及如何自動(dòng)增長(zhǎng)等信息的過程 在一個(gè)MicrosoftSQLServer實(shí)例中 最多可以創(chuàng)建32767個(gè)數(shù)據(jù)庫 數(shù)據(jù)庫的名稱必須滿足系統(tǒng)的標(biāo)識(shí)符規(guī)則 在命名數(shù)據(jù)庫時(shí) 一定要保證數(shù)據(jù)庫名稱簡(jiǎn)短并有一定的含義 具有CREATEDATABASE CREATEANYDATABASE或ALTERANYDATABASE權(quán)限的用戶才可以執(zhí)行創(chuàng)建數(shù)據(jù)庫的操作 2020年3月13日 第10頁 創(chuàng)建一個(gè)最簡(jiǎn)單的數(shù)據(jù)庫 2020年3月13日 第11頁 使用CREATEDATABASE語句創(chuàng)建數(shù)據(jù)庫 2020年3月13日 第12頁 創(chuàng)建有多個(gè)文件和文件組的數(shù)據(jù)庫 2020年3月13日 第13頁 數(shù)據(jù)庫的狀態(tài)和選項(xiàng) 為了理解數(shù)據(jù)庫的運(yùn)行特征 需要了解數(shù)據(jù)庫的狀態(tài)和選項(xiàng) 下面分別介紹數(shù)據(jù)庫的狀態(tài)和選項(xiàng) 數(shù)據(jù)庫總是處在某個(gè)特定的狀態(tài)中 例如 ONLINE狀態(tài)表示數(shù)據(jù)庫處于正常的在線狀態(tài) 可以對(duì)數(shù)據(jù)庫執(zhí)行正常的操作 數(shù)據(jù)庫的狀態(tài)清單和特征描述如表4 1所示 2020年3月13日 第14頁 數(shù)據(jù)庫選項(xiàng) 設(shè)置數(shù)據(jù)庫選項(xiàng)是定義數(shù)據(jù)庫狀態(tài)或特征的方式 例如可以設(shè)置數(shù)據(jù)庫的狀態(tài)為EMERGENCY 每一個(gè)數(shù)據(jù)庫都有許多選項(xiàng) 可以使用ALTERDATABASE語句中的SET子句來設(shè)置這些數(shù)據(jù)庫選項(xiàng) 需要注意的是 使用MicrosoftSQLServerManagementStudio工具只能設(shè)置其中大多數(shù)的選項(xiàng) 下面 首先介紹MicrosoftSQLServer2008系統(tǒng)提供的數(shù)據(jù)庫選項(xiàng) 然后講述如何設(shè)置這些選項(xiàng) 在MicrosoftSQLServer2008系統(tǒng)中 共有大約30多個(gè)數(shù)據(jù)庫選項(xiàng) 這些數(shù)據(jù)庫選項(xiàng)的清單和功能描述如表4 2所示 2020年3月13日 第15頁 設(shè)置數(shù)據(jù)庫選項(xiàng) 2020年3月13日 第16頁 查看數(shù)據(jù)庫信息 在MicrosoftSQLServer2008系統(tǒng)中 可以使用一些目錄視圖 函數(shù) 存儲(chǔ)過程查看有關(guān)數(shù)據(jù)庫的基本信息 sys databases數(shù)據(jù)庫和文件目錄視圖可以查看有關(guān)數(shù)據(jù)庫的基本信息 sys database files可以查看有關(guān)數(shù)據(jù)庫文件的信息 sys filegroups可以查看有關(guān)數(shù)據(jù)庫文件組的信息 sys master files可以查看數(shù)據(jù)庫文件的基本信息和狀態(tài)信息 DATABASEPROPERTYEX函數(shù)可以查看指定數(shù)據(jù)庫的指定選項(xiàng)的信息 一次只能返回一個(gè)選項(xiàng)的設(shè)置 2020年3月13日 第17頁 查看數(shù)據(jù)庫選項(xiàng)的設(shè)置 2020年3月13日 第18頁 查看數(shù)據(jù)庫空間使用狀況信息 2020年3月13日 第19頁 使用sp helpdb存儲(chǔ)過程查看數(shù)據(jù)庫 2020年3月13日 第20頁 數(shù)據(jù)庫屬性 AdventureWorks 對(duì)話框 2020年3月13日 第21頁 4 4修改數(shù)據(jù)庫 數(shù)據(jù)庫創(chuàng)建之后 可以根據(jù)需要使用ALTERDATABASE語句對(duì)數(shù)據(jù)庫進(jìn)行修改 除了前面講過的設(shè)置數(shù)據(jù)庫選項(xiàng)之外 修改操作還包括更改數(shù)據(jù)庫名稱 擴(kuò)大數(shù)據(jù)庫 收縮數(shù)據(jù)庫 修改數(shù)據(jù)庫文件 管理數(shù)據(jù)庫文件組以及修改字符排列規(guī)則等 下面將詳細(xì)討論這些內(nèi)容 2020年3月13日 第22頁 更改數(shù)據(jù)庫名稱 數(shù)據(jù)庫創(chuàng)建之后 一般情況下不要更改數(shù)據(jù)庫的名稱 因?yàn)樵S多應(yīng)用程序都可能使用了該數(shù)據(jù)庫的名稱 數(shù)據(jù)庫名稱更改之后 需要修改相應(yīng)的應(yīng)用程序 如果確實(shí)需要更改數(shù)據(jù)庫名稱 可以使用ALTERDATABASE語句做到 使用ALTERDATABASE語句更改數(shù)據(jù)庫名稱的語法形式如下 ALTERDATABASEdatabase nameMODIFYNAME new database name 2020年3月13日 第23頁 更改數(shù)據(jù)庫名稱 2020年3月13日 第24頁 擴(kuò)大數(shù)據(jù)庫 在MicrosoftSQLServer系統(tǒng)中 如果數(shù)據(jù)庫的數(shù)據(jù)量不斷膨脹 可以根據(jù)需要擴(kuò)大數(shù)據(jù)庫的尺寸 有3種擴(kuò)大數(shù)據(jù)庫的方式 第一種方式是設(shè)置數(shù)據(jù)庫為自動(dòng)增長(zhǎng)方式 可以在創(chuàng)建數(shù)據(jù)庫時(shí)設(shè)置 第二種方式是直接修改數(shù)據(jù)庫的數(shù)據(jù)文件或日志文件的大小 第三種方式是在數(shù)據(jù)庫中增加新的次要數(shù)據(jù)文件或日志文件 2020年3月13日 第25頁 通過增加新的數(shù)據(jù)文件來擴(kuò)大數(shù)據(jù)庫 2020年3月13日 第26頁 通過擴(kuò)大數(shù)據(jù)文件的大小來擴(kuò)大數(shù)據(jù)庫 2020年3月13日 第27頁 收縮數(shù)據(jù)庫 如果數(shù)據(jù)庫的設(shè)計(jì)尺寸過大了 或者刪除了數(shù)據(jù)庫中的大量數(shù)據(jù) 這時(shí)數(shù)據(jù)庫會(huì)白白耗費(fèi)大量的磁盤資源 根據(jù)用戶的實(shí)際需要 可以收縮數(shù)據(jù)庫的大小 在MicrosoftSQLServer系統(tǒng)中 有3種收縮數(shù)據(jù)庫的方式 第一種方式是設(shè)置數(shù)據(jù)庫為自動(dòng)收縮 可以通過設(shè)置AUTO SHRINK數(shù)據(jù)庫選項(xiàng)實(shí)現(xiàn) 第二種方式是收縮整個(gè)數(shù)據(jù)庫的大小 可以通過使用DBCCSHRINKDATABASE命令完成 第三種方式是收縮指定的數(shù)據(jù)文件 可以使用DBCCSHRIKNFILE命令實(shí)現(xiàn) 除了這些命令方式之外 也可以使用SQLServerManagementStudio工具來收縮數(shù)據(jù)庫 2020年3月13日 第28頁 不能使用ALTERDATABASE語句直接收縮數(shù)據(jù)庫文件 2020年3月13日 第29頁 使用AUTO SHRINK數(shù)據(jù)庫選項(xiàng)設(shè)置 在MicrosoftSQLServer系統(tǒng)中 數(shù)據(jù)庫引擎會(huì)定期檢查每一個(gè)數(shù)據(jù)庫的空間使用情況 如果某個(gè)數(shù)據(jù)庫的AUTO SHRINK選項(xiàng)設(shè)置為ON 則數(shù)據(jù)庫引擎將自動(dòng)收縮數(shù)據(jù)庫中文件的大小 如果該選項(xiàng)的設(shè)置為OFF 則不自動(dòng)收縮數(shù)據(jù)庫的大小 該選項(xiàng)的默認(rèn)值是OFF 在ALTERDATABASE語句中 設(shè)置AUTO SHRINK選項(xiàng)的語法形式如下 ALTERDATABASEdatabase nameSETAUTO SHRINKON 2020年3月13日 第30頁 使用DBCCSHRINKDATABASE命令 DBCCSHRINKDATABASE命令是一種比自動(dòng)收縮數(shù)據(jù)庫更加靈活收縮數(shù)據(jù)庫的方式 可以對(duì)整個(gè)數(shù)據(jù)庫進(jìn)行收縮 DBCCSHRINKDATABASE命令的基本語法形式如下 DBCCSHRINKDATABASE database name target percent 2020年3月13日 第31頁 使用DBCCSHRINKDFILE命令 DBCCSHRINKDFILE命令可以收縮指定的數(shù)據(jù)庫文件 并且可以將文件收縮至小于其初始創(chuàng)建的大小 重新設(shè)置當(dāng)前的大小為其初始創(chuàng)建的大小 這是該命令與自動(dòng)收縮 DBCCSHRINKDATABASE命令不同的地方 在執(zhí)行收縮數(shù)據(jù)庫操作時(shí) DBCCSHRINKDFILE命令的功能最強(qiáng)大 DBCCSHRINKDFILE命令的基本語法形式如下 DBCCSHRINKFILE file name target size 2020年3月13日 第32頁 使用SQLServerManagementStudio 2020年3月13日 第33頁 修改數(shù)據(jù)庫文件 用戶可以根據(jù)需要使用ALTERDATABASE語句修改數(shù)據(jù)庫中指定的文件 這些修改操作包括增加數(shù)據(jù)文件 在指定的文件組中增加指定文件 增加日志文件 刪除指定的文件以及修改指定的文件等 增加數(shù)據(jù)文件 修改指定的文件等操作已經(jīng)講過了 下面通過一些示例講述有關(guān)數(shù)據(jù)庫文件的其他操作 如果要在指定的文件組中增加文件 則可以使用TOFILEGROUP子句 例如 如果LGCom數(shù)據(jù)庫包含了兩個(gè)數(shù)據(jù)文件 即LGF1和LGF2 現(xiàn)在要新增加一個(gè)LGF3文件并且將其放在FG1文件組 該文件組必須已經(jīng)存在 中 那么可以使用如圖4 14所示的命令 2020年3月13日 第34頁 在指定的文件組中增加文件 2020年3月13日 第35頁 管理文件組 文件組是數(shù)據(jù)庫數(shù)據(jù)文件的邏輯組合 它可以對(duì)數(shù)據(jù)文件進(jìn)行管理和分配 以便提高數(shù)據(jù)庫文件的并發(fā)使用效率 Transact SQL語言沒有提供獨(dú)立的管理文件組的命令 只能通過ALTERDATABASE語句提供管理文件組的命令 這些管理文件組的命令包括新建文件組 設(shè)置默認(rèn)的文件組 設(shè)置文件組的屬性 修改文件組以及刪除文件組等 下面詳細(xì)介紹管理文件組的操作 2020年3月13日 第36頁 新建文件組的同時(shí)不能設(shè)置為默認(rèn)文件組 2020年3月13日 第37頁 將文件組設(shè)置為只讀狀態(tài) 2020年3月13日 第38頁 4 5管理數(shù)據(jù)庫快照 相片是被照對(duì)象在照像時(shí)刻的靜態(tài)圖像表示 數(shù)據(jù)庫快照與此類似 數(shù)據(jù)庫快照提供了源數(shù)據(jù)庫在創(chuàng)建快照時(shí)刻的只讀 靜態(tài)視圖 可以有效地支持報(bào)表數(shù)據(jù)匯總和數(shù)據(jù)分析等只讀操作 如果源數(shù)據(jù)庫中包含了未提交事務(wù) 那么這些事務(wù)不包含在數(shù)據(jù)庫快照中 需要說明的是 數(shù)據(jù)庫快照必須與源數(shù)據(jù)庫在同一個(gè)服務(wù)器實(shí)例上 數(shù)據(jù)庫快照是在數(shù)據(jù)頁級(jí)上進(jìn)行的 2020年3月13日 第39頁 限制 必須與源數(shù)據(jù)庫在相同的服務(wù)器實(shí)例上創(chuàng)建數(shù)據(jù)庫快照 數(shù)據(jù)庫快照捕捉開始創(chuàng)建快照的時(shí)刻點(diǎn) 不包括所有未提交的事務(wù) 數(shù)據(jù)庫快照是只讀的 不能在數(shù)據(jù)庫快照中執(zhí)行修改操作 不能修改數(shù)據(jù)庫快照的文件 不能創(chuàng)建基于model master tempdb等系統(tǒng)數(shù)據(jù)庫的快照 不能對(duì)數(shù)據(jù)庫快照?qǐng)?zhí)行備份或還原操作 不能附加或分離數(shù)據(jù)庫快照 數(shù)據(jù)庫快照不支持全文索引 因此源數(shù)據(jù)庫中的全文目錄不能傳輸過來 數(shù)據(jù)庫快照繼承快照創(chuàng)建時(shí)源數(shù)據(jù)庫的安全約束 但是由于快照是只讀的 源數(shù)據(jù)庫中對(duì)權(quán)限的修改不能反映到快照中 數(shù)據(jù)庫快照始終反映創(chuàng)建該快照時(shí)的文件組狀態(tài) 2020年3月13日 第40頁 創(chuàng)建數(shù)據(jù)庫快照 2020年3月13日 第41頁 4 6其他相關(guān)操作 除了前面講述的數(shù)據(jù)庫操作之外 數(shù)據(jù)庫管理操作還包括分離數(shù)據(jù)庫 附加數(shù)據(jù)庫和刪除數(shù)據(jù)庫等 下面介紹這些操作 2020年3月13日 第42頁 分離數(shù)據(jù)庫 分離數(shù)據(jù)庫是指將數(shù)據(jù)庫從MicrosoftSQLServer實(shí)例中刪除 但是該數(shù)據(jù)庫的數(shù)據(jù)文件和事務(wù)日志文件依然保持不變 這樣可以將該數(shù)據(jù)庫附加到任何的MicrosoftSQLServer實(shí)例中 可以使用sp detach db存儲(chǔ)過程來執(zhí)行數(shù)據(jù)庫分離操作 當(dāng)然也可以使用SQLServerManagementStudio工具來執(zhí)行分離操作 例如 如果要分離LGCom數(shù)據(jù)庫 可以執(zhí)行如圖4 18所示的命令 2020年3月13日 第43頁 分離數(shù)據(jù)庫 2020年3月13日 第44頁 附加分離的數(shù)據(jù)庫 2020年3月13日 第45頁 刪除數(shù)據(jù)庫 如果