mysql show语句_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中最后的语句生产成的错误，警告灯信息