卷王D5--数据库（从入门到跑路）

SQL sever学习-基础篇1（day5）

11、聚合函数

count：求数量
max：求最大值
min：求最小值
sum：求和
avg：求平均值

--求员工总人数
SELECT COUNT(*) FROM PEOPLE
--求最大值，求工资最大值
SELECT MAX(PEOPLESALARY) 最高工资 FROM PEOPLE
--求最小值，求工资最小值
SELECT MIN(PEOPLESALARY) 最低工资 FROM PEOPLE
--求和，求所有员工的工资总和
SELECT SUM(PEOPLESALARY) 工资总和 FROM PEOPLE
--求平均值，求所有员工的平均值(工资的数据小数保留位数太多，采用round函数)
SELECT AVG(PEOPLESALARY) 平均工资 FROM PEOPLE
SELECT ROUND(25.5555,2)--保留两位小数
SELECT ROUND(AVG(PEOPLESALARY),2) FROM PEOPLE--保留两位小数
--求数量，最大值，最小值，总和，平均值，在一行显示
SELECT COUNT(*) 数量,MAX(PEOPLESALARY) 最高工资,MIN(PEOPLESALARY) 最低工资,SUM(PEOPLESALARY) 工资总和,ROUND(AVG(PEOPLESALARY),2) 平均工资 FROM PEOPLE
--查询出中国地区的员工总人数，总工资，最高工资，最低工资和平均工资
SELECT COUNT(*) 数量,MAX(PEOPLESALARY) 最高工资,MIN(PEOPLESALARY) 最低工资,SUM(PEOPLESALARY) 工资总和,ROUND(AVG(PEOPLESALARY),2) 平均工资 FROM PEOPLE
WHERE PEOPLEADRESS = '中国'
--求出工资 比平均工资高的人的信息
SELECT * FROM PEOPLE
WHERE PEOPLESALARY > (SELECT ROUND(AVG(PEOPLESALARY),2) FROM PEOPLE)
--求数量，年龄最大值，年龄最小值，年龄总和，年龄平均值，在一行显示
SELECT *,YEAR(GETDATE())-YEAR(PEOPLEBIRTH) 年龄 FROM PEOPLE
SELECT COUNT(*) 数量,MAX(YEAR(GETDATE())-YEAR(PEOPLEBIRTH)) 年龄最大值,MIN(YEAR(GETDATE())-YEAR(PEOPLEBIRTH)) 年龄最小值,SUM(YEAR(GETDATE())-YEAR(PEOPLEBIRTH)) 年龄总和,
AVG(YEAR(GETDATE())-YEAR(PEOPLEBIRTH)) 年龄平均值
FROM PEOPLE
--计算出月薪在10000以上的男性员工的最大年龄，最小年龄和平均年龄
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 PEOPLESALARY >= 10000
--统计出所在地在“武汉和上海”的所有女员工数量以及最大年龄，最小年龄和平均年龄
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 (PEOPLEADRESS IN ('中国' ,'北京'))
--求出年龄比平均年龄高的人员信息
SELECT * FROM PEOPLE
WHERE YEAR(GETDATE())-YEAR(PEOPLEBIRTH) > (SELECT AVG(YEAR(GETDATE())-YEAR(PEOPLEBIRTH)) FROM PEOPLE)