鲁达 简介：数据字典就类似于系统编目或花名册（和目录不太相同），保存数据库服务器上的元数据信息（数据库的整体属性信息）。元数据信息包括：关系（表）的名字、每个关系的各字段的名字、各字段的数据类型和长度约束、每个关系上的视图的名字及视图的定义、授权用户的名字、用户的授权和账户信息、统计类的数据：、每个关系字段的个数、每个关系中的行数、每个关系的存储方法。
在mysql上保存元数据的数据库有：Mysql库、infomation_schema库和performance_schema库，performance_schema库是一个虚拟的库。
本文主要介绍Mysql数据库的常用数据字典查看及使用。详细内容请参考下文。
一、访问Mysql
1、登陆linux系统
2、查看mysql服务
执行指令 # systemctl status mysqld
二、Mysql数据字典管理
1、查看mysql版本
mysql> select version();
2、查看当前数据库
mysql> select database();
3、查看当前登录账户
mysql> select user();
4、查询所有账户信息
mysql> select user as '账户',host as '主机',authentication_string as '密码' from my;
备注：5.7版本下的mysql数据库下已经没有password这个字段，password字段改成了authentication_string。
5、查询连接主机IP地址
mysql> select host as `主机`,current_connections as `当前连接数`,total_connections as `所有连接数` from order by host;
6、查询所有存储过程信息
mysql> select db as '数据库',name as '过程名',type,definer,created,modified from my;
7、查询所有进程信息
mysql> select processlist_id as `id号`,processlist_user as `进程用户`,processlist_host as `进程主机`,processlist_db as '进程数据库',processlist_command as '执行指令',processlist_time as time,processlist_state as state,processlist_info as info from where type = 'foreground';
8、查询所有数据表
mysql> select object_schema as `数据库名`,object_name as `数据表名` from where object_schema not in ('mysql','performance_schema') order by object_schema,object_name;
9、查询所有索引
mysql> select object_schema as `数据库名`,object_name as `数据表名`,index_name as `索引名` from where object_schema not in ('mysql','performance_schema') and index_name is not null order by object_schema,object_name;
10、查询数据文件和日志文件
mysql> select file_name as '文件名',event_name as '事件名',open_count as '连接数' from order by file_name;
11、查询mysql所有表数据和字段信息
mysql> select * from in where table_schema='mysql';
12、根据库名获取所有表名称和表说明
mysql> select table_name as '表名称',table_comment as '表说明' from in where table_schema='mysql';
13、根据库名获取所有的字段信息
mysql> select table_schema as '库名',table_name as '表名',column_name as '列名',ordinal_position as '列的排列顺序',column_default as '默认值',is_nullable as '是否为空',data_type as '数据类型',character_maximum_length as '字符最大长度',numeric_precision as '数值精度(最大位数)',numeric_scale as '小数精度',column_type as 列类型,column_key '关键字',extra as '额外说明',column_comment as '注释' from in where table_schema = 'mysql' order by table_name,ordinal_position;
14、根据库名获取所有的库和表字段的基本信息
mysql> select c.table_schema as '库名',t.table_name as '表名',t.table_comment as '表注释',c.column_name as '列名',c.column_comment as '列注释',c.ordinal_position as '列的排列顺序',c.column_default as '默认值',c.is_nullable as '是否为空',c.data_type as '数据类型',c.character_maximum_length as '字符最大长度',c.numeric_precision as '数值精度(最大位数)',c.numeric_scale as '小数精度',c.column_type as 列类型,c.column_key '关键字',c.extra as '额外说明' from information_schema.`tables` t left join information_schema.`columns` c on t.table_name = c.table_name and t.table_schema = c.table_schema where t.table_schema = 'mysql' order by c.table_name,c.ordinal_position;