第4章T-SQL與可編程對(duì)象

第4章T-SQL與可編程對(duì)象SQL語言是結(jié)構(gòu)化查詢語言（StructureQueryLanguage）的簡(jiǎn)稱，它是一個(gè)通用的、功能強(qiáng)大的關(guān)系數(shù)據(jù)庫操作語言，最初由IBM公司在20世紀(jì)70年代中期開發(fā)成功。并被國(guó)際標(biāo)準(zhǔn)化組織采納為關(guān)系數(shù)據(jù)庫語言的國(guó)際標(biāo)準(zhǔn)。Transact-SQL（

簡(jiǎn)稱T-SQL）是SQLServer中支持的擴(kuò)展SQL語言，它提供了數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)控制等語句，支持對(duì)數(shù)據(jù)庫的操縱和管理，是開發(fā)數(shù)據(jù)庫應(yīng)用程序的基本語言。SQL

Server支持采用T-SQL語言定義服務(wù)器端應(yīng)用程序，這些應(yīng)用程序被統(tǒng)稱為可編程對(duì)象。

4.1.1T-SQL概述1.

T-SQL語言的特點(diǎn)1）非過程化：使用T-SQL時(shí)，不必描述解決問題的全過程，只需提出“做什么”，至于“如何做”的細(xì)節(jié)則由語言系統(tǒng)本身去完成并給出操作的結(jié)果。2）兩種不同的使用方式：兩種方式下，T-SQL語言的語法結(jié)構(gòu)基本相同。

聯(lián)機(jī)交互方式：SQL語言可獨(dú)立使用嵌入程序設(shè)計(jì)語言中：例如嵌入C#、VB.net中。3)高度一體化：集數(shù)據(jù)定義語言（DDL）、數(shù)據(jù)操縱語言（DML）、數(shù)據(jù)控制語言（DCL）和T-SQL增加的語言元素于一體，可獨(dú)立完成數(shù)據(jù)庫生命周期的所有活動(dòng)。

4)語言簡(jiǎn)潔、易學(xué)易用。面向過程的程序設(shè)計(jì)語言，必須一步一步地將解題的全過程描述出來，不僅要告訴計(jì)算機(jī)“做什么”，而且還要告訴計(jì)算機(jī)“怎么做”。2.T-SQL語言的組成元素DDL(DataDefinitionLanguage)數(shù)據(jù)庫定義語言，用于定義或修改數(shù)據(jù)庫。包括Create、Alter、Drop等語句。例如：CreateDATABASEStudentDML(DataManipulationLanguage)數(shù)據(jù)庫操縱語言，實(shí)現(xiàn)對(duì)數(shù)據(jù)庫基本操作，包括Selete、Insert、Update、Delete等語句。DCL(DataControlLanguage)數(shù)據(jù)庫控制語言，實(shí)現(xiàn)對(duì)數(shù)據(jù)對(duì)象的授權(quán)、數(shù)據(jù)完整性規(guī)則描述及控制事務(wù)等，包括Grant、Revoke、Deny等語句。T-SQL增加的語言元素

變量、運(yùn)算符、函數(shù)、流程控制語句和注解等。

3．T-SQL語言書寫規(guī)則①在T-SQL語句中，不區(qū)分字母大小寫，即大小寫字母的意義完全相同。②一條語句可以寫在一行，也可以寫在若干行上。在本講義中：為了閱讀方便，用大寫字母書寫SQL語句的關(guān)鍵詞，用小寫字母書寫標(biāo)識(shí)符、表達(dá)式及各種參數(shù)；為了體現(xiàn)語法結(jié)構(gòu)，一條語句寫在多行上。例如：SELECT*FROM

Student

4．注釋為了增強(qiáng)程序代碼的可讀性，可在適當(dāng)?shù)牡胤郊由献⑨尅-SQL有兩種注釋方法：①單行注釋（--）。語法格式如下：--注釋文本內(nèi)容②多行注釋（/*…*/）。語法格式如下：/*注釋文本內(nèi)容*/

例如：USESchool/*打開School數(shù)據(jù)庫。當(dāng)?shù)谝淮卧L問某數(shù)據(jù)庫時(shí)，需要使用USE語句打開數(shù)據(jù)庫，否則其他訪問該數(shù)據(jù)庫的SQL語句不能執(zhí)行*/SELECT*FROMStudent--查詢顯示學(xué)生表中所有學(xué)生的信息操作對(duì)象操作方式創(chuàng)建刪除修改使用數(shù)據(jù)庫CREATEDATABASEDROPDATABASEALTERDATABASEUSE基本表CREATETABLEDROPTABLEALTERTABLE視圖CREATEVIEWDROPVIEWALTERVIEW索引CREATEINDEXDROPINDEX存儲(chǔ)過程CREATEPROCEDUREDROPPROCEDUREALTERPROCEDURE觸發(fā)器CREATETRIGGERDROPTRIGGERALTERTRIGGER數(shù)據(jù)定義語言主要是定義數(shù)據(jù)庫中的對(duì)象，包括數(shù)據(jù)庫、數(shù)據(jù)表、視圖、索引、存儲(chǔ)過程、觸發(fā)器等的建立或修改。常用語句如下：4.1.2數(shù)據(jù)定義語言DDL

1．DDL概述如果想查看或保存已存在的數(shù)據(jù)庫對(duì)象的DDL語句，可在SQLServerManagementStudio中自動(dòng)生成各種數(shù)據(jù)庫對(duì)象的SQL腳本，也可將其保存到一個(gè)擴(kuò)展名為.sql的文件中，在查詢編輯器中可打開.sql文件，執(zhí)行SQL腳本，重建相應(yīng)的數(shù)據(jù)庫。提示：使用SQL定義腳本重建的數(shù)據(jù)庫不包括表中數(shù)據(jù)。2．自動(dòng)生成數(shù)據(jù)庫定義的SQL腳本前面已經(jīng)學(xué)習(xí)過使用SQLServerManagementStudio創(chuàng)建各種數(shù)據(jù)庫對(duì)象，實(shí)質(zhì)上手工創(chuàng)建的過程就是在構(gòu)建DDL語句。SQL腳本的生成方法如下：①右擊要生成SQL腳本的數(shù)據(jù)庫或數(shù)據(jù)表，選“編寫數(shù)據(jù)庫(或表)腳本為/Create到/新查詢編輯窗口”。②在“查詢編輯”窗口可查看和復(fù)制SQL腳本。選擇“文件/保存”或“文件/另存為”菜單可將這些SQL腳本保存到一個(gè)擴(kuò)展名為“.sql”的文本文件。③使用“文件/打開”菜單可打開腳本文件。執(zhí)行可創(chuàng)建新的表。選數(shù)據(jù)庫，右擊用“任務(wù)/生成腳本”菜單可選擇該庫中的多個(gè)對(duì)象，生成腳本文件。（1）創(chuàng)建數(shù)據(jù)庫【例4.1】創(chuàng)建一個(gè)名為SchoolTest的數(shù)據(jù)庫。3．幾個(gè)常用DDL語句CREATEDATABASE數(shù)據(jù)庫名[ONNAME=邏輯文件名，F(xiàn)ILENAME=物理文件名]答案1

:CREATEDATABASE

SchoolTest

--數(shù)據(jù)庫名提示：[]表示可選項(xiàng)以系統(tǒng)數(shù)據(jù)庫model作為模板在SQLServer安裝文件夾的Data文件夾下（一般為C:\ProgramFile\MicrosoftSQLServer\MSSQL\Data）建立數(shù)據(jù)庫文件SchoolTest.mdf（大小為3MB）和日志文件SchoolTest_log.LDF（大小為1MB）。答案2

:CREATEDATABASE

SchoolTest

--數(shù)據(jù)庫名ON(NAME=‘SchoolTest’,

--主數(shù)據(jù)文件邏輯名FILENAME=‘D:\SchoolTest.MDF’)

--主數(shù)據(jù)文件物理名創(chuàng)建了一個(gè)數(shù)據(jù)庫SchoolTest，其主數(shù)據(jù)文件為大小為3MB的D:\SchoolTest.mdf“；并且自動(dòng)創(chuàng)建一個(gè)大小為1MB的“D:\SchoolTest_log.LDF”作為日志文件。（2）打開和刪除數(shù)據(jù)庫語句打開數(shù)據(jù)庫：USE數(shù)據(jù)庫名--其中數(shù)據(jù)庫名是要打開作為當(dāng)前數(shù)據(jù)庫的數(shù)據(jù)庫例如：USESchool--打開數(shù)據(jù)庫School當(dāng)用戶登錄到SQLServer后，系統(tǒng)指定系統(tǒng)數(shù)據(jù)庫master作為默認(rèn)當(dāng)前數(shù)據(jù)庫。所謂當(dāng)前數(shù)據(jù)庫是指當(dāng)前可以操作的數(shù)據(jù)庫。刪除數(shù)據(jù)庫：DROPDATABASE數(shù)據(jù)庫名例如：DROPDATABASESchoolTest--刪除SchoolTest注意：刪除數(shù)據(jù)庫時(shí)不允許該數(shù)據(jù)庫有對(duì)象處于打開狀態(tài)。（3）建立數(shù)據(jù)表語句CREATETABLE數(shù)據(jù)表名（列名列數(shù)據(jù)類型列約束

……

Constraint約束說明）【例4.2】在數(shù)據(jù)庫“SchoolTest”中建立學(xué)生表StudentTest。CREATETABLEStudentTest(StudentCodeCHAR(8)NOTNULLUNIQUE,/*不允許空，取值唯一*/StudentNameVARCHAR(16)NOTNULL,SexCHAR(2)NOTNULL,LiveInDormBIT

DEFAULT1，/*默認(rèn)值為1*/ConstraintStudentPKPrimaryKey(StudentCode),/*StudentCode主鍵*/ConstraintSexCheckCheck(Sex='男'

orSex='女')/*Sex檢查約束*/)【例4.3】刪除StudentTest表。（4）刪除表語句當(dāng)數(shù)據(jù)表不再需要時(shí)，可以使用DROPTABLE語句將其刪除。DROPTABLE數(shù)據(jù)表名DROPTABLEStudentTest（5）建立索引語句【例4.4】在StudentTest表的姓名（StudentName）和生日（Birthday）字段上建立唯一索引NameIndex,姓名按升序排列，姓名相同時(shí)，按生日降序排列。（6）刪除索引語句CREATEINDEX索引名ON數(shù)據(jù)表名(字段ASC|DESC)--默認(rèn)為索引順序?yàn)锳SC。DROPINDEX數(shù)據(jù)表名.索引名【例4.5】刪除StudentTest表中名稱為NameIndex的索引。CREATEUNIQUE

INDEX

NameIndex

onStudentTest(StudentName,

BirthdayDESC)DROPINDEXStudentTest.NameIndex4.1.3數(shù)據(jù)操縱語言DMLSELECT語句：從一個(gè)表或多個(gè)表查詢數(shù)據(jù)INSERT語句：向一個(gè)表中添加一條記錄UPDATE語句：修改表中某一個(gè)或幾個(gè)字段的值DELETE語句：從一個(gè)表中刪除記錄DML語句實(shí)現(xiàn)對(duì)數(shù)據(jù)的查詢和更新，主要語句及功能如下：1．查詢語句SELECT又稱為選擇查詢語句，用于從數(shù)據(jù)庫的一個(gè)或多個(gè)表中檢索滿足一定條件的記錄集，并按一定順序顯示指定的字段內(nèi)容。SELECT字段表FROM表名[WHERE查詢條件][GROUPBY分組字段[HAVING分組條件]][ORDERBY字段名[ASC/DESC]][]表示可選項(xiàng)。第2行至最后一行稱作SQL子句，不同的子句完成不同的功能。在書寫SELECT語句時(shí)，字母大小寫意義完全相同。語句可以寫在若干行上（如上述語法格式中的描述），也可以不換行。字段表位于關(guān)鍵字SELECT后面，這些字段將作為查詢的結(jié)果被顯示。①可選任意多個(gè)字段，字段與字段之間用逗號(hào)分隔?！纠?.6】查詢Student表中的所有學(xué)生的姓名和性別。(1)字段表SELECTStudentName,Sex

FROMStudent②可以使用通配符“*”表示表中的所有字段?！纠?.7】查詢Student表中所有學(xué)生的全部字段信息。SELECT*

FROMStudent③若字段名或表名中含有空格，則該字段名或表名必須用方括號(hào)括起來。④在字段表中，可以使用如下方式將原字段名以新的字段名顯示：字段名AS新字段名【例4.8】查詢Class表中班號(hào)和班名班號(hào)別名為ClassNumber。提示：“ClassNumber”含空格，要寫為[ClassNumber]SELECTClassCodeAS[ClassNumber],ClassNameFROMClass⑤在字段名前可以加上一些關(guān)鍵字，以便進(jìn)一步優(yōu)化查詢結(jié)果。常用的關(guān)鍵字有：DISTINCT：若多條記錄的字段值具有相同數(shù)據(jù)，只顯示一條記錄。【例4.11】顯示Course表中開課的學(xué)院名稱Academy（與所開課程無關(guān)）。SELECTDISTINCTAcademyFROMCourse由于每個(gè)學(xué)院都開設(shè)了多門課程，因此多條記錄在字段Academy具有部分相同的值。增加Distinct限制后，查詢結(jié)果中相同的值只顯示一次。TOPn|mPERCENT：查詢前n條記錄或前m%的記錄?！纠?.9】查詢Course表中的前3條記錄?！纠?.10】查詢Course表中前20%的記錄，顯示課程號(hào)和課程名稱。SELECTTOP3*FROMCourseSELECTTOP20PERCENTCourseCode,CourseNameFROMCourse⑥新增字段查詢。查詢的字段可以是表中字段名，也可以是常量和表達(dá)式，表達(dá)式中可以使用內(nèi)部聚合函數(shù)進(jìn)行統(tǒng)計(jì)計(jì)算。常用函數(shù)表函數(shù)名函數(shù)功能AVG計(jì)算某一字段的平均值（字段值必須是數(shù)值型）COUNT統(tǒng)計(jì)某一字段的個(gè)數(shù)MAX查找某一字段的最大值MIN查找某一字段的最小值SUM計(jì)算某一字段的總和（字段的值必須是數(shù)值型）【例4.12】查詢Grade表，顯示學(xué)號(hào)、所選課程號(hào)及加2分調(diào)整后的成績(jī)（Grade），并顯示一列說明“情況屬實(shí)”?！纠?.13】查詢Grade表，統(tǒng)計(jì)所有學(xué)生的分?jǐn)?shù)總和、平均分、最高分、最低分及總選課人次。SELECTStudentCode,CourseCode,Grade+2AS成績(jī),'情況屬實(shí)'AS說明FROMGradeSELECTSum(Grade)AS總分,Avg(Grade)AS平均分,Max(Grade)AS最高分,Min(Grade)AS最低分,Count(StudentCode)AS總?cè)舜蜦ROMGrade【例4.14】將Student表中所有記錄的學(xué)生姓名、出生日期復(fù)制到一張新表NewTable。⑦使用查詢創(chuàng)建新表。在字段名后加上“INTO表名”可將查詢結(jié)果復(fù)制到一張新表中。SELECTStudentName,BirthDayINTONewTableFROMStudent提示：語句運(yùn)行后，數(shù)據(jù)庫School中產(chǎn)生一張表名為NewTable的新表。刷新數(shù)據(jù)庫，即可查看到。（2）FROM子句FROM子句是SELECT語句必不可少的一個(gè)子句，指定要查詢的數(shù)據(jù)來自哪個(gè)或哪些表或視圖。FROM表名1[，表名2，……，表名n]【例4.15】查詢Student表和Grade表，顯示學(xué)生的選課和成績(jī)情況。SELECTStudent.StudentCode,StudentName,CourseCode,GradeFROMStudent,GradeWHERE

Grade.StudentCode=Student.StudentCodeSELECTStudentCode,StudentName,CourseCode,GradeFROMStudent,Grade

()注意：表的排列順序不影響執(zhí)行結(jié)果；如果FROM子句中包含多個(gè)表名，且不同的表中具有相同的字段，那么SELECT子句的字段名必須表示成“表名.字段名”。（3）WHERE子句WHERE子句說明查詢條件，它是一個(gè)可選的子句。它必須緊跟在FROM子句的后面。

WHERE查詢條件查詢條件謂詞比較>、>=、<、<=、=、<>(不等于)、!>(不大于)、!<(不小于)、!=(不等于)確定范圍BETWEEN…AND、NOTBETWEEN…AND確定集合IN、NOTIN、EXISTS字符匹配LIKE、NOTLIKE空值ISNULL、ISNOTNULL否定NOT邏輯運(yùn)算AND、ORT-SQL中常用的關(guān)系和邏輯運(yùn)算其中，查詢條件是一個(gè)關(guān)系或邏輯表達(dá)式。①

比較和邏輯運(yùn)算【例4.16】查詢Student表中所有女學(xué)生的姓名。

【例4.17】查詢未住校的女學(xué)生的情況。

SELECTStudentCode,StudentName,

LiveInDormFROMStudentWHERELiveInDorm=0ANDSex=‘女’SELECTStudentNameFROMStudentWHERESex=‘女’②

BETWEEN…AND…運(yùn)算BETWEEN值1AND值2NOTBETWEEN值1AND值2【例4.18】在Grade表中查詢選修課程號(hào)為“101”、成績(jī)?cè)?0分到90分之間的所有學(xué)生，顯示其學(xué)號(hào)及成績(jī)。查詢某字段的值在指定范圍內(nèi)的數(shù)據(jù)記錄（結(jié)果包括值1和值2）。查詢不在指定范圍中的數(shù)據(jù)記錄。SELECTStudentCode,CourseCode,GradeFROMGradeWHERECourseCode='101‘ANDGradeBETWEEN70AND90此例的WHERE子句也可以改為：WHERECourseCode=‘101'ANDGrade>=70ANDGrade<=90【例4.19】查詢Student表中不在1990年到1994年中出生的學(xué)生的學(xué)號(hào)和姓名。SELECTStudentCode,StudentName,BirthdayFROMStudentWHEREBirthdayNOTBETWEEN'1990-01-01'AND'1994-12-30'在WHERE子句中可使用Year()函數(shù)：SELECTStudentCode,StudentName,BirthdayFROMStudentWHEREYear(Birthday)NOTBETWEEN1990AND1994注意：日期型常量的寫法?、鄞_定在集合中的IN運(yùn)算：查詢某字段值在或不在某集合的數(shù)據(jù)記錄【例4.20】查詢Student表中班號(hào)為“11”、“21”、“31”的學(xué)生。SELECTStudentCode,StudentName,Sex,ClassCodeFROMStudentWHEREClasscodeIN('11','21','31')

此例的WHERE子句也可以改為：WHEREClassCode='11'ORClassCode='21'ORClassCode='31'④字符串模式匹配的LIKE運(yùn)算：確定某字符字段的子串值是否符合指定的模式，可查詢滿足字符串匹配的數(shù)據(jù)記錄。例如：姓“王”的名字：‘王%’；第2個(gè)字是“小”的姓名：‘_小%’姓“張”的單名：‘張_’【例4.21】查詢Course表中課程名稱中包含“化學(xué)”兩個(gè)字的課程號(hào)及課程名稱?！纠?.22】查詢Student表中不姓“王”的學(xué)生。SELECTCourseCode,CourseNameFROMCourseWHERECourseNameLIKE'%化學(xué)%'SELECTStudentCode,StudentNameFROMStudentWHEREStudentNameLIKE'[^王]%'【例4.30】統(tǒng)計(jì)每門課程的選課人數(shù)和平均分。(4)GROUPBY子句：用于對(duì)數(shù)據(jù)記錄進(jìn)行分類匯總，即按指定字段把具有相同值的記錄通過匯總計(jì)算合并成一條記錄。GROUPBY分組[HAVING分組條件]HAVING子句：與WHERE子句作用類似，在使用GROUPBY完成分組后，顯示滿足HAVING子句中分組條件的所有記錄。

SELECTCourseCode,Count(StudentCode)AS選課人數(shù),AVG(Grade)AS平均分FROMGradeGROUPBYCourseCode如只查詢平均分大于等于80的記錄，則在語句后增加：[HAVINGavg(Grade)>=80]如果GROUPBY后的分組字段有多個(gè)，則表示多次分組。

【例4.31】統(tǒng)計(jì)各學(xué)院男、女教師的人數(shù)。SELECTAcademy,Sex,Count(TeacherCode)AS教師人數(shù)FROMTeacherGROUPBYSex,Academy注意：使用GROUPBY子句時(shí)，分組字段必須出現(xiàn)在SELECT后的字段列表中（可以是字段，也可在聚合函數(shù)中），否則不允許分組?！纠?.32】按成績(jī)升序顯示Grade表中的所有數(shù)據(jù)。(5)ORDERBY子句：按指定字段為查詢結(jié)果排序。通常是SQL語句最后一項(xiàng)。ORDERBY字段名[ASC/DESC]其中：ASC為升序排序，DESC為降序排序。缺省排列次序?yàn)樯?。ASC:Ascending，DESC:DescendingSELECT*FROMGradeORDERBYGrade可以指定多個(gè)排序的字段。規(guī)則：首先用指定的第一個(gè)字段對(duì)記錄排序，然后對(duì)此字段中具有相同值的記錄用第二個(gè)字段進(jìn)行排序，依此類推。若在SELECT語句中無此子句，則按原數(shù)據(jù)表的次序顯示數(shù)據(jù)。【例4.33】按姓名升序（若姓名相同，則按出生日期降序）顯示班級(jí)代碼、學(xué)生姓名及出生日期。SELECTClassName,StudentCode,StudentNameFROMClass,StudentWHEREClass.ClassCode=Student.ClassCodeORDERBYClassName,StudentNameDESCClassName默認(rèn)為ASC(6).聯(lián)接查詢：涉及多表的查詢稱為聯(lián)接查詢，可以用兩種方法實(shí)現(xiàn)。①在FROM子句中指出需查詢的表，在WHERE子句中說明兩個(gè)表相關(guān)聯(lián)字段的聯(lián)接條件

。注意：Student表和Grade表都有StudentCode字段，所以要在Select后的該字段名前指明其中一個(gè)表名Student.StudentCode?！纠?.23】查詢未住校學(xué)生的選課及成績(jī)的情況。SELECTStudent.StudentCode,StudentName,CourseCode,Grade,LiveinDormFROMGrade,StudentWHEREGrade.StudentCode=Student.StudentCodeANDLiveInDorm=0【例4.24】查詢優(yōu)異生的情況。要求顯示學(xué)生姓名、所在班級(jí)、認(rèn)定時(shí)間、學(xué)分積點(diǎn)。

SELECTStudentName,ClassName,GrantTime,GPAFROMExcellentStudent,Student,ClassWHEREExcellentStudent.StudentCode=Student.StudentCode

ANDStudent.ClassCode=Class.ClassCode注意：指明Student表和Grade表中StudentCode字段值相同的記錄相聯(lián)接。若在查詢時(shí)無此條件，查詢結(jié)果錯(cuò)誤。②使用聯(lián)接關(guān)鍵字JOIN…ON說明兩個(gè)表及相關(guān)聯(lián)字段的聯(lián)接條件。

FROM表1聯(lián)接關(guān)鍵字表2ON表1.字段名1<比較運(yùn)算符>

表2.字段名2其中：表1、表2是被聯(lián)接的表名；字段名是被聯(lián)接的字段。必須有相同的數(shù)據(jù)類型并包含同類數(shù)據(jù)，但不需要有相同的名稱。比較運(yùn)算符：=、<、>、<=、>=、<>常用聯(lián)接關(guān)鍵字：INNERJOIN（內(nèi)聯(lián)接）、LEFTOUTERJOIN（左外聯(lián)接）、RIGHTOUTERJOIN（右外聯(lián)接）

INNERJOIN（內(nèi)聯(lián)接）：查詢結(jié)果僅包含兩個(gè)表中每對(duì)聯(lián)接匹配的行。內(nèi)聯(lián)接是系統(tǒng)默認(rèn)的，可以將關(guān)鍵詞INNER省略。【例4.25】使用內(nèi)聯(lián)接查詢未住校學(xué)生的選課及成績(jī)情況。SELECTStudent.StudentCode,StudentName,CourseCode,Grade,LiveinDormFROMStudentINNER

JOINGradeONStudent.StudentCode=Grade.StudentCodeWHERELiveInDorm=0本句的查詢結(jié)果與例4.23完全相同，但語句中表的聯(lián)接關(guān)系表達(dá)更為清晰，WHERE子句只有一個(gè)條件“LiveInDorm=0”。Inner可省略

LEFTOUTERJOIN（左外聯(lián)接）：結(jié)果除了包含兩張表中符合聯(lián)接條件的記錄，還包含左表（寫在關(guān)鍵字LEFTOUTERJOIN左邊的表）中不符合聯(lián)接條件、但符合WHERE條件的全部記錄?？梢詫㈥P(guān)鍵字OUTER省略?！纠?.26】使用左外聯(lián)接查詢未住校學(xué)生的選課及成績(jī)情況。

SELECTStudent.StudentCode,StudentName,CourseCode,Grade,LiveInDormFROMStudentLEFTJOINGradeONStudent.StudentCode=Grade.StudentCodeWHERELiveInDorm=0有2條記錄的CourseCode和Grade字段值為NULL。這2條記錄是左表（Student）中未住校學(xué)生的信息，但在Grade表中沒有其相關(guān)選課記錄。

RIGHTOUTERJOIN（右外聯(lián)接）：結(jié)果除了包含兩張表中符合聯(lián)接條件的記錄，還包含右表（寫在關(guān)鍵字RIGHTTOUTERJOIN右邊的表）中不符合聯(lián)接條件、但符合WHERE條件的全部記錄?？梢詫㈥P(guān)鍵字OUTER省略?！纠?.27】使用右外聯(lián)接查詢未住校學(xué)生的選課及成績(jī)情況。

SELECTStudent.StudentCode,StudentName,CourseCode,Grade,LiveInDormFROMStudentRIGHTJOINGradeONStudent.StudentCode=Grade.StudentCodeWHERELiveInDorm=0結(jié)果與例4.25內(nèi)聯(lián)接完全相同。由于在表設(shè)計(jì)時(shí)，在表Student和Grade之間通過外鍵建立了參照完整性約束，表Grade中的所有StudentCode必須是Student中出現(xiàn)的值。③多表聯(lián)接嵌套：如果查詢所涉及的數(shù)據(jù)表在3個(gè)以上，形成聯(lián)接嵌套。1）使用WHERE子句設(shè)置查詢條件例如，查詢學(xué)生所選課程成績(jī)，顯示學(xué)生姓名、課程名稱，成績(jī)。涉及Student、Grade、Course三張表。SELECTStudentName,CourseName，GradeFROMStudent,Grade,CourseWHEREStudent.StudentCode=Grade.StudentCodeANDGrade.CourseCode=Course.CourseCode2）使用聯(lián)接關(guān)鍵字的語句FROM表1JOIN表2ON表1.字段i<比較運(yùn)算符>表2.字段jJOIN表3ON表x.字段k<比較運(yùn)算符>表3.字段l…[JOIN表nON表y.字段m<比較運(yùn)算符>

表n.字段n]x<=2,y<=n-1SELECTStudentName,CourseName,GradeFROMStudent

JOIN

GradeON

Student.StudentCode=Grade.StudentCode

JOINCourseONGrade.CourseCode=Course.CourseCode或者寫為如下格式：FROM表1JOIN表2JOIN表3…[JOIN表nON表n.字段i<比較運(yùn)算符>表n-1字段jON表x.字段k<比較運(yùn)算符>表n-2.字段l

…ON表y.字段m<比較運(yùn)算符>表1.字段n]x>=n-1,y>=2SELECTStudentName,CourseName,GradeFROMStudent

JOIN

GradeJOINCourseONGrade.CourseCode=Course.CourseCodeONStudent.StudentCode=Grade.StudentCode【例4.28】用嵌套聯(lián)接實(shí)現(xiàn)例4.24查詢優(yōu)異生的情況。SELECTStudentName,ClassName,GrantTime,GPAFROMExcellentStudentJOINStudentONExcellentStudent.StudentCode=Student.StudentCodeJOINClassONStudent.ClassCode=Class.ClassCode結(jié)果與例4.24使用Where子句說明聯(lián)接條件的結(jié)果完全相同?！纠?.29】查詢班號(hào)是“61”的學(xué)生姓名、班號(hào)、所選課程名稱和成績(jī)。該查詢需要用到3張表Student、Course和Grade。SELECTStudentName,ClassCode,CourseName,Grade

FROMStudentJOINGradeJOINCourseONGrade.CourseCode=Course.CourseCodeONStudent.StudentCode=Grade.StudentCodeWHEREClassCode='61'【例4.34】查詢“林豆豆”同班同學(xué)的學(xué)號(hào)、姓名。(7).子查詢:當(dāng)一個(gè)查詢的結(jié)果是另一個(gè)查詢的條件時(shí)，稱該查詢?yōu)樽硬樵?。也被稱為嵌套查詢。SELECTStudentCode,StudentName,ClassCodeFROMStudentWHEREClassCode=(SELECTClassCodeFROMStudentWHEREStudentName='林豆豆')IN子查詢：用來判斷一個(gè)給定值是否在子查詢的結(jié)果集中?！纠?.35】查詢選修了課程代碼為“101”的學(xué)生的學(xué)號(hào)、姓名和班號(hào)。如果子查詢結(jié)果返回多值則子查詢要與下面介紹的IN、EXIST等結(jié)合使用。SELECTStudentCode,StudentName,ClassCodeFROMStudentWHEREStudentCodeIN(SELECTStudentCodeFROMGradeWHERECourseCode='101')提示：在執(zhí)行過程中，子查詢得到選修課程“101”的所有學(xué)生學(xué)號(hào)返回給主查詢，主查詢?cè)俨樵冞@些學(xué)生的學(xué)號(hào)、姓名和班號(hào)。該查詢也可以利用聯(lián)接查詢完成：SELECTStudent.StudentCode,StudentName,ClassCodeFROMStudentINNERJOINGradeONStudent.StudentCode=Grade.StudentCodeWHERECourseCode='101'提示：多數(shù)情況下，包含子查詢的語句可以用聯(lián)接表示。但子查詢與聯(lián)接相比，有一個(gè)顯著的優(yōu)點(diǎn)，就是子查詢可以計(jì)算一個(gè)變化的聚集函數(shù)值，并返回到主查詢進(jìn)行比較，而聯(lián)接做不到?！纠?.36】查詢年齡最大的學(xué)生的學(xué)號(hào)和姓名。SELECTStudentCode,StudentName,BirthdayFROMStudentWHEREBirthdayIN

(SELECTMIN(Birthday)FROMStudent)提示：子查詢獲取Student表中最小的出生日期即年齡最大學(xué)生的出生日期，主查詢?cè)俨樵冞@一日期出生的學(xué)生的學(xué)號(hào)、姓名和出生日期。EXISTS子查詢：用于判斷一個(gè)子查詢的結(jié)果集是否為非空，如果非空則返回TRUE，否則返回FALSE。NOTEXISTS的返回值與EXISTS相反

。【例4.35】用EXISTS子查詢實(shí)現(xiàn)：查詢選修了課程代碼為“101”的學(xué)生的學(xué)號(hào)、姓名和班級(jí)代號(hào)。SELECTStudentCode,StudentName,ClassCodeFROMStudentWHEREEXISTS

(SELECT*FROMGradeWHEREStudent.StudentCode=Grade.StudentCodeANDGrade.CourseCode='101')提示：本查詢的執(zhí)行過程是：首先查找主查詢中Student表的第一行，將該行的學(xué)號(hào)提供給子查詢，然后子查詢執(zhí)行，若結(jié)果集不為空，則把該行的StudentCode，StudentName，ClassCode作為結(jié)果集的第一行輸出；然后再找Student表中的第2、3……行，重復(fù)上述處理過程，直到Student中的數(shù)據(jù)行全部處理完畢。【例4.37】查詢所有未選修任何課程的學(xué)生。SELECTStudentCode,StudentNameFROMStudentWHERENOTEXISTS(SELECT*FROMGradeWHEREStudent.StudentCode=Grade.StudentCode)２．?dāng)?shù)據(jù)插入語句INSERTINTO①向表中插入一條數(shù)據(jù)記錄。INSERTINTO表名[(字段名1，字段名2，……)]VALUES（表達(dá)式1，表達(dá)式2，……）其中：（1）字段可以是某幾個(gè)字段。表達(dá)式1、表達(dá)式2分別對(duì)應(yīng)字段名1、字段名2，它們是所要添加的記錄的值。（2）如果表中的字段沒有在添加記錄語句中出現(xiàn)，則對(duì)應(yīng)的值為NULL。（3）如果在定義表結(jié)構(gòu)時(shí)已經(jīng)說明了某個(gè)字段為必填字段（不能是空值）則在語句中必須為該字段添加對(duì)應(yīng)的數(shù)值。（4）當(dāng)插入一條完整的記錄時(shí)，可省略字段名，但字段值次序要與表中字段的次序一一對(duì)應(yīng)?！纠?.38】向Grade表插入一條記錄?！纠?.39】向Student表插入一條記錄。INSERTINTOGradeVALUES('2102','113',90,'2012-12-20‘)INSERTINTOStudent(StudentCode,Sex,

StudentName,ClassCode)VALUES('3105','女','張琳','31‘)本例插入完整記錄，可省略字段名，但注意值要與表中字段次序一致。本例插入記錄的部分字段值，注意非空字段必須有值；字段次序可與表不同，但與值的對(duì)應(yīng)順序一致。②從其他表提取一組記錄插入到目標(biāo)表中。INSERTINTO表名[（字段1，字段2，……）]SELECT源字段名表FROM源表名[WHERE添加條件]【例4.40】向StudentTest表插入記錄，數(shù)據(jù)為Student中所有女同學(xué)。INSERTINTOStudentTestSELECTStudentCode,StudentName,Sex,LiveInDormFromStudentWHERESex='女’提示：此處數(shù)據(jù)表必須已經(jīng)存在，且其結(jié)構(gòu)定義與SELECT語句返回的字段值類型一致。3.修改記錄語句UPDATEUPDATE表名SET字段名1=表達(dá)式1[，字段名2=表達(dá)式2……]WHERE

條件表達(dá)式實(shí)現(xiàn)對(duì)一條或多條符合條件記錄中某個(gè)或某些字段值的修改。提示：一個(gè)UPDATE語句可以更新多個(gè)字段值；如沒有WHERE子句，將更新數(shù)據(jù)表中所有記錄?！纠?.41】更新Student表中學(xué)號(hào)為“8101”的聯(lián)系電話為“67792280”【例4.42】更新Course表中信息，將所有開課學(xué)院為“計(jì)算機(jī)學(xué)院”的課程的實(shí)驗(yàn)學(xué)時(shí)增加10%，上課學(xué)時(shí)增加10。UPDATEStudentSETTelephone='67792280'WHEREStudentCode='8101'UPDATECourseSETLabHour=LabHour*(1+0.1),ClassHour=ClassHour+10WHEREAcademy='計(jì)算機(jī)學(xué)院'4.刪除語句DELETEDELETEFROM表名[WHERE刪除條件]【例4.43】刪除Student表中學(xué)號(hào)為“3105”的記錄?！纠?.44】刪除Student表中平均成績(jī)低于70的學(xué)生。刪除指定表中滿足條件的一條或多條記錄。如果沒有WHERE子句，即沒有指定刪除條件，則該語句執(zhí)行后將刪除指定表中的所有記錄。DELETEFROMStudentWHEREStudentCode='3105'DELETEFROMStudentWHEREStudentCodeIN(SELECTStudentCodeFROMGrade

GROUPBYStudentCode

HAVINGAVG(Grade)<70)4.1.4T-SQL的運(yùn)算功能和控制流程T-SQL雖然和高級(jí)語言不同，但它也有運(yùn)算、控制等功能，以支持復(fù)雜的數(shù)據(jù)檢索和集合操縱。

1.標(biāo)識(shí)符、常量和變量（1）標(biāo)識(shí)符：由用戶定義的名稱，用來標(biāo)識(shí)各種對(duì)象如服務(wù)器、數(shù)據(jù)庫、數(shù)據(jù)庫對(duì)象、變量等。第一個(gè)字符必須是字母

、下劃線(_)、@或#。注意：在SQLServer中，如果標(biāo)識(shí)符中包含空格，要用雙引號(hào)（“”）或方括號(hào)([])擴(kuò)起來。如：mytable不合法，必須將其表示為[mytable]或＂mytable＂。（2）常量：在程序運(yùn)行過程中值不變的量。

注意：在T-SQL語句中不同類型常量的寫法數(shù)值型：直接寫。如：23,45.6字符串：用西文單引號(hào)擴(kuò)起來。如：‘男’。如字符串包含單引號(hào)，則可以使用兩個(gè)單引號(hào)表示嵌入的單引號(hào)。

‘’’211’’

工程’---‘211’工程日期時(shí)間型：用西文單引號(hào)擴(kuò)起來。如：'2008-08-08'，'2008-08-0820:08:08'Bit型：直接寫。如：0,1@@ERROR:返回最后執(zhí)行的Transact-SQL語句的錯(cuò)誤代碼。（3）變量：在程序運(yùn)行過程中其值可以被改變的量。1)變量的分類局部變量：由用戶定義和使用，其名稱前有一個(gè)@符號(hào)。全局變量：全局變量由系統(tǒng)定義和維護(hù)，其名稱前有兩個(gè)@符號(hào)。例如：@@ERROR:返回最后執(zhí)行的T-SQL語句的錯(cuò)誤代碼。2）局部變量的定義和賦值DECLARE@局部變量名數(shù)據(jù)類型說明：如在一條語句中聲明多個(gè)變量，各變量之間用“，”分隔。局部變量被定義后其初始值為NULL。例如：DECLARE@xfloat,@varchar(8)該語句定義了變量@x是浮點(diǎn)實(shí)型，變量@var是長(zhǎng)度為8的字符數(shù)據(jù)類型。【例4.45】使用課程號(hào)變量和成績(jī)變量查詢Grade表中課程代碼為“105”且成績(jī)低于80分的記錄。

局部變量賦值的基本語法格式如下：SET@局部變量名=表達(dá)式SELECT@局部變量名=表達(dá)式DECLARE@codechar(3),@scoreNumeric(3,1)--變量@code表示課程號(hào)，@score表示成績(jī)SET@code='105'

--給@code賦值圖4.23變量查詢示例SELECT@score=80

--給@score賦值SELECT*--使用變量@code和@score查詢FROMGradeWHERECourseCode=@codeANDGrade<@score提示：變量類型要與Grade表中字段的類型一致，使用前要先賦值?！纠?.46】將學(xué)號(hào)為“1102”的學(xué)生姓名存放到變量@sname中。DECLARE@snamevarchar(16)SELECT@sname=(SELECTStudentName

FROMT_StudentWHEREStudentCode=‘1102')SELECT@snameas'姓名'

提示：SELECT語句可用于將單個(gè)值賦于一個(gè)變量。如果子查詢返回值有多個(gè)，則將最后一個(gè)返回值賦給變量；如果子查詢沒有返回值，變量就保留當(dāng)前值。SET

@sname2．運(yùn)算符和表達(dá)式(1)運(yùn)算符1)算術(shù)運(yùn)算符：+、-、*、/、模運(yùn)算（%）2)比較運(yùn)算符和邏輯運(yùn)算符>=、<、<=、=、<>、!>、!<、!=BETWEENAND、NOTBETWEENANDIN、NOTINLIKE、NOTLIKEISNULL、ISNOTNULLNOT、AND、OR3）字符串連接運(yùn)算符：+例如：‘ABCD’+‘1234’結(jié)果為：‘ABCD1234’例如：查詢Grade表中還未輸入成績(jī)的學(xué)生代號(hào)和課程代號(hào)。SELECT*FROMGradeWHEREGradeIS

NULL(2)表達(dá)式

表達(dá)式是由運(yùn)算對(duì)象、運(yùn)算符及圓括號(hào)組成。在SQL查詢分析器中可使用Select語句查看表達(dá)式的結(jié)果。例如：SELECT'ABCD'+'1234'

當(dāng)一個(gè)表達(dá)式中有多個(gè)運(yùn)算符時(shí)，運(yùn)算符的優(yōu)先級(jí)決定運(yùn)算的先后次序。（1）數(shù)學(xué)函數(shù)：常用的數(shù)學(xué)函數(shù)及功能如表所示。3．常用函數(shù)RAND(n):返回0到1之間的隨機(jī)float值。n是種子值，如果沒有提供種子值，系統(tǒng)將生成它自己的不定種子值。如果用種子值調(diào)用RAND，則必須使用不定的種子值來生成隨機(jī)數(shù)。如果用同一種子值多次調(diào)用RAND，它將返回相同的生成值。ROUND(x,n):當(dāng)n為正數(shù)時(shí)，x四舍五入為n所指定的小數(shù)位數(shù)。當(dāng)n為負(fù)數(shù)時(shí)，x則按n所指定的在小數(shù)點(diǎn)的左邊四舍五入。例如：ROUND(878.586,

2)=878.590ROUND(878.586895,3)=878.587000

ROUND(878.586,0)=879.000

ROUND(878.586,-1)=880.000

ROUND(878.586,-3)=1000.000ROUND(878.586,-4)=.000函數(shù)名函數(shù)功能函數(shù)名函數(shù)功能ABS(x)計(jì)算x的絕對(duì)值SQRT(x)計(jì)算x的平方根ATN(x)計(jì)算x的反正切值SIGN(x)返回x的特征符號(hào)COS(x)計(jì)算x的余弦值TAN(x)計(jì)算x的正切值EXP(x)計(jì)算ex([n])產(chǎn)生[0,1)之間的隨機(jī)float值,n為正數(shù)LOG(x)計(jì)算自然對(duì)數(shù)lnxROUND（x,n）將x四舍五入為指定精度，n為小數(shù)位數(shù)SIN(x)計(jì)算x的正弦值表4.7常用數(shù)學(xué)函數(shù)(2)日期時(shí)間函數(shù)：常用日期函數(shù)如表所示。SELECTYEAR(‘03/12/1998’)AS年份提示：在數(shù)據(jù)庫設(shè)計(jì)時(shí)，如將日期型字段的默認(rèn)值設(shè)置為GetDate()，則在添加新記錄時(shí)，如果不賦值，自動(dòng)用當(dāng)前時(shí)間填入。SELECTDATEDIFF(year,'2000-01-01',getdate())SELECTGETDATE()AS今天的日期函數(shù)名函數(shù)功能DAY(x)返回指定日期中所表示的日，x是日期，例如：Day(‘2014-5-1’)=1MONTH(x)返回指定日期中所表示的月，例如：Month(‘2014-5-1’)=5YEAR(x)返回指定日期中所表示的年份，例如：YEAR(‘2014-5-1’)=2014GETDATE()返回當(dāng)前系統(tǒng)日期和時(shí)間(3)字符串函數(shù)：常用字符串函數(shù)如表所示。SELECTLEN(‘Sun’),LEN(‘中國(guó)北京’)

--結(jié)果分別為數(shù)值3和4SELECTSUBSTRING(‘VB語言程序設(shè)計(jì)’,3,2)

--結(jié)果為字符串“語言”SELECTREPLACE('數(shù)據(jù)庫系統(tǒng)','系統(tǒng)','應(yīng)用')--結(jié)果為字符串“數(shù)據(jù)庫應(yīng)用”函數(shù)名函數(shù)功能函數(shù)名函數(shù)功能ASCII(s)返回字符串s最左端字符的ASCII碼STR(n)將數(shù)字?jǐn)?shù)據(jù)n轉(zhuǎn)換為字符串CHAR(n)將ASCII轉(zhuǎn)換為字符SPACE(n)返回n個(gè)空格LEFT(s,n)返回字符串s左邊的n個(gè)字符SUBSTRING(s,m,n)返回字符串s起始m長(zhǎng)度為n的子串RIGHT(s,n)返回字符串s右邊的n個(gè)字符LOWER(s)將字符串中的字母轉(zhuǎn)換為小寫字母LEN(s)返回字符串s的長(zhǎng)度（字符的個(gè)數(shù)）UPPER(s)將字符串中的字母轉(zhuǎn)換為大寫字母LTRIM(s)刪除字符串s開始處的空格REPLACE(s1,s2,s3)用s3替換s1中包含的s2RTRIM(s)刪除字符串s結(jié)尾處的空格SELECTstr(123.4,5)SELECTstr(123.4,5,1)SELECTstr(123.4,2)str(float_expression[,length[,decimal]])(4)聚合函數(shù)函數(shù)名函數(shù)功能AVG([ALL|DISTINCT]expression)計(jì)算某一字段的平均值（此字段的值必須是數(shù)值型）COUNT([ALL|DISTINCT]expression)統(tǒng)計(jì)某一字段的個(gè)數(shù)MAX([ALL|DISTINCT]expression)查找某一字段的最大值MIN([ALL|DISTINCT]expression)查找某一字段的最小值SUM([ALL|DISTINCT]expression)計(jì)算某一字段的總和（此字段的值必須是數(shù)值型）聚合函數(shù)對(duì)一組值進(jìn)行計(jì)算并返回一個(gè)結(jié)果，常用于對(duì)記錄的分類匯總，聚合函數(shù)經(jīng)常與SELECT語句的GROUPBY子句一同使用。常用聚合函數(shù)如下：T-SQL語言也提供了一些流程控制語句，使得對(duì)數(shù)據(jù)庫中數(shù)據(jù)的檢索、更新、插入等操作更加方便。

BEGIN…END語句多條T-SQL語句使用BEGIN…END組合起來形成一個(gè)語句塊。BEGIN…END可以嵌套使用。4．流程控制語句BEGINSQL語句1SQL語句2…ENDIF條件表達(dá)式

SQL語句1[ELSE

SQL語句1](2)IF…ELSE語句通過判定給定的條件來決定執(zhí)行哪條語句或語句塊。說明：1)首先計(jì)算條件表達(dá)式的值，如果為TRUE，則執(zhí)行IF后面的語句塊，否則執(zhí)行ELSE后面的語句塊。2)如果是單分支流程，可不含ELSE。3)如果條件表達(dá)式中包含SELECT語句，則必須用圓括號(hào)將SELECT語句括起來。

【例4.47】統(tǒng)計(jì)學(xué)號(hào)為“1102”的學(xué)生的選課總數(shù)，如果不少于三門課就報(bào)告選課門數(shù)，否則顯示其選修的課程信息，并提示選課太少。

USESchoolDECLARE@cnsmallint,@textvarchar(100)SET@cn=(SELECTcount(StudentCode)FROMGradeWHEREStudentCode=‘1102’)

--查選課門數(shù)IF@cn>=3

SET@text=‘你選了’+LTRIM(STR(@cn))+’門課，很好！’ELSE

BEGIN

SELECT*FROMGradeWHEREStudentCode=‘1102’

--查詢報(bào)告選課信息

SET@text=‘你選了’+LTRIM(STR(@cn))+’門課,選課太少,加油！’

ENDSELECT@textAS選課提示--查詢報(bào)告選課提示信息WHILE條件表達(dá)式

SQL語句1[BREAK]

SQL語句2[CONTINUE]（3）WHILE語句實(shí)現(xiàn)一條SQL語句或SQL語句塊重復(fù)執(zhí)行。說明：計(jì)算條件表達(dá)式的值，如果為TRUE，則執(zhí)行WHILE后的語句塊。重復(fù)上述過程,當(dāng)條件表達(dá)式的值為False時(shí),結(jié)束WHILE語句。2)BREAK為從本層WHILE循環(huán)中退出，當(dāng)存在多層循環(huán)嵌套時(shí)，使用BREAK語句只能退出其所在的內(nèi)層循環(huán)，然后重新開始外層的循環(huán)。3)CONTINUE為結(jié)束本次循環(huán)，開始下一次循環(huán)的判斷?！纠?.48】調(diào)整課程號(hào)為“105”的課程成績(jī)。當(dāng)該課平均成績(jī)小于80時(shí)，為每個(gè)同學(xué)的成績(jī)加5分，循環(huán)操作直到最高分大于等于95分。WHILE(SELECTAVG(Grade)FROMGradeWhereCourseCode='105')<80IF(SELECTMAX(Grade)FROMGrade

WHERECourseCode='105')<95BEGIN

UPDATEGradeSETGrade=Grade+5WHERECourseCode='105'

CONTINUEENDELSEBREAKRETURN表達(dá)式

（4）RETURN語句使程序從一個(gè)查詢或存儲(chǔ)過程中無條件返回，并返回表達(dá)式的值，其后的語句不再執(zhí)行。（5）CASE語句

CASE語句是多分支的選擇語句。簡(jiǎn)單CASE函數(shù)形式：將某個(gè)表達(dá)式與一組簡(jiǎn)單表達(dá)式進(jìn)行比較以確定結(jié)果。CASE搜索函數(shù)形式：計(jì)算一組條件表達(dá)式以確定結(jié)果。

1）簡(jiǎn)單CASE函數(shù)CASE輸入表達(dá)式WHEN情況表達(dá)式THEN結(jié)果表達(dá)式...[ELSE結(jié)果表達(dá)式]END1)當(dāng)輸入表達(dá)式的值與某一個(gè)WHEN子句的情況表達(dá)式的值相等時(shí)，就返回該WHEN子句中結(jié)果表達(dá)式的值。2)如果所有都不相等，則返回ELSE子句后的結(jié)果表達(dá)式的值。若沒有ELSE子句，則返回NULL值?！纠?.49】查詢1994年出生的男同學(xué)的住校情況。SELECTStudentNameAS'姓名',

CASELiveInDorm

WHEN0THEN'未住校'

WHEN1THEN'住校'

ENDAS'是否住校'FROMStudentWHERESex='男'ANDYEAR(Birthday)=19942）CASE搜索函數(shù)CASE

WHEN條件表達(dá)式THEN結(jié)果表達(dá)式

...

[ELSE結(jié)果表達(dá)式]END【例4.50】統(tǒng)計(jì)每個(gè)學(xué)生平均成績(jī)并劃分等級(jí)。

SELECTStudentCodeAS'學(xué)號(hào)',STR(AVG(Grade),5,2)AS'平均成績(jī)',CASE

WHENAVG(Grade)>=90THEN'A'

WHENAVG(Grade)>=80THEN'B'

WHENAVG(Grade)>=70THEN'C'

WHENAVG(Grade)>=60THEN'D'

WHENAVG(Grade)<60THEN'E'ENDAS'等級(jí)'FROMGradeGROUPBYStudentCode

說明：按順序計(jì)算WHEN子句的條件表達(dá)式，當(dāng)布爾表達(dá)式的值為TRUE，則返回THEN后面的結(jié)果表達(dá)式的值，然后跳出CASE語句。4.2視圖視圖是數(shù)據(jù)庫的外模式，通常用來為用戶集中數(shù)據(jù)、簡(jiǎn)化用戶的數(shù)據(jù)查詢。視圖是一個(gè)虛擬表，其內(nèi)容來自對(duì)數(shù)據(jù)表的查詢結(jié)果。和數(shù)據(jù)表一樣，視圖也是二維表結(jié)構(gòu)。視圖也可作為一種安全機(jī)制，允許用戶通過視圖訪問數(shù)據(jù)，而不授予用戶直接訪問某些基礎(chǔ)表的權(quán)限。：4.2.1創(chuàng)建視圖在SQLServer中創(chuàng)建視圖主要有兩種方式：(1)在ManagementStudio中使用向?qū)?chuàng)建視圖；(2)通過執(zhí)行視圖定義SQL語句CREATEVIEW創(chuàng)建視圖。創(chuàng)建視圖時(shí)必須遵循以下原則：只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖。視圖在數(shù)據(jù)庫中作為一個(gè)對(duì)象存儲(chǔ)，視圖名稱不得與數(shù)據(jù)庫中的表重名。1.在ManagementStudio中使用向?qū)?chuàng)建視圖【例4.51】在數(shù)據(jù)庫School中建立視圖V_StudentGrade，查詢學(xué)生姓名、課程名和成績(jī)。①

展開“School/視圖”，可看到數(shù)據(jù)庫中已有一些系統(tǒng)視圖。用鼠標(biāo)右擊“視圖”，選擇“新建視圖”菜單可打開“添加表”對(duì)話框。②選擇與視圖有關(guān)的表、視圖或函數(shù)（同時(shí)按Ctrl或Shift鍵可選擇多項(xiàng)），單擊“添加”按鈕；或直接雙擊要添加的表，即可將其添加到視圖設(shè)計(jì)窗口中。本例添加數(shù)據(jù)表Student、Course和Grade。單擊“關(guān)閉”按鈕，進(jìn)入視圖設(shè)計(jì)窗口。③在上格單擊每個(gè)表字段前的復(fù)選框，可將該字段添加到視圖中，也可在第二個(gè)窗格中選擇視圖字段，并可指定列的別名、排序方式和規(guī)則等,在選擇過程中，第三個(gè)窗格中的SELECT語句也會(huì)隨之自動(dòng)改變。上格是表及其關(guān)系窗格中格是為視圖選擇表中列的網(wǎng)格下格是結(jié)果窗格SQL語句窗格提示:可省略第②~④步驟,直接在第三個(gè)窗格中輸入實(shí)現(xiàn)查詢的SELECT語句。④單擊工具欄上的“”按鈕執(zhí)行，視圖的查詢結(jié)果顯示在結(jié)果窗格中。⑤單擊工具欄上的“”按鈕，在彈出的“另存為”對(duì)話框中為視圖命名，本例輸入“V_StudentGrade”，單擊【確定】按鈕保存視圖，從而完成視圖創(chuàng)建。①在創(chuàng)建視圖的“添加表”對(duì)話框中選擇“視圖”選項(xiàng)卡，添加視圖V_StudentGrade到視圖設(shè)計(jì)器中。

【例4.52】在數(shù)據(jù)庫School中，建立視圖V_StudentAvgGradeExcellent，選拔優(yōu)培生（平均成績(jī)大于等于90的學(xué)生），并要求按平均成績(jī)從高到低排列。該例可參照上例方法在基本表Student和Grade上創(chuàng)建。由于視圖的內(nèi)容也可以來自另一個(gè)視圖，這里基于視圖V_StudentGrade創(chuàng)建。②在“視圖設(shè)計(jì)”窗口的上格，首先為視圖選擇“姓名”和“成績(jī)”字段。右擊第二個(gè)窗格的字段“姓名”，選擇快捷菜單的“添加分組依據(jù)”；在“成績(jī)”行的“分組依據(jù)”列選“Avg”，篩選器列輸入“>=90”，排序類型列選“降序”。③單擊工具欄上的“”按鈕可查看結(jié)果，保存視圖為“V_StudentAvgGradeExcellent”。2．使用SQL語句創(chuàng)建視圖在查詢編輯窗口中執(zhí)行SQL語句來完成視圖創(chuàng)建CREATEVIEW視圖名稱ASSELECT查詢語句【例4.53】創(chuàng)建一個(gè)性別為“男”的學(xué)生視圖V_StudentSexMale，包括學(xué)生的學(xué)號(hào)、姓名、班號(hào)和性別。CREATEVIEWV_StudentSexMaleASSELECTStudentCodeAs姓名,StudentNameAs姓名,ClassCodeAs班號(hào),SexAs性別FROMStudentWHERESEX='男'SELECT課程名稱,AVG(成績(jī))AS平均分FROMV_StudentGradeGROUPBY課程名稱HAVING課程名稱='大學(xué)英語'4.2.2使用視圖創(chuàng)建后的視圖與表的用法相同，可以通過視圖查詢和更新數(shù)據(jù)庫。1．查詢視圖操作【例4.54】在數(shù)據(jù)庫School中，查詢視圖V_StudentGrade統(tǒng)計(jì)“大學(xué)英語”課程的平均分。2．更新視圖操作通過視圖來插入、刪除和修改數(shù)據(jù)。由于視圖是不實(shí)際存儲(chǔ)數(shù)據(jù)的虛表，因此對(duì)視圖的更新最終要轉(zhuǎn)換為對(duì)基本表的更新。

注意并不是所有的視圖都可以執(zhí)行各種更新操作。

如果視圖所依賴的基本表有多個(gè)時(shí)，對(duì)于更新操作有以下限制：①不能向視圖添加數(shù)據(jù)，因?yàn)檫@將影響多個(gè)基本表；②一次只能修改一個(gè)基本表中的數(shù)據(jù)；③不能通過視圖刪除數(shù)據(jù)。視圖更新方法：在ManagementStudio選中要更新的視圖，打開即可更新；也可通過執(zhí)行SQL語句更新視圖。但要更新的視圖必須符合更新規(guī)則，否則系統(tǒng)提示出錯(cuò)?！纠?.55】更新V_StudentSexMale。1）向視圖V_StudentSexMale中插入一個(gè)新的學(xué)生記錄：（'1104','趙謙','11','男'）

INSERTINTOV_StudentSexMaleVALUES('1104','趙謙','11','男')說明：向視圖V_StudentSexMale添加記錄的命令實(shí)際上是對(duì)數(shù)據(jù)表Student添加記錄。2）修改該記錄：將“趙謙”名字改為“趙廉”。UPDATEV_StudentSexMaleSETStudentName=‘趙廉’WHEREStudentName=‘趙謙’說明：對(duì)視圖V_StudentSexMale修改，實(shí)際是修改Student表中相關(guān)的記錄。4.2.3修改和刪除視圖1．修改視圖：修改視圖的定義，即修改視圖中指定字段的字段名、別名、表名、是否輸出等屬性。在ManagementStudio中右擊要修改的視圖，選擇“修改”命令，在視圖設(shè)計(jì)窗口進(jìn)行修改；直接修改視圖定義語句。2．刪除視圖在ManagementStudio中右擊要?jiǎng)h除的視圖，選擇“刪除”命令，在“刪除對(duì)象”對(duì)話框中，按“確定”按鈕即可刪除相應(yīng)視圖。提示：刪除視圖對(duì)基本表沒有任何影響，因?yàn)橐晥D只是個(gè)虛擬表。存儲(chǔ)過程是T-SQL語句的集合，它作為數(shù)據(jù)庫對(duì)象之一被存儲(chǔ)在數(shù)據(jù)庫中，用戶的應(yīng)用程序調(diào)用存儲(chǔ)過程可實(shí)現(xiàn)對(duì)數(shù)據(jù)庫的訪問。

4.3存儲(chǔ)過程存儲(chǔ)過程的作用和使用方式類似于一些編程語言中的過程。由應(yīng)用程序調(diào)用執(zhí)行，可以接受輸入?yún)?shù)，并以輸出參數(shù)的形式將多個(gè)值返回給調(diào)用它的程序。使用存儲(chǔ)過程有以下優(yōu)點(diǎn)：可以在一個(gè)存儲(chǔ)過程中執(zhí)行多條SQL語句?？梢酝ㄟ^輸入?yún)?shù)的變化調(diào)用存儲(chǔ)過程進(jìn)行動(dòng)態(tài)執(zhí)行存儲(chǔ)過程在創(chuàng)建時(shí)就在服務(wù)器端進(jìn)行了編譯，節(jié)省SQL語句的運(yùn)行時(shí)間。提供了安全機(jī)制，它限制了用戶訪問SQL語句的權(quán)利，只為特定用戶開放存儲(chǔ)過程。1．使用SQL語句創(chuàng)建存儲(chǔ)過程

4.3.1創(chuàng)建存儲(chǔ)過程CREATEPROC[EDURE]存儲(chǔ)過程名{@形式參數(shù)數(shù)據(jù)類型}[=默認(rèn)值][OUTPUT]ASSQL語句1…SQL語句n說明：

1）“形式參數(shù)”名稱必須符合標(biāo)識(shí)符規(guī)則；2）OUTPUT表示該參數(shù)是可以返回的，可將信息返回調(diào)用者；3）“默認(rèn)值”表示輸入?yún)?shù)的默認(rèn)值，該值是常量或NULL，如果定義了默認(rèn)值，不必提供實(shí)參，存儲(chǔ)過程就可執(zhí)行；4）如有多個(gè)參數(shù)，可依次列出，用逗號(hào)“，”隔開。執(zhí)行存儲(chǔ)過程：EXEC[UTE]存儲(chǔ)過程名[[@形參=]實(shí)參值|@變量[OUTPUT]|[DEFAULT]]說明：

1)“@形參”是創(chuàng)建存儲(chǔ)過程時(shí)定義的形參名；2)“實(shí)參值”是輸入?yún)?shù)的值；3)“@變量”表示用來保存參數(shù)或者返回參數(shù)的變量；OUTPUT表示指定參數(shù)為返回參數(shù)；4)DEFAULT表示使用該參數(shù)的默認(rèn)值作為實(shí)參