需求背景是给一个表名然后给出相应的表结构信息及索引信息
常用的命令有如下:
1. desc tableName; desc employees.employees;
2. show columns from tableName; show COLUMNS from employees.employees;
3. describe tableName; DESCRIBE employees.employees;
这三个显示的结果都是一样的,显示表中filed,type,null,key,default及extra。
4. show create table tableName; show CREATE TABLE employees.employees;
这个语句会显示这个表的建表语句。
5. select * from columns where table_name='表名';select * from information_schema.COLUMNS where TABLE_SCHEMA='employees' and TABLE_NAME='employees';
这个显示的结果就比较全了。
接下来,来点更全的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';