mysql 查询表字段名,注释 ， 以及sql拼接查询出的内容

#sql查询字段名，注释操作拼接

#查询字段名和注释  
select COLUMN_NAME,COLUMN_COMMENT from information_schema.COLUMNS where table_name = '表名' and table_schema = '库名' order by ordinal_position 
#查询整个内容
select * from information_schema.COLUMNS where table_name = '表名' and table_schema = '库名' order by ordinal_position 

#拼接  group_concat(field，SEPARATOR '拼接字符')  
select group_concat(COLUMN_NAME SEPARATOR ';'),group_concat(COLUMN_COMMENT SEPARATOR ';') from information_schema.COLUMNS where table_name = '表名' and table_schema = '库名' order by ordinal_position 

#按顺序排字段拼接 group_concat(field order by field,SEPARATOR '字符')
select group_concat(COLUMN_NAME order by ordinal_position SEPARATOR ';'),group_concat(COLUMN_COMMENT ORDER BY ordinal_position SEPARATOR ';') from information_schema.COLUMNS where table_name = '表名' and table_schema = '库名' order by ordinal_position 

#整个库下的所有表
select TABLE_NAME,group_concat(COLUMN_NAME order by ordinal_position SEPARATOR ';'),group_concat(COLUMN_COMMENT ORDER BY ordinal_position SEPARATOR ';') from information_schema.COLUMNS where table_schema = '库名'  GROUP BY TABLE_NAME

#整个服务器下的所有库中表，每个表的字段和注释的拼接
select TABLE_SCHEMA,TABLE_NAME,group_concat(COLUMN_NAME order by ordinal_position SEPARATOR ';'),group_concat(COLUMN_COMMENT ORDER BY ordinal_position SEPARATOR ';') from information_schema.COLUMNS   GROUP BY TABLE_NAME,TABLE_SCHEMA ORDER BY TABLE_SCHEMA

#拼接数据库名和表名
select CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) from information_schema.COLUMNS   GROUP BY TABLE_NAME,TABLE_SCHEMA ORDER BY TABLE_SCHEMA;