MySQL系统表相关SQL

一、查询表结构

1 : SQL

查询数据库中所有表信息，包含视图等。

select * from information_schema.TABLES where TABLE_SCHEMA  = '数据库名';

查询单个表信息

select * from information_schema.TABLES where TABLE_SCHEMA  = '数据库名' and table_type = 'BASE TABLE';

2 : 字段说明

字段	说明
TABLE_CATALOG	数据表登记⽬录
TABLE_SCHEMA	数据表所属的数据库名
TABLE_NAME	表名称
TABLE_TYPE	表类型 [system view | base table]
ENGINE	使⽤的数据库引擎 [MyISAM | CSV | InnoDB]
VERSION	版本，默认值10
ROW_FORMAT	⾏格式 [Compact | Dynamic | Fixed]
TABLE_ROWS	表里所存多少行数据
AVG_ROW_LENGTH	平均行长度
DATA_LENGTH	数据长度
MAX_DATA_LENGTH	最大数据长度
INDEX_LENGTH	索引长度
DATA_FREE	空间碎片
AUTO_INCREMENT	做自增主键的自动增量当前值
CREATE_TIME	表的创建时间
UPDATE_TIME	表的更新时间
CHECK_TIME	表的检查时间
TABLE_COLLATION	表的字符校验编码集
CHECKSUM	校验和
CREATE_OPTIONS	创建选项
TABLE_COMMENT	表的注释、备注

二、查询表字段

1 : SQL

查询数据库中所有表字段信息，包含视图等。

select * from information_schema.COLUMNS where TABLE_SCHEMA  = '数据库名' ;

查询单个表或视图字段信息。

select * from information_schema.COLUMNS where TABLE_SCHEMA  = '数据库名' and TABLE_NAME = '表名';

2 : 字段说明

字段	说明
TABLE_CATALOG	数据表登记⽬录
TABLE_SCHEMA	数据表所属的数据库名
TABLE_NAME	表名称
COLUMN_NAME	列名
ORDINAL_POSITION	列标识号，即字段在表中第几列
COLUMN_DEFAULT	列的默认值
IS_NULLABLE	字段是否可以为空，如果列允许 null，那么该列返回 yes。否则，返回 no
DATA_TYPE	数据类型
CHARACTER_MAXIMUM_LENGTH	以字符为单位的最大长度，适于二进制数据、字符数据，或者文本和图像数据。否则，返回 null。
CHARACTER_OCTET_LENGTH	以字节为单位的最大长度，适于二进制数据、字符数据，或者文本和图像数据。否则，返回 null。
NUMERIC_PRECISION	数据精度。近似数字数据、精确数字数据、整型数据或货币数据的精度。否则，返回 null。
NUMERIC_SCALE	数据规模。近似数字数据、精确数字数据、整数数据或货币数据的小数位数。否则，返回 null。
DATETIME_PRECISION	datetime 及 sql-92 interval 数据类型的子类型代码。对于其它数据类型，返回 null。
CHARACTER_SET_NAME	字符集名称。如果该列是字符数据或 text 数据类型，那么为字符集返回唯一的名称。否则，返回 null。
COLLATION_NAME	字符集校验名称。如果列是字符数据或 text 数据类型，那么为排序次序返回唯一的名称。否则，返回 null。
COLUMN_TYPE	列类型
COLUMN_KEY	关键列 [NULL | MUL | PRI]
EXTRA	额外描述 [NULL | on update CURRENT_TIMESTAMP | auto_increment]
PRIVILEGES	字段操作权限 [select | select,insert,update,references]
COLUMN_COMMENT	字段描述、注释
GENERATION_EXPRESSION	组合字段的公式

三、查询表键值

1 : SQL

查询数据库中所有表键值。

select * from information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '数据库名';

查询单个表键值

select * from information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '数据库名' and TABLE_NAME = '表名';

2 : 字段说明

字段	说明
CONSTRAINT_CATALOG	约束登记目录。键值所属的目录名称，该值始终为def
CONSTRAINT_SCHEMA	约束所属的数据库名称
CONSTRAINT_NAME	约束的名称
TABLE_CATALOG	数据表登记目录。键值所在表所属的目录名称，该值始终为def
TABLE_SCHEMA	键值所在表的数据库名称
TABLE_NAME	键值所属的表名
COLUMN_NAME	键值所属的列名。如果是外键约束，名称是该外键列，不是所引用的列
ORDINAL_POSITION	约束中列的位置，约束列在约束中的位置，从1开始标记。单个约束为1，组合约束由左至右从1标记
POSITION_IN_UNIQUE_CONSTRAINT	如果是唯一或者主键约束，值为NULL,如果是外键约束，该值为被引用表的列的位置
REFERENCED_TABLE_SCHEMA	外键依赖的数据库名
REFERENCED_TABLE_NAME	外键依赖的表名
REFERENCED_COLUMN_NAME	外键依赖的列名

四、查询表Check约束

1 : SQL

查询数据库中的主键约束、外键约束、唯一约束、check约束。

select * from information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = '数据库名';

查询单个表约束信息

select * from information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = '数据库名' and TABLE_NAME = '表名';

2 : 字段说明

字段	说明
CONSTRAINT_CATALOG	约束登记目录
CONSTRAINT_SCHEMA	约束所属的数据库名
CONSTRAINT_NAME	约束的名称
TABLE_SCHEMA	约束依赖表所属的数据库名（一般与CONSTRAINT_SCHEMA相同）
TABLE_NAME	约束所属的表名
CONSTRAINT_TYPE	约束类型 [primary key | foreign key | unique | check ]

五、查询表索引

1 : SQL

查询数据库中的表索引信息。

select * from information_schema.STATISTICS WHERE TABLE_SCHEMA = '数据库名';

查询单个表索引信息

select * from information_schema.STATISTICS WHERE TABLE_SCHEMA = '数据库名' and TABLE_NAME = '表名';

2 : 字段说明

字段	说明
TABLE_CATALOG	数据表登记目录。包含索引的表所属的目录的名称。 该值始终为def
TABLE_SCHEMA	索引所属表的数据库名
TABLE_NAME	索引所属的表名
NON_UNIQUE	索引不唯一标识。索引字段值不能重复，则为0；能重复，则为1
INDEX_SCHEMA	索引所属的数据库名 （一般与TABLE_SCHEMA相同）
INDEX_NAME	索引名称。如果是主键，则始终为PRIMARY
SEQ_IN_INDEX	索引中的列序列号，索引列在索引中的位置，从1开始标记。单个索引为1，组合索引由左至右从1标记
COLUMN_NAME	索引列的列名
COLLATION	校对。列在索引中排序方式：A（升序），D（降序），NULL（未排序）
CARDINALITY	基数（一般与该表的数据行数相同）。估计索引中唯一值的数量，该值不一定精确
SUB_PART	索引前缀。 如果列仅被部分索引，则索引字符的数量，如果整列被索引，则为NULL
PACKED	指示密钥的打包方式。 默认为NULL
NULLABLE	列是否可能包含NULL值，是则 YES，否则 “”
INDEX_TYPE	索引类型 [BTREE | HASH | FULLTEXT | RTREE ]，一般设置为BTREE（平衡树索引）
COMMENT	有关未在其自己的列中描述的索引的信息
INDEX_COMMENT	索引注释、备注

六、其他

1 : SHOW相关用法

SHOW DATABASES
查询MySQL Server上的数据库。

SHOW TABLES FROM db_name
查询数据库（db_name）中的表名称。

SHOW TABLE STATUS FROM db_name
查询数据库（db_name）的表信息，比较详细。

SHOW COLUMNS FROM tb_name FROM db_name
SHOW FIELDS FROM tb_name FROM db_name
查询数据库（db_name）的表（tb_name）的列信息。

DESCRIBE tb_name col_name
需先选择数据库，查询表（tb_name）的列（col_name）信息。

SHOW FULL COLUMNS FROM tb_name FROM db_name
SHOW FULL FIELDS FROM tb_name FROM db_name
查询数据库（db_name）的表（tb_name）的列信息，比较详细。

SHOW INDEX FROM tb_name FROM db_name
查询数据库（db_name）的表（tb_name）的索引信息。

SHOW STATUS
查询MySQL Server的状态信息。

SHOW VARIABLES
查询MySQL Server系参数值。

SHOW PROCESSLIST
查看当前MySQL查询进程。

SHOW GRANTS FOR user
查询用户（user）的授权命令。

有什么不对的还望指正，书写不易，觉得有帮助就点个赞吧！☺☺☺