搜索
1.管理员登录sqlplussys/密码assysdba2.解锁:SQLalteruserscottaccountunlock;3.修改密码SQLalteruserscottidentifiedby新密码;保存文档：spool系统路径如：c:\a.txtSpooloff单行函数SQL--字符函数SQLselectlower('HelloWOrlD')转小写,upper('HelloWOrlD')转大写,initcap('helloworld')首字母大写2fromdual;转小写转大写首字母大写---------------------------------helloworldHELLOWORLDHelloWorldSQL--substr(a,b):从a里面的b位开始取SQLselectsubstr('HelloWorld',4)fromdual;SQL--substr(a,b,c)SQLselectsubstr('HelloWorld',4,4)fromdual;SQL--length字符数lengthb字节数SQLselectlength('HelloWorld')字符数,lengthb('HelloWorld')字节数2fromdual;已写入fileafiedt.buf1selectlength('中国')字符数,lengthb('中国')字节数2*fromdualSQL--instr:在母串中查找子串，找到了返回下标（从1开始）；否则返回0SQLselectinstr('HelloWorld','ll')fromdual;INSTR('HELLOWORLD','LL')------------------------3SQL--lpad左填充rpad右填充SQLselectlpad('abcd',10,'*')左填充,rpad('abcd',10,'*')右填充2fromdual;左填充右填充--------------------******abcdabcd******SQL--trim:去掉前后指定的字符SQLselecttrim('H'from'HelloWorldH')fromdual;TRIM('H'FR----------elloWorldSQL--replace:替换SQLselectreplace('HelloWorld','l','*')fromdual;REPLACE('HE-----------He**oWor*dSQL--数字函数SQLselectround(45.926,2)一,round(45.926,1)二,round(45.926,0)三,round(45.926,-1)四,round(45.926,-2)五2fromdual;一二三四五--------------------------------------------------45.9345.946500SQLed已写入fileafiedt.buf1selectTRUNC(45.926,2)一,TRUNC(45.926,1)二,TRUNC(45.926,0)三,TRUNC(45.926,-1)四,TRUNC(45.926,-2)五2*fromdualSQL/一二三四五--------------------------------------------------45.9245.945400SQL--日期数据：日期+时间SQLselectsysdatefromdual;SYSDATE--------------07-6月-12SQLselectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss:mm')fromdual;TO_CHAR(SYSDATE,'YYYY-----------------------2012-06-0714:26:43:06SQL--在日期上加上或减去一个数字结果仍为日期SQL--昨天今天明天SQLselect(sysdate-1)昨天,sysdate今天,(sysdate+1)明天2fromdual;昨天今天明天------------------------------------------06-6月-1207-6月-1208-6月-12SQL--两个日期相减返回日期之间相差的天数SQL--查询员工的工龄，分别以天星期月年SQLselectename,hiredate,(sysdate-hiredate)天,(sysdate-hiredate)/7星期,(sysdate-hiredate)/30月,(sysdate-hiredate)/365年2fromemp;ENAMEHIREDATE天星期月年--------------------------------------------------------------SMITH17-12月-8011495.60621642.22945383.18687231.4948114ALLEN20-2月-8111430.60621632.94374381.02020631.3167292WARD22-2月-8111428.60621632.65802380.95353931.3112498JONES02-4月-8111389.60621627.0866379.65353931.2044005MARTIN28-9月-8111210.60621601.51517373.68687230.7139895BLAKE01-5月-8111360.60621622.94374378.68687231.1249484CLARK09-6月-8111321.60621617.37231377.38687231.0180991SCOTT13-7月-879096.606171299.51517303.22020624.9222087KING17-11月-8111160.60621594.37231372.02020630.5770032TURNER08-9月-8111230.60621604.37231374.35353930.768784ADAMS13-7月-879096.606171299.51517303.22020624.9222087JAMES03-12月-8111144.60621592.0866371.48687230.5331676FORD03-12月-8111144.60621592.0866371.48687230.5331676MILLER23-1月-8211093.60621584.80088369.78687230.3934416已选择14行。SQL--日期能相加吗？SQLselectsysdate+hiredatefromemp;selectsysdate+hiredatefromemp*第1行出现错误:ORA-00975:不允许日期+日期SQLhostclsSQL--对日期进程四舍五入SQLselectround(sysdate,'month'),round(sysdate,'year')fromdual;SQLselecttrunc(sysdate,'month'),trunc(sysdate,'year')fromdual;SQL--months_between返回两个日期相差的月数SQLselectempno,ename,hirdate,(sysdate-hiredate)/30方式一,months_between(sysdate,hiredate)方式二2fromemp;selectempno,ename,hirdate,(sysdate-hiredate)/30方式一,months_between(sysdate,hiredate)方式二*第1行出现错误:ORA-00904:HIRDATE:标识符无效SQLed已写入fileafiedt.buf1selectempno,ename,hiredate,(sysdate-hiredate)/30方式一,months_between(sysdate,hiredate)方式二2*fromempSQL/EMPNOENAMEHIREDATE方式一方式二----------------------------------------------------7369SMITH17-12月-80383.187136377.6972287499ALLEN20-2月-81381.020469375.6004547521WARD22-2月-81380.953802375.5359387566JONES02-4月-81379.653802374.1810997654MARTIN28-9月-81373.687136368.3423897698BLAKE01-5月-81378.687136373.2133577782CLARK09-6月-81377.387136371.9552937788SCOTT13-7月-87303.220469298.826267839KING17-11月-81372.020469366.6972287844TURNER08-9月-81374.353802368.9875517876ADAMS13-7月-87303.220469298.826267900JAMES03-12月-81371.487136366.1488417902FORD03-12月-81371.487136366.1488417934MILLER23-1月-82369.787136364.50368已选择14行。SQLhostclsSQL--ADD_MONTHS:SQLselectadd_months(sysdate,123)fromdual;ADD_MONTHS(SYS--------------07-9月-22SQL--next_day从某个日期算起，下一个出现星期几的日期SQLselectnext_day(sysdate,'星期四')fromdual;NEXT_DAY(SYSDA--------------14-6月-12SQLselectnext_day(sysdate,'星期五')fromdual;NEXT_DAY(SYSDA--------------08-6月-12SQLselectnext_day(sysdate,'礼拜五')fromdual;selectnext_day(sysdate,'礼拜五')fromdual*第1行出现错误:ORA-01846:周中的日无效SQLselectlast_day(sysdate)fromdual;LAST_DAY(SYSDA--------------30-6月-12SQLhostclsSQL--转换函数SQL--隐式转换的前提：被转换对象是可以转换的SQL--显式转换SQL--2012-06-0714：55:12;09今天是星期四SQLselectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss:mm今天是day')2fromdual;TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS:--------------------------------------2012-06-0714:57:17:06今天是星期四SQL--查询员工信息，姓名和月薪（两位小数，千位符，货币代码）SQLselectename,to_char(sal,'L9,999.99')fromemp;SQL--通用函数SQL--nvl2(a,b,c)当a=null时返回c，否则返回bSQLselectempno,ename,sal,sal*12,comm,sal*12+nvl2(comm,comm,0)fromemp;SQL--nullif(a,b)当a=b时，返回null；否则返回aSQL