show databases;
show tables from db_name;

show columns from table_name from db_name;
show index from talbe_name [from db_name];

show status;
show variables;

show [full] processlist;
show table status [from db_name];

show grants for user;

除了status,processlistå’Œgrants外，其它的都可以带有like wild选项åQŒå®ƒå¯ä»¥ä½¿ç”¨SQLçš?%'å’?_'字符åQ?br />
show databases like '%t';

ž®†ä¼š(x¨¬)列出所有数据库名字末尾ä¸?t'字符的数据库

当然了，在这些sql中，你也可以用db_name.table_name来代æ›?table_name from db_name˜q™æ ·å†™ä¼š(x¨¬)更简便些!

如果一个用æˆäh²¡æœ‰ä¸€ä¸ªè¡¨çš„ä“Q何权限，表将不在SHOW TABLESæˆ?code>mysqlshow db_name中的输出中显½C?br />

大家可能˜q˜è®°å¾—describe table_name åQŒå®ƒå®žçŽ°çš„是与show columns from db_name.table_name一æ ïL(f¨¥ng)š„效果

show statusž®†å¯ä»¥ç”¨mysqlshow --status 来得到同æ ïL(f¨¥ng)š„效果

�/strong>	含义
Name	表名
Type	表的¾cÕdž‹ (ISAMåQŒMyISAM或HEAP)
Row_format	行存储格å¼?(固定, 动æ€? 或压¾~©ï¼‰
Rows	行数�/td>
Avg_row_length	òq›_‡è¡Œé•¿åº?/td>
Data_length	数据文äšgçš„é•¿åº?/td>
Max_data_length	数据文äšg的最大长åº?/td>
Index_length	索引文äšgçš„é•¿åº?/td>
Data_free	已分配但未ä‹É用了字节æ•?/td>
Auto_increment	下一ä¸?autoincrement(自动åŠ?åQ‰å€?/td>
Create_time	表被创造的旉™—´
Update_time	数据文äšg最后更新的旉™—´
Check_time	最后对表运行一个检查的旉™—´
Create_options	ä¸?code>CREATE TABLE一起ä‹É用的额外选项
Comment	当创造表æ—Óž¼Œä½¿ç”¨çš„注é‡?(或äØ“(f¨´)什么MySQL不能存取表信息的一些信æ?ã€?

SHOW FIELDSæ˜?code>SHOW COLUMNS一个同义词åQ?code>SHOW KEYSæ˜?code>SHOW INDEX一个同义词。你也可以用mysqlshow db_name tbl_nameæˆ?code>mysqlshow -k db_name tbl_name 列出一张表的列或烦引ã€?/p>

SHOW INDEX以非常相ä¼égºŽODBCçš?code>SQLStatistics调用的格式返回烦引信息。下面的列被˜q”回åQ?

SHOW STATUS提供服务器的状态信æ?è±?code>mysqladmin extended-status一æ ?。输出类ä¼égºŽä¸‹é¢çš„显½Cºï¼Œž®½ç®¡æ ¼å¼å’Œæ•°å­—可以有点不同：(x¨¬)

+--------------------------+--------+

| Variable_name            | Value  |

+--------------------------+--------+

| Aborted_clients          | 0      |

| Aborted_connects         | 0      |

| Connections              | 17     |

| Created_tmp_tables       | 0      |

| Delayed_insert_threads   | 0      |

| Delayed_writes           | 0      |

| Delayed_errors           | 0      |

| Flush_commands           | 2      |

| Handler_delete           | 2      |

| Handler_read_first       | 0      |

| Handler_read_key         | 1      |

| Handler_read_next        | 0      |

| Handler_read_rnd         | 35     |

| Handler_update           | 0      |

| Handler_write            | 2      |

| Key_blocks_used          | 0      |

| Key_read_requests        | 0      |

| Key_reads                | 0      |

| Key_write_requests       | 0      |

| Key_writes               | 0      |

| Max_used_connections     | 1      |

| Not_flushed_key_blocks   | 0      |

| Not_flushed_delayed_rows | 0      |

| Open_tables              | 1      |

| Open_files               | 2      |

| Open_streams             | 0      |

| Opened_tables            | 11     |

| Questions                | 14     |

| Slow_queries             | 0      |

| Threads_connected        | 1      |

| Threads_running          | 1      |

| Uptime                   | 149111 |

+--------------------------+--------+

上面列出的状态变量有下列含义åQ?

关于上面的一些注释：(x¨¬)

• 如果Opened_tables太大åQŒé‚£ä¹ˆä½ çš?code>table_cache变量可能太小ã€?
• 如果key_reads太大åQŒé‚£ä¹ˆä½ çš?code>key_cache可能太小。缓存命中率可以ç”?code>key_reads/key_read_requests计算ã€?
• 如果Handler_read_rnd太大åQŒé‚£ä¹ˆä½ å¾ˆå¯èƒ½æœ‰å¤§é‡çš„查询需要MySQL扫描整个表或你有没正¼‹®ä‹É用键值的联结(join)ã€?

SHOW VARIABLES昄¡¤ºå‡ÞZ¸€äº?strong>MySQL¾pȝ»Ÿå˜é‡çš„å€û|¼Œä½ ä¹Ÿèƒ½ä‹Éç”?code>mysqladmin variables命ä×o(h¨´)得到˜q™ä¸ªä¿¡æ¯ã€‚如果缺省å€ég¸åˆé€‚，你能åœ?code>mysqld启动时ä‹É用命令行选项来设¾|®è¿™äº›å˜é‡çš„大多数。输出类ä¼égºŽä¸‹é¢çš„显½Cºï¼Œž®½ç®¡æ ¼å¼å’Œæ•°å­—可以有点不同：(x¨¬)

+------------------------+--------------------------+

| Variable_name          | Value                    |

+------------------------+--------------------------+

| back_log               | 5                        |

| connect_timeout        | 5                        |

| basedir                | /my/monty/               |

| datadir                | /my/monty/data/          |

| delayed_insert_limit   | 100                      |

| delayed_insert_timeout | 300                      |

| delayed_queue_size     | 1000                     |

| join_buffer_size       | 131072                   |

| flush_time             | 0                        |

| interactive_timeout    | 28800                    |

| key_buffer_size        | 1048540                  |

| language               | /my/monty/share/english/ |

| log                    | OFF                      |

| log_update             | OFF                      |

| long_query_time        | 10                       |

| low_priority_updates   | OFF                      |

| max_allowed_packet     | 1048576                  |

| max_connections        | 100                      |

| max_connect_errors     | 10                       |

| max_delayed_threads    | 20                       |

| max_heap_table_size    | 16777216                 |

| max_join_size          | 4294967295               |

| max_sort_length        | 1024                     |

| max_tmp_tables         | 32                       |

| net_buffer_length      | 16384                    |

| port                   | 3306                     |

| protocol-version       | 10                       |

| record_buffer          | 131072                   |

| skip_locking           | ON                       |

| socket                 | /tmp/mysql.sock          |

| sort_buffer            | 2097116                  |

| table_cache            | 64                       |

| thread_stack           | 131072                   |

| tmp_table_size         | 1048576                  |

| tmpdir                 | /machine/tmp/            |

| version                | 3.23.0-alpha-debug       |

| wait_timeout           | 28800                    |

+------------------------+--------------------------+
SHOW PROCESSLIST昄¡¤ºå“ªä¸ª¾U¿ç¨‹æ­£åœ¨˜qè¡ŒåQŒä½ ä¹Ÿèƒ½ä½¿ç”¨mysqladmin processlist命ä×o(h¨´)得到˜q™ä¸ªä¿¡æ¯ã€?br />
如果你有process权限åQ?你能看见所有的¾U¿ç¨‹åQŒå¦åˆ™ï¼Œä½ ä»…能看见你自己的线½E‹ã€?br />
è§?.20 KILL句法。如果你不ä‹Éç”?code>FULL选项åQŒé‚£ä¹ˆæ¯ä¸ªæŸ¥è¯¢åªæœ‰å¤´100字符被显½Cºå‡ºæ¥ã€?
SHOW GRANTS FOR user列出对一个用户必™åÕd‘å‡ÞZ»¥é‡å¤æŽˆæƒçš„授权命令ã€?
mysql> SHOW GRANTS FOR root@localhost;

+---------------------------------------------------------------------+

| Grants for root@localhost                                           |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root''localhost' WITH GRANT OPTION  |

+---------------------------------------------------------------------+