数据库期末复习(全面解析）
第一部分 基本理论
1.1 四个基本概念
- 数据 描述事物的符号记录
- 数据库 概括地讲，数据库数据具有永久存储、有组织和可共享的三个基本特点。
- 数据库管理系统 是位于⽤户和操作系统之间的⼀层数据管理软件
- 数据库系统 是由数据库、数据库管理系统（及其应⽤开发⼯具）、应⽤程序和数据库管理员（DBA）组成的存储、管理、处理和维护数据的系统。
1.2 主要功能
- 数据定义功能
- 数据组织、存储和管理
- 数据操纵功能
- 数据库的事务管理和运⾏管理
- 数据库的建⽴和维护功能
- 其他功能（通信功能，数据转换功能，互访和互操作功能等）
1.3 数据库主要特点
- 数据共享性 数据库中的数据可以被多个应用程序和用户共享，从而提高数据利用率和资源利用效率。
- 数据独立性 数据库中的数据与应用程序相互独立，使得应用程序可以更加方便地访问和管理数据，而不必关心数据存储的细节。
- 数据库管理系统 数据库管理系统(DBMS)是一个软件系统，它提供了对数据库进行管理和访问的功能。使用DBMS可以大大提高数据管理的效率和可靠性。
- 数据库安全性 数据库中的数据需要受到保护，因此数据库系统提供了各种安全机制，如数据备份、恢复、权限控制、加密等。
- 数据完整性 数据库中的数据需要满足一定的完整性约束条件，如主键、外键、唯一性约束等，以保证数据的正确性和一致性。
- 数据冗余度低 数据库系统通过数据表的设计和建立关系来减少数据的冗余度，从而降低数据存储空间和维护成本。
- 数据的持久性 在数据库系统中，数据是永久保存的，即使在计算机系统发生故障或断电时，也能够恢复之前的数据状态。
- 物理独⽴性 是指⽤户的应⽤程序与数据库中数据的物理存储是相互独⽴的。
- 逻辑独⽴性 是指⽤户的应⽤程序与数据库的逻辑结构是相互独⽴的。
1.4 数据库和数据库管理系统
1.4.1 定义
==数据库==是指在计算机系统中，由一组相关数据和其相应的处理程序组成的集合。它可以用来存储、管理和检索数据。
==数据库系统==是指一个包含一个或多个数据库的完整软件系统，它提供了对数据库进行管理和访问的功能。
==数据库管理系统（DBMS==）则是一个具体的软件系统，用于管理数据库系统中的数据。
数据库管理系统是数据库系统的另一种说法。二者概念相同,都是专门用于管理和控制数据库的软件系统。
1.4.2 关系
- 数据库 数据的集合,是静态的资源库。
- 数据库系统/数据库管理系统 是管理数据库的软件,用于操作数据库中的数据,是动态的管理工具。
- 数据库依赖于数据库系统来管理与维护,没有数据库系统就无法实现数据库的功能。
注意 MySQL SQL Server OpenGauss这类是属于数据库系统就是DBMS,用于管理数据库。
而Navicate 这类是属于客户端工具，是属于数据库管理工具,用于管理和控制DBMS的
1.4.3 特点
数据库特点 见 1.3
数据库管理系统特点
- 数据库具备的8条特点他都具备
- 并发控制 DBMS具有并发控制机制，可以支持多个用户或应用程序同时访问和修改数据，从而提高了数据的处理效率。
- 备份和恢复 DBMS提供了数据备份和恢复功能，保证数据在遭受灾难性损失时能够及时恢复。
1.5 数据库三级模式
- 外部模式（External Schema） 也叫子模式或用户模式，是指用户或应用程序看到的数据库模式。它描述了用户需要的数据视图和数据操作，包括数据的查询、插入、删除和更新等。外部模式与具体的应用程序相关，一个数据库可以有多个外部模式。
- 概念模式（Conceptual Schema） 也称为全局模式或逻辑模型，是数据库的全局逻辑结构。它描述了数据库的所有实体、关系、属性和约束条件，是所有用户和应用程序都可以看到的数据模型。概念模式独立于任何具体的物理存储设备或数据库管理系统，通常由数据库管理员或设计人员定义。
- 内部模式（Internal Schema） 也叫存储模式或物理模式，是数据库的内部表示形式。它描述了数据在物理存储设备上的存储方式、数据块的排列方式、索引结构、存储空间分配等细节问题。内部模式与具体的数据库管理系统和存储设备相关，是对物理存储和数据访问的最底层描述。
三级模式的主要目的是实现数据独立性,包括逻辑独立性和物理独立性
相关实例
- 内模式 数据文件结构,如结构化的数据块,记录格式,签索引等。
- 逻辑模式 一张学生表
- 外模式 针对不同的用户创建不同的视图
1.6 数据库二级映像
- 外部映像（External Mapping） 也叫子映像或用户映像，是指用户或应用程序需要的数据映像。它描述了用户需要的数据视图和数据操作，包括数据的查询、插入、删除和更新等。外部映像与具体的应用程序相关，一个数据库可以有多个外部映像。
- 内部映像（Internal Mapping） 也叫存储映像或物理映像，是数据库的内部表示形式。它描述了数据在物理存储设备上的存储方式、数据块的排列方式、索引结构、存储空间分配等细节问题。内部映像与具体的数据库管理系统和存储设备相关，是对物理存储和数据访问的最底层描述。
1.7 数据库数据模型
常见的数据模型有三种 层次模型、网状模型和关系模型。其中，关系模型是目前应用最广泛的一种数据模型
- 层次模型 使用一棵树来表示数据结构,树的每个节点都只有一个父节点。典型的数据库是IMS。层次模型数据结构简单,但是不能有效地支持1 N的关系。
- 网状模型 网状数据模型使用更一般的图结构来连接元组和集合。每个节点可以有多个父节点。网状模型比层次模型更复杂,也更灵活和功能更强。典型的数据库是DBTG
- 关系模型 使用二维表来表示数据结构。表是一个数据集合,每个表有一个名字,由行和列组成。同一列的元素具有相同的数据类型。典型的关系型数据库是MySQL、Oracle等。关系模型天然支持1 N的关系,并且有较强的理论基础。
第二部分 关系型数据库
2.1 数据库关系数据模型
关系数据模型是目前应用最广泛的数据模型之一，也是数据库管理系统（DBMS）中最基本的数据模型。它建立在集合论和谓词逻辑的基础上，使用表格来表示数据和数据之间的关系。下面介绍几个关系数据模型的核心概念。
- 关系 关系是由若干属性组成的数据集合，通常用一个二维表格来表示。每个关系都 有一个名称，例如“学生”、“教师”等。
- 属性 属性是关系中的列，表示数据的一个特征。每个属性都有一个名称和一个数据类型，例如“学号”、“姓名”等。
- 元组 元组是关系中的行，表示具体的一条记录。每个元组包含所有属性的值，例如“101”、“张三”等。
- 主键 主键是一组属性，能够唯一地标识关系中的每个元组。一个关系只能有一个主键，例如“学号”、“工号”等。
- 外键 外键是一个或多个属性，用于将不同的关系连接起来。外键定义了一个关系与另一个关系之间的链接关系，例如“学生表”中的“班级号”连接“班级表”中的“班级号”。
关系数据模型的优点在于
- 简单易懂 关系数据模型使用表格来表示数据和数据之间的关系，易于理解和使用。
- 数据独立性 关系数据模型实现了数据与应用程序相互独立，使得应用程序可以更加方便地访问和管理数据。
- 数据容易扩展 关系数据模型允许增加、删除或修改表格中的属性和元组，从而能够轻松地扩展数据。
- 数据安全性 关系数据模型提供了各种安全机制，包括数据备份、恢复、权限控制、加密等，来保护数据的安全性和完整性。
2.2 关系代数及其运算
- 选择（Selection） 从一个关系中选取满足特定条件的元组。
- 投影（Projection） 从一个关系中选取特定的属性列或字段，生成一个新的关系。
- 连接（Join） 将两个关系合并成一个新的关系，该新关系包含两个原始关系中的所有元组，并且满足某些特定条件(没指定的话，就按照相等元素，即自然连接）。
- 差集（Difference） 从一个关系中删除另一个关系中存在的元组，产生一个新的关系。
- 并集（Union） 将两个关系合并成一个新的关系，该新关系包含两个原始关系中的所有元组。
- 交集（Intersection） 仅保留在两个关系之间共同存在的元组，产生一个新的关系。
- 自然连接（Natural Join） 根据两个关系之间相等的属性值将它们连接起来形成一个新的关系。
小例子
//选择
/*
学号 姓名 性别 院系
1 张三 男 信息工程学院
2 李四 女 经济学院
3 王五 男 信息工程学院
选择性别为男的学生
学号 姓名 性别 院系
1 张三 男 信息工程学院
3 王五 男 信息工程学院
*/
-----------------------------------------------------------------
//投影
/*
学号 姓名 性别 院系 课程号 课程名 学分
1 张三 男 信息工程学院 1 数据库 4
3 王五 男 信息工程学院 1 数据库 4
从原表中投影到学号、姓名、课程号
学号 姓名 课程名
1 张三 数据库
3 王五 数据库
*/
-----------------------------------------------------------------
//连接
/*
学生表
学号 姓名 性别 院系
1 张三 男 信息工程学院
2 李四 女 经济学院
3 王五 男 信息工程学院
课程表
课程号 课程名 学分 院系
1 数据库 4 信息工程学院
2 经济学原理 3 经济学院
连接两表得到
学号 姓名 性别 院系 课程号 课程名 学分
1 张三 男 信息工程学院 1 数据库 4
3 王五 男 信息工程学院 1 数据库 4
*/
-----------------------------------------------------------------
//差集
/*
A表
学号 姓名
1 张三
2 李四
3 王五
B表
学号 姓名
2 李四
3 王五
A表减去B表
学号 姓名
1 张三
*/
-----------------------------------------------------------------
//并集
/*
A表并集B表
学号 姓名
1 张三
2 李四
3 王五
*/
-----------------------------------------------------------------
//交集
/*
A表交集B表
学号 姓名
2 李四
3 王五
*/
-----------------------------------------------------------------
//自然连接
/*
学生表
学号 姓名 性别 院系
1 张三 男 信息工程学院
2 李四 女 经济学院
3 王五 男 信息工程学院
选课表
学号 课程号
1 1
2 2
3 1
自然连接
学号 姓名 性别 院系 课程号
1 张三 男 信息工程学院 1
2 李四 女 经济学院 2
3 王五 男 信息工程学院 1
*/
2.3 查询关系代数
-- 1.选择（Selection） 从一个关系中选取满足特定条件的元组。
-- 关系代数表达式
σ score>80 (Student)
-- SQL代码
SELECT * FROM Student WHERE score > 80;
-- 2. 连接（Join） 将两个关系合并成一个新的关系，该新关系包含两个原始关系中的所有元组，并且满足某些特定条件。
-- 关系代数表达式
R ⋈ S
-- SQL代码
SELECT * FROM R JOIN S ON R.id = S.id;
-- 3. 差集（Difference） 从一个关系中删除另一个关系中存在的元组，产生一个新的关系。
-- 关系代数表达式
R - S
-- SQL代码
SELECT * FROM R WHERE id NOT IN (SELECT id FROM S);
-- 4. 并集（Union） 将两个关系合并成一个新的关系，该新关系包含两个原始关系中的所有元组。
-- 关系代数表达式
R ∪ S
-- SQL代码
SELECT * FROM R UNION SELECT * FROM S;
-- 5. 交集（Intersection） 仅保留在两个关系之间共同存在的元组，产生一个新的关系。
-- 关系代数表达式
R ∩ S
-- SQL代码
SELECT * FROM R INTERSECT SELECT * FROM S;
-- 6. 自然连接（Natural Join） 根据两个关系之间相等的属性值将它们连接起来形成一个新的关系。
-- 关系代数表达式
R ⋈⋈ S
-- SQL代码
SELECT * FROM R NATURAL JOIN S;
2.4 查询优化基本概念及策略
在数据库管理系统中，查询优化是一种提高查询效率的方法。它旨在从多个可能的执行计划中选择一个开销最小的计划。这对于大型数据库和复杂查询尤为重要，因为不合适的查询计划可能导致性能瓶颈。下面是一些关于查询优化的基本概念和策略
- 索引 索引可以显著提高查询性能。根据实际需求，可以创建合适的索引来提高特定查询的速度。但请注意，过多的索引可能会影响数据更新和插入的性能。
- 查询重写 查询重写涉及到将原始查询转换为等价的、性能更好的形式。例如，将子查询转换为连接操作。
- 查询分解 将复杂查询分解成多个简单查询，然后再组合结果。这样可以充分利用数据库的优化机制。
- 连接策略(考点） 连接操作的顺序和方式对查询性能有很大影响。主流的连接策略有嵌套循环连接、哈希连接和排序归并连接。此外，有一种策略叫做“后执行自然连接”，它是指在其他操作（如投影、选择等）之后再执行连接操作。这样可以减少连接过程中的数据量，提高查询效率。
- 缓存机制 缓存机制是将经常使用的数据保存在内存中，以便下次访问时能够更快地响应请求。缓存机制可以有效地减少查询的响应时间，但是需要注意缓存的清理机制，以避免脏数据的产生。
- 数据库分区 数据库分区是将一个大的数据库拆分成多个小的数据库，以便更好地管理和查询数据。分区可以根据数据的特性进行划分，例如按照时间、地理位置等方式进行划分。
- 数据库表设计 数据库表设计是优化查询的重要因素之一。通过规范化和反规范化等手段，可以使得查询更加高效，同时避免冗余数据的存在。
第三部分 SQL语言（上）
3.1基本概念
基本理论
SQL是Structured Query Language的缩写，意思是结构化查询语⾔，是⼀种在数据库管理系统中查询或对数据库⾥⾯的数据进⾏更改的语⾔
SQL语言分类
数据定义语⾔DDL（Data Ddefinition Language）
SQL数据定义语⾔主要⽤来定义逻辑结构，包括定义基表，视图和索引。删除表 定义表 修改表
数据查询语⾔DQL（Data Query Language）
SQL的数据查询语⾔主要⽤来对数据库中的各种数据对象进⾏查询。
数据操纵语⾔DML（Data Manipulation Language）
SQL的数据操纵语⾔，⽤于改变数据库中的数据，包括插⼊，删除，修改
数据控制功能DCL（Data Control Language）
SQL的数据控制语⾔，对表和视图的授权，完整性规则的描述以及事务开始和结束等控制语句。
整体分类检查
- CREATE DATABASE 创建数据库
- 删除数据库
- CREATE TABLE 创建表格
- ALTER TABLE 修改表格结构
- DROP TABLE 删除表格
- CREATE INDEX 创建索引
- DROP INDEX 删除索引
- SELECT 查询数据
- INSERT INTO 插入数据
- UPDATE 更新数据
- DELETE FROM 删除数据
- 创建视图
- 修改视图
- 删除视图
- 查询视图
- 授予权限
- 回收权限
- 创建用户
- 删除用户
- 创建角色
- 删除角色
下面都有解析，一个一个对着
3.2特点
- 一体化。SQL语言风格统一，可以完成数据库活动中的全部工作，包括创建数据库、定义模式、更改和查询数据以及安全控制和维护数据库等。
- 高度非过程化。在使用SQL语句访问数据库时，用户没有必要告诉计算机如何一步步完成任务，只需要用SQL语言描述要做什么就行了，数据库管理系统会自动完成全部工作。
- 面向集合的操作方式。SQL语言采用集合操作方式，不仅查询结果是记录的集合，而且插入、删除和更新操作也是记录的集合。
- 提供多种方式使用。SQL既是自含式语言，又是嵌入式语言。自含式语言可以独立地联机交互，即用户可以直接以命令的方式交互使用。嵌入式语言是指SQL可以嵌入到向Java、C#等高级程序设计语言中使用。
- 语言简洁。SQL语法简单，易学易用
3.3数据类型
以在 MySQL 中的数据类型为例，有三种主要的类型 文本、数字和日期/时间类型。
Text 类型
| 数据类型 | 描述 |
|---|---|
| CHAR(size) | 保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。 |
| VARCHAR(size) | 保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释 如果值的长度大于 255，则被转换为 TEXT 类型。 |
| TINYTEXT | 存放最大长度为 255 个字符的字符串。 |
| TEXT | 存放最大长度为 65,535 个字符的字符串。 |
| BLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。 |
| MEDIUMTEXT | 存放最大长度为 16,777,215 个字符的字符串。 |
| MEDIUMBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。 |
| LONGTEXT | 存放最大长度为 4,294,967,295 个字符的字符串。 |
| LONGBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。 |
| ENUM(x,y,z,etc.) | 允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。注释 这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值 ENUM('X','Y','Z') |
| SET | 与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。 |
Number 类型
| 数据类型 | 描述 |
|---|---|
| TINYINT(size) | -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。 |
| SMALLINT(size) | -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。 |
| MEDIUMINT(size) | -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。 |
| INT(size) | -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。 |
| BIGINT(size) | -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。 |
| FLOAT(size,d) | 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
| DOUBLE(size,d) | 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
| DECIMAL(size,d) | 作为字符串存储的 DOUBLE 类型，允许固定的小数点。 |
这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。
Date 类型
| 数据类型 | 描述 |
|---|---|
| DATE() | 日期。格式 YYYY-MM-DD注释 支持的范围是从 '1000-01-01' 到 '9999-12-31' |
| DATETIME() | *日期和时间的组合。格式 YYYY-MM-DD HH MM SS注释 支持的范围是从 '1000-01-01 00 00 00' 到 '9999-12-31 23 59 59' |
| TIMESTAMP() | *时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00 00 00' UTC) 至今的描述来存储。格式 YYYY-MM-DD HH MM SS注释 支持的范围是从 '1970-01-01 00 00 01' UTC 到 '2038-01-09 03 14 07' UTC |
| TIME() | 时间。格式 HH MM SS注释 支持的范围是从 '-838 59 59' 到 '838 59 59' |
| YEAR() | 2 位或 4 位格式的年。注释 4 位格式所允许的值 1901 到 2155。2 位格式所允许的值 70 到 69，表示从 1970 到 2069。 |
即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，
比如 YYYYMMDDHHMMSS、
YYMMDDHHMMSS、
YYYYMMDD 或 YYMMDD。
3.4 模式与库相关操作
create schema "hfx" authorization dbuser;
-- 模式定义
-- 模式定义加视图
drop schema "hfx" CASCADE;
-- 模式删除
-- 注意 在删除时，必须接上CASCADE 或者 RESTRICT
-- 关系
-- CASCADE是(级联) 删除模式的同时也把该模式的所有数据库对象删除.
-- RESTRICT(限制) 如果该模式下有下属对象,⽐如表视图 就拒绝这个删除语句的执⾏
CREATE DATABASE testStudent;
-- 创建数据库
DROP DATABASE testStudent;
-- 删除数据库
3.5 基本表相关操作
CREATE TABLE table_name (column_name column_type); -- 通用语法
CREATE TABLE IF NOT EXISTS `stuTab`(
`id` INT UNSIGNED AUTO_INCREMENT primary key, -- 主键且是自增属性
`username` VARCHAR(100) NOT NULL, -- 非空属性
`age` VARCHAR(40) NOT NULL,
`grade` VARCHAR(40) NOT NULL,
PRIMARY KEY ( `runoob_id` )
)
-- 1.创建基本表
drop table hfX_tab CASCADE;
-- 2.删除基本表
-- 注意
-- 使用CASCADE 是如果表有外键,视图 触发器的话,也会强⾏删除
-- RESTRICT会拒绝删除
-- 3.修改表（放下面了）
ALTER TABLE <表名> ADD COLUMN <列名> <类型>
ALTER TABLE stuTab ADD COLUMN comments VARCHAR(100) NOT NULL;
-- 3.1 (添加属性）
ALTER TABLE <表名> CHANGE COLUMN <旧列名> <新列名> <新列类型>
-- 或者
ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
-- 或者
MODIFY COLUMN <列名> <类型>
ALTER TABLE 'stuTab' CHANGE COLUMN 'comments' 'otherComments' VARCHAR(50) NOT NULL;
-- 3.2 (修改属性）
ALTER TABLE <表名> DROP COLUMN <列名>
ALTER TABLE 'stuTab' DROP COLUMN 'otherComments';
-- 3.3 (删除属性）
3.6 索引相关操作
create index <索引名> on <表名> (列名) -- 一般索引
create UNIQUE index <索引名> on <表名> (列名) -- 唯一索引
create CLUSTER index <索引名> on <表名> (列名) -- 聚簇索引
CREATE INDEX 'stuTab_grade_idx' ON 'stuTab' (grade);
-- 3.4 (索引的建立）（建立分为 一般 聚簇索引 唯一索引）
ALTER TABLE 'stuTab' RENAME INDEX stuTab_grade_idx TO newIndex;
-- 3.5 (索引的修改）
DROP INDEX <索引名>
ALTER TABLE stuTab DROP INDEX newIndex;
-- 3.6 (索引的删除）
3.7 相关注意点
主键与唯一索引的关系
主键一定是唯一性索引，唯一性索引并不一定就是主键。
一个表中可以有多个唯一性索引，但只能有一个主键。
主键列不允许空值，而唯一性索引列允许空值。
索引可以提高查询的速度
当没有设定主键时，非空唯一索引自动称为主键。
表中的数据在此列必须是不相同（不管是主键还是唯一索引，都是这样）
一般索引与唯一索引与聚簇索引的关系
首先 索引主要用于提高数据库的查询性能；
一般索引
简单的索引,允许索引列的值重复。
通过一般索引可以查询指定的值,也可以查询范围内的值。
一般索引的数据列允许有NULL值。
唯一索引
- 索引列的值必须唯一,不允许重复。
- 可以通过唯一索引快速查询到指定的值,但无法进行范围查询。
- 唯一索引的数据列允许有NULL值,但NULL值只能有一个。
聚簇索引
- 将表中行数据的物理顺序与索引值的逻辑顺序相对应。
- 聚簇索引的表数据行会以索引值的顺序存储,所以通过聚簇索引查询可以直接定位到数据。
- 每个表只能有一个聚簇索引。
- 聚簇索引不允许有NULL值。
所以，关系总结为
- 唯一索引是在一般索引的基础上加了唯一性约束。
- 聚簇索引会对表数据进行物理重组,其他索引则只保存索引值和行指针的对应关系。
- 相比一般索引和唯一索引,聚簇索引可以更快地查询数据。
- 一个表只能有一个聚簇索引,但可以有多个一般索引或唯一索引。
第四部分 SQL语言（中）
4.1 一般查询部分
SELECT * FROM "hfX_tab";
-- 查询所有
SELECT id as "编号" , titlt as "名称" from "hfX_tab" as "学生表";
-- 查询部分列
SELECT distinct titlt as "名称" from "hfX_tab";
-- 查询去重
4.2 代运算符的查询部分
所涉及的表
| 符号 | 描述 | 备注 |
|---|---|---|
| = | 等于 | |
| != | 不等于 | |
| > | 大于 | |
| < | 小于 | |
| <= | 小于等于 | |
| >= | 大于等于 | |
| BETWEEN | 在两值之间 | >=min&&<=max |
| NOT BETWEEN | 不在两值之间 | |
| IN | 在集合中 | |
| NOT IN | 不在集合中 | |
| <=> | 严格比较两个NULL值是否相等 | 两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0 |
| LIKE | 模糊匹配 | |
| REGEXP 或 RLIKE | 正则式匹配 | |
| IS NULL | 为空 | |
| IS NOT NULL | 不为空 |
SELECT * FROM "hfX_tab" where title="h1";
-- 约束字符WHERE
SELECT * FROM "hfX_tab" where score < = 85;
-- 相关比较符
SELECT * FROM "hfX_tab" where age BETWEEN 80 AND 100;
SELECT * FROM "hfX_tab" where age in（80,100）;
-- 范围运算符 between and 和in
select * from table1 where name like '%刘浩%' -- 查询name字段中包含有 刘浩 的。
select * from table1 where name like '刘* ' -- 查询name字段中以 刘 开头。
select * from table1 where name like '_刘' --
-- 模糊匹配 like 百分号与下划线关系
SELECT * FROM "hfX_tab" where title is NULL;
-- 判断空字符 null
SELECT * FROM "hfX_tab" where title="h1" AND id="22";
SELECT * FROM "hfX_tab" where title="h1" or id="22";
-- and和or(就是字面意思理解就可以了)
4.3 聚集函数与分组查询
具体的聚集函数
| 聚集函数 | 功能 | 格式 | 数据类型 |
|---|---|---|---|
| count | 求符合条件的记录数 | count(列名) 或者 count(DISTINCT 列名) | |
| MAX | 求某一列的最大值 | MAX(列名) | 列的类型可以比较大小 |
| MIN | 求某一列的最小值 | MIN(列名) | 列的类型可以比较大小 |
| AVG | 求某一列的平均值 | AVG(列名) | 列的类型只能是数值型或者货币型 |
| SUM | 求某一列的总和 | SUM(列名) | 列的类型只能是数值型或者货币型 |
select count（*）FROM "hfX_tab";
-- count（*） 不包含重复的
select count（title）FROM "hfX_tab" WHERE age = "22";
-- count（列名）包含重复的 但不包含空值
-- 想要不重复的 加distinct就行
select sum（salary）FROM "hfX_tab";
-- sum（列名）
select AVG（salary）FROM "hfX_tab";
-- avg（列名）
select MAX（salary）FROM "hfX_tab";
-- max（列名）
select MIN（salary）FROM "hfX_tab";
-- min（列名）
SELECT * from "hfX_tab" WHERE kind="student" GROUP BY grade
-- 分组 group by
-- 例子的意思是 查询属于学生类型的人所有信息 并按找年级分组
SELECT * from "hfX_tab" WHERE kind="student" GROUP BY grade HAVING COUNT（*）>100;
-- 对每组的组相同数据进行分组 用having
-- 例子的意思是 找出属于学生类型的人所有信息 并按找年级分组，只查询其中人数大于100的年级；
4.4 连接查询部分
select userTab，goodsTab where userTab.id=goodsTab.id
-- (一般项目不会起名为id ；对应是user表的外键就可以 也可能是userID/......)
-- 两张基本表进行基础连接 （求两张表的笛卡尔积）
select userTab as "ec1"，userTab as "ec2" where ec.id=ec2.id
-- 自身连接
select * from userTab left join goodsTab ON userTab.id=goodsTab.id
-- 左外连接（外连接每个数据库语言不通，这里演示的是mysql的连接）
-- 会将左表中的全部数据拿出来 按照匹配条件和右表中数据做对应，左表中没有匹配上的数据会被置空（null）
-- 右外连接就是将left改成right
-- 具体不解释 将上面的外面的反过来就行
-- 外连接比较抽象，这里没有实例
-- 看这里 https://blog.csdn.net/wulimingde/article/details/109134307
-- 多表查询部分（两个表以上的连查）
select * from goodsTab where userID in（select id from userTab where age<18）
-- 嵌套查询
-- 例子的意思是检索网站中年龄小于18岁的用户所购买的商品的全部信息
-- 嵌套查询简单来说就是 底层的检索结果是上层检索的准备
select * from emp where scal > any (select scal from emp where deptno=10 )and deptno <> 10;
-- 查询工资大于部门编号是10的任意的一个员工工资即可的其他部门的员工信息
select * from emp where scal > all (select scal from emp where deptno =30) and deptno <>30;
-- 查询工资大于部门编号是30的所有的员工工资的员工信息
-- 所以 大约等于下面这个
select * from emp where scal > (select max(scal) from emp where deptno =30) and deptno <> 30;
-- 带 ANY ALL的子查询 就不用写in了
-- 意思是
-- 假设子查询 查出来了 13和16和12和11四条数据
-- 那么>ANY()就是大于11的即可
-- 那么>ALL()就是大于16的才可以
SELECT s.Sname FROM student s
WHERE NOT EXISTS
(SELECT * FROM sc, course c WHERE sc.sno = s.sno AND sc.cno = c.cno AND c.cname = '数字电路')
-- 查询没有选修课程 数字电路 的同学
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT * FROM Course WHERE NOT EXISTS
(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno)
);
-- 查询选修了全部课程的学生姓名
-- 上面是带exists的子查询（经典例子，理解上面两个这个应该就OK了）
-- 例子的意思是
-- NOT EXISTS 理解为意思取反即可 where NOT EXISTS
4.5 集合查询
(select Mname from mangerTab)
union
(select Tname from teacherTab)
-- 求并集 返回两个子结果集的并集
-- 两个select中间加上UNION即可
(select Mname from mangerTab)
INTERSECT
(select Tname from teacherTab)
-- 求交集 返回两个结果集的交集（返回相同记录）
-- 两个select中间加上INTERSECT即可
(select Mname from mangerTab)
EXCEPT
(select Tname from teacherTab)
-- 求差集
-- 两个select中间加上EXCEPT即可
-- 意思很好理解，就不解释了，想看例子的可以看这个
-- 链接 https://blog.csdn.net/sinat_32832727/article/details/54177602
第四部分 SQL语言（下）
4.1 元组操作部分
-- 增加元组 （注意！ 注意！ 如果数据库中的id或其他列具有自增属性，插入时，不能手动插入改数据，否则直接报错）
INSERT INTO table_name (column1,column2,column3,所有列名)VALUES (value1,value2,value3，所有列值);
INSERT INTO stuTab (id,username,age,grade)VALUES (1,'liuhf',20,2021);
-- 指定 所有字段
INSERT INTO userTab (id,name,age) VALUES (17,"张三",22);
-- 指定部分字段(前提是有默认值或可为空才行)
INSERT INTO userTab VALUES (18,"李四",19);
INSERT INTO stuTab VALUES (2,'liuhfxxx',24,2022);
INSERT INTO stuTab VALUES (NULL,'lzteee',28,2022);//有自增属性也要填写一个null或者0的
-- 不指定，默认全部字段
insert into stuTab values
(NULL,'小明', 33, 2029),
(NULL,'小黑', 88, 2022),
(NULL,'小张', 73, 2020),
(NULL,'小理', 63, 2029),
(NULL,'小粉', 53, 2028),
(NULL,'小绿', 43, 2021),
(NULL,'小刘', 23, 2022);
-- 批量添加数据
update 表名 set 列名1=新数据1,列名2=新数据2列名3=新数据3 where 查询条件
UPDATE stuTab SET username='小哦222222',age=39 WHERE id=11;
-- 修改元组
delete from 表名 where 查询条件;
DELETE FROM stuTab WHERE username='小张';
-- 删除元组
4.2 视图操作部分
4.2.1 视图概念与理论
- 视图(VIEW)也被称作虚表，即虚拟的表，是⼀组数据的逻辑表示,其本质是对应于⼀条SELECT 结果集被赋予⼀个名字，即视图名字
- 视图本身并不包含任何数据，它只包含映射到基表的⼀个查询语句，当基表数据发⽣变化，视图随即变化
- ⽬的 ⽅便,简化数据操作
- 当我们业务需求要查出多张表的数据,这时我们可能会关联多张表查询处理，如果这个联合查询的sq语句很复杂，会严重影响了查询效率.这个时候我们就可以创建视图,查询时候只需要查询相应的试图就可以！
4.2.2 视图操作代码
CREATE VIEW userView("姓名"，"年龄"，"时间") AS SELECT (name，age，time) from userTab
where age>18 WITH CHECK OPTION -- 例子
-- 创建视图
-- 当使用了 [WITH CHECK OPTION] 约束后，视图会对基本表增添的数据进行最初的约束检验，符合之后才会加到视图中
DROP VIEW userView CASCADE;
-- 删除视图 有cascade
-- CASCADE与之前一样，是级联的意思，即当他存在着子视图的时候，会一同删除了。
-- 如果不写，且存在子视图，会报错（拒绝删除）
select * from userView where age<60;
-- 查询视图
-- 使用视图和使用表完全一样，只需要把视图当成一张表就OK了。视图是一张虚拟表。
-- （视图只是映射，需要反映到基本表，要么直接修改基本表，要么两个都修改）
-- 修改视图
-- 对于一般视图，要注意
-- 1. 视图修改，会影响基本表数据；
-- 2. 基本表修改，也会影响到视图的数据
-- 3. 因为本身就只有一份数据，视图只是映射的信息
4.2.3 一般视图与物化视图的区别
一般视图
- 虚拟表,没有数据的实际存储,只保存 SQL 语句
- 当访问视图时,会执行其 SQL 语句并返回结果。
- 一般视图的数据是动态的,取决于其 SQL 语句查询的基表数据。
- 创建语法
CREATE VIEW view_name AS select_statement
物化视图
- 实际上是一个表,存储了查询结果的数据
- 数据与基表分离,数据变化时需要定期刷新与基表同步
- 相比一般视图,查询性能更高,因为直接从已存储的数据中获取结果。
- 创建语法
CREATE MATERIALIZED VIEW view_name AS select_statement
相关小例子
-- 一般视图
CREATE VIEW prod_view AS
SELECT * FROM products;
-- 物化视图
CREATE MATERIALIZED VIEW prod_mview AS
SELECT * FROM prod_view;
-- 刷新物化视图
REFRESH MATERIALIZED VIEW prod_mview;
-- 简析
-- 我们定义了一个基于 products 表的一般视图 prod_view
-- 然后创建物化视图 prod_mview ,内容来自 prod_view 的查询结果
-- 通过 REFRESH 语句可以定期刷新 prod_mview,保证其数据与基表 products 同步
-- 这样既可以通过 prod_view 动态地链接基表,也可以直接查询 prod_mview 获得更高的性能
两者关系
- 物化视图的内容来自其选择语句查询的结果,而选择语句可以定义在一般视图上
- 所以可以将一般视图的内容定期刷新到物化视图中,物化视图实时保持与基表的数据同步
- 这种结合可以同时获得一般视图的动态联系和物化视图的查询性能
第五部分 数据库安全控制
5.1 数据库安全概念
-- 第一类 用户身份鉴别
-- 静态口令鉴别
-- 动态口令鉴别
-- ⽣物特征鉴别
-- 智能卡鉴别
-- 第二类 存取控制
-- 就是我们⽤户可以⾃定义和分配其他⽤户的操作
-- 下面有具体阐述
5.2 自主存取控制的代码部分
5.2.1 基本权限控制
-- 首先，要清楚数据库中的权限控制
-- 数据库的权限控制主要通过用户、权限和用户组实现。主要的权限有
-- SELECT 允许读取数据
-- INSERT 允许插入数据
-- UPDATE 允许更新数据
-- DELETE 允许删除数据
-- CREATE 允许创建数据库和表
-- DROP 允许删除数据库和表
-- GRANT 允许授予权限
-- REVOKE 允许撤销权限
-- ALTER 允许修改表结构
-- INDEX 允许创建和删除索引
-- etc.
-- 涉及的相关操作
-- 创建用户
CREATE USER '用户名' IDENTIFIED BY '密码';
-- 删除用户
DROP USER '用户名';
-- 修改用户密码
SET PASSWORD FOR '用户名' = PASSWORD('新密码');
-- 创建角色
create role <⻆⾊名>
create role teacherRole;
-- 授予权利的方式
GRANT 权限 ON 数据库名.表名 TO '用户名' With grant option;
GRANT select ON database1.userTab TO 'xiaoming' With grant option;
GRANT select ON database1.userTab(userName) TO 'xiaoming' With grant option;
-- 注意 With grant option⼦句，代表普通⽤户获权后,可把⾃⼰的权限授予其他⽤户
-- 角色权限授予
grant select on userTab to "teacherRole","stuRole"
-- 挥手权限
REVOKE <权限> ON <数据对象> FROM <⽤户名> cascade
REVOKE select ON userTab(userName) FROM "xiaoming" cascade;
5.2.2 视图机制
-- 实现目的 为不同的⽤户定义不同的视图，把不需要的数据给隐藏起来，这样⽤户就不会进⾏误操作
-- 例子A
create view userTab_Stu as
select * from students where age='18'
grant select on userTab_Stu to "学生A"；
-- 例子B
grant all privileges on userTab_Stu to "班长";
5.2.3 审计与数据加密
-- 审计
-- 概念 把对数据库的所有操作都记录到审计⽇志中，然后就可以通过⽇志审查这个⾥⾯是否有⼀些⾮法
-- 1.例子 对修改用户表的数据进行审计
-- audit update on userTab;
-- 2.例子 取消对用户表的所有审计；
-- noaudit update on userTab;
-- 数据加密 通过⼀些加密算法，把明⽂变成密⽂，这样别⼈就⽆法查看
-- 例如基于MD5加密方式 先对数据进行MD5的加密，在将秘文中的数据添加一些特有标记位；即可达到机密的效果
第六部分 数据库完整性
6.1 完整性概述
6.1.1 正确性
符合现实世界的描述
6.1.2 相容性
同⼀对象在不同表⾥⾯是符合逻辑的 ⽐如我的地址,年龄 在两个表⾥⾯都应⼀致
6.1.3 维护完整性
- 提供定义完整性约束条件的机制
- 提供完整性检查的⽅法
- 进⾏违约处理
6.2 三大完整性
6.2.1 实体完整性
Create table t1(
id int primary key auto_increment,-- 在表级定义实体完整性
name varchar(100)，
age INT
);
create table SC(
Sno char(9) not null,
Cno char(4) not null,
Grade int,
primary key (Sno, Cno), -- 在表级定义实体完整性
);
-- 实体完整性
-- 主码唯一且非空
-- 上面两个例子即为在创建表时设立主键的形式
6.2.2 参照完整性
create table SC(
Sno char(9) not null,
Cno char(4) not null,
Grade int,
primary key (Sno, Cno), /*在表级定义实体完整性*/
foreign key (Sno) references Student(SNO), /*在表级定义参照完整性*/
foreign key (Cno) references Course(Cno) /*在表级定义参照完整性*/
);
-- 参照完整性
-- 对于外码 要么没有，要么只有一个
-- 也就是外键
6.2.3 用户自定义完整性
-- 1.定义NULL/NOT NULL约束
CREATE TABLE nation2(/*国家表*/
nationkey INTEGER PRIMARY KEY, /*国家编号*/
name CHAR(25)NOT NULL, /*国家名称*/
regionkey INTEGER REFERENCES region(regionkey),/*地区编号*/
comment VARCHAR(152)NULL /*备注*/
);
-- 2.定义属性的缺省值
-- 定义零件基本表的制造厂的缺省值为，表示其他厂商
CREATE TABLE part2(/*零件基本表*/
partkey INTEGER PRIMARY KEY, /*零件编号*/
name VARCHAR(100), /*零件名称*/
mfgr CHAR(50)DEFAULT '0', /*制造厂*/
brand VARCHAR(50), /*品牌*/
type VARCHAR(25), /*零件类型*/
size INTEGER, /*尺寸*/
container CHAR(10), /*包装*/
retailprice REAL, /*零售价格*/
comment VARCHAR (23) /*备注*/
);
-- 3.定义属性UNIQUE约束
-- 定义地区表的地区名称必须唯一的约束
CREATE TABLE region2(/*地区表*/
regionkey INTEGER PRIMARY KEY, /*地区编号*/
name CHAR(25)UNIQUE, /*地区名称*/
comment CHAR(152) /*备注*/
);
-- 4.使用CHECK
-- 使用CHECK定义订单表中某些属性应该满足的约束
CREATE TABLE orders2(/*订单表*/
orderkey INTEGER PRIMARY KEY,/*订单编号*/
custkey INTEGER,/*顾客编号*/
/*订单状态有中，0表示尚未处理，1表示已经处理*/
orderstatus CHAR(1)CHECK(orderstatus IN('0','1')),/*订单状态*/
totalprice REAL,/*订单总金额*/
orderdate DATE,/*订单日期*/
/*订单优先级有三等，0表示最高，1次之，2表示最低*/
orderpriority CHAR(15)CHECK (orderpriority IN('0','1','2')),/*订单优先级别*/
clerk CHAR(15),/*记账员*/
shippriority INTEGER ,/*运输优先级别*/
comment VARCHAR(79),/*备注*/
);
-- 用户自定义完整性
-- 3种主要形式
-- 1. 非空 not null
-- 2. 定义属性的缺省值 default
-- 3. 列值唯一 unique
-- 4. 满足一个表达式，check来写
6.3 断言
通过声明性断言(declarative assertions)来制定更具一般性的约束。
每个断言都被赋予一个名字，<CHECK子句>中的约束条件与WHERE子句的条件表达式类似
CREATE ASSERTION <断言句> <CHECK 子句>
CREATE ASSERTION duanyan1
CHECK(60>=(SELECT COUNT(*)
FROM Course,SC WHERE SC.Cno=Course.Cno AND Course.Cname=’数据库’)
);
-- 限制数据库课程选修人数不超过60人
-- 创建断言
DROP ASSERTION <断言名字>
-- 删除断言
6.4 触发器
6.4.1 基础概念
- 触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。
- 遵循 事件-条件->动作 的规则
- 意思是 执行时触发了预定的规则，则执行相应的动作，否则不做
6.4.2 触发器分类
| 类型 | 解释 |
|---|---|
| before | 表示在数据库动作之前触发器执行 |
| after | 表示在数据库动作之后出发器执行 |
| 类型 | 解释 |
|---|---|
| insert | 数据库插入会触发此触发器 |
| update | 数据库修改会触发此触发器 |
| delete | 数据库删除会触发此触发器 |
| 类型 | 解释 |
|---|---|
| for each row | 对表的每一行触发器执行一次（行级触发器） |
| 默认 | 则只对一整个语句执行一次（语句级触发器） |
创建触发器的完整语法规则
CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
-- trigger_name 标识触发器名称，用户自行指定
-- trigger_time 标识触发时机，取值为 BEFORE 或 AFTER
-- trigger_event 标识触发事件，取值为 INSERT、UPDATE 或 DELETE
-- tbl_name 标识建立触发器的表名，即在哪张表上建立触发器
-- trigger_stmt 触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句
CREATE TRIGGER test_user
AFTER INSERT ON "userTab"
FOR EACH ROW
BEGIN
INSERT INTO bysj_dt VALUES (new.id,new.et_name);
END
-- 常见触发器案例（行级 插入 后执行触发器）
DROP TRIGGER test_user
-- 删除触发器
show triggers
-- 查看所有触发器
第七部分 关系型数据库理论
7.1 候选码与最小候选码
7.1.1 概念
- 候选码(Candidate Key) 能够唯一标识表中的每条记录的数据集合。一个表可以有多个候选码
- 最小候选码(Minimal Candidate Key) 一个表中不包含冗余的候选码。它是所有候选码中包含数据量最少的一个。
样例
-- 例如,在学生表中,学号、姓名加出生日期都可以唯一标识一条学生记录,所以它们都是候选码。
-- 例如,在学生表中,学号长度最小,只需要一个属性即可唯一标识一条记录,所以学号是最小候选码。而姓名加出生日期包含两个属性,相对来说数据量更大一些。
-- 所以 一个表的主键必须是最小候选码,但最小候选码不一定是主键。主键是表中记录的唯一标识,是实现其他表与之建立关系的依据,所以要求不可为空和不可重复。
7.1.2 求候选码
-- 第一步，根据规则筛选
-- 规则是
-- 1.只出现在左边的⼀定是候选码
-- 2.只出现在右边的⼀定不是候选码
-- 3.左右都出现的不⼀定
-- 4.左右都不出现的⼀定是候选码
-- 第二部
-- 再求确定的候选码的闭包,如果可以推出全部,那么当前确定的就是候选码
7.1.3 超码与相关概念
-- 超码 能表示出所有属性的集合, ⽐如 (BDA),(BDC),(BDE) BDCA BDEA ABCDE
-- 通过上面可以看出候选码肯定是，再在候选码基础上加数也一定是（因为更能推出全部关系）
-- 主码 从候选码⾥⾯任意跳出⼀个 作为主码
-- 主属性 包含在所有候选码的属性 比如ABCDE
-- 非主属性 不包含在候选码中的属性 ,上题为G
-- 全码 所有的属性都是主码
7.2 三大范式与BCNF
7.2.1 范式的定义分类
-- 第一范式 1NF
-- 定义 所有字段值都是不可分解的原⼦值
-- 人话 上面已经是人话了
-- 第二范式 2NF
-- 定义 包含⾮主属性对码的部分函数依赖
-- 人话 ⼀个表中只能保存⼀种数据，不可以把多种数据保存在同⼀张数据库表中
-- 例如 商品编号与订单编号就是两类数据，应该分开两个表进行存储
-- 第三范式 3NF
-- 定义 不包含⾮主属性对码的传递函数依赖
-- 人话 数据表中的每⼀列数据都和主键直接相关，而不能间接相关
-- 例如 作为用户表的主键（userID），用户购买的商品与userID是间接的关系，而不是直接关联，
-- 所以不能出现在用户信息表中，应该独立领出来成为一张表
-- BC范式 BCNF
-- 定义 消除每⼀属性对候选键的传递依赖 ,BCNF是修正的第三范式
-- 人话 一个表中不能出现某一属性或多个属性的集合可以和主键相互推出
-- 判断的问题在下面的连接：
https://www.cnblogs.com/HanJie0824/p/14718027.html#:~:text=%E4%B8%80%E6%98%AF%E5%BF%85%E9%A1%BB%E6%9C%89%E4%B8%80%E4%B8%AA%EF%BC%88%E5%8F%8A%E4%BB%A5%E4%B8%8A%EF%BC%89%E4%B8%BB%E9%94%AE%EF%BC%9B%E4%BA%8C%E6%98%AF%E6%B2%A1%E6%9C%89%E5%8C%85%E5%90%AB%E5%9C%A8%E4%B8%BB%E9%94%AE%E4%B8%AD%E7%9A%84%E5%88%97%E5%BF%85%E9%A1%BB%E5%85%A8%E9%83%A8%E4%BE%9D%E8%B5%96%E4%BA%8E%E5%85%A8%E9%83%A8%E4%B8%BB%E9%94%AE%EF%BC%8C%E8%80%8C%E4%B8%8D%E8%83%BD%E5%8F%AA%E4%BE%9D%E8%B5%96%E4%BA%8E%E4%B8%BB%E9%94%AE%E7%9A%84%E4%B8%80%E9%83%A8%E5%88%86%E8%80%8C%E4%B8%8D%E4%BE%9D%E8%B5%96%E5%85%A8%E9%83%A8%E4%B8%BB%E9%94%AE%E3%80%82,%E5%AE%9A%E4%B9%89%E5%90%AC%E8%B5%B7%E6%9D%A5%E6%9C%89%E7%82%B9%E7%BB%95%EF%BC%8C%E4%B8%8D%E6%85%8C%EF%BC%8C%E7%9B%B4%E6%8E%A5%E7%9C%8B%E5%9B%BE%EF%BC%8C%E5%8F%AA%E6%9C%89%E5%85%A8%E9%83%A8%E7%9A%84%E9%9D%9E%E4%B8%BB%E9%94%AE%E5%88%97%E4%BE%9D%E8%B5%96%E4%BA%8E%E5%85%A8%E9%83%A8%E4%B8%BB%E9%94%AE%EF%BC%8C%E6%89%8D%E6%BB%A1%E8%B6%B3%E7%AC%AC%E4%BA%8C%E8%8C%83%E5%BC%8F%E3%80%82
https://blog.csdn.net/qq_40177015/article/details/111590534
记住A依赖B是看B能不能推A，而且部分依赖是B的部分推A（但B没有部分，就不存在部分依赖）
7.2.2 注意事项
一般的项目不需要达到过高的范式，一般达到第三范式完全足够
7.3 公里系统
-- 1. 公里本身
-- 2.公里推出的定理
7.4 最小函数依赖
-- 7.4.1 依赖的概念
-- 依赖是指关系中⼀个或⼀组属性的值可以决定其它属性的值⽐如 A->B这就是⼀个依赖
-- 7.4.2 解最小函数依赖
-- 1.拆右边为多个元素的 ⽐如A->BC 拆为 A->B 和A->C
-- 2. 出去当前元素所推得值,求它的闭包（看能不能再次把右边的值推出，能就去掉本个，不能就留着）,把集合⾥⾯所有元素都弄完
-- 3. 左边最小化(通过遮住元素来看能不能退出其他元素) 比如BCD,遮住B看能退出CD吗 , 遮住C能退出BD吗 遮住D看能退出BC吗
-- 例题在下面
-- 例题题目 已知关系 R<U,F> ,U{A,B,C,D,E,F,G} F={BCD->A,BC->E,A->F,F->G,C->D,A->G} 求F的最小依赖集
7.5 模式分解
7.5.1 准则与概念
-- 1 准则 做到无损连接 和 保持函数依赖
-- 2 无损 就是分解后再次连接,和分解之前⼀样
-- 3 依赖 依赖不变
-- 相关例子
-- 例1 学生表(学号,姓名) 课程表(课程号,名称) 成绩表(学号,课程号,分数)
-- 进行学生表与成绩表连接,连接条件是学号,这是一个无损连接,也保留了函数依赖。因为学生表中的学号函数决定姓名,在成绩表中也依然如此。
-- 例2 供应商表(供应商号,名称,地址) 零件表(零件号,名称,颜色) 供应表(供应商号,零件号)
-- 进行供应商表与供应表连接,连接条件是供应商号,这也是一个无损连接,且保留函数依赖。因为供应商表中的供应商号函数决定名称和地址,在连接后的结果中也仍然如此。
7.5.2 求模式分解
## 具体步骤
-- 1.求出本题的最小函数依赖集
-- 2. 把不在F⾥⾯的属性都找出来,单独分⼀类
-- 3.把每⼀个依赖左边相同的分成⼀类,没有⼀样的,那么就把A->D改为{AD}
-- 4.如果⼀样{A->B,A->C} 那么久改为{ABC}
-- 5.如果候选码没出现在分离⾥⾯, 把任意⼀个候选码作为⼀类
## 例题与解答
-- 例题 已知R(ABCDEGH) ,F={A->D,E->D,D->B,BC->D,DC->A} ,求保持函数依赖的3NF
-- 分布解答
-- 1.最小函数依赖集Fmin={A->D,E->D,D->B,BC->D,DC->A}
-- 2.GH没在F⾥⾯, 单独⼀类{GH}
-- 3.求出候选码 CE
-- 4.写出结果 {AD}{ED}{DB}{BCD}{DCA }{CE}{GH}
7.6 判断无损连接分解
https://blog.csdn.net/wonz5130/article/details/80502746
先求出候选键
7.7 判断是否保持函数依赖
https://www.cnblogs.com/dragondragon/p/12575774.html
第八部分 数据库设计
8.1 设计的基本步骤
- 需求分析阶段 对于数据库设计的第一步就是明确需求。在这一阶段，需要对数据及其使用场景进行分析，确定系统所需要的数据类型、数据量、数据流以及数据处理方式等。
- 概念设计阶段 在需求分析阶段确定了数据模型和需求后，就需要对数据库进行概念设计。在这个阶段，需要定义实体、属性、关系和约束条件等，形成概念模型。
- 逻辑设计阶段 在概念设计阶段后，需要将概念模型转换为逻辑模型。在这个阶段，需要确定表结构、字段、主键、外键等，并建立相应的关系模型。
- 物理设计阶段 在逻辑设计阶段完成后，需要将逻辑模型转化为物理模型。在这个阶段，需要确定表的存储格式、索引、分区、分布和安全性等，以及确定具体的数据库管理系统。
- 实施和评价阶段 在完成物理设计后，需要将数据库实施到实际环境中，并进行测试和评价，以确保数据库能够满足业务需求。
8.2 各阶段主要任务
- 需求分析阶段 明确业务需求、数据需求和用户需求，包括制定需求文档、收集数据以及确定数据流程等。
- 概念设计阶段 定义实体、属性、关系和约束条件等，在此基础上形成概念模型，包括绘制E-R图、建立实体关系模型等。
- 逻辑设计阶段 根据概念模型转换为逻辑模型，确定表结构、字段、主键、外键等，并建立相应的关系模型，包括建立实体与关系之间的关系、规范化等。
- 物理设计阶段 根据逻辑模型转换为物理模型，确定表的存储格式、索引、分区、分布和安全性等，以及确定具体的数据库管理系统，包括选择合适的数据库软件、优化数据库访问和查询性能等。
- 实施和评价阶段 将数据库实施到实际环境中，并进行测试和评价，以确保数据库能够满足业务需求，包括数据库的部署、测试和维护等
8.3 ER图（重点）
8.3.1 构建
注意几个点即可
- 实体是⻓⽅形体现，
- 属性则是椭圆形
- 关系为菱形
8.3.2 对应关系
类型
- 1对1（1 1） 1对1关系是指对于实体集A与实体集B，A中的每⼀个实体⾄多与B中⼀个实体有反之，在实体集B中的每个实体⾄多与实体集A中⼀个实体有关系
- 1对多（1 N） 1对多关系是指实体集A与实体集B中⾄少有N(N>0)个实体有关系；并且实体集B中每⼀个实体⾄多与实体集A中⼀个实体有关系
- 多对多（M N） 多对多关系是指实体集A中的每⼀个实体与实体集B中⾄少有M(M>0)个实体有并且实体集B中的每⼀个实体与实体集A中的⾄少N（N>0）个实体有关系
例子
1对1的例子 一个人对应一个身份证号
1对多的例子
-- 部门和员工 一个部门有多名员工,一个员工只属于一个部门
多对多的例子
-- 学生和课程 一个学生可以选择多门课程,一门课程也可以被多个学生选修
-- 角色和权限 一个角色可以具有多个权限,一个权限也可以被赋予多个角色
-- 医生和病人 一个医生可以诊疗多个病人,一个病人也可以找多名医生就诊
8.4 ER图转关系模型（重点）
8.4.1 简介
就是将ER图中的实体转换成表，属性转换为列，关系转化为中介（但构建时，中介必须合并到实体中）
8.4.2 中介合并问题！！！！！！！！
- 1 1的关系， 中介属性随便合并到任意一个表中（假设是中介到A表中 ，那么还要将B表的主键传给A一份）
- 1 n的关系， 中介属性和1表的主键都要放到N表中去
- n m的关系，两个实体表的主键和需要中介实体的属性单独封装成一个表
实例图片
二元类型转化与上面解析
-- 1）如果实体间联系是1 1，可以在两个实体类型转换成的两个关系模式中任意一个关系模式的属性中加入另一个关系模式的键和联系类型的属性。
-- 2）如果实体间联系是1 N，则在N端实体类型转换成的关系模式中加入1端实体类型的键和联系类型的属性。
-- 这两句话听起来是不是有点绕，看下面这个例子（由于上述ER图没有1 1联系，为方便说明，我们假设平台和管理员之间的联系同时存在1 1和1 N）
-- 对于1 1联系“聘用”，可以在“平台”关系模式中加入管理员账号ID（管理员账号ID是外键）；
-- 对于1 N联系“聘用”，则在“管理员”关系模式中加入商标和聘期两个属性（商标是1端的键）
-- ①平台（商标，名称，所属公司，管理员的账号ID）
-- ②管理员（账号ID，账号密码，用户名，商标、聘期）
-- 3）如果实体间联系是M N，则将联系类型也转换成关系模式，其属性为两端实体类型的键加上联系类型的属性，而键为两端实体键的组合。
-- 对于M N联系“下单”和“上传发布”，则生成两个新的关系模式
-- ①下单（商品编号，顾客账号ID，订单编号，订单数量，下单时间）
-- ②上传发布（商品编号，管理员账号ID，发布时间）
三元类型转化
-- 1）如果实体间联系是1 1 1，可以在三个实体类型转换成的三个关系模式中任意一个关系模式的属性中加入另两个关系模式的键（作为外键）和联系类型的属性。
-- 2）若实体间联系是1 1 N，则在N端实体类型转换成的关系模式中加入两个1端实体类型的键（作为外键）和联系类型的属性。
-- 3）若实体间联系是1 M N，则将联系类型也转换成关系模式，其属性为M端和N端实体类型的键（作为外键）加上联系类型的属性，而键为M端和N端实体键的组合。
-- 4）若实体间联系是M N P，则将联系类型也转换成关系模式，其属性为三端实体类型的键（作为外键）加上联系类型的属性，而键为三端实体键的组合。
8.4.3 不错的一个例子：
https://www.cnblogs.com/vvlj/p/12750853.html
8.4.4 例题：
第九部分 嵌入式SQL
本部分不同的语言，运用的方案都不一样，实际开发根据技术进行选型就行，期末考试不会考实践，撑死考相关的理论
9.1 简介
就是把SQL语句嵌⼊到 其他编程语⾔⾥⾯ ⽐如java、python
9.2 处理过程
- 预编译转换为函数调⽤
- 主语⾔编译
- 变成主语⾔所编译的类型
9.3 主语言通信
- SQL给主语⾔传递状态
- 主语⾔给SQL提供参数
- SQL把查询结果交给主语⾔处理(游标,和主变量实现)
第十部分 关系模式优化
10.1 查询的处理流程
- 查询分析
- 查询检查
- 查询优化
- 查询执⾏
10.2 查询的优化方案
代数优化就是本章讲的重点, 即为 优化树
- 选择运算尽量先做
- 把投影运算和选择运算同时执⾏
- 把投影同它前后的双⽬运算符连接起来
10.3 关系模式优化（重点 重点）
- 规范化（Normalization） 规范化是一种将数据库设计分解为多个表的过程。通过规范化可以避免数据冗余和不一致，提高数据库的可维护性和可扩展性。
- 索引优化（Index Optimization） 索引是用于加速数据检索的一种数据结构。通过优化索引结构和数量，可以提高数据检索的效率和响应速度。
- 查询优化（Query Optimization） 查询优化是指对SQL语句进行优化，以提高查询效率和减少资源消耗。通过修改查询语句、调整索引、使用缓存等方式，可以优化查询操作。
- 数据库分区（Database Partitioning） 数据库分区是将数据库划分为多个逻辑部分的过程。通过数据库分区可以提高数据库的负载均衡和可扩展性，同时也可以提高数据访问的效率。
第十一部分 数据库恢复技术
11.1 事务的概念及四大特性
ACID
- 原⼦性 autom 要么全做，要么全不做（做一半出问题了，前面就不能要了）
- ⼀致性 consistent ⼀致性与原⼦性密切相关，要么全做要么全不做，否则就会造成数据不⼀致。⽐如说 银⾏汇钱
- 隔离性isolate ⼀个事务的执⾏不能被其他事务所⼲扰
- 持久性 duration 数据库的改变是永久的。⽐如要落⼊磁盘
11.2 故障种类
- 事物内部故障 采取REDO重做和UNDO撤销技术
- 系统故障 DBMS 系统突然停转，系统要重启
- 介质故障 硬件损坏
- 计算机病毒
11.3 恢复方式与备份策略
11.3.1 恢复方式
- 事务日志（Transaction Log） 事务日志是记录数据库中所有事务操作的操作日志。在数据库恢复时，可以利用事务日志来进行回滚和重放操作，把数据库恢复到最新的一致状态。
- 检查点（Checkpoint） 检查点是数据库在某个时间点上的快照，记录了当前正在运行的事务已经提交的所有数据以及相关信息。在数据库出现问题时，可以利用检查点来缩短恢复时间。
- 数据库备份（Database Backup） 数据库备份是将整个数据库或部分数据备份到另一个位置的过程。在数据库出现故障时，可以通过数据库备份来恢复数据库。
- 崩溃恢复（Crash Recovery） 当数据库发生崩溃时，需要通过对事务日志进行分析和处理，来恢复数据库到最近的一致状态。崩溃恢复主要包括回滚未完成的事务、重做已执行但未写入磁盘的事务等操作。
11.3.2 恢复策略
- 事务故障的恢复 事务异常终⽌，那么就撤销之前的所有操作
- 系统故障的恢复 还没执⾏完的事务UNDO、丢失的事务REDO。
- 介质故障的恢复 重装数据库，重做已经完成的事务
11.3.3 备份策略
理论考试不会考，实际项目中还是不同语言不一样
第十二部分 数据库并发控制
12.1 并发的概念
12.2 并发所存在的问题
- 丢失修改 我修改的东⻄没⽣效。⽐如说我把票价改成99元，结果还是原来的299元
- 读脏数据 ⽐如，你要读取数据库中的字段A、字段B，你读取时恰巧有其他⽤户正在更新这2个字段，⽽且如果那个⽤户更新⼀半你就读取了，也就是说更新了A，正打算要更新B但尚未更新时，你就读取
- 不可重复读 我在⼀个事务中，连续两次读到的数据不⼀样。⽐如我刚开始读到银⾏余额为10元。此时单位突然给发⼯资100到这张卡,那么我第⼆次读就变
例子
-- 1. 丢失修改
-- 事务1读取数据A,值为1。
-- 事务2也读取数据A,值为1。
-- 事务1将数据A修改为2,并提交。
-- 事务2也将数据A修改为3,并提交。
-- 结果数据A的值为3,事务1的修改被丢失。
-- 2. 脏读
-- 事务1读取数据A,值为1。
-- 事务2修改数据A为2,但未提交。
-- 事务1又读取数据A,值为2。
-- 事务2回滚,数据A回到1。
-- 事务1读取的值（1）是脏数据,不准确。
-- 3. 不可重复读
-- 事务1读取数据A,值为1。
-- 事务2修改数据A为2,并提交。
-- 事务1再次读取数据A,值为2。
-- 事务1两次读取的数据不同,产生不可重复读问题。
12.3 了解并发锁
- 排他锁又叫做X锁是指在事务对某一数据进行修改时，会将该数据锁定，其他事务无法对该数据进行任何操作，直到该事务提交或回滚后才能释放锁。排他锁只允许一个事务同时访问被锁定的数据。
- 共享锁又叫做S锁是指事务对某一数据进行读取时，会将该数据锁定，其他事务可以继续对该数据进行读取操作，但不能进行修改操作。共享锁允许多个事务同时访问被锁定的数据。
12.4 解决办法与三级锁协议
-- 1.⼀级封锁协议
-- 修改时，必须加X锁，直到结束
-- 2.⼆级封锁协议
-- 读的时候，加S锁，⽤完就放
-- 3、三级封锁协议
-- 读的时候，加S锁，直到结束
-- ⼀级锁协议 解决丢失修改
-- ⼆级锁协议 解决读脏数据
-- 三级锁协议 解决不可重复读
12.5 可串行性
-- 假设多种情况都可以,然后获得结果,
-- 如果并发执⾏的结果跟上⾯的结果⾥⾯任意⼀个⼀样就可以
-- 事务T1 读取B,A=B+1,写回A
-- 事务T2 读取A,B=A+1,写回B
-- 假设先T1, 再T2 , 那么A=4,B =3;
-- 假设先T2 ,再T1那么A=3, B =4;
-- 只要数据库执行结果满足其中之一，即满足可串行性
第十三部分 数据库层次结构
13.1 主要层次
- 用户界面层 用户界面层提供给最终用户交互的接口，包括数据输入、查询和显示等功能。通常通过图形界面或者Web界面实现。
- 应用逻辑层 应用逻辑层是连接用户界面层和数据存储层的重要部分。在这一层中，数据库应用程序可以对数据进行处理、计算和验证，实现一些复杂的业务逻辑。
- 数据存储层 数据存储层是数据库系统中最核心的部分，包括数据库管理系统和数据库文件等。在这一层中，实现了数据的存储、管理、检索以及保护。
13.2 相关应用
- 分层设计 通过将数据库应用程序划分为不同的层次结构，可以使得系统的设计更加合理和模块化，从而更容易进行功能扩展、维护和升级。
- 安全性控制 通过对不同层次的安全访问控制，可以提高数据库应用程序的安全性，避免未授权的用户访问敏感数据。
- 业务逻辑处理 在应用逻辑层中实现复杂的业务逻辑处理，可以使得数据库应用程序更加灵活和可定制，从而更好地满足不同用户的需求。
- 数据库性能优化 通过合理的分层设计和优化，可以提高数据库应用系统的性能和效率，从而更好地满足高并发、大数据量等复杂环境下的需求。
额外第一部分 可能题目类型
x_1.1 选择题
x_1.2 填空题
x_1.3 大题目
- 给需求，设计sql语句（可能是基础的增删改，也可能是权限控制）！！！（1.2）和（5.1）
- 给表和sql语句，写出结果
- 判断视图操作是否能执行，并写出对基本表的转化的sql！！！（注意顺序，转化的sql永远是建立视图优先的）
- 相关子查询与不相关子查询（概率不大）
- withCheckOption的问题（和3放一块考）
- 给需求 设计ER图（方形 圆形 菱形）
- ER图转化为逻辑模型（基本表）
- 简述三级模式与二级印像
- 简述数据库的两种独立性问题
- sql语句与关系语言的转化
- 简述关系完整性与外键空值问题
- 根据要求，写触发器的函数
- 数据库恢复问题（undo和redo）
- 数据库并发控制（三大问题与解决，各种锁的理解 死锁和干预的方法）