第5章數(shù)據(jù)查詢sql語言應用

第5章數(shù)據(jù)查詢本章導讀

所謂數(shù)據(jù)查詢就是用戶發(fā)出一個數(shù)據(jù)請求，按照指定的條件，從數(shù)據(jù)庫的相關(guān)表中找到滿足條件的信息的過程。數(shù)據(jù)查詢涉及兩個方面，一是用戶指定查詢條件，二是系統(tǒng)進行處理并把查詢結(jié)果返回給用戶。SQLServer2005中的數(shù)據(jù)查詢是基于SQL語言來實現(xiàn)的，本章將介紹在SQLServer2005中使用SQL語言對數(shù)據(jù)進行查詢的各種操作。本章目錄5.1

SQL概述5.2單表查詢5.3連接查詢5.4

高級查詢本章小結(jié)5.1

SQL概述

5.1.1SQL語言簡介5.1.2SQL的特點5.1.3SQL的查詢功能5.1

SQL概述

SQL是StructuredQueryLanguage（結(jié)構(gòu)化查詢語言）的縮寫，它是一個綜合的、功能極強又簡捷易學的語言，集數(shù)據(jù)查詢、數(shù)據(jù)操縱、數(shù)據(jù)定義和數(shù)據(jù)控制于一體，這使它被用戶和業(yè)界接受，成為國際標準。5.1.1SQL語言簡介

SQL是用于對存放在計算機數(shù)據(jù)庫中的數(shù)據(jù)進行組織、管理和檢索的一種工具。用戶想要檢索數(shù)據(jù)庫中的數(shù)據(jù)時，就通過SQL語言發(fā)出請求，接著DBMS（數(shù)據(jù)庫管理系統(tǒng)。譬如：SQLServer、Oracle）對該

SQL請求進行處理并檢索所要求的數(shù)據(jù)，最后將其返回給用戶，此過程被稱作為數(shù)據(jù)查詢，這也就是數(shù)據(jù)查詢語言這一名稱的由來。表5-1SQL語言動詞SQL功能動詞數(shù)據(jù)定義CREATE，DROP，ALTER數(shù)據(jù)查詢SELECT數(shù)據(jù)操縱INSERT，UPDATE，DELETE數(shù)據(jù)控制GRANT，REMOVE查詢是SQL語言的重要組成部分，但不是全部，SQL語言集數(shù)據(jù)查詢、數(shù)據(jù)操縱、數(shù)據(jù)定義和數(shù)據(jù)控制功能于一體，功能極強。5.1.2SQL的特點SQL是一種一體化的語言2.高度非過程化3.面向集合的操作方式4.以同一種語法結(jié)構(gòu)提供兩種使用方式5.1.3SQL的查詢功能SQL語言的核心功能是查詢，故查詢命令也稱為SELECT命令，其基本形式由SELECT-FROM-WHERE查詢塊組成，多個查詢可以嵌套執(zhí)行。SELECT查詢語句的目標是從數(shù)據(jù)庫中檢索滿足條件的記錄，查詢語句并不會改變數(shù)據(jù)庫中的數(shù)據(jù)，它只是檢索數(shù)據(jù)，查詢的結(jié)果仍是一個表。

SELECT語句:其主要功能是實現(xiàn)數(shù)據(jù)源數(shù)據(jù)的篩選、投影和連接操作，并能夠完成篩選字段重命名、對數(shù)據(jù)源數(shù)據(jù)組合、分類匯總、排序等具體操作，具有非常強大的數(shù)據(jù)查詢功能。SELECT語句的一般格式：

SELECT[ALL|DISTINCT]<目標列表達式>[,<目標列表達式>]...FROM<表名或視圖名>[,<表名或視圖名>]...[WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]];【例5-1】查詢students表中姓名為“王芳”的stu_name字段和class_name字段。selectstu_name，class_namefromstudentsWherestu_name=‘王芳’5.2單表查詢5.2.1指定列查詢5.2.2指定記錄行查詢5.2.3函數(shù)的使用5.2.4對查詢結(jié)果排序5.2.5對查詢結(jié)果分組5.2單表查詢5.2.1指定列查詢1．查詢所有列查詢所有列是指將表中的所有屬性列都選出來，可有兩種方法。一是在SELECT關(guān)鍵字后面列出所有列名。若列的顯示順序與其在基表中的順序相同，也可以簡單地將“目標列表達式”指定為星號“*”。【例5-2】查詢所有學生信息。select*fromstudents或selectstu_no,stu_name,stu_age,stu_spec,stu_starttime,class_namefromstudents2．查詢指定列當用戶只對表中的一部分屬性列感興趣時，可選擇表中的部分列，查詢結(jié)果集合中數(shù)據(jù)的排列順序與選擇列表中所指定的列名排列順序相同?！纠?-3】查詢所有學生的學號，姓名，年齡，班級selectstu_no,stu_name,stu_age,stu_specfromstudents或改變列顯示順序selectstu_name,stu_no,stu_spec,stu_agefromstudents3.查詢經(jīng)過計算的值SELECT子句的“目標列表達式”不僅可以是表中的屬性列，也可以是有關(guān)表達式，即可以將查詢出來的屬性列經(jīng)過一定的計算后列出結(jié)果?！纠?-4】查全體學生的姓名及其出生年份。selectstu_name,2010-stu_agefromstudentsselectstu_name,'出生年份：',2010-stu_agefromstudents4．指定列別名除添加注釋列為指定列進行解釋和說明外，用戶還可以通過指定別名的方式來改變查詢結(jié)果的列標題，這對于含算術(shù)表達式、常量、函數(shù)名的目標列表達式尤為有用。指定列別名的方法是在列的后面（用空格分開）指定相應列的別名，其語法格式有以下3種。（1）列名別名（2）列名AS別名（3）別名=列名【例5-5】將上例結(jié)果列分別用“姓名”和“出生年月”表示。（1）selectstu_name姓名,2010-stu_age出生年份fromstudents（2）selectstu_nameas姓名,2010-stu_ageas出生年份fromstudents（3）select姓名=stu_name,出生年份=2010-stu_agefromstudents5.2.2指定記錄行查詢1．簡單的條件查詢簡單的條件查詢即只設定一個條件的查詢。查詢條件謂詞比較=，>，<，>=，<=，!=<>，!>，！<，NOT+含上述比較運算符的條件表達式確定范圍BETWEEN

AND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOT

NULL多重條件AND，OR表5-2WHERE子句常用的查詢條件【例5-6】查詢所有軟件工程班學生的信息select*fromstudentswherestu_spec='軟件工程‘【例5-7】在students表中查詢所有年齡在20歲以下的學生姓名及其年齡，查詢列名用中文表示。selectstu_nameas姓名,stu_ageas年齡fromstudentswherestu_age<202．復合條件查詢當查詢條件不只一個時，可使用邏輯運算符AND或OR進行聯(lián)接，當運算結(jié)果為真時，相應記錄就加入結(jié)果集。如果AND和OR這兩個運算符同時出現(xiàn)在同一個WHERE條件子句中，則AND的優(yōu)先級高于OR，但可以通過使用括號改變優(yōu)先級。【例5-8】在學生表students中查詢學習軟件工程專業(yè)且年齡在19歲以上（包括19歲）的學生學號、姓名、年齡、專業(yè)。selectstu_no學號,stu_name姓名,stu_age年齡,stu_spec專業(yè)fromstudentswherestu_spec='軟件工程'andstu_age>=19【例5-9】在teachers表中查詢職稱為講師的男教師或職稱為助教的女教師信息。select*fromteacherswhere(teach_sex='男'andteach_tech='講師')or(teach_sex='女'andteach_tech='助教‘)3．指定范圍查詢

范圍查詢指查找屬性值在（或不在）指定范圍內(nèi)的記錄行。實現(xiàn)方法如下：[NOT]BETWEEN……AND……意為“在…和…之間”，這個查詢條件等價于“x>=表達式1ANDx<=表達式2”【例5-10】在學生表students中查詢所有年齡在18-19歲之間的學生的信息。usexjglselect*fromstudentswherestu_agebetween18and19【例5-11】在學生表students中查詢所有年齡不在18至20歲之間的學生的信息。select*fromstudentswherestu_agenotbetween18and204.指定集合查詢

集合查詢用來查找屬性值屬于（或不屬于）指定集合的元組。通過謂詞[NOT]IN來實現(xiàn)。具體格式如下：字段表達式[NOT]IN（集合元素列表）【例5-12】在學生表students中查詢所有軟件工程專業(yè)和計算機應用專業(yè)的學生的信息。select*fromstudentswherestu_specin('軟件工程','計算機應用')【例5-13】在學生表students中查詢所有不學軟件工程專業(yè)和計算機應用專業(yè)的學生的信息select*fromstudentswherestu_specnotin(‘軟件工程’,’計算機應用’)5．字符匹配查詢謂詞LIKE可以用來進行字符的匹配查詢，它判斷列值是否與指定的字符串格式相匹配，可用于char、varchar、text、ntext、datetime和smalldatetime等類型查詢。其一般語句格式如下：[NOT]LIKE匹配串該語句的功能是查找指定的屬性列值與“匹配串”相匹配的記錄?！捌ヅ浯敝械乃阉髂Ｊ?，可以使用下列有效SQLServer的通配符。%（百分號）：表示從0~n個任意字符。_（下劃線）：表示單個的任意字符。[]（封閉方括號）：表示方括號中列出的任意一個字符。[^]：任意一個沒有在方括號里列出的字符。匹配結(jié)果為邏輯類型，如果為邏輯真值則將對應的記錄加入結(jié)果集中?！纠?-14】在學生表students中查詢所有姓唐的學生的信息。select*fromstudentswherestu_namelike'唐%‘【例5-15】在學生表students中查詢所有不姓唐，且名字只有二個漢字的學生的信息。select*fromstudentswherestu_namelike'唐_‘【例5-16】在學生表students中查詢所有學號以‘T’開頭，第三位學號是7或8的學生的信息。select*fromstudentswherestu_nolike'T_[7,8]%'【例5-17】在學生表students中查詢所有學號第三位取7~8，第4位不是2~3的學生的信息。select*fromstudentswherestu_nolike'__[7,8]_[^2-3]%'6．空值判斷符謂詞ISNULL和ISNOTNULL可用來查詢空值和非空值，其中NULL表示的是空值，不是0，也不是空字符串，而是指不存在或不確定的值。注意“IS”不能用等號“=”代替?！纠?-18】在選課表xcourses中查詢?nèi)笨紝W生的學號、課程號、成績。selectstu_no學號,cour_no課程號,xcour_score課程成績fromxcourseswherexcour_scoreisnull7．刪除重復行

SELECT語句中使用ALL或DISTINCT選項來顯示表中符合條件的所有行或刪除其中重復的數(shù)據(jù)行，默認為ALL。使用DISTINCT選項時，對于所有重復的數(shù)據(jù)行在SELECT返回的結(jié)果集合中只保留一行?！纠?-19】在學生表students中查詢學生表中的各個系名。selectstu_spec系名fromstudents刪除重復行selectdistinctstu_spec系名fromstudents8．取N行若只想顯示查詢結(jié)果集的前部分記錄，可利用TOP子句限制返到結(jié)果集中的行數(shù)，其基本格式如下：TOPn[PERCENT]其中可選項“PERCENT”用于返回的結(jié)果集記錄的百分比，如未指定PERCENT，n就是返回的行數(shù)，如指定了PERCENT，n就是返回的結(jié)果集行的百分比?！纠?-20】在學生表students中查詢前三位學生的信息。selecttop3*fromstudents【例5-21】在學生表students中查詢前20%記錄的學生的信息。selecttop20percent*fromstudents5.2.3函數(shù)的使用為了進一步方便用戶，增強檢索功能，SQL提供了許多集函數(shù)

，主要包括：COUNT（[DISTINCT|ALL]*）：統(tǒng)計元組個數(shù)COUNT（[DISTINCT|ALL]<列名>）：統(tǒng)計一列中值的個數(shù)SUM（[DISTINCT|ALL]<列名>）：計算一列值的總和（此列必須是數(shù)值型）AVG（[DISTINCT|ALL]<列名>）：計算一列值的平均值（此列必須是數(shù)值型）MAX（[DISTINCT|ALL]<列名>）：求一列值中的最大值MIN（[DISTINCT|ALL]<列名>）：求一列值中的最小值【例5-22】在學生表students中查詢學軟件工程學生的平均年齡。selectavg(stu_age)平均年齡fromstudentswherestu_spec='軟件工程'【例5-23】在選課表xcourses中查詢選修了課程的學生的人數(shù)。selectcount(distinctstu_no)as學生人數(shù)fromxcourses【例5-24】在選課表xcourses中查詢學習C01課程的學生的最高分數(shù)。selectmax(xcour_score)as最高分fromxcourseswherecour_no='C01’5.2.4對查詢結(jié)果排序如果沒有指定查詢結(jié)果的顯示順序，DBMS將按其最方便的順序（通常是記錄在表中的先后順序）輸出查詢結(jié)果。用戶也可以用ORDERBY子句指定按照一個或多個屬性列的升序（ASC）或降序（DESC）重新排列查詢結(jié)果，其中升序ASC為缺省值。ORDERBY子句的語法格式為：ORDERBY列名[ASC|DESC][,…n]排序規(guī)則如下：（1）中英文字符按其ASCII碼大小進行比較。（2）數(shù)值型數(shù)據(jù)根據(jù)其數(shù)值大小進行比較。（3）日期型數(shù)據(jù)按年、月、日的數(shù)值大小進行比較。（4）邏輯型數(shù)據(jù)false小于true。若有多個屬性列進行排序，則先按第一個屬性列排序，若該屬性列中有相同的內(nèi)容，則按第二個屬性列進行排序，依次類推。【例5-25】在選課表xcourses中查詢學號為“T07001”學生的課程號及成績，并將其成績從低到高排序。selectstu_no學號,cour_no課程號,xcour_score成績fromxcourseswherestu_no='T07001'orderbyxcour_score【例5-26】在學生表students中查詢所有男生的學號、姓名、性別、年齡，要求按年齡降序排列，如年齡相同則按學號升序排列。selectstu_no學號,stu_name姓名,stu_sex性別,stu_age年齡fromstudentswherestu_sex='男'orderbystu_agedesc,stu_no5.2.5對查詢結(jié)果分組GROUPBY子句的語法格式如下：GROUPBY列名[,…n][HAVING分組條件]可按一列或多列分組，如果分組后還要求按一定的條件對這些組進行篩選，最終只輸出滿足指定條件的組，則可以使用HAVING短語指定篩選條件。【例5-27】在學生表students中查詢軟件工程各個班級學生的平均年齡。selectclass_name'班級',avg(stu_age)'平均年齡'fromstudentswherestu_spec='軟件工程'groupbyclass_name【例5-28】在學生表students中查詢各個系、各個班學生的平均年齡。selectstu_spec院系,class_name班級,avg(stu_age)平均年齡fromstudentsgroupbystu_spec,class_name【例5-29】在選課表xcourses中查詢平均成績大于75的學生的學號及平均成績，要求計算平均成績時只將幾個成績計算在內(nèi)，并按成績從高到低排序。selectstu_no學號,avg(xcour_score)平均成績fromxcourseswherexcour_score>=60groupbystu_nohavingavg(xcour_score)>75orderby平均成績5.3連接查詢5.3.1等值與非等值連接查詢5.3.2自身連接5.3.3外連接5.3.4交叉連接5.3.5關(guān)系圖5.3連接查詢5.3.1等值與非等值連接查詢用來連接兩個表的條件稱為連接運算符，通過連接運算符可以實現(xiàn)多個表查詢，其一般格式為：[表名1.]列名1比較運算符[表名2.]列名2其中比較運算符主要有：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、!=（不等于）?！纠?-30】在學籍管理數(shù)據(jù)庫xjgl中查詢學生的學號、姓名和成績信息。usexjglselectstudents.stu_no學號,stu_name姓名,xcour_score成績fromstudentsjoinxcoursesonstudents.stu_no=xcourses.stu_no或selectstudents.stu_no學號,stu_name姓名,xcour_score成績fromstudents,xcourseswherestudents.stu_no=xcourses.stu_no【例5-31】在學籍管理數(shù)據(jù)庫xjgl中查詢成績在80分以上的學生的學號、姓名，所選課程的課程號，課程名和成績信息。selectstudents.stu_no學號,stu_name姓名,xcourses.cour_no課程號,cour_name課程名,xcour_score成績fromstudentsjoinxcoursesonstudents.stu_no=xcourses.stu_nojoincoursesoncourses.cour_no=xcourses.cour_nowherexcour_score>=80或selectstudents.stu_no學號,stu_name姓名,xcourses.cour_no課程號,cour_name課程名,xcour_score成績fromstudents,xcourses,courseswherexcour_score>=80andstudents.stu_no=xcourses.stu_noandcourses.cour_no=xcourses.cour_no5.3.2自身連接

連接操作不僅可以在兩個表之間進行，也可以是一個表與其自己進行連接，這種連接稱為表的自身連接。【例5-32】在學籍管理數(shù)據(jù)庫xjgl中查詢學分不低于操作系統(tǒng)學分的課程名及學分信息。并按學分的降序排列。selecty.cour_name課程名,y.cour_credit學分fromcoursesx,coursesywherex.cour_name='操作系統(tǒng)'andx.cour_credit<y.cour_creditorderbyy.cour_creditdesc5.3.3外連接采用外連接時，返回到查詢結(jié)果集合中的不僅包含符合連接條件的記錄行，而且還包括左表(左外連接時)、右表(右外連接時)或兩個連接表(全外連接)中的所有記錄行。1.左外連接其語法格式如下：SELECT列FROM表1LEFT[OUTER]JOIN表2ON表1.列1=表2.列2【例5-33】在學籍管理數(shù)據(jù)庫xjgl中查詢所有學生的選課信息，輸出學號、姓名、課程號和成績4項。包括不選任何課程的學生。selectstudents.stu_no,students.stu_name,xcourses.cour_no,xcourses.xcour_scorefromstudentsleftjoinxcoursesonstudents.stu_no=xcourses.stu_no2.右外連接右外連接是對連接條件中右邊的表不加限制，即連接條件右邊的表中的數(shù)據(jù)會全部顯示出來，其語法格式如下：完成語句如下：SELECT列FROM表1RIGHT[OUTER]JOIN表2ON表1.列1=表2.列2【例5-34】在學籍管理數(shù)據(jù)庫xjgl中查詢所有學生的選課信息，輸出學號、姓名、課程號和成績4項。包括有成績但沒有學生信息的學生。selectstudents.stu_no,students.stu_name,xcourses.cour_no,xcourses.xcour_scorefromstudentsrightjoinxcoursesonstudents.stu_no=xcourses.stu_no3.全外連接全外連接是對兩個表都不加限制，即所有兩個表中的記錄行會全部包括在結(jié)果集中，不管是否匹配，其語法格式如下：SELECT列FROM表1FULL[OUTER]JOIN表2ON表1.列1=表2.列2【例5-35】在學籍管理數(shù)據(jù)庫xjgl中查詢所有學生的選課信息，輸出學號、姓名、課程號和成績4項。selectstudents.stu_no,students.stu_name,xcourses.cour_no,xcourses.xcour_scorefromstudentsfulljoinxcoursesonstudents.stu_no=xcourses.stu_no5.3.4交叉連接交叉連接不帶WHERE子句，其語句格式如下：SELECT列FROM表1CROSSJOIN表2或

SELECT列FROM表1,表2【例5-36】在學籍管理數(shù)據(jù)庫xjgl中對teachers表和teach表進行交叉連接。select*fromteacherscrossjointeach或select*fromteachers,teach5.3.5關(guān)系圖SQLServer2005中的關(guān)系是表之間的連接，用一個表中的外鍵引用另一個表中的主鍵，要了解多個表之間都存在哪些內(nèi)在聯(lián)系，可使用SSMS的數(shù)據(jù)庫關(guān)系圖。5.4

高級查詢5.4.1嵌套查詢5.4.2聯(lián)合查詢5.4.3在SSMS中實現(xiàn)查詢5.4.1嵌套查詢1．帶有IN謂詞的子查詢——指父查詢與子查詢之間用IN進行連接，判斷某個屬性列值是否在子查詢的結(jié)果中?！纠?-38】在學籍管理數(shù)據(jù)庫xjgl中查詢與“唐薇”在同一個專業(yè)學習的學生。第①步：selectstu_specfromstudentswherestu_name='唐薇‘第②步：selectstu_no,stu_name,stu_specfromstudentswherestu_spec='計算機應用'或selectstu_no,stu_name,stu_specfromstudentswherestu_specin(selectstu_specfromstudentswherestu_name='唐薇')【例5-39】在學籍管理數(shù)據(jù)庫xjgl中查詢選修了操作系統(tǒng)的學生的學號、姓名及性別。selectstu_no,stu_name,stu_sexfromstudentswherestu_noin(selectstu_nofromxcourseswherecour_no=(selectcour_nofromcourseswherecour_name='操作系統(tǒng)'))【例5-40】在學籍管理數(shù)據(jù)庫xjgl中查詢非講師職稱的教師所教課程的課程號及所用教材。selectcour_no,teach_bookfromteachwhereteach_nonotin(selectteach_nofromteacherswhereteach_tech='講師')5.4.1嵌套查詢2.帶有比較運算符的子查詢——父查詢與子查詢之間用比較運算符進行連接?！纠?-38】在學籍管理數(shù)據(jù)庫xjgl中查詢與“唐薇”在同一個專業(yè)學習的學生。selectstu_no,stu_name,stu_specfromstudentswherestu_spec=(selectstu_specfromstudentswherestu_name='唐薇')5.4.1嵌套查詢3．帶有ANY或ALL謂詞的子查詢比較運算符及其語義見課本表5-3【例5-41】在學籍管理數(shù)據(jù)庫xjgl中查詢除了軟件工程的其他專業(yè)的，并且年齡不大于軟件工程專業(yè)最小年齡的學生信息。selectstu_no,stu_name,stu_spec,stu_agefromstudentswherestu_age<=all(selectstu_agefromstudentswherestu_spec='軟件工程')andstu_spec!='軟件工程'或selectstu_no,stu_name,stu_spec,stu_agefromstudentswherestu_age<=(selectmin(stu_age)fromstudentswherestu_spec='軟件工程')andstu_spec!='軟件工程'5.4.1嵌套查詢4．帶有EXISTS謂詞的子查詢——判斷子查詢結(jié)果集合中是否有數(shù)據(jù)行返回。它的返回值為true或false，不產(chǎn)生實際值。【例5-42】在學籍管理數(shù)據(jù)庫xjgl中查詢選修了課程C02的學生學號、姓名和性別。selectstu_no,stu_name,stu_sex