视图之一--创建简单的视图

一、视图

　　视图是数据库中的一个虚拟表，其内容由查询语句查询出来的。就像真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。它的行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

　　视图优点如下：

• 视点集中

　　视图集中即是使用户只关心它感兴趣的某些特定数据和他们所负责的特定任务。这样通过只允许用户看到视图中所定义的数据而不是视图引用表中的数据而提高了数据的安全性。

• 简化操作

　　视图大大简化了用户对数据的操作。因为在定义视图时，若视图本身就是一个复杂查询的结果集，这样在每一次执行相同的查询时，不必重新写这些复杂的查询语句，只要一条简单的查询视图语句即可。可见视图向用户隐藏了表与表之间的复杂的连接操作。

• 定制数据

　　视图能够实现让不同的用户以不同的方式看到不同或相同的结果集。因此，当有许多不同水平的用户共用同一数据库时，这显得极为重要。

• 合并分割数据

　　在有些情况下，由于表中数据量太大，故在表的设计时常将表进行水平分割或垂直分割，但表的结构的变化却对应用程序产生不良的影响。如果使用视图就可以重新保持原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据。

• 安全性

　　视图可以作为一种安全机制。通过视图用户只能查看和修改他们所能看到的数据。其它数据库或表既不可见也不可以访问。如果某一用户想要访问视图的结果集，必须授予其访问权限。视图所引用表的访问权限与视图权限的设置互不影响。

二、实例

　　以下示例表结构如下：

-- ----------------------------
-- 学生表
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stu_no` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`stu_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- 学生记录
-- ----------------------------
INSERT INTO `student` VALUES ('10001', '张三', '20', 'zhang123');
INSERT INTO `student` VALUES ('10002', '李四', '21', 'lisi123');
INSERT INTO `student` VALUES ('10003', '王五', '22', 'wang123');
-- ----------------------------
-- 学生成绩表
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_fk` (`stu_no`),
  CONSTRAINT `id_fk` FOREIGN KEY (`stu_no`) REFERENCES `student` (`stu_no`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- 学生成绩记录
-- ----------------------------
INSERT INTO `course` VALUES ('1', '10001', 'c++', '70');
INSERT INTO `course` VALUES ('2', '10001', 'java', '80');
INSERT INTO `course` VALUES ('3', '10001', 'lisp', '90');
INSERT INTO `course` VALUES ('4', '10002', 'c++', '75');
INSERT INTO `course` VALUES ('5', '10002', 'java', '80');
INSERT INTO `course` VALUES ('6', '10003', 'lisp', '91');
INSERT INTO `course` VALUES ('7', '10003', 'c++', '73');

　　学生表与学生成绩表通过stu_no学生学号这个字段进行关联。

　　1、视图创建

　　语法如下：

　　CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]
       VIEW 视图名 [(属性清单)]
       AS SELECT 语句
       [WITH [CASCADED|LOCAL] CHECK OPTION];

　　ALGORITHM子句:

　　　　ALGORITHM子句是可选的,是对标准SQL的MySQL扩展。ALGORITHM可取三个值：MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。

 

　　对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。

 

　　对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。

　　对于UNDEFINED，MySQL将选择所要使用的算法。如果可能，它应该倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。明确选择TEMPTABLE的1个原因在于，创建临时表之后、并在完成语句处理之前，能够释放基表上的锁定。与MERGE算法相比，锁定释放的速度更快，这样，使用视图的其他客户端不会被屏蔽过长时间。

　　WITH CHECK OPTION可用于对于可更新视图，可给定WITH CHECK OPTION子句来防止插入或更新行，除非作用在行上的select_statement中的WHERE子句为“真”。意思是说更新后，所更新的结果是否还会在视图中存在。如果更新后的值不在视图范围内，就不允许更新。如果创建视图的时候.没有加上with check option，更新视图中的某项数据的话，mysql并不会进行有效性检查。删掉了就删掉了。在视图中将看不到了。

　　　例：对于一下视图使用WITH CHECK OPTION

CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
WITH CHECK OPTION;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
WITH LOCAL CHECK OPTION;
CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
WITH CASCADED CHECK OPTION;

　　v1视图的内容是t1表中的，v2视图的内容是视图v1中的，v3视图的内容是视图v1中的，但是v3视图定义的时候使用了WITH CHECK OPTION。现在我们执行一些语句

INSERT INTO v3 VALUES (2); -- [Err] 1369 - CHECK OPTION failed 'test.v3'
INSERT INTO v2 VALUES (2);

　　执行第一行的时候会报错。v3视图定义的时候使用了WITH CHECK OPTION，索引v3具有CASCADED检查选项。因此，不仅会针对它自己的检查对插入项进行测试，也会针对基本视图的检查对插入项进行测试。这里对v1进行了检查，v1视图中不符合，所以插入失败。

　　执行第二行会成功。v2具有LOCAL检查选项，因此，仅会针对v2检查对插入项进行测试。因为v2定义的时候没有时候WITH CHECK OPTION，索引v2不会对v1继续检查，索引插入成功。

 　　查询学生的最高科目成绩。

DROP VIEW IF EXISTS highest_course;
CREATE VIEW highest_course AS
SELECT s.NAME AS stuname, c.NAME AS course,    max(c.score) AS score
FROM
        course AS c, student AS s
WHERE
    s.stu_no = c.stu_no
GROUP BY
    c.stu_no;

　　执行DESCRIBE highest_course;可以查看这个视图的结构，如字段，字段大小是否为空等。执行结果如下：

　

　　执行SELECT * FROM highest_course;即可查询视图数据内容，结果如下：

 　　我们也可以查询名称为张三的最高科目成绩，执行SELECT * FROM highest_course WHERE stuname='张三';即可查询视图内容，结果如下：

　　

 　　2、视图结构的修改

　　语法如下：

　　ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
　   VIEW view_name [(column_list)]AS
　　select_statement
　　[WITH [CASCADED | LOCAL] CHECK OPTION]

 　　在原来的视图增加一个studentno字段

-- 修改mysql视图
ALTER VIEW highest_course  AS
SELECT s.stu_no AS studentno, s.NAME AS stuname, c.NAME AS course,    max(c.score) AS score
FROM
        course AS c, student AS s
WHERE
    s.stu_no = c.stu_no
GROUP BY
    c.stu_no;
DESCRIBE highest_course;

修改mysql

　　执行第10行之后视图结构如下：

 　　已经增加了一个字段studentno。

　　3、删除视图

DROP VIEW highest_course;

　　4、关于查看视图的相关语句

SELECT * FROM highest_course; -- 查询视图的所有内容
DESCRIBE highest_course; -- 查询视图的每个字段的信息
SHOW CREATE VIEW highest_course; -- 查询视图的创建语句

三、视图主要点

　　视图定义服从下述限制：

• SELECT语句不能包含FROM子句中的子查询。
• SELECT语句不能引用系统或用户变量。
• SELECT语句不能引用预处理语句参数。
• 在存储过程内，定义不能引用子程序参数或局部变量。
• 在定义中引用的表或视图必须存在。但是，创建了视图后，能够删除定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。
• 在定义中不能引用临时表，不能创建临时视图。
• 在视图定义中命名的表必须已存在。
• 不能将触发程序与视图关联在一起。
• 在视图定义中使用了ORDER BY，但是调用的时候使用了新的ORDER BY,那么视图中定义的ORDER BY 将失效，会按照调用时候的ORDER BY排序。

　　对于更新视图的内容，我们可以通过更新表数据的方式从而间接地去更新视图内容。

　　视图的内容是否更新与WITH CHECK OPTION子句有关，有些视图是可以更新，有些视图是不能。也与视图的可更新性可能会受到系统变量updatable_views_with_limit的值的影响。