Mysql数据库操作指引（七）——Mysql常用数据字典

简介：数据字典就类似于系统编目或花名册（和目录不太相同），保存数据库服务器上的元数据信息（数据库的整体属性信息）。元数据信息包括：关系（表）的名字、每个关系的各字段的名字、各字段的数据类型和长度约束、每个关系上的视图的名字及视图的定义、授权用户的名字、用户的授权和账户信息、统计类的数据：、每个关系字段的个数、每个关系中的行数、每个关系的存储方法。

在mysql上保存元数据的数据库有：mysql库、infomation_schema库和performance_schema库mssql查看数据库，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 mysql.user;

备注：5.7版本下的mysql数据库下已经没有password这个字段，password字段改成了authentication_string。

5、查询连接主机IP地址

mysql> select host as `主机`,current_connections as `当前连接数`,total_connections as `所有连接数` from performance_schema.hosts order by host;

6、查询所有存储过程信息

mysql> select db as '数据库',name as '过程名',type,definer,created,modified from mysql.proc;

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 performance_schema.threads where type = 'foreground';

8、查询所有数据表

mysql> select object_schema as `数据库名`,object_name as `数据表名` from performance_schema.table_io_waits_summary_by_table 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 performance_schema.table_io_waits_summary_by_index_usage 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 performance_schema.file_instances order by file_name;

11、查询mysql所有表数据和字段信息

mysql> select * from information_schema.tables where table_schema='mysql';

12、根据库名获取所有表名称和表说明

mysql> select table_name as '表名称',table_comment as '表说明' from information_schema.tables 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 information_schema.columns 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;

（编辑：成都站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!