作者:gqk:
什么是组函数:
用于一组数据,并对一组数据返回一个值:
组函数类型:
- AVG:求平均值
- COUNT:计数
- MAX:最大值
- MIN:最小值
- SUM:求和
--查询所有工资的总和
SELECT SUM(salary) FROM employees;
--组函数
SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(salary)
FROM employees
WHERE department_id=50;
--所有组函数都是忽略空值的
SELECT COUNT(commission_pct) FROM employees;
--查询所有没有奖金的人数
SELECT COUNT(*)-COUNT(commission_pct) FROM employees;
SELECT COUNT(*) FROM employees WHERE commission_pct IS NULL;
--查询部门的总数(统计不重复的计数)
SELECT COUNT(DISTINCT department_id) FROM employees;
--以下是错误的写法,两列的行数不兼容
--SELECT employee_id,SUM(salary) FROM employees;
分组查询:
--查询每个部门的ID,员工工资总和,最高工资
SELECT department_id,SUM(salary) sal_sum,MAX(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
ORDER BY sal_sum DESC;
--多个分组表达式
SELECT department_id,job_id,SUM(SALARY)
FROM employees
GROUP BY department_id,job_id;
--查询每个部门的部门名称,人数:
SELECT d.department_name,count(e.employee_id) emp_count
FROM employees e
INNER JOIN departments d ON e.department_id=d.department_id
GROUP BY d.department_name;
--统计每年入职的人数:年份,人数
SELECT to_char(hire_date,'yyyy') 年份,count(*) 人数
FROM employees
GROUP BY to_char(hire_date,'yyyy')
ORDER BY 1;
非法使用组函数:
- 不能在where子句中使用组函数
- 可以在having子句中使用组函数
--查询部门编号,每个部门的平均共组,平均工资大于8000的
select department_id, avg(salary) from employees group by department_id having avg(salary)>8000 and department_id is not null;
--统计每年入职的人数:年份,人数(仅返回入职不少于2人的年份的数据)
select to_char(hire_date,'yyyy'),count(*)
from employees
group by to_char(hire_date,'yyyy')
having count(*)>=2;
--WHERE条件:过滤分组之前的数据
--HAVING条件:过滤分组之后的数据
--查询每个部门的ID,员工工资总和(where 可以存放在 分组之前)
select department_id,sum(salary)
from employees
group by department_id
having department_id is not null;
子查询:
--查询谁的工资比Abel高?(先查询Abel的工资,再去查询比他高的值)
select salary from employees where last_name='Abel';
select * from employees where salary>(select salary from employees where last_name='Abel');
--查询工资最低的员工(先查询最低工资为多少,在查询最低工资的员工)
select min(salary) from employees;
SELECT *
FROM employees
WHERE salary = (SELECT MIN(salary)
FROM employees);
--查询和149号员工同一个部门的其他员工
- 先去查询149号员工所在的部门编号:
SELECT department_id
FROM employees
WHERE employee_id = 149
- 再查询80号员工部门的员工出过149号员工
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE employee_id = 149)
AND employee_id <> 149;
--查询比202号员工的部门经理的工资还要高的员工
- 查询202号员工所在的部门编号(20号部门)
- 查询20号部门的员工经理id(201号)
- 查询经理的工资
- 查询还要高的员工
SELECT *
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE employee_id =
(SELECT manager_id
FROM departments
WHERE department_id =
(SELECT department_id
FROM employees
WHERE employee_id = 202)));
--查询员工编号,姓名,工资,工资总和(在查询列表中使用子查询,只能是单行单列)
SELECT employee_id,last_name,salary,
(SELECT SUM(salary) FROM employees) AS 工资总和
FROM employees;
--查询工资最高的前5名的员工(临时视图,内联视图)
SELECT *
FROM (SELECT *
FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 5;
--查询员工表中第6到第12条数据
SELECT *
FROM (SELECT ROWNUM rnum,
e.*
FROM employees e)
WHERE rnum BETWEEN 6 AND 12;
--推荐以下写法,效率较高
SELECT *
FROM (SELECT ROWNUM rnum,
e.*
FROM employees e
WHERE ROWNUM <= 12)
WHERE rnum >= 6;
--查询工资最高的第6到第12条员工
SELECT *
FROM (SELECT rownum rnum,
e.*
FROM (SELECT *
FROM employees
ORDER BY salary DESC) e
WHERE rownum <= 12)
WHERE rnum >= 6;
--查询所有是部门经理的员工(多行子查询)
SELECT *
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM departments);
--查询所有不是部门经理的员工(多行子查询)
SELECT *
FROM employees
WHERE employee_id NOT IN
(SELECT manager_id
FROM departments
WHERE manager_id IS NOT NULL);
--ANY逻辑或比较 >ANY(50,80,90)
--查询大于60号部门任何一个员工工资的员工
SELECT *
FROM employees
WHERE salary > ANY (SELECT salary
FROM employees
WHERE department_id = 60);
--ALL逻辑与比较 >ALL(50,80,90)
--查询所有员工人数不少于3人的部门信息
SELECT *
FROM departments
WHERE department_id IN (SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 3);