欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL基础:show命令总结

程序员文章站 2022-04-29 18:15:43
show命令 show 命令可以提供关于数据库、表、列,或关于服务器的状态信息。 总结 实例 显示建表建库语句 sql mysql SHOW DATABASES; + + | Database | + + | information_schema | | mysql | | performance_ ......

show命令

show命令可以提供关于数据库、表、列,或关于服务器的状态信息。

总结

# 显示二进制文件以及文件大小(需要开启二进制日志记录功能)
show {binary | master} logs

# 显示二进制文件的执行过程
show binlog events [in 'log_name'] [from pos] [limit [offset,] row_count]

# 显示mysql当前支持哪些字符集
show character set [like_or_where]

# 显示mysql支持字符集的排序规则
show collation [like_or_where]

# 显示表的列信息(等同于desc,需要先创建表)
show [full] columns from tbl_name [from db_name] [like_or_where]

# 显示已经创建的库,创建时的语句
show create database db_name

# 显示已经创建的事件,创建时的语句
show create event event_name

# 显示已经创建的函数,创建时的语句
show create function func_name

# 显示已经创建的存储过程,创建时的语句
show create procedure proc_name

# 显示已经创建的表,创建时的语句
show create table tbl_name

# 显示已经创建的触发器,创建时的语句
show create trigger trigger_name

# 显示已经创建的视图,创建时的语句
show create view view_name

# 显示mysql中所有数据库的名称
show databases [like_or_where]

# 显示存储引擎的详细信息
show engine engine_name {status | mutex}

# 显示数据库支持的存储引擎和默认存储引擎
show [storage] engines

# 显示最后一个执行语句所产生的错误信息
show errors [limit [offset,] row_count]

# 显示事件信息
show events

# 服务器内部调试,显示一个指定存储的内部实现的表示形式过程
show function code func_name

# 显示存储函数信息(需要先创建存储函数)
show function status [like_or_where]

# 显示指定用户拥有的权限
show grants for user

# 显示表索引信息(需要先创建索引)
show index from tbl_name [from db_name]

# 显示master当前正在使用的二进制信息
show master status

# 列举在表缓存中当前被打开的非temporary表
show open tables [from db_name] [like_or_where]

# 显示mysql插件信息
show plugins

# 服务器内部调试,显示一个指定存储的内部实现的表示形式过程
show procedure code proc_name

# 显示存储过程信息(需要先创建存储过程)
show procedure status [like_or_where]

# 显示mysql所支持的所有权限,及权限可操作的对象
show privileges

# 显示系统中正在运行的所有进程,普通用户只能查看自己的进行信息
show [full] processlist

# 显示当前会话执行语句资源使用情况
show profile [types] [for query n] [offset n] [limit n]

# 显示当前会话执行语句资源使用情况
show profiles

# 显示relaylog事件信息(需要先做主从复制)
show relaylog events [in 'log_name'] [from pos] [limit [offset,] row_count]

# 显示master主机上已注册的复制主机列表(需要先做主从复制)
show slave hosts

# 显示slave主机状态信息(需要先做主从复制)
show slave status [for channel channel]

# 显示mysql状态信息
show [global | session] status [like_or_where]

# 显示表属性信息
show table status [from db_name] [like_or_where]

# 显示当前数据库中所有表的名称
show [full] tables [from db_name] [like_or_where]

# 显示触发器信息(需要先创建触发器)
show triggers [from db_name] [like_or_where]

# 显示mysql变量信息
show [global | session] variables [like_or_where]

# 显示最后一个执行语句所产生的警告信息
show warnings [limit [offset,] row_count]

实例

显示建表建库语句

mysql> show databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| pubmmrpg           |
| student            |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

mysql> show create database mysql;
+----------+----------------------------------------------------------------+
| database | create database                                                |
+----------+----------------------------------------------------------------+
| mysql    | create database `mysql` /*!40100 default character set utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use mysql;

database changed
mysql> show tables;
+---------------------------+
| tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| 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                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql> show create table mysql.user\g;
*************************** 1. row ***************************
       table: user
create table: create table `user` (
  `host` char(60) collate utf8_bin not null default '',
  `user` char(32) collate utf8_bin not null default '',
  `select_priv` enum('n','y') character set utf8 not null default 'n',
  `insert_priv` enum('n','y') character set utf8 not null default 'n',
  `update_priv` enum('n','y') character set utf8 not null default 'n',
  `delete_priv` enum('n','y') character set utf8 not null default 'n',
  `create_priv` enum('n','y') character set utf8 not null default 'n',
  `drop_priv` enum('n','y') character set utf8 not null default 'n',
  `reload_priv` enum('n','y') character set utf8 not null default 'n',
  `shutdown_priv` enum('n','y') character set utf8 not null default 'n',
  `process_priv` enum('n','y') character set utf8 not null default 'n',
  `file_priv` enum('n','y') character set utf8 not null default 'n',
  `grant_priv` enum('n','y') character set utf8 not null default 'n',
  `references_priv` enum('n','y') character set utf8 not null default 'n',
  `index_priv` enum('n','y') character set utf8 not null default 'n',
  `alter_priv` enum('n','y') character set utf8 not null default 'n',
  `show_db_priv` enum('n','y') character set utf8 not null default 'n',
  `super_priv` enum('n','y') character set utf8 not null default 'n',
  `create_tmp_table_priv` enum('n','y') character set utf8 not null default 'n',
  `lock_tables_priv` enum('n','y') character set utf8 not null default 'n',
  `execute_priv` enum('n','y') character set utf8 not null default 'n',
  `repl_slave_priv` enum('n','y') character set utf8 not null default 'n',
  `repl_client_priv` enum('n','y') character set utf8 not null default 'n',
  `create_view_priv` enum('n','y') character set utf8 not null default 'n',
  `show_view_priv` enum('n','y') character set utf8 not null default 'n',
  `create_routine_priv` enum('n','y') character set utf8 not null default 'n',
  `alter_routine_priv` enum('n','y') character set utf8 not null default 'n',
  `create_user_priv` enum('n','y') character set utf8 not null default 'n',
  `event_priv` enum('n','y') character set utf8 not null default 'n',
  `trigger_priv` enum('n','y') character set utf8 not null default 'n',
  `create_tablespace_priv` enum('n','y') character set utf8 not null default 'n',
  `ssl_type` enum('','any','x509','specified') character set utf8 not null default '',
  `ssl_cipher` blob not null,
  `x509_issuer` blob not null,
  `x509_subject` blob not null,
  `max_questions` int(11) unsigned not null default '0',
  `max_updates` int(11) unsigned not null default '0',
  `max_connections` int(11) unsigned not null default '0',
  `max_user_connections` int(11) unsigned not null default '0',
  `plugin` char(64) collate utf8_bin not null default 'mysql_native_password',
  `authentication_string` text collate utf8_bin,
  `password_expired` enum('n','y') character set utf8 not null default 'n',
  `password_last_changed` timestamp null default null,
  `password_lifetime` smallint(5) unsigned default null,
  `account_locked` enum('n','y') character set utf8 not null default 'n',
  primary key (`host`,`user`)
) engine=myisam default charset=utf8 collate=utf8_bin comment='users and global privileges'
1 row in set (0.00 sec)

mysql> show columns from user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| field                  | type                              | null | key | default               | extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| host                   | char(60)                          | no   | pri |                       |       |
| user                   | char(32)                          | no   | pri |                       |       |
| select_priv            | enum('n','y')                     | no   |     | n                     |       |
| insert_priv            | enum('n','y')                     | no   |     | n                     |       |
| update_priv            | enum('n','y')                     | no   |     | n                     |       |
| delete_priv            | enum('n','y')                     | no   |     | n                     |       |
| create_priv            | enum('n','y')                     | no   |     | n                     |       |
| drop_priv              | enum('n','y')                     | no   |     | n                     |       |
| reload_priv            | enum('n','y')                     | no   |     | n                     |       |
| shutdown_priv          | enum('n','y')                     | no   |     | n                     |       |
| process_priv           | enum('n','y')                     | no   |     | n                     |       |
| file_priv              | enum('n','y')                     | no   |     | n                     |       |
| grant_priv             | enum('n','y')                     | no   |     | n                     |       |
| references_priv        | enum('n','y')                     | no   |     | n                     |       |
| index_priv             | enum('n','y')                     | no   |     | n                     |       |
| alter_priv             | enum('n','y')                     | no   |     | n                     |       |
| show_db_priv           | enum('n','y')                     | no   |     | n                     |       |
| super_priv             | enum('n','y')                     | no   |     | n                     |       |
| create_tmp_table_priv  | enum('n','y')                     | no   |     | n                     |       |
| lock_tables_priv       | enum('n','y')                     | no   |     | n                     |       |
| execute_priv           | enum('n','y')                     | no   |     | n                     |       |
| repl_slave_priv        | enum('n','y')                     | no   |     | n                     |       |
| repl_client_priv       | enum('n','y')                     | no   |     | n                     |       |
| create_view_priv       | enum('n','y')                     | no   |     | n                     |       |
| show_view_priv         | enum('n','y')                     | no   |     | n                     |       |
| create_routine_priv    | enum('n','y')                     | no   |     | n                     |       |
| alter_routine_priv     | enum('n','y')                     | no   |     | n                     |       |
| create_user_priv       | enum('n','y')                     | no   |     | n                     |       |
| event_priv             | enum('n','y')                     | no   |     | n                     |       |
| trigger_priv           | enum('n','y')                     | no   |     | n                     |       |
| create_tablespace_priv | enum('n','y')                     | no   |     | n                     |       |
| ssl_type               | enum('','any','x509','specified') | no   |     |                       |       |
| ssl_cipher             | blob                              | no   |     | null                  |       |
| x509_issuer            | blob                              | no   |     | null                  |       |
| x509_subject           | blob                              | no   |     | null                  |       |
| max_questions          | int(11) unsigned                  | no   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | no   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | no   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | no   |     | 0                     |       |
| plugin                 | char(64)                          | no   |     | mysql_native_password |       |
| authentication_string  | text                              | yes  |     | null                  |       |
| password_expired       | enum('n','y')                     | no   |     | n                     |       |
| password_last_changed  | timestamp                         | yes  |     | null                  |       |
| password_lifetime      | smallint(5) unsigned              | yes  |     | null                  |       |
| account_locked         | enum('n','y')                     | no   |     | n                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.01 sec)

显示二进制文件相关

mysql> show master logs;
+------------------+-----------+
| log_name         | file_size |
+------------------+-----------+
| mysql-bin.002022 |    817345 |
| mysql-bin.002023 |    817932 |
| mysql-bin.002024 |    816758 |
| mysql-bin.002025 |    664678 |
+------------------+-----------+
4 rows in set (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| log_name         | file_size |
+------------------+-----------+
| mysql-bin.002022 |    817345 |
| mysql-bin.002023 |    817932 |
| mysql-bin.002024 |    816758 |
| mysql-bin.002025 |    665265 |
+------------------+-----------+
4 rows in set (0.01 sec)

显示主从相关

mysql> show slave hosts;
empty set (0.00 sec)

mysql> show slave status;
empty set (0.00 sec)

mysql> show relaylog events limit 10;
+--------------------+-----+----------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| log_name           | pos | event_type     | server_id  | end_log_pos | info                                                                                                                                                                      |
+--------------------+-----+----------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| slave-relay.001351 |   4 | format_desc    | 1339158577 |         123 | server ver: 5.7.20-log, binlog ver: 4                                                                                                                                     |
| slave-relay.001351 | 123 | previous_gtids | 1339158577 |         194 | 4941480b-c2fa-11e8-850c-506b4bbe1cf4:1-3111622                                                                                                                            |
| slave-relay.001351 | 194 | rotate         | 2241191473 |           0 | mysql-bin.001063;pos=4                                                                                                                                                    |
| slave-relay.001351 | 241 | format_desc    | 2241191473 |         123 | server ver: 5.7.20-log, binlog ver: 4                                                                                                                                     |
| slave-relay.001351 | 360 | rotate         |          0 |         407 | mysql-bin.001063;pos=234                                                                                                                                                  |
| slave-relay.001351 | 407 | gtid           | 2241191473 |         299 | set @@session.gtid_next= '4941480b-c2fa-11e8-850c-506b4bbe1cf4:3111623'                                                                                                   |
| slave-relay.001351 | 472 | query          | 2241191473 |         539 | /* rds internal mark */ create table if not exists mysql.ha_health_check (
  id   bigint  default 0,
  type char(1) default '0',
  primary key (type)
)
  engine = innodb |
| slave-relay.001351 | 712 | gtid           | 2241191473 |         604 | set @@session.gtid_next= '4941480b-c2fa-11e8-850c-506b4bbe1cf4:3111624'                                                                                                   |
| slave-relay.001351 | 777 | query          | 2241191473 |         672 | begin                                                                                                                                                                     |
| slave-relay.001351 | 845 | table_map      | 2241191473 |         734 | table_id: 81659 (mysql.ha_health_check)                                                                                                                                   |
+--------------------+-----+----------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.01 sec)

mysql> show slave status\g;
*************************** 1. row ***************************
               slave_io_state: waiting for master to send event
                  master_host: 192.168.120.26
                  master_user: replicator
                  master_port: 3069
                connect_retry: 60
              master_log_file: mysql-bin.001063
          read_master_log_pos: 3564725
               relay_log_file: slave-relay.001352
                relay_log_pos: 844419
        relay_master_log_file: mysql-bin.001063
             slave_io_running: yes
            slave_sql_running: yes
              replicate_do_db: 
          replicate_ignore_db: 
           replicate_do_table: 
       replicate_ignore_table: 
      replicate_wild_do_table: 
  replicate_wild_ignore_table: 
                   last_errno: 0
                   last_error: 
                 skip_counter: 0
          exec_master_log_pos: 3564725
              relay_log_space: 3565260
              until_condition: none
               until_log_file: 
                until_log_pos: 0
           master_ssl_allowed: no
           master_ssl_ca_file: 
           master_ssl_ca_path: 
              master_ssl_cert: 
            master_ssl_cipher: 
               master_ssl_key: 
        seconds_behind_master: 0
master_ssl_verify_server_cert: no
                last_io_errno: 0
                last_io_error: 
               last_sql_errno: 0
               last_sql_error: 
  replicate_ignore_server_ids: 
             master_server_id: 2241191473
                  master_uuid: 4941480b-c2fa-11e8-850c-506b4bbe1cf4
             master_info_file: mysql.slave_master_info
                    sql_delay: 0
          sql_remaining_delay: null
      slave_sql_running_state: slave has read all relay log; waiting for more updates
           master_retry_count: 86400
                  master_bind: 
      last_io_error_timestamp: 
     last_sql_error_timestamp: 
          last_sql_error_gtid: 
               master_ssl_crl: 
           master_ssl_crlpath: 
           retrieved_gtid_set: 4941480b-c2fa-11e8-850c-506b4bbe1cf4:1-3119404
            executed_gtid_set: 3c09db04-c2fa-11e8-b5cc-506b4bff2084:1-2275307,
4941480b-c2fa-11e8-850c-506b4bbe1cf4:1-3119404
                auto_position: 1
         replicate_rewrite_db: 
                 channel_name: 
           master_tls_version: 
1 row in set (0.01 sec)

显示字符集变量相关

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| charset  | description                     | default collation   | maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | big5 traditional chinese        | big5_chinese_ci     |      2 |
| dec8     | dec west european               | dec8_swedish_ci     |      1 |
| cp850    | dos west european               | cp850_general_ci    |      1 |
| hp8      | hp west european                | hp8_english_ci      |      1 |
| koi8r    | koi8-r relcom russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 west european            | latin1_swedish_ci   |      1 |
| latin2   | iso 8859-2 central european     | latin2_general_ci   |      1 |
| swe7     | 7bit swedish                    | swe7_swedish_ci     |      1 |
| ascii    | us ascii                        | ascii_general_ci    |      1 |
| ujis     | euc-jp japanese                 | ujis_japanese_ci    |      3 |
| sjis     | shift-jis japanese              | sjis_japanese_ci    |      2 |
| hebrew   | iso 8859-8 hebrew               | hebrew_general_ci   |      1 |
| tis620   | tis620 thai                     | tis620_thai_ci      |      1 |
| euckr    | euc-kr korean                   | euckr_korean_ci     |      2 |
| koi8u    | koi8-u ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | gb2312 simplified chinese       | gb2312_chinese_ci   |      2 |
| greek    | iso 8859-7 greek                | greek_general_ci    |      1 |
| cp1250   | windows central european        | cp1250_general_ci   |      1 |
| gbk      | gbk simplified chinese          | gbk_chinese_ci      |      2 |
| latin5   | iso 8859-9 turkish              | latin5_turkish_ci   |      1 |
| armscii8 | armscii-8 armenian              | armscii8_general_ci |      1 |
| utf8     | utf-8 unicode                   | utf8_general_ci     |      3 |
| ucs2     | ucs-2 unicode                   | ucs2_general_ci     |      2 |
| cp866    | dos russian                     | cp866_general_ci    |      1 |
| keybcs2  | dos kamenicky czech-slovak      | keybcs2_general_ci  |      1 |
| macce    | mac central european            | macce_general_ci    |      1 |
| macroman | mac west european               | macroman_general_ci |      1 |
| cp852    | dos central european            | cp852_general_ci    |      1 |
| latin7   | iso 8859-13 baltic              | latin7_general_ci   |      1 |
| utf8mb4  | utf-8 unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | windows cyrillic                | cp1251_general_ci   |      1 |
| utf16    | utf-16 unicode                  | utf16_general_ci    |      4 |
| utf16le  | utf-16le unicode                | utf16le_general_ci  |      4 |
| cp1256   | windows arabic                  | cp1256_general_ci   |      1 |
| cp1257   | windows baltic                  | cp1257_general_ci   |      1 |
| utf32    | utf-32 unicode                  | utf32_general_ci    |      4 |
| binary   | binary pseudo charset           | binary              |      1 |
| geostd8  | geostd8 georgian                | geostd8_general_ci  |      1 |
| cp932    | sjis for windows japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | ujis for windows japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | china national standard gb18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.02 sec)

mysql> show global variables like 'character%';
+--------------------------+----------------------------+
| variable_name            | value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

显示用户权限相关

mysql> show grants for test;
+-------------------------------------------+
| grants for test@%                         |
+-------------------------------------------+
| grant all privileges on *.* to 'test'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

显示运行进程

mysql> show processlist;
+----+------+-----------+-------+---------+------+----------+------------------+
| id | user | host      | db    | command | time | state    | info             |
+----+------+-----------+-------+---------+------+----------+------------------+
|  6 | root | localhost | mysql | query   |    0 | starting | show processlist |
+----+------+-----------+-------+---------+------+----------+------------------+
1 row in set (0.00 sec)