使用联接和子查询来查询数据
--Chapter 3 使用联接和子查询来查询数据
--内容提要
go
/* (一)、使用联接查询数据
1. 内联接
2. 外联接
3. 交叉联接
4. 等值联接
5. 自联接
*/ go
/* (二)、使用子查询查询数据
1. 使用比较运算符，IN和EXISTS关键字
2. 使用修改过的比较运算符
3. 使用聚合函数
4. 使用嵌套子查询
5. 使用关联子查询
6. APPLY运算符
*/ go
/* (三)、管理结果集
1. 并集，交集和差集
2. 临时结果集
3. 派生表
*/ go
---------------------- (一)、使用联接查询数据------------------------------
use AdventureWorks --切换到AdventureWorks数据库
--创建Student表和Marks表，用于操作各种联接
create table Student --创建学生表，里面包含两列，学号和姓名
(
RollNo char(4),
Name varchar(20)
)
insert into Student values --向Student表中插入5行记录
('S001','Allen'),
('S002','Jhon'),
('S003','David'),
('S004','Stefen'),
('S005','Steve')
create table Marks --创建成绩表，里面包含三列，学号，RDMBS和Math
(
RollNo char(4),
RDBMS int,
Math int
)
insert into Marks values --向成绩表中插入三行记录
('S001',98,76),
('S002',67,64),
('S003',76,96)
select * from Student
select * from Marks
--1. 内联接 INNTER JOIN- 显示满足公共列中联接条件的行 inner可加可不加
--问题：查询有考试成绩的学生的学号，姓名，RDBMS成绩和Math成绩
-----练习：已知
select * from HumanResources.Employee
select * from HumanResources.EmployeeAddress
go
--显示：EmployeeID, Title, AddressID 的匹配信息 ----inner join
--给表名一个别名
--问题：查询RDBMS成绩在70以上的学生的学号，姓名和RDBMS成绩
select * from Student
select * from Marks
--练习C3-编写一个查询来显示所有销售人员的销售人员ID和他们所属的所在地名称 Sales.SalesPerson，Sales.SalesTerritory
select * from Sales.SalesPerson
select * from Sales.SalesTerritory
--2. 外联接 - 显示包含一个表中的所有行以及另外一个表中匹配行的结果集，不匹配的用NULL值填充
--（1）左外联接 - 返回LEFT OUTER JOIN 左侧的表的所有行，以及右侧指定的表的匹配行，若右边找不到匹配项，显示NULL值
--（2）右外联接 - 返回RIGHT OUTER JOIN 右侧的表的所有行，以及左侧指定的表的匹配行，若左边找不到匹配项，显示NULL值
--（3）完整外联接 - 左外联接和右外联接的组合，返回两个表中所有匹配的行和不匹配的行，匹配记录只显示一次
--3. 交叉联接（Cross Join） Product运算，将一个表中的每一行与另一个表中的
--------------------
create table Course --创建Course表，里面包含一列CourseName
(CourseName varchar(10))
insert into Course values --向Course表中插入两行记录
('English'),
('C Language')
select * from Student
select * from Course
--要求显示结果为每个学生都修一遍Course表中的所有课程
--4. 等值联接 --使用=号联接表的内联接
--练习：查询员工的员工编号，所属部门名称和工资 联接多个表
select * from HumanResources.Employee
select * from HumanResources.EmployeeDepartmentHistory
select * from HumanResources.Department
--5. 自联接 - 同一个表当成两张表使用，一个表中的一行联接另一个表中的一行
select * from HumanResources.Employee
select a.EmployeeID,a.Title,a.ManagerID,b.Title from --查询员工的编号，职位，其主管的员工编号和其主管的职位
HumanResources.Employee a join HumanResources.Employee b on a.ManagerID=b.EmployeeID --根据其主管的员工编号找到对应的职位
select a.EmployeeID,a.Title,a.ManagerID,b.Title from --查询员工的编号，职位，其主管的员工编号和其主管的职位
HumanResources.Employee a , HumanResources.Employee b where a.ManagerID=b.EmployeeID --根据其主管的员工编号找到对应的职位
go
---------------------- (二)、使用子查询查询数据----------------------------
--子查询：将一个select的查询结果作为另外一个select查询的输入/条件,查询里面的查询
--1. 使用比较运算符，IN和EXISTS关键字
--比较运算符，以=号为主
select * from HumanResources.Employee
--问题：查询和员工编号为1的员工职位（Title）相同的员工的信息
--IN 多个值
--问题：查询和员工编号为1,3,4的员工的职位相同的员工的信息
--EXISTS关键字-检查一组记录是否存在，返回True或False
--if exists(select * from databases where name='UDB') drop database UDB
------------------
select * from HumanResources.Employee
select * from HumanResources.EmployeeDepartmentHistory
--2. 使用修改过的比较运算符 ALL,ANY
--问题：查询
--查询RDBMS成绩高于S002或者高于S003的学生的信息
select * from Marks
go
--查询RDBMS成绩高于S002并且高于S003的学生的信息
--3. 使用聚合函数
--问题：查询RDBMS成绩最高的学生的学号和RDBMS成绩
--4. 使用嵌套子查询 --子查询里面可以包含一个或多个子查询，这样叫做嵌套子查询
--问题：查询工资最高的员工的编号 HumanResources.EmployeePayHistory
select * from HumanResources.EmployeePayHistory
--问题：查询工资最高的员工所在的部门编号
select * from HumanResources.EmployeeDepartmentHistory
--5. 使用关联子查询 - 根据外部查询作为评估依据的查询
--问题：查询每个部门最早加入的员工的信息
select * from HumanResources.EmployeeDepartmentHistory a
where StartDate=
(
select min(StartDate) from HumanResources.EmployeeDepartmentHistory
where DepartmentID=a.DepartmentID
)
--6. APPLY运算符 --合并两个查询的结果集,
---------------------------------------------
create table Depositor --创建Depositor表，存储储蓄用户信息，表中有两列，客户姓名和储蓄账户
(
客户姓名 varchar(20),
储蓄账户 char(3)
)
insert into Depositor values --向Depositor表中插入两条记录
('Allen','D01'),
('David','D02')
create table Borrower --创建Borrower表，存储贷款用户信息，表中有两列，客户姓名和贷款账户
(
客户姓名 varchar(20),
贷款账户 char(3)
)
insert into Borrower values --向Borrower表中插入两行记录
('Amy','B11'),
('David','B12')
--------------------------------------
select * from Depositor
select * from Borrower
--CROSS APPLY - 返回外部结果集中与内部结果集匹配的行
select a.客户姓名,a.储蓄账户,br.贷款账户 from Depositor a --外部结果集
cross apply
(select * from Borrower b where b.客户姓名=a.客户姓名) br --br为内部结果集的别名
--OUTER APPLY - 返回外部结果集中所有的行，即使内部结果集中没有找到此行
select a.客户姓名,a.储蓄账户,br.贷款账户 from Depositor a --外部结果集
outer apply
(select * from Borrower b where b.客户姓名=a.客户姓名) br --br为内部结果集的别名
--练习：查询Math成绩最高的学生的学号，姓名和Math成绩
select a.RollNo,a.Name,b.Math from Student a join Marks b on a.RollNo=b.RollNo
where b.Math=(select max(Math) from Marks)
go
---------------------- (三)、管理结果集------------------------------------
--1. 交集，并集，差集 --操作两个select语句查询的结果
/* 前提条件
（1）两个结果集的列的数量和顺序要一致
（2）所有查询中的列的数据类型必须兼容 如char(10)和varchar(10)
*/
select 客户姓名 from Depositor
select 客户姓名 from Borrower
--UNION 并集 --默认不显示重复的行，ALL显示出重复的记录
select 客户姓名 from Depositor
UNION ALL
select 客户姓名 from Borrower
--INTERSECT 交集
select 客户姓名 from Depositor
INTERSECT
select 客户姓名 from Borrower
--EXCEPT 差集
select 客户姓名 from Depositor
EXCEPT
select 客户姓名 from Borrower
--2. 临时结果集 - 将一个查询结果在执行的时候临时存储，用于执行其他查询
--不保存在数据库中，只有在执行的时候存在，语句执行完之后不存在
--问题：查询工资最高的10位员工的平均工资
WITH RateCTE(Salary) --RateCTE为临时结果集的名字，可自定义，Rate为临时结果集中的列名
AS
(
select top 10 Rate from HumanResources.EmployeePayHistory order by Rate desc --临时结果集的数据来源
)
select '平均工资'=avg(Salary) from RateCTE --从临时结果集里面查询数据
--锚定查询/递归查询
WITH Employee_CTE(ManagerID,EmployeeID) AS
(
select ManagerID,EmployeeID from HumanResources.Employee where ManagerID is null --锚查询，第一个被执行
UNION ALL -- Union是将两个select语句的结果合并成一个结果
select a.ManagerID,a.EmployeeID from HumanResources.Employee a join Employee_CTE b on --递归查询，反复被执行，直到查询到最后一行结束
a.ManagerID=b.Employee
)
select * from Employee_CTE
--3. 派生表 -- 将查询结果当做一个派生表/临时表使用
select Table1.ProductID,Table1.销售总数量,Table2.每个订单对应产品数 from
(select ProductID,'销售总数量'=sum(OrderQty) from Sales.SalesOrderDetail group by ProductID) as Table1 --将该select查询的结果作为一个派生表使用，相当于一个临时表
inner join
(select ProductID,'每个订单对应产品数'=sum(OrderQty) from Sales.SalesOrderDetail group by ProductID,SalesOrderID) as Table2 --将该select查询的结果作为另一个派生表使用，相当于一个临时表
on Table1.ProductID=Table2.ProductID
go