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,但是只显示指定列相关的信息。