多表查询
一、笛卡尔乘积
--笛卡尔乘积
--查询结果将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
标签:多表,People,--,09,查询,DepartmentId,Department,join,select
From: https://www.cnblogs.com/Starry-blog/p/16797312.html