SQL課件第03章2.ppt

1、第三章 管理表,第二部分,本章知識要點,了解表的類型及創(chuàng)建 熟悉各種數(shù)據(jù)類型 掌握列的各種屬性 掌握修改表和列的操作 熟悉保持數(shù)據(jù)完整性的各種約束 了解表的索引和視圖,1修改表的結(jié)構(gòu),1）命令格式 ALTER TABLE ALTER COLUMN 類型（寬度）NULL|NOT NULL |ADD 類型（寬度）NULL|NOT NULL完整性約束,n |DROP COLUMN CASCADERESTRICT,n |DROP CONSTRAINT|ALL ,n,3.4 維護數(shù)據(jù)表,2）功能 ALTER TABLE：將要修改的當(dāng)前數(shù)據(jù)庫中的指定數(shù)據(jù)表的表名。 ALTER COLUMN：修改當(dāng)前數(shù)據(jù)庫

2、中的指定數(shù)據(jù)表的指定屬性。 ADD：向當(dāng)前數(shù)據(jù)庫中的指定數(shù)據(jù)表增加指定屬性或列級完整性約束。 DROP COLUMN：刪除當(dāng)前數(shù)據(jù)庫中的指定數(shù)據(jù)表中的指定屬性。 DROP：刪除當(dāng)前數(shù)據(jù)庫中的指定數(shù)據(jù)表中的指定列級完整性約束。,【例3.15】修改當(dāng)前數(shù)據(jù)庫“studentcourse”中S表的系屬性改成char(25)，增加一個入學(xué)時間字段，它的數(shù)據(jù)類型為date time，并設(shè)置默認值為getdate()，最后刪除入學(xué)時間字段。 方法一：使用SQL Server Management Studio 方法二：使用命令。 Use studentcourse Go ALTER TABLE s ALT

3、ER COLUMN 系char(25) Go ALTER TABLE s ADD 入學(xué)時間 datetime Go ALTER TABLE s ADD CONSTRAINT DF_sj DEFAULT(getdate() for 入學(xué)時間,3.4 維護數(shù)據(jù)表,2修改表的名稱,1）命令格式 Sp_rename , 2）功能 重命名當(dāng)前數(shù)據(jù)庫中的指定數(shù)據(jù)表名。 【例3.16】重命名數(shù)據(jù)庫bookshop的數(shù)據(jù)表book名稱，改為“書籍資料”。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 Exec Sp_rename book, 書籍資料,3.2.

4、6 修改數(shù)據(jù)表,1）命令格式 DROP TABLE 表名 2）功能 刪除表 【例3.17】刪除當(dāng)前數(shù)據(jù)庫中的表S。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 DROP TABLE S 注意: 如果只刪除表中數(shù)據(jù)行，而保留表結(jié)構(gòu) Delete table s,3.2.7 刪除數(shù)據(jù)表,3.5 表數(shù)據(jù)完整性,數(shù)據(jù)完整性是為了防止數(shù)據(jù)庫中出現(xiàn)不符合語義的數(shù)據(jù)，為了維護數(shù)據(jù)的完整性，數(shù)據(jù)庫管理系統(tǒng)必須提供一種機制來檢查數(shù)據(jù)庫的數(shù)據(jù)是否滿足語義規(guī)定的條件，這些加在數(shù)據(jù)庫之上的語義約束條件就是數(shù)據(jù)庫中的數(shù)據(jù)完整性約束條件。,8,3.5.1 表主鍵,主鍵（

5、PRIMARY KEY）約束使用數(shù)據(jù)表中的一列數(shù)據(jù)或者多列數(shù)據(jù)來惟一標識一行數(shù)據(jù)。也就是說，在數(shù)據(jù)表中不能存在主鍵相同的兩行數(shù)據(jù)。而且，位于主鍵約束下的數(shù)據(jù)應(yīng)使用確定的數(shù)據(jù)，不能輸入NULL來代替確定的數(shù)值。在管理數(shù)據(jù)時，應(yīng)確保每一個數(shù)據(jù)表都擁有自己惟一的主鍵，從而實現(xiàn)數(shù)據(jù)的實體完整性。,9,CREATE database bookshop Go CREATE TABLE book (書號 int identity(1000,1) PRIMARY KEY CLUSTERED, 書名 char(20) not null, 出版社 char(20), 出版日期 datetime DEFAULT (

6、getdate(), 單價 smallint check(單價0), 數(shù)量 smallint check(數(shù)量=0), 總價 as 單價*數(shù)量, 電子郵件地址 varchar(25), check(電子郵件地址 like %), check (出版社 in (高教,浙大,電子,中央),3.5.2 外鍵,外鍵（FOREIGN KEY）約束定義了表之間的關(guān)系，主要用來維護兩個表之間的一致性。當(dāng)一個表中的一個列或者多個列的組合和其他表中的主關(guān)鍵字定義相同時，就可以將這些列或者列的組合定義為外關(guān)鍵字，并設(shè)定它適合與哪個表中的哪些列相關(guān)聯(lián)。,11,3.5.3 CHECK約束,CHECK約束通過檢查輸入表

7、列的數(shù)據(jù)的值來維護值域的完整性，它就像一個過濾器依次檢查每一個要進入數(shù)據(jù)庫的數(shù)據(jù)，只有符合條件的數(shù)據(jù)才允許通過。,12,第13頁,【例3.2.4】要在當(dāng)前數(shù)據(jù)庫StudentsInfo中的加入Enrollment表，表中各列的要求見表3-3。,第14頁,CREATE TABLE Enrollment( Sno CHAR(10) NOT NULL, Cno CHAR(6) NOT NULL, Grade INT, CONSTRAINT EPK PRIMARY KEY ( Sno, Cno ), CONSTRAINT ESlink FOREIGN KEY (Sno) REFERENCES Stud

8、ents ( Sno ), CONSTRAINT EClink FOREIGN KEY (Cno ) REFERENCES Courses ( Cno ) ),定義該表的SQL語句：,第15頁,等價于： CREATE TABLE Enrollment( Sno CHAR(10) NOT NULL FOREIGN KEY ( Sno) REFERENCES Students ( Sno ), Cno CHAR(6) NOT NULL FOREIGN KEY ( Cno ) REFERENCES Courses( Cno ), Grade INT, PRIMARY KEY ( Sno, Cno )

9、 ),第16頁,等價于： CREATE TABLE Enrollment( Sno CHAR(10) NOT NULL REFERENCES Students ( Sno ), Cno CHAR(6) NOT NULL REFERENCES Courses( Cno ), Grade INT, PRIMARY KEY ( Sno, Cno ) ),3.5.4 表關(guān)系,可以在數(shù)據(jù)庫關(guān)系圖中的表間創(chuàng)建關(guān)系，以顯示某個表中的列如何鏈接到另一表中的列。在關(guān)系數(shù)據(jù)庫中，關(guān)系可以防止冗余數(shù)據(jù)，關(guān)系通過匹配鍵列（通常是兩個表中同名的列）中的數(shù)據(jù)來發(fā)揮作用。在大多數(shù)情況下，關(guān)系將一個表的主鍵（它為每行提供唯一

10、標識符）與另一表中外鍵的項相匹配。,17,1索引的作用 1）加速數(shù)據(jù)檢索 2）優(yōu)化查詢 3）強制數(shù)據(jù)完整性。 2索引的分類 1）聚集索引 2）非聚集索引 3）唯一索引,3.4 索引管理,3.4.1 索引概述,SQL Server 訪問數(shù)據(jù)的方式,表掃描 SQL Server 掃描表的 所有頁 索引 SQL Server 使用索引 頁找到行,6.1.1 SQL Server 訪問數(shù)據(jù)的方式,1）命令格式 CREATEUNIQUECLUSTERED|NOCLUSTERED INDEX 索引名 ON數(shù)據(jù)表名|視圖名（字段名表ASC|DESC,n） 2）功能 只有在指定的索引名稱存在時，才能使用DRO

11、PEXISTING選項，該項說明首先刪除指定表的索引后再重新構(gòu)造它。 “UNIQUE”表示建立唯一索引。 CLUSTERED表示建立聚集索引，NOCLUSTERED表示建立非聚集索引。,3.4.2 創(chuàng)建索引,3.4.2 創(chuàng)建索引,表3.17 “studentcourse”索引情況表,唯一索引,確保在索引鍵上不存在相同的值,CREATE UNIQUE NONCLUSTERED INDEX AK_Employee_LoginID ON HumanResources.Employee ( LoginID ASC),不允許存在相同值,6.2.2 唯一索引,多媒體：新建索引,用戶反映對于Sales.Sa

12、lesOrderDetail表的訪問相當(dāng)緩慢，你發(fā)現(xiàn)用戶經(jīng)產(chǎn)需要查詢OrderQty列。請為該表創(chuàng)建索引，同時索引應(yīng)當(dāng)盡可能小。,【例3.28】使用SQL命令，在數(shù)據(jù)庫“studentcourse”中的數(shù)據(jù)表S中，關(guān)于“學(xué)號”建立聚集索引，關(guān)于“姓名”建立非聚集索引。 CREATE INDEX IN_姓名 on s(姓名) CREATE unique clustered INDEX IN_學(xué)號 on s(學(xué)號) WITH pad_index,fillfactor=100 -填充因子為100 【例3.29】 為數(shù)據(jù)庫“studentcourse”中的數(shù)據(jù)表關(guān)于c.課程名降序建立唯一索引IN_課程

13、名。 IF EXISTS(select name from sysindexes where name=IN_課程名) DROP INDEX c.IN_課程名 Go USE studentcourse CREATE unique INDEX IN_課程名 on c(課程名 desc) 。,3.4.2 創(chuàng)建索引,1）命令格式 DROP INDEX 索引名,n 2）功能 刪除指定的索引?？梢粤谐龆鄠€要刪除的索引名。利用DROP INDEX命令刪除通過定義PRIMARY KEY或UNIQUE約束創(chuàng)建的索引，必須先刪除指定的約束。在系統(tǒng)表的索引不能使用DROP INDEX刪除。刪除表中的聚集索引，將使

14、表中的所在非聚集索引重建。 【例3.32】刪除數(shù)據(jù)庫“studentcourse”中，數(shù)據(jù)表sc中的索引IN_成績、數(shù)據(jù)表c中的索引IN_課程名。 Use studentcourse DROP INDEX sc. IN_成績,c.IN_課程名,3.4.3 刪除索引,習(xí)題（續(xù)）,6.訂單表Orders的列OrderID的類型是小整型（smallint），根據(jù)業(yè)務(wù)的發(fā)展需要改為整型（integer），應(yīng)該使用下面的哪條語句？ A. ALTER COLUMN OrderID integer FROM Orders B. ALTER TABLE Orders ( OrderID integer ) C

15、. ALTER TABLE Orders ALTER COLUMN OrderID integer D. ALTER COLUMN Orders.OrderID integer,3.5習(xí)題,習(xí)題（續(xù)）,7.有家企業(yè)要用表tblCustomerInfo來存儲客戶的信息。客戶的信息包括：代號（整型IDENTITY，從100001開始，每次增加5），名稱（最長40個漢字），電話（20個字符），傳真（20個字符），備注（最長1 000個漢字）。電話號碼和傳真號碼要用同一自定義類型type_TelphoneNum。 （1）請寫出創(chuàng)建該表的SQL語句。 （2）后來因手機流行，需要在表tblCustomerInfo中再添加 “手機”列，該列的類型也是type_TelphoneNum。請寫出添加該列的SQL語句。,3.5習(xí)題,（1）要注意存放一個漢字要用兩個字節(jié)，所以“名稱”和“備注”的長度要乘2。具體的SQL語句如下： CREATE TABLE tblCustomerInfo ( 代號 integer IDENTITY(10001,5) NOT NULL, 名稱 varchar(80) NOT NULL, 電話 type_TelphoneNum , 傳真 type_TelphoneNum , 備注 varc