自考-知识点总结-数据库系统原理 04735

imwhyang

已于 2024-08-14 13:46:53 修改

阅读量6.5k

点赞数 27

分类专栏：自考文章标签：数据库知识图谱

于 2021-10-11 19:45:42 首次发布

本文链接：https://blog.csdn.net/imwhyang/article/details/120710231

版权

自考专栏收录该内容

1 篇文章 3 订阅

订阅专栏

本文详细介绍了数据库系统原理，涵盖数据库基本概念、关系数据库、数据库设计与规范化理论，以及SQL语言与数据库编程。深入讲解了关系数据模型、数据完整性、存储过程与触发器，并涉及数据库安全与保护，如用户权限管理和事务控制。通过对数据库生命周期的理解，帮助读者掌握数据库的全面知识。

摘要由CSDN通过智能技术生成

数据库系统原理

第一章数据库系统概述

数据库基本概念

数据：符号记录（文本或多媒体）
数据库（DB）：长期储存、有组织、可共享的数据集合
数据库管理系统（DBMS）：建立与管理数据库的软件
基本功能：数据-定义、操纵。数据库运行管理、建立、维护。
数据库系统（DBS）：数据库、管理系统、工具、程序、DBA、用户等。

数据库管理技术发展

人工管理阶段：记录无结构、整体无结构
文件系统阶段：长期保存、专门管理、可共享（差）、冗余大、记录内有结构、整体无结构
数据库系统阶段：数据集成、数据共享性高、冗余小、数据一致性、独立性高、统一管理控制、减少开发维护工作量。

数据库系统的结构

三级模式结构：
模式：概念模式或逻辑模式，指数据的逻辑关系与特征
外模式：数据的表现形式（程序或表）
内模式：数据的物理存储方式与结构
运行与应用结构：C/S or B/S

数据模型

特征与模型结成要素：
数据结构：静态属性
数据操作：动态特性
数据约束（域）：正确、有效、相容性。
模型分类：
概念层：
信息基本概念：实体、属性、码键、域、实体型、实体集、联系
概念模型的表示：E-R图实体型、属性、联系
逻辑层：
层次模型（树）
网状模型
关系模型、面向对象模型

物理层：
存储介质上的组织结构

第二章关系数据库

第二节关系数据模型

关系数据结构：单一数据结构-关系。
1. 表 table
  1. 表就是关系，二维表由表名、列、行数据组成。
2. 关系 Relation
  1. 基本关系，基本表，真实存在数据。
  2. 查询表，查询结果对应表。
  3. 视图表，其它表导出表，虚表。
3. 列 Column
  1. 作字段Field 属性Attribute
  2. 同表列唯一
4. 属性 Attribute
  1. 同 ->列
  2. 属性的个数：元或度
5. 行 Row
  1. 元组Tuple 或记录Record
6. 元组 Tuple
  1. 一行就是一个元组
7. 分量 Component
  1. 元组中的每一个属性值都叫分量
8. 码或键 Key
  1. 一个表中的若干属性值，在任何元组中都不相同（标识数据行唯一），这若干属性值称为该表的码或键。
9. 超码或超键 Super Key
  1. 移去某个属性仍然是表的码，称超码。
  2. 默认超码：所有属性。也是最大超码。
10. 候选码或候选键 Candidate Key
  1. 最小超码
  2. 不能去掉任何一个属性
11. 主码或主键 Primary Key
  1. 候选码中选择其中一个作为元组唯一标识，称主键。
12. 全码或全键 All-Key
  1. 表中所有属性。
13. 主属性 Primary Attribute 和非主属性 Nonprimary Attribute
  1. 主属性：包含在候选码中的。
  2. 非主属性：不包含在候选码中的。
14. 外码或外键 Foreign Key
  1. 当前表中的非主属性是另一表的主键，称为当前表的外键。
15. 参照关系 Referencing Relation 和被参照关系 Referenced Relation
  1. 参照关系/从关系：外键为非候选码的。
  2. 被参照关系/主关系：外键为主键的表。
16. 域 Domain
  1. 属性值的取值范围。
17. 数据类型 Data Type
  1. 属性值的数据类型。
18. 关系模式 Relation Schema
  1. 型：表结构不变
  2. 值：数据变化
19. 关系数据库
  1. 属性不可分解
  2. 仅有一种关系模式：表结构固定。
  3. 属性不为空、且唯一。
  4. 候选码无相同：无重复数据。
  5. 无行序。
  6. 无列序。
关系操作集合
1. 基本的关系操作
  1. 查 Query
  2. 插入 Insert
  3. 删除 Delete
  4. 修改 Update
2. 关系数据语言的分类
  1. 关系代数语言
  2. 关系演算语言
  3. both：SQL
    1. 数据定义：DDL
    2. 数据操纵：DML
    3. 数据控制：DCL
3. 关系代数
  1. 操作对象：表
  2. 操作结果：表
  3. 操作符：
    1. 集合运算符：
      1. 并 UNION
      2. 差 DIFFERENCE
      3. 交 INTERSECTION
      4. 笛卡尔积 CERTESIAN PRODUCT
    2. 关系运算符：
      1. 选择 SELECT tableName WHERE … 生成新表
      2. 投影 PROJECTON tableName（Column…）生成新表
      3. 连接 JOIN table1 AND table2 WHERE … 生成新表
      4. 除 DIVISION
    3. 比较操作符：>,>=,<,<=,=,!=
    4. 逻辑操作符：非，与，或
关系的完整性约束
1. 实体完整性约束
2. 参照完整性约束
3. 用户定义完整性约束
4. 关系模型完整性约束检验

第三节关系数据库的规范化理论

关系模式中可能存在的冗余与异常问题
1. 数据冗余
2. 更新异常
3. 插入异常
4. 删除异常
函数依赖与关键字
1. 完全函数依赖
2. 部分函数依赖
3. 传递函数依赖
范式与关系规范化过程
1. 第一范式：不存在嵌套关系。属性值不可再分。
2. 第二范式：在第一范式的基础上属性完全依赖于主键。
3. 第三范式：关系中不包含已在其它关系已包含的非主关键字信息。
4. BCNF 范式：3NF基础上，任何非主属性不能对主键子集依赖。

第三章数据库设计

第一节数据库设计与概述

数据库的生命周期
1. 数据库分析与设计阶段
2. 数据库实现与操作阶段
数据库的设计与目标
1. 满足应用功能需求
2. 良好的数据库性能
  1. 数据高效率存取、空间节省
  2. 良好的数据共享性、完整性、一致性及安全保密性
数据库设计的内容
1. 数据库结构设计：表结构
2. 数据库行为设计：程序
数据库设计的方法
1. 直观设计法：个人经验与技巧，不可靠
2. 规范设计法：
  1. 新奥尔良法：
    1. 需求分析
    2. 概念结构设计
    3. 逻辑结构设计
    4. 物理结构设计
  2. 基于E-R
  3. 基本第三范式
3. 基于计算机辅助设计法：
数据库设计过程
1. 需求分析阶段
2. 结构设计阶段
  1. 概念结构
  2. 逻辑结构
  3. 物理结构
3. 行为设计阶段
  1. 功能设计
  2. 事务设计
  3. 程序设计
4. 数据库实施
  1. 加载数据库数据
  2. 调试运行程序
5. 数据库运行与维护

第二节数据库设计的基本步骤

需求分析
1. 确定数据库范围
  1. 支持哪些功能
2. 分析数据应用过程
  1. 哪些数据
  2. 数据使用的顺序
  3. 数据任何处理
  4. 处理的策略
  5. 处理的结果
3. 收集与分析数据
  1. 动态结构
    1. 数据分类表
    2. 数据元素表
  2. 动态结构
    1. 任务分类表
    2. 数据操作特征表
  3. 数据约束
    1. 数据的安全保密性
    2. 数据完整性
    3. 响应时间
    4. 数据恢复
  4. 编写需求分析报告
4. 编写需求分析报告
  1. 数据库的应用功能目标
  2. 标明不同用户视图范围
  3. 应用处理过程需求说明
    1. 数据流程图
    2. 任务分类表
    3. 数据操作特征表
    4. 操作过程说明书
  4. 数据字典
  5. 数据量
  6. 数据约束
概念结构设计：最理想情况
1. 自顶向下法
2. 自底向上法
逻辑结构设计
1. 逻辑结构设计的
  1. 输入信息：
  2. 输出信息
2. 逻辑结构设计的步骤
物理设计
数据库实施
1. 加载数据
2. 应用程序设计
3. 数据库试运行
数据库运行与维护

第三节关系数据库设计方法

关系数据库设计过程与各级模式
1. 概念模式：应用要求的功能
2. 逻辑模式：外模式，也就是应用业务逻辑实现。
3. 内模式：物理模式，物理存储。
概念结构设计方法
1. E-R图表示方法
  1. 实体型：矩形
  2. 属性：椭圆
  3. 联系：菱形
    1. 一对一
    2. 一对多
    3. 多对多
2. 局部信息结构设计
  1. 确定局部范围
  2. 选择实体
  3. 选择实体的关键字属性
  4. 确定实体间的联系
  5. 确定实体的属性
3. 全局信息结构设计
  1. 属性冲突
  2. 全名冲突
  3. 结构冲突
逻辑结构设计方法
1. E-R图向关系模式转换
2. 数据模型的优化
3. 设计用户子模式
物理设计方法
1. 建立索引：只查，动态或静态
2. 建立聚集：相关数据集中存放物理块

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

第一节 SQL概述

一、SQL发展

二、SQL特点

三、SQL的组成

数据查询
数据定义：数据定义语言 DDL
1. CREATE 创建数据库或对象
2. ALTER 修改
3. DROP 删除
数据操纵：数据操纵语言 DML
1. SELECT 从表或视图中检索数据
2. INSERT 插入到表或视图中
3. UPDATE
4. DELETE
数据控制：数据控制语言 DCL
1. GRANT 权限
2. REVOKE 收回权限
嵌入式和动态SQL规则
SQL调用和会话规则

第二节 MySQL 预备知识

一、MySQL使用基础

LAMP：Linux、Apache、MySQL、PHP/Perl/Python
WAMP：Windows、Apache、MySQL、PHP/Perl/Python

二、MySQL中的SQL

常量
1. 字符串
2. 数值：整数、浮点
3. 十六进制：x X
4. 日期时间：‘2021-10-10’
5. b’value’ 位字段值
6. 布尔：TRUE 1、FALE 0
7. NULL
变量
1.
运算符
1. 算术运算
2. 位运算符
3. 比较运算
4. 逻辑运算
表达式
内置函数

第三节数据定义

一、数据库模式定义

创建数据库
1. CREATE DATABASE/SCHEMA [IF NOT EXISTS] dbName [DEFAULT] CHARACTER SET[=] ‘UTF8’ COLLATE utf8_general_ci
选择数据库
1. USE dbName
修改数据库
1. ALTER DATABASE/SCHEMA dbName …
删除数据库
1. DROP DATABASE/SCHEMA [IF EXISTS] dbName
查看数据库
1. SHOW DATABASE/SCHEMA [LIKE ‘pattern’|WHERE expr]
2. SHOW DATABASES/SCHEMAS 查看所有数据库

二、表定义

创建表
1. CREATE [TEMPORARY] TABLE tableName (cust_id INT NOT NULL AUTO_INCREMMENT,… cust_name CHAR(50) NOT NULL DEFAULT ‘XX’, PRIMARY KEY(id) );
2. TEMPORARY 临时表
3. 日期：date、timestamp、time
更新表: ALTER TABLE …
1. 新增列：ALTER TABLE db.table ADD COLUMN cust_age INT NOT NULL DEFAULT 0 AFTER cust_name
2. 修改列：ALTER TABLE db.table CHANGE COLUMN cust_age cust_old INT NOT NULL DEFAULT 0
3. 修改列默认值：ALTER TABLE db.table ALTER COLUMN cust_age SET DEFAULT 100
4. 更新列属性（不改列名）：ALTER TABLE db.table MODIFY COLUMN cust_age CHAR(20) FIRST/AFTER
5. 删除列：ALTER TABLE db.table DROP COLUMN cust_age;
重命名表
1. ALTER TABLE db.table RENAME TO db.table2
2. RENAME TABLE table1 TO table2, table3 TO table4
删除表
1. DROP TABLE [IF EXISTS] table1,table2…
查看表
1. SHOW TABLES
2. SHOW COLUMNS FROM table1 / DESCRIBE|DESC table

三、索引定义

索引创建
1. CREATE INDEX index1 ON db.table(cust_name(length) ASC|DESC)
2. CREATE INDEX index1 ON db.table(cust_name,cust_id) //组合索引
3. CREATE TABLE table1(id…INDEX index_id(id));//建表时创建索引
4. ALTER TABLE db.table ADD INDEX index_name(id);
索引查看
1. SHOW INDEX|INDEXES|KEYS FROM table1 FROM db1 WHERE
索引删除
1. DROP INDEX index_name ON table1
2. ALTER TABLE db.table DROP PRIMARY KEY, DROP INDEX index_name;

第四节数据更新

一、插入数据

INSERT [INTO] table[(column…)] VALUES|VALUE(…)
INSERT [INTO] table1 SET column_name=‘xxx’,…
INSERT [INTO] table1(column…) SELECT …

二、删除数据

DELETE FROM table1 [WHERE…] [ORDER BY] [LIMIT count]
TRUNCATE 重置了primaryId

三、修改数据

UPDATE table1 SET column=‘’ [WHERE…] [ORDER BY] [LIMIT count]

第五节数据查询

一、SELECT 语句

SELECT [ALL|DISTINCT|DISTINCTROW] … FROM table1 [WHERE…] [GROUP BY … [ASC|DESC],… [WITH ROLLUP]] [HAVING …] [ORDER BY…[ASC|DESC]] [LIMIT …]

二、列的选择与指定

指定列 SELECT * FROM db.table1
定义列别名使用 SELECT column AS column_alias FROM db.table1
替换查询结果：SELECT column CASE WHEN sex=‘M’ THEN ‘男’ ELSE ‘女’ END AS 性别 FROM db.table1
计算列值： SELECT id+100 FROM db.table1
聚合函数：COUNT,MAX,MIN…

三、FROM 子句与多表连接查询

交叉连接
1. SELECT * FROM db.table1 CROSS JOIN table2
内连接
1. SELECT * FROM db.table1 INNER JOIN table2 ON table.id = table.id
外连接
1. SELECT * FROM db.table1 LEFT JOIN table2 ON table.id = table.id

四、WHERE 子句与条件查询

比较运算
1. 模糊查询 LIKE '_ '代表单个字符，‘%’代表若干
判定范围
1. WHERE id BETWEEN 10 AND 20
2. WHERE id IN(1,2,4)
判定空值
1. WHERE name IS[NOT] NULL
子查询
1. WHERE id IN(SELECT …)

五、GROUP BY 子句与分组数据

SELECT
cust_address,cust_sex,
COUNT( * ) AS ‘人数’ //按人数汇总
FROM db.table1
GROUP BY cust_address,cust_sex,
WITH ROLLUP; //包含汇总项

六、HAVING 子句与GROUP BY 组合使用

SELECT
cust_address,cust_sex,
FROM db.table1
GROUP BY cust_address,cust_sex,
HAVING COUNT( * ) <= 3

七、ORDER BY 子句

SELECT
cust_address,cust_sex,
FROM db.table1
ORDER BY cust_address DESC,cust_sex DESC；

八、LIMIT 子句

SELECT
cust_address,cust_sex,
FROM db.table1
ORDER BY cust_id
LIMIT 4,3 | LIMIT 3 OFFSET 4 //从第5个开始，找3个

第六节视图

一、创建视图

CREATE VIEW view1 AS SELECT * FROM table1 WHERE sex=‘M’ WITH [CASCADED|LOCAL] CHECK OPTION;

二、删除视图

DROP VIEW IF EXISTS [RESTRICT|CASCADE]

三、修改视图定义

ALTER VIEW view1[(columns)] AS WHERE… WITH [CASCADED|LOCAL] CHECK OPTION;

四、查看视图定义

SHOW CREATE VIEW view1

五、更新视图数据

INSERT INTO view1 values(‘a’,‘1’)
UPDATE view1 SET sex=‘M’;
DELETE FROM view1 WHERE…

六、查询视图数据

SELECT name,sex FROM view1 WHERE…

第五章数据库编程

第一节存储过程

完成特定功能的SQL语句集，存储在数据库中的代码。

一、存储过程的基本概念

可增强SQL语句的功能和灵活性
良好的封装性
高性能
可减少网络流量
存储过程可作为安全机制：数据安全与完整性。

二、创建存储过程

修改语句结束符：DELIMITER $$
CREATE PROCEDURE
sp_name(IN cid INT,OUT name CHAR(20),INOUT age INT)
BEGIN
…
END $$

三、存储过程体

局部变量
1. DECLARE cid INT(10);
SET语句
1. SET cid=1001;
SELECT…INTO 语句,存储列数据列变量中。
1. SELECT cid,name INTO var_cid,var_name;
流程控制语句
1. IF…THEN…ELSE / CASE
2. 循环：WHILE,REPEAT,LOOP
游标
1. 声明游标：DECLARE cursor1 CURSOR FOR SELECT id FROM table1
2. 打开游标：OPEN cursor1
3. 读取游标：FETCH cursor1 INTO var1
4. 关闭游标：CLOSE cursor1

四、调用存储过程

CALL sp_name

五、删除存储过程

DROP PROCEDURE sp_name;

第二节存储函数

存储函数不能拥有输出参数
可以直接对存储函数进行调用。不用CALL
存储函数中必须有RETURN 语句。

一、创建存储函数

CREATE FUNCTION fn_name(parameter) //不能用输出参数
RETURNS ypte //声明返回值类型
BEGIN
…RETURN ‘’;
END $$

二、调用存储函数

SELECT fn_name(…);

三、删除存储函数

DROP FUNCTION [IF EXISTS] fn_name;

第六章数据库安全与保护

第一节数据库完整性

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

列级约束
1. 数据类型：类型、长度、精度。。。
2. 数据格式：统一格式
3. 取值范围：1-10
元组约束
1. 元组中字段间约束
表级约束
1. 关联表字段的约束

二、定义与实现完整性约束

实体完整性
1. 主键约束：自动产生PRIMARY KEY 索引
  1. 一表只能有一个主键
  2. 主键值唯一性原则。
  3. 复合主键最小化规则。
2. 候选键约束：自动产生UNIQUE 索引
  1. 可以有多个候选键
参照完整性：外键的校验
1. CREATE TABLE table1
2. (…
3. PRIMARY KEY(id)
4. FOREIGN KEY(id)
5. REFERENCES table2(cust_id) //外键存在才能插入数据
6. ON DELETE RESTRICT
7. ON UPDATE RESTRICT );
用户自定义完整性
1. 非空约束：NOT NULL
2. CHECK 约束

三、命名完整性约束

CONSTRAINT [symbol]

四、更新完整性约束

ALTER TABLE ADD CONSTRAINT

第二节触发器

一、创建触发器

CREATE TRIGGER trigger_name
trigger_timer // 触发时机：AFTER|BEFORE
trigger_event // 触发事件：INSERT|UPDATE|DELETE
ON table1
FOR EACH ROW
trigger_body // 执行事件（多个语句）：|BEGIN…END

二、删除触发器

DROP TRIGGER IF EXISTS table1.trigger1;

三、使用触发器

直接调用相关触发事件

第三节安全性与访问控制 MySQL

一、用户账号管理

创建用户账号
1. CREATE USER ‘user’@‘localhost’
2. IDENTIFIED BY ‘123’,
3. IDENTIFIED BY PASSWORD ‘123’;
删除用户
1. DROP USER user1,user2…
修改用户账号
1. RENAME USER old1 TO new1,…
修改用户口令
1. SET PASSWORD FOR user1=‘…’;

二、账户权限管理

查看权限：SHOW GRAINT FOR user1

权限授予
1. GRANT
2. priv_type // 权限类型：SELECT, UPDATE , DELETE,ALL
3. [(column_list)] // 指定具体列
4. ON
5. [object_type] // 权限授予对象：TABLE,FUNCTION,PROCEDURE
6. priv_level // 权限级别：库，表，视图， ,.* ,db.*,db.table
7. TO user1,user2;
8. WITH GRANT OPTION;
权限转移
1. TO user1 IDENTIFYED BY ‘123’ //不同密码，不同权限
2. WITH GRANT OPTION
权限撤销
1. REVOKE // （ALL PRIVILEGES, GRANT OPTION FROM user1,… 撤销全部）
2. priv_type // 权限类型：SELECT, UPDATE , DELETE,ALL
3. [(column_list)] // 指定具体列
4. ON
5. [object_type] // 权限授予对象：TABLE,FUNCTION,PROCEDURE
6. priv_level // 权限级别：库，表，视图， ,.* ,db.*,db.table
7. FROME user1,user2;

第四节事务与并发控制

一、事务的概念

数据操作序列，完整工作单元，不可分割。
BEGIN TRANSACTION 开始执行
COMMIT 提交
ROLLBACK 回滚

二、事务的特征 ACID

Atomicity 原子性：不可分割
Consistency 一致性：从一个一致性状态到另一个一致性状态
Isolation 隔离性：事务彼此是独立、隔离的。
Durability 持续性：事务提交后数据的变动是永久的。

三、迸发操作的问题

丢失更新
不可重复读
读脏数据

四、封锁

锁
1. Exclusive Lock X锁
2. Shared Lock S锁
并发控制
封锁粒度：粒度越细，开销越大
封锁级别
1. 0级
2. 1级：不重写未提交的更新数据
3. 2级：不重写与不读取更新数据
4. 3级：不重写与不读取更新数据，不写未提交的读数据。
活锁与死锁
1. 一次性锁请求
2. 锁请求排序
3. 序列化处理
4. 资源剥夺
可串行性
两段封锁法

第五节备份与恢复

数据丢失因素
硬件故障
软件故障
病毒
人为误操作
自然灾害
盗窃

备份数据
1. SELECT *
2. INTO OUTFILE
3. file_name //输出文件名
4. FILES
  1. TERMINATED BY ‘string’ // 字段间隔符号
  2. OPTIONALLY ENCLOSED BY ‘char’ // 包裹字符串的符号
  3. ESCAPED BY ‘char’ // 转义字符（ \n ）中的\
5. LINES TERMINATED BY ‘string’; // 行数据的结束标识
恢复数据
1. LOAD DATA
2. INFILE
3. file_name //输出文件名
4. FILES
  1. TERMINATED BY ‘string’ // 字段间隔符号
  2. OPTIONALLY ENCLOSED BY ‘char’ // 包裹字符串的符号
  3. ESCAPED BY ‘char’ // 转义字符（ \n ）中的\
5. LINES
  1. STARTING BY ‘string’; // 行前缀，若不包含被跳过
  2. TERMINATED BY ‘string’;// 行数据的结束标识