SQL完全教程第5章高级查询2-集合查询子查询

5
5.2.1 UNION运算符 UNION运算符
例2：查询员工表employees来源于美国的员工的的姓 查询员工表employees来源于美国的员工的的姓 职务TitleOfCourtesy 城市City和经销商表 名，职务TitleOfCourtesy ，城市City和经销商表 Customers中来源于美国的联系人 Customers中来源于美国的联系人ContactName 、联系人 中来源于美国的联系人ContactName 职务ContactTitle、城市City 职务ContactTitle、城市City 。 SELECT firstname + ‘ ‘+ lastname as name,TitleOfCourtesy, name,TitleOfCourtesy, City FROM employees WHERE Country=‘USA’ UNION SELECT ContactName,ContactTitle, City FROM Customers WHERE Country=‘USA’
14
5.2.3 EXCEPT运算符 EXCEPT运算符
例7：查询PRODUCTS表单价小于等于20并且不是未 打折商品的产品编号。 SELECT productid FROM Products WHERE unitprice<=20 EXCEPT SELECT productid FROM products WHERE Discontinued='0'
9
5.2.2 INTERSECT运算符 INTERSECT运算符
例4：查询员工表employees来源于美国并且雇佣日期 在1994年以前的员工的员工编号及姓名 SELECT EmployeeID,LastName,FirstName FROM employees WHERE Country='USA' INTERSECT SELECT EmployeeID,LastName,FirstName FROM employees WHERE hiredate<'1994-1-1'
3ຫໍສະໝຸດ Baidu
5.2.1 UNION运算符 UNION运算符
功能:UNION运算符可以将多个结果集求并集并形成一个结果集 功能:UNION运算符可以将多个结果集求并集并形成一个结果集.要 运算符可以将多个结果集求并集并形成一个结果集. 2 求源表的列类型及个数必须一致 1 语法格式: 语法格式: select_statement1 UNION [ALL] select_statement2 说明: 说明: 1、ALL选项表示将所有行合并到结果集中,即保留重复行,当不选择 ALL选项表示将所有行合并到结果集中, 保留重复行, 选项表示将所有行合并到结果集中 此项时, 此项时,则结果集中的重复行只保留一行 2、查询结果集中的列标题为第一个查询语句的列标题。 查询结果集中的列标题为第一个查询语句的列标题。 3、SELECT列表中要有相同的列 SELECT列表中要有相同的列
6
5.2.1 UNION运算符 UNION运算符
例3：查询员工表employees来源于美国或者雇佣日期 查询员工表employees来源于美国或者雇佣日期 1994年以前的员工的员工编号及姓名 在1994年以前的员工的员工编号及姓名 SELECT EmployeeID,LastName,FirstName
21
练习
请查询出PRODUCTS表中高于所有产品平均价格的产 品信息.
22
5.3 子查询
5.3.1 单行子查询 5.3.2 多行子查询 5.3.3 其他子查询
23
5.3.2 多行子查询
多行子查询指返回单列多行 单列多行数据的子查询.当在WHERE中使用多 单列多行 行子查询时,必须使用多行比较符(IN、ALL、ANY) 1、使用IN操作符 例4:查询出产品表PRODUCTS中其供应商所在城市为‘London’ 的所有产品的信息.
显示的结果:经销商的所有信息* 数据来源: CUSTOMERS表 查询条件: CustomerId IN IN(子 查询) 子查询显示的列: CustomerId 子查询的数据来源:ORDERS， ORDER DETAILS 子查询条件:quantity>=50
8
5.2.2 INTERSECT运算符 INTERSECT运算符
功能:INTERSECT运算符可以将形成的两个结果集求交集.要 求源表的列类型及个数必须一致 1 2 语法格式: select_statement1 INTERSECT select_statement2 说明: 1、查询结果集中的列标题为第一个查询语句的列标题。 2、 INTERSECT运算返回两个结果集中相同的行
语句
SELECT employeeid,firstname +‘ ‘+ lastname ename,city FROM EMPLOYEES WHERE CITY=( SELECT CITY FROM EMPLOYEES WHERE employeeid=5) 分析
显示的结果:employeeid,ename,city 数据来源:EMPLOYEES表 查询条件:CITY=(5号员工所在城市) =(5号员工所在城市 号员工所在城市) 子查询显示结果:city 子查询数据来源:EMPLOYEES表 子查询条件:员工编号为5
显示的结果:employeeid,ename,hiredate 数据来源:EMPLOYEES表 查询条件:year(雇佣时间)=(子 查询) 子查询显示结果:year(雇佣时间) 子查询数据来源:EMPLOYEES表 子查询条件:EMPLOYEEID=9
20
5.3.1 单行子查询
例3:查询出供应商表SUPPLIERS中与经销商表 CUSTOMERS中客户编号为’AROUT’ 同在一个城市的所有 供应商的信息.
2
语法格式: select_statement1 EXCEPT select_statement2
13
5.2.3 EXCEPT运算符 EXCEPT运算符
例6：查询PRODUCTS表中未被订购过的产品的产品编 号。 SELECT productid FROM Products EXCEPT SELECT productid FROM [order details]
Sql server 2005 程序设计与管理基础
1
第5章 高级查询
5.1 连接查询 5.2 集合查询 5.3 子查询
2
5.2 集合操作
5.2.1 UNION运算符 UNION运算符 5.2.2 INTERSECT运算符 INTERSECT运算符 5.2.3 EXCEPT运算符 EXCEPT运算符
15
第5章 高级查询
5.1 连接查询 5.2 集合查询 5.3 子查询
16
5.3 子查询
子查询:指嵌入在其他SQL 语句中的SELECT语句,也 称嵌套查询. 根据子查询返回的结果不同,子查询分为:
单行子查询:返回单列单行数据 子查询语句 单列单行数据的 单列单行数据 多行子查询:返回单列多行数据 子查询语句 单列多行数据的 单列多行数据 多列子查询:返回多列数据 子查询语句 多列数据的 多列数据
4
5.2.1 UNION运算符 UNION运算符
例1：查询来源于美国的供应商表Suppliers和经销商表Customers的 公司名称CompanyName、联系人ContactName 、联系人职务ContactTitle, 地址Address、城市City 。 SELECT CompanyName，ContactName， ContactTitle， Address， CompanyName，ContactName， ContactTitle， Address，
19
5.3.1 单行子查询
例2:查询出EMPLOYEES表中与员工编号为9号的员工 2:查询出 查询出EMPLOYEES表中与员工编号为 表中与员工编号为9 雇佣时间是同一年的所有员工的编号、姓名、雇佣日期. 雇佣时间是同一年的所有员工的编号、姓名、雇佣日期.
语句
SELECT employeeid, firstname + ‘ ‘ + lastname ename,hiredate FROM EMPLOYEES WHERE year(HireDate)=( SELECT year (HireDate) FROM EMPLOYEES WHERE employeeid=9) 分析
City FROM Suppliers WHERE Country=‘USA’
UNION SELECT CompanyName，ContactName， ContactTitle， Address， CompanyName，ContactName， ContactTitle， Address，
City FROM Customers WHERE Country=‘USA’
5.2 集合操作
5.2.1 UNION运算符 UNION运算符 5.2.2 INTERSECT运算符 INTERSECT运算符 5.2.3 EXCEPT运算符 EXCEPT运算符
12
5.2.3 EXCEPT运算符 EXCEPT运算符
功能:EXCEPT运算符可以找到两个给定的集合之间 的差异，即从其左边查询结果中去除右边查询结果中 存在的行。 1
5.3.2 多行子查询
例5:查询经销商表CUSTOMERS中其订购数量在50个以上 5:查询经销商表 查询经销商表CUSTOMERS中其订购数量在 个以上 中其订购数量在50 的所有经销商的信息. 的所有经销商的信息.
语句
SELECT * FROM CUSTOMERS WHERE CustomerID IN( SELECT Customerid FROM Orders O join [Order Details] OD on O.orderID=OD.orderID WHERE OD.quantity>=50) 分析
语句
SELECT * FROM PRODUCTS WHERE SupplierID IN( SELECT supplierid FROM suppliers WHERE city=‘London’ ) 分析
显示的结果:产品的所有信息* 数据来源: PRODUCTS表 查询条件: SupplierId IN IN(子 查询) 子查询显示的列: SupplierId 子查询的数据来源:SUPPLIERS 24 子查询条件:CITY=‘London’
10
5.2.2 INTERSECT运算符 INTERSECT运算符
例5：查询ORDERDETAILS表中被订购的产品的产 品编号都有哪些？ SELECT productid FROM Products INTERSECT SELECT productid FROM [order details]
11
分析
语句
SELECT * FROM SUPPLIERS WHERE CITY=( SELECT CITY FROM CUSTOMERS WHERE customerid=‘AROUT’ )
显示的结果:供应商的所有信息* 数据来源: SUPPLIERS表 查询条件: CITY=(子查询) 子查询显示的列:CITY 子查询的数据来源:CUSTOMERS 子查询条件:customerid=‘AROUT’
17
5.3 子查询
5.3.1 单行子查询 5.3.2 多行子查询 5.3.3 其他子查询
18
5.3.1 单行子查询
当在WHERE中引用单行子查询时,可以使用单行比较符 (=,>,<,>=,<=,<>)等. 例1:查询出EMPLOYEES表中与员工编号为5号员工是 同一个城市的员工的编号、姓名、城市.
FROM employees WHERE Country=‘USA’
UNION SELECT EmployeeID,LastName,FirstName
FROM employees WHERE hiredate<‘1994-1-1‘ hiredate<‘1994-
7
5.2 集合操作
5.2.1 UNION运算符 UNION运算符 5.2.2 INTERSECT运算符 INTERSECT运算符 5.2.3 EXCEPT运算符 EXCEPT运算符