MYSQL进阶学习笔记六:MySQL视图的创建,理解及管理!(视频序号:进阶_14,15)

时间:2023-03-08 15:36:03

知识点七:MySQL视图的创建(14)

视图的定义:

  什么是视图:

    视图数由查询结果形成的一张虚拟的表。

   什么时候要用到视图?

    如果某个查询结果出现的非常频繁,也就是,要经常拿这个查询结果来做子查询。

  使用视图的好处:

    1.简化查询语句:

      案例:比如求每个人的平均工资。

 --简化查询语句
use mzjj;
CREATE OR REPLACE VIEW v_salaries
AS
SELECT employees.emp_no,first_name,last_name,IFNULL(AVG(salary),0) AS salary FROM employees LEFT JOIN salaries
ON employees.emp_no =salaries.emp_no GROUP BY employees.emp_no; SELECT * FROM v_salaries;
SELECT * FROM v_salaries WHERE emp_no =1001;

简化查询语句测试

    2.可以进行权限控制:

     把表的权限封闭,但是开放相应的视图权限,视图只开放部分数据列;

      案例:比如把员工的工资表的权限关闭,但是开放员工的发放工资的次数的权限。

 --权限控制
CREATE VIEW v_getSalaryCount
AS
SELECT employees.emp_no,first_name,last_name,COUNT(salary) AS salaryNum FROM employees LEFT JOIN salaries
ON employees.emp_no =salaries.emp_no GROUP BY employees.emp_no; SELECT * FROM v_getSalaryCount;
SELECT * FROM v_getSalaryCount WHERE emp_no =1001;

权限管理测试

    3.大数据表分类的时候,比如某张表的数据有100万条,那么可以将这张表分为四个视图

     按照对ID取余的计算;

 --大数据分区
CREATE VIEW v_employees1
AS
SELECT * FROM employees WHERE emp_no % 4 =0; CREATE VIEW v_employees2
AS
SELECT * FROM employees WHERE emp_no % 4 =1; CREATE VIEW v_employees3
AS
SELECT * FROM employees WHERE emp_no % 4 =2; CREATE VIEW v_employees4
AS
SELECT * FROM employees WHERE emp_no % 4 =3;

分区测试

  视图与表的关系:

    视图是表的查询结果,自然表的数据变化了,会影响视图的结果。

  能否对视图表进行增删改查:

    视图的增删改也会影响表;

    但视图并不总是能增删改的

    视图的数据与表的数据一一对应时可以修改;

    对视图的insert还应注意:视图必须包含表中没有默认值的列。

视图的创建:

   视图的创建语法:

    CREATE [OR REPLACE] [ALGORITHM ={UNDEFINED | MERGE | TEMPEABLE}]

      VIEW view_name [(column_list)]

      AS select_statement

      [WITH [CASCADED | LOCAL] CHECK OPTION]

    注释:该语句能创建新的视图,如果给定了OR REPLACE子句,该语句还能替换已有的视图。

select_statement是一种SELECT语句,它给出了视图的定义。该语句可从基表或其他视图进行选择。

深入理解视图:

  视图的存放位置:

    视图存放在information_schema数据库的views表里。

  存在两种执行算法:

    Merge:合并的执行方式,每当执行的时候,先将我们的视图的sql语句与外部查询视图的sql语句,混合在一起,最终执行。

    Temptable:临时表模式,每当查询的时候,将视图所使用的SELECT语句生成一个结果的临时表,再在当前的临时表内进行查询。

  指的是一个视图是在什么时候执行,依据那些方式执行:

    对于MERGE,会将引用视图的语句的文本与视图定义结合起来,使得视图定义的某一部分取代语句的对应部分。

    对于REMPTABLE,视图的结果将被置于临时表中,然后使用它执行语句。

    对于UNDEFINED,MySQL将选择所需要使用的算法。如果可能,它倾向于MERGE而不是REMPTABLE,这是因为MERGE通常更有效,而且如果使用了临时表,视图是不可更新的。

视图的管理:

  查看视图的定义:

    SHOW TABLE STATUS [FROM 数据库名称] [LIKE ‘视图名称匹配’];

  删除视图

    只能删除视图的定义,不能删除数据,必须有drop权限。

    DROP VIEW [IF EXISTS] view_name [RESTRICT | CASCADE]

  查看权限:

    SELECT drop_priv FROM mysql.user WHERE USER=’root’;

   删除视图:

    DROP VIEW IF EXISTS worker_view1;

   删除多个:

    DROP VIEW IF EXISTS department_view1, department_view2;

关于视图更新的问题:

    某些视图是可跟新的。也就是说,可以在诸如UNDATE,DELETE或INSERT等语句中使用它们,以更新基表的内容。对于可更新的视图,在视图

  中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构,这类结构会使得视图不可更新。更巨头的讲,如果视图包含下述结构的任意一种,那么它就是不可更新的:
    1.聚合函数(SUM(),MIN(),MAX(),COUNT(),等

    2.DISTINCT

    3.GROUP BY

    4.HAVING

    5.UNION或UNION
ALL
    6.位于选择列表的子查询

    7.JOIN

    8.FROM子句中的不可更新视图

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

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

    11.ALGORITHM = TEMPTABLE(使用临时表总会使视图成为不可更新的)。

 --视图更新
--原表
CREATE VIEW v_salaries
AS
SELECT emp_no,salary FROM salaries; UPDATE v_salaries SET salary=2500 WHERE emp_no =1001; --修改后
CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v_salaries
AS
SELECT emp_no,salary FROM salaries; --更新不了,因为存在 ALGORITHM = TEMPTABLE,破坏了更新条件
UPDATE v_salaries SET salary=2500 WHERE emp_no =1001;

视图的更新测试

关于WITH CHECK OPTION的理解及应用:

  with check option可以这么解释:通过视图进行的修改,必须也能通过该视图看到修改后的结果。

  with check option:更新视图的数据,那么必须先满足视图的条件,满足之后才能够更新到基表中。

 --对WITH CHECK OPTION的理解及应用
CREATE OR REPLACE VIEW v_salaries
AS
SELECT emp_no,salary FROM salaries WHERE emp_no=1001
WITH CHECK OPTION; --不能更新,因为1002不在视图条件内
UPDATE v_salaries SET salary=2000 WHERE emp_no =1002;
--可以跟新
UPDATE v_salaries SET salary=2000 WHERE emp_no =1001;

WITH CHECK OPTION的理解及应用测试