数据库

数据库

在网络开发中，大量的用户数据必须存储在网页后端，为此，数据库为我们提供了支持。数据库类似于Excel表格，允许我们以高度结构化的形式存储用户数据并能够与程序产生交互。

数据库分为关系型数据库和非关系型数据库，关系型数据库是基于关系模型来存储和管理数据的数据库系统。关系就是基于表格的数据模型，它使用行和列来表示数据，并通过主键和外键来定义表之间的关系。而非关系型数据库则不强制要求使用关系模型来存储数据，这一点是倒是更灵活。

目前主流的关系型数据库有：Mysql，Sql Server，Oracle，DB2，DM达梦。

我们以最广泛的开源数据库Mysql展开讨论。

安装

下载地址：https://dev.mysql.com/downloads/mysql/

安装过程中需要设置用户，建议设置一个管理员用户和一个普通用户，我个人习惯是管理员用户名和密码都是root，期间安装程序会提醒你输入原有已经输入的密码，安装过程才能继续。

完成后，我们就能够通过命令行来使用数据库，但这很麻烦，毕竟在计算机性能发达的今天，图形化才是趋势。

DBeaver

下载地址：https://dbeaver.io/download/

安装过程略过

安装完成后创建连接：

然后你就能在DBeaver中操作数据库的表了。

但是，这一类可视化图形工具起到的作用只是简化操作，帮我们输入并执行SQL语句而已，作为开发人员我们需要具备使用命令行实现增删改查的能力。

SQL语句

SQL语句不是像java一样的命令式编程语言，而是声明式编程语言，就和开源项目的命令行使用方法类似。

这一部分不会比我们之前学习的java难，放松。

SQL语句适用于大多数关系型数据库，是统一操作语言，但某些关系型数据库在语法解析上可能有细微差别，你可以理解成编译器不同导致的输出不同。SQL语句和根据数据库不同特征化的语句可以理解为是普通话和方言一样。

概念

常见概念

  • 数据库 (Database)

一个存储数据的仓库，可以包含多个表，用于组织和管理大量结构化数据。

  • 表 (Table)

数据库中的一种数据结构，由行和列组成，用于存储特定类型的数据（如用户表、订单表）。

  • 列名 (Column Name)

表中的每一列都有一个名称，用于标识该列存储的数据（如 username、age）。

  • 数据类型 (Data Type)

定义列中数据的类型，如整数 (INT)、字符串 (VARCHAR)、日期 (DATE) 等。

  • 自增 (Auto Increment)

一种属性，通常用于主键列，每次插入新数据时自动递增（如 1, 2, 3…）。

  • 非空 (NOT NULL)

一种约束，确保列中不能存储空值（必须填写数据）。

  • 唯一 (UNIQUE)

一种约束，确保列中的值不能重复（但允许空值）。

  • 主键 (Primary Key)

一种唯一且非空的列（或列组合），用于唯一标识表中的每一行。

  • 外键 (Foreign Key)

一种列，用于关联另一张表的主键，确保数据的一致性和完整性。

  • 默认值 (Default Value)

如果插入数据时未指定值，列将自动填充的预设值（如 DEFAULT 0）。

  • 注释 (Comment)

对表、列或索引的说明，用于帮助理解其用途（如 COMMENT ‘用户姓名’）。

  • 索引 (Index)

一种数据结构，用于加快数据检索速度（类似于书的目录），但会增加写入数据的开销。

总结一下，数据库包含表，表又包含列和行，其中列起到锚定作用，自增，唯一，主键，外键，都是列的属性或者类型，行起到保存数据作用，行和列围成的格子中存放的原子数据又包含了不同的数据类型。

SQL的分类

  1. DDL（Data Definition Language，数据定义语言）：用于创建、修改和删除数据库中的各种对象，如表、索引、视图、触发器等。常见的DDL命令包括CREATE（创建）、ALTER（修改）和DROP（删除）。例如，CREATE TABLE用于创建新的数据库表，ALTER TABLE用于修改表的结构，DROP TABLE则用于删除表。
  2. DML（Data Manipulation Language，数据操纵语言）：允许用户对数据库中的数据进行基本操作，如插入、更新、删除和查询数据记录。常见的DML命令有INSERT（插入）、UPDATE（更新）、DELETE（删除）和SELECT（查询）。其中，SELECT是SQL语言的基础，用于从数据库中检索数据。
  3. DQL（Data Query Language，数据查询语言）：主要用于从数据库中检索数据，其核心指令是SELECT。DQL是DML的一个子集，专注于数据查询操作。
  4. DCL（Data Control Language，数据控制语言）：用于控制数据库用户访问权限和安全性的语言。DCL包括授予和撤销用户权限的命令，以及管理数据库对象的权限。常见的DCL命令有GRANT（授予）和REVOKE（撤销）。通过DCL，数据库管理员可以设定哪些用户有权访问哪些数据库对象，以及他们可以进行哪些操作。
  5. TCL（Transaction Control Language，事务控制语言）：用于管理事务的语言，包括开始事务、提交事务、回滚事务等命令。常见的TCL命令有BEGIN（开始事务）、COMMIT（提交事务）和ROLLBACK（回滚事务）。TCL在快速原型开发、脚本编程、GUI和测试等方面非常有用，它允许将一系列操作组合为一个逻辑事务，从而确保数据的完整性和一致性。

命令行操作

创建数据库和表

1
create DateBase [数据库名]；
SQL

例如：创建名为willmo的数据库

1
create datebase willmo;
SQL

SQL语句对关键字大小写不敏感，但是对名称等标识敏感。

其他的参考java的编程语言书写规范就可以了。

你可以在图形化软件操作后查看操作对应的sql语句,例如在创建表的同时新建列，并列出列的属性：

1
2
3
4
5
6
CREATE TABLE 表名 (
    列名1 数据类型 [约束条件],
    列名2 数据类型 [约束条件],
    ...
    [表级约束]
);
SQL

例如：

1
2
3
4
5
6
7
CREATE TABLE willmo.test (           --这里是全限定表名，提及了数据库名willmo，如果选中了数据库就不用提及数据库名，这和绝对路径和相对路径是一个道理。
  id INT auto_increment NOT NULL COMMENT 'id',  --列名为id,属性是自增非空，数据类型是INT整型
  name varchar(100) NOT NULL COMMENT '姓名',--列名为name,属性是非空，别名为“姓名”，数据类型字符串
  CONSTRAINT test_pk PRIMARY KEY (id     --设置id为主键，也就是起唯一标识作用的列
)
ENGINE=InnoDB                            --指定引擎
DEFAULT CHARSET=utf8mb4;                 --指定字符集，utf8mb4相较于utf8他能够存储表情
SQL

数据类型

在MySQL中，为列选择合适的数据类型至关重要，因为它直接影响到数据的存储效率、查询性能以及数据的完整性。以下是根据数据类型和用途给出的一些推荐：

数值类型
  1. 整数类型
  • TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT：根据数值的范围选择合适的大小。例如，如果确定数值不会超过255，那么使用TINYINT可以节省存储空间。
  • UNSIGNED：如果数值不需要负数，使用UNSIGNED属性可以将正数的范围扩大一倍。
  • AUTO_INCREMENT：如果需要自动生成唯一的标识符，可以在主键列上使用AUTO_INCREMENT属性。
  1. 浮点数类型
  • FLOAT、DOUBLE：用于存储近似数值。FLOAT占用4个字节，DOUBLE占用8个字节。如果需要更高的精度，可以选择DOUBLE。
  • DECIMAL：用于存储精确数值，如金融计算。DECIMAL类型可以指定小数点前后的位数，如DECIMAL(10,2)表示总共10位数字，其中小数点后有2位。
字符串类型
  1. CHAR
  • 用于存储固定长度的字符串。如果字符串长度几乎相同，或者长度很短且变更不频繁，CHAR是更好的选择。
  • CHAR类型会删除字符串末尾的空格，并根据需要填充空格以方便比较。
  1. VARCHAR
  • 用于存储可变长度的字符串。VARCHAR类型仅使用必要的空间，因此比CHAR更节省空间。
  • 当字符串列的最大长度远大于平均长度时，或者字符串经常更新时，VARCHAR是更好的选择。
  1. TEXT类型
  • 用于存储大文本数据。TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT分别可以存储不同大小的数据。
  • 如果需要存储非常大的文本数据，可以选择LONGTEXT。
日期和时间类型
  1. DATE：用于存储日期值，格式为YYYY-MM-DD。
  2. TIME：用于存储时间值，格式为HH:MM:SS。
  3. DATETIME：用于存储日期和时间值，格式为YYYY-MM-DD HH:MM:SS。
  4. TIMESTAMP：用于存储UNIX时间戳，表示从1970年1月1日以来的秒数。TIMESTAMP值依赖于时区设置。

查询

查询我们使用这个格式：

1
select 字段名1,字段名2,... from 表名;
SQL

当然你也可以使用通配符*，虽然这么做可能引发性能问题。

1
select * from 表名;
SQL

使用select能够找出相应列，但是我们需要使用where来实现条件查询

比如：

1
2
3
4
5
6
SELECT * FROM employees 
WHERE salary > 5000;     --找出5000块工资以上的职员

SELECT * FROM products
WHERE category = 'Electronics'    --找出电子类和1000块以下的产品
AND price < 1000;
SQL

查询条件：

  • =, <>/!=, >, <, >=, <=
  • BETWEEN … AND …
    • 意思是查找区间范围的数据，如找12-19的数据可以是select number from num Between 12 and 19
  • LIKE（配合%和_使用）
    • LIKE是模糊查询，如果你想找张某某，你可以查询select name from user LIKE "张%";
  • IN (值列表)，比如IN 12,13,15,16;

排列

1
2
3
4
5
6
7
-- 按工资降序排列
SELECT * FROM employees
ORDER BY salary DESC;

-- 获取前10条记录（不同数据库语法可能不同）
SELECT * FROM products
LIMIT 10;
SQL

asc是顺序排序，desc是逆向排序。

聚合查询

1
2
3
4
5
6
7
SELECT 
    COUNT(*) AS total_employees,   --输出全体职员的数量
      SUM(salary) AS sum_salary,   --求薪水之和
    AVG(salary) AS average_salary, --使用关键字计算平均薪水
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees;
SQL

你可以使用having语句和group by 语句来实现对某一群体进行聚合查询和计算

1
2
3
4
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 6000;   --计算所以6000薪资以上的职员的平均工资
SQL

子查询

你一定能够注意到，查询的结果返回的也是一个表，那么我们就能够对返回表再做一次查询。

1
2
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);    --查询出高于产品平均价的那一批产品
SQL

新增

使用INSERT关键字来实现

1
2
INSERT INTO 表名 (字段1, 字段2, ..., 字段N)
VALUES (值1, 值2, ..., 值N);
SQL

如果你向插入一行完整的数据，就可以省略字段

1
INSERT INTO 表名 VALUES (值1, 值2, ..., 值N);   --确保只有N列
SQL

你也可以批量新增

1
2
3
4
5
6
INSERT INTO 表名 (字段1, 字段2, ..., 字段N)
VALUES 
    (值1_1, 值1_2, ..., 值1_N),
    (值2_1, 值2_2, ..., 值2_N),
    ...,
    (值M_1, 值M_2, ..., 值M_N);
SQL

修改

1
2
3
UPDATE 表名
SET 字段1 = 新值1, 字段2 = 新值2, ...
WHERE 条件;
SQL

删除

1
DELETE FROM 表名 WHERE 条件;
SQL

注意：where条件查询关键字的缺失不会引发编译错误，但是增加删除修改等破坏性操作如果没有where关键字那操作对象就是整个表格，很可能会对全表格造成增删改的不可逆破坏。

数据库设计范式

第一范式（1NF, First Normal Form）

简单来说： 每个字段只存储一项信息，每一列都是原子的，不可再分。

例子： 假设有一个记录学生信息的表格，如果有一个字段叫做“个人信息”，里面包含了姓名、年龄和性别等多个信息，这就违反了第一范式。正确的做法是将“个人信息”这个字段拆分成姓名、年龄、性别等多个字段，每个字段只存储一项信息。

第二范式（2NF, Second Normal Form）

简单来说： 在满足第一范式的基础上，要求表格中的每一非主键字段都完全依赖于主键字段，而不能依赖于主键的一部分或是其他非主键字段。

例子：
学生ID 课程ID 课程名称 授课教师 学分
S001 C01 数学 王老师 3
S001 C02 英语 李老师 2
S002 C01 数学 王老师 3
  • 主键：(学生ID, 课程ID)（复合主键）
  • 问题：
  • 课程名称 和 授课教师 仅依赖于 课程ID（主键的一部分），而非完整主键。
  • 学分 也仅依赖于 课程ID。
  • 结论：存在非主属性对主键的部分依赖，满足1NF（所有列原子），但不满足2NF。
第三范式（3NF, Third Normal Form）

简单来说： 在满足第二范式的基础上，要求表格中的每一非主键字段都直接依赖于主键字段，而不能通过其他非主键字段间接依赖于主键字段。换句话说，非主键字段之间不能有传递依赖关系。

例子：

学生ID 姓名 年龄 所在学院 学院电话
S001 张三 20 计算机学院 021-1234
S002 李四 21 经济管理学院 021-5678
  • 主键：学生ID
  • 问题：
  • 学院电话 依赖于 所在学院，而 所在学院 又依赖于 学生ID。
  • 存在非主属性 学院电话 对主键的传递依赖。
  • 结论：满足2NF（无部分依赖），但不满足3NF（存在传递依赖）。
反范式化（Denormalization）

话虽如此，但是在实际业务中，尤其是对查询性能要求很高的场景（如大数据分析、报表系统），有时会故意违反更高的范式，允许一定的数据冗余，这就是反范式化。这是因为范化（规范化）在减少冗余的同时，也增加了表的数量，复杂的查询可能需要关联多张表，从而降低查询速度。因此，数据库设计往往是在数据冗余和查询性能之间做出权衡。

外键

外键起到链接两个表的作用，简单来说，就是一个数据库表中的字段，它指向另一个表的主键。

1
2
3
4
5
6
7
8
9
10
CREATE TABLE 子表名 (
    子表字段1 数据类型 约束条件,
    子表字段2 数据类型 约束条件,
    ...
    外键字段 数据类型,
    ...
    CONSTRAINT 外键约束名 FOREIGN KEY (外键字段) REFERENCES 主表名(主表主键字段)
    [ON DELETE CASCADE | SET NULL | NO ACTION | SET DEFAULT]
    [ON UPDATE CASCADE | SET NULL | NO ACTION | SET DEFAULT]
);
SQL

如果表已经存在

1
2
3
4
ALTER TABLE 子表名
ADD CONSTRAINT 外键约束名 FOREIGN KEY (外键字段) REFERENCES 主表名(主表主键字段)
[ON DELETE CASCADE | SET NULL | NO ACTION | SET DEFAULT]
[ON UPDATE CASCADE | SET NULL | NO ACTION | SET DEFAULT];
SQL

关于外键的补充

优点
  1. 数据完整性：
  • 外键约束确保了子表中的外键值必须存在于父表对应的主键或唯一键中，从而避免了孤立记录的出现，维护了数据的引用完整性。
  1. 级联操作：
  • 外键可以配置为自动处理相关表中的数据更新或删除操作，如级联更新和级联删除。这有助于保持数据的一致性，并减少了手动维护数据关系的复杂性。
  1. 减少应用程序代码复杂度：
  • 通过数据库层面的约束来管理数据关系，可以减轻应用程序开发人员在业务逻辑中实现这些规则的负担，使代码更加简洁和易于维护。
  1. 提高数据质量：
  • 自动化的检查和维护有助于保持数据的准确性和一致性，减少了人为错误的可能性。
  1. 索引支持：
  • 通常情况下，外键会被自动创建索引，这不仅加速了对关联数据的查找，也提高了连接查询（JOIN）的效率。
  1. 查询优化器辅助：
  • 外键信息可以帮助数据库查询优化器更好地理解表间的关系，从而生成更高效的执行计划。
缺点
  1. 性能开销：
  • 外键约束会增加数据库的维护成本。每次插入、更新或删除涉及外键的记录时，数据库都需要检查外键约束是否得到满足，这可能会导致额外的性能开销。在高并发或大数据量的系统中，这种性能开销可能更加明显。
  1. 级联操作风险：
  • 虽然级联操作有助于保持数据的一致性，但在某些情况下，它可能不是期望的行为。例如，删除一个记录可能会意外地删除与之相关的多个记录，导致数据丢失。
  1. 增加数据库复杂性：
  • 使用外键会增加数据库的复杂性，使得数据库结构更难理解和维护。特别是在大型系统中，多个表之间的复杂关系可能导致混淆和错误。
  1. 依赖性问题：
  • 外键创建了一种依赖性，即一个表中的数据依赖于另一个表中的数据。这种依赖性可能导致数据迁移或重构时的困难。
  1. 死锁风险：
  • 在高并发环境中，外键可能导致死锁问题。当多个事务试图同时更新涉及外键的数据时，可能会出现死锁，导致系统性能下降。
  1. 灵活性限制：
  • 使用外键可能会限制数据库结构的灵活性。例如，在分区或分表操作时，外键可能会成为一种约束，限制数据库设计的变化。

多表查询/连接查询

多表查询允许你一次在多张表中查询

常见的连接

连接类型 关键词 数据保留方向 未匹配数据处理 SQL示例
内连接 INNER JOIN 仅保留两个表中匹配的行 丢弃不匹配的行 SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
左外连接 LEFT JOIN 保留左表所有行及与右表匹配的行 右表未匹配部分填充NULL SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;
右外连接 RIGHT JOIN 保留右表所有行及与左表匹配的行 左表未匹配部分填充NULL SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;
全外连接 FULL JOIN 保留左表和右表中的所有行 未匹配部分在相应侧填充NULL SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id;（注意：不是所有数据库都支持FULL JOIN，有些数据库使用UNION ALL结合LEFT JOIN和RIGHT JOIN来实现类似功能）
交叉连接（笛卡尔积） CROSS JOIN 生成两个表中所有行的组合（笛卡尔积） 无特殊处理（所有组合均保留） SELECT * FROM table1 CROSS JOIN table2; 或简单地 SELECT * FROM table1, table2;

事务

SQL是操作语言，而操作必须是原子化的，事务的作用就是保证原子化。事务是一系列数据库操作的集合，这些操作被视为一个单一的工作单元。事务中的操作要么全部执行成功，要么在遇到错误时全部撤销。

事务具有四个关键特性，通常称为ACID特性：

  1. 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不执行。事务在执行过程中发生错误，则会回滚到事务开始前的状态。
  2. 一致性（Consistency）：事务执行前后，数据库都必须处于一致性状态。这意味着事务执行的结果必须是有效的，符合所有定义的规则、约束和触发器。
  3. 隔离性（Isolation）：并发执行的事务之间不会互相干扰。一个事务内部的操作对其他并发事务是不可见的，直到该事务提交。
  4. 持久性（Durability）：一旦事务提交，它对数据库的影响是永久的，即使系统崩溃也不会丢失。