數(shù)據(jù)庫技術及應用第3章 結構化查詢語言SQL

3.1.1SQL的產生與發(fā)展SQL(StructuredQueryLanguage)最早是1974年由Boyce等人提出并在IBM公司的SystemR中實現(xiàn)，后來成為關系數(shù)據(jù)庫的標準語言。ANSI公布了SQL86→SQL89→SQL92→SQL99

(SQL2)

(SQL3)SQL92是一次非常重要的SQL語言標準的升級，其文檔達到了622頁，與SQL89的120頁文檔相比，標準的內容增加了許多。SQL92標準有4個層次，即入門級、過渡級、中間級、完備級。

目前，SQL的標準化工作還在繼續(xù)，已經發(fā)布的標準有SQL2003、SQL2008、SQL2011和SQL2016。目前的RDBMS產品基本上都支持SQL92的入門級，如果使用了SQL92過渡級、中間級或完備級里的特性，或者使用了SQL99里的特性，就可能存在無法“移植”應用的風險。

3.1.2SQL的功能與特點

(1)綜合統(tǒng)一它集DDL、DML和DCL功能于一體，語言風格統(tǒng)一(2)面向集合的操作方式操作對象和操作的結果都是元組的集合(3)高度非過程化只要提出“做什么”，不必指出“怎么做”，無需了解存取路徑(4)以同一種語法結構提供兩種使用方式

SQL既是自含式語言，又是嵌入式語言(5)支持三級模式結構用戶1用戶2用戶3視圖1視圖2基表1基表2基表3基表4存儲文件1存儲文件2外模式模式內模式

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除SQL語言沒有創(chuàng)建數(shù)據(jù)庫的語句。在介紹T-SQL中的CREATEDATABASE語句之前，先介紹SQLServer數(shù)據(jù)庫的結構。1、SQLServer數(shù)據(jù)庫結構數(shù)據(jù)庫分為兩個層次：

物理數(shù)據(jù)庫是面向操作系統(tǒng)的，由數(shù)據(jù)文件與日志文件、文件組、盤區(qū)與頁等組成；

邏輯數(shù)據(jù)庫是面向用戶的，由表、約束、默認值、規(guī)則、用戶自定義數(shù)據(jù)類型、索引、視圖、用戶、角色、存儲過程、觸發(fā)器等一系列數(shù)據(jù)庫對象組成。

(1)數(shù)據(jù)文件一個數(shù)據(jù)庫必須有且只能有一個主數(shù)據(jù)文件，擴展名為mdf而輔助數(shù)據(jù)文件可以有多個，也可以沒有，擴展名為ndf

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除(2)日志文件每個數(shù)據(jù)庫必須至少有一個日志文件，也可以有多個，擴展名為ldf(3)文件組文件組是數(shù)據(jù)庫中數(shù)據(jù)文件的邏輯組合，一個數(shù)據(jù)文件只能屬于一個文件組。日志文件是獨立存在的，不屬于任何文件組。主文件組（Primary）每個數(shù)據(jù)庫有且僅有一個主文件組。輔助文件組可以有若干個，也可以沒有。默認文件組是沒有分配文件組的用戶自定義對象的首選文件組，每個數(shù)據(jù)庫只能有一個默認文件組。注意，默認文件組和主文件組不是同一個概念，數(shù)據(jù)庫初始建立時，主文件組是默認文件組。

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除(4)盤區(qū)與頁每個盤區(qū)由8個連續(xù)頁組成，大小為8KB×8=64KB。當創(chuàng)建一個數(shù)據(jù)庫對象（如一個表、一個索引）時，SQLServer自動以盤區(qū)為單位給它們分配存儲空間。每個盤區(qū)只能包含一個數(shù)據(jù)庫對象，每個數(shù)據(jù)庫對象可以占用多個盤區(qū)。頁的大小為8KB，每頁開始部分的96個字節(jié)是頁頭信息，其余的8096個字節(jié)用于存放該頁數(shù)據(jù)庫對象的數(shù)據(jù)信息。SQLServer2014中的頁分為數(shù)據(jù)頁、索引頁、文本頁、圖像頁等8種。

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除2、CREATEDATABASE語句T-SQL語言中CREATEDATABASE語句格式如下：

CREATEDATABASE<數(shù)據(jù)庫名>[ON[primary]<文件說明>[,…n][,<文件組說明>[,…n]][LOGON<文件說明>[,…n]]]例3.1

創(chuàng)建一個數(shù)據(jù)庫test1，其他所有參數(shù)都取默認值。

CREATEDATABASEtest1

T-SQL語言是大小寫不敏感的語句執(zhí)行后會自動創(chuàng)建一個數(shù)據(jù)文件、一個日志文件、一個文件組。

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除2、CREATEDATABASE語句例3.2

創(chuàng)建一個數(shù)據(jù)庫test2，要求主數(shù)據(jù)文件邏輯名為test2_data，物理文件名為d:\database\test2_data.mdf，其他所有參數(shù)都取默認值。

CREATEDATABASEtest2ON(name=test2_data,filename='d:\database\test2_data.mdf')

說明：邏輯文件名是指在T-SQL語句中引用文件時使用的名稱。創(chuàng)建數(shù)據(jù)庫時，用戶可以只指定數(shù)據(jù)文件，而不指定日志文件；但不可以不指定數(shù)據(jù)文件，而只指定日志文件。

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除2、CREATEDATABASE語句例3.3

創(chuàng)建一個數(shù)據(jù)庫test3，要求：①主數(shù)據(jù)文件邏輯名為test3_data，物理文件名為d:\database\test3_data.mdf，文件初始大小為10MB，最大容量不受限制，文件增長量為2MB；②日志文件邏輯名為test3_log，物理文件名為d:\database\test3_log.ldf，文件初始大小為5MB，最大容量為10MB，文件增長量為5%。

CREATEDATABASEtest3ON(name=test3_data,filename='d:\database\test3_data.mdf',size=10,maxsize=unlimited,filegrowth=2)LOGON(name=test3_log,filename='d:\database\test3_log.ldf',size=5,maxsize=10,filegrowth=5%)

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除2、CREATEDATABASE語句例3.4

創(chuàng)建一個數(shù)據(jù)庫test4，要求：①數(shù)據(jù)文件有4個，其邏輯名分別為test4a_data、test4b_data、test4c_data、test4d_data，物理文件都放在d:\database文件夾中，其文件名分別為test4a.mdf、test4b.ndf、test4c.ndf、test4d.ndf，文件其他所有參數(shù)都取默認值；②文件test4a_data組成主文件組primary，文件test4b_data和test4c_data組成輔助文件組group1，文件test4d_data組成輔助文件組group2；③日志文件邏輯名為test4_log，物理文件名為d:\database\test4.ldf，文件其他所有參數(shù)都取默認值。

CREATEDATABASEtest4ONprimary(name=test4a_data,filename='d:\database\test4a.mdf'),filegroupgroup1(name=test4b_data,filename='d:\database\test4b.ndf'),(name=test4c_data,filename='d:\database\test4c.ndf'),filegroupgroup2(name=test4d_data,filename='d:\database\test4d.ndf')LOGON(name=test4_log,filename='d:\database\test4.ldf')

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除3、CREATESCHEMA語句

模式（Schema）實際上是定義了一個命名空間，在這個空間中可以定義該模式包含的數(shù)據(jù)庫對象，如基本表、視圖等T-SQL語言支持CREATESCHEMA語句，創(chuàng)建的模式（T-SQL語言稱為架構）都屬于當前數(shù)據(jù)庫，該語句格式如下：

CREATESCHEMA<模式名>[AUTHORIZATION<所有者名>][{<表定義子句>|<視圖定義子句>|<授權定義子句>}[,…n]]例3.5

為test2數(shù)據(jù)庫中的用戶dbo創(chuàng)建一個模式Study。

CREATESCHEMAStudyAUTHORIZATIONdbo例3.6

為test2數(shù)據(jù)庫中的用戶dbo創(chuàng)建一個模式Exam，并在其中定義一張表Table1。

CREATESCHEMAExamAUTHORIZATIONdboCREATETABLETable1(Tnosmallint,Cnamevarchar(20),Resultchar(2))

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除4、ALTERDATABASE語句T-SQL語言提供了修改數(shù)據(jù)庫語句ALTERDATABASE，通過該語句可以增加或刪除數(shù)據(jù)文件（或日志文件），增加或刪除文件組，修改文件或文件組的屬性，也可以重命名文件組名或數(shù)據(jù)庫名。格式如下：

ALTERDATABASE<數(shù)據(jù)庫名>{ADDFILE<文件說明>[,…n][TOFILEGROUP<文件組名>]|ADDLOGFILE[<文件說明>[,…n]]|REMOVEFILE<邏輯文件名>|ADDFILEGROUP<文件組名>|REMOVEFILEGROUP<文件組名>|MODIFYFILE<文件說明>|MODIFYFILEGROUP<文件組名>{<文件組屬性>|<NAME=文件組新名>}|MODIFYNAME=<數(shù)據(jù)庫新名>}文件組屬性有只讀Readonly、可讀寫Readwrite以及默認文件組Default三種，主文件組不能設置為只讀。

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除4、ALTERDATABASE語句例3.7

將例3.2中數(shù)據(jù)庫test2的主數(shù)據(jù)文件的最大容量改為6MB，文件增長量改為10%。

ALTERDATABASEtest2MODIFYFILE(name=test2_data,maxsize=6,filegrowth=10%)例3.8

給例3.3中數(shù)據(jù)庫test3添加一個包含兩個輔助數(shù)據(jù)文件的輔助文件組usergroup。

ALTERDATABASEtest3ADDFILEGROUPusergroupGOALTERDATABASEtest3ADDFILE(name=test3a_data,filename='d:\database\test3a_data.ndf'),(name=test3b_data,filename='d:\database\test3b_data.ndf')TOFILEGROUPusergroup

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除4、ALTERDATABASE語句例3.9

刪除例3.4中數(shù)據(jù)庫test4中的數(shù)據(jù)文件test4d_data和文件組group2。

ALTERDATABASEtest4REMOVEFILEtest4d_dataGOALTERDATABASEtest4REMOVEFILEGROUPgroup2

說明：必須先刪除數(shù)據(jù)文件，再刪除文件組。刪除數(shù)據(jù)文件時，用的是文件的邏輯名，而不是物理名。

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除5、ALTERSCHEMA語句T-SQL語言提供了修改模式語句ALTERSCHEMA，通過該語句可以在同一數(shù)據(jù)庫中的模式之間移動對象。ALTERSCHEMA語句格式如下：

ALTERSCHEMA<模式名>TRANSFER<對象名>例3.10

將數(shù)據(jù)庫test2中的模式Exam中的表Table1移到模式Study中。

ALTERSCHEMAStudyTRANSFERExam.Table16、DROPDATABASE語句

DROPDATABASE<數(shù)據(jù)庫名>例3.11

刪除數(shù)據(jù)庫test4。

DROPDATABASEtest4

3.2.1數(shù)據(jù)庫的創(chuàng)建、修改與刪除7、DROPSCHEMA語句

DROPSCHEMA<模式名>{CASCADE|RESTRICT}

說明：語句中的CASCADE和RESTRICT兩者必選其一。T-SQL語言支持DROPSCHEMA語句，但被刪除的模式中不能包含任何對象，所以該語句不能有CASCADE或RESTRICT選項。例3.12

刪除數(shù)據(jù)庫test2中的模式Exam和Study。

DROPSCHEMAExamGODROPTABLEStudy.Table1GODROPSCHEMAStudy當代數(shù)據(jù)庫系統(tǒng)提供了三層結構的關系（表）命名機制。最頂層的由目錄（Catalog）構成（在一些RDBMS的實現(xiàn)中用術語“數(shù)據(jù)庫”代替術語“目錄”），每個目錄都可以包含模式，諸如表和視圖等SQL對象都包含在模式中。每個連接到RDBMS的用戶都有一個默認的目錄和模式，如果用戶想訪問其他目錄和模式中的表，那么必須指定目錄名和模式名。

3.2.2SQL中的數(shù)據(jù)類型SQL語言中的數(shù)據(jù)類型是指數(shù)據(jù)在計算機內的表現(xiàn)形式，包括數(shù)據(jù)的存儲格式、分配的字節(jié)數(shù)、取值范圍、數(shù)據(jù)的精度和小數(shù)位數(shù)以及可參加的運算。1、基本數(shù)據(jù)類型SQL標準定義了多種主要的數(shù)據(jù)類型，如表3.1所示。2、SQLServer2014中的數(shù)據(jù)類型(1)字符類型char(n)用于存儲定長字符串，1≤n≤8000varchar(n)用于存儲可變長度字符串，1≤n≤8000text可用于存儲最多231-1個字節(jié)的非Unicode字符數(shù)據(jù)注意，char(4)能存儲4個英文字母，但只能存儲2個漢字

(2)Unicode字符類型Unicode字符類型中的每個字符都采用2字節(jié)編碼

3.2.2SQL中的數(shù)據(jù)類型nchar(n)用于存儲定長Unicode字符串，1≤n≤4000nvarchar(n)用于存儲可變長度Unicode字符串，1≤n≤4000ntext可用于存儲最多230-1個Unicode字符數(shù)據(jù)

注意，nchar(4)能存儲4個英文字母，也能存儲4個漢字。(3)bit類型bit類型只能用于存儲1、0或null，占用1個比特的存儲空間。如果一個表中只有一個bit類型的列，則也要占用1個字節(jié)；如果一個表中有小于等于8個bit類型的列，則這些列作為1個字節(jié)存儲；如果有9~16個bit類型的列，則這些列作為2個字節(jié)存儲，以此類推。

3.2.2SQL中的數(shù)據(jù)類型(4)整數(shù)類型tinyint用于存儲0~255之間的整數(shù)，占用1個字節(jié)smallint用于存儲-215~215-1之間的整數(shù)，占用2個字節(jié)int用于存儲-231~231-1之間的整數(shù)，占用4個字節(jié)bigint用于存儲-263~263-1之間的整數(shù)，占用8個字節(jié)(5)精確數(shù)值類型精確數(shù)值類型為decimal[(p[,s])]≡numeric[(p[,s])]用于存儲-1038+1~1038-1之間的數(shù)據(jù)。它在使用時可以指定精度p（1≤p≤38）和小數(shù)位數(shù)s（0≤s≤p），默認精度p為18，默認小數(shù)位數(shù)s為0。

3.2.2SQL中的數(shù)據(jù)類型(6)近似（

浮點）數(shù)值類型real用于存儲-3.40E+38~3.40E+38之間的浮點數(shù)，占用4個字節(jié)float[(n)]用于存儲-1.79E+308~1.79E+308之間的浮點數(shù)，1≤n≤53，n的默認值為53

注意，real的SQL92同義詞為float(24)，doubleprecision的同義詞為float(53)，但SQLServer2014本身沒有doubleprecision類型。(7)日期時間類型等價于SQL2003標準中的timestamp類型smalldatetime用于存儲1900年1月1日~2079年6月6日的日期時間，精度為1分鐘，占用4個字節(jié)datetime用于存儲1753年1月1日~9999年12月31日的日期時間，精度為3.33毫秒，占用8個字節(jié)

3.2.2SQL中的數(shù)據(jù)類型(8)貨幣類型smallmoney存儲-214748.3648~214748.3647之間的數(shù)money：-922337203685477.5808~922337203685477.5807之間的數(shù)(9)二進制數(shù)據(jù)類型二進制數(shù)據(jù)類型用于存儲圖像、聲音等數(shù)據(jù)，有3種類型：binary(n)用于存儲定長二進制數(shù)據(jù)，1≤n≤8000varbinary(n)用于存儲可變長二進制數(shù)據(jù)，1≤n≤8000image可用于存儲最多231-1個字節(jié)的二進制數(shù)據(jù)，如一首歌曲或一部電影。

3.2.2SQL中的數(shù)據(jù)類型最后要說明的是：(1)SQLServer的未來版本中將刪除text、ntext和image數(shù)據(jù)類型，分別改為varchar(max)、nvarchar(max)和varbinary(max)數(shù)據(jù)類型。SQL標準中提供了與此相關的數(shù)據(jù)類型，稱為“大對象類型”。大對象類型有clob和blob兩種，前者用于存放字符數(shù)據(jù)，而后者用于存放二進制數(shù)據(jù)。顯然，T-SQL語言在支持大對象類型時采用了非標準格式。(2)SQLServer2014中也有timestamp數(shù)據(jù)類型，但它不同于SQL2003標準中定義的timestamp數(shù)據(jù)類型，存儲大小為8個字節(jié)。一個表只能有一個timestamp列，每次插入（或修改）包含timestamp列的行時，就會在timestamp列中插入（或修改）時間戳值（它是一個相對時間，而不是與時鐘相關聯(lián)的實際時間），以便確定該行中的任何值自上次讀取以后是否發(fā)生了修改。

3.2.2SQL中的數(shù)據(jù)類型3、用戶自定義的數(shù)據(jù)類型

兩種用戶自定義數(shù)據(jù)類型：獨特類型和結構化數(shù)據(jù)類型

SQLServer將獨特類型的用戶自定義數(shù)據(jù)類型稱為別名數(shù)據(jù)類型。下面舉例說明如何用sp_addtype和CREATETYPE創(chuàng)建別名數(shù)據(jù)類型。例3.13

創(chuàng)建別名數(shù)據(jù)類型ssn，它基于char(8)，并且不允許取空值。

EXECsp_addtypessn,'char(8)','NOTNULL'

或

CREATETYPEssnFROMchar(8)NOTNULL需要說明的是：(1)用戶可以刪除創(chuàng)建的別名數(shù)據(jù)類型。如例3.13中創(chuàng)建的ssn可以用EXECsp_droptypessn或DROPTYPEssn刪除。

3.2.2SQL中的數(shù)據(jù)類型(2)盡量不使用sp_addtype，改為使用CREATETYPE語句。另外，SQLServer2014中系統(tǒng)將自動授予PUBLIC數(shù)據(jù)庫角色對通過使用sp_addtype創(chuàng)建的別名數(shù)據(jù)類型具有REFERENCES權限，當使用CREATETYPE時不是。(3)就創(chuàng)建獨特類型的用戶自定義數(shù)據(jù)類型而言，SQLServer2014中的CREATETYPE語句與SQL99標準中的CREATETYPE語句用法相似，其他情況下用法差異較大。(4)SQLServer2014不支持SQL92標準的CREATEDOMAIN語句來創(chuàng)建域，域與類型相似但又有差異，具體請參考相關文獻。

3.2.3基本表的創(chuàng)建、修改與刪除基本表是實際存在的表，在數(shù)據(jù)庫中既要存放它的定義（即基本表的結構），又要存放它的數(shù)據(jù)，基本表的定義存放在數(shù)據(jù)庫的數(shù)據(jù)字典中。在SQLServer中，基本表分為系統(tǒng)表和用戶表兩種。系統(tǒng)表中的數(shù)據(jù)構成了SQLServer系統(tǒng)的數(shù)據(jù)字典，用戶不能直接修改系統(tǒng)表，也不能直接檢索系統(tǒng)表中的信息，如確要檢索存儲在系統(tǒng)表中的信息，應通過系統(tǒng)視圖（sys開頭或INFORMATION_SCHEMA開頭的視圖）來進行。用戶表又分為永久表和臨時表兩種。永久表存儲在用戶數(shù)據(jù)庫中，臨時表存儲在tempdb數(shù)據(jù)庫中。本地臨時表的表名以#開頭，僅對連接數(shù)據(jù)庫的當前用戶有效，用戶斷開連接，自動刪除。全局臨時表的表名以##開頭，對連接數(shù)據(jù)庫的所有用戶有效，所有用戶斷開連接，才自動刪除。

3.2.3基本表的創(chuàng)建、修改與刪除1、CREATETABLE語句T-SQL語言中CREATETABLE語句格式如下：

CREATETABLE[<數(shù)據(jù)庫名>.[<模式名>].|<模式名>.]<表名>(<列名><數(shù)據(jù)類型>[<列級完整性約束條件>][,…n][,<表級完整性約束條件>[,…n]][ON{文件組名|default}])說明：在創(chuàng)建表時，如果缺省數(shù)據(jù)庫名，則創(chuàng)建在當前數(shù)據(jù)庫中；如果缺省模式名，則創(chuàng)建的表屬于當前用戶的默認模式。

3.2.3基本表的創(chuàng)建、修改與刪除例3.14

創(chuàng)建一張名為S的學生表。

CREATETABLES(Snochar(8),Snamechar(10),Ssexchar(2),Sagetinyint,Majorchar(8))實際應用常常要求創(chuàng)建與現(xiàn)有的某個表的模式（結構）相同的表，SQL語言提供了CREATETABLELIKE語句來支持這項任務。如CREATETABLES_CopyLIKES。MySQL5.5支持CREATETABLELIKE語句，但SQLServer2014不支持該語句。

3.2.3基本表的創(chuàng)建、修改與刪除2、ALTERTABLE語句修改表不僅能修改表的結構，還能增加約束、刪除約束等。T-SQL語言中ALTERTABLE語句格式如下：

ALTERTABLE[<數(shù)據(jù)庫名>.[<模式名>].|<模式名>.]<表名>{ADD{<列名><數(shù)據(jù)類型>[<列級約束>]|<表級約束條件>}[,…n]|DROP{COLUMN<列名>|[CONSTRAINT]<約束名>}[,…n]|ALTERCOLUMN<列名><數(shù)據(jù)類型>}例3.15

修改例3.14中創(chuàng)建的S表，給它增加Address和Mphone列，同時將Major列的數(shù)據(jù)類型改為char(12)。

ALTERTABLESADDAddressvarchar(50),Mphonechar(11)GOALTERTABLESALTERCOLUMNMajorchar(12)

3.2.3基本表的創(chuàng)建、修改與刪除3、DROPTABLE語句可以刪除表，但不能刪除系統(tǒng)表和外碼約束所參照的表，如果確實需要刪除，必須先刪除外碼約束或參照表。刪除表的同時會刪除表中的所有數(shù)據(jù)以及表相關的索引、約束、觸發(fā)器和指定的權限。任何引用已刪除表的視圖或存儲過程都必須顯式刪除。T-SQL語言中DROPTABLE語句格式如下：

DROPTABLE[<數(shù)據(jù)庫名>.[<模式名>].|<模式名>.]<表名>[,…n]例3.16

刪除例3.14中創(chuàng)建的S表。

DROPTABLES

3.3.1數(shù)據(jù)庫完整性的概念數(shù)據(jù)庫的完整性是指數(shù)據(jù)的正確性和相容性。所謂正確性是指數(shù)據(jù)是有效的，有意義的，而不是荒謬的或不符合實際的。所謂相容性是指數(shù)據(jù)之間不能相互矛盾。關系數(shù)據(jù)庫中數(shù)據(jù)的正確性和相容性是通過關系模型中的完整性約束條件來保證的。由DBMS實現(xiàn)完整性約束條件就有效減輕了程序員的負擔，既提高了完整性檢測的效率又可以防止漏檢。為了維護數(shù)據(jù)庫的完整性，DBMS必須：(1)提供定義完整性約束條件的機制。這一機制是由SQL語言中的DDL語句（如CREATETABLE語句）提供的。(2)提供完整性檢查的方法。(3)違約處理。目前商用的DBMS產品都支持完整性控制，即DBMS都有完整性定義、完整性檢查和違約處理這三方面的機制。

3.3.2各類完整性約束的實現(xiàn)1、實體完整性約束CREATETABLE語句中的PRIMARYKEY短語實現(xiàn)。當主碼由單個屬性構成時，PRIMARYKEY可以定義為列級約束條件，也可以定義為表級約束條件。當主碼由多個屬性構成時，PRIMARYKEY必須定義為表級約束條件。定義了實體完整性約束后，當插入或修改操作使得表中屬性的取值違反實體完整性約束時，系統(tǒng)一般采用拒絕執(zhí)行方式處理。2、參照完整性約束CREATETABLE語句中的FOREIGNKEY…REFERENCES短語實現(xiàn)。當外碼由單個屬性構成時，F(xiàn)OREIGNKEY可以定義為列級約束條件，也可以定義為表級約束條件。當外碼由多個屬性構成時，F(xiàn)OREIGNKEY必須定義為表級約束條件。

3.3.2各類完整性約束的實現(xiàn)在定義參照完整性約束時，參照表的外碼的列數(shù)與被參照表主碼的列數(shù)必須相同，并且對應列的數(shù)據(jù)類型也必須相同，但是外碼的列名與被參照表主碼的列名不必相同。下表給出了可能破壞參照完整性的情況及違約處理方式。下面介紹違約處理方式(1)拒絕（NOACTION）執(zhí)行，即不允許執(zhí)行該操作，一般為默認違約處理方式。(2)級聯(lián)（CASCADE）操作，例如，刪除了S中學號為16001的學生，自動刪除SC中所有學號為16001的元組。被參照表（例如S）參照表（例如SC）違約處理方式可能破壞插入元組拒絕可能破壞修改外碼值拒絕刪除元組可能破壞拒絕/級聯(lián)刪除/設置為空值修改主碼值可能破壞拒絕/級聯(lián)修改/設置為空值

3.3.2各類完整性約束的實現(xiàn)(3)設置為空值（SETNULL），表示當刪除或修改被參照表的一個元組造成了參照表中某些元組的外碼違反了參照完整性約束，則系統(tǒng)會自動將參照表中所有違反參照完整性約束的元組的外碼設置為空值。設有下列關系模式：專業(yè)（專業(yè)號，專業(yè)名，創(chuàng)辦日期，所屬學院）學生（學號，姓名，性別，年齡，專業(yè)號，班長）課程（課程號，課程名，課程類型，學時，學分）選修（學號，課程號，選修日期，成績）

例如，當專業(yè)表中專業(yè)號為14的元組被刪除后，學生表中專業(yè)號為14的所有元組的專業(yè)號（外碼）設置為空值。

要說明的是，有時不能選擇“設置為空值”這種處理方式。例如，當學生表中學號為16001的學生被刪除后，選修表中的學號（外碼）不能設置為空值。因為選修表中的學號既是外碼又是本表主碼中的屬性，如果主碼中的屬性取空值，就違反了實體完整性約束。

3.3.2各類完整性約束的實現(xiàn)3、用戶定義的完整性約束用戶定義的完整性就是針對某一具體應用的數(shù)據(jù)必須滿足的語義要求，具體反映在三個方面：①單個屬性上；②同一個表中各屬性之間（即元組上）；③多個表中各屬性之間。當插入或修改操作使得表中屬性的取值違反用戶定義的完整性約束時，系統(tǒng)一般采用拒絕執(zhí)行方式處理。在SQL語言中，列級（單個屬性上）和表級（元組上）完整性約束都有NOTNULL、UNIQUE和CHECK三種形式。另外，T-SQL語言中的DEFAULT也可以理解為列級完整性約束。多個表中各屬性之間的完整性約束（除了參照完整性約束）的實現(xiàn)一般可采用觸發(fā)器機制。

3.3.2各類完整性約束的實現(xiàn)4、各類完整性約束實現(xiàn)舉例

例3.17

創(chuàng)建學生表S、課程表C和教師表T，同時定義各表的主碼并給相關屬性加上必要的約束條件，以實現(xiàn)實體完整性約束和用戶定義的完整性約束。

CREATETABLES(Snochar(8)PRIMARYKEY,/*實體完整性*/Snamechar(10)NOTNULL,/*用戶定義的完整性*/Ssexchar(2)CHECK(SsexIN('男','女')),/*用戶定義的完整性*/Sagetinyint,Majorchar(8),Address varchar(50),/*下面也是用戶定義的完整性*/Mphone char(11)CONSTRAINTuq_SUNIQUE)

3.3.2各類完整性約束的實現(xiàn)4、各類完整性約束實現(xiàn)舉例

例3.18

創(chuàng)建選修表SC和授課表TC，同時定義各表的主碼和外碼以實現(xiàn)實體完整性約束和參照完整性約束。

CREATETABLESC(Snochar(8)FOREIGNKEYREFERENCESS(Sno)ONUPDATECASCADE,Cnochar(6),Sdatechar(6),Scoretinyint,CONSTRAINTpk_SCPRIMARYKEY(Sno,Cno,Sdate),FOREIGNKEY(Cno,Sdate)REFERENCESTC(Cno,Tdate)ONDELETECASCADEONUPDATECASCADE)注意，SC表中的外碼（Cno,Sdate）與被參照表TC中的主碼（Cno,Tdate）的屬性名可以不同

3.3.3表中完整性約束的增加與刪除

在SQLServer2014中不管對表級還是列級約束條件，只要用戶沒有給約束條件命名，系統(tǒng)都會自動給約束條件命名，如圖3.2所示。例3.19

如果規(guī)定：①職稱不是教授的教師必須在60歲退休；②對教師授課的評價在0到10分之間；③學生選修某門課程的成績在0到100分之間，請給已經創(chuàng)建的基本表T、TC和SC增加這些完整性約束條件。

ALTERTABLETADDCONSTRAINTck_TCHECK(Title!='教授'ANDTage<60ORTitle='教授')ALTERTABLETCADDCONSTRAINTck_TCCHECK(RemarkBETWEEN0AND10)ALTERTABLESCADDCONSTRAINTck_SCCHECK(ScoreBETWEEN0AND100)

3.3.3表中完整性約束的增加與刪除

例3.20

如果規(guī)定對教師授課的評價在0到5分之間，請修改例3.19中的完整性約束條件ck_TC。

ALTERTABLETCDROPCONSTRAINTck_TCALTERTABLETCADDCONSTRAINTck_TCCHECK(RemarkBETWEEN0AND5)

3.4數(shù)據(jù)查詢

查詢操作是數(shù)據(jù)庫的核心操作，SQL語言提供了SELECT語句實現(xiàn)對數(shù)據(jù)庫的查詢，該語句的一般格式如下：

SELECT[ALL|DISTINCT]<目標列表達式>[,…]FROM<表名或視圖名>[,…][WHERE<元組篩選條件>][GROUPBY<列名>[,…][HAVING<小組篩選條件>]][ORDERBY<列名>[ASC|DESC][,…]]

3.4.1單表查詢

1、SELECT子句例3.21

找出每個學生的學號、姓名和出生年份。

SELECTSno,Sname,2018-SageASBirthYear--AS可省略

FROMS例3.22

找出全體學生所學專業(yè)的專業(yè)名。

SELECTDISTINCTMajor--默認是ALL

FROMS例3.23

找出全體學生的詳細信息。

SELECT*--*表示S表中的所有列都要輸出顯示

FROMS

說明：輸出顯示的順序與創(chuàng)建S表時的屬性列的順序一致。如果順序不一致，還是要把屬性一個一個列出來，如：

SELECTSno,Sname,Ssex,Sage,Mphone,Major,AddressFROMS

3.4.1單表查詢

2、WHERE子句例3.24

找出計算機專業(yè)中年齡為21歲的學生的學號、姓名和聯(lián)系電話。

SELECTSno,Sname,MphoneFROMSWHEREMajor='計算機'ANDSage=21

SQL標準中，字符串上的比較運算是大小寫敏感的，例如‘Computer’=‘COMputer’結果是假，但SQLServer2014在默認情況下不區(qū)分大小寫例3.25

找出年齡在30~50歲（包括30歲和50歲）之間的教師的工號、姓名和職稱。

SELECTTno,Tname,TitleFROMTWHERETageBETWEEN30AND50

說明：條件不在30~50歲之間表示為TageNOTBETWEEN30AND50，等價于Tage<30ORTage>50。等價于30<=TageANDTage<=50

3.4.1單表查詢

2、WHERE子句例3.26

找出職稱是教授或副教授的教師的工號、姓名和年齡。

SELECTTno,Tname,TageFROMTWHERETitleIN('教授','副教授')

說明：條件職稱不是教授或副教授表示為TitleNOTIN('教授','副教授')，等價于Title!='教授'ANDTitle!='副教授'

字符匹配運算符LIKE用來確定某字符串是否與指定的模式串相匹配，其一般格式如下：

<待匹配的字符串表達式>[NOT]LIKE<模式串>[ESCAPE<轉義字符>]

需要說明的是：SQLServer2014中文版在默認情況下，模式串中的普通字符與待匹配的字符串表達式中指定的字符匹配是不區(qū)分大小寫的，“_”既可以表示一個ASCII字符，也可以表示一個漢字，%（百分號）表示任意長度（長度可以為0）的字符串。等價于Title='教授'ORTitle='副教授'

3.4.1單表查詢

2、WHERE子句例3.27

找出家在上海市的學生的學號、姓名和家庭地址。

SELECTSno,Sname,AddressFROMSWHEREAddressLIKE'上海市%'例3.28

找出職稱不是教授或副教授的教師的工號、姓名和年齡。

SELECTTno,Tname,TageFROMTWHERETitleNOTLIKE'%教授'例3.29

找出姓名中第二個字是“文”的學生的學號、姓名和家庭地址。

SELECTSno,Sname,AddressFROMSWHERESnameLIKE'_文%'

說明：姓名是“張文杰”或“李文”的學生均滿足查詢條件。但姓名是“歐陽文杰”的學生不滿足查詢條件

3.4.1單表查詢

2、WHERE子句如果待匹配的字符串表達式中本身就含有通配符%或_，這時就要使用ESCAPE<轉義字符>，對通配符進行轉義。例3.30

找出課程名以“C_”開頭的課程的課程號、課程名和學分。

SELECTCno,Cname,CreditFROMCWHERECnameLIKE'C\_%'ESCAPE'\'

說明：由于在通配符_前有轉義字符\，所以該_被轉義為普通的_字符，而它后面的%仍然作為通配符。例3.31

找出專業(yè)還沒有確定的學生的詳細信息。

SELECT*FROMSWHEREMajorISNULL--不能寫成Major=NULL

說明：條件“專業(yè)已經確定”應寫成MajorISNOTNULL。

3.4.1單表查詢

SQL92規(guī)定對空值NULL進行運算時使用下列規(guī)則：(1)NULL值和其他任何值（包括另一個NULL值）進行算術運算（＋、－、×、÷）時，其結果為NULL；(2)NULL值和其他任何值（包括另一個NULL值）進行比較運算（>、>=、<、<=）時，其結果為UNKNOWN。在SQLServer2014中，如果一個元組對應它的元組篩選條件計算出的結果值為“UNKNOWN”，那么該元組不會出現(xiàn)在結果集中。

3.4.1單表查詢

3、ORDERBY子句例3.32

找出所有課程的課程號、課程名和學分，查詢結果按學分降序排列。

SELECTCno,Cname,CreditFROMCORDERBYCreditDESC--默認是ASC

說明：對于Credit值為空值的元組，排序時的次序由具體系統(tǒng)實現(xiàn)來決定例3.33

找出每個學生的姓名、專業(yè)和出生年份，查詢結果按專業(yè)升序排列，專業(yè)相同按出生年份降序排列。

SELECTSname,Major,2018-SageFROMSORDERBYMajor,3DESC

3.4.1單表查詢

4、聚集函數(shù)和GROUPBY子句

例3.34

找出教師的總人數(shù)。

SELECTCOUNT(*)FROMT例3.35

找出選修了1002號課程的學生人數(shù)。

SELECTCOUNT(DISTINCTSno)--如果缺省，默認是ALL

FROMSCWHERECno='1002'例3.36

找出2017年秋1001號課程成績的最高分、最低分、平均分以及選修人數(shù)。

SELECTMAX(Score),MIN(Score),AVG(Score),COUNT(Sno)FROMSCWHERESdate='2017秋'ANDCno='1001'

需要說明的是：①在聚集函數(shù)遇到空值時，除COUNT(*)外，都會忽略空值而只處理非空值；②WHERE子句的元組篩選條件中不能出現(xiàn)聚集函數(shù)。

3.4.1單表查詢

4、聚集函數(shù)和GROUPBY子句

例3.37

找出2017年秋各門課程成績的最高分、最低分、平均分以及選修人數(shù)。

SELECTCno,MAX(Score),MIN(Score),AVG(Score),COUNT(Sno)FROMSCWHERESdate='2017秋'

GROUPBYCno例3.38

找出各個學期各門課程成績的最高分、最低分、平均分以及選修人數(shù)，查詢結果按選修日期升序排列，選修日期相同按課程號升序排列。

SELECTSdate,Cno,MAX(Score),MIN(Score),AVG(Score),COUNT(Sno)FROMSC

GROUPBYSdate,CnoORDERBYSdate,Cno

3.4.1單表查詢

4、聚集函數(shù)和GROUPBY子句

需要說明的是：分組時出現(xiàn)在SELECT子句中但沒有被聚集的屬性只能是出現(xiàn)在GROUPBY子句中的那些屬性。例如，下面的查詢就是錯誤的，因為Tno沒有出現(xiàn)在GROUPBY子句中，但它出現(xiàn)在SELECT子句中，而且沒有被聚集。

SELECTTitle,Tno,AVG(Salary)FROMTGROUPBYTitle5、HAVING子句例3.39

找出從未有過考試成績不及格的學生的學號。

SELECTSnoFROMSC

GROUPBYSnoHAVINGMIN(Score)>=60

說明：①出現(xiàn)在HAVING子句中但沒有被聚集的屬性只能是出現(xiàn)在GROUPBY子句中的那些屬性；②HAVING子句只能出現(xiàn)在GROUPBY子句的后面，在沒有GROUPBY子句的情況下，出現(xiàn)HAVING子句是沒有意義的。

3.4.1單表查詢

5、HAVING子句如果在同一個查詢語句中同時出現(xiàn)WHERE子句與HAVING子句時，它們的區(qū)別在于：WHERE子句作用于元組，從中選擇滿足條件的元組；而HAVING子句作用于小組，從中選擇滿足條件的小組。例3.40

找出至少有過2次考試成績不及格的學生的學號。

SELECTSnoFROMSC

WHEREScore<60GROUPBYSno

HAVINGCOUNT(*)>=2思考：找出2017年秋門門課程成績在85分以上且平均分在90分以上的學生學號

SELECTSnoFROMSC

WHERESdate='2017秋'GROUPBYSno

HAVINGMIN(Score)>85ANDAVG(Score)>90

3.4.2連接查詢凡查詢條件或結果涉及到多個表時，就需要將多個表連接起來形成一個包含條件和結果中涉及的全部數(shù)據(jù)的臨時表，再對該臨時表用上面單表查詢的方法進行查詢。連接兩個表一定要有連接條件，連接條件中一般都有外碼與被參照表的主碼。當外碼與被參照表的主碼同名時，必須加上表名前綴，即表名.列名，否則會引起“列名不明確”錯誤。1、內連接在SQL語言的早期標準中沒有連接運算符，也不區(qū)分等值連接和自然連接，連接條件寫在WHERE子句中。例3.41

找出學生信息以及他（她）選修課程的信息。

SELECT*--改為S.*,Cno,Sdate,Score自然連接

FROMS,SCWHERES.Sno=SC.Sno--等值連接

注意理解連接操作的執(zhí)行過程

3.4.2連接查詢在SQL92標準中，引進了JOIN運算符，例3.41中的查詢可改寫為：

SELECT*FROMSINNERJOINSCONS.Sno=SC.Sno

其中關鍵字INNER可以省略不寫。引入ON子句有兩個優(yōu)點：①對于馬上要介紹的外連接來說，ON子句的表現(xiàn)與WHERE子句是不同的；②用ON子句指定連接條件，用WHERE子句指定其余的查詢條件，這樣的SQL查詢更容易讓人理解。例3.42

找出選修了“數(shù)據(jù)庫原理”課程的學生的學號、姓名、選修日期和成績。

SELECTS.Sno,Sname,Sdate,ScoreFROMSJOINSCONS.Sno=SC.SnoJOINCONSC.Cno=C.CnoWHERECname='數(shù)據(jù)庫原理'

三張表的連接操作查詢條件

3.4.2連接查詢2、自連接連接操作可以在兩張不同的表之間進行，也可以是一張表與自己進行連接，這種連接稱為自連接。例3.43

找出至少選修過1001號課程和1002號課程的學生的學號。

SELECTDISTINCTA.SnoFROMSCASAJOINSCASBONA.Sno=B.Sno--AS可以省略

WHEREA.Cno='1001'ANDB.Cno='1002'

SCASCB

SnoCnoSdateScore

SnoCnoSdateScore1600310022017春781600310022017春781600320022017秋861600320022017秋861600710012017秋851600710012017秋851600710022016秋831600710022016秋831600720022017秋911600720022017秋91

3.4.2連接查詢3、外連接如“例3.41”中由于16005號學生沒有選修課程，在SC表中沒有相應的元組，造成該生的信息在連接操作時被丟棄了。為了在查詢結果關系中保留該生的信息，就需要使用外連接。例3.44

用左外連接改寫例3.41中的查詢要求，保留沒有選修課程的學生信息。

SELECT*FROMSLEFTOUTERJOINSCONS.Sno=SC.Sno說明：關鍵字OUTER可以省略不寫。右外連接和全外連接的運算符分別為RIGHTOUTERJOIN和FULLOUTERJOIN。本例也充分說明了ON子句不能用WHERE子句來代替，因為ON子句是表達外連接運算的一部分，而WHERE子句卻不是。

3.4.2連接查詢4、連接的類型與條件自連接本質上它不是一種新的連接類型，只不過連接的對象是自身，所以連接分為內連接和外連接兩種。SQL早期標準中把連接條件寫在WHERE子句中，而SQL新標準中連接條件可以有三種表達方法。從表達的能力和靈活性來說，ON子句表達的能力強且靈活（因為ON后面的是任意一個合法的邏輯表達式）；而NATURAL或者USING子句只能對被連接的兩表中的同名列做“=”比較，但書寫比較簡潔、方便。注意，SQLServer2014不支持用NATURAL或者USING子句表達連接條件，而MySQL5.5則支持。

要說明的是，如果SELECT語句中缺省連接條件，那么本質上做的就是關系代數(shù)中的廣義笛卡爾積。例如，SELECT*FROMR,S實現(xiàn)的就是關系R和S的廣義笛卡爾積。

3.4.3嵌套查詢在SQL語言中，一個SELECT－FROM－WHERE語句稱為一個查詢塊，將一個查詢塊嵌套在另一個查詢塊的WHERE子句或者其他子句中的查詢稱為嵌套查詢，這也正是“結構化”的含義所在。1、帶有IN謂詞的子查詢例3.46

找出選修了1001號課程的學生的姓名和性別。

SELECTSname,SsexFROMSWHERESnoIN(SELECTSnoFROMSCWHERECno='1001')

子查詢的結果往往是元組的集合，本例中為('16001','16004','16007')所以謂詞IN是嵌套查詢中最常用的謂詞。SELECTSname,SsexFROMSJOINSCONS.Sno=SC.SnoWHERECno='1001'不同方法的執(zhí)行效率可能會有差別，甚至相差很大，這取決于實際RDBMS的優(yōu)化算法

子查詢中不能使用ORDERBY子句

3.4.3嵌套查詢例3.47

找出選修了“數(shù)據(jù)庫原理”課程的學生的姓名和性別。

SELECTSname,SsexFROMSWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCWHERECname='數(shù)據(jù)庫原理'))當查詢結果表中的列來自兩張或兩張以上的表時，就必須要用連接查詢來實現(xiàn)，而不能用嵌套查詢來實現(xiàn)，例3.42就是這樣。其次，有時嵌套查詢也不一定能用連接查詢來實現(xiàn)，例3.58就是這樣；有時嵌套查詢比連接查詢更容易實現(xiàn)，例3.48和3.49就是這樣。

用連接查詢來實現(xiàn)

SELECTSname,SsexFROMSJOINSCONS.Sno=SC.SnoJOINCONSC.Cno=C.CnoWHERECname='數(shù)據(jù)庫原理'

3.4.3嵌套查詢例3.48

用嵌套查詢重做例3.43中的重新要求。

SELECTDISTINCTSnoFROMSCWHERECno='1001'ANDSnoIN(SELECTSnoFROMSCWHERECno='1002')例3.49

找出沒有選修過1001號課程的學生的姓名和性別。

SELECTSname,SsexFROMSWHERESnoNOTIN(SELECTSnoFROMSCWHERECno='1001')

3.4.3嵌套查詢2、帶有比較運算符的子查詢例3.50

找出與“張文杰”在同一個專業(yè)學習的學生的姓名、性別和年齡。

SELECTSname,Ssex,SageFROMSWHEREMajor=(SELECTMajorFROMSWHERESname='張文杰')

說明：這里假定姓名為“張文杰”的學生只有一個，如果不能肯定，保險起見，還是用IN代替=。另外，本例也可以用自連接實現(xiàn)，留作練習。例3.51

找出工資低于全體教師的平均工資的教師的姓名、年齡和職稱。

SELECTTname,Tage,TitleFROMTWHERESalary<(SELECTAVG(Salary)FROMT)

3.4.3嵌套查詢2、帶有比較運算符的子查詢例3.52

找出每個學生選修某課程的平均成績低于全體學生選修該課程的平均成績的學號和課程號。

SELECTSno,CnoFROMSCAGROUPBYSno,CnoHAVINGAVG(Score)<(SELECTAVG(Score)FROMSCBWHEREB.Cno=A.Cno)

說明：前面的舉例中，子查詢中的查詢條件不依賴于父查詢，這類子查詢稱為不相關子查詢。反之，如果子查詢中的查詢條件依賴于父查詢，這類子查詢稱為相關子查詢（CorrelatedSubquery）。

相關子查詢的執(zhí)行過程與不相關子查詢很不相同，而類似于C語言中for語句嵌套的執(zhí)行過程。

3.4.3嵌套查詢3、帶有SOME或ALL謂詞的子查詢例3.53

找出其他專業(yè)中比通信專業(yè)某些學生年齡小的學生姓名和年齡。

SELECTSname,SageFROMSWHEREMajor!='通信'ANDSage<SOME(SELECTSageFROMSWHEREMajor='通信')

本例也可以用聚集函數(shù)來實現(xiàn)，SQL語句如下：

SELECTSname,SageFROMSWHEREMajor!='通信'ANDSage<(SELECTMAX(Sage)FROMSWHEREMajor='通信')集合(20,21)

SOME（早期為ANY

）表示一組值中的某些值，ALL表示一組值中的全部值。

3.4.3嵌套查詢3、帶有SOME或ALL謂詞的子查詢例3.54

找出其他專業(yè)中比通信專業(yè)所有學生年齡都小的學生姓名和年齡。

SELECTSname,SageFROMSWHEREMajor!='通信'ANDSage<ALL(SELECTSageFROMSWHEREMajor='通信') =!=或<> < <= > >=SOMEIN － <MAX<=MAX>MIN>=MINALL－NOTIN<MIN <=MIN>MAX>=MAX(SELECTMIN(Sage)

3.4.3嵌套查詢3、帶有SOME或ALL謂詞的子查詢有了上述對照表，可能有人會認為沒有必要使用帶SOME或ALL謂詞的子查詢，這種想法是不對的。例3.55

找出所有考試