ITPub博客

首页 > 数据库 > MySQL > MySQL入门--元数据

MySQL入门--元数据

MySQL 作者:panpong 时间:2019-06-20 09:41:11 0 删除 编辑

MySQL入门--元数据

1.   MySQL 元数据

1.1.        元数据访问方法

数据库是数据的结构化集合。元数据是“有关数据的数据”。 MySQL 通过以下方法提供对元数据的访问:

INFORMATION_SCHEMA MySQL 服务器包含一个名为 INFORMATION_SCHEMA 的数据库(模式)的数据字典,其中包含许多显示为表的对象。

SHOW 语句: 用于获取服务器统计信息、模式和模式对象的相关数据的专用语法。 SHOW DATABASES SHOW TABLES 返回包含数据库和表名的列表; SHOW COLUMNS 生成表中列的定义;使用 SHOW 语句需要有 SELECT 权限。

DESCRIBE 可用于查询表结构和列属性的 SQL 语句快捷方式

mysqlshow 用作指向一些 SHOW 语句的命令行命令。您设置的参数将决定要显示的信息,然后程序会发出相应的 SHOW 语句并显示语句的结果。

下面将详细介绍这 4 中访问元数据的方法。

1.2.        INFORMATION_SCHEMA 数据库

INFORMATION_SCHEMA 数据库充当数据库元数据的中央系统信息库,包含模式和模式对象、服务器统计信息(状态变量、设置、连接)。它未存储在硬盘上,从这个方面来看,它是“虚拟数据库”;但是,它与其他任何数据库一样包含表,与其他任何表一样可以使用 SELECT 来访问其中表的内容

1)        查询 INFORMATION_SCHEMA 中的表

mysql> SELECT TABLE_NAME

-> FROM INFORMATION_SCHEMA.TABLES

-> WHERE TABLE_SCHEMA = 'information_schema'

-> ORDER BY TABLE_NAME;

INFORMATION_SCHEMA 表包含以下类型的信息:

Ø   表信息

COLUMNS :表和视图中的列

ENGINES :存储引擎

SCHEMATA :数据库

TABLES :数据库中的表

VIEWS :数据库中的视图

Ø   分区

PARTITIONS :表分区

FILES :存储 MySQL NDB 磁盘数据表的文件

Ø   权限

COLUMN_PRIVILEGES MySQL 用户帐户所拥有的列权限

SCHEMA_PRIVILEGES MySQL 用户帐户所拥有的数据库权限

TABLE_PRIVILEGES MySQL 用户帐户所拥有的表权限

USER_PRIVILEGES MySQL 用户帐户所拥有的全局权限

Ø   字符集支持

CHARACTER_SETS :可用的字符集

COLLATIONS :每个字符集的整理

COLLATION_CHARACTER_SET_APPLICABILITY :适用于特定字符集的整理

Ø   约束和索引

KEY_COLUMN_USAGE :关键列的约束

REFERENTIAL_CONSTRAINTS :外键

STATISTICS :表索引

TABLE_CONSTRAINTS :表的约束

Ø   服务器设置和状态

KEY_COLUMN_USAGE :约束

GLOBAL_STATUS :所有 MySQL 连接的状态值

GLOBAL_VARIABLES :用于新的 MySQL 连接的值

PLUGINS :服务器插件

PROCESSLIST :指示哪些线程正在运行

SESSION_STATUS :当前 MySQL 连接的状态值

SESSION_VARIABLES :当前 MySQL 连接的生效值

Ø   例程及相关信息

EVENTS :预定事件

ROUTINES :存储过程和功能

TRIGGERS :数据库中的触发器

PARAMETERS :存储过程和功能参数以及存储函数

Ø   InnoDB

INNODB_CMP INNODB_CMP_RESET :对压缩的 InnoDB 表的相关操作的状态

INNODB_CMPMEM INNODB_CMPMEM_RESET InnoDB 缓冲池中压缩页面的状态

INNODB_LOCKS InnoDB 事务所请求和持有的每个锁

INNODB_LOCK_WAITS :每个阻塞的 InnoDB 事务的一个或多个行锁

INNODB_TRX :当前正在 InnoDB 内部执行的所有事务

TABLESPACES :活动的表空间

 

有关 INFORMATION_SCHEMA 表的更多信息,请参阅《 MySQL 参考手册》:

http://dev.mysql.com/doc/refman/5.6/en/information-schema.html

 

例如:查询 INFORMATION_SCHEMA 数据库表列:

mysql> SELECT COLUMN_NAME

-> FROM INFORMATION_SCHEMA.COLUMNS

-> WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'

-> AND TABLE_NAME = 'VIEWS';

 

当使用 SELECT 语句在 INFORMATION_SCHEMA 表中检索元数据时,您可以使用任何常见的 SELECT 功能。通过使用 CREATE TABLE...SELECT 语句或 INSERT...SELECT 语句,您可以将 INFORMATION_SCHEMA 查询的结果检索到其他表中。您可以保存结果,以便稍后在其他语句中使用它们。

A.        显示用于给定数据库中表的存储引擎

mysql> SELECT TABLE_NAME, ENGINE

-> FROM INFORMATION_SCHEMA.TABLES

-> WHERE TABLE_SCHEMA = 'world_innodb';

B.        查找所有包含 SET 列的表

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

-> FROM INFORMATION_SCHEMA.COLUMNS

-> WHERE DATA_TYPE = 'set';

C.        显示每个字符集的默认整理

mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME

-> FROM INFORMATION_SCHEMA.COLLATIONS

-> WHERE IS_DEFAULT = 'Yes';

D.        显示每个数据库中表的编号

mysql> SELECT TABLE_SCHEMA, COUNT(*)

-> FROM INFORMATION_SCHEMA.TABLES

-> GROUP BY TABLE_SCHEMA;

E.         INFORMATION_SCHEMA 表是只读的,无法用 INSERT DELETE UPDATE 之类的语句进行修改。如果执行这些类型的语句以尝试更改 INFORMATION_SCHEMA 表中的数据,服务器将生成错误。

mysql> DELETE FROM INFORMATION_SCHEMA.VIEWS;

ERROR 1044 (42000): Access denied for user

'root'@'localhost' to database 'information_schema'

 

2)        使用 INFORMATION_SCHEMA 表创建 Shell 命令

使用 CONCAT 功能可以将字符串内容结合起来创建可在命令行中执行的 shell 脚本。如示例所示, SQL 语句将生成一条输出,仅转储 world_innodb 数据库中那些以单词“ Country ”开始的的表。输出将生成可以在 shell 命令行上正确执行的 shell 脚本。下一步是将此输出存储在一个可在 shell 命令行中执行的批处理文件中。这通过添加子句 INTO OUTFILE 来完成:

mysql> SELECT CONCAT("mysqldump -uroot -p ",

-> TABLE_SCHEMA, " ",TABLE_NAME, " >> ",TABLE_SCHEMA,".sql")

-> FROM TABLES WHERE TABLE_NAME LIKE 'Country%'

-> INTO OUTFILE '/Country_Dump.sh';

然后可以在命令行中执行此文件,命令行将运行示例的两个 mysqldump 命令:

shell> \tmp\Country_Dump.sh

shell> \tmp\mysqldump -uroot -poracle world_innodb Country >>world_innodb.sql

shell> \tmp\mysqldump -uroot -poracle world_innodb Country_Language >>world_innodb.sql

 

3)        使用 INFORMATION_SCHEMA 表创建 SQL 语句

使用 mysql 命令创建 SQL 语句,使用 -e 选项输入 SELECT/CONCAT 语句:

shell> mysql -uroot -p --silent --skip-column-names -e

"SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.',

TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.',

TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'world_innodb';"

将导致以下语句发送到标准输出:

CREATE TABLE world_innodb.City_backup LIKE world_innodb.City;

CREATE TABLE world_innodb.Country_backup LIKE world_innodb.Country_backup;

CREATE TABLE world_innodb.CountryLanguage_backup LIKE world_innodb.CountryLanguage_backup;

INFORMATION_SCHEMA 表可创建可在命令行中执行的 SQL 语句。本示例使用 mysql 命令执行了一个语句,以制作 world_innodb 数据库中所有表的精确副本。此命令将创建 SQL 输出,如果执行该输出,将基于 world_innodb 数据库中的表创建三个备份表。

注: --silent 命令在输出中删除列标题, --skip-column-names 命令删除输出中的格式(使输出类似于表的格式)。这两个命令用来确保对命令自身的解释是正确的,没有任何干扰执行的外部格式或标题行问题。

添加管道符号 (|) 并随之执行 mysql 命令会将这些 SQL 语句发送到 MySQL 服务器以便执行:

shell> mysql -uroot -p --silent --skip-column-names -e "SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'world_innodb';" | mysql -uroot -poracle


1.3.        SHOW 语句

除了 INFORMATION_SCHEMA 表之外, MySQL 还支持 SHOW DESCRIBE 语句,作为访问元数据的备选方式。 SHOW DESCRIBE 语法不如使用 INFORMATION_SCHEMA 查询灵活,但是对于大多数用途, SHOW DESCRIBE 语法就足够了。在这些情况下,使用 MySQL 特定语法通常会更快速、简单。 MySQL 支持的 SHOW 语句有:

SHOW DATABASES

SHOW TABLES

SHOW TABLE STATUS

SHOW CREATE TABLE

SHOW OPEN TABLES

SHOW INDEX

SHOW COLUMNS

SHOW PROCESSLIST

SHOW COLLATION

SHOW CHARACTER SET

 

SHOW 语句示例:

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

| world_innodb |

+--------------------+

mysql> SHOW TABLES;

mysql> SHOW TABLES FROM mysql;

mysql> SHOW TABLES FROM INFORMATION_SCHEMA;

mysql> SHOW COLUMNS FROM CountryLanguage;

mysql> SHOW FULL COLUMNS FROM CountryLanguage\G

mysql> SHOW DATABASES LIKE 'm%';

mysql> SHOW COLUMNS FROM Country WHERE `Default` IS NULL;

mysql> SHOW INDEX FROM City\G

mysql> SHOW CHARACTER SET;

mysql> SHOW COLLATION;

 

1.4.        DESCRIBE 语句

DESCRIBE 等效于 SHOW COLUMNS ,可以缩写为 DESC

mysql> DESCRIBE <table_name>;

 

显示 INFORMATION_SCHEMA 表信息

mysql> DESCRIBE INFORMATION_SCHEMA.CHARACTER_SETS;

+----------------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------------------+-------------+------+-----+---------+-------+

| CHARACTER_SET_NAME | varchar(64) | NO | | | |

| DEFAULT_COLLATE_NAME | varchar(64) | NO | | | |

| DESCRIPTION | varchar(60) | NO | | | |

| MAXLEN | bigint(3) | NO | | 0 | |

+----------------------+-------------+------+-----+---------+-------+

例如:

mysql> DESCRIBE table_name;

mysql> DESC table_name;

         以下语句等效于上述 DESCRIBE/DESC 示例:

mysql> SHOW COLUMNS FROM table_name;

         但是, SHOW COLUMNS 支持可选的 LIKE WHERE 子句,而 DESCRIBE 不支持。

 

EXPLAIN :当指定表名称作为参数时, EXPLAIN 等效于 DESCRIBE

mysql> EXPLAIN table_name;

 

1.5.        mysqlshow 命令

mysqlshow 为各种格式的 SHOW 语句提供了一个命令行界面,这些语句用于列出数据库的名称、数据库中的表或有关表列或索引的信息。

mysqlshow 的选项部分可包含任一标准连接参数选项,例如 --host --user 。如果默认连接参数不适合,则必须提供选项。 mysqlshow 也接受特定于其自身运行的选项。使用 --help 选项调用 mysqlshow 可查看其选项的完整列表。 mysqlshow 所执行的操作取决于已提供的非选项参数的数量。

mysqlshow 示例

显示所有数据库或特定数据库、表和 / 或列的相关信息:

A.        在没有参数的情况下, mysqlshow 将显示类似于 SHOW DATABASES 的结果

shell> mysqlshow -u<user_name> -p<password>

+--------------------+

| Databases |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

| world_innodb |

+--------------------+

B.        在使用单个参数的情况下, mysqlshow 将该参数解释为数据库名称,并针对该数据库显示类似于 SHOW TABLES 的结果。

shell> mysqlshow world_innodb

C.        在有两个参数的情况下, mysqlshow 将参数解释为数据库和表名称,并针对该表显示类似于 SHOW FULL COLUMNS 的结果。

shell> mysqlshow world_innodb City

D.        在有三个参数的情况下,其输出与两个参数的情况相同,不同之处在于: mysqlshow 将第三个参数当做列名称,且仅针对该列显示 SHOW FULL COLUMNS 输出。

shell> mysqlshow world_innodb City CountryCode

E.         如果命令行中最后的参数包含特殊字符, mysqlshow 会将该参数解释为模式,且仅显示与该模式匹配的名称。特殊字符包括: % * (匹配任一字符序列),以及 _ ? (匹配任一单个字符)。本示例中的命令仅显示那些名称始于 w 的数据库。

shell> mysqlshow "w%"

注:这些示例要求在执行命令时使用用户和口令作为参数。

 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16976507/viewspace-2648163/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
数据库工程师,熟悉oracle,postgresql,mysql,DB2等主流关系型数据库运维,调优,一直活跃在运维一线。了解Python,django,简单自动化运维系统开发。

注册时间:2009-03-06

  • 博文量
    138
  • 访问量
    268984