第6章 存儲(chǔ)技術(shù)與數(shù)據(jù)庫(kù)物理設(shè)計(jì)
6.1 物理設(shè)計(jì)是在具體的硬件環(huán)境、OS、DBMS約束下,基于邏輯設(shè)計(jì),設(shè)計(jì)具體數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)和存取方式。目的:占用空間少、訪(fǎng)問(wèn)效率高、維護(hù)代價(jià)低。主要步驟有數(shù)據(jù)庫(kù)邏輯模式調(diào)整、文件組織與存取設(shè)計(jì)、數(shù)據(jù)分布設(shè)計(jì)、安全模式設(shè)計(jì)、確定系統(tǒng)配置、物理模式評(píng)估。
6.2 索引技術(shù)(Indexing)是一種快速文件訪(fǎng)問(wèn)技術(shù),它將文件記錄在某個(gè)或某些域(或稱(chēng)為屬性)上的取值與該記錄的物理地址直接聯(lián)系起來(lái),提供了一種根據(jù)記錄域的取值快速訪(fǎng)問(wèn)文件記錄的機(jī)制。索引文件是一種利用索引技術(shù)支持快速文件訪(fǎng)問(wèn)的文件組織和存取方法。
索引加快了查詢(xún)記錄卻減慢了數(shù)據(jù)更新速度,本身還占用一定的存儲(chǔ)空間。
6.3 文件組織:如何將關(guān)系數(shù)據(jù)庫(kù)中的關(guān)系映射為操作系統(tǒng)中的數(shù)據(jù)庫(kù)文件,及管理文件。
文件結(jié)構(gòu):如何將DB文件中的邏輯記錄映射到物理文件的中磁盤(pán)塊。
文件存。横槍(duì)某種結(jié)構(gòu)的DB文件,如何查、添刪改其中的邏輯記錄
6.4 數(shù)據(jù)字典:數(shù)據(jù)庫(kù)各類(lèi)對(duì)象的描述信息、數(shù)據(jù)庫(kù)管理系統(tǒng)的控制信息。包括關(guān)系模式信息、與視圖描述有關(guān)的信息、關(guān)系的存儲(chǔ)結(jié)構(gòu)和存取方法信息、完整性約束、安全性有關(guān)的信息、數(shù)據(jù)庫(kù)運(yùn)行統(tǒng)計(jì)信息。
作用:DBA用來(lái)監(jiān)視DBMS的使用情況并協(xié)助完成管理工作;一般用戶(hù)可用于查閱部分?jǐn)?shù)據(jù)庫(kù)結(jié)構(gòu)信息;DBS運(yùn)行時(shí)各子系統(tǒng)頻繁使用以完成相應(yīng)的存儲(chǔ)和查詢(xún)處理功能。
6.5 DBMS的三種完整性控制機(jī)制:CHECK子句、斷言、觸發(fā)器
斷言語(yǔ)句:Create assertion 斷言約束名 check (…)
6.6 堆文件:數(shù)據(jù)量少且操作頻繁;批量加載數(shù)據(jù)(先選為堆文件再調(diào)整文件結(jié)構(gòu))
順序文件:查詢(xún)條件定義在查找碼上;快速的二分查找
散列文件:基于散列域值的等值匹配,特別是訪(fǎng)問(wèn)順序是隨機(jī)的。非精確查詢(xún);非散列域
B-樹(shù)和B+-樹(shù):大數(shù)據(jù)量基本表;聚焦文件:多表連接操作
6.7有序索引技術(shù)利用索引文件實(shí)現(xiàn)查找碼取值到記錄物理地址間的映射關(guān)系。索引文件由索引記錄組成,每個(gè)記錄中的索引項(xiàng)記錄了某個(gè)特定的查找碼值和具有該值的數(shù)據(jù)文件記錄的物理地址。
當(dāng)需要訪(fǎng)問(wèn)數(shù)據(jù)文件中某個(gè)數(shù)據(jù)記錄時(shí),先根據(jù)查找碼值查閱索引文件,找到對(duì)應(yīng)的索引項(xiàng),然后從索引項(xiàng)中找出數(shù)據(jù)記錄在數(shù)據(jù)文件中的物理地址.根據(jù)這個(gè)地址訪(fǎng)問(wèn)數(shù)據(jù)記錄。
6.8散列技術(shù)是一種快速文件訪(fǎng)問(wèn)技術(shù),它利用散列函數(shù)實(shí)現(xiàn)文件記錄域取值到記錄物理地址間的直接映射關(guān)系。當(dāng)需要訪(fǎng)問(wèn)數(shù)據(jù)文件中查找碼值為si的某個(gè)或某些文件記錄時(shí),將si作為散列函數(shù)h的輸入計(jì)算得出的散列函數(shù)輸出值h(si)就是文件記錄在數(shù)據(jù)文件中的物理地址。
6.9 權(quán)限:允許用戶(hù)對(duì)一給定的數(shù)據(jù)庫(kù)對(duì)象可執(zhí)行的操作(查詢(xún)、添刪改、新建、備份等)。
第12章 SQL Server2000數(shù)據(jù)庫(kù)管理系統(tǒng)
12.1 四個(gè)服務(wù):SQL Server 核心服務(wù);
SQL Server Agent:代理服務(wù),代理定期進(jìn)行的管理工作;
DTC:Distributed Transaction Coordinator,分布式事務(wù)協(xié)調(diào)器,同一事務(wù)訪(fǎng)問(wèn)多個(gè)服務(wù)器
Microsoft Search:全文檢索服務(wù)
12.2 四個(gè)版本:企業(yè)版(全部功能、大型數(shù)據(jù)庫(kù))、標(biāo)準(zhǔn)版(小部門(mén))、開(kāi)發(fā)版(同企業(yè)版,作開(kāi)發(fā)測(cè)試系統(tǒng)用,不作生產(chǎn)服務(wù)用)個(gè)人版(移動(dòng)環(huán)境、本地?cái)?shù)據(jù))
12.3 服務(wù)帳戶(hù):使用本地系統(tǒng)帳戶(hù):自動(dòng)取當(dāng)前登錄到Windows的用戶(hù),沒(méi)有Windows的網(wǎng)絡(luò)訪(fǎng)問(wèn)權(quán)限,適用于非網(wǎng)絡(luò)服務(wù)器操作系統(tǒng)(如XP);使用域用戶(hù)帳戶(hù):使用Windows身份驗(yàn)證設(shè)置連接到SQL Server,用戶(hù)必是Windows系統(tǒng)管理員,適用于網(wǎng)絡(luò)服務(wù)器OS
12.4網(wǎng)絡(luò)庫(kù):在SQL S客戶(hù)端和服務(wù)器間傳遞網(wǎng)絡(luò)數(shù)據(jù)包。服務(wù)器可一次監(jiān)聽(tīng)多個(gè)網(wǎng)絡(luò)庫(kù)
12.5 SQLServer的兩大類(lèi)數(shù)據(jù)庫(kù):系統(tǒng)數(shù)據(jù)庫(kù):DBMS自動(dòng)創(chuàng)建及維護(hù),存放維護(hù)系統(tǒng)正常運(yùn)行的信息,master(系統(tǒng)級(jí)信息)、msdb(支持自動(dòng)執(zhí)行任務(wù))、model(所有用戶(hù)數(shù)據(jù)庫(kù)的公共信息)、tempdb(臨時(shí)數(shù)據(jù)庫(kù)),示例Pubs、Northwind;用戶(hù)數(shù)據(jù)庫(kù)
12.6 估算存儲(chǔ)空間:SQLServer數(shù)據(jù)存儲(chǔ)單位為頁(yè)(Page),一個(gè)數(shù)據(jù)頁(yè)是一塊8KB的連續(xù)磁盤(pán)空間,行不能跨頁(yè)存儲(chǔ),一行數(shù)據(jù)的大小不能超過(guò)一頁(yè)的大小。一個(gè)表10000行數(shù)據(jù),每行3000字節(jié),則需要(10000/2)*8KB=40MB的空間。
12.7 主數(shù)據(jù)文件:擴(kuò)展名為.mdf,包含數(shù)據(jù)庫(kù)系統(tǒng)信息并可存放用戶(hù)數(shù)據(jù)庫(kù)數(shù)據(jù),每個(gè)數(shù)據(jù)庫(kù)只有一個(gè)主數(shù)據(jù)文件。輔助數(shù)據(jù)文件:擴(kuò)展名為.ndf,數(shù)據(jù)量很大時(shí)使用,可存放在不同的磁盤(pán)驅(qū)動(dòng)器上,以便得利用多個(gè)磁盤(pán)上的存儲(chǔ)空間并提高數(shù)據(jù)存取的并發(fā)性。
12.8 每個(gè)數(shù)據(jù)文件及日志文件(.ldf)初始大小均不得小于512KB,主數(shù)據(jù)文件大小不得小于model數(shù)據(jù)庫(kù)主數(shù)據(jù)文件,日志文件最好不小于1MB
12.9 創(chuàng)建數(shù)據(jù)庫(kù):CREAT DATABASE jessymin
ON ――表示數(shù)據(jù)庫(kù)按下面參數(shù)創(chuàng)建
( NAME = jessymin, ――邏輯文件名
FILENAME = ‘…MSSQLDatajessymin_Data.mdf’, ――OS下的物理文件名
SIZE = 10,――文件初始大小,單位默認(rèn)為MB,下同
MAXSIZE =30,――文件最大大小
FILEGROWTH = 5,――文件增量,為0表示不自動(dòng)增長(zhǎng),默認(rèn)按當(dāng)前10%增長(zhǎng))
LOG ON ――表示該數(shù)據(jù)庫(kù)日志文件按下面參數(shù)創(chuàng)建
(……。.同上,只是物理文件名為jessymin.LDF)
12.10 刪除數(shù)據(jù)庫(kù):DROP DATABASE jessymin。刪除六種數(shù)據(jù)庫(kù)對(duì)象均用DROP
12.11 Transact-SQL:非過(guò)程化高級(jí)語(yǔ)言,全司變量@@,局部變量@,局部變量可以是自定義類(lèi)型但不能是text或image類(lèi)型。
12.12 Transact-SQL示例:計(jì)算1+2+3+…+100的和:
DECLARE @i int, @sum int
SET @i = 1,@sum = 0 --SET可換為SELECT
WHILE @i 《=100
BEGIN
SET @sum = @sum + @i
SET @i = @i +1
END
PRINT @sum
12.13 DTS(Data Transformation Service)數(shù)據(jù)轉(zhuǎn)換服務(wù)。注意區(qū)別DTC(分布式事務(wù)協(xié)調(diào)器)。導(dǎo)出數(shù)據(jù)時(shí)用戶(hù)必須是要連接的數(shù)據(jù)庫(kù)服務(wù)器的合法用戶(hù),且對(duì)要導(dǎo)出的表具有查詢(xún)權(quán)限
第13章 數(shù)據(jù)庫(kù)對(duì)象
13.1 存儲(chǔ)過(guò)程的:SQL語(yǔ)句和控制流語(yǔ)句的預(yù)編譯集合,應(yīng)用程序可通過(guò)調(diào)用方法來(lái)執(zhí)行
優(yōu)點(diǎn):模塊化程序設(shè)計(jì);提高性能;減少網(wǎng)絡(luò)流量;可作為安全機(jī)制使用
13.2 帶有多個(gè)參數(shù)并有默認(rèn)值及輸出參數(shù)的存儲(chǔ)過(guò)程示例:
CREATE PROCEDURE p_Example
@area varchar(20) = ‘武漢大學(xué)’,@Price money,@Sum int output
AS
SELECT/UPDATE/INSERT/DELETE……
SET @Sum = …。.
應(yīng)用程序中執(zhí)行的SQL語(yǔ)句:
Declare @res int
EXECUTE p_Example ‘武漢大學(xué)信息學(xué)部’,1000,@res output
或者 EXECUTE p_Example @Price =1000,@res output
13.3 用戶(hù)自定義函數(shù):標(biāo)量函數(shù)(返回單值,非text、Image類(lèi)型,任何允許出現(xiàn)表達(dá)式的地方)、內(nèi)嵌表值函數(shù)(返回一個(gè)表,放在查詢(xún)語(yǔ)句的From子句中)、多語(yǔ)句表值函數(shù)(返回一個(gè)可自定義的表,也放在查詢(xún)語(yǔ)句的From子句中,視圖和存儲(chǔ)過(guò)程的結(jié)合)
13.4 標(biāo)量函數(shù)救示例:根據(jù)指定的商品類(lèi)別查詢(xún)?cè)擃?lèi)的商品個(gè)數(shù)。
CREAT FUNCTION dbo.f_GoodsCount(@class varchar(10))
RETURNS int
AS
BEGIN
DECLARE @x int
SELECT @x=count(*) From T_GoodsClass a JION T_Goods b
ON a.GoodClassID = b.GoodClassID
WHERE GoodClassName = @class
RETURN @x
END
調(diào)用:SELECT dbo.f_GoodsCount(‘服裝’) 或者
SELECT GoodsName AS 商品名,dbo.f_GoodsCount(‘服裝’) AS 種類(lèi)數(shù) From …。
13.5 內(nèi)嵌表值函數(shù)的不同之處在于RETURNS 后只能是table,RETURN后面只能是單個(gè)的 SELECT語(yǔ)句,沒(méi)有相關(guān)聯(lián)的返回變量也沒(méi)有函數(shù)體。調(diào)用時(shí)放在查詢(xún)語(yǔ)句的FROM子句中。
13.6 觸發(fā)器是一種不需要由用戶(hù)來(lái)調(diào)用的存儲(chǔ)過(guò)程,當(dāng)用戶(hù)對(duì)表進(jìn)行UPDATE、INSERT或DELETE操作時(shí)自動(dòng)觸發(fā)執(zhí)行。作用:保證業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性。優(yōu)點(diǎn):用編程方法來(lái)實(shí)現(xiàn)復(fù)雜的處理邏輯和業(yè)務(wù)規(guī)則,增強(qiáng)數(shù)據(jù)完整性約束。
13.7 觸發(fā)器適用場(chǎng)合:比CHECK語(yǔ)句更復(fù)雜的數(shù)據(jù)約束(可引用其他表中的列);為保證數(shù)據(jù)庫(kù)性能而維護(hù)的非規(guī)范化數(shù)據(jù)(如增加統(tǒng)計(jì)總值的列);實(shí)現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則
13.8 AFTER/FOR:后觸發(fā)型觸發(fā)器,可在同一操作上建立多個(gè);INSTEAD OF:前觸發(fā)型,在同一操作上只能建立一個(gè)。所有的涉及對(duì)數(shù)據(jù)庫(kù)對(duì)象操作的語(yǔ)句均不允許出現(xiàn)在觸發(fā)器中。
13.9 DELETED表:存儲(chǔ)UPDATE和DELETED操作語(yǔ)句所影響行的更新前的舊數(shù)據(jù);
INSERTED表:存儲(chǔ)UPDATE和INSERT操作語(yǔ)句所影響行的更新后的新數(shù)據(jù)。
13.10 維護(hù)數(shù)據(jù)操作完整性的后觸發(fā)器示例:銷(xiāo)售量大于庫(kù)存量則撤銷(xiāo)當(dāng)前銷(xiāo)售,小于時(shí)則在插入銷(xiāo)售單據(jù)明細(xì)時(shí)同時(shí)修改庫(kù)存量。
Create Trigger OperatonCon
ON T_SaleDetail FOR INSERT
AS
IF EXISTS(Select * From inserted a Jion T_Goods b
ON a.GoodsID = b.GoodsID WHERE a.Quanity 》 b.TotalCharge)
BEGIN
ROLLBACK
PRINT ‘此商品庫(kù)存量小于此次銷(xiāo)售數(shù)量’
END
ELSE
UPDATE T_Goods SET TotalCharge = TotalCharge –
(SELECT Quanity From inserted)
13.11 維護(hù)不同列之間取值完整性的后觸發(fā)器示例:保證商品表中的單價(jià)與價(jià)格變動(dòng)表中一致
CREATE TRIGGER PriceConstraint
ON T_PriceHistory FOR INSERT, UPDATE
AS
DECLARE @newprice money
SELECT @newprice = SalePrice From inserted
UPDATE T_Goods SET SaleUnitPrice = @newprice
WHERE GoodsID IN (SELECT GoodID From inserted )
13.12 前觸發(fā)器指定執(zhí)行觸發(fā)器而不執(zhí)行引發(fā)觸發(fā)器的SQL語(yǔ)句,因此,如果數(shù)據(jù)操作滿(mǎn)足完整性約束則在觸發(fā)器中必須重新執(zhí)行這些數(shù)據(jù)操作語(yǔ)句。
前觸發(fā)器示例:保證銷(xiāo)售單據(jù)中的會(huì)員卡是有效日期內(nèi)的會(huì)員卡:
CREATE TRIGGER CardValid
ON T_SaleDetail INSTEAD OF INSERT, UPDATE
AS
IF NOT EXISTS (SElETCT * From inserted a JOIN T_Card b ON a.CardID=b.CardID
WHERE a.SalDate NOT BETWEEN b.StartDate AND b.EndDate)
INSERT INTO T_SaleDetail SELECT * From inserted (若滿(mǎn)足條件此語(yǔ)句重新執(zhí)行)
13.13 用SQL語(yǔ)句修改存儲(chǔ)過(guò)程、用戶(hù)自定義函數(shù)、觸發(fā)器的語(yǔ)法與創(chuàng)建基本一致,只是將CREATE改為了ALTER。(查詢(xún)分析器中實(shí)現(xiàn))
第14章 安全管理
14.1 數(shù)據(jù)庫(kù)的安全控制:在DBMS的不同層次提供對(duì)有意和無(wú)意損害行為的安全防范。
有意的非法活動(dòng):加密存、取數(shù)據(jù);有意的非法操作:用戶(hù)身份驗(yàn)證、限制操作權(quán);
無(wú)意的損壞:提高系統(tǒng)的可靠性和數(shù)據(jù)備份
14.2 數(shù)據(jù)庫(kù)權(quán)限的種類(lèi):對(duì)DBMS進(jìn)行維護(hù)的權(quán)限;對(duì)數(shù)據(jù)庫(kù)對(duì)象和數(shù)據(jù)進(jìn)行操作的權(quán)限
SQL Server權(quán)限種類(lèi)(與數(shù)據(jù)庫(kù)用戶(hù)分類(lèi)對(duì)應(yīng)):隱含權(quán)限(預(yù)定義的內(nèi)置權(quán)限);語(yǔ)句權(quán)限(DDL語(yǔ)句權(quán)限,創(chuàng)建刪除數(shù)據(jù)庫(kù)對(duì)象);對(duì)象權(quán)限(DML語(yǔ)句權(quán)限,操作數(shù)據(jù)庫(kù)對(duì)象)
14.3 數(shù)據(jù)庫(kù)用戶(hù)的分類(lèi):數(shù)據(jù)庫(kù)系統(tǒng)管理員(SA,全部權(quán)限);數(shù)據(jù)庫(kù)對(duì)象擁有者(創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象的用戶(hù),對(duì)所擁有的對(duì)象具有一切權(quán)限);普通用戶(hù):只具有對(duì)數(shù)據(jù)的編輯查詢(xún)功能
14.4 三個(gè)認(rèn)證過(guò)程:身份認(rèn)證,只認(rèn)證用戶(hù)是否有連接到數(shù)據(jù)庫(kù)服務(wù)器的“連接權(quán)”;合法用戶(hù),驗(yàn)證是否是數(shù)據(jù)庫(kù)的合法用戶(hù);權(quán)限認(rèn)證,驗(yàn)證用戶(hù)是否具有要進(jìn)行的操作的操作權(quán)限
14.5 系統(tǒng)內(nèi)置的登錄賬戶(hù):BUILTINAdministrator;SA;域名Administrator,均DBMS管理員
14.6 創(chuàng)建SQL Server身份認(rèn)證的登錄賬戶(hù):EXECUTE sp_addlogin ‘user3’,’123’,’jessymin’
User3為登錄賬戶(hù),123為密碼,jessymin為默認(rèn)數(shù)據(jù)庫(kù);WINDOWS認(rèn)證:sp_grantlogin
14.7 刪除登錄賬戶(hù)存儲(chǔ)過(guò)程:EXEC droplogin ‘user3’(SQL Server身份驗(yàn)證);EXEC revokelogin ‘Server1nt_user’(WINDOWS身份驗(yàn)證)
14.8 登錄賬戶(hù)可以連接到SQL Server服務(wù)器上但并不具有訪(fǎng)問(wèn)任何數(shù)據(jù)庫(kù)的能力,必須再成為數(shù)據(jù)庫(kù)的合法用戶(hù)。一個(gè)登錄賬戶(hù)可以映射為多個(gè)數(shù)據(jù)庫(kù)用戶(hù),管理數(shù)據(jù)庫(kù)用戶(hù)的過(guò)程實(shí)際上就是建立登錄賬戶(hù)與數(shù)據(jù)庫(kù)用戶(hù)之間的映射關(guān)系的過(guò)程。新建的數(shù)據(jù)默認(rèn)只有一個(gè)用戶(hù):dbo,它是數(shù)據(jù)庫(kù)的擁有者。
14.9 創(chuàng)建數(shù)據(jù)庫(kù)用戶(hù):EXEC sp_adduser ‘U2’,’U2’,’user_role’,用戶(hù)名與登錄賬戶(hù)一致,并讓其成為“user_role”角色的成員。刪除:EXEC sp_dropuser ‘U2’
14.10 合法用戶(hù)除了對(duì)所屬數(shù)據(jù)庫(kù)系統(tǒng)表具有一些查詢(xún)權(quán)限外并不對(duì)數(shù)據(jù)庫(kù)中的用戶(hù)數(shù)據(jù)和對(duì)象具有任何權(quán)限,還得得到對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)和對(duì)象的操作權(quán)限
14.11 收回權(quán)限:不允許用戶(hù)或角色具有某種操作權(quán),或者收回曾經(jīng)授予的權(quán)限,置空標(biāo)記;
拒絕訪(fǎng)問(wèn):拒絕某用戶(hù)或角色具有某種操作權(quán),即使由于繼承獲得的權(quán)限也無(wú)效,叉叉標(biāo)記
14.12 用Transact-SQL語(yǔ)句管理對(duì)象權(quán)限入管理語(yǔ)句權(quán)限:P245
14.13 角色:數(shù)據(jù)庫(kù)中具有相同權(quán)限的一組用戶(hù)。系統(tǒng)預(yù)定義的固定角色;自定義用戶(hù)角色
14.14 固定的服務(wù)器角色:*amdin + dbcreator,權(quán)限最重要最高的是sysadmin,角色成員源均為系統(tǒng)的登錄賬戶(hù):EXEC sp_addsrvrolemember ‘Server1nt_user’ ,‘sysadmin’
14.15 固定的數(shù)據(jù)庫(kù)角色:db_*+public,權(quán)限最高的是db_owner:EXEC sp_addrolemember ‘db_owner’,’user3’ (注意用戶(hù)和用色的順序)
14.16 public角色:每個(gè)用戶(hù)均自動(dòng)為其成員,不具任何權(quán)限但可賦予權(quán)限。如果想讓所有數(shù)據(jù)庫(kù)用戶(hù)均具有某個(gè)特定權(quán)限則可將該權(quán)限授予public
14.17 用戶(hù)自定義的用戶(hù)角色:EXEC sp_addrole ‘myrole’
14.18 只要權(quán)限沒(méi)有被拒絕過(guò),角色中成員權(quán)限是角色的權(quán)限加上成員自己的權(quán)限。
相關(guān)推薦:
各地2016年全國(guó)計(jì)算機(jī)等級(jí)考試費(fèi)用匯總
北京 | 天津 | 上海 | 江蘇 | 山東 |
安徽 | 浙江 | 江西 | 福建 | 深圳 |
廣東 | 河北 | 湖南 | 廣西 | 河南 |
海南 | 湖北 | 四川 | 重慶 | 云南 |
貴州 | 西藏 | 新疆 | 陜西 | 山西 |
寧夏 | 甘肅 | 青海 | 遼寧 | 吉林 |
黑龍江 | 內(nèi)蒙古 |