MYSQL SHOW 用法
2024-10-06 18:04:47
1、SHOW CHARACTER SET 显示所有可用的字符集
mysql> SHOW CHARACTER SET LIKE 'utf8';
+---------+---------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
+---------+---------------+-------------------+--------+
1 row in set (0.00 sec)
2、SHOW COLLATION 显示所有的校对规则
mysql> SHOW CHARACTER SET LIKE 'utf8';
+---------+---------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
+---------+---------------+-------------------+--------+
1 row in set (0.00 sec)
3、SHOW COLUMNS 显示在一个给定表中的各列的信息（等同于DESC）
mysql> SHOW COLUMNS FROM zjf.a1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.15 sec)
4、SHOW CREATE TABLE 显示用于创建给定表的CREATE TABLE语句
mysql> SHOW CREATE TABLE zjf.a1;
+-------+----------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------+
| a1 | CREATE TABLE `a1` (
`id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
5、SHOW OPEN TABLES 显示数据库中正在打开的表
mysql> SHOW OPEN TABLES FROM zjf;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| zjf | a1 | 0 | 0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)
6、SHOW TABLES 显示数据库的所有表
mysql> USE zjf;
Database changed
mysql> SHOW TABLES;
+---------------+
| Tables_in_zjf |
+---------------+
| a1 |
+---------------+
1 row in set (0.00 sec)
7、SHOW TABLE STATUS 性质与SHOW TABLE类似，不过，可以提供每个表的大量信息
mysql> SHOW TABLE STATUS FROM zjf WHERE Name='a1';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| a1 | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2016-02-17 11:24:19 | NULL | NULL | utf8_general_ci | NULL | | |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
8、SHOW DATABASES 在MySQL服务器主机上列举数据库
mysql> SHOW DATABASES LIKE 'zjf';
+----------------+
| Database (zjf) |
+----------------+
| zjf |
+----------------+
1 row in set (0.00 sec)
9、SHOW ENGINE 显示存储引擎的日志或状态信息
mysql> SHOW ENGINE innodb status \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2016-02-17 13:28:24 0x7f9eeea16700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 24 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4 srv_active, 0 srv_shutdown, 7149 srv_idle
srv_master_thread log flush and writes: 7153
...
10、SHOW ENGINES 显示存储引擎的状态信息
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
11、SHOW ERRORS 本语句与SHOW WARNINGS接近，不过该语句只显示错误，不同时显示错误、警告和注意。
mysql> SHOW ERRORS;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE 'DEFAULT'' at line 1 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) mysql> SHOW COUNT(*) ERRORS;
+-----------------------+
| @@session.error_count |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
12、SHOW WARNINGS显示由上一个生成消息的语句导致的错误、警告和注意消息。
mysql> SHOW WARNINGS;
Empty set (0.08 sec) mysql> SHOW COUNT(*) WARNINGS;
+-------------------------+
| @@session.warning_count |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.00 sec)
13、SHOW VARIABLES显示了部门MySQL系统变量的值
mysql> SHOW VARIABLES LIKE 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 5.7.11 |
+---------------+--------+
1 row in set (0.00 sec)
14、 SHOW GRANTS 用户账户复制权限时必须发布的GRANT语句
mysql> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
15、SHOW INDEX 返回表索引信息
mysql> SHOW INDEX FROM mysql.user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user | 0 | PRIMARY | 1 | Host | A | NULL | NULL | NULL | | BTREE | | |
| user | 0 | PRIMARY | 2 | User | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.04 sec)
16、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 |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
...
17、SHOW PROCESSLIST显示哪些线程正在运行（高端）
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
18、SHOW STATUS提供服务器状态信息
mysql> SHOW STATUS LIKE 'Binlog%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
+----------------------------+-------+
4 rows in set (0.05 sec)
19、SHOW TRIGGERS 列出了目前被MySQL服务器定义的触发程序
mysql> SHOW TRIGGERS LIKE 'acc%';
+---------+--------+---------+-------------------------------+--------+---------+
| Trigger | Event | Table | Statement | Timing | Created |
+---------+--------+---------+-------------------------------+--------+---------+
| ins_sum | INSERT | account | SET @sum = @sum + NEW.amount | BEFORE | NULL |
+---------+--------+---------+-------------------------------+--------+---------+
最新文章
- 关于wamp5中（apache）设置虚拟主机
- java 文件按行读取
- yii Html中的a标签使用
- jQuery中的ajax服务端返回方式详细说明
- Windows Phone 版 Cocos2d-x 程序的结构
- ThinkPHP框架部署
- 去除浏览器下jquey easyui datagrid、combotree 缓存问题
- 容器 MAP
- MySQL 优化方案
- poj1995-快速幂取模
- Developing your first FNC custom control
- 04_关于元数据，ResultSetMetaData对象以及API方法介绍
- bower 基础认识
- 1.[Andriod]之Andriod布局 VS WinPhone布局
- Item 13: 比起iterator优先使用const_iterator
- 元数据管理器中存在错误。 实例化来自文件“\\?\C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data\Tfs_Analysis.0.db\vDimTestCaseOverlay.874.dim.xml”的元数据对象时出错。
- 题解 P5239 【回忆京都】
- Qt5.3.2_Oracle驱动
- json_decode($str,true)的结果为null
- c++ vs 快捷方式