Home Database Mysql Tutorial MySQL中show命令方法得到表列及整个库的详细信息(精品珍藏)

MySQL中show命令方法得到表列及整个库的详细信息(精品珍藏)

Jun 07, 2016 pm 06:02 PM
mysql

MySQL中show 句法得到表列及整个库的详细信息,方便查看数据库的详细信息。

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选项,它可以使用SQL的'%'和'_'字符;

show databases like '%t';

将会列出所有数据库名字末尾为't'字符的数据库


当然了,在这些sql中,你也可以用db_name.table_name来代替 table_name from db_name这样写会更简便些!

如果一个用户没有一个表的任何权限,表将不在SHOW TABLESmysqlshow db_name中的输出中显示

大家可能还记得describe table_name ,它实现的是与show columns from db_name.table_name一样的效果

show status将可以用mysqlshow --status 来得到同样的效果


SHOW FIELDSSHOW COLUMNS一个同义词,SHOW KEYSSHOW INDEX一个同义词。你也可以用mysqlshow db_name tbl_namemysqlshow -k db_name tbl_name 列出一张表的列或索引。

SHOW INDEX以非常相似于ODBC的SQLStatistics调用的格式返回索引信息。下面的列被返回:

SHOW STATUS提供服务器的状态信息(象mysqladmin extended-status一样)。输出类似于下面的显示,尽管格式和数字可以有点不同:

+--------------------------+--------+<br>| Variable_name      | Value |<br>+--------------------------+--------+<br>| Aborted_clients     | 0   |<br>| Aborted_connects     | 0   |<br>| Connections       | 17   |<br>| Created_tmp_tables    | 0   |<br>| Delayed_insert_threads  | 0   |<br>| Delayed_writes      | 0   |<br>| Delayed_errors      | 0   |<br>| Flush_commands      | 2   |<br>| Handler_delete      | 2   |<br>| Handler_read_first    | 0   |<br>| Handler_read_key     | 1   |<br>| Handler_read_next    | 0   |<br>| Handler_read_rnd     | 35   |<br>| Handler_update      | 0   |<br>| Handler_write      | 2   |<br>| Key_blocks_used     | 0   |<br>| Key_read_requests    | 0   |<br>| Key_reads        | 0   |<br>| Key_write_requests    | 0   |<br>| Key_writes        | 0   |<br>| Max_used_connections   | 1   |<br>| Not_flushed_key_blocks  | 0   |<br>| Not_flushed_delayed_rows | 0   |<br>| Open_tables       | 1   |<br>| Open_files        | 2   |<br>| Open_streams       | 0   |<br>| Opened_tables      | 11   |<br>| Questions        | 14   |<br>| Slow_queries       | 0   |<br>| Threads_connected    | 1   |<br>| Threads_running     | 1   |<br>| Uptime          | 149111 |<br>+--------------------------+--------+
Copy after login

上面列出的状态变量有下列含义:

关于上面的一些注释:

  • 如果Opened_tables太大,那么你的table_cache变量可能太小。
  • 如果key_reads太大,那么你的key_cache可能太小。缓存命中率可以用key_reads/key_read_requests计算。
  • 如果Handler_read_rnd太大,那么你很可能有大量的查询需要MySQL扫描整个表或你有没正确使用键值的联结(join)。

SHOW VARIABLES显示出一些MySQL系统变量的值,你也能使用mysqladmin variables命令得到这个信息。如果缺省值不合适,你能在mysqld启动时使用命令行选项来设置这些变量的大多数。输出类似于下面的显示,尽管格式和数字可以有点不同:

+------------------------+--------------------------+<br>| Variable_name     | Value          |<br>+------------------------+--------------------------+<br>| back_log        | 5            |<br>| connect_timeout    | 5            |<br>| basedir        | /my/monty/        |<br>| datadir        | /my/monty/data/     |<br>| delayed_insert_limit  | 100           |<br>| delayed_insert_timeout | 300           |<br>| delayed_queue_size   | 1000           |<br>| join_buffer_size    | 131072          |<br>| flush_time       | 0            |<br>| interactive_timeout  | 28800          |<br>| key_buffer_size    | 1048540         |<br>| language        | /my/monty/share/english/ |<br>| log          | OFF           |<br>| log_update       | OFF           |<br>| long_query_time    | 10            |<br>| low_priority_updates  | OFF           |<br>| max_allowed_packet   | 1048576         |<br>| max_connections    | 100           |<br>| max_connect_errors   | 10            |<br>| max_delayed_threads  | 20            |<br>| max_heap_table_size  | 16777216         |<br>| max_join_size     | 4294967295        |<br>| max_sort_length    | 1024           |<br>| max_tmp_tables     | 32            |<br>| net_buffer_length   | 16384          |<br>| port          | 3306           |<br>| protocol-version    | 10            |<br>| record_buffer     | 131072          |<br>| skip_locking      | ON            |<br>| socket         | /tmp/mysql.sock     |<br>| sort_buffer      | 2097116         |<br>| table_cache      | 64            |<br>| thread_stack      | 131072          |<br>| tmp_table_size     | 1048576         |<br>| tmpdir         | /machine/tmp/      |<br>| version        | 3.23.0-alpha-debug    |<br>| wait_timeout      | 28800          |<br>+------------------------+--------------------------+<p><code>SHOW PROCESSLIST</code>显示哪个线程正在运行,你也能使用<code>mysqladmin processlist</code>命令得到这个信息。<br>如果你有<strong>process</strong>权限, 你能看见所有的线程,否则,你仅能看见你自己的线程。<br> 见7.20<code> KILL</code>句法。如果你不使用<code>FULL</code>选项,那么每个查询只有头100字符被显示出来。 </p><p><code>SHOW GRANTS FOR user</code>列出对一个用户必须发出以重复授权的授权命令。 </p><pre class="brush:php;toolbar:false">mysql&gt; SHOW GRANTS FOR root@localhost;<br>+---------------------------------------------------------------------+<br>| Grants for root@localhost                      |<br>+---------------------------------------------------------------------+<br>| GRANT ALL PRIVILEGES ON *.* TO 'root''localhost' WITH GRANT OPTION |<br>+---------------------------------------------------------------------+
Copy after login
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

PHP's big data structure processing skills

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

How to optimize MySQL query performance in PHP?

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

How to use MySQL backup and restore in PHP?

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into a MySQL table using PHP?

What are the application scenarios of Java enumeration types in databases? What are the application scenarios of Java enumeration types in databases? May 05, 2024 am 09:06 AM

What are the application scenarios of Java enumeration types in databases?

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

How to fix mysql_native_password not loaded errors on MySQL 8.4

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

How to use MySQL stored procedures in PHP?

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

How to create a MySQL table using PHP?

See all articles