mysql查询表基础信息

-- 一、查询数据库名称为db_name的所有表

 SELECT t.table_catalog,t.table_schema,t.table_name,table_type FROM information_schema.TABLES t where t.table_schema='db_name'
SELECT t.table_catalog,t.table_schema,t.table_name,table_type FROM information_schema.TABLES t where t.table_schema='ry-vue';

-- 二、查询数据库名称为db_name，表名为tb_name的表

SELECT t.table_catalog,t.table_schema,t.table_name,table_type FROM information_schema.TABLES t where t.table_schema='db_name' and t.table_name = 'tb_name'
SELECT t.table_catalog,t.table_schema,t.table_name,table_type FROM information_schema.TABLES t where t.table_schema='ry-vue' and t.table_name = 'sys_menu';

-- 三、查询数据库名称为db_name，以sys_开头的表

SELECT t.table_catalog,t.table_schema,t.table_name,table_type FROM information_schema.TABLES t where t.table_schema='db_name' and t.table_name like 'sys_%';
SELECT t.table_catalog,t.table_schema,t.table_name,table_type FROM information_schema.TABLES t where t.table_schema='ry-vue' and t.table_name like 'sys_%';

-- 四、获取表注释

show table status where NAME='tb_name';
show table status where NAME='sys_user';

 
-- 五、获取表主键

select k.column_name from information_schema.table_constraints t
join information_schema.key_column_usage k
using (constraint_name,table_schema,table_name)
where t.constraint_type='PRIMARY KEY'
and t.table_schema='db_name' and t.table_name='tb_name';

select k.column_name from information_schema.table_constraints t
join information_schema.key_column_usage k
using (constraint_name,table_schema,table_name)
where t.constraint_type='PRIMARY KEY'
and t.table_schema='ry-vue' and t.table_name='sys_menu';

-- 六、获取某个表的所有列

SELECT t.table_schema,t.table_name,t.column_name,t.column_default,
t.is_nullable,t.data_type,t.character_maximum_length,t.numeric_precision,
t.numeric_scale,t.column_type,t.column_key, t.column_comment,t.extra
FROM information_schema.columns t
WHERE t.table_schema = 'db_name' AND t.table_name = 'tb_name';

SELECT t.table_schema,t.table_name,t.column_name,t.column_default,
t.is_nullable,t.data_type,t.character_maximum_length,t.numeric_precision,
t.numeric_scale,t.column_type,t.column_key, t.column_comment,t.extra
FROM information_schema.columns t
WHERE t.table_schema = 'ry-vue' AND t.table_name = 'sys_menu';