Oracle从小白到精通第三天（组函数和子查询）（尚硅谷学习Oracle笔记）

1.组函数

1. avg 求平均值------其中会自动去掉空值和个数
2. count 求总数
   返回表中记录不为空的总数
   select count(employee_id),count(*),count(1)
   from employees;
   返回员工的部门个数
   select count(distinct department_id)----这里返回的是非空且不重复的数值
   from employees
3. max 求最大值
4. min 求最小值
5. sum 求和
   1.select avg(salary),max(salary),min(salary),sum(salary)
   from employees
   上面这个语句是分别求得薪水得平均值，最大值，最小值，总和
   max和min不止可以用来计算number，还可以计算char和date
   avg和sum只能够用来计算number型得
6. stddev 用来求标准差，可以用做聚集和分析函数
7. 组函数是可以嵌套的例如：max（avg（salary））

2.分组数据

1. 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当中
2. 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
)