第05章SQL數(shù)據(jù)定義與完整性約束的實現(xiàn)-副本

1、SQL的表定義和完整性定義功能SQL數(shù)據(jù)操作與完整性約束的作用5.1.1 定義架構(gòu)5.1.2 定義表及其完整性約束5.1.3 修改表結(jié)構(gòu)的命令CREATE SCHEMA 倉儲CREATE SCHEMA 訂貨CREATE SCHEMA 基礎(chǔ)CREATE TABLE命令的基本格式列的定義計算列的定義表級約束5.1.2 定義表CREATE TABLE ( , , , ) 給出要創(chuàng)建的基本表的名稱； 給出列名或字段名； 為列指定數(shù)據(jù)為列指定數(shù)據(jù)類型及其數(shù)據(jù)類型及其數(shù)據(jù)寬度；寬度； 關(guān)系數(shù)據(jù)庫支關(guān)系數(shù)據(jù)庫支持非常豐富的持非常豐富的數(shù)據(jù)類型，不數(shù)據(jù)類型，不同的數(shù)據(jù)庫管同的數(shù)據(jù)庫管理系統(tǒng)支持的理系統(tǒng)支持的數(shù)

2、據(jù)類型基本數(shù)據(jù)類型基本是一樣的，在是一樣的，在描述時稍有不描述時稍有不同。同。 用于定義列或字段一級的完整性約束，一般包括：用于定義列或字段一級的完整性約束，一般包括：lNOT NULL和和NULL約束約束lPRIMARY KEY約束約束 lUNIQUE約束約束lFOREIGN KEY約束約束lCHECK約束約束 lDEFAULT定義定義用于定義表一級的完整性約束，一般包括：用于定義表一級的完整性約束，一般包括：lPRIMARY KEY約束（復(fù)合屬性構(gòu)成的主關(guān)鍵字說約束（復(fù)合屬性構(gòu)成的主關(guān)鍵字說明）明）例如：PRIMARY KEY(學(xué)號，課程號)lFOREIGN KEY約束（外部關(guān)鍵字及參照關(guān)

3、系說明）約束（外部關(guān)鍵字及參照關(guān)系說明）例如： FOREIGN KEY(學(xué)號) REFERENCES 學(xué)生(學(xué)號)lCHECK約束（同時涉及到多個屬性的域完整性約約束（同時涉及到多個屬性的域完整性約束）束） 例如：check (起始日期終止日期）說明外部關(guān)鍵字，F(xiàn)OREIGN KEY可以省略什么是外部關(guān)鍵字？外部關(guān)鍵字的作用？ schema_name . referenced_table_name ( ref_column ) 指出參照的表和列，默認(rèn)模式時schema_name可以省略，列名相同時( ref_column )可以省略column_name NULL | NOT NULL CON

4、STRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT | CHECK ( logical_expression ) DEFAULT constant_expression ON DELETE和

5、ON UPDATE說明在執(zhí)行刪除和更新操作時如何處理參照完整性刪除操作檢查的完整性？更新操作檢查的完整性？column_name NULL | NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL

6、 | SET DEFAULT | CHECK ( logical_expression ) DEFAULT constant_expression 其中column_name給出計算列的列名，computed_column_expression是對應(yīng)的計算表達式，PERSISTED說明物理存儲計算列的值。其他選項和定義實列時類似。column_name AS computed_column_expression PERSISTED NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES

7、referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE ON UPDATE NO ACTION | CHECK ( logical_expression ) 要求：倉庫表屬于倉儲架構(gòu)，包含: 倉庫號（主關(guān)鍵字） 城市 面積（倉庫的面積值是大于0的整數(shù)）CREATE TABLE 倉儲.倉庫(倉庫號 CHAR(6) ,城市 CHAR(10) ,面積 INT)PRIMARY KEYCHECK (面積 0) 要求：職工表屬于基礎(chǔ)架構(gòu)，包含： 倉庫號（參照倉庫表的倉庫號字段值, 外鍵）職工號（主關(guān)鍵字）姓名工資（1000到50

8、00之間，默認(rèn)是1200班組長（參照本關(guān)系的職工號屬性）CREATE TABLE 基礎(chǔ).職工(倉庫號 CHAR(6) CONSTRAINT ref_wh FOREIGN KEY REFERENCES 倉儲.倉庫(倉庫號),職工號 CHAR(8) PRIMARY KEY,姓名 CHAR(10),工資 numeric(8,2) CHECK (工資= 1000 AND 工資=0),PRIMARY KEY(倉庫號,器件號)要求：供應(yīng)商表屬于訂貨架構(gòu)，它包含：供應(yīng)商號（主關(guān)鍵字）供應(yīng)商名地址CREATE TABLE 訂貨.供應(yīng)商(供應(yīng)商號 CHAR(5) PRIMARY KEY,供應(yīng)商名 CHAR(20

9、),地址 CHAR(20)要求：訂購單表屬于訂貨架構(gòu)，它包含：訂購單號（主關(guān)鍵字）經(jīng)手人（非空，參照職工關(guān)系的職工）供貨方（參照供應(yīng)商關(guān)系的供應(yīng)商號）訂購日期（默認(rèn)值是系統(tǒng)的當(dāng)前日期）金額CREATE TABLE 訂貨.訂購單(訂購單號 CHAR(5) PRIMARY KEY,經(jīng)手人 CHAR(8) NOT NULL FOREIGN KEY REFERENCES 基礎(chǔ).職工(職工號),供貨方 CHAR(5) NULL FOREIGN KEY REFERENCES 訂貨.供應(yīng)商(供應(yīng)商號),訂購日期 DATETIME DEFAULT getdate(),金額 MONEY NULL)要求：訂購明細(xì)

10、表屬于訂貨架構(gòu)，它包含：訂購單號（參照訂購單關(guān)系的訂購單號）序號（大于等于1）器件號（參照器件表的器件號）單價數(shù)量（大于等于0）。其中主關(guān)鍵字由訂購單號和序號共同構(gòu)成CREATE TABLE 訂貨.訂購明細(xì)(訂購單號 CHAR(5) FOREIGN KEY REFERENCES 訂貨.訂購單ON DELETE CASCADE ON UPDATE CASCADE,序號 SMALLINT CHECK (序號=1),器件號 CHAR(6) FOREIGN KEY REFERENCES 基礎(chǔ).器件,單價 SMALLMONEY,數(shù)量 INT CONSTRAINT num CHECK (數(shù)量=0),PRI

11、MARY KEY (訂購單號,序號) )創(chuàng)建表時創(chuàng)建表時_列級列級創(chuàng)建表時創(chuàng)建表時_表級表級在已創(chuàng)建表上添加在已創(chuàng)建表上添加系統(tǒng)對完整性約束的檢查系統(tǒng)對完整性約束的檢查PRIMARY KEYPRIMARY KEY(只適合于單屬性主關(guān)鍵字只適合于單屬性主關(guān)鍵字)PRIMARY KEY(主關(guān)主關(guān)鍵字屬性集鍵字屬性集)ALTER TABLE ADD PRIMARY KEY(主主關(guān)鍵字屬性集關(guān)鍵字屬性集)INSERTUPDATEUNIQUEUNIQUEUNIQUE(列名集列名集)ALTER TABLE ADD UNIQUE(列名集列名集)INSERTUPDATEFOREIGN KEYFOREIGN K

12、EY(外部關(guān)鍵外部關(guān)鍵字字) REFERENCES 被參照被參照表表(主關(guān)鍵字主關(guān)鍵字)FOREIGN KEY(外部外部關(guān)鍵字關(guān)鍵字) REFERENCES 被參被參照表照表(主關(guān)鍵字主關(guān)鍵字)ALTER TABLE ADD FOREIGN KEY(外外部關(guān)鍵字部關(guān)鍵字) REFERENCES 被參照表被參照表(主關(guān)鍵字主關(guān)鍵字)參照表參照表:INSERTUPDATE被參照表被參照表:DELETE（4種選擇）種選擇）UPDATE （4種選擇）種選擇）DEFAULT DEFAULT 默認(rèn)值默認(rèn)值A(chǔ)LTER TABLE ADD DEFAULT 默認(rèn)值默認(rèn)值 FOR 列名列名INSERTCHECKC

13、HECK(邏輯表達式邏輯表達式)(只適合于單列只適合于單列)CHECK(邏輯表達式邏輯表達式) ALTER TABLE ADD CHECK(邏輯表達邏輯表達式式)INSERTUPDATEALTER TABLE schema_name.table_name ALTER COLUMN column_name NULL | NOT NULL | ADD | | | DROP CONSTRAINT constraint_name | COLUMN column_name ALTER COLUMN修改已有列的定義，但是只能修改為兼容數(shù)據(jù)類型或重新定義是否允許空值； ADD添加新列、或新的計算列或表級完整

14、性約束； ALTER TABLE schema_name.table_name ALTER COLUMN column_name NULL | NOT NULL | ADD | | | DROP CONSTRAINT constraint_name | COLUMN column_name DROP刪除指定的完整性約束或指定的列。 ALTER TABLE schema_name.table_name ALTER COLUMN column_name NULL | NOT NULL | ADD | | | DROP CONSTRAINT constraint_name | COLUMN colu

15、mn_name 刪除職工表上定義的約束ref_wh。ALTER TABLE 基礎(chǔ).職工DROP CONSTRAINT ref_wh如果在定義表和約束的時候沒有用CONSTRAINT短語指定約束名稱，系統(tǒng)將自動生成約束名稱。為職工表的倉庫號字段添加約束，說明該字段是外部關(guān)鍵字，同時規(guī)定當(dāng)被參照記錄被刪除時，將參照記錄的倉庫號字段值置為空值（即空值刪除），當(dāng)被參照記錄的倉庫號修改時級聯(lián)修改參照記錄的倉庫號字段值（即級聯(lián)更新）。ALTER TABLE 基礎(chǔ).職工ADD CONSTRAINT ref_whFOREIGN KEY (倉庫號) REFERENCES 倉儲.倉庫(倉庫號)ON DELETE

16、SET NULL ON UPDATE CASCADE為訂購明細(xì)表增加一個完成日期字段：ALTER TABLE 訂貨.訂購明細(xì)ADD 完成日期 datetime 刪除剛為訂購明細(xì)表增加的完成日期字段：ALTER TABLE 訂貨.訂購明細(xì) DROP COLUMN 完成日期 為訂購單表增加一個完成日期字段，默認(rèn)值是空值NULL：ALTER TABLE 訂貨.訂購單 ADD 完成日期 datetime DEFAULT NULL為訂購明細(xì)表增加一個計算字段，字段名為金額，計算表達式為單價*數(shù)量：ALTER TABLE 訂貨.訂購明細(xì) ADD 金額 AS 單價*數(shù)量插入操作及其完整性約束刪除操作及其完整

17、性約束更新操作及其完整性約束SQL的插入語句是INSERT，常用格式：INSERT INTO schema_name.table_name ( column_list ) VALUES(expression | DEFAULT | NULL ,.n ) schema_nameschema_name指出模式名指出模式名 table_nametable_name指出表名指出表名column_listcolumn_list給出插入操作所涉及列的列表（默認(rèn)是表的全部列）給出插入操作所涉及列的列表（默認(rèn)是表的全部列）expression | DEFAULT | NULL ,.n expression |

18、 DEFAULT | NULL ,.n 給出對應(yīng)于給出對應(yīng)于column_listcolumn_list的各個列的值的各個列的值 例5-12：插入倉庫關(guān)系的第1條記錄。INSERT INTO 倉儲.倉庫 VALUES(WH1,北京,500)或INSERT INTO 倉儲.倉庫(倉庫號,城市,面積) VALUES(WH1,北京,500)CREATE TABLE 倉儲.倉庫(倉庫號 CHAR(6) PRIMARY KEY,城市 CHAR(10),面積 INT CHECK (面積 0) )例5-14：插入職工關(guān)系的第1條記錄。INSERT INTO 基礎(chǔ).職工 VALUES(WH1, E2, 王月,

19、 1220, NULL)其中經(jīng)理屬性的NULL說明該職工暫時沒有直接領(lǐng)導(dǎo)（班組長）。CREATE TABLE 基礎(chǔ).職工(倉庫號 CHAR(6) CONSTRAINT ref_wh FOREIGN KEY REFERENCES 倉儲.倉庫(倉庫號),職工號 CHAR(8) PRIMARY KEY,姓名 CHAR(10),工資 numeric(8,2) CHECK (工資= 1000 AND 工資= 1000 AND 工資 0) )例5-15：嘗試執(zhí)行如下命令：INSERT INTO 基礎(chǔ).職工 VALUES(WH7, E17, 張揚, 1250, E2)違背參照完整性約束CREATE TABL

20、E 基礎(chǔ).職工(倉庫號倉庫號 CHAR(6) CONSTRAINT ref_wh FOREIGN KEY REFERENCES 倉儲倉儲.倉庫倉庫(倉庫號倉庫號),職工號 CHAR(8) PRIMARY KEY,姓名 CHAR(10),工資 numeric(8,2) CHECK (工資= 1000 AND 工資= 1000 AND 工資= 1000 AND 工資工資=0),PRIMARY KEY(倉庫號,器件號)違背實體完整性約束例5-22：嘗試為主屬性指定空值的插入。嘗試執(zhí)行如下命令：INSERT INTO 倉儲.庫存 VALUES(WH1,NULL,18)違背實體完整性約束CREATE T

21、ABLE 倉儲.庫存(倉庫號 CHAR(6) FOREIGN KEY REFERENCES 倉儲.倉庫,器件號 CHAR(6) FOREIGN KEY REFERENCES 基礎(chǔ).器件,數(shù)量 INT CHECK (數(shù)量=0),PRIMARY KEY(倉庫號,器件號)CREATE TABLE 訂貨訂貨.訂購明細(xì)訂購明細(xì)(訂購單號訂購單號 CHAR(5) FOREIGN KEY REFERENCES 訂貨訂貨.訂購單訂購單ON DELETE CASCADE ON UPDATE CASCADE,序號序號 SMALLINT CHECK (序號序號=1),器件號器件號 CHAR(6) FOREIGN K

22、EY REFERENCES 基礎(chǔ)基礎(chǔ).器件器件,單價單價 SMALLMONEY,數(shù)量數(shù)量 INT CONSTRAINT num CHECK (數(shù)數(shù)量量=0),PRIMARY KEY (訂購單號訂購單號,序號序號) )ALTER TABLE 訂貨訂貨.訂購明細(xì)訂購明細(xì) ADD 金額金額 AS 單價單價*數(shù)量數(shù)量例5-25：插入訂購明細(xì)關(guān)系的第一條記錄。INSERT INTO 訂貨.訂購明細(xì) VALUES(OR67, 1, P2, 120, 5, 600)正確的插入命令是：INSERT INTO 訂貨.訂購明細(xì) VALUES(OR67, 1, P2, 120, 5)錯誤SQL的刪除語句是DELET

23、E，常用格式：DELETE FROM schema_name.table_nameWHERE schema_nameschema_name指出模式名；指出模式名；table_nametable_name指出從哪個表刪除記錄；指出從哪個表刪除記錄；WHEREWHERE用來指出刪除記錄的條件，默認(rèn)是用來指出刪除記錄的條件，默認(rèn)是刪除全部記錄。刪除全部記錄。刪除操作只與參照完整性有關(guān)，并且只有在刪除被參照表的記錄時才需要檢查參照完整性，系統(tǒng)將根據(jù)定義參照完整性時確定的處理方法（拒絕刪除、空值刪除、默認(rèn)值刪除或級聯(lián)刪除）進行處理。DELETE FROM 訂貨.訂購明細(xì) WHERE 訂購單號=OR91或

24、DELETE 訂貨.訂購明細(xì) WHERE 訂購單號=OR91即關(guān)鍵詞FROM可以省略。WHERE指定被刪除記錄滿足的條件，如果缺省WHERE短語將刪除全部記錄，所以在執(zhí)行刪除操作的時候要慎重。訂購明細(xì)表不是任何表的被參照表，所以在訂購明細(xì)表上的刪除操作不需要做數(shù)據(jù)完整性檢查。DELETE 訂貨.訂購單 WHERE 訂購單號=OR67DELETE FROM 基礎(chǔ).器件 WHERE 器件號=P2失??！拒絕刪除失??！拒絕刪除CREATE TABLE 倉儲.庫存(倉庫號 CHAR(6) FOREIGN KEY REFERENCES 倉儲.倉庫,器件號 CHAR(6) FOREIGN KEY REFER

25、ENCES 基礎(chǔ).器件,數(shù)量 INT CHECK (數(shù)量=0),PRIMARY KEY(倉庫號,器件號)DELETE 倉儲.倉庫 WHERE 倉庫號=WH4ALTER TABLE 基礎(chǔ).職工ADD CONSTRAINT ref_whFOREIGN KEY (倉庫號) REFERENCES 倉儲.倉庫(倉庫號)ON DELETE SET NULL ON UPDATE CASCADECREATE TABLE 倉儲.庫存(倉庫號 CHAR(6) FOREIGN KEY REFERENCES 倉儲.倉庫,器件號 CHAR(6) FOREIGN KEY REFERENCES 基礎(chǔ).器件,數(shù)量 INT CHECK (數(shù)量=0),PRIMARY KEY(倉庫號,器件號)DELETE 倉儲.倉庫 WHERE 倉庫號=WH3失??！失??！ALTER TABLE 基礎(chǔ).職工ADD CONSTRAINT ref_whFOREIGN KEY (倉庫號) REFERENCES 倉儲.倉庫(倉庫號)ON DELETE SET NULL ON UPDATE CASCADECREATE TABLE 倉儲.庫存(倉庫號 CHAR(6) FOREIGN KEY REFERENCES 倉儲.倉庫,器件號 CHAR(6) FOREIGN KEY REFERENCES 基礎(chǔ).器