項(xiàng)目4 創(chuàng)建和維護(hù)數(shù)據(jù)庫(kù)中的表((Unsaved-304727220209771028))

1、數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用教程任務(wù)4-1 創(chuàng)建表和表約束n一、數(shù)據(jù)表分類1.系統(tǒng)表：用于存儲(chǔ)服務(wù)器的配置信息、數(shù)據(jù)表的定義信息的一組特殊表，稱為系統(tǒng)表。2.用戶表：用戶自己創(chuàng)建和維護(hù)的表。3.已分區(qū)表：已分區(qū)表是將超大表按照某種業(yè)務(wù)規(guī)則分別存儲(chǔ)在不同的文件組中，以提高性能和方便管理。4.臨時(shí)表：臨時(shí)表是一種因?yàn)闀簳r(shí)需要所產(chǎn)生的數(shù)據(jù)表，它存放在Tempdb數(shù)據(jù)庫(kù)中，當(dāng)使用完臨時(shí)表且關(guān)閉連接后，系統(tǒng)會(huì)自動(dòng)刪除臨時(shí)表。項(xiàng)目項(xiàng)目4 4 創(chuàng)建和維護(hù)數(shù)據(jù)庫(kù)中的表創(chuàng)建和維護(hù)數(shù)據(jù)庫(kù)中的表 n二、數(shù)據(jù)類型 數(shù)據(jù)庫(kù)存儲(chǔ)的對(duì)象主要是數(shù)據(jù)，而現(xiàn)實(shí)中存在著各種不同類型的數(shù)據(jù)，在計(jì)算機(jī)中，數(shù)據(jù)的特征主要表現(xiàn)在數(shù)據(jù)類型上。數(shù)據(jù)類型決

2、定了數(shù)據(jù)的存儲(chǔ)格式、長(zhǎng)度、精度等屬性。n1.二進(jìn)制數(shù)據(jù)SQL Server用Binary、Varbinary和Image三種數(shù)據(jù)類型來(lái)存儲(chǔ)二進(jìn)制數(shù)據(jù)。二進(jìn)制類型可用于存儲(chǔ)聲音圖像等數(shù)字類型的數(shù)據(jù)。n2.數(shù)值型數(shù)據(jù)SQL Server的數(shù)值型數(shù)據(jù)共8種，其中整型數(shù)據(jù)4種，實(shí)型數(shù)據(jù)4種。 (1)字節(jié)型整數(shù)TinyInt。(2)短整型整數(shù)SmaLLInt。 (3)基本整型整數(shù)Int或Integer。(4)長(zhǎng)整型整數(shù)Bigint。(5)近似值實(shí)型浮點(diǎn)數(shù)real。(6)可變精度實(shí)型浮點(diǎn)數(shù)float(n)。(7)精確小數(shù)型數(shù)據(jù)Numeric(p,s)。 (8)精確小數(shù)型數(shù)據(jù)Decimal(p,s)或Dec

3、(p,s)。n3. 文本型數(shù)據(jù)(1)定長(zhǎng)字符型Char(n)。(2)變長(zhǎng)字符型Varchar(n)。(3)定長(zhǎng)統(tǒng)一字符型Nchar(n)。(4)變長(zhǎng)統(tǒng)一字符型Nvarchar(n)。(5)文本類型Text。(6)統(tǒng)一字符文本類型Ntextn4.日期時(shí)間型數(shù)據(jù)n5.貨幣型數(shù)據(jù)n6.位類型數(shù)據(jù)數(shù)據(jù)類型數(shù)據(jù)類型類型說(shuō)明符類型說(shuō)明符占內(nèi)存字節(jié)數(shù)占內(nèi)存字節(jié)數(shù)數(shù)值范圍數(shù)值范圍二進(jìn)制二進(jìn)制Binary(n)Binary(n)定長(zhǎng)定長(zhǎng)n n字節(jié)，超過(guò)截?cái)嘧止?jié)，超過(guò)截?cái)?n80001n8000Varbinary(n)Varbinary(n)變長(zhǎng)，按實(shí)際超過(guò)變長(zhǎng)，按實(shí)際超過(guò)n n字節(jié)截?cái)嘧止?jié)截?cái)?n80001n8

4、000imageimage最大最大2 23131-1-1個(gè)字節(jié)，二進(jìn)制數(shù)個(gè)字節(jié)，二進(jìn)制數(shù) 字符型字符型Char(n)Char(n)定長(zhǎng)，定長(zhǎng)，n n個(gè)字符（字節(jié)）個(gè)字符（字節(jié)）1n80001n8000varchar(n)varchar(n)變長(zhǎng)，按實(shí)際不超過(guò)變長(zhǎng)，按實(shí)際不超過(guò)n n個(gè)字符個(gè)字符1n80001n8000texttext最大最大2 23131-1-1個(gè)字符個(gè)字符 統(tǒng)一字符統(tǒng)一字符nchar(n)nchar(n)定長(zhǎng)，定長(zhǎng)，n n個(gè)個(gè)UnicodeUnicode字符（字符（2 2字節(jié)）字節(jié)）1n40001n4000nvarchar(n)nvarchar(n)變長(zhǎng)，按實(shí)際不超過(guò)變長(zhǎng)，按

5、實(shí)際不超過(guò)n n個(gè)字符個(gè)字符1n40001n4000ntextntext最大最大2 23030-1-1個(gè)個(gè)UnicodeUnicode統(tǒng)一字符統(tǒng)一字符 日期時(shí)間日期時(shí)間datetimedatetime1/1/175312/31/99991/1/175312/31/9999日期時(shí)間日期時(shí)間精確到精確到0.001s,0.001s,用單引號(hào)用單引號(hào)smalldatetimesmalldatetime1/1/19006/6/20791/1/19006/6/2079日期時(shí)間日期時(shí)間精確到分，用單引號(hào)精確到分，用單引號(hào)位型位型bitbit一位二進(jìn)制，只取一位二進(jìn)制，只取0 0、1 1或或nullnull用

6、于邏輯型用于邏輯型整型整型tinyinttinyint1 1字節(jié)字節(jié) 無(wú)符號(hào)整數(shù)無(wú)符號(hào)整數(shù)02550255smallintsmallint2 2字節(jié)字節(jié) 有符號(hào)整數(shù)有符號(hào)整數(shù)-3276832767-3276832767intint4 4字節(jié)字節(jié) 有符號(hào)整數(shù)有符號(hào)整數(shù)-2-23131223131-1-1bigintbigint8 8字節(jié)字節(jié) 有符號(hào)整數(shù)有符號(hào)整數(shù)-2-26363226363-1-1小數(shù)小數(shù)decimal(p,s)decimal(p,s)p p為總位數(shù)，為總位數(shù)，s s為小數(shù)位為小數(shù)位-10-10383810103838-1-1numeric(p,s)numeric(p,s)1p3

7、8,0s531p38,0s53-10-10383810103838-1-1浮點(diǎn)數(shù)浮點(diǎn)數(shù)realreal十進(jìn)制浮點(diǎn)數(shù)十進(jìn)制浮點(diǎn)數(shù)-3.4E383.4E38-3.4E383.4E38float(p)float(p)p p為有效位數(shù)為有效位數(shù)1p531p53-1.79E+3081.79E+308-1.79E+3081.79E+308貨幣貨幣smallmoneysmallmoney214748.3648214748.3647214748.3648214748.3647實(shí)際為實(shí)際為4 4位小數(shù)的位小數(shù)的decimaldecimal類型類型moneymoney9223372036855447.580792

8、23372036855447.5807n三、列屬性 數(shù)據(jù)表的列具有若干屬性，包括是否允許空屬性、默認(rèn)值屬性、標(biāo)識(shí)屬性等。n1.允許空屬性n2.默認(rèn)值屬性n3.標(biāo)識(shí)屬性n四、數(shù)據(jù)的完整性1.實(shí)體完整性 實(shí)體完整性要求表中的每一行數(shù)據(jù)都反映不同的實(shí)體，不能存在相同的數(shù)據(jù)行?？赏ㄟ^(guò)設(shè)置主鍵約束、惟一約束、索引約束或標(biāo)識(shí)列，來(lái)實(shí)現(xiàn)表的實(shí)體完整性。2.域完整性 域完整性約束是指限定列信息的有效性?？赏ㄟ^(guò)限定數(shù)據(jù)類型、檢查約束、默認(rèn)值、非空約束，來(lái)實(shí)現(xiàn)表的域完整性。3.引用完整性 引用完整性約束是用來(lái)保持表之間已定義的關(guān)系，確保插入到表中的數(shù)據(jù)是有效的?？赏ㄟ^(guò)主鍵和外鍵之間的引用關(guān)系來(lái)實(shí)現(xiàn)。 n五、數(shù)據(jù)的

9、約束1.主鍵約束primary key 2.唯一性約束unique3.外鍵約束foreign key4.檢查約束check not nulldefault任務(wù)4-1-1 在SQL Server Management Studio中創(chuàng)建表和表約束n【任務(wù)實(shí)施】 在教學(xué)成績(jī)管理數(shù)據(jù)庫(kù)中，分別創(chuàng)建“學(xué)生信息表”、“課程表”、“成績(jī)表”，其表結(jié)構(gòu)分別如表4-2、4-3、4-4所示。列名列名數(shù)據(jù)類型數(shù)據(jù)類型長(zhǎng)度長(zhǎng)度屬性屬性約束約束是否允許為空是否允許為空值值默認(rèn)值默認(rèn)值標(biāo)識(shí)列標(biāo)識(shí)列學(xué)號(hào)學(xué)號(hào)charchar6 6否否 主鍵主鍵姓名姓名char char 8 8否否 唯一唯一所在系所在系varcharvar

10、char5050否否 專業(yè)名專業(yè)名varcharvarchar5050否否 性別性別charchar2 2否否男男 男或女男或女出生日出生日期期smalldatetismalldatetimeme4 4否否 民族民族varcharvarchar5050是是漢漢 聯(lián)系電聯(lián)系電話話charchar1111否否 8 8位數(shù)字位數(shù)字備注備注texttext3030是是 表4-2 學(xué)生信息表的結(jié)構(gòu)表4-3 課程表的結(jié)構(gòu)列名列名數(shù)據(jù)類數(shù)據(jù)類型型長(zhǎng)度長(zhǎng)度屬性屬性約束約束是否允許為是否允許為空值空值默認(rèn)值默認(rèn)值標(biāo)識(shí)列標(biāo)識(shí)列課程號(hào)課程號(hào)char char 4 4否否 主鍵主鍵課程名課程名varcharvarch

11、ar5050否否 授課教授課教師師charchar8 8是是 開課學(xué)開課學(xué)期期tinyinttinyint 否否1 1 只能為只能為1-61-6學(xué)分學(xué)分tinyinttinyint 是是 列名列名數(shù)據(jù)類數(shù)據(jù)類型型長(zhǎng)度長(zhǎng)度屬性屬性約束約束是否允許為是否允許為空值空值默認(rèn)值默認(rèn)值標(biāo)識(shí)列標(biāo)識(shí)列序號(hào)序號(hào)intint 否否 初始值、增量初始值、增量均為均為1 1 學(xué)號(hào)學(xué)號(hào)char char 6 6否否 主主鍵鍵外鍵外鍵課程號(hào)課程號(hào)charchar4 4否否 外鍵外鍵成績(jī)成績(jī)tinyintinyint t1 1否否 0-1000-100是否重是否重修修charchar2 2否否 表4-4 成績(jī)表的結(jié)構(gòu)命令

12、格式ncreate table 表名 (字段名 類型(長(zhǎng)度) ,n)任務(wù)4-1-2 使用CREATE TABLE語(yǔ)句創(chuàng)建表和表約束 【任務(wù)實(shí)施】-創(chuàng)建學(xué)生信息表 CREATE TABLE 學(xué)生信息表 ( 學(xué)號(hào) CHAR(6) NOT NULL，姓名 CHAR(8) NOT NULL，所在系 VARCHAR(50) NOT NULL，專業(yè)名 VARCHAR(50) NOT NULL， 性別 CHAR(2) NOT NULL DEFAULT 男 ， 出生日期 SMALLDATETIME NOT NULL， 民族 VARCHAR(50) 聯(lián)系電話 CHAR(11)，備注TEXT CONSTRAINT

13、 PK_學(xué)生信息表_學(xué)號(hào) PRIMARY KEY(學(xué)號(hào))， CONSTRAINT UQ_學(xué)生信息表_姓名 UNIQUE(姓名)， CONSTRAINT CK_學(xué)生信息表_性別 CHECK(性別= 男 OR 性別= 女 )， CONSTRAINT CK_學(xué)生信息表_電話 CHECK(聯(lián)系電話LIKE 0-90-90-90-90-90-90-90-9 ) )n-創(chuàng)建課程表CREATE TABLE 課程表 (課程號(hào) CHAR(4) NOT NULL PRIMARY KEY(課程號(hào))，課程名 VARCHAR(50) NOT NULL，授課教師 CHAR(8)，開課學(xué)期 TINYINT NOT NULL

14、 DEFAULT 1，學(xué)分 TINYINT，CONSTRAINT CK_課程表_學(xué)期 CHECK(開課學(xué)期=1 AND開課學(xué)期=0 AND成績(jī)=100)， 學(xué)分 TINYINT PRIMARY KEY(學(xué)號(hào)，課程號(hào))， FOREIGN KEY(課程號(hào)) REFERENCES KC(課程號(hào))任務(wù)4-2 修改表和表約束n任務(wù)任務(wù)4-2-1 在在SQL Server Management Studio中修改表和表中修改表和表約束約束【任務(wù)實(shí)施】 若沒(méi)有關(guān)閉“表設(shè)計(jì)器”，可直接在設(shè)計(jì)器中反復(fù)設(shè)置修改各個(gè)字段；若已經(jīng)關(guān)閉(創(chuàng)建完成)，則可隨時(shí)再打開要修改表的“表設(shè)計(jì)器”，對(duì)表結(jié)構(gòu)進(jìn)行修改。 打開SSMS

15、(SQL Server Management Studio)，依次展開到要修改的數(shù)據(jù)庫(kù)，右擊“學(xué)生信息”表，在彈出的快捷菜單中選擇“修改”命令，即可打開該表的“表設(shè)計(jì)器”。1.修改字段屬性。2.添加新字段。3.刪除字段。4.移動(dòng)字段順序。5.關(guān)閉表設(shè)計(jì)器。n任務(wù)任務(wù)4-2-2 使用使用ALTER TABLE語(yǔ)句修改表和表約束語(yǔ)句修改表和表約束n命令格式：nalter table 表名 add ,n n【任務(wù)實(shí)施】1增加列【例】 在學(xué)生信息表中，增加以下兩列： “籍貫”字段，char(12)，默認(rèn)值為“重慶”。 “email”字段，varchar(30)。 ALTER TABLE 學(xué)生信息表 A

16、DD 籍貫 CHAR(12) CONSTRAINT DF_學(xué)生信息表_籍貫 DEFAULT 重慶 ， EMAIL VARCHAR(30)n 2修改列 在修改數(shù)據(jù)表的列時(shí)，只能修改列的數(shù)據(jù)類型以及列值是否為空。但在下列這些情況下不能修改列的數(shù)據(jù)類型：（1）不能修改類型為text、image、ntext、timestamp的列。（2）不能修改類型為varchar、nvarchar、varbinary的列的數(shù)據(jù)類型，但可增加其長(zhǎng)度。（3）不能修改設(shè)置了主鍵或外鍵或默認(rèn)值或檢查或唯一性約束、包含索引的列的數(shù)據(jù)類型，但可增加其長(zhǎng)度。（4）不能修改用列表達(dá)式定義或被引用在列表達(dá)式中的列。 （5）不能修改復(fù)

17、制列(FOR REPLICATION)。n【例】將課程表中的成績(jī)列的數(shù)據(jù)類型修改為numeric：(4,1)。 ALTER TABLE 課程表 ALTER COLUMN 成績(jī) NUMERIC(4，1)n3添加約束【例】 在學(xué)生信息表的“姓名”列上增加唯一性約束，約束名為UK_學(xué)生信息表_姓名，并忽略對(duì)原有數(shù)據(jù)的約束檢查。 ALTER TABLE 學(xué)生信息表 WITH NOCHECK ADD CONSTRAINT UK_學(xué)生信息表_姓名 UNIQUE(姓名)n4刪除約束【例】 將學(xué)生信息表中的“姓名”列上的約束刪除。 ALTER TABLE 學(xué)生信息表 DROP CONSTRAINT UK_學(xué)生

18、信息表_姓名 n5刪除列【例】 將學(xué)生信息表表中的“籍貫”、“email”列刪除。ALTER TABLE 學(xué)生信息表 DROP CONSTRAINT DF_學(xué)生信息表_籍貫 ALTER TABLE 學(xué)生信息表 DROP， COLUMN 籍貫，EMAIL n6啟用或暫停約束【例】 暫停學(xué)生信息表中的所有約束。 ALTER TABLE 學(xué)生信息表 NOCHECK CONSTRAINT ALL任務(wù)4-3 管理表中的數(shù)據(jù)n任務(wù)任務(wù)4-3-1 在在SQL Server Management Studio中管理中管理數(shù)據(jù)數(shù)據(jù)【任務(wù)實(shí)施任務(wù)實(shí)施】1在SQL Server Management Studio中

19、向表中添加記錄2在SQL Server Management Studio中更新表中的記錄3在SQL Server Management Studio中刪除表中的記錄n任務(wù)任務(wù)4-3-2 使用使用SQL語(yǔ)句管理數(shù)據(jù)語(yǔ)句管理數(shù)據(jù)【任務(wù)實(shí)施】1.使用INSERT語(yǔ)句向表中添加記錄(1)插入一行數(shù)據(jù) INSERT命令的語(yǔ)法如下： INSERT INT0 表名(列名，n) VALUES (表達(dá)式 | NULL | DEFAULT，n)【例】 向?qū)W生信息表插入兩行數(shù)據(jù)。 INSERT INT0 學(xué)生信息表 (學(xué)號(hào)，姓名，所在系，專業(yè)名，性別，出生日期，聯(lián)系電話，備注)VALUES(120104，章程信息

20、技術(shù)，計(jì)算機(jī)應(yīng)用男，1992-5-2l，67674111，學(xué)習(xí)委員) INSERT INTO 學(xué)生信息表 VALUES(120105，田甜，信息技術(shù)，計(jì)算機(jī)應(yīng)用，女1992-7-15，89223476)n(2)使用SELECT子句插入多行數(shù)據(jù)使用SELECT子句的INSERT命令語(yǔ)法如下： INSERT INT0 目的表名(列名，n) SELECT源表名列名，n FROM源表名 ，n WHERE邏輯表達(dá)式【例】 將學(xué)生信息表中的“姓名、性別、民族、聯(lián)系電話”數(shù)據(jù)存入到新表XS中。 INSERT INT0 XS SELECT 姓名，性別，民族，聯(lián)系電話 FROM 學(xué)生信息表n2使用UPDATE語(yǔ)

21、句更新表中的記錄 UPDATE命令的語(yǔ)法如下： UPDATE表名 SET 列名：表達(dá)式 | NULL | DEFAULT ，n) WHERE邏輯表達(dá)式【例】 將成績(jī)表中課程號(hào)為“1001”的不及格的學(xué)生成績(jī)加5分。 UPDATE 成績(jī)表 SET 成績(jī)=成績(jī)+5 WHERE(課程號(hào)=1001AND成績(jī)=0 AND 成績(jī)=100n2.綁定和解綁規(guī)則（1）用存儲(chǔ)過(guò)程sp_bindrule綁定規(guī)則存儲(chǔ)過(guò)程sp_bindrule可以綁定一個(gè)規(guī)則到表的一個(gè)列或一個(gè)用戶自定義數(shù)據(jù)類型上。其語(yǔ)法格式如下： sp_bindrule rulename= 規(guī)則對(duì)象名， objname= object_name【例】 將規(guī)則CJ_rule綁定到表成績(jī)表的成績(jī)列上。EXEC sp_bindrule CJ_rule,成績(jī)表.成績(jī)n（2）用存儲(chǔ)過(guò)程sp_unbindrule解綁規(guī)則 存儲(chǔ)過(guò)程sp_unbindrule可解除規(guī)則與列或用戶自定義數(shù)據(jù)類型的綁定，其語(yǔ)法如下： EXEC sp_unbindrule objname= object_name 【例】 解除規(guī)則CJ_rule與成績(jī)表的成績(jī)列的綁定。 EXEC sp_unbindrule CJ_rule,成績(jī)表.成績(jī)n3.刪除規(guī)則可以在SQL Server Management Studi0窗口的對(duì)象資源管