本节简要介绍视图的含义及作用,并学习如何操作视图。

介绍

关键字:view

  • 视图(view)是一个虚拟表,非真实存在,其本质是根据SQL语句获取动态的数据集,并为其命名,用户使用时只需使用视图名称即可获取结果集,并可以将其当作表来使用。

  • 数据库中只存放了视图的定义,而并没有存放视图中的数据。这些数据存放在原来的表中。

  • 使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。因此,视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变,显示在视图中的数据也会发生改变。

视图可以理解为一种快捷方式,一种映射。有点像c语言、python之类的函数。

作用

  • 简化代码,可以把重复使用的查询封装成视图重复使用,同时可以使复杂的查询易于理解和使用。

  • 安全原因,如果一张表中有很多数据,很多信息不希望让所有人看到,此时可以使用视图视,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等,可以对不同的用户,设定不同的视图。

视图的操作

创建视图的语法:

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]:可选项,表示更新视图时要保证在该视图的权限范围之内。

(6)加上or replace就会在存在的情况下进行更换,不然会报错

准备数据

-- 准备数据
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 view_emp
as
select ename,job from emp;

-- 查看表和视图
-- 查看完整的表
show full tables;   
-- 运行结果:
-- dept        BASE TABLE
-- emp         BASE TABLE
-- salgrade    BASE TABLE
-- view_emp    VIEW 

使用视图

视图的使用,就是把创建的视图当做一张表使用,尽管是一张虚拟的表。

这张虚拟的表来自于原表。

特别注意,当原表发生了变化,这个视图里的内容也会发生变化。

-- 使用视图
# 视图的使用,就是把创建的视图当做一张表使用,尽管是一张虚拟的表
select * from view_emp;
# 简单来说,就是把view_emp视图当做一个表
-- 运行结果     
-- 甘宁   文员
-- 黛绮丝  销售员
-- 殷天正  销售员
-- 刘备   经理
-- 谢逊   销售员
-- .......

修改视图

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

语法:

alter view 视图名 as select语句

示例:

-- 修改视图 
alter view view_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 view_emp;
-- 运行结果:
-- 10   教研部 北京  张飞  24500.00
-- 10   教研部 北京  曾阿牛 50000.00
-- 10   教研部 北京  黄盖  13000.00
-- 20   学工部 上海  甘宁  8000.00
-- 20   学工部 上海  刘备  29750.00
-- 20   学工部 上海  诸葛亮 30000.00
-- 20   学工部 上海  周泰  11000.00
-- 20   学工部 上海  庞统  30000.00
-- 30   销售部 广州  黛绮丝 16000.00
-- 30   销售部 广州  殷天正 12500.00
-- 30   销售部 广州  谢逊  12500.00
-- 30   销售部 广州  关羽  28500.00
-- 30   销售部 广州  韦一笑 15000.00
-- 30   销售部 广州  程普  9500.00 

更新视图

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

某些视图是可更新的。也就是说,可以在UPDATE、DELETE或INSERT等语句中使用它们,以更新基表的内容。对于可更新的视图,在视图中的行和基表中的行之间必须具有一对一的关系。如果视图包含下述结构中的任何一种,那么它就是不可更新的:

•聚合函数(SUM(), MIN(), MAX(), COUNT()等)

•DISTINCT

•GROUP BY

•HAVING

•UNION或UNION ALL

•位于选择列表中的子查询

•JOIN

•FROM子句中的不可更新视图

•WHERE子句中的子查询,引用FROM子句中的表。

•仅引用文字值(在该情况下,没有要更新的基本表)

-- 1、插入数据时,视图只是引用表格中的某些字段,
-- 而另外一些字段又没有指定默认值时会插入失败    
INSERT INTO view1_emp
VALUES
    ( '周瑜', '文员' );

-- 2、视图包含聚合函数不可更新
CREATE 
    OR REPLACE VIEW view2_emp AS SELECT
    count(*) cnt
FROM
    emp;

SELECT
    * 
FROM
    view2_emp;
INSERT INTO view2_emp
VALUES
    ( 100 );

UPDATE view2_emp 
SET cnt = 100;

-- 3、视图包含distinct不可更新
CREATE 
    OR REPLACE VIEW view3_emp AS SELECT DISTINCT
    job 
FROM
    emp;

SELECT
    * 
FROM
    view3_emp;

INSERT INTO view3_emp
VALUES
    ( '财务' );


-- 4、视图包含group by、having不可更新
CREATE 
    OR REPLACE VIEW view4_emp AS SELECT
    deptno 
FROM
    emp 
GROUP BY
    deptno 
HAVING
    deptno > 10;

SELECT
    * 
FROM
    view4_emp;

insert into view4_emp values(40);


-- 5、视图包含union、union all不可更新
-- union all不去重,union会去重
CREATE 
    OR REPLACE VIEW view5_emp AS SELECT
    empno,
    ename 
FROM
    emp 
WHERE
    empno <= 5 UNION SELECT empno, ename FROM emp WHERE empno > 8;

SELECT
    * 
FROM
    view5_emp;

INSERT INTO view5_emp
VALUES
    ( 1015, '宋江' );

-- 6、视图包含子查询不可更新
CREATE 
    OR REPLACE VIEW view6_emp AS SELECT
    empno,
    ename,
    sal 
FROM
    emp 
WHERE
    sal = (
    SELECT
        max( sal ) 
    FROM
    emp);

SELECT
    * 
FROM
    view6_emp;

INSERT INTO view6_emp
VALUES
    ( 1015, '血刀老祖', 30000.0 );

-- 7、视图包含join不可更新
CREATE VIEW view7_emp AS SELECT
dname,
ename,
sal 
FROM
    emp a
    JOIN dept b ON a.deptno = b.deptno;

INSERT INTO view7_emp
VALUES
    ( '行政部', '韦小宝', 6500.00 );

-- 8、视图包含常量文字值不可更新
CREATE 
    OR REPLACE VIEW view8_emp AS SELECT
    '行政部' dname,
    '杨过' ename;

INSERT INTO view8_emp
VALUES
    ( '行政部', '韦小宝' );

重命名视图

关键字:rename

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

删除视图

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

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


声明:内容来源于B站视频《2022黑马程序员最新MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程》,博客内容仅作学习参考使用。



博主个人公众号
版权声明 ▶ 本网站名称:陶小桃Blog
▶ 本文链接:https://www.52txr.cn/2022/mysql18.html
▶ 本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长进行核实删除。
▶ 转载本站文章需要遵守:商业转载请联系站长,非商业转载请注明出处!!

最后修改:2022 年 06 月 05 日
如果觉得我的文章对你有用,请随意赞赏