第4章-數(shù)據(jù)庫查詢

1、數(shù)據(jù)庫技術(shù)與應(yīng)用數(shù)據(jù)庫技術(shù)與應(yīng)用教材編寫組第4章 數(shù)據(jù)庫查詢查詢概述基本查詢嵌套查詢連接查詢嵌套查詢、連接查詢2問題提出為什么要查詢數(shù)據(jù)？如何實(shí)現(xiàn)查詢？3？4.1 查詢概述4.1.1 圖形界面的菜單方式4.1.2 查詢語句SELECT 44.1 查詢概述在數(shù)據(jù)庫應(yīng)用中，最常見的操作是數(shù)據(jù)查詢，它是數(shù)據(jù)庫系統(tǒng)中最重要的功能，也是數(shù)據(jù)庫其他操作（如統(tǒng)計(jì)、插入、刪除及修改）的基礎(chǔ)。無論是創(chuàng)建數(shù)據(jù)庫、還是創(chuàng)建數(shù)據(jù)表等最終的目的都是為了使用數(shù)據(jù)，而使用數(shù)據(jù)的前提是需要從數(shù)據(jù)庫中獲取數(shù)據(jù)庫所提供的數(shù)據(jù)信息。54.1.1 圖形界面的菜單方式在SSMS圖形界面中，通過“對象資源管理器”可以直接查詢數(shù)據(jù)表中的數(shù)

2、據(jù)。64.1.2 查詢語句SELECT 數(shù)據(jù)查詢語句SELECT的基本框架是:SELECT-FROM-WHERE語法格式：SELECT select_list/*指定要選擇的列或行及其限定*/INTO new_table /*指定結(jié)果存入新表*/FROM table_source /*指定數(shù)據(jù)來源的表和視圖*/ WHERE search_condition /*指定查詢條件*/ GROUP BY group_by_expression /*指定分組表達(dá)式*/ HAVING search_condition /*指定分組統(tǒng)計(jì)條件*/ORDER BY order_expression ASC|DE

3、SC /*指定查詢結(jié)果的排序方式*/7需要哪些列從哪些表根據(jù)什么條件4.2 基本查詢4.2.1 簡單查詢4.2.2 條件查詢4.2.3 查詢結(jié)果處理84.2.1簡單查詢簡單查詢是指SELECT語句只包含SELECT子句和FROM子句的操作，涉及的對象是單表中的列，即在查詢過程對一張表的列進(jìn)行操作。語法格式：SELECT ALL | DISTINCT TOP n PERCENT select_list FROM table_name其中: ALL表示輸出所有記錄，包括重復(fù)記錄。 DISTINCT表示輸出無重復(fù)結(jié)果的記錄。 TOP n指定返回查詢結(jié)果的前n行數(shù)據(jù)。 select_list中的選項(xiàng)可

4、以是：、字段名、表達(dá)式或函數(shù)。94.2.1簡單查詢查詢?nèi)苛谢蛑付蠸ELECT ALL * FROM st_infoSELECT all st_name FROM st_Info消除重復(fù)行或定義列別名SELECT DISTINCT St_name FROM st_InfoSELECT DISTINCT st_id,St_name FROM st_InfoSELECT st_name as 姓名,st_sex AS 性別 FROM st_InfoSELECT st_name 姓名,st_sex 性別 FROM st_Info104.2.1簡單查詢限制結(jié)果集的行數(shù) 例4.5：對St_info表選

5、擇姓名、性別查詢，返回結(jié)果集中前5行。SELECT top 5 st_name AS 姓名, St_Sex as 性別 FROM st_info又如：SELECT top 3 St_id FROM s_c_InfoSELECT top 20 PERCENT St_id FROM s_c_Info/*返回結(jié)果集中前20%行*/114.2.1簡單查詢計(jì)算列值例4.6 按120分計(jì)算成績并顯示S_C_Info表中前5行學(xué)生的成績情況。SELECT TOP 5 St_ID 學(xué)號, C_No 課程編號, 成績120=Score*1.2FROM S_C_Info124.2.1簡單查詢計(jì)算列值使用聚合函數(shù)是

6、對一組值執(zhí)行計(jì)算并返回單一的值的函數(shù)常用聚合函數(shù)13函數(shù)功能函 數(shù)功能AVG()求一列數(shù)據(jù)的平均值MIN()求列中的最小值SUM()求一列數(shù)據(jù)的和MAX()求列中的最大值COUNT(*)統(tǒng)計(jì)查詢的行數(shù)？什么是聚合函數(shù)4.2.1簡單查詢例4.74.8：分別查詢St_Info表的學(xué)生總數(shù)和平均年齡查詢學(xué)生總數(shù)（使用COUNT(*) ）SELECT COUNT(*) AS 總數(shù) FROM St_Info查詢學(xué)生的平均年齡（使用AVG）SELECT AVG(YEAR(GETDATE()- YEAR(Birthdate ) AS 平均年齡 FROM St_info144.2.2 條件查詢語法格式：WHE

7、RE search_condition 條件運(yùn)算符：比較運(yùn)算邏輯運(yùn)算字符匹配運(yùn)算范圍比較運(yùn)算空值比較運(yùn)算 154.2.2 條件查詢語法格式：WHERE search_condition164.2.2 條件查詢比較運(yùn)算例4.12，查詢St_Info表中1998年以前出生的學(xué)生情況，要求列出學(xué)號、姓名、出生年份和所在班級。SELECT St_ID,St_Name,YEAR(Birthdate) 出生年份, Cl_Name FROM St_Info WHERE YEAR(Birthdate )=80 and Score9017這是一個(gè)關(guān)系表達(dá)式這是一個(gè)邏輯表達(dá)式4.2.2 條件查詢字符匹配運(yùn)算 LI

8、KE關(guān)鍵字的語法格式：match_ expression NOT LIKE pattern ESCAPE escape_character 18運(yùn)算符描述示例%包含零個(gè)或多個(gè)字符的任意字符串a(chǎn)ddress LIKE %公司% 將查找地址任意位置包含公司的所有職員_下劃線，對應(yīng)任何單個(gè)字符employee_name LIKE _海燕 將查找以“海燕”結(jié)尾的所有6個(gè)字符的名字 指定范圍（如a-f）或集合（如abcdef ）中的任何單個(gè)字符employee_name LIKE 張李王海燕 將查找張海燕、李海燕、王海燕等不屬于指定范圍或集合的任何單個(gè)字符employee_name LIKE 張李海燕

9、將查找不姓張、李的名為海燕的職員4.2.2 條件查詢例4.14:查詢st_info表中姓“張”的男學(xué)生的信息。SELECT * FROM st_info WHERE st_name LIKE 張% AND st_sex=男 其中等價(jià)的形式：WHERE LEFT(st_name,1)=張 AND st_sex=男例4.15：在St_Info表中查詢學(xué)號倒數(shù)第3個(gè)數(shù)為1，倒數(shù)第1個(gè)數(shù)在14之間的學(xué)生的學(xué)號、姓名、班級信息。SELECT St_ID, St_Name, Cl_Name FROM St_Info WHERE St_ID LIKE %1_1234194.2.2 條件查詢例4.16：在St

10、_Info表中，查詢所有“口腔”班，名叫“小玲”的學(xué)生的學(xué)號、姓名、班級信息。SELECT St_ID, St_Name, Cl_Name FROM St_Info WHERE st_name like _小玲% and Cl_Name like 口腔%例4.17：在St_Info表中，查詢學(xué)生“張好然”和“楊平娟”的信息。要求顯示學(xué)號、姓名、班級和電話號碼。注意此表中是用下劃線（_）將區(qū)號與電話號碼連接的。SELECT St_ID, St_Name, Cl_Name,Telephone FROM St_Info WHERE Telephone like %#_%ESCAPE# /* 定義#為

11、轉(zhuǎn)義字符*/204.2.2 條件查詢范圍比較運(yùn)算語法格式：expression NOT BETWEEN begin_expression AND end_expression例4.18：在St_Info表中查詢1984年出生的學(xué)生信息。SELECT * FROM St_Info WHERE Birthdate BETWEEN 1997-1-1 AND 1997-12-31214.2.2 條件查詢例4.13：在st_info表中，查詢年齡在2021歲之間的學(xué)生信息。SELECT * FROM st_info WHERE YEAR(GETDATE()-YEAR(Birthdate) BETWEEN

12、 20 AND 2122 此句的等價(jià)形式是什么？4.2.2 條件查詢?nèi)粢樵兊氖橇斜碇械臄?shù)據(jù)，可以使用IN關(guān)鍵字 語法格式：expression NOT IN (expression ,.n ) 例4.20：在st_info表中，“法學(xué)1601”、“法學(xué)1701”和“材料科學(xué)1701”班的學(xué)生信息。SELECT * FROM St_Info WHERE Cl_Name IN (法學(xué)1601,法學(xué)1701,材料科學(xué)1701)234.2.2 條件查詢空值比較運(yùn)算例4.21：對st_info表，查詢所有Telephone為空值的學(xué)生的信息。SELECT * FROM st_info WHERE Te

13、lephone IS NULL24 可以寫成：Telephone = NULL?空值表示值未知。空值不同于空白或零值。沒有兩個(gè)相等的空值。4.2.3 查詢結(jié)果處理排序輸出（ORDER BY）語法格式：ORDER BY order_by_expression1ASC|DESC,order_by_expression2ASC|DESC ,例4.22：對st_info表，按性別順序列出學(xué)生的信息，性別相同的再按年齡由小到大排序。SELECT * FROM st_info ORDER BY st_sex, BirthDate DESC254.2.3 查詢結(jié)果處理重定向輸出（INTO）語法格式：INTO

14、 new_table例4.23：對s_c_info表，查詢選修“大學(xué)計(jì)算機(jī)基礎(chǔ)”（課程號為“9710011”）課程的所有學(xué)生信息，并將結(jié)果存入newstudent表中。SELECT st_id 學(xué)號, c_no 大學(xué)計(jì)算機(jī)基礎(chǔ), score 成績 INTO newstudentFROM s_c_info WHERE c_no= 9710011264.2.3 查詢結(jié)果處理輸出合并（UNION）語法格式： UNION ALL 例4.24：對c_info表，列出課程編號為“9710011”或“9720033”的課程名稱和學(xué)分。 SELECT c_name,c_credit FROM c_info W

15、HERE c_no=9710011 UNION SELECT c_name,c_credit FROM c_info WHERE c_no=9720033274.2.3 查詢結(jié)果處理分組統(tǒng)計(jì)（GROUP BY）與篩選（HAVING）語法格式：GROUP BY group_by_expression1 ,group_by_expression2,例4.25：對st_info表，分別統(tǒng)計(jì)男女學(xué)生人數(shù)。SELECT st_sex,COUNT(st_sex) FROM st_info GROUP BY st_sex例4.21：對s_c_info表，查詢平均成績大于80的課程編號和平均成績。SELECT

16、 c_no, AVG(score) AS 平均成績 FROM s_c_info GROUP BY c_no HAVING AVG(score)=8028WHERE與 HAVING兩者的區(qū)別?4.2.3 查詢結(jié)果處理使用COMPUTE和COMPUTE BY子句匯總語法格式：COMPUTE row_aggregate(column_name) , row_aggregate(column_name) .BY column_name , column_name. . . 例4.28：列出St_Info表中“材料科學(xué)1701”班學(xué)生的年齡及平均年齡（即年齡的明細(xì)行和匯總行）。SELECT St_ID,

17、YEAR(GETDATE()- YEAR(Birthdate) AS 年齡 FROM St_InfoWHERE Cl_Name = 材料科學(xué)1701 ORDER BY St_ID COMPUTE AVG(YEAR(GETDATE()- YEAR(Birthdate)294.2.3 查詢結(jié)果處理例4.29：對St_Info表中“材料科學(xué)1701”和“口腔（七）1701”班學(xué)生的年齡，生成分組匯總行和明細(xì)行 。SELECT St_ID,YEAR(GETDATE()- YEAR(Birthdate) AS 年齡 FROM St_InfoWHERE Cl_Name = 材料科學(xué)1701 OR Cl_N

18、ame =口腔（七）1701 ORDER BY Cl_Name COMPUTE SUM(YEAR(GETDATE()- YEAR(Birthdate) BY Cl_Name30注意 ：COMPUTE和COMPUTE BY區(qū)別4.3 嵌套查詢4.3.1 單值嵌套查詢4.3.2 多值嵌套查詢314.3 嵌套查詢在一個(gè)SELECT 語句的WHERE 子句或HAVING 子句中嵌套另一個(gè)SELECT 語句的查詢稱為嵌套查詢，又稱子查詢。嵌套查詢的類型單值嵌套查詢多值嵌套查詢324.3.1 單值嵌套查詢子查詢的返回結(jié)果是一個(gè)值的嵌套查詢稱為單值嵌套查詢。例4.30：對student_db數(shù)據(jù)庫，查詢選修

19、“大學(xué)計(jì)算機(jī)基礎(chǔ)”的所有學(xué)生的學(xué)號和成績。SELECT st_id,score FROM s_c_info WHERE c_no=(SELECT c_no FROM c_info WHERE c_name=大學(xué)計(jì)算機(jī)基礎(chǔ))33 內(nèi)查詢的結(jié)果作為外查詢的條件4.3.2 多值嵌套查詢子查詢的返回結(jié)果是一列值的嵌套查詢稱為多值嵌套查詢。若某個(gè)子查詢的返回值不止一個(gè)，則必須在WHERE子句中指明如何使用這些返回值。通常使用條件運(yùn)算符： ANY ALL IN34ALL 表示大于每一個(gè)值；即大于最大值。例如，ALL (1, 2, 3) 表示大于 3。ANY 表示至少大于一個(gè)值，即大于最小值。例如， ANY

20、 (1, 2, 3) 表示大于 1。 4.3.2 多值嵌套查詢使用ANY運(yùn)算符例4.31：對Student數(shù)據(jù)庫，查詢選修“9710011”即“大學(xué)計(jì)算機(jī)基礎(chǔ)”課程的學(xué)生的成績比選修“29000011”即“體育”課程的學(xué)生的最低成績高的學(xué)生的學(xué)號和成績 。SELECT st_id,score FROM s_c_info WHERE c_no=9710011 and score ANY (SELECT score FROM s_c_info WHERE c_no=29000011)354.3.2 多值嵌套查詢使用ALL運(yùn)算符ALL運(yùn)算符指定子查詢結(jié)果集中每個(gè)值都滿足比較條件時(shí)返回TURE，否則返

21、回FALSE。 例4.32：對Student數(shù)據(jù)庫，列出選修“29000011”即“體育”的學(xué)生的成績比選修“9710011”即“大學(xué)計(jì)算機(jī)基礎(chǔ)”的學(xué)生的最高成績還要高的學(xué)生的學(xué)號和成績 。SELECT st_id, score FROM s_c_info WHERE c_no=29000011 and score ALL ( SELECT score FROM s_c_info WHERE c_no=9710011 )364.3.2 多值嵌套查詢使用IN運(yùn)算符IN是屬于的意思，等價(jià)于“=ANY”，即等于子查詢中任何一個(gè)值。例4.33：對Student數(shù)據(jù)庫，列出選修“29000011”即“體

22、育”或選修“9710011”即“大學(xué)計(jì)算機(jī)基礎(chǔ)”的學(xué)生學(xué)號和成績 。SELECT st_id,score FROM s_c_info WHERE c_no IN ( SELECT c_no FROM c_info WHERE c_name=大學(xué)計(jì)算機(jī)基礎(chǔ) OR c_name=體育)374.4 連接查詢4.4.1 自連接4.4.2 內(nèi)連接4.4.3 外連接4.4.4 交叉連接384.4 連接查詢連接查詢的概念同時(shí)涉及多個(gè)表的查詢稱為連接查詢 。可根據(jù)各個(gè)表之間的邏輯關(guān)系從兩個(gè)或多個(gè)表中檢索數(shù)據(jù)。連接查詢的類型 自連接(Self join) 內(nèi)連接（Inner join） 外連接（Outer jo

23、in） 交叉聯(lián)接（Cross join） 394.4 連接查詢連接查詢的建立在WHERE子句中建立在連接FROM子句中建立連接字段連接謂詞中的列名稱為連接字段。連接條件中的各連接字段類型必須是可比的， 但不必是相同的。連接的結(jié)果一個(gè)表中的行和與另外一個(gè)表中的行匹配連接。表中的數(shù)據(jù)決定了如何對這些行進(jìn)行組合。從每一個(gè)表中選取一行，根據(jù)這些列的值是否相同，組合方式分為一對一、多對一和多對多的關(guān)系。404.4 連接查詢在SELECT語句的WHERE子句中建立連接當(dāng)需要對兩個(gè)或多個(gè)表連接時(shí)，可以指定連接的列，在WHERE子句中給出連接條件，在FROM子句中指定要連接的表。在FROM子句中建立連接（推薦

24、使用）在FROM子句中指出連接時(shí)有助于將連接操作與WHERE子句中的搜索條件區(qū)分開來。FROM子句連接的語法格式：FROM join_table join_type JOIN join_table ON join_condition414.4.1 自連接自連接(Self join)是指一個(gè)表自己與自己建立連接，也稱為自身連接。例4.35：查詢選修“大學(xué)計(jì)算機(jī)基礎(chǔ)”（9710011）課程的成績高于學(xué)號為“2001160308”學(xué)生的成績的所有學(xué)生信息，并按成績從高到低排列。（在WHERE子句中建立連接）SELECT x.* /*將成績表s_c_info 分別取別名為x和y*/FROM s_c_i

25、nfo x , s_c_info yWHERE x.C_No=9710011 And x.Scorey.Score And y.St_ID=2001160308 And y.C_No=9710011 ORDER BY x.score DESC42在FROM子句中指定要連接的表，在WHERE子句中給出連接條件。4.4.2 內(nèi)連接內(nèi)連接（Inner join）使用比較運(yùn)算符進(jìn)行表間某(些)列數(shù)據(jù)的比較操作，并列出這些表中與連接條件相匹配的數(shù)據(jù)行。內(nèi)連接類型等值連接不等值連接434.4.2 內(nèi)連接等值連接在連接條件中使用等號“=”運(yùn)算符比較被連接列的列值，按對應(yīng)列的共同值將一個(gè)表中的記錄與另一個(gè)表中

26、的記錄相連接，包括其中的重復(fù)列，這種連接稱為等值連接。例4.36：在Student數(shù)據(jù)庫中，查詢所有選課學(xué)生的學(xué)號、所選課程的名稱和成績。（在WHERE子句中建立連接）SELECT x.st_id, y.c_name, x.scoreFROM S_C_Info x, C_Info yWHERE x.c_no = y.c_no44在WHERE子句中用“=”形式進(jìn)行連接。4.4.2 內(nèi)連接等值連接例4.38：在Student數(shù)據(jù)庫中，查詢學(xué)生的選課情況。要求列出選課表s_c_info中的所有列，學(xué)生信息表st_info中的學(xué)生姓名st_name列。SELECT b.*FROM St_Info a

27、INNER JOIN S_C_Info b ON a.St_ID =b.St_IDSELECT a.st_name, b.* FROM st_info a INNER JOIN s_c_info b ON a.st_id =b.st_id當(dāng)連接的兩個(gè)表，兩個(gè)連接列的值完全相同，則兩個(gè)表連接相當(dāng)于一對一的關(guān)系。45在FROM子句中指出連接4.4.2 內(nèi)連接兩種查詢操作比較46連接的結(jié)果集為1：1關(guān)系等值連接的特例自然連接4.4.2 內(nèi)連接不等值連接使用的運(yùn)算符包括：、=、=、!和。例4.40：對例4.35中要求的查詢，使用如下語句實(shí)現(xiàn)。SELECT a.st_id, a.score FROM s

28、_c_info a INNER JOIN s_c_info b ON a.score b.score AND a.c_no=b.c_no WHERE a.C_No=9710011 AND b.St_ID=2001160308ORDER BY a.score DESC47在FROM子句中指出連接4.4.3 外連接外連接（Outer join）左外連接（Left outer join）右外連接(Right outer join)全外連接(Full outer join)484.4.3 外連接左外連接使用LEFT OUTER JOIN關(guān)鍵字進(jìn)行連接。左外連接保留了第一個(gè)表的所有行，但只包含第二個(gè)表與

29、第一個(gè)表匹配的行。第二個(gè)表相應(yīng)的空行被放入NULL值。例4.41：st_info表左外連接s_c_info表，列出女同學(xué)的信息SELECT a.St_ID, St_Name, St_Sex C_No, Score FROM St_Info a LEFT OUTER JOIN S_C_Info b ON a.St_ID = b.St_IDWHERE St_Sex =女494.4.3 外連接50查詢前左外連接保留了第一個(gè)表的所有行，但只包含第二個(gè)表與第一表匹配的行。第二個(gè)表相應(yīng)的空行被放入NULL值。左外連接，查詢后一對多關(guān)系4.4.3 外連接右外連接右外連接使用RIGHT OUTER JOIN關(guān)

30、鍵字進(jìn)行連接。右外連接通過右向外連接引用右表的所有行。例4.42：st_info表右外連接s_c_info表。為了說明方便，先在s_c_info表中插入一條選課信息，此信息的學(xué)號在st_info表中不存在。INSERT INTO s_c_info (st_id,c_no,score) VALUES (2001060155, 29000011,100)goSELECT a.st_id, a.st_name,b.c_no, b.score FROM st_info a RIGHT OUTER JOIN s_c_info b ON a.st_id = b.st_id514.4.3 外連接52SELECT a.st_id, a.st_name,b.c_no, b.score FROM st_info a RIGHT OUTER JOIN s_c_info b ON a.st_id = b.st_id右外連接保留了第二個(gè)表的所有行，但只包含第一個(gè)表