1. show character set [like 'pattern']
显示所有可利用的字符集
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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表结构
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 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语句。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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上所有的数据库
1
2
3
4
5
6
7
8
9
10
|
mysql> show databases;
+
----------+
|
Database
|
+
----------+
| django |
| mysql |
| test |
| trac |
+
----------+
4
rows
in
set
(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 |
+
----------------------------+
12
rows
in
set
(0.00 sec)
|
7.show grants [for user]
显示对user的授权语句
1
2
3
4
5
6
7
8
9
10
11
12
|
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]
显示索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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
显示当前表缓冲区中的表
1
2
3
4
5
6
7
|
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)
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 row
in
set
(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 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中最后的语句生产成的错误,警告灯信息