mysql 数据库操作获取某一个表的所有信息主键外键类型_mysqlworkbench 8.0 查看某张表的外键-CSDN博客

本文提供了多个SQL查询示例，包括从指定数据库中检索表结构、外键关系及表注释等详细信息。通过这些示例，读者可以学习如何高效地使用SQL进行数据管理和维护。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT * FROM information_schema.columns a JOIN information_schema.TABLES b ON a.table_schema=b.table_schema AND a.table_name =b.table_name WHERE a.table_name IN( SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA ='表名'

SELECT a.TABLE_SCHEMA, a.TABLE_NAME,a.COLUMN_NAME,a.CONSTRAINT_NAME,
a.REFERENCED_TABLE_NAME,a.REFERENCED_COLUMN_NAME FROM KEY_COLUMN_USAGE a
WHERE
a.TABLE_NAME ='表名' AND a.TABLE_SCHEMA='数据库名称' AND REFERENCED_COLUMN_NAME IS NOT NULL

) AND a.TABLE_SCHEMA ='表名'

SELECT COLUMN_NAME,b.TABLE_SCHEMA,b.table_name,b.table_comment,column_name,table_comment FROM information_schema.columns a JOIN information_schema.TABLES b ON a.table_schema=b.table_schema AND a.table_name =b.table_name WHERE a.table_name IN( SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA ='unmanplane') and a.table_schema='unmanplane' group by b.table_comment;

获得实例

SELECT b.table_name,COLUMN_NAME,concat(upper(left(b.table_name,1)),substring(b.table_name,2)), b.table_comment FROM information_schema.columns a JOIN information_schema.TABLES b ON a.table_schema=b.table_schema AND a.table_name =b.table_name WHERE a.table_name IN( SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA ='unmanplane') and a.table_schema='unmanplane' and table_comment is not null group by table_name ;