课件数据库的查询

1、数据库的查询四川大学计算机（软件）学院 龚勋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 聚集函数用于计算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表中数据例子：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表中数据71、右击表“Staff”2、在弹出的下拉列表中点击“编辑前200行”3、可见表Staff的数据4、外部关键字不按原表列的顺序查询84、结果按查询语句中各列的顺序展示，非原表S

5、taff各列的顺序1、点击“新建查询（N）”3、点击“执行（X）”2、在查询窗口中输入查询所有职员完整信息语句：SELECT staffNo,staffFName,staffLName,position,sex,DOB,salary,branchNo,Sup_staffNo FROM Staff5、外部关键字按原表列的顺序查询94、结果按查询语句中各列的顺序展示，同原表Staff各列的顺序1、点击“新建查询（N）”3、点击“执行（X）”2、在查询窗口中输入查询所有职员完整信息语句：SELECT staffNo,branchNo,Sup_staffNo,staffFName,staffLName

6、,position,sex,DOB,salaryFROM Staff5、外部关键字用*查询104、结果按查询语句中各列的顺序展示，同原表Staff各列的顺序1、点击“新建查询（N）”3、点击“执行（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表中数据121、右击表“Viewing”2、在弹出的下拉列表中点击“编辑前200行”3、可见表Viewing的数据4、被看过的房产编号默认方式选择指定列131、点击“新建查询（N）”3、点击“执行（X）”2、在查询窗口中输入查询被看房产编号的语句：SELECT propertyNoFROM Viewing4、查询结果只展示房产编号propertyNo这一列，且包含重复的值5、重复的值用ALL选择指定列141、点击“新建查

8、询（N）”3、点击“执行（X）”2、在查询窗口中输入查询被看房产编号的语句：SELECT ALL propertyNoFROM Viewing5、重复的值4、查询结果只展示房产编号propertyNo这一列，且包含重复的值，同于不用ALL的情况，说明默认是保留重复值用DISTINCT选择指定列151、点击“新建查询（N）”3、点击“执行（X）”2、在查询窗口中输入查询被看房产编号的语句：SELECT DISTINCT propertyNoFROM Viewing4、查询结果只展示房产编号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表中数据171、右击表“Staff”2、在弹出的下拉列表中点击“编辑前200行”3、可见表Staff的数据4、员工工资计算列不重名列名184

10、、结果只显示工号、姓名（staffFName和staffLName）、月工资4列1、点击“新建查询（N）”3、点击“执行（X）”2、在查询窗口中输入计算月工资的语句：SELECT staffNo,staffFName,staffLName,salary/12 FROM Staff5、计算的月工资，由于Staff表本无此列，所以无列名计算列并重名列名194、结果只显示工号、姓名（staffFName和staffLName）、月工资4列1、点击“新建查询（N）”3、点击“执行（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 复杂的比较：用逻辑运算符其优先级为：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执行简单比较运算语句224、结果只显示工资大于100000的员工信息1、点击“新建查询（N）”3、点击“执行（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表中数据241、右击表“Branch”2、在弹出的下拉列表中点击“编辑前200行”3、可见表Branch的数据4、各分公司所在城市： London、Glasgow、 Bristol、 Aberdeen执行复杂比较运算语句254、结果只显示在London或Glasgow的分公司的信

14、息1、点击“新建查询（N）”3、点击“执行（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的房产数量是5原因：COUNT会计算相同的值5、 PropertyForRent表本无统计房产数量这一列，所以结果无列名查看Viewing表中数据481、右击表“Viewing”2、在弹出的下拉列表中点击“编辑前200行”

16、3、可见表Viewing的数据4、在2011年5月到7月被看过的房产记录是3条，但只有2个房产PG4和PA141.4.2 消除重复计算数量并重命名491、点击“新建查询（N）”3、点击“执行（X）”2、在查询窗口中输入计算被看时间在2011年5月到7月的房产数量并重命名的语句：SELECT COUNT(DISTINCT propertyNo) AS myCountFROM ViewingWHERE viewDate BETWEEN 2011-5-1 AND 2011-7-31 4、结果显示被看时间在2011年5月到7月的房产数量是2原因：DISTINCT去掉了重复值再让COUNT计算5、 指定

17、的新列名myCount查看Staff表中数据501、右击表“Staff”2、在弹出的下拉列表中点击“编辑前200行”3、可见表Staff的数据4、有两个员工工资相同，都是1900001.4.3 计算最大、最小、平均和总数并重命名511、点击“新建查询（N）”3、点击“执行（X）”2、在查询窗口中输入计算员工工资的最大、最小和平均数并重命名的语句：SELECT MIN(salary) AS myMin,MAX(salary) AS myMax, AVG(salary) AS myAvg,SUM(salary) AS mySum FROM Staff4、结果显示员工工资的最大数是200000、最小

18、数是600005、结果显示员工工资的平均数是144444.4444，总数是1300000原因：不消除重复的值一起计算1.4.4 去除重复计算最大、最小、平均和总数并重命名521、点击“新建查询（N）”3、点击“执行（X）”2、在查询窗口中输入计算员工工资的最大、最小和平均数并重命名的语句：SELECT MIN(DISTINCT salary) AS myMin, MAX(DISTINCT salary) AS myMax, AVG(DISTINCT salary) AS myAvg, SUM(DISTINCT salary) AS mySum FROM Staff4、结果显示员工工资仍然是最大

19、数200000、最小数60000原因：消除重复的值对计算最大最小值没影响5、结果显示员工工资的平均数是138750.00，总数是1110000.00原因：消除重复的值影响平均数和总数的计算1.5 分组 Grouping和选组HAVING分组的应用：Use GROUP BY clause to get sub-totals 产生部分和 选组的应用：HAVING clause is designed for use with GROUP BY to restrict groups that appear in final result table. 和WHERE的区别： WHERE选一行 HAVI

20、NG选一组执行的顺序：FROMWHEREGROUPHAVINGORDER BY531.5 分组 Grouping和选组HAVING1.5.1 说明执行顺序的原因1.5.2 分组的例子：Find number of staff in each branch and their total salaries.分不同分公司算职员数量和工资总和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、 说明执行顺序的原因55不排序1.5.1 说明执行顺序的原因56按增序排序1.5.1 说明执行顺序的原因57按降序排序查看Staff表中数据581、可见分公司B001有员工1名，工资总和2000002、可见分公司B002有员工5名，工资总和6600003、可见分公司B003有员工3名，工资总和4400001.5.2 执行分组聚集计算语句591、点击“新建查询（N）”3、点击“执行（X）”2、在查询窗口中输入根据不同分公司分别计算员工总数和工资总数的语句：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 执行分组选组聚集计算的语句601、点击“新建查询（N）”3、点击“执行（X）”2、在查询窗口中输入根据不同分公司分别计算员工总数和工资总数并选出员工人数大于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、现嵌套子查询嵌套聚集函数：List all staff whose salary is greater than the average salary.2.3 没在SELECT中嵌套聚集函数：出错2.4 在SELECT中嵌套聚集函数：正确2.5 用IN实现嵌套子查询：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实现嵌套子查询2.7 用ANY实现嵌套子查询：与用SOME实现等价Find staff whose salary is larger than salary of every member of staff at branch B003.2.8 用ALL实现嵌套子查询63查看Branch表中数据64地址在163 Main 的分公司是B003查看Staff表中数据65在分公司B003工作的员工有3名：S007、 S008、 S009查看Registers表中数据66在分公司B003工作的员工负责注册的客户：只有两名员工S007和 S008分别负责注册了一位客户2.1 执行嵌套子查询语句

26、671、点击“新建查询（N）”3、点击“执行（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实现嵌套子查询）2.2 用EXISTS实现嵌套子查询681、点击“新建查询（N）”3、点击“执行（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中嵌套聚集函数691、点击“新建查询（N）”3、点击“执行（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中嵌套聚集函数701、点击“新建查询（N）”3、点击“执行（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实现嵌套子查询711、点击“新建查询（N）”3、点击“执行（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表中数据72在分公司B003工作的员工工资是190000、 150000和 100000，最低工资是100000，最高工资是190000比分公司B003中最低工资100000高的所有员工有6人比分公司B003中最高工资190000高的所有员工有1人2.6 用SOME实现嵌套子查询731、点击“新建查询（N）”3、点击“执行（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实现嵌套子查询741、点击“新建查询（N）”3、点击“执行（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实现嵌套子查询751、点击“新建查询（N）”3、点击“执行（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、点击“执行（X）”2、在查询窗口中输入显示房产及所属分公司的信息的语句：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、点击“执行（X）”2、在查询窗口中输入显示房产及所属分公司的信息的语句：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、点击“执行（X）”2、在查询窗口中输入显示房产及所属分公司的信息的语句：SELECT b.branchNo,b.branchStreet,b.branchCity,b.branchPostcode,pertyNo,p.pOwnerNo, p.branchNo,p.bOwnerNo,pertyStreet,pertyCity,p.type,p.rooms,per