Mysql基础部分(18)---视图

朴灿烈づ我的快乐病毒、 2023-03-14 05:52 14阅读 0赞

Mysql基础部分内容：  
[Mysql基础部分(1)—基础操作指令与语法规范][Mysql_1]  
[Mysql基础部分(2)—基础查询][Mysql_2]  
[Mysql基础部分(3)—条件查询][Mysql_3]  
[Mysql基础部分(4)—排序查询][Mysql_4]  
[Mysql基础部分(5)—常见函数][Mysql_5]  
[Mysql基础部分(6)—分组函数][Mysql_6]  
[Mysql基础部分(7)—分组查询][Mysql_7]  
[Mysql基础部分(8)—sql99语法][Mysql_8_sql99]  
[Mysql基础部分(9)—子查询][Mysql_9]  
[Mysql基础部分(10)—分页查询][Mysql_10]  
[Mysql基础部分(11)—联合查询][Mysql_11]  
[Mysql基础部分(12)—数据的增删改][Mysql_12]  
[Mysql基础部分(13)—库和表的管理][Mysql_13]  
[Mysql基础部分(14)—常见的数据类型][Mysql_14]  
[Mysql基础部分(15)—常见约束][Mysql_15]  
[Mysql基础部分(16)—标识列][Mysql_16]  
[Mysql基础部分(17)—事务][Mysql_17]  
[Mysql基础部分(18)—视图][Mysql_18]  
[Mysql基础部分(19)—变量][Mysql_19]  
[Mysql基础部分(20)—存储过程][Mysql_20]  
[Mysql基础部分(21)—函数][Mysql_21]  
[Mysql基础部分(22)—流程控制结构][Mysql_22]

#视图
    /* 含义：虚拟表，和普通表一样使用 mysql5.1版本出现的新特性，是通过表动态生成的数据 比如：舞蹈班和普通班的对比 创建语法的关键字 s是否实际占用物理空间 使用 视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改 表 create table 保存了数据 增删改查 */
    
    #案例：查询姓张的学生名和专业名
    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 emp_v1,emp_v2,myv3;
    
    #四、查看视图
    DESC myv3;
    
    SHOW CREATE VIEW myv3;
    
    #五、视图的更新
    CREATE OR REPLACE VIEW myv1
    AS 
    SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) 'annual salary'
    FROM employees;
    
    CREATE OR REPLACE VIEW myv1
    AS 
    SELECT last_name,email
    FROM employees;
    
    SELECT * FROM myv1;
    SELECT * FROM employees;
    #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) m,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 department_id,(SELECT MAX(salary) FROM employees) 最高工资
    FROM departments;
    
    SELECT * FROM myv3;
    #更新
    UPDATE myv3 SET 最高工资=100000;
    
    #④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('陈真','xxx');
    
    #⑤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';

[Mysql_1]: https://blog.csdn.net/weixin_43900321/article/details/105664839
[Mysql_2]: https://blog.csdn.net/weixin_43900321/article/details/105729424
[Mysql_3]: https://blog.csdn.net/weixin_43900321/article/details/105729465
[Mysql_4]: https://blog.csdn.net/weixin_43900321/article/details/105729506
[Mysql_5]: https://blog.csdn.net/weixin_43900321/article/details/105729526
[Mysql_6]: https://blog.csdn.net/weixin_43900321/article/details/105729554
[Mysql_7]: https://blog.csdn.net/weixin_43900321/article/details/105729593
[Mysql_8_sql99]: https://blog.csdn.net/weixin_43900321/article/details/105810240
[Mysql_9]: https://blog.csdn.net/weixin_43900321/article/details/105849897
[Mysql_10]: https://blog.csdn.net/weixin_43900321/article/details/105869419
[Mysql_11]: https://blog.csdn.net/weixin_43900321/article/details/105978422
[Mysql_12]: https://blog.csdn.net/weixin_43900321/article/details/105978492
[Mysql_13]: https://blog.csdn.net/weixin_43900321/article/details/106000721
[Mysql_14]: https://blog.csdn.net/weixin_43900321/article/details/106000793
[Mysql_15]: https://blog.csdn.net/weixin_43900321/article/details/106057742
[Mysql_16]: https://blog.csdn.net/weixin_43900321/article/details/106057793
[Mysql_17]: https://blog.csdn.net/weixin_43900321/article/details/106156900
[Mysql_18]: https://blog.csdn.net/weixin_43900321/article/details/106156942
[Mysql_19]: https://blog.csdn.net/weixin_43900321/article/details/106233375
[Mysql_20]: https://blog.csdn.net/weixin_43900321/article/details/106233530
[Mysql_21]: https://blog.csdn.net/weixin_43900321/article/details/106233577
[Mysql_22]: https://blog.csdn.net/weixin_43900321/article/details/106253492