ORACLE-DB2-SQLSERVER-MYSQL 数据库 时间转换 查询表结构

ORACLE：

--时间转字符串

TO_CHAR(SYSDATE,‘YYYYMMDDHH24MISS‘)

--字符串转时间

select  TO_DATE(‘20140701125751‘,‘YYYYMMDDHH24MISS‘) from dual  -- 2014-07-01 12:57:51

--查询table

SELECT * FROM ALL_TABLES WHERE TABLE_NAME =‘{0}‘

--查询column

SELECT

    aa.table_name                     AS tableename,

    aa.column_name                    AS colname,

    NVL (bb.comments, aa.column_name) AS coldesc

FROM

    (

        SELECT

            table_name,

            column_name,

            comments

        FROM

            all_col_comments

        WHERE

            table_name = ‘{0}‘

        AND owner = ‘{1}‘) bb,

    (

        SELECT

            table_name,

            column_name,

            column_id

        FROM

            all_tab_columns

        WHERE

            table_name = ‘{0}‘

        AND owner = ‘{1}‘) aa

WHERE

    aa.column_name = bb.column_name

ORDER BY

    aa.column_id ASC

DB2:

--时间转字符串

TO_CHAR (timestamp_expression,‘YYY-MM-DD HH24:MI:SS‘)

--字符串转时间

TO_DATE (string_expression, ‘YYY-MM-DD HH24:MI:SS‘)

--查询table

SELECT * FROM SYSCAT.TABLES WHERE tabname =‘{0}‘

--查询column

SELECT

    tabname AS tableename,

    colname,

    NVL (remarks, colname) AS coldesc

FROM

    SYSCAT.COLUMNS

WHERE

    tabname = ‘{0}‘

AND tabschema =‘{1}‘

ORDER BY

    colno ASC

SQLSERVER:

--时间转字符串

REPLACE(REPLACE(replace((convert(varchar, getdate(), 120)),‘-‘,‘‘),‘:‘,‘‘),‘ ‘,‘‘)

--字符串转时间

http://www.cnblogs.com/windphoenix/archive/2013/04/26/3044784.html

--查询table

SELECT * from sysobjects where xtype=‘U‘ and name =‘{0}‘

--查询column

select dbo.syscolumns.name as Colname, CONVERT(Varchar(50), Isnull(sys.extended_properties.value ,dbo.syscolumns.name))  as ColDesc from dbo.sysobjects inner join dbo.syscolumns on (dbo.syscolumns.id = dbo.sysobjects.id ) left join sys.extended_properties on (dbo.syscolumns.id = sys.extended_properties.major_id and syscolumns.colid=sys.extended_properties.minor_id) where dbo.sysobjects.xtype=‘U‘ and dbo.sysobjects.name =‘{0}‘

MYSQL:

---时间转字符串
select date_format(now(),‘%Y%m%d%H%i%S‘) --20150425102256

--字符串转时间
SELECT str_to_date(‘20150425102222‘, ‘%Y%m%d%h%i%s‘); --2015-04-25 10:22:22

--查询table

select table_name as TABLENAME,TABLE_COMMENT as TABLEDESC from information_schema.tables where table_name not LIKE ‘T‘ ORDER BY table_name；

--查询column

SELECT * FROM information_schema.columns WHERE table_name = ‘T‘;

SQLITE:

--时间转字符串

strftime(‘%Y%m%d%H%M%S‘,‘now‘)

--字符串转时间

http://blog.csdn.net/xmphoenix/article/details/6438854

http://www.cnblogs.com/ungshow/archive/2010/10/10/1847081.html

--查询table

SELECT *  from sqlite_master  where type=‘table‘ and name =‘{0}‘

--查询column

PRAGMA table_info([T1])