mysql查询表名和注释，根据表名查询表字段

1、查询数据库所有表名和表注释

SELECT
	TABLE_NAME AS tableName,
	TABLE_COMMENT AS tableComment 
FROM
	information_schema.`TABLES` 
WHERE
	TABLE_SCHEMA = 'signal_notification';



下面是增加序号列
SELECT
  (@i:=@i+1)"序号",
	TABLE_NAME AS tableName,
	TABLE_COMMENT AS tableComment 
FROM
	information_schema.`TABLES` ,(select @i:=0)j
WHERE
	TABLE_SCHEMA = 'digital_potal'

2、根据表名查询表字段和表注释

SELECT
	TABLE_NAME 表名,
	COLUMN_NAME 列名,
	COLUMN_TYPE 数据类型,
	DATA_TYPE 字段类型,
	CHARACTER_MAXIMUM_LENGTH 长度,
	IS_NULLABLE 是否为空,
	COLUMN_DEFAULT 默认值,
	COLUMN_COMMENT 备注 
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_NAME IN ( '表名2', '表名1' ) 
	AND TABLE_SCHEMA = '数据库名';





SELECT
	column_name,
	NULL,
	data_type,
	column_comment,
IF(column_key='PRI','是','否') AS '主键唯一',
    IF(IS_NULLABLE='NO','是','否') AS '非空'
	-- column_type 
FROM
	information_schema.COLUMNS 
WHERE
	table_name = "user_info" 
	AND table_schema = 'signal_notification';






SELECT
	table_name, -- 表名
	column_name as '字段',
	-- NULL,
	column_type as '字段类型',
	IF(IS_NULLABLE='NO','Y','N') AS '非空',
	-- data_type,
	column_comment as '字段描述'
	-- IF(column_key='PRI','是','否') AS '主键唯一',
	-- column_type 
FROM
	information_schema.COLUMNS 
WHERE
	table_name in("alarm_manage","anomaly_detection") -- 表名
	-- data-supervision 为数据库名称
	AND table_schema = 'data-supervision';

SELECT
	COLUMN_COMMENT,
	COLUMN_NAME 
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_NAME = 'application' 
	-- 要指定查询的数据库，不然会查询到其他库相同表名的字段信息
	AND table_schema = 'test';