第一章和第二章distinctlike'S%' S打头的'_A%' 第二个字母是A的绑定变量select ename from &haha;select &&hehe from scott.emp whre &hehe.2000 order by &hehe 多重替换，在一句话中，出现多次，第一个需要用双&&，后面再引用，只要单&definedefine定义的东西只在当前会话有效define a=salselect &a from scott.empverifyset verify off/on 当前会话有效，针对&不确认########################################################alter user scott account unlock identified by tiger;alter user hr account unlock identified by hr;conn hr/hrselect table_name from user_tables;########################################################第三章 单行函数select ename,lower(ename) from scott.emp 转换成小写 upper 大写 initcap 每个单词的首字母大写select ename,sal from emp where upper(ename)=upper('&haha');select concat(ename,sal) from emp; 相当于上午讲的||,连接符select ename,substr(ename,1,2) from emp; 从ename第1位开始取2位select ename,length(ename) from emp; 字串长度select instr('1290000','9') from dual; 从左往右，第一次出现的位置，如果没有，返回0select lpad(sal,10,'*') from emp; 如果sal不满10位，左边用*补齐，rpad是右边补齐select lpad(rpad(sal,10,'*'),20,'*'),先右补齐10位，再左边补齐20位select trim('x' from 'xxxaaaaaxxxaaaxxx') 掐头去尾，通常用来去掉两端空格）select replace('13940000429','0','8') from dual; 把0替换成8select round(123.3456,2) from dual; 小数点后两位四舍五入，结果是123.45，如果是-2，返回100select trunc(123.456789,2)from dual; 截取，返回123.45，如果是-2，代表只留下百位1，即100select mod（100,3） from dual； 100被3除宇1select round(45.923,2) ,round(45.923,0) ,round(45.923,-1) from dual;日期select sysdate from dual; 查看当前时间alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select sysdate+15 from dual; 只能处理-4713到9999年年份在50-99之间，用yy解析，是当前世纪，用rr来解析是上个世纪 0-49之间，用yy，rr，都是当前世纪select sysdate,sysdate+1/24,sysdate+1/1440 from dual; 加一小时，加一分钟，oracle真牛逼啊。。。呵呵select months_between(sysdate,hiredate) from emp; 两个日期之间相差多少个自然月select sysdate,add_months(sysdate,1000) from dual; 1000个自然月后的日期select next_day(sysdate,'sunday') from dual; 下一个礼拜天的日期select lastday（sysdate）from dual； 当前月的最后一天select round(sysdate,'month') from dual;select round(sysdate,'year') from dual;select trunc(sysdate,'year') from dual; 2011.1www.oracle.com/pls/db102后面的公式要罩得住前面的to_charselect to_char(sysdate,'yyyy-mm-dd hh24:mi:ss am day') 2011-09-14 dy select to_char(sysdate,'fmyyyy-mm-dd hh24:mi:ss am day')去掉前导0，显示2011-9-14select to_char(123,'9999999999.99') from dual; 返回 123.00select to_char(123,'0999999999.99') from dual; 返回 000000000000123.00 前导0select to_char(123.'$99999999999999.00') from dual; 返回$123.00select to_char(1234567890,'999,999,999,999,999,999.99') from dual; 返回1,234,567,890.00to_numberselect to_number('123.00','9999999.99')+2 from dual; 返回125to_dateselect to_date('2011-01-01','yyyy-mm-dd'+1 from dual; 返回 2011-01-02 00:00:00通用函数NVLselect ename,comm,nvl(comm,0)+500 from emp 如果有人comm是null，就用0代替NVL2select ename,comm,nvl2(comm,'a','b') from emp 如果comm是null就打印b，不为null，打印aNULLIFNULLIF（expr1，expr2），如果相同，返回空，如果不等，返回参数1 select first_name,length(first_name) "expr1" , last_name,length(last_name) "expr2" , nullif(length(first_name),length(last_name)) result from employees; coalesce（expr1，expr2....)返回第一个不为空的 select last_name,coalesce(manager_id,commission_pct,-1) comm from employees order by commission_pct;if-else格式的SQL语句case select last_name ,job_id ,salary, case job_id when 'IT_PROG' then 1.10*salary when 'ST_CLERK' then 1.15*salary when 'SA_REP' then 1.20*salary else salary end "REVISED_SALARY" from employees; decode select last_name ,job_id ,salary, decode (trunc(salary/2000,0) 0,0.00, 1,0.09, 2,0.20, 3,0.30, 4,0.40, 5,0.50, 6,0.60, 0.45) tax_rate from employees where department_id=80; ?