MySQL数据库(9)----从命令行获取元数据
1. mysqlshow 命令提供的信息与某些 SHOW 语句很相似，因此可以从命令行提示符获取数据库和表的信息。
(i)列出服务器所管理的数据库：
root@javis:~$ mysqlshow -p -uroot Enter password: +--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | performance_schema | | sampdb | | test | +--------------------+
(ii)列出数据库里的表：
root@javis:~$ mysqlshow -p -uroot sampdb Enter password: Database: sampdb +-------------+ | Tables | +-------------+ | absence | | grade_event | | member | | mytable | | president | | score | | student | +-------------+
(iii) 显示表里的列信息：
root@javis:~$ mysqlshow -p -uroot sampdb student Enter password: Database: sampdb Table: student +------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | name | varchar(20) | utf8_general_ci | NO | | | | select,insert,update,references | | | sex | enum('F','M') | utf8_general_ci | NO | | | | select,insert,update,references | | | student_id | int(10) unsigned | | NO | PRI | | auto_increment | select,insert,update,references | | +------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
(iv) 显示表里的索引信息：
root@javis:~$ mysqlshow -p -uroot --keys sampdb student Enter password: Database: sampdb Table: student +------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | name | varchar(20) | utf8_general_ci | NO | | | | select,insert,update,references | | | sex | enum('F','M') | utf8_general_ci | NO | | | | select,insert,update,references | | | student_id | int(10) unsigned | | NO | PRI | | auto_increment | select,insert,update,references | | +------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | student | 0 | PRIMARY | 1 | student_id | A | 6 | | | | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
(v) 显示数据库里所有表的描述性信息：
root@javis:~$ mysqlshow -p -uroot --status sampdb Enter password: Database: sampdb +-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | absence | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | | 2016-07-17 11:49:27 | | | utf8_general_ci | | | | | grade_event | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 2 | 2016-07-17 11:44:18 | | | utf8_general_ci | | | | | member | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2016-07-17 10:37:29 | | | utf8_general_ci | | | | | mytable | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2016-07-26 20:48:29 | | | utf8_general_ci | | | | | president | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | | 2016-07-17 10:20:49 | | | utf8_general_ci | | | | | score | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | | 2016-07-17 11:47:25 | | | utf8_general_ci | | | | | student | InnoDB | 10 | Compact | 6 | 2730 | 16384 | 0 | 0 | 0 | 10 | 2016-07-17 11:39:17 | | | utf8_general_ci | | | | +-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
2. 客户端程序 mysqldump 能够让用户看到CREATE TABLE 语句 (与 SHOW CREATE TABLE 语句很像) 所定义的表结构。
(i) 如果使用 mysqldump 来查看表结构，切记要加上 --no-data 选项，否则看到的内容将是表里的数据
root@javis:~$ mysqldump -p -uroot --no-data sampdb Enter password: -- MySQL dump 10.13 Distrib 5.6.31, for Linux (x86_64) -- -- Host: localhost Database: sampdb -- ------------------------------------------------------ -- Server version 5.6.31 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `absence` -- DROP TABLE IF EXISTS `absence`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `absence` ( `student_id` int(10) unsigned NOT NULL, `date` date NOT NULL, PRIMARY KEY (`student_id`,`date`), CONSTRAINT `absence_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */;
......以下省略......
如果指定了数据库的名字，而没有给出任何表名，那么 mysqldump 将把该数据库里所有表的结构显示出来。否则，它将只会显示那些通过名字指定的那些表的信息。
在使用 mysqlshow 和 mysqldump 时，记得要指定必要的连接参数，如 --host、--usr 或 --password。
