视图

含义：理解成一张虚拟的表

视图和表的区别：

name | 创建语法的关键字 | 是否实际占用物理空间 | 使用
-|-|-|-
视图 | CREATE VIEW | 只保存了sql逻辑 | 一般不能增删改
表   | CREATE TABLE | 保存了数据 | 增删改查

视图的好处： 
    1、sql语句提高重用性，效率高
    2、和表实现了分离，提高了安全性
    
    #视图
    #案例：查询姓张的学生名和专业名
    SELECT stuname, majorname
    FROM stuinfo s
    INNER JOIN major m ON s.majorid=m.id
    WHERE s.stuname LIKE '张%';
    
    CREATE VIEW v1
    AS 
    SELECT stuname, majorname
    FROM stuinfo s
    INNER JOIN major m ON s.majorid=m.id;
    
    SELECT * FROM v1 WHERE stuname LIKE '张%';
    
    

视图的创建

    语法：
    CREATE VIEW  视图名
    AS
    查询语句;

视图的增删改查

    1、查看视图的数据 ★
    
    SELECT * FROM my_v4;
    SELECT * FROM my_v1 WHERE last_name='Partners';
    
    2、插入视图的数据
    INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90);
    
    3、修改视图的数据
    
    UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹';
    
    
    4、删除视图的数据
    DELETE FROM my_v4;
    

某些视图不能更新

    注意：视图一般用于查询，而不用于更新
    
    包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
    常量视图
    Select中包含子查询
    join
    from一个不能更新的视图
    where子句的子查询引用了from子句中的表

视图逻辑的更新

    #方式一：
    CREATE OR REPLACE VIEW test_v7
    AS
    SELECT last_name FROM employees
    WHERE employee_id>100;
    
    #方式二:
    ALTER VIEW test_v7
    AS
    SELECT employee_id FROM employees;
    
    SELECT * FROM test_v7;

视图的删除

    DROP VIEW test_v1,test_v2,test_v3;

视图结构的查看

    DESC test_v7;
    SHOW CREATE VIEW test_v7;

    #视图
    #案例：查询姓张的学生名和专业名
    SELECT stuname, majorname
    FROM stuinfo s
    INNER JOIN major m ON s.majorid=m.id
    WHERE s.stuname LIKE '张%';
    
    CREATE VIEW v1
    AS 
    SELECT stuname, majorname
    FROM stuinfo s
    INNER JOIN major m ON s.majorid=m.id;
    
    SELECT * FROM v1 WHERE stuname LIKE '张%';
    
    #一、创建视图
    /*
    语法：
    create view 视图名
    as
    查询语句;
    */
    USE myemployees;
    #1.查询邮箱中包含a字符的员工名、部门名和工种信息
    CREATE VIEW myv1
    AS
    
    SELECT last_name,department_name,job_title
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    JOIN jobs j ON j.job_id = e.job_id;
    
    SELECT * FROM myv1 WHERE last_name LIKE '%a%';
    
    #2.查询各部门的平均工资级别
    #创建视图查看每个部门的平均工资
    CREATE VIEW myv2
    AS
    SELECT AVG(salary) ag, department_id
    FROM employees
    GROUP BY department_id;
    
    SELECT myv2.ag,g.grade_level 
    FROM myv2
    JOIN job_grades g
    ON myv2.ag BETWEEN g.lowest_sal AND g.highest_sal;
    
    #3.查询平均工资最低的部门信息
    SELECT *
    FROM myv2
    ORDER BY ag LIMIT 1;
    
    #4.查询平均工资嘴的部门名和工资
    CREATE VIEW myv3
    AS
    SELECT * FROM myv2 ORDER BY ag LIMIT 1;
    
    SELECT d.*,m.ag
    FROM myv3 m
    JOIN departments d
    ON m.department_id = d.department_id;
    
    #二、视图的修改
    #方式一：
    /*
    CREATE OR REPLACE VIEW 视图名
    AS
    查询语句;
    */
    SELECT * FROM myv3;
    CREATE OR REPLACE VIEW myv3
    AS
    SELECT AVG(salary),job_id
    FROM employees
    GROUP BY job_id;
    
    #方式二：
    /*
    ALTER VIEW 视图名
    AS
    查询语句;
    */
    ALTER VIEW myv3
    AS
    SELECT * FROM employees;
    
    #三、删除视图
    /*
    DROP VIEW 视图名,视图名,....,
    */
    DROP VIEW myv1,myv2,myv3;
    
    #四、查看视图
    DESC myv3;
    
    SHOW CREATE VIEW myv3;
    
    #五、视图的更新
    CREATE OR REPLACE VIEW myv1
    AS 
    SELECT last_name,email
    FROM employees;
    
    SELECT * FROM myv1;
    #1.插入
    INSERT INTO myv1 VALUES('张飞','zf@qq.com');
    
    #2.修改
    UPDATE myv1 SET last_name = '张无忌' WHERE last_name = '张飞';
    
    #3.删除
    DELETE FROM myv1 WHERE last_name = '张无忌';
    
    #具备以下特点的视图不允许更新
    #①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
    CREATE OR REPLACE VIEW myv1
    AS
    SELECT MAX(salary),department_id
    FROM employees
    GROUP BY department_id;
    SELECT * FROM myv1;
    #更新
    UPDATE myv1 SET m=9000 WHERE department_id=10;
    
    #②常量视图
    CREATE  OR REPLACE VIEW myv2
    AS
    SELECT 'john' NAME;
    SELECT * FROM myv2;
    #更新
    UPDATE myv2 SET NAME='lucy';
    
    #③SELECT中包含子查询
    CREATE OR REPLACE VIEW myv3
    AS
    SELECT (SELECT MAX(salary) FROM employees) 最高工资;
    #更新
    SELECT * FROM myv3;
    UPDATE myv3 SET 最高工资=1000000;
    
    #④JOIN
    CREATE OR REPLACE VIEW myv4
    AS
    SELECT last_name,department_name
    FROM employees e
    JOIN departments d
    ON e.department_id = d.department_id;
    #更新(可以更新，不能插入)
    SELECT * FROM myv4;
    UPDATE myv4 SET last_name = '张飞' WHERE last_name = 'Whalen';
    INSERT INTO myv4 VALUES('陈真','xxxx');
    
    #⑤FROM一个不能更新的视图
    CREATE OR REPLACE VIEW myv5
    AS
    SELECT * FROM myv3;
    #更新
    SELECT * FROM myv5;
    UPDATE myv5 SET 最高工资 = 10000 WHERE department_id = 60;
    
    #⑥WHERE子句的子查询引用了FROM子句中的表
    CREATE OR REPLACE VIEW myv6
    AS
    SELECT last_name,email,salary
    FROM employees
    WHERE employee_id IN(
        SELECT manager_id
        FROM employees
        WHERE manager_id IS NOT NULL
    );
    
    SELECT * FROM myv6;
    UPDATE myv6 SET salary = 10000 WHERE last_name = 'k_ing';
    
    #一、创建视图emp_v1，要求查询电话号码以'001'开头的员工姓名和工资、邮箱
    CREATE 
        OR REPLACE VIEW emp_v1 AS SELECT
        last_name,
        salary,
        email 
    FROM
        employees 
    WHERE
        phone_number LIKE '011%';
    
    #二、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
    CREATE OR REPLACE VIEW emp_v2 
    AS 
    SELECT
        MAX( salary ) mx_dep,
        department_id 
    FROM
        employees 
    GROUP BY
        department_id 
    HAVING
        MAX( salary ) > 12000;
    
    SELECT d.*,m.mx_dep
    FROM departments d
    JOIN emp_v2 m
    ON m.department_id=d.department_id;