关系型数据库应用基础教程

关系型数据库应用基础教程目录文档概要．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．21.1什么是数据库．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．21.2关系型数据库的原理．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．41.3常见的数据库管理系统．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．8SQL语言基础．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．112.1SQL语句的基本结构．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．112.2数据类型与数据约束．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．112.3数据库表的创建与维护．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．17数据查询与管理．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．183.1单表查询操作．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．183.2多表查询操作．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．203.3数据汇总与分组．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．22数据库设计与优化．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．254.1数据库范式理论．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．254.2索引的设计与管理．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．264.3查询性能优化．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．274.3.1EXPLAIN分析查询计划．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．314.3.2查询语句的优化策略．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．334.3.3硬件环境对性能的影响．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．35事务管理与并发控制．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．395.1事务的基本特性．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．395.2事务的并发控制．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．405.3存储过程与触发器．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．42实际应用案例．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．446.1小型电商系统的数据库设计．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．446.2图书管理系统的实现．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．526.3数据库安全配置．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．551.文档概要1.1什么是数据库数据库（Database）在现代信息管理中扮演着至关重要的角色，它被定义为一种结构化、组织化信息的集合，旨在高效地存储、检索和操作数据。通俗来讲，数据库可以理解为一种电子化的数据仓库，它不仅仅是一堆数据的简单堆积，而是通过特定的组织和关联机制，使得数据能够被系统化地管理和利用。◉数据库的核心特征为了更好地理解数据库的概念，我们可以从以下几个核心特征入手：特征描述数据结构化数据按照特定的模型进行组织，以便于访问和更新，如关系模型、层次模型或网络模型。共享性数据可以被多个用户或应用程序同时访问，但需要通过权限控制来保证数据安全。独立性数据的逻辑结构与物理存储结构是独立的，这样可以在不影响逻辑结构的情况下调整存储方式。高效性数据库管理系统（DBMS）提供了优化的查询和更新机制，确保数据操作的效率。◉数据库与文件系统的区别尽管数据库和文件系统都能存储数据，但它们在功能和使用方式上有显著的区别。【表】展示了数据库与文件系统的对比：特征数据库文件系统数据共享高度共享，支持多用户并行访问通常不设计为多用户共享，安全性较低数据完整性提供约束和事务管理，确保数据一致性缺乏内置的数据完整性机制复杂查询支持SQL等高级查询语言，能够进行复杂的条件检索查询能力有限，通常只能进行简单的文件读取操作维护成本相对较高，需要专业的数据库管理员（DBA）维护相对较低，一般用户即可管理◉数据库的应用场景数据库的应用范围非常广泛，几乎涵盖了所有需要管理数据的领域。以下是一些典型的应用场景：电子商务平台：存储用户信息、商品信息、订单数据等。银行系统：管理客户账户、交易记录、信用卡信息等。医疗系统：记录患者病历、医生信息、药品库存等。企业管理系统：存储员工信息、财务数据、生产报表等。通过以上内容，我们可以得出一个较为清晰的定义：数据库是一种按照数据结构来组织、存储和管理数据的系统，它能够提供数据共享、减少数据冗余、确保数据安全并提高数据利用效率。1.2关系型数据库的原理关系型数据库（RelationalDatabaseManagementSystem,RDBMS）基于关系模型（RelationalModel），该模型由埃德加·科德（E.F.Codd）于1970年提出。关系模型将现实世界中的数据结构化，以二维表格的形式表示实体及其之间的关系。关系的核心概念包括关系（Relation）、元组（Tuple）、属性（Attribute）和域（Domain）。关系模型的基本概念关系（Relation）：通常对应于现实世界中的一个实体集合，如表（Table）。元组（Tuple）：关系中的一行，代表一个完整的记录或实体。属性（Attribute）：关系中的一列，代表实体的一个特征，具有特定的数据类型（即域）。域（Domain）：属性的有效取值范围，定义了属性的数据类型和约束。关系数据库的结构关系数据库由多个关系（表）组成，每个关系都具有固定的结构。我们可以用以下方式定义一个关系：R其中R是关系名，A1关系操作关系操作主要包括集合操作和关系代数，用于查询、更新和管理关系数据。常见的集合操作包括并集（∪）、交集（∩）、差集（−）和笛卡尔积（imes）。关系代数则通过一些基本操作（如选择σ、投影π、连接⋈等）组合起来，实现复杂的查询。3.1选择操作（Selection）选择操作从关系中选择满足特定条件的元组，操作符为σ，其形式如下：σ例如，选择关系R中满足A1σ3.2投影操作（Projection）投影操作从关系中选择特定的属性列，忽略其他列。操作符为π，其形式如下：π例如，从关系R中选择属性A1和Aπ3.3连接操作（Join）连接操作将两个关系根据某个条件合并成一个新的关系，常见的连接操作包括等值连接（Equi-Join）和自然连接（NaturalJoin）。等值连接的形式如下：其中S是连接条件。例如，将关系R和T根据R.R数据完整性关系数据库需要确保数据的完整性和一致性，主要包括以下三种完整性约束：实体完整性（EntityIntegrity）：关系中的每个元组必须有一个唯一的标识符（主键），且主键不能为空。参照完整性（ReferentialIntegrity）：外键的值必须是另一个关系的主键值或空值，确保关系之间的引用有效。用户定义的完整性（User-DefinedIntegrity）：根据具体应用场景定义的约束，如检查约束（CHECK）和数据类型约束等。总结关系型数据库基于关系模型，通过二维表格结构和关系操作实现数据的存储、查询和管理。其核心概念包括关系、元组、属性和域，并通过选择、投影、连接等操作实现复杂的数据处理。同时关系数据库通过完整性约束确保数据的完整性和一致性，保证数据的有效性和可靠性。概念描述关系（Relation）实体集合的二维表格表示元组（Tuple）关系中的一行，代表一个记录属性（Attribute）关系中的一列，代表实体的一个特征域（Domain）属性的有效取值范围选择（Selection）选择满足特定条件的元组投影（Projection）选择特定的属性列连接（Join）将两个关系根据某个条件合并成一个新的关系实体完整性主键不能为空且唯一参照完整性外键值必须是另一个关系的主键值或空值用户定义的完整性根据具体应用场景定义的约束通过以上原理，关系型数据库为数据的管理和应用提供了强大的支持，是现代信息系统的基石。1.3常见的数据库管理系统在关系型数据库应用中，选择合适的数据库管理系统（DBMS）对于数据库的设计、开发和维护至关重要。以下是几种常见的数据库管理系统及其特点和应用场景。◉常见数据库管理系统对比表数据库管理系统特点应用领域MySQL支持关系型和非关系型数据快速处理复杂的事务广泛支持Web应用网页开发、数据分析、企业应用PostgreSQL开源、免费支持复杂的SQL操作高可靠性和安全性数据分析、科学计算、教育类应用Oracle商业数据库，功能强大支持大规模事务处理高安全性和高可用性企业级数据仓库、金融、医疗等行业MongoDB非关系型数据库灵活的文档存储适合处理海量数据大数据处理、实时分析、互联网应用CouchDB面向非关系型数据支持多文档存储高可用性和分片能力频繁更新数据、实时应用、移动应用MariaDB开源、免费支持多种存储引擎兼容MySQL语法网页开发、数据仓库、企业应用SQLite轻量级数据库嵌入式应用无服务器部署mobile应用、嵌入式系统、单用户应用◉详细介绍MySQLMySQL是最流行的关系型数据库管理系统，广泛应用于网页开发、数据分析等领域。它支持多种存储引擎，能够快速处理复杂的事务，适合需要高性能和高可用性的应用场景。MySQL社区活跃，文档丰富，支持在线文档和丰富的第三方工具支持。PostgreSQLPostgreSQL是一个开源的关系型数据库管理系统，支持复杂的SQL操作和高可靠性。它适合需要高安全性和稳定性的场景，常用于数据分析、科学计算等领域。PostgreSQL的灵活性和扩展性使其在教育和研究环境中也非常受欢迎。OracleOracle是一个功能强大的商业数据库管理系统，支持大规模事务处理和高安全性。它广泛应用于企业级数据仓库和金融、医疗等行业。Oracle的高可用性和强大的查询优化功能使其成为企业级数据库的首选。MongoDBMongoDB是一个非关系型数据库管理系统，基于文档存储，支持灵活的数据模型。它特别适合处理海量数据和实时分析，常用于互联网应用和大数据处理。MongoDB的分片能力使其能够处理分布式系统中的数据。CouchDBCouchDB是一个面向非关系型数据的数据库管理系统，支持多文档存储。它以高可用性和分片能力著称，适合需要频繁更新数据的应用场景，常见于实时分析和移动应用开发。MariaDBMariaDB是一个开源的关系型数据库管理系统，兼容MySQL语法，支持多种存储引擎。它被设计为轻量级和高性能，适合需要灵活配置的应用场景，常用于网页开发和数据仓库管理。SQLiteSQLite是一个轻量级的关系型数据库管理系统，适合嵌入式应用和无服务器部署。它支持文件式存储，能够在移动应用和单用户应用中高效运行。◉总结选择合适的数据库管理系统需要根据具体的应用需求、性能要求、支持的技术栈以及后续的维护和扩展需求来决定。无论是关系型数据库如MySQL、PostgreSQL，还是非关系型数据库如MongoDB、CouchDB，都有其独特的优势和适用场景。2.SQL语言基础2.1SQL语句的基本结构SQL（StructuredQueryLanguage，结构化查询语言）是用于管理关系型数据库的编程语言。它包括各种命令，用于检索和更新数据库中的数据。SQL语句的基本结构包括以下几个部分：（1）标识符标识符是用来命名对象（如表、列、索引等）的名称。标识符必须遵循以下规则：以字母（a-z,A-Z）或下划线（_）开头。后续字符可以是字母、数字（0-9）或下划线。不能使用保留字作为标识符。（2）数据类型数据类型定义了列中存储的数据的类型，常见的数据类型有：字符串类型：如CHAR,VARCHAR,TEXT。（3）关键字（4）表达式表达式是由常量、变量、函数和运算符组成的数学或逻辑语句。例如：age=25total_price=pricequantity（5）语句SQL语句是SQL语言的基本构建块，包括：SELECT：用于从数据库表中检索数据。INSERT：用于向数据库表中此处省略新记录。UPDATE：用于修改数据库表中的记录。DELETE：用于删除数据库表中的记录。CREATE：用于创建新的数据库对象，如表、视内容和索引。DROP：用于删除数据库对象。（6）条件语句条件语句允许根据特定条件执行不同的SQL语句。例如：（7）循环语句循环语句允许重复执行一段代码，直到满足某个条件。例如：WHILEage<21DOage=age+1;ENDWHILE;通过学习和掌握这些基本概念，你将能够编写基本的SQL查询来操作关系型数据库中的数据。2.2数据类型与数据约束在关系型数据库中，数据类型定义了列（字段）可以存储的数据种类。正确选择数据类型对于数据库的性能和存储效率至关重要，常见的数据类型包括数值型、字符型、日期时间型、逻辑型等。◉数值型数据类型数值型数据类型用于存储数值数据，常见的数值型数据类型有：数据类型描述范围（示例）TINYINT小整数，通常用于存储很小范围的整数-128到127或0到255（无符号）SMALLINT小整数，范围比TINYINT大-XXXX到XXXX或0到XXXX（无符号）MEDIUMINT中整数，范围比SMALLINT大-XXXX到XXXX或0到XXXX（无符号）INT常规整数，范围较大-XXXX到XXXX或0到XXXX（无符号）BIGINT大整数，范围非常大-XXXXXXXX到XXXXXXXX或0到XXXXXXXX（无符号）DECIMAL(M,D)十进制数，精确存储固定小数点数依赖于M和D的值FLOAT浮点数，近似存储小数点数6-9位有效数字DOUBLE双精度浮点数，近似存储小数点数15位有效数字◉字符型数据类型字符型数据类型用于存储文本数据，常见的字符型数据类型有：数据类型描述示例CHAR(N)定长字符串，不足部分用空格填充CHAR(10)存储如“Hello”的字符串VARCHAR(N)可变长字符串，长度最多为N字符VARCHAR(10)存储如“Hello”的字符串BINARY(N)定长二进制字符串存储2进制数据VARBINARY(N)可变长二进制字符串存储2进制数据TEXT长文本，最大长度为65,535字符存储较长的文本数据BLOB二进制大型对象，最大长度为4,294,967,295字节存储内容片、文件等二进制数据◉日期时间型数据类型日期时间型数据类型用于存储日期和时间数据，常见的日期时间型数据类型有：数据类型描述示例DATE日期，格式为YYYY-MM-DD2023-10-27TIME时间，格式为HH:MM:SS14:30:00DATETIME日期和时间，格式为YYYY-MM-DDHH:MM:SS2023-10-2714:30:00TIMESTAMP时间戳，存储从1970-01-0100:00:00UTC开始的秒数XXXX（对应2023-10-2714:30:00UTC）◉逻辑型数据类型逻辑型数据类型用于存储布尔值，常见的逻辑型数据类型有：数据类型描述示例BOOLEAN布尔值，通常存储TRUE或FALSETRUEBIT(N)位字段，存储二进制位BIT(1)◉数据约束数据约束是数据库表中的规则，用于确保数据的完整性和一致性。常见的数据约束包括：◉主键约束（PrimaryKeyConstraint）主键约束确保表中的每一行都有唯一标识符，且主键列不能为NULL。主键通常使用PRIMARYKEY关键字定义。例如：◉唯一约束（UniqueConstraint）唯一约束确保列中的所有值都是唯一的，但唯一约束列可以包含一个NULL值（除非表中其他行也包含NULL）。唯一约束通常使用UNIQUE关键字定义。例如：◉外键约束（ForeignKeyConstraint）外键约束确保一个表中的数据参照另一个表中的数据，从而维护表之间的引用完整性。外键通常使用FOREIGNKEY关键字定义。例如：◉非空约束（NotNullConstraint）非空约束确保列不能包含NULL值。非空约束通常使用NOTNULL关键字定义。例如：◉检查约束（CheckConstraint）检查约束确保列中的值满足特定条件，检查约束通常使用CHECK关键字定义。例如：通过合理使用数据类型和数据约束，可以确保数据库中存储的数据既准确又一致，从而提高数据库的整体性能和可靠性。2.3数据库表的创建与维护（1）创建数据库表在关系型数据库中，表是存储数据的基本单位。创建表的过程通常包括以下几个步骤：确定表名：选择一个描述性强且易于理解的表名，避免使用模糊或不相关的词汇。设计表结构：根据需求定义表中的字段及其类型、长度、是否允许为空等属性。例如，创建一个用户表，可以包含以下字段：用户名（varchar）、密码（varchar）、邮箱（varchar）等。创建表结构：使用SQL语句创建表。例如，使用CREATETABLE语句创建用户表：此处省略数据：向表中此处省略数据时，需要指定要此处省略的数据值和对应的字段。例如，向用户表中此处省略一条记录：INSERTINTOusers删除数据：从表中删除不再需要的记录。例如，删除用户表中的所有记录：DELETEFROMusers;删除数据：从表中删除不再需要的记录。例如，删除用户表中的所有记录：DELETEFROMusers此处省略索引：为表中的某个字段创建索引，以提高查询效率。例如，为users表中的username字段创建索引：CREATEINDEXidx3.数据查询与管理3.1单表查询操作（1）单表查询基础单表查询是关系型数据库中最基本也是最重要的查询操作，它允许用户从单一数据表中检索所需的数据。无论对于数据库初学者还是高级用户，掌握单表查询语法和技巧都是数据库操作的核心基础。在MySQL中，单表查询主要通过SELECT语句实现。基本语法格式：（此处内容暂时省略）说明：选择列表可以包含列名（用逗号分隔）或者使用``表示选择所有列FROM子句指定要查询的表名WHERE子句用于设置行过滤条件（可选）（2）条件查询操作符在查询条件中常用的比较操作符包括：$操作符功能描述示例=等于判断两个表达式是否相等price=100!=或`|判断两个表达式是否不相等|status!=‘active’||>|大于|salary>5000||<|小于|age<30||>=|大于或等于|price>=200||<=|小于或等于|stock<=100||BETWEEN|判断是否在某个范围内|priceBETWEEN100AND200||IN|判断是否在列表中|colorIN(‘red’,‘blue’,‘green’)||ISNULL|判断是否为空值|emailISNULL||LIKE|模式匹配（需配合通配符使用）|nameLIKE‘张%’`通配符说明：_表示单个字符char​cha（3）常用查询函数MySQL提供了丰富的内置函数，可以增强单表查询能力：聚合函数SELECTCOUNT(*)AS记录总数,SUM(销售额)AS总销售额FROM销售表;COUNT()表示计算记录数，公式为：COUNT(column)=记录总数SELECTAVG(价格)AS平均价格FROM商品表WHERE库存>0;AVG()表示计算平均值，公式为：AVG(column)=Σ(value)/N数值函数SELECTROUND(价格,2)AS价格(元)FROM商品表;字符串函数SELECTCONCAT(姓名,‘(’,部门,‘)’)AS格式化姓名FROM员工表;（4）排序与去重查询增强查询功能的子句：◉ORDERBY子句（排序查询）SELECT姓名,薪资FROM员工表ORDERBY薪资DESC,入职日期ASC;◉DISTINCT关键字（去重查询）SELECTDISTINCT部门FROM员工表;（5）多表连接过渡知识点虽然本节讲的是单表查询，但需要说明的是：多表查询语法与单表查询基本一致语法仅在FROM子句和连接条件部分有所不同连接操作符包括：内连接INNERJOIN左连接LEFTJOIN右连接RIGHTJOIN这部分内容将在后续章节”3.2多表连接查询”中详细讲解。◉小结掌握单表查询是使用关系型数据库的第一步关键能力，本节主要介绍了：单表查询的基本语法结构常用比较运算符和IN/BETWEEN等复杂条件模式匹配、聚合运算等高级功能排序、去重等增强功能3.2多表查询操作在关系型数据库中，数据往往存储在不同的表格中，这些表格之间通过外键相互关联。为了获取完整的信息，需要执行多表查询操作，将来自不同表格的相关数据组合起来。本节将介绍常用的多表查询方法，包括连接查询（JOIN）、子查询（Subquery）和集合运算（SetOperations）。（1）连接查询（JOIN）连接查询是最常用的多表查询方法，用于根据两个或多个表格之间的关联关系（通常是外键）来组合数据。SQL提供了四种基本的连接类型：内连接（INNERJOIN）：返回两个表格中匹配的记录。左外连接（LEFTJOIN/LEFTOUTERJOIN）：返回左表的所有记录，以及右表中匹配的记录。如果右表中没有匹配，则右表的列返回NULL。右外连接（RIGHTJOIN/RIGHTOUTERJOIN）：返回右表的所有记录，以及左表中匹配的记录。如果左表中没有匹配，则左表的列返回NULL。全外连接（FULLJOIN/FULLOUTERJOIN）：返回两个表格中的所有记录，无论是否匹配。如果没有匹配，则另一边的列返回NULL。1.1内连接示例假设有两个表格：employees（员工表）和departments（部门表），它们通过department_id外键关联。以下是一个内连接查询的示例，用于获取员工姓名及其所在部门的名称：FROMemployees（此处内容暂时省略）sqlFROMemployees（此处内容暂时省略）sqlFROMemployees（此处内容暂时省略）sqlFROMemployees结果如下：namedepartment_nameAliceHRBobITCharlieHRDavidNULLNULLManagement（2）子查询（Subquery）子查询是指在主查询中嵌套一个查询，用于提供主查询所需的条件或数据。子查询可以是嵌套在WHERE子句、SELECT子句或FROM子句中。子查询通常用于过滤数据或获取计算结果。假设需要找出工资高于部门平均工资的员工，以下是一个子查询的示例：（3）集合运算（SetOperations）集合运算是另一种多表查询方法，用于组合两个或多个查询的结果集。常用的集合运算包括：并集（UNION）：合并两个查询的结果集，并去除重复的记录。交集（INTERSECT）：返回两个查询的结果集的交集，即同时存在于两个结果集中的记录。差集（EXCEPT/MINUS）：返回存在于第一个查询结果集中，但不在第二个查询结果集中的记录。3.1并集示例假设有两个查询：以下是一个并集查询的示例，用于合并这两个查询的结果集：UNION（此处内容暂时省略）sqlINTERSECT（此处内容暂时省略）sqlEXCEPT结果如下：nameAliceCharlie◉总结多表查询是关系型数据库操作中的重要部分，通过连接查询、子查询和集合运算等方法，可以灵活地组合和分析来自不同表格的数据。这些方法在数据分析和业务决策中具有广泛的应用价值。3.3数据汇总与分组在关系型数据库中，数据汇总（aggregate）和分组（grouping）是常用的分析手段。这些操作可以帮助我们从大量数据中发现规律、趋势和关键指标。本节将详细介绍数据汇总与分组的概念、常用函数以及实际应用。（1）数据汇总数据汇总是指通过统计函数对一组数据进行计算，从而得到一个单一的值。常用的汇总函数包括：COUNT()：计算指定列的行数（不考虑NULL值）。SUM()：计算指定列数值的总和（只对数值型列有效）。AVG()：计算指定列数值的平均值（只对数值型列有效）。MAX()：返回指定列的最大值。MIN()：返回指定列的最小值。例如，假设我们有以下名为sales的表：sale_iddateamount12023-01-0110022023-01-0115032023-01-0220042023-01-0225052023-01-03300要计算每天的销售总额，可以使用SQL语句如下：执行结果如下：datetotal_amount2023-01-012502023-01-024502023-01-03300（2）数据分组数据分组是指根据指定列的值将数据分成不同的组，然后对每一组进行汇总。GROUPBY语句用于实现分组操作。继续使用前面的sales表，如果我们要按日期分组并计算每天的平均销售金额，可以使用如下SQL语句：执行结果如下：dateaverage_amount2023-01-01125.02023-01-02225.02023-01-03300.0（3）分组与汇总综合应用分组和汇总经常结合使用，可以更详细地分析数据。例如，假设我们有一个包含员工信息的表employees：employee_iddepartmentsalary1IT50002IT55003HR45004HR50005Marketing6000要计算每个部门的总薪水和平均薪水，可以使用如下SQL语句：执行结果如下：departmenttotal_salaryaverage_salaryITXXXX5250.0HR95004750.0Marketing60006000.0通过上述例子，我们可以看到如何利用分组和汇总函数从数据中提取有价值的信息。这些操作是数据分析的核心部分，广泛应用于商业智能、报表生成等领域。4.数据库设计与优化4.1数据库范式理论范式的定义范式（Normalization）是数据库设计中的核心理论，通过分解关系模式消除数据冗余和操作异常，提升数据库的逻辑结构化程度。其核心思想源于函数依赖理论，旨在将关系模式逐步规范化到更高范式层级。函数依赖与码函数依赖是范式理论的基础，若属性集X的每个取值唯一决定属性集Y的每个取值，则称X→Y。码（键）：唯一标识关系中元组的最小属性集。超码（Superkey）：能唯一标识元组的属性集。候选码（CandidateKey）：最小区分能力的超码，无真子集满足码属性。主码（PrimaryKey）：选定的候选码。第一范式（1NF）定义：属性值均为原子值，不可再分。应用：确保所有列均为单一属性值，避免列表式数据存储（如“导师列表”字段存储多个教师）。第二范式（2NF）前提：满足1NF；且非主属性完全依赖于主码。部分依赖现象（反例）：订单明细表：订单号→客户姓名订单号+商品ID→商品名称,单价,数量•存在部分依赖：订单号→客户姓名（非整个订单明细）•引发问题：○同一客户服务多次→客户姓名重复存储○单条订单记录损坏→整个订单信息失效正确分解：客户表（订单号+客户ID,客户姓名）→满足2NF订单明细表（订单ID,商品ID,数量）→唯一键为（订单ID+商品ID）第三范式（3NF）定义：满足2NF，且非主属性不传递依赖于主码。传递依赖示例：供应商表：供应商ID→供应商名称供应商ID→所在城市所在城市→邮编•传递链：供应商ID→所在城市→邮编•违反3NF：邮编仅依赖于唯一标识“城市”，与供应商ID无关分解方法：供应商基本表（供应商ID,供应商名称,所在城市）→含传递依赖邮编表（城市，邮编）→新建表消解传递依赖（此处内容暂时省略）plaintext•实战建议：–低依赖冗余模型适用于数据一致性高的场景–高依赖冗余模型适用于数据分析频繁、对实时性要求低的应用–建议：3NF+适当预聚合是主流系统设计实践练习题判断以下关系模式是否满足2NF：员工（部门ID，员工ID，员工姓名，部门名称）主码=（部门ID，员工ID）设有函数依赖集F={学号→班级,班级→班长}，计算以下属性集闭包：（学号）⁺解答索引是关系型数据库中非常重要的组成部分，它能够显著提高数据查询的效率，但也需要合理的设计和管理。不当的索引设计可能会导致性能下降，而过多或无用的索引会增加维护成本。本节将详细介绍索引的设计原则、类型、创建与删除方法，以及索引的管理策略。（1）索引的设计原则设计索引时需要遵循以下原则：选择性高的列优先：选择性高的列意味着列中有大量唯一值，这样的列作为索引效果更好。选择性可以用以下公式计算：选择性选择性接近1的列是理想索引列。查询频率高的列：经常用于查询条件的列应该建立索引，以加快查询速度。复合索引的合理性：当多个列经常一起出现在查询条件中时，可以考虑创建复合索引。复合索引的列顺序也非常重要，应按照在查询中出现的频率和选择性来排序。例如，若经常查询(last_name,first_name)，可以创建复合索引INDEXnama(last_name,first_name)。避免过度索引：每个索引都会增加数据此处省略、更新、删除的开销，因为索引本身也需要维护。应避免为表中所有列创建索引。（2）索引类型常见的索引类型包括：B树索引：最常见的索引类型，适用于范围查询和等值查询。优点：平衡树结构，查询效率高。缺点：不支持部分索引（部分数据行上创建的索引）。哈希索引：基于哈希表实现，仅支持精确等值查询。优点：查询速度极快，适用于等值查询。缺点：不支持范围查询和排序操作。全文索引：用于搜索文本数据，支持自然语言查询。优点：适用于全文检索。缺点：占用空间大，查询效率相对较低。位内容索引：适用于低基数列（不同值较少的列）。优点：在低基数列上效率高，支持布尔运算。缺点：只适用于某些数据库系统，如Sybase。（3）索引的创建与删除◉创建索引创建索引的基本语法如下：（此处内容暂时省略）例如，为users表创建索引：CREATEINDEXidxu删除索引的基本语法如下：DROPINDEX索引名;例如，删除idx_user_nameDROPINDEXidxu索引的管理主要包括以下方面：重建索引：当索引存在碎片时，可以通过重建索引来优化性能。REBUILDINDEX索引名ON表名分区索引：对于大表，可以创建分区索引，将数据分布到不同的分区以提高性能。CREATEINDEX索引名ON表名(列名)索引监控：定期监控索引的使用情况，删除命中率低的索引。–示例查询SELECT*索引维护计划：许多数据库系统支持自动索引维护功能，可以定期执行索引重建或重建操作。通过合理设计和管理索引，可以显著提升关系型数据库的性能和可维护性。4.3查询性能优化◉查询性能优化概述查询性能优化是关系型数据库管理中的核心环节，直接影响数据库应用的响应时间和用户体验。通过合理的查询设计、索引优化和数据结构调整，可以显著提升数据库的查询效率。本节将介绍常见的查询性能优化方法，包括索引使用、查询重构、批处理操作和硬件优化等方面。（1）索引优化索引是提高数据库查询性能最常用的手段之一，索引通过建立数据与键值之间的映射关系，减少了数据库扫描的数据量。以下是索引优化的关键要点：◉索引类型与适用场景索引类型描述适用场景B-Tree索引最常用的索引类型，适用于等值查询、范围查询和排序操作主键、经常用于WHERE条件的列Hash索引基于哈希表实现，适用于等值查询快速查找唯一值全文索引用于文本内容搜索文本字段、全文检索GIN索引用于复杂字段的索引，如数组、JSON等复杂数据类型的字段BTREEGIN组合结合多种索引类型，优化特定查询复合查询条件◉索引创建最佳实践选择合适的列创建索引优先选择查询频繁的列，如WHERE、JOIN和ORDERBY条件中的列避免对高基数（不同值比例高的列）建立索引复合索引设计索引列的顺序至关重要：ext效率=1ext选择性imes索引维护定期重建索引以释放碎片空间监控索引使用情况，删除未使用的索引（2）查询重构优化查询语句本身也是提升性能的重要手段，以下是一些常见查询重构技巧：避免全表扫描大型查询容易出现全表扫描问题，可以通过以下方式解决：避免SELECT，指定需要的列使用EXISTS代替IN在子查询中对关联操作使用适当的JOIN而不是子查询查询条件优化–低效查询示例–优化后查询批量操作处理对于需要处理大量数据的场景，可以考虑以下方法：使用临时表存储中间结果分批执行大查询，每批次处理一定数量的数据使用批量此处省略/更新代替单条记录操作（3）执行计划分析理解数据库的执行计划是优化查询的关键，大多数数据库管理系统提供了执行计划分析工具，例如MySQL的EXPLAIN命令。◉执行计划关键指标指标描述优化方向type查询类型（ALL,INDEX,INDEXSCAN等）尽量优化为范围扫描或索引查找possible_key可用的索引确保使用了最有效的索引key实际使用的索引分析是否选择了最佳索引rows预估扫描的行数尽量减少扫描行数Extra其他执行信息识别潜在问题点通过分析执行计划中的这些指标，可以对查询进行针对性的优化。（4）其他优化方法除了上述方法外，还有其他一些可以提高查询性能的手段：查询缓存对于重复执行相同查询的场景，数据库缓存可以显著提高性能缓存命中率公式：ext命中率=1对大数据表进行分区可以改善查询性能分区策略pods:范围分区（按日期、ID等）聚合分区（按业务类型、地区等）归档旧数据定期将旧数据移至归档表或冷存储，减少活动表的数据量ext性能提升系数=ext原表数据量查询性能优化是一个系统工程，需要综合考虑索引设计、查询重构、硬件资源等多方面因素。通过持续监控数据库性能指标、分析执行计划并根据实际情况调整优化策略，可以实现数据库查询性能的显著提升。建议维护人员建立完善的性能监控体系，定期进行性能评估，以便及时发现并解决潜在的性能瓶颈。4.3.1EXPLAIN分析查询计划在关系型数据库中，EXPLAIN是一种重要的工具，用于分析SQL查询的执行计划（ExecutionPlan）。它能够揭示数据库如何处理查询，从而帮助开发人员优化查询性能。EXPLAIN的作用类似于调试工具，帮助我们了解数据库在执行查询时的具体操作流程。◉EXPLAIN的作用分析查询执行计划：EXPLAIN会生成一个详细的计划，展示数据库如何执行给定的查询。识别性能瓶颈：通过分析计划，开发人员可以找出导致查询性能低下的原因，如索引不足、Join操作过多或是过多的排序操作等。评估索引效率：EXPLAIN会显示哪些索引被使用，以及它们的效率，有助于判断是否需要此处省略新的索引。优化数据库性能：通过了解数据库如何执行查询，开发人员可以对查询进行优化，减少不必要的操作，提高整体性能。◉使用EXPLAIN的示例以下是一个使用EXPLAIN的示例：–创建表–查询示例运行上述查询后，输出会显示数据库如何处理该查询，包括以下信息：运算类型说明SELECT从orders表中选择所有列WHEREorder_date>=‘2023-01-01’过滤满足条件的记录WHEREorder_amount>1000过滤满足条件的记录ORDERBYorder_date按order_date进行排序ORDERBYorder_amount按order_amount进行排序◉EXPLAIN的结果解读表扫描：如果orders表有大量记录，且查询条件不严格，可能会导致全表扫描，影响性能。索引使用：如果查询使用索引，会在结果中显示索引名称，否则显示Usingindex或Usingwhere。排序操作：如果查询需要排序，会显示ORDERBY操作，并显示排序字段。◉EXPLAIN的优势帮助开发人员理解数据库如何执行查询。提供性能问题的具体原因，例如索引不足或过多的排序操作。有助于优化查询，减少数据库开销。通过使用EXPLAIN，开发人员可以更好地理解数据库的工作原理，并通过优化查询，提升数据库性能。4.3.2查询语句的优化策略在关系型数据库中，查询语句的性能直接影响到整个系统的运行效率。为了提高查询性能，需要采取一系列优化策略。以下是一些常见的查询语句优化策略：（1）选择合适的索引索引是提高查询性能的关键，通过为表中的关键列创建索引，可以加快查询速度。索引可以大大减少数据库在查找数据时需要扫描的数据量，从而提高查询效率。索引类型描述B-tree索引B-tree索引是一种平衡树结构，它可以保持数据有序，适用于等值查询和范围查询。Hash索引Hash索引基于哈希表实现，适用于等值查询，但不支持范围查询。Full-text索引Full-text索引用于全文搜索，适用于文本数据的检索。（2）使用分页查询当查询结果集较大时，可以使用分页查询来减少每次查询返回的数据量，从而提高查询性能。分页查询可以通过LIMIT和OFFSET子句实现，也可以使用游标分页。分页方式描述使用LIMIT和OFFSET子句通过限制返回的数据行数和起始位置来实现分页查询。游标分页通过使用游标（如MySQL的LIMIT和OFFSET子句中的游标）来实现分页查询。（3）避免使用SELECT查询在查询语句中，尽量避免使用SELECT来查询数据。因为SELECT会返回表中的所有列，这会导致数据库需要读取更多的数据，从而降低查询性能。优化方法描述只查询需要的列只查询需要的列，而不是使用SELECT。（4）使用JOIN代替子查询在某些情况下，使用JOIN语句可以提高查询性能。相比于子查询，JOIN语句可以减少查询的复杂性，提高查询效率。JOIN类型描述INNERJOIN返回两个表中匹配的数据行。LEFTJOIN返回左表中的所有数据行，以及右表中匹配的数据行。RIGHTJOIN返回右表中的所有数据行，以及左表中匹配的数据行。FULLOUTERJOIN返回两个表中的所有数据行，以及匹配和不匹配的数据行。（5）使用数据库的查询缓存许多数据库系统都提供了查询缓存功能，可以将常用的查询结果缓存起来，从而提高查询性能。需要注意的是查询缓存并不适用于所有场景，因为查询结果可能会随着数据的更新而发生变化。查询缓存类型描述数据库内置查询缓存数据库系统提供的查询缓存功能。应用程序缓存在应用程序中实现的查询结果缓存。通过合理地运用这些优化策略，可以显著提高关系型数据库查询语句的性能，从而提升整个系统的运行效率。4.3.3硬件环境对性能的影响硬件环境是关系型数据库系统运行的基础，其性能直接影响数据库的响应速度、并发处理能力和稳定性。合理的硬件配置能够显著提升数据库的性能，而不足或不当的配置则可能导致性能瓶颈。本节将从CPU、内存、存储和网络四个方面探讨硬件环境对关系型数据库性能的影响。（1）CPUCPU是数据库系统的核心处理器，负责执行查询、更新、删除等操作。CPU的性能直接影响数据库的并行处理能力和事务处理速度。1.1CPU核心数CPU核心数决定了数据库系统可以同时处理的任务数量。对于关系型数据库，增加核心数可以提高并发处理能力。通常情况下，核心数与数据库的并发用户数成正比关系。假设每个用户平均占用1个核心，那么核心数可以表示为：ext所需核心数1.2CPU频率CPU频率（单位：GHz）决定了每个核心的运算速度。较高的频率可以加快单个任务的执行时间，例如，假设有两个核心数相同但频率不同的CPU，其性能差异可以表示为：ext性能差异◉表格：不同CPU配置的性能对比CPU型号核心数频率（GHz）并发处理能力（用户）单任务处理时间（ms）InteliXXX63.606100InteliXXXK84.70880AMDRyzen72700X84.3885（2）内存内存（RAM）是数据库系统用于存储临时数据和工作区的主要存储介质。内存的大小直接影响数据库的缓存能力和查询速度。内存容量决定了数据库可以缓存的数据量，较大的内存容量可以提高查询速度，减少磁盘I/O操作。假设数据库的缓存命中率为H，则查询速度可以表示为：ext查询速度◉表格：不同内存配置的性能对比内存配置容量（GB）缓存命中率查询速度（次/s）16GB160.70100032GB320.80120064GB640.851300（3）存储存储设备是数据库系统用于持久化数据的主要介质，存储的性能直接影响数据的读写速度。常见的存储类型包括机械硬盘（HDD）和固态硬盘（SSD）。SSD的读写速度远高于HDD，可以显著提高数据库的I/O性能。◉表格：不同存储类型的性能对比存储类型读写速度（MB/s）查询速度（次/s）HDD150800SSD5001500（4）网络网络设备是数据库系统与客户端通信的桥梁，网络性能直接影响数据库的远程访问速度和并发处理能力。网络带宽决定了数据传输的速率，较高的网络带宽可以提高数据库的远程访问速度。假设网络带宽为B（单位：Mbps），则数据传输时间可以表示为：ext传输时间◉表格：不同网络带宽的性能对比网络带宽数据量（MB）传输时间（s）100Mbps1000.81Gbps1000.0810Gbps1000.008◉总结硬件环境对关系型数据库性能的影响是多方面的，合理的硬件配置可以提高数据库的响应速度、并发处理能力和稳定性。在实际应用中，应根据数据库的具体需求选择合适的硬件配置，并进行适当的优化。5.事务管理与并发控制5.1事务的基本特性（一）事务的定义事务是数据库操作的一个逻辑单位，它包括一组SQL语句的执行。一个事务要么全部成功，要么全部失败，不存在部分提交的情况。（二）事务的特性原子性：事务中的所有SQL语句要么全部执行，要么全部不执行，不存在中间状态。一致性：事务在执行过程中，数据库的状态保持一致，即事务开始前和事务结束后数据库的状态是一致的。隔离性：并发访问时，一个事务的执行不会对其他事务产生影响，即一个事务的修改被另一个事务看到时，看到的是事务开始前的数据。持久性：一旦事务提交，则对数据库的修改永久保存下来，即使系统崩溃也不会丢失。（三）事务的ACID特性ACID（Atomicity,Consistency,Isolation,Durability）是关系型数据库事务处理的四个基本属性。Atomicity：原子性，指事务作为一个整体，要么完全成功，要么完全失败，不可分割。Consistency：一致性，指事务执行前后数据库的状态保持一致。Isolation：隔离性，指多个事务并发执行时，保证它们之间的数据独立性。Durability：持久性，指事务一旦提交，其影响就应永久保留。5.2事务的并发控制（1）并发事务及其问题多个事务（Transaction）同时执行时，称为并发控制（ConcurrencyControl）。并发执行可提高系统资源利用率和响应速度，但也可能引入以下问题：丢失更新（LostUpdate）当两个事务同时读取同一数据，后提交的事务覆盖了先提交事务的更新，造成先提交数据的丢失。示例：脏读（DirtyRead）事务T1读取事务T2尚未提交的数据，若T2回滚，则会导致T1读取无效数据。不可重复读（Non-RepeatableRead）事务T1多次读取同一数据时，两次读取结果不一致（由于事务T2修改了该数据）。幻读/幻影读（PhantomRead）事务T1在一次查询中看到某一范围的数据，而由于事务T2此处省略了相同范围的数据，导致后续查询发现额外数据行。（2）隔离级别（IsolationLevels）数据库系统通过设置不同的隔离级别来控制事务并发的可见性，ANSISQL定义了四种标准级别：隔离级别定义主要问题性能影响未提交读（ReadUncommitted）最低隔离，允许脏读脏读、不可重复读、幻读性能最优读已提交（ReadCommitted）防止脏读，允许不可重复读和幻读MySQL默认级别性能较高可重复读（RepeatableRead）通过多版本并发控制防不可重复读MySQL默认隔离级别，存在幻读风险性能中等串行化（Serializable）严格顺序执行事务不存在以上并发问题性能最低（3）封锁机制（Locking）基本思想：事务在读/写数据前需获得相应锁，其他事务需等待锁释放。锁类型共享锁（SharedLock，S锁）：允许多个事务读取同数据，但禁止修改。排他锁（ExclusiveLock，X锁）：仅一个事务可读写数据，其他事务均需等待。封锁协议一级封锁协议：解决丢失更新（仅为读加S锁，写加X锁）。二级封锁协议：防止脏读（读写操作均需加锁，且直到结束才释放）。三级封锁协议：防止不可重复读（对读数据加S锁至事务结束）。两阶段封锁协议（2PL）：事务分为增长阶段（获取所有锁）和缩减阶段（释放所有锁），确保可串行性。（4）并发控制优化策略多版本并发控制（MVCC）：通过维护数据行历史版本，使读操作不阻塞写操作（如PostgreSQL）。时间戳排序（TimestampOrdering）：为事务分配全局时间戳，并按时间顺序强制串行化执行。乐观并发控制（OptimisticConcurrencyControl）：仅在事务提交时检查冲突，适用于冲突频率较低的场景。（5）实践建议在高并发OLTP系统中，推荐使用可重复读或串行化级别。对于金融交易等关键业务，选择串行化级别确保数据一致性。复杂查询可通过分区、索引等技术减少锁竞争，优化数据库性能。📌练习方向：分析银行转账场景中不同隔离级别可能导致的问题。对比MySQL与Oracle数据库的默认隔离级别差异。设计一个多表（如订单与库存）的并发更新事务。5.3存储过程与触发器存储过程和触发器是关系型数据库中两种重要的数据库对象，它们能够帮助开发者实现更复杂的数据库操作和自动化任务。本节将详细介绍存储过程和触发器的概念、语法和应用场景。（1）存储过程1.1概念存储过程是一组为了完成特定功能的SQL语句集合，这些SQL语句被编译并存储在数据库中，可以像函数一样被调用执行。存储过程可以接收输入参数、返回结果集，并且可以包含控制结构（如IF-ELSE、循环等）。1.2语法存储过程的定义语法通常如下所示：CREATEPROCEDURE存储过程名称(@参数1数据类型,@参数2数据类型,…)ASBEGIN–SQL语句SELECT@返回值=根据参数计算的结果;RETURN@返回值;END;1.3应用场景存储过程常用于以下场景：封装复杂的业务逻辑：将复杂的业务逻辑封装在存储过程中，可以提高代码的可读性和可维护性。提高性能：存储过程在编译后存储在数据库中，执行时不需要重新解析，可以提高性能。减少网络传输：通过存储过程在数据库端处理数据，可以减少客户端和服务器之间的数据传输。（2）触发器2.1概念触发器是一种特殊类型的存储过程，它会在INSERT、UPDATE或DELETE操作发生时自动执行。触发器可以用于强制数据完整性、执行复杂的业务规则或记录变更日志。2.2语法触发器的定义语法通常如下所示：CREATETRIGGER触发器名称ON表名ASBEGIN–SQL语句END;2.3应用场景触发器常用于以下场景：强制数据完整性：通过触发器强制执行复杂的约束条件。记录变更日志：在数据发生变化时自动记录日志。自动更新相关数据：在数据发生变化时自动更新相关表的数据。（3）示例3.1存储过程示例以下是一个简单的存储过程示例，用于计算两种产品的折扣价格：CREATEPROCEDURE计算折扣价格(@产品IDINT,@折扣率DECIMAL(5,2))ASBEGINDECLARE@原价格DECIMAL(10,2);DECLARE@折扣后价格DECIMAL(10,2);–获取原价格SELECT@原价格=价格FROM产品表WHERE产品ID=@产品ID;–计算折扣后价格SET@折扣后价格=@原价格*(1-@折扣率);–返回结果SELECT@折扣后价格AS折扣后价格;END;3.2触发器示例以下是一个简单的触发器示例，用于此处省略新订单时自动更新库存：CREATETRIGGER更新库存ON订单表AFTERINSERTASBEGINUPDATE库存表SET库存数量=库存数量-INSERTed.数量FROM库存表JOINinsertedON库存表.产品ID=INSERTed.产品ID;END;通过以上内容，我们可以看到存储过程和触发器在数据库应用中具有重要的地位。合理使用存储过程和触发器能够显著提高数据库应用的开发效率和运行性能。6.实际应用案例6.1小型电商系统的数据库设计小型电商系统通常需要支持基本的商品展示、购物车管理、订单处理等功能。为了实现这些功能，我们需要设计一套合理的关系型数据库来存储和管理数据。本节将介绍一个小型电商系统的数据库设计方案，主要包括实体识别、属性定义、关系建立以及关键表结构的设计。（1）实体与属性根据小型电商系统的业务需求，我们可以识别出以下几个核心实体：实体名称描述用户系统中注册的用户，可以进行商品浏览、加入购物车、下单等操作商品商店中销售的物品，包含多种属性，如价格、库存等订单用户下的一笔订单，包含多个商品条目购物车用户此处省略的待购买商品集合每个实体都具有特定的属性，属性定义如下：◉用户实体属性属性名数据类型约束条件描述user_idINTPRIMARYKEY,AUTO_INCREMENT用户IDusernameVARCHAR(50)NOTNULL,UNIQUE用户名passwordVARCHAR(100)NOTNULL密码（加密存储）emailVARCHAR(100)NOTNULL,UNIQUE邮箱地址phoneVARCHAR(20)UNIQUE联系电话created_atDATETIMEDEFAULTCURRENT_TIMESTAMP账号创建时间◉商品实体属性属性名数据类型约束条件描述product_idINTPRIMARYKEY,AUTO_INCREMENT商品IDnameVARCHAR(100)NOTNULL商品名称descriptionTEXT商品描述priceDECIMAL(10,2)NOTNULL商品价格stockINTNOTNULL,DEFAULT0库存数量category_idINTFOREIGNKEY商品分类IDcreated_atDATETIMEDEFAULTCURRENT_TIMESTAMP商品创建时间◉订单实体属性属性名数据类型约束条件描述order_idINTPRIMARYKEY,AUTO_INCREMENT订单IDuser_idINTFOREIGNKEY下单用户IDtotal_amountDECIMAL(10,2)NOTNULL订单总金额statusVARCHAR(20)NOTNULL订单状态（如：待支付、已支付）created_atDATETIMEDEFAULTCURRENT_TIMESTAMP订单创建时间◉购物车实体属性属性名数据类型约束条件描述cart_idINTPRIMARYKEY,AUTO_INCREMENT购物车ID（或组合用户ID+商品ID作为唯一键）user_idINTFOREIGNKEY对应的用户IDproduct_idINTFOREIGNKEY对应的商品IDquantityINTNOTNULL,DEFAULT1商品数量（2）关系设计各实体间的关系如下：用户与商品（多对多）：用户可以浏览多个商品，一个商品被多个用户浏览。通过订单表或购物车表建立联系。表：orders（一对多从用户到订单）表：order_items（多对多从订单到商品）商品与分类（一对多）：一个分类下有多个商品，一个商品属于一个分类。通过product表中的category_id关联。用户与订单（一对多）：一个用户可以下多个订单，一个订单由一个用户下。关系：orders_id外键关联users_id订单与订单项（一对多）：一个订单包含多个订单项，一个订单项属于一个订