Mysql_view视图语法及介绍

#视图介绍

 

    含义：虚拟表，和普通表一样使用

        mysql5.1版本出现的新特性，是通过表动态生成的数据

        应用场景：

            多个地方用到同样的查询结果

            该查询结果使用的sql语句较复杂

 

     视图与普通表的对比：

                    创建语法的关键字       是否实际占用物理空间            使用

        视图        create view              只是保存sql逻辑                    增删改查，一般不能增删改一般只用到查

        表            create table            保存了数据                              增删改查

 

        #案例：查新姓名以字母k开头的员工名和部门名

            #不用视图做 以前的连接查询

                SELECT Last_name,department_name

                FROM employees e

                INNER JOIN departments d ON e.`department_id`=d.`department_id`

                WHERE e.`last_name` LIKE 'k%';

 

            #视图 就相当于将查询进行封装了

                CREATE VIEW v1

                AS

                SELECT Last_name,department_name

                FROM employees e

                INNER JOIN departments d ON e.`department_id`=d.`department_id`;

            #直接用视图做表进行筛选

                SELECT * FROM v1 WHERE `last_name` LIKE 'k%';

            

select *from v1;　同是也可以查看创建视图的信息表

 

#一、创建视图

        语句：

            create view 视图名

            as

            查询语句;

 

    #案例1.查询姓名中包含a字符的员工名、部门名和工种信息

    #1.创建视图

        CREATE VIEW myv1

        AS

        SELECT Last_name,department_name,j.*

        FROM employees e

        INNER JOIN departments d ON e.department_id=d.department_id

        INNER JOIN jobs j ON e.job_id=j.job_id;

    #2.对视图进行筛选

        SELECT *FROM myv1 WHERE Last_name LIKE '%a%';

 

    #案例2.查询各部门的平均工资级别

    #Ⅰ.创建视图查看每个部门的平均工资

        CREATE VIEW myv2

        AS

        SELECT AVG(e.salary) ag_sal,e.department_id

        FROM employees e

        GROUP BY e.department_id;

 

    #Ⅱ.在Ⅰ的基础上进行一个非等值连接的操作

        SELECT ag_sal,department_id,grade_level

        FROM myv2 v2

        INNER JOIN job_grades g

        ON v2.`ag_sal` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

    #mysql视图是不支持子查询的

        CREATE VIEW myv2

        AS

        SELECT ag_sal,department_id,grade_level

        FROM (#子查询mysql不支持

            SELECT AVG(e.salary) ag_sal,e.department_id

            FROM employees e

            GROUP BY e.department_id

        ) avg_dep

        INNER JOIN job_grades g ON ag_sal BETWEEN g.lowest_sal AND g.highest_sal;

        

 

    #案例3.查询平均工资最低的部门信息

        #1.创建视图 查询每个部门的平均工资的部门信息

        CREATE VIEW myv3

        AS

        SELECT AVG(salary) ag,d.*

        FROM employees e

        INNER JOIN departments d ON e.department_id=d.department_id

        GROUP BY d.department_id;

        #2.在myv3视图基础上进行筛选

        SELECT MIN(myv3.`ag`),myv3.* FROM myv3;

 

    #案例4.查询平均工资最低的部门名和工资

        在myv3视图基础上进行筛选

        SELECT MIN(myv3.`ag`) AS salary,department_name FROM myv3;

 

    #二、视图的修改

    #方式一：

        create or replace view 视图名  v3视图如果存在则替换新的 视图不存在直接创建

        as

        查询语句;

 

        SELECT * FROM v1;

        #将视图v1 替换成查询每个工种的平均工资

            CREATE OR REPLACE VIEW v1

            AS

            SELECT AVG(salary),job_id

            FROM employees

            GROUP BY job_id;

 

    #方式二：

        语法：

        alter view 视图名

        as

        查询语句;

 

        #v3修改为员工工资的级别

            ALTER VIEW v1

            AS

            SELECT last_name,salary,grade_level

            FROM employees e

            INNER JOIN job_grades g

            ON e.salary BETWEEN `lowest_sal` AND `highest_sal`;

 

    #三、删除视图

        语法：drop view 视图名,视图名,视图名,...

        DROP VIEW v1;

 

    #四、查看视图

        #方式一 desc 视图名;

                DESC v1;

        #方式二 show create view 视图名;

                SHOW CREATE VIEW v1;

 

        #案例一、创建视图emp_v1,要求查询电话号码以'011'开头的员工姓名和工资、邮箱

            CREATE OR REPLACE VIEW emp_v1

            AS

            SELECT last_name,salary,email

            FROM employees

            WHERE phone_number LIKE '011%';

            SELECT * FROM emp_v1;

            

 

        #案例二.创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息

            CREATE OR REPLACE VIEW emp_2

            AS

            SELECT MAX(salary) mx,department_id

            FROM employees

            GROUP BY department_id;

          #通过查询emp_v2视图 对视图进行筛选

            SELECT d.*,mx

            FROM emp_2

            INNER JOIN departments d

            ON emp_2.`department_id`=d.`department_id`

            WHERE mx>12000;

        

 

#五、视图的更新

       #可以更改视图的数据

 

        CREATE OR REPLACE VIEW my_info

        AS

        SELECT Last_name,email

        FROM employees;

        SELECT *FROM my_info;

 

    #1.插入

        INSERT INTO my_info VALUE('李四','admi999@qq.com');

        视图插入数据成功 

        

          同时原始employees表中也成功插入数据

 

    #2.更新

        #my_info视图成功修改

         UPDATE my_info SET Last_name='俊杰' WHERE Last_name='李四';

    

    同时原始employees表中也成功修改数据

 

    

  #3.删除

        #my_info视图成功删除 原employees表也修改删除

        DELETE FROM my_info WHERE Last_name='俊杰';

      

         同时原始employees表中也成功删除数据

        

    对于视图的更新、修改、删除、会对原始表进行修改 一般会对视图进行权限限制 一般视图是只读

 

#具备以下特点的视图不允许更新的

    #1.包含以下关键字的sql语句：分组函数、 distinct、 group by、 having、 union或者union all

    #创建视图查询每个部门的最高工资

        CREATE OR REPLACE VIEW max_sa_dep

        AS

        SELECT MAX(salary) mx,department_id

        FROM employees

        GROUP BY department_id;

 

        SELECT *FROM max_sa_dep;

    #更新操作 存在分组函数和group by子句 执行更新操作会     出现不允许更新错误

        UPDATE max_sa_dep SET mx=11111 WHERE department_id=10

    

 

    #2.常量视图

        CREATE OR REPLACE VIEW max_sa_dep

        AS

        SELECT 'tomcat' NAME;

        SELECT *FROM max_sa_dep;

        #对常量视图 也不可更新等相关操作 也会报错

        UPDATE max_sa_dep SET NAME='lucy';

 

    #3.Select中包含子查询

        CREATE OR REPLACE VIEW v2

        AS

        SELECT (SELECT MAX(salary) FROM employees) 最高工资;

        #对Select中包含子查询视图 也不可更新等相关操作 也会报错

        UPDATE v2 SET 最高工资=10000;

 

    #4.join 可以update 但不能insert、delete

        CREATE OR REPLACE VIEW v2

        AS

        SELECT Last_name,department_name

        FROM employees e

        INNER JOIN departments d ON e.department_id=d.department_id;

        #更新 但还是更新没错 但对原始表数据进行了修改  插入就会报异常

        UPDATE v2 SET Last_name='李四' WHERE Last_name='whalen';

        

 

        INSERT INTO v2 VALUE('lucy','xxx');    插入就会报异常

        

 

    #5.from一个不能更新的视图

        CREATE OR REPLACE VIEW v3

        AS

        SELECT * FROM v2; #那视图v2做数据 创建v3视图

        #更新

        INSERT INTO v3 VALUE('lucy','xxx');

        

 

    #6.where子句的子查询引用了from子句中的表

        CREATE OR REPLACE VIEW v4

        AS

        SELECT Last_name,Salary,email

        FROM employees

        WHERE manager_id IN (

            SELECT e.manager_id

            FROM employees e

            WHERE e.manager_id IS NOT NULL

        );

        #更新

        SELECT * FROM v4;

        UPDATE v4 SET Salary=12222 WHERE Last_name='kocjjar';