SQL SERVER学习笔记(六)

笛卡尔乘积

查询结果是将People所有记录和People表所有记录依次排列组合形成i新的结果

select * from People,Department

一. 简单多表查询
语法：select * from 表1名称,表2名称 where 表1名称.字段名 = 表2名称.字段名
NOTE：表1名称.字段名 与 表2名称.字段名需有所关联
1.查询员工信息，并且显示部门名称

select * from People ,Department where People.DepartmentID = Department,ID

2.查询员工信息，并且显示职级名称

select * from People,[Rank] where People.RankID = [Rank].ID

3.查询员工信息，显示部门名称及职级名称

select * from People,Department,[Rank] where People.DepartmentID = Department.ID and
People.RankID = [Rank].ID

二 . 内连接查询
关键字： …inner join…on…
语法：select * from 表1名称 inner join 表2名称 on 表1.字段名 = 表2.字段名
1.查询员工信息，并且显示部门名称

select * from People inner join Department on People.DepartmentID = Department,ID

2.查询员工信息，并且显示职级名称

select * from People inner join [Rank] on People.RankID = [Rank].ID

3.查询员工信息，显示部门名称及职级名称
简单多表查询和内连接查询共同的特点：不符合主外键关系的数据不能被查询出来

select * from People 
inner join Department on People.DepartmentID = Department.ID
inner join [Rank] on People.RankID = [Rank].ID

二 . 外连接查询
1.左外连：以左表为主表进行数据显示，主外键关系无法找到的数据用NULL取代
关键字： …左表 left join 右表 on…
语法：select * from 表1名称 left join 表2名称 on 表1.字段名 = 表2.字段名
1.查询员工信息，并且显示部门名称

select * from People left join Department on People.DepartmentID = Department,ID

2.右外连：A Left join B = B Right join A
左连：A表的字段放前面
右连：B表的字段放前面

3.全外连：两张表数据，无论是否符合关系都要显示，不符合主外键关系的用NULL取代
关键字：…full join…on
1.查询员工信息，并且显示部门名称

select * from People full join Department on People.DepartmentID = Department,ID

多表查询综合示例
举例：根据部门分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资，平均工资在10000以下的不参与统计，并且根据平均工资降序排列

select DepartmentName 部门名称,count(*) 员工人数, sum(Salary) 工资总和, avg(Salary) 平均工资, max(Salary) 最高工资, min(Salary) 最低工资 from People 
left join Department on People.DepartmentID = Department.ID
left join [Rank] on People.RankID = [Rank].ID
group by Department.ID,DepartmentName
having avg(Salary) >= 10000			---聚合函数条件 需用having关键字	
order by avg(Salary) desc			---平均工资降序

自连接

Create table Dept
(
	DeptID int primary key,	----部门编号
	DeptName varchar(10),	----部门名称
	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,'硬件开发部',2)
insert into Dept(DeptID,DeptName,ParentID) values(6,'硬件测试部',2)
select * from Dept A inner join Dept B on...
select A.DeptID 部门编号,A.DeptName 部门名称,B.DeptName 上级部门名称 from Dept A inner join Dept B on A.ParentID = B.DeptID