<span style="font-size:18px;">第5节 分组函数1.分组函数：avg count max min sum2.使用group by 进行分组3.使用having 进行分组数据的过滤–1.avg()/sum():只试用与number类型的数据SELECT AVG(salary) , sum(salary)FROM employees;select avg(last_name) from employees;–2.max() min() count() :适用于number,Date类型 varchar2类型 select max(salary) , min(salary) , max(last_name) , min(last_Name) ,max(hire_date) , min (hire_date)from employees–count 在累加个数时不计算空值select count (employee_id) , count (salary) , count(1) , count(2),count(*) , count(commission_pct)from employees;select commission_pctfrom employeeswhere commission_pct is not null ;–结论:avg = sum / countselect avg(salary) , sum(salary)/count(salary)from employeesselect avg(commission_pct) , sum(commission_pct)/count(commission_pct),sum(commission_pct)/count(1)from employees;select count(nvl(commission_pct , 0))from employees;select count(distinct nvl(department_id , 0))from employees;–2.使用group by — 求出employees表中各部门的平均薪资—-结论：如果在查询项里出现了分组函数以外的列，–那么分组函数以外的列都应该出现在group by当中，否则出错。select department_id , avg(salary)from employeesgroup by department_id;select job_id , avg(salary)from employeesgroup by job_id;select department_id , job_id , avg(salary)from employeesgroup by department_id , job_id ;–包含在group by 子句中的列不必包含在select 列表中select department_id , avg(salary)from employeesgroup by department_id , job_id;–3使用having 进行过滤–如果过滤条件中出现了分组函数，使用having 替代whereselect department_id , avg(salary)from employees–having avg(salary)>600group by department_idhaving avg(salary)>600;–部门最高工资比1000高的部门select department_id , max (salary)from employeeshaving max(salary)>10000group by department_id;–分组函数可以嵌套select max(avg(salary))from employeesgroup by department_id;练习–查询公司员工工资的最大值，最小值，平均值，总和select max (salary) , min (salary) ,avg(salary),sum(salary)from employees;–查询各job_id的员工工资的最大值，最小值，平均值，总和select job_id , max(salary) , min(salary) , avg(salary) , sum(salary)from employees group by job_id;–选择具有各个job_id的员工人数select job_id , count(employee_id)from employeesgroup by job_id;–查询员工最高工资和最低工资的差距（DIFFERENCE）select max(salary) , min(salary) , max(salary)-min(salary)"difference"from employees;–查询各个管理者手下员工的最低工资，其中最低工资不能低于–6000，没有管理者的员工不计算在内select manager_id , min(salary)from employeesgroup by manager_idhaving min(salary) >= 6000 and manager_id is not null;–查询所有部门的名字，location_id，员工数量和工资平均值select department_name , location_id , count(employee_id) , avg(salary)from departments d join employees eon e.department_id = e.department_idgroup by location_id ,department_name ;–查询公司在1995-1998年之间，每年雇用的人数，结果类似下面的格式select hire_date , count(employee_id)from employeesgroup by hire_datehaving hire_date between to_date('1995','yyyy') and to_date('1998','yyyy');select count(1) , count(decode( to_char(hire_date , 'yyyy'),'1995',1 ,null ))"1995", count(decode( to_char(hire_date , 'yyyy'),'1996',1 ,null ))"1996", count(decode( to_char(hire_date , 'yyyy'),'1997',1 ,null ))"1997", count(decode( to_char(hire_date , 'yyyy'),'1998',1 ,null ))"1998" from employees where to_char(hire_date , 'yyyy') IN('1995' , '1996' , '1997' ,'1998');*******************************************************第六节 子查询–子查询–外查询select last_name , salaryfrom employeeswhere salary >(–内查询SELECT salaryfrom employeeswhere last_name='Abel');–查询last_name 为'Chen'的manager的信息select manager_idfrom employeeswhere last_Name = 'Chen';select last_name , salaryfrom employeeswhere employees_id = 108;–自连接select e2.last_name , e2.salaryfrom employees e1 , employees e2 where e1.manager_id = e2.manager_id and e1.last_name = 'Chen';–子查询select last_name , salary from employeeswhere employee_id = (select manager_idfrom employeeswhere last_name = 'Chen');–如何书写一个包含子查询的select语句？第一种方式：–从里向外写 第二种方式：从外向里写。–返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资select last_name , job_id , salaryfrom employees where job_id = (select job_idfrom employeeswhere employee_id = 141) select last_name , job_id ,salaryfrom employees where job_id = (select job_idfrom employeeswhere employee_id = 141)and salary > (select salaryfrom employeeswhere employee_id =143)–返回公司工资最少的员工的last_name,job_id和salaryselect last_name , job_id ,salaryfrom employeeswhere salary = (select min(salary)from employees)–查询最低工资大于50号部门最低工资的部门id和其最低工资select department_id , min(salary)from employees group by department_idhaving min(salary) > (SELECT min(salary)from employeesgroup by department_id—???????having department_id = 50)–如下的操作是错误的select employee_id , last_name from employeeswhere salary = (select MIN(salary)from employeesgroup by department_id)select last_name , job_idfrom employeeswhere job_id = (select job_idfrom employeeswhere last_name = 'Haas')–查询工资最低的员工信息: last_name, salaryselect last_name , salaryfrom employeeswhere salary = (select min(salary)from employees)–查询平均工资最低的部门信息select * from departmentswhere department_id = (select department_idfrom employeesgroup by department_idhaving avg(salary) = (select min(avg(salary))from employeesgroup by department_id))–查询平均工资最低的部门信息和该部门的平均工资select * from departmentswhere department_id = (select department_idfrom employeesgroup by department_idhaving avg(salary) = (select Min(avg(e.salary))from employees egroup by department_id))第六节–谁的工资比Abel的高–子查询–外查询select last_name , salaryfrom employeeswhere salary > (–内查询select salaryfrom employeeswhere last_Name = 'Abel')–查询last_name为'Chen'的manager的信息select *from employeeswhere employee_id IN (select manager_idfrom employeeswhere last_name = 'Chen')–自连接select d.*from employees e , employees dwhere e.manager_id = d.employee_id and e.Last_name = 'Chen'–子查询select last_name , salary from employeesgroup by manager_idhaving employee_id = (select manager_idfrom employeeswhere last_name = 'Chen')select *from employees where employee_id = (select manager_idfrom employeeswhere last_name ='Chen')–返回job_id与141号员工相同，，–salary比143号员工多的员工姓名，job_id 和工资select last_name , job_id , salaryfrom employeeswhere job_id in(select job_idfrom employeeswhere employee_id = 141) and salary > (select salaryfrom employeeswhere employee_id =143)select last_name , job_id salaryfrom employeeswhere job_id = (select job_idfrom employeeswhere employee_id = 141)and salary > (select salaryfrom employeeswhere employee_id = 143)–返回公司工资最少的员工的last_name,job_id和salaryselect last_name ,job_id ,salaryfrom employeeswhere salary = (select min(salary)from employees)–查询最低工资大于–50号部门最低工资–的部门id和其最低工资select department_id , min(salary)from employees group by department_idhaving min(salary) > (select min(salary)from employeeswhere department_id = 50)select last_name , job_id FROM employeeswhere job_id = (select job_idfrom employeeswhere last_name = 'Chen')–查询工资最低的员工信息: last_name, salaryselect last_name , salaryfrom employeeswhere salary = (select min(salary)from employees)–查询平均工资最低的部门信息select * from departmentswhere department_id = (select department_idfrom employeesgroup by department_idhaving avg(salary)= (select min(avg(salary))from employeesgroup by department_id))–查询平均工资最低–的部门信息和该部门的平均工资select d.* , ( select avg(salary)from employees ewhere e.department_id =d.department_id)from departments d where department_id = (select department_idfrom employees e1group by department_idhaving avg(salary) = (select min(avg(salary))from employeesgroup by department_id))–查询平均工资最高的 job 信select *from jobswhere job_id = (select job_idfrom employeesgroup by job_idhaving avg(salary) = (select min(avg(salary))from employeesgroup by job_id))–查询平均工资高于公司平均工资的部门有哪些?select department_name from departmentswhere department_id IN(select department_idfrom employeesgroup by department_idhaving avg(salary)> (select min(avg(salary))from employeesgroup by department_id))–查询出公司中所有 manager 的详细信息.select * from employeeswhere employee_id in (select manager_idfrom employeesgroup by manager_id)–各个部门中 最高工资中最低的那个部门的 –最低工资是多少select min(salary)from employeesgroup by department_id having department_id =(select department_idfrom employeeswhere salary = (select max(salary)from employees))–查询平均工资最高的部门的 manager– 的详细信息: last_name, department_id, email, salary select last_name , department_id , email , salaryfrom employeeswhere employee_id in (select distinct manager_idfrom employeeswhere department_id = (select department_idfrom employeesgroup by department_idhaving avg(salary) = (select max(avg(salary))from employeesgroup by department_id)))select last_name, department_id, email, salaryfrom employeeswhere employee_id in (select distinct manager_idfrom employeeswhere department_id = (select department_idfrom employeesgroup by department_idhaving avg(salary) = (select max(avg(salary))from employeesgroup by department_id)))–查询 1999 —–年来公司的人——所有员工的最高工资—的那个员工的信息.–1.查询出1999年来公司的职工的最高工资–2.找到最高工资为11000的员工select *from employeeswhere employee_id in(select employee_idfrom employees e1where salary = (select min(salary)from employees e2where hire_date like '%97')and hire_date like '%97')–返回其它job_id中比job_id为‘IT_PROG’–的任一工资低的员工的员工号、姓名、job_id–这样是不对的select min(salary),hire_datefrom employeesgroup by hire_dathavinghire_date like '%99'–这是个对的select salary,hire_datefrom employeeswhere salary = (select min(salary)from employees e2where hire_date like '%99') and hire_date like '%97'–查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.–1.查询出1999年来公司的职工的最高工资–2.找到最高工资为11000的员工select *from employeeswhere salary in (select max(salary)from employeeswhere to_char(hire_date , 'yyyy') = '1999')and to_char(hire_date , 'yyyy') = '1999'–返回其它job_id中比job_id为‘IT_PROG’的任一工资低的员工–的员工号、姓名、job_id 以及salaryselect job_id ,employee_id , last_name , job_id ,salaryfrom employeeswhere salary > (select min(salary)from employeesgroup by job_idhaving job_id = 'IT_PROG')and job_id <> 'IT_PROG'练习–查询和Zlotkey相同部门的员工姓名和雇用日期select last_name , hire_datefrom employeeswhere department_id = (select department_idfrom employeeswhere last_name ='Zlotkey')–查询工资—比公司平均工资–高的员工的员工号，–姓名和工资。select employee_id , last_Name , salaryfrom employeeswhere salary >(select avg(salary)from employees)–查询各部门中工资比本部门平均工资–高的员工的员工号, 姓名和工资select employee_id , last_name , salaryfrom employees where salary > (select avg(salary)from employees)–查询和姓名中包含字母u的员工在相同部门的–员工的员工号和姓名select employee_id , last_namefrom employeeswhere department_id in (select department_idfrom employeeswhere last_name like '%u%')–查询在部门的location_id为1700的部门–工作的员工的员工号select employee_id from employeeswhere department_id IN (select distinct department_idfrom departmentswhere location_id = 1700)–查询管理者是King的员工姓名和工资select last_name , salaryfrom employeeswhere manager_id in (select employee_idfrom employeeswhere last_name = 'King')</span>
当你能梦的时候就不要放弃梦
