《数据库技术与应用》课件演示文稿

数据库技术与应用课程概述课程目标学习内容考核方式本课程的目标是使大家能够理解数据课程内容涵盖数据库基础、SQL语言库的基本概念和原理，熟练掌握SQL基础、数据库设计、数据库编程、数语言，能够进行数据库设计和编程，据库事务与并发控制、数据库索引与了解数据库安全与优化技术，掌握数优化、数据库安全与备份恢复、数据据仓库与数据挖掘的基本概念，了解仓库与数据挖掘、新兴数据库技术等新兴数据库技术多个方面第一章数据库基础数据库概念数据库系统12数据库是按照数据结构来数据库系统是由数据库、组织、存储和管理数据的数据库管理系统仓库，其特点是数据的结（DBMS）、应用系统、构化、共享性、独立性和数据库管理员和用户组成可控制冗余的计算机系统数据模型数据库管理系统（）DBMS的功能常见产品DBMS DBMSDBMS是数据库系统的核心组成部分，负责数据的定义、常见的DBMS产品包括Oracle、MySQL、SQL Server、数据的组织、存储和管理、数据的操纵、数据的安全保护、PostgreSQL、DB

2、MongoDB、Redis等不同的数据的完整性控制和并发控制等功能DBMS产品具有不同的特点和适用场景数据库系统的发展历史文件系统1早期的数据管理方式，存在数据冗余、一致性差、共享性差等问题层次模型和网状模型2解决了文件系统的一些问题，但结构复杂，灵活性差，难以满足复杂的应用需求关系模型3基于关系代数理论，结构简单，易于理解和使用，成为目前最主流的数据库模型对象关系模型4融合了面向对象技术，支持复杂数据类型和对象操作，适用于处理复杂应用的数据关系数据库关系模型的基本概念关系模型使用关系（表）来表示数据，关系由元组（行）和属性（列）组成关系模型的核心是关系代数，用于描述数据的操作关系的特征关系中的每个属性都必须是原子性的，即不可再分；关系中的元组是无序的；关系中的属性是无序的；关系中不允许有完全相同的元组实体联系模型（模型）-E-R实体与属性联系类型实体是现实世界中可以区分的对象，1联系是实体之间的关联关系，包括如学生、课程等属性是实体的特2一对一联系、一对多联系和多对多征，如学生的姓名、年龄等联系图的绘制E-R实体表示属性表示联系表示使用矩形表示实体，矩形内写实体名使用椭圆形表示属性，椭圆形内写属使用菱形表示联系，菱形内写联系名，性名，并用线连接到对应的实体并用线连接到相关的实体第二章语言基础SQL简介1SQLSQL（Structured QueryLanguage）是结构化查询语言，是访问和管理数据库的标准语言分类2SQLSQL主要分为数据定义语言（DDL）、数据操作语言（DML）和数据查询语言（DQL）数据定义语言（）DDL语句语句语句CREATE ALTERDROP用于创建数据库、表、索引等数据库用于修改数据库、表等数据库对象的用于删除数据库、表、索引等数据库对象结构对象创建数据库语法CREATE DATABASECREATEDATABASE database_name;数据库命名规则数据库名应具有描述性，易于理解，避免使用特殊字符，长度不宜过长创建表语法数据类型约束条件CREATE TABLECREATETABLE table_name常见的数据类型包括整数型、浮点型、约束条件用于保证数据的完整性和一column_name data_type字符型、日期型等不同的DBMS支致性，包括主键约束、外键约束、唯constraint,...;持的数据类型可能有所不同一约束、非空约束等修改表结构1ALTER TABLE语法ALTER TABLE table_name action;添加列2ALTER TABLEtable_name ADDcolumn_name data_typeconstraint;修改列3ALTER TABLEtable_name MODIFYcolumn_namedata_type constraint;删除列4ALTER TABLEtable_name DROPcolumn_name;删除数据库对象DROP TABLE2DROP TABLEtable_name;DROP DATABASE1DROP DATABASEdatabase_name;DROP INDEXDROP INDEX index_name ON3table_name;数据操作语言（）DML语句INSERT1语句2UPDATE语句3DELETE插入数据INSERT INTO语法单行插入多行插入INSERT INTOtable_name一次插入一条数据一次插入多条数据，可以提高插入效column1,column2,...VALUES率value1,value2,...;更新数据语法子句UPDATE WHEREUPDATEtable_name SETcolumn1=value1,WHERE子句用于指定更新数据的条件，只有满足条件的column2=value2,...WHERE condition;行才会被更新如果省略WHERE子句，则会更新表中的所有行删除数据语法1DELETEDELETE FROMtable_name WHEREcondition;2TRUNCATE TABLETRUNCATE TABLEtable_name;用于删除表中的所有数据，但表结构保留TRUNCATETABLE比DELETE FROM效率更高，因为它不会记录删除日志数据查询语言（）DQL语句基础SELECTSELECT column1,column2,...FROMtable_name WHEREcondition;子句FROMFROM子句用于指定查询的表子句WHEREWHERE子句用于指定查询的条件语句详解SELECT列选择别名使用关键字DISTINCT可以使用*选择所有列，也可以指定可以使用AS关键字为列或表指定别可以使用DISTINCT关键字去除重复要选择的列名名，方便查询结果的显示和引用的行子句条件表达式WHERE比较运算符逻辑运算符12包括=、、、=、包括AND、OR、NOT等，=、!=等，用于比较两个用于连接多个条件表达式值的大小关系模式匹配3LIKE使用LIKE关键字进行模糊查询，可以使用%表示任意多个字符，使用_表示任意单个字符排序与分组子句GROUP BY2用于对查询结果进行分组，可以根子句据一个或多个列进行分组ORDER BY1用于对查询结果进行排序，可以使用ASC表示升序，使用DESC表示降序子句HAVING用于对分组后的结果进行过滤，只3有满足条件的组才会被显示多表连接查询内连接外连接自连接只返回两个表中连接字段相匹配的行返回左表的所有行，以及右表中连接将同一个表连接自身，通常用于查找字段相匹配的行如果右表中没有匹具有层次关系的数据配的行，则右表中的列显示为NULL子查询单行子查询多行子查询相关子查询子查询的结果只返回一行数据，可以子查询的结果返回多行数据，可以使子查询的执行依赖于外部查询，即子使用比较运算符进行比较用IN、ANY、ALL等关键字进行比查询需要引用外部查询的列较集合操作UNION INTERSECTEXCEPT返回两个或多个SELECT语句的结果返回两个或多个SELECT语句的结果返回第一个SELECT语句的结果集中集的并集，去除重复的行集的交集，即只返回两个结果集中都存在，而第二个SELECT语句的结果存在的行集中不存在的行第三章数据库设计数据库设计的目标1保证数据的完整性、一致性、有效性和安全性，提高数据的存储效率和查询效率，满足用户的需求设计步骤2数据库设计通常包括需求分析、概念设计、逻辑设计和物理设计四个步骤需求分析用户需求收集数据流图（）DFD通过访谈、问卷调查、需求分析文档等方式收集用户对数使用数据流图描述系统的数据流程，包括数据的来源、数据库的需求，包括数据存储需求、数据查询需求、数据更据的处理和数据的去向，帮助理解系统的数据需求新需求等概念设计实体识别2识别现实世界中需要存储的对象，即实体模型设计E-R1使用E-R模型描述数据库的概念结构，包括实体、属性和联系关系确定确定实体之间的关系，包括一对一3关系、一对多关系和多对多关系逻辑设计图转换为关系模式关系规范化E-R将E-R图转换为关系模式，即转换为表结构每个实体对对关系模式进行规范化，消除数据冗余，提高数据的完整应一个表，每个属性对应表中的一个列，每个联系对应表性和一致性常用的规范化方法包括第一范式、第二范式之间的关联关系和第三范式物理设计存储结构设计索引设计选择合适的存储结构，包括文件组织方式、索引结构等，为表中的列创建索引，以加快数据的查询速度需要根据以提高数据的存储效率和查询效率实际的查询需求选择合适的索引类型规范化理论第一范式（）1NF1每个属性都必须是原子性的，即不可再分第二范式（）2NF2在满足1NF的基础上，非主属性必须完全依赖于主键第三范式（）3NF3在满足2NF的基础上，非主属性之间不能存在传递依赖反规范化何时使用反规范化反规范化技术当规范化导致查询效率降低时，可以考虑使用反规范化常用的反规范化技术包括增加冗余列、增加派生列、合并例如，当需要频繁进行多表连接查询时，可以将一些常用表等的列冗余地存储在同一个表中第四章数据库编程存储过程触发器用户自定义函数123一组预编译的SQL语句，可以一种特殊的存储过程，在特定用户根据自己的需求编写的函像函数一样被调用，提高数据的数据库事件发生时自动执行，数，可以在SQL语句中调用，库的执行效率和安全性用于实现数据的完整性约束和扩展SQL的功能业务规则存储过程创建存储过程使用CREATE PROCEDURE语句创建存储过程，需要指定存储过程的名称、参数和SQL语句执行存储过程使用CALL语句或EXECUTE语句执行存储过程，需要传递存储过程的参数修改和删除存储过程使用ALTER PROCEDURE语句修改存储过程，使用DROP PROCEDURE语句删除存储过程触发器触发器类型创建触发器触发器应用场景根据触发事件的不同，触发器可以分使用CREATE TRIGGER语句创建触触发器常用于实现数据的完整性约束、为INSERT触发器、UPDATE触发器发器，需要指定触发器的名称、触发审计功能和业务规则例如，可以使和DELETE触发器根据触发时间的事件、触发时间和触发的SQL语句用触发器实现级联更新和级联删除先后，触发器可以分为BEFORE触发器和AFTER触发器用户自定义函数标量函数表值函数12返回单个值的函数返回一个表的函数创建和使用自定义函数3使用CREATE FUNCTION语句创建自定义函数，需要在SQL语句中调用自定义函数第五章数据库事务与并发控制事务的概念事务是数据库操作的逻辑单元，要么全部执行，要么全部不执行特性ACID原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）事务控制语句COMMIT提交事务，将事务中的所有修改永2久保存到数据库中BEGIN TRANSACTION1开始一个事务ROLLBACK回滚事务，撤销事务中的所有修改，使数据库回到事务开始之前的状态3并发问题脏读不可重复读幻读一个事务读取了另一个事务未提交的一个事务多次读取同一行数据，但每一个事务多次读取同一范围的数据，数据次读取的结果不一致，因为其他事务但每次读取的结果不一致，因为其他修改了该行数据事务在该范围内插入或删除了数据隔离级别READ UNCOMMITTED1最低的隔离级别，允许脏读、不可重复读和幻读READ COMMITTED2防止脏读，但允许不可重复读和幻读REPEATABLE READ3防止脏读和不可重复读，但允许幻读SERIALIZABLE4最高的隔离级别，防止脏读、不可重复读和幻读锁机制共享锁排他锁死锁处理多个事务可以同时持有共享锁，用于只有一个事务可以持有排他锁，用于当多个事务互相等待对方释放锁时，读取数据，防止其他事务修改数据修改数据，防止其他事务读取或修改会发生死锁DBMS通常会自动检测数据和解决死锁，例如通过回滚其中一个事务第六章数据库索引与优化索引的概念索引是一种特殊的数据结构，用于加快数据的查询速度类似于书籍的目录，可以快速定位到所需的数据索引的类型常见的索引类型包括B树索引、哈希索引、全文索引等不同的索引类型适用于不同的查询场景创建和管理索引语法删除和重建索引1CREATE INDEX2CREATE INDEXindex_name ONtable_name使用DROPINDEX语句删除索引，使用ALTERcolumn1,column2,...;INDEX语句重建索引索引设计原则适合建立索引的列避免过度索引经常用于查询条件的列、用于连接查询的列、用于排序和索引会占用存储空间，并且会降低数据的更新速度因此，分组的列需要避免过度索引，只为必要的列创建索引查询优化执行计划分析统计信息更新1分析SQL语句的执行计划，了解定期更新表的统计信息，使查询优SQL语句的执行过程，找出性能瓶2化器能够选择更优的执行计划颈性能优化技巧合理使用索引避免全表扫描12根据实际的查询需求选择尽量使用索引来定位数据，合适的索引类型，并避免避免全表扫描过度索引减少操作3I/O尽量减少磁盘I/O操作，例如使用缓存、批量处理等第七章数据库安全与备份恢复安全机制概述数据库安全机制包括用户认证、权限管理、数据加密、审计等，用于保护数据库的数据安全用户与权限管理通过用户和角色管理，控制用户对数据库的访问权限，防止未经授权的访问创建用户和角色角色分配CREATE USERCREATE ROLE使用CREATE USER语句创建用户，使用CREATE ROLE语句创建角色，将角色分配给用户，用户就可以拥有需要指定用户名和密码角色是一组权限的集合角色所包含的权限授权与撤销权限语句GRANT1使用GRANT语句授权，将权限授予用户或角色语句REVOKE2使用REVOKE语句撤销权限，将权限从用户或角色中移除数据加密透明数据加密（）列级加密TDE对整个数据库进行加密，对应用程序透明，不需要修改应对表中的某一列进行加密，需要修改应用程序的代码用程序的代码数据库备份策略完整备份1备份整个数据库，包括数据和日志差异备份2备份自上次完整备份以来发生变化的数据和日志事务日志备份3备份自上次日志备份以来产生的事务日志数据库恢复时间点恢复还原过程1将数据库恢复到指定的时间点，可将备份文件还原到数据库中2以使用完整备份、差异备份和事务日志备份进行恢复第八章数据仓库与数据挖掘数据仓库概念1数据仓库是一个面向主题、集成的、稳定的、时变的数据库，用于支持管理决策数据仓库架构2数据仓库架构通常包括数据源、数据抽取、数据转换、数据加载、数据存储和数据分析等几个部分联机分析处理（）OLAP多维数据模型操作OLAPOLAP使用多维数据模型来组织数据，例如星型模型和雪OLAP操作包括切片、切块、钻取、上卷和旋转等，用于花模型从不同的维度分析数据数据挖掘简介数据挖掘任务数据挖掘的任务包括关联分析、分类、聚类、预测和异常检测等常见算法常见的算法包括决策树、支持向量机、K-means、Apriori等第九章新兴数据库技术数据库大数据处理NoSQLNoSQL数据库是一种非关系型数据库，适用于存储非结构大数据处理是指处理海量数据的技术，包括数据的存储、化和半结构化数据，具有高可扩展性和高性能计算和分析常用的技术包括Hadoop和Spark数据库类型NoSQL键值存储文档数据库列族存储图数据库以键值对的形式存储数据，以文档的形式存储数据，适以列族的形式存储数据，适以图的形式存储数据，适用适用于存储简单的键值数据用于存储半结构化数据用于存储海量数据于存储关系复杂的数据大数据处理框架生态系统1HadoopHadoop是一个分布式计算框架，包括HDFS、MapReduce和YARN等组件，适用于批处理大数据简介2SparkSpark是一个快速的通用计算引擎，支持多种编程语言，适用于实时处理和机器学习课程总结知识点回顾本课程涵盖了数据库的基本概念、SQL语言、数据库设计、数据库编程、数据库安全与优化、数据仓库与数据挖掘、新兴数据库技术等方面的知识应用前景数据库技术广泛应用于各个领域，例如Web开发、移动应用开发、数据分析、人工智能等掌握数据库技术可以为职业发展提供广阔的空间结束语学习建议1多练习SQL语句，掌握数据库设计方法，关注数据库技术的发展趋势参考资源2数据库相关的书籍、网站、博客等。