注意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