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;

相关内容

热门资讯

ios系统和安卓区别,系统差异... 你有没有发现,现在手机市场上,iOS系统和安卓系统就像是一对双胞胎,长得差不多,但性格却截然不同。今...
安卓系统2.3优酷,优酷的崛起... 你有没有发现,安卓系统2.3时代的那股怀旧风?那时候,优酷可是视频界的巨头,多少人都是看着优酷长大的...
安卓导航系统密封,安卓导航系统... 你有没有发现,现在手机导航系统越来越智能了?尤其是安卓系统的导航,简直就像一个贴心的导航小助手,带你...
a版安卓11系统,a版深度解析... 你知道吗?最近手机界可是炸开了锅,各大品牌纷纷发布了搭载a版安卓11系统的手机。这可不是什么小打小闹...
安卓系统的模拟吉他,随时随地弹... 你有没有想过,在手机上也能弹奏吉他呢?没错,就是那种模拟吉他的安卓系统应用,让你随时随地都能享受音乐...
王者适配的安卓系统,深度解析适... 你有没有发现,最近玩《王者荣耀》的小伙伴们都在议论纷纷,说新出的安卓系统简直是为王者量身定做的!没错...
安卓系统自动定位关闭,隐私保护... 你有没有发现,手机里的安卓系统有时候会自动定位,这可真是让人又爱又恨啊!有时候,我们并不想让别人知道...
安卓系统电量耗尽测试,全面解析... 手机电量耗尽,这可是每个手机用户都头疼的问题。你有没有想过,你的安卓手机在电量耗尽前,到底经历了哪些...
如何升级车载安卓系统,车载安卓... 亲爱的车主朋友们,你是不是也和我一样,对车载安卓系统升级这件事充满了好奇和期待呢?想象当你驾驶着爱车...
安卓办公哪个系统好,深度解析哪... 你有没有想过,在安卓办公的世界里,哪个系统才是你的最佳拍档呢?在这个信息爆炸的时代,选择一个既强大又...
安卓系统差劲怎么解决,重拾流畅... 你有没有发现,安卓系统有时候真的让人头疼得要命?手机卡顿、应用崩溃、电池续航短,这些问题是不是让你抓...
喜欢安卓系统的原因,探索用户偏... 你有没有发现,身边的朋友、同事,甚至家人,越来越多的人开始使用安卓手机了呢?这可不是简单的潮流,而是...
安卓系统金立手机,品质生活新选... 你有没有发现,最近安卓系统下的金立手机突然火了起来?没错,就是那个曾经陪伴我们走过无数时光的金立手机...
无安卓系统的电视,新型无系统电... 亲爱的读者们,你是否厌倦了那些充斥着安卓系统的电视?想要尝试一些新鲜玩意儿?那就跟我一起探索一下无安...
麒麟系统能刷安卓系统吗,轻松刷... 你有没有想过,你的麒麟手机能不能装上安卓系统呢?这可是个让人好奇不已的问题。现在,就让我来带你一探究...
手机公司安卓系统吗,手机公司引... 你有没有想过,为什么你的手机里装的是安卓系统而不是苹果的iOS呢?这背后可是有着不少故事和门道的哦!...
安卓系统 文件网络传输,安卓系... 你有没有想过,手机里的文件怎么才能轻松地传给朋友呢?今天,就让我来给你揭秘安卓系统中的文件网络传输技...
安卓手机系统怎样备份,安卓手机... 你有没有想过,如果你的安卓手机突然“罢工”了,里面的照片、联系人、应用和数据怎么办?别担心,今天就来...
安卓系统怎样分享app,安卓系... 你是不是也和我一样,手机里装了超多好用的APP,但是有时候想和朋友分享这些宝藏,却不知道怎么操作呢?...
sonicarekids安卓系... 最近是不是你也遇到了Sonicare Kids安卓系统打不开的烦恼?别急,让我来帮你一探究竟,找出解...