第6章 数据查询2

1、2021-10-1711第第6章章 数据查询（二）数据查询（二）2例例6-176-17 对对employeeemployee表中部门编号为表中部门编号为“D001”D001”或或“D002”D002”的员工工资，的员工工资，按照其部门编号生成分组汇总行和明细行。按照其部门编号生成分组汇总行和明细行。 SELECTSELECT department_id, wages department_id, wages FROMFROM employee employee WHERE WHERE department_id = D001 department_id = D001 OROR departme

2、nt_id = D002 department_id = D002 ORDER BYORDER BY department_id department_id COMPUTE COMPUTE sum(wages) sum(wages) BY BY department_id department_id例：查询选课表中各科的同学成绩信息，及各科的最高分、最低分和平均分。SELECT * FROM 选课表 Order by 课号 Compute max(成绩),min(成绩),avg(成绩) BY 课号36.2 嵌套查询SQL Server允许多层嵌套查询。嵌套查询一般的查询方法是由里向外进行处理，

3、即每个子查询在上一级查询处理之前处理，子查询的结果用于建立其父查询的查找条件。子查询中所存取的表可以是父查询没有存取的表，子查询选出的记录不显示。 v为了与外层查询有所区别，总把一个为了与外层查询有所区别，总把一个子查询写在圆括号中子查询写在圆括号中；注意事项注意事项v子查询可以用在允许使用表达式的任何地方，不过通常用子查询可以用在允许使用表达式的任何地方，不过通常用在外层查询的在外层查询的WhereWhere子句或子句或HavingHaving子句中，与比较运算符或子句中，与比较运算符或逻辑运算符一起构成查询条件，从而完成有关测试。逻辑运算符一起构成查询条件，从而完成有关测试。 4学号学号姓

4、名姓名性别性别年龄年龄是否党员是否党员入学年月入学年月专业专业890163张文青张文青男男20009/01/1989软件软件890239李名义李名义女女20009/01/1989应用应用980012张洋张洋男男18109/02/1998软件软件960012李明李明女女20103/01/1998应用应用学生表学生表练习都基于以下所给表练习都基于以下所给表5课程表课程表课号课号课名课名学分学分1 1程序设计程序设计4 42 2汇编语言汇编语言4 43 3数据库数据库3 34 4人工智能人工智能3 36选课表选课表学号学号课号课号成绩成绩8901638901631 18585890163890163

5、2 278788901638901634 475758902398902391 172728902398902393 356569800129800121 186869800129800122 287879600129600121 1787876.2 嵌套查询单值嵌套查询 例例 列出选修汇编语言课的所有学生的学号列出选修汇编语言课的所有学生的学号. . SELECT 学号学号 FROM 选课表选课表 WHERE 课号课号 = ( SELECT 课号课号 FROM 课程表课程表 WHERE 课名课名=汇编语言汇编语言)例例 列出列出“张文青张文青”同学所有课程的课号和成绩。同学所有课程的课号和成

6、绩。 SELECT SELECT 课号课号, ,成绩成绩 FROMFROM 选课表选课表 WHEREWHERE 学号学号= =( ( SELECTSELECT 学号学号FROMFROM 学生学生 WHEREWHERE 姓名姓名=张文青张文青) )86.2 嵌套查询 多值嵌套查询 (1) ANY(SOME)运算符的用法 满足子查询中任意一个值的记录。 例例 求选修求选修2 2号课的学生中成绩比选修号课的学生中成绩比选修1 1号课的最低成绩要号课的最低成绩要 高的学生的学号和成绩高的学生的学号和成绩. . SELECT 学号学号,成绩成绩 FROM 选课表选课表 WHERE 课号课号=2 AND

7、成绩成绩 ANY(SELECT 成绩成绩 FROM 选课表选课表 WHERE 课号课号=1)9例例6-19 6-19 对对SalesSales数据库，列出数据库，列出D001D001号部门中工资比号部门中工资比D002D002号部门的员工最低工资高的员工编号和工资。号部门的员工最低工资高的员工编号和工资。SELECT employee_id,wages FROM employeeSELECT employee_id,wages FROM employeeWHERE department_id=D001 AND wagesANYWHERE department_id=D001 AND wages

8、ANY(SELECT wages FROM employee(SELECT wages FROM employeeWHERE department_id=D002)WHERE department_id=D002)10(2) ALL运算符的用法例例6-20 6-20 对对SalesSales数据库，列出部门编号为数据库，列出部门编号为“D001”D001”的员工，这些员工的工资比部的员工，这些员工的工资比部门为门为“D002”D002”的员工的最高工资还要高的员工的编号和工资。的员工的最高工资还要高的员工的编号和工资。SELECT employee_id,wages FROM employee

9、SELECT employee_id,wages FROM employeeWHERE department_id=D001 AND wagesALLWHERE department_id=D001 AND wagesALL(SELECT wages FROM employee(SELECT wages FROM employee WHERE department_id=D002) WHERE department_id=D002)例例 求选修求选修2 2号课的学生号课的学生, ,这些学生的成绩比选修这些学生的成绩比选修1 1号课的任何号课的任何 学生的成绩都要高的学号和成绩学生的成绩都要高的

10、学号和成绩. . SELECT 学号学号,成绩成绩 FROM 选课表选课表 WHERE 课号课号=2 AND 成绩成绩ALL(SELECT 成绩成绩 FROM 选课表选课表 WHERE 课号课号=1)11例6-21 对Sales数据库，列出部门为“市场部”或“销售部”的所有员工的编号。SELECT employee_id FROM employeeWHERE department_id IN(SELECT department_id FROM departmentWHERE department_name=市场部 OR department_name=销售部) (3)IN (3)IN运算符的用

11、法运算符的用法例例 求软件专业所有必修课的课程设置情况求软件专业所有必修课的课程设置情况. . SELECT SELECT * * FROM FROM 课程表课程表 WHERE WHERE 课号课号 IN (SELECT IN (SELECT 课号课号 FROM FROM 必修课表必修课表 WHERE WHERE 必修专业必修专业=软件软件) )126.3 连接查询 连接概述 通过连接运算符可以实现多个表查询。连接是关系数通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其他类型数据库管理据库模型的主要特点，也是它区别于其他类型数据库管理系统的一个标志。系统的一个标

12、志。 连接可以在连接可以在SELECTSELECT语句的语句的WHEREWHERE子句中建立。子句中建立。13 例6-22 对Sales数据库输出所有员工的销售单，要求给出员工编号、姓名、商品编号、商品名和销售数量。 SELECT employee.employee_id, employee.employee_name, goods.goods_id, goods.goods_name, sell_order.order_numFROM employee,sell_order,goodsWHERE employee.employee_id = sell_order.employee_id AN

13、D sell_order.goods_id = goods.goods_id146.3 连接查询连接查询连接也可以在FROM子句中建立，而且在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。所以，在Transact-SQL中推荐使用这种方法。 FROM子句建立连接的语法格式是FROM join_table join_type JOIN join_table ON join_condition15内连接分3种：等值连接、不等值连接和自然连接。1等值连接在连接条件中使用等号(=)运算符比较被连接列的列值，按对应列的共同值将一个表中的记录与另一个表中的记录相连接，包括其

14、中的重复列。例6-23 Sales数据库中部门表department和员工表employee的等值连接。SELECT * FROM department INNER JOIN employee ON employee.department_id = department.department_idn内连接内连接16在连接条件中使用除等于(=)运算符以外的其他比较运算符比较被连接的列的列值。这些运算符包括、=、=、!和。 例6-24 对Sales数据库，列出销售“G00001”产品的员工中，销售数量大于编号为“E001”的员工销售该类产品销售数量的那些员工的编号和销售数量。SELECT a.em

15、ployee_id, a.order_numFROM sell_order a INNER JOIN sell_order b ON a.order_num b.order_num AND a.goods_id = b.goods_id WHERE (b.goods_id = G00001) AND (b.employee_id = E001)2 2不等值连接不等值连接17 在连接条件中使用等于(=)运算符比较被连接列的列值，它使用选择列表方式来指出查询结果集合中所包括的列，并删除连接表中的重复列。例6-25 Sales数据库中部门表department和员工表 employee的自然连接。S

16、ELECT a.department_name, b.*FROM department a INNER JOIN employee b ON b.department_id = a.department_id3 3自然连接自然连接18在内连接查询时，返回查询结果集合中的仅是符合查询条件(WHERE搜索条件或 HAVING条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个连接表(全外连接)中的所有数据行。n外连接外连接19左外连接通过左向外连接引用左表的所有行。例6-26 员工信息表employee左外连

17、接销售信息表sell_order。SELECT a.employee_id, a.employee_name, b.goods_id, b.order_num, b.send_dateFROM employee a LEFT OUTER JOIN sell_order b ON a.employee_id = b.employee_id1 1使用左外连接使用左外连接20右外连接通过右向外连接引用右表的所有行。例6-27 员工信息表employee右外连接销售信息表sell_order。 为了说明方便，先在sell_order表中插入一条销售信息。INSERT INTO sell_order (

18、order_id1, goods_id, employee_id,customer_id, transporter_id, order_num, discount,order_date, send_date, arrival_date, cost) VALUES (S00006, G00005, , C0006, T002, 21, 0.5, GETDATE(), GETDATE(), GETDATE(),100)SELECT a.employee_id, a.employee_name, b.goods_id, b.order_num, b.send_date, b.order_id1FRO

19、M employee a RIGHT OUTER JOIN sell_order b ON a.employee_id = b.employee_id2 2使用右外连接使用右外连接21 全外连接返回两个表的所有行。不管两个表的行是否满足连接条件，均返回查询结果集。对不满足连接条件的记录，另一个表相对应字段用NULL代替。例6-28 员工信息表employee全外连接销售信息表sell_order。SELECT a.employee_id, a.employee_name, b.goods_id, b.order_num, b.send_date, b.order_id1FROM employee a FULL OUTER JOIN sell_order b ON a.employee_id =