Chapter 3 使用联接和子查询来查询数据.doc

-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 Studentselect * from Marks-1. 内联接 INNTER JOIN- 显示满足公共列中联接条件的行 inner可加可不加-问题：查询有考试成绩的学生的学号，姓名，RDBMS成绩和Math成绩select Student.RollNo,Student.Name,Marks.RDBMS,Marks.Math from Student join Marks on Student.RollNo=Marks.RollNo-给表名一个别名select a.RollNo,a.Name,b.RDBMS,b.Math from Student a join Marks b on a.RollNo=b.RollNo select a.RollNo,a.Name,b.RDBMS,b.Math from Student a inner join Marks b on a.RollNo=b.RollNo -问题：查询RDBMS成绩在70以上的学生的学号，姓名和RDBMS成绩select Student.RollNo,Student.Name,Marks.RDBMS from Student join Marks on Student.RollNo=Marks.RollNo where Marks.RDBMS70-练习C3-编写一个查询来显示所有销售人员的销售人员ID和他们所属的所在地名称 Sales.SalesPerson，Sales.SalesTerritoryselect * from Sales.SalesPersonselect * from Sales.SalesTerritoryselect a.SalesPersonID,b.Name from Sales.SalesPerson a join Sales.SalesTerritory b on a.TerritoryID=b.TerritoryID-2. 外联接 - 显示包含一个表中的所有行以及另外一个表中匹配行的结果集，不匹配的用NULL值填充-（1）左外联接 - 返回LEFT OUTER JOIN 左侧的表的所有行，以及右侧指定的表的匹配行，若右边找不到匹配项，显示NULL值select a.RollNo,a.Name,b.RDBMS,b.Math from Student a LEFT OUTER JOIN Marks b on a.RollNo=b.RollNo -（2）右外联接 - 返回RIGHT OUTER JOIN 右侧的表的所有行，以及左侧指定的表的匹配行，若左边找不到匹配项，显示NULL值select a.RollNo,a.Name,b.RDBMS,b.Math from Student a RIGHT OUTER JOIN Marks b on a.RollNo=b.RollNo insert into Marks values(S006,60,69) select a.RollNo,a.Name,b.RDBMS,b.Math from Student a RIGHT OUTER JOIN Marks b on a.RollNo=b.RollNo -（3）完整外联接 - 左外联接和右外联接的组合，返回两个表中所有匹配的行和不匹配的行，匹配记录只显示一次select a.RollNo,a.Name,b.RDBMS,b.Math from Student a FULL OUTER JOIN Marks b on a.RollNo=b.RollNo -3. 交叉联接（Cross Join） Product运算，将一个表中的每一行与另一个表中的create table Course -创建Course表，里面包含一列CourseName(CourseName varchar(10)insert into Course values -向Course表中插入两行记录(English),(C Language)select * from Studentselect * from Course-要求显示结果为每个学生都修一遍Course表中的所有课程select * from Student a cross join Course b-4. 等值联接 -使用=号联接表的内联接-练习：查询员工的员工编号，所属部门名称和工资 联接多个表select a.EmployeeID,b.Title,c.Name from HumanResources.EmployeeDepartmentHistory a join HumanResources.Employee b on a.EmployeeID=b.EmployeeIDjoin HumanResources.Department c on a.DepartmentID=c.DepartmentID-5. 自联接 - 同一个表当成两张表使用，一个表中的一行联接另一个表中的一行select * from HumanResources.Employeeselect a.EmployeeID,a.Title,a.ManagerID,b.Title from -查询员工的编号，职位，其主管的员工编号和其主管的职位HumanResources.Employee a join HumanResources.Employee b on a.ManagerID=b.EmployeeID -根据其主管的员工编号找到对应的职位go- (二)、使用子查询查询数据-子查询：将一个select的查询结果作为另外一个select查询的输入/条件,查询里面的查询-1. 使用比较运算符，IN和EXISTS关键字-比较运算符，以=号为主select * from HumanResources.Employee-问题：查询和员工编号为1的员工职位（Title）相同的员工的信息select * from HumanResources.Employee where Title=(select Title from HumanResources.Employee where EmployeeID=1)-IN 多个值-问题：查询和员工编号为1,3,4的员工的职位相同的员工的信息select * from HumanResources.Employee where Title in(select Title from HumanResources.Employee where EmployeeID in(1,3,4)-EXISTS关键字-检查一组记录是否存在，返回True或Falseselect * from Marks where EXISTS(select * from Marks where RDBMS80 and Math80)-2. 使用修改过的比较运算符 ALL,ANY-问题：查询select * from Marks where RDBMSALL(select RDBMS from Marks where RollNo in(S002,S003) -查询RDBMS成绩高于S002且高于S003的学生的信息select * from Marks where RDBMSANY(select RDBMS from Marks where RollNo in(S002,S003) -查询RDBMS成绩高于S002或者高于S003的学生的信息-3. 使用聚合函数-问题：查询RDBMS成绩最高的学生的学号和RDBMS成绩select RollNo,RDBMS from Marks where RDBMS=(select max(RDBMS) from Marks)-4. 使用嵌套子查询 -子查询里面可以包含一个或多个子查询，这样叫做嵌套子查询-问题：查询工资最高的员工的编号 HumanResources.EmployeePayHistoryselect * from HumanResources.EmployeePayHistoryselect EmployeeID from HumanResources.EmployeePayHistory where Rate=(select max(Rate) from HumanResources.EmployeePayHistory)-问题：查询工资最高的员工所在的部门编号 select * from HumanResources.EmployeeDepartmentHistoryselect DepartmentID from HumanResources.EmployeeDepartmentHistory where EmployeeID=(select EmployeeID from HumanResources.EmployeePayHistory where Rate=(select max(Rate) from HumanResources.EmployeePayHistory)-5. 使用关联子查询 - 根据外部查询作为评估依据的查询-问题：查询每个部门最早加入的员工的信息select * from HumanResources.EmployeeDepartmentHistory awhere StartDate=(select min(StartDate) from HumanResources.EmployeeDepartmentHistorywhere 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 Depositorselect * 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.RollNowhere b.Math=(select max(Math) from Marks) go- (三)、管理结果集-1. 交集，并集，差集 -操作两个select语句查询的结果/* 前提条件（1）两个结果集的列的数量和顺序要一致（2）所有查询中的列的数据类型必须兼容 如char(10)和varchar(10)*/select 客户姓名 from Depositorselect 客户姓名 from Borrower-UNION 并集 -默认不显示重复的行，ALL显示出重复的记录select 客户姓名 from DepositorUNION ALLselect 客户姓名 from Borrower-INTERSECT 交集select 客户姓名 from DepositorINTERSECTselect 客户姓名 from Borrower-EXCEPT 差集select 客户姓名 from DepositorEXCEPTselect 客户姓名 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 Human