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;

相关内容

热门资讯

通用制卡安卓系统版本 你有没有发现,手机里的那些卡片应用越来越智能了?这不,最近我在研究通用制卡安卓系统版本的时候,发现了...
安卓系统最好的省电王,助你续航... 手机电量总是不够用?是不是每次出门前都要检查一下充电宝在不在?别急,今天我要给你介绍一个安卓系统里的...
安卓通话系统变成黑色了,探究原... 最近你的安卓手机通话界面是不是突然变成了黑色?别急,别慌,让我来给你详细解析一下这个现象,让你轻松应...
长沙雨天攻略系统和安卓,畅游雨... 长沙这座美丽的城市,一到雨天就变得特别有味道。但你知道吗,雨天出行可不是件容易事儿,尤其是对于不熟悉...
安卓系统照片打印机,便捷打印新... 你有没有想过,手机里的照片,怎么才能变成实体的相片呢?没错,就是那种可以捧在手心、贴在墙上的那种!今...
安卓系统内录声音破解 你有没有想过,你的安卓手机里那些看似平常的录音,其实可能藏着不为人知的秘密呢?没错,就是那些你在不知...
安卓系统开发外包,共创卓越移动... 你有没有想过,为什么你的手机里那么多应用,而有些应用却让你爱不释手?这其中,安卓系统开发外包可是功不...
安卓系统应用分身微信 你有没有想过,你的手机里那个天天不离手的微信,竟然还能有个分身呢?没错,就是那个安卓系统下的应用分身...
安卓系统如何打开hwp,安卓系... 你是不是也遇到了这样的问题:手里拿着一份HWP格式的文档,却苦于不知道如何在安卓系统上打开它?别急,...
蜜豆下载安卓系统 你有没有想过,手机里多一款有趣的APP,生活就能多一份乐趣呢?今天,就让我来给你安利一款超级好用的A...
天天2棋牌安卓系统,体验指尖竞... 你有没有发现,最近手机上的一款游戏特别火?没错,就是天天2棋牌安卓系统!这款游戏不仅玩法多样,而且界...
安卓系统屏幕上的图标,图标解析... 你有没有发现,每次打开安卓手机,那屏幕上密密麻麻的图标就像是一张五彩斑斓的地图,指引着你在数字世界的...
安卓系统搞怪软件有哪些,笑点连... 你有没有发现,安卓系统里的软件真是五花八门,有些甚至能让你忍俊不禁呢!今天,就让我带你一起探索那些安...
安卓系统如何给ios系统送皮肤... 你知道吗?现在手机界可是热闹非凡,安卓和iOS两大阵营的粉丝们都在为自家的系统争得面红耳赤。不过,今...
苹果转换安卓系统时蓝屏,揭秘蓝... 你有没有遇到过这种情况?手机突然蓝屏,屏幕上什么都没有,就那么静静地躺在那里,仿佛在向你诉说着什么。...
安卓系统的滑盖,便捷操作背后的... 你有没有发现,最近手机界又掀起了一股复古风潮?没错,就是那些曾经风靡一时的滑盖手机!它们又回来了,而...
苹果系统容易操作吗安卓,轻松上... 说到手机操作系统,你是不是也和我一样,对苹果系统和安卓系统有着浓厚的兴趣呢?毕竟,这两个系统在市场上...
安卓系统镜头怎么调焦距,安卓系... 你有没有发现,用安卓手机拍照的时候,有时候镜头的焦距调得不对,照片就变得模糊不清呢?别急,今天就来手...
安卓经典系统壁纸高清,感受时光... 亲爱的手机控们,你是否曾在某个午后,百无聊赖地翻看着手机,突然被那些精美的壁纸吸引住了目光?没错,就...
安卓系统怎么样安装wd系统,安... 你有没有想过给你的安卓手机来个换装大变身?没错,就是将安卓系统升级为WD系统!听起来是不是有点酷炫?...