数据库系统原理

数据库系统概述

数据 Data

数据是描述事物的符号记录，是指利用物理符号记录下来的、可以鉴别的信息
- 数据是信息存在的一种形式，只有通过解释或处理的数据才能成为有用的信息

数据库 DB

数据库 Database 是指长期储存在计算机中的有组织的、可共享的数据集合
- 数据库中存储的数据具有永久存储、有组织和可共享三个基本特点
- 数据要按照一定的数据模型组织、描述和存储，具有较小的冗余度、较高的数据独立性，系统易于扩展，并可以被多个用户分享

数据库管理系统 DBMS

数据库管理系统是专门用于建立和管理数据库的一套软件，介于应用程序和操作系统之间
- 数据定义功能
- 数据操纵功能
- 数据库的运行管理功能
- 数据库的建立和维护功能
- 数据组织、存储和管理功能
- 其他功能

数据库系统 DBS

数据库系统的构成
- 通常，一个完整的数据库系统包括数据库、数据库管理系统及相关实用工具、应用程序、数据库管理员(DBA)和用户

数据库系统的结构分类

从DBA的视角

内部系统结构，采用三级模式: 外模式, 模式(核心), 内模式

名称	别称	视图类型
外模式`用户级`	子模式、用户模式	数据视图、用户视图
模式`概念级`	概念模式、逻辑模式	概念视图
内模式`物理级`	存储模式	内部视图、存储视图

三级模式结构的两层映像(即对应规则)与数据独立性
1. 外模式/模式映像保证了数据与程序的逻辑独立性
2. 模式/内模式映像保证了数据与程序的物理独立性

外部系统结构: 集中式结构, 分布式结构, 并行结构

从数据库应用的用户的视角(程序员、数据库终端用户)
1. 客户/服务器结构C/S(APP) 表示层<=>数据层
2. 浏览器/服务器结构B/S(WEB) 表示层<=>处理层(中间层)<=>数据层
- 单机方式网络方式

数据模型 Model

模型是现实世界特征的模拟和抽象表达
数据模型是对现实世界数据特征的抽象，描述的是数据的共性内容
数据的特征
1. 静态特征
  - 数据的基本结构
  - 数据间的联系
  - 数据取值范围的约束
2. 动态特征 对数据可以进行符合一定规则的操作
数据模型组成要素
- 数据结构描述的是系统的静态特征，即数据对象的数据类型、内容、属性以及数据对象之间的联系
- 数据操作描述的是系统的动态特征
- 数据约束描述数据结构中数据间的语法和语义关联
数据模型的分类

数据模型是模型化数据和信息的工具，也是数据库系统的核心和基础；比较真实地模拟现实世界，容易为人们理解，便于在计算机上实现
1. 概念层数据模型
  - 概念层是数据抽象级别的最高层。概念层数据模型，也称为数据的概念模型或信息模型，主要用于数据库的设计阶段
  - 信息世界涉及的基本概念: 实体Entity、属性Attribute、码或键Key、域Domain、实体型Entity Type、实体集 Entity Set、联系 Relationship
  - 数据模型中有型 Type和值 Value两个概念
  - 概念模型的表示方法E-R图
2. 逻辑层数据模型
  - 逻辑层是数据抽象级别的中间层。逻辑层数据模型，也称为数据的逻辑模型。任何DBMS都是基于某种逻辑数据模型
  - 逻辑模型的类型
    1. 层次模型
    2. 网状模型
    3. 关系模型 用二维表格结构来表示实体间的联系
      - 建立在严格的数学概念的基础上
      - 概念单一
      - 存取路径对用户透明，有更高的数据独立性，更好的安全保密性
    4. 面向对象模型(面向对象方法+数据库) 既是概念模型又是逻辑模型
3. 物理层数据模型
  - 是数据库最底层的抽象，也称为数据的物理模型，描述数据在存储介质上的组织结构，是逻辑模型的物理实现；设计目标是提高数据库性能和有效利用存储空间

关系数据库

关系数据模型

组成要素

关系数据结构
关系操作集合
关系完整性约束

关系数据结构

表 Table
- 也称为关系，是一个二维的数据结构，由表名、列、若干行数据组成
- 每个表有唯一的表名，表中每一行数据描述一条具体的记录值
关系 Relationship
- 一个关系逻辑上对应一张二维表，可以为每个关系取一个名称进行表示
- 关系的三种类型
  1. 基本关系/基本表(实际存在的表)
  2. 查询表(导出的虚表)
  3. 视图表(导出的虚表)
列 Column
- 也称为字段Field 或属性Attribute
- 属性的个数称为关系的元或度
- 列的值称为属性值，其取值范围称为值域
行 Row
- 也称为元组Tuple 或记录Record
- 表中的数据按行存储
- 行中的一个属性值，即具体的数据项，称为分量Component
码/键 Key
- 能用来唯一标识关系记录的属性/属性组
- 超码/超键 Super Key 在码中去除某个属性，它仍可唯一标识
- 候选码/候选键 Candidate Key 在码中不能从中移去任何一个属性；Candidate Key是关系的最小超码/超键
- 主属性 Primary Attribute 包含在任何一个候选码中的属性称为主属性或码属性
- 非主属性 Nonprimary Attribute
- 主码/主键 Primary Key 在若干个候选码中指定一个唯一标识关系的记录
- 全码/全键 All Key 主码/主键是所有属性集合
- 外码/外键 Foreign Key 某个属性/属性组不是这个关系参照关系/从关系的主码/候选码，而是另一个关系被参照关系/主关系的主码
- 参照关系/被参照关系 Referencing Relation/Referenced Relation 参照关系也称为从关系，被参照关系也称为主关系，它们是指以 Foreign Key 相关联的两个关系
域 Domain 表示属性的取值范围
数据类型 Data Type
关系模式 Relation Schema
- 关系模式是型 Type静态的，关系是值 Value动态的，关系模式是对关系的描述，即表头
关系数据库 Relation DataBase
- 所有关系的集合，构成一个关系数据库
- 以关系模型作为数据的逻辑模型，并采用关系作为数据组织方式的一类数据库，其数据库操作建立在关系代数的基础上
关系数据库对关系的限定要求
- 每一个属性都是不可分解的(不允许表中有表)
- 每一个关系仅仅有一种关系模式(不可以多个表头)
- 每一个关系模式中的属性必须命名，属性名不同
- 同一个关系中不允许出现候选码/候选键值完全相同的元组
- 在关系中元组的顺序(行序)是无关紧要的，可以任意交换
- 在关系中属性的顺序(列序)是无关紧要的，可以任意交换

关系操作

CURD 增删改查

更新: 增删改

插入 Insert 创建 Create
删除 Delete
修改 Update
查询 Query 读取 Read
- 传统操作
  1. 选择
  2. 投影
  3. 并
  4. 差
  5. 笛卡尔积
- 专门操作
  1. 连接
  2. 除
  3. 交

特点: 集合操作方式一次一集合

关系数据语言

关系代数语言
- 传统的集合运算
  1. 并 Union 两个关系必须有相同的属性个数 ∪
  2. 差 Difference 两个关系必须有相同的属性个数 −
  3. 交 Intersection 两个关系必须有相同的属性个数 ∩
  4. 笛卡尔积 Cartesian Product 行乘列加 × (排列组合)
- 专门的关系运算
  1. 选择 SELECT 关系名 WHERE 姓名 = “Bobo”
    - δF(R) F条件 R关系/表名 δ姓名=Bobo (关系名)
    - 选行
  2. 投影 PROJECTION 关系名 (姓名, 学号, …)
    - πA(R) A属性 R关系/表名 π姓名, 学号 (关系名)
    - 选列
  3. 连接 JOIN tableA AND tableB WHERE 条件 θ ∞
    - 等值连接
    - 自然连接
  4. 除 DIVISION R÷S
    - 关系 R 包含 ABC 三个属性; 关系 S 包含 BCD 三个属性
    - 关系R 和关系S 拥有共同的属性 BC
    - 除得到的属性值是关系R 包含而关系S 不包含的属性
    - R÷S = A
关系演算语言
- 元组关系演算
- 域关系演算
结构化查询语言 SQL

共同特点: 具有完备的表达能力，是非过程化的集合操作语言，功能强，能够独立使用也可以嵌入高级语言中使用

关系运算符

任何一种操作都包含三大要素: 操作对象操作符操作结果

关系完整性约束

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

实体完整性约束 Entity Integrity Constraint 关系的两个不变性
- 主码的组成不能为空，主属性不能是空值 NULL
参照完整性约束 Referential Integrity Constraint 关系的两个不变性
- 定义外码和主码之间的引用规则
- 要么外码等于主码中某个元组的主码值，要么为空值 NULL
用户定义完整性约束 User-defined Integrity Constraint

插入操作 先检查实体完整性约束，参照关系(存在外键的关系)再检查参照完整性约束

删除操作 一般只需要对被参照关系检查参照完整性约束

更新操作 (删除+插入)上述两种情况的综合

规范化理论

冗余与异常

数据冗余: 同一数据被反复存储
更新异常: 数据不一致
插入异常: 应该被插入的数据没有执行插入
删除异常: 不应该删去的数据会出现被删去

函数依赖与关键字

对于给定关系，属性X的每一个值，属性Y只有唯一值与之对应，则称X函数决定Y，Y函数依赖X X->Y，X称为决定因素

完全函数依赖 (学号, 课程号)->成绩 候选码/主码
部分函数依赖 (学号, 姓名)->性别 超码
传递函数依赖 X->Y Y->Z X->Z Z传递函数依赖于X

候选关键字: 候选码

范式与关系规范化过程

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

第一范式1NF（不含重复组）
- 设R为任一给定关系，如果R中每个列与行的交点处的取值都是不可再分的基本元素，则R为第一范式
第二范式2NF👍（消除了非主属性对候选键的部分函数依赖）
- 设R为任一给定关系，若R为1NF，且其所有非主属性都完全函数依赖于候选关键字，则R为第二范式
- 第一范式1NF冗余高，存在非主属性对码的部分函数依赖，需要消除部分函数依赖：拆表
第三范式3NF（消除了非主属性对候选键的传递函数依赖）
- 设R为任一给定关系，若R为2NF，且其每一个非主属性都不传递函数依赖于候选关键字，则R为第三范式
巴斯范式BCNF 第三范式的改进形式 3.5NF👎
- 设R为任一给定关系，X、Y为其属性集合，F为其函数依赖集，若R为3NF，且其F中所有函数依赖X->Y(Y不属于X)中的X必包含候选关键字，则R为巴斯范式

数据库设计

设计概述

生命周期
1. 数据库分析与设计阶段
  - 需求分析
  - 概念设计: 形成独立于机器特点、独立于各个关系数据库管理系统产品的概念模式 E-R图
  - 逻辑设计: 外模式
  - 物理设计: 内模式
2. 数据库实现与操作阶段
  - 实现
  - 操作与监督
  - 修改与调整
设计目标
- 满足应用功能需求: 存取删改
- 良好的数据库性能: 数据的高效率存取和空间的节省共享性、完整性、一致性、安全保密性
设计内容
- 结构设计静态的: 概念结构设计逻辑结构设计物理结构设计
- 行为设计动态的: 功能设计事务设计程序设计
设计方法
- 直观设计法 最原始的数据库设计方法
- 规范设计法
  1. 新奥尔良设计方法需求分析概念结构设计逻辑结构设计物理结构设计
  2. 概念结构上，基于E-R模型的数据库设计方法
  3. 逻辑结构上，基于第三范式的设计方法，是一类结构化设计方法
- 计算机辅助设计法辅助软件工程工具(CASE工具)
设计过程: 需求分析 -> 结构设计、行为设计 -> 数据库实施(加载数据库数据、调试运行应用程序) -> 数据库运行与维护

基本步骤

需求分析

数据库设计的起点；目标是了解与分析用户的信息及应用处理的要求，并将结果按一定格式整理而形成需求分析报告；分析报告是后续概念设计、逻辑设计、物理设计、数据库建立与维护的依据
1. 确定数据库范围数据库的第一项工作
  - 有效地利用计算机设备及数据库系统的潜在能力
  - 提高数据库的应变能力
  - 避免应用过程中对数据库做太多或太大的修改
  - 延长数据库的生命周期
2. 分析数据应用过程应用过程分析的结果是数据库结构设计的重要依据
  - 用到哪些数据
  - 数据使用的顺序
  - 对数据作何处理和处理的策略以及结果
3. 收集与分析数据
  - 静态结构: 数据分类表、数据元素表
  - 动态结构: 任务分类表、数据特征操作表
  - 数据约束: 数据的安全保密性、数据的完整性、响应时间、数据恢复
4. 编写需求分析报告
  - 数据库的应用功能目标
  - 标明不同用户视图范围
  - 应用处理过程需求说明
  - 数据字典
  - 数据量
  - 数据约束
概念结构设计自顶向下
- 独立于任何软件与硬件
- 主要目标：最大限度的满足应用需求
逻辑结构设计
- 模型转换: 关系模型
- 设计评价: 分析并检验模式及子模式的正确性与合理性
物理结构设计
- 具体任务主要是确定数据库在存储设备上的存储结构及存取方法，因 DBMS 的不同还可能包括建立索引和聚集，以及物理块大小、缓冲区个数和大小、数据压缩的选择等
数据库实施
- 加载数据：收集、分类、整理校验、输入等
- 应用程序设计
- 数据库试运行：有利于工作人员掌握并熟悉系统、有利于正式运行时避免人为的操作不当等损害
数据库运行与维护
- 保障数据库的正常运行
- 系统维护中最困难的工作是数据库重组与重构

设计方法

实体关系E-R图: 实体型矩形 属性椭圆形 联系 菱形

1:1 联系
1:n 联系
m:n 联系
三个实体之间的一对多联系
三个实体之间的多对多联系
单个实体型内的一对多联系

局部信息结构设计
1. 确定局部范围: 局部范围主要依据需求分析报告中标明的用户视图范围来确定
2. 选择实体: 数据分类表是选择实体的直接依据; 实体选择的最大困难是如何区别实体与属性
3. 选择实体的关键字属性: 实体的存在依赖于其关键字的存在
4. 确定实体间联系: 数据间的联系必须在概念设计时确定
5. 确定实体的属性: 属性分为标识属性和说明属性
全局信息结构设计

逻辑结构设计

E-R图向关系模型的转换
- 一个实体型转换为一个关系模式，实体的属性作为关系的属性，实体的码作为关系的码
- 一个一对一联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并
- 一个一对多联系可以转换为一个独立的关系模式，也可以与 N 端对应的关系模式合并
- 一个多对多联系转换为一个关系模式
- 三个或以上实体间的一个多元联系可以转换为一个关系模式
- 具有相同码的关系模式可合并
对关系数据模型进行优化: 优化结果不是唯一的
- 确定各属性间的函数依赖关系
- 对于各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系
- 判断每个关系模式的范式，根据实际需要确定最合适的范式
- 按照需求分析阶段得到的处理要求，分析这些模式对于这样的应用环境是否合适，确定是否要对某些模式进行合并或分解
- 对关系模式进行必要的分解，提高数据操作的效率和存储空间的利用率
设计面向用户的外模式/设计用户子模式
- 可以通过视图机制在设计用户视图时，重新定义某些属性的别名，使其更符合用户的习惯，以方便使用
- 可以对不同级别的用户定义不同的视图，以保证系统的安全性
- 简化用户对系统的使用

物理结构设计

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

建立索引
- 静态建立索引
- 动态建立索引
建立聚集
- 聚集是将相关数据集中存放的物理存储技术
- 数据聚集结构的一种有效方式是块结构方式
- 数据聚集可在一个或多个关系上建立

SQL与关系数据库基本操作

SQL 概述

结构化查询语言Structured Query LanguageSQL是专门用来与数据库通信的语言，它可以帮助用户操作关系数据库

SQL 的特点

SQL 不是某个特定数据库供应商专有的语言
SQL 简单易学
SQL 强大、灵活，可以进行非常复杂和高级的数据库操作

SQL 的组成

数据查询
数据定义Data Definition LanguageDDL
- CREATE创建数据库或数据库对象
- ALTER修改数据库或数据库对象
- DROP删除数据库或数据库对象
数据操纵Data Manipulation LanguageDML
- SELECT从表或视图中检索数据
- INSERT将数据插入到表或视图中
- UPDATE修改表或视图中的数据
- DELETE从表或视图中删除数据
数据控制Data Control LanguageDCL
- GRANT用于授予权限
- REVOKE用于收回权限

嵌入式和动态SQL规则规定了SQL语句在高级语言程序设计中使用的规范方法，以便适应较为复杂的应用

SQL调用(SQL例程) 和 SQL会话规则

MySQL 预备知识

MySQL, 关系数据库管理系统RDBMS, 体积小、速度快、开放源代码、遵循GPL

常量

字符串常量
- 用单引号或双引号括起来的字符序列，分为ASCII字符串常量和Unicode字符串常量
数值常量
- 整数常量
- 浮点数常量
十六进制常量
- 每对十六进制数字被转换为一个字符，其最前面有一个字母“X”（或“x”）
时间日期常量
- 用单引号把表示日期时间的字符串括起来而构成的 ‘2023-11-21’
位字段值
- b’value’ 二进制值
布尔值
- TRUE 1
- FALSE 0
NULL 值

变量

用户变量 @
系统变量 @@
局部变量 DECLARE 声明

运算符

算术运算符 +（加）、-（减）、*（乘）、/（除）、%（求模）
位运算符 &（位与）、|（位或）、^（位异或）、~（位取反）、>>（位右移）、<<（位左移）
比较运算符 =（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）、!=（不等于）、<=>（相等或都等于空）
逻辑运算符 NOT 或！（逻辑非）、AND 或&&（逻辑与）、OR 或||（逻辑或）、XOR（逻辑异或）

表达式

表达式是常量、变量、列名、复杂计算、运算符和函数的组合

字符型表达式
数值型表达式
日期型表达式

内置函数

数学函数 ABS()
聚合函数 COUNT()
字符串函数 ASII()
日期和时间函数 NOW()
加密函数 ENCODE()
控制流程函数 IF()
格式化函数 FORMAT()
类型转换函数 CAST()
系统信息函数 USER()

数据定义

mysql -u root -p

创建数据库

1
2
3

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

CREATE DATABASE IF NOT EXISTS db_name DEFAULT CHARACTER SET=utf8mb4 DEFAULT COLLATE=utf8_general_ci;

查看数据库

1
2
3

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

SHOW DATABASES;

选择数据库

1	USE db_name;

修改数据库

1	ALTER DATABASE db_name DEFAULT CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci;

删除数据库

1	DROP DATABASE IF EXISTS db_name;

创建表

数据表是关系数据库中最重要、最基本的数据对象，也是数据存储的基本单位
数据表，被定义为字段的集合按行和列的格式来存储的，每一行代表一条记录，每一列代表记录中一个字段的取值

-- 字段名 数据类型 列级完整性约束条件 默认值
-- 表级完整性约束条件

CREATE [TEMPORARY] TABLE IF NOT EXISTS tbl_name
(
  id INT NOT NULL AUTO_INCREMENT,
  name CHAR(20) NOT NULL UNIQUE,
  address VARCHAR(50) NULL,
  state CHAR(1) NOT NULL DEFAULT 0,
  PRIMARY KEY(id)
--INDEX index_name(index_col_name, ...)
)[ENGINT=引擎类型];

整型 int
浮点型 double
布尔型 bool
日期型 date
时间戳 timestamp
时间型 time
定长字符类型 char
可变长字符 varchar

更新表

使用ALTER TABLE语句，增加或删减列、创建或取消索引、更改原有列的数据类型、重新命名列或表，更改表的评注和表的引擎类型，为表重新创建触发器、存储过程、索引和外键等

ADD[COLUMN]子句

1 2	ALTER TABLE db_name.tbl_name ADD COLUMN city char(10) NOT NULL DEFAULT 'GuangZhou' AFTER address;

CHANGE[COLUMN]子句修改指定列的名称或数据类型

1 2	ALTER TABLE db_name.tbl_name CHANGE COLUMN old_column new_column char(20) NULL DEFAULT 'ShenZhen';

ALTER[COLUMN]子句修改或删除指定列的默认值

1 2	ALTER TABLE db_name.tbl_name ALTER COLUMN city SET DEFAULT 'Beijing' \| NULL;

MODIFY[COLUMN]子句修改指定列的数据类型

1 2	ALTER TABLE db_name.tbl_name MODIFY COLUMN city char(20) First;

DROP[COLUMN]子句

1 2	ALTER TABLE db_name.tbl_name DROP COLUMN column_1, column_2;

RENAME[TO]子句

1 2	ALTER TABLE db_name.tbl_name RENAME TO db_name.tbl_backup_name;

ADD INDEX 子句

ALTER TABLE db_name.tbl_name
ADD [INDEX | KEY] index_name(index_col_name, ...),
ADD PRIMARY KEY(index_col_name),
ADD UNIQUE [INDEX | KEY] index_name(index_col_name, ...),
ADD FOREIGN KEY index_name(index_col_name, ...) REFERENCES primary_tbl(id);

DROP INDEX 子句 👎

ALTER TABLE db_name.tbl_name
DROP PRIMARY KEY,
DROP FOREIGN KEY,
DROP INDEX index_name

重命名表

1	RENAME TABLE db_name.tbl_backup_name TO db_name.tbl_name;

删除表

1	DROP [TEMPORARY] TABLE IF EXISTS db_name_1, db_name_2 [RESTRICT \| CASCADE];

查看表

-- SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

-- SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]
-- {DESCRIBE | DESC} tbl_name [col_name | wild]

SHOW TABLES FROM mysql;

SHOW COLUMNS FROM user FROM mysql;
SHOW COLUMNS FROM mysql.user;

USE mysql;
DESC user;

索引定义

索引是提高数据文件访问效率的有效方法

索引通常被创建成单列索引和组合索引

CREATE INDEX 语句
CREATE TABLE 语句
ALTER TABLE 语句

弊端

索引是以文件的形式存储的，如果有大量的索引，索引文件可能比数据文件更快达到最大的文件尺寸
索引在提高查询速度的同时，会降低更新表的速度

普通索引 INDEX｜KEY (码)

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

CREATE INDEX index_name ON tbl_name(index_col_name, ...);

-- 在数据库 db_name 的表 user 上, 根据 user_name 的前两个字符创建一个 升序｜降序 索引index_user
CREATE INDEX index_user ON db_name.user(user_name(2) [ACS | DESC]);

SHOW {INDEX | INDEXS | KEYS} {FROM | in} tbl_name {FROM | in} db_name [WHERE expr];

DROP INDEX index_name ON db_name.tbl_name;

唯一性索引 UNIQUE (候选码)

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

UNIQUE INDEX | UNIQUE KEY

主键 PRIMARY KEY (主码)

是一种唯一性索引。每个表只能有一个，且不能为空

外键 FOREIGN KEY (外码)

数据更新

INSERT

-- INSERT...VALUES 语句
INSERT INTO db_name.tbl_name
VALUES(0, 'Bobo', '广州', DEFAULT, NULL),
(NULL, 'daidai', '深圳', DEFAULT, NULL);

-- INSERT...SET 语句
INSERT INTO db_name.tbl_name
SET name='Bobo', age=18;

-- INSERT...SELECT 语句
INSERT [INTO] tbl_name [(col_name, ...)] SELECT...;

DELETE

1	DELETE FROM db_name.tbl_name WHERE address='上海' ORDER BY id [ACS \| DESC] limit 2;

UPDATE

1
2
3

UPDATE db_name.tbl_name
SET name='Bobo', age=18
WHERE address='上海' ORDER BY id [ACS | DESC] limit 2;

数据查询

SELECT 语句

SELECT {* | ALL} FROM tbl_name
WHERE ... 在数据分组前过滤
GROUP BY ...
HAVING ... 在数据分组后过滤
ORDER BY ...
LIMIT ...

列的选择与指定

-- 别名
SELECT name, address AS 姓名, 地址 FROM tbl_name;

-- 聚合函数
COUNT
MAX MIN SUM AVG
BIT_AND BIT_OR BIT_XOR
GROUP_CONCAT

-- CASE
SELECT name,
   CASE
      WHEN sex='M' THEN '男' ELSE '女'
   END AS 性别
FROM db_name.tbl_name;

-- 计算
SELECT age-2 FROM tbl_name;

FROM 子句与多表连接查询

交叉连接笛卡尔积

1 2	SELECT * FROM tbl_1 CROSS JOIN tbl_2; SELECT * FROM tbl_1, tbl_2;

内连接
1
SELECT * FROM tbl_1 INNER JOIN tbl_2 ON tbl_1.id=tbl_2.foreign_id;
- 等值连接使用运算符=
- 非等值连接使用除=之外的其他比较运算符
- 自连接将一个表与它自身进行连接
外连接

左外连接 LEFT OUTER JOIN | LEFT JOIN
右外连接 RIGHT OUTER JOIN | RIGHT JOIN

-- 设一个图书借阅管理数据库中包括三个关系模式：
--   图书(图书编号，书名，作者，出版社，单价) 
--   读者(借书证号，姓名，性别，单位，地址) 
--   借阅(借书证号，图书编号，借阅日期，归还日期，备注)

-- 查询Bobo所借阅的所有图书的书名及借阅时间

SELECT 书名, 借阅时间
FROM  图书, 借阅, 读者
WHERE 姓名 = 'Bobo' AND 读者.借书证号 = 借阅.借书证号 AND 借阅.图书编号 = 图书.图书编号;

select 书名, 借阅时间 from 借阅 inner join 图书 on 借阅.图书编号 = 图书.图书编号 where 借书证号 in (select 借书证号 from 读者 where 姓名 = 'Bobo');

WHERE 子句与条件查询

子查询: 表子查询行子查询列子查询标量子查询

-- expression [NOT] IN (subquery)
SELECT studentNo, studentName
FROM tb_student
WHERE studentNo IN(SELECT studentNo FROM tb_score WHERE score>80);

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

--  EXISTS (subquery) 子查询的结果集不为空，则返回TRUE，否则返回FALSE

比较运算
- = 等于
  - null=’Bobo’ UNKNOWN
  - null=null UNKNOWN
- <> 不等于
- != 不等于
- < 小于
- <= 小于等于
- > 大于
- >= 大于等于
- <=> 等于, 不会返回 unknown(认识 NULL)
  - null=’Bobo’ false
  - null<=>null true

判断范围

1 2	select * from db_name.tbl_name where age [NOT] BETWEEN 23 AND 24; select * from db_name.tbl_name where age [NOT] IN(23, 24);

判定空值

1	select * from db_name.tbl_name where sex IS [NOT] NULL;

GROUP BY 子句与分组数据

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

SELECT address, sex, COUNT(*) AS 'count'
FROM user
GROUP BY address, sex
WITH ROLLUP;

HAVING 子句

HAVING子句
1. 过滤分组
2. 可以包含聚合函数
3. 在数据分组后过滤
WHERE子句
1. 过滤数据行
2. 不包含聚合函数
3. 在数据分组前过滤

SELECT name, address
FROM user
GROUP BY name, address
HAVING COUNT(*) <= 3;

ORDER BY 子句

-- ORDER {col_name | expr | position} [ASC⬆️ | DESC⬇️], ...

SELECT name, sex
FROM user
ORDER BY name DESC, address
HAVING COUNT(*) <= 3;

LIMIT 子句

1
2
3

-- LIMIT {[offset, ]row_count | row_count OFFSET offset}

SELECT * FROM user ORDER BY id LIMIT 4, 3 | LIMIT 3 OFFSET 4;

视图

视图是数据库中的一个对象，它是数据库管理系统提供给用户的以多种角度观察数据库中数据的一种重要机制
视图不是数据库中真实的表，而是一张虚拟表，其自身并不存储数据

集中分散数据
简化查询语句
重用SQL语句
保护数据安全
共享所需数据
更改数据格式

创建视图

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

CREATE OR REPLACE VIEW db_name.user_view
AS
SELECT * FROM db_name.user WHERE sex = '女'
WITH CHECK OPTION;

删除视图

1	DROP VIEW IF EXISTS view_name_1, view_name_2 [RESTRICT \| CASCADE];

修改视图定义

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

查看视图定义

1	SHOW CREATE VIEW view_name;

更新视图数据

INSERT INTO db_name.view_name
VALUES(0, 'Bobo', '广州', DEFAULT, NULL),
(NULL, 'daidai', '深圳', DEFAULT, NULL);

DELETE FROM db_name.view_name WHERE age=18;

UPDATE db_name.view_name
SET name='Bobo', age=18
WHERE address='上海';

查询视图数据

1	select * from db_name.view_name where sex IS [NOT] NULL;

数据库编程

存储过程

存储过程是一组为了完成某项特定功能的SQL语句集，其实质就是一段存储在数据库中的代码；它可以由声明式的SQL语句(UPDATE、SELECT等语句)和过程式语句(IF…THEN…ELSE流程控制语句)组成

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

创建存储过程

USE db_name;

DELIMITER $$

-- [IN | OUT | INOUT] param_name type
CREATE PROCEDURE sp_name(IN uid INT, IN uaddress VARCHAR(50))
BEGIN
  UPDATE user SET address=uaddress WHERE id=uid;
END $$

DELIMITER ;

调用存储过程

1	CALL sp_name(1, '广州');

删除存储过程

1	DROP PROCEDURE [IF EXISTS] sp_name;

存储过程体

-- 使用 DECLARE 语句声明局部变量
DECLARE variable_name CHAR(10);

SET variable_name='variable_value', ...;

-- SELECT...INTO语句 返回的结果集只能有一行数据
-- SELECT col_name[, ...] INTO var_name[, ...] FROM db_name;

-- 使用 DECLARE CURSOR 语句创建游标
DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;
FETCH cursor_name INTO var_name[, var_name, ...];
CLOSE cursor_name;

只能在存储过程体的 BEGIN…END 语句块中声明
必须在存储过程的开头处声明
作用范围仅限于声明它的 BEGIN…END 语句块
不同于用户变量
- 局部变量声明时，在其前面没有@符号，并且它只能被声明它的 BEGIN…END 语句块中的语句所使用
- 用户变量在声明时，会在其名称前面使用@符号，同时已声明的用户变量存在于整个会话之中

流程控制语句👎
1. 条件判断语句
  - IF…THEN…ELSE 语句
  - CASE 语句
2. 循环语句👎👎
  - WHILE 语句
  - REPEAT 语句
  - LOOP 语句
  - -
  - ITERATE 语句用于表示退出当前循环

存储函数

存储函数与存储过程一样，是由声明式的SQL语句和过程式语句组成的代码片段

USE db_name;

DELIMITER $$

CREATE FUNCTION sp_name(uid INT)
   RETURNS CHAR(10)
   DETERMINISTIC -- 提高 WHERE 语句的性能
BEGIN
   DECLARE MSG CHAR(10);
   SELECT sex INTO MSG FROM user WHERE id=uid;
   IF MSG IS NULL THEN
      RETURN(SELECT ‘没有该客户‘);
   ELSE
      IF MSG='female' THEN
        RETURN(SELECT ‘女‘);
      ELSE
        RETURN(SELECT ‘男‘);
      END IF;
   END IF;
END $$

DELIMITER ;

SELECT sp_name(2);

DROP FUNCTION [IF EXISTS] sp_name;

存储函数不能与存储过程具有相同的名字

存储函数	存储过程
不能拥有输出参数	可以拥有输出参数
必须包含一条 RETURN 语句	不允许包含 RETURN 语句
使用 SELECT 语句调用存储过程	使用 CALL 语句调用存储过程

数据库安全与保护

数据库完整性

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

列级约束包括对列的类型、取值范围、精度等的约束
元组约束指元组中各个字段之间的相互约束
表级约束指若干元组、关系之间的联系的约束

定义与实现完整性约束

实体完整性约束 实体完整性是通过 主键约束 PRIMARY KEY(唯一一个) 和 候选键约束 UNIQUE(可以多个) 实现的
1. 每一个表只能定义一个主键
2. 主键的值(键值)必须能够唯一标志表中的每一行记录，且不能为 NULL
3. 复合主键不能包含不必要的多余列
4. 一个列名在复合主键的列表中只能出现一次

参照完整性约束

REFERENCES tbl_name(index_col_name, ...)
[ON DELETE 限制策略(默认) | 级联策略 | 置空策略 | 不采取实施策略]
[ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION]

CREATE TABLE 学生表
(
   id INT PRIMARY KEY AUTO_INCREMENT,
   name CHAR(20) NOT NULL,
   class_id INT,
   FOREIGN KEY(class_id)
   REFERENCES 班级表(id) ON DELETE RESTRICT ON UPDATE RESTRICT;
);

用户定义完整性约束
1. 非空约束 NOT NULL
2. CHECK 约束
3. 触发器 TRIGGER

命名完整性约束

只能给基于表的完整性约束指定名字
无法给基于列的完整性约束指定名字
如果没有用 CONSTRAINT 给表的完整性约束指定名字，数据库会自动创建一个唯一的名字

1	CONSTRAINT [symbol]

更新完整性约束

使用ALTER TABLE语句更新与列或表有关的各种约束

完整性约束不能直接被修改（先删除，再增加）
使用 ALTER TABLE 语句，可以独立地删除完整性约束，而不会删除表本身
DROP TABLE 语句删除一个表，则表中所有的完整性约束都会被自动删除

触发器

触发器是用户定义在关系表上的一类由事件驱动的数据对象，是一种保证数据完整性的方法

实现主键和外键不能保证的复杂的参照完整性和数据的一致性，从而有效地保护表中的数据

-- CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body

CREATE TRIGGER trigger_name AFTER INSERT
ON tbl_name FOR EACH ROW
SET @str=NEW.address;

SELECT @str;

DROP TRIGGER [IF EXISTS] trigger_name;

INSERT 触发器
- 在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表，来访问被插入的行
- 在 BEFORE INSERT 触发器中，NEW 中的值可以被更新
DELETE 触发器
- 在 DELETE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表，来访问被删除的行
- OLD 中的值全部是只读的，不能被更新
UPDATE 触发器
- 在 UPDATE 触发器代码内，可引用一个名为 OLD 的虚拟表，来访问 UPDATE 语句执行前的值
- 也可以引用一个名为 NEW 的虚拟表来访问更新后的值
1
2
3
CREATE TRIGGER trigger_name BEFORE UPDATE
ON tbl_name FOR EACH ROW
SET NEW.update_count=OLD.update_count+1;

每个表每个事件每次只允许一个触发器，每个表最多支持6个触发器，每条 INSERT、UPDATE 和 DELETE 的BEFORE和AFTER

安全性与访问控制

用户账号管理

select user from mysql.user;

-- CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'];
-- 'user_name'@'host_name'

SELECT PASSWORD(456); -- *531E182E272080AB0740FE2F2D689DBE0146E04

CREATE USER 'Bobo'@'localhost' IDENTIFIED BY '123',
'daidai'@'%' IDENTIFIED BY PASSWORD '*531E182E272080AB0740FE2F2D689DBE0146E04';

-- DROP USER user [, user, ...];
DROP USER 'daidai'@'%';

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

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('123'); -- encrypted password

账号权限管理

GRANT SELECT(id, age) ON user_tbl TO 'Bobo'@'localhost' WITH GRANT OPTION;

GRANT SELECT, UPDATE ON db_name.tbl_name TO 'daidai'@'localhost' IDENTIFIED BY '456';

GRANT CREATE USER ON *.* TO 'daidai'@'localhost';
GRANT All ON *.* TO 'root'@'%';

-- 用户权限 > 数据库权限 > 表权限 > 列权限
SHOW GRANTS FOR 'Bobo'@'localhost';

REVOKE SELECT, UPDATE ON db_name.tbl_name FROM 'daidai'@'localhost';

事务与并发控制

概念

事务是用户定义的一个数据操作序列，这些操作可作为一个完整的工作单元，要么全部执行，要么全部不执行，是一个不可分割的工作单位
事务中的操作一般是对数据的更新操作，包括增、删、改
程序是静态的，事务是动态的，程序执行就成了事务

以BEGIN TRANSACTION语句开始
以COMMIT语句或ROLLBACK语句结束

特征

ACID

原子性Atomicity 事务是不可分割的最小工作单位
一致性Consistency
隔离性Isolation
持续性Durability 永久性

并发操作问题

丢失更新 事务 T2 的提交结果会破坏 T1 提交的结果
不可重复读 事务 T2 执行更新操作，使 T1 无法再现前一次读取结果
读”脏“数据 事务 T1 修改数据后撤销，使得 T2 读取的数据与数据库中不一致

封锁

封锁是最常用的并发控制技术

基本思想：事务通过向系统请求对它所希望的数据对象加锁，以确保它不被非预期改变
锁实质：允许或阻止一个事务对一个数据对象的存取特权

排他锁 X锁用于写操作
共享锁 S锁用于读操作

封锁的工作原理

若事务T对数据D加了X锁，则所有别的事务对数据D的锁请求都必须等待直到事务T释放锁
若事务T对数据D加了S锁，则别的事务还可对数据D请求S锁，而对数据D的X锁请求必须等待直到事务T释放锁
事务执行数据库操作时都要先请求相应的锁，即对读请求S锁，对更新请求X锁。这个过程一般是由DBMS在执行操作时自动隐含地进行
事务一直占有获得的锁直到结束时释放

封锁的粒度

通常以粒度来描述封锁的数据单元的大小
DBMS可以决定不同粒度的锁
粒度越细，并发性就越大，但软件复杂性和系统开销也就越大

封锁的级别

封锁的级别又称为一致性级别或隔离度

0 级封锁: 不重写其他非 0 级封锁事务的未提交的更新数据（实用价值低）
1 级封锁: 不允许重写未提交的更新数据 防止丢失更新
2 级封锁: 既不重写也不读未提交的更新数据 防止读脏数据
3 级封锁: 不读未提交的更新数据，不写任何（包括读操作）未提交数据 防止不可重读

死锁和活锁

活锁先来先服务
死锁预防
1. 一次性锁请求
2. 锁请求排序
3. 序列化处理
4. 资源剥夺

可串行性

一组事务的一个调度就是它们的基本操作的一种排序
在数据库系统中，可串行性就是并发执行的正确性准则，即当且当一组事务的并发执行调度是可串行化的，才认为它们是正确的

两段封锁法 Two-Phase Locking, 2PL

发展Growing 或加锁阶段
收缩Shrinking 或释放锁阶段

备份与恢复

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

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

-- SELECT INTO...OUTFILE 语句备份数据
-- SELECT ... INTO {OUTFILE | DUMPFILE} 'file_name' export_options
SELECT * FROM db_name.tbl_name
INTO OUTFILE 'C:/BACKUP/backupfile.txt'
FIELDS TERMINATED BY ',' -- 指定字段值之间的符号
       OPTIONALLY ENCLOSED BY "" -- 指定包裹文件中字符值的符号
LINES TERMINATED BY '?'; -- 指定一个数据行结束的标志

-- LOAD DATA...INFILE 语句恢复数据
-- LOAD DATA INFILE 'file_name.txt' INTO TABLE tbl_name
LOAD DATA INFILE 'C:/BACKUP/backupfile.txt'
INTO TABLE db_name.tbl_name_copy
FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY ""
LINES TERMINATED BY '?';

数据库应用设计与开发

以数据库的生命周期为演化主线，数据库应用软件的设计与开发过程可由需求分析、系统功能与数据库的设计、系统功能与数据库的实现、测试与维护等阶段构成

/**
  1. 首先建立与Mysql数据库服务器的连接
  2. 然后选择要对其进行操作的数据库
  3. 再执行相应的数据库操作，包括对数据的添加、删除、修改和查询等
  4. 最后关闭与Mysql数据库服务器的连接
 */
<?php
$con = mysql_connect("localhost:3306", "root", "123456");
if (!$con) {
  echo "错误编号: ".mysql_errno()."<br>";
  echo "错误信息: ".mysql_error()."<br>";
  die("数据库服务器连接失败<br>");
}
echo "已成功建立与 MySQL 服务器的连接<br>";

mysql_select_db("db_name", $con) or die("数据库选择失败<br>");
echo "已成功选择数据库<br>";

mysql_query("set names 'gbk'");

$sql = "SELECT * FROM user WHERE id = 1";
$result = mysql_query($sql, $con);
if ($result) {
  echo "查询成功<br>";
  $array = mysql_fetch_array($result, MYSQL_NUM);
  var_dump($array);
}

mysql_close($con) or die("关闭与 MySQL 数据库服务的连接失败<br>");
echo "已成功关闭与 MySQL 数据库服务器的连接<br>";
?>

数据管理技术的发展

数据库是管理数据的技术，发展了以数据建模和数据库管理系统为核心技术

人工管理阶段
- 数据不保存
- 应用程序管理数据
- 数据面向应用程序
文件系统阶段
数据库系统阶段
- 数据集成(主要目的)
- 数据共享性高
- 数据冗余小
- 数据一致性
- 数据独立性高(数据定义与使用数据的应用程序分离称为数据独立)
- 实施统一管理与控制(数据的安全性、完整性、并发控制与故障恢复等，即数据库保护)
- 减少应用程序开发与维护的工作量

第一代数据库系统
- 层次数据库系统和网状数据库系统的数据模型虽然分别为层次模型和网状模型，但实质上层次模型是网状模型的特例
- 支持三级模式（外模式、模式、内模式）的体系结构
- 用存取路径来表示数据之间的联系
- 独立的数据定义语言
- 导航的数据操纵语言
第二代数据库系统关系数据库系统 20世纪70年代
- 以关系模型为基础；关系模型是由数据结构、关系操作和数据完整性三部分组成，关系代数作为语言基础，关系数据理论作为理论基础
- 模型简单清晰
- 理论基础好
- 数据独立性强
- 数据库语言非过程化
- 标准化
第三代数据库系统
- 应支持数据管理、对象管理和知识管理
- 必须保持或继承第二代数据库系统的技术
- 必须对其他系统开放

数据仓库与数据挖掘

计算机数据处理工作的类型

OLTP 操作型处理（联机事务处理）传统的数据库技术
OLAP 分析型处理（联机分析处理）数据仓库

数据仓库

数据仓库 Data Warehouse, DW 是面向主题的、集成的、稳定的、随时间变化的数据集合，用以支持管理决策的过程
数据仓库不是可以买到的产品，而是一种面向分析的数据存储方案

面向主题
集成性
数据的非易失性
数据的时变性

数据仓库具有三个常用的重要概念: 粒度、分隔、维

数据挖掘

数据挖掘是从大量的、不完全的、有噪声的、模糊的、随机的实际应用数据中发现并提取隐藏在其中的、人们事先不知道的、但又是潜在有用的信息和知识的一种技术。又被称为知识发现

数据挖掘的功能: 概念描述、关联分析、分类与预测、聚类、孤立点检测、趋势和演变分析

数据挖掘的步骤: 确定业务对象、数据的选择、数据的预处理、建模、模型评估、模型部署

大数据管理技术

大数据是指无法在可容忍的时间内用现有信息技术和软、硬件工具对其进行感知、获取、管理、处理的服务的数据集合

大量化
多样化
快速化
价值密度低

大数据管理技术典型代表: 大数据存储、分布式文件系统 HDFS、NoSQL 数据管理系统、MapReduce 并行编程

NoSQL

键值存储 采用最多的数据存储方式，适合通过主键进行查询或遍历
文档存储 适合存储系统日志等非结构化数据，可以通过复杂的查询条件来获取数据
列存储 比较适合对某一列进行随机查询处理，主要应用于需要处理大量数据的情况
图存储 图存储数据库是基于图理论构建的，使用结点、属性和边的概念