查看所有的库
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 = 'tableName';
查看某一个库下某一个表的列的注释
SELECT LOWER(column_name) column_name,
column_comment comments
FROM information_schema.columns
WHERE table_schema ='databaseName'
AND table_name = 'tableName';
Mysql查看表结构信息
最新推荐文章于 2024-06-06 11:08:49 发布