DB设计 11.1.1 数据库应用系统的生命期数据库应用系统的生命周期分为数据库规划、需求描述与分析、数据库与应用程序设计、数据库设计实现、测试、运行维护6个阶段。1、数据库规划数据库规划的任务是确定软件的开发目标及可行性。该阶段应该给出问题定义、可行性分析和项目开发计划。2、需求描述与分析需求描述与分析是以用户的角度，从系统中的数据和业务规则入手，收集和整理用户的信息，以特定的方式加以描述，是下一步工作的基础。3、数据库与应用程序设计数据库的设计是对用户数据的组织和存储设计；应用程序设计是在数据库设计基础上对数据操作及业务实现的设计，包括事务设计和用户界面设计。4、数据库设计实现数据库设计实现是依照设计，使用DBMS支持的数据定义语言实现数据库的建立，用高级语言编写应用程序5、测试测试是在数据库系统投入使用之前，通过精心制订的测试计划和测试数据来测试系统的性能是否满足设计要求，以便发现问题。6、运行维护数据库应用系统经过测试、试运行后即可正式投入运行。运行维护是系统投入使用后，必须不断地对其进行评价、调整和修改，直至系统消亡。11.1.2 数据库设计的一般策略数据库设计的一般策略有两种：自顶向下和自底向上。11.1.3 数据库设计的基本步骤一般将数据库设计分为如下6个阶段：（1）用户需求分析。（2）概念结构设计。（3）逻辑结构设计。（4）物理结构设计。（5）数据库实施阶段。（6）数据库运行和维护阶段。11.2.1 需求分析的任务、方法和目标需求分析阶段的任务：综合各个用户的应用需求，对现实世界要处理的对象（组织、部门和企业等）进行详细调查，在了解现行系统的概况，确定新系统功能的过程中，收集支持系统目标的基础数据及处理方法。（1）信息要求。用户需要在系统中保存哪些信息，由这些保存的信息要得到什么样的信息，这些信息以及信息间应当满足的完整性要求。（2）处理要求。用户在系统中要实现什么样的操作功能，对保存信息的处理过程和方式，各种操作处理的频度、响应时间要求、处理方式等以及处理过程中的安全性要求和完整性要求。（3）系统要求。包括安全性要求、使用方式要求和可扩充性要求。安全性要求：系统有几种用户使用，每一种用户的使用权限如何。使用方式要求：用户的使用环境是什么，平均有多少用户同时使用，最高峰有多少用户同时使用，有无查询相应的时间要求等。可扩充性要求：对未来功能、性能和应用访问的可扩充性的要求。11.2.2 需求分析阶段的文档需求分析阶段的成果是系统需求说明书，主要包括数据流图、数据字典、各种说明性表格、统计输出表和系统功能结构图等。数据字典（Data Dictionary,DD)是各类数据描述的集合，它是关于数据库中数据的描述，即元数据，而不是数据本身。如用户将向数据库中输入什么信息，从数据库中要得到什么信息，各类信息的内容和结构，信息之间的联系等。数据字典包括数据项、数据结构、数据流、数据存储和处理过程5个部分。数据字典是数据流图的补充，以下是五个部分的总结：{callout color="#f0ad4e"}数据项：数据的最小单位，不可再分的数据单位。数据项名称： 订单编号 别名： 订单号、OrderID 类型： 字符型 长度： 12位 取值范围： ORD20240100001 ~ ORD99999999999 含义说明： 格式：ORD+年份+月+5位序列号 示例：ORD20240100001表示2024年1月第1号订单 与其他项关系： 唯一标识一个订单，主键特征原子性：不能再分解为更小的数据单位独立性：具有独立的业务含义基础性：构成数据结构的基本元素数据结构：由多个数据项组成的数据单位，描述数据之间的关系。数据结构名称： 客户信息 别名： 客户资料、CustomerInfo 组成： 客户编号 客户姓名 客户类型 联系电话 电子邮箱 注册日期 信用等级 数据结构类型： 客户基本信息 备注： 用于存储客户的核心信息特征组合性：由数据项或其他数据结构组成层次性：可以嵌套定义完整性：表达一个完整的业务对象数据流：数据在系统内的流动，包括来源、去向、组成和流量等。数据流名称： 支付请求 编号： DF-003 来源： 支付处理过程(P2) 去向： 银行支付网关 数据流组成： 订单编号 支付金额 支付方式代码 时间戳 商户ID 客户支付令牌（加密） 流量： 平均200次/小时，高峰1000次/小时 峰值流量： 双十一期间可达5000次/小时 备注： 采用RSA加密传输，JSON格式特征方向性：有明确的源和目的地瞬时性：在某个时间点传递内容性：包含具体的数据内容数据存储：系统中保存数据的地方，是数据结构的载体。数据存储名称： 订单表 编号： DS-001 别名： 订单主表、Orders 输入数据流： 新增订单、修改订单状态 输出数据流： 订单查询结果、订单统计信息 组成： 订单编号 [PK] 客户编号 [FK] 下单时间 订单状态 总金额 收货地址ID 物流单号 支付状态 数据量： 约100万条记录 增长量： 每月约5万条 存取频率： 查询：1000次/分钟，更新：200次/分钟 存储方式： 数据库表（MySQL InnoDB） 索引： 主键：订单编号 索引：客户编号、下单时间、订单状态 分区策略： 按月份水平分区特征持久性：数据长期保存结构性：有明确的组织结构访问性：通过特定接口访问处理过程：对数据进行加工处理的具体操作，描述输入、处理和输出。处理过程名称： 处理订单支付 编号： P2.1 输入： 支付信息（订单编号、金额、支付方式） 输出： 支付结果（成功/失败、交易号） 处理逻辑： 1. 验证订单状态是否为"待支付" 2. 检查支付金额与订单金额是否一致 3. 根据支付方式调用相应支付接口 - 支付宝：调用支付宝SDK - 微信支付：调用微信支付API - 银行卡：调用银联接口 4. 接收支付网关返回结果 5. 更新订单支付状态 IF 支付成功 THEN 更新订单状态为"已支付" 记录交易流水号 发送支付成功通知 ELSE 更新订单状态为"支付失败" 记录失败原因 通知客户重新支付 6. 返回处理结果 激发条件： 用户提交支付请求 执行频率： 平均500次/小时 性能要求： 响应时间<2秒，成功率>99.9% 异常处理： 网络超时重试3次，支付失败记录日志并告警特征功能性：完成特定的数据处理功能转换性：将输入数据转换为输出数据封装性 | 隐藏内部处理细节数据字典各部分的关联关系 数据项 ↓ 数据结构 ←→ 数据存储 ↓ ↓ 数据流 → 处理过程 (流动的数据) (加工数据)关联示例：订单处理数据项：订单编号、商品编号、数量、单价数据结构：订单明细 = 商品编号 + 数量 + 单价数据存储：订单表（存储订单数据结构）数据流：新增订单（从界面流向订单处理过程）处理过程：创建订单（接收数据流，操作数据存储）{/callout}数据流图是数据库设计的逻辑蓝图，它帮助设计者在编码和建表之前，清晰地理解数据从哪里来、经过什么处理、存储在哪里、最后到哪里去。为了从宏观到细节地描述系统，采用自顶向下的分层绘制方法：上下文图：层级：0层图。内容：只有一个代表整个系统的“过程”，以及所有与之交互的“外部实体”和主要数据流。目的：界定系统边界。顶层图：层级：1层图。内容：将上下文图中的单一“过程”分解为几个主要的子“过程”（通常3-9个），并显示它们与外部实体、数据存储之间的数据流。目的：展示系统的主要功能模块。逐层分解：层级：2层、3层图等。内容：对顶层图中的某个复杂“过程”进一步分解，形成更详细的DFD。原则：保持平衡，即子图的输入/输出数据流必须与父图中该过程的输入/输出一致上下文图 ┌─────────────────┐ │ 银行支付 │ │ 网关 │ └────────┬────────┘ │支付验证结果 │ ┌─────────────┐ │ ┌─────────────┐ │ │ │ │ │ │ 顾客 │──────┼──────│ 物流公司 │ │ │ │ │ │ └─────────────┘ │ └─────────────┘ │ │ │ │提交订单 │发货状态更新 │配送信息 │订单查询 │ │ │ │ │ └─────────────┼─────────────┘ │ ┌──────────▼──────────┐ │ │ │ 在线书店系统 │ │ │ └─────────────────────┘顶层图┌─────────────┐ ┌─────────────┐ │ │ 提交订单 │ │ │ 顾客 ├────────────────► 处理订单 │ │ │ │ (P1) │ └─────────────┘ └──────┬──────┘ ▲ │ │ 订单确认 │订单详情 │ │ │ ┌─────────▼──────────┐ │ │ │ │ │ 订单数据库 │ │ │ (D1) │ │ │ │ │ └─────────┬──────────┘ │ │ │ │订单信息 │ │ ┌───────┴───────┐ ┌─────▼─────┐ │ │ 支付请求 │ │ │ 银行支付 │◄───────────────┤ 处理支付 │ │ 网关 │ │ (P2) │ │ ├───────────────►│ │ └───────────────┘ 支付结果 └─────┬─────┘ │ │支付状态 │ ┌───────▼───────┐ │ │ │ 更新库存状态 │ │ (P3) │ └───────┬───────┘ │ │库存调整 │ ┌──────────────▼──────────────┐ │ │ │ 库存数据库 │ │ (D2) │ │ │ └──────────────┬──────────────┘ │ │发货信息 │ ┌─────────▼─────────┐ │ │ │ 处理发货 │ │ (P4) │ └─────────┬─────────┘ │ │发货指令 │ ┌─────────▼─────────┐ │ │ │ 物流公司 │ │ │ └───────────────────┘ │ │配送状态 │ ┌─────────▼─────────┐ │ │ │ 更新订单状态 │ │ (P5) │ └─────────┬─────────┘ │ │状态信息 │ ┌─────────▼─────────┐ │ │ │ 订单数据库 │ │ (D1) │ └───────────────────┘概念结构设计是在需求分析的基础上，依照需求分析中的信息要求，对用户信息加以分类、聚集和概括，建立信息模型。• 概念结构设计最著名最常用的方法是实体-联系方法，简称E-R方法。它将现实世界的信息结构统一用实体、属性以及实体之间的联系来描述。• 概念结构设计工作步骤包括：选择局部应用、逐一设计分E-R图和E-R图合并。1、选择局部应用选择适当层次的数据流图，让这一层的每一部分对应一个局部应用，实现某一项功能，从这一层入手，就能很好地设计分E-R图。2、逐一设计分E-R图划分好各个局部应用之后，就要对每一个局部应用逐一设计分E-R图，又称为局部E-R图。现实生活中许多事物，作为实体还是属性没有明确的界定，这需要根据具体情况而定，一般遵循以下两条准则：（1）属性不可再分，即属性不再具有需要描述的性质，不能有属性的属性。（2）属性不能与其他实体发生联系，联系是实体与实体间的联系。3、E-R图合并根据局部应用设计好各局部E-R图之后，就可以对各分E-R图进行合并。合并的目的在于解决冲突，消除冗余。分E-R图合并时，它们之间存在的冲突主要有以下三类：（1）属性冲突：同一属性可能会存在于不同的分E-R图中，由于设计人员不同或是出发点不同，对属性的类型、取值范围、数据单位等可能会不一致。（2）命名冲突：相同意义的属性，在不同的分E-R图上有着不同的命名，或是名称相同的属性在不同的分E-R图中代表着不同的意义，这些也需要进行统一。（3）结构冲突：同一实体在不同的分E-R图中有不同的属性，同一对象在某一分E-R图中被抽象为实体而在另一分E-R图中又被抽象为属性。分E-R图合并时，可能遇到以下冲突，处理原则如下： 1. 属性冲突 现象：同一属性在不同局部E-R图中的数据类型或取值范围不一致。 处理原则：协商统一该属性的数据类型和取值范围。 目的：确保数据定义的一致性。 2. 命名冲突 现象：不同含义的对象使用了相同名称，或相同含义的对象使用了不同名称。 处理原则：对同名异义对象改名区分，对异名同义对象统一命名。 目的：确保全局模型中的命名唯一且明确。 3. 结构冲突（对象抽象级别不同） 现象：同一对象在一个分E-R图中作为实体，在另一个分E-R图中作为属性。 处理原则：根据整体业务需求，将该对象统一作为实体或属性。 目的：确保对象抽象级别的一致性。 4. 结构冲突（联系类型不同） 现象：相同的两个实体之间的联系方式在不同分E-R图中类型不一致（如一个图中是1:1，另一个是1:N）。 处理原则：分析实际业务语义，确定正确的联系类型。 目的：确保联系类型的正确性。 5. 结构冲突（属性组成不同） 现象：同一实体在不同业务视图中属性不一致。 处理原则：取各分E-R图中该实体属性的并集。 目的：确保全局模型涵盖所有业务需求。逻辑结构设计就是在概念结构设计的基础上进行数据模型设计，可以是层次模型、网状模型和关系模型。• 逻辑结构设计阶段的主要工作步骤包括确定数据模型、将E-R图转换成指定的数据模型、确定完整性约束和确定用户视图。{callout color="#f0ad4e"}“概念结构”和“确定关系模式”是数据库设计中两个不同阶段、不同抽象层次的任务。我们可以用一个比喻来理解：概念结构（ER图）： 就像是建筑的设计蓝图。它描绘了这栋楼里要有多少个房间、房间之间如何连通、每个房间的功能是什么。它不关心墙体是用砖还是用钢筋混凝土。确定关系模式： 就像是根据蓝图，画出一套具体的施工图纸，明确标出每一面墙的尺寸、所用材料、门窗的具体位置。这已经进入了具体的实施层面。关系模式是概念结构的逻辑实现，是一个 “从抽象到具体”的转化过程。概念结构设计和应用程序模块设计都是系统设计的组成部分，它们相互影响、互为前提，都基于同一个需求分析，但不存在简单的单向依赖关系。{/callout}11.4.1 E-R图向关系模式的转换1、实体向关系模式的转换2、联系向关系模式的转换重要，最后讲。11.4.2 关系模式的规范化由E-R图转换得来的初始关系模式并不能完全符合要求，还会有数据冗余、更新异常存在，这就需要经过进一步的规范化处理。（1）根据语义确定各关系模式的数据依赖。（2）根据数据依赖确定关系模式的范式，判断关系模式是否达到了3NF或4NF。（3）如果关系模式不符合要求，要进行分解，达到3NF、BCNF或4NF。（4）关系模式的评价及修正。有时根据处理要求，可能还需要增加部分冗余以满足处理要求，这就需要做部分关系模式的处理，分解、合并或增加冗余属性，提高存储效率和处理效率。11.4.3 确定完整性约束11.4.4 用户视图的确定（1）根据数据流图确定处理过程使用的视图。（2）根据用户类别确定不同用户使用的视图。数据库在物理设备上的存储结构与存取方法称为数据库的物理结构，它依赖于给定的计算机系统。为一个给定的逻辑数据模型设计一个最适合应用要求的物理结构的过程，就是数据库的物理设计。11.5.1 数据库物理设计工作过程11.5.2 数据库物理设计工作步骤物理设计的主要工作步骤包括：确定数据分布、存储结构和访问方式。1、确定数据分布（1）根据不同应用分布数据。（2）根据处理要求确定数据的分布。（3）对数据的分布存储必然会导致数据的逻辑结构的变化，要对关系模式做新的调整，回到数据库逻辑设计阶段做必要的修改。2、确定数据的存储结构存储结构具体指数据文件中记录之间的物理结构。为提高数据的访问速度，通常会采用索引技术。在物理设计阶段，要根据数据处理和修改要求，确定数据库文件的索引字段和索引类型。3、确定数据的访问方式（1）存储记录结构设计。（2）存储记录布局。（3）存取方法的设计。1、数据库重组和重构数据库重组是指在不改变数据库逻辑和物理结构的情况下，去除数据库存储文件中的废弃空间以及碎片空间中的指针链，使数据库记录在物理上紧连。数据库系统运行过程中，会因为一些原因而对数据库的结构做修改，称为数据库重构。重构包括表结构的修改和视图的修改。2、数据库系统的审计审计是一种DBMS工具，它记录数据库资源和权限的使用情况。启用审计功能，可以产生审计跟踪信息，包括哪些数据库对象受到了影响，谁在什么时候执行了这些操作。审计是被动的，它只能跟踪对数据库的修改而不能防止，但作为一个安全性手段，起到对非法入侵的威慑作用，可以据此追究非法入侵者的法律责任。审计功能的开启会影响系统的性能。3、数据库的存储管理在数据库系统运行过程中，随着数据的不断变更，会影响到系统的响应效率。通过以下手段进行存储管理，可有效地提高系统性能。（1）索引文件和数据文件分开存储，事务日志文件存储在高速设备上。（2）适时修改数据文件和索引文件的页面大小。（3）定期对数据进行排序。（4）增加必要的索引项。4、数据安全性管理（1）建立网络安全，主要是防火墙的设置。（2）操作系统级安全，进行登录用户的管理。（3）DBMS级安全，对访问数据库的用户进行密码验证。（4）角色和用户的授权管理。（5）建立视图和存储过程加强安全性。（6）使用审计功能，为追究非法入侵者法律责任提供证据，发现安全漏洞。5、SQL语句的编码检验（1）尽可能地减少多表查询或建立物化视图。（2）以不相关子查询替代相关子查询。（3）只检索需要的列。（4）用带IN的条件子句等价替换OR子句。（5）经常提交COMMIT，以尽早释放锁。6、表设计的评价（1）如果频繁的访问是对两个相关的表进行连接操作，则考虑将其合并。（2）如果频繁的访问只是在表中的某一部分字段上进行，则考虑分解表，将该部分单独作为一个表。（3）对于更新很少的表，引入物化视图。7、索引维护和改进（1）如果查询是瓶颈，则在关系上建立适应的索引，通常在作为查询条件的属性上建立索引，可以提高查询效率。（2）如果更新是瓶颈，每次更新都会重建表上的索引，引起效率的降低，则考虑删除某些索引。（3）选择适当的索引类型，如果是经常使用范围查询，则B树索引比散列索引更高效。（4）将有利于大多数据查询和更新的索引设为聚簇索引。ER图关系模式转换1、实体向关系模式的转换：ER图中的每一个实体单独转换成一个关系模式，实体名对应关系模式的名称，实体的属性转换为关系模式的属性，实体标识符就是关系的码。班主任（工号，姓名，身份证号，住址）班级（班级编号，名称，人数）2、一对一联系的转换(1：1)：通常一对一联系不需要将其转换为一个独立的关系模式，只需要将联系归并到关联的两个实体的任意一方，给待归并的一方实体属性集中增加另一方实体的码和该联系的属性即可，归并后的实体码保持不变。班主任（工号，姓名，身份证号，住址，班级编号，任职时间）或： 班级（班级编号，名称，人数，班主任工号，任职时间）3、一对多联系的转换(1：*)：通常一对多联系也不需要将其转换为一个独立的关系模式，只需要将联系归并到关联的两个实体的多方，给待归并的多方实体属性集中增加一方实体的码和该联系的属性即可，归并后的多方实体码保持不变。员工（工号，姓名，身份证号，公司编号，入职时间，离职时间）4、多对多联系的转换(：)：多对多联系只能转换成一个独立的关系模式，关系模式的名称取联系的名称，关系模式的属性取该联系所关联的两个多方实体的码及联系的属性，关系的码是多方实体的码构成的属性组。选修（学号，课程编号，成绩，出勤率）5、多对多对多联系的转换(：：*)：三方联系的多对多对多（：：*）也是一样，只能转换成一个独立的关系模式，关系模式的属性取三方实体的码及联系的属性，关系模式的码为三方实体的码组成的属性组。如：一个供应商可以给多个项目供应多种零件，一个项目可以使用多个供应商供应的多种零件。供应（供应商编号，项目编号，零件编号，供应数量）联系类型是“关系种类”，关系模式是“表格结构”联系类型（ER模型层面）所在位置：ER图中表现形式：菱形 + 连线关注点：这是什么关系？（师生、订购、拥有）谁和谁有关系？（学生和课程）是一对一、一对多还是多对多？有什么属性吗？（成绩是“选课”关系的属性）例子：学生和课程之间的“选课”联系医生和病人之间的“诊疗”联系关系模式（数据库层面）所在位置：数据库中表现形式：SQL建表语句关注点：表叫什么名字？有哪些列（字段）？每个列的数据类型是什么？主键、外键是什么？扩展E-R图（Enhanced ER或EER）包括一些高级概念，如子类/继承、聚合等。联系可以看作实体，与另一实体产生联系，称为聚合"。聚合是一种抽象，其中一个联系被当作一个高层实体来对待，以便它可以参与其他联系。联系的属性是指联系本身的属性。联系的属性不能是关联实体的标识符属性，因为标识符属性是实体的属性。关于三元联系与多个二元联系的区别。在E-R模型中，三个实体之间的单个三元联系（ternary relationship）并不等价于它们之间的三个两两联系（三个二元联系）。三元联系表示三个实体同时参与一个联系，而两两联系不能表达同样的约束。例如，假设有实体：医生、病人、药物，一个三元联系"治疗"表示哪个医生给哪个病人开了哪种药。如果分解为三个二元联系：医生-病人、病人-药物、医生-药物，那么可能丢失信息，比如无法确保特定的医生-病人-药物组合。所以它们不等价。在 E-R 模型（实体-关系模型） 中，不同类别的 属性（Attribute） 有不同的表示方法，以区分其性质和功能。单值属性（Simple Attribute）定义：不可再分的独立属性（如：姓名、年龄）。E-R 图表示：椭圆（○）多值属性（Multivalued Attribute）定义：一个属性可以存储多个值（如：一个人有多个电话号码）。E-R 图表示：双椭圆（◎）复合属性（Composite Attribute）定义：由多个子属性组成（如：地址 = 省 + 市 + 区）。E-R 图表示：椭圆 + 连线子属性派生属性（Derived Attribute）定义：可通过其他属性计算得到（如：年龄 ← 出生日期）。E-R 图表示：虚线椭圆（┆○┆）主键（Primary Key）定义：唯一标识实体的属性（如：学号、身份证号）。E-R 图表示：下划线（如 学号）或在属性后标注 (PK)外键（FK） Foreign Key表示：虚线下划线或标注 (FK)

数据库知识点 GRANT 子句是 SQL 中用于给数据库用户或角色授予权限的命令。 它是数据库权限管理的核心，控制着“谁能在什么对象上执行什么操作”。简单来说，它的作用就是：开门发钥匙。门： 数据库里的各种对象（如表、视图、数据库、函数等）。钥匙： 对这些对象执行的操作权限（如查看、插入、删除等）。发钥匙的人： 数据库管理员或拥有授权权限的用户。GRANT是标准SQL提供的授权语句，即通过把数据库对象的操作权限授予用户，用户具有对象上的操作权限才能进行相应的操作。视图是建立在基本表上的虚表，通过外模式/模式的映像，将视阁所提供的字段（外模式）指向基本表（模式）中的部分数据，用户通过视图所访问的数据只是对应基本表 中的部分数据，而无需给用户提供基本表中的全部数据，则视图外的数据对用户是不可见的，即受到了保护。存储过程是数据库所提供的一种数据库对象，通过存储过程定义一段代码，提供给应用程序调用来执行。从安全性的角度考虑，更新数据时，通过提供存储过程让第三方 凋用，将需要更新的数据传入存储过程，而在存储过程内部用代码分别对需要的多个表进行更新，从而避免了向第三方提供系统的表结构，保证了系统的数据安全。主要用途授予对数据库的访问权限：允许用户连接到一个数据库。授予对数据对象的操作权限：允许用户对特定的表、视图等进行增删改查。授予执行特定代码的权限：允许用户运行存储过程或函数。授予授予权限的权限：允许用户将自己获得的权限再授予其他人（使用 WITH GRANT OPTION）。GRANT 权限类型 ON 对象 TO 用户或角色 [WITH GRANT OPTION];权限类型{callout color="#f0ad4e"}SELECT： 查询数据INSERT： 插入新数据UPDATE： 更新现有数据DELETE： 删除数据ALL PRIVILEGES： 所有权限CREATE, DROP, ALTER： 对数据库或表结构的操作权限EXECUTE： 执行存储过程或函数USAGE： 使用模式、序列等对象的权限数据库对象权限 ├── 数据访问权限 │ ├── SELECT (读取) │ ├── INSERT (创建) │ ├── UPDATE (修改) │ └── DELETE (删除) │ ├── 结构修改权限 │ ├── ALTER (修改结构) │ ├── INDEX (创建索引) │ └── REFERENCES (外键约束) │ ├── 特殊操作权限 │ ├── EXECUTE (执行) │ └── TRUNCATE (清空) │ └── 管理权限 ├── GRANT OPTION (转授) └── WITH ADMIN OPTION (管理)对象可以是具体的 database_name.table_name可以是整个数据库 database_name.*可以是所有数据库 .用户或角色：一个或多个数据库用户名。角色名（现代数据库更推荐先授权给角色，再把角色分配给用户）。{/callout}授予用户 read_user 对 employees 表的只读权限： GRANT SELECT ON company.employees TO read_user; 授予用户 app_user 对 orders 表的完整数据操作权限： GRANT SELECT, INSERT, UPDATE, DELETE ON shop.orders TO app_user; 授予用户 admin_user 对 reports 数据库的所有权限，并允许其授权他人： GRANT ALL PRIVILEGES ON reports.* TO admin_user WITH GRANT OPTION; --WITH GRANT OPTION 是 SQL 授权语句中的一个可选子句，它允许被授权者（grantee）将自己获得的权限再次授予其他用户或角色。 允许用户 user1 连接到名为 mydb 的数据库： GRANT CONNECT ON DATABASE mydb TO user1; -- PostgreSQL 示例 创建一个只能查询特定视图的只读账户： GRANT SELECT ON my_schema.sales_summary_view TO reporting_user; 与 GRANT 对应的是 REVOKE 子句，用于收回已授予的权限。REVOKE INSERT, UPDATE ON shop.orders FROM app_user;注意事项与实践最小权限原则：只授予用户完成工作所必需的最小权限。不要随意使用 ALL PRIVILEGES。使用角色管理：对于复杂的权限管理，先创建角色（如 analyst, developer），将权限授予角色，再将角色分配给用户。这样更易于维护。定期审计：使用如 SHOW GRANTS FOR user_name;（MySQL）或 \dp（PostgreSQL）等命令检查现有权限。WITH GRANT OPTION 要慎用：这会导致权限链难以追踪和管理。权限生效：通常 GRANT 执行后权限立即生效，但有时用户需要重新登录才能激活。触发器 是一种特殊的存储过程，它在特定的事件（如 INSERT、UPDATE、DELETE）发生时自动执行。但并不是所有数据库操作都能激活触发器。（增删改）COMMIT 是"确认保存"的命令，它将事务中的所有更改永久写入数据库。要理解 COMMIT，必须先明白什么是 事务：事务是一组 SQL 操作，它们要么 全部成功，要么 全部失败，是一个不可分割的工作单元。事务的经典例子：银行转账-- 这是一个完整的事务，包含两个操作： 1. 从A账户扣款100元 2. 向B账户加款100元 -- 这两个操作必须同时成功或同时失败 -- 不能只扣款不加款，也不能只加款不扣款COMMIT 的作用机制事务的生命周期：开始事务 → 执行操作 → (可选：回滚ROLLBACK) → 提交COMMIT-- 1. 开始事务后，所有的修改都是临时的 BEGIN TRANSACTION; -- 开始事务 UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 临时修改 UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 临时修改 -- 此时： -- - 你自己能看到修改后的数据 -- - 其他用户/会话看不到你的修改 -- - 如果系统崩溃，修改会丢失 -- 2. 使用COMMIT后，修改变成永久的 COMMIT; -- 确认保存 -- 此时： -- - 修改被永久写入磁盘 -- - 所有用户都能看到修改 -- - 即使系统崩溃，修改也不会丢失为什么需要 COMMIT？1.保证数据一致性（ACID原则）COMMIT 是 ACID 中 D（持久性） 的实现：A（原子性）：事务的所有操作是一个整体C（一致性）：事务前后数据库状态一致I（隔离性）：事务之间互不干扰D（持久性）：COMMIT 后数据永久保存2.提供"撤销"机会--在 COMMIT 之前，可以用 ROLLBACK 撤销所有操作： BEGIN TRANSACTION; -- 不小心删错了数据 DELETE FROM employees WHERE department = 'IT'; -- 发现错误！可以撤销 ROLLBACK; -- 所有删除操作被撤销，数据恢复 -- 或者确认无误后提交 -- COMMIT; -- 永久删除3.批量操作的性能优化-- 没有事务：每条INSERT都立即写入磁盘，性能差 INSERT INTO log_table VALUES (...); -- 立即写磁盘 INSERT INTO log_table VALUES (...); -- 立即写磁盘 -- ... 1000次 -- 使用事务：批量写入，性能大幅提升 BEGIN TRANSACTION; INSERT INTO log_table VALUES (...); INSERT INTO log_table VALUES (...); -- ... 1000次 COMMIT; -- 一次性写入磁盘，效率高视图视图是一个虚拟表，其内容由查询定义。它不存储数据本身，而是存储查询。当查询视图时，数据库引擎执行底层查询并返回结果。{callout color="#f0ad4e"}视图不存储数据，但可以通过视图访问数据" —— 这基本正确。视图本身通常不存储数据（物化视图除外，但标准SQL视图是虚拟的）。视图允许通过它访问底层表中的数据。"视图提供了一种数据安全机制" —— 视图可用于限制用户对特定行或列的访问，从而实现安全机制。例如，可以创建一个视图只显示某些列或满足某些条件的行，然后授予用户访问视图的权限，而不是底层表。"视图可以实现数据的逻辑独立性" —— 逻辑数据独立性是指应用程序不受数据库逻辑结构（如表结构）变化的影响。视图可以隐藏底层表的复杂变化，提供一致的接口。如果表结构改变，可以修改视图定义以保持相同的视图结构，从而使依赖于视图的应用程序无需修改。"视图能够提高对数据的访问效率" —— 视图本身是虚拟的，不存储数据。查询视图通常需要执行底层查询，可能涉及联接、过滤等，可能不会提高效率，有时甚至可能降低效率，因为额外的抽象层可能导致优化困难。但是，有些数据库支持物化视图，它们存储实际数据并可以提高查询性能。但一般来说，标准视图不会提高访问效率；事实上，它们可能引入性能开销。效率提升通常依赖于索引、物化视图等技术。{/callout}与 NOT IN 等价的操作符是 NOT EXISTS，但二者在 NULL 值处理 上有重要差异假设有两个表：员工 和 已离职员工。使用 NOT IN（注意 NULL 问题）：SELECT * FROM 员工 WHERE 员工ID NOT IN (SELECT 员工ID FROM 已离职员工);如果 已离职员工 中有任意一行的 员工ID 为 NULL，则查询返回 空结果。使用 NOT EXISTS（安全写法）：SELECT * FROM 员工 e WHERE NOT EXISTS (SELECT 1 FROM 已离职员工 d WHERE d.员工ID = e.员工ID);即使 已离职员工 中有 NULL，也能正确查询。<> ALL（与 NOT IN 同样存在 NULL 问题）：SELECT * FROM 员工 WHERE 员工ID <> ALL (SELECT 员工ID FROM 已离职员工);聚合函数 是对一组值进行计算并返回单个值的函数，通常与 GROUP BY 子句一起使用，对数据行进行分组统计。常用聚合函数COUNT() - 计数-- 统计行数（包括NULL） SELECT COUNT(*) FROM 表名; -- 统计特定列非NULL值的数量 SELECT COUNT(列名) FROM 表名; -- 统计唯一值数量 SELECT COUNT(DISTINCT 列名) FROM 表名;SUM() - 求和-- 对数值列求和 SELECT SUM(金额) FROM 订单表; -- 忽略NULL值 SELECT SUM(单价 * 数量) FROM 明细表;AVG() - 平均值-- 计算平均值（忽略NULL） SELECT AVG(成绩) FROM 学生成绩; -- 包含NULL的处理 SELECT AVG(COALESCE(成绩, 0)) FROM 学生成绩;MAX() / MIN() - 最大值/最小值-- 最大值 SELECT MAX(价格) FROM 产品表; -- 最小值 SELECT MIN(入职日期) FROM 员工表;GROUP_CONCAT() (MySQL) / STRING_AGG() (PostgreSQL/SQL Server) / LISTAGG() (Oracle)-- MySQL: 将多行值连接成字符串 SELECT 部门, GROUP_CONCAT(员工姓名 SEPARATOR ', ') FROM 员工表 GROUP BY 部门; -- SQL Server SELECT 部门, STRING_AGG(员工姓名, ', ') FROM 员工表 GROUP BY 部门;STDDEV() / VARIANCE() - 标准差/方差-- 标准差 SELECT STDDEV(分数) FROM 考试成绩; -- 方差 SELECT VARIANCE(分数) FROM 考试成绩;SELECT列表规则-- 错误：非聚合列未包含在GROUP BY中 SELECT 部门, 员工姓名, AVG(工资) -- 员工姓名不在GROUP BY中 FROM 员工 GROUP BY 部门; -- 正确 SELECT 部门, 员工姓名, AVG(工资) FROM 员工 GROUP BY 部门, 员工姓名; -- 或者使用聚合函数处理员工姓名当使用 GROUP BY 分组时，SELECT 列表中只能包含：聚合函数（如 COUNT、SUM、AVG 等）GROUP BY 子句中出现的列常量或表达式（但必须基于上述1或2）逻辑解释：GROUP BY 将多行数据合并为一行，SELECT 列表必须确保每列在分组后只有一个确定的值。-- 假设员工表数据： 员工ID | 姓名 | 部门 | 工资 1 | 张三 | 销售部 | 5000 2 | 李四 | 销售部 | 6000 3 | 王五 | 技术部 | 7000 -- 如果按部门分组： 销售部：有张三(5000)、李四(6000) 两行 技术部：有王五(7000) 一行 问题：销售部分组中有"张三"和"李四"两个姓名，数据库不知道返回哪个SELECT包含未在GROUP BY中的列SELECT 部门, 姓名, -- 错误！每个部门有多个姓名，应该显示哪个？ AVG(工资) as 平均工资 FROM 员工表 GROUP BY 部门;正确写法将列添加到 GROUP BY-- 同时按部门和姓名分组（这样每个组只有一个人） SELECT 部门, 姓名, AVG(工资) FROM 员工表 GROUP BY 部门, 姓名; -- 结果：每人一行，失去了分组统计的意义执行顺序-- SQL执行顺序 1. FROM/JOIN -- 确定数据源 2. WHERE -- 行级筛选 3. GROUP BY -- 分组 4. HAVING -- 组级筛选 5. SELECT -- 选择列 6. ORDER BY -- 排序 7. LIMIT/OFFSET -- 限制结果权限管理主体指可以被授予权限的实体。用户： 登录数据库的单个账户。角色： 一组权限的集合。将权限授予角色，再将角色授予用户，是管理权限的最佳实践。组（在某些数据库中如 SQL Server）： 类似角色。客体指可以被操作的对象。层次结构通常为：服务器/实例 -> 数据库 -> 模式 -> 对象。对象包括：表、视图、存储过程、函数、序列等。权限定义了对某个客体可以执行的操作。主要分为两类：系统权限： 针对数据库或服务器本身的操作权限（如创建数据库、修改用户、查看服务器状态）。对象权限： 针对特定数据库对象的操作权限（如对某张表的 SELECT, INSERT, UPDATE, DELETE, EXECUTE 等）。SQL 中的主要集合操作符有以下几种：注意事项：列的数量和类型必须匹配：所有参与集合操作的 SELECT 语句必须有相同数量的列，且对应列的数据类型必须兼容。排序：可以在最后使用一个 ORDER BY 子句对整个结果排序。数据库支持：UNION 广泛支持。INTERSECT 和 EXCEPT 在 SQL Server、PostgreSQL、SQLite 中支持。Oracle 使用 MINUS 代替 EXCEPT。MySQL 5.7 及以下版本不支持 INTERSECT 和 EXCEPT，需用 JOIN 或子查询模拟。断言（Assertion） 是 SQL 标准中定义的一种完整性约束，用于声明数据库必须始终满足的全局条件（涉及一个或多个表）。如果任何数据修改操作（插入、更新、删除）导致断言条件为假，则该操作会被拒绝。特点：全局性：不依附于特定表，可跨多个表定义约束。自动维护：数据库自动检查并强制执行。复杂条件：可使用子查询、聚集函数等。语法CREATE ASSERTION <断言名称> CHECK (<条件>);通常使用以下方式替代断言：触发器（Triggers）触发器是一种特殊的存储过程，它与表相关联，当特定的数据修改事件发生在表上时，数据库会自动执行触发器中定义的SQL语句。触发器通常用于强制执行业务规则、维护数据完整性、审计数据变更触发器通常被定义为一组（一个或多个）SQL语句的集合，这些语句在特定事件（如INSERT、UPDATE、DELETE）发生时自动执行。这种“集合”意味着触发器可以包含多个SQL语句，这些语句作为一个整体在触发器被触发时执行。存储过程 通过封装数据修改操作来检查约束。应用层验证 在应用程序代码中保证数据完整性。与 CHECK 约束的区别触发器是一种特殊的数据库对象，它在特定事件发生时自动执行，用于实现复杂的业务规则、数据完整性约束和审计跟踪。触发器是什么？一段与表关联的 PL/SQL 或 T-SQL 代码在指定事件发生时自动触发执行依附于特定的表或视图触发器的三个核心要素：触发事件：INSERT、UPDATE、DELETE触发时机：BEFORE（之前）或 AFTER（之后）触发频率：行级（每行）或语句级（每次语句）触发器类型（按执行时机） 1. BEFORE 触发器 在数据修改操作之前执行，常用于： 数据验证 数据转换 防止非法操作 -- MySQL 示例：插入前检查工资 DELIMITER // CREATE TRIGGER check_salary_before_insert BEFORE INSERT ON Teachers FOR EACH ROW BEGIN IF NEW.salary < 3000 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '工资不能低于3000'; END IF; END // DELIMITER ; 2. AFTER 触发器 在数据修改操作之后执行，常用于： 审计日志 同步更新 级联操作 -- SQL Server 示例：记录删除日志 CREATE TRIGGER log_teacher_delete ON Teachers AFTER DELETE AS BEGIN INSERT INTO TeacherAudit (teacher_id, action, audit_time) SELECT deleted.teacher_id, 'DELETE', GETDATE() FROM deleted; END; 不同数据库的触发器语法 MySQL CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name FOR EACH ROW BEGIN -- 触发器逻辑 END; Oracle CREATE OR REPLACE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE} ON {table_name | view_name} [FOR EACH ROW] DECLARE -- 变量声明 BEGIN -- 触发器逻辑 END; 触发器本身并不拥有独立的事务，它只是在已有事务中执行的一组操作。触发器是事务的一部分，而不是事务的控制者。触发器操作会随着主事务一起提交或回滚，不能自己决定事务的边界。-- 想象一个简单的UPDATE操作： BEGIN TRANSACTION; -- 事务开始 UPDATE Teachers SET salary = 6000 WHERE id = 101; -- 触发AFTER UPDATE触发器 -- 触发器中的操作在此执行 COMMIT; -- 事务结束，提交所有更改关键点： 触发器在 UPDATE 之后、COMMIT 之前执行，但触发器本身不能改变事务的边界。触发器不能包含事务控制语句的根本原因：职责分离：触发器负责数据完整性，事务控制由应用程序或存储过程负责事务一致性：保证所有操作要么全部成功，要么全部失败避免死锁和竞态条件：如果多个触发器各自控制事务，容易导致复杂并发问题可预测性：确保数据库行为一致，易于理解和维护记住：触发器是"乘客"，存储过程/应用是"司机"乘客可以提醒司机（抛出异常）但不能抢方向盘（控制事务）最终去哪里（提交/回滚）由司机决定触发器不传递参数对数据库的更新，应采用事务的方式，以对应现实中的业务。用户在现实业务过程中通过调用事务程序，将事务程序交由DBMS来执行，DBMS通过其并发调度机制完成事务的并行执行。存储过程正是在服务器端所提供的功能调用，适用于编写更新数据库的事务程序。触发器是由更新语句来触发执行的，适用于数据的联动操作和复杂约束的实现，无法供应用程序主动调用。嵌入式SQL（Embedded SQL） 是一种将SQL语句直接嵌入到宿主编程语言（如C、C++、Java、COBOL等）中的技术，允许在传统编程语言中直接操作数据库。嵌入式SQL的语法元素声明段（Declare Section）连接数据库游标操作 游标是一种数据库对象，允许你逐行访问查询结果集，就像读书时使用书签一样，可以逐行“翻阅”数据结果。动态SQL

此内容被密码保护 加密文章，请前往内页查看详情

此内容被密码保护 加密文章，请前往内页查看详情

此内容被密码保护 加密文章，请前往内页查看详情