mysql常用show语句

1. show character set [like 'pattern']

显示所有可利用的字符集

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> showcharactersetlike'gbk'     -> ; +---------+------------------------+-------------------+--------+ | Charset | Description            |Defaultcollation | Maxlen | +---------+------------------------+-------------------+--------+ | gbk     | GBK Simplified Chinese | gbk_chinese_ci    |      2 | +---------+------------------------+-------------------+--------+ 1 rowinset(0.00 sec)   mysql> showcharactersetlike'utf%'; +---------+---------------+-------------------+--------+ | Charset | Description   |Defaultcollation | Maxlen | +---------+---------------+-------------------+--------+ | utf8    | UTF-8 Unicode | utf8_general_ci   |      3 | +---------+---------------+-------------------+--------+ 1 rowinset(0.00 sec)

Maxlen列用来说明存储这样的一个字符需要几个字节。如上例，存储一个gbk字符需要2个字节，存储一个utf8字符需要3个字符。

 

2.SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE 'pattern']

用于显示所给表的列。这里感觉跟desc tbl_name的功能相同。

查询django数据库auth_group表结构

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

mysql> show columnsfromauth_groupfromdjango; +-------+-------------+------+-----+---------+----------------+ | Field | Type        |Null|Key|Default| Extra          | +-------+-------------+------+-----+---------+----------------+ | id    |int(11)     |      | PRI |NULL   | auto_increment | |name |varchar(80) |      | UNI |         |                | +-------+-------------+------+-----+---------+----------------+ 2rowsinset(0.00 sec)   mysql> show columnsfromdjango.auth_group; +-------+-------------+------+-----+---------+----------------+ | Field | Type        |Null|Key|Default| Extra          | +-------+-------------+------+-----+---------+----------------+ | id    |int(11)     |      | PRI |NULL   | auto_increment | |name |varchar(80) |      | UNI |         |                | +-------+-------------+------+-----+---------+----------------+ 2rowsinset(0.00 sec)   mysql>descdjango.auth_group; +-------+-------------+------+-----+---------+----------------+ | Field | Type        |Null|Key|Default| Extra          | +-------+-------------+------+-----+---------+----------------+ | id    |int(11)     |      | PRI |NULL   | auto_increment | |name |varchar(80) |      | UNI |         |                | +-------+-------------+------+-----+---------+----------------+ 2rowsinset(0.00 sec)

 

3.show create table tbl_name

显示创建表的sql语句。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

mysql> showcreatetableauth_group; +------------+------------------------------------------------------------------ -------------------------------------------------------------------------------- ------------------------------------------------------+ |Table     |CreateTable                                                         | +------------+------------------------------------------------------------------ -------------------------------------------------------------------------------- ------------------------------------------------------+ | auth_group |CREATETABLE`auth_group` (   `id`int(11)NOTNULLauto_increment,   `name`varchar(80)NOTNULLdefault'',   PRIMARYKEY (`id`),   UNIQUEKEY`name` (`name`) ) ENGINE=InnoDBDEFAULTCHARSET=utf8 | +------------+------------------------------------------------------------------ -------------------------------------------------------------------------------- ------------------------------------------------------+ 1 rowinset(0.00 sec)

4。show create database db_name

显示创建db_name时候的sql语句

5。show databases [like 'pattern']

显示当前mysql上所有的数据库

?

1 2 3 4 5 6 7 8 9 10

mysql> show databases; +----------+ |Database| +----------+ | django   | | mysql    | | test     | | trac     | +----------+ 4rowsinset(0.00 sec)

 

6。show tables [like 'pattern']

显示当前数据库上的所有表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

mysql> show tables; +----------------------------+ | Tables_in_django           | +----------------------------+ | auth_group                 | | auth_group_permissions     | | auth_message               | | auth_permission            | | auth_user                  | | auth_user_groups           | | auth_user_user_permissions | | django_content_type        | | django_session             | | django_site                | | polls_choice               | | polls_poll                 | +----------------------------+ 12rowsinset(0.00 sec)

 

7.show grants [for user]

显示对user的授权语句

?

1 2 3 4 5 6 7 8 9 10 11 12

mysql> show grants; +------------------------------------------------------------------------------- ---------------------------------------------------------+ | Grantsforroot@localhost                                                          | +------------------------------------------------------------------------------- ---------------------------------------------------------+ |GRANTALLPRIVILEGESON*.*TO'root'@'localhost'IDENTIFIEDBYPASSWORD'*A4B 6157319038724E35608'WITHGRANTOPTION| +------------------------------------------------------------------------------- ---------------------------------------------------------+ 1 rowinset(0.00 sec)

 

    8.SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name]

      显示索引

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> showindexfromauth_group; +------------+------------+----------+--------------+-------------+-----------+- ------------+----------+--------+------+------------+---------+ |Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null| Index_type | Comment | +------------+------------+----------+--------------+-------------+-----------+- ------------+----------+--------+------+------------+---------+ | auth_group |          0 |PRIMARY |            1 | id          | A         |           0 |    NULL|NULL  |      | BTREE      |         | | auth_group |          0 |name    |            1 |name       | A         |           0 |    NULL|NULL  |      | BTREE      |         | +------------+------------+----------+--------------+-------------+-----------+- ------------+----------+--------+------+------------+---------+ 2rowsinset(0.00 sec)

 

9。show open table

显示当前表缓冲区中的表

?

1 2 3 4 5 6 7

mysql> showopentables; +----------+------------+--------+-------------+ |Database|Table     | In_use | Name_locked | +----------+------------+--------+-------------+ | django   | auth_group |      0 |           0 | +----------+------------+--------+-------------+ 1 rowinset(0.00 sec)

 

10。SHOW PRIVILEGES

显示mysql所支持的权限

11。SHOW [FULL] PROCESSLIST
显示正在使用的线程(thread)

?

1 2 3 4 5 6 7 8 9 10 11 12

mysql> show processlist; +----+------+----------------+--------+---------+------+-------+---------------- --+ | Id |User| Host           | db     | Command |Time| State | Info   | +----+------+----------------+--------+---------+------+-------+---------------- --+ |  7 | root | localhost:1158 | django | Query   |    0 |NULL | show processlis t | +----+------+----------------+--------+---------+------+-------+---------------- --+ 1 rowinset(0.00 sec)

12.SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']

显示变量

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

mysql> show variableslike"%char%"; +--------------------------+---------------------------------------------------- -----+ | Variable_name            | Value      | +--------------------------+---------------------------------------------------- -----+ | character_set_client     | latin1      | | character_set_connection | latin1      | | character_set_database   | utf8      | | character_set_results    | latin1      | | character_set_server     | utf8      | | character_set_system     | utf8      | | character_sets_dir       | D:\Program Files\MySQL\MySQL Server 4.1\share\chars ets/ | +--------------------------+---------------------------------------------------- -----+ 7rowsinset(0.00 sec)

   13。SHOW WARNINGS [LIMIT [offset,] row_count]
         SHOW COUNT(*) WARNINGS

显示当前session中最后的语句生产成的错误，警告灯信息