Oracle常用代码总结

java开发中的总结 大家可以看看哦

建表语句：
1.bak_emp:
create table bak_emp(empno number(4) primary key,ename varchar2(30),job varchar2(30),mgr number(4),hiredate date,sal number(7,2),comm number(5,2),deptno number(4));
2.acount_asscess:
create table account_asscess(accountno char(15) not null,flag int,money number(10,2));
3.depositor:
create table depositor(accountno char(15) primary key,money number(10,2) not null);
基础查询：
select * from guyuan;（全部查询）
select employee_id,job_id from guyuan;（按列名查询）
select fast_name 姓名,job_id 工作，department_id 部门 from guyuan;（列别名查询）

简单查询：
select * from guyuan where salary between 8000 and 30000;（临界条件查询）
select * from guyuan where department_id = 30;（根据值查询）
select * from guyuan where fast_name = 'Berry';
select * from guyuan where salary <= 6000;（模糊范围查询）
select * from guyuan where salary between 5000 and 30000;（between。。。and...）
select * from guyuan where manager_id in(1001,1003);（in指定值查询）
select * from guyuan where fast_name like '_e%';（第二个字符为e的查询）
select * from guyuan where fast_name like 'B%';（首字母为B的查询）
select * from guyuan where manager_id is null;（列中空值查询）
select * from guyuan where salary >=9000 and job_id like '%项目%';（字符串查询）
select * from guyuan where job_id not in('项目经理','董事');（not in查询）
select * from guyuan where job_id = '项目经理' or job_id = '开发人员';（or查询）
select * from guyuan where job_id like '%项目%' or job_id like '开发%' and salary > 6000;（条件查询+or+and）
select fast_name,job_id,salary from guyuan order by salary;（分组排序 升序）
select fast_name,job_id,salary from guyuan order by salary desc;（分组排序 降序）
select fast_name,job_id,salary,hiredate from guyuan order by hiredate;
select fast_name,salary,commission,salary+commission total from guyuan order by total;（分组排序+组函数）
select fast_name,salary,commission,salary+commission total from guyuan order by total desc;
select employee_id,fast_name,salary,department_id from guyuan order by salary,department_id desc;（多条件分组排序+组函数）
select distinct department_id,job_id from guyuan;（取消重复）
select fast_name,job_id,salary,mod(salary,5000) from guyuan;(整除取余函数)
select fast_name,hiredate from guyuan;(日期函数)
select fast_name,hiredate,sysdate,(-(sysdate-hiredate))/7 from guyuan;(系统时间，雇用周)
select add_months('1-1月-2011',8) from dual;(add_months函数)


简单函数：
select employee_id,fast_name from guyuan where LOWER(fast_name) = 'forever'（使用小写函数转化后为引号的内容）
select employee_i
d,fast_name from guyuan where Upper(fast_name) = 'FOREVER';(使用大写函数转化后为引号的内容)
select employee_id,concat(fast_name,job_id),lpad(salary,10,'*'),le