SQLServer表管理SQLServer2000數據類型創建表修改表刪除表數據完整性SQLServer2000的數據類型系統提供的數據類型創建和刪除用戶定義的數據類型選擇數據類型的指導原則系統提供的數據類型數字數據整型數據：存儲整數小數數據：包含存儲在最小有效數上的數據bigint占8個字節，值的范圍為-263～263-1int占4個字節，值的范圍為-231～231-1smallint占2個字節，值的范圍為-32768～32767tinyint占1個字節，值的范圍為0～255decimal[(p[,s])]p為精度，最大38；

s為小數位數，0≤s≤pnumeric[(p[,s])]在SQLServer中，等價于decimal系統提供的數據類型（續）數字數據（續）近似數字數據：表示浮點數據的近似數字貨幣數據：表示正的或負的貨幣值float[(n)]從-1.79E+308到1.79E+308之間的浮點數字數據；n

為用于存儲科學記數法尾數的位數，同時指示其精度和存儲大小，1≤n≤53real從3.40E+38到3.40E+38之間的浮點數字數據，存儲大小為4字節；SQLServer中，real的同義詞為float(24)

money占8個字節，值的范圍為-922337203685477.5808～+922337203685477.5807smallmoney占4個字節，值的范圍為-214748.3648～214748.3647系統提供的數據類型（續）二進制數據其他binary[(n)]存儲字節個數0～8000varbinary[(n)]存儲字節個數0～8000image存儲字節個數0～2Gbit存儲位數據cursor存儲對游標的引用rowversion(timestamp)時間戳sql_variant可存儲除text、ntext、image、rowversion之外的其他類型table存儲函數返回結果uniqueidentifier存儲GUID以及UUID選擇數據類型的指導原則若列值的長度相差很大，那么使用變長數據類型例如某列存儲的是人名，地址等謹慎使用tinyint數據類型雖然節省空間，但擴展性很小對于小數數據來說，一般使用decimal數據類型可以精確地控制精度如果行的存儲量超過8000字節，使用text或者image若不大于8000字節，可使用char、varchar或者binary數據類型對于貨幣數據，使用money數據類型不要使用類型為float或者real的列作為主鍵因為它們不精確，所以不適合用于比較創建表數據表在企業管理器中創建表在查詢分析器中創建表創建表數據表數據庫表是組成關系數據庫最常見的數據庫對象之一。部件表ID顏色重量AB123Blue10.5CD456Red8.0EF789Green9.25GH012Yellow8.0IJ341Blue1.0設計表的結構：字段的名稱每個字段的數據類型和寬度字段是否允許為空是否有缺省值是否為標識列是否為主鍵、外鍵創建表創建表在企業管理器中創建表在查詢分析器中創建表創建表結構的語法CreateTable

表名

（列名1數據類型[列級約束1]……,

列名2數據類型[列級約束1]……, …………..,

[表級約束1]…...)；創建表創建表

Columnname

DatatypeNULLor

NOTNULLCREATETABLEdbo.Categories (CategoryID CategoryName Description Picture

int

nvarchar(15)ntextimage

NOTNULL,

NOTNULL,NULL,NULL)舉例創建表列名數據類型長度是否允許為空值默認值說明sidsnamesexbirthday必須小于今天IDCard必須惟一phoneemailPhone與email不能同時為空char6×無主鍵varchar20×無varchar2×男男，女smalldatetime4×無varchar50×無varchar20√無varchar20√無利用企業管理器查看表的信息利用系統存儲過程查看表的信息Sp_helpSp_spaceusedSp_depends創建表修改表在企業管理器中修改表在查詢分析器中修改表修改表在企業管理器中修改表使用企業管理器對表的列進行添加、修改和刪除使用查詢分析器修改表結構修改表ALTERTABLE<表名>[ADD<列名><數據類型>[約束]][DROPCOLUMN<列名>][ALTERCOLUMN<列名><數據類型>[約束]][ADDCONSTRAINT[約束名]][DROPCONSTRAINT[約束名]]修改表——添加和刪除列添加列語法：ALTERTABLE表名

ADD列名

數據類型[NULL|NOTNULL]ALTERTABLEXSADD

獎學金等級tinyintNULLGOALTERTABLEXSALTERCOLUMN

姓名char(10)ALTERCOLUMN

出身時間datetimeGOALTERTABLEXSALTERCOLUMN

姓名char(10)GoALTERTABLEXSALTERCOLUMN

出身時間datetimeGO修改表——添加和刪除列刪除列語法：ALTERTABLE表名

DROPCOLUMN列名[,…n]不能刪除以下列正在復制的列用在索引中的列用在CHECK、FOREIGNKEY、UNIQUE或PRIMARYKEY

約束中的列與DEFAULT定義關聯或綁定到某一默認對象的列綁定到規則的列修改表——添加和刪除列ALTERTABLECategoriesNew

ADDCommissionmoneynull添加ALTERTABLECategoriesNew

DROPCOLUMNSales_date刪除Customer_nameSales_amountSales_dateCustomerID

Commission

刪除表在企業管理器中刪除表在查詢分析器中刪除表刪除表在企業管理器中刪除表除去對象對話框刪除表刪除表語法：DROPTABLE表名

DROPTABLEXS數據完整性數據完整性類型實施數據完整性的途徑定義約束數據完整性的類型數據完整性指的是數據庫中存儲的數據的正確性、有效性和一致性數據安全性與完整性的區別安全性：防止用戶非法使用數據庫，包括惡意破壞數據和越權存取數據。完整性：防止合法用戶使用數據庫時向數據庫中加入不合語義的數據。數據完整性的類型域完整性

(列)實體完整性

（行）參照完整性

(表之間)數據完整性的類型（1）在SQL中通過PRIMARYKEY、UNIQUE或IDENTITY實現域完整性是指一個字段的輸入有效性，是否允許為空值。

例：性別（男，女）實體完整性是指保證表中所有的記錄的唯一。SCtablegradecnosno9570888063121239500195001950109501095010外碼Studenttable主碼在SQL中通過FOREIGNKEY實現李敏勇劉晨王敏???張立950019500295003???95010Sname……sno???數據完整性的類型（2）參照完整性（引用完整性）在插入或刪除數據時，維護表間數據一致性的手段。一般建立在主碼（主鍵）與外部碼（外鍵）之間的關系。在SQL中通過check、default、觸發器(trigger)等實現數據完整性的類型（3）用戶定義的完整性體現實際運用的業務規則。例如：學生成績0~100分職工年齡18~60歲保險金+補貼<基本工資實施數據完整性的途徑約束（constraint）規則（rule）默認值（default）標識列（identity）觸發器（trigger）約束的類型決定使用何種約束約束約束的類型約束的用途是限制輸入到表中的值的范圍。約束是實施數據完整性的首選方法約束的分類列級約束：只對一列起作用的約束。列約束是對某一個特定列的約束，包含在列定義中，直接跟在該列的其他定義之后，用空格分隔，不必指定列名。表級約束：對表中的多列起作用的約束。表約束與列定義相互獨立，不包括在列定義中，通常用于對多個列一起進行約束，與列定義用’,’分隔，定義表約束時必須指出要約束的那些列的名稱。約束的類型列級約束有六種：主鍵Primarykey外鍵foreignkey唯一unique、檢查checck默認default非空/空值notnull/null約束的類型表級約束有四種：主鍵外鍵唯一檢查約束的類型PRIMARYKEY約束UNIQUE約束DEFAULT約束CHECK約束FOREIGNKEY約束級聯參考完整性

PRIMARYKEY約束PRIMARYKEY約束利用表中的一列或多列數據唯一地標識某一行數據每個表只有一個PRIMARYKEY約束PRIMARYKEY約束的值必須是唯一的不允許有空值SQLServer中最多可定義16列作為主鍵直接在列名后增加關鍵字

PRIMARYKEY

CREATETABLEStudent (snochar(5)PRIMARYKEY,snamevarchar(20)notnull,

……

);

PRIMARYKEY約束創建sc表，其主碼為（sno,cno）×???

CREATETABLEsc(snochar(5)PRIMARYKEY，cnochar(1)PRIMARYKEY，gradedecimal(4,1)));

PRIMARYKEY約束在CREATETABLE語句各列定義的最后加:PRIMARYKEY(<屬性名表>)CREATETABLEsc(snochar(5)，cnochar(1)，gradedecimal(4,1),PRIMARYKEY(sno,cno));

CREATETABLEsc1(……….,

constraintPK_SCPRIMARYKEY(sno,cno));約束名刪除表上已定義的主鍵

ALTERTABLESC1

DROPCONSTRAINTPK_SC;在沒有定義主鍵的表上，加上一個主鍵

ALTERTABLESC

ADDCONSTRAINTPK_SCprimarykey(sno,cno);

刪除和添加主鍵

UNIQUE約束UNIQUE約束主要被用來確保不受主鍵約束的列上的數據唯一性。UNIQUE約束的創建和使用指導：可以向表中的多列應用UNIQUE約束允許該列存在NULL值向現有表應用UNIQUE約束時，會驗證現有數據

UNIQUE約束UNIQUE與主鍵約束的區別UNIQUE約束主要用在非主鍵的一列或多列上限制數據惟一的情況，而主鍵是用于惟一標識一行數據UNIQUE約束允許該列上存在NULL值，而主鍵決不允許出現這種情況可以在一個表上設置多個UNIQUE約束，而一個表上只能設置一個主鍵

UNIQUE約束UNIQUE約束的操作方法在創建表時定義UNIQUE約束在已經有數據但沒有重復值的列或列的集合上添加UNIQUE約束修改或刪除表上已定義的UNIQUE約束在創建表時定義UNIQUE約束CREATETABLEtest1

(….,

tnamevarchar(20)unique,tmountint);CREATETABLEtest1

(….,

tnamevarchar(8)constraint

UNIQ_TNAME

unique,tmountint);

創建UNIQUE約束列級UNIQUE約束在創建表時定義UNIQUE約束CREATETABLEtest2

(even_idintprimarykey,even_namechar(20),even_typechar(20),even_timedatetime,

)

創建UNIQUE約束CONSTRAINTUNIQ_EVENUNIQUE(even_type,even_time)要求：在even_type和even_time上共同建立惟一約束表級UNIQUE約束向test1表中的tname

列添加UNIQUE約束

ALTERTABLEtest1

ADDCONSTRAINTUNIQ_TNAME

UNIQUE(tname)刪除表上已定義的UNIQUE約束ALTERTABLEtest1

DROPCONSTRAINTUNIQ_TNAME

添加和刪除UNIQUE約束DEFAULT約束如果一個列的值在插入時沒有指定，DEFAULT約束將自動輸入一個值，可以是預先指定的常量、NULL或者一個系統函數運行時的值

語法：

[CONSTRAINT約束名]DEFAULT約束表達式

DEFAULT約束在創建表時使用default屬性CREATETABLEuserInfo(...…countryvarchar(50)notnullDEFAULT‘China’,……)為已經創建好的表添加default屬性USENorthwind

ALTERTABLEdbo.Customers

ADD

CONSTRAINTDF_contactnameDEFAULT'UNKNOWN'

FORContactName應用DEFAULT約束的幾種情況

CHECK約束CHECK約束通過檢查輸入表列的值來維護值域的完整性。CHECK約束通過對一個邏輯表達式的結果進行判斷來對數據進行檢查。ageintCHECK(age>=18andage<=65)可在一列上設置多個CHECK約束，一個CHECK約束可應用多個列。

CHECK約束CHECK約束的操作方法在創建表時定義CHECK約束在已經建立的表上添加CHECK約束修改或刪除表上已定義的CHECK約束在創建SC表時定義CHECK約束

CreateTableSC (snochar(5)notnull, cnochar(1)notnull, gradedecimal(4,1) primarykey(sno,cno));

創建CHECK約束check(grade>=0andgrade<=100),CreateTableSC (snochar(5)notnull, cnochar(1)notnull, gradedecimal(4,1),primarykey(sno,cno),check(grade>=0andgrade<=100));CreateTableSC(……..,

constraintchk_gmkcheck(grade>=0andgrade<=100));

創建CHECK約束CreateTableStudent (snochar(5)notnull, birthdatedatetimenotnull schooldatedatetimenotnull,primarykey(sno),

);

創建CHECK約束表級CHECK約束check(birthdate<getdate()),check(birthdate<schooldate),列級CHECK約束刪除SC表中的CHECK約束ALTERTABLESC

DROPCONSTRAINTchk_gmk向SC表中添加CHECK約束ALTERTABLESC

ADDCONSTRAINTchk_gmk

check(gmark>=0andgmark<=100)

添加和刪除CHECK約束CHECK約束應用CHECK約束的注意事項一個表可以定義多個CHECK約束，一個列上只允許創建一個列級CHECK約束。列級CHECK約束只能引用被約束的列，表級CHECK約束只能引用同一表中的列。

FOREIGNKEY約束應用FOREIGNKEY約束主要用來維護兩個表之間的一致性關系。當一行新的數據加入到表格中，或對表格中已經存在的外鍵上的數據進行修改時新的數據取值必須：存在于另一張表的主鍵上為NULL當主鍵所在表的數據被另一張表的外鍵引用時，用戶無法對主鍵的數據進行修改或刪除。創建一個新的employee表，并向該表中的dno列添加FOREIGNKEY約束CREATETABLEemployee(enochar(4)notnullPRIMARYKEY,enamechar(8)notnull,sexchar(2)notnull,ageinteger,marrychar(1),titlechar(10),dnochar(2)REFERENCESdepartment(dno));FOREIGNKEY約束FOREIGNKEY約束表employee和department的參照關系父表子表FOREIGNKEY約束

CREATETABLEemployee (enochar(4)notnullPRIMARYKEY, enamevarchar(8)notnull, sexchar(2)notnull, ageint,

marrychar(1),

titlechar(10), dnochar(2),

FOREIGNKEY(dno)REFERENCESdepartment(dno));注意：說明dno為employee的外鍵時，department中的dno必須已被說明為主鍵。FOREIGNKEY約束FOREIGNKEY約束CREATETABLEemployee(enochar(4)notnullPRIMARYKEY,enamevarchar(8)notnull,sexchar(2)notnull,ageint,marrychar(1),titlechar(10),dnochar(2),

CONSTRAINTFRNKEY_DNOFOREIGNKEY(dno)REFERENCESdepartment(dno));FOREIGNKEY約束FOREIGNKEY約束刪除表上已定義的外鍵

ALTERTABLEEMPLOYEE

DROPCONSTRAINTFRNKEY_DNO;在沒有定義外鍵的表上添加外鍵

A