数据库相关概念

  • 数据 & 信息：数据+解释=信息
  • 数据管理：数据存储/维护/查询/安全等
  • 数据库：是存储介质上的一个/组文件，不能直接编辑，需通过数据库管理系统来操作
  • 数据库管理系统DBMS：管理数据库的一种大型复杂软件系统
  • 数据库系统DMS：由应用程序、数据库、数据库管理系统和用户组成

ER模型

ER 图 全称是 Entity Relationship Diagram（实体联系图），提供了表示实体类型、属性和联系的方法。

ER 图由下面 3 个要素组成：

  • 实体：通常是现实世界的业务对象，当然使用一些逻辑对象也可以。在 ER 图中，实体使用矩形框表示。
  • 属性：即某个实体拥有的属性，属性用来描述组成实体的要素。在 ER 图中，属性使用椭圆形表示。
  • 联系：即实体与实体之间的关系，在 ER 图中用菱形表示，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。

关系模型

  • 元组：元组（tuple）是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。 在二维表里，元组也称为行。
  • 码/键：码就是能唯一标识实体的属性，对应表中的列。
  • 候选码/候选键：若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码。
  • 主码/主键 : 主码也叫主键。主码是从候选码中选出来的。 一个实体集中只能有一个主码，但可以有多个候选码。
  • 外码/外键 : 外码也叫外键。如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。
  • 主属性：候选码中出现过的属性称为主属性。比如关系 工人（工号，身份证号，姓名，性别，部门）. 显然工号和身份证号都能够唯一标示这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。
  • 非主属性： 不包含在任何一个候选码中的属性称为非主属性。比如在关系——学生（学号，姓名，年龄，性别，班级）中，主码是“学号”，那么其他的“姓名”、“年龄”、“性别”、“班级”就都可以称为非主属性。

范式

1NF

第一范式（First Normal Form）是最基本的规范形式，即关系中每个属性都是不可再分的简单项。

2NF

如果关系模式R ∈ 1NF，且每个非主属性都完全函数依赖于R的每个关系键，则称R属于第二范式（Second Normal Form），简称2NF，记作R ∈ 2NF。

3NF

如果关系模式R ∈ 2NF，且每个非主属性都不传递依赖于R的每个关系键，则称R属于第三范式（Third Normal Form），简称3NF，记作R ∈ 3NF。

其他范式

  • BCNF：如果关系模式R ∈ 1NF，且所有的函数依赖X→Y（Y ∉ X），决定因素X都包含了R的一个候选键，则称R属于BC范式（Boyce-Codd Normal Form），记作R ∈ BCNF。

SQL语言

DQL

单表查询

查询语句基本结构：

  • SELECT子句：指定要显示的属性列（DISTNCT）

  • FROM子句：指定查询对象(基本表或视图)

  • WHERE子句：指定查询条件

  • GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。

  • HAVING短语：筛选出只有满足指定条件的组

  • ORDER BY子句：对查询结果表按指定列值的升序或降序排序

多表查询

连接查询（在From子句中涉及多个表的查询）：

  • 广义笛卡尔积：不带连接谓词的连

  • 等值连接(含自然连接)：

    • 自然连接：等值连接的一种特殊情况，把目标列中重复的属性列去掉

  • 非等值连接查询：连接运算符不是=号的连接

  • 自身连接查询：一个表与其自己进行连接，需要给表的两个副本起别名以示区别

  • 外连接查询：普通连接（内连接，INNER JOIN）只返回两个表中匹配的记录；外连接（OUTTER JOIN）不仅返回匹配的记录，还返回未匹配的一侧或两侧的记录，未匹配部分用 NULL 补齐。

    • LEFT OUTER JOIN 或 LEFT JOIN
    • RIGHT OUTER JOIN 或 RIGHT JOIN
    • FULL OUTER JOIN 或 FULL JOIN

    MySQL 不支持 FULL JOIN，可以用 UNION 实现：Left join Union Right join

  • 复合条件连接查询

子查询

  • 相关子查询：子查询的查询条件依赖于父查询
  • 不相关子查询：由里向外逐层处理

WHERE子句中引出子查询的谓词：IN/=、比较运算符、ALL/ANY、EXISTS/NOT EXISTS；

FROM子句中的子查询相当于返回一张临时表。

集合查询

UNION ：用于合并两个或多个 SELECT 查询的结果，形成一个统一的结果集。

MySQL只支持UNION（并），不支持INTERSECT/EXCEPT（交/差）。

DDL

  • 创建表

    image-20250710191509273

  • 删除表：DROP TABEL <表名>

  • 修改表：

    image-20250710191656484

DML

  • 插入数据

    • 插入单条元组

      image-20250710192432361

    • 插入子查询结果

      image-20250710192530527

  • 修改数据

    image-20250710192637371
    • 修改某一个元组的值
    • 修改多个元组的值
    • 带子查询的修改语句

  • 删除数据

    image-20250710192757239
    • 删除某一个元组的值
    • 删除多个元组的值
    • 带子查询的删除语句

TCL（事务控制语言）

MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。

指令：

  • START TRANSACTION - 指令用于标记事务的起始点。
  • SAVEPOINT - 指令用于创建保留点。
  • ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START TRANSACTION 语句处。
  • COMMIT - 提交事务。

DCL

DCL 的主要功能是控制用户的访问权限。

  • 权限授予GRANT：

    • 如Grant select on TableA on UserA（表级）Grant select (a, b) on TableA on UserA（列级）

  • 权限收回Revoke：

    • 如Revoke select on TableA from UserA

  • 创建新用户：

    • Create user username IDENTIFIED BY ‘password’

  • 角色：命名的权限集合，使用角色可以方便的进行授权管理

    image-20250710213001894

视图

视图是一种虚表（virtual table），是从一个或几个基本表（或视图）导出的表。

视图其实是在数据字典中存储的一条Select 语句

image-20250710192929826

视图的作用：

  • 视图能够简化用户的操作
  • 视图使用户能以多种角度看待同一数据
  • 视图对重构数据库提供了一定程度的逻辑独立性
  • 视图能够对机密数据提供安全保护

索引与查询优化

索引

什么是索引：

  • 是主表上的一种辅助数据结构
  • 对数据库表中一个或多个列的值进行排序
  • 用于提高主表的查询速度
  • 如果想按特定列的值来查找数据，则与在表中搜索所有的行相比，索引有助于更快地获取信息

优点：

  • 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

  • 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 索引需要使用物理文件存储，也会耗费一定空间。

B+树索引

B+树索引是数据库系统中使用最广泛的多级索引。具有如下特点：

  • 将索引键组织成一棵平衡树， 即从树根到树叶的所有路径一样长
  • 数据（指向基本表记录存储位置的指针）存储在叶结点
  • 最底层的叶节点包含每个索引键和指向被索引行的指针（行id）
  • 叶节点之间有通道可供平行查询
  • 每一个叶节点都和磁盘页面大小一致
  • 查询的时间复杂度：$O(log_mn) $(m为分叉数，即B+树的阶）

查询优化

SQL语句执行过程：

image-20250710205448837

查询优化的一般准则：

  • 选择运算应尽可能先做
    • 目的：减小中间关系
  • 在执行连接操作前对关系适当进行预处理
    • 按连接属性排序
    • 在连接属性上建立索引
  • 投影运算和选择运算同时做
    • 目的：避免重复扫描关系
  • 将投影运算与其前面或后面的双目运算结合
    • 目的：减少扫描关系的遍数
  • 提取公共子表达式
  • …

查询优化的一般过程：

  1. 将查询转换成某种内部表示，通常是语法树

  2. 根据一定的等价变换规则把语法树转换成标准（优化）形式 （代数优化）

  3. 选择低层的操作算法（物理优化）

    • 对于语法树中的每一个操作计算各种执行算法的执行代价
    • 选择代价小的执行算法

  4. 生成查询计划(查询执行方案)

存储过程

存储过程可以看成是对一系列 SQL 操作的批处理。存储过程可以由触发器，其他存储过程以及 Java， Python，PHP 等应用程序调用。

使用存储过程的好处：

  • 代码封装，保证了一定的安全性；
  • 代码复用；
  • 由于是预先编译，因此具有很高的性能。

触发器

触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。

我们可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。

使用触发器的优点：

  • SQL 触发器提供了另一种检查数据完整性的方法。
  • SQL 触发器可以捕获数据库层中业务逻辑中的错误。
  • SQL 触发器提供了另一种运行计划任务的方法。通过使用 SQL 触发器，您不必等待运行计划任务，因为在对表中的数据进行更改之前或之后会自动调用触发器。
  • SQL 触发器对于审计表中数据的更改非常有用。

使用触发器的缺点：

  • SQL 触发器只能提供扩展验证，并且不能替换所有验证。必须在应用程序层中完成一些简单的验证。例如，您可以使用 JavaScript 在客户端验证用户的输入，或者使用服务器端脚本语言（如 JSP，PHP，ASP.NET，Perl）在服务器端验证用户的输入。
  • 从客户端应用程序调用和执行 SQL 触发器是不可见的，因此很难弄清楚数据库层中发生了什么。
  • SQL 触发器可能会增加数据库服务器的开销。

MySQL 不允许在触发器中使用 CALL 语句 ，也就是不能调用存储过程。

创建触发器：

1
2
3
4
5
6
7
8
CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
  trigger_statements
END;

例如：

1
2
3
4
5
6
7
8
9
DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
    INSERT INTO `user_history`(user_id, operate_type, operate_time)
    VALUES (NEW.id, 'add a user',  now());
END $
DELIMITER ;

NoSQL

NoSQL（Not Only SQL 的缩写）泛指非关系型的数据库，主要针对的是键值、文档以及图形类型数据存储。并且，NoSQL 数据库天生支持分布式，数据冗余和数据分片等特性，旨在提供可扩展的高可用高性能数据存储解决方案。

NoSQL的类型

NoSQL 数据库主要可以分为下面四种类型：

  • 键值：键值数据库是一种较简单的数据库，其中每个项目都包含键和值。这是极为灵活的 NoSQL 数据库类型，因为应用可以完全控制 value 字段中存储的内容，没有任何限制。Redis 和 DynanoDB 是两款非常流行的键值数据库。
  • 文档：文档数据库中的数据被存储在类似于 JSON（JavaScript 对象表示法）对象的文档中，非常清晰直观。每个文档包含成对的字段和值。这些值通常可以是各种类型，包括字符串、数字、布尔值、数组或对象等，并且它们的结构通常与开发者在代码中使用的对象保持一致。MongoDB 就是一款非常流行的文档数据库。
  • 图形：图形数据库旨在轻松构建和运行与高度连接的数据集一起使用的应用程序。图形数据库的典型使用案例包括社交网络、推荐引擎、欺诈检测和知识图形。Neo4j 和 Giraph 是两款非常流行的图形数据库。
  • 宽列：宽列存储数据库非常适合需要存储大量的数据。Cassandra 和 HBase 是两款非常流行的宽列存储数据库。

SQL与NoSQL的区别

SQL 数据库 NoSQL 数据库
数据存储模型 结构化存储，具有固定行和列的表格 非结构化存储。文档：JSON 文档，键值：键值对，宽列：包含行和动态列的表，图：节点和边
发展历程 开发于 1970 年代，重点是减少数据重复 开发于 2000 年代后期，重点是提升可扩展性，减少大规模数据的存储成本
例子 Oracle、MySQL、Microsoft SQL Server、PostgreSQL 文档：MongoDB、CouchDB，键值：Redis、DynamoDB，宽列：Cassandra、HBase，图表：Neo4j、Amazon Neptune、Giraph
ACID 属性 提供原子性、一致性、隔离性和持久性 (ACID) 属性 通常不支持 ACID 事务，为了可扩展、高性能进行了权衡，少部分支持比如 MongoDB。不过，MongoDB 对 ACID 事务的支持和 MySQL 还是有所区别的。
性能 性能通常取决于磁盘子系统。要获得最佳性能，通常需要优化查询、索引和表结构 性能通常由底层硬件集群大小、网络延迟以及调用应用程序来决定
扩展 垂直（使用性能更强大的服务器进行扩展）、读写分离、分库分表 横向（增加服务器的方式横向扩展，通常是基于分片机制）
用途 普通企业级的项目的数据存储 用途广泛比如图数据库支持分析和遍历连接数据之间的关系、键值数据库可以处理大量数据扩展和极高的状态变化
查询语法 结构化查询语言 (SQL) 数据访问语法可能因数据库而异

参考：https://javaguide.cn/