数据库二

1、聚合函数

--（1）求员工的总人数
select count(*) from People

--（2）求最大值，最高工资
select max(PeopleSalary) from People

--（3）求最小值，最低工资
select min(PeopleSalary) from People

--（4）求和，求所有员工的工资总和
select sum(PeopleSalary) from People

--（5）求平均值，求所有员工的平均工资
select avg(PeopleSalary) from People

--（6）求总人数，最大值、最小值、总和、平均值、在一行显示
select count(*)总人数,max(PeopleSalary)最大值,min(PeopleSalary)最小值,
sum(PeopleSalary)总和,avg(PeopleSalary)平均值 from People

--（7）查询出上海地区的员工人数、总工资、最高工资、最低工资和平均工资
select count(*)总人数,max(PeopleSalary)最大值,min(PeopleSalary)最小值,
sum(PeopleSalary)总和,avg(PeopleSalary)平均值 from People where PeopleAddress ='上海'

--（8）求出工资比平均工资高的人员信息
select * from People where PeopleSalary > (select avg(PeopleSalary) from People)

--（9）求数量，年龄最大值，年龄最小值、年龄总和、年龄平均值、在一行显示
select COUNT(*)数量,
max(year(getdate())-year(PeopleBirth))最大年龄,
min(year(getdate())-year(PeopleBirth))最小年龄,
sum(year(getdate())-year(PeopleBirth))年龄总和,
avg(year(getdate())-year(PeopleBirth))平均年龄
from People

--（10）计算月薪在10000以上的男性员工的最大年龄，最小年龄和平均年龄
select COUNT(*)数量,
max(year(getdate())-year(PeopleBirth))最大年龄,
min(year(getdate())-year(PeopleBirth))最小年龄,
avg(year(getdate())-year(PeopleBirth))平均年龄
from People 
where PeopleSalary>=10000 and PeopleSex='男'

--（11）统计出所在地“武汉或上海 的所有女员工数量以及最大年龄，最小年龄和平均年龄
select '武汉或上海的女员工'描述, 
COUNT(*)数量,
max(year(getdate())-year(PeopleBirth))最大年龄,
min(year(getdate())-year(PeopleBirth))最小年龄,
sum(year(getdate())-year(PeopleBirth))年龄总和,
avg(year(getdate())-year(PeopleBirth))平均年龄
from People 
where PeopleSex='女' and PeopleAddress in('武汉','上海')

--（12）求年龄比平均年龄高的人员信息
select * from People where year(getdate())-year(PeopleBirth)>
(select avg(year(getdate())-year(PeopleBirth)) from People)

 2、分组查询

--（1）根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资
--group by分组
select PeopleAddress 地区, count(*) 员工人数,sum(PeopleSalary)工资总和,avg(PeopleSalary)平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
from People 
group by PeopleAddress

--（2）根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资和最低工资，1985年及以后的员工不参与统计。
select PeopleAddress 地区, count(*) 员工人数,sum(PeopleSalary)工资总和,avg(PeopleSalary)平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
from People 
where PeopleBirth<'1985-1-1'
group by PeopleAddress --把上面的结果查询出来后再分组

--（3）根据员工所在地区分组统计员工人数，员工工资总和、平均工资、最高工资、最低工资
--要求筛选出员工人数至少在2人及以上的记录，并且1985年及以后出生的员工不参与统计。
select PeopleAddress 地区, count(*) 员工人数,sum(PeopleSalary)工资总和,avg(PeopleSalary)平均工资,max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
from People 
where PeopleBirth<'1985-1-1'
group by PeopleAddress
having count(*)>=2

 3、简单多表查询，内连接和外连接

--简单多表查询

--查询员工信息，显示部门名称
select * from People,Department where People.DeparementId = Department.DeparementId

--查询员工信息，显示职级名称
select * from People,[Rank] where People.RankId = [Rank].RankId

--查询员工信息，显示部门名称，显示职级名称
select * from People,Department,[Rank] where People.DeparementId = Department.DeparementId 
and People.RankId = [Rank].RankId

--内连接查询

--查询员工信息，显示部门名称
select * from People 
inner join Department on  People.DeparementId = Department.DeparementId

--查询员工信息，显示职级名称
select * from People
inner join [Rank] on People.RankId = [Rank].RankId

--查询员工信息，显示部门名称，显示职级名称
select * from People
inner join Department on People.DeparementId = Department.DeparementId
inner join [Rank] on  People.RankId = [Rank].RankId

--简单多表查询和内连接共同的特点：不符合主外键关系的数据都不会被显示出来


--外连接（左外连，右外连，全外连）
--左外连：以左表为主表进行数据显示，主外键关系找不到的数据null取代

--查询员工信息，显示部门名称
select * from People 
left join Department on  People.DeparementId = Department.DeparementId

--查询员工信息，显示职级名称
select * from People
left join [Rank] on People.RankId = [Rank].RankId

--查询员工信息，显示部门名称，显示职级名称
select * from People
left join Department on People.DeparementId = Department.DeparementId
left join [Rank] on  People.RankId = [Rank].RankId

--右连：A left join B = B right join A
--下面两个查询含义相同
select * from People
left join Department on People.DeparementId=Department.DeparementId

select * from Department
right join People on People.DeparementId=Department.DeparementId

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

4、多表查询总和示例