1、第三章 創(chuàng)建和操作表,數(shù)據(jù)庫(kù)的建立和修改 數(shù)據(jù)表的建立 加強(qiáng)數(shù)據(jù)完整性 數(shù)據(jù)表修改與刪除 SQL視圖 創(chuàng)建索引 掌握內(nèi)容,數(shù)據(jù)庫(kù)的建立和修改,數(shù)據(jù)庫(kù)界面直接創(chuàng)建 數(shù)據(jù)庫(kù)文件關(guān)聯(lián) 數(shù)據(jù)庫(kù)建立語法： CREATE DATABASE Create Database AppDta On Primary ( Name = AppDta_data, Filename = d:appdtal.mdf , Size = 10MB, MaxSize = 100MB, FileGrowth = 10MB) Log On ( Name = AppDta_Log, Filename = d:appdtalog1.ld

2、f, Size = 10MB, MaxSize = 100MB, FileGrowth = 10MB ),數(shù)據(jù)庫(kù)的建立和修改,數(shù)據(jù)庫(kù)擴(kuò)展名說明 mdf 數(shù)據(jù)庫(kù)主文件 ndf 數(shù)據(jù)庫(kù)從屬文件 ldf 事務(wù)日志文件 數(shù)據(jù)庫(kù)說明 每個(gè)數(shù)據(jù)庫(kù)至少有兩個(gè)文件，一個(gè)主文件和一個(gè)事務(wù)日志文件。 從屬文件保存主文件容納不下的數(shù)據(jù)，可將數(shù)據(jù)分布在多個(gè)磁盤上。 事務(wù)日志文件保存用來恢復(fù)數(shù)據(jù)庫(kù)的日志信息。,數(shù)據(jù)庫(kù)的建立和修改,數(shù)據(jù)庫(kù)對(duì)象的標(biāo)識(shí)符 標(biāo)識(shí)符包含的字符數(shù)必須在1128之間； 標(biāo)識(shí)符的第一個(gè)字符必須是字母、下劃線、符號(hào)或#； 某些處于標(biāo)識(shí)符開始位置的符號(hào)具有特殊意義。 標(biāo)識(shí)符的后續(xù)字符可以為字母、數(shù)字或符號(hào)

3、，$符號(hào)、數(shù)字符號(hào)或下劃線； 如果標(biāo)識(shí)符是保留字或包含空格，則需要使用分割標(biāo)識(shí)符進(jìn)行處理。 SELECT * FROM my table WHERE order=10,數(shù)據(jù)庫(kù)的建立和修改,修改數(shù)據(jù)庫(kù) 語法：ALTER DATABASE Add File | Remove File | Modify File 示例： Alter Database AppDtaAdd File( Name = AppDta2, Fileanme = d:appdta2.ndf , Size = 10MB, FileGrowth = 10MB ) Alter Database AppDtaRemove File A

4、ppDta2 Alter Database AppDtaModify File( Name = AppDta_data, FileGrowth=50MB ) 刪除數(shù)據(jù)庫(kù) DROP DATABASE ,設(shè)計(jì)數(shù)據(jù)庫(kù)表,設(shè)計(jì)表各列和行的數(shù)據(jù)類型，達(dá)到三級(jí)范式要求。 決定哪些列允許空值 決定是否要在表中使用及何時(shí)使用約束、默認(rèn)設(shè)置或規(guī)則 所需索引的類型，哪里需要索引、哪些列是主鍵，哪些是外鍵,設(shè)計(jì)數(shù)據(jù)庫(kù)表,建立數(shù)據(jù)表 用于存放學(xué)生信息，包含以下列：學(xué)號(hào)（ID）、姓名（Name）、性別（Sex）、出生年月日（Bir_day）、出生地（Bir _place）、班級(jí)(Class)等。 定義數(shù)據(jù)類型ID（ch

5、ar 10位）、Name（nchar 10位）、Sex（nchar 2位）、Bir_day（datatime 8位）、Bir _place（nvarchar 50位）、 Class（nchar 10位) 空值？ 主健？ 范圍？,數(shù)據(jù)表的建立,基本表的定義 create table (列名 數(shù)據(jù)類型 可選項(xiàng) ,列名 數(shù)據(jù)類型 可選項(xiàng), , constraint ,constraint ) 可選項(xiàng)包括：not null , default 缺省值 , identity(種子,增量), unique, primary key , check(表達(dá)式) , references 表名(列名) Cons

6、traint 包括：constraint name unique(列名,)constraint name primary key(列名,)constraint name foreign key(列名,) references 引用表名(列名,)constraint name check(表達(dá)式),數(shù)據(jù)表的建立,示例 Create table teach.dbo.student(num int,name char(10),score float); 說明 指定1 到1024 個(gè)列 主鍵約束(可選) 1 到250 個(gè)Unique 約束(可選) 1 到253 個(gè)外鍵約束(可選) 1 個(gè)或者多個(gè)Che

7、ck 約束限制插入表中的數(shù)據(jù)(可選) 如果使用系統(tǒng)保留字作為表名、列名或其他SQL 對(duì)象名稱，必須用雙引號(hào)括起這些名稱。Create table student(“create int,name char(10);,加強(qiáng)數(shù)據(jù)完整性(約束）,NOT NULL（非空） UNIQUE（唯一性） PRIMARY KEY（主鍵） FOREIGN KEY（外鍵） CHECK（檢查） DEFAULT （默認(rèn)值） IDENTITY（標(biāo)識(shí)）,加強(qiáng)數(shù)據(jù)完整性,使用 NOT NULL 約束 NULL表示未定義或未知的值?？詹坏韧诹?、空白或默認(rèn)值，空表示值缺少。 可以把空看作是一個(gè)標(biāo)識(shí)符，表示一種特定狀態(tài)。 在空情

8、況下，如果沒有為列提供值，那么其值是未知的，即空的。 通過使用一個(gè)標(biāo)明列不接收空值的 NOT NULL約束，可以重寫默認(rèn)空能力的特性。,加強(qiáng)數(shù)據(jù)完整性,NOT NULL 語法 列名 數(shù)據(jù)類型 NOT NULL 示例 Create table student( num int not null, name char(10) not null, score float ); 違反NOT NULL出現(xiàn)的問題 數(shù)據(jù)記錄不能添加到表中,加強(qiáng)數(shù)據(jù)完整性,添加UNIQUE約束 UNIQUE可以使一列或一組列只包含唯一值。 列添加UNIQUE約束后，此列就不能具有相同的兩行或多行記錄。 語法： 對(duì)一列添加UN

9、IQUE約束列名 數(shù)據(jù)類型 UNIQUE 對(duì)多列添加UNIQUE約束CONSTRAINT UNIQUE (列名,),加強(qiáng)數(shù)據(jù)完整性,UNIQUE 示例 Create table student( num int unique, name char(10) not null, score float); Create table student( name char(10), lesson char(20), score float, constraint name_lesson unique (num,name) -,constraint name_lesson unique (name) -

10、,constraint name_lesson unique (lesson);,加強(qiáng)數(shù)據(jù)完整性,UNIQUE 與 NULL (SQL:99) UNIQUE是防止相同的值輸入定義有該約束的列 通過NOT NULL 和 UNIQUE 一起使用可以使UNIQUE約束列不存在空值。 Create table student( name char(10) not null unique,); Create table student( name char(10) not null, contraint un_name unique(name);,加強(qiáng)數(shù)據(jù)完整性,添加PRIMARY KEY約束 和UNI

11、QUE約束一樣，是一種SQL唯一約束。 PRIMARY KEY約束定義的列不能包含空值，即使沒有NOT NULL，該列也必須包含一個(gè)數(shù)據(jù)值。 對(duì)于每一個(gè)表只能定義一個(gè)PRIMARY KEY約束，經(jīng)常使用作列標(biāo)識(shí)。 一個(gè)表中，即使沒有定義UNIQUE約束，也應(yīng)該包含主鍵，來確保行的唯一性。,加強(qiáng)數(shù)據(jù)完整性,PRIMARY KEY語法 列名 數(shù)據(jù)類型 PRIMARY KEY CONSTRAINT PRIMARY KEY (列名,) 示例 Create table student( num int primary key , name char(10),); Create table student

12、( num int , name char(10), constraint pk_num primary key(num,name); Create table student( num int,name char(10) unique, constraint pk_num primary key(num);,加強(qiáng)數(shù)據(jù)完整性,PRIMARY KEY約束說明 一個(gè)表可以定義多個(gè) UNIQUE 約束，而只能定義一個(gè) PRIMARY KEY 約束。 允許空值的列上可以定義 UNIQUE 約束，而不能定義 PRIMARY KEY 約束。 當(dāng)為表指定 PRIMARY KEY 約束時(shí)，SQL Server

13、通過為主鍵列創(chuàng)建唯一索引強(qiáng)制數(shù)據(jù)的唯一性。當(dāng)在查詢中使用主鍵時(shí)，該索引還可用來對(duì)數(shù)據(jù)進(jìn)行快速訪問。,加強(qiáng)數(shù)據(jù)完整性,添加FOREIGN KEY約束 涉及一個(gè)表中數(shù)據(jù)如何與另一個(gè)表中數(shù)據(jù)相聯(lián)系。 有關(guān)系的表有目的的聯(lián)系在一起可以保證數(shù)據(jù)的完整性。 參照完整性防止一個(gè)表中的數(shù)據(jù)操作對(duì)另一個(gè)表中的數(shù)據(jù)產(chǎn)生不利影響。,加強(qiáng)數(shù)據(jù)完整性,在表2中沒有列出的課程，就不能被插入到表1中。 如果修改表2中的內(nèi)容，如果在表1中存在相同的值，應(yīng)該可以預(yù)測(cè)到這樣作的結(jié)果。 FOREIGN KEY約束可以增強(qiáng)兩個(gè)表之間的參照完整性。,表1,表2,加強(qiáng)數(shù)據(jù)完整性,使用 FOREIGN KEY 原則 被引用的列必須是被引用

14、表中的候選鍵。被引用表最常用的是主鍵。 FOREIGN KEY約束可以包括一列或多列。 使用FOREIGN KEY約束時(shí)，兩個(gè)表的引用的列數(shù)、列的數(shù)據(jù)類型必須相同，但列名稱可以不同。 如果在定義FOREIGN KEY約束時(shí)沒有指定被引用列，那么在被引用表中的主鍵中定義的列就被當(dāng)作被引用列。 在一表建立外鍵之前，被引用表必須存在，并且被引用表對(duì)應(yīng)列必須定義UNIQUE或PRIMARY KEY約束。,加強(qiáng)數(shù)據(jù)完整性,FOREIGN KEY約束語法 列名 類型 REFERENCES (引用列)MATCH FULL | PARTIAL | SIMPLE ON DELETE CASCADE | NO A

15、CTION ON UPDATE CASCADE | NO ACTION CONSTRAINT FROEIGN KEY (,)REFERENCES (,),加強(qiáng)數(shù)據(jù)完整性,FOREIGN KEY約束示例 Create table 表1( 學(xué)號(hào) char(5) primary key, 課程編號(hào) int REFERENCES 表2); Create table 表1( 學(xué)號(hào) char(5) primary key, 課程編號(hào) int REFERENCES 表2(課程編號(hào)); Create table 表1( 學(xué)號(hào) char(5) primary key, 課程編號(hào) int, CONSTRAINT

16、fk_1 FOREIGN KEY (課程編號(hào)) REFERENCES 表2 (課程編號(hào));,加強(qiáng)數(shù)據(jù)完整性,FOREIGN KEY約束的MATCH字句 (SQL:99) MATCH FULL | PARTIAL | SIMPLE 該字句用于決定如何處理外鍵列中的空值。 如外鍵列不許可空值，MATCH字句就不能應(yīng)用。 MATCH FULL：所有的引用列要么為空值，要么都不是空值(為被引用列的值)。 MATCH PARTIAL：一個(gè)或多個(gè)引用列可以有空值，剩下的引用列和對(duì)應(yīng)的被引用列相同即可。 MATCH SIMPLE：一個(gè)或多個(gè)引用列如果有空值，那么剩下的引用列可以不包含在對(duì)應(yīng)的被引用列中。是F

17、OREIGN KEY 約束的默認(rèn)值。,加強(qiáng)數(shù)據(jù)完整性,被引用表,當(dāng)前表,(SQL Server 缺省),加強(qiáng)數(shù)據(jù)完整性,ON DELETE, ON UPDATE CASCADE：當(dāng)被引用列的數(shù)據(jù)更新或刪除時(shí)，引用列的數(shù)據(jù)也將被更新和刪除。 NO ACTION：當(dāng)更新或刪除被引用列的數(shù)據(jù)將引起外鍵違反時(shí)，該動(dòng)作將被禁止執(zhí)行。 建立外鍵如果未指定 CASCADE和NO ACTION時(shí)，SQL Server 缺省使用NO ACTION 示例： create table 當(dāng)前表 (編號(hào) int primary key,姓名 char(10),年齡 int, constraint fk foreign

18、key(姓名,年齡) references 被引用表(姓名,年齡) on delete cascade on update cascade);,加強(qiáng)數(shù)據(jù)完整性,添加CHECK約束 CHECK約束限制表中列的取值范圍。 可以通過任何基于邏輯運(yùn)算符返回結(jié)果 TRUE 或 FALSE 的邏輯（布爾）表達(dá)式來創(chuàng)建 CHECK 約束。 語法： 列約束： 列名 數(shù)據(jù)類型 CHECK (表達(dá)式) 表約束： CONSTRAINT CHECK (表達(dá)式) 可對(duì)多列進(jìn)行限制,加強(qiáng)數(shù)據(jù)完整性,CHECK約束的表達(dá)式寫法 字段是否為空 IS NULL 或 IS NOT NULL 數(shù)字的取值范圍： 關(guān)系、邏輯運(yùn)算符 年

19、齡=5 AND 年齡=30 AND 年齡!=20 BETWEEN 年齡 BETWEEN 5 AND 30 字符取值范圍 關(guān)系、邏輯預(yù)算符 性別=男 OR 性別=女 IN 性別 IN (男 , 女) LIKE,加強(qiáng)數(shù)據(jù)完整性,CHECK示例 Create Table student( num int , name char(10) CHECK (name=張三 or name=李四), score float CHECK (score=0 and score=0) );,加強(qiáng)數(shù)據(jù)完整性,Default約束 為數(shù)據(jù)列定義一個(gè)默認(rèn)值； 使用默認(rèn)值（Default）約束，用戶在插入新行時(shí)沒有顯示為列提

20、供數(shù)據(jù)，系統(tǒng)將默認(rèn)值賦給該列； 默認(rèn)值（Default）約束所提供的默認(rèn)值可以為常量、函數(shù)、系統(tǒng)函數(shù)、空值等； DEFAULT 定義可適用于除定義為 timestamp 或帶 IDENTITY 屬性的列以外的任何列； 每列只能定義一個(gè)DEFAULT約束。,加強(qiáng)數(shù)據(jù)完整性,語法： 列約束： 列名 數(shù)據(jù)類型 default 默認(rèn)值 表約束： CONSTRAINT DEFAULT (表達(dá)式) 示例 Create table student( num int default 1, name char(10) default abc, score float, curTime datetime defa

21、ult getdate() );,加強(qiáng)數(shù)據(jù)完整性,IDENTITY 屬性 在表中創(chuàng)建一個(gè)標(biāo)識(shí)列 語法：IDENTITY ( seed , increment ) Seed：裝載到表中的第一個(gè)行所使用的值。 Increment：增量值 . 必須同時(shí)指定種子和增量，或者二者都不指定。 如果二者都未指定，則取默認(rèn)值 (1,1)。 示例 Create table student(num int identity(1,2),name char(10),score float);,數(shù)據(jù)表修改與刪除,ALTER TABLE ALTER TABLE ALTER COLUMN 類型 NULL|NOT NULL

22、ALTER TABLE ADD 列名 類型 NULL|NOT NULL CONSTRAINT ALTER TABLE ADD CONSTRAINT ALTER TABLE DROP COLUMN 列名 ALTER TABLE DROP CONSTRAINT DROP TALBE ,SQL視圖,視圖是一種類似表的對(duì)象，定義在一個(gè)或多表上。 視圖可以看成虛擬表，在其中不包含數(shù)據(jù)，只是一些對(duì)視圖的定義語句。 使用視圖可以定義復(fù)雜的查詢，并可以把這些查詢存儲(chǔ)在視圖的定義中，每當(dāng)用到這些查詢時(shí)，就可以調(diào)用視圖，不必重新創(chuàng)建查詢，提供簡(jiǎn)化和限制用戶訪問數(shù)據(jù)的方法。 向用戶隱藏?cái)?shù)據(jù)庫(kù)的內(nèi)部結(jié)構(gòu)。 一旦創(chuàng)建了

23、該視圖就可以像在其他SQL 語句中使用表一樣使用該視圖。,SQL視圖,試圖的優(yōu)點(diǎn) 為用戶簡(jiǎn)化查詢 為用戶定制數(shù)據(jù) 簡(jiǎn)化用戶權(quán)限的管理 導(dǎo)出數(shù)據(jù) 視圖的缺點(diǎn) 降低系統(tǒng)性能 修改限制,SQL視圖,過濾數(shù)據(jù),方便數(shù)據(jù)查看,SQL視圖,視圖的限制 在創(chuàng)建或使用視圖時(shí)，應(yīng)遵守以下規(guī)定： 不能在視圖上建立觸發(fā)器和索引。 一個(gè)視圖最多只能有250列。 不能基于臨時(shí)表建立視圖，由SELECT INTO語句可建立臨時(shí)表，在CREATE VIEW語句中不能使用INTO關(guān)鍵字。 在CREATE VIEW語句中不能使用聯(lián)合操作符UNION。 在插入或修改數(shù)據(jù)時(shí)，每個(gè)INSERT語句和UPDATE語句不能影響一個(gè)以上的

24、視圖基表。 對(duì)視圖中的計(jì)算列不允許使用INSERT語句插入數(shù)據(jù)。 UPDATE語句不能夠修改視圖的計(jì)算列數(shù)據(jù)，也不允許它修改包含集合的函數(shù)和內(nèi)置函數(shù)的視圖列。 在視圖的Text和Image數(shù)據(jù)類型的列上不允許使用READTEXT和WRITETEXT語句。,SQL視圖,視圖創(chuàng)建語法 CREATE VIEW ()AS WITH CHECK OPTION 視圖列,必須使用的情況 通過某種計(jì)算從表中得到，而不是直接從表中復(fù)制的列。 多個(gè)表的列名相同，連接在一起建立在一個(gè)視圖中需提供列名。 不能只給一些列提供名稱，而不給其它列提供名稱。 Query expression 包含查詢語句的復(fù)雜結(jié)構(gòu)，可以執(zhí)行

25、大量操作，包括從表中檢索數(shù)據(jù)、計(jì)算數(shù)據(jù)、限制返回的數(shù)據(jù)類型，或者執(zhí)行查詢表達(dá)式支持的大部分其它操作。 由 SELECT 語句組成,SQL視圖,基本 SELECT 語法 SELECT 字段 FROM 表 WHERE 條件 示例 CREATE VIEW stu AS select 學(xué)號(hào),姓名,班級(jí)名稱 from student CREATE VIEW 工資庫(kù)(姓名,工資,工資改變)AS select 姓名,工資,工資*1.15 from 工資庫(kù) CREATE VIEW lesson_viewAS select 選課.學(xué)號(hào),課程.課程名稱 from 選課,課程where 選課.課程編號(hào)=課程.課程編

26、號(hào),SQL視圖,可更新視圖 視圖中無匯總、聚合等函數(shù)。 視圖中的每一列必須與表中的一源列對(duì)應(yīng)。 視圖中的每一行必須和表中的一源行對(duì)應(yīng)。 在視圖中進(jìn)行更新或插入數(shù)據(jù)，實(shí)際上修改的是內(nèi)在表中的數(shù)據(jù)，不能違反表中設(shè)置的約束。 WITH CHECK OPTION 對(duì)WHERE條件加限制，防止對(duì)視圖更新數(shù)據(jù)后得到的結(jié)果不滿足更新前的條件。,SQL視圖,CREATE VIEW myviewAS select 姓名,工資 from 工資庫(kù)where 工資800 如果對(duì)視圖進(jìn)行更新，使工資小于800，那么這些小于800的數(shù)據(jù)行就不會(huì)出現(xiàn)在視圖中，沒法對(duì)其進(jìn)行進(jìn)一步更新了。 CREATE VIEW myview

27、AS select 姓名,工資 from 工資庫(kù)where 工資800 WITH CHECK OPTION WITH CHECK OPTION 選項(xiàng)將會(huì)阻止將工資更新到800以下。出現(xiàn)出錯(cuò)信息，告訴你不能進(jìn)行這樣的修改。,SQL視圖,管理試圖 sp_helptext 視圖名 修改視圖 ALTER VIEW () AS WITH CHECK OPTION 示例： ALTER VIEW student_view AS select 學(xué)號(hào),姓名 from student,SQL視圖,通過視圖檢索數(shù)據(jù) SELECT * FROM 通過視圖插入數(shù)據(jù) INSERT INTO VALUES(列值) 通過視圖

28、刪除數(shù)據(jù) DELETE FROM WHERE 通過視圖更改數(shù)據(jù) UPDATE SET 列1=列值1 WHERE ,SQL視圖,刪除視圖 DROP VIEW 示例： Drop view lesson_view 刪除視圖，對(duì)所引用表的數(shù)據(jù)沒有任何影響。 如果視圖已被另外的視圖所引用，則盡量不要?jiǎng)h除該視圖，否則另外的視圖將無法正常被檢索。,創(chuàng)建索引,當(dāng)指定一個(gè)主鍵或唯一性約束時(shí)SQL Server 為基表創(chuàng)建一個(gè)內(nèi)部索引。 索引對(duì)數(shù)據(jù)庫(kù)性能有正面影響和負(fù)面影響。簡(jiǎn)單地說索引可以加速數(shù)據(jù)檢索，但是由于SQL Server 需要花費(fèi)一些時(shí)間修改基表上索引列的內(nèi)容，減慢了修改索引列的值。 由 ntext、

29、text 或 image 數(shù)據(jù)類型組成的列不能指定為索引列。,創(chuàng)建索引,創(chuàng)建索引的目的 加速數(shù)據(jù)檢索 加速連接、ORDER BY 和GROUP BY 查詢優(yōu)化器依賴于索引起作用 強(qiáng)制實(shí)施行的唯一性 為什么不在每一列創(chuàng)建索引 創(chuàng)建索引要花費(fèi)時(shí)間和占用存儲(chǔ)空間 減慢了數(shù)據(jù)修改速度,創(chuàng)建索引,考慮創(chuàng)建索引的列 主鍵上創(chuàng)建索引 連接中頻繁使用的列（外鍵） 在某一范圍內(nèi)頻繁搜索的列和按排序次序頻繁檢索的列 不考慮創(chuàng)建索引的列 很少或從來不在查詢中引用的列 只有兩個(gè)或若干個(gè)值的列 小的表,索引的分類,唯一性索引 在表中建立唯一性索引時(shí)，組成該索引的字段或字段組合在表中具有唯一值，對(duì)于表中的任何兩條記錄來說

30、，索引鍵的值都是各不相同的。 主鍵索引 在數(shù)據(jù)庫(kù)關(guān)系圖中為表定義一個(gè)主鍵時(shí)，將自動(dòng)創(chuàng)建主鍵索引，主鍵索引是唯一索引的特殊類型。 聚集索引 在聚集索引中，表中各記錄的物理順序與鍵值的邏輯（索引）順序相同。,創(chuàng)建索引,語法 CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX name ON table | view ( column ASC | DESC ,.n ) 說明 UNIQUE：為表或視圖創(chuàng)建唯一索引，不允許存在索引值相同的兩行。 CLUSTERED：聚簇(聚集)索引，行的物理順序與索引排序相同，一個(gè)表或視圖只允許同時(shí)有一個(gè)聚集索引。 SQL Server