投稿

[

优速盾-小U • • 网站百科 • 阅读 127

新增内容：2023年春数据库期末考试题目整理

均为考后回忆内容，不够准确，仅供参考
题量较大

  • 给表找主键和外键，说明如何实现参照完整性约束
  • 给定关系代数运算说明如何优化
  • 分解范式（给一个表说明存在的问题并拆分）
  • 事务并发执行出现的问题
  • 数据库备份（冷备份与热备份优缺点）
  • JDBC给代码写注释
  • ODBC写出哪些是数据库提供哪些是OS提供
  • NOSQL四种举例还有列存储的应用场景

打车业务系统

  • 司机表，乘客表，业务表，建表写外键完整性约束
  • 简单的查找
  • 视图创建
  • 分组、聚合函数
  • 存储过程统计男生比例女生比例和平均年龄
  • 触发器根据是否租出增减库存数
  • 连锁超市CDM设计（超市，商品，库存，订单，订单索引）
  • 分解范式（运动员，运动项目，裁判，学院，日期，地点，运动员和运动项目多对多，分解成4NF）

期末重点:

SQL编程–难度类似实验测评

关系模型—概念、关系运算

CDM设计–根据题目给出的需求，画出PD格式的CDM图

PDM设计—课件4-4

数据库规范化设计

ODBC—概念、分级、方法

JDBC—概念、分级、编程

存储过程、触发器—难度类似实验

第七章考核点: cap base 5v 四种nosql数据库类型和分类比较

考试题型：分析题，编程题，应用题，设计题

数据库系统基础

  1. 数据库定义：一种依照特定数据模型组织、存储和管理数据的文件集合。这些文件一般存放在外部存储器中，以便长久保存数据，并可快速访问。

  2. 数据库与普通数据文件的主要区别：

    • 数据库可以支持不同应用对数据共享访问，普通数据文件难以支持。
    • 数据库可实现复杂的数据管理，普通数据文件难以实现。
    • 数据库可独立应用程序，普通数据文件与应用程序紧耦合。
    • 数据库的操作访问与控制管理由数据库管理系统软件实现；普通数据文件的操作访问与控制管理，都必须由应用程序实现

  3. DBMS一般具有的功能：

    • 数据定义功能：创建数据库、数据库表以及其它对象
    • 数据操纵功能：读写、修改、删除数据库表中数据
    • 维护数据库结构
    • 执行数据访问规则
    • 提供数据库并发访问控制和安全控制
    • 执行数据库备份和恢复

  4. 数据库系统的构成由数据库、数据库管理系统（创建、操纵和管理数据库的系统软件）、数据库应用系统（在DBMS支持下对数据库进行访问和处理的应用程序）和用户（分为最终用户和DBA用户两种）构成。image.png

  5. 数据模型的构成要素：数据结构（用于描述事物对象的静态特征，包括事物对象的数据组成、数据类型、数据性质等），数据操作（用于描述事物对象的动态特征，包括数据的插入、修改、删除和查询等访问操作），数据约束（用于描述数据结构中数据之间的语义联系*、数据之间的制约和依存关系，数据动态变化的规则，*以及数据取值范围等规则，从而确保数据的完整性、一致性和有效性）注：斜体的内容PPT上有而书上没有

  6. 关系数据模型思想：采用“二维表”结构组织、存储和管理数据，并以关联列实现表之间的联系。

  7. 关系数据模型优点： 数据结构简单、操作灵活；支持关系与集合运算操作；支持广泛使用的SQL数据库操作语言标准；拥有众多的软件厂商产品与用户

  8. 关系数据模型局限： 只用于结构化数据的组织与存储管理；支持的数据类型较简单；难以支持互联网广泛应用的非结构化数据和复杂数据管理

  9. 数据管理技术发展：人工管理——文件系统管理——数据库系统管理

  10. 数据库技术发展经历时代：层次模型数据库技术和网状模型数据库技术——关系模型数据库技术——面向对象数据库技术和对象-关系数据模型数据库技术——本世纪初期出现的半结构化数据库技术，以及当今面向互联网应用的非结构化数据库技术、大规模分布式数据库技术

  11. 数据库应用系统的类型：业务处理系统（商业终端销售系统，航空机票订票系统），管理信息系统（人力资源管理系统，企业客户关系管理系统），决策支持系统（电信营销大数据决策支持系统，证券分析与辅助决策信息系统，法定传染病疫情预测系统）

  12. 数据库应用系统的结构：单用户结构（在单机用户结构系统中，整个数据库应用系统都装在一台计算机上，由一个用户进行访问操作），集中式结构（数据库系统的应用程序、DBMS、数据，都部署在同一服务器上运行，多个用户使用自己的计算机终端网络连接服务器，并可实现共享访问数据库），客户/服务器结构（数据库应用系统的数据集中在数据库服务器管理、应用分布客户机处理。客户端应用程序通过网络并发访问数据库服务器中的数据库），分布式结构（各服务器结点数据库在逻辑上是一个整体，但物理分布在计算机网络的不同服务器结点上运行）

  13. 数据库应用系统生命周期：

    • 需求分析：系统分析人员与用户交流，利用软件工程方法获取系统数据需求信息，并采用需求模型定义系统数据组成，及其数据字典。
    • 系统设计：系统设计人员根据系统功能和性能需求，对系统数据库进行设计，包括系统概念数据模型、系统逻辑数据模型和系统物理数据模型设计。
    • 系统实现：按照系统设计方案进行数据库创建与应用编程实现，主要包括DBMS安装部署、数据库创建、数据对象创建、应用编程实现等方面的工作。
    • 系统测试：系统测试人员将测试数据上载到数据库中，对数据库对象进行测试操作访问，实现数据库功能和性能测试。
    • 系统运行和维护：系统运维人员在信息系统投入运行过程中，对数据库系统进行定期维护和优化，以保证数据库系统正常地、高效地运行。

  14. 讨论题：关系数据库是否适合大数据应用处理？

    结合关系模型的局限作答。存储和性能成本；数据冗余和一致性；处理复杂性。

    • 关系数据库通常使用结构化的数据模型，对于大量非结构化或半结构化数据，这可能变得非常困难和昂贵
    • 关系数据库强调数据一致性和完整性，在大数据环境中，数据的结构和模式可能经常发生变化。这种灵活性要求在关系数据库中频繁地进行模式更改和数据迁移，导致一定的数据冗余和一致性的挑战
    • 大数据应用通常涉及复杂的数据处理操作，如分布式计算、实时处理、图形处理等。关系数据库在这些方面可能缺乏一些先进的功能和技术，无法有效地应对大数据的处理复杂性。
    • 也可以从其它角度作答，如：处理速度慢，关系数据库通常使用SQL查询语言进行数据检索和处理。当数据量很大时，复杂的SQL查询可能需要大量的时间来执行，从而导致性能下降。关系数据库在处理大规模并发请求时也可能面临性能瓶颈。扩展性限制，关系数据库通常基于固定的硬件设备，其扩展性受到硬件资源的限制。当数据量庞大时，关系数据库可能无法有效地扩展以处理大规模的数据。

  15. 讨论题：中国为什么要发展自主可控数据库？应该采取什么样的发展策略？

    发展原因：维护数据主权和安全；提升技术创新和自主研发能力；提升产业竞争力，促进数字经济发展

    策略：加强创新，建立规范体系，人才支持，国际交流合作

数据库关系模型

数据库操作语言SQL

插入：insert into <> values();

更新：update <> set … where…;

删除：delete from <> where…;

数据库设计与实现

  • 实体到关系表的转换：将每一个实体转换成一个关系表，实体属性转换为关系表的列，实体标识符转换为关系表的主键或外键。

  • 弱实体到关系表的转换：为非标识符依赖时，加入强实体标识符作为外键列，为标识符依赖时，不仅加入强实体标识符作为外键列，同时也作为该表的主键列

  • 实体联系的转换：

    • 1:1实体联系：两个实体分别转换为关系表，将任意一个关系表的主键放入另一个关系表做外键。（谁做主键，谁做外键都可以，自主选择）image.pngimage.png
    • 1:N实体联系：两个实体分别转换为关系表，然后将父实体关系表的主键放入子实体关系表中做外键，便可将1:N实体联系转换为关系表参照完整性约束。image.pngimage.png
    • M:N实体联系：M:N实体联系不能像1:1和1:N实体联系那样直接转换。需要创建一个新的关联表，该关联表分别参照原有实体对应的关系表。image.pngimage.png

  • 实体继承的转换：当带有实体继承联系的E-R模型转换关系模型时，首先父实体和子实体都各自转换为表，其属性均转换为表的列。在处理实体继承联系转换时，将父表中的主键放置到子表中，既做主键又做外键。image.pngimage.png

  • 实体递归联系的转换：

    • 1:N实体递归联系”的转换：首先将递归实体转换为表，其属性转换为列，标识符转换为主键，同时还将标识符转换为外键，实现自己对自己的参照。image.pngimage.png
    • M:N实体递归联系的转换：增加一个关联表，该表分别参照原实体对应的关系表，并对原关系表建立两个参照完整性约束。image.pngimage.png

数据库事务管理

  1. 需要数据库管理的原因：

    • 数据库系统随规模增大,系统会变得异常复杂
    • 多用户数据库应用带来数据库访问复杂性
    • 数据安全和数据隐私对机构和用户都非常重要
    • 数据库系统随数据量增加和使用时间增长其性能会降低
    • 系统遭遇意外事件，数据库损坏或数据丢失

  2. 数据库管理的目标：

    • 保障数据库系统正常稳定运行
    • 充分发挥数据库系统的软硬件处理能力
    • 确保数据库系统安全和用户数据隐私性
    • 有效管理数据库用户及其角色权限
    • 解决数据库系统性能优化、系统故障与数据损坏等问题
    • 最大程度地发挥数据库对其所属机构的作用

  3. 数据库管理的内容：DBMS系统运行管理，数据库性能监控，数据库索引管理，数据库查询优化，数据库事务并发控制，数据库角色管理，数据库用户管理，数据库对象权限管理，数据安全管理，数据库备份，数据库恢复

  4. 在数据库中，事务（Transaction）是指由构成单个业务处理单元的一组数据库访问操作，要求它们要么都成功执行，要么都不执行。

  5. 事务ACID特性：

    • 原子性（Atomicity）：事务所有操作在数据库中要么全部执行，要么全部不执行。
    • 一致性（Consistency）：事务多次执行，其结果应一致。
    • 隔离性（Isolation）：事务与事务之间隔离，并发执行透明。
    • 持续性（Durability ）：事务完成后，数据改变必须是永久的。

  6. 事务并发执行原因：改善系统的资源利用率，减少事务运行的平均等待时间

  7. 事务SQL语句

    START TRANSACTION；事务开始语句

    ROLLBACK；事务回滚语句

    COMMIT；事务提交语句

    SAVEPOINT；事务保存点语句

    START TRANSACTION; INSERT INTO college( collegeID, collegename)VALUES ('004', '外语学院'); INSERT INTO college( collegeID, collegename)VALUES ('005', '数学学院'); INSERT INTO college( collegeID, collegename)VALUES ('006', '临床医学院'); COMMIT;

  8. 事务中不能使用的SQL语句：创建、修改、删除、恢复、加载数据库，备份日志文件，恢复日志文件，授权操作

  9. 并发控制目的：

    • 支持并发事务处理，使更多用户并行操作，提高系统的并发访问能力。
    • 保证一个事务工作不会对另一个事务工作产生不合理的影响。

  10. 并发控制需要解决的问题：前三个其实是同类问题

    • 丢失更新数据：一个事务对一共享数据进行更新处理，但是以后查询该共享数据值时，发现该数据与自己的更新值不一致。（T1、T2两个事务并发执行，它们均对数据库共享数据A进行了非锁定资源的读写操作。当事务T1和T2均读入该共享数据A并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失）
    • 不可重复读取：一个事务对一个共享数据重复多次读取，但前后读取的数据不一致。（事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时，得到与前一次不同的值）
    • 幻象读：一个事务对一个共享数据重复读取两次，但发现第二次读取比第一次读取的结果新增了一些数据。（事务T1按一定条件从数据库中读取某些数据记录后，事务T2在其中插入了一些记录，当T1再次按相同条件读取数据时，发现多了一些记录。称为幻象读取）
    • 脏数据读取是指一个事务读取了被取消持久化的共享数据

  11. 并发事务调度就是控制多个事务的数据操作语句按照恰当的顺序访问共享数据，使这些事务执行之后，避免造成数据的不一致性，即解决“丢失更新数据”、“不可重复读”、“脏数据读”等问题。

  12. 可串行化调度：在事务并发执行中，只有当事务中数据操作调度顺序的执行结果与事务串行执行结果一样时，该并发事务调度才能保证数据操作的正确性和一致性。符合这样效果的调度称为可串行化调度。

  13. DBMS并发事务调度目标：使并发事务调度实现的处理结果与串行化调度处理结果一致。

  14. 在DBMS中，通过加入锁表机制，来实现共享数据锁定访问，其加锁方式包含如下类型。

    • 排它锁定(Lock-X)——锁定后，不允许其它事务对共享数据再加锁

    • 共享锁定(Lock-S)——锁定后，只允许其它事务对共享数据添加读取锁

      排它锁 共享锁 无锁
      排它锁 否 否 是
      共享锁 否 是 是
      无锁 是 是 是

  15. 一级加锁协议：任何事务在修改共享数据对象之前，必须对该数据执行排它锁定指令，直到该事务处理完成，才进行解锁指令执行。使用一级加锁协议，可避免出现更新丢失问题。但不能解决“不可重复读取”、“脏读”等数据不一致问题。

  16. 二级加锁协议：在一级加锁协议基础上，针对并发事务的共享数据读操作，必须对该数据执行共享锁定指令，读完数据后即刻释放共享锁定。该加锁协议不但可以防止“丢失更新”的数据不一致性问题，还可防止出现脏读数据问题。但有可能会出现“不可重复读取”的数据不一致问题。

  17. 三级加锁协议：在一级加锁协议基础上，针对并发事务对共享数据进行读操作，必须对该数据执行共享锁定指令，直到该事务处理结束才释放共享锁定。该加锁协议不但可以防止“丢失更新”、“脏读”的数据不一致性问题，还可防止出现“不可重复读取”的数据一致性问题。

    加锁协议级别 排它锁 共享锁 不丢失更新 不脏读 可重复读
    一级 全程加锁 不加 是 否 否
    二级 全程加锁 开始读数据时加锁，读完数据释放锁定 是 是 否
    三级 全程加锁 全程加锁 是 是 是

  18. 并发事务的正确调度准则：一个给定的并发事务调度，当且仅当它是可串行化时，才能保证正确调度。保证可串行化的一个协议是：二阶段锁定协议

  19. 二阶段锁定协议规定每个事务必须分两个阶段提出加锁和解锁申请:增长阶段，事务只能获得锁，但不能释放锁。缩减阶段，事务只能释放锁，但不能获得新锁。

  20. 事务死锁状态：在基于锁机制的并发事务执行中，如果这些事务同时锁定两个以及以上资源时，可能会出现彼此都不能继续执行的状态，即事务死锁状态。

  21. 死锁出现的必要条件：互斥条件，请求和保持条件，不剥夺条件，环路等待条件

  22. 防范死锁的策略：

    • 允许用户一次发出当前所需全部资源的锁定，使用完成后，再释放给其它用户访问。
    • 规定所有应用程序锁定资源的顺序必须完全相同。

  23. 解决死锁的方法：当发生死锁时，回滚其中的一个事务，并取消它对数据库所做的改动。

数据库安全管理

  1. 数据库面临的安全问题：

    • 黑客利用系统漏洞，攻击系统运行、窃取和篡改系统数据。
    • 内部人员非法地泄露、篡改、删除系统的用户数据。
    • 系统运维人员操作失误导致数据被删除或数据库服务器系统宕机。
    • 系统故障导致数据库的数据损坏、数据丢失、数据库实例无法启动。
    • 意外灾害事件（火灾、水灾、地震等自然灾害）导致系统被破坏。

  2. 数据库系统安全模型：用户身份鉴别，用户存取权限控制，操作系统安全保护，数据加密存储image.png

    身份验证：从应用系统层面确认登录用户是否是合法使用者

    权限控制：从DBMS系统层面通过存取权限机制控制用户对数据的访问

    系统防护：从操作系统层面提供的安全机制防范非法系统访问

    加密存储：从数据存储层面通过加密算法对数据库中数据进行加密存储

  3. 数据库存取安全模型image.png

  4. 用户创建：CREATE USER <用户账号名> [ [WITH] option […]]; 书上加了WITH。

  5. 备选项

    SUPERUSER | NOSUPERUSER CREATEDB | NOCREATEDB CREATEROLE | NOCREATEROLE INHERIT | NOINHERIT LOGIN | NOLOGIN REPLICATION | NOREPLICATION BYPASSRLS | NOBYPASSRLS CONNECTION LIMIT connlimit [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL VALID UNTIL 'timestamp' IN ROLE role_name [, ...]

  6. 用户修改、删除SQL语句

    -- 修改用户“userA”的账号密码为“gres123”。同时也限制该用户的数据库连接数为10。 alter user "userA" connection limit 10 password 'gres123'; --其他 alter user <用户名> [ [ with ] option [ ... ] ]; --修改用户的属性 alter user <用户名> rename to <新用户名>; --修改用户的名称 alter user <用户名> set <参数项> { to | = } { value | default }; --修改用户的参数值 alter user <用户名> reset <参数项>; --重置用户参数值 --用户的删除 drop user "userA"

  7. 权限管理SQL语句：
    grant <权限名> on <对象名> to {数据库用户名|用户角色名}；
    revoke <权限名> on <对象名> from {数据库用户名|用户角色名}；
    deny <权限名> on <对象名> to {数据库用户名|用户角色名}；

    -- 对用户“userA”实现授权SQL程序如下 grant select on department to usera; grant select on employee to usera; grant select on project to usera; grant select on assignment to usera;

    grant 是 on后面加表名，to后面加用户/角色名

  8. 数据库角色管理：和用户基本没有区别

    create role <角色名> [ [ with ] option [ ... ] ]; --创建角色 alter role <角色名> [ [ with ] option [ ... ] ]; --修改角色属性 alter role <角色名> rename to <新角色名>; --修改角色名称 alter role <角色名> set <参数项> { to | = } { value | default };--修改角色参数值 alter role <角色名> reset <参数项>; --复位角色参数值 drop role <角色名>; --删除指定角色 --在工程项目管理系统中，假定需要在ProjectDB数据库内创建经理角色Role_Manager。该角色具有登录权限（Login）和角色继承权限（Inherit）系统权限，但它不是超级用户（SuperUser），不具有创建数据库权限（CreateDB）、创建角色权限（CreateRole）、数据库复制权限（Replication），此外数据库连接数（Connection Limit）不受限。 create role "role_manager" with login nosuperuser nocreatedb nocreaterole inherit noreplication connection limit -1; --在创建好经理角色Role_Manager后，还需要赋予该角色对数据库表Department、Employee、Project、Assignment的插入、修改、删除、查询权限。 grant select,insert,update,delete on department to "role_manager"; grant select,insert,update,delete on employee to "role_manager"; grant select,insert,update,delete on project to "role_manager"; grant select,insert,update,delete on assignment to "role_manager";

  9. 数据库系统故障原因：数据库服务器硬件故障，系统软件故障，用户误操作，系统意外断电

  10. 数据库备份——是指将数据库当前数据和状态进行副本复制，以便当数据库受到破坏或丢失数据时可以进行修复。

  11. 数据库恢复——是指数据库中数据丢失或被破坏时，从备份副本将数据库从错误状态恢复到某一正确状态。

  12. 备份SAMPLE数据库到一个G磁盘的根目录文件Sample.bak中。

    BACKUP DATABASE SAMPLE TO DISK = ‘G:\Sample.bak';

  13. 从存储备份文件中恢复SAMPLE数据库。

    RESTORE DATABASE SAMPLE FROM DISK = ‘G:\Sample.bak';

数据库应用编程

  1. ODBC简介：ODBC全称为开放式数据库互连（Open DataBase Connectivity）技术，ODBC实现了应用程序对多种不同DBMS的数据库的访问，实现了数据库连接方式的变革。

    ODBC定义了一套基于SQL的、公共的、与数据库无关的API（应用程序设计接口）；使每个应用程序利用相同的源代码就可访问不同的数据库系统，存取多个数据库中的数据；从而使得应用程序与数据库管理系统（DBMS）之间在逻辑上的独立性，使应用程序具有数据库无关性。image.png

  2. 应用程序使用ODBC访问数据库的步骤：

    • 首先必须用ODBC管理器注册一个数据源
    • 管理器根据数据源提供的数据库位置、数据库类型及ODBC驱动程序等信息，建立起ODBC与具体数据库的联系
    • 应用程序只需将数据源名提供给ODBC，ODBC就能建立起与相应数据库的连接
    • 这样，应用程序就可以通过驱动程序管理器与数据库交换信息
    • 驱动程序管理器负责将应用程序对ODBC API的调用传递给正确的驱动程序
    • 驱动程序在执行完相应的SQL操作后，将结果通过驱动程序管理器返回给应用程序

  3. JDBC全称为Java DataBase Connectivity（Java数据库连接）技术 ，是一种用于执行SQL语句的Java API。它由一组用Java编程语言编写的类和接口组成。这个API由java.sql.*包中的一些类和接口组成，它为数据库开发人员提供了一个标准的API，使他们能够用纯Java API 来编写数据库应用程序。image.png

  4. 对比ODBC和JDBC，可以从平台依赖型，编程语言和数据库无关性三个角度的对比。

  5. JDBC访问数据库步骤：应用程序开始——导入java.sql包——加载并注册驱动程序——创建Connection对象——创建Statement对象——执行SQL语句——使用ResultSet对象返回结果——关闭ResultSet对象——关闭Statement对象——关闭Connection对象——结束

  6. JDBC技术：

    加载驱动：Class.forName("org.postgresql.Driver")；

    建立连接：数据库建立连接的方法是调用Connection conn = DriverManager.getConnection(URL,userName,passWord);方法

    创建Statement对象：Statement stmt = conn.createStatement();

    执行SQL语句：主要掌握两种执行SQL语句的方法：executeQuery()、executeUpdate()

    Statement stmt = conn.createStatement(); String sql = "INSERT INTO public.student (sid, sname, gender, birthday, major, phone)" + " VALUES ('2017001', '张山', '男', '1998-10-10','软件工程','13602810001')"; stmt.executeUpdate(sql);

    ResultSet保存结果集：ResultSet里的数据一行一行排列，每行有多个字段，并且有一个记录指针，指针所指的数据行叫做当前数据行，我们只能来操作当前的数据行。我们如果想要取得某一条记录，就要使用ResultSet的next()方法 ,如果我们想要得到ResultSet里的所有记录，就应该使用while循环。

    import java.sql.*; public class JDBCDemo { public static void main(String[] args) { Connection conn = null; Statement stmt = null; String URL = "jdbc:postgresql://localhost:5432/booksale"; String userName = "postgres"; String password = "123456"; try { Class.forName("org.postgresql.Driver"); conn = DriverManager.getConnection(URL, userName, password); String sql = "select * from book"; stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { String bookISBN = rs.getString("book_isbn"); String bookName = rs.getString("book_name"); System.out.println(bookISBN + " " + bookName); } conn.close(); } catch (Exception e) { System.out.println(e.getMessage()); } } }

    关闭连接：close() 方法

  7. Connection.prepareStatement(String sql)创建PreparedStatement对象，实现动态SQL语句查询，prepareStatement可以使用?替换变量

  8. preparedStatement预编译SQL语句，并支持批处理，执行查询有类似Statement对象的三种执行方式，且执行方法中没有参数。

    • prepareStatement.executeUpdate()执行更新；

    • prepareStatement对象使用addBatch()向批处理中加入更新语句

    • executeBatch()方法用于成批地执行SQL语句。

      String insertSql = "INSERT INTO stu_score(sid, cid, score) VALUES (?,?,?)"; String querySql = "select sid, cid, score from stu_score where score>=?"; PreparedStatement psInsert = conn.prepareStatement(insertSql);  PreparedStatement psQuery = conn.prepareStatement(querySql）;  for (int i=0; i<sid.length; i++) { psInsert.setString(1, sid[i]); psInsert.setString(2, cid[i]); psInsert.setInt(3, score[i]); psInsert.addBatch();  } psInsert.executeBatch(); psQuery.setInt(1, 80); ResultSet rs = psQuery.executeQuery(); while (rs.next()) {  System.out.println(rs.getString("sid") + " " + rs.getString("cid") + " " + rs.getInt("score") ); } psQuery.close(); psInsert.close(); conn.close();

  9. PostgreSQL使用CREATE FUNCTION命令创建函数或存储过程。

    CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) [ RETURNS retype | RETURNS TABLE ( column_name column_type [, ...] ) ] AS $$ --$$用于声明存储过程的实际代码的开始 DECLARE -- 声明段 BEGIN --函数体语句 END; $$ LANGUAGE lang_name; --$$ 表明代码的结束, LANGUAGE后面指明所用的编程语言

    加分号的位置要注意：声明段，函数体，END，language plpgsql

    --创建一个名为countRecords()的存储过程统计STUDENT表的记录数。 create or replace function countRecords () returns integer as $count$ declare count integer; begin select count(*) into count from student; return count; end; $count$ language plpgsql; --执行存储过程 select * from countRecords (); --删除存储过程 drop function if exists countRecords ();

    特别的，在 PostgreSQL 中，当函数定义了输出参数时，不能使用 RETURN 语句来返回结果。相反，应该在函数体内使用 OUT 参数来指定输出结果。

    create or replace function Pro_CurrentSale(out number numeric, out allmoney money) as $count$ begin select sum(Sale_Number) into number from Sale where Sale_Date = '2023-04-21'; select sum(Sale_Amount) into allmoney from Sale where Sale_Date = '2023-04-21'; end; $count$ LANGUAGE plpgsql;

    plpgsql语法：

    --声明局部变量 declare count intger； rec RECORD；--RECORD不是真正的数据类型，只是一个占位符。 --条件语句 IF boolean-expression THEN statements ELSIF boolean-expression THEN statements ELSIF boolean-expression THEN statements ELSE statements END IF; --循环语句1 LOOP count=count+1; EXIT WHEN count > 100; CONTINUE WHEN count < 50; count=count+1; END LOOP; --循环语句2 WHILE amount_owed > 0 AND balance > 0 LOOP --do something END LOOP; --循环语句3 FOR i IN 1...10 LOOP RAISE NOTICE 'i IS %', i; END LOOP; --遍历命令结果 declare rec RECORD ； FOR rec IN SELECT sid,sname FROM student LOOP raise notice ‘%-,%-’,rec.sid, rec.sname; END LOOP;

  10. 触发器是特殊类型的存储过程，主要由操作事件(INSERT、UPDATE、DELETE) 触发而被自动执行。

CREATE TRIGGER 触发器名 { BEFORE | AFTER | INSTEAD OF } ON 表名 [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE 存储过程名 ( 参数列表 )

创建触发器被触发时所要执行的触发器函数，该函数的类型必须是TRINGER型，是触发器的执行函数。

触发器相关的特殊变量：

 1. NEW ：数据类型是RECORD。对于行级触发器，它存有INSERT或UPDATE操作产生的新的数据行。对于语句级触发器，它的值是NULL。 2. OLD ：数据类型是RECORD。对于行级触发器，它存有被UPDATE或DELETE操作修改或删除的旧的数据行。对于语句级触发器，它的值是NULL。 3. TG_OP：数据类型是text；是值为INSERT、UPDATE、DELETE 的一个字符串，它说明触发器是为哪个操作引发。 
--为了防止非法修改stu_score表的课程成绩，创建audit_score表记录stu_score表的成绩变化，创建触发器函数score_audit() CREATE OR REPLACE FUNCTION score_audit() RETURNS TRIGGER AS $score_audit$ BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO audit_score SELECT user, old.sid, old.cid, now(), old.score; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO audit_score SELECT user, old.sid, old.cid, now(), old.score, new.score WHERE old.sid = new.sid AND old.cid = new.cid; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO audit_score SELECT user, new.sid, new.cid, now(), null, new.score; RETURN NEW; END IF; RETURN NULL; END; $score_audit$ LANGUAGE plpgsql; --接下来在stu_score表上创建触发器score_audit_triger CREATE TRIGGER score_audit_triger AFTER INSERT OR UPDATE OR DELETE ON stu_score FOR EACH ROW EXECUTE PROCEDURE score_audit();
  • SQL本身是一种结构化查询语言，而存储过程本质上是过程化的程序；面对复杂的业务逻辑，过程化处理逻辑相对比较复杂；而SQL语言的优势是面向数据查询而非业务逻辑的处理。
  • 如果存储过程的参数或返回数据发生变化，一般需要修改存储过程的代码，同时还需要更新主程序调用存储过程的代码。
  • 开发调试复杂，由于缺乏支持存储过程的集成开发环境，存储过程的开发调试要比一般程序困难。
  • 可移植性差

NoSQL数据库

原创文章，作者：优速盾-小U，如若转载，请注明出处：https://www.cdnb.net/bbs/archives/30897

赞 (0)
优速盾-小U的头像优速盾-小U
0 0
上一篇 2025年6月18日 18:42
下一篇 2025年6月18日 23:04

相关推荐

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

优速盾注册领取大礼包www.cdnb.net
/sitemap.xml