当前位置：首页 > ds >正文

MySQL的视图

ds 2025/7/1 11:09:33

一、MySQL视图的介绍和作用

MySQL视图，加油兄弟们，孰能生巧，完整代码在最后！！！

视图是一个虚拟的表，并不是真是存在的，视图其实并没有真实的数据，他只是根据一个sql语句获取的一个数据集，并为其命名，视图只是代表查出来的数据，如果想要找数据，还是需要从原来的数据里面去找。

原表的数据发生变化，视图的数据也会发生变化

可以简化代码，把重复使用的查询封装成视图重复使用，像子查询里面的需要在写一次查询语句，使用视图，代码更简单一点

可以使用视图，让不一样的人看到的数据不一样

二、创建视图

数据添加

因为视图是根据原来的表产生的，所以原来的表发生变化，视图也会发生变化

比如原来表把名字改为ikun，视图也会变化

三、修改视图

四、更新视图

更新视图其实事实上还是更新的视图所对应的原表的数据，并不是所有情况下都可以使用，会有一些限制，好多情况下都不可以更新

将视图中的甘宁修改为周瑜，原表的甘宁也变成了周瑜

直接插入两个数据就不可以，因为原表一行不止两个数据

比如：视图包含聚合函数不可更新

视图包含distinct不可更新

总的来是：视图包含以下不可更新：聚合函数，distinct，group by，having，union，union all，子查询，join，常量文字值

视图虽然可以更新，但是大部分不可以，一般来说不要想通过视图去修改表，可能会导致数据更新失败

五、删除视图和重命名视图

记住删除视图，并不会删除表的数据，他只是删除了视图的定义

六、练习

看起来很复杂，但是其实只是简单的四层嵌套，第一层分组算平均数，第二层排序号，第三层找出序号为1的，第四层找出部门名称

或

创建视图，可以使代码更加简洁

七、总结

八、完整代码

-- 创建视图

create database 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);

use mydb6_view;
create or replace
view view1_emp
as
select ename,job from emp;

-- 查看表和视图
show tables;
-- 虽然是一个视图，但是也是会当作一个表来查看

-- 查看表和视图的类型
show full tables;

select * from view1_emp;

-- 修改视图
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;

-- 更新视图
create or replace view view1_emp
as
select ename,job from emp;

select * from view1_emp;

update view1_emp set ename = '周瑜' where ename = '甘宁';

insert into view1_emp values('孙权','文员');

-- 视图包含聚合函数不可更新
create or replace view ciew2_emp
AS
select count(*) cnt from emp;
select * from ciew2_emp;
insert into ciew2_emp values(100);
update ciew2_emp set cnt = 100;

-- 视图包含distinct不可更新
create or replace view view2_emp
AS
select job from emp;
select * from view2_emp;
insert into view2_emp values('小鸡');

create or replace view view3_emp
AS
select deptno,count(*) cnt from emp group by deptno having cnt > 2;
select * from view3_emp;
insert into view3_emp values(30,100);

-- union 就是把两个表拼一起
create or replace view view4_emp
AS
select empno,ename from emp where empno <= 1005
union
select empno,ename from emp where empno > 1005;
select * from view4_emp;
insert into view4_emp values(1003,'小');

-- 重命名视图
rename table view1_emp to myview1;

-- 删除视图
drop view if exists myview1;

-- 查询部门平均薪水最高的部门名称
SELECT
a.deptno,
a.dname,
a.loc,
avg_sal
FROM
dept a,
(
SELECT
*
FROM
(
SELECT
*,
rank() over (ORDER BY avg_sal DESC) rn
FROM
(SELECT deptno, avg(sal) avg_sal FROM emp GROUP BY deptno) t
) tt
WHERE
rn = 1
) ttt
WHERE
a.deptno = ttt.deptno;
-- ---------------------------- 视图---------
create view test_view1
as
select deptno,avg(sal) avg_sal from emp group by deptno;

create view test_view2
AS
select *,rank() over(order by avg_sal desc) rn from test_view1;

create view test_view3
as
select * from test_view2 tt where rn = 1;

select * from test_view3;
-- 使用视图简化后
select a.deptno,a.dname,a.loc,avg_sal
from dept a,test_view3 ttt
where a.deptno = ttt.deptno;

create view view1
as
SELECT
a.deptno,
a.dname,
a.loc,
avg_sal
FROM
dept a,
(
SELECT
*
FROM
(
SELECT
*,
rank() over (ORDER BY avg_sal DESC) rn
FROM
(SELECT deptno, avg(sal) avg_sal FROM emp GROUP BY deptno) t
) tt
WHERE
rn = 1
) ttt
WHERE
a.deptno = ttt.deptno;

select * from view1;

-- 查询员工比所属领导薪资高的部门名、员工名、员工领导编号
create view test_view4
AS
select a.ename ename,a.sal esal,b.ename mgrname,b.sal msal,a.deptno from emp a,emp b where a.mgr = b.empno and a.sal > b.sal;
select * from test_view4;
select * from dept a join test_view4 b on a.deptno = b.deptno;

-- 查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资，并查询出薪资在前三名的员工信息
create view test_view5
as
select a.deptno,a.dname,a.loc,b.empno,b.ename,b.sal from dept a join emp b on a.deptno = b.deptno and b.sal between '20010' and '30000' and year(hiredate) > '2000' and a.loc = '上海';
select * from test_view5;

select * from
(
select *,rank() over(order by sal desc) rn
from test_view5
)t
where rn <= 3;