SQL开发模板
查询操作
 简单查询
  1、选择所有 select * from table;
  2、选择部分列 select column1,column2 from table;
  3、使用算数表达式 select salary+300 from emlpoyees；
  4、使用 ()select 12*(salary+20) from emlpoyees；
  5、使用列别名 select first_name Fname from employees；
  6、使用字符串连接符 select first_name || last_name from employees;
  7、过滤重复  select distinct department_id from employees；
  8、where子句查询数据 select employee_id,last_name,job_id from employees where department_id=90；
  9、order by 子句排序 select last_name from employees order by hire_date；
  10、group 分组查询
  
    select avg (salary),
       max (salary),
       min (salary),
       sum (salary),
       count (*),
       count (commission_pct)
       from employees where job_id like ‘%REP%’；
    
    
    select department_id,
        avg (salary)
        from employees group by department_id;
        
        
    select department_id,
        max(salary)
        from employees
        group by department_id having max(salary) >10000;
        
    select job_id ,
        sum(salary) payroll
        from employees
        where job_id not like '%rep%'
        group by job_id having sum(salary)>13000
        order by sum(salary);
        
  11、子查询
    单行子查询
     select last_name
         from employees
         where salary>(select salary from employees where last_name ='Abel');
         
     select last_name ,job_id,salary
          from employess
          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 employess
         where salary= (select min(salary) from employees);
         
     select department_id,min(salary)
         from employees
         group by department_id
         having min(salary)>(select min(salary) from employees where department_id=50);
         
    多行子查询
    
     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';
                    
     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';
     select emp.last_name
         from employees emp
         where emp.employee_id not in (select mgr.manager_id from employees mgr);
         
  12、Top-N 查询
  
    select rownum as rank,last_name,salary
         from (select last_name,salary from employees order by salary desc)
         where rownum<=3;