夺表查询

select * from Department
select * from People
--笛卡尔乘积
select * from People,Department
--查询结果将Department所有记录和people表所有记录依次排列组合形成新的结果
--查询员工信息 显示部门名称
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
--简单多表查询和内连接共同的特点 不符合主外键关系的数据不会被显示出来
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,
PeoplePhone,PeopleAddress,PeopleAddTime)
values(99,99,'刘德华','男','1975-8-9',8000,'13856552252','香港',GETDATE())

select * from Department
select * from People

--外连接 左外连 右外连 全外连
--左外连 以左表为主表进行数据显示 主外键关系找不到的数据null取代 左表是员工表
select * from People
left join Department on People.DepartmentId = Department.DepartmentId

--右连
--下面两个查询含义相同
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