MySQL相关的数据库字典主要在以下几个数据库里面：
（1）information_schema
（2）performance_schema
（3）mysql
（4）sys （在5.7开始出现）
| 命令/语句 | 功能 |
|---|---|
| show engines; | 查看存储引擎 |
| select database(); | 查看当前数据库 |
| show databases; | 查看数据库列表 |
| show create database test; | 查看数据库test的建库语句 |
| show tables; | 查看当前数据库下的表 |
| show tables from test; | 查看数据库test下的表 |
| show create table xxx; | 查看表xxx的建表语句 |
| select user(); | 查看当前用户 |
| show engine innodb status; | 查看InnoDB存储引擎的状态信息 |
| show grants for 'xxx'@'xxx' | 查看用户的权限信息 |
| show create user 'xxx'@'xxx' | 查看用户的属性信息 |
| show columns from columns_priv like '%ab%'; | 查看含有ab 字样的字段 |
| show processlist; | 查看mysql线程列表 |
| show status; | 查看 mysql 状态 |
| show variables like '%xx%'; | 查看含有xx字样的参数 |
| show global status; | 查看 mysql 全局状态 |
| select user,host from mysql.user; | 查看用户列表信息 |
| select * from information_schema.routines; | 查看存储过程列表 |
查看存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
查看当前数据库
mysql> use mysql;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
查看数据库列表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
查看数据库sys的建库语句
mysql> show create database sys;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| sys | CREATE DATABASE `sys` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看当前数据库下的表
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
35 rows in set (0.01 sec)
mysql>
查看当前用户
mysql> select user();
+-----------------+
| user() |
+-----------------+
| ncayu@localhost |
+-----------------+
1 row in set (0.00 sec)
