MySQL---视图（view）

MySQL—视图（view）

视图优点

• 简洁、方便
• 将繁琐的表查询操作转换为对视图的查询，降低了用户查询难度

数据准备

详情见以下文章

[MySQL-- 单表查询](MySQL-- 单表查询_m0_51431003的博客-CSDN博客)

创建单表视图

1.
mysql> create or replace view myview1
    -> as
    -> select empno,ename,job,mgr,hiredate,deptno
    -> from emp
    -> where hiredate<"1981-09-28";
Query OK, 0 rows affected (0.49 sec)

mysql> select * from myview1;
+-------+--------+----------+------+------------+--------+
| empno | ename  | job      | mgr  | hiredate   | deptno |
+-------+--------+----------+------+------------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 |     20 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 |     30 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 |     10 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 |     30 |
+-------+--------+----------+------+------------+--------+
7 rows in set (0.00 sec)

insert into myview1 values(9999,"9999","9999",7839,"1980-12-23",30);

insert into myview1 values(10000,"9999","9999",7839,"1999-12-23",30);

-- 此时没有添加 with check option ，虽然语句2日期不符合条件，也可以添加成功（添加到数据库表中），但查询视图时不会显示

mysql> select * from myview1;
+-------+--------+----------+------+------------+--------+
| empno | ename  | job      | mgr  | hiredate   | deptno |
+-------+--------+----------+------+------------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 |     20 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 |     30 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 |     10 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 |     30 |
|  9999 | 9999   | 9999     | 7839 | 1980-12-23 |     30 |
+-------+--------+----------+------+------------+--------+
8 rows in set (0.00 sec)
2.
mysql> create or replace view myview2
    -> as
    -> select empno,ename,job,mgr,hiredate,deptno from emp
    -> where hiredate<"1981-09-28"
    -> with check option;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into myview2 values(9999,"9999","9999",7839,"1980-12-23",30);
Query OK, 1 row affected (0.22 sec)

mysql> insert into myview2 values(10000,"9999","9999",7839,"1999-12-23",30);
ERROR 1369 (HY000): CHECK OPTION failed 'myab.myview2'

-- 此时添加了 with check option ,（1）可以添加成功，（2）因为日期不符合where 子句，会报错“1369 - CHECK OPTION failed 'myab.myview2'”；

mysql> select * from myview2;
+-------+--------+----------+------+------------+--------+
| empno | ename  | job      | mgr  | hiredate   | deptno |
+-------+--------+----------+------+------------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 |     20 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 |     30 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 |     10 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 |     30 |
|  9999 | 9999   | 9999     | 7839 | 1980-12-23 |     30 |
+-------+--------+----------+------+------------+--------+
8 rows in set (0.00 sec)

创建多表视图

mysql> create or replace view myview3
    -> as
    -> select e.empno,e.ename,e.sal,e.comm,d.deptno,d.dname
    -> from emp e
    -> join dept d
    -> on e.deptno=d.deptno
    -> where e.sal>2500;
Query OK, 0 rows affected (0.49 sec)

mysql> select * from myview3;
+-------+-------+---------+------+--------+------------+
| empno | ename | sal     | comm | deptno | dname      |
+-------+-------+---------+------+--------+------------+
|  7839 | KING  | 5000.00 | NULL |     10 | ACCOUNTING |
|  7566 | JONES | 2975.00 | NULL |     20 | RESTARCH   |
|  7902 | FORD  | 3000.00 | NULL |     20 | RESTARCH   |
|  7698 | BLAKE | 2850.00 | NULL |     30 | SALES      |
+-------+-------+---------+------+--------+------------+
4 rows in set (0.00 sec)

创建统计视图

mysql> create or replace view myview4
    -> as
    -> select e.deptno 部门编号,d.dname 部门名称,avg(sal) 平均工资,max(sal) 最高工资,count(*) 部门人数
    -> from emp e
    -> join dept d
    -> on e.deptno=d.deptno
    -> group by e.deptno
    -> having e.deptno is not null;
Query OK, 0 rows affected (0.53 sec)

mysql> select * from myview4;
+--------------+--------------+--------------+--------------+--------------+
| 部门编号     | 部门名称     | 平均工资     | 最高工资     | 部门人数     |
+--------------+--------------+--------------+--------------+--------------+
|           10 | ACCOUNTING   |  2916.666667 |      5000.00 |            3 |
|           20 | RESTARCH     |  1968.750000 |      3000.00 |            4 |
|           30 | SALES        |  1566.666667 |      2850.00 |            8 |
+--------------+--------------+--------------+--------------+--------------+
3 rows in set (0.00 sec)

创建基于视图的视图

mysql> create or replace view myview5
    -> as
    -> select *
    -> from myview4
    -> where 最高工资>=3000;
Query OK, 0 rows affected (0.52 sec)

mysql> select * from myview5;
+--------------+--------------+--------------+--------------+--------------+
| 部门编号     | 部门名称     | 平均工资     | 最高工资     | 部门人数     |
+--------------+--------------+--------------+--------------+--------------+
|           10 | ACCOUNTING   |  2916.666667 |      5000.00 |            3 |
|           20 | RESTARCH     |  1968.750000 |      3000.00 |            4 |
+--------------+--------------+--------------+--------------+--------------+
2 rows in set (0.00 sec)