admin 管理员组文章数量: 1184232
2024年3月6日发(作者：print screen键在哪里)
mysql元数据表_MySQL元数据查询备注:测试数据库版本为MySQL 8.01.查看某个数据库下的表名select table_namefrom information_where table_schema = 'ZQS';测试记录mysql> select table_name-> from information_-> 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_positionfrom information_swhere table_schema = 'ZQS'and table_name = 'EMP'测试记录mysql> select column_name,data_type,ordinal_position-> from information_s-> 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 empG测试记录
mysql> show index from empG*************************** 1. row ***************************Table: empNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: empnoCollation: ACardinality: 14Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:Visible: YESExpression: NULL*************************** 2. row ***************************Table: empNon_unique: 1Key_name: FK_DEPTNOSeq_in_index: 1Column_name: deptnoCollation: ACardinality: 3Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:Index_comment:Visible: YESExpression: NULL2 rows in set (0.00 sec)
4.列出表的约束select _name,aint_name,_name,aint_typefrom information__constraints a,information__column_usage bwhere _name = 'EMP'and _schema = 'ZQS'and _name = _nameand _schema = _schemaand aint_name = aint_name;测试记录mysql> select _name,-> aint_name,-> _name,-> aint_type-> from information__constraints a,-> information__column_usage b-> where _name = 'EMP'-> and _schema = 'ZQS'-> and _name = _name-> and _schema = _schema-> and aint_name = aint_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)
版权声明：本文标题：mysql元数据表_MySQL元数据查询 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://roclinux.cn/b/1709673101a543092.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论