MySQL的视图
创始人
2024-05-31 16:34:32
0

文章目录

  • MySQL的视图
    • 介绍
    • 作用
    • 视图的创建
      • 数据准备
      • 操作
    • 修改视图
      • 操作
    • 更新视图
      • 操作
    • 其他操作(重命名和删除)
      • 重命名视图
      • 删除视图
    • 练习

MySQL的视图

介绍

• 视图(view)是一个虚拟表,非真实存在,其本质是根据SQL语句获取动态的数据集,并为其命名,用户
使用时只需使用视图名称即可获取结果集,并可以将其当作表来使用。
• 数据库中只存放了视图的定义,而并没有存放视图中的数据。这些数据存放在原来的表中。
• 使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。因此,视图中的数据是依赖于原来的
表中的数据的。一旦表中的数据发生改变,显示在视图中的数据也会发生改变。

作用

• 简化代码,可以把重复使用的查询封装成视图重复使用,同时可以使复杂的查询易于理解
和使用。
• 安全原因,如果一张表中有很多数据,很多信息不希望让所有人看到,此时可以使用视图
视,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数
等,可以对不同的用户,设定不同的视图。

视图的创建

创建视图的语法为:

create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]
参数说明:
(1)algorithm:可选项,表示视图选择的算法。
(2)view_name :表示要创建的视图名称。
(3)column_list:可选项,指定视图中各个属性的名词,默认情况下与SELECT语句中的查询的属性相同。
(4)select_statement:表示一个完整的查询语句,将查询记录导入视图中。
(5)[with [cascaded | local] check option]:可选项,表示更新视图时要保证在该视图的权限范围之内

数据准备

-- 视图
-- 准备数据
create database if not exists mydb6_view;
use mydb6_view;
create table dept(deptno int primary key,dname varchar(20),loc varchar(20)
);
insert into dept values(10, '教研部','北京'),(20, '学工部','上海'),(30, '销售部','广州'),(40, '财务部','武汉');create table emp(empno int primary key,ename varchar(20),job varchar(20),mgr int,hiredate date,sal numeric(8,2),comm numeric(8, 2),deptno int,
-- 	FOREIGN KEY (mgr) REFERENCES emp(empno),FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL ON UPDATE CASCADE
);
insert into emp values(1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, null, 20),(1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),(1004, '刘备', '经理', 1009, '2001-4-02', 29750.00, null, 20),(1005, '谢逊', '销售员', 1006, '2001-9-28', 12500.00, 14000.00, 30),(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, null, 30),(1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, null, 10),(1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, null, 20),(1009, '曾阿牛', '董事长', null, '2001-11-17', 50000.00, null, 10),(1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),(1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, null, 20),(1012, '程普', '文员', 1006, '2001-12-03', 9500.00, null, 30),(1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, null, 20),(1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, null, 10);create table salgrade(grade int primary key,losal int,hisal int
);
insert into salgrade values(1, 7000, 12000),(2, 12010, 14000),(3, 14010, 20000),(4, 20010, 30000),(5, 30010, 99990);

操作

create or replace view view1_empas select ename,job from mydb6_view.emp;
-- 查看表和视图
show tables;

在这里插入图片描述

-- 查看表和视图
show full tables;

在这里插入图片描述

select * from view1_emp;

在这里插入图片描述

修改视图

修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时,可以通过修改视图来保持视图和基
本表之间一致。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句来修改视图。
在这里插入图片描述

操作

alter view view1_emp
as
select a.deptno,a.dname,a.loc,b.ename,b.sal from dept a, emp b where
a.deptno = b.deptno;
select * from view1_emp;

在这里插入图片描述

更新视图

某些视图是可更新的。也就是说,可以在UPDATE、DELETE或INSERT等语句中使用它们,以更新基表的内容。对于可
更新的视图,在视图中的行和基表中的行之间必须具有一对一的关系。如果视图包含下述结构中的任何一种,那么它
就是不可更新的:
• 聚合函数(SUM(), MIN(), MAX(), COUNT()等)
• DISTINCT
• GROUP BY
• HAVING
• UNION或UNION ALL
• 位于选择列表中的子查询
• JOIN
• FROM子句中的不可更新视图
• WHERE子句中的子查询,引用FROM子句中的表。
• 仅引用文字值(在该情况下,没有要更新的基本表)

视图中虽然可以更新数据,但是有很多的限制。一般情况下,最好将视图作为查询数据的虚拟表,而不要通过视图更新数据。因为,使用视图更新数据时,如果没有全面考虑在视图中更新数据的限制,就可能会造成数据更新失败。

操作

-- ---------更新视图-------
create or replace view view1_emp
as
select ename,job from emp;
update view1_emp set ename = '周瑜' where ename = '鲁肃'; -- 可以修改
insert into view1_emp values('孙权'
,
'文员'); -- 不可以插入
-- ----------视图包含聚合函数不可更新--------------
create or replace view view2_emp
as
select count(*) cnt from emp;
insert into view2_emp values(100);
update view2_emp set cnt = 100;
-- ----------视图包含distinct不可更新---------
create or replace view view3_emp
as
select distinct job from emp;
insert into view3_emp values('财务');
-- ----------视图包含goup by 、having不可更新------------------
create or replace view view4_emp
as
select deptno ,count(*) cnt from emp group by deptno having cnt > 2;
insert into view4_emp values(30,100);
-- ----------------视图包含union或者union all不可更新----------------
create or replace view view5_emp
as
select empno,ename from emp where empno <= 1005
union
select empno,ename from emp where empno > 1005;
insert into view5_emp values(1015,
'韦小宝');
-- -------------------视图包含子查询不可更新--------------------
create or replace view view6_emp
as
select empno,ename,sal from emp where sal = (select max(sal) from emp);
insert into view6_emp values(1015,
'韦小宝'
,30000);
-- ----------------------视图包含join不可更新-----------------
create or replace view view7_emp
as
select dname,ename,sal from emp a join dept b on a.deptno = b.deptno;
insert into view7_emp(dname,ename,sal) values('行政部'
,
'韦小宝'
,30000);
-- --------------------视图包含常量文字值不可更新-------------------
create or replace view view8_emp
as
select '行政部' dname,
'杨过' ename;
insert into view8_emp values('行政部'
,
'韦小宝');

其他操作(重命名和删除)

重命名视图

-- rename table 视图名 to 新视图名; 
rename table view1_emp to my_view1

删除视图

-- drop view 视图名[,视图名…];
drop view if exists view_student;

删除视图时,只能删除视图的定义,不会删除数据。

练习

-- 1:查询部门平均薪水最高的部门名称
select dname from dept a ,(select deptno,avg(sal) from emp group by deptno 
order by avg(sal) desc limit 1) b
where a.deptno = b.deptno;
-- 2:查询员工比所属领导薪资高的部门名、员工名、员工领导编号
select * from dept x,
(select a.ename aname ,a.sal asal,b.ename bname,b.sal bsal,a.deptno
from emp a, emp b 
where a.mgr = b.empno and a.sal > b.sal) y
where x.deptno = y.deptno;
-- 3:查询工资等级为4级,2000年以后入职的工作地点为北京的员工编号、姓名和工资,并查询出薪
资在前三名的员工信息
create view xxx
as
SELECT e.empno,e.ename,e.sal,e.hiredate
FROM emp e,dept d,salgrade s
WHERE (e.sal BETWEEN losal AND hisal) AND s.GRADE = 4
AND year(e.hiredate) > '2000'
AND d.loc = '北京';
select * from
(
select
*,
dense_rank() over(order by sal desc ) rn
from xxx
) t
where t.rn <=3;

相关内容

热门资讯

橘子系统安卓12,探索全新体验... 你知道吗?最近手机界可是掀起了一股热潮,那就是安卓12的橘子系统。这款系统不仅带来了全新的界面设计,...
安卓系统录音怎么不能用,排查与... 最近是不是发现你的安卓手机录音功能突然失灵了?别急,别急,让我来给你一探究竟,帮你找出录音不能用的小...
安卓系统查看闪照,闪照生成技术... 你有没有遇到过这种情况:手机里突然弹出一个闪照,瞬间心跳加速,好奇心爆棚,但又担心错过重要信息。别急...
拓实安卓系统驱动下载,轻松实现... 你有没有遇到过手机系统崩溃,然后发现需要下载驱动才能让安卓手机重新焕发生机的情况?别急,今天就来给你...
安卓网上火车订票系统,便捷出行... 你有没有想过,在这个信息爆炸的时代,订火车票竟然也能变得如此轻松愉快?没错,我要跟你聊聊的就是这个神...
安卓系统连接hcair导航,畅... 你有没有想过,你的安卓手机竟然能和HCAir导航这么高科技的东西无缝连接呢?没错,就是那种在驾驶时帮...
安卓如何用苹果系统拍照,安卓设... 你有没有想过,即使你的手机是安卓的,也能享受到苹果系统拍照的乐趣呢?没错,就是那种拍出高清、色彩鲜艳...
怎么开启安卓原生系统,开启纯净... 你有没有想过,你的安卓手机其实隐藏着许多未被发掘的潜能?没错,就是那个我们每天不离手的安卓原生系统!...
安卓系统安装中国象棋 你有没有想过,在手机上安装一款经典的中国象棋游戏,随时随地来一场智慧的较量呢?安卓系统可是个不错的选...
老手机刷机安卓系统,安卓系统刷... 你那台老手机是不是已经有点儿力不从心啦?别急,今天就来给你支个招——老手机刷机,升级安卓系统,让它重...
安卓系统历届的名称,从“糖果”... 你有没有发现,每次打开你的安卓手机,系统界面上的名称总在悄悄地变化?这可不是简单的改个名字那么简单,...
安卓系统的谷歌卸载不了 你是不是也遇到了这个问题?安卓系统的谷歌应用卸载不了,是不是让你头疼得要命?别急,今天就来给你详细解...
鸿蒙系统兼容安卓手表吗,鸿蒙系... 最近手机圈可是热闹非凡呢!鸿蒙系统横空出世,让无数科技爱好者为之疯狂。不过,有人开始好奇了,鸿蒙系统...
安卓系统的移动加密软件,安全守... 你知道吗?在这个信息爆炸的时代,保护个人隐私变得尤为重要。而手机,作为我们日常生活中不可或缺的伙伴,...
最低安卓系统淘汰时间,揭秘最低... 你知道吗?在科技飞速发展的今天,手机更新换代的速度简直就像坐上了火箭!这不,最近有个话题在数码圈里炒...
安卓系统什么框架好用,探索最佳... 你有没有想过,你的安卓手机里那些应用,是怎么运行得那么顺畅的呢?其实,这背后可是有“大功臣”的——那...
平板安卓系统和ios,安卓与i... 你有没有发现,现在身边的朋友几乎人手一台平板电脑呢?无论是追剧、办公还是游戏,平板电脑都成了我们生活...
安卓的定位系统叫什么,GPS导... 你有没有想过,你的手机是怎么知道你在哪儿的呢?是不是觉得这事儿很神奇?其实,这背后有一个强大的技术支...
老式电视安卓系统升级,解锁智能... 你有没有发现,家里的老式电视突然变得聪明起来啦?没错,就是那个陪伴我们多年的老伙伴,它竟然悄悄地升级...
长安车载系统升级安卓 你有没有发现,最近你的长安车好像变得聪明多了?没错,就是那个车载系统,它悄悄地进行了安卓升级,简直就...