SQL Sever数据库----简单多表查询,内连接,左连接,右连接,全连接,自连接

----多表查询---------
--笛卡尔乘积
--查询结构将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,[Rank],Department
where People.RankId=[Rank].RankId 
and People.DepartmentId=Department.DepartmentId

--内连接查询-----
--查询员工信息,显示部门名称
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


--简单多表查询和内连接共同的特点:
--不符合主外键关系的数据不会被显示出来
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,
PeoplePhone,PeopleAddress,PeopleAddTime)
values(99,99,'刘德华','男','1975-8-9',8000,'13556857548','香港',getdate())
insert into Department(DepartmentName,DepartmentRemark)
values('人事部','......')


------外连接(左外连接,右外连接,全外联)
--左外连:以左表为主表进行数据表示,主外键关系找不到的数据null取代
--右外连:以右表为主表进行数据表示,主外键关系找不到的数据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

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


--(1)查询武汉地区所有员工信息,要求显示部门名称以及员工的详细资料(显示中文别名)
select PeopleId 员工编号,
People.DepartmentId 部门编号,RankId 职级编号,
PeopleName 姓名,PeopleSex 性别,PeopleBirth 出生日期,
PeopleSalary 工资,PeoplePhone 电话,
PeopleAddress 地址,PeopleAddTime 入职时间,
Department.DepartmentId 部门编号,
DepartmentName 部门名称,
DepartmentRemark 部门描述
from People
left join Department
on People.DepartmentId=Department.DepartmentId
where PeopleAddress='武汉'

--(2)查询武汉地区所有员工信息,要求显示部门名称,职级名称,以及员工的详细资料(显示中文别名)
select PeopleId 员工编号,
PeopleName 姓名,PeopleSex 性别,PeopleBirth 出生日期,
PeopleSalary 工资,PeoplePhone 电话,
PeopleAddress 地址,PeopleAddTime 入职时间,
People.DepartmentId 部门编号,
DepartmentName 部门名称,
DepartmentRemark 部门描述,People.RankId 职级编号,
RankName 职级名称,RankRemark 职级描述
from People
left join Department
on People.DepartmentId=Department.DepartmentId
left join [Rank]
on People.RankId=[Rank].RankId
where PeopleAddress='武汉'
--(3)根据部门分组统计员工人数,员工工资总和,平均工资,最高工资和最低工资.
select People.DepartmentId 部门编号,DepartmentName 部门名称,
count(*) 员工人数,sum(PeopleSalary) 工资总和,
avg(PeopleSalary) 平均工资,max(PeopleSalary) 最高工资,
min(PeopleSalary) 最低工资
from People
inner join Department
on People.DepartmentId=Department.DepartmentId
group by People.DepartmentId,DepartmentName 
--(4)根据部门分组统计员工人数,员工工资总和,平均工资,最高工资和最低工资.
-----平均工资在10000以下不参与统计,并且根据平均工资降序排列.
select People.DepartmentId 部门编号,DepartmentName 部门名称,
count(*) 员工人数,sum(PeopleSalary) 工资总和,
avg(PeopleSalary) 平均工资,max(PeopleSalary) 最高工资,
min(PeopleSalary) 最低工资
from People
inner join Department
on People.DepartmentId=Department.DepartmentId
group by People.DepartmentId,DepartmentName 
having avg(PeopleSalary)>10000
order by avg(PeopleSalary) desc
--(5)根据部门名称,然后根据职位名称,分组统计员工人数,员工工资总和,
-----平均工资,最高工资和最低工资.
select People.DepartmentId 部门编号,DepartmentName 部门名称,
[Rank].RankName 职级名称,[Rank].RankId 职级编号,
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 People.DepartmentId,DepartmentName,[Rank].RankName,[Rank].RankId


---自连接(自己连自己)---------
create table Dept
(
	DeptId int Primary key,--部门编号
	DeptName varchar(50),--部门名称
	ParetId int --上级部门编号
)
insert into Dept(DeptId,DeptName,ParetId)
values(1,'软件部',0)
insert into Dept(DeptId,DeptName,ParetId)
values(2,'硬件部',0)
insert into Dept(DeptId,DeptName,ParetId)
values(3,'软件研发部',1)
insert into Dept(DeptId,DeptName,ParetId)
values(4,'软件测试部',1)
insert into Dept(DeptId,DeptName,ParetId)
values(5,'软件实施部',1)
insert into Dept(DeptId,DeptName,ParetId)
values(6,'硬件研发部',2)
insert into Dept(DeptId,DeptName,ParetId)
values(7,'硬件测试部',2)
insert into Dept(DeptId,DeptName,ParetId)
values(8,'硬件实施部',2)

select * from Dept
--部门编号  部门名称  上级部门
------------------------------
select A.DeptId 部门编号,A.DeptName 部门名称,B.DeptName 上级部门 from Dept A
inner join Dept B
on A.ParetId=B.DeptId