项目5 数据库优化与编程

计算机专业课程湖南财工职业学院MySQL版主讲：数据库技术及应用xxx目录页ContentsPage项目1数据库认识与体验项目2数据库设计与建模项目3数据库构建与维护项目4数据库查询与分析项目5数据库优化与编程项目6数据库安全与管理项目7电商数据库应用实战项目8嵌入式数据库应用过渡页TRANSITIONPAGE项目5数据库优化与编程任务5.1视图创建与管理任务5.2索引创建与管理任务5.3事务处理任务5.4SQL编程知识结构全景导览【项目导图】项目五应用开发核心知识体系视图创建与管理任务5.15.1.1创建与查看视图5.1.2修改和删除视图5.1.3使用视图操作数据任务二解析数据库视图的定义与价值【知识梳理】项目2数据库对象进阶与应用实践1.视图的概念与特性视图的核心概念视图是一种虚拟表，其内容由SQL查询语句动态定义，本身并不实际存储数据记录，而是在用户调用视图时，从关联的基表中实时检索并动态生成结果集，如同数据库中的“动态查询窗口”。视图的四大核心特性虚拟性：仅存储查询逻辑而非实际数据，数据始终与基表保持同步更新。简单性：封装复杂的多表关联查询，用户只需调用视图名称即可获取结果，降低操作门槛。安全性：可按需求隐藏敏感字段或数据行，实现精细化的用户数据访问权限控制。独立性：隔离底层基表的结构变更，只要视图查询逻辑兼容，上层应用无需修改代码。项目5数据库高级应用实践任务5-1视图的创建与管理任务5-1-1创建与查看视图核心语法与实战案例解析视图定义：视图是基于查询结果的虚拟表，它将复杂的SELECT查询逻辑封装起来。使用视图可以简化数据访问、屏蔽底层表结构细节，并提高数据查询的安全性与效率。01.创建视图(CREATEVIEW)语法：CREATEVIEWview_nameASSELECT字段FROM表名[JOIN关联表ON条件];示例：CREATEVIEWview_uordersASSELECTm.username,o.order_no,o.order_timeFROMmembersmJOINordersoONm.id=o.user_id;02.查询视图数据(SELECT)语法：SELECT*FROMview_name[WHERE筛选条件];示例：SELECT*FROMview_uorders;（效果如左图，直接获取用户与订单的关联信息，无需重复编写复杂的JOIN查询）任务5-1视图的创建与管理任务5-1-2修改和删除视图项目5数据库视图高级应用01.视图的修改与删除核心语法核心任务：掌握使用CREATEORREPLACEVIEW/ALTERVIEW语句修改视图结构，熟练运用DROPVIEW语句删除不再使用的视图，通过实操理解视图定义变更与对象删除的执行逻辑。SQL示例：CREATEORREPLACEVIEWv_studentASSELECT*FROMstudentWHEREage>18;（修改视图）|DROPVIEWIFEXISTSview_uorders;（删除视图）项目5数据库视图与索引应用任务5-1视图创建与数据操作任务5-1-3使用视图操作数据实战：视图的查询与基表写入演示核心要点：视图不仅能简化复杂查询（如筛选特定条件的数据），在满足单表等限制条件下，还可直接通过视图对基表进行插入、更新和删除操作，实现数据的安全管理与便捷维护。--示例1：通过视图查询库存充足的商品

SELECT*FROMview_phonegoodsWHERE库存>100;

--示例2：通过视图向基表插入新的分类数据（如上图所示结果）

INSERTINTOview_categoryVALUES(28,NULL,'CAT004','汽车');索引创建与管理任务5.25.2.1创建索引5.2.2管理索引任务一探索数据库索引机制【知识梳理】项目1数据库性能优化基础1.索引的概念与分类01索引的核心概念索引是数据库中为加速数据检索而创建的一种附加数据结构，其作用类似于书籍的“目录”。它能帮助数据库系统快速定位目标数据的物理存储位置，从而避免对整张表进行逐行扫描，显著提升查询效率。02索引的常见分类维度●按功能属性划分：包含主键索引（唯一且非空）、唯一索引（值不重复）、普通索引（无限制）、全文索引（用于文本检索）。●按数据结构划分：主流为B+树索引（支持范围查询），此外还有哈希索引（适用于等值查询）、R-Tree索引（适用于空间数据）等。●按字段数量划分：分为单列索引（基于单个字段）和复合索引（基于多个字段组合，遵循最左前缀原则）。图解：索引与表数据的映射机制如图所示，左侧是索引结构，存储了关键字段的值和指向实际数据的指针（磁盘地址）；右侧是真实的表数据记录。当执行查询时，数据库引擎首先在索引中快速找到匹配的指针，然后直接通过指针读取磁盘上的数据行，这就是索引加速查询的核心原理。任务5-2索引创建与管理任务5-2-1创建索引项目5数据库性能优化实战5.2.1掌握索引创建的核心语法索引创建主要分为两个场景：①建表时直接声明索引，语法更简洁；②对已存在的表通过ALTER或CREATEINDEX追加索引，灵活性更高。合理的索引设计是提升数据库查询效率的关键手段。CREATETABLEtable_name(...,INDEXidx_name(col_name));（建表时创建）|ALTERTABLEtable_nameADDINDEXidx_name(col_name);（表后添加）任务五数据库索引管理与优化任务5-2-2管理索引项目5MySQL数据库性能优化实战5.2.2索引的查看、删除与修改技巧核心任务：掌握MySQL中索引的生命周期管理，包括使用SQL命令查看表的索引元数据、删除低效或冗余的索引；理解MySQL不支持直接修改索引的特性，通过“先删除旧索引，再创建新索引”的标准流程来实现索引结构的更新，从而优化数据库查询效率。关键SQL命令速查：1.查看索引：SHOWINDEXFROMgoodshistory;——列出表中所有索引的详细信息，包括类型、唯一性、字段等。2.删除索引：DROPINDEXidx_goods_titleONgoodshistory;——删除指定名称的索引，注意：删除主键索引需使用ALTERTABLE命令。事务处理任务5.3任务5.3.1事务操作任务5.3.2查看与设置隔离级别任务二剖析事务核心机制【知识梳理】项目2数据库事务与并发控制02.事务的ACID四大特性原子性(Atomicity)——不可分割的整体事务是数据库中不可分割的最小工作单元，其包含的所有操作要么全部执行成功，要么全部失败回滚，不存在执行一半的中间状态。一致性(Consistency)——数据状态的正确事务执行前后，数据库的完整性约束条件始终保持满足，数据从一个合法状态转换到另一个合法状态，不会出现逻辑错误。隔离性(Isolation)——并发操作的独立多个事务并发执行时，每个事务的内部操作与其他事务相互隔离，彼此之间互不干扰，每个事务都感觉不到其他事务的存在。持久性(Durability)——结果的永久有效事务一旦提交成功，其对数据库中数据的修改就是永久性的，这种修改会被持久化到存储介质中，后续的系统故障或重启都不会丢失。任务5-3-1事务操作项目5数据库事务与锁机制任务五事务处理实操01.事务操作核心指令解析核心目标：掌握事务的开启、提交与回滚三大关键操作，理解事务的原子性如何保障数据的一致性与完整性。01开启事务STARTTRANSACTION;启动新事务，后续SQL操作被纳入管理，此时数据修改仅在内存中，并未持久化到数据库。02提交事务COMMIT;当所有操作执行无误后执行提交，将事务内的所有修改永久保存到数据库，事务正常结束。03回滚事务ROLLBACK;若操作出错或发生异常，执行回滚，撤销所有未提交的修改，数据库恢复到事务开始前状态。任务三事务隔离与并发控制任务5-3-2查看与设置隔离级别项目5数据库事务管理与优化1.隔离级别查看指令与配置实战核心目标：掌握MySQL事务隔离级别的查询语法、动态设置方式，理解四种隔离级别的特性与应用场景。▍查看当前隔离级别SELECT@@transaction_isolation;说明：MySQL8.0起使用该参数，5.7及之前版本使用@@tx_isolation。▍设置会话隔离级别（读已提交）SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;说明：该设置仅对当前数据库连接会话生效，关闭连接后恢复默认。四种隔离级别排序（从低到高）：读未提交(ReadUncommitted)→读已提交(ReadCommitted)→可重复读(RepeatableRead，MySQL默认)→串行化(Serializable)SQL编程任务5.45.4.1SQL语言基础5.4.2游标使用5.4.3自定义函数5.4.4存储过程5.4.5触发器使用任务二探索SQL编程核心语法【知识梳理】项目2SQL编程基础进阶SQL编程基础核心体系01变量体系：数据暂存的载体包含系统变量（全局GLOBAL/会话SESSION级）、用户自定义变量（@变量名）与存储过程局部变量（DECLARE声明），是SQL编程中实现数据传递、临时存储与动态计算的基础单元。02运算符集：逻辑计算的规则涵盖四大核心类型：算术运算（+、-、*、/、%）实现数值计算，赋值运算（:=、=）完成变量赋值，逻辑运算（AND、OR、NOT）构建条件判断，比较运算（=、>、<、IN、LIKE）实现数据关系校验，是编写SQL表达式的核心规则。03流程控制：复杂逻辑的骨架条件分支：IF单/多条件判断、CASE多分支选择，实现“按条件执行不同逻辑”的需求；循环结构：WHILE（先判断后执行）、LOOP（无限循环）、REPEAT（先执行后判断），满足重复执行场景；跳转控制：LEAVE（退出循环/程序块）、ITERATE（跳过本次循环），灵活控制执行流程。任务5-4掌握流程控制结构任务5-4-1SQL流程控制使用项目5数据库编程与存储过程1.条件分支：IF...ELSE语句的语法与实践任务核心：学习SQL中的条件分支控制逻辑，掌握IF...ELSE...ENDIF的基础语法结构；通过“分数判定成绩等级”的实际案例，理解如何在数据库端实现业务规则的动态判断与执行。任务5数据库函数与存储过程应用任务5-4-3自定义函数项目5数据库进阶开发与优化01.函数定义与调用核心语法核心概念：自定义函数用于封装可复用的计算逻辑，通过参数传递数据并返回计算结果。创建后可直接在SQL语句中调用，简化复杂查询与业务计算。创建：CREATEFUNCTION函数名(参数)RETURNS类型...RETURN值;

调用：SELECT函数名(入参);图示：自定义函数调用的实际执行效果示例，展示函数返回的计算结果任务五掌握数据库可编程对象任务5-4-4存储过程项目5数据库编程进阶与优化01.存储过程的创建与调用解析核心目标：深入理解存储过程的预编译特性与业务逻辑封装优势，熟练掌握其创建语法、调用方式，以及IN（输入）、OUT（输出）、INOUT（输入输出）三类参数的配置与应用场景。01核心创建语法CREATEPROCEDUREsp_name([params])

BEGIN...END;支持多条SQL语句的逻辑封装，预编译后存储于数据库服务端。02快捷调用方式CALLsp_name(参数列表);通过CALL命令直接触发执行，简化复杂业务逻辑的调用过程。03灵活参数机制IN(入参)/OUT(出参)/INOUT支持数据的单向传入、结果返回以及双向交互，满足多样化需求。任务五数据库触发器与存储过程任务5-4-5触发器使用项目5数据库高级应用开发1.触发器创建语法与核心作用任务目标：掌握触发器的创建语法结构，理解其作为“表事件驱动程序”的特性，能够识别INSERT/UPDATE/DELETE触发时机并描述其自动执行的业务价值。⚡核心语法结构(CREATETRIGGER)CREATETRIGGERtrigger_nametrigger_time(BEFORE/