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;

相关内容

热门资讯

安卓se系统怎么启用,确保应用... 你有没有发现,你的安卓手机最近有点儿“懒”呢?运行速度慢,反应迟钝,是不是想给它来个“大变身”呢?别...
微软怎么使用安卓系统,技术融合... 你有没有想过,那个以Windows系统著称的微软,竟然也会和安卓系统玩起“亲密接触”?没错,就是那个...
安卓系统耗电特别快,快速诊断与... 手机电量总是不够用?安卓系统耗电特别快,是不是你也遇到了这样的烦恼?别急,今天就来跟你聊聊这个话题,...
安卓机 桌面 系统菜单,功能解... 你有没有发现,你的安卓手机桌面系统菜单,其实就像一个隐藏的宝藏库呢?里面藏着各种各样的功能,等着你去...
安卓ios系统怎么安装,安卓与... 你有没有想过,你的手机里那些好玩的应用是怎么来的呢?是不是觉得安装个软件就像变魔术一样简单?其实,这...
珍奥助手安卓系统下载,轻松体验 你有没有听说最近有个超级好用的助手软件——珍奥助手?没错,就是那个能让你手机生活变得更加便捷的小帮手...
安卓换ios系统.数据,数据迁... 你有没有想过,手机系统就像是我们生活中的衣服,有时候换一件新衣服,整个人都焕然一新呢?没错,今天咱们...
安卓系统提示怎么关,轻松关闭功... 手机屏幕上突然弹出一个安卓系统的提示,让你不禁皱起了眉头。别急,别慌,今天就来手把手教你如何轻松关闭...
安卓系统如何刷回flyme系统... 你是不是也和我一样,对安卓手机的Flyme系统情有独钟呢?有时候,因为一些原因,我们可能需要将手机刷...
手机订餐系统源码安卓,基于手机... 你有没有想过,每天忙碌的生活中,点外卖已经成为了一种不可或缺的享受?而这一切的背后,离不开那些默默无...
顾问营销系统安卓版,助力企业高... 你有没有想过,在这个信息爆炸的时代,如何让你的产品在众多竞争者中脱颖而出呢?别急,今天我要给你介绍一...
安卓系统连接雅马哈音箱,打造个... 你有没有想过,家里的安卓手机和雅马哈音箱也能来个甜蜜的“牵手”呢?没错,今天就要来给你揭秘,如何让这...
安卓系统文件日志查看,揭秘系统... 手机里的安卓系统文件日志,听起来是不是有点儿高深莫测?别担心,今天我就要带你一探究竟,揭开这些神秘日...
努比亚升级安卓p系统,畅享智能... 你知道吗?最近手机界可是热闹非凡呢!努比亚这个品牌,竟然悄悄地给他们的手机升级了安卓P系统。这可不是...
仿苹果装安卓系统,揭秘仿苹果装... 你有没有想过,如果你的苹果手机突然变成了安卓系统,那会是怎样的场景呢?想象你那熟悉的iOS界面,突然...
安装安卓13子系统,全新功能与... 你听说了吗?安卓13子系统终于来了!这可是安卓系统的一大革新,让我们的手机体验更加丰富多元。今天,就...
安卓系统内核日志保存,深度洞察... 你有没有想过,当你手机里的安卓系统在默默运行时,它其实就像一个勤劳的小蜜蜂,不停地记录着它的“工作日...
安卓系统可以调用dll,安卓系... 你知道吗?安卓系统竟然能调用DLL文件,这可是个让人眼前一亮的小秘密呢!想象你手中的安卓设备,不仅能...
安卓通讯 录系统代码,基于安卓... 你有没有想过,你的手机里那个默默无闻的通讯录系统,其实背后有着一套复杂的代码在支撑呢?今天,就让我带...
安卓系统版本对应关系,安卓系统... 你有没有发现,每次手机更新系统,那感觉就像给手机换了个新衣裳,焕然一新呢!不过,你知道吗?安卓系统的...