mysql 元数据表_MySQL 元数据查询

备注:测试数据库版本为MySQL 8.0

1.查看某个数据库下的表名

select table_name

from information_schema.tables

where table_schema = 'ZQS';

测试记录

mysql> select table_name

-> from information_schema.tables

-> where table_schema = 'ZQS';

+---------------------+

| TABLE_NAME |

+---------------------+

| area_admin_contrast |

| area_admin_info |

| audit_log |

| bb |

| bonus |

| char_test |

| dept |

| emp |

| new_emps |

| new_sal |

| salgrade |

| shawn_tmp |

| t |

| t1 |

| t2 |

| t_ctas |

| t_date |

| t_like |

| test |

| test1 |

| test_enum_set |

| testa |

| tmp_zqs |

| user_info |

| user_info_history |

| v |

+---------------------+

26 rows in set (0.00 sec)

2.查询表的字段

select column_name,data_type,ordinal_position

from information_schema.columns

where table_schema = 'ZQS'

and table_name = 'EMP'

测试记录

mysql> select column_name,data_type,ordinal_position

-> from information_schema.columns

-> where table_schema = 'ZQS'

-> and table_name = 'EMP'

-> ;

+-------------+-----------+------------------+

| COLUMN_NAME | DATA_TYPE | ORDINAL_POSITION |

+-------------+-----------+------------------+

| empno | int | 1 |

| ename | varchar | 2 |

| job | varchar | 3 |

| mgr | int | 4 |

| hiredate | date | 5 |

| sal | decimal | 6 |

| comm | decimal | 7 |

| deptno | int | 8 |

+-------------+-----------+------------------+

8 rows in set (0.00 sec)

3.列出表的索引项

show index from emp\G

测试记录

mysql> show index from emp\G

*************************** 1. row ***************************

Table: emp

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: empno

Collation: A

Cardinality: 14

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

Visible: YES

Expression: NULL

*************************** 2. row ***************************

Table: emp

Non_unique: 1

Key_name: FK_DEPTNO

Seq_in_index: 1

Column_name: deptno

Collation: A

Cardinality: 3

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

Visible: YES

Expression: NULL

2 rows in set (0.00 sec)

4.列出表的约束

select a.table_name,

a.constraint_name,

b.column_name,

a.constraint_type

from information_schema.table_constraints a,

information_schema.key_column_usage b

where a.table_name = 'EMP'

and a.table_schema = 'ZQS'

and a.table_name = b.table_name

and a.table_schema = b.table_schema

and a.constraint_name = b.constraint_name;

测试记录

mysql> select a.table_name,

-> a.constraint_name,

-> b.column_name,

-> a.constraint_type

-> from information_schema.table_constraints a,

-> information_schema.key_column_usage b

-> where a.table_name = 'EMP'

-> and a.table_schema = 'ZQS'

-> and a.table_name = b.table_name

-> and a.table_schema = b.table_schema

-> and a.constraint_name = b.constraint_name;

+------------+-----------------+-------------+-----------------+

| table_name | constraint_name | column_name | constraint_type |

+------------+-----------------+-------------+-----------------+

| emp | PRIMARY | empno | PRIMARY KEY |

| emp | FK_DEPTNO | deptno | FOREIGN KEY |

+------------+-----------------+-------------+-----------------+

2 rows in set (0.02 sec)