MySQL利用视图插入修改删除数据

这里写自定义目录标题

1. 准备

已有数据库stu，数据表student、course、score：

createdatabase stu;use stu;createtable student(
sid char(4)primarykey,
sname varchar(20)notnull,
sex enum('男','女'),
birth date,
grade year,
department enum('信息工程系','化学工程系','机械电子系')default'信息工程系',
addr varchar(50))charset=utf8mb4;createtable course(
cno char(2)primarykey,
cname varchar(20)notnull,startint,
credit float)charset=utf8mb4;createtable  score(
scid intprimarykeyauto_increment,
sid char(4),
cno char(2),
result floatcheck(result >=0and result <=100))charset=utf8mb4;

2.创建视图

createview  v1
asselect sid,sname,sex,department,year(now())-year(birth) age
from student where department='化学工程系';createview  v2
asselect sid,sname,sex,department
from student where department='化学工程系';createview v3
asselect student.sid,sname,course.cno,cname,result
from student,score,course
where student.sid=score.sid and course.cno=score.cno;

3.插入数据

利用v1插入记录：

insert v1(sid,sname,sex,department)values('8888','张敏敏','女','信息工程系');

插入失败，错误提示：
Error Code: 1471. The target table v1 of the INSERT is not insertable-into

利用v2插入记录：

insert v2(sid,sname,sex,department)values('8888','张敏敏','女','信息工程系');

插入成功。
v1和v2相比，v1比v2多了个字段计算年龄的表达式year(now())-year(birth)。
插入失败可能原因1：定义视图的select语句后面有数学表达式或聚合函数。

利用v3插入记录

insert v3 values('8888','张敏敏','01','大学英语',90);

Error Code: 1394. Can not insert into join view ‘stu.v3’ without fields list

insert v3(cno,cname)values('55','人工智能');

插入成功
插入失败可能原因2：视图是定义在多个表上，通过视图插入一条记录可能是在多个表中插入记录。

定义视图v4，并在v4上定义视图v5、v6

createview v4
asselect sid,cno,result
from score
where result>=70;createview v5
asselect sid,cno,result
from v4
where result<=90withlocalcheckoption;createview v6
asselect sid,cno,result
from v4
where result<=90withcascadedcheckoption;

利用v4插入记录，不管是否满足result>=70，均可插入：

insert v4 values('0404','01',60);insert v4 values('0404','02',80);

利用v5插入记录：

insert v5 values('0405','01',60);insert v5 values('0405','02',95);insert v5 values('0405','03',80);

成绩为60、80均可插入，95插入失败：Error Code: 1369. CHECK OPTION failed ‘stu.v5’。因为在v5定义的时候有with local check option，必须满足视图v5本身定义的条件result<=90，对v4的条件result>=70不做要求。
with local check option 只要求满足该视图本身定义的条件

利用v6插入数据：

insert v6 values('0406','01',60);insert v6 values('0406','02',95);insert v6 values('0406','03',80);

只有成绩为80的记录插入成功，60和95均失败，因为在定义v6的时候有with cascaded check option，所以v6和v4定义的条件都必须满足。
with cascaded check option 要求满足所有视图和表定义的条件

插入失败可能原因3：插入记录不满足视图定义的基本条件。
在定义视图时select语句中含distinct、union、top、group by等短语时，插入记录也可能失败，不再举例。

4.修改记录、删除记录

利用v2修改学号8888为6688：

update v1 set sid='6688'where sid='8888';

不报错，不修改数据。提示为：0 row(s) affected Rows matched: 0 Changed: 0 Warnings: 0

利用v2删除8888记录：
delete from v2 where sid=‘8888’;
不报错、不删除。
如果修改v2定义去掉department=‘化学工程系’，则允许修改、允许删除：

createorreplaceview  v2
asselect sid,sname,sex,department
from student;update v2 set sid='6688'where sid='8888';deletefrom v2 where sid='6688';

利用视图修改数据、删除数据时，不满足视图定义条件都不能修改或删除。不报错

5.总结：

利用视图插入数据时：
1.定义视图的select语句后面有数学表达式或聚合函数；
2.视图是定义在多个表上，通过视图插入一条记录是在多个表中插入记录；
3.视图定义时带‘with… check option’，不满足视图定义的条件；
4.视图带distinct、union、top、group by等短语时；
都可能导致插入记录失败。
利用视图修改数据、删除数据时，不满足视图定义条件都不能修改或删除。