数据库复习

数据库系统与数据仓库复习

1、什么是存储过程及其优缺点？

存储过程（Stored Procedure）是一组预编译的SQL语句，存储在数据库服务器上，可以通过调用来执行。存储过程可以包含查询、插入、更新、删除等操作，并且支持条件控制、循环等编程逻辑。存储过程可以用来封装复杂的业务逻辑，简化数据库操作，提高性能和安全性。

存储过程的优点

1. 性能提高：

   • 预编译：存储过程在首次执行时会被编译并生成执行计划，之后的调用可以直接使用该执行计划，减少了解析和编译的开销。
   • 减少网络流量：存储过程在服务器端执行，可以减少客户端和服务器之间的数据传输量，因为只需传递调用命令而不是大量的SQL语句。

2. 安全性增强：

   • 权限控制：通过存储过程可以限制对底层表的直接访问，只允许通过存储过程访问数据，从而提高数据的安全性。
   • 避免SQL注入：存储过程参数化查询可以有效防止SQL注入攻击，因为SQL语句和数据是分开的。

3. 简化代码维护：

   • 封装业务逻辑：存储过程可以将复杂的业务逻辑封装在数据库中，减少应用程序的复杂性。
   • 集中管理：所有与数据库相关的业务逻辑集中在数据库中，方便维护和更新。

4. 复用性：

   • 多次使用：存储过程可以被多个应用程序或不同部分的代码重复调用，提高代码复用性。

存储过程的缺点

1. 可移植性差：

   • 数据库依赖：存储过程通常依赖于特定的数据库管理系统（DBMS），不同的DBMS有不同的语法和功能，迁移存储过程可能需要大量的修改工作。

2. 调试和测试困难：

   • 缺乏调试工具：虽然一些现代的数据库系统提供了调试工具，但调试存储过程通常不如调试应用程序代码方便。
   • 复杂性增加：复杂的存储过程可能难以测试和维护，特别是当涉及到大量的条件逻辑和嵌套调用时。

3. 性能瓶颈：

   • 资源占用：存储过程在服务器端执行，如果设计不当，可能导致服务器资源的过度占用，影响整体性能。
   • 难以扩展：对于一些高并发应用，存储过程可能成为性能瓶颈，因为数据库服务器需要处理更多的逻辑运算。

4. 开发成本：

   • 开发复杂度：编写和维护复杂的存储过程需要较高的专业技能，增加了开发成本。

综上所述，存储过程在提高性能、安全性和代码维护性方面有显著优势，但同时也带来了可移植性差、调试和测试困难等问题。在实际应用中，需要根据具体的需求和环境权衡使用存储过程的优缺点。

2、什么叫视图？视图是否都可以更新？为什么？游标是什么？

视图（View）

视图是一个虚拟表，它是基于SQL查询的结果集，可以像表一样被查询和使用。视图本身不存储数据，它只是对底层表的一个查询结果的定义。视图可以包含表的子集或跨多个表的联接结果。

视图的优点

1. 简化复杂查询：将复杂的查询逻辑封装在视图中，简化应用程序的查询操作。
2. 数据安全：通过视图可以限制用户访问表中的特定列或行，从而保护敏感数据。
3. 数据一致性：视图可以确保应用程序访问的一致性数据，减少数据冗余和维护工作。
4. 逻辑独立性：可以在不影响应用程序的情况下更改底层表结构，只需调整视图定义。

视图是否都可以更新？

不是所有的视图都可以更新。视图是否可以更新取决于它们的定义和数据库管理系统的规则。一般来说，可更新视图需要满足以下条件：

1. 单表视图：视图基于单个表创建，且包含的列可以唯一标识每一行。
2. 没有聚合函数：视图中不包含聚合函数（如SUM、AVG等）。
3. 没有DISTINCT关键字：视图定义中不使用DISTINCT关键字。
4. 没有GROUP BY或HAVING子句：视图中不包含GROUP BY或HAVING子句。
5. 没有联合（UNION）：视图中不使用UNION、UNION ALL操作。
6. 没有子查询：视图中不包含复杂的子查询或派生表。
7. 包含主键：视图应包含基础表的主键，以确保每一行的唯一性。

一些数据库管理系统可能允许部分不符合上述条件的视图进行更新，但需要通过触发器或其他机制来处理这些更新。

游标（Cursor）

游标是数据库管理系统中的一个数据库对象，用于逐行处理查询结果集。游标提供了一种从结果集中按行检索数据的机制，适用于需要逐行处理数据的情况，如数据迁移、批量操作等。

游标的类型

1. 静态游标（Static Cursor）：结果集在打开游标时生成，是一个静态的快照，之后对基础表的更改不会反映在游标中。
2. 动态游标（Dynamic Cursor）：结果集是动态的，游标打开后对基础表的更改会立即反映在游标中。
3. 前向游标（Forward-Only Cursor）：只能从第一行到最后一行顺序读取，不支持向后或随机访问。
4. 滚动游标（Scrollable Cursor）：支持向前、向后或随机访问结果集中的任何行。

游标的使用步骤

1. 声明游标：定义游标，并指定要检索的数据。

   DECLARE cursor_name CURSOR FOR SELECT_statement;
   

2. 打开游标：初始化游标，准备从结果集中检索数据。

   OPEN cursor_name;
   

3. 提取数据：从游标中按行检索数据，通常使用FETCH语句。

   FETCH NEXT FROM cursor_name INTO variables;
   

4. 关闭游标：关闭游标，释放相关资源。

   CLOSE cursor_name;
   

5. 释放游标：删除游标定义。

   DEALLOCATE cursor_name;
   

总结

视图是一个虚拟表，可以简化查询操作和提高数据安全性，但并非所有视图都可更新。游标用于逐行处理查询结果集，适用于需要按行处理数据的操作。了解视图和游标的概念和使用方法，有助于优化数据库操作和管理复杂的查询逻辑。

3、数据库系统中的常见故障

数据库系统中的常见故障主要可以分为以下几类：

1. 硬件故障

• 磁盘故障：硬盘损坏可能导致数据丢失或损坏，尤其是当没有进行适当备份时。
• 电源故障：电源中断或波动可能导致数据库服务器突然关闭，可能会导致数据丢失或数据库损坏。
• 网络故障：网络连接问题会影响数据库的可访问性，导致客户端无法连接到数据库服务器。

2. 软件故障

• 数据库管理系统（DBMS）崩溃：数据库软件本身的错误或bug可能导致数据库崩溃或异常行为。
• 操作系统故障：操作系统的崩溃或异常可能影响数据库的正常运行。
• 应用程序错误：应用程序代码中的错误可能导致不正确的数据库操作，如数据插入错误、死锁等。

3. 人为错误

• 误操作：管理员或用户的误操作，如误删除表、错误更新数据等，可能导致数据丢失或损坏。
• 恶意操作：恶意攻击者可能通过SQL注入、DDOS攻击等方式破坏数据库或窃取数据。

4. 数据一致性和完整性问题

• 并发控制问题：在多用户并发操作下，可能发生死锁、幻读、脏读等并发控制问题，导致数据一致性问题。
• 数据完整性约束违反：违反主键、外键、唯一约束等数据完整性约束，可能导致数据不一致或无效。

5. 存储故障

• 日志文件损坏：事务日志文件损坏可能导致数据库恢复困难。
• 备份失败或恢复失败：备份过程中的错误或备份文件损坏，导致无法进行数据恢复。

6. 安全性问题

• 未经授权的访问：未授权的用户访问数据库，可能导致数据泄露或损坏。
• 权限设置错误：错误的权限设置可能导致不应有的访问权限，增加数据被误操作或泄露的风险。

7. 性能问题

• 资源耗尽：CPU、内存、磁盘I/O等资源耗尽，可能导致数据库响应缓慢或无法响应。
• 索引问题：缺乏适当的索引或索引过多，可能导致查询性能下降。
• 锁竞争：大量的锁竞争可能导致系统性能下降，甚至死锁。

预防和解决方案

1. 硬件冗余：使用RAID、双电源等冗余措施，减少硬件故障的影响。
2. 定期备份：定期进行全量和增量备份，确保数据可以在故障后恢复。
3. 性能监控：使用监控工具实时监控数据库性能，及时发现和解决性能瓶颈。
4. 并发控制：采用适当的锁机制和事务隔离级别，解决并发控制问题。
5. 权限管理：严格管理数据库权限，防止未经授权的访问。
6. 数据验证：使用约束、触发器等机制，确保数据的一致性和完整性。
7. 灾难恢复计划：制定并定期演练数据库灾难恢复计划，确保在发生故障时能够快速恢复系统。

了解这些常见故障和相应的预防措施，可以帮助数据库管理员更好地保障数据库系统的稳定性和可靠性。

4、什么样的并发调度是正确的调度？

在数据库系统中，并发调度是指在多用户环境中，数据库管理系统（DBMS）如何安排事务（transactions）的执行顺序，以确保数据的一致性和系统的性能。一个正确的并发调度应该满足以下几个关键特性：

1. 隔离性（Isolation）

隔离性确保了事务之间的操作互不干扰。即每个事务在执行期间都感觉自己是数据库系统中唯一的事务。隔离级别决定了事务看到其他事务的中间状态的程度，常见的隔离级别包括：

• 读未提交（Read Uncommitted）：允许脏读，最低的隔离级别。
• 读已提交（Read Committed）：避免脏读，常用隔离级别。
• 可重复读（Repeatable Read）：避免不可重复读，但可能出现幻读。
• 可串行化（Serializable）：最高的隔离级别，完全避免并发问题。

2. 一致性（Consistency）

一致性确保事务从一个一致状态转移到另一个一致状态。即在事务开始和结束时，数据库的完整性约束必须保持。

3. 持久性（Durability）

持久性确保事务一旦提交，其对数据库的修改将永久保留，即使系统发生崩溃。

4. 原子性（Atomicity）

原子性确保事务的所有操作要么全部完成，要么全部不完成。事务不能只完成一部分。

5. 调度的正确性

一个正确的并发调度应满足可串行化性（Serializability），这是并发调度的黄金标准。可串行化性确保并发执行的事务结果与某种顺序串行执行这些事务的结果相同。常用的实现可串行化调度的方法包括：

• 两段锁协议（Two-Phase Locking, 2PL）：确保事务的所有锁请求在释放任何锁之前完成，这可以防止死锁并确保可串行化。
• 时间戳排序（Timestamp Ordering）：基于事务的开始时间戳，按时间戳顺序执行事务，以确保并发调度的正确性。
• 多版本并发控制（Multiversion Concurrency Control, MVCC）：通过维护数据的多个版本，允许读操作并发执行，避免读写冲突。

6. 避免并发问题

一个正确的并发调度应避免以下常见的并发问题：

• 脏读（Dirty Read）：一个事务读取到另一个未提交事务修改的数据。
• 不可重复读（Non-Repeatable Read）：一个事务在其执行过程中，两次读取同一数据得到了不同的结果。
• 幻读（Phantom Read）：一个事务在两次查询期间，看到另一事务插入或删除了数据行。

7. 实现机制

• 锁机制：通过加锁来控制事务对数据的并发访问。

  • 排它锁（Exclusive Lock, X锁）：一个事务持有排它锁时，其他事务不能读写该数据。
  • 共享锁（Shared Lock, S锁）：多个事务可以同时持有共享锁，用于读操作。

• 封锁协议：例如严格两阶段封锁（Strict Two-Phase Locking, S2PL）协议，确保事务在释放所有锁之前不提交。

• 悲观并发控制：假设冲突会频繁发生，通过加锁预防冲突。

• 乐观并发控制：假设冲突不频繁，通过在提交阶段检测冲突并解决。

总结

一个正确的并发调度应确保事务的隔离性、一致性、持久性和原子性，同时实现可串行化性，避免并发问题。常见的实现方法包括两段锁协议、时间戳排序、多版本并发控制等。通过使用这些方法和机制，数据库管理系统可以有效管理并发事务，确保数据的正确性和系统的高性能。

5、数据库恢复的基本技术有哪些？

数据库恢复的基本技术旨在确保在系统故障、硬件故障或其他异常情况下，数据库可以恢复到一致性状态。这些技术包括备份与恢复、日志管理、检查点等。以下是数据库恢复的基本技术：

1. 备份（Backup）

全量备份（Full Backup）

• 定义：完整备份整个数据库的当前状态，包括所有数据和日志文件。
• 优点：恢复时只需一份备份，操作简单。
• 缺点：占用存储空间大，备份时间长。

增量备份（Incremental Backup）

• 定义：备份自上次备份（全量或增量）以来发生变化的数据。
• 优点：占用存储空间较小，备份速度较快。
• 缺点：恢复时需要应用所有增量备份，恢复过程较复杂。

差异备份（Differential Backup）

• 定义：备份自上次全量备份以来发生变化的数据。
• 优点：恢复时只需上次全量备份和最近一次差异备份，恢复速度较快。
• 缺点：随着时间推移，差异备份的大小会逐渐增加。

2. 日志管理（Logging）

事务日志（Transaction Log）

• 定义：记录所有对数据库进行的修改操作（插入、更新、删除），以及事务的开始和结束。
• 作用：用于在故障后进行数据恢复，将数据库恢复到一致状态。
• 恢复过程：
  • 前滚（Redo）：将提交的事务重新应用到数据库中，以恢复数据到故障前的状态。
  • 回滚（Undo）：撤销未提交的事务，确保数据一致性。

3. 检查点（Checkpoint）

定期检查点（Periodic Checkpoint）

• 定义：定期将内存中的数据页和日志信息写入磁盘，形成一致性点。
• 优点：减少恢复时间，因为只需从最近的检查点开始恢复。
• 实现方式：在检查点时，将所有脏页（修改但未写入磁盘的数据页）写入磁盘，并更新日志记录。

4. 并行恢复（Parallel Recovery）

• 定义：利用多个处理器或线程同时执行恢复操作，以加速恢复过程。
• 优点：提高恢复速度，缩短系统不可用时间。

5. 镜像（Mirroring）

• 定义：将数据库实时复制到一个或多个镜像服务器上，以实现高可用性和快速故障恢复。
• 优点：故障发生时，可以快速切换到镜像服务器，减少停机时间。
• 实现方式：常见技术包括同步和异步复制。

6. 影子分页（Shadow Paging）

• 定义：通过维护两个页面映射表（当前页面表和影子页面表）来确保事务的原子性和一致性。
• 优点：在发生故障时，可以使用影子页面表恢复数据，无需日志重做和回滚。
• 缺点：实现复杂，对存储空间要求较高。

7. 日志结构化存储（Log-Structured Storage）

• 定义：将所有数据库修改记录在日志中，数据存储以日志结构化形式组织。
• 优点：提高写性能和故障恢复能力。
• 缺点：复杂的日志管理和垃圾回收机制。

恢复策略的实施

• 定期备份：根据数据的重要性和变化频率，制定备份计划，定期进行全量、增量或差异备份。
• 日志管理：确保事务日志的完整性和安全性，定期检查日志文件，防止损坏。
• 检查点设置：根据系统负载和恢复需求，合理设置检查点频率，平衡性能和恢复时间。
• 镜像和复制：对于关键业务，采用数据库镜像或数据复制技术，确保高可用性和快速恢复。
• 定期演练：定期进行数据恢复演练，确保在真实故障发生时能够快速、准确地进行数据恢复。

通过以上技术和策略，数据库管理员可以有效保障数据的安全性和一致性，确保在发生故障时能够快速恢复系统，减少数据丢失和业务中断的风险。

6、用SQL语句创建表，并定义相应约束

在SQL中创建表并定义相应约束是一项常见的任务。下面的示例展示了如何使用SQL语句创建一个表并定义各种约束。

假设我们要创建一个名为employees的表，该表包含以下列和约束：

1. 员工ID（employee_id）：主键，不允许为空。
2. 姓名（name）：不允许为空。
3. 年龄（age）：必须大于等于18。
4. 电子邮件（email）：唯一。
5. 部门ID（department_id）：外键，引用departments表中的department_id。
6. 雇佣日期（hire_date）：默认为当前日期。

首先，我们假设已经有一个departments表，它的创建语句如下：

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

接下来，我们创建employees表，并添加相应的约束：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,  -- 主键约束
    name VARCHAR(100) NOT NULL,   -- 非空约束
    age INT CHECK (age >= 18),    -- 检查约束
    email VARCHAR(100) UNIQUE,    -- 唯一约束
    department_id INT,            -- 外键列
    hire_date DATE DEFAULT CURRENT_DATE,  -- 默认值约束
    FOREIGN KEY (department_id) REFERENCES departments(department_id)  -- 外键约束
);

详细解释：

• 主键约束：employee_id INT PRIMARY KEY

  • PRIMARY KEY约束确保employee_id列中的值唯一且不为空。

• 非空约束：name VARCHAR(100) NOT NULL

  • NOT NULL约束确保name列中不能有空值。

• 检查约束：age INT CHECK (age >= 18)

  • CHECK约束确保age列中的值必须大于等于18。

• 唯一约束：email VARCHAR(100) UNIQUE

  • UNIQUE约束确保email列中的值唯一。

• 默认值约束：hire_date DATE DEFAULT CURRENT_DATE

  • DEFAULT约束为hire_date列提供默认值，这里设置为当前日期。

• 外键约束：FOREIGN KEY (department_id) REFERENCES departments(department_id)

  • FOREIGN KEY约束确保department_id列中的值必须存在于departments表的department_id列中。

使用ALTER TABLE添加约束

有时，可能需要在创建表后添加约束。这可以通过ALTER TABLE语句实现。例如：

-- 添加主键约束
ALTER TABLE employees ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);

-- 添加非空约束
ALTER TABLE employees MODIFY name VARCHAR(100) NOT NULL;

-- 添加检查约束
ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);

-- 添加唯一约束
ALTER TABLE employees ADD CONSTRAINT uq_email UNIQUE (email);

-- 添加外键约束
ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id);

通过上述SQL语句，我们能够创建表并定义各种约束，以确保数据的完整性和一致性。

7、数据库管理系统的主要功能有哪些？

数据库管理系统（DBMS）的主要功能包括数据存储、数据管理、数据安全、数据恢复、并发控制和数据完整性。以下是DBMS的主要功能详解：

1. 数据存储和检索

• 数据定义：提供数据定义语言（DDL），用于定义数据库的模式，包括创建、修改和删除数据库对象（如表、索引、视图等）。
  • 示例：CREATE TABLE, ALTER TABLE, DROP TABLE
• 数据操作：提供数据操作语言（DML），用于插入、更新、删除和查询数据。
  • 示例：INSERT, UPDATE, DELETE, SELECT

2. 数据管理

• 索引管理：创建和管理索引以加速数据检索。
  • 示例：CREATE INDEX, DROP INDEX
• 存储管理：管理物理存储结构，优化存储利用率和访问速度。
• 数据归档：定期备份和归档数据，确保数据的长期可用性。

3. 数据安全

• 用户管理：创建和管理用户账户，控制用户访问权限。
  • 示例：CREATE USER, ALTER USER, DROP USER
• 访问控制：通过授予和撤销权限，控制用户对数据库对象的访问。
  • 示例：GRANT, REVOKE
• 加密：对敏感数据进行加密，保护数据隐私。

4. 数据恢复

• 备份与恢复：提供数据备份和恢复功能，确保在数据损坏或丢失时能够恢复数据。
  • 全量备份、增量备份和差异备份
• 日志管理：记录所有对数据库的修改操作，支持事务的回滚和恢复。
  • 事务日志、归档日志
• 崩溃恢复：在系统崩溃后，通过检查点和日志回放，恢复数据库的一致性状态。

5. 并发控制

• 事务管理：提供事务的ACID特性（原子性、一致性、隔离性、持久性），确保事务的正确执行。
  • BEGIN TRANSACTION, COMMIT, ROLLBACK
• 锁机制：通过加锁控制并发访问，避免数据冲突。
  • 排它锁（X锁）、共享锁（S锁）
• 隔离级别：提供不同的事务隔离级别，以平衡并发性和一致性。
  • 读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）、可串行化（Serializable）

6. 数据完整性

• 实体完整性：确保每个表都有唯一标识行的主键。
  • 示例：PRIMARY KEY
• 参照完整性：确保外键引用的完整性，防止引用无效数据。
  • 示例：FOREIGN KEY
• 域完整性：确保列中的数据符合预定义的规则。
  • 示例：数据类型约束、检查约束（CHECK）
• 业务规则完整性：通过触发器和存储过程实现复杂的业务规则。
  • 示例：TRIGGER

7. 数据查询优化

• 查询优化器：通过选择最佳执行计划，提高查询性能。
• 统计信息：收集和维护数据库对象的统计信息，辅助查询优化。
• 执行计划：生成和解释查询的执行计划，帮助调优查询性能。

8. 数据冗余和容错

• 数据复制：在多个数据库服务器之间复制数据，提供高可用性和容错能力。
  • 主从复制、双向复制
• 分区：将大表分为多个小表，提升查询性能和管理效率。
  • 水平分区、垂直分区
• 集群和负载均衡：通过集群和负载均衡技术，提升系统的可扩展性和容错能力。

9. 报告和分析

• 报表生成：生成各种数据报表，支持决策分析。
• OLAP：支持在线分析处理（OLAP），提供多维数据分析功能。

10. 监控和调优

• 性能监控：实时监控数据库性能，发现和解决性能瓶颈。
• 自动调优：自动调整数据库配置，优化性能。
• 日志和审计：记录和分析数据库操作日志，进行安全审计和问题排查。

通过上述功能，数据库管理系统能够高效、安全地管理和操作大量数据，确保数据的安全性、一致性和高可用性。

8、定义并解释以下术语：模式、外模式、内模式、DDL、DML

在数据库系统中，以下术语有着重要的含义：

1. 模式（Schema）

定义：

• 模式是数据库的逻辑结构定义，它描述了数据库的组织和关系。模式包括表、视图、索引、存储过程、触发器等数据库对象的定义。

解释：

• 模式是数据库设计的核心部分，定义了数据库中数据的逻辑视图以及这些数据之间的关系。模式可以在不同的抽象级别上描述数据库，包括外模式、概念模式和内模式。

2. 外模式（External Schema）

定义：

• 外模式，也称为用户模式或视图模式，是数据库的一个子集，描述了特定用户或用户组能看到的数据和视图。

解释：

• 外模式为数据库用户提供了数据的个性化视图，隐藏了数据的复杂性和底层细节。它允许不同的用户以不同的方式查看相同的数据，以满足他们的特定需求和权限。

3. 内模式（Internal Schema）

定义：

• 内模式是数据库的物理存储结构和访问方法的描述，反映了数据在物理设备上的存储方式。

解释：

• 内模式包括数据文件的存储、索引的结构、存储路径等具体实现细节。它负责优化数据存储和访问效率，确保数据库系统的性能和安全性。

4. 数据定义语言（DDL - Data Definition Language）

定义：

• DDL是一组用于定义和管理数据库结构的SQL语句，包括创建、修改和删除数据库对象的语句。

常见语句：

• CREATE：创建数据库对象，如表、视图、索引等。
• ALTER：修改已有的数据库对象。
• DROP：删除数据库对象。
• TRUNCATE：删除表中的所有数据，但保留表结构。

解释：

• DDL语句用于定义数据库的模式和结构，涉及数据库对象的创建、修改和删除等操作。执行DDL语句通常会自动提交事务。

5. 数据操作语言（DML - Data Manipulation Language）

定义：

• DML是一组用于操作数据库中数据的SQL语句，包括插入、更新、删除和查询数据的语句。

常见语句：

• SELECT：查询数据。
• INSERT：插入新数据。
• UPDATE：更新已有数据。
• DELETE：删除数据。

解释：

• DML语句用于对数据库中的数据进行增、删、改、查等操作。与DDL不同，DML操作通常是针对数据的，而不是数据库结构。DML语句的执行可能需要显式提交事务。

总结：

• 模式（Schema）：数据库的逻辑结构定义，包含所有数据库对象。
• 外模式（External Schema）：特定用户视图的子集，展示用户能看到的数据。
• 内模式（Internal Schema）：数据库的物理存储结构，描述数据的物理存储和访问方式。
• DDL（数据定义语言）：定义和管理数据库结构的SQL语句。
• DML（数据操作语言）：操作数据库中数据的SQL语句。

通过理解这些术语，可以更好地掌握数据库系统的设计和操作，确保数据的有效管理和使用。

9、数据库中的自主存取控制方法和强制存取控制方法

数据库中的自主存取控制方法（Discretionary Access Control, DAC）和强制存取控制方法（Mandatory Access Control, MAC）是两种主要的访问控制机制，用于保护数据库中的数据安全。它们在控制策略和应用场景上有显著的差异。

1. 自主存取控制方法（Discretionary Access Control, DAC）

定义：

• 自主存取控制是一种基于数据所有者的访问控制机制，数据所有者（通常是用户或数据库管理员）具有定义和修改其他用户访问权限的能力。

特点：

• 灵活性：数据所有者可以根据需要自由分配权限。
• 基于身份：访问控制是基于用户的身份和权限。
• 细粒度控制：允许对单个数据库对象（如表、视图、列等）设置特定的访问权限。

实现方式：

• 权限授予和回收：使用SQL命令GRANT和REVOKE来授予和撤销用户对数据库对象的权限。

示例：

-- 授予用户alice对表employees的SELECT和INSERT权限
GRANT SELECT, INSERT ON employees TO alice;

-- 撤销用户alice对表employees的INSERT权限
REVOKE INSERT ON employees FROM alice;

优点：

• 易于管理和理解。
• 提供了灵活的权限控制，适用于多用户数据库环境。

缺点：

• 不容易强制实施统一的安全策略。
• 权限可能被滥用，导致安全风险。

2. 强制存取控制方法（Mandatory Access Control, MAC）

定义：

• 强制存取控制是一种基于系统策略的访问控制机制，系统根据预定义的安全策略强制实施访问控制，而不允许数据所有者随意更改权限。

特点：

• 强制性：访问控制由系统强制实施，用户无法自行更改权限。
• 基于安全标签：每个对象和用户都被分配一个安全标签（如级别或类别），系统根据这些标签决定访问权限。
• 统一策略：确保在整个系统中实施一致的安全策略。

实现方式：

• 安全标签和政策：数据库系统使用安全标签（如“机密”、“秘密”、“公开”）和访问控制矩阵来决定用户对数据的访问权限。

示例：

• 一个典型的MAC系统不会允许普通SQL命令直接管理安全标签，而是由数据库管理系统（DBMS）内部机制管理。假设在一个机密数据库系统中，用户和数据被分配不同的安全级别：

-- 创建一个表并分配安全级别
CREATE TABLE classified_data (
    data_id INT,
    data_value VARCHAR(255),
    security_level VARCHAR(50)
);

-- 插入数据并分配安全级别
INSERT INTO classified_data (data_id, data_value, security_level)
VALUES (1, 'Sensitive Information', 'Top Secret');

优点：

• 提供了高度的安全性，适用于需要严格安全控制的环境（如军事、政府）。
• 强制实施统一的安全策略，减少人为错误和权限滥用的风险。

缺点：

• 灵活性较差，难以适应动态变化的访问需求。
• 实现和管理复杂，需要精心设计的安全策略。

总结：

• 自主存取控制方法（DAC）：基于用户身份和数据所有者分配的权限，灵活但易被滥用。
• 强制存取控制方法（MAC）：基于系统强制实施的安全策略和安全标签，安全性高但灵活性差。

通过理解这两种访问控制方法，可以根据具体需求选择合适的策略，以确保数据库的安全性和灵活性。

10、并发操作可能会产生哪几类数据不一致？用什么方法能避免各种不一致的情况？

并发操作在数据库中可能会导致数据不一致的情况，常见的几类数据不一致包括丢失更新（Lost Update）、脏读（Dirty Read）、不可重复读（Non-repeatable Read）和幻读（Phantom Read）。为了避免这些不一致的情况，可以使用事务隔离级别和并发控制机制。下面详细描述每种数据不一致情况及其解决方法。

1. 丢失更新（Lost Update）

现象：

• 两个事务同时读取同一数据并更新，最终一个事务的更新被另一个事务覆盖，导致第一个事务的更新丢失。

示例：

• 事务A和事务B同时读取某行数据并进行更新，事务B的更新覆盖了事务A的更新。

解决方法：

• 使用锁机制：通过锁机制（如排它锁）确保在事务完成之前，其他事务无法修改同一行数据。
• 高隔离级别：使用Serializable隔离级别，确保事务以串行方式执行。

-- 使用排它锁来防止丢失更新
BEGIN TRANSACTION;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
-- 更新操作
COMMIT;

2. 脏读（Dirty Read）

现象：

• 一个事务读取了另一个未提交事务的修改数据，如果该未提交事务回滚，读取到的数据将是无效的。

示例：

• 事务A更新某行数据但未提交，事务B读取了这些未提交的数据。随后，事务A回滚，导致事务B读取了无效数据。

解决方法：

• 使用隔离级别：设置隔离级别为Read Committed或更高，确保事务只能读取已提交的数据。

-- 设置隔离级别为Read Committed
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

3. 不可重复读（Non-repeatable Read）

现象：

• 一个事务在两次读取同一行数据时，由于另一个事务的更新，读取到了不同的值。

示例：

• 事务A第一次读取某行数据，事务B更新了该行数据并提交，事务A第二次读取该行数据时，读取到的值与第一次不同。

解决方法：

• 使用隔离级别：设置隔离级别为Repeatable Read或更高，确保事务在多次读取同一行数据时能读取到相同的值。

-- 设置隔离级别为Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4. 幻读（Phantom Read）

现象：

• 一个事务在两次读取同一个条件的数据集时，由于另一个事务的插入或删除，读取到的数据集不一致。

示例：

• 事务A第一次读取符合条件的若干行数据，事务B插入了新的一行数据符合条件，事务A第二次读取相同条件时，发现数据集多了一行。

解决方法：

• 使用隔离级别：设置隔离级别为Serializable，确保事务在执行过程中不会出现新插入或删除导致的幻读问题。

-- 设置隔离级别为Serializable
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

事务隔离级别总结

• Read Uncommitted：最低级别，可能会发生脏读、不可重复读和幻读。
• Read Committed：防止脏读，但可能会发生不可重复读和幻读。
• Repeatable Read：防止脏读和不可重复读，但可能会发生幻读。
• Serializable：最高级别，防止脏读、不可重复读和幻读，但并发性能最低。

其他并发控制方法

• 乐观并发控制：假设并发冲突不频繁发生，在提交时检测冲突，如果检测到冲突则回滚事务。
• 悲观并发控制：在数据访问前对数据加锁，防止其他事务并发访问。

-- 设置隔离级别为Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

通过使用合适的事务隔离级别和并发控制方法，可以有效避免各种数据不一致的情况，确保数据库的可靠性和数据的一致性。

11、什么是基本表、视图？两者的区别和联系是什么？

在数据库管理系统中，基本表（也称为基表）和视图是两种重要的数据库对象。它们在数据存储和数据操作中发挥着不同的作用。

基本表（Base Table）

基本表是数据库中实际存储数据的表，是物理存在的。每个基本表都包含行和列，行代表记录，列代表属性。基本表是数据库的核心，所有的数据都存储在这些表中。可以对基本表执行各种数据操作，如插入（INSERT）、更新（UPDATE）、删除（DELETE）和查询（SELECT）。

视图（View）

视图是从一个或多个基本表中导出的虚拟表，它本身不存储数据，而是存储一个查询定义（即SQL查询语句）。当访问视图时，数据库系统根据视图的查询定义动态生成数据。因此，视图是逻辑存在的，而不是物理存在的。视图可以用来简化复杂查询、提供数据安全性以及抽象数据层次。

基本表与视图的区别

1. 存储方式：

   • 基本表：实际存储数据在数据库中。
   • 视图：不存储数据，只存储查询定义。

2. 数据操作：

   • 基本表：可以执行插入、更新、删除和查询操作。
   • 视图：通常只能执行查询操作，有些视图是可更新的，但这种情况受到限制。

3. 物理存在性：

   • 基本表：物理存在于数据库中。
   • 视图：逻辑存在，是对基本表或其他视图的查询结果的表示。

4. 性能：

   • 基本表：直接操作实际数据，性能通常较高。
   • 视图：每次访问时根据定义的查询动态生成数据，性能可能受到影响，尤其是复杂视图。

基本表与视图的联系

1. 数据源：视图是基于基本表创建的，视图的数据来自基本表。
2. 数据一致性：由于视图是基于基本表的查询结果，因此视图的数据与基本表的数据是一致的，视图的更新会反映在基本表中（在视图可更新的情况下）。
3. 安全性：视图可以限制对基本表的直接访问，通过视图可以实现细粒度的权限控制，只允许用户访问视图中的特定数据，而不是整个基本表。

总结

基本表是数据库中实际存储数据的表，是数据存储的基础；视图是从基本表导出的虚拟表，用于简化查询、提高数据访问的安全性和灵活性。两者在数据库管理中各有用途，互为补充，共同构成了数据库系统的数据管理机制。

12、试述数据库与数据仓库的主要区别

数据库和数据仓库都是用于数据存储和管理的重要系统，但它们有不同的用途、设计和技术特点。下面是它们的主要区别：

1. 目的和用途

• 数据库：主要用于支持日常业务操作和事务处理（OLTP，Online Transaction Processing）。适用于需要快速插入、更新、删除和查询数据的应用，如银行系统、库存管理系统等。
• 数据仓库：主要用于支持数据分析和决策支持（OLAP，Online Analytical Processing）。适用于存储和分析大量历史数据，帮助企业进行业务分析、数据挖掘和决策支持。

2. 数据结构

• 数据库：数据结构通常是高度规范化的，目的是减少数据冗余和确保数据一致性。数据模型通常是第三范式或更高范式。
• 数据仓库：数据结构通常是非规范化的，使用星型或雪花型模式，以优化查询性能和数据分析。

3. 数据类型

• 数据库：主要存储当前的事务性数据，包括实时数据。
• 数据仓库：主要存储大量的历史数据，用于长时间的数据分析。

4. 操作类型

• 数据库：支持高频率的读写操作，包括大量的插入、更新和删除操作。
• 数据仓库：主要是读操作，写操作相对较少，通常是批量导入数据（ETL，Extract, Transform, Load）。

5. 性能优化

• 数据库：优化实时事务处理的性能，通常使用索引、事务管理和并发控制等技术。
• 数据仓库：优化复杂查询和报表生成的性能，使用分区、物化视图、聚合等技术。

6. 数据存储

• 数据库：通常存储在关系数据库管理系统（RDBMS）中，如MySQL、PostgreSQL、Oracle等。
• 数据仓库：可以存储在专用的数据仓库系统中，如Amazon Redshift、Google BigQuery、Snowflake，以及传统的RDBMS上的数据仓库解决方案如Teradata。

7. 数据集成

• 数据库：数据通常是来自一个或少数几个系统，主要是内部系统的数据。
• 数据仓库：数据通常来自多个异构数据源，包括内部系统、外部系统、第三方数据等，需要进行数据清洗、转换和集成。

8. 更新频率

• 数据库：数据更新频率高，数据是实时或近实时更新的。
• 数据仓库：数据更新频率较低，通常是定期批量更新（如每天、每周）。

9. 用户

• 数据库：主要面向操作人员、事务处理系统的用户。
• 数据仓库：主要面向数据分析师、业务分析人员和高层决策者。

总结

数据库和数据仓库在设计目标、数据结构、操作类型、性能优化等方面都有明显的区别。数据库侧重于高效地处理日常事务和实时数据操作，而数据仓库侧重于优化数据分析和决策支持的性能。两者各自发挥不同的作用，通常在一个企业的信息系统中共同存在，相辅相成。