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

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

4.1SELECT語句概述

4.2單表查詢

4.3多表查詢

4.4嵌套查詢

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

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é)果進行分組。

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

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

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

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

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

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

參數(shù)說明：ALL：默認(rèn)設(shè)置，指定在結(jié)果集中可以顯示重復(fù)行。DISTINCT：指定在結(jié)果集中去除重復(fù)行，只顯示唯一行。TOPn[PERCENT]：指定只從查詢結(jié)果集中輸出前n行或前百分之n行?！纠?.1】查詢?nèi)w學(xué)生的學(xué)號、姓名和年齡。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é)果中包含計算項4.2單表查詢——選擇表中若干列【例4.4】查詢?nèi)w學(xué)生的姓名，并將姓和名分別顯示。SELECTSNAME,SUBSTRING(SNAME,1,1),SUBSTRING(SNAME,2,2)FROMXS查詢結(jié)果中包含計算項4.2單表查詢——選擇表中若干列【例4.5】對例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】對比以下兩條查詢語句的執(zhí)行結(jié)果，理解DISTINCT的作用。查詢結(jié)果中消除重復(fù)行①SELECTSDEPT,SSEXFROMXS②SELECTDISTINCTSDEPT,SSEXFROMXS

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

②SELECTTOP60PERCENT*FROMXS

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

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

WHENSCORE<60THEN'不及格'

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

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

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

ELSE'優(yōu)秀'ENDFROMXS_KC

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

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

比較運算符【例4.10】查詢成績高于85分的學(xué)生的學(xué)號、課程號和成績。

SELECTSNO,CNO,SCOREFROMXS_KCWHERESCORE>854.2單表查詢——選擇表中若干行【例4.11】查詢年齡在20~23歲（含20歲和23歲）之間的學(xué)生姓名、系別。SELECTSNAME,SDEPTFROMXSWHERESAGEBETWEEN20AND23確定范圍4.2單表查詢——選擇表中若干行【例4.12】查詢選修C01或C02的學(xué)生的學(xué)號、課程號和成績。SELECTSNO,CNO,SCOREFROMXS_KCWHERECNOIN('C01','C02')確定集合4.2單表查詢——選擇表中若干行【例4.13】查詢所有姓張的學(xué)生的學(xué)號和姓名。SELECTSNO,SNAMEFROMXSWHERESNAMELIKE'張%'字符匹配【例4.14】查詢姓名中第二個漢字是“一”的學(xué)生的學(xué)號和姓名。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é)號及成績。SELECTSNO,SCOREFROMXS_KCWHERE(CNO='C01'ORCNO='C02')ANDSCORE>854.2單表查詢——選擇表中若干行4.2單表查詢——聚集函數(shù)

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

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

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

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

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

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

[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】查詢各門課程的平均成績和選修人數(shù)。SELECTCNO課程號,AVG(SCORE)平均成績,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】查詢平均成績80分以上的學(xué)生學(xué)號和平均成績。

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

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

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

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

【例4.30】查詢電力系學(xué)生的學(xué)號、姓名、年齡，并產(chǎn)生一個學(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é)號、姓名、年齡、身高。

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

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

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

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

ASselect_statement

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

【例4.47】使用T-SQL語句創(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管理視圖修改視圖，語法格式如下：ALTERVIEW

view_nameASselect_statement刪除視圖，語法格式如下：

DROPVIEW

view_