菜鸟学习日记（SQL sever）——多表查询

--多表查询
SELECT * from Department
SELECT * FROM People
--笛卡尔乘积
--查询结果将Department所有记录和People表所有记录依次排列组合形成新的结果
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 a INNER JOIN Department b on a.DepartmentId=b.DepartmentId
--查询员工信息，显示职级名称
select * from People a INNER JOIN Rank b on a.RankId=b.RankId
--查询员工信息，显示部门名称，显示职级名称
select * from People a 
INNER JOIN Rank b on a.RankId=b.RankId 
INNER JOIN Department c on a.DepartmentId=c.DepartmentId
--简单多表查询和内连接共同的特点：
--不符合主外键关系的数据不会被显示出来

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

--右外连：以右表为主表进行数据显示 A LEFT JOIN B = B RIGHT JOIN A
select * from People a RIGHT JOIN Department b on a.DepartmentId=b.DepartmentId

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

--多表查询的综合示例
--查询出武汉地区所有员工信息，要求显示部门名称以及员工的详细资料（显示中文名）
SELECT PeopleId 员工编号,DepartmentName 部门名称,PeopleName 员工名称,PeopleSex 员工性别,PeopleBirth 员工生日,PeopleSalary 员工薪水,PeoplePhone 员工电话,PeopleAddress 员工地址 
from People INNER JOIN Department on People.DepartmentId=Department.DepartmentId WHERE PeopleAddress='武汉'
--查询出武汉地区所有员工信息，要求显示部门名称职级名称以及员工的详细资料（显示中文名）
SELECT PeopleId 员工编号,DepartmentName 部门名称,RankName 职级名称,PeopleName 员工名称,PeopleSex 员工性别,PeopleBirth 员工生日,PeopleSalary 员工薪水,PeoplePhone 员工电话,PeopleAddress 员工地址 
from People INNER JOIN Department on People.DepartmentId=Department.DepartmentId 
INNER JOIN Rank on People.RankId = Rank.RankId 
WHERE PeopleAddress='武汉'
--根据部门分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资
SELECT DepartmentName 部门名称,COUNT(*) 人员人数,SUM(PeopleSalary) 员工工资总和,avg(PeopleSalary) 平均工资,MAX(PeopleSalary) 最高工资,MIN(PeopleSalary) 最低工资 
from People INNER JOIN Department ON People.DepartmentId=Department.DepartmentId
GROUP BY DepartmentName
--根据部门分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资，
--平均工资在8000以下的不参与统计，并根据平均工资降序排列
SELECT DepartmentName 部门名称,COUNT(*) 人员人数,SUM(PeopleSalary) 员工工资总和,avg(PeopleSalary) 平均工资,MAX(PeopleSalary) 最高工资,MIN(PeopleSalary) 最低工资 
from People INNER JOIN Department ON People.DepartmentId=Department.DepartmentId 
 HAVING AVG(PeopleSalary)>=8000 
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 DepartmentName,RankName
--自连接：自己连自己
CREATE table Dept
(
    DeptId int PRIMARY KEY, --部门编号
    DeptName VARCHAR(50),  --部门名称
    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,'软件测试部',1)
insert into Dept(DeptId,DeptName,ParentId) VALUES(5,'软件实施部',1)
insert into Dept(DeptId,DeptName,ParentId) VALUES(6,'硬件研发部',2)
insert into Dept(DeptId,DeptName,ParentId) VALUES(7,'硬件测试部',2)
insert into Dept(DeptId,DeptName,ParentId) VALUES(8,'硬件实施部',2)

select * from Dept
--部门编号   部门名称      上级部门
--3         软件研发部      软件部
--4         软件测试部      软件部
select A.DeptId 部门编号,A.DeptName 部门名称,B.DeptName 上级部门 from Dept A INNER JOIN Dept B on A.ParentId=B.DeptId