MySQL数据库(9)----从命令行获取元数据
2024-09-17 00:58:41
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。
最新文章
- Eos开发——构造查询条件
- WcfDataService with EntityFramework 6 的若干问题
- [工作中的设计模式]桥接模式bridge
- ASP.NEt MVC5--创建下拉列表
- Syslog
- CSS基础知识点（二）——选择器
- 转载Ajax.Net--ScriptManager和UpdatePanel控件
- 常见shell操作
- (转)如何在Excel2013中制作条形码
- Android开发必知--使用View.setId的正确姿势
- 学习VI的强文，新工作需要呀
- avalon学习笔记一 列表及条件过滤
- hdu 1599 find the mincost route
- Search 命令详解
- I/HwPointEventFilter: do not support AFT because of no config
- POJ1743：Musical Theme
- 自动化服务部署（一）：Linux下安装JDK
- SQL SERVER 临时数据库 tempdb 迁移或增加文件
- Visual studio 2017添加引用时报错未能正确加载ReferenceManagerPackage包的解决方法
- CSS 背景图像 填充部分元素示例