oracle函数

一。oracle常用函数
nvl,sign,decode,substr,instr,to_char,to_date,trunc
1.nvl函数预设默认值
通过查询获得某个字段的合计值，如果这个值位null将给出一个预设的默认值
select nvl(sum(t.dwxhl),1) from tb_jhde t where zydm=-1这里关心的nvl的用法，nvl（arg,value）代表如果前面的arg的值为null那么返回的值为后面的value
2.数字函数sign()
select sign(-10),sign(0),sign(10) from dual
3.decode字符判断
大于8000的加薪15%，小雨8000的加薪15%
select decode(sign(salary-8000),1,salary*1.2,-1,salary*1.15) salary from emp
4.substr(字符串,开始位置,截取长度) ab
substr('abcd',1,2) ab
5.instr截取的字符串在原字符串的的位置
select instr('abc','a') from dual 返回 1
select instr('morning','o') from dual 返回 2
6. to_char()字符串转换
to_char('2010-10-10','yyyy-mm-dd')
7.to_date()日期转换
to_date('2010-12-10','yyyy=mm=dd hh24:mi:ss')
8.数字函数trunc(n,[m])
select trunc(45.201),trunc(54.101,1),trunc(5421.011,-1) from dual


二。Oracle 基础
用户
create user MR --建立用户
identified by MR --建立密码
default tablespace users --默认表空间
Temporary tablespace temp --默认临时表空间
Quota unlimited on users --对users表空间有无空间使用权限
Quota 10m on system --系统表空间的配置是10M
Password Expire --密码过期选项
重置密码：
Alter user MR identified by Oracle
解除锁定：
Alter user MR account unlock/lock;
删除帐号：
Drop user MR (cascade)
怎么获取有哪些用户在使用数据库
select username from v$session;
如何在Oracle服务器上通过SQLPLUS查看本机IP地址
select sys_context('userenv','ip_address') from dual;
如何给表加注释？
comment on table waterhelpinfo is '水域救助信息';
如何给列加注释？
comment on column waterhelpinfo.savername is '救助人姓名';
如何在ORACLE中取毫秒?
select systimestamp from dual
怎么可以看到数据库有多少个tablespace
select *from dba_tablespaces;
如何查询一个表空间下的所有表(单引号里面的要大写)
select *from user_tables where tablespace_name='SHUIYU_DATA';
select table_name from user_tables where tablespace_name='SHUIYU_DIC';
select table_name from user_tables where tablespace_name='SHUIYU_INDEX';
select table_name from user_tables where tablespace_name='SHUIYU_ROLLBACK';
如何查询表在哪个表空间中？(单引号里面的要大写,本用户)
SELECT tablespace_name FROM USER_TABLES WHERE table_name = 'WATERHELPINFO'
如何查询表在哪个表空间中？(单引号里面的要大写,别的用户)
SELECT tablespace_name FROM DBA_TABLES WHERE table_name = 'YOUR_TABLENAME' and owner='表的OWNER'
select *from waterhelpinfo where rownum < 2
select add_months(sysdate,24) from dual;
SELECT CEIL(-10.102) FROM DUAL;
SELECT FLOOR(-10.102) FROM DUAL;
select user from dual
select avg(nvl(score,0)) from

score

三。oracle手动添加注释

create table FeeRate(
FeeID integer not null primary key,
Days integer,
FeeRate number
)
select *From feerate
comment on column feerate.FeeId is '编码';
comment on column feerate.Days is '典当天数';
comment on column feerate.FeeRate is '月费率%'

四、oracle 11g行转列
drop table StudentScores
create table StudentScores(
UserName varchar(50),
Subject varchar(50),
score number
)
insert into StudentScores values('小明','语文',10);
insert into StudentScores values('小a','数学',100);
insert into StudentScores values('小b','英语',120);
insert into StudentScores values('小c','生物',130)

select *from StudentScores

SELECT username,语文,数学,英语,生物
FROM( SELECT UserName,[Subject],Score FROM StudentScores ) AS a
PIVOT( MAX(score)
FOR [Subject] IN ([语文],[英语],[生物],[数学])
) AS bORDER BY UserName