mysql常用show语句

1. show character set [like 'pattern']

显示所有可利用的字符集

mysql> show character set like 'gbk'
    -> ;
+---------+------------------------+-------------------+--------+
| Charset | Description            | Default collation | Maxlen |
+---------+------------------------+-------------------+--------+
| gbk     | GBK Simplified Chinese | gbk_chinese_ci    |      2 |
+---------+------------------------+-------------------+--------+
1 row in set (0.00 sec)

mysql> show character set like 'utf%';
+---------+---------------+-------------------+--------+
| Charset | Description   | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci   |      3 |
+---------+---------------+-------------------+--------+
1 row in set (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表结构

mysql> show columns from auth_group from django;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     |      | PRI | NULL    | auto_increment |
| name  | varchar(80) |      | UNI |         |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show columns from django.auth_group;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     |      | PRI | NULL    | auto_increment |
| name  | varchar(80) |      | UNI |         |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc django.auth_group;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     |      | PRI | NULL    | auto_increment |
| name  | varchar(80) |      | UNI |         |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

 

3.show create table tbl_name

显示创建表的sql语句。

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

                                                      |
+------------+------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------+
| auth_group | CREATE TABLE `auth_group` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(80) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------+
1 row in set (0.00 sec)

4。show create database db_name

显示创建db_name时候的sql语句

5。show databases [like 'pattern']

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

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

 

6。show tables [like 'pattern']

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

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                 |
+----------------------------+
12 rows in set (0.00 sec)

 

7.show grants [for user]

显示对user的授权语句

mysql> show grants;
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| Grants for root@localhost
                                                         |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*A4B
6157319038724E35608' WITH GRANT OPTION |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
1 row in set (0.00 sec)

 

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

      显示索引

mysql> show index from auth_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      |         |
+------------+------------+----------+--------------+-------------+-----------+-
------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

 

9。show open table

显示当前表缓冲区中的表

mysql> show open tables;
+----------+------------+--------+-------------+
| Database | Table      | In_use | Name_locked |
+----------+------------+--------+-------------+
| django   | auth_group |      0 |           0 |
+----------+------------+--------+-------------+
1 row in set (0.00 sec)

 

10。SHOW PRIVILEGES

显示mysql所支持的权限

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

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

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

显示变量

mysql> show variables like "%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/ |
+--------------------------+----------------------------------------------------
-----+
7 rows in set (0.00 sec)

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

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