MySQL 元数据查询

最新推荐文章于 2024-09-03 08:58:12 发布

只是甲

最新推荐文章于 2024-09-03 08:58:12 发布

阅读量1.2k

点赞数

分类专栏： # MySQL CookBook MySQL从小工到专家之路文章标签： sql mysql MySQL元数据查询 MySQL索引查询 MySQL约束查询

本文链接：https://blog.csdn.net/u010520724/article/details/108440653

版权

MySQL从小工到专家之路同时被 2 个专栏收录

181 篇文章 24 订阅

订阅专栏

MySQL CookBook

73 篇文章 3 订阅

订阅专栏

备注:测试数据库版本为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)