Oracle 数据库 Tips

查询数据库表创建时间

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