视图1. 介绍本质视图View是一张虚拟表本身不存储数据只存储查询语句的定义。数据来源视图里的数据是从真实表中动态查询出来的。同步性当真实表的数据更新时视图的结果也会自动同步更新。2. 作用简化查询把复杂、重复的 SQL 封装成视图下次直接用视图名查询不用再写长串代码。数据安全只暴露需要的字段隐藏敏感信息如薪资、社保号不同用户看到不同的视图实现权限控制。逻辑解耦业务逻辑变了只需要改视图定义不用改所有调用它的代码。3. 总结视图就是 “预定义的查询结果”像一张表一样用但数据是实时从原表算出来的。4. 视图的创建语法格式CREATE [OR REPLACE] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION];参数介绍如下参数含义核心要点OR REPLACE可选。如果视图已存在则替换它。避免 “视图已存在” 报错适合迭代更新视图定义。view_name视图名称。必须唯一column_list为视图列自定义别名。不指定时默认使用SELECT语句中的列名指定后视图列名即为自定义名称。select_statement视图的核心查询语句。可以是任意合法的SELECT支持多表关联、聚合、窗口函数等。WITH CHECK OPTION限制对视图的更新操作。确保更新 / 插入的数据仍满足视图的WHERE条件防止 “越界更新”。CASCADED / LOCAL配合CHECK OPTION使用。LOCAL只检查当前视图的条件CASCADE默认递归检查所有关联视图的条件。数据准备CREATE DATABASE mydb6_view; use mydb6_view; -- 建表语句 CREATE TABLE IF NOT EXISTS dept ( deptno INT PRIMARY KEY, -- 部门编号 (主键) dname VARCHAR(20), -- 部门名称 loc VARCHAR(20) -- 部门位置 ); -- 插入数据语句 INSERT INTO dept (deptno, dname, loc) VALUES (10, 教研部, 北京), (20, 学工部, 上海), (30, 销售部, 广州), (40, 财务部, 武汉); -- 建表语句 CREATE TABLE IF NOT EXISTS emp ( empno INT PRIMARY KEY, ename VARCHAR(20), job VARCHAR(20), mgr INT, hiredate DATE, sal DECIMAL(10, 2), comm DECIMAL(10, 2), deptno INT, -- 外键约束关联部门表 FOREIGN KEY (deptno) REFERENCES dept(deptno) ); -- 插入数据语句 (根据图片数据提取) INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (1001, 甘宁, 文员, 1013, 2000-12-17, 8000.00, NULL, 20), (1002, 黛绮丝, 销售员, 1006, 2001-02-20, 16000.00, 3000.00, 30), (1003, 殷天正, 销售员, 1006, 2001-02-22, 12500.00, 5000.00, 30), (1004, 刘备, 经理, 1009, 2001-04-02, 29750.00, NULL, 20), (1005, 谢逊, 销售员, 1006, 2001-09-28, 12500.00, 14000.00, 30), (1006, 关羽, 经理, 1009, 2001-05-01, 28500.00, NULL, 30), (1007, 张飞, 经理, 1009, 2001-09-01, 24500.00, NULL, 10), (1008, 诸葛亮, 分析师, 1004, 2007-04-19, 30000.00, NULL, 20), (1009, 曾阿牛, 董事长, NULL, 2001-11-17, 50000.00, NULL, 10), (1010, 韦一笑, 销售员, 1006, 2001-09-08, 15000.00, 0.00, 30), (1011, 周泰, 文员, 1008, 2007-05-23, 11000.00, NULL, 20), (1012, 程普, 文员, 1006, 2001-12-03, 9500.00, NULL, 30), (1013, 庞统, 分析师, 1004, 2001-12-03, 30000.00, NULL, 20), (1014, 黄盖, 文员, 1007, 2002-01-23, 13000.00, NULL, 10); -- 建表语句 CREATE TABLE IF NOT EXISTS salgrade ( grade INT PRIMARY KEY, losal INT, hisal INT ); -- 插入数据语句 INSERT INTO salgrade (grade, losal, hisal) VALUES (1, 7000, 12000), (2, 12010, 14000), (3, 14010, 20000), (4, 20010, 30000), (5, 30010, 99990);举例CREATE OR REPLACE VIEW view1_emp AS SELECT ename, job FROM emp;5. 修改视图修改视图是指更新视图的定义即其背后的查询语句而非修改视图中的数据。在 MySQL 中主要有两种方式修改视图CREATE OR REPLACE VIEW如果视图不存在则创建如果已存在则替换其定义。这是最常用、最推荐的方式。ALTER VIEW专门用于修改已存在的视图定义语法与CREATE VIEW类似。语法格式alter view 视图名 as select 语句;举例ALTER VIEW view1_emp AS SELECT a.deptno, a.dname, a.loc, b.ename, b.sal FROM dept a, emp b WHERE a.deptno b.deptno; select * from view1_emp;6. 更新视图视图更新指通过视图执行INSERT、UPDATE、DELETE语句间接修改底层基表的数据。核心前提视图中的行与基表中的行必须是一对一映射关系。重要原则视图仅作为 “查询窗口”建议仅用视图查询避免通过视图修改数据防止因限制导致更新失败。规则类型具体说明示例可更新条件视图基于单张基表、无复杂逻辑行与基表一一对应CREATE VIEW v_emp_20 AS SELECT ename, sal FROM emp WHERE deptno20;不可更新场景包含聚合函数SUM、MIN、MAX、COUNT 等SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;包含DISTINCT关键字SELECT DISTINCT job FROM emp;包含GROUP BY/HAVING子句SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*)2;包含UNION/UNION ALLSELECT ename FROM emp WHERE deptno10 UNION SELECT ename FROM emp WHERE deptno20;选择列表包含子查询SELECT ename, (SELECT dname FROM dept WHERE dept.deptnoemp.deptno) FROM emp;包含多表JOINSELECT emp.ename, dept.dname FROM emp JOIN dept ON emp.deptnodept.deptno;依赖不可更新的视图 / 仅引用文字值CREATE VIEW v_tmp AS SELECT 测试 AS val;无底层基表WHERE 子句包含引用基表的子查询SELECT ename FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc北京);举例-- 1. 创建可更新视图单表、无复杂逻辑 CREATE OR REPLACE VIEW v_emp_clerk AS SELECT empno, ename, sal FROM emp WHERE job 文员; -- 2. 通过视图更新基表数据成功 UPDATE v_emp_clerk SET sal sal 1000 WHERE ename 甘宁; -- 3. 通过视图删除基表数据成功 DELETE FROM v_emp_clerk WHERE empno 1012; -- 4. 通过视图插入基表数据成功需满足视图和基表约束 INSERT INTO v_emp_clerk (empno, ename, sal) VALUES (1015, 鲁肃, 9000.00);7. 重命名和删除视图语法格式-- 重命名视图 rename table 视图名 to 新视图名; -- 删除视图 drop view if exists 视图名;举例rename table view1_emp to view2_emp; drop view if exists view2_emp;8. 练习举例-- 1查询部门平均薪水最高的部门名称 CREATE OR REPLACE VIEW test_view1 AS SELECT d.dname, t.deptno, t.avg_sal FROM ( -- 一层子查询完成“计算平均薪资 排名” SELECT deptno, AVG(sal) AS avg_sal, RANK() OVER (ORDER BY AVG(sal) DESC) AS rank_sal FROM emp GROUP BY deptno ) AS t INNER JOIN dept d ON t.deptno d.deptno WHERE t.rank_sal 1; -- 2查询员工比所属领导薪资高的部门名、员工名、员工领导编号 -- 2.1 查询员工比所属领导薪资高的数据 CREATE OR REPLACE VIEW test_view2 AS SELECT e1.deptno, e1.empno AS emp_no, e1.ename AS emp_name, e1.sal AS emp_sal, e2.empno AS mgr_no, e2.ename AS mgr_name, e2.sal AS mgr_sal FROM emp as e1 -- e1员工表 JOIN emp as e2 -- e2领导表直接关联无需子查询 ON e1.mgr e2.empno -- 核心修正员工的领导编号 领导的员工编号 WHERE e1.sal e2.sal; -- 员工薪资 领导薪资 -- 2.1 查询部门名、员工名、员工领导编号 CREATE OR REPLACE VIEW test_view3 AS SELECT d.dname, v.emp_name, v.mgr_no FROM dept as d INNER JOIN test_view2 as v ON d.deptno v.deptno -- 3查询工资等级为4级2000年以后入职的工作地点为上海的员工编号、姓名和工资并查询出薪资在前三名的员工信息 -- 3.1 查询工资等级为4级 CREATE OR REPLACE VIEW test_view4 AS SELECT * FROM salgrade WHERE grade 4; -- 3.2 查询2000年以后入职的工作地点为上海的员工编号、姓名和工资 CREATE OR REPLACE VIEW test_view5 AS SELECT empno, ename, sal FROM emp AS e JOIN dept AS d ON e.deptno d.deptno WHERE d.loc 上海 AND YEAR(e.hiredate) 2000 -- 3.3 查询出薪资在前三名的员工信息 CREATE OR REPLACE VIEW test_view6 AS SELECT * FROM( SELECT *, RANK() over(ORDER BY sal DESC) AS ranking FROM test_view5 WHERE sal (SELECT losal FROM test_view4) AND sal (SELECT hisal FROM test_view4) ) as r WHERE ranking 3;