1、2021-11-251回顧回顧索引索引索引的作用是索引的作用是提高查詢速度提高查詢速度，但索引，但索引會降低更新速度會降低更新速度。聚集索引與非聚集索引，唯一索引與非唯一索引，單列聚集索引與非聚集索引，唯一索引與非唯一索引，單列索引與復(fù)合索引，主鍵索引。索引與復(fù)合索引，主鍵索引。視圖視圖視圖是一個虛表，只存儲視圖是一個虛表，只存儲查詢定義查詢定義，不存儲數(shù)據(jù)。，不存儲數(shù)據(jù)。在視圖上可以進(jìn)行增、刪、改、查。增刪改等操作有一在視圖上可以進(jìn)行增、刪、改、查。增刪改等操作有一定限制。定限制。數(shù)據(jù)庫原理與應(yīng)用數(shù)據(jù)庫原理與應(yīng)用第第11講講 數(shù)據(jù)完整性數(shù)據(jù)完整性2021-11-253提綱提綱重點(diǎn)重點(diǎn)分析數(shù)據(jù)

2、完整性要求分析數(shù)據(jù)完整性要求使用約束來強(qiáng)制完整性使用約束來強(qiáng)制完整性難點(diǎn)：難點(diǎn)：外鍵約束與檢查約束外鍵約束與檢查約束1.數(shù)據(jù)完整性概述數(shù)據(jù)完整性概述關(guān)鍵詞：實(shí)體完整、參照完整、自定義完整關(guān)鍵詞：實(shí)體完整、參照完整、自定義完整.2021-11-255n 數(shù)據(jù)完整性有數(shù)據(jù)完整性有3種類型種類型 實(shí)體完整性實(shí)體完整性(Entity Integrity)、 參照完整性參照完整性(Referential Integrity)、 用戶定義的完整性用戶定義的完整性(User-defined Integrity)。2021-11-256n 在在SQL Server中保障數(shù)據(jù)完整性的辦法中保障數(shù)據(jù)完整性的辦法規(guī)

3、則規(guī)則(Rule)默認(rèn)默認(rèn)(Default)-略略約束約束(Constraint)-重點(diǎn)學(xué)習(xí)重點(diǎn)學(xué)習(xí)觸發(fā)器觸發(fā)器(Trigger) -單獨(dú)的章節(jié)介紹單獨(dú)的章節(jié)介紹2021-11-257n規(guī)則規(guī)則(Rule)p規(guī)則規(guī)則(Rule)只能通過只能通過T_sql語句建，不能使用管理平臺工具建。語句建，不能使用管理平臺工具建。規(guī)則是單獨(dú)存儲的獨(dú)立的數(shù)據(jù)庫對象，可以多次被綁定。規(guī)則是單獨(dú)存儲的獨(dú)立的數(shù)據(jù)庫對象，可以多次被綁定。p語法如下語法如下：1.CREATE RULE語句用于在當(dāng)前數(shù)據(jù)庫中創(chuàng)建規(guī)則，其語法格式如語句用于在當(dāng)前數(shù)據(jù)庫中創(chuàng)建規(guī)則，其語法格式如下：下：CREATE RULE rule_nam

4、e AS condition_expression2.使用使用sp_helptext 系統(tǒng)存儲過程可以查看規(guī)則的文本信息。系統(tǒng)存儲過程可以查看規(guī)則的文本信息。sp_helptext （規(guī)則（規(guī)則/黙認(rèn)值黙認(rèn)值/觸發(fā)器觸發(fā)器/未加密的存儲過程或視圖）未加密的存儲過程或視圖）3.使用使用sp_bindrule綁定規(guī)則綁定規(guī)則sp_bindrule 規(guī)則名規(guī)則名,表名表名.字段名字段名4.使用使用sp_unbindrule解除規(guī)則解除規(guī)則sp_unbindrule employee.hire_date5.刪除規(guī)則刪除規(guī)則。DROP RULE hire_date_rule2021-11-258規(guī)則在管

5、理臺查看。規(guī)則在管理臺查看。2021-11-259n 約束約束(Constraint)p約束定義了一列數(shù)據(jù)或一行數(shù)據(jù)表的的限制條件。使約束定義了一列數(shù)據(jù)或一行數(shù)據(jù)表的的限制條件。使用約束用約束優(yōu)先于優(yōu)先于使用使用觸發(fā)器、規(guī)則和默認(rèn)值觸發(fā)器、規(guī)則和默認(rèn)值。p約束在表定義中聲明。約束在表定義中聲明。n SQL Server中有中有6種約束：種約束：p主鍵約束主鍵約束 p外鍵約束外鍵約束p惟一性約束惟一性約束p檢查約束檢查約束p默認(rèn)約束默認(rèn)約束p非空值約束非空值約束2021-11-2510工號工號商品號商品號數(shù)量數(shù)量銷售日期銷售日期工號工號姓名姓名性別性別身份證號身份證號 生日生日入職日期入職日期移

6、動電話移動電話商品號商品號商品名商品名單位單位單價單價員工表員工表銷售表銷售表商品表商品表數(shù)據(jù)完整性要求：數(shù)據(jù)完整性要求：（1）實(shí)體完整，不能有重復(fù)行，用）實(shí)體完整，不能有重復(fù)行，用主鍵約束主鍵約束，員工表中的身份證不能出現(xiàn)，員工表中的身份證不能出現(xiàn)同號，用同號，用唯一約束唯一約束。（2）參照完整，銷售表中的工號取值必須來自員工表，商品號取值必須來）參照完整，銷售表中的工號取值必須來自員工表，商品號取值必須來自商品表，用自商品表，用外鍵約束外鍵約束（3）姓名不能為空，用）姓名不能為空，用非空約束非空約束。（4）性別只能取）性別只能取男男或或女女，入職日期不能比生日大，入職日期不能比生日大18年

7、，移動電年，移動電話必須為話必須為“1？”，用，用檢查約束檢查約束。（5）當(dāng)性別沒有輸入值時，默認(rèn)為）當(dāng)性別沒有輸入值時，默認(rèn)為男男,當(dāng)單位沒有輸入值時，默認(rèn)為當(dāng)單位沒有輸入值時，默認(rèn)為個個用用默認(rèn)約束默認(rèn)約束。n分析實(shí)例分析實(shí)例2021-11-2511案例表案例表2. 通過創(chuàng)建約束來強(qiáng)制完整性通過創(chuàng)建約束來強(qiáng)制完整性關(guān)鍵詞：可以在每列上定義（稱為列約束關(guān)鍵詞：可以在每列上定義（稱為列約束)，也可以在所有的列之后定義也可以在所有的列之后定義(稱為表約束稱為表約束).2021-11-2513說明：說明：1. 創(chuàng)建的所有約束都有約束名、約束類型和約束對象。創(chuàng)建的所有約束都有約束名、約束類型和約束對

8、象。2. 約束名可以顯式指出，如約束名可以顯式指出，如“CONSTRAINT 約束名約束名”，如果沒有顯式指出，系統(tǒng)會自動生成一個約束名。如果沒有顯式指出，系統(tǒng)會自動生成一個約束名。3. 如果一個約束只涉及一個列，可以在如果一個約束只涉及一個列，可以在列的定義中創(chuàng)建列的定義中創(chuàng)建該約束該約束。一個約束涉及一個或多個列，則。一個約束涉及一個或多個列，則約束都可以約束都可以在所有列之后創(chuàng)建。在所有列之后創(chuàng)建。案例案例2021-11-25148.4.2 8.4.2 外鍵約束外鍵約束例：關(guān)系圖例：關(guān)系圖課堂小練課堂小練如下圖建表，表名：如下圖建表，表名：student，注意表中約束：，注意表中約束：1

9、、主鍵約束、主鍵約束2、姓名不為空、姓名不為空3、性別、性別 默認(rèn)為默認(rèn)為男男4、身份證號唯一，長度為、身份證號唯一，長度為185、手機(jī)約束：、手機(jī)約束：11位，如：位，如：10-90-90-90-90-90-90-90-90-90-92021-11-25153. 綜合練綜合練-課后體會課后體會2021-11-2517如下表如下表學(xué)號學(xué)號課程號課程號成績成績學(xué)號學(xué)號 姓名姓名性別性別身份證號身份證號 生日生日手機(jī)號手機(jī)號課程號課程號課程名課程名學(xué)分學(xué)分學(xué)生表學(xué)生表課程表課程表成績表成績表2021-11-2518-創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫Create Database TEACHON PRIMARY

10、 (NAME=Teach_dat, FILENAME=D:YZTEACH.mdf)LOG ON(NAME=Teach_log, FILENAME=D:YZTEACH.ldf)gouse teachgo2021-11-2519-創(chuàng)建數(shù)據(jù)表創(chuàng)建數(shù)據(jù)表CREATE TABLE 學(xué)生表學(xué)生表(學(xué)號學(xué)號 char(10) Primary Key, 姓名姓名 nchar(4) NOT NULL, 性別性別 nchar(1) NOT NULL DEFAULT 男男,生日生日 DateTime, 身份證號身份證號 char(18) UNIQUE,手機(jī)手機(jī) char(11),CHECK (性別性別=男男 or

11、性別性別=女女),CHECK (手機(jī)手機(jī) Like 10-90-90-90-90-90-90-90-90-90-9)go2021-11-2520CREATE TABLE 課程表課程表(課程號課程號 char(6) PRIMARY KEY NONCLUSTERED, 課程名課程名 nchar(10) NOT NULL UNIQUE, 學(xué)分學(xué)分 int NOT NULL)goCREATE TABLE 成績表成績表(學(xué)號學(xué)號 char(10) NOT NULL FOREIGN KEY REFERENCES 學(xué)生表學(xué)生表(學(xué)號學(xué)號), 課程號課程號 char(6) NOT NULL FOREIGN K

12、EY REFERENCES 課程表課程表(課程號課程號), 成績成績 int DEFAULT(0),PRIMARY KEY NONCLUSTERED (學(xué)號學(xué)號,課程號課程號)go2021-11-2521-添加數(shù)據(jù)添加數(shù)據(jù)INSERT INTO 學(xué)生表學(xué)生表(學(xué)號學(xué)號,姓名姓名,性別性別,生日生日,身份證號身份證號,手機(jī)手機(jī))VALUES (1000000001,一一,男男, 1981-1-113926190001)INSERT INTO 學(xué)生表學(xué)生表(學(xué)號學(xué)號,姓名姓名,性別性別,生日生日,身份證號身份證號,手機(jī)手機(jī))VALUES (100000000

13、2,二二,男男, 1981-1-113926190002)INSERT INTO 學(xué)生表學(xué)生表(學(xué)號學(xué)號,姓名姓名,性別性別,生日生日,身份證號身份證號,手機(jī)手機(jī))VALUES (1000000003,三三, 男男,1981-1-113926190003)goINSERT INTO 課程表課程表(課程號課程號,課程名課程名,學(xué)分學(xué)分) VALUES(NN0001,計算機(jī)網(wǎng)絡(luò)計算機(jī)網(wǎng)絡(luò),4)INSERT INTO 課程表課程表(課程號課程號,課程名課程名,學(xué)分學(xué)分) VALUES(NN0002,數(shù)據(jù)庫原理與應(yīng)用數(shù)據(jù)庫

14、原理與應(yīng)用,4)INSERT INTO 課程表課程表(課程號課程號,課程名課程名,學(xué)分學(xué)分) VALUES(NN0003,計算機(jī)高級語言計算機(jī)高級語言,4)goINSERT INTO 成績表成績表(學(xué)號學(xué)號,課程號課程號,成績成績) VALUES (1000000001,NN0001, 65)INSERT INTO 成績表成績表(學(xué)號學(xué)號,課程號課程號,成績成績) VALUES (1000000002,NN0001,74 )INSERT INTO 成績表成績表(學(xué)號學(xué)號,課程號課程號,成績成績) VALUES (1000000003,NN0001, 53)INSERT INTO 成績表成績表(

15、學(xué)號學(xué)號,課程號課程號,成績成績) VALUES (1000000001,NN0002, 77)INSERT INTO 成績表成績表(學(xué)號學(xué)號,課程號課程號,成績成績) VALUES (1000000002,NN0002, 82)INSERT INTO 成績表成績表(學(xué)號學(xué)號,課程號課程號,成績成績) VALUES (1000000003,NN0002,75 )INSERT INTO 成績表成績表(學(xué)號學(xué)號,課程號課程號,成績成績) VALUES (1000000001,NN0003,81 )INSERT INTO 成績表成績表(學(xué)號學(xué)號,課程號課程號,成績成績) VALUES (100000

16、0002,NN0003, 91)INSERT INTO 成績表成績表(學(xué)號學(xué)號,課程號課程號,成績成績) VALUES (1000000003,NN0003,66 )go2021-11-2522-創(chuàng)建視圖創(chuàng)建視圖CREATE VIEW 課程平均成績視圖課程平均成績視圖 asSELECT 課程名課程名,AVG(b.成績成績) as 平均成績平均成績FROM 課程表課程表 a JOIN 成績表成績表 b on a.課程號課程號=b.課程號課程號GROUP BY a.課程名課程名goCREATE VIEW 學(xué)生修讀學(xué)分視圖學(xué)生修讀學(xué)分視圖 asSELECT a.姓名姓名,SUM(c.學(xué)分學(xué)分) as

17、 修讀學(xué)分修讀學(xué)分, sum(case when b.成成績績60 then c.學(xué)分學(xué)分 else 0 end) as 已獲學(xué)分已獲學(xué)分 FROM 學(xué)生表學(xué)生表 a FULL JOIN 成績表成績表 b on a.學(xué)號學(xué)號=b.學(xué)號學(xué)號 FULL JOIN 課程表課程表 c on b.課程號課程號=c.課程號課程號GROUP BY a.學(xué)號學(xué)號,a.姓名姓名go2021-11-2523CREATE VIEW 平均成績前兩名視圖平均成績前兩名視圖 asSELECT TOP 2 姓名姓名,AVG(成績成績) as 平均成績平均成績FROM 學(xué)生表學(xué)生表 a FULL JOIN 成績表成績表 b

18、on a.學(xué)號學(xué)號=b.學(xué)號學(xué)號GROUP BY a.姓名姓名goCREATE VIEW 平均成績不及格的課程視圖平均成績不及格的課程視圖 asSELECT a.課程號課程號,a.課程名課程名,AVG(b.成績成績) as 平均成績平均成績FROM 課程表課程表 a FULL JOIN 成績表成績表 b on a.課程號課程號=b.課程號課程號GROUP BY a.課程號課程號,a.課程名課程名HAVING AVG(b.成績成績) 60go2021-11-2524總結(jié)總結(jié)1. 數(shù)據(jù)完整性有數(shù)據(jù)完整性有3種類型。實(shí)體完整性、參照完整性和種類型。實(shí)體完整性、參照完整性和用戶定義的完整性。用戶定義的完整性。2. 約束是約束是SQL Server提供的自動保持?jǐn)?shù)據(jù)庫完整性的提供的自動保持?jǐn)?shù)據(jù)庫完整性的一種方法，定義了可輸入表或表的單個列中的數(shù)據(jù)的一種方法，定義了可輸入表或表的單個列中的數(shù)據(jù)的限制條件。限制條件。3. 在在SQL Server中有中有6種約束：非空值約束、主鍵約束、種約束：非空值約束、主鍵約束、外鍵約束、唯一性約束、檢查約束和默認(rèn)約束。外鍵約束、唯一性約束、檢查約束和默認(rèn)約束。 小課內(nèi)容小課內(nèi)容2021-11-25261. 在管理臺實(shí)現(xiàn)約束在管理臺實(shí)現(xiàn)約束在大課的綜合案例的基礎(chǔ)上，學(xué)習(xí)用管理平臺和在