数据库应用教学课件

数据库应用教学课件第一章数据库基础概念数据组织关系模型系统架构了解数据如何在数据库中被结构化存储掌握主流数据库模型的基本概念和设计探索数据库系统的各层次架构和工作原和高效访问原则理什么是数据库？数据库是按照特定结构组织、存储和管理数据的集合，具备以下特点支持高效的数据存储与检索操作•确保数据一致性和完整性•提供对数据的安全控制机制•允许多用户同时访问并操作数据•关系型数据库是最主流的数据库模型，它通过表格（二维表）的形式来组织和表示数据，表与表之间通过关系连接数据库管理系统（）DBMS数据定义数据操作允许用户创建、修改和删除数据库对象（如表、视图、索引等）的能支持用户对数据进行增加、查询、更新和删除操作，通过数据操作语力，通过数据定义语言（）实现言（）实现DDL DML事务管理安全控制确保数据库操作的原子性、一致性、隔离性和持久性（特性），提供用户身份验证、访问权限控制和数据加密等机制，保护数据库安ACID保障数据的可靠性全数据库系统组成数据硬件有意义的信息集合，是数据库系统的核心组成部分，包括服务器、存储设备、网络设备等物理设施，为包括各种结构化和非结构化数据数据库系统提供运行环境用户软件包括操作系统、数据库管理系统（）以及相DBMS关的应用程序和工具软件数据库三层架构外部模式（用户视图层）概念模式（逻辑层）内部模式（物理层）数据库的优势数据共享一致性保障多个应用程序和用户可以同时访问同一数据库，减少数据冗余，节省存通过约束和事务机制，确保数据的一致性和完整性，防止出现不合理的储空间数据状态并发控制安全管理支持多用户同时访问数据库，通过锁机制和事务隔离级别管理并发操作提供细粒度的权限控制和安全机制，保护数据不被未授权访问或恶意破坏相比传统文件系统，数据库系统在数据管理方面具有显著优势，能够更好地满足现代应用对数据处理的复杂需求关系模型基础表（关系）1二维表格结构，由行和列组成，是关系数据库的基本组织单位例如学生表、课程表行（元组）2表中的每一行代表一个数据实例或记录例如学生表中的一条学生信息列（属性）3表中的每一列代表实体的一个属性例如学生的学号、姓名、年龄等关系模型实例图示键（）Key关系代数是关系数据库的理论基础，包括选择、投影、连接等4操作，为SQL语言提供了数学基础主键唯一标识表中每一行的属性或属性组合外键建立表之间关系的机制，引用另一个表的主键第二章数据库设计与建模掌握专业的数据库设计方法，构建高效、可维护的数据库结构本章将介绍从需求分析到物理实现的完整数据库设计流程，以及E-R建模和规范化理论的应用需求分析与概念设计需求分析步骤实体关系（）建模-E-R收集并分析用户需求，明确系统功能图是一种直观的图形化工具，用于表示概念模型中的实体、属性和关系

1.E-R识别关键实体及其属性

2.实体现实世界中可区分的对象，如学生、课程•确定实体之间的关系

3.属性实体的特性，如学生的学号、姓名•定义业务规则和约束条件

4.关系实体之间的联系，如学生选修课程•概念设计阶段应该关注业务逻辑和数据结构，不需要考虑具体的DBMS实现细节图示例E-R逻辑设计关系模式转换逻辑设计阶段需要将概念模型（图）转换为关系模型（表结构），遵循以下转换规则E-R一对一关系转换实体转换可以合并为一个表，或者在任一表中加入对方的主键作为外键每个实体转换为一个关系表，实体的属性成为表的列，主键属性成为表的主键多对多关系转换一对多关系转换创建新的关系表，包含两个实体的主键（作为联合主键）以及关系的属性在多的一方表中增加一的一方的主键作为外键转换后的关系模式示例学生学号,姓名,性别,年龄,专业课程课程编号,课程名称,学分,课时教师工号,姓名,职称,所属院系选课学号,课程编号,成绩,学期授课课程编号,工号下划线表示主键，外键通过约束在数据库中实现数据库规范化规范化是一种通过分解关系模式来消除数据冗余和异常的过程，目的是设计出高质量的数据库结构第三范式（）3NF第二范式（）2NF满足，并且每个非主属性不传递依赖于2NF第一范式（）1NF满足，并且每个非主属性完全依赖于主主键，消除传递依赖1NF要求关系模式的每个属性都是原子的、不可键，消除部分依赖示例将学生学号姓名系名系主任分,,,再分的消除重复组和多值属性示例将选课学号课程号学生姓名课程解为学生学号姓名系名和系部系名,,,,,,示例将学生学号,姓名,电话号码集合分名称,成绩分解为学生学号,学生姓名、系主任解为学生学号姓名和学生电话学号电课程课程号课程名称和选课学号课程,,,,话号码号成绩,规范化的优点是减少数据冗余、避免异常，缺点是可能导致查询性能下降（需要多表连接）在实际应用中，有时会根据性能需求进行适当的反规范化处理规范化前后表结构对比规范化后（）3NF第三章编程实战SQL语法基础查询技巧高级特性掌握语言的基本语法和结构，了解和学习从简单到复杂的各类查询语句，包括条件筛深入了解事务管理、存储过程和触发器等高级功SQL DDL的区别与应用场景选、排序、分组和多表连接能，提升数据库应用开发能力DML通过实际编码练习，掌握语言在数据库操作中的应用SQL语言概述SQL（结构化查询语言）分类SQL数据定义语言（）DDL用于定义数据库对象（表、索引、视图等）的结构CREATE TABLE,ALTER TABLE,DROP TABLE,CREATE INDEX,CREATE VIEW数据操作语言（）DML用于对数据库中的数据进行增删改查操作SELECT,INSERT,UPDATE,DELETE SQL是一种声明式语言，用户描述要什么而不是怎么做，数据库系统负责确定如何高效执行查询虽然有标准，但不同数据库管理系统（如、、SQL ANSIMySQL OracleSQL数据控制语言（）DCL）在语法上有细微差异Server用于控制数据库的访问权限和安全性GRANT,REVOKE事务控制语言（）TCL用于管理数据库中的事务处理COMMIT,ROLLBACK,SAVEPOINT基础查询语句语句基本结构SELECTSELECT[DISTINCT]列名1,列名2,...FROM表名[WHERE条件表达式][ORDER BY列名[ASC|DESC]][LIMIT限制数量];条件筛选（子句）排序（子句）WHERE ORDER BY比较运算符升序排列（默认）•=,,,,=,=•ASC逻辑运算符降序排列•AND,OR,NOT•DESC•特殊条件BETWEEN...AND...,IN,LIKE,IS NULL•可以按多个列排序--查询年龄在18到22岁之间的学生SELECT*FROM学生WHERE年龄BETWEEN18AND22;--查询计算机系或数学系的学生--按成绩降序排列，成绩相同按学号升序SELECT学号,课程号,成绩FROM选课ORDERBY成绩DESC,学号ASC;SELECT*FROM学生WHERE系名IN计算机系,数学系;多表查询与连接内连接（）外连接（）INNER JOINLEFT/RIGHT JOIN返回两个表中满足连接条件的交集部分返回一个表的所有记录和另一个表中满足连接条件的记录聚合函数与分组查询常用聚合函数与子句GROUP BYHAVING用于对查询结果进行分组，用于对分组结果进行筛选GROUP BYHAVINGCOUNT计算符合条件的行数SELECT课程号,COUNT*AS选课人数,AVG成绩AS平均分,MAX成绩AS最高分,MIN成绩AS最低分FROM选课GROUP BY课程号HAVING AVG成绩70;COUNT*,COUNT列名,COUNTDISTINCT列名SUM计算指定列的总和SUM列名AVG计算指定列的平均值AVG列名MAX/MIN求指定列的最大最小值/MAX列名,MIN列名和的区别WHERE HAVING在分组前对记录进行筛选-WHERE在分组后对分组结果进行筛选-HAVING事务管理基础事务的特性事务控制语句ACID--开始事务START TRANSACTION;--执行一系列操作UPDATE账户SET余额=余额-1000WHERE账号=A;UPDATE账户SET余额=余额+1000WHERE账号=B;--提交事务（成功完成）COMMIT;--或者回滚事务（放弃所有修改）--ROLLBACK;原子性（）Atomicity事务中的所有操作要么全部完成，要么全部不完成，不存在部分完成的情况一致性（）Consistency事务必须使数据库从一个一致性状态转换到另一个一致性状态，保证数据满足完整性约束隔离性（）Isolation多个事务并发执行时，一个事务的执行不应影响其他事务的执行持久性（）Durability一旦事务提交，其所做的修改就会永久保存在数据库中，即使系统发生故障也不会丢失存储过程与触发器简介存储过程触发器存储过程是一组预编译的语句集合，可以接受参数，执行特定的数据库操作，并返回结果触发器是在表上执行的特殊存储过程，当特定事件（如插入、更新或删除）发生时自动触发执行SQLCREATE PROCEDURE计算平均成绩IN学生编号CHAR10,OUT平均分DECIMAL5,2BEGIN SELECTAVG成绩CREATE TRIGGER检查成绩BEFORE INSERT ON选课FOR EACHROWBEGIN IFNEW.成绩0OR NEW.成绩100THENINTO平均分FROM选课WHERE学号=学生编号;END;SIGNAL SQLSTATE45000SET MESSAGE_TEXT=成绩必须在0-100之间;END IF;END;存储过程的优势减少网络流量，提高执行效率•代码重用，便于维护•触发器的应用场景增强安全性，可以控制对底层表的访问•强制复杂的数据完整性规则•自动维护派生数据（如统计信息）•记录数据变更历史（审计跟踪）•第四章数据库管理与新技术掌握数据库管理的核心技能，了解行业最新技术趋势数据库安全与权限管理新兴数据库技术••数据库安全与权限管理用户角色与权限分配数据加密与审计数据库系统通过用户认证和权限控制机制保障数据安全，常见的权限管理方式包括数据传输加密使用保护客户端与服务器之间的通信SSL/TLS数据存储加密对敏感字段或整个数据库进行加密存储--创建用户CREATE USER用户名@主机IDENTIFIED BY密码;--创建角色CREATE密码加密使用哈希算法存储用户密码，而非明文ROLE角色名;--授予权限GRANT SELECT,INSERTON数据库.表名TO用户名@主机数据库审计记录数据库操作，跟踪谁在什么时间做了什么操作;GRANT SELECT,UPDATE ON数据库.*TO角色名;--将角色分配给用户GRANT角色名TO用户名@主机;--撤销权限REVOKE INSERTON数据库.表名FROM用户名@主机;权限粒度可以精确到表级甚至列级，常见权限包括、、、、SELECT INSERTUPDATE DELETE、等CREATE DROP数据库安全是一个多层次的防御体系，需要结合网络安全、应用安全和物理安全共同构建权限应遵循最小权限原则，只授予用户完成工作所需的最小权限集合备份与恢复策略备份类型备份策略设计恢复流程完整备份备份整个数据库的所有数据备份频率根据数据变化频率和重要性确定

1.确定故障点和恢复目标时间点增量备份只备份自上次备份以来发生变化的数据备份窗口选择系统负载较低的时间段执行备份

2.准备恢复环境和备份文件差异备份备份自上次完整备份以来发生变化的所有数据备份存储考虑异地存储和多副本策略

3.执行数据库恢复操作日志备份备份数据库事务日志备份验证定期测试备份的有效性和完整性

4.验证恢复结果的正确性

5.切换应用到恢复后的数据库制定完善的备份与恢复策略是数据库管理的核心任务，需要根据业务需求确定恢复点目标（RPO）和恢复时间目标（RTO），并定期进行恢复演练，确保在实际灾难发生时能够快速有效地恢复数据并发控制与锁机制并发问题锁机制锁的类型多用户同时访问数据库可能导致以下并发问题丢失更新两个事务同时更新同一数据，后一个事务覆盖前一个事务的更新共享锁（读锁）允许多个事务同时读取数据，但阻止写入脏读读取到未提交事务的数据，而该事务可能会回滚排他锁（写锁）独占资源，阻止其他事务读取或写入不可重复读同一事务内两次读取同一数据得到不同结果意向锁表示事务想要在更细粒度上获取特定类型的锁幻读同一事务内两次查询返回的结果集不同乐观锁与悲观锁悲观锁假设冲突经常发生，在访问数据前先加锁乐观锁假设冲突很少发生，只在更新时检查冲突（通常使用版本号或时间戳）死锁处理死锁检测系统自动检测死锁循环死锁预防按固定顺序获取锁，设置锁超时死锁解决选择一个事务作为牺牲者回滚新兴数据库技术趋势数据库NoSQL文档数据库键值数据库存储半结构化的文档数据（如、），适合内容管理和目录应用使用键值对存储数据，提供高性能的读写操作，适合缓存和会话管理JSON XML代表产品、代表产品、MongoDB CouchDBRedis DynamoDB列族数据库图数据库以列而非行为存储单位，适合大规模数据分析和时间序列数据存储实体间的关系，适合社交网络、推荐系统和知识图谱代表产品、代表产品、Cassandra HBaseNeo4j ArangoDB大数据与分布式数据库分布式数据库数据分布在多个节点上，提供更高的可伸缩性和容错能力结合了传统关系数据库的特性和的可扩展性NewSQL ACIDNoSQL时序数据库专为时间序列数据优化的存储系统选择合适的数据库技术应基于具体的业务需求、数据特征和应用场景，而不是简单地追随技术潮流在某些复杂系统中，可能需要多种数据库技术协同工作，形成混合数据架构数据仓库与在线分析处理（）OLAP数据仓库架构OLAP vsOLTP数据仓库是一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合，用于支持管理决策数据源来自不同业务系统的原始数据过程ETL提取、转换、加载数据的过程多维分析Extract TransformLoad系统支持多维度分析数据，常见操作包括OLAP数据仓库存储切片（）选择一个维度上的特定值Slice按主题组织的数据，通常采用星型或雪花模式切块（）选择多个维度上的特定值Dice钻取（）增加减少分析的细节级别Drill-down/up/分析旋转（Pivot）改变维度的查看方向OLAP数据仓库和技术使企业能够从海量历史数据中提取有价值的商业洞察，多维分析、数据挖掘和报表生成OLAP支持决策制定案例分享某电商平台数据库设计与优化订单管理表设计查询性能调优技巧合理建立索引在常用查询条件和连接字段上创建索引--订单主表CREATE TABLE订单订单ID CHAR20PRIMARY KEY,用户ID CHAR20语句优化避免，只查询需要的字段NOT NULL,订单状态TINYINT NOT NULL,支付方式TINYINT,创建时间DATETIME SQLSELECT*NOT NULL,支付时间DATETIME,总金额DECIMAL10,2NOT NULL,FOREIGN KEY分表策略对大表进行水平分片或垂直分片用户ID REFERENCES用户用户ID;--订单明细表CREATE TABLE订单明细明细读写分离使用主从复制架构，分担读写压力ID CHAR20PRIMARY KEY,订单ID CHAR20NOT NULL,商品ID CHAR20NOT事务处理保障数据一致性NULL,数量INT NOTNULL,单价DECIMAL10,2NOTNULL,FOREIGN KEY订单ID REFERENCES订单订单ID,FOREIGN KEY商品ID REFERENCES商品商品ID;电商订单涉及多个操作（扣减库存、创建订单、更新用户积分等），必须使用事务确保原子性，防止出现库存扣减但订单未创建等不一致状态课程总结与学习建议谢谢聆听！欢迎提问与交流问题解答案例讨论后续学习针对课程内容的任何疑问，欢迎随时提分享您在实际应用中遇到的数据库问题了解更多深入学习的途径和资源推荐出和解决方案。