第四节:分组函数: select [column,]group_function(column),... from table where condition group by column order by column; select avg(salary) ,max(salary),min(salary),sum(salary) from employees where job_id where '%REP%'; select min(hire_date),max(hire_date) from employees; select count(*) from employees where department_id=50 select count(commission_pct) from employees where department_id=80; select count( distinct department_id) from employees; select avg(nvl(commission_pct,0)) from employees; select department_id dept_id,job_id,sum(salary) from employees group by department_id,job_id; select department_id dept_id,max(salary) from employees group by department_id having max(salary)>1000 order by sum(salary); 第五章: 关联查询 交叉查询: 使用Using字句: select empno,ename,dname ,loc from dept join emp using (deptno); 备注两个表中均有deptno列. select e.empno,e.ename,d.dname ,d.loc from dept d join emp e using (deptno); 使用自然连接: select empno,ename,dname ,loc from dept natural join emp; 使用On字句连接: select e.empno,e.ename,d.dname ,d.loc from dept d join emp e on (e.deptno=d.deptno) ; 使用on进行自关联 select e.last_name emp,m.last_name mgr from employees e join employees m on (e.manager_id=m.employee_id) ; 带条件的关联: select e.empno,e.ename,d.dname ,d.loc from dept d join emp e on (e.deptno=d.deptno) and e.deptno=24; 使用on多个表关联 select employee_id,city,department_name from employees e join departments d on d.department_id =e.department_id join locations l on d.location_id=l.location_id; 使用on作为条件的非连接用法 select last_name,e.salary ,j.grade_level from employees e join job_grades j on e.salary between j.lowest_sal and j.highest_sal; 左外连接: select e.last_name,e.department_id,d.department_name from employees e left outer join departments d on( e.department_id=d.department_id); 右外连接 select e.last_name,e.department_id,d.department_name from employees e right outer join departments d on( e.department_id=d.department_id); 全外连接: select e.last_name,e.department_id,d.department_name from employees e full outer join departments d on( e.department_id=d.department_id); 笛卡儿积形成原因: 1.连接的条件的无效. 2.连接的条件遗漏 3.两表记录交叉连接. 第六章子查询 select last_name,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 ) 带函数的子查询 select last_name,job_id,salary from employees where salary=(select min(salary) from employees); 带having条件的子查询 select deparment_id,min(salary) from employees group by department_id having min(salary)> ( select min(salary) from employees where department_id=50 ); 使用any操作 select employee_id,last_name,job_id,salary from employees where salary< any( select salary from employees where job_id ='IT_PROG' ) and job_id<> 'IT_PROG'; 使用all操作 select employee_id,last_name,job_id,salary from employees where salary< all( select salary from employees where job_id ='IT_PROG' ) and job_id<> 'IT_PROG';
?