結(jié)構(gòu)化查詢語言SQL

1、1 主要內(nèi)容主要內(nèi)容vSQLSQL概述概述 vSQLSQL的數(shù)據(jù)定義的數(shù)據(jù)定義vSQLSQL的數(shù)據(jù)查詢的數(shù)據(jù)查詢 v數(shù)據(jù)更新數(shù)據(jù)更新 v嵌入式嵌入式SQL SQL vTransact SQLTransact SQL語言概語言概述述 2本章概述本章概述v 結(jié)構(gòu)化查詢語言結(jié)構(gòu)化查詢語言SQLSQL（Structured Query LanguageStructured Query Language）是關(guān)系數(shù)據(jù)庫）是關(guān)系數(shù)據(jù)庫的標準語言，對關(guān)系模型的發(fā)展和商用的標準語言，對關(guān)系模型的發(fā)展和商用DBMSDBMS的研制起著重要的作用。的研制起著重要的作用。SQLSQL語語言是介乎于關(guān)系代數(shù)和元組演算之間

2、的一種語言。本章詳細介紹言是介乎于關(guān)系代數(shù)和元組演算之間的一種語言。本章詳細介紹SQLSQL的核心的核心部分內(nèi)容：部分內(nèi)容：數(shù)據(jù)定義數(shù)據(jù)定義、數(shù)據(jù)查詢數(shù)據(jù)查詢、數(shù)據(jù)更新數(shù)據(jù)更新和和嵌入式嵌入式SQLSQL。 34.1 SQL概述概述 主要內(nèi)容主要內(nèi)容vSQL的產(chǎn)生與發(fā)展的產(chǎn)生與發(fā)展vSQL的組成及特點的組成及特點44.1.1 SQL4.1.1 SQL的產(chǎn)生與發(fā)展的產(chǎn)生與發(fā)展 v 19721972年，年，IBMIBM公司開始研制實驗型關(guān)系數(shù)據(jù)庫管理系統(tǒng)公司開始研制實驗型關(guān)系數(shù)據(jù)庫管理系統(tǒng)SYSTEM RSYSTEM R，其配，其配備的查詢語言稱為備的查詢語言稱為SQUARESQUARE (Spe

3、cifying Queries As Relational (Specifying Queries As Relational Expression ) Expression )語言，語言中使用了較多的數(shù)學符號。語言，語言中使用了較多的數(shù)學符號。v 19741974年，年，BoyceBoyce和和ChamberlinChamberlin把把SQUARESQUARE修改為修改為SEQUELSEQUEL (Structured (Structured English Query Language )English Query Language )語言。后來語言。后來SEQUELSEQUEL簡稱為簡

4、稱為SQLSQL (Structured (Structured Query Language )Query Language )，即，即“結(jié)構(gòu)式查詢語言結(jié)構(gòu)式查詢語言”，SQLSQL的發(fā)音仍的發(fā)音仍“sequelsequel”?，F(xiàn)在現(xiàn)在SQLSQL已經(jīng)成為一個標準已經(jīng)成為一個標準 。v SQLSQL有兩個標準：有兩個標準：ANSI SQLANSI SQL和和19921992年通過的修改版本年通過的修改版本SQL-92SQL-92（簡稱（簡稱 SQL2SQL2）。還有一個新的標準）。還有一個新的標準SQL3SQL3，它擴充了，它擴充了SQL2SQL2，引入了遞歸、觸發(fā)，引入了遞歸、觸發(fā) 器和對

5、象等概念和機制。器和對象等概念和機制。54.1.2 SQL4.1.2 SQL的組成及特點的組成及特點 SQLSQL語言從功能上可以分為四部分：數(shù)據(jù)查詢（語言從功能上可以分為四部分：數(shù)據(jù)查詢（Data QueryData Query）、數(shù)據(jù)）、數(shù)據(jù)操縱（操縱（Data ManipulationData Manipulation）、數(shù)據(jù)定義（）、數(shù)據(jù)定義（Data DefinitionData Definition）和數(shù)據(jù)控制）和數(shù)據(jù)控制（Data ControlData Control）。）。 數(shù)據(jù)定義語言，即數(shù)據(jù)定義語言，即SQL DDLSQL DDL，用于定義，用于定義SQLSQL模式、基本

6、表、視圖、索引等模式、基本表、視圖、索引等結(jié)構(gòu)。結(jié)構(gòu)。 數(shù)據(jù)操縱語言，即數(shù)據(jù)操縱語言，即SQL DMLSQL DML。數(shù)據(jù)操縱分成數(shù)據(jù)查詢和數(shù)據(jù)更新兩類。數(shù)據(jù)操縱分成數(shù)據(jù)查詢和數(shù)據(jù)更新兩類。其中數(shù)據(jù)更新又分成插入、刪除和修改三種操作。其中數(shù)據(jù)更新又分成插入、刪除和修改三種操作。 數(shù)據(jù)控制語言，即數(shù)據(jù)控制語言，即SQL DCLSQL DCL，這一部分包括對基本表和視圖的授權(quán)、完，這一部分包括對基本表和視圖的授權(quán)、完整性規(guī)則的描述、事務控制等內(nèi)容。整性規(guī)則的描述、事務控制等內(nèi)容。 SQLSQL的核心部分相當于關(guān)系代數(shù)，同時又具有關(guān)系代數(shù)所沒有的許多的核心部分相當于關(guān)系代數(shù)，同時又具有關(guān)系代數(shù)所沒有

7、的許多特點，如聚集、數(shù)據(jù)庫更新等。特點，如聚集、數(shù)據(jù)庫更新等。6v綜合統(tǒng)一綜合統(tǒng)一v高度非過程化高度非過程化nSQL是一種第四代語言（是一種第四代語言（4GL），用戶只需提出），用戶只需提出“干什干什么么”，無需具體指明，無需具體指明“怎么干怎么干”，像存取路徑選擇和具，像存取路徑選擇和具體處理操作等，均有系統(tǒng)自動完成。體處理操作等，均有系統(tǒng)自動完成。v統(tǒng)一的語法結(jié)構(gòu)統(tǒng)一的語法結(jié)構(gòu)nSQL有兩種使用方式：一是聯(lián)機交互使用，另一種是嵌有兩種使用方式：一是聯(lián)機交互使用，另一種是嵌入到某種高級語言程序設計語言中去使用。入到某種高級語言程序設計語言中去使用。v語言簡潔語言簡潔7SQLSQL的核心動詞的

8、核心動詞 功功 能能動動 詞詞數(shù)據(jù)庫查詢數(shù)據(jù)庫查詢SELECTSELECT數(shù)據(jù)定義數(shù)據(jù)定義CREATECREATE，DROPDROP數(shù)據(jù)操縱數(shù)據(jù)操縱INSERTINSERT，UPDATEUPDATE，DELECTDELECT數(shù)據(jù)控制數(shù)據(jù)控制GRANTGRANT，REVOKEREVOKE84.2 SQL的數(shù)據(jù)定義的數(shù)據(jù)定義 主要內(nèi)容主要內(nèi)容v 數(shù)據(jù)庫的創(chuàng)建與撤銷數(shù)據(jù)庫的創(chuàng)建與撤銷v 定義、修改與刪除基本表定義、修改與刪除基本表v 建立與刪除索引建立與刪除索引v 創(chuàng)建與刪除視圖創(chuàng)建與刪除視圖9操作對操作對象象操操 作作 方方 式式創(chuàng)創(chuàng) 建建刪刪 除除修修 改改數(shù)據(jù)庫數(shù)據(jù)庫CREATE DATABA

9、SECREATE DATABASEDROP DATABASEDROP DATABASE表表CREATE TABLECREATE TABLEDROP TABLEDROP TABLEALTER TABLEALTER TABLE索索 引引CREATE INDEXCREATE INDEXDROP INDEXDROP INDEX視視 圖圖CREATE VIEWCREATE VIEWDROP VIEWDROP VIEWSQLSQL的數(shù)據(jù)定義功能包括數(shù)據(jù)庫定義、表定義、視圖和索引的定義。的數(shù)據(jù)定義功能包括數(shù)據(jù)庫定義、表定義、視圖和索引的定義。 10學號學號SNO姓名姓名SNAME性別性別SEX年齡年齡AGE

10、所在系所在系SDEPT200915121200915122200915123200915125李小勇李小勇劉劉 晨晨王洪敏王洪敏張張 力力MFFM20191819CSCSMAIS學生表學生表：S(S(SNOSNO,SNAME,SEX,AGE,SDEPT) ,SNAME,SEX,AGE,SDEPT) 課程表：課程表：C(C(CNOCNO,CNAME,CPNO,CCREDIT) ,CNAME,CPNO,CCREDIT) 學生選課表學生選課表：SC(SC(SNO,CNOSNO,CNO,GRADE) ,GRADE) 例例4.14.1 定義一個定義一個教務管理數(shù)據(jù)庫教務管理數(shù)據(jù)庫JWGLJWGL，它包含

11、，它包含3 3個表：個表：11課程號課程號CNO課程名課程名CNAME先修課先修課CPNO學分學分CCREDIT1234567數(shù)據(jù)庫數(shù)據(jù)庫數(shù)學數(shù)學信息系統(tǒng)信息系統(tǒng)操作系統(tǒng)操作系統(tǒng)數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)處理數(shù)據(jù)處理C語言語言516764243424課程表實例課程表實例學號學號SNO課程號課程號CNO成績成績GRADE200915121200915121200915121200915122200915122123239285889080124.2.1 數(shù)據(jù)庫的創(chuàng)建與撤銷數(shù)據(jù)庫的創(chuàng)建與撤銷v 數(shù)據(jù)庫的創(chuàng)建數(shù)據(jù)庫的創(chuàng)建 在在SQL2SQL2中，一個數(shù)據(jù)庫模式被定義為基本表的集合。中，一個數(shù)據(jù)庫模式被定義

12、為基本表的集合。對數(shù)據(jù)庫的創(chuàng)建可用對數(shù)據(jù)庫的創(chuàng)建可用CREATECREATE語句實現(xiàn)，其句法如下：語句實現(xiàn)，其句法如下： CREATE DATABASECREATE DATABASE AUTHORIZATIONAUTHORIZATION 其中：其中：AUTHORIZATION AUTHORIZATION 是指該用戶獲得授予創(chuàng)建和使用數(shù)是指該用戶獲得授予創(chuàng)建和使用數(shù)據(jù)庫的權(quán)限。據(jù)庫的權(quán)限。 例如，下面語句定義了教務管理數(shù)據(jù)庫的模式：例如，下面語句定義了教務管理數(shù)據(jù)庫的模式： CREATE DATABASE CREATE DATABASE JWGLJWGL AUTHORIZATION AUTHOR

13、IZATION 張銘張銘 創(chuàng)建的數(shù)據(jù)庫名為創(chuàng)建的數(shù)據(jù)庫名為JWGLJWGL，擁有者為張銘。，擁有者為張銘。13v 數(shù)據(jù)庫的撤消數(shù)據(jù)庫的撤消 當一個數(shù)據(jù)庫及其所屬的基本表、視圖等元素都不需要時，可以用當一個數(shù)據(jù)庫及其所屬的基本表、視圖等元素都不需要時，可以用DROP語句撤消這個數(shù)據(jù)庫。語句撤消這個數(shù)據(jù)庫。DROP語句的句法如下：語句的句法如下：DROP DATABASE CASCADE | RESTRICT撤消的方式有以下兩種撤消的方式有以下兩種:CASCADE（連鎖式）（連鎖式）方式方式：執(zhí)行：執(zhí)行DROP語句時，把數(shù)據(jù)庫及其下屬的語句時，把數(shù)據(jù)庫及其下屬的基本表、視圖、索引等所有元素全部撤消

14、?；颈怼⒁晥D、索引等所有元素全部撤消。 RESTRICT（約束式）（約束式）方式方式：執(zhí)行：執(zhí)行DROP語句時，只有當數(shù)據(jù)庫中沒有語句時，只有當數(shù)據(jù)庫中沒有任何下屬元素時，才能撤消數(shù)據(jù)庫，否則拒絕執(zhí)行任何下屬元素時，才能撤消數(shù)據(jù)庫，否則拒絕執(zhí)行DROP語句。語句。 例如，要撤消數(shù)據(jù)庫例如，要撤消數(shù)據(jù)庫JWGL及其下屬所有的元素，可用下列語句實現(xiàn)：及其下屬所有的元素，可用下列語句實現(xiàn)： DROP DATABASEDROP DATABASE JWGL JWGL CASCADE CASCADE 144.2.2 定義、修改與刪除基本表定義、修改與刪除基本表v 數(shù)據(jù)類型數(shù)據(jù)類型 一個屬性選用哪種數(shù)據(jù)類

15、型要根據(jù)實際情況來決定，一般要從兩個一個屬性選用哪種數(shù)據(jù)類型要根據(jù)實際情況來決定，一般要從兩個方面來考慮，方面來考慮，一是取值范圍，二是要做哪些運算一是取值范圍，二是要做哪些運算。 數(shù)據(jù)類型數(shù)據(jù)類型含含 義義CHAR(n)CHAR(n)VARCHAR(n)VARCHAR(n)INTINTSMALLINTSMALLINTNUMERIC(p,d)NUMERIC(p,d)REALREALDOUBLE PRECISIONDOUBLE PRECISIONFLOAT(n)FLOAT(n)DATEDATETIMETIME度為度為n n的定長字符串的定長字符串 最大長度為最大長度為n n的變長字符串的變長字符

16、串長整數(shù)（也可以寫作長整數(shù)（也可以寫作INTEGERINTEGER）短整數(shù)短整數(shù)定點數(shù)，由定點數(shù)，由P P位數(shù)字（不包括符號、小數(shù)點）組成，小位數(shù)字（不包括符號、小數(shù)點）組成，小數(shù)后面有數(shù)后面有d d位數(shù)字位數(shù)字取決于機器精度的浮點數(shù)取決于機器精度的浮點數(shù)取決于機器精度的雙精度浮點數(shù)取決于機器精度的雙精度浮點數(shù)浮點數(shù)，精度至少為浮點數(shù)，精度至少為n n位數(shù)字位數(shù)字日期，包含年、月、日，格式為日期，包含年、月、日，格式為YYYY-MM-DDYYYY-MM-DD 時間，包含一日的時、分、秒，格式為時間，包含一日的時、分、秒，格式為HH:MM:SSHH:MM:SS定義表定義表 - - 確定表的各個屬

17、性確定表的各個屬性 - - 確定每個屬性域（取值范圍）確定每個屬性域（取值范圍）15v 定義基本表定義基本表 定義基本表，就是創(chuàng)建基本表的結(jié)構(gòu)。定義基本表，就是創(chuàng)建基本表的結(jié)構(gòu)。 CREATE TABLECREATE TABLE （ 列級完整性約束條件列級完整性約束條件 ， 列級完整性約束條件列級完整性約束條件 ，）；）； 建表的同時通常還可以定義與該表有關(guān)的完整性約束條建表的同時通常還可以定義與該表有關(guān)的完整性約束條件，這些完整性約束條件被存入系統(tǒng)的數(shù)據(jù)字典中，當用戶件，這些完整性約束條件被存入系統(tǒng)的數(shù)據(jù)字典中，當用戶操作表中數(shù)據(jù)時由操作表中數(shù)據(jù)時由RDBMS自動檢查該操作是否違背這些完自動

18、檢查該操作是否違背這些完整性約束條件。整性約束條件。 16例例4.2 4.2 建立一個建立一個“學生學生”表表S S CREATE TABLE S (SNO CHAR(9) PRIMARY KEY, /* 列級完整性約束條件，列級完整性約束條件，SNO 是主碼是主碼 */ SNAME CHAR(20) UNIQUE, /* SNAME取唯一值取唯一值 */ SEX CHAR(1), AGE SMALLINT, SDEPT CHAR(20) ); 系統(tǒng)執(zhí)行上面的系統(tǒng)執(zhí)行上面的CREATE TABLECREATE TABLE語句后，就在數(shù)據(jù)庫中建立語句后，就在數(shù)據(jù)庫中建立一個新的空的一個新的空的“

19、學生學生”表表S S，并將有關(guān)，并將有關(guān)“學生學生”表的定義及有表的定義及有關(guān)約束條件存放在數(shù)據(jù)字典中。關(guān)約束條件存放在數(shù)據(jù)字典中。 17例例4.4 4.4 建立學生選課表建立學生選課表SCSC CREATE TABLE SC (SNO CHAR(9), CNO CHAR(4), GRADE SMALLINT, PRIMARY KEY(SNO,CNO), /* 主碼由兩個屬性構(gòu)成，必須作主碼由兩個屬性構(gòu)成，必須作 為表級完整性進行定義為表級完整性進行定義 */ FOREIGN KEY(SNO) REFERENCES S(SNO), /* 表級完整性約束條件，表級完整性約束條件，SNO是外碼，被

20、參照表是是外碼，被參照表是S */ FOREIGN KEY(CNO) REFERENCES C(CNO) /* 表級完整性約束條件，表級完整性約束條件，CNO是外碼，被參照表是是外碼，被參照表是C */ ); 18v 修改基本表修改基本表 SQLSQL語言用語言用ALTER TABLEALTER TABLE語句修改基本結(jié)構(gòu)表，其一般格式為：語句修改基本結(jié)構(gòu)表，其一般格式為： ALTER TABLE ADD 完整性約束完整性約束 DROP ALTER COLUMN ； 其中其中 是要修改的基本表。是要修改的基本表。 ADD子句用于增加新列和新的完整性約束條件；子句用于增加新列和新的完整性約束條件

21、； DROPDROP子句用于刪除指定的完整性約束條件；子句用于刪除指定的完整性約束條件； ALTER COLUMN 子句用于修改原有的列定義。包括修改列名和數(shù)據(jù)類型。子句用于修改原有的列定義。包括修改列名和數(shù)據(jù)類型。 例例4.5 向向S S表增加表增加“入學時間入學時間”列，其數(shù)據(jù)類型為日期型。列，其數(shù)據(jù)類型為日期型。 ALTER TABLE S ADD S_ENTRANCE DATE; ALTER TABLE S ADD S_ENTRANCE DATE; 不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。19例例4.6 alter tab

22、lealter table s s alter columnalter column age age intint； 例例4.7 alter table alter table c c add unique(add unique(cnamecname) )；20n刪除屬性列刪除屬性列 (1) 間接刪除間接刪除v 把表中要保留的列及其內(nèi)容復制到一個新表中把表中要保留的列及其內(nèi)容復制到一個新表中v 刪除原表刪除原表v 再將新表重命名為原表名再將新表重命名為原表名 (2)直接刪除直接刪除 例：例：ALTER TABLEALTER TABLE Student Student DropDrop Scom

23、e Scome ；21完整性約束條件完整性約束條件*一、完整性的概念一、完整性的概念v數(shù)據(jù)完整性是指存儲在數(shù)據(jù)庫中的數(shù)據(jù)正確無誤并且相數(shù)據(jù)完整性是指存儲在數(shù)據(jù)庫中的數(shù)據(jù)正確無誤并且相關(guān)數(shù)據(jù)具有一致性。關(guān)數(shù)據(jù)具有一致性。v引入數(shù)據(jù)完整性是為了在數(shù)據(jù)的添加、刪除、修改等操引入數(shù)據(jù)完整性是為了在數(shù)據(jù)的添加、刪除、修改等操作中不出現(xiàn)數(shù)據(jù)的破壞或多個表數(shù)據(jù)不一致。作中不出現(xiàn)數(shù)據(jù)的破壞或多個表數(shù)據(jù)不一致。22 在在SQL Server 2005中，根據(jù)數(shù)據(jù)完整措施所作用的數(shù)中，根據(jù)數(shù)據(jù)完整措施所作用的數(shù)據(jù)庫對象和范圍不同，可以將數(shù)據(jù)完整性分為以下幾種：據(jù)庫對象和范圍不同，可以將數(shù)據(jù)完整性分為以下幾種：1、

24、實體完整性（主鍵、唯一性約束）、實體完整性（主鍵、唯一性約束）2、參照完整性（外鍵約束）、參照完整性（外鍵約束）3、域完整性（、域完整性（check、默認值約束）、默認值約束）4、用戶定義的完整性、用戶定義的完整性23二、完整性的類型二、完整性的類型1）實體完整性）實體完整性v實體完整性：在表中不能存在完全相同的記錄，且每條記錄實體完整性：在表中不能存在完全相同的記錄，且每條記錄都要具有一個非空且不重復的主鍵值。都要具有一個非空且不重復的主鍵值。v實現(xiàn)實體完整性的方法：實現(xiàn)實體完整性的方法：設置主鍵設置主鍵、惟一約束惟一約束、惟一索引惟一索引2）參照完整性）參照完整性v參照完整性：又稱為引用完

25、整性。是指通過主鍵與外鍵相聯(lián)參照完整性：又稱為引用完整性。是指通過主鍵與外鍵相聯(lián)系的兩個表或兩個以上的表，相關(guān)字段的值要保持一致。系的兩個表或兩個以上的表，相關(guān)字段的值要保持一致。v實現(xiàn)實體完整性的方法：實現(xiàn)實體完整性的方法：外鍵約束外鍵約束243）域完整性）域完整性v域完整性：向表中添加的數(shù)據(jù)必須與數(shù)據(jù)類型、格式及有域完整性：向表中添加的數(shù)據(jù)必須與數(shù)據(jù)類型、格式及有效的數(shù)據(jù)長度相匹配。效的數(shù)據(jù)長度相匹配。v實現(xiàn)域完整性的方法：實現(xiàn)域完整性的方法：CHECK約束約束、外鍵約束外鍵約束、默認約束默認約束、非空定義非空定義、規(guī)則以及在建表時設置的數(shù)據(jù)類型規(guī)則以及在建表時設置的數(shù)據(jù)類型4）用戶定義的

26、完整性）用戶定義的完整性v用戶定義的完整性：是根據(jù)具體的應用領(lǐng)域所要遵循的約用戶定義的完整性：是根據(jù)具體的應用領(lǐng)域所要遵循的約束條件由用戶自己定義的特定的規(guī)則。束條件由用戶自己定義的特定的規(guī)則。25三、約束的類型三、約束的類型v約束：約束：SQL Server提供的自動強制數(shù)據(jù)完整性的一種方法。提供的自動強制數(shù)據(jù)完整性的一種方法。它通過定義列的取值規(guī)則來維護數(shù)據(jù)的完整性。當完整性約它通過定義列的取值規(guī)則來維護數(shù)據(jù)的完整性。當完整性約束創(chuàng)建之后，它作為基本表定義的一部分，存入束創(chuàng)建之后，它作為基本表定義的一部分，存入DD中。中。n列級完整性約束：列級完整性約束：是行定義的一部分，只能應用在一列是

27、行定義的一部分，只能應用在一列上；上；n表級完整性約束表級完整性約束：它的定義獨立于列的定義，可應用在：它的定義獨立于列的定義，可應用在一個基本表中的多列上。當需要在一個基本表中的多列一個基本表中的多列上。當需要在一個基本表中的多列上建立約束條件時，只能建立表級約束。上建立約束條件時，只能建立表級約束。v常用約束：常用約束：NOT NULL、CHECK、UNIQUE、PRIMARY KEY、FOREIGN KEY、DEFAULT261、主鍵、主鍵(PRIMARY KEY)約束約束主鍵約束：主鍵約束：在表中定義一個主鍵來惟一標識表中的每行記錄在表中定義一個主鍵來惟一標識表中的每行記錄v每個表中只

28、能有一個主鍵，主鍵可是一列，也可是多列；主每個表中只能有一個主鍵，主鍵可是一列，也可是多列；主鍵不能為空；主鍵值不能重復鍵不能為空；主鍵值不能重復v不能定義不能定義TEXTTEXT或或IMAGEIMAGE數(shù)據(jù)類型的字段列為主鍵。數(shù)據(jù)類型的字段列為主鍵。27vPRIMARY KEY約束可在列級或表級上進行定義，約束可在列級或表級上進行定義，但不允許同時在兩個級別上進行定義。但不允許同時在兩個級別上進行定義。v創(chuàng)建方法：創(chuàng)建方法： PRIMARY KEY PRIMARY KEY約束直接寫在列名及其類型之后；約束直接寫在列名及其類型之后； 按照語法在相應列名及類型后單獨列出：按照語法在相應列名及類型

29、后單獨列出：CONSTRAINT CONSTRAINT PRIMARY KEY PRIMARY KEY 在在CREATE TABLECREATE TABLE語句列出基本表的所有列之后，語句列出基本表的所有列之后， PRIMARY KEY(PRIMARY KEY(1， 2，)28SSMS創(chuàng)建主鍵的步驟創(chuàng)建主鍵的步驟 29創(chuàng)建表時定義主鍵創(chuàng)建表時定義主鍵例例1 1：列級約束：列級約束CREATE TABLE Tb_Class( ClassId char(8) PRIMARY KEY, ClassName varchar(30) NOT NULL, DeptId char(2) NOT NULL,

30、ClassStuNumber int) ;30例例2 2： CREATE TABLE C ( cno char(6) constraint PK_cno primary key, cname char(20), ccredit smallint, cpno char(6) );31例例3：CREATE TABLE Tb_Class( ClassId char(8) NOT NULL, ClassName varchar(30) NOT NULL, DeptId char(2) NOT NULL, ClassStuNumber int , CONSTRAINT PK_ClassId PRIMAR

31、Y KEY (ClassId) ;32向已有表中添加主鍵向已有表中添加主鍵先創(chuàng)建一個無主鍵的表先創(chuàng)建一個無主鍵的表CREATE TABLE Tb_Class( ClassId char(8) NOT NULL, ClassName varchar(30) NOT NULL, DeptId char(2) NOT NULL, ClassStuNumber int)-再給相應的列（再給相應的列（ ClassId ClassId ）添加主鍵約束）添加主鍵約束ALTER TABLE Tb_ClassADD CONSTRAINT PK_ClassId PRIMARY KEY (ClassId)33刪除主

32、鍵約束刪除主鍵約束ALTER TABLE Tb_ClassDROP CONSTRAINT PK_ClassId PRIMARY KEY (ClassId)342、外鍵（、外鍵（Foreign Key）約束）約束 外鍵：外鍵：出于某種關(guān)聯(lián)的需要，當一個表需要引用另一個表的出于某種關(guān)聯(lián)的需要，當一個表需要引用另一個表的主鍵作為自己的一個字段時，稱這個引用來的字段為外鍵。主鍵作為自己的一個字段時，稱這個引用來的字段為外鍵。 外鍵約束：外鍵約束：定義了表之間的關(guān)系，主要用來維護兩個表之間定義了表之間的關(guān)系，主要用來維護兩個表之間的一致性。的一致性。v當主鍵更新或刪除時，其它所有表中與這個主鍵關(guān)聯(lián)的外鍵

33、當主鍵更新或刪除時，其它所有表中與這個主鍵關(guān)聯(lián)的外鍵也將被相應的更新或刪除。也將被相應的更新或刪除。v當向外鍵所在的表插入或更新數(shù)據(jù)（外鍵字段）時，如果與當向外鍵所在的表插入或更新數(shù)據(jù)（外鍵字段）時，如果與外鍵表相關(guān)聯(lián)的主鍵表的主鍵中無與插入或更新的外鍵有相外鍵表相關(guān)聯(lián)的主鍵表的主鍵中無與插入或更新的外鍵有相同的值時，系統(tǒng)會報錯并拒絕插入或更新數(shù)據(jù)。同的值時，系統(tǒng)會報錯并拒絕插入或更新數(shù)據(jù)。35外鍵約束說明外鍵約束說明3637SSMS設置外鍵約束設置外鍵約束383940在創(chuàng)建表時定義外鍵約束在創(chuàng)建表時定義外鍵約束CREATE TABLE Tb_Dept( DeptId char(2) PRIM

34、ARY KEY, DeptName varchar(16) NOT NULL)-CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL, CONSTRAINT FK_DeptId FOREIGN KEY (DeptId) REFERENCES Tb_Dept (DeptId)41上述創(chuàng)建外鍵約束的語句可以簡化為如下形式：上述創(chuàng)建外鍵約束的語句可以簡化為如下形式：-CREATE TABLE Tb_Student( StuId char(10) PR

35、IMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL REFERENCES Tb_Dept )- 從添加外鍵約束時只需添加REFERENCES關(guān)鍵字和被引用的表名。（需要注意的是，外鍵列和被引用表中的主鍵列應該名稱相同，且具有相同的列數(shù)據(jù)類型）。在創(chuàng)建表時定義外鍵約束在創(chuàng)建表時定義外鍵約束42級聯(lián)更新、刪除級聯(lián)更新、刪除CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL RE

36、FERENCES Tb_Dept ON UPDATE CASCADE ON DELETE CASCADE)43CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL, CONSTRAINT FK_DeptId FOREIGN KEY (DeptId) REFERENCES Tb_Dept (DeptId) ON UPDATE CASCADE ON DELETE CASCADE)44添加和刪除外鍵約束添加和刪除外鍵約束先創(chuàng)建一個無外鍵的表先創(chuàng)建一個

37、無外鍵的表CREATE TABLE Tb_Student ( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL)再給相應的列（再給相應的列（ DeptId ）添加外鍵約束）添加外鍵約束ALTER TABLE Tb_StudentADD CONSTRAINT FK_DeptId FOREIGN KEY (DeptId) REFERENCES Tb_Dept (DeptId) 45刪除外鍵約束刪除外鍵約束ALTER TABLE Tb_StudentDROP CONSTRAINT FK_Dept

38、Id46參照完整性（通過參照完整性（通過外鍵約束外鍵約束來實現(xiàn)）來實現(xiàn)） v參照完整性參照完整性是指兩個表的主關(guān)鍵字（是指兩個表的主關(guān)鍵字（PRIMARY KEY）和）和外關(guān)鍵字（外關(guān)鍵字（FOREIGN KEY）的數(shù)據(jù)要對應一致）的數(shù)據(jù)要對應一致。它確保。它確保了有主關(guān)鍵字的表中對應其它表的外關(guān)鍵字的行存在，即了有主關(guān)鍵字的表中對應其它表的外關(guān)鍵字的行存在，即保證了表之間的數(shù)據(jù)的一致性，防止了數(shù)據(jù)丟失或無意義保證了表之間的數(shù)據(jù)的一致性，防止了數(shù)據(jù)丟失或無意義的數(shù)據(jù)在數(shù)據(jù)庫中擴散。的數(shù)據(jù)在數(shù)據(jù)庫中擴散。v參照完整性是建立在外關(guān)鍵字和主關(guān)鍵字之上的。參照完整性是建立在外關(guān)鍵字和主關(guān)鍵字之上的。n

39、在參照表（從表）中，不允許參照不存在的主鍵值。在參照表（從表）中，不允許參照不存在的主鍵值。n如果主鍵值更改了，那么在整個數(shù)據(jù)庫中，對該鍵值的如果主鍵值更改了，那么在整個數(shù)據(jù)庫中，對該鍵值的所有引用要進行一致的更改。所有引用要進行一致的更改。n在被參照表（主表）中，當其主鍵值被其他表所參照時，在被參照表（主表）中，當其主鍵值被其他表所參照時，該行不能被刪除也不允許改變。該行不能被刪除也不允許改變。47外鍵約束的作用外鍵約束的作用v在子表中插入數(shù)據(jù)時檢查（外鍵）在子表中插入數(shù)據(jù)時檢查（外鍵）v修改父表主鍵時檢查修改父表主鍵時檢查v刪除父表記錄時檢查刪除父表記錄時檢查483、唯一性（、唯一性（UN

40、IQUE）約束）約束v可以使用可以使用 UNIQUE 約束確保在非主鍵列中不輸入重復的約束確保在非主鍵列中不輸入重復的值。盡管值。盡管 UNIQUE 約束和約束和 PRIMARY KEY 約束都強制唯約束都強制唯一性，但想要強制一列或多列組合一性，但想要強制一列或多列組合（不是主鍵）（不是主鍵）的唯一性的唯一性時應使用時應使用 UNIQUE 約束而不是約束而不是 PRIMARY KEY 約束。約束。 v可以對一個表定義多個可以對一個表定義多個 UNIQUE 約束，但只能定義一個約束，但只能定義一個 PRIMARY KEY 約束。約束。vUNIQUE 約束允許約束允許 NULL 值，這與值，這與

41、 PRIMARY KEY 約束約束不同。不過，當與參與不同。不過，當與參與 UNIQUE 約束的任何值一起使用約束的任何值一起使用時，每列只允許一個空值。時，每列只允許一個空值。 vFOREIGN KEY 約束可以引用約束可以引用 UNIQUE 約束。約束。49v創(chuàng)建表時，可以創(chuàng)建創(chuàng)建表時，可以創(chuàng)建 UNIQUE 約束作為表定義的一部分。約束作為表定義的一部分。如果表已經(jīng)存在，可以添加如果表已經(jīng)存在，可以添加 UNIQUE 約束（約束（假設組成假設組成 UNIQUE 約束的列或列組合僅包含唯一的值約束的列或列組合僅包含唯一的值）。）。n默認情況下，向表中的現(xiàn)有列添加默認情況下，向表中的現(xiàn)有列添

42、加 UNIQUE 約束后，約束后，SQL Server 2005 Database Engine 將檢查列中的現(xiàn)將檢查列中的現(xiàn)有數(shù)據(jù)，以確保所有值都是唯一的。如果向含有重復值有數(shù)據(jù)，以確保所有值都是唯一的。如果向含有重復值的列添加的列添加 UNIQUE 約束，數(shù)據(jù)庫引擎約束，數(shù)據(jù)庫引擎 將返回錯誤消息，將返回錯誤消息，并且不添加約束。并且不添加約束。v若要修改若要修改 UNIQUE 約束，必須首先刪除現(xiàn)有的約束，必須首先刪除現(xiàn)有的 UNIQUE 約束，然后用新定義重新創(chuàng)建。約束，然后用新定義重新創(chuàng)建。 v除非顯式指定了聚集索引，否則，默認情況下將創(chuàng)建唯一除非顯式指定了聚集索引，否則，默認情況下

43、將創(chuàng)建唯一的非聚集索引以強制執(zhí)行的非聚集索引以強制執(zhí)行 UNIQUE 約束。約束。 唯一性約束唯一性約束50創(chuàng)建表時創(chuàng)建創(chuàng)建表時創(chuàng)建UNIQUE約束約束CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL UNIQUE NONCLUSTERED, DeptId char(2) NOT NULL REFERENCES Tb_Dept)-CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, De

44、ptId char(2) NOT NULL REFERENCES Tb_Dept, CONSTRAINT UK_StuName UNIQUE (StuName)51創(chuàng)建表結(jié)束后添加、刪除創(chuàng)建表結(jié)束后添加、刪除UNIQUE約束約束CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL REFERENCES Tb_Dept)-添加添加UNIQUE約束約束ALTER TABLE Tb_StudentADD CONSTRAINT UK_DepName U

45、NIQUE (DeptName)52刪除刪除UNIQUE約束約束ALTER TABLE Tb_Student DROP CONSTRAINT UK_DepName53SSMS創(chuàng)建惟一約束創(chuàng)建惟一約束544、默認值約束、默認值約束v如果插入行時沒有為列指定值，默認值則指定列如果插入行時沒有為列指定值，默認值則指定列中使用什么值。默認值可以是計算結(jié)果為常量的中使用什么值。默認值可以是計算結(jié)果為常量的任何值，例如常量、內(nèi)置函數(shù)或數(shù)學表達式。任何值，例如常量、內(nèi)置函數(shù)或數(shù)學表達式。v若要應用默認值，可以通過在若要應用默認值，可以通過在 CREATE TABLE CREATE TABLE 中中使用使用

46、DEFAULT DEFAULT 關(guān)鍵字來創(chuàng)建默認值定義。關(guān)鍵字來創(chuàng)建默認值定義。v默認值必須與要應用默認值必須與要應用 DEFAULT DEFAULT 定義的列的數(shù)據(jù)定義的列的數(shù)據(jù)類型相配。例如，類型相配。例如，int int 列的默認值必須是整數(shù)，列的默認值必須是整數(shù)，而不能是字符串。而不能是字符串。 55CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL REFERENCES Tb_Dept, Sex char(2) NOT NULL D

47、EFAULT M, BIRTHDAY SMALLDATETIME NOT NULL DEFAULT getdate()默認值約束默認值約束56添加默認值約束添加默認值約束ALTER TABLE Tb_StudentADD CONSTRAINT DEF_Sex DEFAULT M FOR Sex刪除默認值約束刪除默認值約束ALTER TABLE Tb_Student DROP CONSTRAINT DEF_Sex57SSMS中設置默認值約束中設置默認值約束585、CHECK約束約束 v通過限制列可接受的值，通過限制列可接受的值，CHECK 約束可以強制約束可以強制域的完整性。域的完整性。vCHE

48、CK約束類似于約束類似于 FOREIGN KEY 約束，因為約束，因為可以控制放入列中的值。但兩者在確定有效值的可以控制放入列中的值。但兩者在確定有效值的方式上有所不同：方式上有所不同：nFOREIGN KEY 約束從其他表獲得有效值列表約束從其他表獲得有效值列表nCHECK 約束通過不基于其他列中的數(shù)據(jù)的邏約束通過不基于其他列中的數(shù)據(jù)的邏輯表達式確定有效值輯表達式確定有效值59v可通過任何基于邏輯運算符返回可通過任何基于邏輯運算符返回 TRUE 或或 FALSE 的邏輯（布爾）表達式創(chuàng)建的邏輯（布爾）表達式創(chuàng)建 CHECK 約束。約束。n例如，邏輯表達式為：例如，邏輯表達式為：grade=

49、0 AND grade = 0 and Score = 100) vCHECK ( EmpId LIKE A-ZA-ZA-Z1-90-90-CHECK ( EmpId LIKE A-ZA-ZA-Z1-90-90-90-90-9FM OR EmpId LIKE A-Z-A-Z1-90-90-9FM OR EmpId LIKE A-Z-A-Z1-90-90-90-90-9FM ) 90-90-90-90-9FM ) vCHECK (TelNo IN (86022679, 86022235, CHECK (TelNo IN (86022679, 86022235, 86022879, 8602288

50、6, 86028225) OR TelNo 86022879, 86022886, 86028225) OR TelNo LIKE 8602210-90-9) LIKE 8602210-90-9) vCHECK (Salary BETWEEN 3000 AND 10000)CHECK (Salary BETWEEN 3000 AND 10000)vCHECK (IS_MANAGER = 1 AND Sex = F )CHECK (IS_MANAGER = 1 AND Sex = F )vCHECK (CHECK (CASECASE WHEN WHEN Is_Manager 1 AND Sex

51、= FIs_Manager 1 AND Sex = F THENTHEN 1 1 ELSEELSE 0 0 ENDEND) )64添加和刪除添加和刪除CHECK約束約束添加添加CHECKCHECK約束約束 ALTER TABLEALTER TABLE Tb_Student Tb_Student WITH NOCHECKWITH NOCHECK ADD CONSTRAINTADD CONSTRAINT CK_Sex CK_Sex CHECKCHECK (Sex IN (M,F) ; (Sex IN (M,F) ; 刪除刪除CHECKCHECK約束約束ALTER TABLEALTER TABLE

52、Tb_Student Tb_Student DROP CONSTRAINTDROP CONSTRAINT CK_Sex CK_Sex 65使用使用SSMS創(chuàng)建創(chuàng)建刪除刪除CHECK約束約束 66NOT NULL或或NULL約束約束vNOT NULLNOT NULL約束不允許列值為空，而約束不允許列值為空，而NULLNULL約束允許約束允許列值為空。列值為空。67v刪除基本表刪除基本表用用DROP TABLE語句刪除基本表。其一般格式為：語句刪除基本表。其一般格式為： DROP TABLE RESTRICT | CASCADE； 缺省情況是缺省情況是RESTRICT。 若選擇若選擇RESTRIC

53、T：則該表的刪除是有限制條件的。：則該表的刪除是有限制條件的。欲刪除的基本表不能被其他表的約束所引用（如欲刪除的基本表不能被其他表的約束所引用（如CHECK，F(xiàn)OREIGN KEY等約束），不能有視圖，不能有觸發(fā)器（等約束），不能有視圖，不能有觸發(fā)器（trigger），不能有存儲過程），不能有存儲過程或函數(shù)等。如果存在這些依賴該表的對象，則此表不能被刪除?；蚝瘮?shù)等。如果存在這些依賴該表的對象，則此表不能被刪除。 若選擇若選擇CASCADE：則該表的刪除沒有限制條件。在刪除基本表的同時，：則該表的刪除沒有限制條件。在刪除基本表的同時，相關(guān)的依賴對象，例如視圖，都將被一起刪除。相關(guān)的依賴對象，例如

54、視圖，都將被一起刪除。 例例4.8 刪除刪除S表。表。 DROP TABLE S CASCADE; 684.2.3 建立與刪除索引建立與刪除索引 拼音檢索法 筆畫檢索法69v索引：包含索引：包含鍵鍵（表或視圖中的一個或多個列所生（表或視圖中的一個或多個列所生成），以及成），以及指針指針（映射到數(shù)據(jù)的存儲位置的指針）（映射到數(shù)據(jù)的存儲位置的指針）n索引是一個單獨的，物理的數(shù)據(jù)庫結(jié)構(gòu)，依賴表而建，索引是一個單獨的，物理的數(shù)據(jù)庫結(jié)構(gòu)，依賴表而建，提供編排表中數(shù)據(jù)的內(nèi)部方法提供編排表中數(shù)據(jù)的內(nèi)部方法n一個表的存儲由兩部分組成：存儲數(shù)據(jù)的頁面，存儲索一個表的存儲由兩部分組成：存儲數(shù)據(jù)的頁面，存儲索引的頁

55、面引的頁面n建立索引，提高查詢速度，但是過多索引，會占據(jù)過多建立索引，提高查詢速度，但是過多索引，會占據(jù)過多的磁盤空間的磁盤空間n創(chuàng)建索引的列：常用，外鍵或主鍵，值唯一創(chuàng)建索引的列：常用，外鍵或主鍵，值唯一70v建立索引是加快查詢速度的有效手段建立索引是加快查詢速度的有效手段n通過創(chuàng)建設計良好的索引以支持查詢，可以顯著提高數(shù)通過創(chuàng)建設計良好的索引以支持查詢，可以顯著提高數(shù)據(jù)庫查詢和應用程序的性能。據(jù)庫查詢和應用程序的性能。n索引可以減少為返回查詢結(jié)果集而必須讀取的數(shù)據(jù)量。索引可以減少為返回查詢結(jié)果集而必須讀取的數(shù)據(jù)量。索引還可以強制表中的行具有唯一性，從而確保表數(shù)據(jù)索引還可以強制表中的行具有唯

56、一性，從而確保表數(shù)據(jù)的數(shù)據(jù)完整性。的數(shù)據(jù)完整性。 711、創(chuàng)建索引的原則、創(chuàng)建索引的原則 索引的創(chuàng)建和維護由索引的創(chuàng)建和維護由DBADBA和和DBMSDBMS完成完成v索引由索引由DBADBA或表的擁有者負責創(chuàng)建和撤消，其他用或表的擁有者負責創(chuàng)建和撤消，其他用戶不能隨意創(chuàng)建和撤消索引。戶不能隨意創(chuàng)建和撤消索引。v索引由系統(tǒng)自動選擇和維護。索引由系統(tǒng)自動選擇和維護。( (不需要用戶指定使不需要用戶指定使用索引，也不需要用戶打開索引或?qū)λ饕龍?zhí)行重索用索引，也不需要用戶打開索引或?qū)λ饕龍?zhí)行重索引操作，這些工作都由引操作，這些工作都由DBMSDBMS自動完成自動完成) )。 72 是否創(chuàng)建索引取決于表

57、的數(shù)據(jù)量大小和對查詢的是否創(chuàng)建索引取決于表的數(shù)據(jù)量大小和對查詢的要求要求v基本表中記錄的數(shù)量越多，記錄越長，越有必要創(chuàng)基本表中記錄的數(shù)量越多，記錄越長，越有必要創(chuàng)建索引，創(chuàng)建索引后加快查詢速度的效果越明顯。建索引，創(chuàng)建索引后加快查詢速度的效果越明顯。v記錄較少的基本表，創(chuàng)建索引意義不大。記錄較少的基本表，創(chuàng)建索引意義不大。v索引要根據(jù)數(shù)據(jù)查詢或處理的要求而創(chuàng)建索引要根據(jù)數(shù)據(jù)查詢或處理的要求而創(chuàng)建( (對那些對那些查詢頻度高、實時性要求高的數(shù)據(jù)一定要建立索引，查詢頻度高、實時性要求高的數(shù)據(jù)一定要建立索引，否則不必考慮創(chuàng)建索引的問題否則不必考慮創(chuàng)建索引的問題) )。 73 一個基本表，不要建過多索

58、引一個基本表，不要建過多索引v索引文件占用文件目錄和存儲空間，索引過多會使索引文件占用文件目錄和存儲空間，索引過多會使系統(tǒng)負擔加重。系統(tǒng)負擔加重。v索引需要自身維護，當基本表的數(shù)據(jù)增加、刪除或索引需要自身維護，當基本表的數(shù)據(jù)增加、刪除或修改時，索引文件要隨之變化，以保持與基本表一修改時，索引文件要隨之變化，以保持與基本表一致。致。v索引過多會影響數(shù)據(jù)增、刪、改的速度。索引過多會影響數(shù)據(jù)增、刪、改的速度。74 包含太多重復值的列；包含太多重復值的列； 查詢中很少被引用的列；查詢中很少被引用的列； 值特別長的列；值特別長的列； 查詢返回率很高的列；查詢返回率很高的列； 具有很多具有很多NULLNU

59、LL值的列；值的列； 需要經(jīng)常插、刪、改的列；需要經(jīng)常插、刪、改的列； 記錄較少的基本表；記錄較少的基本表； 需進行頻繁、大批量數(shù)據(jù)更新的基本表。需進行頻繁、大批量數(shù)據(jù)更新的基本表。752、建立索引、建立索引 在在SQL語言中，建立索引使用語言中，建立索引使用CREATE INDEX語句，其一語句，其一般格式為：般格式為： CREATE UNIQUECLUSTERED INDEX ON (，)； 索引可以建立在該表的一列或多列上，各列名之間用逗號分索引可以建立在該表的一列或多列上，各列名之間用逗號分隔。每個隔。每個 后面還可以用后面還可以用指定索引值的排列次序，指定索引值的排列次序，可選可選A

60、SC（升序，默認是升序）或（升序，默認是升序）或DESC（降序）。（降序）。UNIQUE表明此索引的每一個索引值只對應唯一的數(shù)據(jù)記錄。表明此索引的每一個索引值只對應唯一的數(shù)據(jù)記錄。 CLUSTERED表示要建立的索引是聚簇索引，所謂聚簇索表示要建立的索引是聚簇索引，所謂聚簇索引是指索引項的順序與表中記錄的物理順序一致的索引組織。引是指索引項的順序與表中記錄的物理順序一致的索引組織。76（1）聚簇索引）聚簇索引(或稱為聚集索引或稱為聚集索引)v聚簇索引在數(shù)據(jù)表中按照物理順序存儲數(shù)據(jù)。因為在表中聚簇索引在數(shù)據(jù)表中按照物理順序存儲數(shù)據(jù)。因為在表中只有一個物理順序，所以在每個表中只能有一個簇索引。只有