视图之一--创建简单的视图
一、视图
视图是数据库中的一个虚拟表,其内容由查询语句查询出来的。就像真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。它的行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。我们可以向视图添加 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
1 CREATE TABLE t1 (a INT); 2 CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2 3 WITH CHECK OPTION; 4 CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0 5 WITH LOCAL CHECK OPTION; 6 CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0 7 WITH CASCADED CHECK OPTION;
v1视图的内容是t1表中的,v2视图的内容是视图v1中的,v3视图的内容是视图v1中的,但是v3视图定义的时候使用了WITH CHECK OPTION。现在我们执行一些语句
1 INSERT INTO v3 VALUES (2); -- [Err] 1369 - CHECK OPTION failed 'test.v3' 2 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;即可查询视图数据内容,结果如下:
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]AS
select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
在原来的视图增加一个studentno字段
1 -- 修改mysql视图 2 ALTER VIEW highest_course AS 3 SELECT s.stu_no AS studentno, s.NAME AS stuname, c.NAME AS course, max(c.score) AS score 4 FROM 5 course AS c, student AS s 6 WHERE 7 s.stu_no = c.stu_no 8 GROUP BY 9 c.stu_no; 10 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的值的影响。