課件數(shù)據(jù)庫的查詢

1、數(shù)據(jù)庫的查詢四川大學計算機（軟件）學院 龔勛1Agenda1 SELECT語句（SELECT Statement）2 嵌套子查詢 Subqueries3 連接4 并、交、差Union, Intersect, and Difference (Except)2Notice:Order of the clauses cannot be changedOnly SELECT and FROM are mandatorySELECT和FROM 是必須的，其它的子句可選可套Aggregates 聚集函數(shù)用于計算1 SELECT語句（SELECT Statement）3功能：能用一個語句完成選擇、投影和連接

2、WHERE condition形式： SELECT DISTINCT | ALL * | columnExpression AS newName ,. FROM TableName alias , .GROUP BY columnList HAVING conditionORDER BY columnList1 SELECT語句（SELECT Statement）SELECTSpecifies which columns are toappear in outputFROMSpecifies table(s) to be usedWHEREFilters rows 選行GROUP BYForm

3、s groups of rows with samecolumn valueHAVINGFilters groups subject to somecondition 選組ORDER BY Specifies the order of the output41.1 SELECT和FROM1.1.1 選擇所有列 Retrieve All Columns1.1.2 選擇指定列1.1.3 計算列51.1.1 選擇所有列 Retrieve All Columns查看Staff表中數(shù)據(jù)例子：List full details of all staff.不按原表列的順序查詢SELECT staffNo,s

4、taffFName,staffLName,position, sex,DOB,salary,branchNo,Sup_staffNo FROM Staff按原表列的順序查詢SELECT staffNo,branchNo,Sup_staffNo,staffFName, staffLName,position,sex,DOB,salaryFROM Staff用*查詢SELECT *FROM Staff6查看Staff表中數(shù)據(jù)71、右擊表“Staff”2、在彈出的下拉列表中點擊“編輯前200行”3、可見表Staff的數(shù)據(jù)4、外部關鍵字不按原表列的順序查詢84、結果按查詢語句中各列的順序展示，非原表S

5、taff各列的順序1、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入查詢所有職員完整信息語句：SELECT staffNo,staffFName,staffLName,position,sex,DOB,salary,branchNo,Sup_staffNo FROM Staff5、外部關鍵字按原表列的順序查詢94、結果按查詢語句中各列的順序展示，同原表Staff各列的順序1、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入查詢所有職員完整信息語句：SELECT staffNo,branchNo,Sup_staffNo,staffFName,staffLName

6、,position,sex,DOB,salaryFROM Staff5、外部關鍵字用*查詢104、結果按查詢語句中各列的順序展示，同原表Staff各列的順序1、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入查詢所有職員完整信息語句：SELECT * FROM Staff5、外部關鍵字1.1.2 選擇指定列11List the property numbers of all properties that have been viewed.默認保留重復值：選擇指定列SELECT propertyNoFROM Viewing保留重復值：用ALL選擇指定列SELECT ALL pr

7、opertyNoFROM Viewing不保留重復值：用DISTINCT選擇指定列SELECT DISTINCT propertyNoFROM Viewing查看Viewing表中數(shù)據(jù)121、右擊表“Viewing”2、在彈出的下拉列表中點擊“編輯前200行”3、可見表Viewing的數(shù)據(jù)4、被看過的房產(chǎn)編號默認方式選擇指定列131、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入查詢被看房產(chǎn)編號的語句：SELECT propertyNoFROM Viewing4、查詢結果只展示房產(chǎn)編號propertyNo這一列，且包含重復的值5、重復的值用ALL選擇指定列141、點擊“新建查

8、詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入查詢被看房產(chǎn)編號的語句：SELECT ALL propertyNoFROM Viewing5、重復的值4、查詢結果只展示房產(chǎn)編號propertyNo這一列，且包含重復的值，同于不用ALL的情況，說明默認是保留重復值用DISTINCT選擇指定列151、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入查詢被看房產(chǎn)編號的語句：SELECT DISTINCT propertyNoFROM Viewing4、查詢結果只展示房產(chǎn)編號propertyNo這一列，且不包含重復的值1.1.3 計算列計算列不重命名列名SELECT staffNo

9、,staffFName,staffLName,salary/12FROM Staff計算列并重名列名SELECT staffNo,staffFName,staffLName,salary/12 AS monthlySalaryFROM Staff16Produce list of monthly salaries for all staff, showing staff number, first/last name, and salary.查看Staff表中數(shù)據(jù)171、右擊表“Staff”2、在彈出的下拉列表中點擊“編輯前200行”3、可見表Staff的數(shù)據(jù)4、員工工資計算列不重名列名184

10、、結果只顯示工號、姓名（staffFName和staffLName）、月工資4列1、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入計算月工資的語句：SELECT staffNo,staffFName,staffLName,salary/12 FROM Staff5、計算的月工資，由于Staff表本無此列，所以無列名計算列并重名列名194、結果只顯示工號、姓名（staffFName和staffLName）、月工資4列1、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入計算月工資的語句：SELECT staffNo,staffFName,staffLName,sa

11、lary/12 AS monthlySalary FROM Staff5、計算的月工資，并指定新列名為monthlySalary1.2 WHERE子句(WHERE clause)只想指定檢索某些行：用WHERE子句形式： WHERE condition5個基本的條件運算：比較：1.2.1 簡單的比較：=， （或 !=），=1.2.2 復雜的比較：用邏輯運算符其優(yōu)先級為：NOT AND和OR1.2.3 范圍： BETWEEN / NOT BETWEEN1.2.4 成員關系：IN / NOT IN1.2.5 模式匹配： LIKE / NOT LIKE 1.2.6 空：IS NULL / IS NO

12、T NULL201.2.1 簡單比較運算21List all staff with a salary greater than 100000.SELECT staffNo,staffFName,staffLName,position,salaryFROM StaffWHERE salary100000執(zhí)行簡單比較運算語句224、結果只顯示工資大于100000的員工信息1、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入查詢工資大于100000的語句：SELECT staffNo,staffFName,staffLName,position,salary FROM StaffWH

13、ERE salary100000List addresses of all branch offices in London or Glasgow.SELECT *FROM BranchWHERE branchCity=London OR branchCity=Glasgow1.2.2 復雜比較運算23查看Branch表中數(shù)據(jù)241、右擊表“Branch”2、在彈出的下拉列表中點擊“編輯前200行”3、可見表Branch的數(shù)據(jù)4、各分公司所在城市： London、Glasgow、 Bristol、 Aberdeen執(zhí)行復雜比較運算語句254、結果只顯示在London或Glasgow的分公司的信

14、息1、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入查詢在London或Glasgow的分公司的語句：SELECT *FROM BranchWHERE branchCity=London OR branchCity=Glasgow1.2.3 查詢條件是范圍26BETWEEN test includes the endpoints of range. 含范圍的端點List all staff with a salary between 50000 and 100000.查詢條件是范圍：SELECT staffNo,staffFName,staffLName,position,s

15、alaryFROM StaffWHERE salary BETWEEN 50000 AND 100000 等價的復雜比較運算：SELECT staffNo,staffFName,staffLName,position,salaryFROM StaffWHERE salary=50000 AND salary=50000 AND salary13504、結果顯示租金大于1350的房產(chǎn)數(shù)量是5原因：COUNT會計算相同的值5、 PropertyForRent表本無統(tǒng)計房產(chǎn)數(shù)量這一列，所以結果無列名查看Viewing表中數(shù)據(jù)481、右擊表“Viewing”2、在彈出的下拉列表中點擊“編輯前200行”

16、3、可見表Viewing的數(shù)據(jù)4、在2011年5月到7月被看過的房產(chǎn)記錄是3條，但只有2個房產(chǎn)PG4和PA141.4.2 消除重復計算數(shù)量并重命名491、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入計算被看時間在2011年5月到7月的房產(chǎn)數(shù)量并重命名的語句：SELECT COUNT(DISTINCT propertyNo) AS myCountFROM ViewingWHERE viewDate BETWEEN 2011-5-1 AND 2011-7-31 4、結果顯示被看時間在2011年5月到7月的房產(chǎn)數(shù)量是2原因：DISTINCT去掉了重復值再讓COUNT計算5、 指定

17、的新列名myCount查看Staff表中數(shù)據(jù)501、右擊表“Staff”2、在彈出的下拉列表中點擊“編輯前200行”3、可見表Staff的數(shù)據(jù)4、有兩個員工工資相同，都是1900001.4.3 計算最大、最小、平均和總數(shù)并重命名511、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入計算員工工資的最大、最小和平均數(shù)并重命名的語句：SELECT MIN(salary) AS myMin,MAX(salary) AS myMax, AVG(salary) AS myAvg,SUM(salary) AS mySum FROM Staff4、結果顯示員工工資的最大數(shù)是200000、最小

18、數(shù)是600005、結果顯示員工工資的平均數(shù)是144444.4444，總數(shù)是1300000原因：不消除重復的值一起計算1.4.4 去除重復計算最大、最小、平均和總數(shù)并重命名521、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入計算員工工資的最大、最小和平均數(shù)并重命名的語句：SELECT MIN(DISTINCT salary) AS myMin, MAX(DISTINCT salary) AS myMax, AVG(DISTINCT salary) AS myAvg, SUM(DISTINCT salary) AS mySum FROM Staff4、結果顯示員工工資仍然是最大

19、數(shù)200000、最小數(shù)60000原因：消除重復的值對計算最大最小值沒影響5、結果顯示員工工資的平均數(shù)是138750.00，總數(shù)是1110000.00原因：消除重復的值影響平均數(shù)和總數(shù)的計算1.5 分組 Grouping和選組HAVING分組的應用：Use GROUP BY clause to get sub-totals 產(chǎn)生部分和 選組的應用：HAVING clause is designed for use with GROUP BY to restrict groups that appear in final result table. 和WHERE的區(qū)別： WHERE選一行 HAVI

20、NG選一組執(zhí)行的順序：FROMWHEREGROUPHAVINGORDER BY531.5 分組 Grouping和選組HAVING1.5.1 說明執(zhí)行順序的原因1.5.2 分組的例子：Find number of staff in each branch and their total salaries.分不同分公司算職員數(shù)量和工資總和1.5.3 選組的例子：For each branch with more than 1 member of staff, find number of staff in each branch and sum of their salaries.541.5.1

21、 說明執(zhí)行順序的原因55不排序1.5.1 說明執(zhí)行順序的原因56按增序排序1.5.1 說明執(zhí)行順序的原因57按降序排序查看Staff表中數(shù)據(jù)581、可見分公司B001有員工1名，工資總和2000002、可見分公司B002有員工5名，工資總和6600003、可見分公司B003有員工3名，工資總和4400001.5.2 執(zhí)行分組聚集計算語句591、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入根據(jù)不同分公司分別計算員工總數(shù)和工資總數(shù)的語句：SELECT branchNo,COUNT(staffNo) AS myCount,SUM(salary) AS mySumFROM Staf

22、fGROUP BY branchNoORDER BY branchNo 4、結果顯示分公司B001有員工1名，工資總和2000006、顯示分公司B003有員工3名，工資總和4400005、顯示分公司B002有員工5名，工資總和6600001.5.3 執(zhí)行分組選組聚集計算的語句601、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入根據(jù)不同分公司分別計算員工總數(shù)和工資總數(shù)并選出員工人數(shù)大于1的分公司的語句：SELECT branchNo,COUNT(staffNo) AS myCount,SUM(salary) AS mySum FROM StaffGROUP BY branch

23、NoHAVING COUNT(staffNo)1ORDER BY branchNo 5、顯示分公司B003有員工3名，工資總和4400004、顯示分公司B002有員工5名，工資總和6600002 嵌套子查詢 SubqueriesSubquery（nested query）子查詢/嵌套查詢：將一個SELECT子句嵌套用于外部SELECT語句中可放入WHERE，HAVING，INSERT，UPDATE和 DELETE子句中.612 嵌套子查詢 SubqueriesList staff who work in branch at 163 Main St.2.1 常見嵌套子查詢2.2 用EXISTS實

24、現(xiàn)嵌套子查詢嵌套聚集函數(shù)：List all staff whose salary is greater than the average salary.2.3 沒在SELECT中嵌套聚集函數(shù)：出錯2.4 在SELECT中嵌套聚集函數(shù)：正確2.5 用IN實現(xiàn)嵌套子查詢：List clients registered by staffs at 163 Main St.622 嵌套子查詢 SubqueriesFind staff whose salary is larger than salary of at least one member of staff at branch B003.2.6

25、用SOME實現(xiàn)嵌套子查詢2.7 用ANY實現(xiàn)嵌套子查詢：與用SOME實現(xiàn)等價Find staff whose salary is larger than salary of every member of staff at branch B003.2.8 用ALL實現(xiàn)嵌套子查詢63查看Branch表中數(shù)據(jù)64地址在163 Main 的分公司是B003查看Staff表中數(shù)據(jù)65在分公司B003工作的員工有3名：S007、 S008、 S009查看Registers表中數(shù)據(jù)66在分公司B003工作的員工負責注冊的客戶：只有兩名員工S007和 S008分別負責注冊了一位客戶2.1 執(zhí)行嵌套子查詢語句

26、671、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入找分公司在163 Main St的員工的語句：SELECT staffNo,staffFName,staffLName,positionFROM StaffWHERE branchNo=(SELECT branchNo FROM Branch WHERE branchStreet=163 Main St) 4、結果顯示在分公司B003工作的員工有3名：S007、 S008、 S009（對比用EXISTS實現(xiàn)嵌套子查詢）2.2 用EXISTS實現(xiàn)嵌套子查詢681、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸

27、入找分公司在163 Main St的員工的語句：SELECT staffNo,staffFName,staffLName,positionFROM Staff sWHERE EXISTS (SELECT * FROM Branch b WHERE s.branchNo=b.branchNo AND branchStreet=163 Main St) 4、結果同前一種情況2.3 沒在SELECT中嵌套聚集函數(shù)691、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入找所有工資高于平均工資的員工并計算平均工資的語句：SELECT staffNo,staffFName,staffLNa

28、me,position,salary,AVG(salary) AS myAvg FROM StaffWHERE salary(SELECT AVG(salary) FROM Staff) 4、顯示出錯原因：AVG(salary)沒嵌套在SELECT中2.4 在SELECT中嵌套聚集函數(shù)701、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入找所有工資高于平均工資的員工并計算平均工資的語句：SELECT staffNo,staffFName,staffLName,position,salary,(SELECT AVG(salary) FROM Staff) AS myAvg FR

29、OM StaffWHERE salary(SELECT AVG(salary) FROM Staff) 4、結果顯示所有工資大于平均工資的員工信息2.5 用IN實現(xiàn)嵌套子查詢711、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入找在分公司B003工作的員工負責注冊的客戶的語句：SELECT staffNo,branchNo,clientNo,dataJoinedFROM RegistersWHERE staffNo IN (SELECT staffNo FROM Staff WHERE branchNo= (SELECT branchNo FROM Branch WHERE

30、branchStreet=163 Main St) 4、結果顯示在分公司B003工作員工負責注冊的客戶查看Staff表中數(shù)據(jù)72在分公司B003工作的員工工資是190000、 150000和 100000，最低工資是100000，最高工資是190000比分公司B003中最低工資100000高的所有員工有6人比分公司B003中最高工資190000高的所有員工有1人2.6 用SOME實現(xiàn)嵌套子查詢731、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入找所有比分公司B003中至少一位員工工資高的員工的語句：SELECT staffNo,staffFName,staffLName,p

31、osition,salary FROM StaffWHERE salarySOME (SELECT salary FROM Staff WHERE branchNo =B003) 4、結果顯示6人比分公司B003最低工資100000高（對比用ANY）2.7 用ANY實現(xiàn)嵌套子查詢741、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入找所有比分公司B003中至少一位員工工資高的員工的語句：SELECT staffNo,staffFName,staffLName,position,salary FROM StaffWHERE salaryANY (SELECT salary FR

32、OM Staff WHERE branchNo =B003) 4、結果同用SOME2.8 用ALL實現(xiàn)嵌套子查詢751、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入找所有比分公司B003中全體員工工資高的員工的語句：SELECT staffNo,staffFName,staffLName,position,salary FROM StaffWHERE salaryALL (SELECT salary FROM Staff WHERE branchNo =B003) 4、結果顯示只有一位員工比分公司B003中全體員工工資高3 連接3.1 相等連接：List branches

33、and properties in same city.3.2 左外部連接：List branches and properties in same city along with any unmatched branches.3.3 右外部連接：List branches and properties in same city and any unmatched properties.3.4 全外部連接：List branches and properties in same city and any unmatched branches or properties.763.1 相等連接77

34、1、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入顯示房產(chǎn)及所屬分公司的信息的語句：SELECT b.branchNo,b.branchStreet,b.branchCity,pertyNo,pertyStreet, pertyCity,p.type,p.rooms,pertyRentFROM Branch b,PropertyForRent pWHERE b.branchCity= pertyCity 4、顯示表Branch的信息）5、顯示表PropertyForRent的信息6、值重復的列b.branchCity和 p.p

35、ropertyCity 3.2 左外部連接781、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入顯示房產(chǎn)及所屬分公司的信息的語句：SELECT b.branchNo,b.branchStreet,b.branchCity,b.branchPostcode,pertyNo,p.pOwnerNo, p.branchNo,p.bOwnerNo,pertyStreet,pertyCity,p.type,p.rooms,pertyRent FROM Branch b LEFT JOIN PropertyForRent p ON b.branchCity= pertyCity 4、不能參加連接的記錄左邊保留5、不能參加連接的記錄右邊補空值3.3 右外部連接791、點擊“新建查詢（N）”3、點擊“執(zhí)行（X）”2、在查詢窗口中輸入顯示房產(chǎn)及所屬分公司的信息的語句：SELECT b.branchNo,b.branchStreet,b.branchCity,b.branchPostcode,pertyNo,p.pOwnerNo, p.branchNo,p.bOwnerNo,pertyStreet,pertyCity,p.type,p.rooms,per