1、读取表的字段
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='mammothcode';
SELECT column_name,column_comment FROM information_schema. COLUMNSWHERE table_schema = 'staq' AND table_name = 'jfjy_user_exam' AND table_name = 'jfjy_users_group'
2、读取指定的表、字段
SELECT COLUMN_NAME,column_comment FROM INFORMATION_SCHEMA.Columns WHERE table_name='t_adminuser' AND table_schema='mammothcode'
SELECT COLUMN_NAME 列名, DATA_TYPE 字段类型,COLUMN_COMMENT 字段注释
FROM INFORMATION_SCHEMA. COLUMNS WHERE
table_name = 'jfjy_user_exam' ##表名AND table_schema = 'staq' ##数据库名 AND column_name LIKE 'uid' ##字段名
3、一次性读取全部的字段
SELECT t.TABLE_NAME,t.TABLE_COMMENT,c.COLUMN_NAME,c.COLUMN_TYPE,c.COLUMN_COMMENT FROM information_schema.TABLES t,INFORMATION_SCHEMA.Columns c WHERE c.TABLE_NAME=t.TABLE_NAME AND t.`TABLE_SCHEMA`='mammothcode'
4、查看表的详细信息
SELECT
column_name AS '列名',
data_type AS '数据类型',
character_maximum_length AS '字符长度',
numeric_precision AS '数字长度',
numeric_scale AS '小数位数',
is_nullable AS '是否允许非空',
CASE
WHEN extra = 'auto_increment' THEN 1
ELSE 0
END AS '是否自增',
column_default AS '默认值',
column_comment AS '备注'
FROM
Information_schema.columns
WHERE
table_Name='test_table';