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

1、4.1.3 SQL語言功能概述SQL功能命令動詞數(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功能命令動詞數(shù)據(jù)查詢SE4.2 SQL的數(shù)據(jù)類型數(shù)值型 字符串型日期時間型貨幣型4.2 SQL的數(shù)據(jù)類型數(shù)值型 數(shù)值型準確型 整數(shù)Bigint: 8字節(jié)， Int：4字節(jié)Smallint：2字節(jié)， Tinyint：1字節(jié) Bit：1位，存儲1或0小數(shù)Numeric（p,q）或Decimal（p,q）， 其中

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

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

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

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

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

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

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

9、QUE約束。 用于限制在一個列中不能有重復(fù)的值。創(chuàng)建學生表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 (刪除表當確信不再需要某個表時，可以將其刪除刪除表時會將與表有關(guān)的所

11、有對象一起刪掉，包括表中的數(shù)據(jù)。刪除表的語句格式為：DROP TABLE , 例：刪除test表的語句為：DROP TABLE test刪除表當確信不再需要某個表時，可以將其刪除4.3.2 修改表結(jié)構(gòu) 在定義完表之后，如果需求有變化，比如添加列、刪除列或修改列定義，可以使用ALTER TABLE語句實現(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ù)情況下，我們所提到的完整性，實際是指完整性規(guī)則為保證完整性，數(shù)據(jù)應(yīng)該滿足的約束條件，又稱為完整性約束完整性的原義 完整性的原義關(guān)系模型中的完整性 關(guān)系完整性在關(guān)系模型中的地位三要素之一（關(guān)系, 關(guān)系完整性, 關(guān)系操作）三種類型實體完整性；參照完整性；用戶定義完整性；關(guān)系模型中的完整性 關(guān)系完整性三種關(guān)系完整性規(guī)則解決現(xiàn)實世界的三個問題如何保證一個實體是可識別（區(qū)分）的方法：實體完整性如何保證能夠從一個實體找到另一個相關(guān)聯(lián)的實體，而不會出現(xiàn)找不到的情況方

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

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

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

17、么操作會破壞關(guān)系完整性。查詢操作，還是修改操作?思考課堂練習 依次執(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, 藍, B) 為(3, 藍, E)課堂練習完整性控制概述完整性控制目的：保證數(shù)據(jù)的正確性、有效性和相容性由DBMS負責，包括兩方面完整性規(guī)則的定義：通過SQL（運行時）完整性規(guī)則的檢查：通過專門的“完整性子系統(tǒng)”完整性控制概述主碼約束規(guī)則：主碼值不允

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

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

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

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

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