ORACLE常用的函数总结:

ORACLE常用函数

001).C:\Users\lenovo>sqlplus system/3713@orcl--连接到:xxx002).conn sys/3713 as sysdba;--已连接。003).create user c##scott_1 identified by tiger_1;--用户已创建。004).drop user c##scott_1 cascade;--用户已删除(cascade:级联删除.)。005).grant connect,resource to c##scott;--授权成功。006).conn c##scott/tiger;--已连接。007).show con_name;--CDB$ROOT008).show user;--USER 为 "C##SCOTT"(或:select user from dual;)009).alter session set container=PDBORCL;--会话已更改。010).shutdown immediate;--数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。011).startup--ORACLE 例程已经启动。数据库装载完毕。数据库已经打开。012).alter pluggable database PDBORCL  open;--插接式数据库已变更。013).alter pluggable database PDBORCL  close;--插接式数据库已变更。014).alter session set container=CDB$ROOT;--会话已更改。015).select username from dba_users;--(查看所有用户的用户名.)016).C:\Users\lenovo>sqlplus--请输入用户名:017).select name from v$database;--ORCL(查看当前的SID数据库名称)018).quit--从 Oracle Database断开019).C:\Users\lenovo>set oracle_sid=mikesdb;--(切换SID)020).select * from user_tables;--(查看当前用户下的所有表);021).alter user scott account unlock;--用户已更改。022).select username,account_status from dba_users where username='SCOTT';--(case sensitive)023).select table_name from user_tables;--(查询当前用户拥有的所有表名)024).desc dept;--025).insert into dept(deptno,dname,loc) values(50,'Development','Beijing');--026).commit;--提交完成。027).update dept set loc='SHANGHAI' where deptno=50;--已更新 1 行。028).delete from dept where deptno=50;--已删除 1 行。029).alter session set nls_date_format='yyyy-MM-dd';--会话已更改。(National Language Support.)030).alter session set nls_date_format='yyyy-MM-dd HH:mi:ss';--会话已更改。(12:00:00是默认.)031).alter session set nls_date_format='yyyy-MM-dd HH:mm:ss';--(ERROR:ORA-01810: 格式代码出现两次.)032).select sysdate from dual;--(当前数据库时间.)033).select ename,(sysdate-hiredate) from emp;--(当前系统时间与hiredate之间的天数,结果精确到小数位,e.g.:13168.705天.)034).select ename,round((sysdate-hiredate)) from emp;--(同上,结果精确到个位,e.g.: 13169天.)035).select ename,(sysdate-hiredate)/365 from emp;--(年数,e.g: 36.0786516年.)036).select ename,round((sysdate-hiredate)/365) from emp;--(年数取整,e.g.:36年.)037).select ename,round((sysdate-hiredate)/365,0) from emp;--(同上,0代表精确到个位,1代表精确到1位小数.etc...)038).select ename,hiredate from emp order by hiredate asc;--(默认为升序,asc可以省略.)039).select ename,hiredate from emp order by hiredate desc;--(降序排列.desc不能不能不能省略.)040).select ename,hiredate from emp order by 2;--(按照第2列升序排列<默认升序>.)041).select ename,hiredate from emp order by 2 desc;--(按照第2列降序排列.)042).select ename as "姓名",hiredate as "工作年限" from emp order by "工作年限" desc;--(不能用单引号,as可以选填.)043).select ename as "姓名",hiredate as "工作年限" from emp order by hiredate desc;--(同:order by "工作年限".)044).select ename,sal+1500 from emp order by sal;--045).select ename||'的本月工资(￥):'||(sal+1500) as "公司员工本月工资" from emp order by sal;--(前一个是单引号,后面的是双引号.e.g.:SMITH的本月工资(￥):2300.)046).select distinct deptno from emp;--(去重.)047).select ename,sal from emp where sal between 1500 and 2500 order by sal asc;--(区间查询.)048).select ename,empno,job,sal,comm from emp where comm is null or sal<=1500;049).select ename,sal,comm,sal+nvl(comm,0) total from emp where comm is null or sal<=1500 order by total asc;--(如果comm为空的话,就当做0.)050).select ename from emp where ename like 'M%';--(模糊查询.)051).select ename from emp where ename like 'M_____';--(完全匹配,下划线"_"代表占一个位.e.g:MILLER)052).select ename,job from emp where job in ('SALESMAN','ANALYST','MANAGER');--053).select job,count(*) from emp where job in ('MANAGER','ANALYST') group by job;--(分组查询.)054).select job,count(*) from emp where job in ('MANAGER','ANALYST','SALESMAN') group by job order by 2 asc;--055).select sum(sal)+sum(nvl(comm,0)) from emp;--(查询公司本月总共需要付的所有薪酬.)056).select sum(sal+nvl(comm,0)) from emp;--(同上.)057).select round(avg(sal+nvl(comm,0)),0) average from emp;--(查询公司所有员工工资加奖金的平均值<精确到个位>.)058).select max(sal+nvl(comm,0))-min(sal+nvl(comm,0)) "difference" from emp;--(最高薪酬与最低薪酬之间的差值.)059).select job,avg(sal) aveg from emp having avg(sal)>2500 group by job;--(查询平均工资高于2500的岗位,having字句中不能使用aveg别名.)060).select job,round(avg(sal+nvl(comm,0)),1) aveg from emp having avg(sal)>2500 group by job order by aveg;--(同上.)061).select * from dba_role_privs where grantee='SCOTT';--(查看scott用户具有的角色.)062).select to_char(sysdate,'yyyy-MM-dd hh:mi:ss') "系统时间" from dual;--(Oracle系统时间.)063).select sys_context('userenv','terminal') "主机名" from dual;--(获取当前机器的主机名.e.g.:HZ-WLYHKAIFA11)064).select sys_context('userenv','language') "语言与编码" from dual;--(获取LOCALE.e.g.:SIMPLIFIED CHINESE_CHINA.ZHS16GBK)065).select sys_context('USERENV','AUTHENTICATION_TYPE') from dual;--(用户的认证类型.e.g.OS,DATABASE...)066).SQL> select sys_context('USERENV','HOST') from dual;--(e.g.:WORKGROUP\HZ-WLYHKAIFA11.)067).SQL> select sys_context('USERENV','DB_NAME') from dual;--(数据库实例名.e.g.:mikesdb.)068).SQL> select sys_context('USERENV','ISDBA') from dual;--(当前用户是否是以dba身份登录.e.g.:TRUE,FALSE.)069).SQL> select sys_context('USERENV','LANG') from dual;--(对‘LANGUAGE’的简称,e.g.:ZHS.)070).SQL> select sys_context('USERENV','NLS_CURRENCY') from dual;--(本地化的货币符,e.g.:$,￥.)071).SQL> select sys_context('USERENV','NLS_DATE_FORMAT') from dual;--(当前使用的日期格式,e.g.:DD-MON-RR.)072).SQL> select sys_context('USERENV','NLS_DATE_LANGUAGE') from dual;--(表示日期的语言,e.g.:SIMPLIFIED CHINESE.)073).SQL> select sys_context('USERENV','NLS_TERRITORY') from dual;--(数据库服务器所在区域,e.g.:CHINA.)074).SQL> select sys_context('USERENV','OS_USER') from dual;--(操作系统的用户名,e.g.:HZ-WLYHKAIFA11\lenovo.)075).SQL> select sys_context('USERENV','HOST') from dual;--(当前会话主机操作系统名,e.g.:WORKGROUP\HZ-WLYHKAIFA11.)076).SQL> select floor(10.1) from dual;--(e.g.:10.)077).SQL> select round(123.678, 1) from dual;--(e.g.:123.7.)078).SQL> select sign(5) from dual;--(若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1,e.g.:1.)079).SQL> SQL> select initcap('HHMMSS,sun,guo,qing') from dual;--(把每个字符串的第一个字符大写,其余换成小写,e.g.:Hhmmss,Sun,Guo,Qing.)080).SQL> select lower('HHMMSS,Mike,Sun') from dual;--(将各字符串的每个字符都小写,e.g.:)081).SQL> select replace('xieee_jineee', 'eee', 'QQ') from dual;--(字符串中所有str1换成str2,e.g.:xiQQ_jinQQ.)082).SQL> select substr('abcdefghijklmnopqrstuvwxyz', 3, 100) from dual;--(取出从m字符开始的n个字符的子串,e.g.:cdefghijklmnopqrstuvwxyz.)083).SQL> select length('abcdefghijklmnopqrstuvwxyz') from dual;--(求字符串的长度,e.g:26.)084).SQL> select 'Mike'||'Sun' from dual;--(并置运算符,e.g.:MikeSun.)085).SQL> select concat('Guo', 'qing') from dual;--(同上,e.g.:Guoqing.)086).SQL> SELECT INSTR('aabbaabbaabb','aa') FROM DUAL;--(查找位置,e.g.:1.)087).SQL> SELECT INSTR('aabbaabbaabb','aa',1,2) FROM DUAL;--(同上,从第m个开始,查找第n次匹配的位置,e.g.:5.)088).SQL> select to_char(sysdate,'yyyy-MM-dd HH:mi:ss') from dual;--(e.g.:2017-01-06 01:38:36.)089).SQL> select to_char(last_day(sysdate),'yyyy-MM-dd') from dual;--(本月最后一天,e.g.:2017-01-31.)090).SQL> select to_char(add_months(sysdate, 2),'yyyy-MM-dd') from dual;--(当前日期后推n个月,e.g.:2017-03-06.)091).SQL> select to_char(sysdate,'yyyy-MM-dd hh12:mi:ss') from dual;--(12小时制<默认>,同hh.)092).SQL> select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from dual;--(24小时制.)093).SQL> select to_number('2147483647') from dual;--(将合法的数字字符串转换数值型,e.g.: 2147483647.)094).SQL> select to_number('21474836472147483647') from dual;--(同上,egpt:2.1475E+19.)095).SQL> select ceil(10.6) from dual;--(egpt:11.)096).SQL> select ceil(10.6) from dual;--(egpt:11.)097).SQL> select mod(7,5) from dual;--(egpt:2.)098).SQL> select power(3,2) from dual;--(egpt:9.)099).SQL> select round(1234.5678,2) from dual;--(egpt:1234.57.)100).SQL> select sqrt(25) from dual;--(egpt:5.)101).SQL> select months_between(sysdate,to_date('20020812','YYYYMMDD')) from dual;--(日期sysdate和日期n相差月数,egpt: 172.886816.)102).SQL> select next_day(sysdate,'Monday') from dual;--(下一个'Monday'的日期,egpt:2017-01-09.)103).SQL> select to_char(next_day(to_date('20170108','YYYYMMdd'),'Monday'),'yyyy-MM-dd') from dual;--(同上,egpt:2017-01-09.)104).SQL> select to_char(sysdate,'Q') from dual;--(当前日期是第几季度(quarter),egpt:1.)105).SQL> select to_char(to_date('20171202','yyyy-mm-dd'),'RM') from dual;--(指定日期月份的罗马表示,egpt:XII.)106).SQL> select to_char(sysdate,'year') from dual;--(指定日期年份的英文表示,egpt:twenty seventeen.)107).SQL> select to_char(sysdate,'month') from dual;--(指定日期月份的英文表示,egpt:january.)108).SQL> select to_char(sysdate,'day') from dual;--(指定日期星期的英文表示,egpt:sunday.)109).SQL> select to_char(to_date('20161231','yyyymmdd'),'ww') from dual;--(指定日期是该年度第多少周,egpt:.)110).SQL> select to_char(sysdate,'w') from dual;--(指定日期在当月是第几周,egpt:2.)111).SQL> select to_char(to_date('20161231','yyyymmdd'),'DDD') from dual;--(指定日期在该年度是第多少天(三位,如001),egpt:366.)112).SQL> select to_char(sysdate,'DD') from dual;--(指定日期当月第几天.egpt:8.)113).SQL> select to_char(to_date('20170108','yyyymmdd'),'D') from dual;--(指定日期周内第几天(Sunday第一天),egpt:1.)114).SQL> select to_char(to_date('20170108','yyyymmdd'),'DY') from dual;--(指定日期是星期几的英文缩写,egpt:SUN.)115).SQL> select to_char(to_date('20170108135656','yyyymmddhh24miss'),'hh12') from dual;--(指定时间,在给定的小时制下是几点钟,egpt:01.)116).SQL> select to_char(to_date('20170108135656','yyyymmddhh24miss'),'hh24') from dual;--(egpt:13.)117).SQL> select to_char(to_date('20170108135634','yyyymmddhh24miss'),'Mi') from dual;--(分钟数,egpt:56.)118).SQL> select to_char(to_date('20170108135624','yyyymmddhh24miss'),'ss') from dual;--(秒数,egpt:24.)119).SQL> select to_char(sysdate,'YYYY-MM-DD HH24:mi:ss') from dual;--(获取时间,egpt:2017-01-08 12:44:19.)120).SQL> set heading off;--(不显示列名<不输出域标题><重新显示: set heading on>,egpt:.)121).SQL> set line 100;--(设置行的最大尺寸<同set linesize 100>,每行不超过100个字符,如果超过,则换行显示<默认值是80>,egpt:.)122).SQL> show line;--(显示每行所最多容纳的字符数<同show linesize>,egpt:linesize 80.)123).SQL> set pagesize 0;--(不需要分页,egpt:.)124).SQL> show pagesize;--(egpt:pagesize 0.)125).SQL> select concat( concat(ename, ' is a '), job) from emp;--(egpt:SMITH is a CLERK.)126).SQL> select lpad(ename,15,'*') from emp;--(左填充,egpt:**********SMITH.)127).SQL> select ltrim('abcdab','a') from dual;--(左去除,egpt:bcdab.)128).SQL> select replace('jack and jue','j','bl') from dual;--(替换,egpt:black and blue.)129).SQL> select rpad(ename,15,'*') from emp;--(右填充,egpt:SMITH**********.)130).SQL> select rtrim('abcdef', 'f') from dual;--(右去除,egpt:abcde.)131).SQL> select ename from emp where soundex(ename) = soundex('smythe');--(比较英语中拼写不同而发音类似的字,egpt:SMITH.)132).SQL> select substr('abcdefgijklm',3,4) from dual;--(截取,egpt:cdef.)133).SQL> select translate(ename,'AEIOU', 'XXXXX') from emp;--(相同位置字符替换,egpt:SMXTH.XLLXN...)134).SQL> select upper('aptech computer education') from dual;--(全部转换为大写,egpt:APTECH COMPUTER EDUCATION.)135).SQL> select ascii('aptech') from dual;--(返回第一个字符的ASCII<American Standard Code for Information Interchange>值egpt:97.)136).SQL> select instr('aptech is aptech','ap',1,2) from dual;--(查找字符集合的位置,egpt:11.)137).SQL> select ename, length(ename) from emp;--(egpt:.)138).SQL> select * from emp intersect select * from emp where deptno=10 ;--(集合运算符intersect,egpt:    .)139).SQL> select to_char( to_date(123456,'J'),'Jsp') from dual;--(egpt:One Hundred Twenty-Three Thousand Four Hundred Fifty-Six.)140).SQL> select to_char(to_date('2017-01-09','yyyy-mm-dd'),'day') from dual;--(求某天是星期几,egpt:monday.)141).SQL> alter session set nls_date_language='american';--(修改nls_date_language,egpt:Session altered..)142).SQL> select floor(to_date('20170108','yyyymmdd') - to_date('20020405','yyyymmdd')) from dual;--(两个日期间的天数,egpt:5392.)143).SQL> select  months_between(date'2014-04-23',date'2013-04-23') from dual;--(月份差egpt:12.)144).SQL> select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;--(egpt:monday.)145).SQL> select * from nls_session_parameters;--(同select * from V$NLS_PARAMETERS,egpt:NLS_LANGUAGE AMERICAN.)146).SQL> select trim(leading 9 from 9998767999) from dual;--(去掉前导,egpt:8767999.)147).SQL> select trim(trailing 9 from 9998767999) from dual;--(去掉后缀,egpt:9998767.)148).SQL> select trim(9 from 999879967999) from dual;--(去掉前导和后缀,egpt:879967.)149).SQL> select chr(97) from dual;--(返回ascii值对应的字母,egpt:a.)150).SQL> select deptno,decode(deptno,10,'1部门',20,'2部门',30,'3部门','其他') from dept;--(decode<条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值>,egpt:.)151).SQL> select round(66.667,2) N1,trunc(66.667,2) N2 from dual;--(round:四舍五入,trunc:直接截断,egpt:66.67  66.66.)152).SQL> select sign(-32),sign(293),sign(0) from dual;--(返回值的符号<正数=1,负数=1,0=0>egpt:-1 1 0.)153).SQL> select sal,to_char(sal,'$99999'),to_char(sal,'$99,999') from emp;--(egpt:1300   $1300   $1,300.)154).SQL> select to_number(to_char(to_date('2017010818','yyyymmddhh24'),'hh24')) from dual;--(以数字显示的小时数,egpt:18.)155).SQL> select user from dual;--(返回登录的用户名称,egpt:SCOTT.)156).SQL> select vsize('HELLO') from dual;--(返回表达式所需的字节数,egpt:5.)157).SQL> select comm,nvl(comm,0) from emp;--(如果雇员没有佣金,将显示0,否则显示佣金egpt:.)158).SQL> select nullif(sal,comm),sal,comm from emp;--(值相等返空,否则返回第一个值,egpt:.)159).SQL> select comm,sal,coalesce(comm,sal,sal*10) from emp;--(返回列表中第一个非空表达式,egpt:.)160).SQL> select nvl2(comm,ename,''),comm from emp;--(查看有佣金的雇员姓名以及他们的佣金<nvl2(ex1,ex2,ex3)<如果ex1不为空显示ex2,否则显示ex3>>,egpt:.)

20170109