MySQL系统表相关SQL

╰半橙微兮° 2023-09-29 14:10 7阅读 0赞

#### MySQL系统表相关SQL ####

*   *  一、查询表结构
     *   *   *  1 : SQL
             *  2 : 字段说明
     *  二、查询表字段
     *   *   *  1 : SQL
             *  2 : 字段说明
     *  三、查询表键值
     *   *   *  1 : SQL
             *  2 : 字段说明
     *  四、查询表Check约束
     *   *   *  1 : SQL
             *  2 : 字段说明
     *  五、查询表索引
     *   *   *  1 : SQL
             *  2 : 字段说明
     *  六、其他
     *   *   *  1 : SHOW相关用法

### 一、查询表结构 ###

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

### 二、查询表字段 ###

##### 1 : SQL #####

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

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

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

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

##### 2 : 字段说明 #####

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

### 三、查询表键值 ###

##### 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 : 字段说明 #####

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

### 四、查询表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 : 字段说明 #####

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

### 五、查询表索引 ###

##### 1 : SQL #####

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

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

查询单个表索引信息

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

##### 2 : 字段说明 #####

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

### 六、其他 ###

##### 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查询进程。