mysql命令提取数据库_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` dateNOT 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。