創(chuàng)建和管理數(shù)據表

第四章

創(chuàng)建和管理數(shù)據表預習檢查至少說出SQLServer中三種數(shù)據類型舉例說明在哪些情況下要用到檢查約束什么是標識列，其作用是什么？什么是數(shù)據完整性？創(chuàng)建數(shù)據表的T-SQL語句是什么？

本章目標掌握SQLServer常用數(shù)據類型。能夠使用圖形工具SSMS和T-SQL語句創(chuàng)建數(shù)據表。能夠為表創(chuàng)建主鍵、外鍵、設置默認值、建立檢查約束和非空約束、創(chuàng)建標識列等以確保數(shù)據完整性。SQLServer常用數(shù)據類型數(shù)據表用于存儲各種數(shù)據。不同類型的數(shù)據又適合不同的存儲方式，從而適合不同的數(shù)據類型。首先應該了解需要存儲的數(shù)據在SQLServer數(shù)據庫中的對應哪種數(shù)據類型，或者說這些數(shù)據適合以何種數(shù)據類型存儲。二進制數(shù)據類型二進制數(shù)據類型

Binary[(n)]n位固定長度的二進制數(shù)據。n的取值范圍可從

1到

8000當數(shù)據列內容長度基本相同時使用varbinary[(n)]可變長度的二進制數(shù)據。n的取值范圍可從

1到

8000當數(shù)據列內容長度不同且差別較大時使用image可變長度的二進制數(shù)據。最多

2GB用于存儲文件，圖片或者視頻等大文件文本數(shù)據類型

Char(n)n位固定長度的非Unicode字符串。n取值范圍為1到

8000存儲特定長度的數(shù)據，例如11位手機號碼varchar(n)最大長度為n的可變長度的非Unicode字符串。n取值范圍為1到

8000存儲可變長度文本。根據實際存儲數(shù)據長度分配空間text可變長度的字符串。最多

2GB字符數(shù)據存儲大型的非統(tǒng)一編碼型字符數(shù)據nchar(n)固定長度的

Unicode數(shù)據。最多

4,000個字符存儲特定長度的統(tǒng)一編碼字符型數(shù)據nvarchar(n)可變長度的

Unicode數(shù)據。最多

4,000個字符存儲可變長度的統(tǒng)一編碼字符型數(shù)據ntext可變長度的Unicode數(shù)據。最多

2GB字符數(shù)據存儲大型的統(tǒng)一編碼字符型數(shù)據文本數(shù)據類型Bit數(shù)據類型Bit數(shù)據類型bit允許

0、1或

NULL用于存儲只有兩種可能值的數(shù)據，如真假、男女

數(shù)字數(shù)據類型tinyint允許從0到255之間的整數(shù)smallint-2^15(-32768)到2^15(32767)之間的整數(shù)int從-2^31到2^31之間的整數(shù)decimal(p,s)固定精度和比例的數(shù)字。允許從

-10^38+1到10^38-1之間的數(shù)字。p參數(shù)指示可以存儲的最大位數(shù)（小數(shù)點左側和右側之和）。p必須是

1到

38之間的值。默認是

18。s參數(shù)指示小數(shù)點右側小數(shù)部分存儲的最大位數(shù)。s必須是

0到

p之間的值。默認是

0numeric(p,s)decimal的同義詞smallmoney介于-214,748.3648和

214,748.3647之間的貨幣數(shù)據money從-2^63到2^63-1的貨幣數(shù)據float[(n)]從

-1.79E+308到

1.79E+308的浮動精度數(shù)字，n介于1-53之間，如果

1<=n<=24，則將

n視為

24。如果

25<=n<=53，則將

n視為

53。real相當于float(24)數(shù)字數(shù)據類型時間和日期數(shù)據類型日期和時間數(shù)據類型datetime表示日期和時間。從

1753年

1月

1日到

9999年

12月

31日，精度為

3.33毫秒

smalldatetime表示日期和時間。從

1900年

1月

1日到

2079年

6月

6日，精度為

1分鐘

數(shù)據類型示例身份證號：可能是18位或15位，可以選擇 nvarchar(18)姓名：中國名字大多三個字，且可變長度，可選

擇可以選擇nvarchar(10)性別：只有男女，可選擇bit或char(1)家庭地址：可變長度的字符串，nvarchar(200)電話：可變長度的字符串，nvarchar(20)生日：可選擇日期數(shù)據類型，datatime存款余額：可選擇money數(shù)據類型照片：可選擇image數(shù)據類型數(shù)據完整性的問題大多是由于設計引起的。數(shù)據存放在表中，創(chuàng)建表的時候，就應當保證以后數(shù)據輸入是正確的——錯誤的數(shù)據、不符合要求的數(shù)據不允許輸入。數(shù)據完整性數(shù)據完整性+=準確性可靠性數(shù)據完整性(Integrity)是指數(shù)據的準確性（Accuracy）和可靠性（Reliability）。保證數(shù)據的完整性=實施完整性約束輸入的類型是否正確？——年齡必須是數(shù)字輸入的格式是否正確？——身份證號碼必須是18位是否在允許的范圍內？——性別只能是“男”或者“女”是否存在重復輸入？——學員信息輸入了兩次是否符合其他特定要求？——學生的出生日期必須小于入學日期……列值要求（約束）整行要求（約束）完整性問題舉例數(shù)據列表一數(shù)據行表二域完整性實體完整性引用完整性自定義完整性提供四種類型的約束保證完整性四種完整性約束實體完整性河南洛陽趙可10010016新疆和田張麗10010015上海徐匯區(qū)朱朱10010014湖南長沙吳蘭10010013山東青島劉山10010012….地址姓名學號上海徐匯區(qū)朱朱0010014×約束方法：唯一約束、主鍵約束、標識列實體完整性(EntityIntegrity)指表中數(shù)據行的完整性，主要用于保證每條記錄非空、唯一且不重復。域完整性河北承德趙亮3488888999×約束方法：限制數(shù)據類型、檢查約束、外鍵約束、默認值、非空約束學號要求8位字符河南洛陽趙可10010016新疆和田張麗10010015上海徐匯區(qū)朱朱10010014湖南長沙吳蘭10010013山東青島劉山10010012….地址姓名學號域完整性(DomainIntegrity)又稱列完整性，保證列數(shù)據具有正確的數(shù)據類型、格式和有效的取值范圍、精度等。9810010022C#×約束方法：外鍵約束科目學號分數(shù)高數(shù)1001001288外語1001001374C#1001001267java1001001381數(shù)據庫1001001698參照(引用)完整性河南洛陽趙可10010016新疆和田張麗10010015上海徐匯區(qū)朱朱10010014湖南長沙吳蘭10010013山東青島劉山10010012地址姓名學號參照完整性

(ReferentialIntegrity)

又稱引用完整性，保證主表中的數(shù)據與從表中數(shù)據的一致性參照完整性的操作要求參照完整性禁止以下幾種操作：(1)禁止外鍵列中插入主鍵列中沒有的值；即外鍵的取值必須是主表中主鍵的有效值，或者說向從表中插入數(shù)據時只能添加主表中已經存在的關聯(lián)記錄的相關信息。例如銀行開戶只能給儲戶信息表中已經存在的儲戶開戶；學生成績表中不能添加學生信息表中不存在的學號的成績信息。(2)禁止只修改主表中主鍵列的值，而不修改從表中相應的外鍵列的值；即不能更改主表中的值而導致從表中的記錄孤立無意義。例如學生信息表中一個學生學號變了，那么學生成績表中該學生的學號也必須相應改變。(3)禁止先從主表中刪除數(shù)據行；即主表中刪除數(shù)據時，必須首先刪除從表中的關聯(lián)記錄，否則從表中會遺留大量無意義的記錄。自定義完整性約束方法：規(guī)則、存儲過程、觸發(fā)器編號姓名信用….00192張三700288李四612333王五893000馬六-10×觸發(fā)器：檢查信用值用戶自定義完整性(User-definedIntegrity)是對列數(shù)據的約束，允許用戶定義不屬于以上三種完整性分類的特定的規(guī)則，包括列的值域、列的類型和列的有效規(guī)則等約束，這些是由確定關系結構時所定義的字段的屬性決定的。用戶編號用戶姓名會員證00288張三vip00112333李四vip00290111王五vip003示例：信用不良的客戶“馬六”不能辦理會員證。創(chuàng)建數(shù)據表通過三大范式規(guī)范數(shù)據庫設計并繪制數(shù)據庫模型圖后，就可以在數(shù)據庫中創(chuàng)建數(shù)據表。創(chuàng)建表的基本步驟：第一步，確定表中有哪些列；第二步，確定每列的數(shù)據類型；第三步，給表添加各種約束；第四步，創(chuàng)建各表之間的關系。使用圖形化向導創(chuàng)建數(shù)據庫表操作演示：創(chuàng)建數(shù)據庫表思考：空值NULL身份證允許為空嗎？姓名允許為空嗎?家庭地址允許為空嗎?電子郵件信息允許為空嗎?性別允許為空嗎?保存并命名數(shù)據表操作演示：保存數(shù)據庫表在主鍵列輸入的數(shù)值，允許為空嗎?一個表的主鍵可以有多個列組成嗎?思考-主鍵最少性盡量選擇單個鍵作為主鍵穩(wěn)定性盡量選擇數(shù)值更新少的列作為主鍵選擇主鍵的原則為depositor表創(chuàng)建主鍵操作演示：設置主鍵為depositor表添加默認值示例：為gender列設置默認值1。操作演示：設置默認值操作演示：建立檢查約束為depositor表建立檢查約束示例：為depositorID建立檢查約束，要求該列長度只能是15位或18位。標識列允許為字符數(shù)據類型嗎？如果標識列A的初始值為1，增長量為2，則輸入三行數(shù)據以后，再刪除兩行，下次再輸入數(shù)據行的時候，標識值從多少開始？思考-標識列設置標識列操作演示：設置標識列建立表間(主外鍵)關系操作演示：建立主外鍵關系數(shù)據庫關系圖操作演示：建立數(shù)據庫關系圖利用主外鍵的對應、圖形化表示表之間的關系當主表中沒有對應的記錄時，不能將記錄添加到子表

——成績表中不能出現(xiàn)在學員信息表中不存在的學號不能更改主表中的值而導致子表中的記錄孤立

——把學員信息表中的學號改變了，學員成績表中的學號也應當隨之改變子表存在與主表對應的記錄，不能從主表中刪除該行

——不能把有成績的學員刪除了刪除主表前，先刪子表

——先刪學員成績表、后刪除學員信息表主外鍵建立后注意事項使用T-SQL語句創(chuàng)建數(shù)據表建表的語法CREATETABLE

表名

(

列名1數(shù)據類型列的特征，列名2數(shù)據類型列的特征，

...)列的特征：包括該列是否為空（NULL）、是否是標識列（identity）、是否有默認值(default)、是否為主鍵（PrimaryKey）等創(chuàng)建表StudentUSE

StudentInfo

--將當前數(shù)據庫設置為StudentInfoGOCREATETABLE

Student

--創(chuàng)建學生信息表(

StudentID

nvarchar(20)NOTNULL, --學號，非空（必填）

password

nvarchar(50)NOTNULL,

--密碼，非空（必填）Namenvarchar(50)NOTNULL, --姓名，非空（必填）genderbitNOTNULL,--性別，非空（必填）

GradeIdintNOTNULL,

--年級號（必填）

Phonenvarchar(50)

NOTNULL,--電話

birthday

datetimeNOTNULL,

--出生日期（必填）Address

nvarchar(255),

--地址

Emailnvarchar(50),

--郵件帳號stuIDENTITY

varchar(18)

NOTNULL

--身份證號)GO布爾值TRUE和FALSE可以轉換為以下bit值：TRUE轉換為1，F(xiàn)ALSE轉換為0演示示例：創(chuàng)建學生信息表Student創(chuàng)建Student表使用T-SQL語句修改數(shù)據表1、ALTERTABLE[表名]ADDCOLUMN[列名]數(shù)據類型列特征 --增加一個新列2、ALTERTABLE[表名]addCONSTRAINT[約束名] DEFAULT默認值FOR[列名] --設置默認值3、ALTERTABLE[表名]RENAMEcolumn[列名]TO[新列名] --修改列名4、ALTERTABLE[表名]RENAMETO[新表名] --修改表名5、ALTERTABLE[表名]ADDCONSTRAINT[約束名稱]約束類型(列) --添加約束常用的數(shù)據表修改語句：回顧數(shù)據完整性實體完整性能夠唯一標識表中的每一條記錄實現(xiàn)方式：主鍵、唯一鍵、IDENTITY屬性域完整性表中特定列數(shù)據的有效性，確保不會輸入無效的值實現(xiàn)方式：類型、缺省值、約束、空值引用完整性維護表間數(shù)據的有效性、完整性實現(xiàn)方式：建立外鍵聯(lián)系另一表的主鍵自定義完整性根據業(yè)務處理流程定義的特定業(yè)務規(guī)則實現(xiàn)方式：存儲過程、觸發(fā)器、規(guī)則回顧SQLServer的約束使用約束的目的是確保表中數(shù)據完整性。常用的約束類型有哪些？主鍵約束（PrimaryKeyConstraint）：要求主鍵列數(shù)據唯一，并且不允許為空唯一約束（UniqueConstraint）：要求該列數(shù)據唯一，允許為空，但只能出現(xiàn)一個空值檢查約束（CheckConstraint）：某列取值范圍限制、格式限制等默認約束（DefaultConstraint）：某列的默認值外鍵約束（ForeignKeyConstraint）：用于兩表間建立關系，需要指定引用主表的哪列主鍵約束與唯一約束的區(qū)別：1.主鍵約束所在的列不允許有空值，唯一約束所在的列允許空值2.每個表中可以有一個主鍵，多個唯一鍵使用T-SQL語句添加約束添加約束的語法ALTERTABLE

表名

ADDCONSTRAINT

約束名約束類型具體的約束說明約束名的取名規(guī)則推薦采用：約束類型_約束列主鍵（PrimaryKey）約束：如PK_stuNo唯一（UniqueKey）約束：如

UQ_stuID默認（DefaultKey）約束：如DF_stuAddress檢查（CheckKey）約束：如

CK_stuBornDate外鍵（ForeignKey）約束：如FK_stuNo

為表添加約束ALTERTABLE

Student

ADDCONSTRAINTPK_StuIDPRIMARYKEY(StudentID)ALTERTABLE

Student

ADDCONSTRAINTUQ_IdentityUNIQUE(stuIdentity)ALTERTABLE

Student

ADDCONSTRAINTDF_AddressDEFAULT('地址不詳')FORAddressALTERTABLE

Student

ADDCONSTRAINTCK_birthday

CHECK(birthday>‘1980-01-01’)

ALTERTABLE

Student

ADDCONSTRAINTFK_Student_Grade

FOREIGNKEY(GradeID)REFERENCESGrade(GradeID)ALTERTABLESubject

WITHNOCHECK

--檢查約束（學時必須大于等于0）

ADDCONSTRAINTCK_ClassHourCHECK(ClassHour>=0)GO添加主鍵約束（StudentID作為主鍵）添加唯一約束（因為每人的身份證號全國唯一）添加默認約束（如果地址不填，默認為“地址不詳”）添加檢查check約束，要求出生日期在1980-1-1之后添加外鍵約束（主表Grade和從表Student建立關系，關聯(lián)列為GradeID）WITHNOCHECK對表中已有數(shù)據不做約束檢查，因此不能保證表中數(shù)據的正確性建議：創(chuàng)建表結構后應立即添加約束，不要馬上插入數(shù)據，以避免插入的數(shù)據不符合約束要求，保證表中數(shù)據滿足約束