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 ;
为什么?我们知道两张表都有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;
- DESC v1;
- SHOW CREATE TABLE v1;
- 视图比表还是有一个关键字的区别:View。查看视图的创建语句的时候可以使用View关键字。
- 视图一旦创建:系统会在视图对应的数据库文件夹下创建一个对应的结构文件:frm文件。
1.3 使用视图
- 使用视图主要为了查询,将视图当做表即可。
- 示例:查看v1,v2,v3视图
SELECT * FROM v1;
SELECT * FROM v2;
SELECT * FROM v3;
- 视图的执行:其实本质就是执行封装的select语句。
1.4 修改视图
- 视图本身不可修改,但是视图的来源是可以修改的。
- 修改视图就是修改视图本身的来源语句(select语句)。
- 基本语法:
alter view 视图名字 as 新的select语句;
ALTER VIEW v1 AS SELECT id,NAME,age,gender FROM my_student;
SELECT * FROM v1;
1.5 删除视图
- 基本语法
drop view 视图名字;
CREATE VIEW v4 AS SELECT * FROM my_student; SHOW TABLES;
DROP VIEW v4; SHOW TABLES;
1.6 视图的意义
- ① 视图可以节省SQL语句:将一条复杂的查询语句使用视图进行保存,以后可以直接对视图进行操作。
- ②数据安全:视图操作是主要针对查询的,如果对视图结构进行处理(删除),不会影响基表数据,相对安全。
- ③视图往往是在大项目中使用,而且是多系统使用:可以对外提供有用的数据,但是隐藏关键(对外来说无用)的数据,这样数据可以相对安全。
- ④视图可以对外提供友好性:不同的视图提供不同的数据,对外好像专门设计一样。
- ⑤视图可以更好(容易)的进行权限控制。
1.7 视图数据操作
- 视图的确可以进行数据写操作,但是是有限制的。
1.7.1 视图的新增数据
- 数据的新增就是直接对视图进行数据新增。
- 多表视图不能新增数据
,,,'java002班','C05');
- 可以向单表视图插入数据:但是视图中包含的字段必须有基表中所有不能为空(或者没默认值)的字段。
-- 给学生增加学号 ) NOT NULL AFTER id ; -- 修改学号 ; ; ; ; ; ; ;
-- 单表视图插入:视图不包含所有不允许为空字段(学号) ,'男');
- 视图是可以插入数据的
SELECT * FROM v2;
SELECT * FROM my_class;
INSERT INTO v2 VALUES(NULL,'C++007班','D13');
SELECT * FROM v2;
SELECT * FROM my_class;
1.7.2 视图的删除数据
- 多表视图不能删除,原因:如果有一个视图能查询到学生和班级信息,那么如果我想删除一个学生的时候,却将班级删除了,于是,这个被删除的班级下的所以学生都没班级了,这就尴尬了。想象一下如下场景:学生转班,当然是先在原来的班级中删除此学生,然后在新的班级中增加此学生,而如果多表视图可以删除,岂不是学生不可以转班了,否则一旦转班,必须将自己原来的班级删除,很可怕的哦。
SELECT * FROM v3;
;
- 单表视图可以删除
;
SELECT * FROM v2;
1.7.3 视图的更新数据
- 理论上不但单表视图还是多表视图都可以更新数据
SELECT * FROM v3;
;
SELECT * FROM v3;
- 更新限制:with check option,如果对视图在新增的时候,限定了某个字段有限制;那么在对视图进行数据更新的时候,系统会进行验证:要保证更新之后,数据依然可以被视图查询出来,否则不让更新。
-- 视图:age字段限制更新 WITH CHECK OPTION; -- 表示视图的数据来源都是年龄大于20岁:where age > 20 -- -- with check option:决定通过视图进行数据更新的时候,不能将已经得到的数据 age > 20 改成小于20 的
SELECT * FROM v4;
; --将视图可以查询到的改成小于20
- 为什么会报错呢?因为你在修改视图可以查询到的,如果你改成小于20,那么视图还可以查询到吗?显然不能,所以,综上所述,如果能保证视图查询到的数据不变,否则,系统不会让你修改。当然,如果你改了一些视图查询不到的数据,那么当然可以了,反正视图查询不到。
UPDATE v4 SET age = 30 WHERE id = 1;
- 当然,你可以改了,但是没有效果,因为在视图中id=1是不存在的,只有id=4和id=7的存在。
1.7.4 视图算法
- 视图算法:系统对视图以及外部查询视图的select语句的一种解析方式。
- 视图算法分为三种:
- ①undefined:未定义,这不是一种实际使用的算法,是一种推卸责任的算法,告诉系统,视图没有定义算法,系统自己看着办。
- ②temptable:临时表算法,系统应该先执行视图的select语句,后执行外部查询语句(此种方式效率低,因为相对合并算法,至少需要查询两次)。
- ③merge:合并算法,系统应该先将视图对应的select语句与外部查询视图的select语句进行合并,然后执行(此种方式,效率高,因为只查询一次)
- 示例:没有指定视图算法,查询每个班身高最高的那个学生。
SELECT * FROM my_student;
-- 不使用视图 SELECT * FROM (SELECT * FROM my_student m ORDER BY m.`height` DESC) temp GROUP BY temp.c_id;
-- 不使用视图 SELECT * FROM my_student WHERE height IN ( SELECT MAX(height) FROM my_student m GROUP BY m.`c_id` );
-- 使用视图 CREATE VIEW v5 AS SELECT * FROM my_student m ORDER BY m.height DESC; SELECT * FROM v5 GROUP BY c_id;
- 看吧,查询结果是不真确的,为什么,是因为视图算法是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;
- 视图算法选择:如果视图的select语句会包含一个查询子句(五子句),而且很有可能顺序比外部的查询要靠后,一定要使用算法temptable,其他情况可以不用指定(默认即可)。
2 数据备份与还原
- 备份:将当前已有的数据或者记录保留。
- 还原:将已经保留的数据恢复到对应的表中。
- 为什么要做备份还原?
- ①防止数据丢失:被盗、误操作。
- ②保护数据记录。
- 数据备份还原的方式有多种:数据表备份,单表数据备份,SQL备份,增量备份。
2.1 数据表备份
- 不需要通过SQL来备份:直接进入到数据库文件夹复制对应的表结构以及数据文件,以后还原的时候,直接将备份的内容放进去即可。
- 数据表备份有前提条件:根据不同的存储引擎有不同的区别。
- 存储引擎:MySQL进行数据存储的方式,主要有两种:innodb和myisam(免费)。
- innodb:只有表结构,数据全部存储在ibdata1文件中。
- myisam:表,数据和索引全都单独分开存储。
CREATE TABLE my_isam( id INT )CHARSET utf8 ENGINE = MYISAM;
- 这种文件复制非常适合myisam存储引擎:直接复制这三个文件即可,然后放到对应的数据库下就可以使用了。
2.2 单表数据备份
- 每次只能备份一张表,只能备份数据(表结构不能备份)。
- 通常的使用:将表中的数据进行导出到文件。
- 备份:从表中选出一部分数据保存到外部的文件中(outfile)。
select */字段 into outfile '文件所在路径' from 数据源; --前提外部文件不存在
SELECT * INTO OUTFILE 'e:/a.txt' FROM my_student;
- 数据还原:将一个在外部保存的数据重新恢复到表中(如果表结构不存在,还原不了)
LOAD DATA INFILE '文件所在路径' INTO TABLE 表名;
LOAD DATA INFILE 'e:/a.txt' INTO TABLE my_student;
2.3 SQL备份
- SQL备份:系统会对表结构以及数据进行处理,变成对应的SQL语句,然后进行备份。
- 还原:只要执行SQL语句即可。
- 备份:mysql没有提供备份指令,需要利用mysql提供的软件:mysqldump.exe。
- mysqldump.exe也是一种客户端,需要操作服务器:必须连接认证。
- mysqldump -hPUP 数据库名字 [数据表名字1 [数据表名字2]] > 外部文件目录(建议使用.sql结尾)
- mysqldump.exe也是一种客户端,需要操作服务器:必须连接认证。
- SQL备份
mysqldump -uroot -proot test > e:test.sql
- 还原:
- 使用mysql.exe客户端还原
mysql -uroot -proot 数据库名字 < 备份文件目录
- 使用SQL指令还原
source 备份文件所在路径
- SQL备份优缺点:
- ①优点:
- 可以备份结构。
- ②缺点:
- 会浪费空间(额外的增加SQL指令)
- ①优点:
2.4 增量备份
- 不是针对数据或者SQL指令进行备份:是针对mysql服务器的日志文件进行备份。
- 增量备份:指定时间段开始进行备份,备份数据不会重复,而且所有的操作都会备份。