33.MySQL中的视图

1.简介

视图：是从一个或多个表中导出来的表，它是一种虚拟存在的表，表的结构和数据都依赖于基本表。
视图的优点：

1. 简化查询语句和查询操作，更加方便快捷
2. 安全性高，权限控制更加方便
3. 逻辑数据独立性：屏蔽真实表结构变化带来的影响。

2.创建视图

默认情况下，新创建的视图保存在当前选择的数据库中。
若要指定在某个数据库中创建视图，在创建时应将名称指定为“数据库名.视图名”。
SHOW TABLES的查询结果中包含已经创建的视图。
视图创建后，MySQL会在数据库目录中创建一个“视图名.frm”文件。
准备数据：

CREATE TABLE grade(
    id INT(4) PRIMARY KEY,
    name VARCHAR(36) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO grade VALUES(1,'一班');
INSERT INTO grade VALUES(2,'二班');

CREATE TABLE student(
    sid INT(4) PRIMARY KEY,
    sname VARCHAR(36),
    gid INT(4) NOT NULL
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO student VALUES (1,'stu1',1);
INSERT INTO student VALUES (2,'stu2',2);
INSERT INTO student VALUES (3,'stu3',2);
INSERT INTO student VALUES (4,'stu4',2);
INSERT INTO student VALUES (5,'stu5',1);
INSERT INTO student VALUES (6,'stu6',1);
INSERT INTO student VALUES (7,'stu7',1);
INSERT INTO student VALUES (8,'stu8',2);

查询数据：

SELECT sid,sname,gid FROM student LIMIT 3;

创建view_student视图（CREATE VIEW 视图名 AS SELECT语句）

CREATE VIEW view_student AS
SELECT sid,sname,gid FROM student LIMIT 3;
-- 等价于
CREATE VIEW view_student AS
SELECT * FROM student LIMIT 3;

查询视图：

SELECT * FROM view_student;

删除视图（DROP VIEW 视图名）

DROP VIEW view_student;

在创建视图的SELECT语句中移除LIMIT子句：

CREATE VIEW view_student AS
SELECT * FROM student;

在查询视图时使用LIMIT子句：

SELECT * FROM vie_student LIMIT 3;

自定义列名称：

CREATE VIEW view_student_promo (id, name, gid) AS SELECT sid，sname，gid FROM student；

查询视图：

SELECT * FROM view_student_promo WHERE id <= 3;

多表操作：

CREATE VIEW view_student_grade (sid,sname,gname) AS
SELECT sid,sname,g.name FROM student s
LEFT JOIN grade g ON s.gid=g.id;

查询视图：

SELECT * FROM view_student_grade;

3.修改视图

修改视图：修改数据库中存在的视图的定义。
例如，当基本表中的某些字段发生变化时，视图必须修改才能正常使用。
1）修改已有视图:

CREATE OR REPLACE VIEW view_student AS
SELECT sid,sname FROM student;

查询视图：

SELECT * FROM view_student;

2）使用ALTER VIEW修改视图：

ALTER VIEW view_student AS 
SELECT sname FROM student;

查询视图：

SELECT * FROM view_student;

4.删除视图

语法：

DROP VIEW [IF EXISTS] view_name [, view_name1] …

使用：

DROP VIEW view_student;

删除多个：

DROP VIEW view_student_grade,view_student_promo;

5.视图数据操作

创建视图

CREATE VIEW view_student AS
SELECT * FROM student;

插入数据

INSERT INTO view_student VALUES (9,'stu9',1);

查询如下：

如遇到以下情形，操作可能失败：

1. 操作的视图定义在多张表上。
2. 没有满足视图的基本表对字段的约束条件。
3. 在定义视图的SELECT语句后的字段列表中使用了数学表达式或聚合函数。
4. 在定义视图的SELECT语句中使用了DISTINCT、UNION、TOP、GROUP BY或HAVING子句。

修改数据：

UPDATE view_student SET gid=2 WHERE sid=9;

删除数据：

DELETE FROM view_student WHERE sid=9;