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语句中去对同一条记录多次做修改操作。