sqlsever第4章基本查詢

第4章基本查詢目錄2高等教育出版社4.0SQL4.1Transact-SQL查詢基礎(chǔ)4.2在查詢中使用TOP和PERCENT限制查詢結(jié)果4.3在查詢中使用DISTINCT過濾重復(fù)行4.4模式查詢4.5在查詢中使用聚合函數(shù)4.6分組查詢4.0SQL1.SQL語言的發(fā)展與特點(diǎn)SQL是結(jié)構(gòu)化查詢語言(StructuredQueryLanguage)的英文縮寫，是一種使用關(guān)系模型的數(shù)據(jù)庫(kù)應(yīng)用語言。它利用一些簡(jiǎn)單的句子構(gòu)成基本的語法，從而操作和管理數(shù)據(jù)庫(kù)。目前，已成為關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)的標(biāo)準(zhǔn)語言。

SQL語言是非過程化語言，結(jié)構(gòu)簡(jiǎn)潔，功能強(qiáng)大，簡(jiǎn)單易學(xué)。2.SQL語言的分類按照其實(shí)現(xiàn)的功能可以將SQL劃分為如下幾類：數(shù)據(jù)查詢語言(DataQueryLanguage，DQL)數(shù)據(jù)定義語言(DataDefinitionLanguage，DDL)數(shù)據(jù)操縱語言(DataManipulationLanguage，DML)數(shù)據(jù)控制語言(DataControlLanguage，DCL)(1)?數(shù)據(jù)查詢語言DQL按一定的查詢條件從數(shù)據(jù)庫(kù)對(duì)象中檢索符合條件的數(shù)據(jù)，是SQL的最核心內(nèi)容。

使用語句：select語句(2)?數(shù)據(jù)定義語言DDL用于定義數(shù)據(jù)的邏輯結(jié)構(gòu)以及數(shù)據(jù)項(xiàng)之間的關(guān)系，如定義數(shù)據(jù)庫(kù)、表、視圖和索引等數(shù)據(jù)庫(kù)對(duì)象。例如：數(shù)據(jù)庫(kù)創(chuàng)建語句：CREATEDATABASE表創(chuàng)建語句：CREATETABLE

(3)?數(shù)據(jù)操縱語言DMLSQL處理（插入、刪除、修改和更新）數(shù)據(jù)庫(kù)中數(shù)據(jù)的語言稱為DML。通常包括：。插入語句：INSERT語句。刪除語句：DELETE語句。更新語句：UPDATE語句(4)?數(shù)據(jù)控制語言DCL

從數(shù)據(jù)的安全性、可靠性等方面控制用戶對(duì)數(shù)據(jù)庫(kù)的合法訪問，包括基本表和視圖等對(duì)象的授權(quán)、完整性規(guī)則的描述、事務(wù)開始和結(jié)束控制語句、用戶賬號(hào)管理、鎖定操作等。3.Transact-SQL概述

Transact-SQL（簡(jiǎn)稱T-SQL）就是SQLServer中使用的SQL版本，是Microsoft對(duì)標(biāo)準(zhǔn)的結(jié)構(gòu)化查詢語言SQL的實(shí)現(xiàn)和擴(kuò)展，它是一種交互式查詢語言，具有功能強(qiáng)大，簡(jiǎn)單易學(xué)的特點(diǎn)。既允許用戶直接查詢存儲(chǔ)在數(shù)據(jù)庫(kù)中的數(shù)據(jù)，也可以把語句插入到高級(jí)程序設(shè)計(jì)語言中使用。Transact-SQL對(duì)SQL的擴(kuò)展主要包含如下3個(gè)方面：(1)增加了流程控制語句。(2)加入了局部變量、全局變量等許多新概念，可以寫出更復(fù)雜的查詢語句。(3)增加了新的數(shù)據(jù)類型，處理能力更強(qiáng)。T-SQL語法約定規(guī)范作用|（豎線）分隔括號(hào)或大括號(hào)內(nèi)的語法項(xiàng)目，只能選擇一個(gè)項(xiàng)目[]（方括號(hào)）可選語法項(xiàng)目{}（大括號(hào)）必選語法項(xiàng)目[,…,n]前面的項(xiàng)目可重復(fù)n次，每項(xiàng)之間用逗號(hào)分隔4.1T-SQL查詢基礎(chǔ)11高等教育出版社4.1.1查詢和記錄集4.1.2使用Select語句進(jìn)行查詢

12高等教育出版社

SELECT語句概述SELECT語句是T-SQL的真正核心內(nèi)容，其作用就是查詢數(shù)據(jù)庫(kù)中的數(shù)據(jù)。查詢的對(duì)象：數(shù)據(jù)表或視圖查詢的過程：根據(jù)查詢條件在指定數(shù)據(jù)表或視圖中找到并輸出一個(gè)由滿足條件的數(shù)據(jù)列和行構(gòu)成的記錄集。4.1.1查詢和記錄集13高等教育出版社查詢：是對(duì)存儲(chǔ)在數(shù)據(jù)庫(kù)中的數(shù)據(jù)發(fā)出的一種請(qǐng)求。記錄集：是由數(shù)據(jù)庫(kù)查詢返回的數(shù)據(jù)行組成，并且臨時(shí)存儲(chǔ)在應(yīng)用程序服務(wù)器的內(nèi)存中，以便進(jìn)行快速檢索。當(dāng)服務(wù)器不再需要記錄集時(shí)，就會(huì)將其丟棄。

4.1.2使用SELECT語句進(jìn)行查詢14高等教育出版社

SELECT語句是一個(gè)查詢表達(dá)式，包括SELECT、FROM、WHERE、GROUPBY和ORDERBY子句。

SELECT語句的基本格式SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式表>[AS列名表][INTO<新表名>]

FROM<表名>[，<表名>…][WHERE<條件表達(dá)式>[AND|OR<條件表達(dá)式>...][GROUPBY列名[HAVING<條件表達(dá)式>]][ORDERBY列名[ASC|DESC]]基本格式：SELECT—FROM—WHERE組成的查詢塊。含義：根據(jù)WHERE子句的篩選條件，從FROM子句指定的表中找出滿足條件記錄，再按SELECT子句中指定的字段次序，構(gòu)造一個(gè)顯示結(jié)果表。ALL|DISTINCT：默認(rèn)值為ALL，表示返回查詢結(jié)果集的所有行，包括重復(fù)行。DISTINCT表示若查詢結(jié)果集中有相同的行，則只保留一行。

AS列名表：指定顯示目標(biāo)列的別名。INTO<新表名>：創(chuàng)建一個(gè)新表，表的數(shù)據(jù)為查詢結(jié)果集的數(shù)據(jù)。GROUP子句：指定結(jié)果記錄的分組方式，即該值相等的記錄為一個(gè)組。HAVING短語：只有滿足條件的組才顯示輸出。ORDER子句：指定結(jié)果記錄的排序輸出方式。WHERE子句作用：指定查詢的條件，即指定數(shù)據(jù)記錄應(yīng)滿足的條件。條件：關(guān)系表達(dá)式、邏輯表達(dá)式和特殊表達(dá)式。（1）關(guān)系表達(dá)式

定義：用關(guān)系運(yùn)算符將兩個(gè)表達(dá)式連接在一起的式子

結(jié)果:邏輯值(TRUE、FALSE)，

格式：<表達(dá)式1><關(guān)系運(yùn)算符><表達(dá)式2>

舉例：WHEREstudent_sex=‘男’

關(guān)系運(yùn)算符：=、<、<=、>、>=、!=或<>。不同類型數(shù)據(jù)的比較字符型數(shù)據(jù)比較：采用對(duì)字符的ASCII值進(jìn)行比較的方法。

注意：任何字符都有對(duì)應(yīng)的ASCII值。字符串的比較：從左向右依次逐個(gè)進(jìn)行比較。日期字符串：按照“年-月-日”的格式書寫。漢字比較：按照漢語拼音字母逐個(gè)比較。數(shù)字：按數(shù)值大小比較。(2)邏輯表達(dá)式定義：用邏輯運(yùn)算符將兩個(gè)關(guān)系表達(dá)式連接起來的式子。結(jié)果：邏輯值(TRUE、FALSE)。格式：[<關(guān)系表達(dá)式1>]<邏輯運(yùn)算符><關(guān)系表達(dá)式2>邏輯運(yùn)算符：AND、OR、NOT(3)特殊表達(dá)式運(yùn)算符號(hào)意義%通配符，包含零個(gè)或更多字符的任意字符串_(下劃線)通配符，表示任意單個(gè)字符[]指定范圍（[a-f]）或集合（[abcdef]）中的任何單個(gè)字符[^]不屬于指定范圍([a-f])或集合([abcdef])中的任何單個(gè)字符BETWEEN…AND定義一個(gè)區(qū)間范圍IS[NOT]NULL測(cè)試字段值是否為空值[NOT]LIKE模式匹配，字符串匹配操作符[NOT]IN檢查一個(gè)字段值是否屬于一組值之中[NOT]EXISTS檢查某一個(gè)字段值是否有值，EXISTS是ISNULL的反義詞最基本查詢20高等教育出版社例

查詢學(xué)生表（Student）中所有學(xué)生的信息。查詢語句：

SELECT*FROMStudent

例

查詢學(xué)生表（Student）中所有學(xué)生的學(xué)號(hào)、姓名，并將輸出結(jié)果中的列名顯示為“學(xué)號(hào)”、“姓名”。查詢語句：

selectStu_ID學(xué)號(hào),Stu_Name姓名fromStudent條件查詢21高等教育出版社例

查詢學(xué)生表（Student）中物理系學(xué)生的學(xué)號(hào)、姓名、性別信息。

查詢語句：SELECTStu_ID,Stu_Name,Ssex

FROMStudentWHERESdept='物理系'

例

查詢學(xué)生表（Student）中物理系和化學(xué)系中年齡在22歲以上的學(xué)生的信息。

查詢語句：SELECT*FROMStudentWHERE(Sdept=‘物理系’orSdept=‘化學(xué)系')andsage>22條件查詢22高等教育出版社例

查詢選課表（Stu_Course）中所有考核成績(jī)大于90分的學(xué)生信息。查詢語句：

SELECT*FROMStu_Course

WHEREScore>90

執(zhí)行結(jié)果如圖所示：查詢結(jié)果排序23高等教育出版社例

查詢課程表（Course）中所有課程的信息，并按教材編號(hào)升序排列。

查詢語句：

SELECT*FROMCourseORDERBYBooks_id

執(zhí)行結(jié)果如圖所示：查詢結(jié)果排序24高等教育出版社例

查詢學(xué)生表（Student）中所有學(xué)生的學(xué)號(hào)、姓名、性別和所在系的信息，并按系別升序排列，系別相同的按性別降序排列，性別相同的按學(xué)號(hào)降序排列。查詢語句：

SELECTStu_ID,Stu_name,Ssex,SdeptFROMStudent

orderbySdeptasc,Ssexdesc,Stu_IDdesc25高等教育出版社說明：

（1）如果要引用某數(shù)據(jù)表名稱，而當(dāng)前數(shù)據(jù)庫(kù)不是引用對(duì)象所屬數(shù)據(jù)庫(kù)，可以使用USE語句將數(shù)據(jù)表所屬的數(shù)據(jù)庫(kù)切換為當(dāng)前數(shù)據(jù)庫(kù)。

USEtestdbselect*fromcourse（2）如果當(dāng)前要使用的列名不明確是哪個(gè)表的列，需要通過給該列指定表名來明確其數(shù)據(jù)源。

selectstudent.stu_ID,student.stu_namefromstudent（3）可以使用<數(shù)據(jù)庫(kù)名>.<數(shù)據(jù)表名>.<列名>的方式。4.2使用Top和Percent限制查詢結(jié)果26高等教育出版社

查詢數(shù)據(jù)過程中，如果只需獲取查詢結(jié)果的前面幾行數(shù)據(jù)，可以在SELECT語句中加入TOP子句來實(shí)現(xiàn)。

TOP子句的語法格式

TOP(<數(shù)值表達(dá)式>)[PERCENT][WITHTIES]<數(shù)值表達(dá)式>用來指定返回結(jié)果的行數(shù)或百分比數(shù)。PERCENT指定百分比。WITHTIES必須與ORDERBY子句配合使用，假設(shè)數(shù)值表達(dá)式的結(jié)果是N，此選項(xiàng)可以將按ORDERBY排好順序的前N行數(shù)據(jù)以及與第N行排序列值具有相同值的所有行一并列出。27高等教育出版社例

查詢學(xué)生表Student中前面4位學(xué)生的學(xué)號(hào)、姓名、性別和所在系的信息。查詢語句：

SELECTTOP(4)Stu_ID,Stu_name,Ssex,Sdept

FROMStudentORDERBYStu_ID

執(zhí)行結(jié)果如圖所示：28高等教育出版社例

查詢選課表Stu_Course中成績(jī)居前4位的數(shù)據(jù)行的信息。方法1：采用TOPN

SELECTTOP(4)*FROMStu_CourseORDERBYScoreDESC執(zhí)行結(jié)果如圖（a）所示：（a）采用TOPN的執(zhí)行結(jié)果29高等教育出版社例

查詢選課表Stu_Course中成績(jī)居前4位的數(shù)據(jù)行的信息。方法2：采用TOPNWITHTIES

SELECTTOP(4)WITHTIES*FROMStu_CourseORDERBYScoreDESC執(zhí)行結(jié)果如圖（b）所示：（b）采用TOPNWITHTIES的執(zhí)行結(jié)果30高等教育出版社例查詢選課表Stu_Course中成績(jī)居前30%的數(shù)據(jù)行的學(xué)號(hào)、課程號(hào)和成績(jī)。查詢語句：

SELECTTOP(30)percentStu_ID,Course_ID,ScoreFROMStu

_CourseORDERBYScoreDESC4.3使用DISTINCT過濾重復(fù)記錄31高等教育出版社

關(guān)鍵字DISTINCT可從SELECT語句的結(jié)果中消除重復(fù)行。

32高等教育出版社例

查詢學(xué)生表（Student）中學(xué)生的所在系的信息。

方法1：不帶關(guān)鍵字DISTINCT

SELECTSdept

FROMStudent

執(zhí)行結(jié)果如圖（a）所示：（a）不帶關(guān)鍵字DISTINCT的執(zhí)行結(jié)果33高等教育出版社例

查詢學(xué)生表（Student）中學(xué)生的所在系的信息。

方法2：帶關(guān)鍵字DISTINCT

SELECTDISTINCTSdept

FROMStudent執(zhí)行結(jié)果如圖（b）所示：（b）帶關(guān)鍵字DISTINCT的執(zhí)行結(jié)果4.4模式查詢34高等教育出版社4.4.1使用LIKE和通配符進(jìn)行模糊查詢4.4.2使用BETWEEN在指定范圍內(nèi)進(jìn)行查詢4.4.1使用LIKE和通配符進(jìn)行模糊查詢

35高等教育出版社

T-SQL的查詢語句在處理字符中，提供了LIKE與NOTLIKE兩個(gè)運(yùn)算符。這兩個(gè)運(yùn)算符可以與通配符配合進(jìn)行查詢。語法格式SELECT<列名表>FROM<表名>WHERE<列名>LIKE|NOTLIKE<包含通配符的字符串表達(dá)式>36高等教育出版社

T-SQL提供四種通配符%表示任意多個(gè)（包含0個(gè)）字符?！?”可匹配任意類型和長(zhǎng)度的字符。_表示任意單個(gè)字符?！癬”可匹配單個(gè)任意字符，常用來限制表達(dá)式的字符長(zhǎng)度。[]表示括號(hào)內(nèi)所列字符中的一個(gè)。“[]”指定一個(gè)字符、字符串或范圍，要求所匹配對(duì)象為它們中的任一個(gè)。[^]表示括號(hào)內(nèi)所列的字符以外的單個(gè)字符。37高等教育出版社例

查詢學(xué)生表（Student）中姓“高”的學(xué)生的學(xué)號(hào)、姓名、性別和所在系等信息。查詢語句：

SELECTStu_ID,Stu_Name,Ssex,Sdept

FROMStudentWHEREStu_NameLIKE'高%'執(zhí)行結(jié)果如圖所示：38高等教育出版社例

查詢學(xué)生表（Student）中不姓“高”的學(xué)生的學(xué)號(hào)、姓名、性別和所在系的信息出來。

查詢語句：

SELECTStu_ID,Stu_Name,Ssex,Sdept

FROMStudentWHEREStu_NameNOTLIKE'高%'執(zhí)行結(jié)果如圖所示：39高等教育出版社例

查詢學(xué)生表（Student）中姓名是三個(gè)字且第二個(gè)字是“正”的學(xué)生信息。查詢語句：

SELECT*FROMStudentWHEREStu_NameLIKE'_正_'40高等教育出版社例

查詢學(xué)生表（Student）中姓“高”、“李”、“王”的學(xué)生信息。查詢語句：

SELECT*FROMStudentWHEREStu_NameLIKE'[高李王]%'

例

查詢學(xué)生表（Student）中不姓“高”、“李”、“王”的學(xué)生信息。查詢語句：

SELECT*FROMStudentWHEREStu_NameLIKE'[^高李王]%'

或者查詢語句：

SELECT*FROMStudentWHEREStu_NamenotLIKE'[高李王]%'

4.4.2使用BETWEEN在指定范圍內(nèi)進(jìn)行查詢41高等教育出版社在T-SQL的查詢語句中，一般使用比較運(yùn)算符和邏輯運(yùn)算符來構(gòu)造條件表達(dá)式指定查詢范圍。為了使條件表達(dá)式含義直觀、簡(jiǎn)潔，可以使用BETWEEN…AND結(jié)構(gòu)替代條件表達(dá)式。使用BETWEEN…AND結(jié)構(gòu)要特別注意范圍是包含了前后端點(diǎn)的數(shù)據(jù)。可以參與范圍指定的數(shù)據(jù)類型包括數(shù)值型數(shù)據(jù)、日期時(shí)間型數(shù)據(jù)、文本型數(shù)據(jù)。42高等教育出版社例

查詢選課表（Stu_Course）中成績(jī)?cè)?0到90之間的數(shù)據(jù)行的信息。查詢語句：

SELECT*FROMStu_CourseWHEREScoreBETWEEN80AND90

或者查詢語句：

SELECT*FROMStu_CourseWHEREScore>=80ANDScore<=90

4.5在查詢中使用聚合函數(shù)43高等教育出版社聚合函數(shù)經(jīng)常與select語句的groupby子句一起使用。所有聚合函數(shù)均為確定性函數(shù)，也就是說，只要使用一組特定輸入值調(diào)用聚合函數(shù)，該函數(shù)總是返回相同的值。它可以對(duì)一組值執(zhí)行計(jì)算，并返回單個(gè)值。函數(shù)名稱含義AVG求大量數(shù)據(jù)的平均值COUNT統(tǒng)計(jì)計(jì)數(shù)值，返回參數(shù)中非NULL值的數(shù)目MAX返回多個(gè)數(shù)據(jù)項(xiàng)中的最大值MIN返回多個(gè)數(shù)據(jù)項(xiàng)中的最小值SUM返回指定數(shù)據(jù)項(xiàng)的總和44高等教育出版社1.計(jì)算平均值計(jì)算平均值使用AVG函數(shù)。

語法格式SELECTAVG(<列名>)FROM<表名>WHERE<條件表達(dá)式>查詢結(jié)果返回符合條件行的相應(yīng)列的平均數(shù)值。操作數(shù)據(jù)列的數(shù)據(jù)類型是數(shù)值數(shù)據(jù)，即INT、DECIMAL、MONEY、SMALLMONEY、FLOAT和REAL等。45高等教育出版社例

在選課表（Stu_Course）中，查詢學(xué)號(hào)為200611012901學(xué)生的平均成績(jī)。查詢語句：SELECTAVG(Score)FROMStu_CourseWHEREStu_ID='200611012901'執(zhí)行結(jié)果如圖所示：46高等教育出版社2.計(jì)算最小值與最大值計(jì)算最小值與最大值的函數(shù)為MIN與MAX。語法格式SELECTMIN(<列名>)|MAX(<列名>)FROM<表名>WHERE<條件表達(dá)式>查詢結(jié)果返回符合條件行的相應(yīng)列的最小值與最大值。操作數(shù)據(jù)列的數(shù)據(jù)類型是數(shù)值、字符串和日期時(shí)間等。

47高等教育出版社例

查詢選課表（Stu_Course）中的最低成績(jī)和最高成績(jī)。

查詢語句：SELECTMIN(Score)'最低成績(jī)',MAX(Score)'最高成績(jī)'FROMStu_Course執(zhí)行結(jié)果如圖所示：48高等教育出版社3.求和求和使用SUM函數(shù)。語法格式SELECTSUM(<列名>)FROM<表名>WHERE<條件表達(dá)式>查詢結(jié)果返回符合條件行的相應(yīng)列的所有值的總和。

SUM函數(shù)也可以配合DISTINCT來計(jì)算唯一數(shù)值的總和。49高等教育出版社例

在選課表（Stu_Course）中，查詢學(xué)號(hào)為200611012901的學(xué)生的成績(jī)總和、最低成績(jī)和最高成績(jī)。查詢語句：

SELECTSUM(Score)'成績(jī)總和',MIN(Score)'最低成績(jī)',MAX(Score)'最高成績(jī)'FROMStu_CourseWHEREStu_ID='200611012901'執(zhí)行結(jié)果如圖所示：50高等教育出版社4.統(tǒng)計(jì)行數(shù)

統(tǒng)計(jì)行數(shù)使用函數(shù)COUNT。語法格式SELECTCOUNT(<列名>)|COUNT_BIG(<列名>)FROM<表名>WHERE<條件表達(dá)式>

查詢結(jié)果返回符合條件行的行數(shù)。兩個(gè)函數(shù)功能一樣，不同的是COUNT返回的行數(shù)是INT數(shù)據(jù)類型的；COUNT_BIG返回的行數(shù)是BIGINT數(shù)據(jù)類型。COUNT（*）返回的是所有符合條件的數(shù)據(jù)行行數(shù)，而COUNT（列名）返回的是符合條件的、相應(yīng)列值不是NULL的數(shù)據(jù)行的行數(shù)。51高等教育出版社例

在選課表（Stu_Course）中，查詢學(xué)號(hào)為200611012901學(xué)生的成績(jī)總和、選課門數(shù)、最低成績(jī)和最高成績(jī)。查詢語句：

SELECTSUM(Score)'成績(jī)總和',COUNT(*)'選課門數(shù)',MIN(Score)'最低成績(jī)',MAX(Score)'最高成績(jī)'FROMStu_CourseWHEREStu_ID='200611012901'執(zhí)行結(jié)果如圖所示：4.6分組查詢52高等教育出版社4.6.1數(shù)據(jù)分組統(tǒng)計(jì)GROUPBY4.6.2使用HAVING

4.6.1數(shù)據(jù)分組統(tǒng)計(jì)GROUPBY53高等教育出版社

GROUPBY子句用來為查詢結(jié)果記錄集中的每一行產(chǎn)生聚合值，一般都和聚合函數(shù)配合使用。如果聚合函數(shù)使用GROUPBY子句，則會(huì)根據(jù)GROUPBY后面的列進(jìn)行分組，并按組進(jìn)行匯總計(jì)算。否則，只為SELECT語句報(bào)告一個(gè)聚合值。語法格式SELECT<目標(biāo)列名>FROM<表名>GROUPBY<列名>[ORDERBY<列名>]GROUPBY后跟的列名所指的列，稱為組合列。

“<目標(biāo)列名>”多為聚合函數(shù)和組合列。54高等教育出版社例

統(tǒng)計(jì)學(xué)生表（Student）中每個(gè)系的學(xué)生人數(shù)。查詢語句：SELECTSdept,COUNT(*)FROMStudentGROUPBYSdept

55高等教育出版社例

統(tǒng)計(jì)選課表（Stu_Course）中每個(gè)學(xué)生的所修學(xué)分總和、選課門數(shù)。查詢語句：SELECTStu_ID,SUM(Credit)'所修學(xué)分',COUNT(*)'選課門數(shù)'FROMStu_CourseGROUPBYStu_ID

ORDERBYStu_ID

執(zhí)行結(jié)果如圖所示：4.6.2使用HAVING56高等教育出版社

HAVING子句的用法類似于WHERE子句，用于對(duì)分組按條件進(jìn)行篩選后，再對(duì)每組進(jìn)行匯總計(jì)算。HAVING子句通常與GROUPBY子句配合使用。語法格式HAVING<條件表達(dá)式>“<條件表達(dá)式>”為分組或聚合的查詢條件。