数据库技术与应用（SQL Server）课件 沙有闯 项目7-12 使用视图封装查询-备份和恢复数据库

项目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，要求显示所有学生的姓名、性别、班级、专业和所在系部。总结与实践规范简化复杂查询：在处理多表连接、聚合统计等复杂查询时，通过创建视图将复杂的查询逻辑封装起来，用户只需对视图进行简单查询，就能获取所需的统计结果，大大提高了查询效率和便捷性。精细化权限管理：根据不同用户的角色和权限，创建不同的视图，只向用户授予对特定视图的访问权限，从而实现对数据的精细化控制，确保用户只能访问到其工作所需的数据，提高数据的安全性。数据安全与隐私保护：在视图定义中，可以隐藏敏感字段，如员工的工资信息、客户的身份证号等，防止这些敏感信息被非法获取，保护用户的隐私和数据安全。010302核心应用场景视图名建议以“v_”为前缀，如v_student_details，这样可以清晰地区分视图与表，提高数据库对象的辨识度，方便开发人员进行管理和维护。命名规范避免视图嵌套过深：视图嵌套过深会增加查询的复杂度和执行成本，建议视图嵌套不超过2层，以保证查询性能。复杂聚合查询优先使用索引视图：对于频繁执行的复杂聚合查询，如统计大量数据的总和、平均值等操作，使用索引视图可以显著提升查询性能，减少查询时间。性能优化不通过视图修改聚合字段：由于聚合字段是通过对多个数据进行计算得到的，通过视图修改聚合字段可能会导致数据不一致，因此应避免这种操作。单次更新仅涉及一个基表：为了确保数据的一致性和准确性，通过视图进行数据更新时，应尽量保证单次更新操作仅涉及一个基表，避免同时修改多个基表的数据。更新限制仅授予用户视图的SELECT权限，禁止用户直接访问基表。这样可以有效地限制用户对数据的访问范围，防止用户误操作或恶意篡改基表数据，提高数据的安全性。权限控制最佳实践规范

欢迎大家提出关于视图性能优化方面的问题，比如在大数据量情况下如何创建高效的视图，如何避免视图查询性能低下等。对于复杂场景下的视图设计问题，例如如何在多表关联且数据量庞大的情况下，设计出满足业务需求又能保证性能的视图，大家可以一起探讨交流。针对视图在实际应用中遇到的各种问题，如视图更新失败的排查与解决、视图权限管理的细节等，都可以在此进行提问和交流，共同寻找解决方案。提问与交流谢谢DATABASETECHNOLOGY项目8使用索引优化查询DATABASETECHNOLOGY目录项目描述与学习目标01任务8.1设计索引02任务8.2创建索引03CONTENTS04任务8.3管理索引05任务8.4分析和维护索引06实践规范项目描述与学习目标01Part-DATABASETECHNOLOGY在视图解决复杂查询效率问题后，学生成绩管理系统（SGMS）在运行中仍面临查询性能的挑战。随着数据量的不断增长和用户对查询响应速度要求的提高，仅依靠视图已无法满足系统的高效运行需求，迫切需要进一步优化查询性能。背景为学生成绩管理系统精心设计索引，并持续维护其性能。索引的设计需全面考虑系统的查询模式、数据特点以及未来的扩展性，以确保在各种场景下都能显著提升查询效率；同时，建立完善的性能维护机制，保证索引始终处于最佳工作状态。核心任务学生成绩管理系统涵盖数万名师生的数据，包含丰富的个人信息、教学信息等；此外，还拥有超百万条选课和成绩记录，这些记录详细记录了学生的学习过程和成果，庞大的数据规模对查询性能提出了极高的要求。系统数据规模前面的项目中通过视图解决重复的复杂查询效率提升问题，从而提高开发人员访问数据的便捷性和易用性以及数据库的安全性。作为一名数据库工程师，在数据库设计、数据库实施的过程也需要“未雨绸缪”，考虑数据库系统在日常运行中需要面临艰巨的查询任务，以及随着时间的推移，系统会产生一些性能问题，如查询效率低下等。如何才能提高查询的性能呢？最常用的解决方案就是创建索引。本项目的核心任务是为学生成绩管理系统的主要数据表设计一套索引，并通过创建索引提高查询性能。索引在运行一段时间以后，性能也会不同程度地下降，需要通过分析和维护来维持索引性能。01【项目描述】理解索引的概念、结构和分类深入理解索引是加速表中数据行检索的分散存储结构，如同图书目录一般，能快速定位到所需数据。明晰其结构组成，包括B树结构的各节点作用；熟悉常见的索引分类，如聚集索引、非聚集索引等，以及它们各自的特点和适用场景。掌握设置索引选项的方法透彻了解FILLFACTOR控制叶级索引页填充度、PAD_INDEX控制非叶级索引页填充度的原理和作用；能够根据实际情况合理设置这些选项的值，在减少页拆分、提高查询性能的同时，平衡存储空间的利用。掌握创建索引的方法熟练掌握使用T-SQL语句创建索引的语法，包括创建唯一索引、聚集索引、非聚集索引等不同类型索引的方法；能够准确设置索引选项，如PAD_INDEX、FILLFACTOR等，以优化索引性能。掌握管理索引的方法熟练运用查看索引信息的工具，如sp_helpindex、sp_help等，全面了解索引的详细信息；掌握删除无用索引的方法，使用DROPINDEX语句准确删除不再需要的索引，以减少系统资源的消耗。知识目标能够熟练创建和管理索引能根据需求合理设计关系表的索引会分析索引并维护其性能熟练运用索引分析工具，如SHOWPLAN选项、STATISTICSIO、DBCCSHOWCONTIG等，深入分析索引碎片、使用情况；根据分析结果，灵活运用索引维护方法，如重建索引、ALTERINDEXREORGANIZE、ALTERINDEXREBUILD等，保持索引的高性能。能够深入分析学生成绩管理系统中各关系表的查询需求，综合考虑数据特点、查询频率、数据更新频率等因素，选择合适的索引类型，确定索引列，设计出高效的索引方案，提升系统整体查询性能。无论是使用可视化界面，如在SSMS中通过展开表、进入设计视图、在“管理索引和键”中进行操作；还是使用T-SQL语句，都能准确无误地创建索引，并能根据系统运行情况及时管理索引，如查看索引信息、删除无用索引等。能力目标在索引的设计、创建、管理和维护过程中，不断锻炼逻辑思维能力，学会从复杂的数据和查询需求中抽丝剥茧，分析问题的本质，找到最优解决方案；面对索引相关的性能问题，能够运用所学知识和经验，快速定位问题根源，提出有效的解决措施。强化逻辑思维和问题分析能力深刻认识到索引对系统性能的关键影响，在操作过程中始终保持严谨负责的态度，严格按照标准规范和操作流程进行索引设计、创建、管理和维护；注重细节，避免因疏忽导致的错误，确保系统的稳定高效运行。培养严谨负责的工作态度和规范操作素养职业素质目标任务8.1：设计索引02Part-DATABASETECHNOLOGY为成绩管理数据库各关系表设计索引，提升查询效率全面梳理成绩管理数据库中的各个关系表，深入分析每个表的结构、数据特点以及常用的查询操作；根据分析结果，为每个表设计针对性的索引，确保能够显著提升查询效率，满足系统的性能需求。基于索引结构选择类型并完成设计充分了解不同索引结构的特点和适用场景，如B树结构适用于范围查询、哈希结构适用于等值查询等；结合关系表的查询模式和数据特征，选择最合适的索引类型，并完成详细的索引设计，包括确定索引列、索引顺序等。【任务描述】索引是一种加速表中数据行检索的分散存储结构，就像图书的目录，通过它可以快速定位到所需的数据，避免全表扫描，极大地提高查询效率。索引基于表数据创建，但独立存储索引页面，与表相互关联又相对独立。索引的概念B树结构由根节点、分支节点和叶子节点组成。根节点存储键值范围和指针，用于引导查询方向；分支节点进一步细化键值范围并提供指针；叶子节点存储键值和数据位置，是最终获取数据的地方。索引级数越少，查询时的I/O操作就越少，查询速度也就越快。索引的结构优点：能够大幅加速查询操作，快速定位所需数据；在删除数据时也能提高效率；加快表连接速度，便于多表数据关联查询；减少分组和排序的时间，提高数据处理效率；辅助查询优化，使查询计划更合理；可以实施唯一性约束，保证数据的完整性和准确性。缺点：创建和维护索引需要消耗时间和系统资源，尤其是在数据量较大时；索引会占用额外的物理空间，增加存储成本；数据修改（插入、更新、删除）时，索引需要同步更新，会降低数据修改性能；长期的数据修改可能导致索引产生碎片，影响查询性能。索引的优缺点【知识储备】聚集索引：数据的物理顺序与索引的逻辑顺序一致，一个表只能有一个聚集索引。它适用于范围查询频繁的场景，例如按成绩范围查询学生信息；也适用于主键列，因为主键通常用于唯一标识记录，且查询时经常基于主键进行。非聚集索引：数据的物理顺序与索引的逻辑顺序无关，一个表可以有多个非聚集索引。它适用于频繁单值查询，如按学生姓名查询；以及外键列，方便关联查询不同表之间的数据。选择索引类型例8-1：对于student表，将学号设置为主键并创建聚集索引，因为学号是唯一标识学生的关键信息，且经常用于查询单个学生的详细信息；同时，为姓名和班级编号创建非聚集索引，以满足按姓名或班级查询学生的需求。例8-2：在class表中，将班级编号设置为主键并创建聚集索引，便于快速定位班级信息；为班级名称、专业编号、班长编号创建非聚集索引，以支持按班级名称、专业或班长查询班级相关信息。设计数据表索引（案例）建议创建索引的列：主键列是必须创建索引的，以保证数据的唯一性和快速查询；频繁用于连接操作的列，创建索引可以加快表连接的速度；经常用于范围查询的列，如成绩范围、日期范围等，索引能提高查询效率；用于排序或分组的列，创建索引可以减少排序和分组的时间。不建议创建索引的列：极少被引用的列，创建索引会浪费空间和资源；低基数列，即列中不同值的数量很少，如性别列，索引效果不佳；小表的列，由于数据量小，全表扫描的效率可能更高，创建索引的意义不大。索引设计标准规范【知识储备】任务8.2：创建索引03Part-DATABASETECHNOLOGY根据设计结果创建索引，通过设置PAD_INDEX和FILLFACTOR优化性能本任务将根据索引设计结果，创建成绩管理数据库各关系表中的索引。通过合理地设计PAD_INDEX和FILLFACTOR选项的参数，减少索引页的频繁分页，从而改善索引的性能，提高索引的工作效率。01【任务描述】sqlCREATE[UNIQUE][CLUSTERED|NONCLUSTERED]索引名ON表名(列名[ASC|DESC],...)[WITHPAD_INDEX,FILLFACTOR=值,DROP_EXISTING]例8-6：为student表的ru_date和address列创建组合索引，以满足同时基于这两列进行查询的需求。例8-7：为student表的classid列创建索引，并设置FILLFACTOR为80，以平衡空间和性能。T-SQL创建索引语法【知识储备】使用T-SQL脚本创建索引【任务实施】使用T-SQL创建索引默认情况下，主键列上会默认创建聚集索引。以下两种情况还可以创建聚集索引：一是表上没有主键时；二是在创建主键时指定了非聚集索引，而不是聚集索引。本任务仅为举例说明，而不是实际情况。student表已经在studentid字段创建了主键，实际执行上述脚本时，将会出现“请在创建另一聚集索引前删除现有聚集索引”的错误。【例8-4】模拟在student表的学号列上创建聚集索引。【任务实施】使用T-SQL创建索引聚集索引一般只能有一个，所以就要在查询中常用到的列上创建非聚集索引，而唯一选项一般用于强制数据的唯一性。由于非聚集索引是默认的索引创建方式，所以创建索引的脚本可以简化。【例8-5】在class表的classname列上创建唯一的非聚集索引。任务8.3：管理索引04Part-DATABASETECHNOLOGY索引虽然可以提高查询效率，但是对一个表来说，如果索引过多，不但耗费磁盘空间，而且在修改数据时会增加服务器维护索引的时间。因此，及时有效地维护是非常必要的。学生成绩管理系统常见的索引维护任务包括：查看索引的信息、删除不再需要的索引等。查看索引信息、删除无用索引，减少资源消耗【任务描述】01查看索引工具：sp_helpindex用于查看指定表的索引详情，包括索引名、索引类型、索引列等信息；sp_help不仅可以查看表的结构信息，还能查看表上的索引信息，提供更全面的表和索引相关内容。02删除索引语句：使用DROP

INDEX语句删除索引，语法为DROPINDEX表名.索引名；执行该语句时需谨慎确认，确保删除的是真正无用的索引，以免影响系统的正常运行。【知识储备】例8-10：使用sp_helpindex和sp_help查看student表的索引信息，了解索引的详细情况，为后续的索引管理提供依据。01例8-11：删除student表中名为IX_Student_RuDate_Add的索引，假设该索引已不再被使用或对性能产生负面影响。02【任务实施】【任务实施】使用T-SQL管理索引可以用sp_help查看表格基本信息。如果只查看索引的定义信息，则可以使用sp_helpindex来实现。【例8-10】查看student表上的索引信息。【任务实施】使用T-SQL创建索引对于已经不再使用的索引，可以使用DROPINDEX语句删除，在删除索引的时候，必须指定索引依赖的对象名，即该索引所在的表或视图。不能用DROPINDEX语句删除主键约束或用UNIQUE约束创建的索引，这些索引会在删除约束的时候自动删除。【例8-11】删除student表上的组合索引IX_Student_RuDate_Add。任务8.4：索引的分析与维护05Part-DATABASETECHNOLOGY分析索引碎片、使用情况，通过维护保持性能索引虽然可以提高查询效率，但是对一个表来说，如果索引过多，不但耗费磁盘空间，而且在修改数据时会增加服务器维护索引的时间。因此，及时有效地维护是非常必要的。学生成绩管理系统常见的索引维护任务包括：查看索引的信息、删除不再需要的索引等。【任务描述】重建索引（DROP_EXISTING）：通过删除旧索引并重新创建新索引，彻底整理索引碎片，使索引结构更加紧凑，性能得到显著提升，但在重建过程中会影响用户对数据的访问。ALTERINDEXREORGANIZE：联机重组叶级索引页，在不影响用户访问的情况下，对叶级索引页进行重新组织，减少碎片，但碎片整理效果相对一般。ALTERINDEXREBUILD：联机重建索引，在重建索引的过程中，保持数据的可用性，平衡了性能提升和系统可用性之间的关系。索引维护方法SHOWPLAN选项：通过显示查询执行计划，帮助分析查询过程中索引的使用情况，了解查询优化器如何选择和使用索引，从而发现索引使用不合理的地方。STATISTICSIO：用于统计磁盘I/O活动，通过分析I/O数据，可以评估查询的性能，判断索引是否有效地减少了I/O操作。DBCCSHOWCONTIG：专门用于检测索引碎片，返回索引的碎片信息，如逻辑扫描碎片百分比、区扫描碎片百分比等，帮助确定是否需要进行索引碎片整理。索引分析工具查看：使用DBCCSHOW_STATISTICS(表名,索引名)查看指定表和索引的统计信息，了解索引数据的分布情况，为索引优化提供参考。更新：可以使用UPDATESTATISTICS表名[索引名]更新指定表或索引的统计信息；也可以使用sp_updatestats更新全库的统计信息，确保统计信息的及时性和准确性，以帮助查询优化器生成更合理的查询计划。统计信息管理【知识储备】例8-12：使用SHOWPLAN分析查询“23计算机1班”相关信息时索引的使用情况，通过分析查询执行计划，找出索引使用中存在的问题，如是否存在索引未命中、索引选择不合理等。例8-14：使用DBCCSHOWCONTIG查看student表聚集索引的碎片情况，根据返回的碎片百分比等信息，判断是否需要进行碎片整理。例8-15/16/17：根据例8-14的分析结果，对student表班级编号列索引进行重建或重组操作。如果碎片严重，可使用重建索引的方式；如果碎片较轻，可采用重组索引的方式，以提高索引性能。例8-18/19：使用DBCCSHOW_STATISTICS查看student表主键索引的统计信息，了解主键数据的分布情况；使用UPDATESTATISTICS更新主键索引的统计信息，确保查询优化器能获取最新的统计数据，生成更优的查询计划。02010403任务实施【任务实施】使用T-SQL分析索引使用情况默认情况下，查询语句不显示查询处理过程，若要显示，需要首先打开SHOWPLAN_ALL或SHOWPLAN_text选项。【例8-12】用户需要查询“23计算机1”班学生的学号、姓名和出生日期，请首先显示查询处理过程，查看索引的使用情况。【任务实施】使用T-SQL分析索引使用情况要显示磁盘活动情况，需要首先打开STATISTICSIO选项。【例8-13】用户需要查询“23计算机1”班学生信息，并查看磁盘活动情况。【任务实施】使用T-SQL分析并整理索引碎片可以使用DBCCSHOWCONTIG语句显示索引碎片情况。默认情况下，该语句返回表格聚集索引的碎片情况。【例8-14】查看学生表的聚集索引的碎片情况。【任务实施】使用T-SQL分析并整理索引碎片当遇到查询变慢这样的问题时，需要首先考虑查看索引的性能情况，即有无索引碎片，可以通过使用DBCCSHOWCONTIG语句来实现。当数据存在碎片时，在用户允许的情况下可以重建索引来解决这个问题。【例8-15】系统运行一段时间后，用户发现查询某个学生信息查询功能需要很长时间才能响应，现在需要重建学生表班级编号列的非聚集索引，并修改其填充因子为70。【任务实施】使用T-SQL查看统计信息可以使用DBCCSHOWSTATISTICS语句查看统计信息。【例8-18】查看学生表主键列上的聚集索引的统计信息。实践规范06Part-DATABASETECHNOLOGY索引有很多优点，但不能在表的每一列上创建索引，需要综合考量各种因素来决定需要创建索引的列。那么，具体来说应该考虑在哪些列上建立索引呢？1．考虑创建索引的列（1）主键列。在查询语句中最常见的查询条件就是通过主键列来进行的。在作为主键的列上创建索引可以强制该列的唯一性，组织表中数据的排列结构，加快数据查询的速度。默认情况下，在表上定义主键的时候就会自动创建聚集索引。（2）经常用于连接的列。用于连接的列如果按顺序存放，系统可以很快地执行连接。如外键列，除了用于实现参照完整性约束外，还经常用于连接。（3）经常需要进行范围查询的列。创建索引后，该列就具备一定顺序，其指定的范围也是连续的，可以加快查询速度。（4）经常需要排序的列。因为索引已经排序，因此查询时可以利用索引已有的顺序，从而加快排序的速度。2．不考虑创建索引的列（1）建立索引需要占用系统资源，在进行插入和更新数据的操作时，索引也要花费时间。因此，在没有足够必要的情况下，就不考虑创建索引，如以下几种情况：（2）很少或从来不在查询中引用的列。因为系统很少或从来不根据这个列的值去查找数据，所以不考虑建立索引。（3）只有两个或若干个值的列（如性别列），在这样的列上建立索引是没有意义的。（4）小表（记录数很少的表）一般也没有必要创建索引。实践规范谢谢DATABASETECHNOLOGY项目9使用存储过程

管理数据DATABASETECHNOLOGY项目描述与学习目标任务9.1定义和使用存储过程任务9.2使用事务实现数据完整性目录01020304CONTENTS知识总结与技术规范项目描述与学习与目标01Part-DATABASETECHNOLOGY随着学生成绩管理数据库数据量的增长和业务复杂度的提升，频繁通过简单SQL语句执行数据操作不仅效率低下，还可能因代码冗余导致维护困难。此外，涉及多表联动的操作（如同时更新学生表和成绩表的学号）需要确保数据一致性，单纯依赖逐条执行SQL语句存在一定风险。为解决上述问题，存储过程和事务管理成为关键。存储过程可将高频、复杂的SQL语句封装为可复用的模块，提升执行效率并降低代码冗余；而事务则通过原子性、一致性等特性，确保多步骤操作要么全部成功，要么全部回滚，避免数据不一致问题。本项目任务将以学生成绩管理系统数据库为例，学习存储过程的创建、调用与管理，以及如何通过事务机制保障关键业务逻辑的可靠性，帮助读者掌握高效、安全的数据管理方法。项目描述能力目标1.能够根据需求和数据库设计，独立创建存储过程，满足不同业务场景的需求。2.能在存储过程中熟练使用事务控制语句，实现数据完整性，确保复杂业务操作的可靠性。知识目标1.理解存储过程的作用，认识到它在提高数据库操作效率和简化业务逻辑方面的重要性。2.掌握存储过程的创建和执行方法，包括语法、参数传递等核心要点。3.理解事务的作用和特性，明确事务在保证数据完整性和一致性方面的关键作用。职业素质目标1.通过多样化的存储过程使用方法，培养主动分析问题的能力，能够灵活运用所学知识解决实际问题。2.通过使用事务实现业务规则和数据完整性，引导行业从业人员养成规范做事、严谨做人的职业素养，树立讲规矩、守底线的职业意识。学习目标任务9.1定义和使用存储过程02Part-DATABASETECHNOLOGY在学生成绩管理系统中，学生、成绩数据的批量处理，如班级人数汇总、学业预警等，涉及大量重复性SQL操作。采用传统SQL脚本方式，存在代码冗余度高、执行效率低下及后期维护困难等问题。存储过程是预编译并存储在SQLServer数据库中的数据库对象，具有执行效率高、可重复调用、安全性好等优势，能有效提升系统整体性能。例如，在进行多次相同的复杂查询时，存储过程只需编译一次，后续调用速度更快。本任务主要内容包括设计并实现查询所有学生基本信息及其对应班级名称的存储过程；开发基于班级名称参数的学生信息查询存储过程；创建统计各班级学生人数的计算型存储过程。通过本任务，掌握存储过程的创建语法、参数传递、流程控制和异常处理等核心技术，理解其在提高数据库操作效率、保证数据一致性和简化业务逻辑中的重要作用。任务描述01存储过程的概念

存储过程（Stored

Procedure）是一组为完成特定功能的T-SQL语句集，经编译后存储在SQL

Server服务器端数据库中，是数据库的重要对象。在数据量庞大时，可加速SQL语句执行，提升数据库系统运行效率。优点众多：1.它允许模块化程序设计，创建后可多次调用，提高程序设计效率和应用程序可维护性，也允许应用程序统一访问数据库；2.只在创建时编译，以后执行一般不需重新编译，比一般SQL语句执行速度快，通常快2-10倍；可简化对象权限管理，用户可被授予执行存储过程的权限，而不必拥有直接对存储过程所引用对象的执行权限；3.可提高应用程序安全性，防止SQL注入攻击，因为不能通过拼接字符串调用程序；是一组命名代码，允许延迟绑定，可引用当前不存在但执行时存在的对象；4.可减少网络通信流量，如千条T-SQL语句的命令写成一条执行存储过程的命令，可大大减少客户端和服务器间的通信量。知识储备03存储过程的定义在SQL

Server中，用CREATE

PROCEDURE语句创建存储过程，语法格式如下：CREATE{PROC|PROCEDURE}存储过程名称[;数值选项][{@参数数据类型}[VARYING][=参数的默认值][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]AS{<SQL语句>[;][...n]知识储备1.参数：过程中的参数。参数名必须以“@”为前缀，在CREATEPROCEDURE语句中可以声明一个或多个参数，每个参数都需要确定一个数据类型。其中OUTPUT关键字用来表明该参数是输出参数。2.RECOMPILE：用来表明数据库引擎不缓存该存储过程的执行计划，每次运行时都自动重新编译。3.ENCRYPTION：用来对存储过程的定义进行加密。4.SQL语句：在存储过程中需要执行的操作。参数含义04存储过程的执行创建存储过程后，可以使用EXECUTE（或EXEC）语句来执行存储过程，其语法格式为：[{EXEC|EXECUTE)]{[@返回状态=]{存储过程名}}[[@参数=]{参数值|@变量[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]在上面的语法描述中，各参数说明如下：1.@返回状态：保存存储过程的返回状态，该变量必须在EXEC语句之前声明。2.@参数：输入参数的名称。参数值用来传递给该输入参数的值。3.@变量：用来存放返回参数的值。OUTPUT关键字用来声明输出参数。4.DEFAULT：根据过程的定义，提供参数的默认值。5.WITHRECOMPILE：执行存储过程时强制重新编译。知识储备05存储过程的修改与删除在SQLServer中，可以使用ALTERPROCEDURE语句以命令方式实现存储过程的修改，其语法格式为：ALTER{PROC|PROCEDURE}存储过程名称[;数值][{@参数数据类型}[VARYING][=参数的默认值][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]AS{<SQL语句>[;][...n]其中，各参数的含义与创建时相同。需要注意的是在修改存储过程时要考虑以下因素：（1）ALTERPROCEDURE语句不会更改原存储过程的权限。（2）在创建存储过程时使用的选项，在ALTERPROCEDURE语句中应继续保留。（3）ALTERPROCEDURE语句一般也不能与其他的T-SQL语句混合使用。删除存储过程可以使用DROP命令，其语法格式为：DROPPROCEDURE{存储过程名)[,…n]知识储备使用T-SQL创建存储过程例9-1：创建存储过程proc_stu，查询所有学生信息以及所在班级名称；例9-2：创建存储过程proc_stu_by_class（带有输入参数），查询指定班级的学生信息。任务实施使用T-SQL执行存储过程使用T-SQL执行存储过程例9-4：执行存储过程proc_stu，查询所有学生信息以及所在班级名称；例9-5：执行存储过程proc_stu_by_class，查询“23计算机1”班的学生信息。任务实施使用T-SQL修改与删除存储过程使用T-SQL执行存储过程例9-7：修改存储过程proc_stu，查询所有女生信息以及所在班级名称；例9-8：删除存储过程proc_stu。任务实施任务9.2使用事务实现数据完整性03Part-DATABASETECHNOLOGY在学生成绩管理过程中，学籍信息变更涉及多表数据联动更新，必须确保操作的原子性和数据一致性。例如，将学号为“2023001”（计算机应用技术专业）的同学学号更新为“2023015”（软件技术专业），需同时更新student表学号字段和grade表相关成绩记录。本任务主要内容如下：1.编写事务脚本实现student表和grade表的同步更新；2.验证事务执行效果，确保成功时两表数据同步更新，失败时数据完整回滚。通过本任务学习，理解事务的ACID特性，掌握使用BEGIN

TRANSACTION开启事务、COMMIT提交事务以及出错时ROLLBACK回滚的完整流程。任务描述事务是指用户为完成某项任务所定义的多个操作的序列。在序列中的操作要么全部完成，要么全部不执行。整个序列构成一个不可分割的工作单位，是数据库中不可再分的部分。事务的概念1.自动提交事务；2.显式事务；3.隐式事务；4.批处理级事务。事务类型1.

BEGINTRANSACTION（开始事务）；2.COMMITTRANSACTION（提交事务）；3.SAVETRANSACTION（保存事务）；4.ROLLBACKTRANSACTION（回滚事务）。事务控制语句知识储备知识储备——事务的ACID特性事务的原子性是指事务中包含的所有操作要么全做，要么全不做。原子性（Atomicity）事务开始之前，数据库处于一致性的状态；事务结束后，数据库必须仍处于一致性状态。一致性（Consistency）在事务的处理过程中暂时不一致的数据不能被其他事务应用，直到数据再次一致。隔离性（Isolation）一个事务成功完成后，它对数据库的改变就被保护起来，即便是在系统遇到故障的情况下也不会丢失。持久性（Durability）知识储备——事务的4种类型自动提交事务是指每个单独的T-SQL语句都是一个事务，并且每个T-SQL语句在完成时都被提交或回滚。如果一个语句成功完成，则提交该语句；如果遇到错误，则回滚该语句。自动提交事务显式事务也称为用户定义或用户指定的事务，是指可以显式地在其中定义事务的启动、提交、回滚和结束。每个事务均以BEGINTRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。显式事务在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成。隐式事务只能应用于多个活动结果集（MARS），在MARS会话中启动的T-SQL显式或隐式事务变为批处理级事务。当批处理完成时没有提交或回滚的批处理级事务自动回滚。批处理级事务知识储备——事务的4个控制语句BEGINTRANSACTION语句标记一个本地显式事务的起始点，用于开始事务。其语法如下：BEGIN{TRAN|TRANSACTION}[{transaction_name|@tran_name_variable} [WITHMARK[‘description’]]BEGINTRANSACTION（开始事务）COMMITTRANSACTION语句用于提交事务。标志一个隐性事务或显式事务的结束，将事务所做的数据修改保存到数据库。其语法如下：COMMIT{TRAN|TRANSACTION}[transaction_name|@tran_name_variable]COMMITTRANSACTION（提交事务）SAVETRANSACTION语句用于在事务执行期设置保存点。该保存点可以定义在按条件取消某个事务的一部分后，该事务可以返回的一个位置。其语法如下：SAVE{TRAN｜TRANSACTION}{savepoint_name｜savepoint_variable}SAVETRANSACTION（保存事务）ROLLBACKTRANSACTION语句用于取消（回滚）事务对数据的修改，将显式事务或隐形事务回滚到事务的起点或事务内的某个保存点。其语法如下：ROLLBACK{TRAN|TRANSACTION}[transaction_name|@tran_name_variable|savepoint_name|savepoint_variableROLLBACKTRANSACTION（回滚事务）任务实施——使用T-SQL执行开始与提交事务【例9-9】开始一个事务，student表中studentid的值进行修改，同时修改grade表中studentid的值。任务实施——使用T-SQL执行回滚事务【例9-9】在【例9-9】基础上添加如果操作失败进行事务回滚。知识总结与技术规范04Part-DATABASETECHNOLOGY存储过程和事务可满足用户更高应用需求，存储过程是预编译SQL脚本，能提高数据操作效率，事务可实现比约束更强的业务规则。存储过程分为系统存储过程、自定义存储过程和扩展存储过程三类，用户定义的存储过程可结合事务与预编译SQL脚本实现复杂SQL操作。存储过程中可有输入参数和输出参数，执行动态SQL操作，尤其可基于事务实现复杂数据验证，根据业务需求提交或回滚相关业务。010203知识总结在SQL中创建存储过程时，遵循标准化开发规范与最佳实践，可显著提升代码的可维护性、执行性能和安全性。以下为核心开发规范：1.对象引用规则：存储过程可引用表、视图或其他存储过程。创建阶段允许被引用对象暂不存在，但执行阶段必须确保所有依赖对象已提前创建，否则将引发运行时错误。2.临时表作用域规范：若在存储过程中创建临时表，其生命周期仅限于当前存储过程的执行周期。存储过程执行完毕后，临时表将自动销毁，无需手动删除。3.嵌套定义原则：存储过程支持嵌套定义（即存储过程可调用其他存储过程），但不建议嵌套超过3层，以避免逻辑复杂度失控。4.命名规范：名称需清晰反映存储过程的核心功能。避免系统存储过程使用的“sp_”前缀。存储过程开发规范谢谢DATABASETECHNOLOGY项目10使用触发器维护数据POWERPOINTDESIGN项目描述与学习目标任务10.1创建触发器任务10.2管理和禁用触发器目录010203CONTENTS项目描述与学习目标在AI技术深度渗透各行各业的背景下，学生管理系统的数据生态呈现出多维关联、动态演变的特征。约束虽能保证基础数据的完整性，但难以应对复杂业务规则。例如，学号变更需级联同步至关联表、自动拦截重要数据删除等非法操作等，这些需求仅靠约束难以实现。触发器作为数据库的自动化机制，能够在特定事件（如增删改查）发生时执行预定义逻辑，从而灵活实现业务规则，例如，插入新课程时自动更新课程类型统计，或禁止未经授权的用户对数据库进行删除操作。本项目将结合学生成绩管理系统，深入解析DML触发器、INSTEADOF触发器及DDL触发器的工作原理与应用场景，通过触发器的使用强化数据完整性，提升学生成绩管理系统的健壮性与自动化程度。项目描述01学习目标021.了解触发器的概念和分类；2.理解触发器的工作原理；3.了解inserted和deleted表的作用；4.掌握DML和DDL触发器的一般创建方法。知识目标1.会创建和验证DML触发器；2.会创建和验证DDL触发器；3.能够按照要求修改和删除触发器；4.会禁用和启用触发器。技能目标1.通过触发器定义复杂业务规则，强化分析问题、解决问题的能力；2.能把复杂的操作简单化，面对负责问题时要进行分解、分步骤执行，引导IT行业从业者克服困难，勇于创新。素质目标创建触发器10.1任务-POWERPOINTDESIGN数据库中可以使用约束和触发器两种方法保证数据的有效性和完整性。约束只能实现一些简单的业务规则，遇到较为复杂的业务需求就需要使用触发器来实现。触发器可以自动激活执行，专门用来完成一些复杂业务逻辑。主要任务如下：1．管理员在“学生成绩管理系统”中录入课程信息时，为了保证系统数据的一致性，录入课程后，会同步地更新coursetype表中课程数量；2．管理员在“学生成绩管理系统”中删除课程信息时，为了保证系统数据的一致性，删除课程后，会同步地更新coursetype表中课程数量；3．当“学生成绩管理系统”中学生学号发生改变时，管理员在更新student表中学号后，同步更新grade表中相关学号信息。【任务描述】触发器的作用触发器的工作原理触发器的类型触发器最重要的作用是实现复杂的完整性约束。在SQLServer中，可以用两种方法来保证数据的有效性和完整性：约束和触发器。其中，约束是直接设置于数据表内，只能实现一些比较简单的功能操作，触发器是针对数据表或数据库的特殊存储过程。触发器在特定数据操作发生时自动执行，核心机制依赖系统临时维护的inserted和deleted逻辑表，根据类型（AFTER或INSTEADOF）决定触发时机与执行逻辑。DML触发器的工作原理AFTER触发器的工作原理INSTEADOF触发器的工作原理按照触发事件的不同，触发器分为三类，即DML触发器、DDL触发器和登录触发器。1）DML触发器在数据库中发生数据操作语言（DML）事件时将启用；2）DDL触发器是当服务器或数据库中发生数据定义语言（DDL）事件时调用；3）登录触发器只响应LOGON事件，在登录数据库成功后、用户会话未实际建立前触发。知识储备【任务实施】使用T-SQL脚本创建DML触发器（演示操作）【例10-1】管理员在“学生成绩管理系统”中录入课程信息时，为保证系统数据的一致性，录入课程后，同步更新coursetype表中课程数量。【任务实施】使用T-SQL脚本创建DML触发器（演示操作）【例10-2】管理员在“学生成绩管理系统”删除课程信息时，为保证系统数据的一致性，删除课程后，同步更新coursetype表中的课程数量。在course表上建立一个DELETE触发器，当用户向course表中删除课程时，与之相关联的coursetype表中该课程类型的数量减1。人数可以使用COUNT()函数来统计。表中没有直接存储学生年龄，需要通过调用DATEDIFF()函数来计算得到，再用AVG()函数来统计。【任务实施】使用T-SQL脚本创建DML触发器（演示操作）本任务需要在student表上建立UPDATE触发器，当student表中的学号更改时，触发器被触发，自动将grade表中的相应字段值更改，使之保持一致。获取更改前后的学号需要用到的inserted和deleted表。【例10-3】为student表创建一个UPDATE触发器，当学号发生改变后级联更新grade表中相关学号信息。CREATETRIGGERtri_upd_studentONstudentFORUPDATEASBEGIN

DECLARE@oldsidchar(10),@newsidchar(10) SELECT@oldsid=deleted.studentid,@newsid=inserted.studentid FROMinsertedJOINdeleted ONinserted.studentname=deleted.studentname UPDATEgradeSETstudentid=@newsid WHEREstudentid=@oldsidEND成功创建触发器后，可以通过下列UPDATE语句来验证触发器的工作情况：UPDATEstudentSETstudentid='2023400'WHEREstudentid='2023004'SELECT*FROMgradeWHEREstudentid='2023400'【任务实施】使用T-SQL脚本创建INSTEADOF触发器（演示操作）【例10-4】为teacher表创建一个INSTEADOF触发器，禁止删除教师信息，并提示“禁止删除教师信息”。CREATETRIGGERtri_del_teacherONCREATETRIGGERtri_del_teacherONteacherINSTEADOFDELETEASBEGIN PRINT‘禁止删除教师信息'ENDGO【任务实施】使用T-SQL脚本创建DDL触发器（演示操作）【例10-5】创建DDL触发器，用于保护系统中的数据库不被删除。CREATETRIGGERtri_no_delONALLSERVERforDROP_DATABASEASBEGINPRINT'对不起，您不能删除数据库'ROLLBACKTRANEND【任务实施】使用T-SQL脚本创建DDL触发器（演示操作）【例10-6】创建DDL触发器，禁用对表的修改和删除等管理操作。CREATETRIGGERtri_deny_tableONDATABASEforDROP_TABLE,ALTER_TABLEASBEGINPRINT‘对不起，您不能对数据表进行操作'ROLLBACKTRANEND管理和禁用触发器10.2任务-POWERPOINTDESIGN查看触发器使用系统提供的存储过程sp_help、sp_helptext、sp_depends查看触发器的信息。删除触发器使用DROP

TRIGGER语句可以同时删除一个或多个触发器，禁止和启用触发器当需要禁止某个触发器起作用时（如向有INSERT触发器的表中插入大量数据），可进行禁用操作，触发器仍然存在，只是动作不再执行，直至重新启用。修改触发器使用sp_rename（系统存储过程）可修改触发器名称。也可以使用ALTER

TRIGGER语句修改触发器的定义，关键字及参数说明与创建语句基本相同。知识储备【任务实施】使用T-SQL脚本修改、查看、删除触发器（演示操作）【例10-7】使用SP_RENAME修改触发器tri_del_student名称（改为tri_delete_student）。EXECsp_rename'tri_del_student','tri_delete_student'GO【任务实施】使用T-SQL脚本修改、查看、删除触发器（演示操作）【例10-8】修改触发器tri_upd_student，将其修改为当更新学生学号时，grade表中学号级联更新，同时提示学号已更新消息。ALTERTRIGGERtri_upd_studentONstudentFORUPDATEASBEGINifUPDATE(studentid) BEGIN DECLARE@oldsIDchar(10),@newsidchar(10)SELECT@oldsID=deleted.studentid,@newsid=inserted.studentid FROMinsertedjoindeleted ONinserted.studentname=deleted.studentname UPDATEgrade SETstudentid=@newsid WHEREstudentid=@oldsid PRINT'学号'+@oldsID+'已更新为:'+@newsid ENDEND【任务实施】使用T-SQL脚本修改、查看、删除触发器（演示操作）【例10-9】删除例10-3创建的触发器tri_delete_student。DROPTRIGGERtri_delete_studentGO【任务实施】使用T-SQL脚本禁用触发器（演示操作）【例10-9】禁用student表上所建的触发器tri_upd_student。ALTERTABLEstudentDISABLETRIGGERtri_upd_student--如果要再次启动该触发器，只需将DISABLE改成ENABLE即可GO谢谢DATABASETECHNOLOGY项目11管理数据库安全性POWERPOINTDESIGN目录项目描述与学习目标01任务11.1配置SQLServer安全体系02任务11.2管理服务器级别安全性03CONTENTS04任务11.3管理数据库级别安全性05任务11.4管理数据库对象级别安全性06任务11.5法律法规与数据安全项目描述与学习目标随着大数据、云计算的兴起，网络世界的数据量与日俱增，其安全性越来越受到人们重视。近年来，我国先后颁布并实施《中华人民共和国网络安全法》《中华人民共和国数据安全法》和《中华人民共和国个人信息保护法》，以法律的形式规范网络数据处理活动，保障网络数据安全，促进网络数据依法合理有效利用，维护国家安全和公共利益。学生成绩管理数据库在部署完成以后，向开发人员和终端普通用户交付之前需要进行周密的安全体系设计，为不同的用户（群组）授予合适的权限许可，以确保数据库的安全。根据项目需求，需要完成学生成绩管理系统数据库中Jackie、John、Jones等几种不同的用户角色创建，并为这些用户设置不同的数据库权限。项目描述01学习目标021.了解SQLServer的安全体系；2.了解给予角色的权限管理；3.掌握基本的T-SQL权限管理语句。知识目标1.学会设置SQLServer身份验证模式和密码策略；2.能创建和管理SQLServer数据库用户；3.能为数据库用户分配角色和权限；4.会管理数据库对象的权限。技能目标1.通过数据库安全体系的学习，增强数据库领域信息安全意识；2.通过数据库安全管理与维护的实践锻炼，增强数据管理规范意识、标准意识和工匠精神，强化职业道德素养。素质目标配置SQLServer安全