首页 > 代码库 > 视图之二--视图中数据的更新
视图之二--视图中数据的更新
视图不是表，视图里面的数据是通过sql语句去表中查询得到的。当表中的数据发送更改之后，视图里的数据也会发生相应的更改。所以我么一般有两种方式更新视图里面的数据：一是更新表中的数据，从而间接地更新视图中的数据；二是直接写sql已经更新视图。第一种是方式是一定可以的，第二中方法，能否可行取决于视图的具体结构。以下对第二种方法进行分析。
表结构如下：
1 -- ---------------------------- 2 -- 学生表 3 -- ---------------------------- 4 DROP TABLE IF EXISTS `student`; 5 CREATE TABLE `student` ( 6 `id` varchar(255) NOT NULL, 7 `name` varchar(255) NOT NULL, 8 `age` int(11) DEFAULT NULL, 9 `password` varchar(255) DEFAULT NULL,10 PRIMARY KEY (`id`)11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;12 13 -- ----------------------------14 -- 学生记录15 -- ----------------------------16 INSERT INTO `student` VALUES (‘10001‘, ‘张三‘, ‘20‘, ‘zhang123‘);17 INSERT INTO `student` VALUES (‘10002‘, ‘李四‘, ‘21‘, ‘lisi123‘);18 INSERT INTO `student` VALUES (‘10003‘, ‘王五‘, ‘22‘, ‘wang123‘);19 -- ----------------------------20 -- 学生成绩表21 -- ----------------------------22 DROP TABLE IF EXISTS `course`;23 CREATE TABLE `course` (24 `id` int(11) NOT NULL AUTO_INCREMENT,25 `stu_no` varchar(255) NOT NULL,26 `name` varchar(255) NOT NULL,27 `score` int(11) NOT NULL,28 PRIMARY KEY (`id`),29 KEY `id_fk` (`stu_no`),30 CONSTRAINT `id_fk` FOREIGN KEY (`stu_no`) REFERENCES `student` (`id`)31 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;32 33 -- ----------------------------34 -- 学生成绩记录35 -- ----------------------------36 INSERT INTO `course` VALUES (‘1‘, ‘10001‘, ‘c++‘, ‘70‘);37 INSERT INTO `course` VALUES (‘2‘, ‘10001‘, ‘java‘, ‘80‘);38 INSERT INTO `course` VALUES (‘3‘, ‘10001‘, ‘lisp‘, ‘90‘);39 INSERT INTO `course` VALUES (‘4‘, ‘10002‘, ‘c++‘, ‘75‘);40 INSERT INTO `course` VALUES (‘5‘, ‘10002‘, ‘java‘, ‘80‘);41 INSERT INTO `course` VALUES (‘6‘, ‘10003‘, ‘lisp‘, ‘91‘);42 INSERT INTO `course` VALUES (‘7‘, ‘10003‘, ‘c++‘, ‘73‘);
一、视图只包含一张表的字段
1、包含全部字段
视图定义如下：
DROP VIEW IF EXISTS t_student;CREATE VIEW t_student AS SELECT * FROM studentWITH CHECK OPTION;
该视图是查询student表中的所有记录，进行更新的语句如下。
1 INSERT INTO `t_student` VALUES (‘10004‘, ‘赵六‘, 22, ‘wang123‘); -- 插入时如果其他未设置的字段有默认值，可以插入，如果没有就报错2 UPDATE t_student SET name=‘赵六2‘ where id=‘10004‘; -- 更新可以 3 DELETE FROM t_student WHERE id = ‘10004‘; -- 删除也可以
UPDATE，INSERT，DELETE，都是可以的。INSERT的时候，如果之增加部分字段的值，如果未增加的字段没有默认值，且不能为空，则报错。
2、带有id的部分字段
视图定义如下：
CREATE OR REPLACE VIEW t_student2 AS SELECT id, name, age FROM student;
该视图查询student表中的id,name,age字段的记录，进行更新的语句如下：
INSERT INTO t_student2 VALUES ( 4,‘wangwu‘, 30); -- 插入时如果其他字段有默认值，可以插入，如果没有就报错UPDATE t_student2 SET name=‘王五2‘ where id=‘4‘; -- 更新可以DELETE FROM t_student2 WHERE id = 4; -- 删除也可以
由于student表中password字段默认值为null，所以INSERT的时候password没有赋值，也是可以增加的，UPDATE和DELETE也是成功的。
3、不带有id的部分字段
视图定义如下：
CREATE OR REPLACE VIEW t_student3 AS SELECT name, age FROM student;
该视图查询student表中的name,age字段的记录，进行更新的语句如下：
1 INSERT INTO t_student3 VALUES (‘wangwu‘, 30); -- id 没有默认值，不能插入2 UPDATE t_student3 SET age=40 where name=‘wangwu‘; -- 更新可以3 DELETE FROM t_student3 WHERE name = ‘wangwu‘; -- 删除可以
INSERT的时候id没有默认值，所以失败。UPDATE和DELETE都成功。
二、视图包含两张表
1、使用了group by分组查询
查询所有人各科成绩
CREATE OR REPLACE VIEW t_student_score AS -- 创建或者替换视图SELECT s.name, sum(if(a.name = ‘c++‘, score, 0)) as c, sum(if(a.name = ‘java‘, score, 0)) as java, sum(if(a.name = ‘lisp‘, score, 0)) as lispFROM course AS a, student s WHERE a.stu_no = s.idGROUP BY a.stu_no;
要执行的更新语句如下：
1 INSERT INTO t_student_score VALUES (‘wangwu‘, 30, 40, 50); -- [Err] 1471 - The target table t_student_score of the INSERT is not INSERTable-into2 UPDATE t_student_score SET c=40 where name=‘张三‘; -- [Err] 1288 - The target table t_student_score of the UPDATE is not updatable3 DELETE FROM t_student_score WHERE name = ‘张三‘; -- [Err] 1288 - The target table t_student_score of the DELETE is not updatable4
第一行INSERT，增加name,以及各科成绩。因为视图中的记录使用到了分组语句进行查询，索引无法进行增加，UPDATE和DELETE也一样会报错。
2、没有使用group by等分组查询
视图的定义如下：
CREATE OR REPLACE VIEW t_student_score2 ASSELECT c.id AS id, c.stu_no AS stu_no, s.name AS stu_name, c.name AS course_name, c.score AS scoreFROM course AS c, student AS s WHERE c.stu_no = s.id;
查询student表的id，stu_no，name，course表的name，score。
要进行的更新语句如下：
INSERT INTO t_student_score2 (id, stu_no, course_name, score) VALUES (8,‘10001‘,‘c‘, 80); -- 只能将结果插入单个表中，且插入的自动包含表中的所有不为空的字段（与其他表的关联字段设置的值，也必须是其他表中存在的）UPDATE t_student_score2 SET score = 88 WHERE course_name = ‘c‘; -- 可以更新DELETE FROM t_student_score2 WHERE course_name = ‘c‘; -- 不能删除
对于多表可更新视图，如果是将其插入单个表中，INSERT能够工作。UPDATE也是可以的但不支持DELETE。
三、总结
对于直接用sql语句去更新视图的的时候，有些时候是可以成功，有些时候不会。这 取决于视图的结构。
视图内容是单表的时候，UPDATE，DELETE可以执行成功，对于INSERT如果未设值的字段都有默认值，或者是可以为空的字段的时候可以执行成功，否则会执行失败。对
视图内容是两种或两张表以上的时候，如果使用了分组查询语句的时候，INSERT，updat，DELETE都会执行失败。如果没有使用分组查询语句的时候UPDATE是可以执行成功，DELETE会失败。INSERT只能将结构插入当个表中，所以插入的字段只能都在一张表中，而且未设置的字段都有默认值，或者是可以为空的字段的时候才可以执行成功，否则会执行失败。
视图之二--视图中数据的更新