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

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

2、定了數(shù)據(jù)的存儲格式、長度、精度等屬性。n1.二進制數(shù)據(jù)SQL Server用Binary、Varbinary和Image三種數(shù)據(jù)類型來存儲二進制數(shù)據(jù)。二進制類型可用于存儲聲音圖像等數(shù)字類型的數(shù)據(jù)。n2.數(shù)值型數(shù)據(jù)SQL Server的數(shù)值型數(shù)據(jù)共8種，其中整型數(shù)據(jù)4種，實型數(shù)據(jù)4種。 (1)字節(jié)型整數(shù)TinyInt。(2)短整型整數(shù)SmaLLInt。 (3)基本整型整數(shù)Int或Integer。(4)長整型整數(shù)Bigint。(5)近似值實型浮點數(shù)real。(6)可變精度實型浮點數(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)定長字符型Char(n)。(2)變長字符型Varchar(n)。(3)定長統(tǒng)一字符型Nchar(n)。(4)變長統(tǒng)一字符型Nvarchar(n)。(5)文本類型Text。(6)統(tǒng)一字符文本類型Ntextn4.日期時間型數(shù)據(jù)n5.貨幣型數(shù)據(jù)n6.位類型數(shù)據(jù)數(shù)據(jù)類型數(shù)據(jù)類型類型說明符類型說明符占內(nèi)存字節(jié)數(shù)占內(nèi)存字節(jié)數(shù)數(shù)值范圍數(shù)值范圍二進制二進制Binary(n)Binary(n)定長定長n n字節(jié)，超過截斷字節(jié)，超過截斷1n80001n8000Varbinary(n)Varbinary(n)變長，按實際超過變長，按實際超過n n字節(jié)截斷字節(jié)截斷1n80001n8

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

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

6、于邏輯型用于邏輯型整型整型tinyinttinyint1 1字節(jié)字節(jié) 無符號整數(shù)無符號整數(shù)02550255smallintsmallint2 2字節(jié)字節(jié) 有符號整數(shù)有符號整數(shù)-3276832767-3276832767intint4 4字節(jié)字節(jié) 有符號整數(shù)有符號整數(shù)-2-23131223131-1-1bigintbigint8 8字節(jié)字節(jié) 有符號整數(shù)有符號整數(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浮點數(shù)浮點數(shù)realreal十進制浮點數(shù)十進制浮點數(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實際為實際為4 4位小數(shù)的位小數(shù)的decimaldecimal類型類型moneymoney9223372036855447.580792

8、23372036855447.5807n三、列屬性 數(shù)據(jù)表的列具有若干屬性，包括是否允許空屬性、默認值屬性、標識屬性等。n1.允許空屬性n2.默認值屬性n3.標識屬性n四、數(shù)據(jù)的完整性1.實體完整性 實體完整性要求表中的每一行數(shù)據(jù)都反映不同的實體，不能存在相同的數(shù)據(jù)行?？赏ㄟ^設(shè)置主鍵約束、惟一約束、索引約束或標識列，來實現(xiàn)表的實體完整性。2.域完整性 域完整性約束是指限定列信息的有效性。可通過限定數(shù)據(jù)類型、檢查約束、默認值、非空約束，來實現(xiàn)表的域完整性。3.引用完整性 引用完整性約束是用來保持表之間已定義的關(guān)系，確保插入到表中的數(shù)據(jù)是有效的?？赏ㄟ^主鍵和外鍵之間的引用關(guān)系來實現(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ù)實施】 在教學(xué)成績管理數(shù)據(jù)庫中，分別創(chuàng)建“學(xué)生信息表”、“課程表”、“成績表”，其表結(jié)構(gòu)分別如表4-2、4-3、4-4所示。列名列名數(shù)據(jù)類型數(shù)據(jù)類型長度長度屬性屬性約束約束是否允許為空是否允許為空值值默認值默認值標識列標識列學(xué)號學(xué)號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ù)類型型長度長度屬性屬性約束約束是否允許為是否允許為空值空值默認值默認值標識列標識列課程號課程號char char 4 4否否 主鍵主鍵課程名課程名varcharvarch

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

12、格式ncreate table 表名 (字段名 類型(長度) ,n)任務(wù)4-1-2 使用CREATE TABLE語句創(chuàng)建表和表約束 【任務(wù)實施】-創(chuàng)建學(xué)生信息表 CREATE TABLE 學(xué)生信息表 ( 學(xué)號 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é)號 PRIMARY KEY(學(xué)號)， 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 課程表 (課程號 CHAR(4) NOT NULL PRIMARY KEY(課程號)，課程名 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成績=100)， 學(xué)分 TINYINT PRIMARY KEY(學(xué)號，課程號)， FOREIGN KEY(課程號) REFERENCES KC(課程號)任務(wù)4-2 修改表和表約束n任務(wù)任務(wù)4-2-1 在在SQL Server Management Studio中修改表和表中修改表和表約束約束【任務(wù)實施】 若沒有關(guān)閉“表設(shè)計器”，可直接在設(shè)計器中反復(fù)設(shè)置修改各個字段；若已經(jīng)關(guān)閉(創(chuàng)建完成)，則可隨時再打開要修改表的“表設(shè)計器”，對表結(jié)構(gòu)進行修改。 打開SSMS

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

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

17、制列(FOR REPLICATION)。n【例】將課程表中的成績列的數(shù)據(jù)類型修改為numeric：(4,1)。 ALTER TABLE 課程表 ALTER COLUMN 成績 NUMERIC(4，1)n3添加約束【例】 在學(xué)生信息表的“姓名”列上增加唯一性約束，約束名為UK_學(xué)生信息表_姓名，并忽略對原有數(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ù)實施任務(wù)實施】1在SQL Server Management Studio中

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

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

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