MySQL之使用视图

创建及查看视图

视图用create view来创建,用drop来删除； create view viewname as select ...from ...where...;

代码语言：javascript复制

#创建热门文章视图：找到阅读量大于500的作者姓名和微信
create view TopArticle as 
select ArticleDetail.title,ArticleDetail.reade_times,Author.name,Author.wechat
from ArticleDetail,Article,Author 
where ArticleDetail.ar_id=Article.ar_id 
and Article.au_id=Author.au_id 
and ArticleDetail.reade_times>500;

#查看视图
select * from TopArticle;

用视图过滤数据

代码语言：javascript复制

#过滤文章数目不为空的数据
create view TemArticle AS
select ar_id,type,author,au_id,articles,qq_group,fans from Article where articles is not null;

#查看TemArticle视图；
select * from TemArticle;

对视图进行增加数据

代码语言：javascript复制

insert into TemArticle (type,author,au_id,articles,qq_group,fans)values('Linux','vivi',2019002,20,727998947,400);
insert into TemArticle (type,author,articles,qq_group,fans)values('perl','vivi',23,727998947,50);
insert into TemArticle (type,author,articles,qq_group,fans)values('pytorch','vivi',25,727998947,50);

对视图进行删除数据

delete from viewname where...;

代码语言：javascript复制

delete from TemArticle where fans='50';

对视图进行更新数据

update viewname set column=value where...;

代码语言：javascript复制

update TemArticle set articles=55 where type='MySQL';

查询视图中的数据

select column from viewname where...;

代码语言：javascript复制

#查找TemArticle视图中文章数不小于50的信息
select ar_id,type,author,au_id,articles,qq_group,fans from TemArticle where articles>=50;

附：Author表

Article表

ArticleDetail表

最后是今天的分享：Author、Article、ArticleDetail三张表一键建表SQL语句

sql select set view

0 人点赞