MYSQL视图

最新推荐文章于 2024-12-04 22:19:05 发布

原创最新推荐文章于 2024-12-04 22:19:05 发布 · 213 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#MYSQL视图

学习MySQL 专栏收录该内容

15 篇文章

订阅专栏

本文详细介绍了数据库中视图的创建、查看、修改、查询、更新及删除操作，包括使用WITH CHECK OPTION约束、ALTER VIEW修改视图定义、通过INSERT、UPDATE、DELETE操作视图数据以及DROP VIEW删除视图的方法。

视图的创建

create view stud as select stuid,stuname,stuschool from stu;
select * from stud;
create or replace view stu_view as select * from stu where stubirth>='1998-01-01' with check option;
create view stu_view as select * from stu where stubirth>='1997-01-01' with check option;
create view course_sc as select courseid ,avg(score) as avg_sc from score group by courseid;
create or replace view stu_sc as select stu.stuid,stuname,courseid,score from score join stu on stu.stuid=score.stuid order by score desc;

视图结构的查看与修改

1.查看视图结构

show create view stu_sc;

2.修改视图结构

alter view stu_view as select stuid,stuname,stusex,stubirth from stu where stubirth>='1998-01-01' with check option;
alter view stud as select stuid,stuname,stusex as '性别',stuschool from stu;

3.视图的查询与更新

select stusex,count(*) as number from stu_view group by stusex;
select * from course_sc where courseid regexp '^E';
select stuname,courseid,score from stu_sc where score>=85;

4.视图数据的更新
（1）.数据的插入

insert into stu_view values('20160511020','方琼','女','1998-02-20');
insert into stu_view values('20160511021','刘一','男','1664-03-23');
insert into stu values('20160211070','李明','男','1998-01-01','计算机学院');

(2).数据的修改

update stu_view set stuid='20160511022' where stuname='方琼';
update course_sc set courseid='E2201290' where courseid = 'E2201240';

(3).数据的删除

delete from stu_sc where stuname='王小强';
delete from stu_view where stuname='方琼';

视图的删除

drop view abc;
drop view if exists abc;
drop view stu_sc,stu_view;