多表查询

一、笛卡尔乘积
select * from People.Department

二、简单多表查询 (where)
（1）查询员工信息，同时显示部门名称
select * from People,Department where People.DepartmentId= Department.DepartmentId
（2）查询员工信息，同时显示职称名称
select * from People,Rank where People.RankId= Rank.RankId
（3）查询员工信息，同时显示部门名称,显示职称名称
select * from People,Department,Rank where People.DepartmentId= Department.DepartmentId and People.RankId = Rank.RankId

三、内连接 (inner join)
简单多表查询和此种查询，不符合主外键关系的数据不会被显示
（1）查询员工信息，同时显示部门名称
select * from People inner join Department on People.DepartmentId= Department.DepartmentId
（2）查询员工信息，同时显示职称名称
select * from People inner join [Rank] on People.RankId= [Rank].RankId
（3）查询员工信息，同时显示部门名称,显示职称名称
select * from People inner join Department on People.DepartmentId= Department.DepartmentId inner join [Rank] on People.RankId = [Rank].RankId

四、外连接 (左外连，右外连，全外连)
（1）查询员工信息，同时显示部门名称
(left join)左外连：以左表为主表进行数据显示，主外键关系找不到的数据null取代
select * from People left join Department on People.DepartmentId= Department.DepartmentId
（2）查询员工信息，同时显示部门名称
(right join)右外连：以右表为主表进行数据显示，主外键关系找不到的数据null取代
select * from Department right join People on People.DepartmentId= Department.DepartmentId
（3）查询员工信息，同时显示部门名称
(full join)全外连：两张表的数据，无论是否符合关系，都要显示
select * from Department full join People on People.DepartmentId= Department.DepartmentId

多表查询综合示例
（1）查询所有武汉地区员工信息，显示部门名称以及员工详细资料
select PeopleName 姓名, People.DepartmentId 部门编号 ,DepartmentName 部门名称, PeopleSex 性别，PeopleBirth 生日, PeopleSalary 月薪, PeoplePhone 电话，PeopleAddress 地区 from People left join Department on People.DepartmentId= Department.DepartmentId where PeopleAddress='武汉'

（2）查询所有武汉地区员工信息，显示部门名称，职称名称以及员工详细资料
select PeopleName 姓名, DepartmentName 部门名称, RankName 职位名称, PeopleSex 性别，PeopleBirth 生日, PeopleSalary 月薪, PeoplePhone 电话，PeopleAddress 地区 from People left join Department on People.DepartmentId= Department.DepartmentId left join [Rank] on People.RankId = [Rank].RankId where PeopleAddress='武汉'

（3）根据部门分组统计员工人数，员工总工资，最高工资，最低工资和平均工资
select Department 部门名称, COUNT(*) 人数,MAX(PeopleSalary) 最高工资 ,MIN(PeopleSalary) 最低工资,SUM(PeopleSalary) 工资总和,AVG(PeopleSalary) 平均工资 from People inner join Department on People.DepartmentId = Department.DepartmentId group by Department.DepartmentId, DepartmentName

（4）根据部门分组统计员工人数，员工总工资，最高工资，最低工资和平均工资
--平均工资在10000以下的不参与统计，并且根据平均工资降序排列
select Department 部门名称, COUNT(*) 人数,MAX(PeopleSalary) 最高工资 ,MIN(PeopleSalary) 最低工资,SUM(PeopleSalary) 工资总和,AVG(PeopleSalary) 平均工资 from People inner join Department on People.DepartmentId = Department.DepartmentId group by Department.DepartmentId, DepartmentName having AVG(PeopleSalary) >= 10000 order by AVG(PeopleSalary) desc

（5）根据部门名称，然后根据职位名称
--分组统计员工人数，员工总工资，最高工资，最低工资和平均工资
select Department 部门名称, COUNT(*) 人数,MAX(PeopleSalary) 最高工资 ,MIN(PeopleSalary) 最低工资,SUM(PeopleSalary) 工资总和,AVG(PeopleSalary) 平均工资 from People inner join Department on People.DepartmentId = Department.DepartmentId inner join [Rank] on People.RankId = [Rank].RankId group by Department.DepartmentId, DepartmentName, [Rank].RankId,RankName

自连接（自己连自己）
select A.DeptId 部门编号, A.DeptName 部门名称，B.DeptName 上级部门 from Dept A inner join Dept B on A.ParentId = B.ParentId