MySQL数据库中的SHOW

mysql> show create table t;
+-------+-----------------------------------------------------------| Table | Create Table
+-------+--------------------------------------------------------------------------------------------------------------------
| t     | CREATE TABLE `t` (
  `i` int(10) unsigned NOT NULL,
  `c` char(10) DEFAULT NULL,
  `d` date DEFAULT '1999-12-31'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------1 row in set (1.09 sec)

mysql> show columns from t;
+-------+------------------+------+-----+------------+-------+
| Field | Type             | Null | Key | Default    | Extra |
+-------+------------------+------+-----+------------+-------+
| i     | int(10) unsigned | NO   |     | NULL       |       |
| c     | char(10)         | YES  |     | NULL       |       |
| d     | date             | YES  |     | 1999-12-31 |       |
+-------+------------------+------+-----+------------+-------+
3 rows in set (0.03 sec)

mysql> show full columns from t;
+-------+------------------+-----------------+------+-----+------------+-------+---------------------------------+---------+
| Field | Type             | Collation       | Null | Key | Default    | Extra | Privileges                      | Comment |
+-------+------------------+-----------------+------+-----+------------+-------+---------------------------------+---------+
| i     | int(10) unsigned | NULL            | NO   |     | NULL       |       | select,insert,update,references |         |
| c     | char(10)         | utf8_general_ci | YES  |     | NULL       |       | select,insert,update,references |         |
| d     | date             | NULL            | YES  |     | 1999-12-31 |       | select,insert,update,references |         |
+-------+------------------+-----------------+------+-----+------------+-------+---------------------------------+---------+
3 rows in set (0.00 sec)

mysql> show index from t;
Empty set (0.00 sec)


/*
使用INFORMATION_SCHEMA获取元数据
*/
select table_name from information_schema.tables where table_schema='information_schema' order by table_name;

select column_name from information_schema.columns
where table_schema='information_schema'
and table_name='VIEWS';

/*
查询给定数据库中,表使用的存储引擎
*/
select table_name,engine from information_schema.tables
where table_schema='world';
/*
查询所有包含SET数据类型的列,所在的表
*/
select table_schema,table_name,column_name
from information_schema.columns
where data_type='set';
/*
显示每个字符集默认的排序方式
*/
select character_set_name,collation_name
from information_schema.collations
where is_default='yes';

/*
查询每个数据库中表的数量
*/
select table_schema,count(*)
from information_schema.tables
group by table_schema;
/*information_schema是只读的，它所包含的表都不能够使用insert，update,delete语句进行修改。
如果你修改了，将会发生一个错误
*/

/*使用SHOW,DESCRIBE获取元数据*/
show databases;
show tables;
show tables from db_name;
show columns from tb_name;或show full columns from tb_name;或show columns from tb_name from db_name;
show keys from tb_name;//显示tb_name表上的索引信息
show databases,show tables,show columns支持like正则
如show databases like 'm%';

show 也支持where从句,如：show columns from country where 'default' is null;

show collation;等价于如下SQL:
SELECT * FROM INFORMATION_SCHEMA.COLLATIONS;
show character set;等价于如下SQL:
SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;

mysqlshow是一种客户端的程序，用其也可以显示数据库，表或表的索引，表的列的信息
语法格式：
mysqlshow [options] [db_name[table_name [column_name]]]
mysqlshow完成的动作依赖于你提供的non-option参数的数量
1.如果没有提供参数，mysqlshow显示的结果类似于show databases;
2.如果提供一个参数，mysqlshow把它作为数据库名称，显示的结果类似于show tables from db_name;
3.如果提供两个参数，mysqlshow把它作为数据库和表名称，显示的结果类似于
show full columns from table_name from db_name;
注：
当mysqlshow被用于显示表的结构时，--keys选项也可以被用来显示索引的结构。其显示信息类似于show index from table_name;的显示结果。

4.如果提供三个参数，mysqlshow把它们作为数据库，表盒列名；显示的结果类似于
show full columns from table_name from db_name,但是只显示指定列相关的信息。


如果在命令行的最后一个参数包含特殊字符，mysqlshow将把参数作为一个正则式，并且只显示匹配正则式的名称。特殊字符%或*可以匹配任何字符串，_和？匹配任何单个字符。如以下的命令只显示以w开始的数据库名称。
shell>mysqlshow "w%"

注：任何包含特殊字符的参数都应当被双引号括起来.如:"w%"

C:\Users\Administrator>mysqlshow -hlocalhost -uroot -proot test
Database: test
+------------+
|   Tables   |
+------------+
| log        |
| people     |
| peopleview |
+------------+

C:\Users\Administrator>mysqlshow -hlocalhost -uroot -proot test log
Database: test  Table: log
+----------+------------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field    | Type             | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+----------+------------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| name     | char(30)         | utf8_general_ci | NO   | PRI |         |       | select,insert,update,references |         |
| location | char(30)         | utf8_general_ci | NO   | PRI |         |       | select,insert,update,references |         |
| counter  | int(10) unsigned |                 | NO   |     |         |       | select,insert,update,references |         |
+----------+------------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

C:\Users\Administrator>mysqlshow -hlocalhost -uroot -proot test log name
Database: test  Table: log  Wildcard: name
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type     | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| name  | char(30) | utf8_general_ci | NO   | PRI |         |       | select,insert,update,references |         |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

C:\Users\Administrator>mysqlshow -hlocalhost -uroot -proot --keys test "p%"
Database: test  Wildcard: p%
+------------+
|   Tables   |
+------------+
| people     |
| peopleview |
+------------+

C:\Users\Administrator>mysqlshow -hlocalhost -uroot -proot test log "p%"
Database: test  Table: log  Wildcard: p%
+-------+------+-----------+------+-----+---------+-------+------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+------+-----------+------+-----+---------+-------+------------+---------+
+-------+------+-----------+------+-----+---------+-------+------------+---------+