ORACLE常用的函数总结:

ORACLE常用函数

001).C:\Users\lenovo>sqlplus system/3713@orcl--连接到:xxx

002).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$ROOT

008).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