精华内容
下载资源
问答
  • mysql 视图

    2020-11-23 21:57:48
    作为一个select语句保存在数据字典的。 视图干什么用的? 通过视图可以展现基表的部分数据; 视图数据来自定义视图的查询使用的,使用视图动态生成。 基表:用来创建视图叫做基表 为什么要使用视图? ...

    基本概念

    视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。视图的数据变化 (增删改) 会影响到基表,基表的数据变化也会影响到视图

    什么是视图?

    视图是一种虚拟存在的表,并不在数据库中实际存在,对用户透明,行列数据来自定义视图的查询中使用的表,并且在使用的过程中动态生成的

    视图是干什么用的?

    通过视图,可以展现基表的部分数据;
    视图数据来自定义视图的查询中使用的表,使用视图动态生成。
    基表:用来创建视图的表叫做基表

    为什么要使用视图?

    简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。

    安全:使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。

    数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。

    总而言之,使用视图的大部分情况是为了保障数据安全性,提高查询效率。

    视图的基本使用

    创建视图

    基本语法:create view 视图名 as select 字段名 from 表名
    进阶语法:create view 视图名 as select 字段名 from 视图名2

    mysql> create view myview as select deptno,loc from dept;
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> select * from myview;
    +--------+----------+
    | deptno | loc      |
    +--------+----------+
    |     10 | new york |
    |     20 | dallas   |
    |     30 | chicago  |
    |     40 | bosion   |
    +--------+----------+
    4 rows in set (0.00 sec)
    

    修改视图数据,对基表数据有影响

    mysql> update myview set loc='china' where deptno=10;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from myview;
    +--------+---------+
    | deptno | loc     |
    +--------+---------+
    |     10 | china   |
    |     20 | dallas  |
    |     30 | chicago |
    |     40 | bosion  |
    +--------+---------+
    4 rows in set (0.00 sec)
    
    mysql> select * from dept;
    +--------+------------+---------+
    | deptno | dname      | loc     |
    +--------+------------+---------+
    |     10 | accounting | china   |
    |     20 | research   | dallas  |
    |     30 | sales      | chicago |
    |     40 | operations | bosion  |
    +--------+------------+---------+
    4 rows in set (0.00 sec)
    

    修改基表数据,对视图数据也有影响

    mysql> update dept set loc='new york' where deptno=10;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from dept;
    +--------+------------+----------+
    | deptno | dname      | loc      |
    +--------+------------+----------+
    |     10 | accounting | new york |
    |     20 | research   | dallas   |
    |     30 | sales      | chicago  |
    |     40 | operations | bosion   |
    +--------+------------+----------+
    4 rows in set (0.00 sec)
    
    mysql> select * from myview;
    +--------+----------+
    | deptno | loc      |
    +--------+----------+
    |     10 | new york |
    |     20 | dallas   |
    |     30 | chicago  |
    |     40 | bosion   |
    +--------+----------+
    4 rows in set (0.00 sec)
    

    修改视图

    alter view 视图名 as select 字段名 from 表名;

    mysql> alter view myview as select deptno,dname,loc from dept;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> select * from myview;
    +--------+------------+----------+
    | deptno | dname      | loc      |
    +--------+------------+----------+
    |     10 | accounting | new york |
    |     20 | research   | dallas   |
    |     30 | sales      | chicago  |
    |     40 | operations | bosion   |
    +--------+------------+----------+
    4 rows in set (0.00 sec)
    

    删除视图

    mysql> drop view myview;
    Query OK, 0 rows affected (0.00 sec)
    

    视图实践

    查找每个部门工资最高的人的资料(子查询的案例)

    mysql> select * from (select * from emp order by deptno,sal desc) temp group by deptno;
    +-------+-------+-----------+------+------------+---------+------+--------+
    | empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
    +-------+-------+-----------+------+------------+---------+------+--------+
    |  7839 | king  | president | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
    |  7902 | ford  | analyst   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
    |  7698 | blake | manager   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
    +-------+-------+-----------+------+------------+---------+------+--------+
    3 rows in set (0.00 sec)
    

    视图中不能包含子查询

    //报错: 原因 视图中不能包含子查询
    mysql> create view myview1 as select * from (select * from emp order by deptno,sal desc) temp group by deptno;
    ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
    

    视图中包含视图

    create view 视图名1 as select 字段名 from 视图名2

    //视图中不能包含子查询  换个思路 将子查询做成视图
    mysql> create view myview2 as select * from emp order by deptno,sal desc;
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> select * from myview2;
    +-------+--------+-----------+------+------------+---------+---------+--------+
    | empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    |  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
    |  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
    |  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
    |  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
    |  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
    |  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
    |  7369 | smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
    |  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
    |  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
    |  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
    |  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
    |  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
    |  7900 | james  | clerk     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    13 rows in set (0.00 sec)
    
    //create view 视图名1 as select 字段名 from 视图名2
    mysql> create view myview1 as select * from myview2 temp group by deptno;
    Query OK, 0 rows affected (0.04 sec)
    
    //对试图分组时,没有返回上一个视图分组的第一条记录
    //原因是,在默认情况下,回到原来的基表上进行分组处理,没有使用视图2的结果
    //解决这个问题 只需设置 algorithm=temptable 需要先了解一下视图的算法
    mysql> select * from myview1;
    +-------+-------+----------+------+------------+---------+--------+--------+
    | empno | ename | job      | mgr  | hiredate   | sal     | comm   | deptno |
    +-------+-------+----------+------+------------+---------+--------+--------+
    |  7782 | clark | manager  | 7839 | 1981-06-09 | 2450.00 |   NULL |     10 |
    |  7369 | smith | clerk    | 7902 | 1980-12-17 |  800.00 |   NULL |     20 |
    |  7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
    +-------+-------+----------+------+------------+---------+--------+--------+
    3 rows in set (0.00 sec)
    

    algorithm 算法

    algorithm 三个值: merge,temptable 或 undefined(默认)

    algorithm = undefined, 由系统决定,视图尽可能的选择merge算法而不是temptable算法,因为merge算法更高效,而且临时表被使用时视图不能够被更新

    mysql> show create view myview1;
    +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
    | View    | Create View                                                                                                                                                                                                                                                                                                                                                  | character_set_client | collation_connection |
    +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
    | myview1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `myview1` AS select `temp`.`empno` AS `empno`,`temp`.`ename` AS `ename`,`temp`.`job` AS `job`,`temp`.`mgr` AS `mgr`,`temp`.`hiredate` AS `hiredate`,`temp`.`sal` AS `sal`,`temp`.`comm` AS `comm`,`temp`.`deptno` AS `deptno` from `myview2` `temp` group by `temp`.`deptno` | latin1               | latin1_swedish_ci    |
    +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
    1 row in set (0.00 sec)
    

    merge: 服务器将视图SQL和查询SQL进行合并,然后基于底层表查询,最终查的是基表,没有使用视图即虚拟表的查询结果

    //创建视图a1 查询工资大于2000的员工信息
    mysql> create view a1 as select * from emp where sal>2000;
    Query OK, 0 rows affected (0.65 sec)
    
    mysql> show create view a1;
    +------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
    | View | Create View                                                                                                                                                                                                                                                                                                                            | character_set_client | collation_connection |
    +------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
    | a1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `a1` AS select `emp`.`empno` AS `empno`,`emp`.`ename` AS `ename`,`emp`.`job` AS `job`,`emp`.`mgr` AS `mgr`,`emp`.`hiredate` AS `hiredate`,`emp`.`sal` AS `sal`,`emp`.`comm` AS `comm`,`emp`.`deptno` AS `deptno` from `emp` where (`emp`.`sal` > 2000) | latin1               | latin1_swedish_ci    |
    +------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from a1;
    +-------+-------+-----------+------+------------+---------+------+--------+
    | empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
    +-------+-------+-----------+------+------------+---------+------+--------+
    |  7566 | jones | manager   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
    |  7698 | blake | manager   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
    |  7782 | clark | manager   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
    |  7788 | scott | analyst   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
    |  7839 | king  | president | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
    |  7902 | ford  | analyst   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
    +-------+-------+-----------+------+------------+---------+------+--------+
    6 rows in set (0.00 sec)
    
    //在查询视图 a1 的时候,再加一个where条件 sal<3000
    mysql> select * from a1 where sal<3000;
    +-------+-------+---------+------+------------+---------+------+--------+
    | empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
    +-------+-------+---------+------+------------+---------+------+--------+
    |  7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
    |  7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
    |  7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
    +-------+-------+---------+------+------------+---------+------+--------+
    3 rows in set (0.00 sec)
    
    //以上语句合并 最终等同于 下面的select语句
    mysql> select * from emp where sal>2000 and sal<3000;
    +-------+-------+---------+------+------------+---------+------+--------+
    | empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
    +-------+-------+---------+------+------------+---------+------+--------+
    |  7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
    |  7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
    |  7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
    +-------+-------+---------+------+------------+---------+------+--------+
    3 rows in set (0.00 sec)
    

    temptable: 根据创建语句瞬间创建一张临时表,然后查询视图的语句从该临时表 (视图) 中查数据

    上面案例中 merde最终查的是goods表 temptable去查的是虚拟表

    //视图中不能包含子查询  换个思路 将子查询做成视图,设置 algorithm=temptable
    mysql> create algorithm=temptable view myview2 as select * from emp order by deptno,sal desc;
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> select * from myview2;
    +-------+--------+-----------+------+------------+---------+---------+--------+
    | empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    |  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
    |  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
    |  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
    |  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
    |  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
    |  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
    |  7369 | smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
    |  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
    |  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
    |  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
    |  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
    |  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
    |  7900 | james  | clerk     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    13 rows in set (0.00 sec)
    
    //create view 视图名1 as select 字段名 from 视图名2
    mysql> create view myview1 as select * from myview2 temp group by deptno;
    Query OK, 0 rows affected (0.04 sec)
    
    //查到的结果完全一样
    mysql> select * from myview1;
    +-------+-------+-----------+------+------------+---------+------+--------+
    | empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
    +-------+-------+-----------+------+------------+---------+------+--------+
    |  7839 | king  | president | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
    |  7902 | ford  | analyst   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
    |  7698 | blake | manager   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
    +-------+-------+-----------+------+------------+---------+------+--------+
    3 rows in set (0.00 sec)
    

    练习题

    查询雇员编号,雇员名,部门名称 和 薪水级别(普通方式)

    mysql> select emp.empno,ename,dept.dname,salgrade.grade from emp,dept,salgrade where emp.deptno=dept.deptno and emp.sal between losal and hisal;
    +-------+--------+------------+-------+
    | empno | ename  | dname      | grade |
    +-------+--------+------------+-------+
    |  7369 | smith  | research   |     1 |
    |  7499 | allen  | sales      |     3 |
    |  7521 | ward   | sales      |     2 |
    |  7566 | jones  | research   |     4 |
    |  7654 | martin | sales      |     2 |
    |  7698 | blake  | sales      |     4 |
    |  7782 | clark  | accounting |     4 |
    |  7788 | scott  | research   |     4 |
    |  7839 | king   | accounting |     5 |
    |  7844 | iurner | sales      |     3 |
    |  7900 | james  | sales      |     1 |
    |  7902 | ford   | research   |     4 |
    |  7934 | miller | accounting |     2 |
    +-------+--------+------------+-------+
    13 rows in set (0.64 sec)
    

    查询雇员编号,雇员名,部门名称 和 薪水级别(视图)

    mysql> create view b1 as select * from emp;
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> create view b2 as select * from dept;
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> create view b3 as select * from salgrade;
    Query OK, 0 rows affected (0.03 sec)
    
    //多表查询  虽然用到了视图  但感觉更复杂了   可能我的方法错了 ...
    mysql> select b1.empno,b1.ename,b2.dname,b3.grade from b1,b2,b3 where b1.deptno=b2.deptno and b1.sal between losal and hisal;
    +-------+--------+------------+-------+
    | empno | ename  | dname      | grade |
    +-------+--------+------------+-------+
    |  7369 | smith  | research   |     1 |
    |  7499 | allen  | sales      |     3 |
    |  7521 | ward   | sales      |     2 |
    |  7566 | jones  | research   |     4 |
    |  7654 | martin | sales      |     2 |
    |  7698 | blake  | sales      |     4 |
    |  7782 | clark  | accounting |     4 |
    |  7788 | scott  | research   |     4 |
    |  7839 | king   | accounting |     5 |
    |  7844 | iurner | sales      |     3 |
    |  7900 | james  | sales      |     1 |
    |  7902 | ford   | research   |     4 |
    |  7934 | miller | accounting |     2 |
    +-------+--------+------------+-------+
    13 rows in set (0.00 sec)
    
    展开全文
  • 作为一个select语句保存在数据字典的。视图干什么用的?通过视图可以展现基表的部分数据;视图数据来自定义视图的查询使用的,使用视图动态生成。基表:用来创建视图叫做基表为什么要使用视图?因为...

    在讲解视图的时候我们的明白下面几个概念。

    什么是视图?

    视图(view)是一种虚拟存在的表,是一个逻辑表,本身并不包含数据。作为一个select语句保存在数据字典中的。

    视图是干什么用的?

    通过视图,可以展现基表的部分数据;

    视图数据来自定义视图的查询中使用的表,使用视图动态生成。

    基表:用来创建视图的表叫做基表

    为什么要使用视图?

    因为视图的诸多优点,如下

    1)简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。

    2)安全:使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。

    3)数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。

    总而言之,使用视图的大部分情况是为了保障数据安全性,提高查询效率。

    MySQL中的视图操作

    因为视图是需要基表才能构建,因此在讲解视图的时候,我们需要先创建两张数据表用于后面演示视图操作,下面是测试表和测试数据创建的SQL语句。

    72a0003afa18a7cdaa884296d1bb1d81.png

    1、创建视图

    首先我们来看看创建视图的SQL语法

    b4c9ba0f2183882ac7a3401be4b0700b.png

    OR REPLACE:表示在创建视图时候会替换已有视图

    ALGORITHM:表示视图选择算法,将在文章的后面详细讲解

    select_statement:表示select语句

    [WITH [CASCADED | LOCAL] CHECK OPTION]:表示视图在更新时保证在视图的权限范围之内,详情将在后面讲解

    注意:推荐使用WHIT [CASCADED|LOCAL] CHECK OPTION选项,可以保证数据的安全性,所以建议加上它。

    下面是推荐语法格式:

    656e334d8a6700c3780578533d42a9eb.png

    1.1 创建单表视图

    执行下面的SQL语句创建一个单表视图

    36c80baba05ae8369f6c5b5862f76846.png

    执行结果如下图所示

    使用desc v_author命令查看视图信息,执行结果如下图所示

    然后执行select * from v_author查看视图里面显示的数据,执行结果如下图所示

    acaf68fa0d80d4fe8bcf945327065c20.png

    然后执行select * from v_author查看视图里面显示的数据,执行结果如下图所示

    2403ab31d0643fe8731b3c0241b45b95.png

    1.2 创建多表视图

    执行下面的SQL语句创建一个多表视图

    021228c2d2d2d10a2162b2e0af71a643.png

    然后执行select * from v_blog查看多表视图中的数据,下图是执行结果

    d35d75d845191092b879ad73331ab161.png

    视图将我们不需要的数据过滤掉,将相关的列名用我们自定义的列名替换。视图作为一个访问接口,不管基表的表结构和表名有多复杂。

    如果创建视图时不明确指定视图的列名,那么列名就和定义视图的select子句中的列名完全相同;

    如果显式的指定视图的列名就按照指定的列名。

    注意:显示指定视图列名,要求视图名后面的列的数量必须匹配select子句中的列的数量。

    2、查看视图

    使用show create view语句查看视图信息,比如

    050890c2556967c87a558748f46c5081.png

    视图一旦创建完毕,就可以像一个普通表那样使用,视图主要用来查询,比如

    select * from v_blog where 编号=1;,执行结果如下图

    b8fd0c255e6c5a4c0a296f9d96edf3f4.png

    有关视图的信息记录在information_schema数据库中的views表中,我们可以通过SQL语句来查看,比如

    select * from information_schema.views where TABLE_NAME='v_blog'\G;

    执行结果如下图

    df09472f8d70fdea8bbdc4326724c0bf.png

    3、视图的更改

    3.1 CREATE OR REPLACE VIEW语句修改视图

    create or replace view view_name as select语句;

    在视图存在的情况下可对视图进行修改,视图不在的情况下可创建视图

    3.2 ALTER语句修改视图

    f8c341375b7546ddf4c8d44abbb9035e.png

    注意:修改视图是指修改数据库中已存在的表的定义,当基表的某些字段发生改变时,可以通过修改视图来保持视图和基本表之间一致

    3.3、DML操作更新视图

    因为视图本身没有数据,因此对视图进行的dml操作最终都体现在基表中,比如我们执行以下操作

    c15e0ef7d3fa3c1145033a54fbf03407.png

    当然,视图的DML操作,不是所有的视图都可以做DML操作。

    有下列内容之一,视图不能做DML操作:

    – select子句中包含distinct

    – select子句中包含组函数

    – select语句中包含group by子句

    – select语句中包含order by子句

    – select语句中包含union 、union all等集合运算符

    – where子句中包含相关子查询

    – from子句中包含多个表

    – 如果视图中有计算列,则不能更新

    – 如果基表中有某个具有非空约束的列未出现在视图定义中,则不能做insert操作

    3.4、drop删除视图

    删除视图是指删除数据库中已存在的视图,删除视图时,只能删除视图的定义,不会删除数据,也就是说不会影响基表:

    b5c61d8c8a4b48d491d615f58d572e2e.png

    比如 drop view if exists v_student;

    4、使用WITH CHECK OPTION约束

    对于可以执行DML操作的视图,定义时可以带上WITH CHECK OPTION约束

    作用:对视图所做的DML操作的结果,不能违反视图的WHERE条件的限制。

    首先我在向博客表中插入几条数据

    0e5a17bb9d31fe1cdb46ca2b0ed4c834.png

    然后创建一个视图,获取指定作为为1的数据

    e71aed698c6aaf9ffa21977d95056596.png

    查询一下数据

    87f34cd4d162b1974711dd53f8d21653.png

    再使用update对视图进行修改:

    8bbf7a6b7af0c9e6e4eade3be13817b0.png

    语句执行结果如下图所示

    4027b099d18f773d627bdc97577deb10.png

    因为违反了视图中的where author_id = 1子句,所以抛出异常;

    利用with check option约束限制,保证更新视图是在该视图的权限范围之内。

    使用WITH CHECK OPTION约束时,(不指定选项则默认是CASCADED)

    可以使用CASCADED或者LOCAL选项指定检查的程度:

    CASCADED:检查所有的视图,会检查嵌套视图及其底层的视图

    LOCAL:只检查将要更新的视图本身,嵌套视图不检查其底层的视图

    5、定义视图时的其他选项

    视图的完整语法

    eff9ae49507150c23bc510d14a9b3ea9.png

    5.1 ALGORITHM选项

    选择在处理定义视图的select语句中使用的方法

    – UNDEFINED:MySQL将自动选择所要使用的算法

    – MERGE:将视图的语句与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分

    – TEMPTABLE:将视图的结果存入临时表,然后使用临时表执行语句

    缺省ALGORITHM选项等同于ALGORITHM = UNDEFINED

    5.2 DEFINER选项

    指出谁是视图的创建者或定义者

    – definer= ‘用户名’@’登录主机’

    – 如果不指定该选项,则创建视图的用户就是定义者,指定关键字CURRENT_USER(当前用户)和不指定该选项效果相同

    5.3 SQL SECURITY选项

    要查询一个视图,首先必须要具有对视图的select权限,如果同一个用户对于视图所访问的表没有select权限,那会怎么样?

    SQL SECURITY选项决定执行的结果:

    – SQL SECURITY DEFINER:定义(创建)视图的用户必须对视图所访问的表具有select权限,也就是说将来其他用户访问表的时候以定义者的身份,此时其他用户并没有访问权限。

    – SQL SECURITY INVOKER:访问视图的用户必须对视图所访问的表具有select权限。

    缺省SQL SECURITY选项等同于SQL SECURITY DEFINER

    视图权限总结:

    使用root用户定义一个视图(推荐使用第一种):u1、u2

    1)u1作为定义者定义一个视图,u1对基表有select权限,u2对视图有访问权限:u2是以定义者的身份访问可以查询到基表的内容;

    2)u1作为定义者定义一个视图,u1对基表没有select权限,u2对视图有访问权限,u2对基表有select权限:u2访问视图的时候是以调用者的身份,此时调用者是u2,可以查询到基表的内容。

    展开全文
  • 作为一个select语句保存在数据字典的。视图干什么用的?通过视图可以展现基表的部分数据;视图数据来自定义视图的查询使用的,使用视图动态生成。基表:用来创建视图叫做基表为什么要使用视图?因为...

    在讲解视图的时候我们的明白下面几个概念。

    什么是视图?

    视图(view)是一种虚拟存在的表,是一个逻辑表,本身并不包含数据。作为一个select语句保存在数据字典中的。

    视图是干什么用的?

    通过视图,可以展现基表的部分数据;

    视图数据来自定义视图的查询中使用的表,使用视图动态生成。

    基表:用来创建视图的表叫做基表

    为什么要使用视图?

    因为视图的诸多优点,如下

    1)简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。

    2)安全:使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。

    3)数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。

    总而言之,使用视图的大部分情况是为了保障数据安全性,提高查询效率。

    MySQL中的视图操作

    因为视图是需要基表才能构建,因此在讲解视图的时候,我们需要先创建两张数据表用于后面演示视图操作,下面是测试表和测试数据创建的SQL语句。

    d614d1734d1c1b2c685a941d25c718d6.png

    1、创建视图

    首先我们来看看创建视图的SQL语法

    6555d489f5af7e83dbe174d8462cf90f.png

    OR REPLACE:表示在创建视图时候会替换已有视图

    ALGORITHM:表示视图选择算法,将在文章的后面详细讲解

    select_statement:表示select语句

    [WITH [CASCADED | LOCAL] CHECK OPTION]:表示视图在更新时保证在视图的权限范围之内,详情将在后面讲解

    注意:推荐使用WHIT [CASCADED|LOCAL] CHECK OPTION选项,可以保证数据的安全性,所以建议加上它。

    下面是推荐语法格式:

    e6c339bbfa8a9bf075d825a95a19d30e.png

    1.1 创建单表视图

    执行下面的SQL语句创建一个单表视图

    5ee8d1b4a4cfce95d593163e00ea64ee.png

    执行结果如下图所示

    使用desc v_author命令查看视图信息,执行结果如下图所示

    然后执行select * from v_author查看视图里面显示的数据,执行结果如下图所示

    acaf68fa0d80d4fe8bcf945327065c20.png

    然后执行select * from v_author查看视图里面显示的数据,执行结果如下图所示

    2403ab31d0643fe8731b3c0241b45b95.png

    1.2 创建多表视图

    执行下面的SQL语句创建一个多表视图

    c0a48390174fd6020253b058959c652c.png

    然后执行select * from v_blog查看多表视图中的数据,下图是执行结果

    d35d75d845191092b879ad73331ab161.png

    视图将我们不需要的数据过滤掉,将相关的列名用我们自定义的列名替换。视图作为一个访问接口,不管基表的表结构和表名有多复杂。

    如果创建视图时不明确指定视图的列名,那么列名就和定义视图的select子句中的列名完全相同;

    如果显式的指定视图的列名就按照指定的列名。

    注意:显示指定视图列名,要求视图名后面的列的数量必须匹配select子句中的列的数量。

    2、查看视图

    使用show create view语句查看视图信息,比如

    050890c2556967c87a558748f46c5081.png

    视图一旦创建完毕,就可以像一个普通表那样使用,视图主要用来查询,比如

    select * from v_blog where 编号=1;,执行结果如下图

    b8fd0c255e6c5a4c0a296f9d96edf3f4.png

    有关视图的信息记录在information_schema数据库中的views表中,我们可以通过SQL语句来查看,比如

    select * from information_schema.views where TABLE_NAME=‘v_blog‘\G;

    执行结果如下图

    df09472f8d70fdea8bbdc4326724c0bf.png

    3、视图的更改

    3.1 CREATE OR REPLACE VIEW语句修改视图

    create or replace view view_name as select语句;

    在视图存在的情况下可对视图进行修改,视图不在的情况下可创建视图

    3.2 ALTER语句修改视图

    4f3db562635d8342b46f3a1fcd1e2a1e.png

    注意:修改视图是指修改数据库中已存在的表的定义,当基表的某些字段发生改变时,可以通过修改视图来保持视图和基本表之间一致

    3.3、DML操作更新视图

    因为视图本身没有数据,因此对视图进行的dml操作最终都体现在基表中,比如我们执行以下操作

    c15e0ef7d3fa3c1145033a54fbf03407.png

    当然,视图的DML操作,不是所有的视图都可以做DML操作。

    有下列内容之一,视图不能做DML操作:

    – select子句中包含distinct

    – select子句中包含组函数

    – select语句中包含group by子句

    – select语句中包含order by子句

    – select语句中包含union 、union all等集合运算符

    – where子句中包含相关子查询

    – from子句中包含多个表

    – 如果视图中有计算列,则不能更新

    – 如果基表中有某个具有非空约束的列未出现在视图定义中,则不能做insert操作

    3.4、drop删除视图

    删除视图是指删除数据库中已存在的视图,删除视图时,只能删除视图的定义,不会删除数据,也就是说不会影响基表:

    9942591faa63f50699f1075b842075f6.png

    比如 drop view if exists v_student;

    4、使用WITH CHECK OPTION约束

    对于可以执行DML操作的视图,定义时可以带上WITH CHECK OPTION约束

    作用:对视图所做的DML操作的结果,不能违反视图的WHERE条件的限制。

    首先我在向博客表中插入几条数据

    c175ccca61a650a41cbb02418bd010c8.png

    然后创建一个视图,获取指定作为为1的数据

    380d7fcc0990c42de584d457cb145b89.png

    查询一下数据

    cdd4a244d5263d95b926c6cd343f804b.png

    再使用update对视图进行修改:

    bdfccd0e3f0092f58961dbbcaf994186.png

    语句执行结果如下图所示

    4027b099d18f773d627bdc97577deb10.png

    因为违反了视图中的where author_id = 1子句,所以抛出异常;

    利用with check option约束限制,保证更新视图是在该视图的权限范围之内。

    使用WITH CHECK OPTION约束时,(不指定选项则默认是CASCADED)

    可以使用CASCADED或者LOCAL选项指定检查的程度:

    CASCADED:检查所有的视图,会检查嵌套视图及其底层的视图

    LOCAL:只检查将要更新的视图本身,嵌套视图不检查其底层的视图

    5、定义视图时的其他选项

    视图的完整语法

    7a26404ae0fde50af57b578ccebad84f.png

    5.1 ALGORITHM选项

    选择在处理定义视图的select语句中使用的方法

    – UNDEFINED:MySQL将自动选择所要使用的算法

    – MERGE:将视图的语句与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分

    – TEMPTABLE:将视图的结果存入临时表,然后使用临时表执行语句

    缺省ALGORITHM选项等同于ALGORITHM = UNDEFINED

    5.2 DEFINER选项

    指出谁是视图的创建者或定义者

    – definer= ‘用户名’@’登录主机’

    – 如果不指定该选项,则创建视图的用户就是定义者,指定关键字CURRENT_USER(当前用户)和不指定该选项效果相同

    5.3 SQL SECURITY选项

    要查询一个视图,首先必须要具有对视图的select权限,如果同一个用户对于视图所访问的表没有select权限,那会怎么样?

    SQL SECURITY选项决定执行的结果:

    – SQL SECURITY DEFINER:定义(创建)视图的用户必须对视图所访问的表具有select权限,也就是说将来其他用户访问表的时候以定义者的身份,此时其他用户并没有访问权限。

    – SQL SECURITY INVOKER:访问视图的用户必须对视图所访问的表具有select权限。

    缺省SQL SECURITY选项等同于SQL SECURITY DEFINER

    视图权限总结:

    使用root用户定义一个视图(推荐使用第一种):u1、u2

    1)u1作为定义者定义一个视图,u1对基表有select权限,u2对视图有访问权限:u2是以定义者的身份访问可以查询到基表的内容;

    2)u1作为定义者定义一个视图,u1对基表没有select权限,u2对视图有访问权限,u2对基表有select权限:u2访问视图的时候是以调用者的身份,此时调用者是u2,可以查询到基表的内容。

    原文:https://www.cnblogs.com/ljxt/p/11613167.html

    展开全文
  • MySQL视图操作

    2019-07-31 23:12:10
    什么是视图视图干什么用的?视图(view)是一种虚拟存在的,是一个逻辑,本身并包含数据。作为一个select语句保存在数据字典的。通过视图可以展现基表的部分数据;视图数据来...

    什么是视图?视图是干什么用的?

    视图(view)是一种虚拟存在的表,是一个逻辑表,本身并不包含数据。作为一个select语句保存在数据字典中的。
    通过视图,可以展现基表的部分数据;视图数据来自定义视图的查询中使用的表,使用视图动态生成。基表:用来创建视图的表叫做基表base table.

    为什么要使用视图?

    . 简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。
    . 安全:使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。
    . 数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。

    如何创建视图

    CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
    

    . OR REPLACE:表示替换已有视图
    . ALGORITHM:表示视图选择算法,默认算法是UNDEFINED(未定义的):MySQL自动选择要使用的算法 ;merge合并;temptable临时表
    . column_list:视图字段,显示指定视图列名,要求视图名后面的列的数量必须匹配select子句中的列的数量。
    . select_statement:表示select语句
    . [WITH [CASCADED | LOCAL] CHECK OPTION]:表示视图在更新时保证在视图的权限范围之内
    . cascade是默认值,表示更新视图的时候,要满足视图和表的相关条件
    . local表示更新视图的时候,要满足该视图定义的一个条件即可
    推荐使用WHIT [CASCADED|LOCAL] CHECK OPTION选项,可以保证数据的安全性.

    查看视图内部结构

    show create view 视图名;
    

    删除视图

    drop view 视图名;
    

    如何查看库中的视图

    . 第一种方式

    mysql> show table status where comment='view';
    

    (说明:Mysql5.1支持视图,视图被看作一种抽象表,因此显示视图状态的语句与显示表状态的语句相同,只是在comment列中以‘view’区分)
    . 第二种方式

     mysql> select * from information_schema.tables where
     table_schema='你当前的数据库名' and table_type='view';
    

    (说明:这种方法通过系统表查找,效果同上,显示信息更详细。如果不能正确显示结果,可能是大小写的问题,Mysql在不同系统平台不同配置参数下的显示结果可能不同,注意这点。)

    视图更新限制

    . 视图中如果包含了group by、union等有关的聚合函数,此时视图是不能做更新操作的.
    . 视图中如果出现了关联多表的sql语句,此时修改的字段必须来源同一个基表.
    . 视图如果在定义的时候,使用了临时表的算法是无法进行更新操作.

    视图的限制

    . 视图中无法创建索引
    . 视图中无法创建触发器
    . 视图中不支持物化视图
    . 无法保存视图定义的sql语句

    使用演示

    mysql> create view v_user
        -> as
        -> select * from user
        -> with check option;
    
    mysql> select * from v_user;
    +----+--------+-----+------+
    | id | name   | age | sex  |
    +----+--------+-----+------+
    |  1 | 张三   |  12 | 男   |
    |  2 | 小红   |  10 | 女   |
    |  3 | 李倩   |  12 | 女   |
    +----+--------+-----+------+
    3 rows in set (0.00 sec)
    
    mysql> show create view v_user\G;
    *************************** 1. row ***************************
    View: v_user
    Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_user` AS select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`sex` AS `sex` from `user` WITH CASCADED CHECK OPTION
    character_set_client: utf8
    collation_connection: utf8_general_ci
    1 row in set (0.00 sec)
    
    mysql> drop view v_user;
    
    展开全文
  • MySQL视图示例

    2018-12-16 13:37:04
    什么是视图视图干什么用的?视图(view)是一种虚拟存在的,是一个逻辑,本身并包含数据。作为一个select语句保存在数据字典的。 通过视图可以展现基表的部分数据;视图数据...
  • MySQL之视图

    2019-03-18 09:41:08
    视图干什么用的? A:  视图(view)是一种虚拟存在的,是一个逻辑,本身并包含数据。作为一个select语句保存在数据字典的。  通过视图可以展现基表的部分数据;视图数据来自定义视图的查询使用...
  • 视图干什么用的?A:视图(view)是一种虚拟存在的,是一个逻辑,本身并包含数据.作为一个select语句保存在数据字典的.通过视图,可以展现基表的部分数据;视图数据来自定义视图的查询使用的,使用视图动态生成...
  • 深入解析MySQL视图VIEW

    2019-12-18 18:56:58
    视图干什么用的? 视图(view)是一种虚拟存在的,是一个逻辑,本身并包含数据。作为一个select语句保存在数据字典的。通过视图可以展现基表的部分数据;视图数据来自定义视图的查询使用的,使用...
  • mysql -- 视图

    2019-08-03 11:18:00
    深入解析MySQL视图VIEW ... Q:什么是视图视图干什么用的?... 视图(view)是一种虚拟存在的,是一个逻辑,本身...作为一个select语句保存在数据字典的。  通过视图可以展现基表的部分数据;视图数据...
  • 什么使用视图 视图在数据库应用的比较频繁,主要基于以下几点:重用SQL语句。化复杂的SQL操作。编写查询后,可以方便地重用它而不必知道它的基本查询细节。用的组成部分而不是整个。护数据可以给用户...
  • 下面的文章都有对应的原创精美PDF,持续更新可以来找我催更~ 47页的SpringMVC 92页的Mybatis 129页的多线程 141页的Servlet 158页的JSP 76页的集合 64页的JDBC 105页的数据结构和算法 142页的Spring 58页的...
  • Karen Morton及其团队本书提供了专业的方案:先掌握语言特性,再学习Oracle为提升语言效率而加入的支持特性,进而将两者综合考虑并工作加以应用。作者通过总结各自多年的软件开发和教学培训经验,与大家...
  • Karen Morton及其团队本书提供了专业的方案:先掌握语言特性,再学习Oracle为提升语言效率而加入的支持特性,进而将两者综合考虑并工作加以应用。作者通过总结各自多年的软件开发和教学培训经验,与大家...
  • 但是文档没有实战用例,没有告诉我们哪些可行或者哪些可行,什么情况下可行或者什么情况下可行,为什么可行或者为什么不可行,它只是“公事公办”为你呈上厚厚的一摞文字,告诉你情况就是这样,你自己看着办吧...
  •  本书是一本关于oracle database 9i、10g 和11g 数据库体系结构的权威图书,涵盖了所有重要的oracle 体系结构特性,包括文件、内存结构和进程,锁和闩,事务、并发和多版本,和索引,数据类型,分区和并行,以及...
  • 当然也排除很多 hard 题目也可以暴力模拟,大家平时多注意数据范围即可。 以下是我列举的经典题目(带 91 字样的表示出自 91 天学算法活动): 面试题 17.12. BiNode 0001. 两数之和 0020. 有效的括号 ...
  • 错误: -source 1.7 中不支持 lambda 表达式,请使用 -source 8 或更高版本以启用 lambda 表达式 android { //jdk1.8 compileOptions { sourceCompatibility JavaVersion.VERSION_1_8 targetCompatibility Java...
  • 这里仅列举两个最主要的原因,一方面,随着近年来企业信息化进程的深入,企业里各种系统的数据量持续上升,这一切给数据的管理、维护和安全带来了机会和挑战,另一方面,企业间同质化竞争的加剧,也迫使企业加强...
  • 软件工程教程

    热门讨论 2012-07-06 23:10:29
    用例只描述参与者和系统交互过程做些什么,并描述怎么做。 用例图 关联关系 用例图 泛化关系 用例图 泛化关系 用例图 用例图 用例图 用例用于什么情况? 知道什么情况不用用例 如果没有用到用例,...
  • 只要模版声明视图组件是和什么状态进行绑定的,双向绑定引擎就会状态更新的时候自动更新视图(关于MV*模式的内容,可以看这篇介绍</a>)。 <p>MVVM 可以很好的降低我们维护状态 ->...
  • 13.2.1 设计数据库及结构 222 13.2.2 设计SharePreference存储 223 13.3 项目实现流程 223 13.3.1 创建项目工程 223 13.3.2 项目各功能及界面实现 224 13.3.3 实现数据存取 247 13.3.4 实现Service 252 ...
  • 本书既考虑到实际开发中经常遇到的困惑和难题,也分析了解决问题的思路和方法,更总结出项目开发中不可或缺的技术点及思想。读者可以在欣赏一个个有趣例子的过程中,不知不觉具备开发真正商业项目的能力。 本书集...

空空如也

空空如也

1 2
收藏数 24
精华内容 9
关键字:

在数据表视图中不可以干什么