获取表名数据
SELECT
TABLE_NAME 表名 ,
TABLE_COMMENT 表注释
FROM
INFORMATION_SCHEMA.`TABLES`
WHERE
table_schema = '数据库名'
ORDER BY
table_name ;
获取表字段数据
SELECT
table_name 表名,
COLUMN_NAME 列名,
COLUMN_COMMENT 备注,
COLUMN_TYPE 数据类型,
DATA_TYPE 字段类型,
CHARACTER_MAXIMUM_LENGTH 长度,
IS_NULLABLE 是否必填
FROM
INFORMATION_SCHEMA.`COLUMNS`
WHERE
table_schema = '数据库名'
ORDER BY
table_name
表字段改大写为小写(LOWER),小写改为大写(UPPER)
SELECT concat('ALTER TABLE '
, '表名'
, ' CHANGE COLUMN '
, COLUMN_NAME, ' '
, LOWER(COLUMN_NAME)
, ' '
, COLUMN_TYPE
, ' COMMENT '''
, COLUMN_COMMENT
, ''';') AS '修改脚本'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '数据库名'
and TABLE_NAME = '表名';
关联查询表名和列数据
SELECT DISTINCT
sc.table_name 表名,
st.TABLE_COMMENT 表备注
-- ,
-- sc.COLUMN_NAME 列名,
-- sc.COLUMN_COMMENT 备注,
-- sc.COLUMN_TYPE 数据类型,
-- sc.DATA_TYPE 字段类型,
-- sc.CHARACTER_MAXIMUM_LENGTH 长度,
-- sc.IS_NULLABLE 是否必填
FROM
INFORMATION_SCHEMA.`COLUMNS` sc
INNER JOIN INFORMATION_SCHEMA.TABLES st ON sc.table_name = st.TABLE_NAME
WHERE
sc.table_schema = '数据库名'
AND sc.COLUMN_NAME IN ( 'xxx', 'xxx' )
AND sc.table_name NOT IN ( 'xxx', 'xxx' )
ORDER BY
sc.table_name;
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '数据库名';