第4章-SQLSERVER-數(shù)據(jù)庫(kù)的操作(查詢語(yǔ)句)

四數(shù)據(jù)查詢與視圖

4.1SELECT語(yǔ)句概述

4.2單表查詢

4.3多表查詢

4.4嵌套查詢

4.5視圖 SQL語(yǔ)言最主要的功能是數(shù)據(jù)庫(kù)查詢，它是數(shù)據(jù)庫(kù)其他操作（如統(tǒng)計(jì)、插入、修改、刪除）的基礎(chǔ)，也是DBMS的核心功能之一。使用SQL語(yǔ)句將要連接的表、查詢所需的字段、篩選記錄的條件、記錄分組的依據(jù)、排序的方式以及查詢結(jié)果的顯示方式，寫在一條SQL語(yǔ)句中，就可以從表或視圖中迅速、方便地檢索數(shù)據(jù)，具有十分靈活的使用方式和豐富的功能。4.1SELECT語(yǔ)句概述

SELECT

select_list[INTO

new_table]FROM

table_source

[WHERE

search_condition][GROUP

BY

group_by_expression][HAVING

search_condition][ORDER

BY

order_expression[ASC|DESC]]

<select_list>::={*|{table_name|view_name|table_alias}.* |{column_name|expression}[[AS]column_alias] |column_alias=expression}[,...n]

將查詢結(jié)果存入新表new_table中。指定查詢條件。指定將查詢結(jié)果進(jìn)行分組。

與GROUPBY聯(lián)合使用，指定分組條件。

指定查詢結(jié)果按order_expression進(jìn)行排序。

指定在FROM子句內(nèi)返回表或視圖的所有列。指定查詢的列名。指定列名、常量、函數(shù)以及由運(yùn)算符連接的列名、常量和函數(shù)的任意組合，或者是子查詢。

指定查詢結(jié)果集內(nèi)列名的別名。

整個(gè)SELECT語(yǔ)句的含義是：根據(jù)WHERE子句的條件表達(dá)式，從FROM子句指定的基本表或視圖中找出滿足條件的記錄，再按照SELECT子句中的select_list，選出記錄中的列值、或計(jì)算值、或匯總值形成的查詢結(jié)果。

SELECT語(yǔ)句既可以完成簡(jiǎn)單的單表查詢，也可以完成復(fù)雜的多表查詢和嵌套查詢。4.2單表查詢——選擇表中若干列如果用戶只對(duì)表中部分列感興趣，查詢時(shí)可不使用WHERE子句，選擇表中的全部列或部分列，也稱作投影查詢?；菊Z(yǔ)法為：SELECT[ALL|DISTINCT][TOPn[PERCENT]]<select_list>FROMtable_source

參數(shù)說(shuō)明：ALL：默認(rèn)設(shè)置，指定在結(jié)果集中可以顯示重復(fù)行。DISTINCT：指定在結(jié)果集中去除重復(fù)行，只顯示唯一行。TOPn[PERCENT]：指定只從查詢結(jié)果集中輸出前n行或前百分之n行?！纠?.1】查詢?nèi)w學(xué)生的學(xué)號(hào)、姓名和年齡。SELECTSNO,SNAME,SAGEFROMXS查詢指定列4.2單表查詢——選擇表中若干列【例4.2】查詢?nèi)w學(xué)生的情況。SELECTSNO,SNAME,SDEPT,SSEX,SAGE,SHEIGHT,SCPM,SRMKFROMXS或SELECT*FROMXS

查詢?nèi)苛?.2單表查詢——選擇表中若干列【例4.3】查詢?nèi)w學(xué)生的姓名及出生年份。（假設(shè)當(dāng)前年份為2009年）。SELECTSNAME,2009-SAGEFROMXS

查詢結(jié)果中包含計(jì)算項(xiàng)4.2單表查詢——選擇表中若干列【例4.4】查詢?nèi)w學(xué)生的姓名，并將姓和名分別顯示。SELECTSNAME,SUBSTRING(SNAME,1,1),SUBSTRING(SNAME,2,2)FROMXS查詢結(jié)果中包含計(jì)算項(xiàng)4.2單表查詢——選擇表中若干列【例4.5】對(duì)例4.4中的查詢結(jié)果指定列標(biāo)題。查詢結(jié)果中的列標(biāo)題顯示SELECTSNAMEAS學(xué)生姓名,SUBSTRING(SNAME,1,1)AS姓,SUBSTRING(SNAME,2,2)AS名FROMXS或SELECT學(xué)生姓名=SNAME,姓=SUBSTRING(SNAME,1,1),名=SUBSTRING(SNAME,2,2)FROMXS4.2單表查詢——選擇表中若干列【例4.6】對(duì)比以下兩條查詢語(yǔ)句的執(zhí)行結(jié)果，理解DISTINCT的作用。查詢結(jié)果中消除重復(fù)行①SELECTSDEPT,SSEXFROMXS②SELECTDISTINCTSDEPT,SSEXFROMXS

4.2單表查詢——選擇表中若干列【例4.7】查詢XS表中前6個(gè)學(xué)生的情況。限制結(jié)果集的返回行數(shù)①SELECTTOP6*FROMXS

②SELECTTOP60PERCENT*FROMXS

4.2單表查詢——選擇表中若干列【例4.8】查詢xs_kc表中學(xué)生成績(jī)，對(duì)其成績(jī)按以下規(guī)則進(jìn)行替換：成績(jī)90~100為“優(yōu)秀”，80~89為“良好”，70~79為“中等”，60~69為“及格”，<60為“不及格”。

替換顯示結(jié)果的內(nèi)容SELECT學(xué)號(hào)=SNO,課程號(hào)=CNO,成績(jī)=SCORE,等級(jí)=CASE

WHENSCORE<60THEN'不及格'

WHENSCORE>=60ANDSCORE<=69THEN'及格'

WHENSCORE>=70ANDSCORE<=79THEN'中等'

WHENSCORE>=80ANDSCORE<=89THEN'良好'

ELSE'優(yōu)秀'ENDFROMXS_KC

4.2單表查詢——選擇表中若干列查詢時(shí)使用WHERE子句，選擇表中滿足條件的若干行，也稱作選擇查詢?；菊Z(yǔ)法為：SELECTselect_listFROMtable_sourceWHEREsearch_condition

查詢條件運(yùn)算符比較=、>、<、>=、<=、<>或!=等；確定范圍BETWEENAND,NOTBETWEENAND確定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL多重條件AND,OR4.2單表查詢——選擇表中若干行【例4.9】查詢選修課程號(hào)為‘C01’的學(xué)生的學(xué)號(hào)和成績(jī)。SELECTSNO,SCOREFROMXS_KCWHERECNO='C01'

比較運(yùn)算符【例4.10】查詢成績(jī)高于85分的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)。

SELECTSNO,CNO,SCOREFROMXS_KCWHERESCORE>854.2單表查詢——選擇表中若干行【例4.11】查詢年齡在20~23歲（含20歲和23歲）之間的學(xué)生姓名、系別。SELECTSNAME,SDEPTFROMXSWHERESAGEBETWEEN20AND23確定范圍4.2單表查詢——選擇表中若干行【例4.12】查詢選修C01或C02的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)。SELECTSNO,CNO,SCOREFROMXS_KCWHERECNOIN('C01','C02')確定集合4.2單表查詢——選擇表中若干行【例4.13】查詢所有姓張的學(xué)生的學(xué)號(hào)和姓名。SELECTSNO,SNAMEFROMXSWHERESNAMELIKE'張%'字符匹配【例4.14】查詢姓名中第二個(gè)漢字是“一”的學(xué)生的學(xué)號(hào)和姓名。SELECTSNO,SNAMEFROMXSWHERESNAMELIKE'_一%'4.2單表查詢——選擇表中若干行【例4.15】查詢沒有備注信息的學(xué)生情況。SELECT*FROMXSWHERESRMKISNULL涉及空值4.2單表查詢——選擇表中若干行【例4.16】查詢電力專業(yè)所有男生的情況。SELECT*FROMXSWHERESDEPT='電力'ANDSSEX='男'多重條件【例4.17】查詢選修C01或C02且分?jǐn)?shù)大于85分的學(xué)生的學(xué)號(hào)及成績(jī)。SELECTSNO,SCOREFROMXS_KCWHERE(CNO='C01'ORCNO='C02')ANDSCORE>854.2單表查詢——選擇表中若干行4.2單表查詢——聚集函數(shù)

計(jì)算諸如平均值和總和的函數(shù)被稱為聚集函數(shù)。使用聚集函數(shù)時(shí)，系統(tǒng)對(duì)整個(gè)表或表的某個(gè)組中的列進(jìn)行匯總、計(jì)算，然后為它創(chuàng)建相應(yīng)字段的單個(gè)的值。在SELECT語(yǔ)句中可以單獨(dú)使用聚集函數(shù)，也可以與語(yǔ)句GROUPBY聯(lián)合使用，列的數(shù)據(jù)類型決定了在該列上可以使用的聚集函數(shù)類型。

函數(shù)作用數(shù)據(jù)類型COUNT統(tǒng)計(jì)列中元組的個(gè)數(shù)任意類型MIN求一列值中的最小值除bit以外的數(shù)據(jù)類型MAX求一列值中的最大值SUM計(jì)算一列值的總和只能用于數(shù)值型字段，如int、decimal、float、money等AVG計(jì)算一列值的平均值【例4.18】查詢電力系學(xué)生的總?cè)藬?shù)。SELECTCOUNT(*)FROMXSWHERESDEPT='電力'COUNT函數(shù)【例4.19】查詢選修了課程的學(xué)生人數(shù)。SELECTCOUNT(DISTINCTSNO)FROMXS_KC4.2單表查詢——聚集函數(shù)

【例4.20】查詢課程號(hào)為‘C01’的學(xué)生成績(jī)的最高分和最低分。SELECTMAX(SCORE)最高分,MIN(SCORE)最低分FROMXS_KCWHERECNO='C01'MAX函數(shù)和MIN函數(shù)【例4.21】查詢課程號(hào)為‘C01’的學(xué)生成績(jī)的總分和平均分。

SELECTSUM(SCORE)總分,AVG(SCORE)平均分FROMXS_KCWHERECNO='C01'SUM函數(shù)和AVG函數(shù)4.2單表查詢——聚集函數(shù)

4.2單表查詢——查詢結(jié)果分組

在SELECT語(yǔ)句中使用GROUPBY子句可以將查詢結(jié)果分組，并返回行的匯總信息，即對(duì)于GROUPBY子句中定義的每個(gè)組，各返回一個(gè)結(jié)果。此時(shí)，SELECT子句中的列名必須為分組列或列函數(shù)。基本語(yǔ)法為：

[GROUPBY[ALL]group_by_expression[,...n][WITH{CUBE|ROLLUP}]]其中，WITHCUBE或WITHROLLUP用于在查詢結(jié)果中附加匯總結(jié)果。

【例4.22】查詢各系學(xué)生人數(shù)。SELECTSDEPT系別,COUNT(*)人數(shù)FROMXSGROUPBYSDEPTGROUPBY基本用法4.2單表查詢——查詢結(jié)果分組

【例4.23】查詢各門課程的平均成績(jī)和選修人數(shù)。SELECTCNO課程號(hào),AVG(SCORE)平均成績(jī),COUNT(*)選修人數(shù)FROMXS_KCGROUPBYCNOGROUPBY基本用法4.2單表查詢——查詢結(jié)果分組

【例4.24】查詢各系男生人數(shù)、女生人數(shù)。SELECTSDEPT系別,SSEX性別,COUNT(*)人數(shù)FROMXSGROUPBYSDEPT,SSEXGROUPBY基本用法4.2單表查詢——查詢結(jié)果分組

【例4.25】查詢各系男生人數(shù)、女生人數(shù)、各系人數(shù)和總?cè)藬?shù)。SELECTSDEPT系別,SSEX性別,COUNT(*)人數(shù)FROMXSGROUPBYSDEPT,SSEXWITHROLLUP使用ROLLUP和CUBE子句4.2單表查詢——查詢結(jié)果分組

【例4.26】除例4.25的查詢要求外，還需匯總男生總數(shù)和女生總數(shù)。SELECTSDEPT系別,SSEX性別,COUNT(*)人數(shù)FROMXSGROUPBYSDEPT,SSEXWITHCUBE使用ROLLUP和CUBE子句4.2單表查詢——查詢結(jié)果分組

【例4.27】查詢平均成績(jī)80分以上的學(xué)生學(xué)號(hào)和平均成績(jī)。

SELECTSNO,AVG(SCORE)AS平均成績(jī)FROMXS_KCGROUPBYSNOHAVINGAVG(SCORE)>=80使用HAVING子句4.2單表查詢——查詢結(jié)果分組

【例4.28】查詢?nèi)w學(xué)生的情況，查詢結(jié)果按所在系的系號(hào)升序排列，同系學(xué)生按年齡降序排列。SELECT*FROMXSORDERBYSDEPT,SAGEDESC【例4.29】查詢學(xué)生的學(xué)號(hào)和平均分，且按平均分降序排列。

SELECTSNO,AVG(SCORE)AS平均成績(jī)FROMXS_KCGROUPBYSNOORDERBY平均成績(jī)

DESC4.2單表查詢——查詢結(jié)果分組

【例4.30】查詢電力系學(xué)生的學(xué)號(hào)、姓名、年齡，并產(chǎn)生一個(gè)學(xué)生總?cè)藬?shù)行。

SELECTSNO,SNAME,SAGEFROMXSWHERESDEPT='電力'COMPUTECOUNT(SNO)4.2單表查詢——查詢結(jié)果分組

【例4.31】將學(xué)生按專業(yè)名排序，并匯總各專業(yè)人數(shù)和平均身高。SELECTSDEPT,SNO,SNAME,SAGE,SHEIGHTFROMXSORDERBYSDEPTCOMPUTECOUNT(SNO),AVG(SHEIGHT)BYSDEPT4.2單表查詢——查詢結(jié)果分組

4.2單表查詢——查詢結(jié)果生成新表

【例4.32】由XS表創(chuàng)建“電力系學(xué)生”表，包括學(xué)號(hào)、姓名、年齡、身高。

SELECTSNO,SNAME,SAGE,SHEIGHTINTO電力系學(xué)生FROMXSWHERESDEPT='電力'4.2單表查詢——聯(lián)合查詢

【例4.33】將XS表中數(shù)學(xué)系學(xué)生和“電力系學(xué)生”表數(shù)據(jù)合并。SELECTSNO,SNAME,SAGE,SHEIGHTFROMXSWHERESDEPT='數(shù)學(xué)'UNIONSELECT*FROM電力系學(xué)生

4.3多表查詢實(shí)際應(yīng)用中，數(shù)據(jù)往往需要同時(shí)在相關(guān)聯(lián)的多個(gè)表中得到。例如，需要查詢電力系所有學(xué)生的成績(jī)。多表查詢是指同時(shí)涉及兩個(gè)以上的表的查詢，又稱為連接查詢。連接查詢是關(guān)系數(shù)據(jù)庫(kù)中最主要的查詢。包括內(nèi)連接、外連接和自連接。

內(nèi)連接連接查詢中用來(lái)連接多個(gè)表的條件稱為連接條件，連接的表名之間用逗號(hào)隔開。內(nèi)聯(lián)接使用比較運(yùn)算符根據(jù)每個(gè)表共有的列的值匹配兩個(gè)表中的行。其一般格式為：[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>也可使用[INNER]JOINON子句實(shí)現(xiàn)，其一般格式為：FROM<表名1>[INNER]JOIN<表名2>[ON<條件表達(dá)式>]內(nèi)連接【例4.33】查詢每個(gè)學(xué)生的情況及選修課程的情況。SELECTXS.*,XS_KC.*FROMXS,XS_KCWHEREXS.SNO=XS_KC.SNOSELECTXS.*,XS_KC.*FROMXSJOINXS_KCONXS.SNO=XS_KC.SNO4.3多表查詢內(nèi)連接【例4.34】查詢選修了“電磁場(chǎng)”課程且成績(jī)?cè)?5分以上的學(xué)生學(xué)號(hào)、姓名、課程名和成績(jī)。SELECTXS.SNO,SNAME,CNAME,SCOREFROMXS,KC,XS_KCWHEREXS.SNO=XS_KC.SNOANDKC.CNO=XS_KC.CNOANDCNAME='電磁場(chǎng)'ANDSCORE>=85SELECTXS.SNO,SNAME,CNAME,SCOREFROMXSJOINXS_KCONXS.SNO=XS_KC.SNOJOINKCONKC.CNO=XS_KC.CNOWHERECNAME='電磁場(chǎng)'ANDSCORE>=854.3多表查詢外連接外連接返回FROM子句中提到的至少一個(gè)表或視圖的所有行，包括左外連接、右外連接和全外連接。其一般格式為：FROM<表名1>LEFT|RIGHT|FULL[OUTER]JOIN<表名2>[ON<條件表達(dá)式>]左外連接的結(jié)果集包括LEFTOUTER子句中指定的左表的所有行。如果左表的某行在右表中沒有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為NULL。右外連接是左外連接的反向連接，返回右表的所有行，如果右表的某行在左表中沒有匹配行，則將為左表返回NULL。全外連接是左外連接和右外連接的并集。

4.3多表查詢外連接【例4.37】查詢課程被選修的情況，要求包含學(xué)號(hào)、課程號(hào)、課程名，同時(shí)還需顯示沒人選修的課程。SELECTXS_KC.SNO,KC.CNO,CNAMEFROMXS_KCRIGHTJOINKCONKC.CNO=XS_KC.CNO4.3多表查詢自連接將一個(gè)表與它自身進(jìn)行連接，稱為自連接。使用自連接時(shí)需為表指定兩個(gè)別名，且對(duì)列的引用都要用別名限定。

【例4.38】查詢與“趙一”同一專業(yè)的學(xué)生姓名。SELECTDISTINCTA.SNAMEFROMXSAJOINXSBONA.SDEPT=B.SDEPTANDB.SNAME='趙一'4.3多表查詢?cè)赟QL語(yǔ)言中，一個(gè)SELECT-FROM-WHERE語(yǔ)句稱為一個(gè)查詢塊。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING子句的條件中的查詢稱為嵌套查詢。外層查詢稱為父查詢（或主查詢），內(nèi)層查詢稱為子查詢（或從查詢），先處理子查詢，再處理父查詢。子查詢可以嵌套多層，子查詢的結(jié)果集又成為父查詢的條件。4.4嵌套查詢4.4嵌套查詢簡(jiǎn)單嵌套查詢【例4.39】查詢課程號(hào)為‘C01’且成績(jī)高于學(xué)號(hào)為‘010’的所有學(xué)生的學(xué)號(hào)和成績(jī)。SELECTSNO,SCOREFROMXS_KCWHERECNO='C01'ANDSCORE> (SELECTSCOREFROMXS_KC WHERECNO='C01'ANDSNO='010')【例4.40】查詢課程號(hào)為‘C01’且成績(jī)?cè)谄骄忠陨系膶W(xué)生的學(xué)號(hào)和姓名。SELECTXS.SNO,SNAMEFROMXS,XS_KCWHEREXS.SNO=XS_KC.SNOANDCNO='C01'ANDSCORE>(SELECTAVG(SCORE)FROMXS_KC WHERECNO='C01')4.4嵌套查詢帶IN謂詞的嵌套查詢【例4.41】查詢選修了課程的學(xué)生情況。SELECT*FROMXSWHERESNOIN(SELECTDISTINCTSNOFROMXS_KC)【例4.42】查詢所有課程中不及格的學(xué)生的學(xué)號(hào)、姓名和所在院系。SELECTSNO,SNAME,SDEPTFROMXSWHERESNOIN(SELECTDISTINCTSNOFROMXS_KC WHERESCORE<60)4.5視圖視圖是從一個(gè)或多個(gè)表或視圖中導(dǎo)出的表，是一個(gè)“虛”表，其結(jié)構(gòu)和數(shù)據(jù)建立在對(duì)基本表的查詢基礎(chǔ)之上。和基本表一樣，視圖也包括多個(gè)被定義的列和多個(gè)數(shù)據(jù)行，但視圖中的數(shù)據(jù)并不以視圖結(jié)構(gòu)存儲(chǔ)在數(shù)據(jù)庫(kù)中，而是存儲(chǔ)在視圖所引用的表中，并且在引用視圖時(shí)動(dòng)態(tài)生成。對(duì)視圖的操作與對(duì)表的操作一樣，可以對(duì)其進(jìn)行查詢、修改(有一定的限制)、刪除。對(duì)視圖中的數(shù)據(jù)進(jìn)行修改時(shí)，相應(yīng)的基本表的數(shù)據(jù)也會(huì)發(fā)生變化，同時(shí)，若基本表的數(shù)據(jù)發(fā)生變化，則這種變化也可以自動(dòng)地反映到視圖中。4.5視圖4.5使用T-SQL管理視圖創(chuàng)建視圖，語(yǔ)法格式如下：CREATEVIEWview_name

ASselect_statement

注意：在SELECT語(yǔ)句中，不能使用COMPUTE、COMPUTEBY等語(yǔ)句，不能使用INTO關(guān)鍵字，不能使用臨時(shí)表。

【例4.47】使用T-SQL語(yǔ)句創(chuàng)建視圖。CREATEVIEWVIEW2ASSELECTXS.SNO,XS.SNAME,XS.SDEPT,XS_KC.CNO,XS_KC.SCOREFROMXS,XS_KCWHEREXS.SNO=XS_KC.SNOANDXS.SDEPT='電力'ANDXS_KC.SCORE>=804.5使用T-SQL管理視圖修改視圖，語(yǔ)法格式如下：ALTERVIEW

view_nameASselect_statement刪除視圖，語(yǔ)法格式如下：

DROPVIEW

view_