《sql server數據庫應用與實踐教程》課件第4章-單表查詢

1練習：創(chuàng)建表Employee列名數據類型說明EmpIDintPrimaryKeyProjIDintPrimaryKey,參照Project表中ProjID字段EmpNamevarchar(20)NOTNULLDepIDintNOTNULL，參照完整性Cityvarchar(20)只能是上海，南京，蘇州三個城市解決方案：createtableEmployee(EmpIDintProjIDintconstraintfkProjIDforeignkeyreferencesProject(ProjID)constraintpkEmpProjPrimarykey(EmpID,ProjID),EmpNamevarchar(20)notnull,DepIDintnotnullconstraintfkDepIDforeignkeyreferencesDepartment(DepID),Cityvarchar(20)constraintchkCitycheck(Cityin(‘上?！?’南京’,’蘇州’)))第四章

表的管理—單表查詢3目標帶有Where子句的查詢根據條件對表記錄排序對記錄分組顯示對記錄分組統(tǒng)計基本查詢語句4單表的查詢使用SELECT語句實現查詢基本語法：

SELECT子句

[INTO子句]

FROM子句

[WHERE子句][GROUPBY子句][HAVING子句][ORDERBY子句][COMPUTE子句]5簡單的查詢語句有關系模式：Student(StuID,StuName,StuAge,StuAddress,DepID)顯示格式為：StuID

StuNameStuAge12001

Mary2112002Tom206簡單的查詢語句語法：SELECT[ALL|DISTINCT]select_column_list

FROM{table_name}StuID

StuNameStuAge12001

Mary2112002Tom20顯示特定字段：SELECTStuID,StuName,StuAgeFROMStudent7簡單的查詢語句Student(StuID,StuName,StuAge,StuAddress,DepID)顯示格式為：StuID

StuNameStuAgeStuAddressDepID12001

Mary21NanJing112002Tom20BeiJing2顯示所有字段：SELECT*FROMStudent8簡單的查詢語句Student(StuID,StuName,StuAge,StuAddress,DepID)顯示格式為：學號姓名年齡12001

Mary2112002Tom209簡單的查詢語句方法1:SELECT'別名1'=列名1,'別名2'=列名2,…'別名n'=列名nFROMtable_name方法2：SELECT列名1AS'別名1',列名2AS'別名2',…列名nAS'別名n'FROMtable_name學號姓名年齡12001

Mary2112002Tom20SELECT'學號'

=StuID,'姓名'

=StuName,'年齡'=StuAgeFROMstudent10簡單的查詢語句StuIDStuNameStuAge12001

Mary2112002Tom2012003Jack21注：默認情況為ALL。為ALL時，SELECTStuAgeFROMStudent有3條記錄；為DISTINCT時，SELECTDISTINCTStuAgeFROMStudent有2條記錄。語法：SELECT[ALL|DISTINCT]select_column_list

FROM{table_name}114.2.2聚合函數聚合函數用于對數據庫表中的一列或幾列數據進行統(tǒng)計匯總，常用于查詢語句中。聚合函數功能AVG(表達式)返回表達式(含列名)的平均值。COUNT(表達式)對表達式指定的列值進行計數，忽略空值。COUNT(*)對表或組中的所有行進行計數，包含空值。MAX(表達式)表達式中最大的值。MIN(表達式)表達式中最小的值SUM(表達式)表達式值的合計。124.2.2聚合函數【例】設某學生數據庫中有一個“SC”表，該表包含的列有：StuID、CourseID、Grade。其中，Grade列為int類型，使用聚合函數實現以下各功能。（1）求所有學生的成績的平均值。（2）求選修“2”號課程的學生的總成績。（3）求選修“2”號課程的學生最高成績，最低成績（4）統(tǒng)計選修“2”號課程的學生總人數解決方案：（1）SELECTAVG(Grade)FROMSC（2）SELECTSUM(Grade)FROMSCWHERECourseID=2（3）SELECTMAX(Grade),MIN(Grade)FROMSCWHERECourseID=2（4）SELECTCOUNT(*)FROMSCWHERECourseID=2134.4.2使用CASE函數CASE函數可以根據指定表達式的值返回多個可能的結果之一CASE命令的基本語法如下：

CASE條件表達式

WHEN值1THEN結果1......ELSE其他結果

END示例：對“Student”表中“StuSex”列的值進行處理，當值等于

“女”時返回0，當值等于“男”時返回1。解決方案：

SELECTStuName,‘SexValue’=CASEStuSexWHEN'女'THEN0ELSE1ENDFROMStudent144.2.2字符串函數多數字符串函數用于對字符串參數值執(zhí)行操作，返回結果為字符串或數字值。示例返回值UPPER('Abcd')'ABCD'LOWER('HELLO')'hello'LTRIM('howareyou')'howareyou'RTRIM('howareyou')'howareyou''Hello'+SPACE(3)+'Zhang''HelloZhang'REPLICATE('ab',3)'ababab'STUFF('abcdef',2,3,'ijklmn')'aijklmnef'REVERSE('abc')'cba'154.2.2字符串函數示例返回值ASCII('A')ASCII('Abc')6565CHAR(65)'A'STR(3.1415926,8,4)STR(3.1415926,5)'3.1416''3'LEN('abc')LEN('abc')33RIGHT('hello',3)'llo'LEFT('hello',3)'hel'SUBSTRING('hello',3,2)SUBSTRING('hello',3,5)'ll''llo'164.2.2字符串函數示例返回值CHARINDEX('cd','abcdabcd')CHARINDEX('cd','abcdabcd',4)CHARINDEX('dc','abcdabcd')370REPLACE('abcdefghicde','cde','xxx')'abxxxfghixxx'【例】將Student表中姓名的左右空格去掉后顯示出來。174.2.2數學函數用于對數字表達式進行數學運算并返回運算結果。示例返回值ABS(-1.0)1.0SQRT(2)1.4142135623730951SQUARE(2)4.0POWER(2,6)64SIN(30*3.1416/180)0.50000106036260283COS(30*3.1416/180)0.86602479158293899tan(45*3.1416/180)1.0000036732118496LOG(2.7182)0.99996989653910984LOG10(10)1.0EXP(1)2.7182818284590451184.2.2數學函數示例返回值ROUND(123.9994,3)ROUND(748.58,-2)123.9990700.00CEILING(123.45)CEILING(-123.45),124.00-123.00FLOOR(123.45)FLOOR(-123.45)123-124PI()3.1415926535897931RAND(7)0.71370379104047277【例】聲明變量保存根號2的值，并將該值打印出來。194.2.2日期和時間函數

用于對日期和時間數據進行各種不同的處理或運算，并返回一個字符串、數字值或日期和時間值。示例返回值GETDATE()2006-08-2121:46:38.320DATEADD(DAY,1,'1780-11-01')DATEADD(MONTH,5,'1780-11-01')112178012:00AM041178112:00AMDATEDIFF(MONTH,'1780-1-11','1780-11-01')DATEDIFF(YEAR,'1790-1-11','1780-11-01')10-10204.2.2日期和時間函數【例】有關系模式Employee(EmpID,EmpName,EmpBirthDate)顯示學生的年齡。示例返回值DATENAME(month,getdate())08(設當前為8月份)DAY('03/12/1998')12DATEPART(datepart,date)Datepart(yy,’2006-08-21’)返回2006SELECTdatediff(yy,EmpBirthDate,getdate())AS'年齡'FROMEmployee214.2.2轉換函數隱式轉換顯式轉換：CAST或CONVERT224.2.2轉換函數1.CAST函數用于將某種數據類型的表達式顯式轉換為另一種數據類型。格式:CAST(表達式AS數據類型)2.CONVERT函數用于將某種數據類型的表達式顯式轉換為另一種數據類型，在將日期時間類型的數據轉換為字符類型的數據時，可以指定轉換后的字符樣式。格式:CONVERT(數據類型[(長度)],表達式[,樣式])234.2.2轉換函數【例】在查詢分析器中執(zhí)行以下語句：DECLARE@date_var1datetimeSET@date_var1=GETDATE()PRINT'當前時間為'+CAST(@date_var1asvarchar(50))PRINT'當前時間為'+CONVERT(varchar(50),@date_var1)顯示結果為：當前時間為1620148:20AM當前時間為1620148:20AM244.4.3設置查詢條件WHERE子句用于指定返回的行的搜索條件。相當于選擇運算。它的基本語法如下：

WHERE條件表達式條件表達式中可包含：邏輯運算符(NOT,AND,OR)比較運算符(>,<=…)范圍運算符（BETWEEN）列表運算符（IN,LIKE…）NULL關鍵字25使用NOT,AND和OR運算符語法：SELECT列名列表FROM表名WHERE表達式1{AND/OR}[NOT]表達式2示例1：使用SELECT語句查詢“Student”表中所有111051班的女生。示例2：使用SELECT語句查詢“Student”表中所有111051和111053班的學生。解決方案：1.SELECT*FROMStudentWHEREStuClass='111051'ANDStuSex='女生'2.SELECT*FROMStudentWHEREStuClass='111051'ORStuClass='111053'26比較運算符示例：選擇年齡介于20-25歲且是5系的學生解決方案：SELECT*FROMstudentWHEREStuAge>=20ANDStuAge<=25ANDDepID=527使用BETWEEN運算符在WHERE子句中使用BETWEEN運算符可以查詢指定范圍的記錄。示例:使用SELECT語句查詢“Student”表中所有StuScore在60到80之間的學生記錄。解決方案：

SELECT*FROMStudentWHEREStuScoreBETWEEN60AND8028使用IN（NOTIN）運算符解決方案：SELECT*FROMstudentWHEREStuCityIN('Beijing','Shanghai')當要篩選的值在有限個可數的范圍之內時，可使用IN關鍵字。語法：SELECT列名列表FROM表名WHERE表達式{IN|NOTIN}(‘值列表‘)示例:查找來自于上海，北京城市的學生信息29使用LIKE運算符可以在WHERE子句中使用LIKE運算符進行模糊查詢,用LIKE運算符指定匹配條件的語法如下：

[NOT]LIKE'匹配串'示例:查詢“Student”表中所有姓名中包含“李”記錄。解決方案：SELECT*FROMStudentWHEREStuNameLIKE'%李%'30選擇包含未知信息的列示例:查詢“Student”表中來自城市為未知的學生。解決方案：SELECT*FROMStudentWHEREStuCityISNULL314.4.4對查詢結果排序使用ORDERBY子句可以對查詢結果進行排序?；菊Z法：

ORDERBY{列名[ASC|DESC]}[,...n]示例:使用SELECT語句查詢表“Student”所有記錄，按照Score字段從高到低排序。解決方案：

SELECT*FROMStudentORDERBYStuScoreDESC

SELECT

Sex,max(StuScore)

FROMstudent

WHEREStuSex=‘女’GROUPBY

ALLStuSex

—1.執(zhí)行where語句篩選所有女同學信息2.按Sex進行分組統(tǒng)計3.女同學可以獲取最高分，男同學信息沒被where篩選中，故統(tǒng)計最高分時無此項信息，用NULL替代。324.4.5對查詢結果分組當SELECT子句中包含聚合函數時，可以使用GROUPBY子句對查詢結果進行分組統(tǒng)計，計算每組記錄的匯總值。GROUPBY子句的基本語法如下：

GROUPBY[ALL]分組表達式[,...n]示例：在“Student”表中按性別統(tǒng)計所有學生的最高成績。解決方案：

SELECTStuSex,max(StuScore)

FROMstudent

GROUPBYStuSex

—先分組再執(zhí)行聚合函數其中ALL表示不滿足where條件的所有組也顯示在結果中，通常由NULL表示。

SELECTStuSex,max(StuScore)

FROMstudent

WHEREStuSex=‘女’GROUPBY

ALLStuSex

—1.執(zhí)行where語句篩選所有女同學信息2.按Sex進行分組統(tǒng)計3.女同學可以獲取最高分，男同學信息沒被where篩選中，故統(tǒng)計最高分時無此項信息，用NULL替代。334.4.5對查詢結果分組3女92…IDSexScore…1女90…2男85…3女92…34注意

在使用GROUPBY子句時，SELECT子句中每一個非聚合表達式內的所有列都應包含在GROUPBY列表中。否則將會返回錯誤信息。示例：

SELECTStuSex,StuClass,MAX(StuScore)FROMStudentGROUPBYStuSex執(zhí)行上面的語句會因為GROUPBY表達式與SELECT子句不匹配而產生錯誤。改成：

SELECTStuSex,StuClass,MAX(StuScore)FROMStudentGROUPBYStuSex,StuClass354.4.6指定組或聚合的搜索條件HAVING子句的功能是指定組或聚合的搜索條件。HAVING通常與GROUPBY子句一起使用。如果不使用GROUPBY子句，HAVING的作用與WHERE子句一樣。HAVING與WHERE的區(qū)別在于：1.WHERE子句搜索條件在進行分組操作之前應用；而HAVING搜索條件在進行分組操作之后應用。2.HAVING語法與WHERE語法類似，但在HAVING子句中可以包含聚合函數,而WHERE子句不能包含聚合函數。36示例示例：統(tǒng)計最高成績超過90的院系及最高入學成績信息。

解決方案：SELECTDepID,MAX(StuScore)FROMstudentGROUPBYDepIDHAVINGMAX(StuScore)>90SELECTDepID,MAX(StuScore)FROMstudentWHEREMAX(StuScore)>90GROUPBYDepID聚合不應出現在WHERE子句中，除非該聚合位于HAVING子句或選擇列表所包含的子查詢中。37練習：

解釋下列查詢語句代表的含義：SELECTTitle,avg(VacationHours)FROMEmployeeWHEREVacationHours>30GROUPBYTitleHavingavg(VacationHours)>55解決方案：對度假時間超過30小時的員工按照職稱進行分組，將平均度假時間超過55小時的組的職稱及平均度假時間顯示出來。384.4.7生成匯總行使用COMPUTE子句可以在結果集的最后生成附加的匯總行，因此既可以查看明細行，又可以查看匯總行。語法：COMPUTE{{AVG|COUNT|MAX|MIN|SUM}(表達式)}[,...n][BY表達式[,...n]]39COMPUTE與COMPUTEBY當COMPUTE不帶BY子句時，查詢結果包含：第一個結果集是包含查詢結果的所有明細行。第二個結果集有一行，其中包含COMPUTE子句中所指定的聚合函數的合計。當COMPUTE與BY一起使用時，COMPUTE子句可以對結果集進行分組并在每一組之后附加匯總行，符合查詢條件的每個組都包含：每個組的第一個結果集是明細行集。每個組的第二個結果集有一行，其中包含該組的COMPUTE子句中所指定的聚合函數的小計。40示例:COMPUTE解決方案：SELECTStuName,StuScoreFROMStudentCOMPUTESUM(StuScore)1.查詢所有學生的成績，并統(tǒng)計總成績。明細行匯總41COMPUTEBYCOMPUTE與BY一起使用時，必須結合使用ORDERBY子句，并且COMPUTE子句中的表達式必須與在ORDERBY后列出的子句相同或是其子集