mysql的常用系统表和视图有哪几种

       这期内容当中小编将会给大家带来有关mysql的常用系统表和视图有哪些，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
 
一.information_schema下常用表
 
1.SCHEMATA
 
      实例下所有数据库的信息
 
*************************** 1. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: information_schema
DEFAULT_CHARACTER_SET_NAME: utf8
    DEFAULT_COLLATION_NAME: utf8_general_ci
                  SQL_PATH: NULL
 
2.TABLES
 
提供了表和视图的详细信息
 
*************************** 298. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: wwj2
     TABLE_NAME: t1
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 3
 AVG_ROW_LENGTH: 5461
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 4
    CREATE_TIME: 2018-04-17 10:44:04
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
 
3.COLUMNS
 
详细的表列信息
 
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: wwj
              TABLE_NAME: t3
             COLUMN_NAME: idt3
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY: PRI
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
   GENERATION_EXPRESSION:
 
4.STATISTICS
 
表的索引信息
 
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: wwj
   TABLE_NAME: t3
   NON_UNIQUE: 0
 INDEX_SCHEMA: wwj
   INDEX_NAME: PRIMARY
 SEQ_IN_INDEX: 1
  COLUMN_NAME: idt3
    COLLATION: A
  CARDINALITY: 8
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE:
   INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: wwj
   TABLE_NAME: t3
   NON_UNIQUE: 1
 INDEX_SCHEMA: wwj
   INDEX_NAME: idx-1
 SEQ_IN_INDEX: 1
  COLUMN_NAME: idt4
    COLLATION: A
  CARDINALITY: 8
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE:
   INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:
 
5.USER_PRIVILEGES
 
用户权限信息
 
+-----------------------------+---------------+-------------------------+--------------+
| GRANTEE                     | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+-----------------------------+---------------+-------------------------+--------------+
| 'root'@'localhost'          | def           | SELECT                  | YES          |
| 'root'@'localhost'          | def           | INSERT                  | YES          |
| 'root'@'localhost'          | def           | UPDATE                  | YES          |
| 'root'@'localhost'          | def           | DELETE                  | YES          |
| 'root'@'localhost'          | def           | CREATE                  | YES          |
| 'root'@'localhost'          | def           | DROP                    | YES          |
| 'root'@'localhost'          | def           | RELOAD                  | YES          |
| 'root'@'localhost'          | def           | SHUTDOWN                | YES          |
| 'root'@'localhost'          | def           | PROCESS                 | YES          |
| 'root'@'localhost'          | def           | FILE                    | YES          |
| 'root'@'localhost'          | def           | REFERENCES              | YES          |
| 'root'@'localhost'          | def           | INDEX                   | YES          |
| 'root'@'localhost'          | def           | ALTER                   | YES          |
| 'root'@'localhost'          | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost'          | def           | SUPER                   | YES          |
| 'root'@'localhost'          | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost'          | def           | LOCK TABLES             | YES          |
| 'root'@'localhost'          | def           | EXECUTE                 | YES          |
| 'root'@'localhost'          | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost'          | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost'          | def           | CREATE VIEW             | YES          |
| 'root'@'localhost'          | def           | SHOW VIEW               | YES          |
| 'root'@'localhost'          | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost'          | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost'          | def           | CREATE USER             | YES          |
| 'root'@'localhost'          | def           | EVENT                   | YES          |
| 'root'@'localhost'          | def           | TRIGGER                 | YES          |
| 'root'@'localhost'          | def           | CREATE TABLESPACE       | YES          |
| 'mysql.session'@'localhost' | def           | SUPER                   | NO           |
| 'mysql.sys'@'localhost'     | def           | USAGE                   | NO           |
| 'REPL_USER'@'109.115.12.41' | def           | REPLICATION SLAVE       | NO           |
| 'REPL_USER'@'109.115.12.42' | def           | REPLICATION SLAVE       | NO           |
| 'REPL_USER'@'109.115.12.%'  | def           | REPLICATION SLAVE       | NO           |
| 'REPL_USER'@'109.115.12.%'  | def           | REPLICATION CLIENT      | NO           |
| 'root'@'%'                  | def           | SELECT                  | NO           |
| 'root'@'%'                  | def           | INSERT                  | NO           |
| 'root'@'%'                  | def           | UPDATE                  | NO           |
| 'root'@'%'                  | def           | DELETE                  | NO           |
| 'root'@'%'                  | def           | CREATE                  | NO           |
| 'root'@'%'                  | def           | DROP                    | NO           |
| 'root'@'%'                  | def           | RELOAD                  | NO           |
| 'root'@'%'                  | def           | SHUTDOWN                | NO           |
| 'root'@'%'                  | def           | PROCESS                 | NO           |
| 'root'@'%'                  | def           | FILE                    | NO           |
| 'root'@'%'                  | def           | REFERENCES              | NO           |
| 'root'@'%'                  | def           | INDEX                   | NO           |
| 'root'@'%'                  | def           | ALTER                   | NO           |
| 'root'@'%'                  | def           | SHOW DATABASES          | NO           |
| 'root'@'%'                  | def           | SUPER                   | NO           |
| 'root'@'%'                  | def           | CREATE TEMPORARY TABLES | NO           |
| 'root'@'%'                  | def           | LOCK TABLES             | NO           |
| 'root'@'%'                  | def           | EXECUTE                 | NO           |
| 'root'@'%'                  | def           | REPLICATION SLAVE       | NO           |
| 'root'@'%'                  | def           | REPLICATION CLIENT      | NO           |
| 'root'@'%'                  | def           | CREATE VIEW             | NO           |
| 'root'@'%'                  | def           | SHOW VIEW               | NO           |
| 'root'@'%'                  | def           | CREATE ROUTINE          | NO           |
| 'root'@'%'                  | def           | ALTER ROUTINE           | NO           |
| 'root'@'%'                  | def           | CREATE USER             | NO           |
| 'root'@'%'                  | def           | EVENT                   | NO           |
| 'root'@'%'                  | def           | TRIGGER                 | NO           |
| 'root'@'%'                  | def           | CREATE TABLESPACE       | NO           |
+-----------------------------+---------------+-------------------------+--------------+
 
6.SCHEMA_PRIVILEGES
 
数据库权限信息
 
+-----------------------------+---------------+--------------------+----------------+--------------+
| GRANTEE                     | TABLE_CATALOG | TABLE_SCHEMA       | PRIVILEGE_TYPE | IS_GRANTABLE |
+-----------------------------+---------------+--------------------+----------------+--------------+
| 'mysql.sys'@'localhost'     | def           | sys                | TRIGGER        | NO           |
| 'mysql.session'@'localhost' | def           | performance_schema | SELECT         | NO           |
+-----------------------------+---------------+--------------------+----------------+--------------+
 
7.TABLE_PRIVILEGES
 
表权限信息
 
*************************** 3. row ***************************
       GRANTEE: 'wwj'@'%'
 TABLE_CATALOG: def
  TABLE_SCHEMA: wwj
    TABLE_NAME: t3
PRIVILEGE_TYPE: SELECT
  IS_GRANTABLE: NO
 
8.COLUMN_PRIVILEGES
 
关于列权限信息
 
9.CHARACTER_SETS
 
mysql可用字符集
 
10.TABLE_CONSTRAINTS
 
表的约束信息
 
11.KEY_COLUMN_USAGE
 
描述了具有约束的列的信息
 
*************************** 79. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: wwj2
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: wwj2
                   TABLE_NAME: t1
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL