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）的授权命令。
有什么不对的还望指正，书写不易，觉得有帮助就点个赞吧！☺☺☺
129
