JZTXT

04735-数据库系统原理知识点

发布时间 2023-03-30 15:22:49作者: 若隐随心

04735-数据库系统原理

人生最大的遗憾，往往并不是没有去做，而是没有坚持；明明只要再坚持努力一丢丢，却选择放弃，与成功失之交臂。

一、数据库系统概述

1. 数据库基本概念

1.1 数据

是描述事务的符号记录，是指用物理符号记录下来的、可以鉴别的信息。

多种表现形式包括数字、字母、文字、特殊字符组成的文本数据，也可以是图形、图像、动画、影像、声音、语言等多媒体数据。

数据的解释是对数据含义的说明，也称为数据的语义，即数据所蕴含的信息。数据与其语义密不可分，没有语义的数据是没有意义和不完整的。因此，数据是信息存在的一种形式，只有通过解释或处理的数据才能成为有用的信息。

1.2 数据库

是长期存储在计算机中的有组织的、可共享的大量数据集合，且数据库中的数据按一定的数据模型组织、描述和存储，具有较小的冗余度、较高的数据独立性，系统易于扩展，并可以被多个用户共享。

数据库中存储的数据具有永久存储、有组织和可共享三个基本特点。

1.3 数据库管理系统

是专门用来建立和管理数据库的一套软件，介于应用程序和操作系统之间。它负责科学有效地组织和存储数据，并帮助数据库的使用者能够从大量的数据中快速地获取所需数据，以及提供必要的安全性和完整性等统一控制机制，实现对数据有效的管理和维护。

数据库管理系统的主要功能包括：

  • 数据定义功能 （DDL）
  • 数据操纵功能（DML）
  • 数据库的运行管理功能
  • 数据库的建立维护功能
  • 数据组织、存储和管理功能
  • 其他功能，主要包括与其他软件的网络通信功能、不同数据库管理系统之间的数据传输以及相互访问的功能等。

1.4 数据库系统

是指在计算机中引入数据库技术之后的系统。通常，一个完整的数据库系统包括数据库、数据库管理系统以及相关实用工具、应用程序、数据库管理员和用户：

  • 数据库管理员（DBA）

    不同于普通数据库用户，他们是专门负责对数据库进行维护，并保证数据库正常、高效运行的人员。

  • 用户

    数据库系统的服务对象，其通常包括程序员和数据库终端用户两类用户，程序员通过高级程序设计语言和数据库语言编写数据库应用程序，应用程序会根据需要向数据库管理系统发出适当的请求，再由数据库管理系统对数据库执行相应操作。而终端用户则是从客户机或联机终端上以交互方式向数据库系统提出各种操作请求，并由数据库管理系统操作执行，而后访问数据库中的数据。

2. 数据管理技术的发展

数据管理可以从两个方面来理解：一是针对组织业务的管理，负责制定并执行整个组织中关于数据的定义、组织、保护与有效使用的策略、过程和计划。二是依靠技术，负责实现数据作为一种资源的集中控制管理。

将收集的数据进行适当的构造，这称为数据组织。数据的组织分为逻辑组织和物理组织两种：逻辑组织是用户或应用程序所使用的数据结构形式；而物理组织则是数据在物理存储设备上的结构形式。这两者之间可以相互独立。

为了备用，需要将数据归类进行存储。为了向用户提供信息，存储的数据要能够方便的被选择提取，这称为检索。为了保护数据的正确性和安全性等，必须建立一些相应的规则和执行这些规则的过程来控制数据的存与取，以实现数据库管理的目标。

保管的数据是那些需要长期、多次使用的数据，但随着时间的推移，数据组织单位的内外环境会发生变化，因而存储的数据在规模和结构方面都可能变化，所以数据管理还必须能够对其进行维护。

数据管理的任务：进行数据收集、组织、控制、存储、选取、维护，实现在适当的时刻、以适当的形式、给适当的人、提供适当的数据，它是数据处理的中心问题，而数据处理则是指对各种数据进行收集、存储、加工和传播的一系列活动的总和。

数据管理技术共经历了人工管理、文件系统和数据库系统三个阶段：

2.1 人工管理阶段

  • 数据不保存
  • 应用程序管理数据
  • 数据面向应用

2.2 文件系统阶段

  • 文件系统是将数据的逻辑结构和物理结构分离，由 “存放方法” 实现逻辑结构和物理结构之间的映射。

  • 应用程序只涉及数据的逻辑结构，系统决定数据的物理结构，两者之间可以有差别。

  • 当物理结构发生改变时，不会导致应用程序的修改，这称为数据的物理独立性。

  • 数据的物理独立性使应用程序脱离数据的物理结构，使其适用性得以提高。同时，应用程序的编程人员不必关心数据的物理存储细节，因而其生产效率相对也得到提高。

  • 相对于人工管理数据的方法，文件系统管理数据有了很大的改进，具有数据可以长期保存和专门管理的特点，它提供了物理数据独立性，使应用程序与数据的具体物理结构分离并通过数据的抽取、排序、合并等可以为应用程序提供新的文件，从而使得数据共享成为可能；但是在文件系统中，不能实现数据的普通共享，只能实现文件级的共享，而不能在记录或数据项级实现数据的共享。文件的逻辑结构使根据它的应用而设计的，数据的逻辑结构与应用程序之间相互依赖。

2.3 数据库系统阶段

与人工管理、文件系统两种数据库管理方法相比较，数据库系统具有以下特点：

  • 数据集成

    是数据库管理系统的主要目的

  • 数据共享性高

    不只是同一数据可以被多个不同用户存取，还包含了并发共享

  • 数据冗余小

    并非所有的冗余都可以或者应该被消除

  • 数据一致性

    通过消除或者控制数据冗余，可以在一定的范围内避免数据的不一致性，引起不一致的根源是数据冗余

  • 数据独立性高

    数据定义与使用数据的应用程序分离称为数据独立。数据定义的修改，在一定范围内不会引起应用程序的修改，这种独立称为数据的逻辑独立。可改变数据的存储结构或存取方法以响应变化的需求而无需修改现有程序，这种称为数据的物理独立。

  • 实施统一管理与控制

    主要包括数据的安全性、完整性、并发控制与故障恢复等，即数据库保护

  • 减少应用程序的开发与维护的工作量

    数据库方法表现在应用方面的一个优点是在数据库上开发与维护新的应用所花费的代价和时间大大减少

3. 数据库系统结构

若从数据库管理员的角度来看，数据库系统可分为内部系统结构和外部体系结构，其中内部系统结构通常采用三级模式结构。

3.1 数据库系统的三级模式结构

  • 模式

    在三级模式中，模式也称为概念模式或逻辑模式，它是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。

    DBMS 提供模式描述语言来严格的定义模式，不仅要定义数据的逻辑结构，还要定义数据之间的联系，定义数据相关的安全性、完整性等要求。

  • 外模式

    外模式也称为子模式或用户模式，它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是与某一应用有关的数据的逻辑表示。

    外模式实际上是用于满足不同数据库用户需求的数据视图，即用户视图。相应地，DBMS 提供子模式描述语言来严格的定义子模式。

  • 内模式

    内模式也称为存储模式，它是对数据库中物理结构和存储方式的描述，是数据在数据库内部的表现形式。

    内模式实际上是整个数据库的最底层表示，它不同于物理层，是数据库管理员所见到的，特定的 DBMS 所处理的数据库的内部结构，即内部视图或存储视图。相应地，DBMS 提供内模式描述语言来严格的定义内模式，即定义所有的内部记录类型、索引和文件的组织方式，以及所有数据控制方面的细节。

  • 三级模式的两层映像与数据独立性

    构成数据库系统三级模式结构的三个模式分别是对数据的三级抽象，它们彼此之间具有以下特点：

    1. 一个数据库的整体逻辑结构和特征的描述（概念模式）是独立于数据库其他层次结构（内/外模式）的描述，其是数据库的核心，也是数据库设计的关键。
    2. 一个数据库的内部存储模式依赖于概念模式，但存储模式独立于外部模式，也独立于具体的存储设备。
    3. 用户逻辑结构（外模式）是在全局逻辑结构描述的基础上定义的，它面向具体的应用程序，独立于内部模式和存储设备。
    4. 特定的应用程序是在外模式的逻辑结构上编写的，它依赖于特定的外模式，与数据库的模式和存储结构独立。

    外模式/模式映像（逻辑独立性），所谓映像，就是一种对应规则，它指出映像双方是如何进行转换的。

    模式/内模式映像（物理独立性），定义了数据库全局逻辑结构与物理存储之间的对应关系，这种映像的定义通常是在模式中加以描述的，模式/内模式映像是唯一的。

3.2 数据库系统的运行与应用结构

  • 客户端/服务器结构（C/S）

    在客户端/服务器结构中，命令行客户端、图形化界面管理工具、应用程序等称为“客户端”、“前台”或“表示层”，主要完成数据库使用者的交互任务。而数据库管理系统则称为“服务器”、“后台”或“数据层”，主要负责数据管理。这种操作数据库的模式称为客户端/服务器（C/S）模式。在这个模式中，客户端与服务器可以同时工作在同一台计算机上，这种工作方式称为“单机方式”，也可以“网络方式”进行运行。

  • 浏览器/服务器结构（B/S）

    是一种基于 Web 应用的客户端/服务器结构，也成为三层客户端/服务器结构。在数据库系统中，它将与数据库管理系统交互的客户端进一步细分为“表示层”和“处理层”，“表示层”是数据使用者的操作和展示界面，“处理层”也称为中间层。

4. 数据模型

数据模型是对现实世界数据特征的抽象，描述的是数据的共性内容。

4.1 数据特征与数据模型组成要素

数据具有静态和动态两种特征。其中数据的静态特征包括数据的基本结构、数据间的联系以及对数据取值范围的约束；数据的动态特征是指对数据可以进行符合一定规则的操作。

数据模型通常由数据结构、数据操作和数据约束三个要素组成。

  • 数据结构描述的是系统的静态特性，即对象的数据类型、内容、属性以及数据对象之间的联系。
  • 数据操作描述的是系统的动态特性，是对各种对象的实例允许执行的操作的集合，包括操作以及有关的操作规则。
  • 数据约束描述数据结构中数据间的语法和语义关联，包括相互制约与依存关系以及数据动态变化规则，以保证数据的正确性、有效性和相容性。

4.2 数据模型的分类

  • 概念层数据模型

    也称为数据的概念模型或信息模型，它用来描述现实世界的事物，与具体的计算机系统无关，且独立于任何 DBMS，但容易向 DBMS 所支持的逻辑数据模型转换。

    基本概念：

    1. 实体（Entity）

      客观存在并可相互区别的事物称为实体

    2. 属性（Attribute）

      实体所具有的某种特性称为实体的属性

    3. 码/键（Key）

      可唯一标识实体的属性集称为码或键

    4. 域（Domain）

      属性的取值范围称为该属性的域。

    5. 实体型（Entity Type）

      具有相同属性的实体必然具有共同的特征和性质

    6. 实体集（Entity Set）

      同类型实体的集合称为实体集

    7. 联系（Relationship）

      实体内部的联系通常是指实体个属性之间的联系，实体之间的联系是指不同实体之间的联系

    8. 概念模型的表示方法

      用 E-R 图来描述现实世界的概念模型，实体用矩形表示，属性用椭圆表示，联系用菱形表示。

  • 逻辑层数据模型

    逻辑层是数据抽象的中间层，描述数据整体的逻辑结构。

    主要的逻辑数据模型有：

    1. 层次模型

      数据库最早使用的数据模型。

      特点：有且仅有一个节点没有父节点，它称作根节点，其他节点有且仅有一个父节点。

    2. 网状模型

      以网状结构表示实体与实体之间的联系。

    3. 关系模型

      用二维表结构来表示实体与实体之间的联系的模型，并以二维表格的形式组织数据库中的数据。

      优点：关系模型是建立在严格的数学概念基础之上的，关系模型的概念单一，统一用关系来表示实体与实体之间的联系，对数据的检索和更新结果同样也是用关系来表示，关系模型的存取路径对用户透明，从而具有更高的数据独立性、更好的安全保密性，也简化了程序员的工作和数据库开发建立的工作。

    4. 面向对象模型

      与数据库相结合所构成的数据模型。

  • 物理层数据模型

    也称为数据的物理模型，其描述数据在存储介质上的组织结构，是逻辑模 型的物理实现，即每一种逻辑模型在实现时都有与之对应的物理模型。

    从现实世界到概念模型的转换是由数据库设计人员完成的；从概念模型到逻辑模型的转换可以由数据库设计人员完成，也可以用数据库设计工具协助设计人员完成；从逻辑模型到物理模型的转换是由数据库管理系统完成。

二、关系数据库

1. 关系数据库概述

关系数据库的基本特征是使用关系数据模型组织数据，这种思想源于数学。

关系数据库的优点：包括高级的非过程语言接口、较好的数据独立性等，为商品化的关系数据库管理系统的研制做好了技术上的准备。

2. 关系数据模型

关系数据库系统是支持关系模型的数据库系统。作为一种数据模型，关系模型同样包含三个组成要素，分别是关系数据结构、关系操作结合和关系完整性约束。

2.1 关系数据结构

关系模型只包含单一的数据结构，即关系。在关系模型中，现实世界的实体以及实体间的各种联系，均是使用关系来表示。在用户看来，关系模型是把数据库表示为关系的集合，且关系数据库是以二维表格的形式组织数据。

  • 表

    也称关系，是一个二维的数据结构，它由表名、构成表的各个列及若干行数据组成。

    每个表有一个唯一的表名，表中每一行数据描述一条具体的记录值。

  • 关系

    一个关系逻辑上对应一张二维表，可以为每个关系取一个名称进行标识。

    关系可以有三种类型，即基本关系、查询表和视图表。基本关系通常又称为基本表或基表，是实际存在的表，它是实际存储数据的逻辑表示；查询表是查询结果；视图表是由基本表或其他视图表导出的表，是虚表，不对应实际存储的数据。

  • 列

    表中的列，也称作字段或属性。表中每一列都有一个名称，称为列名、字段名或属性名。每一列表示实体的一个属性，具有相同的数据类型。

    在一个数据库中，表名必须唯一；在表中，字段名必须唯一，不同表中可以出现相同字段名；表和字段的命名应尽量有意义，并尽量简单。

  • 属性

    表中的一个列即为一个属性，给每一个属性起一个名称即为属性名。与之同义的术语是“列”。表中属性的个数称为关系的元或度。列的值称为属性值；属性的取值范围称为值域。

  • 行

    表中的行，也称作元组或记录。表中的数据是按行存储的，表中的一行数据即为一个元组或一条记录，其每行由若干字段值组成，每个字段描述该对象的一个属性或特征。

  • 元组

    表中的一行即为一个元组

  • 分量

    元组中的一个属性值称为分量

  • 码或键

    如果在一个关系中，存在这样的属性，使得在该关系的任何一个关系状态中的两个元组，在该属性上值的组合都不相同，即这些属性的值都能用来唯一标识该关系的元组，则称这些属性为该关系的码或键。

  • 超码或超键

    如果在关系的一个码中移去某个属性，它仍然是这个关系的码，则称这样的码或键为该关系的超码或超键。一般每个关系至少有一个默认的超码或超键，即该关系的所有属性的集合，也是这个关系的最大超码或超键。

  • 候选码或候选键

    如果在关系的一个码或键中，不能从中移去任何一个属性，否则它就不是这个关系的码或键，则称这样的码或键为该关系的候选码或候选键，一个关系的候选码或候选键是这个关系的最小超码或超键。

    有些情况下，需要几个属性才能唯一确定一条记录。

  • 主码或主键

    在一个关系的若干个候选码或候选键中指定一个用来唯一标识关系的元组，则称这个被指定的候选码或候选键位该关系的主码或主键。

  • 全码或全键

    一个关系模式的所有属性集合是这个关系的主码或主键，则称这样的主码或主键为全码或全键。

  • 主属性和非主属性

    关系中包含任何一个候选码中的属性称为主属性或码属性，不包含在任何一个候选码中的属性称为非主属性或非码属性。

  • 外码或外键

    当关系中的某个属性不是这个关系的主码或候选码，而是另一个关系的主码时，称该属性为这个关系的外码或外键。

  • 参照关系和被参照关系

    参照关系也称为从关系，被参照关系也称为主关系，它们是指以外码或外键相关联的两个关系，以外码作为主码的关系称为被参照关系，外码所在的关系称为参照关系。被参照关系域参照关系是通过外码相联系的，这种联系通常是一对多的联系。

  • 域

    表示属性的取值范围

  • 数据类型

    表中每个列都有相应的数据类型，它用于限制该列中存储的数据。每个字段表示同一类信息，具有相同的数据类型。

  • 关系模式

    同数据模型一样，数据库也有型和值之分，在关系数据库中，关系模式是型，关系是值，即关系模式是对关系的描述。

    关系模式是静态的、稳定的，而关系是动态的、随时间不断变化的。这是因为关系操作在不断地更新着数据库中的数据。人们常常会把关系模式和关系都笼统地称为关系。

  • 关系数据库

    关系数据库是以关系模式作为数据库的逻辑模型，并采用关系作为数据组织方式的一类数据，其数据库操作建立在关系代数的基础上。在一个给定的应用领域中，所有关系的集合构成一个关系数据库。

    关系数据库对关系是有限定的，具体要求如下：

    1. 每一个属性都是不可分解的
    2. 每一个关系仅仅有一种关系模式
    3. 每一个关系模式中的属性必须命名，在同一个关系模式中，属性名必须是不同的
    4. 同一关系中不允许出现候选码或候选键完全相同的元组
    5. 在关系中元组和属性的顺序是无关紧要的，可以任意交换

2.2 关系操作集合

  • 基本的关系操作

    关系模型中常用的关系操作包括查询（Query）操作和插入（Insert）、删除（Delete）、修改（Update）操作两大部分。

    查询操作分为：选择、投影、连接、除、并、差、交、笛卡尔积等。

    五种基本操作：选择、投影、并、差、笛卡尔积。

    关系操作的特点是集合操作方式，即操作的对象和结果都是集合，这种操作方式也称为一次一集合的方式。

  • 关系数据语言的分类

    关系操作是通过关系语言来实现的，关系语言的特点是高度非过程化，即用户不必请求数据库管理员为其建立特殊的存取路径，存取路径的选择由 DBMS 的优化机制来完成；用户也不必求助于循环和递归来完成数据的重复操作。

    关系操作的能力可以用代数方式和逻辑方式来表示。

    关系代数、元组关系演算和域关系演算均是抽象的查询语言。

    结构化查询语言 SQL 充分体现了关系数据语言的特点和优点，是关于数据库的标准语言。

    关系数据语言可以分为三类：关系代数语言、关系演算语言及兼具两种特点的语言。共同特点是：语言具有完备的表达能力，是非过程化的集合操作语言，功能强，能够独立使用也可以嵌入高级语言中使用。

  • 关系代数

    操作包含三大要素：操作对象、操作符、操作结果。在关系代数操作中，操作对象和操作结果均为关系。

    关系代数直接应用关系的运算来表达操作目的，而代数用到的运算符包括集合运算符和专门的关系运算符两类：

    运算符 含义
    集合运算符 ∪ 并
    ∩ 交
    - 差
    ⨉ 笛卡尔积
    专门的关系运算符 ∂ 选择
    π 投影
    ⨝ 连接
    ÷ 除
    比较操作符 > 大于
    ⩾ 大于等于
    < 小于
    ⩽ 小于等于
    = 等于
    ≠ 不等于
    逻辑操作符 ┐ 非
    ∧ 与
    ∨ 或

    关系代数经过有限次复合的式子称为关系代数操作表达式，简称关系代数表达式。

    传统的集合运算

    是二目运算，关系看成元组的集合，其运算关系是从关系的水平方向，即行的角度来进行，具体有并、差、交、笛卡尔积 4 种运算。

    1. 并

      R3 = R1 ∪ R2，R3 是由属于关系 R1 或 R2 的所有不同元组组成

    2. 差

      R3 = R1 - R2，R3 是由属于关系 R1，但不属于 R2 的元组组成

    3. 交

      R3 = R1 ∩ R2，R3 是由既属于关系 R1，同时又属于 R2 的元组组成

    4. 笛卡尔积

      R3 = R1 X R2，R1 为 M 元关系，R2 为 N 元关系，R3 是由 R1 和 R2 的所有元组连接而成的具有（M + N）个分量的元组组成

    专门的关系运算

    不仅涉及行，而且涉及列，它可分为一元专门关系操作和二元专门关系操作。一元专门关系操作包括对单个关系进行垂直分解的投影运算和进行水平分解的选择运算。二元专门关系操作则是对两个关系进行操作，包括连接运算和除运算。

    1. 选择（SELECT）

      SELECT 关系名 WHERE 条件，其中条件是由常数、属性名或列名、比较操作符及逻辑操作符组成的条件表达式。

    2. 投影（PROJECTION）

      PROJECTION 关系名（属性名1，属性名2，...... 属性名N）

    3. 连接（JOIN）

      JOIN 关系名1 AND 关系名2 WHERE 条件，条件是由比较操作符和属性名或列名组成的条件表达式，连接操作中有两种最为常用的连接：等值连接和自然连接。

    4. 除（DIVISION）

      R ÷ S，R 和 S 代表两个不同的关系，在除运算中，若被除关系为 M 元关系，除关系为 N 元关系，则运算结果为一个 M- N 元关系。

2.3 关系的完整性约束

数据库的数据完整性是指数据库中数据的正确性、相容性和一致性。

  • 实体完整性约束

    是指关系的主属性，即主码的组成不能为空，也就是关系的主属性不能是空值 NULL。

  • 参照完整性约束

    就是定义外码和主码之间的引用规则，它是对关系间引用数据的一种限制。

    描述定义：若属性 F 是基本关系 R 的外码，它与基本关系 S 的主码 K 相对应，则对 R 中每个元组在 F 上的值只允许两种可能，即要么取空值，要么等于 S 中某个元组的主码值。

  • 用户定义完整性约束

    是针对某一应用环境的完整性约束条件，它反映了某一具体应用所涉及的数据应满足的要求。

  • 关系模型完整性约束的校验

    为了维护数据库中数据的完整性，在对关系数据库执行插入、删除和更新操作时，需要检验是否满足上述三类完整性约束。

3. 关系数据库的规范化理论

3.1 关系模式下可能存在的冗余和异常问题

  • 数据冗余
  • 更新异常
  • 插入异常
  • 删除异常

3.2 函数依赖与关键字

函数依赖是指关系中属性间的对应关系。设 R 为任一给定关系，如果对于 R 中属性 X 的每一个值，R 中属性 Y 只有唯一值与之对应，则称 X 函数决定 Y 或称 Y 函数依赖于 X，记作 X -> Y，其中 X 称为决定因素。

  • 完全函数依赖

    设 R 为任一给定关系，X、Y 为其属性集，若 X -> Y，且对 X 中的任何真子集 X 都有 X -> Y，则称 Y 完全函数依赖于 X。

  • 部分函数依赖

    设 R 为任一给定关系，X、Y 为其属性集，若 X -> Y，且 X 中存在一个真子集 X，满足 X -> Y，则称 Y 部分函数依赖于 X。

  • 传递函数依赖

    设 R 为任一给定关系，X、Y、Z 为其不同属性子集，若 X -> Y，Y -> X，Y -> Z，则有 X -> Z，称为 Z 传递函数依赖于 X。

关键字的定义：设 R 为任一给定关系，U 为其所含的全部属性集合，X 为 U 的子集，若有完全函数依赖 X -> U，则 X 为 R 的一个候选关键字。

3.3 范式与关系规范化过程

一个低一级范式的关系模式通过模式分解可以转换为若干个高一级范式的关系模式的集合，这种过程化就叫规范化。

  • 第一范式

    设 R 为任一给定关系，如果 R 中每一列与行的交点处的取值都是不可再分的基本元素，则称 R 为第一范式。第一范式是一个不含重复组的关系，其中不存在嵌套结构，不满足第一范式的关系为非规范关系。

  • 第二范式

    设 R 为任一给定关系，若 R 为 1NF，且其所有非主属性都完全函数依赖与候选关键字，则 R 为第二范式。

  • 第三范式

    设 R 为任一给定关系，若 R 为 2NF，且其每一个非主属性都不传递函数依赖于候选关键字，则 R 为第三范式。

  • BCNF

    设 R 为任一给定关系，X、Y 为其属性集，F 为其函数依赖集，若 R 为 3NF，且其 F 中所有函数依赖 X -> Y（Y 不属于 X）中的 X 必须包含候选关键字，则 R 为 BCNF。

3.4 关系规范化理论的应用

主要应用于数据库设计中的概念设计阶段。在实现设计中当将 E-R 图向关系模型转换时，还可以用它来分析发现概念设计中可能存在的遗漏或不当之处，特别是联系实体是否不单独转换为一独立关系而集成到与之相连的基本实体中取处理时，规范化理论时最有效的评价准则。

三、数据库设计

1. 数据库设计概述

1.1 数据库的生命周期

数据库的生命周期可分为两个阶段：

  • 数据库分析与设计阶段
    • 需求分析
    • 概念设计
    • 逻辑设计
    • 物理设计
  • 数据库实现与操作阶段
    • 数据库的实现
    • 操作与监督
    • 修改与调整

1.2 数据库设计的目标

设计的目标：

  • 满足应用功能需求

    指用户当前与可预知的将来应用所需求的数据及其联系，应全部准确地存储在数据库中，从而可满足用户应用中所需要的对数据进行存、取、删、改等操作。

  • 良好的数据库性能

    主要是指对数据库的高效率存取和空间的节省，并具有良好的数据共享性、完整性、一致性及安全保密性。

1.3 数据库设计的内容

  • 数据库结构设计

    是针对给定的应用环境进行数据库的模式或子模式的设计，包括数据库的概念结构设计、逻辑结构设计和物理结构设计。模式定义并给出各应用程序共享的结构，是静态的，一经形成通常不会改变。

  • 数据库行为设计

    是确定数据库用户的行为和动作，而用户的行为和动作是对数据库的操作，它们通常是通过应用程序来实现的，行为设计是动态的。

1.4 数据库设计的方法

  • 直观设计法

    最原始的数据库设计方法

  • 规范设计法

    1. 新奥尔良设计方法
    2. 基于 E-R 模型的数据库设计方法
    3. 基于第三范式的设计方法

  • 计算机辅助设计法

    通常通过人机交互的方式来完成（例如 CASE 工具）

1.5 数据库设计的过程

数据库设计过程实际上是一个反复修改、反复设计的迭代过程。

  • 需求分析阶段

  • 结构设计阶段

    包括概念结构设计、逻辑结构设计和物理结构设计

  • 行为设计阶段

    包括功能设计、事务设计和程序设计

  • 数据库实施阶段

    包括加载数据库和调试运行应用程序

  • 数据库运行和维护阶段

2. 数据库设计的基本步骤

数据库设计的特点是结构设计与行为设计分离。

2.1 需求分析

需求分析是数据库设计的起点，目标是了解与分析用户的信息及应用处理的要求，并将结果按一定的格式整理而形成需求分析报告，该分析报告是后续过程的依据。

数据库设计人员进行需求分析的基本方法是听取数据库应用部门工作人员的报告，并与之座谈。

需求分析分为四个步骤：

  • 确定数据库范围

    数据库设计的第一项工作就是要确定数据库范围，即确定数据库应支持哪些应用功能。

  • 应用过程分析

    是指了解并分析数据与数据处理间的关系，应用过程分析的结果是数据库结构设计的重要依据。数据库应用过程可以借助数据流图或其他信息及应用结构图形表示。

  • 收集与分析数据

    数据收集与分析的任务是了解并分析数据的组成格式及操作特征，每个数据元素的语义及关系等，并将它们收集起来整理归档。

    数据流图中所涉及的数据都是收集与分析的对象，主要包括报表、文件、单据及各种原始资料。

    数据的收集与分析工作：

    • 静态结构

      是指不施加应用操作于其上时数据的原始状况，这可通过数据分类表和数据元素表进行说明：

      1. 数据分类表用于数据的总体描述，格式如下：

        数据 id 数据名 用途 主人 用户 来源 去向 存档时间 数据量

      2. 数据元素表指通常意义下的数据项或属性，格式如下：

        数据 id 数据元素 id 元素名 意义 类型 长度 算法 备注

    • 动态结构

      是指将应用操作施加于数据之上后数据的状况，可通过任务分类表和数据表操作特征表进行说明。

      1. 任务分类表示根据对数据流图的分析，可将业务处理过程划分为不同任务，格式如下：

        任务 id 名称 功能 类型 主人 用户 执行日期 频率 操作过程 备注

      2. 数据操作特征表用以描述任务和数据之间的关系，包括不同任务对数据执行不同的操作频率，格式如下：

        任务 id 数据 id 建立 查询 插入 修改 删除 数据量

    • 数据约束

      是指使用数据时的特殊要求：

      1. 数据的安全保密性
      2. 数据的完整性
      3. 响应时间
      4. 数据恢复

  • 编写需求分析报告

    作为需求分析的一个总结，内容如下：

    • 数据库的应用功能目标

    • 标明不同用户视图范围

    • 应用处理过程需求说明

      1. 数据流程图
      2. 任务分类表
      3. 数据操作特征表
      4. 操作过程说明书

    • 数据字典

      是数据库系统中存储三级结构定义的数据库，通常指的是数据库系统中各类数据详细描述的集合。包括数据分类表、数据元素表和各类原始资料。

    • 数据量

      根据数据分类表中的静态数据量和操作特征表中的动态数据量，进行统计计算，求出数据总量。

    • 数据约束

      1. 数据的安全保密性
      2. 数据的完整性
      3. 响应时间
      4. 数据恢复

2.2 概念结构设计

概念结构设计的任务是需求分析中产生的需求分析报告的基础上，按照特定的方法设计满足应用需求的用户信息结构，该信息结构通常称为概念模型。E-R 图作为概念模型的描述工具。

概念结构设计的常用方法有实体分析法和属性综合法两种，它们也分别称为自顶向下和自底向上法。

2.3 逻辑结构设计

逻辑结构设计的目标是将概念模型转为等价的、并为特定 DBMS 所支持数据模型的结构。

数据库逻辑模型一般由层次、网状、关系数据模型表示。

逻辑结构设计的输入信息包括：

  1. 独立于特定 DBMS 的概念模型
  2. 有关响应时间、安全保密性、数据完整性及恢复方法的要求说明，包括保持数据一致性的规则说明。
  3. 数据量及使用频率
  4. 特定 DBMS 特性，包括 DBMS 支持的数据模型及数据定义语言的说明。

逻辑结构设计的输出信息包括：

  1. 一个特定的 DBMS 支持的概念模式，或称之为模式
  2. 一个或多个外部视图，或称之为子模式
  3. 物理设计说明，其主要包括存入数据库中的数据量、使用频率及响应时间要求
  4. 程序设计说明

逻辑结构设计的步骤：

  1. 模型转换是指将概念模型等价地转换为特定 DBMS 支持的关系模型、网状模型或层次模型表示
  2. 子模式设计的目标是抽取或导出模式的子集，以构造不同用户使用的局部数据逻辑结构
  3. 编制应用程序设计说明的目的是为可实际运行的应用程序设计提供依据与指导，并作为设计评价的基础
  4. 设计评价的任务是分析并检验模式及子模式的正确性与合理性

2.4 物理设计

是指对于一个给定的数据库逻辑结构，研究并构造物理结构的过程，其具体任务主要是确定数据库在存储设备上的存储结构及存取方法，因 DBMS 的不同还可能包括建立索引和聚集，以及物理块大小、缓冲区个数和大小、数据压缩的选择等。

2.5 数据库实施

工作包括：加载数据、应用程序设计和数据库试运行。

2.6 数据库运行和维护

系统维护中最困难的工作是数据库重组与重构。

3. 关系数据库设计方法

关系数据库是一类采用关系模型作为逻辑数据模型的数据库系统，遵从数据库设计的基本步骤，包括：需求分析、逻辑结构设计、物理结构设计、数据库实施、数据库的运行和维护等阶段。

关系数据库的概念结构设计与逻辑结构设计是关系数据库整个设计过程的关键。

3.1 关系数据库设计过程与各级模式

在关系数据库设计的不同阶段，会形成数据库的各级模式：

  1. 在需求分析阶段，综合各个用户的应用需求
  2. 在概念结构设计阶段，形成独立于机器特点、独立于各个关系数据库管理系统产品的概念模式
  3. 在逻辑结构设计阶段，将 E-R 图转换成具体的数据库产品支持的关系数据模型，形成数据库逻辑模式，然后根据用户处理要求、安全性的考虑，在基本表的基础上再建立必要的视图，形成数据的外模式
  4. 在物理结构设计阶段，根据关系数据库管理系统的特点和处理的需要，进行物理存储安排，建立索引，形成数据库内模式

3.2 概念结构设计方法

关系数据库的概念结构设计通常采用自顶向下法，它通过两个步骤来完成概念设计，首先建立局部信息结构，然后将局部信息结构合成全局信息结构并优化，使用 E-R 图作为概念模式的描述工具。

E-R 图的表示方法：

概念结构设计就是将需求分析得到的用户需求抽象为信息结构的过程，通常使用 E-R 图来作为描述现实世界的建模工具。E-R 图提供了表示现实世界中实体、属性和联系的方法。

  1. 实体型，用矩形表示，写明实体的名称
  2. 属性，用椭圆形表示，并用无向边将其与相应的实体连接起来
  3. 联系，用菱形表示，写明联系的名称，用无向边分别与有关实体连接起来，同时在无向边旁标注联系的类型（1:1、1:N 或 M:N），如果一个联系具有属性，则这些属性也要用无向边与该联系连接起来

两个及两个以上的实体型之间和单个实体型内的联系，都会存在如下关系：

  • 一对一联系（1:1）
  • 一对多联系（1:N ）
  • 多对多联系（M:N）

局部信息结构设计：

根据需求分析报告中标明的不同用户视图范围所建立的满足该范围内用户需求的信息结构，称为局部信息结构。

局部信息结构的设计步骤包括确定局部范围、选择实体、选择实体关键字、确定实体间联系、确定实体的属性。

全局信息结构设计：

全局信息结构设计是将上述步骤中产生的所有局部信息结构合并成一个全局信息结构。

各局部 E-R 图之间的冲突主要表现在三个方面：

  1. 属性冲突

    属性域冲突和属性取值单位冲突

  2. 命名冲突

    同名异义或异名同义

  3. 结构冲突

    1. 同一对象在一个局部 E-R 图中作为实体，而在另一个局部 E-R 图中作为属性
    2. 同一实体在不同的 E-R 图中属性个数和类型不同
    3. 实体之间的联系在不同的 E-R 图中是不同的类型

3.3 逻辑结构设计方法

逻辑结构设计的任务是把概念结构设计产生的概念模型转换为具体的 DBMS 所支持的逻辑数据模型，也就是导出特定的 DBMS 可以处理的数据库逻辑结构。

通常包括三项工作：

  1. E-R 图向关系模型转换

    遵循原则如下：

    • 一个实体型转换为一个关系模式
    • 一个一对一联系可以转换为一个独立的关系模式
    • 一个一对多联系可以转换为一个关系模式
    • 一个多对多联系转换为一个关系模式
    • 三个多对多联系转换为一个关系模式
    • 具有相同码的关系模式可以合并

  2. 数据模型的优化

    方法如下：

    • 确定各属性之间的函数依赖关系
    • 对于各个关系模式的范式，根据实际需要确定最合适的范式
    • 判断每个关系模式的范式，根据实际需要确定最合适的范式
    • 按照需求分析阶段得到的处理要求，分析这些模式对于这样的应用环境是否合适，确定是否要对某些模式进行合并或分解
    • 对关系模式进行必要的分解，提高数据操作的效率和存储空间的利用率

  3. 设计用户子模式

    定义用户模式具体包括以下几个方面：

    • 可以通过视图机制在设计用户视图时，重新定义某些属性的别名，使其更符合用户的习惯，以方便使用
    • 可以对不同级别的用户定义不同的视图，以保证系统的安全性
    • 简化用户对系统的使用

3.4 物理设计方法

关系数据库系统的优点之一是用户通常不用进行数据的存储结构和存取方法的设计。

物理设计的任务主要是通过对关系建立索引和聚集来实现与应用相关数据的逻辑连接和物理聚集，以改善对数据库的存取效率。

建立索引：

索引的建立是通过 DBMS 提供的有关命令来实现的，用以建立索引的哪些属性也应是其所在关系中使用频率较高的属性，建立索引通常有静态和动态两种：

静态建立索引是指应用人员预先建立索引，一旦建立，后续的应用程序均可通过该索引存取数据，它多适合于用户较多且使用周期相对较长的数据。

动态建立索引是指应用人员在程序内外临时建立索引，它适合于单独用户或临时性使用要求情况。

建立聚集：

聚集是将相关数据集中存放的物理存储技术，借以提高 IO 的数据命中率而改善存取速度，其功能由具体的 DBMS 所提供，如 MySQL。

数据聚集结构的一种有效结构方式是块结构方式，块与块之间由指针连接，一个块对应于一个物理分区。

四、SQL 与关系数据库基本操作

1. SQL 概述

SQL 已经成为关系数据库的标准语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理数据库系统。功能不仅仅是查询，还包括数据定义、数据操纵和数据控制等用于数据库有关的一系列功能。

1.1 SQL 的发展

各个关系数据库管理系统产品在实现标准 SQL 时各有差别，与 SQL 标准的符合程度也不相同，但它们仍然遵循 SQL 标准。

1.2 SQL 的特点

  • SQL 不是某个特定数据库供应商专有的语言
  • 简单易学
  • 尽管看上去简单，但它实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作

1.3 SQL 的组成

四大功能：数据查询、数据定义、数据操纵和数据控制。

数据定义语言：

主要用于对数据库中的各种对象进行创建、删除、修改等操作。

数据库对象主要有表、默认约束、规则、视图、触发器、存储过程等。

包括 SQL 语句有：

  1. CREATE

    用于创建数据库或数据库对象

  2. ALTER

    用于对数据库或数据库对象进行更改

  3. DROP

    用于删除数据库或数据库对象

数据操纵语言：

主要用于操纵数据库中各种对象，检索和修改数据。

包括 SQL 语句主要有：

  1. SELECT

    用于从表或视图中检索数据，使用最为频繁的语句之一

  2. INSERT

    用于将数据插入到列表或视图中

  3. UPDATE

    用于修改表或视图中的数据，其既可修改表或视图中一行数据，也可同时修改多行或全部数据

  4. DELETE

    用于删除表或视图中的数据

数据控制语言：

主要用于安全管理。

主要 SQL 语句如下：

  1. GRANT

    用于授权，把语句许可或对象许可的权限授予其他用户和角色

  2. REVOKE

    用于收回权限

嵌入式和动态 SQL 规则：

规定了 SQL 语句在高级程序设计语言中使用的规范方法，以便适应较为复杂的应用。

SQL 调用和会话规则:

调用包括 SQL 例程和调用规则，以便提高 SQL 的灵活性、有效性、共享性以及使 SQL 具有更多的高级语言的特征。

2. MySQL 预备知识

是一个关系数据库管理系统（RDBMS），它具有客户/服务器体系结构。

2.1 MySQL 使用基础

两种架构方式：

  1. LAMP

    即使用 Linux 作为操作系统，Apache 作为 Web 服务器，MySQL 作为数据库管理系统，PHP、Perl 或 Python 语言作为服务器端脚本解释器。

  2. WAMP

    即使用 Windows 作为操作系统，Apache 作为 Web 服务器，MySQL 作为数据库管理系统，PHP、Perl 或 Python 语言作为服务器端脚本解释器。

2.2 MySQL 中的 SQL

作为一种关系数据库管理系统，遵循 SQL 标准，提供了对数据库定义语言 DDL、数据操纵语言 DML、数据控制语言 DCL 的支持，同样支持关系数据库的三级模式结构。

MySQL 中一个关系对应一个基本表，一个或多个基本表对应一个存储文件，一个表可以有若干索引，索引也存放在存储文件中。

MySQL 在 SQL 标准的基础上增加了部分扩展语言要素：包括常量、变量、运算符、表达式、函数、流程控制语句和注解等：

  1. 常量

    是指在程序运行过程中值不变的量，也称为字面值或标量值。常量的使用格式取决于值的数据类型，可分为字符串常量、数值常量、十六进制常量、时间日期常量、位字段值、布尔值和 NULL 值：

    • 字符串常量是指用单引号括起来的字符序列，分为 ASCII 字符串常量和 Unicode 字符串常量。
    • 数据值常量可以分为整数常量和浮点数常量。整数常量是不带小数点的十进制数。浮点数常量则是使用小数点的数值常量。
    • 一个十六进制值通常指定为一个字符串常量，每对十六进制数字被转换为一个字符，其最前面有一个大写字母 X 或小写字母 x。
    • 日期和时间常量是用单引号将表示日期时间的字符串括起来构成的。
    • 使用 b'value' 格式书写位字段值，Value 是一个用 0 和 1 书写的二进制值。
    • 布尔值的两个值：TRUE（数字1）和 FALSE （数值0）。
    • NULL 值通常用于表示 “没有值”、“无数据” 等意义，与数字类型的 “0” 或字符串类型的空字符串是完全不同的。

  2. 变量

    在 MySQL 中，变量分为用户变量和系统变量。用户变量前常添加一个符号 “@”，大多数系统变量前添加两个 “@” 符号。

  3. 运算符

    MySQL 几类编程语言中常用的运算符：

    • 算数运算符

      +（加）、-（减）、*（乘）、/（除）、%（取模）

    • 位运算符

      &（位与）、|（位或）、^（异位或）、~（位取反）、>>（位右移）、<<（位左移）

    • 比较运算符

      =（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）、!=（不等于）、<=>（相等或都等于空）

    • 逻辑运算符

      NOT 或 !（逻辑非）、AND 或 &&（逻辑与）、OR 或 ||（逻辑或）、XOR（逻辑异或）

  4. 表达式

    是常量、变量、列名、复杂计算、运算符和函数的组合，表达式可分为字符表达式、数值型表达式和日期表达式。

  5. 内置函数

    MySQL 包含了 100 多个函数，基本分类如下：

    • 数学函数，如 ABS()、SORT()
    • 聚合函数，如 COUNT()
    • 字符串函数，如 ASCII()、CHAR()
    • 日期和时间函数，如 NOW()、YEAR()
    • 加密函数，如 ENCODE()、ENCRYPT()
    • 控制流程函数，如 IF()、IFNULL()
    • 格式化函数，如 FORMAT()
    • 类型转换函数，如 CAST()
    • 系统信息函数，如 USER()、VERSION()

3. 数据定义

关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有数据库模式、表、索引、视图等。

SQL 标准提供的数据定义语句如下表：

操作对象 操作方式
创建 删除 修改
模式 CREATE SCHEMA 语句 DROP SCHEMA 语句
表 CREATE TABLE 语句 DROP TABLE 语句 ALTER TABLE 语句
视图 CREATE VIEW 语句 DROP VIEW 语句

3.1 数据库模式定义

  1. 创建数据库

    CREATE {DATABASE|SCHEMA}[IF NOT EXISTS] db_name
[DEFAULT]CHARACTER SET[=]charset_name | [DEFAULT]COLLATE[=]collation_name

  2. 选择数据库

    USE db_name;

  3. 修改数据库

    ALTER {DATABASE|SCHEMA}[db_name]
	alter_specification ...

  4. 删除数据库

    DROP {DATABASE|SCHEMA}[IF EXISTS]db_name

  5. 查看数据库

    SHOW {DATABASES|SCHEMAS}[LIKE 'pattern'|WHERE expr]

3.2 表定义

成功创建数据库之后，才能在数据库中创建数据表。

数据表被定义为字段的集合，数据在表中是按照行和列的格式来存储的，每一行代表一条记录，每一列代表记录中一个字段的取值。

  1. 创建表

    CREATE [TEMPORARY] TABLE tbl_name
(
字段名1 数据类型 [列级完整性约束条件][默认值]
[,字段名2 数据类型 [列级完整性约束条件][默认值]
......]
    [,表级完整性约束条件]
)[ENGINE=引擎类型]；

    • 临时表与持久表

      在创建表的 CREATE TABLE 语句中，若添加可选项 “TEMPORARY” 关键字，则表示使用该语句创建的表为临时表，而若不选用该关键字创建表则为持久表。

    • 数据类型

      指系统中所允许的数据的类型。主要的数据类型包括数值类型、日期和时间类型、字符串类型、空间数据类型等。

    • 关键字（AUTO_INCREMENT）

      可以为表中数据类型为整型的列设置为自增属性。

    • 指定默认值

      是指在向表插入数据时，如果没有明确给出某个表所对应的值，则 DBMS 此时允许为此表列指定一个值。

    • NULL 值

      NULL 值是指没有值或缺值。不能将 NULL 值与空串相混淆，NULL 是没有值的，它不是空串。

    • 主键

      是通过 PRIMARY KEY 关键字来指定的。主键值必须唯一，而且主键一定要为 NOT NULL。

  2. 更新表

    在 MySQL 中，可以使用 ALTER TABLE 语句来更改原有表的结构。例如可以增加或删减列、创建或取消索引、更改原有列的数据类型、重新命名列或表，还可以更改表的评注和引擎类型等，甚至还可以使用该语句为表重新创建触发器、存储过程、索引和外键等。

    • ADD[COLUMN] 子句

      增加新列，可同时增加多个列。

    • CHANGE[COLUMN] 子句

      修改表中列的名称或或数据类型。

    • ALTER[COLUMN] 子句

      需要修改或删除表中指定列的默认值。

    • MODIFY[COLUMN] 子句

      只会修改指定列的数据类型，而不会干涉它的列名。

    • DROP[COLUMN] 子句

      卸除表中多余的列。

    • RENAME[TO] 子句

      为表重新赋予一个表名。

  3. 重命名表

    RENAME TABLE tb1_name TO new_tb1_name[,tb1_name2 TO new_tb1_name2] ...

  4. 删除表

    DROP [TEMPORARY] TABLE [IF EXISTS] 
tb1_name[,tb1_name] ...
[RESTRICT | CASCADE]

  5. 查看表

    • 显示表的名称

      SHOW [FULL] TABLES [{FROM | IN} db_name]

    • 显示表的结构

      SHOW [FULL] COLUMNS {FROM | IN} tb_name [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
{DESCRIBE | DESC} tb1_name [co1_name | wild]

3.3 索引定义

索引是 DBMS 根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系，因而索引实质上是一张描述索引的列值与原表记录中记录行之间一一对应关系的有序表。

弊端：

  • 索引是以文件形式存储的，DBMS 会将一个表的所有索引保存在同一个索引文件中，索引文件需要占用磁盘空间。
  • 索引在提高查询速度的同时，会降低更新表的速度。

分类：

  1. 普通索引

    最基本的索引类型，没有任何限制。

  2. 唯一性索引

    索引列中的所有值都只能出现一次，必须是唯一的。

  3. 主键

    一种特殊的唯一性索引。

索引的创建：

  • 使用 CREATE INDEX 语句创建

    CREATE [UNIQUE] INDEX index_name ON tb1_name(index_col_name,...)

  • 使用 CREATE TABLE 语句创建

  • 使用 ALTER TABLE 语句创建

索引的查看：

SHOW {INDEX|INDEXES|KEYS} {FROM|IN} tb1_name [{FROM|IN} db_name][WHERE expr]

索引的删除：

  • 使用 DROP INDEX 语句

    DROP INDEX index_name ON tb1_name

  • 使用 ALTER TABLE 语句

4. 数据更新

添加若干行数据、修改表中的数据和删除表中若干行数据。

4.1 插入数据

  • 使用 INSERT …… VALUES 语句插入单行或多行元组数据

    INSERT [INTO] tb1_name [(col_name,……)] {VALUES|VALUE}({expr|DEFAULT},……),(……),……

  • 使用 INSERT …… SET 语句插入部分列值数据

    INSERT [INTO] tb1_name SET col_name = {expr|DEFAULT},……

  • 使用 INSERT …… SELECT 语句插入子查询数据

    INSERT [INTO] tb1_name [(col_name,……)] SELECT ……

4.2 删除语句

DELETE FROM tb1_name [WHERE where_condition][ORDER BY ……][LIMIT row_count]

4.3 修改数据

UPDATE tb1_name SET col_name1 = {expr1|DEFAULT}[,col_name2 = {expr2|DEFAULT}]……
[WHERE where_condition]
[ORDER BY ……]
[LIMIT row_count]

5 数据查询

是 SQL 语言的核心功能。

5.1 SELECT 语句

SELECT
	[ALL|DISTINCT|DISTINCTROW]
	select_expr[,select_expr ...]
	FROM table_references
	[WHERE where_condition]
	[GROUP BY {col_name|expr|position} [ASC|DESC], ... [WITH ROLLUP]]
	[HAVING where_condition]
	[ORDER BY {col_name|expr|position} [ASC|DESC], ...]
	[LIMIT {[offset,]row_count|row_count OFFSET offset}]

5.2 列的选择与指定

最简单的形式是 “SELECT select_expr”。

  1. 选择指定的列

    选择一个或多个表中的某个或某些列作为 SELECT 语句的查询列。

  2. 定义并使用列的别名

    column_name [AS] column_alias

  3. 替换查询结果集中的数据

    CASE
WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
...
ELSE 表达式
END [AS] column_alias

  4. 计算列值

    方法是将 SELECT 语句的语法项 “select_expr” 指定为对应列参与计算的表达式。

  5. 聚合函数

    SELECT 语句的语法项 “select_expr” 也可以指定为聚合函数。聚合函数通常是数据库系统中一类系统内置函数，常用于对一组值进行计算，然后返回单个值。它通常与 GROUP BY 子句一起使用。

    常用的聚合函数：

    函数名 说明
    COUNT 求组中项数，返回 INT 类型整数
    MAX 求最大值
    MIN 求最小值
    SUM 返回表达式中所有值的和
    AVG 求组中值的平均值
    STD/STDDEV 返回给定表达式中所有值的标准值
    VARIANCE 返回给定表达式中所有值的方差
    GROUP_CONCAT 返回由属于一组的列值连接组合而成的结果
    BIT_AND 逻辑与
    BIT_OR 逻辑或
    BIT_XOR 逻辑异或

5.3 FROM 子句与多表连接查询

SELECT 子句的查询对象是由 FROM 子句指定的，可根据用户的查询需求实现单表或多表查询。若一个查询同时涉及到一个或两个以上的表，则称之为多表连接查询，也称多表查询或连表查询。为了减少冗余，以及增强数据库的稳定性和灵活性，在表与表之间通过设置 “键” 的方式来保持多表之间的连接关系。多表连接查询是关系数据库中最主要的查询。

连接方式主要包括：

  1. 交叉连接

    又称笛卡尔积

  2. 内连接

    是一种最常用的连接类型，它是通过在查询中设置连接条件的方式，来移除查询结果集中某些数据行之后的交叉连接。

    通过 FROM 子句中的关键字 “INNER JOIN” 连接两张表：

    SELECT some_columns
FROM table1
INNER JOIN table2 ON some_conditions

    • 等值连接

      在 FROM 子句中使用关键字 “INNER JOIN” 或 “JOIN” 连接两张表时，如若在 ON 子句中的连接条件中使用运算符 “=”（即等号），即进行相等性测试，则此连接方式称为等值连接，也称为相等连接。在等值连接的条件设置中会包含一个主键和一个外键。

    • 非等值连接

      在 FROM 子句中使用关键字 “INNER JOIN” 或 “JOIN” 连接两张表时，如若在 ON 子句的连接条件中使用除运算符 “=” 之外的其他比较运算符，即进行不相等性测试，则此连接方式称为非等值连接，也称不等连接。

    • 自连接

      在 FROM 子句中使用关键字 “INNER JOIN” 或 “JOIN” 连接两张表时，可以将一个表与它自身进行连接，这种连接方式称为自连接。

  3. 外连接

    外连接是首先将两张表分为基表和参考表，然后再以基表为依据返回满足和不满足条件的记录。

    • 左外连接

      也称左连接。在 FROM 子句中使用关键字 “LEFT OUTER JOIN” 或关键字 “LEFT JOIN” 来连接两张表。

    • 右外连接

      也称右连接。在 FROM 子句中使用关键字 “RIGHT OUTER JOIN” 或关键字 “RIGHT JOIN” 来连接两张表。

5.4 WHERE 子句与条件查询

  1. 比较运算

    用于比较两个表达式的值：

    比较运算符 说明
    = 等于
    <> 不等于
    != 不等于
    < 小于
    <= 小于等于
    > 大于
    >= 大于等于
    <=> 不会返回 UNKNOW

  2. 判断范围

    • BETWEEN ... AND

      expression [NOT] BETWEEN expression1 AND expression2

    • IN

      expression IN (expression[,...])

  3. 判断空值

    expression IS [NOT] NULL

  4. 子查询

    • 表子查询

      子查询返回的结果集是一个表。

    • 行子查询

      子查询返回的结果集是带有一个或多个值的一行数据。

    • 列子查询

      子查询返回的结果集是一列数据，该列可以有一行或多行，但每行只有一个值。

    • 标量子查询

      即子查询返回的结果集仅仅是一个值。

    子查询与关键字 “IN” “EXIST” 和比较运算符结合使用：

    1. 结合关键字 “IN”

      expression [NOT] IN (subquery)

    2. 结合比较运算符使用

      expression {=|<=|>|>=|<=>|<>|!=}{ALL|SOME|ANY}(subquery)

    3. 结合关键字 “EXIST”

      EXIST(subquery)

5.5 GROUP BY 子句与分组数据

允许使用 GROUP BY 子句，将结果集中的数据行 genuine 选择列的值进行逻辑分组，以便能汇总表内容的子集，即实现对每个组的聚集计算。

GROUP BY {col_name|expr|position}[ASC|DESC],...[WITH ROLLUP]

使用需注意以下几点：

  1. GROUP BY 子句可以包含任意数目的列，使得其可对分组进行嵌套，为数据分组提供更加细致的控制。
  2. 如果在 GROUP BY 子句中嵌套了分组，那么将按 GROUP BY 子句中列的排列顺序的逆序方式依次进行汇总，并将在最后规定的分组上进行一个完全汇总。
  3. GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式，但是不能是聚合函数。
  4. 除聚合函数外，SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
  5. 如果用于分组的列中含有 NULL 值，则 NULL 将作为一个单独的分组返回。如果该列中存在多个 NULL 值，则将这些 NULL 值所在的行分为一组。

5.6 HAVING 子句

HAVING where_condition # where_condition 用于指定过滤条件

HAVING 子句与 WHERE 子句非常相似，HAVING 子句支持 WHERE 子句中所有的操作符和句法，但两者之间仍存在以下几点差异：

  1. WHERE 子句主要用于过滤数据行，而 HAVING 子句主要用于过滤分组，即 HAVING 子句可基于分组的聚合值而不是特定行的值来过滤数据。
  2. HAVING 子句中的条件可以包含聚合函数，而 WHERE 子句中不可以。
  3. WHERE 子句会在数据分组前进行过滤，HAVING 子句则会在数据分组后进行过滤。因而 WHERE 子句排除的行不包含在分组中，这就可能会改变计算值，从而影响 HAVING 子句基于这些值过滤掉的分组。

5.7 ORDER BY 子句

ORDER BY {col_name|expr|position}[ASC|DESC],...

5.8 LIMIT 子句

使用 LIMIT 子句来限制被 SELECT 语句返回的行数。

LIMIT {[offset,]row_count OFFSET offset}

6. 视图

外模式对应到数据库中的概念就是视图。视图是数据库中的一个对象，它是数据库管理系统提供给用户的以多种角度观察数据库中数据的一种重要机制。

视图仍不同于数据库中真实存在的基本表，它们存在以下区别：

  1. 视图不是数据库中真实的表，而是一张虚拟表。
  2. 视图的内容是由存储在数据库中进行查询操作的 SQL 语句来定义的，它的列数据与行数据均来自于定义视图的查询所引用的真实表，并且这些数据是在引用视图时动态生成的。
  3. 视图不是以数据集的形式存储在数据库中，它所对应的数据实际上是存储在视图所引用的真实表中。
  4. 视图是用来查看存储在别处的数据的一种虚拟表，而其自身并不存储数据。

使用视图有以下优点：

  1. 集中分散数据
  2. 简化查询语句
  3. 重用 SQL 语句
  4. 保护数据安全
  5. 共享所需数据
  6. 更改数据格式

6.1 创建视图

CREATE VIEW view_name[(column_list)] 
AS select_statement [WITH[CASCADED|LOCAL]CHECK OPTION]

6.2 删除视图

DROP VIEW [IF EXISTS]
view_name[,view_name]...
[RESTRICT|CASCADE]

6.3 修改视图定义

ALTER VIEW view_name[(column_list)]
AS select_statement [WITH[CASCADED|LOCAL]CHECK OPTION]

6.4 查看视图定义

SHOW CREATE VIEW view_name

6.5 更新视图数据

视图是一个虚拟表，通过插入、修改和删除等操作方式来更新视图中的数据，实质上是更新视图所引用的基本表中的数据。对于更新的视图，需要该视图中的行和基本表中的行之间具有一对一的关系。

  1. 使用 INSERT 语句通过视图向基本表插入数据

    # 例如在数据库 mysql_test 中，向视图 customer_view 插入一条记录：(909,'周明','M','武汉市','洪山区')
INSERT INTO mysql_test.customer_view VALUES(909,'周明','M','武汉市','洪山区')

  2. 使用 UPDATE 语句通过视图修改基本表的数据

    # 例如将视图 customers_view 中所有客户的 cust_address 列更新为 “上海市”
UPDATE mysql_test.customer_view SET cust_address = '上海市'

  3. 使用 DELETE 语句通过视图删除基本表的数据

    # 例如删除 “周明” 信息
DELETE FROM mysql_test.customer_view WHERE cust_name = '周明'

    对于依赖多个基本表的视图，不能使用 DELETE 语句。

6.6 查询视图数据

视图用于查询检索，主要体现的应用：

  • 利用视图简化复杂的表连接
  • 使用视图重新格式化检索出新的数据
  • 使用视图过滤不想要的数据

五、数据库编程

1 存储过程

1.1 存储过程的基本概念

存储过程是一组为了完成某项特定功能的 SQL 语句集，其实质上就是一段存储在数据库中的代码，它可以由声明式的 SQL 语句（如 CREATE、UPDATE 和 SELECT 等语句）和过程式 SQL 语句（如 IF ... THEN ... ELSE 控制结构语句）组成。

使用存储过程通常具有以下一些好处：

  • 可增强 SQL 语言的功能和灵活性
  • 良好的封装性
  • 高性能
  • 可减少网络流量
  • 存储过程可作为一种安全机制来确保数据库的安全性和数据的完整性

1.2 创建存储过程

DELIMITER 命令将 MySQL 语句的结束标志临时修改为其他符号，从而使得 MySQL 服务器可以完整地处理存储过程体中所有的 SQL 语句，而后可通过 DELIMITER 命令再将 MySQL 语句的结束标志改为 MySQL 的默认结束标志，即分号（;）。

DELIMITER 命令的使用语法格式：

DELIMITER $$

在 MySQL 中，是使用 CREATE PROCEDURE 语句来创建存储过程，其常用的语法格式：

CREATE PROCEDURE sp_name([proc_parameter[,...]])
Routine_body

其中，语法项 "proc_parameter" 的语法格式：

[IN|OUT|INOUT]param_name type

在此语法格式中：

  • 语法项 "sp_name" 用于指定存储过程的名称，且默认在当前数据库中创建。
  • 语法项 "proc_parameter" 用于指定存储过程的参数列表。
  • 语法项 "routine_body" 表示存储过程的主体部分，也称为存储过程体。

1.3 存储过程体

  1. 局部变量

    在存储过程体中可以声明局部变量，用来存储存储过程体中的临时结果。在 MySQL 中，可以使用 DECLARE 语句来声明局部变量，并且同时还可以对该局部变量赋予一个初始值，其使用语法格式是：

    DECLARE var_name[,...] type [DEFAULT value]

    其中，语法项 "var_name" 用于指定局部变量的名称；语法项 "type" 用于声明局部变量的数据类型；DEFAULT 子句用于为局部变量指定一个默认值，若没有指定，则默认为 NULL。

    注意事项如下：

    • 局部变量只能在存储过程体的 BEGIN ... END 语句块中声明
    • 局部变量必须在存储过程体开头处声明
    • 局部变量的作用范围仅限于声明它的 BEGIN .. END 语句块，其他语句块中的语句不可以使用它
    • 局部变量不同于用户变量，两者的区别是：局部变量声明时，在其前面没有使用 @ 符号，并且它只能被声明它的 BEGIN ... END 语句块中的语句所使用；而用户变量在声明时，会在其名称前面使用 @ 符号，同时已声明的用户变量存在于整个会话之中。

  2. SET 语句

    在 MySQL 中，可以使用 SET 语句为局部变量赋值，其使用的语法格式是：

    SET var_name = expr[,var_name = expr]...

  3. SELECT ... INTO 语句

    在 MySQL 中，可以使用 SELECT ... INTO 语句把指定列的值直接存储到局部变量中，其使用的语法格式是：

    SELECT col_name[,...] INTO var_name[,...] table_expr

    在此语格式中，语法项 "col_name" 用于指定列名；语法项 "var_name" 用于指定要赋值的变量名；语法项 "table_expr" 表示 SELECT 语句中的 FROM 子句及后面的语法部分。此外，需要注意的是，存储过程体中的 SELECT ... INTO 语句返回的结果集只能有一行数据。

  4. 流程控制语句

    • 条件判断语句

      常用的条件判断语句有 IF ... THEN ... ELSE 语句和 CASE 语句。它们的使用语法及方式类似于高级程序设计语言。

    • 循环语句

      常用的循环语句有 WHILE 语句、REPEAR 语句和 LOOP 语句。它们的使用语法及方式同样类似于高级程序设计语言。此外，循环语句中还可以使用 ITERATE 语句，但它只能出现在循环语句的 LOOP、REPEAT 和 WHILE 子句中，用于表示退出当前循环，且重新开始一个循环。

  5. 游标

    游标是一个被 SELECT 语句检索出来的结果集。

    具体步骤如下：

    1. 声明游标

      DECLARE cursor_name CURSOR FOR select_statement
# cursor_name 用于指定要创建的游标名称，其命名规则与表名相同
# select_statement 用于指定一个 SELECT 语句，其会返回一行或多行的数据，且需注意此处的 SELECT  语句不能有 INTO 子句

    2. 打开游标

      OPEN cursor_name
# cursor_name 用于指定要打开的游标

    3. 读取数据

      FETCH cursor_name INTO var_name[,var_name]...
# cursor_name 用于指定已打开的游标
# var_name 用于指定存放数据的变量名

    4. 关闭游标

      CLOSE cursor_name
# cursor_name 用于指定要关闭的游标

    使用游标需注意：

    • 游标只能用于存储过程或存储函数中，不能单独在查询操作中使用。
    • 在存储过程或存储函数中可以定义多个游标，但是在一个 BIGIN ... END 语句块中每一个游标的名字必须是唯一的。
    • 游标不是一条 SELECT 语句，是被 SELECT 语句检索出来的结果集。

1.4 调用存储过程

CALL sp_name[parameter[,...]]
CALL sp_name[()]
# sp_name 用于指定被调用的存储过程名称。如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。
# parameter 用于指定调用存储过程所要使用的参数。调用语句中参数的个数必须等于存储过程的参数个数。
# 当调用没有参数的存储过程时，使用 CALL sp_name() 语句与使用 CALL sp_name 语句是相同的。

1.5 删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name
# sp_name 用于指定要删除的存储过程名称

2. 存储函数

存储函数与存储过程的区别：

  • 存储函数不能有输出参数，这是因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。
  • 可以直接对存储函数进行调用，且不需要使用 CALL 语句；而对存储过程的调用，需要使用 CALL 语句。
  • 存储函数中必须包含一条 RETURN 语句，而这条特殊的 SQL 语句不允许包含于存储过程中。

2.1 创建存储函数

CREATE FUNCTION sp_name([func_parameter[,...]])
RETURNS type
routine body
# func_parameter 的语法格式是 param_name type
# sp_name 用于指定存储函数的名称，需注意，存储函数不能与存储过程具有相同名字。
# func_parameter 用于指定存储函数的参数，这里的参数只有名称和类型，不能指定关键字 "IN"、"OUT" 和 "INOUT"。
# RETURNS 子句用于声明存储函数的返回值的数据类型，其中 type 用于指定返回值的数据类型。
# routine body 用于指定存储函数的主题部分，也称为存储函数体。所有在存储过程中使用的 SQL 语句在存储函数中同样也适用，包括前面所介绍的局部变量、SET 语句、流程控制语句、游标等。但是，存储函数体中还必须包含一个 RETURN value 语句，其中 value 用于指定存储函数的返回值。

2.2 调用存储函数

成功创建存储函数后，就可以如同调用系统内置函数一样，使用关键字 SELECT 进行调用，其使用语法格式是：

SELECT sp_name([func_parameter[,...]])

2.3 删除存储函数

DROP FUNCTION [IF EXISTS] sp_name
# sp_name 用于指定要删除的存储函数的名称

六、数据库安全与保护

1. 数据库完整性

数据库完整性是指数据库中数据的正确性和相容性。

数据完整性约束是为了防止数据库中存在不符合语义的数据，为了维护数据的完整性，DBMS 必须提供一种机制来检查数据库中的数据，以判断其是否满足语义规定的条件。这些加在数据库之上的语义约束条件就是数据库完整性约束，而 DBMS 检查数据是否满足完整性约束条件的机制就称为完整性检查。

1.1 完整性约束条件的作用对象

完整性检查是围绕完整性约束条件进行的，因而完整性约束条件是完整性控制机制的核心。完整性约束条件的作用对象可以是列、元组和表。

  1. 列级约束

    主要是指对列的类型、取值范围、精度等的约束，具体包括如下内容：

    • 对数据类型的约束，其包括数据类型、长度、精度等
    • 对数据格式的约束
    • 对取值范围或取值集合的约束
    • 对空值的约束

  2. 元组约束

    指元组中各个字段之间的相互约束，例如某个活动的开始日期小于结束日期。

  3. 表级约束

    指若干元组之间、关系之间的联系的约束。

1.2 定义与实现完整性约束

关系模型的完整性规则是对关系的某种约束条件，有 3 类完整性约束：

  1. 实体完整性

    • 主键约束

      主键可以是表中的某一列，也可以是表中多个列所构成的一个组合。其中，由多个列组合而成的主键也称为复合主键。在 MySQL 中，主键列必须遵守如下一些规则：

      • 每一个表只能定义一个主键
      • 主键的值，也称为键值，必须能够唯一标识表中的每一行记录，且不能为 NULL
      • 复合主键不能包含不必要的多余列
      • 一个列名在复合主键的列表中只能出现一次

      主键约束可以在 CREATE TABLE 或 ALTER TABLE 语句中使用关键字 PRIMARY KEY 来实现，实现方式有两种：

      • 一种是作为列的完整性约束，此时只需在表中某个列的属性定义后加上关键字 PRIMARY KEY 即可
      • 一种是作为表的完整性约束，需要在表中所有列的属性定义后添加一条 PRIMARY KEY(index_col_name,...) 格式的句子

    • 候选键约束

      候选键可以是表中的某一列，也可以是表中的多个列所构成的一个组合。任何时候，候选键的值必须是唯一的，且不能为 NULL。候选键可以在 CREATE TABLE 或 ALTER TABLE 语句中使用关键字 UNIQUE 来定义，其实现方法与主键约束相似，同样可以作为列或表的完整性约束两种方式。

      MySQL 中候选键与主键之间存在以下几点区别：

      • 一个表中只能创建一个主键，但可以定义若干个候选键
      • 定义主键约束时，系统会自动产生 PRIMARY KEY 索引，而定义候选键约束时，系统自动产生 UNIQUE 索引

  2. 参照完整性

    在 MySQL 中，参照完整性是通过在创建表 CREATE TABLE 或更新表 ALTER TABLE 的同时定义一个外键声明来实现的。其中，外键声明有两种方式：

    • 在表中某个列的属性定义后直接加上 reference_definition 语法项
    • 在表中所有列的属性定义后添加 FOREIGN KEY(index_col_name,...) reference_definition 子句的语法项

    reference_definition 语法项的定义：

    REFERENCES tb1_name(index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
# tb1_name 指定外键所参照的表名，这个表称为被参照表（或父表），而外键所在的表称为参数表（或子表）
# col_name 指定被参照表的列名
# ON DELETE、ON UPDATE 指定参照动作相关的 SQL 语句
# reference_option 指定参照完整性约束的实现策略

    index_col_name 的语法格式：

    RESTRICT|CASCADE|SET NULL|NOT ACTION

    在指定外键时，需要遵守以下规则：

    • 被参照表必须已经用一条 CREATE TABLE 语句创建了，或者必须是当前正在创建的表。如若是后一种情形，则被参照表与参照表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性
    • 必须为被参照表定义主键
    • 必须在被参照表的表名后面指定列名或列名的组合。这个列或列名的组合必须是这个被参照表的主键或候选键
    • 尽管主键是不能够包含空值的，但允许在外键出现一个空值。这意味着只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的
    • 外键中的列的数目必须和被参照表的主键中的列的数目相同
    • 外键中的列的数据类型必须和被参照表的主键中的对应列的数据类型相同

  3. 用户定义的完整性

    • 非空约束

      在 MySQL 中，非空约束可以通过在 CREATE TABLE 或 ALTER TABLE 语句中的某个列定义后面，加上关键字 NOT NULL 作为限定词，来约束该列的取值不能为空。

    • CHECK 约束

      与非空约束一样，CHECK 约束也是在创建表或更新表的同时，根据用户的实际完整性要求来定义的，语法格式是：

      CHECK(expr)
# expr 是一个 SQL 表达式，用于指定需要检查的限定条件

1.3 命名完整性约束

CONSTRAINT[symbol]
# symbol 是指定的约束名字，这个名字是在完整性约束前面被定义，其在数据库里必须是唯一的。倘若没有明确给出约束名字，则 MySQL 自动创建一个约束名字。

1.4 更新完整性约束

当对各种约束进行命名后，就可以使用 ALTER TABLE 语句来更新与列表有关的各种约束，需注意：

  • 完整性约束不能直接被修改。若要修改某个约束，实际上是用 ALTER TABLE 语句先删除该约束，然后再增加一个与该约束同名的新约束
  • 使用 ALTER TABLE 语句，可以独立地删除完整性约束，而不会删除表本身。若使用 DROP TABLE 语句删除一个表，则表中所有的完整性约束都会自动被删除

2. 触发器

触发器是用户定义在关系表上的一类由事件驱动的数据库对象，也是一种保证数据完整性的方法。触发器一旦定义，无需用户调用，任何表的修改操作均由数据库服务器自动激活相应的触发器。触发器与表的关系十分密切，其主要作用是实现主键和外键不能保证复杂的参照完整性和数据一致性，从而有效地保护表中的数据。

2.1 创建触发器

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tb1_name FOR EACH ROW trigger_body
# trigger_name 用于指定触发器的名称，触发器在当前数据库必须具有唯一的名称，如果要在某个特定数据库中创建，名称前面应加上数据库的名称
# trigger_time 指定触发器的触发时刻，它有两个选项，即关键字 BEFORE 和 AFTER，用于表示触发器是在激活它的语句之前或之后触发。如果希望验证新数据是否满足使用限制，则使用 BEFORE 选项；如果希望在激活触发器的语句执行之后完成几个或更多的改变，通常使用 AFTER 选项
# trigger_event 指定触发事件，即指定激活触发器的语句的种类，其可以是下述值之一：关键字 INSERT，表示将新的数据行插入到表时激活触发器；关键字 UPDATE，表示更改表中某一行数据时激活触发器；关键字 DELETE，表示从表中删除某一行时激活触发器
# tb1_name 指定与触发器相关联的表名，必须引用永久性表，不能将触发器与临时表或视图关联起来，且同一个表不能拥有两个具有相同触发时刻和事件的触发器
# FOR EACH ROW 指定对于受触发事件影响的每一行都要激活触发器的动作
# trigger_body 指定触发器动作主体，即包含触发器激活时将要执行的 MySQL 语句，如果要执行多个语句，可使用 BEGIN ... END 复合语句结构

2.2 删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.] trigger_name
# IF EXISTS 用于避免在没有触发器的情况下删除触发器
# schema_name 指定触发器所在的数据库的名称，若没有指定，则为当前默认数据库
# trigger_name 指定要删除的触发器名称

2.3 使用触发器

  • INSERT 触发器

    可在 INSERT 语句执行前后执行，使用时需注意：

    1. 在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表，来访问被插入的行
    2. 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值
    3. 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的是 0 值，在 INSERT 执行之后将包含新的自动生成值

  • DELETE 触发器

    可在 DELETE 语句执行前后执行，使用时需注意：

    1. 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表，来访问被删除的行
    2. OLD 中的值全部都是只读的，不能被更新

  • UPDATE 触发器

    可在 UPDATE 语句执行前后执行，使用时需注意：

    1. 在 UPDATE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表访问 UPDATE 语句执行前的值，也可以引用一个名为 NEW（不区分大小写）的虚拟表访问更新的值
    2. 在 BEFORE UPDATE 触发器中，NEW 中的值也可能被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）
    3. OLD 中的值全部是只读的，不能被更新
    4. 当触发器涉及对触发表自身的更新操作时，只能用 BEFORE UPDATE 触发器，而 AFTER UPDATE 触发器将不被允许

3. 安全性与访问控制

3.1 用户账号管理

MySQL 的用户账号及相关信息都存储在一个名为 mysql 的 MySQL 数据库中，这个数据库里有一个名为 user 的数据表，包含了所有的用户账号，并且它用一个名为 user 的列存储用户的登录名。

# 查看 MySQL 数据库的使用账号
select user from mysql.user

  • 创建用户账号

    CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
# user 指定创建用户账号，其格式为 'user_name'@'host_name'，其中，user_name 表示用户名，host_name 表示主机名，即用户连接 MySQL 时所在主机的名字。如果在创建过程中，只给出了账户中的用户名，而没指定主机名，则主机名会默认为是 “ ”，其表示一组主机
# IDENTIFIED BY 是可选项，指定用户账号对应的口令，若该用户账号无口令，则可省略此子句
# PASSWORD 是可选项，指定散列口令，即若使用明文设置口令时，而忽略 PASSWORD 关键字；如果不想以明文设置口令，且知道 PASSWORD() 函数返回给密码的散列值，则可以在此口令设置语句中指定此散列值，但需加上关键字 PASSWORD
# password 指定用户账号的口令，其在 IDENTIFIED BY 关键字或 PASSWORD 关键字之后，设置的口令值可以只有字母和数字组成的明文，也可以是通过 PASSWORD() 函数得到的散列值

    在使用时需注意：

    1. 要使用 CREATE USER 语句，必须拥有 MySQL 中 mysql 数据库的 INSERT 权限或全局 CREATE USER 权限
    2. 使用 CREATE USER 语句创建一个用户账号后，会在系统自身的 mysql 数据库的 user 表中添加一条新记录，如果创建的账户已存在，则语句执行会出现错误
    3. 如果两个用户具有相同的用户名和不同的主机名，MySQL 会将他们视为不同的用户，并允许为这两个用户分配不同的权限集合
    4. 如果在 CREATE USER 语句的使用中，没有为用户指定口令，那么 MySQL 允许该用户可以不使用口令登录系统，然而从安全角度来说，不推荐这种做法
    5. 新创建用户拥有的权限很少，他们可以登录到 MySQL，只允许进行不需要权限的操作，比如使用 SHOW 语句查询所有存储引擎和字符集的列表等，不能使用 USE 语句来让其他用户已经创建了任何数据库成为当前数据库，因而无法访问那些数据库的表

  • 删除用户

    删除一个或多个用户账号及相关权限

    DROP USER user[,user]...

    使用时需注意：

    1. DROP USER 语句可用于删除一个或多个 MySQL 账户，并消除其权限
    2. 要使用 DROP USER 语句，必须拥有 MySQL 中 mysql 数据库的 DELETE 权限或全局 CREATE USER 权限
    3. 在 DROP USER 语句使用中，如果没有明确地给出账户的主机名，则该主机名默认为是 “ ”
    4. 用户的删除不会影响到他们之前所创建的表、索引或其他数据库对象，这是因为 MySQL 并没有记录是谁创建了这些对象

  • 修改用户账号

    RENAME USER old_user TO new_user[,old_user TO new_user]...

    使用时需注意：

    1. RENAME USER 语句对于原有 MySQL 账户进行重命名
    2. 要使用 RENAME USER 语句，必须拥有 MySQL 中的 mysql 数据库的 UPDATE 权限或全局 CRETAE USER 权限
    3. 倘若系统中旧帐户不存在或者新账户已存在，则语句执行会出现错误

  • 修改用户口令

    SET PASSWORD[FOR user]=
{
PASSWORD('new_password')|'encrypted password'
}

    使用时需注意：

    1. 若不加上 FOR 子句，表示修改当前用户的口令；若加上 FOR 子句，表示修改账户为 user 的用户口令，其中 user 的格式必须以 'user_name'@'host_name' 的格式给定，user_name 为账户的用户名，host_name 为账户所在的主机名，改账户必须在系统中存在，否则语句执行会出现错误
    2. 只能使用选项 PASSWORD('new_password') 和 'encrypted password' 中的一项，且必须使用其中的某一项

3.2 账户权限管理

  • 权限的授予

    GRANT Pri_type[(column_list)][,pri_type[(column_list)]]...
ON [object_type] priv_level
TO user_specification[,user_specification]...
[WITH GRANT OPTION]
# pri_type 指定权限的名称
# column_list 指定权限要授予给表中的哪些具体的列
# ON 指定权限授予的对象和级别
# object_type 指定权限授予的对象类型，包括表、函数和存储过程，分别用关键字 TABLE、FUNCTION 和 PROCRDURE 标识
# priv_level 指定权限的级别，其可以授予的权限有这样几个：列权限、表权限、数据库权限和用户权限。相应的，在 GRANT 语句中可用于指定权限级别的值有这样几类格式：“*” 表示当前数据库中的所有表；“*.*” 表示所有数据库中的所有表；“db_name*” 表示某个数据库中的所有表，db_name 指定数据库名；“db_name.tb1_name” 表示某个数据库中的某个表或视图，db_name 指定数据库名，tb1_name 指定表名或视图名；“db_name.routine_name” 表示某个数据库中的某个存储过程或函数，rountime_name 指定存储过程名或函数名
# TO 用来设定用户的口令，以及被授予权限的用户 user。若在 TO 子句中给系统中存在的用户指定口令，则新密码会将原密码覆盖；如果权限被授予给一个不存在的用户，MySQL 会自行执行一条 CREATE USER 语句来创建这个用户，但同时必须为该用户指定口令。由此可见，GRANT 语句也可用于创建用户账号
# user_specification 是 TO 子句中的具体描述部分，其常用的语法格式是：user[IDENTIFIED BY[PASSWORD] 'password']
# WITH 子句为可选项，实现权限的转移或限制

    使用时需注意：

    1. 授予表权限时，语法项 priv_type 可以指定为以下值：

      • SELECT

        授予用户可以使用 SELECT 语句访问特定的表的权限

      • INSERT

        授予用户使用 INSERT 语句向一个特定的表中添加数据行的权限

      • DELETE

        授予用户可以使用 DELETE 语句向一个特定表中删除数据行的权限

      • UPDATE

        授予用户可以使用 UPDATE 语句修改特定数据表中值的权限

      • REFERENCES

        授予用户可以创建一个外键来参照特定数据表的权限

      • CREATE

        授予用户可以使用特定的名字创建一个数据表的权限

      • ALTER

        授予用户可以使用 ALTER TABLE 语句修改数据表的权限

      • INDEX

        授予用户可以在表上定义索引的权限

      • DROP

        授予用户可以删除数据表的权限

      • ALL 或 ALL PRIVILEGES

        表示所有的权限名

    2. 授予权限时，语法项 priv_type 的值只能指定为 SELECT、INSERT 和 UPDATE，同时权限的后面需要加上列名列表 column_list

    3. 授予数据库权限时，语法项 priv_type 可以指定为以下值：

      • SELECT

        授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限

      • INSERT

        授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限

      • DELETE

        授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限

      • UPDATE

        授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限

      • REFERENCES

        授予用户可以创建指向特定数据库中表外键的权限

      • CREATE

        授予用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限

      • ALTER

        授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限

      • INDEX

        授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限

      • DROP

        授予用户可以删除特定数据库中所有表和视图的权限

      • CREATE TEMPORARY TABLES4

        授予用户可以在特定数据库中创建临时表的权限

      • CREATE VIEW

        授予用户可以在特定数据库中创建新的视图的权限

      • SHOW VIEW

        授予用户可以查看特定数据库中已有视图的视图定义的权限

      • CREATE ROUTINE

        授予用户可以为特定数据库创建存储过程和存储函数等权限

      • ALTER ROUTINE

        授予用户可以更新和删除数据库中已有的存储过程和存储函数等权限

      • EXECUTE ROUTINE

        授予用户可以调用特定数据库的存储过程和存储函数的权

      • LOOK TABLES

        授予用户可以锁定特定数据库的已有数据表的权限

      • ALL 或 ALL PRIVILEGES

        表示所有权限名

    4. 最有效率的权限是用户权限。授予权限时，语法项 priv_type 除了可以指定为授予数据库权限时的所有值之外，还可以是下面这些值：

      • CREATE USER

        授予用户可以创建和删除新用户的权限

      • SHOW DATABASES

        授予用户可以使用 SHOW DATABASES 语句查看所有已有数据库定义的权限

  • 权限的转移

    权限转移可以通过 GRANT 语句中使用 WITH 子句来实现。如果将 WITH 子句指定为关键字 WITH GRANT OPTION，则表示 TO 子句中所指定的所有用户都具有把自己所拥有的权限给其他用户的权力，而无论那些其他用户是否拥有该权限。

  • 用户的撤销

    # 回收用户的某些权限
REVOKE priv_type[(column_list)][,priv_type[(column_list)]]...
ON [object_type] priv_level
FROM user[,user]...
# 回收用户的所有权限
REVOKE ALL PRIVILEGES,GRANT OPTION FROM user[,user]...

4. 事务与并发控制

4.1 事务的概念

  • 所谓事务就是用户定义的一个数据操作序列，这些操作可作为一个完整地工作单元，要么全部执行，那么全部不执行，是一个不可分割的工作单元
  • 事务与程序的不同：程序是静止的，事务是动态的，是程序的执行而不是程序本身；同一程序的多个独立执行可以同时进行，每一步执行则是一个不同的事务
  • 在 SQL 中，用户显示定义事务的语句一般有三条：BEGIN TRANSACTION、COMMIT 和 ROLLBACK，且事务通常是以 BEGIN TRANSACTION 语句开始，以 COMMIT 语句或 ROLLBACK 语句结束
  • 事务中的操作一般是对数据的更新操作，包括增、删、改

4.2 事务的特征

  • 原子性

    事务的原子性保证事务包含的一组更新操作是原子不可分的，即事务是不可分割的最小工作单元，所包含的这些操作是一个整体

  • 一致性

    一致性要求事务必须满足数据库的完整性约束，且事务执行完毕后将数据库由一个一致性状态转变为另一个一致性状态。其中，数据库的一致性状态是一种以一致性规则为基础的逻辑属性

  • 隔离性

    隔离性要求事务是彼此独立的、隔离的，即一个事务的执行不能被其他事务所干扰，一个事务对数据库变更的结果必须在它 COMMIT 后，另一个事务才能存取

  • 持续性

    持续性也称为持久性，是指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，且接下来的其他操作或故障不应该对其执行结果有任何影响

4.3 并发操作问题

事务是并发控制的基础单位，保证事务的 ACID 特征是事务处理的重要任务，而事务的 ACID 特征可能遭到破坏的原因之一是多个事务对数据库的并发操作。为了保证事务的隔离性和一致性，DBMS 需要对并发操作进行正确调度。其中，完整性校验可以保证一个事务单独执行时，若输入的数据库状态是正确的，则其输出的数据库状态也是正确的。但当多个事务交错执行时，可能出现不一致问题，这也都称为并发操作问题，典型的有如下三种：

  1. 丢失更新

    设有两个事务 T1 和 T2，当它们同时读入同一数据并加以修改时，事务 T2 的提交结果会破坏事务 T1 的提交结果，由此导致事务 T1 的修改被丢失。这就是一种由于对数据的并发操作而带来的数据不一致

  2. 不可重复读

    包括三种情况：

    • 事务 T1 读取某一数据后，事务 T2 对其做了修改，当事务 T1 再次读该数据时，得到与前一次不同的值
    • 事务 T1 按一定条件从数据库中读取了某些数据记录后，事务 T2 删除了其中部分记录，当事务 T1 再次按相同条件读取数据时，发现某些记录神秘的消失了
    • 事务 T1 按一定条件从数据库中读取某些数据记录后，事务 T2 插入了一些记录，当事务 T1 再次按相同条件读取数据时，发现多了一些记录

  3. 读脏数据

    设有两个事务 T1 和 T2，读脏数据是指事务 T1 修改某一数据，并将其写回磁盘，事务 T2 读取同一数据后，事务 T1 由于某种原因被撤销，这时事务 T1 已修改过的数据恢复原值，事务 T2 读到的数据就与数据库中的数据不一致，则事务 T2 读到的数据称为脏数据，即不正确的数据。

解决并发控制所带来的数据不一致问题的方法有封锁、时间戳、乐观控制法和多版本并发控制等。

4.4 封锁

封锁是常用的并发控制技术，它的基本思想是：需要时，事务通过向系统请求对它所希望的数据对象加锁，以确保它不被非预期改变。

  • 锁

    一个锁实际上就是允许或组织一个事务对一个数据对象的存取特权。基本的封锁类型有两种：排他锁和共享锁。

  • 用封锁进行并发控制工作原理：

    1. 若事务 T 对数据 D 加了写锁，则所有别的事务对数据 D 的锁请求都必须等待直到事务 T 释放锁
  1. 若事务 T 对数据 D 加了读锁，则别的事务还可以对数据 D 请求读锁，而对数据 D 的写锁请求必须等待直到事务 T 释放锁
  2. 事务执行数据库操作时都要先请求相应的锁，即对读请求读锁，对更新（插入、修改、删除）请求写锁。这个过程一般是由 DBMS 在执行操作时自动隐式的进行
  3. 事务一致占有获得的锁直到结束（COMMIT 或 ROLLBACK）时释放

因此，利用封锁机制可以解决并发操作所带来的三个不一致问题。

  • 封锁的粒度

    以粒度来描述封锁的数据单元大小。DBMS 可以决定不同粒度的锁，由最底层的数据元素到最高层的整个数据库，粒度越细，并发性就越大，但软件复杂性和系统开销也就越大。

  • 封锁的级别

    封锁的级别又称为一致性级别或隔离度，由各种锁的类型与其封锁期限组合可形成不同的封锁级别：0~3 级封锁。

  • 活锁与死锁

    在并发事务处理过程中，由于锁会使一事务处于等待状态而调度其他事务处理，因而该事务可能会因优先级低而永远等待下去，这种现象称为 “活锁”。活锁问题的解决与调度算法有关，一种最简单的办法是 “先来先服务”。两个以上事务循环等待被同组中另一事务锁住的数据单元的情形，称为 “死锁”，DBMS 需要提供提供死锁预防、死锁检测和死锁发生后的处理技术与方法。预防死锁的办法在操作系统中已普遍讨论，其主要有以下几种：

    1. 一次性锁请求
    2. 锁请求排序
    3. 序列化处理
    4. 资源剥夺

  • 可串行性

    一组事务的一个调度就是它们的基本操作的一种排序。若在一个调度中，对于任意两个事务 T1 和 T2，要么 T1 所有操作都在 T2 所有操作之前，要么反之，则该调度是串行的，因而是正确的。

    通常，在数据库系统中，可串行性就是并发执行的正确性准则，即当且仅当一组事务的并发执行调度是可串行化，才认为它们是正确的。

  • 两段封锁法

    采用两段封锁法是一种最简单而有效的保障封锁其调度是可串行的方法，两段锁协议规定在任何一个事务中，所有加锁操作都必须在所有释放锁操作之前。其中，事务划分为如下两个阶段：

    1. 发展（Growing）或加锁阶段
    2. 收缩（Shrinking）或释放锁阶段

    定理：遵循两段锁协议的事务的任何并发调度都是可串行化的。

5. 备份与恢复

会造成数据库运行事务异常中断的因素可能是：

  • 计算机硬件故障
  • 计算机软件故障
  • 病毒
  • 认为误操作
  • 自然灾害
  • 盗窃

备份与恢复策略来保证数据库中数据的可靠性与完整性。数据备份是指通过导出数据或者复制表文件的方式制作数据库的副本；数据库恢复则是当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。数据库的恢复是以备份为基础的，它是与备份相对应的系统维护和管理操作。

5.1 使用 SELECTION ... OUTFILE 语句备份数据

SELECT * INTO OUTFILE 'file_name' export_options|INTO DUMPFILE 'file_name'
# export_options 的格式
[FIELDS
 [TERMINATED BY 'string']
 [[OPTIONALLY]ENCLOSED BY 'char']
 [ESCAPED BY 'char']
]
[LINES TERMINATED BY 'string']
# file_name 指定数据备份文件的名称
# 导出语句中使用关键字 OUTFILE 时，可以在语法项 export_options 中加入两个自选的子句，即 FIELDS 子句和 LINES 子句，它们的作用是决定数据行在备份文件中的存储格式。如果 FIELDS 子句和 LINES 子句都不指定，则默认声明的是子句 FIELDS TERMINATED BY '\t' ENCLOSED BY ESCAPED BY '\\' LINES TERMINATED BY '\n'
# FIELDS 子句中有三个亚子句，分别是 TERMINATED BY 子句、[OPTIONALLY]ENCLOSED BY 子句和 ESCAPED BY 子句。如果指定了 FIELDS 子句，则这三个亚子句中至少要求指定一个。其中，TERMINATED BY 子句用来指定字段之间的符号。
# 在 LINES 子句中使用关键字 TERMINATED BY 指定一个数据行结束的标志
# 导出语句中使用的是关键字 DUMPFILE 而非 OUTFILE 时，导出的备份文件里面所有的数据行都会彼此紧挨着放置，即值和行之间没有任何标记。

5.2 使用 LOAD DATA ... INFILE 语句恢复数据

LOAD DATA INFILE 'file_name.txt'
INTO TABLE tb1_name
[FIELDS
 [TERMINATED BY 'string']
 [[OPTIONALLY] ENCLOSED BY 'char']
 [ESCAPED BY 'char']
]
[LINES
 [STARTING BY 'string']
 [TERMINATED BY 'string']
]
# file_name 指定待导入的数据库备份文件名，文件中保持了待载入数据库的所有数据行
# tb1_name 指定需要导入的数据库的表名
# 此处的 FIELDS 子句和 SELECT * INTO OUTFILE 语句中的 FIELDS 子句类似，用于判断字段之间的数据行之间的符号
# 语法项 LINES 子句中的 TERMINATED BY 亚子句用来指定一行结束的标志；STARTING BY 亚子句则指定一个前缀，导入数据行时，忽略数据行中的该前缀与前缀之前的内容。如果某行不包括该前缀，则整个数据行被跳过。

七、数据库管理技术的发展

1. 数据库技术发展概述

  • 数据库是管理数据的技术，它从 20 世纪 60 年代中期产生到今天，经历了三代演变，造就了 C.W.Bachman、E.F.Codd 和 James Gray 三位图灵奖得主，发展了以数据建模和数据管理系统核心技术为主，内容丰富、领域宽广的一门学科，带动了一类巨大的软件产业---数据库管理系统产品及其相关工具和解决方案。
  • 数据库技术是计算机科学技术中发展最快的领域之一，也是应用最广的技术之一，它已成为计算机信息系统与智能应用系统的核心技术和重要基础。
  • 数据模型是数据库系统的核心和基础。以数据模型的发展为主线，数据库技术可以相应地分为三个发展阶段，即第一代的网状、层次数据库系统，第二代的关系数据库系统，以及新一代的数据库系统。

1.1 第一代数据库系统

  • 层次数据库系统和网状数据库系统的关系

    两者的数据模型虽然分别为层次模型和网状模型，但实质上层次模型是网状模型的特例。它们都是格式化模型，它们从体系结构、数据库语言到数据存储管理均具有共同特征，是第一代数据库系统。

  • 第一代数据库系统两类代表

    1. 1969 年 IBM 公司研制的层次模型数据库管理系统 IMS
    2. 美国数据库系统语言研究会下属的数据库任务组于 20 世纪 60 年代末 70 年代初提出了 DBTG 报告。DBTG 报告确定并建立数据库系统的许多概念、方法和技术，是网状模型数据库系统的典型代表。

  • 两类数据库系统具有的共同特点

    1. 支持三级模式的体系结构
    2. 用存取路径来表示数据之间的关系
    3. 独立的数据定义语言
    4. 导航的数据操纵语言

1.2 第二代数据库系统

  • 支持关系数据模型的关系数据库系统是第二代数据库系统
  • 1970 年，IBM 公司 San Jose 研究室的研究院 E.F.Codd 在其发表的论文中提出了数据库的关系模型，开创了数据库关系方法和关系数据理论的研究，为关系数据库技术奠定了理论基础。20 世纪 70 年代是关系数据库理论研究和原型开发的时代，其主要成果有：
    1. 奠定了关系模型的理论基础，给出了人们一致接受的关系模型的规范说明
    2. 研究了关系数据语言，有关系代数、关系演算、SQL 语言及 QBE 等
    3. 研制了大量的 RDBMS 的原型，攻克了系统实现中查询优化、并发控制、故障恢复等一系列关键技术
  • 在计算机领域，有人把 20 世纪 70 年代称为数据库时代。关系数据库是以关系模型为基础的，而关系模型是由数据结构、关系操作和数据完整性三部分组成。关系模型不仅简单、清晰，而且由关系代数作为语言模型，由关系数据理论作为理论基础
  • 第二代关系数据库系统具有模型简单清晰、理论基础好、数据独立性强、数据库语言非过程化和标准化等特点

1.3 新一代数据库系统

  • 从 20 世纪 80 年代开始，数据库界广泛开展了面向对象数据库系统（OODBS）的研究。从面向对象（OO）模型、面向对象数据库管理系统（OODBMS）实现技术、OODBMS 产品研发和应用等各个层面进行了大量的创新工作。但最终 OODBMS 未能替代关系数据库系统，成为第三代数据库系统的代表
  • 第三代数据库系统应具有的三特征
    1. 应支持数据管理、对象管理和知识管理
    2. 必须保持或继承第二代数据库系统的技术
    3. 必须对其他系统开放
  • 通常把第二代以后的数据库系统称为新一代数据库系统。新一代数据库系统将是以更加丰富的数据模型和更大的数据管理功能为特征，从而满足广泛更加复杂的新应用的要求
  • 数据库技术于其他计算机技术相结合，是数据库技术发展的一个显著特征

2. 数据仓库与数据挖掘

2.1 从数据库到数据仓库

  • 计算机系统中存在着两类不同的数据处理工作：一类是操作型处理，也称联机事务处理，另一类是分析型处理，也称联机分析处理。

    事务型处理数据和分析型处理数据的区别：

    特性 OLTP OLAP
    特征 操作处理 信息处理
    面向 事务 分析
    用户 办事员、DBA、数据库专业人员 知识工人（如经理、主管、分析员）
    功能 日常操作 长期信息需求，决策支持
    DB 设计 基于 E-R，面向应用 星形/雪花，面向主题
    数据 当前的，确保最新 历史的，跨时间维护
    汇总 原始的，高度详细 汇总的，统一的
    视图 详细，一般关系 汇总的，多维的
    工作单位 短的、简单事务 复杂查询
    存取 读写 大多为读
    关注 数据进入 信息输出
    操作 主关键字上索引/散列 大量扫描
    访问记录数 数十个 数百万个
    用户数 数千 数百
    DB 规模 100 MB 到 GB 100 GB 到 TB
    优先 高性能、高可用性 高灵活性，端点用户自治
    度量 事务吞吐量 查询吞吐量，响应时间

  • 数据仓库的建立将操作型处理和分析型处理区分开来。传统的数据库技术为操作型处理服务，数据仓库为分析性处理服务。

  • 1992 年数据仓库概念的创始人 W.H.Inmon 在其《Building the Data Warehouse》一书中定义了数据仓库的概念：数据仓库是面向主题的、集成的、稳定的、随时间变化的数据集合，用以支持管理决策的过程。

  • 数据仓库的特征：

    1. 面向主题
    2. 集成性
    3. 数据的非易失性
    4. 数据的时变性

  • 数据仓库三个常用的重要概念

    1. 粒度

      指数据仓库的数据单位中保存数据的细化或综合程度的级别，细化程度越高，粒度级就越小，相反地，细化程度越低，粒度级就越大。

    2. 分割

      是将数据分散到各自的物理单元中，以便能分别处理，以提高数据处理的效率。数据分割后的单元称为切片。

    3. 维

      是人们观察数据的特定角度，是考虑问题时的一类属性。此类属性的集合构成了一个维度，例如时间维、产品维等。

  • 数据仓库有时也称企业仓库。建设数据仓库是一项庞大的系统工程，为此，人们提出数据集市体系结构的数据仓库概念。数据集市分为独立的数据集市和从属的数据集市或两种数据集市的混合。

2.2 数据挖掘技术

  • 数据挖掘是从大量的、不完全的、有噪声的、模糊的、随机的实际应用数据中发现并提取隐藏在其中的、人们事先不知道的、但又是潜在有用的信息和知识的一种技术。它又称为数据库中的知识发现，其与数据库、数据统计、机器学习、模式识别、模糊教学等诸多技术相关。

  • 数据处理不是数据挖掘

  • OLAP 与数据挖掘的区别：

    在数据仓库技术中，OLAP 是数据汇总/聚集工具，可帮助简化数据分析，而数据挖掘是自动地发现隐藏在大量数据中的隐含模式和有趣知识；OLAP 工具的目标是简化和支持交互式数据分析，而数据挖掘工具的目标是尽可能自动处理。在这种意义下，数据挖掘比传统的联机分析处理前进了一步。

  • 数据挖掘具备的功能

    1. 概念描述
    2. 关联分析
    3. 分类与预测
    4. 聚类
    5. 孤立点检测
    6. 趋势和演变分析

  • 在实际使用中，数据挖掘的过程通常由六个步骤构成

    1. 确定业务对象
    2. 数据的选择
    3. 数据的预处理
    4. 建模
    5. 模型评估
    6. 模型部署

3. 大数据管理技术

3.1 大数据的定义

  • 一般意义上，大数据是指无法在可容忍的时间内用现有的信息技术和软、硬件工具对其进行感知、获取、管理、处理的服务的数据集合。
  • 大数据的特征
    1. 数据量巨大，即大量化
    2. 数据种类繁多，即多样化
    3. 处理速度快，即快速化
    4. 价值密度低

3.2 大数据管理技术典型代表

  • 大数据存储

    1. 在通常情况下，大数据的存储可能需要几十台、上百台甚至更多的服务器节点进行分布式存储。一种特殊的文件系统应运而生，即分布式文件系统，用于统一管理这些服务器节点上存储的数据，典型的案例是 Hadoop 开源架构下的分布式文件系统（HDFS）。
    2. HDFS 与常规文件系统不同的是，它以大粒度数据块的方式存储文件，从而减少了元数据的数量，这些数据块则通过随机方式选择不同的节点并存储在各个地方。HDFS 的分布式存储方式使其具有良好的可扩展性，不仅支持在一个文件系统中存储千万量级的文件，还为上层的大数据处理应用提供了透明的数据访问和存储功能，也就是说应用程序并不会感受到物理上分布存储在不同机器上数据的差异性。除了大规模存储数据和高并发访问能力，HDFS 还具有强大的容错能力，通过多副本数据块的存储方式保障系统从故障中快速恢复。

  • NoSQL 数据管理系统

    1. NoSQL 是以互联网大数据应用为背景发展起来的分布式数据管理系统。NoSQL 由两种解释：一种是 Non-Relational，即非关系数据库；另一种是 Not Only SQL，即数据管理技术不仅仅是 SQL。

    2. NoSQL 系统为了提高存储能力和并发读写能力采用了极其简单的数据模型，支持简单的查询操作，而将负责操作留给应用层实现。该系统对数据进行划分，对各个数据区进行备份，以应对结点可能失败问题，提高系统的可用性；通过大量节点的并行处理获得高性能，采用的是横向扩展的方式。弥补了传统数据库由于事务等机制而带来的对海量数据高并发请求处理性能上的欠缺，采用一种非关系的方式来解决大数据存储和管理的问题。

    3. NoSQL 系统支持的数据存储模型

      • 键值（Key-Value）存储

        是 NoSQL 数据库采用最多的数据存储方式。适合通过主键进行查询或遍历，但基本上只能通过 Key 的完全一致查询获取数据。常见的键值存储数据库包括 Tokyo Cabinet/Tyrant、Redis 和 Oracle BDB 等。

      • 文档存储

        适合存储系统日志等非结构化数据，可以通过复杂的查询条件来获取数据。常见的文件型数据库有 CouchDB、MongoDB 等。

      • 列存储

        是以列为单位来存储数据的，擅长以列为单位读入数据，比较适合对某一列进行随机查询处理，主要应用于需要处理大量数据的情况。常见的列存储数据库有 Cassandra、HBase 等。

      • 图存储

        图存储数据库是基于图理论构建的，使用特点、属性和边的概念。

  • MapReduce 技术

    1. MapReduce 技术是 Google 公司于 2004 年提出的大规模并行计算机解决方案，主要应用于大规模廉价集群上的大数据并行处理。MapReduce 以 Key/Value 的分布式存储系统为基础，通过元数据集中存储、数据以 chunk 为单位分布存储和数据 chunk 冗余复制来保证其高可用性。
    2. MapReduce 是一种并行编程模型。它把计算过程分解为两个阶段，即 Map 阶段和 Reduce 阶段。Map 函数和 Reduce 函数是用户根据应用的具体需求进行编写。
    3. MapReduce 是一种简单易用的软件框架。通常，计算节点和存储节点是同一个节点，即 MapReduce 框架和 Hadoop 分布式文件系统运行于相同的结果集。