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

4.1.3SQL語言功能概述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.2SQL的數(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〕，其中：p為數(shù)字位長度，q：小數(shù)位長度。近似型 Float：8字節(jié)Real：4字節(jié)字符串型普通編碼字符串類型統(tǒng)一字符編碼字符串類型二進制字符串類型普通編碼字符串類型Char〔n〕:定長存儲，n<=8000Varchar〔n〕：不定長存儲〔按實際長度存儲〕，長度最大不超過n,n<=8000注：n為字符個數(shù)Text：存儲大于8000字節(jié)的文本統(tǒng)一字符編碼字符串類型nchar〔n〕:定長存儲，n<=4000nvarchar〔n〕：不定長存儲，長度最大不超過n,n<=4000ntext：存儲大于8000字節(jié)的文本特點：每個字符占兩個字節(jié)二進制字符串類型Binary(n)：固定長度，n<=8000。Varbinary(n)：可變長度，n<＝8000。注：n為二進制數(shù)據(jù)的字節(jié)數(shù)image：大容量、可變長二進制字符數(shù)據(jù)，可用于存儲文件。日期時間型Datetime：8字節(jié)，年月日時分秒毫秒 〔例：‘2001/08/0310:30:00.000’〕

SmallDateTime：4字節(jié)，年月日時分〔例：‘2001/08/0310:30:00’〕日期、時間的輸入格式貨幣類型Money：8個字節(jié)，精確到貨幣單位的千分之十。Smallmoney：4個字節(jié)，精確到貨幣單位的千分之十。限制到小數(shù)點后4位?？梢詭в羞m當?shù)呢泿欧枴＠纾?00英鎊可表示為￡100。4.3根本表的定義、刪除及修改4.3.1根本表的定義與刪除4.3.2修改表結(jié)構(gòu)4.3.1根本表的定義與刪除1．定義根本表使用SQL語言中的CREATETABLE語句實現(xiàn)，其一般格式為：CREATETABLE<表名>〔<列名><數(shù)據(jù)類型>[列級完整性約束定義]{,<列名><數(shù)據(jù)類型>[列級完整性約束定義］…}[,表級完整性約束定義]〕在列級完整性約束定義處可以定義的約束NOTNULL：限制列取值非空。DEFAULT：給定列的默認值。UNIQUE：限制列取值不重。CHECK：限制列的取值范圍。PRIMARYKEY：指定本列為主碼。FOREIGNKEY：定義本列為引用其他表的外碼。使用形式為：［FOREIGNKEY〔<外碼列名>〕］REFERENCES<外表名>〔<外表列名>〕幾點說明NOTNULL和DEFAULT只能是列級完整性約束；其他約束均可在表級完整性約束處定義。注意以下幾點：第一，如果CHECK約束是定義多列之間的取值約束，那么只能在表級完整性約束處定義；第二，如果表的主碼由多個列組成，那么也只能在表級完整性約束處定義，并將主碼列用括號括起來，即：PRIMARYKEY〔列1｛[，列2]…｝〕；第三，如果在表級完整性約束處定義外碼，那么“FOREIGNKEY(<外碼列名>)”局部不能省。約束定義

①列取值非空約束<列名><類型>NOTNULL

例：snamechar(10)NOTNULL約束定義〔續(xù)〕②表主碼約束在定義列時定義主碼〔僅用于單列主碼〕列定義PRIMARYKEY例：SNOchar(7)PRIMARYKEY在定義完列時定義主碼〔用于單列或多列主碼〕PRIMARYKEY〔<列名序列>〕 例：PRIMARYKEY(SNO)PRIMARYKEY(SNO，CNO)約束定義〔續(xù)〕③外碼引用約束指明本表外碼列引用的表及表中的主碼列。

[FOREIGNKEY〔<本表列名>〕]REFERENCES<外表名>〔<外表主碼列名>〕例：FOREIGNKEY〔sno〕REFERENCES學生表〔sno〕 約束定義〔續(xù)〕④默認值約束格式：DEFAULT默認值例：定義系的默認值為“計算機系”。

DEFAULT‘計算機系’約束定義〔續(xù)〕⑤CHECK約束格式：CHECK(約束表達式)例：定義成績大于等于0。

CHECK(grade>=0)約束定義〔續(xù)〕⑥UNIQUE約束在列級約束定義〔僅用于單列約束〕列定義UNIQUE例：SNAMEchar(7)UNIQUE在表級約束定義〔用于單列或多列組合約束〕UNIQUE〔<列名序列>〕 例：UNIQUE(SNO，CNO)用于限制在一個列中不能有重復的值。用在事實上具有惟一性的屬性列上，比方每個人的身份證號碼、駕駛證號碼等均不能有重復值。注意：允許有一個空值；在一個表中可以定義多個UNIQUE約束；可以在一個列或多個列上定義UNIQUE約束。創(chuàng)立學生表CREATETABLEStudent(Snochar(7)PRIMARYKEY,Snamechar(10)NOTNULL,Ssexchar(2)CHECK(Ssex='男'ORSsex='女'),SagetinyintCHECK(Sage>=15ANDSage<=45),Sdeptchar(20)DEFAULT'計算機系')創(chuàng)立課程表CREATETABLECourse(Cnochar(10)NOTNULL,Cnamechar(20)NOTNULL,CcredittinyintCHECK(Ccredit>0),SemestertinyintCHECK(Semester>0),PeriodintCHECK(Period>0),PRIMARYKEY(Cno))

創(chuàng)立SC表CREATETABLESC(Snochar(7)NOTNULL,Cnochar(10)NOTNULL,Gradetinyint,CHECK(Grade>=0andGrade<=100),PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno))刪除表當確信不再需要某個表時，可以將其刪除刪除表時會將與表有關(guān)的所有對象一起刪掉，包括表中的數(shù)據(jù)。刪除表的語句格式為：DROPTABLE<表名>{[,<表名>]…}例：刪除test表的語句為：DROPTABLEtest4.3.2修改表結(jié)構(gòu)在定義完表之后，如果需求有變化，比方添加列、刪除列或修改列定義，可以使用ALTERTABLE語句實現(xiàn)。ALTERTABLE語句可以對表添加列、刪除列、修改列的定義、定義主碼、外碼，也可以添加和刪除約束。修改表結(jié)構(gòu)語法ALTERTABLE<表名>[ALTERCOLUMN<列名><新數(shù)據(jù)類型>]|[ADD[COLUMN]<列名><數(shù)據(jù)類型>|[DROPCOLUMN<列名>]|[ADDPRIMARYKEY〔列名[，…n]〕]|[ADDFOREIGNKEY〔列名〕REFERNECES表名〔列名〕]例如例2．為SC表添加“修課類別”列，此列的定義為：XKLBchar(4)ALTERTABLESCADDXKLBchar(4)NULL

例如例3．將新添加的XKLB的類型改為char(6)。ALTERTABLESCALTERCOLUMNXKLBchar(6)例如例4．刪除Course表的Period列。ALTERTABLECourseDROPCOLUMNPeriod

指數(shù)據(jù)的正確性、有效性和相容性然而大多數(shù)情況下，我們所提到的完整性，實際是指完整性規(guī)那么為保證完整性，數(shù)據(jù)應該滿足的約束條件，又稱為完整性約束完整性的原義關(guān)系模型中的完整性

關(guān)系完整性在關(guān)系模型中的地位三要素之一〔關(guān)系,關(guān)系完整性,關(guān)系操作〕三種類型實體完整性；參照完整性；用戶定義完整性；三種關(guān)系完整性規(guī)那么解決現(xiàn)實世界的三個問題如何保證一個實體是可識別〔區(qū)分〕的方法：實體完整性如何保證能夠從一個實體找到另一個相關(guān)聯(lián)的實體，而不會出現(xiàn)找不到的情況方法：參照完整性如何保證用于描述實體的屬性，其取值是合理的方法：用戶定義完整性三種關(guān)系完整性規(guī)那么

實體完整性目的：保證每個元組〔所代表的實體〕是可標識〔區(qū)分〕的規(guī)那么：元組在主碼的每個屬性上取唯一值，且不能為空意義：關(guān)系往往對應一個實體集，這時關(guān)系中的每個元組對應一個實體現(xiàn)實世界中的實體是可區(qū)分的，關(guān)系中的元組也必須是可區(qū)分的主碼起到唯一標識〔區(qū)分〕每個元組的作用，所以它必須是唯一的，同時也是非空的三種關(guān)系完整性規(guī)那么

三種關(guān)系完整性規(guī)那么要點：如果一個關(guān)系的主碼由多個屬性構(gòu)成，那么每個屬性都不能取空值三種關(guān)系完整性規(guī)那么參照完整性復習：外部碼外部碼定義：關(guān)系R1中的單個屬性或?qū)傩约希瑢硪粋€關(guān)系R2的主碼R1稱為參照關(guān)系，R2稱為被參照關(guān)系來源：由聯(lián)系集轉(zhuǎn)化而來，反映實體〔元組〕之間的聯(lián)系復習：外部碼三種關(guān)系完整性規(guī)那么參照完整性目的：保證外碼引用的是一個有效的主碼規(guī)那么：如果關(guān)系R的外部碼對應關(guān)系S的主碼，那么R每個元組在外部碼上的取值必須滿足：或者等于空值或者等于某個對應的主碼值〔S某個元組的主碼值〕意義：在被參照關(guān)系S中，一個主碼值代表所在的元組〔一個實體〕在參照關(guān)系R中，元組的外部碼要么取空值，表示不和任何S元組〔實體〕存在聯(lián)系要么取S的某個主碼值，表示表示聯(lián)系是存在的，且涉及的是這個主碼值所代表的S元組〔實體〕如果違反以上兩種情況，表示聯(lián)系涉及了不存在的實體，這是不允許的思考Thinking：根據(jù)外部碼取值的可能性，我們在定義外部碼的屬性類型時，應遵循什么原那么?三種關(guān)系完整性規(guī)那么

用戶定義完整性目的：保證實體屬性的取值有效，等等規(guī)那么：用戶根據(jù)具體的應用環(huán)境定義例如年齡的取值范圍為0到200，性別只能是“男”或“女”職工編號是4位整數(shù)意義：反映了現(xiàn)實世界的真實情況，即實體的屬性必須滿足要滿足這些條件，反映了程序編制的要求思考Thinking

什么操作會破壞關(guān)系完整性。查詢操作，還是修改操作?課堂練習依次執(zhí)行如下操作，哪些能夠成功？1．零件關(guān)系：添加(3,綠,null)2．供給商關(guān)系：添加(null,四化,廣州)3．供給商關(guān)系：添加(E,北電,廣州)4．零件關(guān)系：修改(2,白,A)為(2,黑,F)5．供給商關(guān)系：刪除(A,紅星,北京)6．零件關(guān)系：修改(3,藍,B)為(3,藍,E)完整性控制概述完整性控制目的：保證數(shù)據(jù)的正確性、有效性和相容性由DBMS負責，包括兩方面完整性規(guī)那么的定義：通過SQL〔運行時〕完整性規(guī)那么的檢查：通過專門的“完整性子系統(tǒng)”主碼約束規(guī)那么：主碼值不允許空，也不允許出現(xiàn)重復定義：在創(chuàng)立或修改關(guān)系的定義語句中，用PRIMARYKEY關(guān)鍵字聲明主碼的同時，即定義了一個主碼約束createtable關(guān)系名(……[,primarykey(屬性{,屬性})]……)主碼約束例：createtable選修(學號VARCHAR(10),課程號VARCHAR(10),……,primarykey(學號,課程號),……)唯一約束規(guī)那么：對指定的一個屬性或?qū)傩越M合，不允許出現(xiàn)重復值定義：在創(chuàng)立或修改關(guān)系的定義語句中，用Unique關(guān)鍵字聲明createtable關(guān)系名(……[,unique(屬性{,屬性})]……)主碼約束例：createtable學生(……,身份證號VARCHAR(15),……,unique(身份證號),……)外部碼約束規(guī)那么：外部碼要么取空值，要么取對應的某個主碼值定義：在創(chuàng)立或修改關(guān)系的定義語句中，用FOREIGNKEY關(guān)鍵字聲明一個外部碼的同時，即定義了一個外部碼約束createtable表名(……{,foreignkey(屬性名{,屬性名})references關(guān)系名(屬性名{,屬性名})ondelete<參照動作>onupdate<參照動作>}參照動作說明當某個主碼值被刪除/更新時〔這個主碼值在被參照關(guān)系中〕，如何處理對應的外部碼值〔這些外部碼值在參照關(guān)系中〕RESTRICT方式：僅當沒有任何對應的外碼值時，才可以刪除/更新這個主碼值，否那么系統(tǒng)拒絕執(zhí)行此操作CASCADE方式：連帶將所有對應的外碼值一塊刪除/更新〔刪除外碼值，實際上就是將所在的元組刪除掉〕SETNULL方式：將所有對應的外碼值設為空值外部碼約束外部碼約束

CASCADE方式例如：當主碼值被刪除/更新時，連帶刪除/更新對應的外碼值外部碼約束

RESTRICT方式例如：僅當沒有任何對應的外碼值時，才可以刪除/更新主碼值，否那么系統(tǒng)拒絕執(zhí)行此操作外部碼約束

外部碼約束

外部碼約束

SETNULL方式例如：主碼值被刪除/更新時，將對應的外碼值設為空值外部碼約束

外部碼約束例：createtable學生(學號VARCHAR(4),姓名……,primarykey(學號),……)createtable選修(……,foreignkey(學號)references學生(學號)ondeleterestrictonupdatecascade,……,)外部碼約束外部碼約束非空約束

非空約束規(guī)那么：屬性值不允許取空值定義：在定義關(guān)系的語句中，聲明某個屬性不能取空值〔notnull〕，即定義了一個非空約束createtable關(guān)系名(……,屬性名域[default缺省值][notnull]……,)非空約束例：createtable學生(……,姓名VARCHAR(20)notnull,……,)完整性約束命名子句CONSTRAINT<完整性約束條件名>

[PRIMARYKEY|GOREIGNKEY短語|CHECK短語]例：對雇員表和工作表添加主碼約束ALTERTABLE雇員表ADDCONSTRAINTPK_EMPPRIMARYKEY〔雇員編號〕ALTERTABLE工作表ADDCONSTRAINTPK_JOBPRIMARYKEY〔工