长春移动端网站设计_模板之家怎么免费下载_seo实战论坛_南宁网络推广服务商

视图 

        定义：

                是基于一个或多个表（或视图）的虚拟表，本质是预定义的查询语句。

        特点：

                1.视图不存储实际数据，仅保存查询逻辑。

                2.数据动态生成，随基表数据变化而变化。

                3.可以像普通表一样进行查询、过滤、连接等操作。

        作用：

                1.简化复杂查询：将多表连接、聚合等复杂操作封装为视图，简化后续查询

                2.权限控制：隐藏敏感字段，仅开放视图权限而非基表权限

                3.数据抽象：为不同用户或应用提供定制化数据视图

        示例：

-- 创建视图：查询学生姓名和成绩
CREATE VIEW student_grade_view AS
SELECT s.name, g.score
FROM students s
JOIN grades g ON s.id = g.student_id;-- 使用视图
SELECT * FROM student_grade_view WHERE score > 80;
-- 删除视图
DROP VIEW IF EXISTS student_grade_view;

使用注意事项

        1.性能影响：

                潜在问题：视图的查询效率依赖基表及查询逻辑。若视图嵌套复杂查询（如多表连接，子查询），可能导致性能下降。

                优化建议： 

                        1.避免在视图中使用复杂聚合或嵌套视图

                        2.确保基表有合适的索引

        2.权限管理：

                创建权限：用户需要拥有CREATE VIEW 权限及基表的SELCET权限

                访问权限：视图的权限独立于基表，需要单独授权

        3.依赖关系：

                基表结构变更：若基表字段被删除或重命名，依赖该字段的视图将会失效，需要重新定义视图

                检查依赖：通过 SHOW CREATE VIEW view_name查看视图的依赖关系

        4.命名冲突：

                避免视图与基表同名，否则可能导致查询歧义

        5.使用场景：

                适合场景：

                        简化高频复杂查询

                        数据权限隔离（如隐藏敏感字段）

                不适合场景：

                        需要高性能的查询（优先用优化后的sql）

                        需要频繁更新数据库的操作（优先直接操作基表）

下面是我再实际项目中遇到的情况，数据库版本是5.7.38的，无法执行CTE，所以采用的视图的方式

该语句是要查询到属于123456单位下的岗位信息，且岗位是空的，没有分配给人员，还要根据岗位所属的部门id，判断是xxxx部门还是zzzz部门（这两种情况是岗位直接属于单位，没有部门处室等信息和岗位直属与部门 没有处室）。而且查询得到的List结果还要合并在已有的list中（为了快速处理漏问题采用了该方法）。

该语句可以实现功能，但是查询很慢

SELECT* 
FROM(SELECTa.postId,a.postName,ifnull( a.postCode, a.postName ) AS postCode,a.duty,a.amount AS postAmount,a.isCadre,a.postSort,NULL AS id,NULL AS `name`,NULL AS number,CASEWHEN d.type = 0 THENa.deptId ELSE ( SELECT unitId FROM sys_dept WHERE deptId = a.deptId ) END unitId,
CASEWHEN d.type = 1 THENa.deptId WHEN d.type = 2 THEN( SELECT parentId FROM sys_dept WHERE deptId = a.deptId ) ELSE NULL END deptId,
CASEWHEN d.type = 2 THENa.deptId ELSE NULL END officeId,
CASEWHEN d.type = 0 THEN'xxxx部门' WHEN d.type = 1 THEN'zzzz部门' ELSE ( SELECT shortName FROM sys_dept WHERE deptId = a.deptId ) END officeName,
CASEWHEN d.type = 1 THEN( SELECT shortName FROM sys_dept WHERE deptId = a.deptId ) WHEN d.type = 2 THEN(SELECTshortName FROMsys_dept WHEREdeptId = ( SELECT parentId FROM sys_dept WHERE deptId = a.deptId )) ELSE '部门' END deptName,
CASEWHEN d.type = 0 THENd.orderNum WHEN d.type = 1 THEN( SELECT orderNum FROM sys_dept WHERE deptId =( SELECT unitId FROM sys_dept WHERE deptId = a.deptId ) ) WHEN d.type = 2 THEN( SELECT orderNum FROM sys_dept WHERE deptId =( SELECT unitId FROM sys_dept WHERE deptId = a.deptId ) ) ELSE 999 END unitOrderNum,
CASEWHEN d.type = 0 THEN'999' WHEN d.type = 1 THEN( SELECT orderNum FROM sys_dept WHERE deptId = a.deptId ) WHEN d.type = 2 THEN( SELECT orderNum FROM sys_dept WHERE deptId =( SELECT parentId FROM sys_dept WHERE deptId = a.deptId ) ) ELSE 999 END deptOrderNUm,
CASEWHEN d.type = 2 THENd.orderNum ELSE 999 END officeIdOrderNum,NULL AS startDate,NULL AS endDate,NULL AS label,NULL AS beforeRetireDate,NULL AS retireDate,NULL AS clerkDate,NULL AS employTime,NULL AS remark 
FROM(SELECT* FROMsys_post WHEREpostId NOT IN (SELECT DISTINCTpostId FROM(SELECTpostId FROMemp_basic eLEFT JOIN sys_dept d ON e.unitId = d.unitId WHEREe.postId > '' AND d.oneUnitId = '123456' UNION ALLSELECTborrowJob AS postId FROMemp_basic eLEFT JOIN sys_dept d ON e.borrowUnitId = d.unitId WHEREe.borrowJob > '' AND d.oneUnitId = '123456' ) b ) ) aLEFT JOIN sys_dept d ON a.deptId = d.deptId 
WHEREd.oneUnitId = '123456' AND d.shortName NOT IN ( 'xxxxxx', 'xxxxxxx' ) ) a 
ORDER BYunitOrderNum ASC,deptOrderNUm ASC,officeIdOrderNum ASC,isCadre DESC,postId ASC

sql运行时间

优化方法

方案一：CTE语法

 但是数据库版本不支持

 -- 定义公共表表达式 used_posts，用于存储已使用的岗位 IDWITH used_posts AS (-- 从 emp_basic 表和 sys_dept 表连接查询已使用的岗位 IDSELECT DISTINCT postIdFROM (-- 查询正常岗位的岗位 IDSELECT postIdFROM emp_basic eLEFT JOIN sys_dept d ON e.unitId = d.unitIdWHERE e.postId > '' AND d.oneUnitId = '123456'-- 联合查询借用岗位的岗位 IDUNION ALLSELECT borrowJob AS postIdFROM emp_basic eLEFT JOIN sys_dept d ON e.borrowUnitId = d.unitIdWHERE e.borrowJob > '' AND d.oneUnitId = '123456') b),-- 定义公共表表达式 available_posts，用于存储可用的岗位信息available_posts AS (-- 从 sys_post 表中选择不在已使用岗位 ID 列表中的岗位信息SELECT *FROM sys_postWHERE postId NOT IN (SELECT postId FROM used_posts)),-- 定义公共表表达式 dept_info，用于存储部门相关信息dept_info AS (-- 从 sys_dept 表中选择符合条件的部门信息SELECTdeptId,unitId,parentId,shortName,orderNum,type,oneUnitIdFROMsys_deptWHEREoneUnitId = '123456'AND shortName NOT IN ('xxxxx', 'zzzzzz'))-- 主查询，从 available_posts 和 dept_info 表连接查询所需信息SELECT * from (SELECTa.postId,a.postName,IFNULL(a.postCode, a.postName) AS postCode,a.duty,a.amount AS postAmount,a.isCadre,a.postSort,NULL AS id,NULL AS `name`,NULL AS number,CASEWHEN d.type = 0 THEN a.deptIdELSE d.unitIdEND AS unitId,CASEWHEN d.type = 1 THEN a.deptIdWHEN d.type = 2 THEN (SELECT parentId FROM dept_info di WHERE di.deptId = a.deptId)ELSE NULLEND AS deptId,CASEWHEN d.type = 2 THEN a.deptIdELSE NULLEND AS officeId,CASEWHEN d.type = 0 THEN 'xxxx部门'WHEN d.type = 1 THEN 'zzzz部门'ELSE d.shortNameEND AS officeName,CASEWHEN d.type = 1 THEN d.shortNameWHEN d.type = 2 THEN (SELECT shortName FROM dept_info di WHERE di.deptId = (SELECT parentId FROM dept_info di2 WHERE di2.deptId = a.deptId))ELSE '部门'END AS deptName,CASEWHEN d.type = 0 THEN d.orderNumWHEN d.type = 1 THEN (SELECT orderNum FROM dept_info di WHERE di.deptId = d.unitId)WHEN d.type = 2 THEN (SELECT orderNum FROM dept_info di WHERE di.deptId = d.unitId)ELSE 999END AS unitOrderNum,CASEWHEN d.type = 0 THEN '999'WHEN d.type = 1 THEN d.orderNumWHEN d.type = 2 THEN (SELECT orderNum FROM dept_info di WHERE di.deptId = (SELECT parentId FROM dept_info di2 WHERE di2.deptId = a.deptId))ELSE 999END AS deptOrderNUm,CASEWHEN d.type = 2 THEN d.orderNumELSE 999END AS officeIdOrderNum,NULL AS startDate,NULL AS endDate,NULL AS label,NULL AS beforeRetireDate,NULL AS retireDate,NULL AS clerkDate,NULL AS employTime,NULL AS remarkFROMavailable_posts aLEFT JOINdept_info d ON a.deptId = d.deptId where unitId > ''ORDER BYunitOrderNum ASC,deptOrderNUm ASC,officeIdOrderNum ASC,isCadre DESC,postId ASC ) s

方案二

创建视图

 创建员工岗位id视图，岗位id有两种来源，两种来源的岗位id均需要剔除掉

CREATE OR REPLACE VIEW used_posts ASSELECT DISTINCT postIdFROM (SELECT postIdFROM emp_basic eLEFT JOIN sys_dept d ON e.unitId = d.unitIdWHERE e.postId > '' AND d.oneUnitId = '123456'UNION ALLSELECT borrowJob AS postIdFROM emp_basic eLEFT JOIN sys_dept d ON e.borrowUnitId = d.unitIdWHERE e.borrowJob > '' AND d.oneUnitId = '123456') b;

 创建岗位视图，岗位id不在员工岗位id视图中的数据

CREATE OR REPLACE VIEW available_posts ASSELECT *FROM sys_postWHERE postId NOT IN (SELECT postId FROM used_posts);

 创建部门信息视图：查询123456单位下的数据以及单位名称简称不属于xxxx和zzzzz

 CREATE OR REPLACE VIEW dept_info ASSELECTdeptId,unitId,parentId,shortName,orderNum,type,oneUnitIdFROMsys_deptWHEREoneUnitId = '123456'AND shortName NOT IN ('xxxx', 'zzzzz');

 最终的查询都来自视图数据，所有的嵌套查询全部剔除，只有一个left join 外层包了一个selcet * from 是因为后续有查询条件

SELECT *FROM (SELECTa.postId,a.postName,IFNULL(a.postCode, a.postName) AS postCode,a.duty,a.amount AS postAmount,a.isCadre,a.postSort,NULL AS id,NULL AS `name`,NULL AS number,CASEWHEN d.type = 0 THEN a.deptIdELSE d.unitIdEND AS unitId,CASEWHEN d.type = 1 THEN a.deptIdWHEN d.type = 2 THEN (SELECT parentId FROM dept_info di WHERE di.deptId = a.deptId)ELSE NULLEND AS deptId,CASEWHEN d.type = 2 THEN a.deptIdELSE NULLEND AS officeId,CASEWHEN d.type = 0 THEN 'xxxx部门'WHEN d.type = 1 THEN 'zzzz部门'ELSE d.shortNameEND AS officeName,CASEWHEN d.type = 1 THEN d.shortNameWHEN d.type = 2 THEN (SELECT shortName FROM dept_info di WHERE di.deptId = (SELECT parentId FROM dept_info di2 WHERE di2.deptId = a.deptId))ELSE '部门'END AS deptName,CASEWHEN d.type = 0 THEN d.orderNumWHEN d.type = 1 THEN (SELECT orderNum FROM dept_info di WHERE di.deptId = d.unitId)WHEN d.type = 2 THEN (SELECT orderNum FROM dept_info di WHERE di.deptId = d.unitId)ELSE 999END AS unitOrderNum,CASEWHEN d.type = 0 THEN '999'WHEN d.type = 1 THEN d.orderNumWHEN d.type = 2 THEN (SELECT orderNum FROM dept_info di WHERE di.deptId = (SELECT parentId FROM dept_info di2 WHERE di2.deptId = a.deptId))ELSE 999END AS deptOrderNUm,CASEWHEN d.type = 2 THEN d.orderNumELSE 999END AS officeIdOrderNum,NULL AS startDate,NULL AS endDate,NULL AS label,NULL AS beforeRetireDate,NULL AS retireDate,NULL AS clerkDate,NULL AS employTime,NULL AS remarkFROMavailable_posts aLEFT JOINdept_info d ON a.deptId = d.deptIdWHEREunitId > ''ORDER BYunitOrderNum ASC,deptOrderNUm ASC,officeIdOrderNum ASC,isCadre DESC,postId ASC) s

 最后的sql运行时间