數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用第3章 結(jié)構(gòu)化查詢語(yǔ)言SQL

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

(SQL2)

(SQL3)SQL92是一次非常重要的SQL語(yǔ)言標(biāo)準(zhǔn)的升級(jí)，其文檔達(dá)到了622頁(yè)，與SQL89的120頁(yè)文檔相比，標(biāo)準(zhǔn)的內(nèi)容增加了許多。SQL92標(biāo)準(zhǔn)有4個(gè)層次，即入門(mén)級(jí)、過(guò)渡級(jí)、中間級(jí)、完備級(jí)。

目前，SQL的標(biāo)準(zhǔn)化工作還在繼續(xù)，已經(jīng)發(fā)布的標(biāo)準(zhǔn)有SQL2003、SQL2008、SQL2011和SQL2016。目前的RDBMS產(chǎn)品基本上都支持SQL92的入門(mén)級(jí)，如果使用了SQL92過(guò)渡級(jí)、中間級(jí)或完備級(jí)里的特性，或者使用了SQL99里的特性，就可能存在無(wú)法“移植”應(yīng)用的風(fēng)險(xiǎn)。

3.1.2SQL的功能與特點(diǎn)

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

SQL既是自含式語(yǔ)言，又是嵌入式語(yǔ)言(5)支持三級(jí)模式結(jié)構(gòu)用戶1用戶2用戶3視圖1視圖2基表1基表2基表3基表4存儲(chǔ)文件1存儲(chǔ)文件2外模式模式內(nèi)模式

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

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

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

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

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

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

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

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

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

CREATEDATABASEtest1

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

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

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

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

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

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

創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)test3，要求：①主數(shù)據(jù)文件邏輯名為test3_data，物理文件名為d:\database\test3_data.mdf，文件初始大小為10MB，最大容量不受限制，文件增長(zhǎng)量為2MB；②日志文件邏輯名為test3_log，物理文件名為d:\database\test3_log.ldf，文件初始大小為5MB，最大容量為10MB，文件增長(zhǎng)量為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ù)庫(kù)的創(chuàng)建、修改與刪除2、CREATEDATABASE語(yǔ)句例3.4

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

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ù)庫(kù)的創(chuàng)建、修改與刪除3、CREATESCHEMA語(yǔ)句

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

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

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

CREATESCHEMAStudyAUTHORIZATIONdbo例3.6

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

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

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

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

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

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

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

給例3.3中數(shù)據(jù)庫(kù)test3添加一個(gè)包含兩個(gè)輔助數(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ù)庫(kù)的創(chuàng)建、修改與刪除4、ALTERDATABASE語(yǔ)句例3.9

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

ALTERDATABASEtest4REMOVEFILEtest4d_dataGOALTERDATABASEtest4REMOVEFILEGROUPgroup2

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

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

ALTERSCHEMA<模式名>TRANSFER<對(duì)象名>例3.10

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

ALTERSCHEMAStudyTRANSFERExam.Table16、DROPDATABASE語(yǔ)句

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

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

DROPDATABASEtest4

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

DROPSCHEMA<模式名>{CASCADE|RESTRICT}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

兩種用戶自定義數(shù)據(jù)類型：獨(dú)特類型和結(jié)構(gòu)化數(shù)據(jù)類型

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

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

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

或

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

DROPTABLES

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

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

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

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

例如，當(dāng)專業(yè)表中專業(yè)號(hào)為14的元組被刪除后，學(xué)生表中專業(yè)號(hào)為14的所有元組的專業(yè)號(hào)（外碼）設(shè)置為空值。

要說(shuō)明的是，有時(shí)不能選擇“設(shè)置為空值”這種處理方式。例如，當(dāng)學(xué)生表中學(xué)號(hào)為16001的學(xué)生被刪除后，選修表中的學(xué)號(hào)（外碼）不能設(shè)置為空值。因?yàn)檫x修表中的學(xué)號(hào)既是外碼又是本表主碼中的屬性，如果主碼中的屬性取空值，就違反了實(shí)體完整性約束。

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

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

例3.17

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

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

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

例3.18

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

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中不管對(duì)表級(jí)還是列級(jí)約束條件，只要用戶沒(méi)有給約束條件命名，系統(tǒng)都會(huì)自動(dòng)給約束條件命名，如圖3.2所示。例3.19

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

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

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

例3.20

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

ALTERTABLETCDROPCONSTRAINTck_TCALTERTABLETCADDCONSTRAINTck_TCCHECK(RemarkBETWEEN0AND5)

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

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

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

3.4.1單表查詢

1、SELECT子句例3.21

找出每個(gè)學(xué)生的學(xué)號(hào)、姓名和出生年份。

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

FROMS例3.22

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

SELECTDISTINCTMajor--默認(rèn)是ALL

FROMS例3.23

找出全體學(xué)生的詳細(xì)信息。

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

FROMS

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

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

3.4.1單表查詢

2、WHERE子句例3.24

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

SELECTSno,Sname,MphoneFROMSWHEREMajor='計(jì)算機(jī)'ANDSage=21

SQL標(biāo)準(zhǔn)中，字符串上的比較運(yùn)算是大小寫(xiě)敏感的，例如‘Computer’=‘COMputer’結(jié)果是假，但SQLServer2014在默認(rèn)情況下不區(qū)分大小寫(xiě)例3.25

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

SELECTTno,Tname,TitleFROMTWHERETageBETWEEN30AND50

說(shuō)明：條件不在30~50歲之間表示為T(mén)ageNOTBETWEEN30AND50，等價(jià)于Tage<30ORTage>50。等價(jià)于30<=TageANDTage<=50

3.4.1單表查詢

2、WHERE子句例3.26

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

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

說(shuō)明：條件職稱不是教授或副教授表示為T(mén)itleNOTIN('教授','副教授')，等價(jià)于Title!='教授'ANDTitle!='副教授'

字符匹配運(yùn)算符LIKE用來(lái)確定某字符串是否與指定的模式串相匹配，其一般格式如下：

<待匹配的字符串表達(dá)式>[NOT]LIKE<模式串>[ESCAPE<轉(zhuǎn)義字符>]

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

3.4.1單表查詢

2、WHERE子句例3.27

找出家在上海市的學(xué)生的學(xué)號(hào)、姓名和家庭地址。

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

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

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

找出姓名中第二個(gè)字是“文”的學(xué)生的學(xué)號(hào)、姓名和家庭地址。

SELECTSno,Sname,AddressFROMSWHERESnameLIKE'_文%'

說(shuō)明：姓名是“張文杰”或“李文”的學(xué)生均滿足查詢條件。但姓名是“歐陽(yáng)文杰”的學(xué)生不滿足查詢條件

3.4.1單表查詢

2、WHERE子句如果待匹配的字符串表達(dá)式中本身就含有通配符%或_，這時(shí)就要使用ESCAPE<轉(zhuǎn)義字符>，對(duì)通配符進(jìn)行轉(zhuǎn)義。例3.30

找出課程名以“C_”開(kāi)頭的課程的課程號(hào)、課程名和學(xué)分。

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

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

找出專業(yè)還沒(méi)有確定的學(xué)生的詳細(xì)信息。

SELECT*FROMSWHEREMajorISNULL--不能寫(xiě)成Major=NULL

說(shuō)明：條件“專業(yè)已經(jīng)確定”應(yīng)寫(xiě)成MajorISNOTNULL。

3.4.1單表查詢

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

3.4.1單表查詢

3、ORDERBY子句例3.32

找出所有課程的課程號(hào)、課程名和學(xué)分，查詢結(jié)果按學(xué)分降序排列。

SELECTCno,Cname,CreditFROMCORDERBYCreditDESC--默認(rèn)是ASC

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

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

SELECTSname,Major,2018-SageFROMSORDERBYMajor,3DESC

3.4.1單表查詢

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

例3.34

找出教師的總?cè)藬?shù)。

SELECTCOUNT(*)FROMT例3.35

找出選修了1002號(hào)課程的學(xué)生人數(shù)。

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

FROMSCWHERECno='1002'例3.36

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

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

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

3.4.1單表查詢

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

例3.37

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

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

GROUPBYCno例3.38

找出各個(gè)學(xué)期各門(mén)課程成績(jī)的最高分、最低分、平均分以及選修人數(shù)，查詢結(jié)果按選修日期升序排列，選修日期相同按課程號(hào)升序排列。

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

GROUPBYSdate,CnoORDERBYSdate,Cno

3.4.1單表查詢

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

需要說(shuō)明的是：分組時(shí)出現(xiàn)在SELECT子句中但沒(méi)有被聚集的屬性只能是出現(xiàn)在GROUPBY子句中的那些屬性。例如，下面的查詢就是錯(cuò)誤的，因?yàn)門(mén)no沒(méi)有出現(xiàn)在GROUPBY子句中，但它出現(xiàn)在SELECT子句中，而且沒(méi)有被聚集。

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

找出從未有過(guò)考試成績(jī)不及格的學(xué)生的學(xué)號(hào)。

SELECTSnoFROMSC

GROUPBYSnoHAVINGMIN(Score)>=60

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

3.4.1單表查詢

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

找出至少有過(guò)2次考試成績(jī)不及格的學(xué)生的學(xué)號(hào)。

SELECTSnoFROMSC

WHEREScore<60GROUPBYSno

HAVINGCOUNT(*)>=2思考：找出2017年秋門(mén)門(mén)課程成績(jī)?cè)?5分以上且平均分在90分以上的學(xué)生學(xué)號(hào)

SELECTSnoFROMSC

WHERESdate='2017秋'GROUPBYSno

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

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

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

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

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

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

3.4.2連接查詢?cè)赟QL92標(biāo)準(zhǔn)中，引進(jìn)了JOIN運(yùn)算符，例3.41中的查詢可改寫(xiě)為：

SELECT*FROMSINNERJOINSCONS.Sno=SC.Sno

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

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

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

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

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

找出至少選修過(guò)1001號(hào)課程和1002號(hào)課程的學(xué)生的學(xué)號(hào)。

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號(hào)學(xué)生沒(méi)有選修課程，在SC表中沒(méi)有相應(yīng)的元組，造成該生的信息在連接操作時(shí)被丟棄了。為了在查詢結(jié)果關(guān)系中保留該生的信息，就需要使用外連接。例3.44

用左外連接改寫(xiě)例3.41中的查詢要求，保留沒(méi)有選修課程的學(xué)生信息。

SELECT*FROMSLEFTOUTERJOINSCONS.Sno=SC.Sno說(shuō)明：關(guān)鍵字OUTER可以省略不寫(xiě)。右外連接和全外連接的運(yùn)算符分別為RIGHTOUTERJOIN和FULLOUTERJOIN。本例也充分說(shuō)明了ON子句不能用WHERE子句來(lái)代替，因?yàn)镺N子句是表達(dá)外連接運(yùn)算的一部分，而WHERE子句卻不是。

3.4.2連接查詢4、連接的類型與條件自連接本質(zhì)上它不是一種新的連接類型，只不過(guò)連接的對(duì)象是自身，所以連接分為內(nèi)連接和外連接兩種。SQL早期標(biāo)準(zhǔn)中把連接條件寫(xiě)在WHERE子句中，而SQL新標(biāo)準(zhǔn)中連接條件可以有三種表達(dá)方法。從表達(dá)的能力和靈活性來(lái)說(shuō)，ON子句表達(dá)的能力強(qiáng)且靈活（因?yàn)镺N后面的是任意一個(gè)合法的邏輯表達(dá)式）；而NATURAL或者USING子句只能對(duì)被連接的兩表中的同名列做“=”比較，但書(shū)寫(xiě)比較簡(jiǎn)潔、方便。注意，SQLServer2014不支持用NATURAL或者USING子句表達(dá)連接條件，而MySQL5.5則支持。

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

3.4.3嵌套查詢?cè)赟QL語(yǔ)言中，一個(gè)SELECT－FROM－WHERE語(yǔ)句稱為一個(gè)查詢塊，將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或者其他子句中的查詢稱為嵌套查詢，這也正是“結(jié)構(gòu)化”的含義所在。1、帶有IN謂詞的子查詢例3.46

找出選修了1001號(hào)課程的學(xué)生的姓名和性別。

SELECTSname,SsexFROMSWHERESnoIN(SELECTSnoFROMSCWHERECno='1001')

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

子查詢中不能使用ORDERBY子句

3.4.3嵌套查詢例3.47

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

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

用連接查詢來(lái)實(shí)現(xiàn)

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

3.4.3嵌套查詢例3.48

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

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

找出沒(méi)有選修過(guò)1001號(hào)課程的學(xué)生的姓名和性別。

SELECTSname,SsexFROMSWHERESnoNOTIN(SELECTSnoFROMSCWHERECno='1001')

3.4.3嵌套查詢2、帶有比較運(yùn)算符的子查詢例3.50

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

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

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

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

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

3.4.3嵌套查詢2、帶有比較運(yùn)算符的子查詢例3.52

找出每個(gè)學(xué)生選修某課程的平均成績(jī)低于全體學(xué)生選修該課程的平均成績(jī)的學(xué)號(hào)和課程號(hào)。

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

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

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

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

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

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

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

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

SOME（早期為ANY

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

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

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

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

3.4.3嵌套查詢3、帶有SOME或ALL謂詞的子查詢有了上述對(duì)照表，可能有人會(huì)認(rèn)為沒(méi)有必要使用帶SOME或ALL謂詞的子查詢，這種想法是不對(duì)的。例3.55

找出所有考試