Oracle 10g SQL Fundamentals I(学习笔记第1-3章)

第一章和第二章
distinct

like
'S%'  S打头的
'_A%'  第二个字母是A的

绑定变量
select ename from &haha;
select &&hehe from scott.emp whre &hehe.2000 order by &hehe 多重替换，在一句话中，出现多次，第一个需要用双&&，后面再引用，只要单&

define
define定义的东西只在当前会话有效
define a=sal
select &a from scott.emp

verify
set verify off/on 当前会话有效，针对&不确认

########################################################
alter user scott account unlock identified by tiger;
alter user hr account unlock identified by hr;
conn hr/hr
select 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;  从左往右，第一次出现的位置，如果没有，返回0
select 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替换成8
select round(123.3456,2) from dual;  小数点后两位四舍五入，结果是123.45，如果是-2，返回100
select trunc(123.456789,2)from dual;   截取，返回123.45，如果是-2，代表只留下百位1，即100
select mod（100,3） from dual；       100被3除宇1


select 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.1
www.oracle.com/pls/db102

后面的公式要罩得住前面的
to_char
select 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-14
select to_char(123,'9999999999.99') from dual; 返回 123.00
select to_char(123,'0999999999.99') from dual; 返回 000000000000123.00 前导0
select to_char(123.'$99999999999999.00') from dual; 返回$123.00
select to_char(1234567890,'999,999,999,999,999,999.99') from dual; 返回1,234,567,890.00

to_number
select to_number('123.00','9999999.99')+2 from dual; 返回125
to_date
select to_date('2011-01-01','yyyy-mm-dd'+1 from dual; 返回 2011-01-02 00:00:00

通用函数
NVL
select ename,comm,nvl(comm,0)+500 from emp  如果有人comm是null，就用0代替
NVL2
select ename,comm,nvl2(comm,'a','b') from emp 如果comm是null就打印b，不为null，打印a
NULLIF
NULLIF（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;