mysql建表语句 numeric_mysql-查看表结构、建表语句和与oracle的比较(转)

1、查看表结构

desc tableName;

show columns from tableName;

describe tableName;

这三个显示的结果都是一样的，显示表中filed，type，null，key，default及extra。

--TABLE_SCHEMA表示数据库名称

select * from information_schema.COLUMNS where TABLE_SCHEMA='gm' and TABLE_NAME='t_role';

这个显示的结果就比较全了。

2、查看建表语句

show create table tableName;

3、接下来，来点更全的sql，这个是用来同步mysql和orac数据字典的所有sql。

mysql部分：

## 查看所有的库

SELECT

lower(schema_name) schema_name

FROM

information_schema.schemata

WHERE

schema_name NOT IN (

'mysql',

'information_schema',

'test',

'search',

'tbsearch',

'sbtest',

'dev_ddl'

)

## 产看某一个库中的所有表

SELECT

table_name,

create_time updated_at,

table_type,

ENGINE,

table_rows num_rows,

table_comment,

ceil(data_length / 1024 / 1024) store_capacity

FROM

information_schema.TABLES

WHERE

table_schema = 'employees'

AND table_name NOT LIKE 'tmp#_%' ESCAPE '#'

##查看某一个库下某一个表的所有字段

SELECT

lower(column_name) column_name,

ordinal_position position,

column_default dafault_value,

substring(is_nullable, 1, 1) nullable,

column_type data_type,

column_comment,

character_maximum_length data_length,

numeric_precision data_precision,

numeric_scale data_scale

FROM

information_schema.COLUMNS

WHERE

table_schema = 'employees'

AND table_name = 'employees';

## 查看某一个库下某一张表的索引

SELECT DISTINCT

lower(index_name) index_name,

lower(index_type) type

FROM

information_schema.statistics

WHERE

table_schema = 'employees'

AND table_name = 'employees';

## 查看某一个库下某一张表的某一个索引

SELECT

lower(column_name) column_name,

seq_in_index column_position

FROM

information_schema.statistics

WHERE

table_schema = 'employees'

AND table_name = 'employees'

AND index_name = 'primary';

## 查看某一个库下某一个表的注释

SELECT

table_comment comments

FROM

information_schema.TABLES

WHERE

table_schema = 'employees'

AND table_name = 'employees';

## 查看某一个库下某一个表的列的注释

SELECT

lower(column_name) column_name,

column_comment comments

FROM

COLUMNS

WHERE

table_schema = 'employees'

AND table_name = 'employees';

oracle部分：

#table structure:

SELECT

lower(table_name) table_name,

TEMPORARY,

tablespace_name,

num_rows,

duration,

'ORACLE' table_type,

partitioned,

(

SELECT

ceil(sum(bytes) / 1024 / 1024)

FROM

dba_segments b

WHERE

a. OWNER = b. OWNER

AND a.table_name = b.segment_name

) AS store_capacity

FROM

dba_tables a

WHERE

OWNER = ?

AND table_name NOT LIKE 'TMP%';

SELECT

lower(column_name) column_name,

column_id position,

data_type,

data_length,

data_precision,

data_scale,

nullable,

data_default default_value,

default_length

FROM

dba_tab_columns

WHERE

OWNER = ?

AND table_name = ?;

# index

SELECT

lower(index_name) index_name,

index_type type

FROM

dba_indexes

WHERE

OWNER = ?

AND table_name = ?

AND index_name NOT LIKE 'SYS_IL%';

SELECT

lower(column_name) column_name,

column_position,

descend

FROM

dba_ind_columns

WHERE

table_owner = ?

AND table_name = ?

AND index_name = ?;

#collect description

SELECT

comments

FROM

dba_tab_comments

WHERE

OWNER = ?

AND table_name = ?;

SELECT

lower(column_name) column_name,

comments

FROM

dba_col_comments

WHERE

OWNER = ?

AND table_name = ?;

#database

SELECT

lower(username) username

FROM

dba_users

WHERE

username NOT IN (

'STDBYPERF',

'READONLY',

'APPQOSSYS',

'ANYSQL',

'DBFLASH',

'SYS',

'SYSTEM',

'MONITOR',

'TBSEARCH',

'MANAGER',

'SYSMAN',

'EXFSYS',

'WMSYS',

'DIP',

'TSMSYS',

'ORACLE_OCM',

'OUTLN',

'DBSNMP',

'PERFSTAT',

'SEARCH',

'TOOLS',

'TBDUMP',

'DMSYS',

'XDB',

'ANONYMOUS',

'DEV_DDL'

);

#segsize

SELECT

round(sum(bytes) / 1024 / 1024, 0) mbytes

FROM

dba_segments

WHERE

OWNER = ?

AND segment_name = ?;