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 '*A4B6157319038724E35608' 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 processlist |+----+------+----------------+--------+---------+------+-------+------------------+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\charsets/ |+--------------------------+---------------------------------------------------------+7 rows in set (0.00 sec)

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

顯示當前session中最後的語句生產成的錯誤，警告燈資訊