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';