MySQL （七）

1 视图

• 视图：View，是一种有结构（有行有列）但是没结果（结构中不真实存放的数据）的虚拟表，虚拟表的结构来源不是自己定义，而是从对应的基表中产生（视图的数据来源）。

 

• 示例脚本：

CREATE TABLE my_class(    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',    c_name VARCHAR(20) NOT NULL COMMENT '班级名字',    room VARCHAR(20) 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 VARCHAR(20) NOT NULL COMMENT '学生姓名',    age INT NOT NULL COMMENT '学生年龄',    gender VARCHAR(2) NOT NULL COMMENT '学生性别',    c_id INT COMMENT '外键' ,    height INT COMMENT '身高',    CONSTRAINT fk_c_id FOREIGN KEY (c_id) REFERENCES my_class (id)    );-- 插入学生信息INSERT INTO my_student VALUES (NULL,'张三',20,'男',1,180);INSERT INTO my_student VALUES (NULL,'李四',18,'女',2,170);INSERT INTO my_student VALUES (NULL,'王五',19,'女',2,165);INSERT INTO my_student VALUES (NULL,'赵六',25,'男',3,190);INSERT INTO my_student VALUES (NULL,'田七',14,'女',1,155);INSERT INTO my_student VALUES (NULL,'王八',19,'男',3,160);INSERT INTO my_student VALUES (NULL,'陈九',26,'男',NULL,195);

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 视图的新增数据

•  数据的新增就是直接对视图进行数据新增。　

 

• 多表视图不能新增数据 

INSERT INTO v3 VALUES (NULL,'呵呵',50,'女',1,180,'java002班','C05');

• 可以向单表视图插入数据：但是视图中包含的字段必须有基表中所有不能为空(或者没默认值)的字段。

-- 给学生增加学号ALTER TABLE my_student ADD number VARCHAR(5) NOT NULL AFTER id ;-- 修改学号UPDATE my_student SET number = '001' WHERE id =1;UPDATE my_student SET number = '002' WHERE id =2;UPDATE my_student SET number = '003' WHERE id =3;UPDATE my_student SET number = '004' WHERE id =4;UPDATE my_student SET number = '005' WHERE id =5;UPDATE my_student SET number = '006' WHERE id =6;UPDATE my_student SET number = '007' WHERE id =7;

-- 单表视图插入：视图不包含所有不允许为空字段（学号）INSERT INTO v1 VALUES (NULL,'张三丰',120,'男');

• 视图是可以插入数据的

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;

DELETE FROM v3 WHERE id = 7;

 

 

• 单表视图可以删除 

DELETE FROM v2 WHERE id = 4;

SELECT * FROM v2;

 

 1.7.3 视图的更新数据

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

SELECT * FROM v3;

UPDATE v3 SET c_id = 1 WHERE id = 7;

SELECT * FROM v3;

 

• 更新限制：with check option，如果对视图在新增的时候，限定了某个字段有限制；那么在对视图进行数据更新的时候，系统会进行验证：要保证更新之后，数据依然可以被视图查询出来，否则不让更新。 

-- 视图：age字段限制更新CREATE VIEW v4 AS SELECT * FROM my_student WHERE age >20 WITH CHECK OPTION;-- 表示视图的数据来源都是年龄大于20岁：where age > 20-- -- with check option：决定通过视图进行数据更新的时候，不能将已经得到的数据 age > 20 改成小于20 的

SELECT * FROM v4;

UPDATE v4 SET age = 18 WHERE id = 4; --将视图可以查询到的改成小于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结尾）　　　　

 

• SQL备份

mysqldump -uroot -proot test > e:test.sql

 

• 还原：
  • 使用mysql.exe客户端还原　　

mysql -uroot -proot 数据库名字 < 备份文件目录

• • 使用SQL指令还原　　

source 备份文件所在路径

 

• SQL备份优缺点：
  • ①优点：
    • 可以备份结构。
  • ②缺点：
    • 会浪费空间（额外的增加SQL指令）　　

 

2.4 增量备份 

•  不是针对数据或者SQL指令进行备份：是针对mysql服务器的日志文件进行备份。

 

• 增量备份：指定时间段开始进行备份，备份数据不会重复，而且所有的操作都会备份。