快捷導(dǎo)航

MySQL如何使用視圖插入修改刪除數(shù)據(jù)

更新時(shí)間：2023年06月06日 16:29:26 作者：喜歡的牡丹花

視圖除了進(jìn)行查詢記錄外,也可以利用視圖進(jìn)行插入、更新、刪除記錄的操作,減少對(duì)基表中信息的直接操作,提高了數(shù)據(jù)的安全性,下面這篇文章主要給大家介紹了關(guān)于MySQL如何使用視圖插入修改刪除數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下

1. 準(zhǔn)備

已有數(shù)據(jù)庫(kù)stu，數(shù)據(jù)表student、course、score：

create database stu;
use stu;

create table student(
sid char(4) primary key,
sname varchar(20) not null,
sex enum('男','女'),
birth date,
grade year,
department enum('信息工程系','化學(xué)工程系','機(jī)械電子系') default '信息工程系',
addr varchar(50)
)charset=utf8mb4;

create table course(
cno char(2) primary key,
cname varchar(20) not null,
start int,
credit float
)charset=utf8mb4;

create table  score(
scid int primary key auto_increment ,
sid char(4),
cno char(2),
result float  check(result >= 0 and result <= 100)
)charset=utf8mb4;

2.創(chuàng)建視圖

create view  v1
as 
select sid,sname,sex,department,year(now())-year(birth) age
from student where department='化學(xué)工程系';

create view  v2
as 
select sid,sname,sex,department
from student where department='化學(xué)工程系';

create view v3
as 
select student.sid,sname,course.cno,cname,result
from student,score,course
where student.sid=score.sid and course.cno=score.cno;

3.插入數(shù)據(jù)

利用v1插入記錄：

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

插入失敗，錯(cuò)誤提示：

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多了個(gè)字段計(jì)算年齡的表達(dá)式y(tǒng)ear(now())-year(birth)。

插入失敗可能原因1：定義視圖的select語(yǔ)句后面有數(shù)學(xué)表達(dá)式或聚合函數(shù)。

利用v3插入記錄

insert v3 values('8888','張敏敏','01','大學(xué)英語(yǔ)',90);

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

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

插入成功

插入失敗可能原因2：視圖是定義在多個(gè)表上，通過(guò)視圖插入一條記錄可能是在多個(gè)表中插入記錄。

定義視圖v4，并在v4上定義視圖v5、v6

create  view v4
as
select sid,cno,result
from score
where result>=70 ;

create  view v5
as
select sid,cno,result
from v4
where result<=90 with local check option ;

create  view v6
as
select sid,cno,result
from v4
where result<=90 with cascaded check option ;

利用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);

成績(jī)?yōu)?0、80均可插入，95插入失敗：Error Code: 1369. CHECK OPTION failed ‘stu.v5’。因?yàn)樵趘5定義的時(shí)候有with local check option，必須滿足視圖v5本身定義的條件result<=90，對(duì)v4的條件result>=70不做要求。

with local check option 只要求滿足該視圖本身定義的條件

利用v6插入數(shù)據(jù)：

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

只有成績(jī)?yōu)?0的記錄插入成功，60和95均失敗，因?yàn)樵诙xv6的時(shí)候有with cascaded check option，所以v6和v4定義的條件都必須滿足。

with cascaded check option 要求滿足所有視圖和表定義的條件

插入失敗可能原因3：插入記錄不滿足視圖定義的基本條件。

在定義視圖時(shí)select語(yǔ)句中含distinct、union、top、group by等短語(yǔ)時(shí)，插入記錄也可能失敗，不再舉例。

4.修改記錄、刪除記錄

利用v2修改學(xué)號(hào)8888為6688：

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

不報(bào)錯(cuò)，不修改數(shù)據(jù)。提示為：0 row(s) affected Rows matched: 0 Changed: 0 Warnings: 0

利用v2刪除8888記錄：

delete from v2 where sid=‘8888';

不報(bào)錯(cuò)、不刪除。

如果修改v2定義去掉department=‘化學(xué)工程系’，則允許修改、允許刪除：

create or replace view  v2
as 
select sid,sname,sex,department
from student;
update v2 set sid='6688' where sid='8888';
delete from v2 where sid='6688';

利用視圖修改數(shù)據(jù)、刪除數(shù)據(jù)時(shí)，不滿足視圖定義條件都不能修改或刪除。不報(bào)錯(cuò)