第7章SQLServer2008的T-SQLIChapter7T-SQL

IofSQLServer2008

本章重點(diǎn)本章主要介紹了SQLServer的數(shù)據(jù)庫(kù)、數(shù)據(jù)表、數(shù)據(jù)更新、數(shù)據(jù)查詢(xún)、視圖和函數(shù)，要求學(xué)生重點(diǎn)掌握T-SQL語(yǔ)言的數(shù)據(jù)定義和數(shù)據(jù)操縱功能，掌握視圖的作用和用法，了解函數(shù)的使用方法。7.1SQLServer的數(shù)據(jù)庫(kù)7.1.1數(shù)據(jù)庫(kù)及其組成1．?dāng)?shù)據(jù)庫(kù)與事務(wù)日志

數(shù)據(jù)庫(kù)是SQLServer存放表和索引等數(shù)據(jù)庫(kù)對(duì)象的邏輯實(shí)體，一個(gè)數(shù)據(jù)庫(kù)可以存放在一個(gè)或多個(gè)文件中。

每個(gè)數(shù)據(jù)庫(kù)都有一個(gè)相關(guān)的事務(wù)日志，事務(wù)日志記錄了SQLServer所有的事務(wù)和由這些事務(wù)引起的數(shù)據(jù)庫(kù)的變化。

2．?dāng)?shù)據(jù)庫(kù)文件（1）主數(shù)據(jù)文件Primary：存放數(shù)據(jù)，每個(gè)數(shù)據(jù)庫(kù)都必須有一個(gè)主數(shù)據(jù)文件。（2）其他數(shù)據(jù)文件（Secondary）：也用來(lái)存放數(shù)據(jù)，一個(gè)數(shù)據(jù)庫(kù)可以沒(méi)有也可以有多個(gè)Secondary文件。（3）事務(wù)日志文件（TransactionLog）：存放事務(wù)日志，每個(gè)數(shù)據(jù)庫(kù)必須有一個(gè)或多個(gè)日志文件。

數(shù)據(jù)文件名為“數(shù)據(jù)庫(kù)名_Data.MDF”

日志文件名為“數(shù)據(jù)庫(kù)名_Log.LDF”

3．?dāng)?shù)據(jù)庫(kù)對(duì)象當(dāng)一個(gè)用戶連接到數(shù)據(jù)庫(kù)后，他所看到的是這些邏輯對(duì)象，而不是物理的數(shù)據(jù)庫(kù)文件。

SQLServer2008中有以下數(shù)據(jù)庫(kù)對(duì)象:表（Table）、視圖（View）、存儲(chǔ)過(guò)程（Storedprocedures）、觸發(fā)器(Triggers)、用戶自定義數(shù)據(jù)類(lèi)型(User-defineddata

types)、用戶自定義函數(shù)(User-defined

functions)、索引(Indexes)、規(guī)則(Constraints)、默認(rèn)值(Defaults)等。4．?dāng)?shù)據(jù)庫(kù)組成（1）系統(tǒng)數(shù)據(jù)庫(kù)

1）Master數(shù)據(jù)庫(kù)：它記錄了SQLServer系統(tǒng)級(jí)的信息，用來(lái)管理和控制整個(gè)DBMS的運(yùn)行。

2）Model數(shù)據(jù)庫(kù)：model數(shù)據(jù)庫(kù)是系統(tǒng)所有數(shù)據(jù)庫(kù)的模板，所有在系統(tǒng)中創(chuàng)建的新數(shù)據(jù)庫(kù)的內(nèi)容，在剛創(chuàng)建時(shí)都和model數(shù)據(jù)庫(kù)完全一樣。

3）Tempdb數(shù)據(jù)庫(kù)：用于存放所有連接到系統(tǒng)的用戶的臨時(shí)表和臨時(shí)存儲(chǔ)過(guò)程，以及SQLServer產(chǎn)生的其他臨時(shí)性的對(duì)象。

4）Msdb數(shù)據(jù)庫(kù)：它主要被SQLServerAgent用來(lái)進(jìn)行復(fù)制、作業(yè)調(diào)度以及管理報(bào)警等活動(dòng)，也可通過(guò)調(diào)度任務(wù)來(lái)排除故障。（2）用戶數(shù)據(jù)庫(kù)用戶數(shù)據(jù)庫(kù)可以利用CREATEDATABASE語(yǔ)句來(lái)建立；也可以在進(jìn)入SQLServerManagementStudio后，通過(guò)右擊對(duì)象資源管理器中的“數(shù)據(jù)庫(kù)”，再選“新建數(shù)據(jù)庫(kù)”來(lái)建立。

SQLServer2008在安裝時(shí)還自動(dòng)創(chuàng)建了另外兩個(gè)數(shù)據(jù)庫(kù)：AdventureWorks和AdventureWorksDW，它們是SQLServer的樣本數(shù)據(jù)庫(kù)。

7.1.2創(chuàng)建用戶數(shù)據(jù)庫(kù)1、在查詢(xún)分析器中創(chuàng)建數(shù)據(jù)庫(kù)CreatedatabasN(NAMEFILENAME='d:\ProgramFiles\SQLServerDBmdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAMElog,FILENAME='d:\ProgramFiles\SQLServerDBlog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)GO注：存放數(shù)據(jù)庫(kù)文件的目錄要事先已存在！

2、在對(duì)象資源管理器中創(chuàng)建數(shù)據(jù)庫(kù)

在進(jìn)入SQLServerManagementStudio后，通過(guò)右擊對(duì)象資源管理器中的“數(shù)據(jù)庫(kù)”，再選“新建數(shù)據(jù)庫(kù)”就會(huì)出現(xiàn)如下對(duì)話框，再輸入數(shù)據(jù)庫(kù)名稱(chēng)（例如學(xué)號(hào)），根據(jù)需要調(diào)整數(shù)據(jù)文件和日志文件的初始大小、自動(dòng)增長(zhǎng)和路徑，最后按確定鍵來(lái)建立一個(gè)用戶數(shù)據(jù)庫(kù)。7.1.3管理用戶數(shù)據(jù)庫(kù)1、在查詢(xún)分析器中管理數(shù)據(jù)庫(kù)（1）查看數(shù)據(jù)庫(kù)sp_helpd/p>

或者sp_helpdb

（2）修改數(shù)據(jù)庫(kù)

AlterdatabasDDFILE(NAME2,

FILENAME='d:\ProgramFiles\SQLServerDB2.mdf',

SIZE=5MB,

MAXSIZE=50MB,

FILEGROWTH=1MB)

其中ADDFILE是指增加一個(gè)數(shù)據(jù)文件，還可以是ADDLOGFILE、REMOVEFILE、MODIFYFILE等，分別代表增加日志文件、刪除數(shù)據(jù)庫(kù)文件和修改文件信息。比如，以下的語(yǔ)句據(jù)庫(kù)的第二個(gè)數(shù)據(jù)文2.mdf的初始大小修改為20MB，具體代碼如下：AlterdatabasODIFYFILE(NAME2,

SIZE=20MB)（3）刪除數(shù)據(jù)庫(kù)

使用T-SQL語(yǔ)句刪除數(shù)據(jù)庫(kù)需要使用Dropdatabase語(yǔ)句。使用該語(yǔ)句可以一次刪除多個(gè)數(shù)據(jù)庫(kù)。例如，若已創(chuàng)test二個(gè)數(shù)據(jù)庫(kù)，則刪除這二個(gè)數(shù)據(jù)庫(kù)的代碼如下：Dropdatabastest

必須注意：如果用戶沒(méi)有備份某個(gè)數(shù)據(jù)庫(kù)的話，則刪除這個(gè)數(shù)據(jù)庫(kù)后是不能恢復(fù)的，這一點(diǎn)要格外小心。2、在對(duì)象資源管理器中管理數(shù)據(jù)庫(kù)

在進(jìn)入SQLServerManagementStudio后，通過(guò)點(diǎn)擊對(duì)象資源管理器中“數(shù)據(jù)庫(kù)”左邊的+符號(hào)，展開(kāi)“數(shù)據(jù)庫(kù)”，選中要管理的用戶數(shù)據(jù)庫(kù)并右擊此數(shù)據(jù)庫(kù)，在彈出的快捷菜單中根據(jù)需要選擇要管理的項(xiàng)目即可。7.2SQLServer的數(shù)據(jù)表7.2.1數(shù)據(jù)表結(jié)構(gòu)和內(nèi)容1．?dāng)?shù)據(jù)表的結(jié)構(gòu)

在進(jìn)入SQLServerManagementStudio后，通過(guò)點(diǎn)擊對(duì)象資源管理器中“數(shù)據(jù)庫(kù)”左邊的“+”號(hào)來(lái)展開(kāi)“數(shù)據(jù)庫(kù)”，接著展開(kāi)teaching數(shù)據(jù)庫(kù)、表和dbo.student，鼠標(biāo)右擊dbo.student并在彈出的菜單中選擇“設(shè)計(jì)”將會(huì)出現(xiàn)如下圖7.3的數(shù)據(jù)表結(jié)構(gòu)的對(duì)話框，圖中student表的列名處有5個(gè)屬性，每個(gè)屬性有一個(gè)數(shù)據(jù)類(lèi)型和是否允許為空的選項(xiàng)。2．SQLServer數(shù)據(jù)類(lèi)型（1）常用系統(tǒng)數(shù)據(jù)類(lèi)型字符數(shù)據(jù)：char(n),varchar(n),text二進(jìn)制數(shù)據(jù)：binary(n),varbinary(n),image整數(shù)數(shù)據(jù)：bigint，int,smallint,tinyint,bit

8字節(jié)4字節(jié)2字節(jié)1字節(jié)1位浮點(diǎn)數(shù)據(jù)：float,real8字節(jié)4字節(jié)貨幣數(shù)據(jù)：money,smallmoney8字節(jié)4字節(jié)日期時(shí)間數(shù)據(jù)：datetime,smalldatetime8字節(jié)4字節(jié)（2）用戶定義數(shù)據(jù)類(lèi)型格式：sp_addtype<類(lèi)型名>，<系統(tǒng)類(lèi)型>[,<null說(shuō)明>]例如：usepubsexecsp_addtypephone_type,’varchar(24)’,

’notnull’execsp_addtypefax_type,’varchar(24)’,’null’

使用sp_droptype<類(lèi)型名>可刪除用戶自定義數(shù)據(jù)類(lèi)型。3．?dāng)?shù)據(jù)表的內(nèi)容7.2.2創(chuàng)建用戶數(shù)據(jù)表1、在查詢(xún)分析器中創(chuàng)建數(shù)據(jù)表格式：CREATETABLE[[<數(shù)據(jù)庫(kù)名>.]<主人名>.]<表名>(<列名1><類(lèi)型1>{NOTNULL∣NULL}[,<列名2><類(lèi)型2>{NOTNULL∣NULL}……])功能：建立一個(gè)新的基本表，指明基本表的表名與結(jié)構(gòu)，包括組成該表的每一個(gè)字段名，數(shù)據(jù)類(lèi)型等，任選項(xiàng)“NOTNULL”表明該列的值不能為空，通常是關(guān)鍵字屬性不能為空。例1選擇數(shù)據(jù)當(dāng)前數(shù)據(jù)庫(kù)，使用CREATETABLE語(yǔ)句來(lái)創(chuàng)據(jù)庫(kù)的三個(gè)基本表：student、course和student_course。

1）創(chuàng)建student表CREATETABLEstudent( snochar(11)NOTNULLPRIMARYKEY, snamevarchar(20)NOTNULL, deptvarchar(20)NOTNULL, birthdaydatetimeNULL, emailvarchar(20)NULLUNIQUE)2）創(chuàng)建course表CREATETABLEcourse(cnochar(5)NOTNULLPRIMARYKEY, cnamevarchar(20)NOTNULL,chourtinyintNULL)

3)創(chuàng)建student_course表CREATETABLEstudent_course(idintIDENTITY(1,1)NOTNULLPRIMARYKEY,Snochar(11)NOTNULLFOREIGNKEY(sno)

REFERENCESstudent(sno)ONDELETECASCADE,Cnochar(5)NOTNULLFOREIGNKEY(cno)

REFERENCEScourse(cno)ONDELETENOACTION,Gradedecimal(5,0)NULLCHECK(grade>=0and

grade<=100))或者：CREATETABLEstudent_course(

snochar(7)not

null

foreign

key(sno)references

student(sno)

on

delete

cascade,

cnochar(5)not

null

foreign

key(cno)references

course(cno)

on

delete

noaction,

gradedecimal(5,0)null

check(grade>=0and

grade<=100))goaltertablestudent_courseaddconstraint

pk_student_courseprimarykey(sno,cno)go

在student_course表，(sno,cno)非空且設(shè)置為主鍵；

sno字段不能為空，設(shè)置成相對(duì)于student表來(lái)說(shuō)為外鍵，并且當(dāng)在student表中刪除某個(gè)學(xué)生記錄時(shí)，會(huì)根據(jù)學(xué)號(hào)自動(dòng)在student_course表中刪除此學(xué)生的選課記錄；

cno字段不能為空，設(shè)置成相對(duì)于course表來(lái)說(shuō)為外鍵，只有當(dāng)在student_course表中無(wú)某門(mén)課的選課記錄時(shí)才能在course表中刪除這門(mén)課程的記錄；

grade字段可以為空，但取值必須在0到100之間。2、在對(duì)象資源管理器中創(chuàng)建數(shù)據(jù)表

7.2.3管理用戶數(shù)據(jù)表1、在查詢(xún)分析器中管理數(shù)據(jù)表（1）添加和刪除表中的列（2）修改表中的列（3）修改表中的約束（4）刪除基本表

例如，選擇數(shù)據(jù)當(dāng)前數(shù)據(jù)庫(kù)，若存在要?jiǎng)h除的表table1，則可使用DROP

TABLE語(yǔ)句，其代碼如下：DROPTABLEtable1注意：DROPTABLE語(yǔ)句不能用來(lái)刪除系統(tǒng)表，否則系統(tǒng)將會(huì)不正常，這要引起高度的重視。（5）表中索引的建立和刪除簇集索引根據(jù)鍵的值對(duì)行進(jìn)行排序，而非簇集索引不根據(jù)鍵值排序。1)CREATE[UNIQUE]{CLUSTERED|NONCLUSTERED}INDEX<索引名>ON<表名>(<列名1>[,<列名2>···])2)DROPINDEX<表名>.<索引名1>

[,<表名>.<索引名2>···]例如在課程表上建立和刪除索引，可用命令：Createuniqueclusteredindexkh_indon

course(cno);Dropindexcourse.kh_ind;2、在對(duì)象資源管理器中管理數(shù)據(jù)表

進(jìn)入SQLServerManagementStudio后，在對(duì)象資源管理器中展開(kāi)“數(shù)據(jù)庫(kù)”，選中要管理的用戶數(shù)據(jù)庫(kù)（，展開(kāi)此數(shù)據(jù)庫(kù)及下屬的表，選擇dbo.student，右擊此表并在彈出的菜單中選擇“設(shè)計(jì)”，就可修改此表的結(jié)構(gòu)。

類(lèi)似地，在對(duì)象資源管理器中展開(kāi)“數(shù)據(jù)庫(kù)”，選中要管理的用戶數(shù)據(jù)庫(kù)，展開(kāi)此數(shù)據(jù)庫(kù)及下屬的表，選擇dbo.student，右擊此表并在彈出的菜單中選擇“編輯前200行”，就可修改此表的內(nèi)容。

7.3SQLServer的數(shù)據(jù)更新7.3.1數(shù)據(jù)插入1、插入語(yǔ)句格式一：INSERT[INTO]<表名>[(<列名1>，…，<列名n>)]VALUES(<常量1>，…，<常量n>)

此語(yǔ)句的功能是將VALUES后面的數(shù)據(jù)插入到指定表中。2、插入語(yǔ)句格式二：INSERTINTO<表名>[(<列名1>，…，

<列名n>)]子查詢(xún)

此語(yǔ)句的功能是利用一個(gè)子查詢(xún)將從一個(gè)或多個(gè)表或視圖中選擇來(lái)的數(shù)據(jù)添加到表中去。例2選擇數(shù)據(jù)當(dāng)前數(shù)據(jù)庫(kù)，在student_course表中插入一條記錄。Insertintostudent_course(sno,cno,grade)values(,’10108’,90)例3選擇數(shù)據(jù)當(dāng)前數(shù)據(jù)庫(kù)，在student_course表中插入teaching數(shù)據(jù)庫(kù)中相同結(jié)構(gòu)數(shù)據(jù)表中的記錄。insertintostudent_courseselect*fromstudent_course1

7.3.2數(shù)據(jù)修改UPDATE語(yǔ)句格式為：

UPDATE<表名>SET<列名1>=<表達(dá)式l>[,<列名2>=<表達(dá)式2>，…][FROM<表名1>[,<表名2>,…]][WHERE<條件>]

此語(yǔ)句是對(duì)由表名指定的表進(jìn)行修改。修改時(shí)，對(duì)表中滿足條件的行，將表達(dá)式的值替換相應(yīng)列的值。例4單表更新updatestudentsetsno='1548'+ltrim(rtrim(sno))updatestudentsetemail=’WHEREsno=例5非單表更新UPDATEstudent_courseSETgrade=0FROMstudentWHEREdept=’計(jì)算機(jī)系’andsno=student_course.sno

7.3.3數(shù)據(jù)刪除

DELETEFROM<表名>[FROM<表名1>[,<表名2>,…]][WHERE條件]

從由表名指定的表中刪除滿足條件的行，其中第2個(gè)FROM子句選項(xiàng)表示依據(jù)另一個(gè)表中的數(shù)據(jù)約束條件來(lái)刪除一些行。當(dāng)不選擇WHERE子句時(shí)，表示刪除表中全部數(shù)據(jù)。例6刪除student_course表中沒(méi)有成績(jī)的選課記錄。

DELETEFROMstudent_course

WHEREgradeisnull提醒：誤刪將會(huì)丟失數(shù)據(jù)。例7選擇數(shù)據(jù)當(dāng)前數(shù)據(jù)庫(kù)，刪除選修’計(jì)算機(jī)專(zhuān)業(yè)英語(yǔ)’課程的所有選課記錄。

DELETEFROMstudent_courseFROMstudent_coursesc,coursecWHEREo=oand

ame=’計(jì)算機(jī)專(zhuān)業(yè)英語(yǔ)’

這里出現(xiàn)的sc是student_course表的別名,c是course表的別名。7.4SQLServer的數(shù)據(jù)查詢(xún)

最常用的SELECT語(yǔ)句的語(yǔ)法：

SELECT<投影的字段列表>FROM<參與查詢(xún)的表列表>[WHERE<查詢(xún)選擇的條件>][GROUPBY<分組表達(dá)式>][HAVING<分組查詢(xún)條件>][ORDERBY<排序表達(dá)式>[ASC∣DESC]]

必須區(qū)分WHERE子句中查詢(xún)選擇的條件與HAVING子句中分組查詢(xún)條件的不同。7.4.1數(shù)據(jù)基本查詢(xún)例8

請(qǐng)分析下列語(yǔ)句的含義。SELECTDISTINCTsnameas姓名,deptas系名,year(getdate())-year(birthday)as年齡FROMstudent

例9請(qǐng)分析下列語(yǔ)句的含義。SELECT*FROMstudentWherebirthdayBetween'1996-01-01'

and'1996-12-31'anddeptIN('計(jì)算機(jī)系','電子系')andemaillike'%@'

其中SQLServer的通配符有以下幾個(gè)：%代表任意多個(gè)字符。（下劃線）代表單個(gè)字符。[]代表指定范圍內(nèi)的若個(gè)字符，[]中可以有單個(gè)字符，也可以是字符范圍。[^]代表不在指定范圍內(nèi)的若個(gè)字符，[^]中可以有單個(gè)字符，也可以是字符范圍。例10

在student_course表中查詢(xún)成績(jī)非空的選課記錄，結(jié)果集要求按成績(jī)的升序排列。

SELECT*FROMstudent_courseWHEREgradeISNOTNULLORDERBYgradeASC

7.4.2數(shù)據(jù)分組查詢(xún)SQLServer2008提供以下5個(gè)常用的集合函數(shù)：1、MIN(<表達(dá)式>)

求(字符、日期、數(shù)值)列的最小值；2、MAX(<表達(dá)式>)

求(字符、日期、數(shù)值)列的最大值；3、COUNT(*)

計(jì)算選中結(jié)果的行數(shù)；

COUNT([ALL∣DISTINCT]<表達(dá)式>)

計(jì)算

所有/不同值的個(gè)數(shù)；4、SUM([ALL∣DISTINCT]<表達(dá)式>)

計(jì)算所有

/不同列值的總和；5、AVG([ALL∣DISTINCT]<表達(dá)式>)

計(jì)算所有

/不同]列值的平均值。例11在student和student_course表中，按學(xué)號(hào)分組查詢(xún)每個(gè)學(xué)生選課成績(jī)的總分和平均分，并按每個(gè)學(xué)生選課成績(jī)的平均分的降序排列。SELECTs.snoas學(xué)號(hào),s.snameas姓名,sum(sc.grade)as總分,avg(sc.grade)as平均分FROMstudents,student_coursescWHEREs.sno=sc.snoGROUPBYs.sno,s.snameORDERBY

平均分

DESC

7.4.3多表連接查詢(xún)1、內(nèi)連接（Innerjoin）內(nèi)連接也叫自然連接，它將兩個(gè)表中的列進(jìn)行比較，將兩個(gè)表中滿足連接條件的行組合起來(lái)作為結(jié)果。自然連接有以下兩種形式的語(yǔ)法：（1）SELECT<投影的字段列表>FROM<表1>,<表2>WHERE<表1.列1>=<表2.列2>（2）SELECT<投影的字段列表>FROM<表1>[INNER]JOIN<表2>ON<表1.列1>=<表2.列2>例12在student、student_course和course表中，查詢(xún)選修“數(shù)據(jù)庫(kù)技術(shù)與設(shè)計(jì)”課程的所有學(xué)生情況。SELECTs.*FROMstudents,student_coursesc,coursecWHEREs.sno=sc.sno

ando=oandame='數(shù)據(jù)庫(kù)技術(shù)與設(shè)計(jì)'

2、外連接（Outerjoin）在自然連接中，只有在兩個(gè)表中匹配的行才能在結(jié)果集中出現(xiàn)。而在外連接中可以只限制一個(gè)表，而對(duì)另外一個(gè)表不加限制（即所有的行都出現(xiàn)在結(jié)果集中）。

左外連接是對(duì)連接條件中左邊的表不加限制，在右邊增加萬(wàn)能行（全由空值組成）；

右外連接是對(duì)連接條件中右邊的表不加限制，在左邊增加萬(wàn)能行（全由空值組成）；

全外連接是對(duì)連接條件中的兩個(gè)表都不加限制，在兩邊增加萬(wàn)能行（全由空值組成），所有兩個(gè)表中的行都會(huì)包括在結(jié)果集中。

左外連接的語(yǔ)法為：

SELECT<投影的字段列表>FROM<表1>LEFTJOIN<表2>ON<表1.列1>=<表2.列2>右外連接的語(yǔ)法為：

SELECT<投影的字段列表>FROM<表1>RIGHTJOIN<表2>ON<表1.列1>=<表2.列2>全外連接的語(yǔ)法為：

SELECT<投影的字段列表>FROM<表1>FULLJOIN<表2>ON<表1.列1>=<表2.列2>

例13在student_course和course表中進(jìn)行右外連接，并說(shuō)明以下代碼的含義。SELECTsno,student_o,cname,gradeFROMstudent_courseRIGHTJOINcourseONstudent_o=o

右外連接就是在左邊的表中增加一個(gè)“萬(wàn)能”的行（此行全部由空值組成），它可以與右邊的表中所有不滿足連接條件的元組進(jìn)行連接。關(guān)于左外連接和全外連接的操作也類(lèi)似，讀者可自己進(jìn)行練習(xí)。

3、自連接（Selfjoin）連接操作不僅可以在不同的表上進(jìn)行，而且在同一張表內(nèi)也可以進(jìn)行自身連接，即將同一個(gè)表中的不同行連接起來(lái)。自連接可以看作一張表的兩個(gè)副本之間進(jìn)行的連接。在自連接中，必須為兩個(gè)表分別指定別名，使它們?cè)谶壿嬌铣蔀閮蓮埍?。?4在student表中，查詢(xún)同名同姓的學(xué)生。SELECTs1.sno,s1.sname,s2.sno,s2.snameFROMstudents1,students2

WHEREs1.sname=s2.snameands1.sno<s2.sno4、交叉連接（Crossjoin）交叉連接也叫非限制連接，它將兩個(gè)表不加任何約束地組合起來(lái)，在數(shù)學(xué)上就是兩個(gè)表的笛卡兒積。交叉連接的語(yǔ)法如下：SELECT<投影的字段列表>FROM<表1>,<表2>或者SELECT<投影的字段列表>FROM<表1>CROSSJOIN<表2>

在實(shí)際應(yīng)用中，使用交叉連接產(chǎn)生的結(jié)果集沒(méi)什么意義，但在理論研究上有重要的作用。

7.4.4數(shù)據(jù)的子查詢(xún)

子查詢(xún)是指一條SELECT語(yǔ)句作為另一條SELECT語(yǔ)句的一部分，外層的SELECT語(yǔ)句被稱(chēng)為外部查詢(xún)，內(nèi)層的SELECT語(yǔ)句被稱(chēng)為內(nèi)部查詢(xún)（或子查詢(xún)）。子查詢(xún)分為以下兩種：1、嵌套子查詢(xún)

首先執(zhí)行子查詢(xún)，子查詢(xún)得到的結(jié)果集不被顯示出來(lái)，而是傳遞給外部查詢(xún)并作為外部查詢(xún)的條件來(lái)使用，然后執(zhí)行外部查詢(xún)并顯示查詢(xún)結(jié)果集。嵌套子查詢(xún)一般可分為返回單個(gè)值和返回一個(gè)值列表兩種。

例15返回單個(gè)值SELECTs1.*FROMstudents1,student_coursescWHEREs1.sno=sc.snoandsc.grade>(SELECTaveragegrade=AVG(grade)

FROMstudent_course)例16返回一個(gè)值列表SELECTstudent.*FROMstudentWHEREbirthday>ALL(SELECTbirthdayFROMstudentWHEREdept='計(jì)算機(jī)系')

2、相關(guān)子查詢(xún)?cè)谙嚓P(guān)子查詢(xún)中，子查詢(xún)的執(zhí)行依賴(lài)于外部查詢(xún)且需要重復(fù)地執(zhí)行，多數(shù)情況下是在子查詢(xún)的WHERE子句中引用了外部查詢(xún)的表；而在嵌套子查詢(xún)中，子查詢(xún)的執(zhí)行不依賴(lài)于外部查詢(xún)且只需要執(zhí)行一次。下面介紹相關(guān)子查詢(xún)的執(zhí)行過(guò)程：（1）子查詢(xún)?yōu)橥獠坎樵?xún)的每一行執(zhí)行一次，外部查詢(xún)將子查詢(xún)引用的列的值傳給子查詢(xún)。（2）如果子查詢(xún)的任何行與其匹配，則外部查詢(xún)就返回結(jié)果行。（3）再回到第一步，直到處理完外部表的每一行。

例17在student和student_course表中，查詢(xún)所有選修了'10101'號(hào)課程的學(xué)生姓名。SELECTDISTINCTsnameFROMstudentWHEREEXISTS(SELECT*FROMstudent_courseWHEREstudent.sno=snoandcno='10101')

使用存在量詞EXISTS或NOTEXISTS后，若內(nèi)層查詢(xún)結(jié)果非空或空，則外層的WHERE子句返回真值，否則返回假值。

7.4.5附加子句1、合并結(jié)果集使用UNION語(yǔ)句把兩個(gè)或兩個(gè)以上的查詢(xún)結(jié)果集合并為一個(gè)結(jié)果集，其語(yǔ)法如下：

SELECT語(yǔ)句1UNION[ALL]SELECT語(yǔ)句2

合并結(jié)果集有以下四點(diǎn)限制：（1）UNION中的每一個(gè)查詢(xún)所涉及到的列必須在列數(shù)、順序和類(lèi)型上保持一致；（2）最后結(jié)果集中的列名來(lái)自第一個(gè)查詢(xún)語(yǔ)句；（3）若UNION中包含ORDERBY子句，則將對(duì)最后的結(jié)果集排序；（4）默認(rèn)將在結(jié)果集中刪除重復(fù)的行，除非使用ALL關(guān)鍵字。

2、查詢(xún)創(chuàng)建新表

SELECT<投影的字段列表>

INTO<新表>FROM<參與查詢(xún)的表列表>[WHERE<查詢(xún)選擇的條件>][GROUPBY<分組表達(dá)式>]

[HAVING<分組查詢(xún)條件>][ORDERBY<排序表達(dá)式>[ASC∣DESC]]

由于新表的結(jié)構(gòu)由<投影的字段列表>定義，所以<投影的字段列表>中的每一列必須有名稱(chēng)，如果是一個(gè)表達(dá)式，則應(yīng)該為其指定別名。

例18在student和student_course表中，查詢(xún)沒(méi)有選修10101號(hào)課程的所有學(xué)生姓名。SELECTDISTINCTsnameINTOnew1FROMstudentWHERENOTEXISTS(SELECT*FROMstudent_courseWHEREstudent.sno=snoandcno='10101')

如果在<新表>前加一個(gè)#或加二個(gè)#，將自動(dòng)產(chǎn)生局部臨時(shí)表和全局臨時(shí)表。如在上例中，將INTOnew1換成INTO#new1或INTO##new1，將會(huì)產(chǎn)生局部臨時(shí)表#new1或全局臨時(shí)表##new1。

7.5SQLServer的視圖7.5.1視圖的建立格式：CREATEVIEW<視圖名>[(<視圖列名表>)][WITHENCRYPTION]AS<SELECT語(yǔ)句>[WITHCHECKOPTION]

視圖列名表是個(gè)可選項(xiàng)，如果不選該項(xiàng)時(shí)，新生成視圖的列名與SELECT命令所選擇數(shù)據(jù)列的名稱(chēng)相同；如果選擇該項(xiàng)時(shí)，則給SELECT命令所選擇的數(shù)據(jù)重新起個(gè)名字作為視圖的列名，它們的對(duì)應(yīng)關(guān)系是按順序?qū)?yīng)。

其中三個(gè)選項(xiàng)的含義如下：

(1)WITHENCRYPTION:如果使用此選項(xiàng)，則syscomments系統(tǒng)表中的視圖定義被加密。

(2)As<SELECT語(yǔ)句>:用來(lái)定義視圖的內(nèi)容,但SELECT語(yǔ)句中不能含有ORDERBY子句及COMPUTE子句（若要進(jìn)行排序，則必須在selelct語(yǔ)句后加Top(n)選項(xiàng)）；不能含有DISTINCT及INTO關(guān)鍵字；不準(zhǔn)引用臨時(shí)表。

(3)WITHCHECKOPTION:強(qiáng)制所有通過(guò)視圖修改的數(shù)據(jù)滿足SELECT語(yǔ)句中指定的條件。

注意：視圖只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建；用戶定義的視圖名稱(chēng)必須唯一且不能與某個(gè)表名相同；不能將規(guī)則、默認(rèn)值定義綁定在視圖上；不能將觸發(fā)器與視圖相關(guān)聯(lián)。

例19先定義一個(gè)視圖avg_grade，然后在此視圖基礎(chǔ)上再建立一個(gè)視圖computer_avg_grade。CREATEVIEWavg_grade(sno,avg_grade)ASSELECTsno,avg(grade)

FROMstudent_course

GROUPBYsnoGOCREATEVIEWcomputer_avg_grade(sno,sname,

avg_grade,email)ASSELECTs1.sno,sname,avg_grade,emailFROMstudents1,avg_grades2WHEREs1.sno=s2.snoanddept='計(jì)算機(jī)系'GO

請(qǐng)自己理解這二個(gè)視圖的含義。7.5.2視圖的查詢(xún)和刪除1、視圖信息的獲取（1）利用系統(tǒng)存儲(chǔ)過(guò)程sp_help<視圖名>來(lái)獲得視圖的主人、類(lèi)型、創(chuàng)建日期、列名和數(shù)據(jù)類(lèi)型等信息。（2）利用系統(tǒng)過(guò)程sp_helptext<視圖名>可查看用于定義該視圖的CREATEVIEW語(yǔ)句。問(wèn)題:1、視圖是否都可以更新？請(qǐng)舉例說(shuō)明。2、什么樣的視圖可以更新？3、對(duì)視圖的查詢(xún)和更新如何轉(zhuǎn)換成對(duì)基本表的更新？關(guān)于視圖更新，一般只有行列子集視圖才能進(jìn)行更新。2、視圖的查詢(xún)

視圖創(chuàng)建后可以和基本表一樣進(jìn)行查詢(xún)；例20查詢(xún)?cè)谟?jì)算機(jī)系就讀且平均成績(jī)大于85分的所有學(xué)生的學(xué)號(hào)和姓名。selectsno,snamefromcomputer_avg_gradewhereavg_grade>853、視圖的刪除

語(yǔ)句格式：DROPVIEW〈視圖名表〉

將視圖的定義從數(shù)據(jù)字典中刪除，由此視圖導(dǎo)出的其他視圖也將自動(dòng)刪除；若導(dǎo)出此視圖的基本表刪除了，則此視圖也將自動(dòng)刪除。例21DROPVIEWcomputer_avg_grade

7.6SQLServer的函數(shù)7.6.1內(nèi)置函數(shù)的分類(lèi)

內(nèi)置函數(shù)共分14類(lèi)，常用的包括聚合函數(shù)、日期和時(shí)間函數(shù)、數(shù)學(xué)函數(shù)和字?jǐn)?shù)串函數(shù)等。系統(tǒng)內(nèi)置函數(shù)可通過(guò)查看幫助系統(tǒng)得到使用方法，也可在“對(duì)象資源管理器”中根據(jù)需要查看相應(yīng)的函數(shù)，如下圖7.6所示。7.6.2用戶定義函數(shù)1．函數(shù)名的命名規(guī)則

用戶定義的函數(shù)名必須唯一且符合如下命名規(guī)則：(1)有效字符：SQL函數(shù)名一般應(yīng)以字母開(kāi)頭，后跟字母、數(shù)字等。(2)有效長(zhǎng)度：函數(shù)名的有效長(zhǎng)度為1~128個(gè)字符。(3)SQLServer的保留關(guān)鍵字不能用做函數(shù)名。(4)嵌入的空格或其他特殊字符不能在函數(shù)名中使用。2.創(chuàng)建用戶定義函數(shù)(1)使用CREATEFUNCTION語(yǔ)句創(chuàng)建用戶定義函數(shù)。具體語(yǔ)法參見(jiàn)SQLServer2008在線手冊(cè)。(2)使用SQLServerManagementStudio創(chuàng)建用戶定義函數(shù)的操作如下：

在“對(duì)象資源管理器”窗格中展開(kāi)“數(shù)據(jù)庫(kù)”結(jié)點(diǎn)，接下來(lái)展開(kāi)“可編程性”結(jié)點(diǎn)，右擊“函數(shù)”結(jié)點(diǎn)，在彈出的快捷菜單中選擇“新建”命令，在打開(kāi)的級(jí)聯(lián)菜單中選擇需要?jiǎng)?chuàng)建的函數(shù)類(lèi)型后，再添加相應(yīng)代碼即可。3．執(zhí)行用戶定義函數(shù)

可以在查詢(xún)或其他語(yǔ)句及表達(dá)式中調(diào)用用戶定義函數(shù)，也可用EXECUTE語(yǔ)句執(zhí)行標(biāo)量值函數(shù)。(1)在查詢(xún)中調(diào)用用戶定義函數(shù)：可以在SELECT語(yǔ)句的列表中使用，也可以在WHERE或HAVING子句中使用。(2)賦值運(yùn)算符可調(diào)用用戶定義函數(shù)：在指定為右操作數(shù)的表達(dá)式中返回標(biāo)量值。

關(guān)于用戶定義函數(shù)的相關(guān)內(nèi)容，讀者可以在SQLServer2008在線手冊(cè)中找到，在此不再贅述。

7.7典型案例分析典型案例1---SQLServer2008基本表查詢(xún)的應(yīng)用1、案例描述

假設(shè)有三個(gè)數(shù)據(jù)表：

S(SNO,SNAME,AGE,SEX)，SC(SNO,CNO,GRADE)，

C(CNO,CNAME,TEACHER)，要求用T-SQL語(yǔ)言完成下列數(shù)據(jù)查詢(xún)。2、案例分析3、案例實(shí)現(xiàn)1)檢索LIU老師所授課程的課程號(hào)、課程名。SelectCNO,CNAMEFromCWhereTEACHERlike

'LIU%'；2)檢索年齡大于23歲的男學(xué)生的學(xué)號(hào)與姓名。SelectSNO,SNAMEFromSWhereAGE>23and

SEX='男'；3)檢索學(xué)號(hào)為'S1'學(xué)生所學(xué)課程的課程名與任課教師名。SelectCNAME,TEACHERFromSC,CWhereSC.CNO=C.CNOand

SNO='S1';4)檢索至少選修LIU老師所授課程中一門(mén)課的女學(xué)生姓名。

SelectSNAMEFromS,SC,CWhereS.SNO=SC.SNOandSC.CNO=C.CNOandTEACHERlike'LIU%'andSEX='女';5)檢索WANG同學(xué)不學(xué)的課程的課程號(hào)。SelectCNOfromCwhereCNOnotin(selectCNOFromS,SCWhereS.SNO=SC.SNOandSNAMElike'WANG%');6)檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)。SelectSNOFromSCGroupbySNOhavingcount(*)>=2;7)檢索全部學(xué)生都選修的課程的課程號(hào)與課程名。[解1]selectSC.CNO,CNAMEFromS,SC,C

WhereS.SNO=SC.SNOandSC.CNO=C.CNOGroupbySC.CNO,CNAMEhavingcount(*)=(selectcount(*)fromS);[解2]若用p表示謂詞”學(xué)生y選修了課程x”，則有

意即：沒(méi)有一個(gè)學(xué)生y是不選修課程x。SelectCNO,CNAMEFromCWherenot