MySQL (七)--视图、数据库备份和还原

1 视图

  • 视图:View,是一种有结构(有行有列)但是没结果(结构中不真实存放的数据)的虚拟表,虚拟表的结构来源不是自己定义,而是从对应的基表中产生(视图的数据来源)。
  • 示例脚本:
CREATE TABLE my_class(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    c_name ) NOT NULL COMMENT '班级名字',
    room ) NOT NULL COMMENT '班级所在教室'
);
-- 插入班级信息
INSERT INTO my_class VALUES (NULL,'java001班','A01');
INSERT INTO my_class VALUES (NULL,'Linux003班','C15');
INSERT INTO my_class VALUES (NULL,'c005班','B23');

-- 创建学生表
CREATE TABLE my_student(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    NAME ) NOT NULL COMMENT '学生姓名',
    age INT NOT NULL COMMENT '学生年龄',
    gender ) NOT NULL COMMENT '学生性别',
    c_id INT COMMENT '外键' ,
    height INT COMMENT '身高',
    CONSTRAINT fk_c_id FOREIGN KEY (c_id) REFERENCES my_class (id)
);
-- 插入学生信息
,,);
,,);
,,);
,,);
,,);
,,);
,);

1.1 创建视图

  • 基本语法
create view 视图名字 as  select 语句;
-- select 语句可以是普通查询;可以是连接查询;可以是联合查询;可以是子查询
  • 创建单表视图:基表只有一个
CREATE VIEW v1 AS SELECT * FROM my_student;
CREATE VIEW v2 AS SELECT * FROM my_class;
  • 创建多表视图:基表至少两个
CREATE VIEW v3 AS SELECT * FROM my_student AS s LEFT OUTER JOIN my_class AS c ON s.c_id = c.id ;

MySQL (七)--视图、数据库备份和还原

为什么?我们知道两张表都有id字段,而我们又知道的是视图是有结构但没结果的虚拟表,既然它是虚拟表,怎么可能一张表有两个相同的字段呢?

CREATE VIEW v3 AS SELECT s.*,c.c_name,c.room FROM my_student AS s LEFT OUTER JOIN my_class AS c ON s.c_id = c.id ;

1.2 查看视图

  • 查看视图:查看视图的结构
  • 视图是一张虚拟表,那么表的所有查看方式都适用于表。
    • show tables;  

MySQL (七)--视图、数据库备份和还原

    • DESC v1;    

MySQL (七)--视图、数据库备份和还原

    • SHOW CREATE TABLE v1;  

MySQL (七)--视图、数据库备份和还原

    • 视图比表还是有一个关键字的区别:View。查看视图的创建语句的时候可以使用View关键字。  

MySQL (七)--视图、数据库备份和还原

  • 视图一旦创建:系统会在视图对应的数据库文件夹下创建一个对应的结构文件:frm文件。

MySQL (七)--视图、数据库备份和还原

1.3 使用视图

  • 使用视图主要为了查询,将视图当做表即可。
  • 示例:查看v1,v2,v3视图
SELECT * FROM v1;

MySQL (七)--视图、数据库备份和还原

SELECT * FROM v2;

MySQL (七)--视图、数据库备份和还原

SELECT * FROM v3;

MySQL (七)--视图、数据库备份和还原

  • 视图的执行:其实本质就是执行封装的select语句。

1.4 修改视图

  • 视图本身不可修改,但是视图的来源是可以修改的。
  • 修改视图就是修改视图本身的来源语句(select语句)。
  • 基本语法:
alter view 视图名字 as 新的select语句;
ALTER VIEW v1 AS SELECT id,NAME,age,gender FROM my_student;
SELECT * FROM v1;

MySQL (七)--视图、数据库备份和还原

1.5 删除视图

  • 基本语法
drop view 视图名字;
CREATE VIEW v4 AS SELECT * FROM my_student;
SHOW TABLES;

MySQL (七)--视图、数据库备份和还原

DROP VIEW v4;
SHOW TABLES;

MySQL (七)--视图、数据库备份和还原

1.6 视图的意义

  • ① 视图可以节省SQL语句:将一条复杂的查询语句使用视图进行保存,以后可以直接对视图进行操作。
  • ②数据安全:视图操作是主要针对查询的,如果对视图结构进行处理(删除),不会影响基表数据,相对安全。
  • ③视图往往是在大项目中使用,而且是多系统使用:可以对外提供有用的数据,但是隐藏关键(对外来说无用)的数据,这样数据可以相对安全。
  • ④视图可以对外提供友好性:不同的视图提供不同的数据,对外好像专门设计一样。
  • ⑤视图可以更好(容易)的进行权限控制。

1.7 视图数据操作

  • 视图的确可以进行数据写操作,但是是有限制的。

1.7.1 视图的新增数据

  • 数据的新增就是直接对视图进行数据新增。 
  • 多表视图不能新增数据
,,,'java002班','C05');

MySQL (七)--视图、数据库备份和还原

  • 可以向单表视图插入数据:但是视图中包含的字段必须有基表中所有不能为空(或者没默认值)的字段。
-- 给学生增加学号
) NOT NULL AFTER id ;
-- 修改学号
;
;
;
;
;
;
;
-- 单表视图插入:视图不包含所有不允许为空字段(学号)
,'男');

MySQL (七)--视图、数据库备份和还原

  • 视图是可以插入数据的
SELECT * FROM v2;

MySQL (七)--视图、数据库备份和还原

SELECT * FROM my_class;

MySQL (七)--视图、数据库备份和还原

INSERT INTO v2 VALUES(NULL,'C++007班','D13');
SELECT * FROM v2;

MySQL (七)--视图、数据库备份和还原

SELECT * FROM my_class;

MySQL (七)--视图、数据库备份和还原

1.7.2 视图的删除数据

  • 多表视图不能删除,原因:如果有一个视图能查询到学生和班级信息,那么如果我想删除一个学生的时候,却将班级删除了,于是,这个被删除的班级下的所以学生都没班级了,这就尴尬了。想象一下如下场景:学生转班,当然是先在原来的班级中删除此学生,然后在新的班级中增加此学生,而如果多表视图可以删除,岂不是学生不可以转班了,否则一旦转班,必须将自己原来的班级删除,很可怕的哦。
SELECT * FROM v3;

MySQL (七)--视图、数据库备份和还原

;

MySQL (七)--视图、数据库备份和还原

  • 单表视图可以删除
;
SELECT * FROM v2;

MySQL (七)--视图、数据库备份和还原

1.7.3 视图的更新数据

  • 理论上不但单表视图还是多表视图都可以更新数据
SELECT * FROM v3;

MySQL (七)--视图、数据库备份和还原

 ;
SELECT * FROM v3;

MySQL (七)--视图、数据库备份和还原

  • 更新限制:with check option,如果对视图在新增的时候,限定了某个字段有限制;那么在对视图进行数据更新的时候,系统会进行验证:要保证更新之后,数据依然可以被视图查询出来,否则不让更新。
-- 视图:age字段限制更新
 WITH CHECK OPTION;
-- 表示视图的数据来源都是年龄大于20岁:where age > 20
-- -- with check option:决定通过视图进行数据更新的时候,不能将已经得到的数据 age > 20 改成小于20 的
SELECT * FROM v4;

MySQL (七)--视图、数据库备份和还原

 ; --将视图可以查询到的改成小于20

MySQL (七)--视图、数据库备份和还原

  • 为什么会报错呢?因为你在修改视图可以查询到的,如果你改成小于20,那么视图还可以查询到吗?显然不能,所以,综上所述,如果能保证视图查询到的数据不变,否则,系统不会让你修改。当然,如果你改了一些视图查询不到的数据,那么当然可以了,反正视图查询不到。
UPDATE v4 SET age = 30 WHERE id = 1;

MySQL (七)--视图、数据库备份和还原

  • 当然,你可以改了,但是没有效果,因为在视图中id=1是不存在的,只有id=4和id=7的存在。

1.7.4 视图算法

  • 视图算法:系统对视图以及外部查询视图的select语句的一种解析方式。
  • 视图算法分为三种:
    • ①undefined:未定义,这不是一种实际使用的算法,是一种推卸责任的算法,告诉系统,视图没有定义算法,系统自己看着办。
    • ②temptable:临时表算法,系统应该先执行视图的select语句,后执行外部查询语句(此种方式效率低,因为相对合并算法,至少需要查询两次)。
    • ③merge:合并算法,系统应该先将视图对应的select语句与外部查询视图的select语句进行合并,然后执行(此种方式,效率高,因为只查询一次)    
  • 示例:没有指定视图算法,查询每个班身高最高的那个学生。
SELECT * FROM my_student;

MySQL (七)--视图、数据库备份和还原

-- 不使用视图
SELECT * FROM (SELECT * FROM my_student m ORDER BY m.`height` DESC) temp GROUP BY temp.c_id; 

MySQL (七)--视图、数据库备份和还原

-- 不使用视图
SELECT * FROM my_student WHERE height IN ( SELECT MAX(height) FROM my_student m GROUP BY m.`c_id` );  

MySQL (七)--视图、数据库备份和还原

-- 使用视图
CREATE VIEW v5 AS SELECT * FROM my_student m ORDER BY m.height DESC;
SELECT * FROM v5 GROUP BY c_id;

MySQL (七)--视图、数据库备份和还原

  • 看吧,查询结果是不真确的,为什么,是因为视图算法是undefined。
  • 所以,指定视图算法吧
create algorithm=指定算法 view 视图名字 as select语句;
-- 使用视图
CREATE ALGORITHM=TEMPTABLE VIEW v5 AS SELECT * FROM my_student m ORDER BY m.height DESC;
SELECT * FROM v5 GROUP BY c_id;

MySQL (七)--视图、数据库备份和还原

  • 视图算法选择:如果视图的select语句会包含一个查询子句(五子句),而且很有可能顺序比外部的查询要靠后,一定要使用算法temptable,其他情况可以不用指定(默认即可)。

2 数据备份与还原

  • 备份:将当前已有的数据或者记录保留。
  • 还原:将已经保留的数据恢复到对应的表中。
  • 为什么要做备份还原?
    • ①防止数据丢失:被盗、误操作。
    • ②保护数据记录。  
  • 数据备份还原的方式有多种:数据表备份,单表数据备份,SQL备份,增量备份。

2.1 数据表备份

  • 不需要通过SQL来备份:直接进入到数据库文件夹复制对应的表结构以及数据文件,以后还原的时候,直接将备份的内容放进去即可。
  • 数据表备份有前提条件:根据不同的存储引擎有不同的区别。
  • 存储引擎:MySQL进行数据存储的方式,主要有两种:innodb和myisam(免费)。
    • innodb:只有表结构,数据全部存储在ibdata1文件中。   

MySQL (七)--视图、数据库备份和还原

MySQL (七)--视图、数据库备份和还原

    • myisam:表,数据和索引全都单独分开存储。  
CREATE TABLE my_isam(
    id INT
)CHARSET utf8 ENGINE = MYISAM;

MySQL (七)--视图、数据库备份和还原

  • 这种文件复制非常适合myisam存储引擎:直接复制这三个文件即可,然后放到对应的数据库下就可以使用了。

2.2 单表数据备份

  • 每次只能备份一张表,只能备份数据(表结构不能备份)。
  • 通常的使用:将表中的数据进行导出到文件。
  • 备份:从表中选出一部分数据保存到外部的文件中(outfile)。
select */字段 into outfile '文件所在路径' from 数据源; --前提外部文件不存在
SELECT * INTO OUTFILE 'e:/a.txt' FROM my_student;

MySQL (七)--视图、数据库备份和还原

MySQL (七)--视图、数据库备份和还原

  • 数据还原:将一个在外部保存的数据重新恢复到表中(如果表结构不存在,还原不了)
LOAD DATA INFILE '文件所在路径' INTO TABLE 表名;
LOAD DATA INFILE 'e:/a.txt' INTO TABLE my_student;

MySQL (七)--视图、数据库备份和还原

2.3 SQL备份

  • SQL备份:系统会对表结构以及数据进行处理,变成对应的SQL语句,然后进行备份。
  • 还原:只要执行SQL语句即可。
  • 备份:mysql没有提供备份指令,需要利用mysql提供的软件:mysqldump.exe。
    • mysqldump.exe也是一种客户端,需要操作服务器:必须连接认证。
      • mysqldump -hPUP 数据库名字 [数据表名字1 [数据表名字2]]  > 外部文件目录(建议使用.sql结尾)    
  • SQL备份
mysqldump -uroot -proot test > e:test.sql

MySQL (七)--视图、数据库备份和还原

  • 还原:
    • 使用mysql.exe客户端还原  
mysql -uroot -proot 数据库名字 < 备份文件目录
    • 使用SQL指令还原  
source 备份文件所在路径
  • SQL备份优缺点:
    • ①优点:
      • 可以备份结构。
    • ②缺点:
      • 会浪费空间(额外的增加SQL指令)  

2.4 增量备份

  • 不是针对数据或者SQL指令进行备份:是针对mysql服务器的日志文件进行备份。
  • 增量备份:指定时间段开始进行备份,备份数据不会重复,而且所有的操作都会备份。
上一篇:[转]MySQL数据库备份和还原的常用命令小结


下一篇:Delphi 中DataSnap技术网摘