Oracel常用函数

decode()

decode(column,if_value,value,else if_value,value)
例子：select decode(score,’A’,’优秀’,’B’,’良好’,’C’,’及格’,’不及格’) from t_table
–sum
求和函数
–wm_concat
该函数可以把列值以”,”号分隔起来,并显示成一行

常用sql

-新建用户
create user xx identified by xx;
–给用户权限
grant dba to zhaopei ;
-删除表字段
alter table Tbl_Survey_Human_New DROP COLUMN 字段名;
alter table Tbl_Survey_Human_New DROP COLUMN n_oa_report_FLAG;
alter table Tbl_Survey_Human_New add(字段名 字段类型);
alter table Tbl_Survey_Human_New add(n_protocaryon_report_FLAG varchar2(2));
–修改表字段
alter table Tbl_Survey_Human_New modify(字段名 字段类型);
alter table Tbl_Survey_Human_New modify(c_caseisreal_type varchar2(30));

-创建序列

create sequence mysq
minvalue 1 –最小值
maxvalue 99999999999999999 –最大值
start with 1 –开始值
increment by 1 –每次增长值
cache 20; –缓存大小

case when && case .. when

CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘2’ THEN ‘女’
ELSE ‘其他’ END
–Case搜索函数

CASE
WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘2’ THEN ‘女’
ELSE ‘其他’ END
–查询相同条件的其中一条数据
select * from tb k_id in (select min(k_id) from tb group by d_id)