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

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

3、(p,s)。n3. 文本型數據(1)定長字符型Char(n)。(2)變長字符型Varchar(n)。(3)定長統一字符型Nchar(n)。(4)變長統一字符型Nvarchar(n)。(5)文本類型Text。(6)統一字符文本類型Ntextn4.日期時間型數據n5.貨幣型數據n6.位類型數據數據類型類型說明符占內存字節數數值范圍二進制Binary(n)定長n字節，超過截斷1n8000Varbinary(n)變長，按實際超過n字節截斷1n8000image最大231-1個字節，二進制數 字符型Char(n)定長，n個字符（字節）1n8000varchar(n)變長，按實際不超過n個字符1n8000

4、text最大231-1個字符 統一字符nchar(n)定長，n個Unicode字符（2字節）1n4000nvarchar(n)變長，按實際不超過n個字符1n4000ntext最大230-1個Unicode統一字符 日期時間datetime1/1/175312/31/9999日期時間精確到0.001s,用單引號smalldatetime1/1/19006/6/2079日期時間精確到分，用單引號位型bit一位二進制，只取0、1或null用于邏輯型整型tinyint1字節 無符號整數0255smallint2字節 有符號整數-3276832767int4字節 有符號整數-231231-1bigint

5、8字節 有符號整數-263263-1小數decimal(p,s)p為總位數，s為小數位-10381038-1numeric(p,s)1p38,0s53-10381038-1浮點數real十進制浮點數-3.4E383.4E38float(p)p為有效位數1p53-1.79E+3081.79E+308貨幣smallmoney214748.3648214748.3647實際為4位小數的decimal類型money9223372036855447.5807n三、列屬性 數據表的列具有若干屬性，包括是否允許空屬性、默認值屬性、標識屬性等。n1.允許空屬性n2.默認值屬性n3.標識屬性n四、數據的完整性1

6、.實體完整性 實體完整性要求表中的每一行數據都反映不同的實體，不能存在相同的數據行?？赏ㄟ^設置主鍵約束、惟一約束、索引約束或標識列，來實現表的實體完整性。2.域完整性 域完整性約束是指限定列信息的有效性?？赏ㄟ^限定數據類型、檢查約束、默認值、非空約束，來實現表的域完整性。3.引用完整性 引用完整性約束是用來保持表之間已定義的關系，確保插入到表中的數據是有效的?？赏ㄟ^主鍵和外鍵之間的引用關系來實現。 n五、數據的約束1.主鍵約束primary key 2.唯一性約束unique3.外鍵約束foreign key4.檢查約束check not nulldefault任務4-1-1 在SQL Ser

7、ver Management Studio中創建表和表約束n【任務實施】 在教學成績管理數據庫中，分別創建“學生信息表”、“課程表”、“成績表”，其表結構分別如表4-2、4-3、4-4所示。列名數據類型長度屬性約束是否允許為空值默認值標識列學號char6否 主鍵姓名char 8否 唯一所在系varchar50否 專業名varchar50否 性別char2否男 男或女出生日期smalldatetime4否 民族varchar50是漢 聯系電話char11否 8位數字備注text30是 表4-2 學生信息表的結構表4-3 課程表的結構列名數據類型長度屬性約束是否允許為空值默認值標識列課程號char

8、 4否 主鍵課程名varchar50否 授課教師char8是 開課學期tinyint 否1 只能為1-6學分tinyint 是 列名數據類型長度屬性約束是否允許為空值默認值標識列序號int 否 初始值、增量均為1 學號char 6否 主鍵外鍵課程號char4否 外鍵成績tinyint1否 0-100是否重修char2否 表4-4 成績表的結構命令格式ncreate table 表名 (字段名 類型(長度) ,n)任務4-1-2 使用CREATE TABLE語句創建表和表約束 【任務實施】-創建學生信息表 CREATE TABLE 學生信息表 ( 學號 CHAR(6) NOT NULL，姓名 C

9、HAR(8) NOT NULL，所在系 VARCHAR(50) NOT NULL，專業名 VARCHAR(50) NOT NULL， 性別 CHAR(2) NOT NULL DEFAULT 男 ， 出生日期 SMALLDATETIME NOT NULL， 民族 VARCHAR(50) 聯系電話 CHAR(11)，備注TEXT CONSTRAINT PK_學生信息表_學號 PRIMARY KEY(學號)， CONSTRAINT UQ_學生信息表_姓名 UNIQUE(姓名)， CONSTRAINT CK_學生信息表_性別 CHECK(性別= 男 OR 性別= 女 )， CONSTRAINT CK_

10、學生信息表_電話 CHECK(聯系電話LIKE 0-90-90-90-90-90-90-90-9 ) )n-創建課程表CREATE TABLE 課程表 (課程號 CHAR(4) NOT NULL PRIMARY KEY(課程號)，課程名 VARCHAR(50) NOT NULL，授課教師 CHAR(8)，開課學期 TINYINT NOT NULL DEFAULT 1，學分 TINYINT，CONSTRAINT CK_課程表_學期 CHECK(開課學期=1 AND開課學期=0 AND成績=100)， 學分 TINYINT PRIMARY KEY(學號，課程號)， FOREIGN KEY(課程號)

11、 REFERENCES KC(課程號)任務4-2 修改表和表約束n任務任務4-2-1 在在SQL Server Management Studio中修改表和表中修改表和表約束約束【任務實施】 若沒有關閉“表設計器”，可直接在設計器中反復設置修改各個字段；若已經關閉(創建完成)，則可隨時再打開要修改表的“表設計器”，對表結構進行修改。 打開SSMS(SQL Server Management Studio)，依次展開到要修改的數據庫，右擊“學生信息”表，在彈出的快捷菜單中選擇“修改”命令，即可打開該表的“表設計器”。1.修改字段屬性。2.添加新字段。3.刪除字段。4.移動字段順序。5.關閉表設計

12、器。n任務任務4-2-2 使用使用ALTER TABLE語句修改表和表約束語句修改表和表約束n命令格式：nalter table 表名 add ,n n【任務實施】1增加列【例】 在學生信息表中，增加以下兩列： “籍貫”字段，char(12)，默認值為“重慶”。 “email”字段，varchar(30)。 ALTER TABLE 學生信息表 ADD 籍貫 CHAR(12) CONSTRAINT DF_學生信息表_籍貫 DEFAULT 重慶 ， EMAIL VARCHAR(30)n 2修改列 在修改數據表的列時，只能修改列的數據類型以及列值是否為空。但在下列這些情況下不能修改列的數據類型：（1

13、）不能修改類型為text、image、ntext、timestamp的列。（2）不能修改類型為varchar、nvarchar、varbinary的列的數據類型，但可增加其長度。（3）不能修改設置了主鍵或外鍵或默認值或檢查或唯一性約束、包含索引的列的數據類型，但可增加其長度。（4）不能修改用列表達式定義或被引用在列表達式中的列。 （5）不能修改復制列(FOR REPLICATION)。n【例】將課程表中的成績列的數據類型修改為numeric：(4,1)。 ALTER TABLE 課程表 ALTER COLUMN 成績 NUMERIC(4，1)n3添加約束【例】 在學生信息表的“姓名”列上增加唯

14、一性約束，約束名為UK_學生信息表_姓名，并忽略對原有數據的約束檢查。 ALTER TABLE 學生信息表 WITH NOCHECK ADD CONSTRAINT UK_學生信息表_姓名 UNIQUE(姓名)n4刪除約束【例】 將學生信息表中的“姓名”列上的約束刪除。 ALTER TABLE 學生信息表 DROP CONSTRAINT UK_學生信息表_姓名 n5刪除列【例】 將學生信息表表中的“籍貫”、“email”列刪除。ALTER TABLE 學生信息表 DROP CONSTRAINT DF_學生信息表_籍貫 ALTER TABLE 學生信息表 DROP， COLUMN 籍貫，EMAIL

15、 n6啟用或暫停約束【例】 暫停學生信息表中的所有約束。 ALTER TABLE 學生信息表 NOCHECK CONSTRAINT ALL任務4-3 管理表中的數據n任務任務4-3-1 在在SQL Server Management Studio中管理中管理數據數據【任務實施任務實施】1在SQL Server Management Studio中向表中添加記錄2在SQL Server Management Studio中更新表中的記錄3在SQL Server Management Studio中刪除表中的記錄n任務任務4-3-2 使用使用SQL語句管理數據語句管理數據【任務實施】1.使用INS

16、ERT語句向表中添加記錄(1)插入一行數據 INSERT命令的語法如下： INSERT INT0 表名(列名，n) VALUES (表達式 | NULL | DEFAULT，n)【例】 向學生信息表插入兩行數據。 INSERT INT0 學生信息表 (學號，姓名，所在系，專業名，性別，出生日期，聯系電話，備注)VALUES(120104，章程信息技術，計算機應用男，1992-5-2l，67674111，學習委員) INSERT INTO 學生信息表 VALUES(120105，田甜，信息技術，計算機應用，女1992-7-15，89223476)n(2)使用SELECT子句插入多行數據使用SEL

17、ECT子句的INSERT命令語法如下： INSERT INT0 目的表名(列名，n) SELECT源表名列名，n FROM源表名 ，n WHERE邏輯表達式【例】 將學生信息表中的“姓名、性別、民族、聯系電話”數據存入到新表XS中。 INSERT INT0 XS SELECT 姓名，性別，民族，聯系電話 FROM 學生信息表n2使用UPDATE語句更新表中的記錄 UPDATE命令的語法如下： UPDATE表名 SET 列名：表達式 | NULL | DEFAULT ，n) WHERE邏輯表達式【例】 將成績表中課程號為“1001”的不及格的學生成績加5分。 UPDATE 成績表 SET 成績=

18、成績+5 WHERE(課程號=1001AND成績=0 AND 成績=100n2.綁定和解綁規則（1）用存儲過程sp_bindrule綁定規則存儲過程sp_bindrule可以綁定一個規則到表的一個列或一個用戶自定義數據類型上。其語法格式如下： sp_bindrule rulename= 規則對象名， objname= object_name【例】 將規則CJ_rule綁定到表成績表的成績列上。EXEC sp_bindrule CJ_rule,成績表.成績n（2）用存儲過程sp_unbindrule解綁規則 存儲過程sp_unbindrule可解除規則與列或用戶自定義數據類型的綁定，其語法如下： EXEC sp_unbindrule objname= object_name 【例】 解除規則CJ_rule與成績表的成績列的綁定。 EXEC sp_unbindrule CJ_rule,成績表.成績n3.刪除規則可以在SQL Server Management Studi0窗口的對象資源管理器中，展開目標數據庫中的“可編程性”節點。鼠標右鍵單擊要刪除的規則，從快捷菜單中選擇“刪除”命令，刪除規則。也可以使用DROP RULE命令刪除當前數據庫中的一個或多個規則。其語法如下： DROP RUL