MySQL常用SQL语句之SHOW语句详解
SQL语句之SHOW语句
SHOW DATABASES – 显示当前所有数据库的名称
mysql> SHOW DATABASES;
SHOW TABLES – 显示当前数据库中所有表的名称(需要设置默认数据库use DATABASE_NAME)
Mysql> SHOW TABLES;
SHOW TABLES FROM db_name – 显示数据库中的所有表
Mysql> SHOW TABLES FROM db_name;
SHOW ENGINES - 显示MySQL当前支持哪些存储引擎和默认存储引擎
mysql> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
SHOW CHARACTER SET – 显示MySQL当前支持哪些字符集
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 | -------------------------------------------------------------------------
mysql> SHOW CHARACTER SET LIKE '%utf%'; +---------+------------------+--------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+------------------+--------------------+--------+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | +---------+------------------+--------------------+--------+ 5 rows in set (0.00 sec)
SHOW COLLATION – 显示MySQL支持字符集的排序规则
mysql> SHOW COLLATION; +--------------------------+----------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------------+----------+-----+---------+----------+---------+ | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | | big5_bin | big5 | 84 | | Yes | 1 |
SHOW BINARY | MASTER – 显示二进制文件以及文件大小（需要开启二进制日志记录功能）
mysql> SHOW BINARY LOGS; mysql> SHOW MASTER LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 143 | | mysql-bin.000002 | 143 |
SHOW BINLOG EVENTS – 显示二进制文件的执行过程
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001'; +------------------+-----+-------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.27-log, Binlog ver: 4 | | mysql-bin.000001 | 120 | Stop | 1 | 143 | | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ 2 rows in set (0.00 sec)
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 120; +------------------+-----+------------+-----------+-------------+------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+------------+-----------+-------------+------+ | mysql-bin.000001 | 120 | Stop | 1 | 143 | | +------------------+-----+------------+-----------+-------------+------+ 1 row in set (0.01 sec)
SHOW COLUMNS – 显示表的列信息（等同于DESC，需要先创建表）
mysql> SHOW COLUMNS FROM blog.info; +---------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | NAME | char(8) | NO | | NULL | | | AGE | int(11) | NO | | NULL | | | ADDRESS | varchar(20) | NO | | NULL | | | SALARY | decimal(10,2) | NO | | NULL | | +---------+---------------+------+-----+---------+----------------+ 5 rows in set (0.06 sec)
SHOW CREATE DATABASES – 显示已经创建的库，创建时的语句
mysql> SHOW CREATE DATABASE blog; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec)
SHOW CREATE TABLE – 显示已经创建的表，创建时的语句
mysql> SHOW CREATE TABLE infoG *************************** 1. row *************************** Table: info Create Table: CREATE TABLE `info` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `NAME` char(8) NOT NULL, `AGE` int(11) NOT NULL, `ADDRESS` varchar(20) NOT NULL, `SALARY` decimal(10,2) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
SHOW CREATE FUNTCION – 显示已经创建的函数，创建时的语句
SHOW CREATE PROCEDURE – 显示已经创建的存储过程，创建时的语句
mysql> SHOW CREATE PROCEDURE test.simpleprocG *************************** 1. row *************************** Procedure: simpleproc - 存储过程的名字 sql_mode: Create Procedure: CREATE PROCEDURE `simpleproc`(OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM t; END character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci
SHOW CREATE TRIGGER - 显示已经创建的触发器，创建时的语句
mysql> SHOW CREATE TRIGGER ins_sumG *************************** 1. row *************************** Trigger: ins_sum sql_mode: NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`me`@`localhost` TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci
SHOW CREATE VIEW – 显示已经创建的视图，创建时的语句
mysql> SHOW CREATE VIEW vG *************************** 1. row *************************** View: v Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`bob`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select 1 AS `a`,2 AS `b` character_set_client: latin1 collation_connection: latin1_swedish_ci
SHOW CREATE EVENTS – 显示已经创建的事件，创建时的语句
mysql> SHOW CREATE EVENT test.e_dailyG *************************** 1. row *************************** Event: e_daily sql_mode: time_zone: SYSTEM Create Event: CREATE EVENT `e_daily` ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 6 HOUR ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Saves total number of sessions then clears the table each day' DO BEGIN INSERT INTO site_activity.totals (time, total) SELECT CURRENT_TIMESTAMP, COUNT(*) FROM site_activity.sessions; DELETE FROM site_activity.sessions; END character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci
SHOW ENGINE – 显示存储引擎的详细信息
mysql> SHOW ENGINE INNODB STATUSG *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2015-11-22 19:05:05 7f6b6c780700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 61 seconds ..................
SHOW WARNINGS – 显示最后一个执行语句所产生的警告信息
mysql> SHOW WARNINGSG mysql> SELECT @@warning_count; +-----------------+ | @@warning_count | +-----------------+ | 1 | +-----------------+ 1 row in set (0.01 sec)
SHOW ERRORS – 显示最后一个执行语句所产生的错误信息
mysql> SHOW ERRORSG
SHOW EVENTS – 显示事件信息
mysql> SHOW EVENTS; Empty set (0.02 sec)
SHOW GRANTS – 显示一个用户所拥有的权限
mysql> SHOW GRANTS; mysql> SHOW GRANTS FOR CURRENT_USER(); mysql> SHOW GRANTS FOR 'root'@'localhost'; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+
SHOW PROCESSLIST – 显示系统中正在运行的所有进程，普通用户只能查看自己的进行信息
mysql> SHOW PROCESSLIST; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 6 | root | localhost | blog | Query | 0 | init | SHOW PROCESSLIST | +----+------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec)
SHOW PRIVILEGES – 显示MySQL所支持的所有权限，及权限可操作的对象
mysql> SHOW PRIVILEGES;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
SHOW PLUGINS – 显示MySQL插件信息
mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | ...................
SHOW MASTER STATUS – 显示Master当前正在使用的二进制信息
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000010 | 7040 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
SHOW TABLE STATUS – 显示表属性信息
mysql> SHOW TABLE STATUS LIKE 'info'G *************************** 1. row *************************** Name: info - 表名 Engine: InnoDB - 表存储引擎 Version: 10 - 表文件版本号 Row_format: Compact - 行存储格式（固定、动态、压缩、冗余、紧凑） Rows: 1 - 表中行数量 Avg_row_length: 16384 - 平均行长度 Data_length: 16384 - 数据文件长度 Max_data_length: 0 - 数据文件最大长度，这是可以存储在表中的数据的总字节数 Index_length: 0 - 索引文件长度 Data_free: 0 - 表文件已分配但未使用的字节数 Auto_increment: 2 - 下一个auto_increment的值 Create_time: 2015-11-22 17:53:10 - 表创建时间 Update_time: NULL - 表更新时间，没有为NULL Check_time: NULL - 表检查时间，没有为NULL Collation: utf8_general_ci - 表使用的排序规则 Checksum: NULL - 表校验 Create_options: Comment: 1 row in set (0.00 sec)
SHOW INDEX – 显示表索引信息（需要先创建索引，详情看“索引文章”）
mysql> show index from testdb.infoG *************************** 1. row *************************** Table: info - 表名 Non_unique: 0 Key_name: PRIMARY - 索引名字 Seq_in_index: 1 - 索引中列顺序号从1开始 Column_name: ID - 索引列 Collation: A - 列怎样在索引中被排序 Cardinality: 6 - 索引中唯一值的数量 Sub_part: NULL Packed: NULL Null: Index_type: BTREE - 索引算法类型 Comment: Index_comment: 1 row in set (0.01 sec)
SHOW PROCEDURE STATUS – 显示存储过程信息（需要先创建存储过程，详情请看“存储过程文章”）
mysql> SHOW PROCEDURE STATUS LIKE 'sp1'G *************************** 1. row *************************** Db: test Name: sp1 Type: PROCEDURE Definer: testuser@localhost Modified: 2004-08-03 15:29:37 Created: 2004-08-03 15:29:37 Security_type: DEFINER Comment: character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci
SHOW FUNCTION STATUS – 显示存储函数信息（需要先创建存储函数，详情看“存储函数文章”）
mysql> SHOW fUNCTION STATUS; Empty set (0.00 sec)
SHOW TRIGGERS – 显示触发器信息（需要先创建触发器，详情看“触发器部分”）
mysql> SHOW TRIGGERS LIKE 'acc%'G *************************** 1. row *************************** Trigger: ins_sum - 触发器名字 Event: INSERT - 触发事件 Table: account - 在那个表定义触发器 Statement: SET @sum = @sum + NEW.amount - 触发体 Timing: BEFORE - 是在触发之前激活触发事件，还是在触发之后激活触发事件 Created: NULL - sql_mode: NO_ENGINE_SUBSTITUTION - 触发器执行时的SQL模式 Definer: me@localhost - 谁创建了触发器 character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci
SHOW PROFILE and SHOW PROFILES – 显示执行语句的资源使用情况
mysql> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SET profiling = 1; mysql> CREATE TABLE T1 (id INT); mysql> SHOW PROFILES; +----------+------------+--------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------+ | 1 | 0.11698325 | CREATE TABLE T1 (id INT) | | 2 | 0.12255200 | CREATE TABLE T2 (id INT) | +----------+------------+--------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> SHOW PROFILE; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000080 | | checking permissions | 0.000015 | | Opening tables | 0.000129 | | creating table | 0.106344 | | After create | 0.000033 | | query end | 0.000066 | | closing tables | 0.000017 | | freeing items | 0.000241 | | cleaning up | 0.015628 | +----------------------+----------+ 9 rows in set, 1 warning (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 2; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000080 | ----------------------------------- mysql> SHOW PROFILE CPU FOR QUERY 2; +----------------------+----------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +----------------------+----------+----------+------------+ | starting | 0.000080 | 0.000000 | 0.000000 | ---------------------------------------------------------- TYPE: CPU | MEMORY | IPC | SOURCE | ALL | BLOCK IO | SWAPS
SHOW SLAVE HOSTS – 显示Master主机上已注册的复制主机列表（需要先做主从复制，详情看“主从复制部分”）
mysql> SHOW SLAVE HOSTS; +-----------+-----------+-------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+-----------+-------+-----------+--------------------------------------+ | 192168010 | iconnect2 | 3306 | 192168011 | 14cb6624-7f93-11e0-b2c0-c80aa9429562 | | 1921680101 | athena | 3306 | 192168011 | 07af4990-f41f-11df-a566-7ac56fdaf645 | +------------+-----------+------+-----------+--------------------------------------+
SHOW SLAVE STATUS – 显示Slave主机状态信息（需要先做主从复制，详情看“主从复制部分”）
mysql> SHOW SLAVE STATUSG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 13000 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 1307 Relay_Log_File: slave-relay-bin.000003 Relay_Log_Pos: 1508 Relay_Master_Log_File: master-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: ...........
SHOW GLOBAL | SESSION STATUS – 显示MySQL状态变量信息
mysql> SHOW GLOBAL STATUS; +-----------------------------------------------+--------------------------------------------------+ | Variable_name | Value | +-----------------------------------------------+--------------------------------------------------+ | Aborted_clients | 3 | | Aborted_connects | 1 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 25 | ................
SHOW GLOBAL | SESSION VARIABLES – 显示MySQL系统变量信息
mysql> SHOW SESSION VARIABLES LIKE '%INNODB%'; +------------------------------------------+------------------------+ | Variable_name | Value | +------------------------------------------+------------------------+ | ignore_builtin_innodb | OFF | | innodb_adaptive_flushing | ON | | innodb_adaptive_flushing_lwm | 10 | | innodb_adaptive_hash_index | ON | | innodb_adaptive_max_sleep_delay | 150000 | | innodb_additional_mem_pool_size | 8388608 | | innodb_api_bk_commit_interval | 5 | | innodb_api_disable_rowlock | OFF | | innodb_api_enable_binlog | OFF | | innodb_api_enable_mdl | OFF | | innodb_api_trx_level | 0 | | innodb_autoextend_increment | 64 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_dump_at_shutdown | ON | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_filename | ib_buffer_pool | ..........................
GOOD LUCK！
参考博客 http://www.ywnds.com/?p=2929
更多详情，请访问个人博客：https://www.wchonge.com