Oracle 数据库 Tips

4z24ri5z7f

　　

查询数据库表创建时间
DBA权限下：
select object_name,created from dba_objects where object_type='TABLE' and object_name ='your_tab';
普通用户查本用户的表：
select object_name,created from user_objects where object_type='TABLE' and object_name ='your_tab';
　　
　　数据转换函数
　　to_char转换为字符串，to_char(date, 'yyyy-MM-dd HH24:mm:ss')
　　add_months加月份，add_months(date, 3)。如果需要加天，可直接把日期时间字段加上数字，date+3。
　　to_number 转换为数字，to_number(string)
　　
　　分组排序查询

select *
from (select rank() over(partition by f1 order by f2) rk, a.* from tab_tab a)t
where rk = 1
　　
　　Flash query

select *
from table_name as of timestamp
to_timestamp('2012-02-08 11:00:29', 'YYYY-MM-DD hh24:mi:ss');
 　　行变列，查询2012年每月新增用户：

select count(decode(to_char(t.created_time, 'MM'), '01', t.id)) as 一月,
count(decode(to_char(t.created_time, 'MM'), '02', t.id)) as 二月,
count(decode(to_char(t.created_time, 'MM'), '03', t.id)) as 三月,
count(decode(to_char(t.created_time, 'MM'), '04', t.id)) as 四月,
count(decode(to_char(t.created_time, 'MM'), '05', t.id)) as 五月,
count(decode(to_char(t.created_time, 'MM'), '06', t.id)) as 六月,
count(decode(to_char(t.created_time, 'MM'), '07', t.id)) as 七月,
count(decode(to_char(t.created_time, 'MM'), '08', t.id)) as 八月,
count(decode(to_char(t.created_time, 'MM'), '09', t.id)) as 九月,
count(decode(to_char(t.created_time, 'MM'), '10', t.id)) as 十月,
count(decode(to_char(t.created_time, 'MM'), '11', t.id)) as 十一月,
count(decode(to_char(t.created_time, 'MM'), '12', t.id)) as 十二月
from user t where to_char(t.created_time, 'yyyy')='2012'
 　　获取数据库表结构

select b.COLUMN_ID    as "序号",
b.COLUMN_NAME  as "字段名称",
a.COMMENTS     as "字段描述",
b.DATA_TYPE    as "字段类型",
b.DATA_LENGTH  as "长度",
b.NULLABLE     as "允许空值",
b.DATA_DEFAULT as "缺省值"
from all_col_comments a, all_tab_columns b, all_tab_comments c
where a.table_name = b.TABLE_NAME and b.TABLE_NAME='TABLE_NAME'
and a.column_name = b.COLUMN_NAME
And c.table_name = a.table_name
and c.owner = a.owner
order by a.table_name, b.COLUMN_ID