Mysql show命令

1、  show databases;列表当前mysql服务器上当前登录用户所有的数据库列表
2、  show tables;列表当前选中数据库下的所有表，记住使用此命令之前必须要选中一个数据库
    mysql> use phpchengdu_test;
    Database changed
    mysql> show tables;
    +---------------------------+
    | Tables_in_phpchengdu_test |
    +---------------------------+
    | article                   |
    | comment                   |
    | newarticle                |
    | newarticle2               |
    | tt                        |
    | user                      |
    +---------------------------+
    6 rows in set (0.00 sec)

3、describe tablename;查询指定table的结构，命令可以简写为desc tablename;

      mysql> desc article;
    +---------+--------------+------+-----+---------+----------------+
    | Field   | Type         | Null | Key | Default | Extra          |
    +---------+--------------+------+-----+---------+----------------+
    | id      | int(8)       | NO   | PRI | NULL    | auto_increment |
    | title   | varchar(255) | NO   | MUL | NULL    |                |
    | content | text         | NO   |     | NULL    |                |
    | time    | datetime     | NO   |     | NULL    |                |
    | author  | varchar(60)  | NO   |     | NULL    |                |
    | tag     | varchar(255) | YES  |     | NULL    |                |
    +---------+--------------+------+-----+---------+----------------+
    6 rows in set (0.07 sec)

          mysql> describe article;
    +---------+--------------+------+-----+---------+----------------+
    | Field   | Type         | Null | Key | Default | Extra          |
    +---------+--------------+------+-----+---------+----------------+
    | id      | int(8)       | NO   | PRI | NULL    | auto_increment |
    | title   | varchar(255) | NO   | MUL | NULL    |                |
    | content | text         | NO   |     | NULL    |                |
    | time    | datetime     | NO   |     | NULL    |                |
    | author  | varchar(60)  | NO   |     | NULL    |                |
    | tag     | varchar(255) | YES  |     | NULL    |                |
    +---------+--------------+------+-----+---------+----------------+
    6 rows in set (0.01 sec)

4、show create table tablename;查看指定表的原始创建语句

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



              |
    +---------+---------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------+
    | article | CREATE TABLE `article` (
      `id` int(8) NOT NULL auto_increment,
      `title` varchar(255) NOT NULL,
      `content` text NOT NULL,
      `time` datetime NOT NULL,
      `author` varchar(60) NOT NULL,
      `tag` varchar(255) default NULL,
      PRIMARY KEY  (`id`),
      KEY `title` (`title`)
    ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 |
    +---------+---------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------+
    1 row in set (0.01 sec)


5、show index from tablename;查看表的索引
    mysql> show index from article;
    +---------+------------+----------+--------------+-------------+-----------+----
    ---------+----------+--------+------+------------+---------+
    | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Car
    dinality | Sub_part | Packed | Null | Index_type | Comment |
    +---------+------------+----------+--------------+-------------+-----------+----
    ---------+----------+--------+------+------------+---------+
    | article |          0 | PRIMARY  |            1 | id          | A         |
          10 |     NULL | NULL   |      | BTREE      |         |
    | article |          1 | title    |            1 | title       | A         |
          10 |     NULL | NULL   |      | BTREE      |         |
    +---------+------------+----------+--------------+-------------+-----------+----
    ---------+----------+--------+------+------------+---------+
    2 rows in set (0.00 sec)
6、show engines;查看可用的表类型
    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
    es      |
    | InnoDB     | YES     | Supports transactions, row-level locking, and foreign k
    eys     |
    | BerkeleyDB | NO      | Supports transactions and page-level locking
        |
    | BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disa
    ppears) |
    | EXAMPLE    | NO      | Example storage engine
        |
    | ARCHIVE    | YES     | Archive storage engine
        |
    | CSV        | NO      | CSV storage engine
        |
    | ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables
        |
    | FEDERATED  | YES     | Federated MySQL storage engine
        |
    | MRG_MYISAM | YES     | Collection of identical MyISAM tables
        |
    | ISAM       | NO      | Obsolete storage engine
        |
    +------------+---------+--------------------------------------------------------
    --------+
    12 rows in set (0.00 sec)
7、show processlist;显示连接到当前数据库服务器的活动连接的相关信息
    mysql> show processlist;
    +----+------+-----------------+-----------------+---------+------+-------+------
    ------------+
    | Id | User | Host            | db              | Command | Time | State | Info
            |
    +----+------+-----------------+-----------------+---------+------+-------+------
    ------------+
    | 14 | root | localhost:53898 | phpchengdu_test | Query   |    0 | NULL  | show
    processlist |
    +----+------+-----------------+-----------------+---------+------+-------+------
    ------------+
    1 row in set (0.01 sec)
8、show errors;显示当前数据库服务器上发生的错误信息
9、show warnings;显示当前数据库服务器上发生的警告信息
    mysql> show errors;
    Empty set (0.00 sec)
    mysql> show warnings;
    +-------+------+----------------------------------------------------------------
    --------------------------------------------------------------------------------
    -----------------+
    | Level | Code | Message

             |
    +-------+------+----------------------------------------------------------------
    --------------------------------------------------------------------------------
    -----------------+
    | Error | 1064 | You have an error in your SQL syntax; check the manual that cor
    responds to your MySQL server version for the right syntax to use near 'shoe war
    nings' at line 1 |
    +-------+------+----------------------------------------------------------------
    --------------------------------------------------------------------------------
    -----------------+
    1 row in set (0.00 sec)
10、show status;显示当前服务器上的状态信息，比如服务器启动时间、当前正在执行的查询数量、当前的并发连接数等，此处可以通过like 关键字只匹配某些执行信息
    mysql> show status like 't%';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | Table_locks_immediate | 31    |
    | Table_locks_waited    | 0     |
    | Tc_log_max_pages_used | 0     |
    | Tc_log_page_size      | 0     |
    | Tc_log_page_waits     | 0     |
    | Threads_cached        | 0     |
    | Threads_connected     | 1     |
    | Threads_created       | 14    |
    | Threads_running       | 1     |
    +-----------------------+-------+
    9 rows in set (0.00 sec)
11、show table status;显示选中数据库中表的详细信息，仍可使用like来匹配某些表
    mysql> show table status like 'article%';
    +---------+--------+---------+------------+------+----------------+-------------
    +-----------------+--------------+-----------+----------------+-----------------
    ----+---------------------+---------------------+-----------------+----------+--
    --------------+---------+
    | 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 | C
    reate_options | Comment |
    +---------+--------+---------+------------+------+----------------+-------------
    +-----------------+--------------+-----------+----------------+-----------------
    ----+---------------------+---------------------+-----------------+----------+--
    --------------+---------+
    | article | MyISAM |      10 | Dynamic    |   10 |             55 |         556
    | 281474976710655 |         6144 |         0 |             21 | 2009-11-28 17:20
    :48 | 2009-11-28 17:20:50 | 2009-11-28 17:20:48 | utf8_general_ci |     NULL |
              |         |
    +---------+--------+---------+------------+------+----------------+-------------
    +-----------------+--------------+-----------+----------------+-----------------
    ----+---------------------+---------------------+-----------------+----------+--
    --------------+---------+
    1 row in set (0.00 sec)
12、show character set;显示当前服务器上可用的编码列表