屌丝学编程,SQL视频学习笔记
一、笛卡尔乘积
select * from People,Department
此查询结果会将People表的所有数据和Department表的所有数据进行依次排列组合形成新的记录。例如People表有10条记录,Department表有3条记录,则排列组合之后查询结果会有10*3=30条记录。
二、简单多表查询
此种查询不符合主外建关系的数据不会被显示
查询员工信息,同时显示部门名称
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.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
四、外连接
外连接分为左外连接、右外连接和全外连接。
左外联接:以左表为主表显示全部数据,主外键关系找不到数据的地方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
右外连接(right join):右外连接和左外连接类似,A left join B == B right join A
全外连接(full join):两张表的所有数据无论是否符合主外键关系必须全部显示,不符合主外键关系的地方null取代。
五、多表查询综合示例
(1)查询出武汉地区所有的员工信息,要求显示部门名称以及员工的详细资料
select PeopleId 员工编号,DepartmentName 部门名称,PeopleName 员工姓名,PeopleSex 员工性别,
PeopleBirth 生日,PeopleSalary 月薪,PeoplePhone 电话,PeopleAddress 地址
from People
left join Department on People.DepartmentId=Department.DepartmentId
(2)查询出武汉地区所有的员工信息,要求显示部门名称,职级名称以及员工的详细资料
select PeopleId 员工编号,DepartmentName 部门名称,RankName 职级名称,PeopleName 员工姓名,PeopleSex 员工性别,
PeopleBirth 生日,PeopleSalary 月薪,PeoplePhone 电话,PeopleAddress 地址
from People
left join Department on People.DepartmentId=Department.DepartmentId
left join [Rank] on People.RankId=[Rank].RankId
where PeopleAddress='武汉'
(3)根据部门分组统计员工人数,员工工资总和,平均工资,最高工资和最低工资。
select 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
(4)根据部门分组统计员工人数,员工工资总和,平均工资,最高工资和最低工资,平均工资在10000 以下的不参与统计,并且根据平均工资降序排列。
select 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)>10000
order by avg(PeopleSalary) desc
(5)根据部门名称,然后根据职位名称,分组统计员工人数,员工工资总和,平均工资,最高工资和最低工资
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 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
select A.DeptId 部门编号,A.DeptName 部门名称,B.DeptName 上级部门 from Dept A
inner join Dept B on A.ParentId=B.DeptId