SQLServer2008數(shù)據(jù)庫應(yīng)用教程

第5章數(shù)據(jù)操作與數(shù)據(jù)查詢1234掌握數(shù)據(jù)操作的方法

熟練掌握各種數(shù)據(jù)查詢的方法

學(xué)習(xí)目標(biāo)理解數(shù)據(jù)操作與數(shù)據(jù)查詢的含義

熟練掌握使用T-SQL語句操作數(shù)據(jù)和查詢數(shù)據(jù)主要內(nèi)容5.1數(shù)據(jù)的基本操作5.2數(shù)據(jù)的簡單查詢

5.3數(shù)據(jù)的高級(jí)查詢

5.1數(shù)據(jù)的基本操作數(shù)據(jù)庫用來存儲(chǔ)和管理數(shù)據(jù)。新表創(chuàng)建完成后，表中不包含任何記錄，要實(shí)現(xiàn)數(shù)據(jù)的存儲(chǔ)必須向表中添加數(shù)據(jù)。同樣，在日常的數(shù)據(jù)管理過程中，還經(jīng)常需要用到修改、刪除等操作。對(duì)數(shù)據(jù)的各種操作，既可以通過圖形界面的方式來完成，也可以通過執(zhí)行T-SQL語句實(shí)現(xiàn)。相對(duì)而言，T-SQL語句功能更強(qiáng)大，對(duì)數(shù)據(jù)的操作更靈活。5.1.1添加記錄1.使用圖形界面方式向表中添加記錄

啟動(dòng)【MicrosoftSQLServerManagementStudio】，在【對(duì)象資源管理器】窗口中，依次展開【數(shù)據(jù)庫】→【學(xué)生成績管理系統(tǒng)】→【表】→【學(xué)生】節(jié)點(diǎn)，在【學(xué)生】節(jié)點(diǎn)上右鍵單擊，在彈出的快捷菜單中選擇【編輯前200行】菜單項(xiàng)。如圖5.1所示。5.1.1添加記錄圖5.1學(xué)生表添加記錄2.使用INSERT語句向表中添加記錄

5.1.1添加記錄INSERTINTO語句用于向表中插入新的行。它有三種基本的語法格式:格式一：INSERTINTOtable_nameVALUES(值1,值2,....)，（值1,值2,....）說明：INTO可以省略，數(shù)據(jù)列表中的數(shù)據(jù)個(gè)數(shù)和數(shù)據(jù)類型必須和數(shù)據(jù)表中的字段數(shù)目與類型一致，字符型和日期型數(shù)據(jù)在輸入時(shí)用單引號(hào)，數(shù)值類數(shù)據(jù)直接輸入。每一條記錄用一對(duì)括號(hào)括起來，多個(gè)記錄之間用逗號(hào)隔開。允許為空的列也要輸入null填充，不允許省略。5.1.1添加記錄【例5.1】向?qū)W生表添加多條記錄。USE學(xué)生成績管理系統(tǒng)GOINSERTINTO學(xué)生VALUES(‘1101010131',‘馬爽',‘女',‘1992-09-16',‘團(tuán)員',‘11010101',‘2011',null),(‘1101010132',‘牛玉環(huán)',‘女',‘1991-08-26',‘黨員',‘11010101',‘2011',null)我們也可以指定所要插入數(shù)據(jù)的列，對(duì)于允許為空的列可以暫時(shí)不插入值。5.1.1添加記錄格式二：INSERTINTOtable_name(列1,列2,...)VALUES(值1,值2,....)說明：列1，列2為要添加數(shù)據(jù)的字段名，各個(gè)字段名稱用逗號(hào)分隔。值1、值2與列1、列2按順序相對(duì)應(yīng)，即值1將添加到列1字段，值2添加到列2字段，其它依次類推。5.1.1添加記錄【例5.2】向?qū)W生表添加一條記錄，只向?qū)W號(hào)，姓名，性別，出生日期，政治面貌列添加記錄。USE學(xué)生成績管理系統(tǒng)GOINSERTINTO學(xué)生（學(xué)號(hào)，姓名，性別，出生日期，政治面貌）VALUES(‘1101010131',‘馬爽',‘女',‘1992-09-16',‘團(tuán)員’）對(duì)于字段列表中沒有涉及的列，按照默認(rèn)值或空值填充。5.1.1添加記錄格式三：INSERTINTOtable_nameSELECT語句格式三的功能是將SELECT語句的查詢結(jié)果添加到INSERTINTO后指定的表中，如：INSERTINTO學(xué)生2SELECT學(xué)號(hào)，姓名FROM學(xué)生。該語句的功能是將學(xué)生表中的學(xué)號(hào)姓名兩列的數(shù)據(jù)插入到學(xué)生2表中。在執(zhí)行INSERT語句時(shí)，如果插入的數(shù)據(jù)與約束或規(guī)則的要求產(chǎn)生沖突或值的數(shù)據(jù)類型與列的數(shù)據(jù)類型不匹配，那么INSERT語句將執(zhí)行失敗。注意：使用INSERT語句添加記錄時(shí)，不能為計(jì)算字段、標(biāo)識(shí)字段指定數(shù)據(jù)，這些字段的值由SQLServer自動(dòng)產(chǎn)生。5.1.2修改記錄啟動(dòng)【MicrosoftSQLServerManagementStudio】，在【對(duì)象資源管理器】窗口中，依次展開【數(shù)據(jù)庫】→【學(xué)生成績管理系統(tǒng)】→【表】→【學(xué)生】節(jié)點(diǎn)，在【學(xué)生】節(jié)點(diǎn)上右鍵單擊，在彈出的快捷菜單中選擇【編輯前200行】菜單項(xiàng)，打開表視圖窗口，選中要更新的記錄，輸入新的記錄值即可。操作界面如圖5.1所示。1.使用圖形界面方式修改表中記錄

5.1.2修改記錄UPDATE語句用來修改表中的數(shù)據(jù)，每個(gè)UPDATE語句可以修改一行或多行數(shù)據(jù)，但每次僅能對(duì)一個(gè)表進(jìn)行操作。UPDATE語句的基本語法格式如下：UPDATEtable_nameSETcolumn_name=expression[FROMtable_source][WHEREsearch_condition]2.使用UPDATE語句對(duì)表中的記錄進(jìn)行更新5.1.2修改記錄格式說明：table_name為將要被更新的表名。column_name為指定要修改數(shù)據(jù)的字段名。expression指定字段的新值，可以是一個(gè)常數(shù)、表達(dá)式或變量。FROMtable_source為可選項(xiàng)，用來從其他表中取數(shù)據(jù)來修改某表中的數(shù)據(jù)。WHEREsearch_condition指定搜索條件，只有滿足條件的記錄才會(huì)被修改。如果省略WHERE選項(xiàng)，則修改整個(gè)表中的記錄。5.1.2修改記錄【例5.3】將課程表中課程編號(hào)為“101006”的課程名稱修改為“C程序設(shè)計(jì)基礎(chǔ)”。USE學(xué)生成績管理系統(tǒng)GOUPDATE課程SET課程名=‘C程序設(shè)計(jì)基礎(chǔ)’WHERE課程編號(hào)=‘101006’5.1.2修改記錄【例5.4】將課程表中所有專業(yè)課的學(xué)時(shí)上調(diào)10%。USE學(xué)生成績管理系統(tǒng)GOUPDATE課程SET學(xué)時(shí)=學(xué)時(shí)+學(xué)時(shí)*0.1WHERE課程性質(zhì)=‘專業(yè)課'5.1.3

刪除記錄啟動(dòng)【MicrosoftSQLServerManagementStudio】，在【對(duì)象資源管理器】窗口中，依次展開【數(shù)據(jù)庫】→【學(xué)生成績管理系統(tǒng)】→【表】→【學(xué)生】節(jié)點(diǎn)，在【學(xué)生】節(jié)點(diǎn)上右鍵單擊，在彈出的快捷菜單中選擇【編輯前200行】菜單項(xiàng)，打開表視圖窗口，單擊最左邊的空白區(qū)域，選中要?jiǎng)h除的記錄，右擊選擇【刪除】即可刪除該記錄。如圖5.2所示。1.使用圖形界面方式刪除表中記錄

5.1.3

刪除記錄圖5.2刪除記錄界面5.1.3

刪除記錄基本的語法格式如下：DELETE【FROM】table_nameWHEREsearch_condition FROM可以省略 table_name為將要被刪除記錄的表名。 WHEREsearch_condition指定搜索條件，只有滿足條件的記錄才會(huì)被刪除。如果省略WHERE選項(xiàng)，則刪除整個(gè)表中的記錄。2.使用DELETE語句刪除表中記錄5.1.3

刪除記錄【例5.6】將學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫中課程表中學(xué)時(shí)小于50的課程刪除。USE學(xué)生成績管理系統(tǒng)GODELETEFROM課程WHERE學(xué)時(shí)<50【例5.7】創(chuàng)建班級(jí)副表，并將班級(jí)副表的內(nèi)容清空。INSERTINTO班級(jí)副表SELECT*FROM班級(jí)DELETEFROM班級(jí)副表5.1.3

刪除記錄語法格式：TRUNCATETABLEtable_name該語句將刪除指定表中的所有數(shù)據(jù)，因此也稱為清空表。由TRUNCATETABLE語句刪除的數(shù)據(jù)將無法恢復(fù)，因此使用時(shí)要十分當(dāng)心。TRUNCATETABLE語句在功能上與不帶WHERE子句的DELETE語句相同，二者均刪除表中的全部行（表的結(jié)構(gòu)及其列、約束、索引等保持不變）；但TRUNCATETABLE比DELETE速度快，且使用的系統(tǒng)和事務(wù)日志資源少。對(duì)于有外鍵（FOREIGNKEY）約束引用的表，不能使用TRUNCATETABLE刪除數(shù)據(jù)，應(yīng)使用不帶WHERE子句的DELETE語句，另外，TRUNCATETABLE語句也不能用于參與了索引視圖的表。3.使用TRUNCATETABLE語句刪除表中數(shù)據(jù)5.2數(shù)據(jù)的簡單查詢使用數(shù)據(jù)庫和表的主要目的是：存儲(chǔ)數(shù)據(jù)以便在需要時(shí)進(jìn)行檢索、統(tǒng)計(jì)或組織輸出，通過T-SQL語句中的SELECT查詢語句即可以從表或視圖中方便快捷的檢索數(shù)據(jù)。5.2.1SELECT語句

SELECT語句用來從數(shù)據(jù)庫中檢索滿足特定條件的記錄。一個(gè)SELECT語句主要包含如下幾部分： 從哪個(gè)或哪些表或視圖中提取數(shù)據(jù) 選取表中的哪些列 選擇滿足什么條件的數(shù)據(jù) 查詢結(jié)果以什么順序顯示5.2.1SELECT語句SELECT語句是T-SQL的核心。語法格式如下：SELECT[ALL|DISTINCT][TOPexpression[PERCENT] <SELECT_list>[INTOnew_table]

/*INTO子句，指定結(jié)果存入新表*/[FROMtable_source]

/*FROM子句，指定表或視圖*/[WHEREsearch_condition]

/*WHERE子句，指定查詢條件*/[GROUPBYgroup_by_expression]

/*GROUPBY子句，指定分組表達(dá)式*/[HAVINGsearch_condition]

/*HAVING子句，指定分組統(tǒng)計(jì)條件*/[ORDERBYorder_expression[ASC|DESC]]/*ORDER子句，指定排序表達(dá)式和順序，ASC為升序，DESC為降序，在默認(rèn)狀態(tài)下為升序*/5.2.1SELECT語句ALL：查詢結(jié)果是數(shù)據(jù)源全部數(shù)據(jù)的記錄集DISTINCT：查詢結(jié)果是不包含重復(fù)行的記錄集TOP：顯示結(jié)果中的前幾條記錄TOPnPERCENT顯示前百分之n條數(shù)據(jù)SELECT_list:字段列表，即指定要查詢的列，各個(gè)字段之間用逗號(hào)分隔。所有被使用的子句必須按照語法說明中顯示的順序嚴(yán)格的排序。如HAVING子句必須位于GROUPBY子句之后，并位于ORDERBY子句之前。5.2.2選擇若干列ALL：查詢結(jié)果是數(shù)據(jù)源全部數(shù)據(jù)的記錄集DISTINCT：查詢結(jié)果是不包含重復(fù)行的記錄集TOP：顯示結(jié)果中的前幾條記錄TOPnPERCENT顯示前百分之n條數(shù)據(jù)SELECT_list:字段列表，即指定要查詢的列，各個(gè)字段之間用逗號(hào)分隔。所有被使用的子句必須按照語法說明中顯示的順序嚴(yán)格的排序。如HAVING子句必須位于GROUPBY子句之后，并位于ORDERBY子句之前。5.2.2選擇若干列

選擇表中的全部列或部分列就是表的投影運(yùn)算。字段列表中的列可以是表中現(xiàn)有的列，也可以是表達(dá)式列，即經(jīng)過多個(gè)列運(yùn)算產(chǎn)生的列。1.選擇所有的列使用“*”表示選擇一個(gè)表或視圖中的所有列?！纠?.8】查詢學(xué)生表中的所有信息。USE學(xué)生成績管理系統(tǒng)GOSELECT*FROM學(xué)生5.2.2選擇若干列2.選擇表中的部分列

使用SELECT語句顯示一個(gè)表中的某些列，可以將要顯示的字段名在SELECT關(guān)鍵字之后依次列出，各列之間用逗號(hào)分隔。字段的順序依據(jù)需要指定?！纠?.9】查詢學(xué)生表中所有學(xué)生的學(xué)號(hào)，姓名信息。USE學(xué)生成績管理系統(tǒng)GOSELECT學(xué)號(hào)，姓名FROM學(xué)生5.2.2選擇若干列3.輸出計(jì)算列

在結(jié)果集中可以存在表中沒有的列，這些列是以現(xiàn)有的列為基礎(chǔ)計(jì)算得到的，稱為計(jì)算列?！纠?.10】查詢每個(gè)學(xué)生的學(xué)號(hào)，姓名，年齡。分析：現(xiàn)有表中不存在年齡列，只有出生日期列，用當(dāng)前日期的年份減去出生日期的年份即為每個(gè)人的年齡。計(jì)算日期年份的函數(shù)為YEAR（），獲取系統(tǒng)當(dāng)前日期的函數(shù)為GETDATE（）。代碼如下：USE學(xué)生成績管理系統(tǒng)GOSELECT學(xué)號(hào)，姓名，YEAR（GETDATE（））-YEAR（出生日期）FROM學(xué)生5.2.2選擇若干列4.為結(jié)果集內(nèi)的列指定別名

從例5.10的結(jié)果可以看出，在默認(rèn)狀態(tài)下查詢結(jié)果中的列名可以是表中列名或無列名。根據(jù)實(shí)際需要，可以為沒有列名的列指定列名，也可對(duì)表中的列名另行指定列名。指定列名的方法有三種： SELECT列名（或表達(dá)式）列別名FROM數(shù)據(jù)源 SELECT列名（或表達(dá)式）AS列別名FROM數(shù)據(jù)源 SELECT列別名=列名（或表達(dá)式）FROM數(shù)據(jù)源5.2.2選擇若干列

【例5.11】查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、年齡信息，為計(jì)算列“年齡”指定列名。USE學(xué)生成績管理系統(tǒng)GOSELECT學(xué)號(hào)，姓名，YEAR（GETDATE（））-YEAR（出生日期）AS年齡FROM學(xué)生執(zhí)行結(jié)果與例5.10結(jié)果相同，區(qū)別在于將圖5.4中的無列名顯示為年齡。5.2.2選擇若干列5.替換查詢結(jié)果中的數(shù)據(jù)對(duì)表進(jìn)行查詢操作時(shí)，有時(shí)候希望對(duì)所查詢的某些列得到的是一種概念而不是具體的數(shù)據(jù)。如查詢成績表時(shí)，希望知道各個(gè)層次的情況，這時(shí)，就可以用等級(jí)來替換具體的數(shù)字。要替換查詢結(jié)果中的數(shù)據(jù)，則要使用查詢中的CASE表達(dá)式，格式為：CASEWHEN條件1THEN表達(dá)式1WHEN條件2THEN表達(dá)式2……ELSE

表達(dá)式END5.2.2選擇若干列【例5.13】查詢成績表中的成績，60分以下的顯示不及格，60至80分之間的顯示及格，80分以上的顯示優(yōu)秀。USE學(xué)生成績管理系統(tǒng)GOSELECT學(xué)號(hào)，課程編號(hào)，等級(jí)=CASEWHEN成績<60THEN‘不及格’WHEN成績>=60AND成績<80THEN‘及格’WHEN成績〉=80THEN‘優(yōu)秀’ENDFROM成績5.2.3選擇若干條記錄

選擇若干條記錄就是關(guān)系運(yùn)算中的選擇運(yùn)算。這種運(yùn)算可以通過使用WHERE子句來實(shí)現(xiàn)。1.消除結(jié)果集中的重復(fù)記錄

當(dāng)執(zhí)行了投影運(yùn)算后，兩個(gè)本來不相同的記錄投影到指定的某些列上后，可能變?yōu)橄嗤男小Ｈ绻サ艚Y(jié)果集中的相同記錄，可以在字段列表前面加上DISTINCT關(guān)鍵字。5.2.3選擇若干條記錄

【例5.14】從成績表中查詢緩考學(xué)生的學(xué)號(hào)。SELECT學(xué)號(hào)FROM成績WHERE性質(zhì)=‘緩考’執(zhí)行結(jié)果如圖5.7所示，緩考學(xué)生的學(xué)號(hào)有重復(fù)。使用DISTINCT關(guān)鍵字可以去除重復(fù)記錄。代碼為：SELECTDISTINCT學(xué)號(hào)FROM成績WHERE性質(zhì)=‘緩考’執(zhí)行結(jié)果如圖5.8所示5.2.3選擇若干條記錄5.7緩考學(xué)生學(xué)號(hào)（有重復(fù)值）圖5.8緩考學(xué)生學(xué)號(hào)（無重復(fù)值）5.2.3選擇若干條記錄2.限制返回的行數(shù)

如果一個(gè)表中有成百上千甚至上萬條記錄，而用戶只需要看一看記錄的樣式和內(nèi)容，這就沒有必要顯示所有記錄。如果要限制返回結(jié)果集的行數(shù)，就需要在字段列表前使用TOPn或TOPnPERCENT關(guān)鍵字，TOPn表示顯示前n條記錄，TOPnPERCENT表示顯示前n%條記錄?！纠?.15】顯示學(xué)生表中的前10條記錄。USE學(xué)生成績管理系統(tǒng)GOSELECTTOP10*FROM學(xué)生5.2.3選擇若干條記錄3.查詢滿足條件的記錄

如果希望得到表中滿足條件的記錄，可以在查詢語句中使用WHERE子句。WHERE子句必須緊跟在FROM子句后面，常用的條件表達(dá)式如表5.1所示。5.2.3選擇若干條記錄表5.1WHERE子句常用的查詢條件查詢條件運(yùn)算符意義比較=，>，<，>=，<=，!=，<>，!>，!<比較大小確定范圍BETWEENAND，NOTBETWEENAND判斷值是否在范圍之內(nèi)確定集合IN，NOTIN判斷值是否在集合中字符匹配LIKE，NOTLIKE判斷值是否與指定的字符通配格式相符空值ISNULL，NOTISNULL判斷是否為空多重條件AND，OR，NOT用于多重條件判斷5.2.3選擇若干條記錄1）比較大小比較運(yùn)算符用于比較兩個(gè)表達(dá)式的值，共有9個(gè)，分別是=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、!=（不等于）、<>（不等于）、!>（不大于）、!<（不小于）?！纠?.17】從學(xué)生表查詢學(xué)生年齡小于21的學(xué)生信息。USE學(xué)生成績管理系統(tǒng)GOSELECT*FROM學(xué)生WHEREYEAR（GETDATE（））-YEAR（出生日期）<215.2.3選擇若干條記錄2）確定范圍范圍運(yùn)算符BETWEEN…AND…和NOTBETWEEN…AND…可以查找字段值在（或不在）指定范圍內(nèi)的記錄。語法格式如下：字段名（列表達(dá)式）BETWEEN起始值A(chǔ)ND終止值【例5.20】從學(xué)生表查詢不是1990年出生的學(xué)生信息。USE學(xué)生成績管理系統(tǒng)GOSELECT*FROM學(xué)生WHERE出生日期NOTBETWEEN‘1990-1-1’AND‘1990-12-31’5.2.3選擇若干條記錄3）確定集合確定集合運(yùn)算符IN和NOTIN用來查找字段或表達(dá)式屬于（不屬于）指定集合的記錄。語法格式如下：字段名（列表達(dá)式）[NOT]IN（值1，值2，值3，…）【例5.22】從班級(jí)表中查找所屬專業(yè)為“計(jì)算機(jī)應(yīng)用”，“網(wǎng)絡(luò)工程”，“電子商務(wù)”專業(yè)的班級(jí)編號(hào)，所屬系部。USE學(xué)生成績管理系統(tǒng)GOSELECT班級(jí)編號(hào)，所屬系部

FROM班級(jí)WHERE所屬專業(yè)IN（‘計(jì)算機(jī)應(yīng)用’，‘網(wǎng)絡(luò)工程’，‘電子商務(wù)’）5.2.3選擇若干條記錄4）字符匹配在實(shí)際應(yīng)用中，有時(shí)候不能給出精確的查詢條件。因此需要根據(jù)不確定的信息來進(jìn)行查詢。運(yùn)算符LIKE用于進(jìn)行字符串的匹配運(yùn)算，實(shí)現(xiàn)模糊查詢。語法格式如下：字段名（列表達(dá)式）[NOT]LIKE‘匹配串’[ESCAPE‘換碼字符’]匹配串可以是一個(gè)完整的字符串，也可以包含通配符，通配符包含如表5.2所示：5.2.3選擇若干條記錄表5.2通配符表通配符說明%代表0個(gè)或多個(gè)字符_（下劃線）代表單個(gè)字符[]指定范圍（如[a-f]、[0-9]）或集合（如[abcdef]）中的任何單個(gè)字符[^]指定不屬于范圍（如[^a-f]、[^0-9]）或集合（如[^abcdef]）的任何單個(gè)字符5.2.3選擇若干條記錄【例5.25】查詢學(xué)生表中學(xué)號(hào)倒數(shù)第3個(gè)數(shù)字為1，且倒數(shù)第1個(gè)數(shù)在1-5之間的學(xué)生的學(xué)號(hào)、姓名及班級(jí)編號(hào)。USE學(xué)生成績管理系統(tǒng)GOSELECT學(xué)號(hào),姓名,班級(jí)編號(hào)FROM學(xué)生WHERE學(xué)號(hào)LIKE‘%1_[1-5]'5.2.3選擇若干條記錄【例5.26】從班級(jí)表中查詢所屬專業(yè)帶有符號(hào)“_”的班級(jí)編號(hào)、所屬專業(yè)、所屬系部。USE學(xué)生成績管理系統(tǒng)GOSELECT班級(jí)編號(hào)，所屬專業(yè)，所屬系部FROM班級(jí)WHERE所屬專業(yè)LIKE‘%a_%’ESCAPE‘a(chǎn)’如果要查詢的字符串本身含有“%”或“_”，需要使用ESCAPE“換碼字符”對(duì)通配符進(jìn)行轉(zhuǎn)義，本例中“a”為轉(zhuǎn)義字符，“a”后面的“_”就失去了它原來的特殊意義。專業(yè)字符可以任選字母或“#”、“/”等特殊字符。轉(zhuǎn)義字符不參與匹配過程。5.2.3選擇若干條記錄5）空值查詢一般情況下，表中的每一列都有存在的意義，但有時(shí)候可能暫時(shí)沒有確定的值，這時(shí)可以不輸入該列的值，那么該列的值為空值（NULL），NULL與0或空格是不一樣的。當(dāng)需要判定一個(gè)表達(dá)式的值是否為空值時(shí)，使用ISNULL關(guān)鍵字。語法格式為：字段名（列表達(dá)式）[NOT]ISNULL【例5.27】查詢學(xué)生表中備注不為空值的學(xué)生信息。USE學(xué)生成績管理系統(tǒng)GOSELECT*FROM學(xué)生WHERE備注ISNOTNULL5.2.3選擇若干條記錄6）多重條件查詢用戶可以通過AND（與）、OR（或）、NOT（非）連接多個(gè)查詢條件，實(shí)現(xiàn)多重查詢。格式為：[NOT]邏輯表達(dá)式AND|OR[NOT]邏輯表達(dá)式【例5.28】查詢學(xué)生表中年齡大于20的男生信息。USE學(xué)生成績管理系統(tǒng)GOSELECT*FROM學(xué)生WHEREYEAR（GETDATE（））-YEAR（出生日期）>20AND性別=‘男’5.2.4

對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)1.使用集合函數(shù)SQLSERVER提供了許多集合函數(shù)，經(jīng)常用到的有：COUNT（DISTINCT|ALL*）統(tǒng)計(jì)記錄個(gè)數(shù)COUNT（DISTINCT|ALL列名）統(tǒng)計(jì)一列中值得個(gè)數(shù)SUM（DISTINCT|ALL列名）計(jì)算一列的總和（此列必須是數(shù)值類數(shù)據(jù)）AVG（DISTINCT|ALL列名）計(jì)算一列的平均值（此列必須是數(shù)值類數(shù)據(jù)）MAX（DISTINCT|ALL列名）求一列的最大值MIN（DISTINCT|ALL列名）求一列的最小值在SELECT子句中集合函數(shù)用來對(duì)結(jié)果集進(jìn)行統(tǒng)計(jì)計(jì)算。DISTINCT是去掉指定列中的重復(fù)值，ALL是不取消重復(fù)，默認(rèn)為ALL。5.2.4

對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)【例5.29】統(tǒng)計(jì)學(xué)生表中學(xué)生人數(shù)。USE學(xué)生成績管理系統(tǒng)GOSELECTCOUNT（*）AS學(xué)生人數(shù)FROM學(xué)生【例5.30】統(tǒng)計(jì)備注不為空的學(xué)生人數(shù)。USE學(xué)生成績管理系統(tǒng)GOSELECTCOUNT（備注）AS‘備注不為空的學(xué)生人數(shù)’FROM學(xué)生注意：COUNT（備注）計(jì)算時(shí)備注為NULL的行被忽略。5.2.4

對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)【例5.31】統(tǒng)計(jì)學(xué)生表中有幾個(gè)不同的班級(jí)。USE學(xué)生成績管理系統(tǒng)GOSELECTCOUNT（DISTINCT班級(jí)編號(hào)）AS班級(jí)個(gè)數(shù)FROM學(xué)生【例5.34】求課程編號(hào)為“101001“的學(xué)生的最高分和最低分。USE學(xué)生成績管理系統(tǒng)GOSELECTMAX（成績）AS‘101001課程的最高分’，MIN（成績）AS‘101001課程的最低分’FROM成績

WHERE課程編號(hào)=‘101001’5.2.4

對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)2.對(duì)結(jié)果進(jìn)行分組統(tǒng)計(jì)GROUPBY子句主要用于根據(jù)字段對(duì)行分組。1）GROUPBY子句GROUPBY子句將查詢結(jié)果按照某一個(gè)字段或多個(gè)字段值進(jìn)行分組，值相同的為一組，對(duì)每一組的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)。語法格式為:GROUPBY列名[HAVING篩選條件表達(dá)式]5.2.4

對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)其中：By列名:是按照列名指定的列進(jìn)行分組，若需要按照多個(gè)列來分組，只需要在列名之間用逗號(hào)分隔即可。HAVING篩選條件表達(dá)式:表示對(duì)生成的組的篩選。SELECT子句的列名必須是GROUPBY子句已有的列名或是計(jì)算列。【例5.35】從學(xué)生表中統(tǒng)計(jì)每個(gè)班級(jí)的人數(shù)。USE學(xué)生成績管理系統(tǒng)GOSELECT班級(jí)編號(hào)，COUNT（學(xué)號(hào)）AS人數(shù)FROM學(xué)生GROUPBY班級(jí)編號(hào)5.2.4

對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)2)非ISO標(biāo)準(zhǔn)的GROUPBY子句語法格式如下：GROUPBY分組字段名WITHCUBE|ROLLUP非ISO標(biāo)準(zhǔn)的GROUPBY子句只是在普通的GROUPBY子句后面增加WITH語句，WITH語句有兩個(gè)操作符CUBE或ROLLUP。CUBE或ROLLUP與聚合函數(shù)一起使用，在查詢結(jié)果中增加附加記錄。使用帶ROLLUP操作符的GROUPBY子句，指定在結(jié)果集中不僅包含由GROUPBY提供的正常行，還包含匯總行。5.2.4

對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)【例5.39】統(tǒng)計(jì)每個(gè)班級(jí)的男生人數(shù)和女生人數(shù)。SELECT班級(jí)編號(hào)，性別，COUNT（*）AS人數(shù)FROM學(xué)生

GROUPBY班級(jí)編號(hào)，性別【例5.40】使用帶ROLLUP操作符，統(tǒng)計(jì)每個(gè)班級(jí)的男生人數(shù)，女生人數(shù)。USE學(xué)生成績管理系統(tǒng)GOSELECT班級(jí)編號(hào)，性別，COUNT（*）AS人數(shù)FROM學(xué)生GROUPBY班級(jí)編號(hào)，性別WITHROLLUP5.2.4

對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)【例5.40】使用帶ROLLUP操作符，統(tǒng)計(jì)每個(gè)班級(jí)的男生人數(shù)，女生人數(shù)。USE學(xué)生成績管理系統(tǒng)GOSELECT班級(jí)編號(hào)，性別，COUNT（*）AS人數(shù)FROM學(xué)生GROUPBY班級(jí)編號(hào)，性別WITHROLLUP使用了ROLLUP后，將對(duì)GROUPBY子句中指定的各列產(chǎn)生匯總行。使用帶CUBE操作符的GROUPBY子句，CUBE操作符對(duì)GROUPBY子句中各列的所有可能組合均產(chǎn)生匯總行，匯總的結(jié)果將更詳細(xì)。5.2.4

對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)3）ISO標(biāo)準(zhǔn)的GROUPBY子句ISO標(biāo)準(zhǔn)的GROUPBY子句與非ISO標(biāo)準(zhǔn)的GROUPBY子句含義相同，使用方式不同。語法格式為：GROUPBYROLLUP（分組字段名）|CUBE（分組字段名）如例5.40可以寫成：SELECT班級(jí)編號(hào)，性別，COUNT（*）AS人數(shù)FROM學(xué)生GROUPBYROLLUP（班級(jí)編號(hào)，性別）5.2.5

對(duì)數(shù)據(jù)進(jìn)行排序在實(shí)際應(yīng)用中，經(jīng)常需要對(duì)查詢的結(jié)果排序輸出，使用ORDERBY子句可對(duì)查詢結(jié)果進(jìn)行排序輸出，語法格式為：ORDERBYorder_by_expression[ASC|DESC][,…n]order_by_expression是排序表達(dá)式，可以是列名或表達(dá)式。關(guān)鍵字ASC為升序、DESC為降序，系統(tǒng)默認(rèn)為升序。【例5.42】將課程表中的課程按照學(xué)時(shí)降序排列。USE學(xué)生成績管理系統(tǒng)GOSELECT*FROM課程ORDERBY學(xué)時(shí)DESC5.2.5

對(duì)數(shù)據(jù)進(jìn)行排序ORDERBY子句可以與COMPUTE子句一起使用，在對(duì)結(jié)果集排序的同時(shí)還產(chǎn)生附加的匯總行，COMPUTE子句對(duì)查詢結(jié)果集中的所有記錄進(jìn)行匯總統(tǒng)計(jì)，并顯示所有參加匯總記錄的詳細(xì)信息。語法格式為：COMPUTE集合函數(shù)BY列名BY列名是指按指定的列名字段進(jìn)行分組計(jì)算，并顯示被統(tǒng)計(jì)記錄的詳細(xì)信息。BY選項(xiàng)必須與ORDERBY子句一起使用。5.2.5

對(duì)數(shù)據(jù)進(jìn)行排序【例5.44】查詢2011級(jí)學(xué)生的學(xué)號(hào)，姓名，出生日期，并產(chǎn)生一個(gè)學(xué)生總?cè)藬?shù)行。USE學(xué)生成績管理系統(tǒng)GOSELECT學(xué)號(hào)，姓名，出生日期FROM學(xué)生WHERE年級(jí)=2011COMPUTECOUNT（學(xué)號(hào)）從結(jié)果可以看出，COMPUTE子句產(chǎn)生附加的匯總行，其標(biāo)題是系統(tǒng)自定義的。對(duì)于COUNT函數(shù)為CNT，對(duì)于AVG函數(shù)為AVG，對(duì)于SUM函數(shù)為SUM等等。COMPUTEBY與GROUPBY子句區(qū)別在于，前者既顯示統(tǒng)計(jì)記錄又顯示詳細(xì)記錄，后者僅顯示分組統(tǒng)計(jì)的匯總記錄。5.2.6

合并結(jié)果集使用UNION語句可以將多個(gè)查詢的結(jié)果合并為一個(gè)結(jié)果集，也就是集合的并運(yùn)算。UNION子句的語法格式如下：SELECT語句UNION【ALL】SELECT語句使用UNION組合兩個(gè)查詢的結(jié)果集的基本規(guī)則是：所有查詢中的列數(shù)和列的順序必須相同，數(shù)據(jù)類型必須兼容。關(guān)鍵字ALL表示合并的結(jié)果中包含所有的行，不去除重復(fù)行，不使用ALL則表示在合并的結(jié)果集中去除重復(fù)行。含有UNION關(guān)鍵字的SELECT查詢也稱為聯(lián)合查詢。5.2.7

其他用法在實(shí)際的數(shù)據(jù)庫應(yīng)用系統(tǒng)中，有時(shí)候需要將查詢的結(jié)果保存成一個(gè)新表。這個(gè)功能可以使用SELECT語句中的INTO子句實(shí)現(xiàn)。INTO子句的格式為：INTO新表名新表的字段由結(jié)果集中的字段列表決定。若SELECT語句的查詢結(jié)果為空，則創(chuàng)建一個(gè)只有結(jié)構(gòu)而沒有記錄的空表?！纠?.48】創(chuàng)建一個(gè)空的學(xué)生表副表SELECT*INTO學(xué)生副表

FROM學(xué)生

WHERE1=2由于WHERE子句中的條件永遠(yuǎn)為“假”，所以查詢結(jié)果為空，即創(chuàng)建的“學(xué)生副表”即為空記錄。1.INTO子句（用結(jié)果集生成新表）5.2.7

其他用法EXCEPT和INTERSECT用于比較兩個(gè)查詢的結(jié)果，EXCEPT從EXCEPT關(guān)鍵字左邊的查詢中返回右邊查詢沒有找到的所有非重復(fù)值。即集合的差運(yùn)算。INTERSECT返回INTERSECT關(guān)鍵字左右兩邊的兩個(gè)查詢都返回的所有非重復(fù)值。即集合的交運(yùn)算。EXCEPT和INTERSECT的語法規(guī)則同UNION類似。語法格式為：SELECT語句EXCEPT|INTERSECTSELECT語句2.EXCEPT和INTERSECT5.2.7

其他用法在SELECT語句的最前面可以使用一條WITH子句來指定臨時(shí)結(jié)果集。臨時(shí)結(jié)果集也稱為公用表值表達(dá)式（COMMONTABLEEXPRESSION，簡稱CTE）。CTE用于存儲(chǔ)一個(gè)臨時(shí)的結(jié)果集，相當(dāng)于一個(gè)臨時(shí)表，只不過它的生命周期在該批處理語句執(zhí)行完后就結(jié)束。CTE下方的SELECT語句可以直接查詢CTE中的數(shù)據(jù)。不允許在一個(gè)CTE中指定多個(gè)WITH子句。3.CTE5.2.7

其他用法【例5.51】使用CTE從成績表中查詢選修了“101001”號(hào)課程的學(xué)生學(xué)號(hào)、成績，并定義新的列名為xuehao，chengji。再使用SELECT語句從CTE和學(xué)生表中查找姓名為“陳立葉”的學(xué)生學(xué)號(hào)和成績信息。USE學(xué)生成績管理系統(tǒng)GOWITHlinshi（xuehao，chengji）AS（SELECT學(xué)號(hào)，成績FROM成績WHERE課程編號(hào)=‘101001’）SELECTxuehao，chengjiFROMlinshi，學(xué)生WHERElinshi.xuehao=學(xué)生.學(xué)號(hào)AND學(xué)生.姓名=‘陳立葉’5.3數(shù)據(jù)的高級(jí)查詢5.3.1子查詢?cè)赥-SQL語言中，一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊，將一個(gè)查詢塊嵌套到另一個(gè)查詢塊的WHERE子句或HAVING子句中的查詢稱為嵌套查詢。其中外層的查詢稱為父查詢，WHERE子句中的查詢稱為子查詢。換句話說，就是將一個(gè)查詢的結(jié)果作為另一個(gè)查詢的條件，子查詢總是用圓括號(hào)括起來SELECT班級(jí)編號(hào)父查詢SELECT學(xué)號(hào)，姓名FROM學(xué)生WHERE班級(jí)編號(hào)IN（子查詢FROM學(xué)生WHERE姓名=‘馬爽’）5.3.1子查詢?cè)撜Z句實(shí)現(xiàn)查詢和馬爽同班的學(xué)生信息，括號(hào)中的查詢塊是嵌套在上層的SELECT語句的WHERE條件中的，括號(hào)中的查詢稱為子查詢或內(nèi)層查詢，而包含子查詢的查詢塊稱為父查詢或外層查詢。子查詢可以嵌套在SELECT、INSERT、UPDATE或DELETE語句的WHERE或HAVING子句內(nèi)，或者其他子查詢中。SQLServer對(duì)嵌套查詢的求解方法是由里向外處理。即每個(gè)子查詢?cè)谏弦患?jí)查詢處理之前求解，子查詢的結(jié)果用于建立父查詢的條件。嵌套查詢可以用多個(gè)簡單的查詢構(gòu)造復(fù)雜的查詢，從而提高了T-SQL語言的能力，嵌套最多不能超過32層。嵌套查詢有三種基本的子查詢，分別是帶有IN運(yùn)算符的子查詢、帶有比較運(yùn)算符的子查詢和帶有ANY、ALL或EXISTS運(yùn)算符分子查詢。5.3.1子查詢?cè)撜Z句實(shí)現(xiàn)查詢和馬爽同班的學(xué)生信息，括號(hào)中的查詢塊是嵌套在上層的SELECT語句的WHERE條件中的，括號(hào)中的查詢稱為子查詢或內(nèi)層查詢，而包含子查詢的查詢塊稱為父查詢或外層查詢。子查詢可以嵌套在SELECT、INSERT、UPDATE或DELETE語句的WHERE或HAVING子句內(nèi)，或者其他子查詢中。SQLServer對(duì)嵌套查詢的求解方法是由里向外處理。即每個(gè)子查詢?cè)谏弦患?jí)查詢處理之前求解，子查詢的結(jié)果用于建立父查詢的條件。嵌套查詢可以用多個(gè)簡單的查詢構(gòu)造復(fù)雜的查詢，從而提高了T-SQL語言的能力，嵌套最多不能超過32層。嵌套查詢有三種基本的子查詢，分別是帶有IN運(yùn)算符的子查詢、帶有比較運(yùn)算符的子查詢和帶有ANY、ALL或EXISTS運(yùn)算符分子查詢。5.3.1子查詢?cè)趲в蠭N運(yùn)算符的子查詢中，子查詢的結(jié)果是一個(gè)集合。父查詢通過IN運(yùn)算符將父查詢中的一個(gè)表達(dá)式與子查詢結(jié)果集中的每一個(gè)值進(jìn)行比較。如果表達(dá)式的值與子查詢結(jié)果集中的任何一個(gè)值相當(dāng)，父查詢的條件返回TRUE，否則返回FALSE。NOTIN運(yùn)算符與IN運(yùn)算符結(jié)果相反。1. 帶有IN運(yùn)算符的子查詢5.3.1子查詢【例5.52】使用學(xué)生表和成績表查詢學(xué)生“馬爽”的各科成績。USE學(xué)生成績管理系統(tǒng)GOSELECT*FROM成績WHERE學(xué)號(hào)IN（SELECT學(xué)號(hào)FROM學(xué)生表WHERE姓名=‘馬爽’）5.3.1子查詢?cè)趲в斜容^運(yùn)算符的子查詢中，子查詢的結(jié)果是一個(gè)單值。父查詢通過比較運(yùn)算符將父查詢中的一個(gè)表達(dá)式與子查詢結(jié)果進(jìn)行比較。常用的比較運(yùn)算符有：=、>、>=、<、<=、!=、<>、!>、!<2. 帶有比較運(yùn)算符的子查詢5.3.1子查詢【例5.54】列出選修了“101001”號(hào)課程，且成績高于該課程平均分的學(xué)生信息。USE學(xué)生成績管理系統(tǒng)GOSELECT*FROM學(xué)生WHERE學(xué)號(hào)IN(

SELECT學(xué)號(hào)

FROM成績WHERE成績>(

SELECTAVG(成績)

FROM成績

WHERE課程編號(hào)=‘101001’)

AND課程編號(hào)=‘101001’)5.3.1子查詢使用ANY或ALL運(yùn)算符時(shí)，必須同時(shí)使用比較運(yùn)算符，如>ANY、<ANY、>ALL、<ALL、=ALL、>SOME等。在帶有ANY或ALL運(yùn)算符的子查詢中，子查詢的結(jié)果是一個(gè)結(jié)果集。SOME或ANY表示表達(dá)式只要與子查詢結(jié)果集中的某個(gè)值滿足比較的關(guān)系，就返回TRUE，否則返回FALSE。ALL指定表達(dá)式要與子查詢結(jié)果集中的每個(gè)值都進(jìn)行比較，當(dāng)表達(dá)式與每個(gè)值都滿足比較的關(guān)系時(shí)，才返回TRUE，否則返回FALSE。3. 帶有ANY或ALL運(yùn)算符的子查詢5.3.1子查詢【例5.55】查找比所有“10010101”班學(xué)生年齡都大的學(xué)生。USE學(xué)生成績管理系統(tǒng)GOSELECT*FROM學(xué)生WHERE出生日期<ALL(SELECT出生日期FROM學(xué)生WHERE班級(jí)編號(hào)=