MySQL show命令

注意show命令后面的一般为复数，最后要加“；”。

1. show tables或show tables from database_name; // 显示当前数据库中所有表的名称。

2. show databases; // 显示mysql中所有数据库的名称

3. show columns from table_name from database_name; 或show columns from database_name.table_name; //此和desc类似， 显示表中列名称

4. show grants for user_name; //? 显示一个用户的权限，显示结果类似于grant 命令

5. show index from table_name; // 显示表的索引

6. show status; // 显示一些系统特定资源的信息，例如，正在运行的线程数量 ，此命令主要涉及到MySQL性能调优；

7. show variables; // 显示系统变量

8. show processlist; // 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。

9. show table status; // 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间

10. show privileges; // 显示服务器所支持的不同权限

11.show create database database_name; // 显示create database 语句是否能够创建指定的数据库 ,包含默认的字符集。

mysql> show create database test;

+----------+-----------------------------------------------------------------+

| Database | Create Database |

+----------+-----------------------------------------------------------------+

| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+----------+-----------------------------------------------------------------+

1 row in set (0.00 sec)

12 show create table table_name; // 显示createtables 语句创建指定的表

mysql> show create table a;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------+
| Table | Create Table|

+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------+
| a | CREATE TABLE `a` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(10) NOT NULL,
`day` mediumint(9) default NULL,
PRIMARY KEY (`id`),
KEY `namekey` (`name`),
KEY `daykey` (`day`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------+
1 row in set (0.00 sec)

13 show engines; // 显示安装以后可用的存储引擎和默认引擎

mysql> show engines;
ERROR 1064 (42000): 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 'engie
s' at line 1? （My god，sorry，输错了）；
mysql> show engines;
+------------+---------+--------------------------------------------------------
| Engine | Support | Comment
+------------+---------+--------------------------------------------------------
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance
| MEMORY | YES | Hash based, stored in memory, useful for temporary tabl
| InnoDB | YES | Supports transactions, row-level locking, and foreign k
| BerkeleyDB | YES | Supports transactions and page-level locking
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disa
| EXAMPLE | NO | Example storage engine
| ARCHIVE | NO | Archive storage engine
| CSV | NO | CSV storage engine
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables
| FEDERATED | NO | Federated MySQL storage engine
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| ISAM | NO | Obsolete storage engine
+------------+---------+--------------------------------------------------------

12 、rows in set (0.00 sec)

14 、show innodb status; // 显示innoDB