1.组函数
- avg 求平均值------其中会自动去掉空值和个数
- count 求总数
返回表中记录不为空的总数
select count(employee_id),count(*),count(1)
from employees;
返回员工的部门个数
select count(distinct department_id)----这里返回的是非空且不重复的数值
from employees - max 求最大值
- min 求最小值
- sum 求和
1.select avg(salary),max(salary),min(salary),sum(salary)
from employees
上面这个语句是分别求得薪水得平均值,最大值,最小值,总和
max和min不止可以用来计算number,还可以计算char和date
avg和sum只能够用来计算number型得 - stddev 用来求标准差,可以用做聚集和分析函数
- 组函数是可以嵌套的例如:max(avg(salary))
2.分组数据
- group by的使用
求出employees表中各部门的平均工资
select department_id,avg(salary)
from employees
group by department_id
如果你要使用过滤条件,要在from后面添加 例如 where department_id in(40,60,80)
如果你想进行多个分组就在group by后面添加想要分组的组名就可以了
查询的列,只要没有使用组函数,就必须放在group by当中 - having的使用
不能在where子句中使用组函数,但是可以在having子句中是使用组函数。
–求出各部门中平均工资大于6000的部门,以及其平均工资
select department_id,avg(salary)
from employees
having avg(salary)>6000
group by department_id
–查询公司在1995年到1998年之间的总人数,以及每年雇佣的人数,表头是总数,年数,下面是人数
select count(1) total,
count(case hire_date when to_date(‘1995’,‘yyyy’) then 1 else null end ) “1995”,
count(case to_char(hire_date,‘yyyy’) when ‘1995’ then 1 else null end) “1995555”,
count(case to_char(hire_date,‘yyyy’) when ‘1996’ then 1 else null end) “1996”,
count(case to_char(hire_date,‘yyyy’) when ‘1997’ then 1 else null end) “1997”,
count(case to_char(hire_date,‘yyyy’) when ‘1998’ then 1 else null end) “1998”
from employees
where to_char(hire_date,‘yyyy’) in (‘1995’,‘1996’,‘1997’,‘1998’)
在做这个题的时候,一开始打算把字符转换成date结果是错误的,不知道怎么修改,只好改成to_char了
3.子查询
1. 首先先写一个简单的子查询
–谁的工资比Abel的高
按照平常的思路,要先查出Abel的工资,然后进行比较,最终得出谁的工资比Abel的工资高,这里我将使用一个子查询
select last_name,salary
from employees
where salary >(select salary
from employees
where last_name=‘Abel’)
子查询在主查询之前一次执行完成,子查询要包含在括号内,通常在右侧
子查询的结果被主查询使用。
2. 单行子查询
主要有=,>,<,不等于,大于等于,小于等于等操作符
–返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
select job_id,salary
from employees
where job_id =(select job_id
from employees
where employee_id=141)
and salary>(select salary
from employees
where employee_id=143)
–查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary)
from employees
group by department_id
having min(salary)>(select min(salary)
from employees
where department_id=‘50’)
3. 多行子查询
1.in 等于列表中的任意一个
2.any 和子查询返回的某一个值比较
–返回其他部门中比job_id为’IT_PROG’部门任一工资低的员工的员工号
–姓名,job_id,以及salary
select employee_id,last_name,job_id,salary
from employees
where job_id <>‘IT_PROG’
and salary < any(select salary
from employees
where job_id=‘IT_PROG’)
3.all 和子查询返回的所有值比较
–返回其他部门中比job_id为’IT_PROG’部门所有工资低的员工的员工号
–姓名,job_id,以及salary
select employee_id,last_name,job_id,salary
from employees
where job_id <>‘IT_PROG’
and salary < all(select salary
from employees
where job_id=‘IT_PROG’)
4.注意事项
单行和多行子查询的时候要注意空值的问题,单行子查询不能返回多条
几个重要语句
1.查询平均工资最低的部门信息和该部门的平均工资
第一步 :首先查出各个部门的平均工资
第二部:查出最低的平均工资
第三步:把最低的平均工资的部门号给查出来
第四步,把部门信息给输出
第五步,添加该部门的平均工资
select d.*,(select avg(salary) from employees where department_id=d.department_id)
from departments d
where d.department_id=(
select e.department_id
from employees e
having avg(salary)=(select min(avg(salary))
from employees
group by department_id
)
group by e.department_id
)
2.查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
select employee_id,last_name,salary
from employees e1
where salary>(
select avg(salary)
from employees e2
where e2.department_id=e1.department_id
group by department_id
)