数据技术应用 6

项目7使用视图封装查询DATABASETECHNOLOGY项目描述与学习目标任务7.1创建视图任务7.2使用视图目录01020304CONTENTS总结与实践规范项目描述与学习目标“学生成绩管理系统”的核心业务就是数据的查询、插入、删除和修改，其中查询是使用频率最高的。实际业务中，数据查询操作往往比较复杂，比如连接、分组汇总、子查询等等。这些查询操作往往需要访问多张基础数据表，对于数据库管理员之外的用户来说，直接授权操作这些数据表会带来安全隐患。因此，使用视图来封装常用复杂查询是非常有效的手段。学生成绩管理系统中对学生详情的查询、学生成绩详情的查询以及各班级学生成绩的汇总统计等都比较复杂，而且涉及多张表。本项目需要通过视图来封装这些复杂查询，以提高开发人员访问数据的便捷性和易用性，并提高数据库的安全性。项目描述01视图可以对不同用户设置不同的访问权限，只提供其工作所需的数据，避免因直接授权基表访问而带来的数据泄露风险，确保数据的安全性和完整性。在SGMS系统里，借助视图封装像学生详情、成绩统计这类复杂查询。开发人员无需深入了解底层复杂的表结构和关联关系，直接操作视图就能获取所需数据，极大地降低了开发难度和出错概率。优点一优点二为什么要使用视图？02知识目标1.透彻理解视图的概念，明白视图是基于查询语句构建的虚拟表，其数据来源于基表，但并不实际存储数据。2.清晰区分视图的类型，包括标准视图、索引视图和分区视图，掌握它们各自的特点和适用场景。3.精准把握视图与表的区别，从数据存储方式、物理存在形式等方面深入理解，避免在使用过程中混淆。4.熟练掌握视图创建、修改、删除的各种方法，无论是使用T-SQL语句，还是通过可视化界面操作，都能游刃有余。学习目标能力目标1.面对各种业务需求，能够熟练运用所学知识，创建合适的视图。例如，根据复杂的多表关联条件，创建出满足查询需求的视图。2.可以灵活地通过视图进行数据查询，编写高效的查询语句，快速获取所需数据。同时，能够在符合条件的情况下，通过视图安全地更新数据，确保数据的一致性和准确性。3.能够将视图巧妙地运用到实际开发流程中，优化数据访问和处理逻辑，提高开发效率和系统性能。学习目标素质目标1.在操作数据库的过程中，时刻保持规范操作意识，严格遵循数据库开发的最佳实践和行业标准。2.深刻认识到数据安全与隐私保护的重要性，通过合理使用视图，保护敏感数据不被非法访问和泄露。3.严格遵循数据库访问权限管理规范，根据用户角色和职责，精确分配视图访问权限，确保整个数据库系统的安全稳定运行。学习目标创建视图任务-7.1DATABASETECHNOLOGY

数据库管理员为了简化常见的查询操作以及方便对开发人员的权限管理，需要封装常见的查询语句，以视图的形式给开发人员使用。在学生成绩管理系统中，需要定义多个视图封装如下的查询操作：1．学生详情的查询操作：连接学生、班级、专业等基础表查询学生的详细信息。2．学生成绩详情的查询操作：连接学生、成绩、课程等基础表查询成绩详细信息。3．统计学生的平均成绩：连接学生表和成绩表，查询学生选课数量级平均成绩。4．查询所有少数民族学生的数据：从学生表中筛选非汉族学生的详细信息。【任务描述】定义：视图本质上是一种虚拟表，其内容由预先精心定义好的SELECT查询语句构成。它的数据并非物理存储在数据库中，而是在被访问时，动态地从一个或多个基表（甚至可以是其他视图）中获取并组合而成。区别：视图与表的核心区别在于，表是实实在在物理存储实体数据的结构，数据持久地保存在数据库文件中；而视图仅仅保存了查询逻辑，本身不存储任何实际数据，每次查询视图时，都要依据其定义的查询逻辑从基表中获取数据。【知识储备】视图的定义01标准视图由基础的SELECT语句构成，它在数据库中虚拟存在，不占用实际的物理存储空间。适用于简化复杂查询场景，将多表连接、条件筛选等复杂操作封装在视图中，用户只需查询视图即可获取简洁结果；同时在权限控制方面发挥重要作用，通过授予用户对标准视图的访问权限，限制其只能看到特定的数据。标准视图02索引视图是创建了聚集索引的视图，它的数据会被物理存储在数据库中。这种视图能够显著提升查询性能，尤其是在聚合查询频繁的场景下，例如统计大量数据的总和、平均值等操作时，索引视图可以大大减少查询时间。适用于基表更新较少的场景，因为每次基表数据更新时，索引视图也需要相应更新，如果基表频繁更新，会导致索引视图的维护成本过高，反而影响性能。索引视图03分区视图的作用是组合多个表（可以是本地表，也可以是分布式在不同服务器上的表）的数据，将这些分散的数据整合起来，形成一个统一的虚拟表，用户在查询时就像在操作一个完整的大表。主要应用于大型系统的数据分区管理，当数据量巨大，一张表无法满足存储和性能需求时，将数据按一定规则分区存储在多个表中，再通过分区视图进行统一管理和查询，能够提高系统的可扩展性和查询效率。分区视图【知识储备】视图的类型优点简化查询：视图能够巧妙地屏蔽复杂的表结构，将多表连接、聚合操作等复杂逻辑封装起来。用户无需了解底层表的具体结构和关联关系，只需对视图进行简单查询，就能获取所需数据，大大降低了查询的难度和复杂度。权限管控：通过仅授权用户访问视图，而不直接暴露基表，能够有效地限制用户对数据的访问范围。例如，对于某些敏感数据，只有特定权限的用户才能通过视图访问，其他用户则无法获取，从而提高了数据的安全性。数据安全：可以在视图定义中隐藏敏感字段，如密码、身份证号等信息。用户在查询视图时，无法看到这些敏感字段，有效保护了用户的隐私和数据安全。灵活定制：根据不同用户的需求和业务场景，创建个性化的视图。每个用户都可以拥有自己专属的数据视角，只获取与自己工作相关的数据，提高工作效率。【知识储备】优缺点分析缺点性能损耗：当查询视图时，数据库需要将对视图的查询转换为对基表的查询。如果视图定义复杂，涉及多个表的连接和复杂的计算，那么转换后的基表查询可能会消耗更多的资源和时间，从而降低查询效率。更新限制：通过视图修改数据存在诸多限制，例如，视图中包含聚合函数、多表连接、GROUPBY等操作时，往往无法直接通过视图进行数据更新。这是因为这些操作会使视图与基表之间的映射关系变得复杂，难以准确地将更新操作应用到基表中。【知识储备】优缺点分析该视图使用聚合函数COUNT和AVG，统计每个学生的选课门数和平均成绩，并通过GROUP

BY子句按学生进行分组，方便对学生的成绩情况进行统计分析。CREATEVIEW视图名[(列名1,列名2...)][WITHENCRYPTION]--加密视图定义，防止视图定义被他人轻易查看AS<SELECT查询语句>[WITHCHECKOPTION]--强制更新符合查询条件，确保通过视图插入或修改的数据满足视图定义中的条件这个视图通过JOIN操作将student表、class表和speciality表连接起来，整合了学生的基本信息、所在班级以及专业名称，方便查询学生的详细情况。（2）成绩统计视图（聚合函数）此视图筛选出非汉族学生的记录，并通过WITHCHECKOPTION子句确保通过视图插入或修改的数据必须满足民族不为“汉”的条件，保证了视图数据的一致性和准确性。（3）少数民族学生视图（带检查约束）【任务实施】创建视图——T-SQL脚本（SGMS案例）【任务实施】创建视图——T-SQL脚本（SGMS案例）【例7-1】创建视图v_student_details，封装学生详细信息的查询，包含学号、姓名、民族、性别、班级编号、班级名称等数据。【任务实施】创建视图——T-SQL脚本（SGMS案例）将数据库的student、grade、course表连接才可以查询到成绩的详细数据。将类似复杂且常用的查询适合使用视图封装。【例7-2】创建视图v_grade_details，封装成绩明细数据的查询，包含学号、姓名、课程名称、学期、成绩等。【任务实施】创建视图——T-SQL脚本（SGMS案例）将数据库的student、grade表中的有关学生选课和平均成绩的统计数据保存到视图v_grade_statistics中，需要使用COUNT()、AVG()等函数，生成汇总数据。因为聚合函数生成的值没有列名，所以应该用AS关键字指定列名。由于视图是统计后产生的汇总数据，所以该视图只能执行查询操作，数据不可更改。【例7-3】创建视图v_grade_details，封装成绩明细数据的查询，包含学号、姓名、课程名称、学期、成绩等。【任务实施】创建视图——T-SQL脚本（SGMS案例）该任务要使用WITHCHECKOPTION选项，通过该选项创建的视图还可以实现约束功能，当视图中有新记录插入或被修改时，若不符合视图的创建条件，将会被拒绝执行。【例7-4】创建视图v_minority_students，查询所有少数民族的学生信息，从学生表中筛选非汉族的学生数据，并约束后续对该视图的修改必须满足特定的条件。01打开SSMS（SQLServerManagementStudio），这是SQLServer提供的一个集成化管理工具，功能强大，方便用户进行各种数据库操作。展开“SGMS→视图”节点，在“视图”上右键单击，选择“新建视图”，此时会弹出“添加表”对话框，这个对话框用于选择创建视图所需的表。02在“添加表”对话框中，添加相关表，如student、class、speciality、grade、course等，这些表包含了我们创建视图所需的各种数据。添加完成后，关闭对话框。03在“关系图窗格”中，设置表与表之间的连接关系，例如设置student.classid=class.classid，通过这些连接条件，将不同表中的数据关联起来，形成一个完整的数据集合。04勾选目标列，如studentname、classname、coursename等，这些列将构成视图的输出内容，用户查询视图时将看到这些列的数据。05在“条件窗格”设置筛选条件，比如设置specialityname='计算机应用技术'，这样视图将只返回专业为“计算机应用技术”的数据，满足特定的查询需求。06完成上述设置后，单击“保存”按钮，在弹出的对话框中输入视图名“v_student_cs”，然后点击确定，即可完成视图的创建。【任务实施】创建视图——可视化界面（SGMS案例）【任务实施】创建视图——可视化界面（演示操作）“添加表”对话框：展示了可添加的相关表，直观呈现了创建视图时可供选择的数据来源，方便用户快速定位和添加所需表。视图设计器（关系图窗格+条件窗格）：清晰地呈现了表连接和筛选条件设置，用户可以一目了然地看到视图的数据来源和数据筛选逻辑，便于检查和调整视图的定义。自动生成的SQL脚本：基于上述操作生成的对应查询语句，这是可视化操作的背后逻辑体现，用户可以通过查看SQL脚本，进一步了解视图的创建原理，也方便在需要时进行手动修改和优化。010203【任务实施】创建视图——可视化界面（SGMS案例）使用视图任务-7.2DATABASETECHNOLOGY视图与基本表的使用方法基本相同，可以应用视图进行基本表的数据查询，就像对基本表的查询一样，直接使用SELECT语句查询视图。在满足一定条件下，可以应用视图进行数据的添加、删除、更新操作。在本任务中，需要将原来对学生、成绩、班级等基本表的数据操作，转换为对视图的查询、添加、修改和删除。【任务描述】对视图进行查询的操作方式与对普通表的查询基本一致，用户可以使用熟悉的SELECT语句，结合WHERE、GROUP

BY等子句进行灵活的数据查询。例如，上述第一条语句查询出所有男生的详细信息，第二条语句统计了各专业的学生人数。【知识储备】视图的查询操作通过视图插入数据时，需要满足视图定义中的条件。在v_minority_students视图中，由于设置了WITHCHECKOPTION，所以插入的数据必须满足民族不为“汉”的条件。如上述示例，第一条插入语句符合条件，能够成功插入数据；第二条插入语句违反了条件，将会插入失败。（1）插入数据（基于v_minority_students）01通过视图修改数据时，如果只涉及单个基表的字段修改，且满足视图的其他限制条件，通常可以成功修改。但如果修改操作涉及多个基表的字段，由于视图与基表之间的复杂映射关系，往往会导致修改失败。如上述示例，第一条修改语句只修改了grade表中的成绩字段，能够成功；第二条修改语句同时涉及student表和course表的字段修改，将会报错。（2）修改数据（单表更新）02通过视图删除数据时，实际上是同步删除基表中的对应数据。例如，上述语句通过v_minority_students视图删除了studentid为'2024098'的学生记录，同时也会在基表student中删除该记录，确保数据的一致性。（3）删除数据03【知识储备】视图的数据更新操作使用系统存储过程可以方便地查看视图的各种信息。EXEC

sp_help用于查看视图的基本信息，包括视图的列信息、数据类型等；EXEC

sp_helptext用于查看视图的定义脚本，即创建视图时使用的SELECT语句；EXEC

sp_depends用于查看视图依赖的基表，了解视图的数据来源。01【任务实施】使用T-SQL管理视图——查看视图当业务需求发生变化时，可以使用ALTERVIEW语句修改视图的定义。例如，上述示例中，通过修改视图的筛选条件，将专业范围从原来的“计算机应用技术”扩大到“计算机应用技术”和“软件技术”，以满足新的查询需求。【任务实施】使用T-SQL管理视图——修改视图1.

可以使用系统存储过程EXEC

sp_rename对视图进行重命名，使其名称更符合业务需求或命名规范。2.使用DROP

VIEW语句可以删除视图，删除视图不会影响基表中的数据，只是删除了视图的定义和相关的元数据信息。01【任务实施】使用T-SQL管理视图——重命名与删除【任务实施】基于视图查询数据——T-SQL脚本（SGMS案例）对视图进行查询操作没有任何限制，和对基表的查询一致。与基表的查询一样，视图也可以进行数据筛选、指定数据列等操作。视图也可以与基本表一样，进行分组汇总、连接等操作，也可以在此基础上再定义新的视图。【例7-2】查询视图v_student_details中的数据，根据实际需要对数据进行筛选、数据统计等操作。【任务实施】使用视图更新基本表数据——T-SQL脚本（SGMS案例）学生表中学号、姓名、学分字段不能为空，因此INSERTINTO语句中至少包含这三个字段，并且该视图定义数据必须为少数民族。插入后的数据可以通过视图查询来验证数据是否添加成功，也可以通过对基表的查询，验证是否写入到基表。【例7-7】基于视图v_minority_students，向学生表添加一条记录（学号为“2024098”，姓名为“张三”，民族为“回”，学分为“25”）。【任务实施】管理视图——T-SQL脚本（SGMS案例）查看视图的基本信息可以使用SP_HELP实现，查看视图的定义脚本可以使用SP_HELPtext实现，视图的数据一般来自一个或多个数据表，查看视图依赖的对象可以使用SP_DEPENDS实现。【例7-10】查看视图v_student_details的基本信息。【任务实施】使用可视化界面管理视图【例7-14】修改视图v_student_details，要求显示所有学生的姓名、性别、班级、专业和所在系部。总结与实践规范简化复杂查询：在处理多表连接、聚合统计等复杂查询时，通过创建视图将复杂的查询逻辑封装起来，用户只需对视图进行简单查询，就能获取所需的统计结果，大大提高了查询效率和便捷性。精细化权限管理：根据不同用户的角色和权限，创建不同的视图，只向用户授予对特定视图的访问权限，从而实现对数据的精细化控制，确保用户只能访问到其工作所需的数据，