第3講 創(chuàng)建數(shù)據(jù)表和表約束

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

——?jiǎng)?chuàng)建數(shù)據(jù)表和表約束

第3講復(fù)習(xí)請(qǐng)說出下列命令的功能？createdatabaseDropdatabaseSp_helpdbAlterdatabaseaddfile|addfilegroup|addlogfilemodifyfile|modifyfilegroupmodifyname=新庫名removefile|removefilegroup創(chuàng)建數(shù)據(jù)庫修改數(shù)據(jù)庫刪除數(shù)據(jù)庫顯示數(shù)據(jù)庫信息增加數(shù)據(jù)文件增加文件組增加日志文件修改數(shù)據(jù)文件修改文件組修改數(shù)據(jù)庫名刪除數(shù)據(jù)文件刪除文件組數(shù)據(jù)表的創(chuàng)建和管理

——?jiǎng)?chuàng)建數(shù)據(jù)表和表約束

教學(xué)內(nèi)容基礎(chǔ)知識(shí)：設(shè)計(jì)表創(chuàng)建數(shù)據(jù)表創(chuàng)建約束

刪除約束數(shù)據(jù)表的創(chuàng)建和管理

——?jiǎng)?chuàng)建數(shù)據(jù)表和表約束

學(xué)習(xí)目標(biāo)認(rèn)知目標(biāo)：了解SQLServer2005的數(shù)據(jù)類型了解表約束的含義能力目標(biāo):掌握用命令方式創(chuàng)建數(shù)據(jù)表的方法(重點(diǎn))掌握表約束的創(chuàng)建、修改和刪除(重點(diǎn)、難點(diǎn))一、設(shè)計(jì)表

1.

數(shù)據(jù)表的類型系統(tǒng)表用于存儲(chǔ)服務(wù)器的配置信息、數(shù)據(jù)表的定義信息的一組特殊表，稱為系統(tǒng)表。用戶表已分區(qū)表已分區(qū)表是將超大表按照某種業(yè)務(wù)規(guī)則分別存儲(chǔ)在不同的文件組中，以提高性能和方便管理。臨時(shí)表臨時(shí)表是一種因?yàn)闀簳r(shí)需要所產(chǎn)生的數(shù)據(jù)表，它存放在Tempdb數(shù)據(jù)庫中，當(dāng)使用完臨時(shí)表且關(guān)閉連接后，系統(tǒng)會(huì)自動(dòng)刪除臨時(shí)表。一、設(shè)計(jì)表

2.

創(chuàng)建數(shù)據(jù)表前應(yīng)考慮的問題表中要存儲(chǔ)的數(shù)據(jù)類型（用來確定數(shù)據(jù)的取值范圍）表中需要的列數(shù)、每列的類型列是否可以為空是否需要在列上使用約束、默認(rèn)值和規(guī)則需要使用什么樣的索引哪些列作為主鍵、哪些列作為外鍵哪些用戶擁有訪問該數(shù)據(jù)庫或表的權(quán)限

表約束一、設(shè)計(jì)表

3.

數(shù)據(jù)類型占1字節(jié)。存放邏輯值。只能取0、1、null。bit位類型存放固定長度的n個(gè)字符數(shù)據(jù)1~4000。存放可變長度的n個(gè)字符數(shù)據(jù)1~4000。存放最大長度為230-1的字符數(shù)據(jù)nchar(n)nvarchar(n)ntext

Unicode寬字符型占8字節(jié)占4字節(jié)占2字節(jié)占1字節(jié)bigintintsmallintTinyint

整數(shù)類型存放固定長度的字符，n為1~8000個(gè)字符。存放可變長度的字符，n為1~8000個(gè)字符。存放最大長度為231-1的字符數(shù)據(jù)。char(n)varchar(n)text

字符型長度定義標(biāo)識(shí)數(shù)據(jù)類型字符型：1個(gè)字符占1個(gè)字節(jié)。Unicode型：1個(gè)字符占2個(gè)字節(jié)一、設(shè)計(jì)表

3.

數(shù)據(jù)類型占8字節(jié)。占4字節(jié)。Moneysmallmoney貨幣型占8字節(jié)。日期為從1/1/1753到12/31/9999。占4字節(jié)。日期為從1/1/1900到6/6/2079。Datetimesmalldatetime

日期時(shí)間類型定長二進(jìn)制數(shù)據(jù)。n為1~8000字節(jié)。變長二進(jìn)制數(shù)據(jù)。n為1~8000字節(jié)。存放最大長度為231-1的二進(jìn)制數(shù)據(jù)(2G)。Binary(n)Varbinary(n)Image：二進(jìn)制型占8字節(jié)。存儲(chǔ)小數(shù)點(diǎn)位數(shù)不確定的數(shù)據(jù)占4字節(jié)。存儲(chǔ)小數(shù)點(diǎn)位數(shù)不確定的數(shù)據(jù)Float(n)Real

浮點(diǎn)數(shù)類型占2~17字節(jié)。存儲(chǔ)小數(shù)點(diǎn)位數(shù)確定的數(shù)據(jù)占2~17字節(jié)。存儲(chǔ)小數(shù)點(diǎn)位數(shù)確定的數(shù)據(jù)Decimal(p,s)Numeric(p,s)精確數(shù)類型存放最大長度為231-1的二進(jìn)制數(shù)據(jù)。image圖像型二、創(chuàng)建數(shù)據(jù)表

1.創(chuàng)建簡單的數(shù)據(jù)表命令格式createtable

表名

(字段名類型[(長度)][,……n])實(shí)例1：在xscj庫中,創(chuàng)建一個(gè)簡單的表xsqk。createtable

xscj.dbo.xsqk

(學(xué)號(hào)

char(6),

姓名

char(8),

性別

bit

,

電話

char(11))數(shù)據(jù)庫名,可省略所有者名,可省略表名方法2：use

xscjcreatetable

xsqk……二、創(chuàng)建數(shù)據(jù)表

2.

列屬性類型空值屬性默認(rèn)時(shí)，列的值為null，表示允許該列為空。如果值為notnull，表示不允許該列為空。默認(rèn)值屬性標(biāo)識(shí)屬性為標(biāo)識(shí)屬性的列，其值由系統(tǒng)自動(dòng)填充。二、創(chuàng)建數(shù)據(jù)表

3.

表約束含義約束定義了關(guān)于允許什么數(shù)據(jù)進(jìn)入數(shù)據(jù)庫的原則。目的為了防止列出現(xiàn)非法數(shù)據(jù)，以保證數(shù)據(jù)中數(shù)據(jù)的一致性和完整性。二、創(chuàng)建數(shù)據(jù)表

3.

表約束類型主鍵（PRIMARYKEY）約束惟一（UNIQUE）約束外鍵（FOREIGNKEY）約束檢查（CHECK）約束說明：非空和默認(rèn)值也可看成是約束。創(chuàng)建表約束的方法新建表時(shí)，在單列后創(chuàng)建約束在所有列之后，再創(chuàng)建約束表已存在，只能通過修改表，添加約束實(shí)現(xiàn)實(shí)體完整性實(shí)現(xiàn)參照完整性實(shí)現(xiàn)域完整性二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建表約束—(1)非空值約束空值（NULL）就是尚不知道或不確定的數(shù)據(jù)值，它不同于０或空格。用戶常常將不確定的列值定義為空值。實(shí)例2：在xscj庫中,創(chuàng)建“學(xué)號(hào)”非空約束的表xsqk。createtable

xsqk

(學(xué)號(hào)

char(6)notnull,

姓名

char(8),

性別

bit

,

電話

char(11))若xscj不是當(dāng)前庫，應(yīng)先輸入：use

xscj默認(rèn)情況下為空值。二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束—(1)非空值約束課堂練習(xí)1：在xscj庫中,創(chuàng)建“課程號(hào)”非空的課程表kc(P88)。

usexscjcreatetablekc(課程號(hào)char(3)notnull

,

課程名char(20),

授課教師

char(8),

開課學(xué)期tinyint)二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束—(2)主鍵約束主鍵(PRIMARYKEY)約束的作用：不允許輸入重復(fù)的值不能取空值

當(dāng)主鍵是由多個(gè)屬性組成時(shí)：某一屬性上的數(shù)據(jù)可以重復(fù)，但其組合必須是惟一的；每個(gè)屬性的值都不能為空。一個(gè)表上只能有一個(gè)主鍵。二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束—(2)主鍵約束實(shí)例3：在學(xué)生情況表xsqk中，添加“學(xué)號(hào)”的主鍵約束,主鍵約束命名為pk_xsqk_xh。createtable

xsqk

(學(xué)號(hào)char

(6),

姓名char(8),

性別

bit,

電話char(11)

constraint

pk_xsqk_xh

primarykey(學(xué)號(hào)))主鍵約束名主鍵：保證了學(xué)號(hào)不能為空，且不重復(fù)。二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束—(2)主鍵約束課堂練習(xí)2：在課程表kc中,添加“課程號(hào)”的主鍵約束，主鍵約束命名為pk_kc_kh。

usexscjaltertablekcaddconstraintpk_kc_khprimarykey(課程號(hào))二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束—(3)惟一性約束惟一性（UNIQUE）約束的作用：保證列中不會(huì)出現(xiàn)重復(fù)的數(shù)據(jù)。主鍵約束與惟一性約束的區(qū)別：一個(gè)表中只能定義一個(gè)主鍵約束，但可以定義多個(gè)惟一約束。定義了惟一約束的列數(shù)據(jù)可以為空值，而定義了主鍵約束的列數(shù)據(jù)不能為空值。二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束—(3)惟一性約束實(shí)例4：在xsqk表中，創(chuàng)建“電話”字段的惟一性約束,并且將約束命名為uq_xsqk_dh。altertable

xsqk

add

constraint

uq_xsqk_dh

unique(電話)惟一性約束名惟一性：保證了“電話”列的取值不能相同。二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束—(4)外鍵約束外鍵（FOREIGNKEY）約束的作用：用于建立和強(qiáng)制兩個(gè)表間的關(guān)聯(lián)，限制外鍵的取值必須是主表的主鍵值。實(shí)例5：在xscj庫中，按P91表4-4創(chuàng)建

xs_kc表。二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束—(4)外鍵約束usexscjcreatetable

xs_kc(學(xué)號(hào)

char(6)notnullreferences

xsqk(學(xué)號(hào)),

課程號(hào)char(3),

成績

tinyintprimarykey(學(xué)號(hào),課程號(hào)),

foreignkey(課程號(hào))references

kc(課程號(hào)))已設(shè)置外鍵約束已設(shè)置外鍵約束二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束—(5)檢查約束檢查（CHECK）約束的作用：用來限制列上可以接受的數(shù)據(jù)值。使用邏輯表達(dá)式來判斷數(shù)據(jù)合法性。實(shí)例6：在xsqk表中，設(shè)置名為ck_xsqk_xb的檢查約束，該約束限制“性別”為0或1。altertable

xsqk

add

constraint

ck_xsqk_xb

check(性別=1or性別=0)二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束—(5)檢查約束課堂練習(xí)3：在學(xué)生與課程表xs_kc中,添加名ck_xs_kc_cj的檢查約束，該約束限制“成績”在0到100之間。

usexscjaltertablexs_kcaddconstraintck_xs_kc_cjcheck(成績>=0and成績<=100)二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束—(6)默認(rèn)約束默認(rèn)（DEFAULT）約束的作用：當(dāng)列值未確定且該列又不能為空時(shí)，可由系統(tǒng)自動(dòng)為該列添加一個(gè)值。實(shí)例7：在xsqk表中，設(shè)置名為df_xsqk_xb的默認(rèn)約束，該約束使“性別”的默認(rèn)值為1。altertable

xsqk

add

constraint

df_xsqk_xb

default1for性別二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束說明：所有約束可一次創(chuàng)建完成。usexscj--從默認(rèn)的master數(shù)據(jù)庫切換到xsqk庫createtable

xsqk

(學(xué)號(hào)

char(6)notnull,

姓名

char(8)notnull,

性別

bit,電話

char(11))altertablexsqkaddconstraint

pk_xsqk_xh

primarykey(學(xué)號(hào)),constraint

df_xsqk_xb

default1for

性別,constraint

ck_xsqk_xb

check(性別=1or性別=0),constraint

uq_xsqk_dh

unique(電話)二、創(chuàng)建數(shù)據(jù)表

4.創(chuàng)建約束說明：所有約束可直接創(chuàng)建在列定義之后。use

xscjcreatetable

xsqk

(學(xué)號(hào)char(6)primarykey(學(xué)號(hào))check(學(xué)號(hào)

like‘[0-9][0-9][0-9][0-9][0-9][0-9]’),

姓名char(8)notnull,

性別bit

default1check(性別=0or性別=1),

電話char(11)unique(電話))該方式創(chuàng)建的約束由系統(tǒng)自動(dòng)命名