MySQL - 视图(VIEW)
about
视图是虚拟表,是从数据库中一个或多个表中导出来的表,其内容由查询定义;同真实表(基表)一样,视图包含一系列带有名称的字段和记录,在使用视图时动态生成。视图的数据变化会影响到基表,基表的数据变化也会影响到视图(insert、update、delete
)。
另外,创建视图需要有create view
权限,并且查询的列有select
权限,使用create or update or alter
修改视图,还需要有相应的drop
权限。
视图可以查询、修改和删除,但不允许通过视图向基表插入数据。
视图的做用
对其中所引用的基础表来说,视图的做用类似于筛选,定义视图的筛选可以来自当前或者其他数据库的一个或多个表,也可以是其他视图;通过视图进行查询没有任何限制,通过它们进行数据修改时的限制也很少。
使用视图的优点
-
安全性,视图的安全性可以防止未授权用户查看特定的行或列,使有权限用户只能看到表中特定行的方法,如下:
-
在表中增加一个标志用户名的列。
-
建立视图,使用户只能看到标有自己用户名的行。
-
把视图授权给其他用户。
-
-
简单性,看到的就是需要的。视图不仅可以简化用户对数据的理解,也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。
-
逻辑数据独立性,视图可以使应用程序和数据库表在一定程度上独立。如果没有视图,程序一定是建立在表上的。有了视图之后,程序可以建立在视图之上,从而程序与数据库表被视图分割开来。视图可以在以下几个方面使程序与数据独立。
-
如果应用建立在数据库表上,当数据库表发生变化时,可以在表上建立视图,通过视图屏蔽表的变化,从而使应用程序可以不动。
-
如果应用建立在数据库表上,当应用发生变化时,可以在表上建立视图,通过视图屏蔽应用的变化,从而使数据库表不动。
-
如果应用建立在视图上,当数据库表发生变化时,可以在表上修改视图,通过视图屏蔽表的变化,从而使应用程序可以不动。
-
如果应用建立在视图上,当应用发生变化时,可以在表上修改视图,通过视图屏蔽应用的变化,从而使数据库可以不动。
-
视图基本操作
创建视图
-- 基本语法 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 视图名称;
生成备份语句
如果有个需求是,生成全部数据库下所有表的备份语句,也就是生成一个bak.sh
文件,其内存放的是一条条的备份语句,完事使用sh bak.sh
就能批量的执行备份语句得到最终的备份文件。这整个流程该怎么做?
首先要先了解一个备份命令:
-- 注意其中的空格也是特殊字符(分隔符) mysqldump -uroot -p123 world city >/bak/world_city.sql -- 上面的例子是生成world.city表的备份,并且将备份导出到/bak/world_city.sql中
查询出来全部(这里以指定数据库为例)数据库下所有表,并且使用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)
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