Oracle SQL语句学习汇总

select Upper('abcd') from dual; 
--小写转大写
select Lower('ABCD') from dual; 
--大写转小写
select initcap(LOC) from  DEPT; 
--将第一个字母变成大写
select concat('a','b') from dual;
--连接字符串
select substr('abcdef',-3,3) from dual; 
--截取字符串函数
select length('abcdefg') from dual; 
--计算字符串长度
select replace('abcd','b','B') from dual;
--字符串替换函数
select instr('ABCABCDABCDE','C',4,2) from dual;
--字符串包含函数
select lpad('mzl',10,'@#$') from dual; 
--左填充函数
select rpad('mzl',10,'@#$') from dual; 
--右填充函数
select trim('   txt  ') from dual; 
--过滤首尾空格

select round(4.12,1) from dual; 
--返回按指定位数进行四舍五入的数值
select mod(-20,3) from dual; 
--求余函数 返回值与被除数的符号一致
select trunc(412.13,-2) from dual;
--不对指定小数前或后的部分做相应舍入选择处理,而统统截去

select months_between(sysdate,hiredate) from emp4; 
--计算雇佣日期到当前时间的相差月份
select add_months(sysdate,1) from dual; 
--增加月份函数
select last_day(sysdate) from dual;
--返回某个时间值的当月最后一天
select next_day(sysdate,'星期二')from dual;
--返回符合条件的下一天时间

select to_char(sysdate,'yyyy-mm-dd')from dual;
--把系统时间转换为字符串类型
select to_char(sysdate,'D') from dual;
--返回当前时间是星期几
select to_number('236')+to_number('658') from dual;
--字符类型转化为数字类型
select to_date('2011-4-13','YYYY-MM-DD') from dual;
--字符类型转化为日期类型

select nvl(comm,0) from emp4;
--如果第一个参数为空，则返回第二个参数。第二个参数表示是第一个参数的替换对象。
select nullif(6+9,8+10)from dual;
--如果表达式1和表达式2的值相等返回值为null 否则返回表达式1的值
select empno, ename, sal, comm, nvl2(comm, sal+comm, sal) total from emp4; 
--如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；
--如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。
select empno, ename, sal, comm, coalesce(sal+comm, sal, 0) from emp4;
--依次考察各参数表达式，遇到非null值即停止并返回该值

select empno,ename,job,  
--case表达式
case  job
when 'CLERK' then '办事员'
when 'SALESMAN' then '售货员'
when 'ANALYST' then '统计员'
else '打字员'
end 职位
from emp4;


select empno 工号,ename 姓名,sal 薪水,  
--decode表达式
decode(job,'CLERK','办事员',
'SALESMAN','销售员',
'ANALYST','统计员',
'未知'
)
职位
from emp4;

select lpad(initcap(trim(' dad  ')),10,'@#$') from dual; 
--单行函数嵌套
select count(*)  from emp4;
--统计函数
select max(sal) from emp4;
--最大值函数
select min(sal) from emp4;
--最小值函数
select avg(sal) from emp4;
--平均值函数

select deptno 部门编号, avg(nvl(mgr,0))平均值 from emp4 where mgr>7500 group by deptno; 
--group by 分组函数
--出现在SELECT列表中的字段或者出现在order by 后面的字段，如果不是包含在分组函数中，那么该字段必须同时在GROUP BY子句中出现。
--包含在 GROUP BY子句中的字段则不必须出现在SELECT列表中。
--可使用where字句限定查询条件 不允许在 WHERE 子句中使用分组函数。
--可使用Order by子句指定排序方式
select deptno 部门编号, avg(nvl(mgr,0))平均值 from emp4 where mgr>7500 group by deptno having avg(mgr)>7730; 
--having函数 从group by 函数中获取符合条件的值
select deptno 部门编号, avg(nvl(mgr,0))平均值 from emp4 where mgr>7500 group by deptno order by deptno desc;
--order by 函数 默认升序排列（asc）  降序排列可以在其后加上desc;

select * from emp4,dept4;
--笛卡尔积

select a.ename 用户名,a.job 工作,b.dname 部门名称  
from emp4 a,dept4 b
where a.deptno=b.deptno;
--等值连接查询

select a.ename 姓名,a.job 工作,a.sal 薪水
from emp4 a,dept4 b
where sal between 3000 and 5000;
--非等值连接

select a.ename,a.job,b.sal,b.hiredate
from emp4 a,emp4 b
where a.mgr=b.deptno;
--自连接

select a.deptno,a.ename,a.job
from emp4 a ,dept4 b
where a.deptno=b.deptno(+);
--左外连接

select a.deptno,a.ename,a.job
from emp4 a,dept4 b
where a.deptno(+)=b.deptno;
--右外连接


select * from emp4 
union
select * from emp6;
--并集，所有的内容都查询，重复的显示一次

select * from emp4
union all
select * from emp6;
--并集，所有的内容都显示，包括重复的

select * from emp4
intersect
select * from emp6;
--交集：只显示重复的

select * from emp4
minus
select * from emp6;
--差集：只显示对方没有的（跟顺序是有关系的）

select sal from emp4
where
sal > 
(
select sal
from emp4
where ename = 'JAMES'
);
--单行子查询

select * from emp4
where job in (select job from emp4 where ename = 'MARTIN' or ename = 'SMITH');
select * from emp4 where sal > all(select avg(sal) from emp4 group by deptno);
select * from emp4 where sal > any(select avg(sal) from emp4 group by deptno);
--多行子查询

select *
from emp4
where rownum <= 5
order by sal desc; 
--查询前n行数据并进行排序

select sal
from emp4
where sal > 1000 and exists
(
select sal
from emp4
group by deptno
);
--EXISTS：指定一个子查询，检测行的存在。 
--如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

select sal
from emp4
where sal not in
(
select sal
from emp4
where job <> 'CLERK'
);
--如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,

Select * from (select * from emp4 order by dbms_random.value()) where rownum<=6;
--随机返回5条记录

select * from emp4 order by comm desc nulls first;
--处理空值排序

select * from (select row_number() over (order by ename) rn,ename from emp4) x where mod(rn,2)=1;
--查询跳过表中的偶数行


select ename,sal,max(sal) over(),min(sal) over() from emp4;
--查询员工信息和其中工资最高最低的员工

select ename,sal,sum(sal) over(), sum(sal) over(order by ename) from emp4;
--连续求和

select deptno,sal ,sum(sal) over (partition by deptno order by ename) as s from emp4;
--分部门连续求和

select ename,sal,lead(sal) over(order by sal) aaa ,lag(sal) over(order by sal) bbb from emp4;
--得到当前行上一行或者下一行的数据

Select to_char(hiredate,'yyyy'),avg(sal) from emp4 group by to_char(hiredate,'yyyy');
--根据子串分组

select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') from dual;
--确定一年中的天数

select * from all_tab_columns where table_name='EMP4';
--查询EMP4表中所有字段（列）

select table_name ,comments from dictionary where table_name like '%TABLE%';
--在oracle中描述数据字典视图

alter table emp4  add phone varchar(20); 
--添加字段

alter table emp4 modify phone varchar(18);
--修改字段

alter table emp4 drop column phone;
--删除字段

truncate table emp4;
--清空表数据

drop table emp4;
--删除表emp4

rename emp4 to emp5;
--重命名表emp4为emp5

insert into emp4
(empno,ename,job,mgr,hiredate,sal,comm,deptno)  values
('2','LJL','WORKER','9000',to_date('2011-2-14','yyyy-mm-dd'),'5000','','03');
insert into emp4
values('01','MZL','ENGIEER','8000',to_date('2011-2-18', 'yyyy-mm-dd'),'6000','0','02');
--插入数据

delete from emp;
--清空表数据

create table test1(eid number(10), name varchar2(20),birth date,salary number(8,2));
insert into test1 values (1001, '张三', '20-5月-70', 2300);
insert into test1 values (1002, '李四', '16-4月-73', 6600);
select * from test1;
create table test2(eid number(10), name varchar2(20),birth date,salary number(8,2));
select * from test2;
merge into test2
using test1
on(test1.eid = test2.eid )
when matched then
update set name = test1.name, birth = test1.birth, salary = test1.salary
when not matched then
insert (eid, name, birth, salary) values(test1.eid, test1.name, test1.birth, test1.salary);
select * from test2;
--merge 命令可以用来用一个表中的数据来修改或者插入到另一个表。插入或者修改的操作取决于on子句的条件。
--该语句可以在同一语句中执行两步操作，可以减少执行多条insert 和update语句。
--merge是一个确定性的语句，即不会在同一条merge语句中去对同一条记录多次做修改操作。