SQL數(shù)據(jù)庫創(chuàng)建表與完整性約束課件

1、4.1.3 SQL語言功能概述SQL功能命令動(dòng)詞數(shù)據(jù)查詢SELECT數(shù)據(jù)定義CREATE、DROP、ALTER數(shù)據(jù)操縱INSERT、UPDATE、DELETE數(shù)據(jù)控制GRANT、REVOKE四部分：數(shù)據(jù)定義功能、數(shù)據(jù)控制功能、數(shù)據(jù)查詢功能和數(shù)據(jù)操縱功能。 4.1.3 SQL語言功能概述SQL功能命令動(dòng)詞數(shù)據(jù)查詢SE4.2 SQL的數(shù)據(jù)類型數(shù)值型 字符串型日期時(shí)間型貨幣型4.2 SQL的數(shù)據(jù)類型數(shù)值型 數(shù)值型準(zhǔn)確型 整數(shù)Bigint: 8字節(jié)， Int：4字節(jié)Smallint：2字節(jié)， Tinyint：1字節(jié) Bit：1位，存儲(chǔ)1或0小數(shù)Numeric（p,q）或Decimal（p,q）， 其中

2、：p為數(shù)字位長度，q：小數(shù)位長度。近似型 Float：8字節(jié) Real：4字節(jié)數(shù)值型準(zhǔn)確型 字符串型普通編碼字符串類型統(tǒng)一字符編碼字符串類型二進(jìn)制字符串類型字符串型普通編碼字符串類型普通編碼字符串類型Char（n）:定長存儲(chǔ)，n=8000 Varchar（n）：不定長存儲(chǔ)（按實(shí)際長度存儲(chǔ)），長度最大不超過n , n=8000 注：n 為字符個(gè)數(shù)Text：存儲(chǔ)大于8000字節(jié)的文本普通編碼字符串類型Char（n）:定長存儲(chǔ)，n=8000 統(tǒng)一字符編碼字符串類型nchar（n）:定長存儲(chǔ)，n=4000 nvarchar（n）：不定長存儲(chǔ)，長度最大不超過n , n=4000 ntext：存儲(chǔ)大于80

3、00字節(jié)的文本特點(diǎn)：每個(gè)字符占兩個(gè)字節(jié)統(tǒng)一字符編碼字符串類型nchar（n）:定長存儲(chǔ)，n=40二進(jìn)制字符串類型Binary(n)：固定長度，n = 8000。Varbinary(n)：可變長度，n 8000 。注：n為二進(jìn)制數(shù)據(jù)的字節(jié)數(shù)image：大容量、可變長二進(jìn)制字符數(shù)據(jù)，可用于存儲(chǔ)文件。二進(jìn)制字符串類型Binary(n)：固定長度，n = 80日期時(shí)間型Datetime：8字節(jié)，年月日時(shí)分秒毫秒（例：2001/08/03 10:30:00.000 ） SmallDateTime：4字節(jié)，年月日時(shí)分 （例： 2001/08/03 10:30:00 ）日期、時(shí)間的輸入格式日期時(shí)間型Date

4、time：8字節(jié)，年月日時(shí)分秒毫秒貨幣類型Money： 8 個(gè)字節(jié)，精確到貨幣單位的千分之十。Smallmoney： 4 個(gè)字節(jié)，精確到貨幣單位的千分之十。限制到小數(shù)點(diǎn)后 4 位?？梢詭в羞m當(dāng)?shù)呢泿欧?hào)。例如，100 英鎊可表示為 100。貨幣類型Money： 8 個(gè)字節(jié)，精確到貨幣單位的千分之十。4.3 基本表的定義、刪除及修改 4.3.1 基本表的定義與刪除 4.3.2 修改表結(jié)構(gòu) 4.3 基本表的定義、刪除及修改 4.3.1 基本表的定義與4.3.1 基本表的定義與刪除1定義基本表 使用SQL語言中的CREATE TABLE語句實(shí)現(xiàn)，其一般格式為：CREATE TABLE （ 列級(jí)完整性

5、約束定義, 列級(jí)完整性約束定義 , 表級(jí)完整性約束定義 ） 4.3.1 基本表的定義與刪除1定義基本表在列級(jí)完整性約束定義處可以定義的約束NOT NULL：限制列取值非空。DEFAULT：給定列的默認(rèn)值。UNIQUE：限制列取值不重。CHECK：限制列的取值范圍。PRIMARY KEY：指定本列為主碼。FOREIGN KEY：定義本列為引用其他表的外碼。使用形式為：FOREIGN KEY（） REFERENCES （） 在列級(jí)完整性約束定義處可以定義的約束NOT NULL：限制列幾點(diǎn)說明NOT NULL和DEFAULT只能是列級(jí)完整性約束；其他約束均可在表級(jí)完整性約束處定義。注意以下幾點(diǎn)：第一

6、，如果CHECK約束是定義多列之間的取值約束，則只能在表級(jí)完整性約束處定義；第二，如果表的主碼由多個(gè)列組成，則也只能在表級(jí)完整性約束處定義，并將主碼列用括號(hào)括起來，即：PRIMARY KEY（列1，列2 ）；第三，如果在表級(jí)完整性約束處定義外碼，則“FOREIGN KEY ()”部分不能省。幾點(diǎn)說明NOT NULL和DEFAULT只能是列級(jí)完整性約束約束定義 列取值非空約束 NOT NULL例：sname char(10) NOT NULL約束定義 列取值非空約束約束定義（續(xù)） 表主碼約束在定義列時(shí)定義主碼（僅用于單列主碼）列定義 PRIMARY KEY例： SNO char(7) PRIMA

7、RY KEY在定義完列時(shí)定義主碼（用于單列或多列主碼）PRIMARY KEY （）例： PRIMARY KEY(SNO) PRIMARY KEY(SNO，CNO)約束定義（續(xù)） 表主碼約束約束定義（續(xù)）外碼引用約束指明本表外碼列引用的表及表中的主碼列。 FOREIGN KEY （）REFERENCES （）例： FOREIGN KEY （sno） REFERENCES 學(xué)生表（sno）約束定義（續(xù)）外碼引用約束指明本表外碼列引用的表及表中的主約束定義（續(xù)）默認(rèn)值約束格式：DEFAULT 默認(rèn)值例：定義系的默認(rèn)值為“計(jì)算機(jī)系”。 DEFAULT 計(jì)算機(jī)系約束定義（續(xù)）默認(rèn)值約束約束定義（續(xù)）CH

8、ECK約束格式：CHECK (約束表達(dá)式)例：定義成績大于等于0。 CHECK ( grade = 0 )約束定義（續(xù)）CHECK約束約束定義（續(xù)）UNIQUE約束在列級(jí)約束定義（僅用于單列約束）列定義 UNIQUE例： SNAME char(7) UNIQUE在表級(jí)約束定義（用于單列或多列組合約束）UNIQUE （）例： UNIQUE (SNO，CNO)約束定義（續(xù)）UNIQUE約束用于限制在一個(gè)列中不能有重復(fù)的值。用在事實(shí)上具有惟一性的屬性列上，比如每個(gè)人的身份證號(hào)碼、駕駛證號(hào)碼等均不能有重復(fù)值。注意：允許有一個(gè)空值；在一個(gè)表中可以定義多個(gè)UNIQUE約束；可以在一個(gè)列或多個(gè)列上定義UNI

9、QUE約束。 用于限制在一個(gè)列中不能有重復(fù)的值。創(chuàng)建學(xué)生表CREATE TABLE Student ( Sno char ( 7 ) PRIMARY KEY, Sname char ( 10 ) NOT NULL, Ssex char (2) CHECK (Ssex = 男 OR Ssex = 女), Sage tinyint CHECK (Sage = 15 AND Sage 0), Semester tinyint CHECK (Semester 0), Period int CHECK (Period 0), PRIMARY KEY(Cno) ) 創(chuàng)建課程表CREATE TABLE Co

10、urse (創(chuàng)建SC表CREATE TABLE SC ( Sno char(7) NOT NULL, Cno char(10) NOT NULL, Grade tinyint, CHECK (Grade = 0 and Grade = 100), PRIMARY KEY ( Sno, Cno ), FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ), FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) )創(chuàng)建SC表CREATE TABLE SC (刪除表當(dāng)確信不再需要某個(gè)表時(shí)，可以將其刪除刪除表時(shí)會(huì)將與表有關(guān)的所

11、有對象一起刪掉，包括表中的數(shù)據(jù)。刪除表的語句格式為：DROP TABLE , 例：刪除test表的語句為：DROP TABLE test刪除表當(dāng)確信不再需要某個(gè)表時(shí)，可以將其刪除4.3.2 修改表結(jié)構(gòu) 在定義完表之后，如果需求有變化，比如添加列、刪除列或修改列定義，可以使用ALTER TABLE語句實(shí)現(xiàn)。ALTER TABLE語句可以對表添加列、刪除列、修改列的定義、定義主碼、外碼，也可以添加和刪除約束。 4.3.2 修改表結(jié)構(gòu) 在定義完表之后，如果需求有變化，比如修改表結(jié)構(gòu)語法ALTER TABLE ALTER COLUMN | ADD COLUMN | DROP COLUMN | ADD

12、PRIMARY KEY（列名 ， n ） | ADD FOREIGN KEY（列名） REFERNECES 表名（列名）修改表結(jié)構(gòu)語法ALTER TABLE 示例例2為SC表添加“修課類別”列，此列的定義為：XKLB char(4)ALTER TABLE SC ADD XKLB char(4) NULL 示例例2為SC表添加“修課類別”列，此列的定義為：XKLB示例例3將新添加的XKLB的類型改為 char(6)。ALTER TABLE SC ALTER COLUMN XKLB char(6)示例例3將新添加的XKLB的類型改為示例例4刪除Course表的Period列。 ALTER TABL

13、E Course DROP COLUMN Period 示例例4刪除Course表的Period列。 指數(shù)據(jù)的正確性、有效性和相容性 然而大多數(shù)情況下，我們所提到的完整性，實(shí)際是指完整性規(guī)則為保證完整性，數(shù)據(jù)應(yīng)該滿足的約束條件，又稱為完整性約束完整性的原義 完整性的原義關(guān)系模型中的完整性 關(guān)系完整性在關(guān)系模型中的地位三要素之一（關(guān)系, 關(guān)系完整性, 關(guān)系操作）三種類型實(shí)體完整性；參照完整性；用戶定義完整性；關(guān)系模型中的完整性 關(guān)系完整性三種關(guān)系完整性規(guī)則解決現(xiàn)實(shí)世界的三個(gè)問題如何保證一個(gè)實(shí)體是可識(shí)別（區(qū)分）的方法：實(shí)體完整性如何保證能夠從一個(gè)實(shí)體找到另一個(gè)相關(guān)聯(lián)的實(shí)體，而不會(huì)出現(xiàn)找不到的情況方

14、法：參照完整性如何保證用于描述實(shí)體的屬性，其取值是合理的方法：用戶定義完整性三種關(guān)系完整性規(guī)則三種關(guān)系完整性規(guī)則實(shí)體完整性目的：保證每個(gè)元組（所代表的實(shí)體）是可標(biāo)識(shí)（區(qū)分）的規(guī)則：元組在主碼的每個(gè)屬性上取唯一值，且不能為空意義：關(guān)系往往對應(yīng)一個(gè)實(shí)體集，這時(shí)關(guān)系中的每個(gè)元組對應(yīng)一個(gè)實(shí)體現(xiàn)實(shí)世界中的實(shí)體是可區(qū)分的，關(guān)系中的元組也必須是可區(qū)分的主碼起到唯一標(biāo)識(shí)（區(qū)分）每個(gè)元組的作用，所以它必須是唯一的，同時(shí)也是非空的三種關(guān)系完整性規(guī)則實(shí)體完整性三種關(guān)系完整性規(guī)則三種關(guān)系完整性規(guī)則要點(diǎn)：如果一個(gè)關(guān)系的主碼由多個(gè)屬性構(gòu)成，那么每個(gè)屬性都不能取空值三種關(guān)系完整性規(guī)則三種關(guān)系完整性規(guī)則要點(diǎn)：三種關(guān)系完整性規(guī)

15、則參照完整性三種關(guān)系完整性規(guī)則復(fù)習(xí)：外部碼 外部碼定義：關(guān)系R1中的單個(gè)屬性或?qū)傩约?，對?yīng)另一個(gè)關(guān)系R2的主碼R1稱為參照關(guān)系， R2稱為被參照關(guān)系來源：由聯(lián)系集轉(zhuǎn)化而來，反映實(shí)體（元組）之間的聯(lián)系復(fù)習(xí)：外部碼 外部碼復(fù)習(xí)：外部碼復(fù)習(xí)：外部碼三種關(guān)系完整性規(guī)則 參照完整性目的：保證外碼引用的是一個(gè)有效的主碼規(guī)則：如果關(guān)系R的外部碼對應(yīng)關(guān)系S的主碼，則R每個(gè)元組在外部碼上的取值必須滿足： 或者等于空值 或者等于某個(gè)對應(yīng)的主碼值（S某個(gè)元組的主碼值）意義：在被參照關(guān)系S中， 一個(gè)主碼值代表所在的元組（一個(gè)實(shí)體）在參照關(guān)系R中，元組的外部碼要么取空值，表示不和任何S元組（實(shí)體）存在聯(lián)系要么取S的某

16、個(gè)主碼值，表示表示聯(lián)系是存在的，且涉及的是這個(gè)主碼值所代表的S元組（實(shí)體）如果違反以上兩種情況，表示聯(lián)系涉及了不存在的實(shí)體，這是不允許的三種關(guān)系完整性規(guī)則 參照完整性思考Thinking ：根據(jù)外部碼取值的可能性，我們在定義外部碼的屬性類型時(shí)，應(yīng)遵循什么原則?思考Thinking ：三種關(guān)系完整性規(guī)則用戶定義完整性目的：保證實(shí)體屬性的取值有效，等等規(guī)則：用戶根據(jù)具體的應(yīng)用環(huán)境定義例如年齡的取值范圍為0到200，性別只能是“男”或“女”職工編號(hào)是4位整數(shù)意義：反映了現(xiàn)實(shí)世界的真實(shí)情況，即實(shí)體的屬性必須滿足要滿足這些條件，反映了程序編制的要求三種關(guān)系完整性規(guī)則用戶定義完整性思考Thinking 什

17、么操作會(huì)破壞關(guān)系完整性。查詢操作，還是修改操作?思考課堂練習(xí) 依次執(zhí)行如下操作，哪些能夠成功？1零件關(guān)系： 添加(3, 綠, null)2供應(yīng)商關(guān)系： 添加(null, 四化, 廣州)3供應(yīng)商關(guān)系： 添加(E, 北電, 廣州)4零件關(guān)系： 修改(2, 白, A) 為(2, 黑, F)5供應(yīng)商關(guān)系： 刪除(A, 紅星, 北京)6零件關(guān)系： 修改(3, 藍(lán), B) 為(3, 藍(lán), E)課堂練習(xí)完整性控制概述完整性控制目的：保證數(shù)據(jù)的正確性、有效性和相容性由DBMS負(fù)責(zé)，包括兩方面完整性規(guī)則的定義：通過SQL（運(yùn)行時(shí)）完整性規(guī)則的檢查：通過專門的“完整性子系統(tǒng)”完整性控制概述主碼約束規(guī)則：主碼值不允

18、許空，也不允許出現(xiàn)重復(fù)定義：在創(chuàng)建或修改關(guān)系的定義語句中，用PRIMARY KEY關(guān)鍵字聲明主碼的同時(shí)，即定義了一個(gè)主碼約束create table 關(guān)系名( , primary key (屬性, 屬性) )主碼約束規(guī)則：主碼值不允許空，也不允許出現(xiàn)重復(fù)主碼約束例：create table 選修(學(xué)號(hào)VARCHAR (10),課程號(hào)VARCHAR (10),primary key (學(xué)號(hào), 課程號(hào)),)主碼約束唯一約束規(guī)則：對指定的一個(gè)屬性或?qū)傩越M合，不允許出現(xiàn)重復(fù)值定義：在創(chuàng)建或修改關(guān)系的定義語句中，用Unique關(guān)鍵字聲明create table 關(guān)系名( , unique (屬性, 屬性

19、) )唯一約束主碼約束例：create table 學(xué)生( , 身份證號(hào)VARCHAR (15), , unique (身份證號(hào)), )主碼約束外部碼約束規(guī)則：外部碼要么取空值，要么取對應(yīng)的某個(gè)主碼值 定義：在創(chuàng)建或修改關(guān)系的定義語句中，用FOREIGN KEY關(guān)鍵字聲明一個(gè)外部碼的同時(shí)，即定義了一個(gè)外部碼約束create table 表名( , foreign key (屬性名, 屬性名) references 關(guān)系名(屬性名, 屬性名) on delete on update 外部碼約束規(guī)則：外部碼要么取空值，要么取對應(yīng)的某個(gè)主碼值參照動(dòng)作說明當(dāng)某個(gè)主碼值被刪除/更新時(shí)（這個(gè)主碼值在被參照

20、關(guān)系中），如何處理對應(yīng)的外部碼值（這些外部碼值在參照關(guān)系中）RESTRICT 方式：僅當(dāng)沒有任何對應(yīng)的外碼值時(shí)，才可以刪除/更新這個(gè)主碼值，否則系統(tǒng)拒絕執(zhí)行此操作CASCADE 方式：連帶將所有對應(yīng)的外碼值一塊刪除/更新（刪除外碼值，實(shí)際上就是將所在的元組刪除掉）SET NULL 方式：將所有對應(yīng)的外碼值設(shè)為空值外部碼約束參照動(dòng)作外部碼約束外部碼約束CASCADE方式示例：當(dāng)主碼值被刪除/更新時(shí)，連帶刪除/更新對應(yīng)的外碼值外部碼約束CASCADE方式示例：當(dāng)主碼值被刪除/更新時(shí)，外部碼約束外部碼約束RESTRICT方式示例：僅當(dāng)沒有任何對應(yīng)的外碼值時(shí)，才可以刪除/更新主碼值，否則系統(tǒng)拒絕執(zhí)行此

21、操作外部碼約束RESTRICT方式示例：僅當(dāng)沒有任何對應(yīng)的外碼值時(shí)，才可以外部碼約束外部碼約束外部碼約束SET NULL方式示例：主碼值被刪除/更新時(shí)，將對應(yīng)的外碼值設(shè)為空值外部碼約束SET NULL方式示例：主碼值被刪除/更新時(shí)，外部碼約束外部碼約束外部碼約束例：create table 學(xué)生( 學(xué)號(hào)VARCHAR (4), 姓名, primary key (學(xué)號(hào)), )create table 選修( , foreign key (學(xué)號(hào)) references 學(xué)生(學(xué)號(hào)) on delete restrict on update cascade, ,)外部碼約束例：外部碼約束外部碼約束外

22、部碼約束外部碼約束非空約束非空約束規(guī)則：屬性值不允許取空值定義：在定義關(guān)系的語句中，聲明某個(gè)屬性不能取空值（notnull），即定義了一個(gè)非空約束create table 關(guān)系名( , 屬性名域default 缺省值 not null , )非空約束非空約束非空約束例：create table 學(xué)生(,姓名VARCHAR(20) not null,)非空約束例：完整性約束命名子句CONSTRAINT PRIMARY KEY |GOREIGN KEY 短語|CHECK短語 完整性約束命名子句CONSTRAINT 例：對雇員表和工作表添加主碼約束ALTER TABLE 雇員表 ADD CONSTRAINT PK_EMP PRIMARY KEY （雇員編號(hào)）ALTER TABLE 工作表 ADD CONSTRAINT PK_JOB PRIMARY KEY （工作編號(hào)） 完整性約束命名子句例：對雇員表和工作表添加主碼約束完整性約束命名子句添加UNIQUE約束的語法格式為： ALTER TABLE 表名 A