什么是元数据?
我们在安装完成MySQL成功后,会发现数据库中已存在information_schema数据库(视图,虚拟)。为什么我们没有建数据库,它就已经存在呢?information_schema是MySQL自带的提供访问数据库元数据的视图。那什么是元数据呢?元数据就是指非数据的数据,这句话是不是很矛盾,一切皆数据,怎么会有非数据呢?其实我想说的就是数据库名,列名,数据类型,访问权限等等数据属性。

元数据访问方法
information_schema数据库表说明:

 

  • SCHEMATA表:提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。
  • TABLES表:提供了关于数据库中的表的信息(包括视图)。详细表述了某个表属于哪个schema,表类型,表引擎,创建时间等信息。是show tables from schemaname的结果取之此表。
  • COLUMNS表:提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
  • STATISTICS表:提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
  • USER_PRIVILEGES(用户权限)表:给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。
  • SCHEMA_PRIVILEGES(方案权限)表:给出了关于方案(数据库)权限的信息。该信息来自mysql.db授权表。是非标准表。
  • TABLE_PRIVILEGES(表权限)表:给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
  • COLUMN_PRIVILEGES(列权限)表:给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
  • CHARACTER_SETS(字符集)表:提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。
  • COLLATIONS表:提供了关于各字符集的对照信息。
  • COLLATION_CHARACTER_SET_APPLICABILITY表:指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
  • TABLE_CONSTRAINTS表:描述了存在约束的表。以及表的约束类型。
  • KEY_COLUMN_USAGE表:描述了具有约束的键列。
  • ROUTINES表:提供了关于存储子程序(存储程序和函数)的信息。此时,ROUTINES表不包含自定义函数(UDF)。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
  • VIEWS表:给出了关于数据库中的视图的信息。需要有show views权限,否则无法查看视图信息。
  • TRIGGERS表:提供了关于触发程序的信息。必须有super权限才能查看该表

使用 SHOW 语句
 -- 用于获取数据库和表信息的 MySQL 专用语句

使用 DESCRIBE(或 DESC)语句。
 -- 用于检查表结构和列属性的快捷方式
 
使用 mysqlshow 客户端程序。
 -- SHOW 语法的命令行程序
 
INFORMATION_SCHEMA 数据库介绍
充当数据库元数据的中央系统信息库
  • 模式和模式对象
  • 服务器统计信息(状态变量、设置、连接)
采用表格式以实现灵活访问
  • 使用任意 SELECT 语句
是“虚拟数据库”
  • 表并非“真实”表(基表),而是“系统视图”
  • 根据当前用户的特权动态填充表

对 INFORMATION_SCHEMA 使用 SELECT

  • 指定要检索哪个表和哪些列。
  • 通过使用 WHERE 子句,可仅检索特定条件。
  • 对结果分组或排序。
  • 使用 JOIN、UNION 和子查询。
  • 将结果检索到其他表中。基于 INFORMATION_SCHEMA 表创建视图

例如:

### 查看world数据中的所有表和对应的引擎
mysql> select table_name ,engine from information_schema.tables where table_schema = 'world';
+-----------------+--------+
| table_name      | engine |
+-----------------+--------+
| city            | InnoDB |
| country         | InnoDB |
| countrylanguage | InnoDB |
+-----------------+--------+
3 rows in set (0.00 sec)

### 查看set数据类型的数据库名,表名,列名
mysql> select table_schema,table_name,column_name from information_schema.columns where data_type = 'set';
+--------------+--------------+-------------+
| table_schema | table_name   | column_name |
+--------------+--------------+-------------+
| mysql        | columns_priv | Column_priv |
| mysql        | event        | sql_mode    |
| mysql        | proc         | sql_mode    |
| mysql        | procs_priv   | Proc_priv   |
| mysql        | tables_priv  | Table_priv  |
| mysql        | tables_priv  | Column_priv |
+--------------+--------------+-------------+
6 rows in set (0.01 sec)

### 统计每个数据下表的个数
mysql> select table_schema,count(0) from information_schema.tables group by table_schema;
+--------------------+----------+
| table_schema       | count(0) |
+--------------------+----------+
| information_schema |       59 |
| mysql              |       28 |
| performance_schema |       52 |
| world              |        3 |
+--------------------+----------+
4 rows in set (0.01 sec)

### 无法删除表 
mysql> DELETE FROM INFORMATION_SCHEMA.VIEWS;
ERROR 1044 (42000): Access denied for user 
'root'@'localhost' to database 'information_schema'

 

使用 INFORMATION_SCHEMA 表创建 Shell 命令
  • 使用 INFORMATION_SCHEMA 表获取有关创建 shell 命令的信息
  • 将 SELECT 和 CONCAT 一起使用以创建 mysqldump 脚本:
    正常情况下备份某个数据库中的一个表:
    [root@db01 ~]# mysqldump -uroot -p123456 world country >> /data/world_country.bak.sql

    利用information_schema 连接所有表:

    mysql> SELECT CONCAT("mysqldump -uroot -p ", TABLE_SCHEMA," ", TABLE_NAME, " >> ", TABLE_SCHEMA,".bak.sql") FROM TABLES WHERE TABLE_NAME LIKE 'Country%';
    +-----------------------------------------------------------------------------------------------+
    | CONCAT("mysqldump -uroot -p ", TABLE_SCHEMA," ", TABLE_NAME, " >> ", TABLE_SCHEMA,".bak.sql") |
    +-----------------------------------------------------------------------------------------------+
    | mysqldump -uroot -p world country >> world.bak.sql                                            |
    | mysqldump -uroot -p world countrylanguage >> world.bak.sql                                    |
    +-----------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> 

     

使用 INFORMATION_SCHEMA 表创建 SQL 语句 

  • 使用 mysql 命令创建 SQL 语句。
  • 使用 -e 选项输入 SELECT/CONCAT 语句:
    [root@db01 ~]#  mysql -uroot -p123456 --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';"
    Warning: Using a password on the command line interface can be insecure.
    CREATE TABLE world.city_backup LIKE world.city;
    CREATE TABLE world.country_backup LIKE world.country;
    CREATE TABLE world.countrylanguage_backup LIKE world.countrylanguage;
    [root@db01 ~]# 
    

     

MySQL中的show语句 

  • SHOW databases:列出所有数据库
  • SHOW create database <database_name>:查看数据库创建信息
  • SHOW TABLES:列出默认数据库中的表
  • SHOW TABLES FROM <database_name>:列出指定数据库中的表
  • SHOW COLUMNS FROM <table_name>:显示表的列结构
  • SHOW INDEX FROM <table_name>:显示表中有关索引和索引列的信息
  • SHOW CHARACTER SET:显示可用的字符集及其默认整理
  • SHOW COLLATION:显示每个字符集的整理
  • SHOW STATUS:列出当前数据库状态
  • SHOW VARIABLES:列出数据库中的参数定义值
DESCRIBE 语句
  • 等效于 SHOW COLUMNS
  • 语法使用:mysql> DESCRIBE <table_name>
    mysql> describe information_schema.character_sets;
    +----------------------+-------------+------+-----+---------+-------+
    | Field                | Type        | Null | Key | Default | Extra |
    +----------------------+-------------+------+-----+---------+-------+
    | CHARACTER_SET_NAME   | varchar(32) | NO   |     |         |       |
    | DEFAULT_COLLATE_NAME | varchar(32) | NO   |     |         |       |
    | DESCRIPTION          | varchar(60) | NO   |     |         |       |
    | MAXLEN               | bigint(3)   | NO   |     | 0       |       |
    +----------------------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)
    
    mysql> 

     

mysqlshow 客户端工具
  • 有关数据库和表的结构的信息(与 SHOW 语句相似),在shell命令行运行
  • 一般语法:shell> mysqlshow [options] [db_name [table_name [column_name]]]
  • 选项可以是标准连接参数(mysqlshow --help)
    [root@db01 ~]# mysqlshow -uroot -p123456 world
    Warning: Using a password on the command line interface can be insecure.
    Database: world
    +-----------------+
    |     Tables      |
    +-----------------+
    | city            |
    | country         |
    | countrylanguage |
    +-----------------+
    [root@db01 ~]# 

     

 

最后修改于 2019-08-06 16:42:29
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付
上一篇