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

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

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

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

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

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

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

CHECK(grade>=0)約束定義〔續(xù)〕⑥UNIQUE約束在列級(jí)約束定義〔僅用于單列約束〕列定義UNIQUE例：SNAMEchar(7)UNIQUE在表級(jí)約束定義〔用于單列或多列組合約束〕UNIQUE〔<列名序列>〕 例：UNIQUE(SNO，CNO)用于限制在一個(gè)列中不能有重復(fù)的值。用在事實(shí)上具有惟一性的屬性列上，比方每個(gè)人的身份證號(hào)碼、駕駛證號(hào)碼等均不能有重復(fù)值。注意：允許有一個(gè)空值；在一個(gè)表中可以定義多個(gè)UNIQUE約束；可以在一個(gè)列或多個(gè)列上定義UNIQUE約束。創(chuàng)立學(xué)生表CREATETABLEStudent(Snochar(7)PRIMARYKEY,Snamechar(10)NOTNULL,Ssexchar(2)CHECK(Ssex='男'ORSsex='女'),SagetinyintCHECK(Sage>=15ANDSage<=45),Sdeptchar(20)DEFAULT'計(jì)算機(jī)系')創(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))刪除表當(dāng)確信不再需要某個(gè)表時(shí)，可以將其刪除刪除表時(shí)會(huì)將與表有關(guān)的所有對(duì)象一起刪掉，包括表中的數(shù)據(jù)。刪除表的語(yǔ)句格式為：DROPTABLE<表名>{[,<表名>]…}例：刪除test表的語(yǔ)句為：DROPTABLEtest4.3.2修改表結(jié)構(gòu)在定義完表之后，如果需求有變化，比方添加列、刪除列或修改列定義，可以使用ALTERTABLE語(yǔ)句實(shí)現(xiàn)。ALTERTABLE語(yǔ)句可以對(duì)表添加列、刪除列、修改列的定義、定義主碼、外碼，也可以添加和刪除約束。修改表結(jié)構(gòu)語(yǔ)法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ù)情況下，我們所提到的完整性，實(shí)際是指完整性規(guī)那么為保證完整性，數(shù)據(jù)應(yīng)該滿足的約束條件，又稱為完整性約束完整性的原義關(guān)系模型中的完整性

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

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

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

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

什么操作會(huì)破壞關(guān)系完整性。查詢操作，還是修改操作?課堂練習(xí)依次執(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,藍(lán),B)為(3,藍(lán),E)完整性控制概述完整性控制目的：保證數(shù)據(jù)的正確性、有效性和相容性由DBMS負(fù)責(zé)，包括兩方面完整性規(guī)那么的定義：通過SQL〔運(yùn)行時(shí)〕完整性規(guī)那么的檢查：通過專門的“完整性子系統(tǒng)”主碼約束規(guī)那么：主碼值不允許空，也不允許出現(xiàn)重復(fù)定義：在創(chuàng)立或修改關(guān)系的定義語(yǔ)句中，用PRIMARYKEY關(guān)鍵字聲明主碼的同時(shí)，即定義了一個(gè)主碼約束createtable關(guān)系名(……[,primarykey(屬性{,屬性})]……)主碼約束例：createtable選修(學(xué)號(hào)VARCHAR(10),課程號(hào)VARCHAR(10),……,primarykey(學(xué)號(hào),課程號(hào)),……)唯一約束規(guī)那么：對(duì)指定的一個(gè)屬性或?qū)傩越M合，不允許出現(xiàn)重復(fù)值定義：在創(chuàng)立或修改關(guān)系的定義語(yǔ)句中，用Unique關(guān)鍵字聲明createtable關(guān)系名(……[,unique(屬性{,屬性})]……)主碼約束例：createtable學(xué)生(……,身份證號(hào)VARCHAR(15),……,unique(身份證號(hào)),……)外部碼約束規(guī)那么：外部碼要么取空值，要么取對(duì)應(yīng)的某個(gè)主碼值定義：在創(chuàng)立或修改關(guān)系的定義語(yǔ)句中，用FOREIGNKEY關(guān)鍵字聲明一個(gè)外部碼的同時(shí)，即定義了一個(gè)外部碼約束createtable表名(……{,foreignkey(屬性名{,屬性名})references關(guān)系名(屬性名{,屬性名})ondelete<參照動(dòng)作>onupdate<參照動(dòng)作>}參照動(dòng)作說明當(dāng)某個(gè)主碼值被刪除/更新時(shí)〔這個(gè)主碼值在被參照關(guān)系中〕，如何處理對(duì)應(yīng)的外部碼值〔這些外部碼值在參照關(guān)系中〕RESTRICT方式：僅當(dāng)沒有任何對(duì)應(yīng)的外碼值時(shí)，才可以刪除/更新這個(gè)主碼值，否那么系統(tǒng)拒絕執(zhí)行此操作CASCADE方式：連帶將所有對(duì)應(yīng)的外碼值一塊刪除/更新〔刪除外碼值，實(shí)際上就是將所在的元組刪除掉〕SETNULL方式：將所有對(duì)應(yīng)的外碼值設(shè)為空值外部碼約束外部碼約束

CASCADE方式例如：當(dāng)主碼值被刪除/更新時(shí)，連帶刪除/更新對(duì)應(yīng)的外碼值外部碼約束

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

外部碼約束

外部碼約束

SETNULL方式例如：主碼值被刪除/更新時(shí)，將對(duì)應(yīng)的外碼值設(shè)為空值外部碼約束

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

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

[PRIMARYKEY|GOREIGNKEY短語(yǔ)|CHECK短語(yǔ)]例：對(duì)雇員表和工作表添加主碼約束ALTERTABLE雇員表ADDCONSTRAINTPK_EMPPRIMARYKEY〔雇員編號(hào)〕ALTERTABLE工作表ADDCONSTRAINTPK_JOBPRIMARYKEY〔工