09.多表查询

多表查询

一、笛卡尔乘积

--笛卡尔乘积
--查询结果将People所有记录和Department所有记录依次排列组合形成新的结果
select*from People,Department;

二、简单的多表查询

--简单的多表查询
--查询员工信息，显示部门名称
select*from People,Department
where People.DepartmentId = Department.DepartmentId;
--查询员工信息，显示职级名称
select*from People,[Rank]
where People.RankId = [Rank].RankId;
--查询员工信息，显示部门名称.显示职级名称
select*from People,Department,[Rank]
where People.DepartmentId = Department.DepartmentId
and People.RankId = [Rank].RankId;

三、内连接

--内连接查询
--查询员工信息，显示部门名称
select*from People 
inner join Department
on People.DepartmentId = Department.DepartmentId;
--查询员工信息，显示职级名称
select*from People
inner join [Rank] 
on People.RankId = [Rank].RankId;
--查询员工信息，显示部门名称，显示职级名称
select*from People 
inner join Department
on People.DepartmentId = Department.DepartmentId
inner join [Rank] 
on People.RankId = [Rank].RankId;

四、外连接

--外连接（左外连，右外连，全外连）
--左外连：以左表为主表进行数据显示，主外键关系找不到将会用null取代
--查询员工信息，显示部门名称
select*from People 
left join Department
on People.DepartmentId = Department.DepartmentId;
--查询员工信息，显示职级名称
select*from People
left join [Rank] 
on People.RankId = [Rank].RankId;
--查询员工信息，显示部门名称，显示职级名称
select*from People 
left join Department
on People.DepartmentId = Department.DepartmentId
left join [Rank] 
on People.RankId = [Rank].RankId;

--右连接  A left join B = B right join A
--下面两个查询含义一样
select*from People 
left join Department
on People.DepartmentId = Department.DepartmentId;

select*from Department
right join People
on People.DepartmentId = Department.DepartmentId;

--全外连
--两张表的数据，无论是否符合关系，都要显示
select*from People 
full join Department
on People.DepartmentId = Department.DepartmentId;

四、综合实例

--查询出武汉地区所有员工信息，
--要求显示部门名称以及员工的详细信息(显示中文别名)
select PeopleId 编号,DepartmentName 部门名称,
PeopleName 姓名,PeopleSex 性别,	PeopleBirth 出生日期,
PeopleSalary 薪资,PeoplePhone 手机号,PeopleAddr 住址  
from People 
left join Department 
on People.DepartmentId = Department.DepartmentId
where PeopleAddr = '武汉'; 

--查询出武汉地区的所有员工信息，
--要求显示部门名称，职级名称以及员工的详细资料（显示中文别名）
select PeopleId 编号,DepartmentName 部门名称,RankName 职级名称,
PeopleName 姓名,PeopleSex 性别,	PeopleBirth 出生日期,
PeopleSalary 薪资,PeoplePhone 手机号,PeopleAddr 住址   
from People 
left join Department 
on People.DepartmentId = Department.DepartmentId
left join [Rank]
on People.RankId = [Rank].RankId
where PeopleAddr = '武汉';

--根据部门分组统计员工人数，员工工资总和，平均工资，最高工资，最低工资
select Department.DepartmentId 部门编号 ,DepartmentName 部门名称 ,count(*) 员工人数,sum(PeopleSalary) 员工工资总和,
avg(PeopleSalary) 平均工资,max(PeopleSalary) 最高工资,
min(PeopleSalary) 最低工资
from People 
inner join Department 
on People.DepartmentId = Department.DepartmentId 
group by Department.DepartmentId,DepartmentName;

--根据部门分组统计员工人数，员工工资总和，平均工资，最高工资，最低工资
--平均工资在2000以下的不参与统计，并根据平均工资进行降序排列
select Department.DepartmentId 部门编号 ,DepartmentName 部门名称 ,count(*) 员工人数,sum(PeopleSalary) 员工工资总和,
avg(PeopleSalary) 平均工资,max(PeopleSalary) 最高工资,
min(PeopleSalary) 最低工资
from People 
inner join Department 
on People.DepartmentId = Department.DepartmentId 
group by Department.DepartmentId,DepartmentName
having avg(PeopleSalary) >= 2000 
order by avg(PeopleSalary) desc;

--根据部门名称，然后根据职位名称
--分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资
select DepartmentName 部门名称 ,RankName 职级,
count(*) 员工人数,sum(PeopleSalary) 员工工资总和,
avg(PeopleSalary) 平均工资,max(PeopleSalary) 最高工资,
min(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

五、自连接

create table dept 
(
	DeptId int primary key ,
	DeptName nvarchar(20) ,
	ParentId int,  --上级部门编号
)
--一级部门
insert into dept(DeptId,DeptName,ParentId)
values(1,'软件部',0)
insert into dept(DeptId,DeptName,ParentId)
values(2,'硬件部',0)
--二级部门
insert into dept(DeptId,DeptName,ParentId)
values(3,'软件测试部',1)
insert into dept(DeptId,DeptName,ParentId)
values(4,'硬件测试部',2)

--进行自连接
select A.DeptId 部门编号,A.DeptName 部门名称,B.DeptName 上级部门 from Dept A
inner join Dept B
on A.ParentId = B.DeptId