1、case表达式
select country_name,region_id, case region_id when 1 then 'europe' when 2 then 'america' when 3
then 'asia' else 'other' end continent from countries where country_name like 'I%';
在9i中提供的case表达式，可以在sql语句中达到if then else逻辑。
2、order by
order by子句中可以使用列名，列别名，或者列的位置。位置必须是select-list表达式的数目
select name，sex from v_table order by 2
3、oracle单行函数
通用函数
nvl(expr1,expr2) 判断第一个字符是否null,是显示expr2
nvl2(expr1,expr2,expr3) 判断第一字符是否null，是显示expr3,不是显示expr2
nullif(expr1,expr2)如果两个字符相等，则返回空，否则返回expr1
coalesce(expr1,expr2,...exprn)判断每个参数是否未空，直到不为空显示出来，如果都是空则报错
日期函数
to_number,to_date,
to_char(date,'format-model')
format-model:
{
yyyy full year in numbers
year year spelled out
mm two-digit value for month
month full name of the month
mon three-letter abbreviation of the month
dy three-letter abbreviation of the day of the week
day full name of the day of the week
dd numeric day of month
}
to_char(number,'format-model')
{
9 prepresents a number
0 forces a zero to be displayed
$ places a floating dollar sign
L 使用本地货币符号
. prints a decimal point
, prints a thousand indicator
}
months_between
add_months
next_day
last_day
round
trunc
字符函数
lower，upper，initcap(大小写转化函数)
initcap('SQL Cource') result is Sql Cource
concat，substr,length,instr,lpad|rpad,trim,replace
concat字符连接，等于||
substr截断字符串substr(column,1,5)
substr(column,-1,5)从右边开始取
length字符长度
instr字符所在的位置
trim压缩空格
lpad左填充
rpad右填充
数字函数
round四舍五入round(45.926,2)---45.93
trunc截取数字trunc(45.926,2)---45.92
mod求余数mod(1500,200)--100
转换函数
4、sql*plus
set wrap off
set linesize 1000
5、case when else end
6、decode简化的if then else
7、oracle中对层次结构数据的递归查询
create table Dept(
DepartNO varchar2(10),
DepartName varchar2(20),
TopNo varchar2(10));
insert into Dept values('001','董事会','0');
insert into Dept values('002','总裁办','001');
insert into Dept values('003','财务部','001');
insert into Dept values('004','市场部','002');
insert into Dept values('005','公关部','002');
insert into Dept values('006','销售部','002');
insert into Dept values('007','分销处','006');
insert into Dept values('008','业务拓展处','004');
insert into Dept values('009','销售科','007');
connect by prior start with
例子：
select departname,level,sys_connect_by_path(departname,'|') from dept
connect by prior departno = topno
start with departname='董事会'
departname level
市场部 2
公关部 2
销售部 2
或
select departname,level,sys_connect_by_path(departname,'|') from dept
where level=2
connect by departno =prior topno
start with departname='总裁办'
Sys_connect_by_path(x,c)x是列，从是单字符，适用层次化查询。
prior位置表示是往父亲节点搜索还是往子节点搜索
level表示搜索深度
8，insert with check option
9、插入的值显示的定义为default值
insert into departments(department_id,name)
values(300,default)
name值由表定义时的default值填充，强制用当前column定义的default值
10、merge语句，要不更新，要不插入
merge into table_name ....
merge into ttt ...
when metched then update set
...
when not matched then
insert values....
11、隐式事务
没有提交的数据，别人不能修改
通过回滚段实现读一致性
语句级
12、rollup、cube等分析函数
select decode(cust_gender,'F','男','M','女') cust_gender,count(cust_gender)
from sh.customers
group by cust_gender
union select '合计'cust_gender,count(cust_gender) from sh.customers
order by cust_gender desc
普通的写法
select cust_gender gender,nvl(cust_marital_status,'unknow') marital_status,count(*),grouping(cust_gender),grouping(nvl(cust_marital_status,'unknow'))
from sh.customers
group by rollup(cust_gender,nvl(cust_marital_status,'unknow'))
rullup写法
13、rank、dense_rank函数
select department_id,rank(10000) within group(order by salary desc nulls last) dense_rank_10k
from hr.employees
group by department_id
判断10000在salary中排在第几，按dept分组
select department_id,salary,rank() over(partition by department_id order by salary desc) dense_rank_10k
from hr.employess列出在部门排第几
dence_rank在并列关系是，相关等级不会跳过。rank则跳过。
14、select length('HELLO WORLD!')-length(replace(translate('HELLO WORLD!','L',chr(13)),chr(13),'')) from dual;
判断字符串中的某个字符的个数
select length('HELLO WORLD!')-length(replace('HELLO WORLD!','L')) from dual;
更简洁
select (length('你好你好LLO WOR你好LD!')-length(replace('你好你好LLO WOR你好LD!','你好')))/length('你好') from dual;
可以同时处理汉字和英文
15 alter table add / modify cName
drop column cName
涉及到数据删除时所需要的时间，可以使用set unused使column无用，在系统空闲的时候可以drop column
delete删除时做很多日志记录，效率低下
truncate是ddl，不能rollback！速度效率都高，同时也清空索引和存储空间还原
comment on table tName is 'comment content';给对象增加注释
查看注释信息：all_col_comments,user_col_comments,all_tab_comments,user_tab_comments
16、约束 including constraints 数据完整性 列完整性，实体完整性
约束类型：(列一级的 not null,check),unique,primary key,foreign key
create table test(
id int not null,
lname varchar(20),
rname varchar(20),
rid int not null,
constraint test_u_1 unique(lname,rname)
)
alter table test add constraint pk_test primary key(id);增加主键
alter table test add constraint fk_test foreign key(rid) references test1(id);
级联删除 on delete cascade,或者on delete set null
check 不能用系统函数、sequence、查询
alter table drop constraint c_name
alter table disable/enable constraint c_name
alter table drop column col_name cascade constraint删除列的时候连constraint一块删除
查看constaint select * from user_constraints/user_cons_columns
17、视图view
view是一个逻辑结构，不含有数据
create force view viewname as ...
force 强制视图编译通过，不管后面的select语句是否成功
with check option constraint ...
with read only constraint ...
透过视图可以发生数据改变，改变基表！带有group by，函数，rownum，distinct等不可改变
18、greatest、least
greatest(p1,p2,p3...)找出其中最大的
least(p1,p2,p3...)找出最小的
coalesce计算多个值的非空值
coalesce(null,null,'a')返回a
coalesce(overtime,faultreorttime)如果同一行中overtime为null，faultreporttime不为null返回faultreporttime
greatest、least函数不将日期格式的直接量串作为日期处理，还是当作串处理
extract代替to_char来选择日期值的成分（如从一个日期中选择月份和天）
extract(Month from sysdate)
BUN_TO_NUM(1)二进制转换成十进制
translate(string,if,then)在一个字符串中进行逐字符的替换
translate(7671234,234567890,'BCDEFGHIJ') 结果 GFG1BCD
19、not in、not exists和外连接
一般情况下用外部连接代替not in，可以极大的提高性能。not exists也可以替代not in，只是性能提升不够。
select vipid,vipName from t_vip where vipid not in (select v.vipid from t_vip v,t_vip_detail d where v.vipid=d.vipid)
select v.vipid,v.vipName from t_vip v where not exists (select 'x' from t_vip_detail d where v.vipid=d.vipid)
select t_vip_detail.vipid,t_vip.vipName from t_vip left join t_vip_detail on t_vip.vipid=t_vip_detail.vipid where t_vip_detail.vipid is null
以上三个效果一样
20、union(并)、intersect(交)和minus(差)
union相同的只显示一次，对这中重复现象可以用union all
intersect包含在两个查询基表都出现的
in子句的优选级比union高
21、建立临时表
create global temporary table tableName()
on commit preserve rows子句指定是否在整个会话期间都存在
on commit delete rows子句指定是否在事务处理完成时删除它的行
22、索引编排表(Index-Organized Table)
根据表的主键列对数据进行排序，索引编排表存储数据时就像把整个表存储在一个索引中那样。
create table命令的organization index子句来建立一个索引编排表
23、分区表
a、改善表的查询性能
b、使表更容易管理
c、备份和恢复操作会执行得更好
对表分区可以使用create table子句的partition by range
eg:partition by range(CategoryName)(
partition part1 values less than ('B')
tablespace part1_ts,
partition part2 values less than (MAXVALUE)
tablespace part2_ts
);
by range 是范围分区
by hash 是散列分区
by list 是列表分区
24、索引
构造较少的索引但每个索引中列数较多比构造较多索引但每个所以列数较少更好。
索引类型：一般索引（Not Sorted 传统索引（B树索引）对于包含有大量编号数据的列是非常有用的）、
位图索引(bitmap)、唯一索引(unique)、函数索引(function)
表空间：增加表空间的空间，可以扩展相应的数据文件或者增加新
