ORACLE SQL大全

nics

简单查询　　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