第3章_數(shù)據(jù)定義與完整性約束的實現(xiàn)

1、數(shù)據(jù)定義與完整性約束講解：許志英 1.SQL 的表定義和完整性定義功能 2.SQL數(shù)據(jù)操作與完整性約束的作用SQL的數(shù)據(jù)定義功能 基本表的定義 視圖的定義 索引的定義CREATE TABLEDROP TABLEALTER TABLE CREATE VIEWDROP VIEWCREATE INDEXDROP INDEX基本表定義：CREATE TABLE ( , ) 數(shù)據(jù)類型 為列指定數(shù)據(jù)類型及其數(shù)據(jù)寬度； 關系數(shù)據(jù)庫支持非常豐富的數(shù)據(jù)類型，不同的數(shù)據(jù)庫管理系統(tǒng)支持的數(shù)據(jù)類型基本是一樣的，右表列出了常用的數(shù)據(jù)類型。 列級完整性約束 用于定義列或字段一級的完整性約束，一般包括： NOT NULL和

2、NULL約束 PRIMARY KEY約束 UNIQUE約束 FOREIGN KEY約束 DEFAULT定義 CHECK約束 表級完整性約束表級完整性約束 用于定義表一級的完整性約束，一般包括：PRIMARY KEY約束（復合屬性構成的主關鍵字說明）FOREIGN KEY約束（外部關鍵字及參照關系說明）CHECK約束（同時涉及到多個屬性的域完整性約束） 其它參數(shù)其它參數(shù) 不是SQL的標準選項，一般用于與物理存儲有關的說明，不同的數(shù)據(jù)庫管理系統(tǒng)定義的方式肯定不同，另外該項參數(shù)一般也不是必需的。 建立表的例子 倉庫倉庫職工職工供應商供應商訂購單訂購單使用“倉儲訂貨”數(shù)據(jù)庫 create databa

3、se 倉儲訂貨 on ( name =order_dat, filename=F:dataorder_dat.mdf, size=3mb, maxsize=300mb, filegrowth=5% ) log on ( name=order_log, filename=F:dataorder_log.ldf, size=3mb, maxsize=300mb, filegrowth=5% )例：建立倉庫表CREATE TABLE 倉庫( 倉庫號 CHAR(6) PRIMARY KEY, 城市 CHAR(10), 面積 INT CHECK (面積 0) )例：建立職工表CREATE TABLE 職

4、工( 倉庫號 CHAR(6) FOREIGN KEY REFERENCES 倉庫, 職工號 CHAR(5) PRIMARY KEY, 工資 INT CHECK (工資 = 1000 AND 工資 = 1000 AND 工資 = 5000) DEFAULT 1200,PRIMARY KEY (倉庫號,職工號)修改表結(jié)構命令ALTER TABLE ADD | DROP | DROP COLUMN | ALTER COLUMN 增加新的屬性（字段） 往訂購單關系中增加一個新屬性“完成日期”ALTER TABLE 訂購單 ADD 完成日期 DATETIME NULL修改屬性的定義將訂購單關系的完成日期

5、屬性的數(shù)據(jù)類型修改為SMALLDATETIME ALTER TABLE 訂購單 ALTER COLUMN 完成日期 SMALLDATETIME NULL 刪除完整性約束 建立的列級或表級完整性約束可以刪除，但是前提是在定義完整性約束時必須給出約束名稱，否則不能刪除。 設有：CREATE TABLE 職工(倉庫號 CHAR(5) FOREIGN KEY (倉庫號) REFERENCES 倉庫(倉庫號),職工號 CHAR(5) PRIMARY KEY,工資 INT CONSTRAINT salary CHECK (工資 = 1000 AND 工資 = 5000)DEFAULT 1200 )則可以：

6、 ALTER TABLE 職工 DROP CONSTRAINT salary刪除屬性 刪除訂購單關系中的“完成日期”屬性：ALTER TABLE 訂購單 DROP COLUMN 完成日期注意：有些系統(tǒng)的ALTER TABLE命令不允許刪除屬性，如果必須要刪除屬性，一般步驟是：先將舊表中的數(shù)據(jù)備份，然后刪除舊表、并建立新表，最后將原來的數(shù)據(jù)恢復到新表中。 刪除表命令格式：DROP TABLE 例：DROP TABLE 訂購單數(shù)據(jù)操作與數(shù)據(jù)完整性 插入操作 刪除操作 更新操作插入操作 SQL的插入語句是INSERT： INSERT INTO (,) VALUES(,) 插入倉庫元組 倉庫號 CHA

7、R(5) PRIMARY KEY 城市 CHAR(10) 面積 INT CHECK (面積 0) 實體完整性 用戶定義完整性插入職工元組 倉庫號 CHAR(5) FOREIGN KEY REFERENCES 倉庫, 職工號 CHAR(5) PRIMARY KEY, 工資 INT CHECK (工資 = 1000 AND 工資 = 5000) DEFAULT 1200 實體完整性 參照完整性 用戶定義完整性插入供應商元組 供應商號 CHAR(5) PRIMARY KEY 供應商名 CHAR(20) 地址 CHAR(20) 實體完整性插入訂購單元組 職工號 CHAR(5) NOT NULL FOR

8、EIGN KEY REFERENCES 職工 供應商號 CHAR(5) NULL FOREIGN KEY REFERENCES 供應商 訂購單號 CHAR(5) PRIMARY KEY 訂購日期 DATETIME DEFAULT getdate() 實體完整性 參照完整性 用戶定義完整性數(shù)據(jù)操作與數(shù)據(jù)完整性 插入操作 刪除操作 更新操作刪除操作 SQL的刪除語句是DELETE： DELETE FROM FROM WHERE DELETE命令從指定的表中刪除滿足“邏輯表達式”條件的元組； 如果沒有指定刪除條件則刪除表中的全部元組，所以在使用該命令時要格外小心； 刪除的條件可以與其他的表相關（使用

9、可選的FROM指定）； DELETE命令只刪除元組，它不刪除表或表結(jié)構。刪除訂購單記錄z不檢查數(shù)據(jù)完整性刪除供應商記錄 檢查參照完整性 空值刪除 拒絕刪除 級聯(lián)刪除z思考題如何實現(xiàn)刪除操作時的數(shù)據(jù)完整性控制？刪除職工記錄 檢查參照完整性 拒絕刪除 級聯(lián)刪除z思考題如何實現(xiàn)刪除操作時的數(shù)據(jù)完整性控制？數(shù)據(jù)操作與數(shù)據(jù)完整性 插入操作 刪除操作 更新操作更新操作 SQL的更新語句是UPDATE： UPDATE SET = , = FROM WHERE UPDATE更新滿足“邏輯表達式”條件的記錄； 一次可以更新多個屬性的值； 更新的條件可以與其他的表相關（使用FROM指定）； 如果沒有指定更新條件則

10、更新表中的全部記錄。查詢與數(shù)據(jù)操作 將一個查詢結(jié)果插入到指定的表 更新條件與另一個表相關聯(lián) 刪除條件與另一個表相關聯(lián)插入操作 插入一個查詢結(jié)果：INSERT INTO (,) 舉例： INSERT INTO 訂購單備份 SELECT * FROM 訂購單更新操作 UPDATE SET = , = FROM WHERE z UPDATE更新滿足“邏輯表達式”條件的記錄；z 一次可以更新多個屬性的值； z 更新的條件可以與其他的表相關（使用FROM指定）； z 如果沒有指定更新條件則更新表中的全部記錄。 更新操作舉例 給“武漢”倉庫的職工提高10%的工資 UPDATE 職工 SET 工資 = 工資

11、*1.10FROM 倉庫WHERE 倉庫.倉庫號 = 職工.倉庫號 AND 城市=武漢 刪除操作 DELETE FROM FROM WHERE z DELETE命令從指定的表中刪除滿足“邏輯表達式”條件的元組；z 如果沒有指定刪除條件則刪除表中的全部元組，所以在使用該命令時要格外小心； z 刪除的條件可以與其他的表相關（使用可選的FROM指定）； z DELETE命令只刪除元組，它不刪除表或表結(jié)構。 刪除操作舉例 刪除所在城市是上海的倉庫的所有職工元組。 DELETE FROM 職工 FROM 倉庫 WHERE 倉庫.倉庫號=職工.倉庫號 AND 城市=上海幾點注意 在執(zhí)行插入、更新和刪除操作

12、時可能會受到關系完整性的約束，這種約束可以保證數(shù)據(jù)庫中的數(shù)據(jù)是正確的。 雖然視圖可以像基本表一樣進行各種查詢，但是插入、更新和刪除操作在視圖上卻有一定限制。因為視圖是由基本表導出的，對視圖的任何操作最后都落實在基本表上，這些操作不能違背定義在表上的完整性約束。 可以利用游標（Cursor）對表進行刪除和更新操作，這部分內(nèi)容將在稍后介紹。 索引定義的命令格式CREATE UNIQUE CLUSTERED INDEX ON ( ASC | DESC , ASC | DESC ) 普通索引普通索引 唯一（唯一（UNIQUE）索引）索引 聚集（聚集（CLUSTERED）索引）索引 普通索引 如果沒有指

13、定UNIQUE或CLUSTERED等將建立普通索引。 在單個字段上建立普通索引：CREATE INDEX sup_idx ON 訂購單(供貨方)在多個字段上建立普通索引：CREATE INDEX sup_emp_idx ON 訂購單(供貨方,經(jīng)手人 DESC)唯一索引唯一索引 通過指定UNIQUE則為表創(chuàng)建唯一索引（不允許存在索引值相同的兩個元組）。 在CREATE TABLE命令中的UNIQUE約束將隱式創(chuàng)建唯一索引。 在倉庫關系的城市屬性上建立一個唯一索引： CREATE UNIQUE INDEX city_idx ON 倉庫(城市) 聚集索引聚集索引 z通過指定CLUSTERED建立聚集

14、索引。（索引值與關系中元組的順序物理相同 ）。 z在CREATE TABLE命令中的PRIMARY KEY約束將隱式創(chuàng)建聚集索引。 z一個表只允許建立一個聚集索引。 z如果在創(chuàng)建表時已經(jīng)指定了主關鍵字，則不可以再創(chuàng)建聚集索引。 使用索引的原則： 不應該在一個表上建立太多的索引（一般不超過兩到三個），索引能改善查詢效果，但也耗費了磁盤空間，降低了更新操作的性能，因為系統(tǒng)必須花時間來維護這些索引。除了為數(shù)據(jù)的完整性而建立的唯一索引外，建議在表較大時再建立普通索引，表中的數(shù)據(jù)越多，索引的優(yōu)越性才越明顯。索引的刪除命令格式： DROP INDEX 例：DROP INDEX 訂購單.sup_emp_id

15、x視圖視圖的概念和作用視圖的建立通過視圖進行查詢通過視圖進行更新倉庫號 城市 面積倉庫號 職工號 工資倉庫號 城市 職工號 工資倉庫號 面積基本表視圖視圖的概念和作用 三層結(jié)構之外部層 關系 從基本表派生虛擬表 可以源自單個表或多個表 可以包含虛擬列 用SQL SELECT語句定義視圖的概念和作用 增強了數(shù)據(jù)獨立性概念數(shù)據(jù)獨立性 安全屏蔽敏感數(shù)據(jù) 簡化應用定義視圖的命令 視圖是根據(jù)對基本表的查詢定義的，其命令格式如下：CREATE VIEW AS WITH CHECK OPTION通過視圖操作的數(shù)據(jù)是否滿足定義視圖時的條件做檢查例：定義視圖emp_v1,使之只包含職工基本表的職工號、倉庫號和姓

16、名字段。 create view emp_v1 as select 職工號,倉庫號,姓名 from 職工insert into emp_v1 values(e13,WH1,郭天華)例：定義視圖wh_v1,使之只包含城市在北京的倉庫號和面積信息。 Create view wh_v1 as Select 倉庫號，面積 From倉庫 Where 城市北京Insert into wh_v1 values (WH9,777)基于多個表的視圖 定義視圖 wh_emp_v1，使之包含倉庫號、城市、職工號和職工姓名信息。 create view wh_emp_v1 as select 倉庫.倉庫號,城市,職工號,姓名 from 職工 join 倉庫 on 職工.倉庫號 =倉庫.倉庫號insert into wh_emp_v1 values(wh11,杭州,E13,海燕)