1-MySQL - 视图(VIEW)

目录

about

视图是虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义；同真实表(基表)一样，视图包含一系列带有名称的字段和记录，在使用视图时动态生成。视图的数据变化会影响到基表，基表的数据变化也会影响到视图(insert、update、delete)。

另外，创建视图需要有create view权限，并且查询的列有select权限，使用create or update or alter修改视图，还需要有相应的drop权限。

视图可以查询、修改和删除，但不允许通过视图向基表插入数据。

视图的做用

对其中所引用的基础表来说，视图的做用类似于筛选，定义视图的筛选可以来自当前或者其他数据库的一个或多个表，也可以是其他视图；通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。

使用视图的优点

  • 安全性，视图的安全性可以防止未授权用户查看特定的行或列，使有权限用户只能看到表中特定行的方法，如下：

    1. 在表中增加一个标志用户名的列。

    2. 建立视图，使用户只能看到标有自己用户名的行。

    3. 把视图授权给其他用户。

  • 简单性，看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

  • 逻辑数据独立性，视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，程序一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。视图可以在以下几个方面使程序与数据独立。

    1. 如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而使应用程序可以不动。

    2. 如果应用建立在数据库表上，当应用发生变化时，可以在表上建立视图，通过视图屏蔽应用的变化，从而使数据库表不动。

    3. 如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而使应用程序可以不动。

    4. 如果应用建立在视图上，当应用发生变化时，可以在表上修改视图，通过视图屏蔽应用的变化，从而使数据库可以不动。

视图基本操作

创建视图

-- 基本语法
CREATE VIEW 视图名称 AS SQL语句;

-- 示例：查询中国所有城市信息,只展示前10条
CREATE VIEW v1 AS
SELECT * FROM city WHERE countrycode='CHN' LIMIT 10;

查询视图

-- 查询 v1 视图
SELECT * FROM v1;

修改视图

ALTER VIEW 视图名称 as SQL语句;

删除视图

DROP VIEW 视图名称;

information_schema

information_schema是视图库(虚拟库)：

USE information_schema;
SHOW TABLES;	-- 返回了一堆视图

表由两部分组成：

  • 元数据，表相关信息+字段信息(属性，约束)。
  • 数据行，就是普通的记录了。

元数据单独存储在"基表"中，是我们无法直接访问的。但MySQL提供了DDL、DCL来进行对元数据修改；提供了information_schema和SHOW语句查询元数据。

MySQL5.7版本中，共有information_schema、performance_schema、sys三张视图库。但在MySQL早期版本中，只有information_schema视图库，后来方便，就把一些复杂的操作封装了一下，这就是performance_schema视图库，再后来又有了sys库，直到如今的版本中的三张视图库。

常用操作

这次，我们主要对information_schema.TABLES表进行学习，这个表存储了整个数据库中所有表的元数据。

-- information_schema.TABLES中常用的字段
DESC infoRmation_schema.TABLES;
TABLE_SCHEMA	-- 库名
TABLE_NAME		-- 表名
ENGINE			-- 引擎
TABLE_ROWS		-- 表行数
AVG_ROW_LENGTH	-- 表中行平均长度(字节)
INDEX_LENGTH	-- 索引的占用空间大小(字节)

知道了上面常用的字段之后，来看看我们平常用它来做什么：

-- 查询information_schema.TABLES表信息
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,AVG_ROW_LENGTH,INDEX_LENGTH
FROM information_schema.TABLES;

-- 查询整个数据库中所有库和对应的表信息
SELECT TABLE_SCHEMA, GROUP_CONCAT(TABLE_NAME)
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA;

-- 统计所有库下表的个数
SELECT TABLE_SCHEMA,COUNT(TABLE_NAME)
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA;

-- 查询所有使用innodb引擎的表及所在的库
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE
FROM information_schema.TABLES
WHERE ENGINE='innodb';

-- 统计指定数据库(world)下每张表的磁盘空间占用
SELECT  TABLE_SCHEMA,TABLE_NAME,(TABLE_ROWS * AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 AS 'size(KB)'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='world';

-- 统计所有数据库的总磁盘空间占用
SELECT COUNT(TABLE_SCHEMA) AS '数据库个数',SUM((TABLE_ROWS * AVG_ROW_LENGTH + INDEX_LENGTH) / 1024) AS 'size(KB)'
FROM information_schema.TABLES;

生成备份语句

如果有个需求是，生成全部数据库下所有表的备份语句，也就是生成一个bak.sh文件，其内存放的是一条条的备份语句，完事使用sh bak.sh就能批量的执行备份语句得到最终的备份文件。这整个流程该怎么做？

  1. 首先要先了解一个备份命令：
-- 注意其中的空格也是特殊字符(分隔符)
mysqldump -uroot -p123 world city >/bak/world_city.sql
-- 上面的例子是生成world.city表的备份，并且将备份导出到/bak/world_city.sql中
  1. 查询出来全部(这里以指定数据库为例)数据库下所有表，并且使用CONCAT()函数进行拼接备份命令：
-- 生成指定(world)数据库下所有表的单独备份语句
SELECT CONCAT('mysqldump -uroot -p123 ',TABLE_SCHEMA,' ',TABLE_NAME,' >/bak/',TABLE_SCHEMA,'_',TABLE_NAME,'.sql')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='world';	-- 去掉 where 语句，就是整个数据库下所有表的备份语句
+------------------------------------------------------------------------------------------------------------+
| CONCAT('mysqldump -uroot -p123 ',TABLE_SCHEMA,' ',TABLE_NAME,' >/bak/',TABLE_SCHEMA,'_',TABLE_NAME,'.sql') |
+------------------------------------------------------------------------------------------------------------+
| mysqldump -uroot -p123 world city >/bak/world_city.sql                                                     |
| mysqldump -uroot -p123 world country >/bak/world_country.sql                                               |
| mysqldump -uroot -p123 world countrylanguage >/bak/world_countrylanguage.sql                               |
+------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
  1. 拼接的备份命令完事了，现在使用INTO OUTFILE '/tmp/xxx.sh'往本地磁盘上生成.sh文件了，这里还要做个设置，就是INTO OUTFILE后的tmp目录需要设置为安全路径(不然后续执行会报错)，修改/etc/my.cnf配置文件，添加如下字段：
[mysqld]
secure-file-priv=/tmp

完事重启(systemctl restart mysqld.service)MySQL服务，下面是没有配置安全路径就执行命令报的错：

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
  1. 生成.sh文件：
-- 生成指定(world)数据库下所有表的单独备份语句
SELECT CONCAT('mysqldump -uroot -p123 ',TABLE_SCHEMA,' ',TABLE_NAME,' >/bak/',TABLE_SCHEMA,'_',TABLE_NAME,'.sql')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='world'	-- 去掉 where 语句，就是整个数据库下所有表的备份语句
INTO OUTFILE '/tmp/mysql_bak.sh';
-- 这里要保证 /tmp 和 /bak 两个目录都存在
-- /tmp/mysql_bak.sh 是最终的脚本文件，里面存放的是一个个备份命令
-- /bak/ 该目录下面是当执行 /tmp/mysql_bak.sh 后生成的备份文件目录
  1. 执行.sh文件，即执行备份命令：
[root@cs data]# ls /tmp/mysql_bak*
/tmp/mysql_bak.sh
[root@cs data]# sh /tmp/mysql_bak.sh 
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
  1. 生成的备份文件在/bak目录中：
[root@cs data]# ls /bak/
world_city.sql  world_countrylanguage.sql  world_country.sql

在整个流程中，需要注意的点：

  • /tmp and /bak目录要存在。
  • mysqldump命令要熟悉。
  • CONCAT()函数拼接要玩的溜，尤其注意其中的空格分隔符别忘了。
  • my.cnf配置文件要记得配置安全路径，相关参数(secure-file-priv=/tmp)要知道。
  • 还要记得INTO OUTFILE '/tmp/mysql_bak.sh'命令。

show命令

前面，我们使用SELECT命令对information_schema.TABLES表一顿操作；那么SHOW命令就是对常用的视图操作进行封装，便于操作，其实它本质上也是对information_schema库进行操作。

下面列举一些常用的SHOW命令：

show  databases;					-- 查看所有数据库
show tables;						-- 查看当前库的所有表
show TABLES FROM					-- 查看某个指定库下的表
show create database world			-- 查看建库语句
show create table world.city		-- 查看建表语句
show grants for root@'localhost'	-- 查看用户的权限信息
show charset;						-- 查看字符集
show collation						-- 查看校对规则
show processlist;					-- 查看数据库连接情况
show index from						-- 表的索引情况
show status 						-- 数据库状态查看
show STATUS LIKE '%lock%';		-- 模糊查询数据库某些状态
show VARIABLES					-- 查看所有配置信息
show variables LIKE '%lock%'; 	-- 查看部分配置信息
show engines					-- 查看支持的所有的存储引擎
show engine innodb status\G		-- 查看InnoDB引擎相关的状态信息
show binary logs				-- 列举所有的二进制日志
show master status				-- 查看数据库的日志位置信息
show binlog evnets in			-- 查看二进制日志事件
show slave status \G			-- 查看从库状态
show RELAYLOG EVENTS 			-- 查看从库relaylog事件信息
desc (show colums from city) 	-- 查看表的列定义信息
-- 不知道更多，请使用help
help show

更多SHOW命令参考：http://dev.mysql.com/doc/refman/5.7/en/show.html

最后，我们需要精通SHOW命令，因为我们对于视图的操作有6,70%都可以使用SHOW命令来完成；剩下的使用SELECT也就OK了。

see also： 第六篇：视图、触发器、事务、存储过程、函数 | MySql创建视图不支持子查询解决方案(left join) | MySQL视图(view) | MySQL视图

posted @ 2020-09-01 17:34  听雨危楼  阅读(807)  评论(0)  编辑  收藏  举报