简介和环境配置¶

Acknowledgement:

常见的数据库模型¶

数据库模型是指数据如何在数据库中组织、存储和操作的方式。以下是三种常见的数据库模型：

关系数据库模型（Relational Database Model）
- 特点: 使用 表格（表）来表示数据及其关系。每个表包含行和列，行表示记录，列表示字段。表与表之间通过外键关联。
- 优点: 数据的组织方式易于理解和使用，支持复杂的查询操作，数据的一致性和完整性容易维护。
- 常见的关系数据库管理系统: MySQL、PostgreSQL、Oracle、Microsoft SQL Server。
层次数据库模型（Hierarchical Database Model）
- 特点: 数据以 树形结构 组织，每个节点代表一个数据记录，父节点与子节点之间具有一对多的关系。
- 优点: 数据的访问路径固定，访问速度快，适用于处理层次结构明显的场景（例如组织结构、文件系统）。
- 缺点: 结构僵化，不易适应变化，复杂的查询较为困难。
- 示例: IBM的Information Management System（IMS）。
网络数据库模型（Network Database Model）
- 特点: 数据以 图结构 组织，允许多对多的关系。每个节点可以有多个父节点和多个子节点，节点之间通过边来表示关系。
- 优点: 灵活的关系表示方式，适用于复杂的多对多数据关系。
- 缺点: 数据结构复杂，操作难度大，设计和维护成本较高。
- 示例: Integrated Data Store (IDS)。

关系数据库模型是目前最广泛使用的一种，我们在本章节提到的所有内容都是以关系型数据库展开的。

引入¶

关系型数据库是建立在关系模型上的，其将数据看作若干个二维表格，我们可以将其看作一个个 Excel 表。其中：

表的每一行称为一条记录（Record），即一条 完整的数据；
表的每一列称为一个字段（Column），代表一条记录的 某个属性；

与 Excel 不同的是，在关系型数据库中表与表之间可以存在“一对多”，“多对一”以及“一对一”的关系。例如我们考虑选课问题：

每位同学拥有 [sid, name, major] 三个属性；
每个课程拥有 [cid, title, credit, time, grade] 五个属性；

倘若用一张 Excel 表格来存储，假设有 10 个学生，每个学生均选择了 10 门相同的课程，那么 Excel 表格的数据存储量约为 (3+5)×10×10=800，其中存在大量冗余；而在关系型数据库中，我们可以设计三张数据表（现阶段我们只需考虑 Field 与 Type 两列，每个 Field 表示一个字段：

alt text

SQL
mysql> DESC Students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| sid   | char(10)     | NO   | UNI | NULL    |                |
| name  | varchar(255) | NO   |     | NULL    |                |
| major | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> DESC Courses;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int          | NO   | PRI | NULL    | auto_increment |
| cid    | varchar(8)   | NO   | UNI | NULL    |                |
| title  | varchar(255) | NO   |     | NULL    |                |
| credit | int          | NO   |     | NULL    |                |
| time   | varchar(255) | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> DESC Association;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int     | NO   | PRI | NULL    | auto_increment |
| student_id | int     | YES  | MUL | NULL    |                |
| course_id  | int     | YES  | MUL | NULL    |                |
| grade      | char(2) | YES  |     | NULL    |                |
+------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

此时我们只需为每名同学和每个课程建立一行信息，并在 Association 中添加其关系即可，因此数据存储量约为 4×10+5×10+4×10×10=490。

Note

表结构解释

Students 表:
- 存储学生信息。包括 sid（学生ID）、name（姓名）和 major（专业）。
- 假设有 10 个学生，那么这个表将有 10 行数据。
Courses 表:
- 存储课程信息。包括 cid（课程ID）、title（课程名）、credit（学分）、time（时间）。
- 假设有 10 门课程，这个表将有 10 行数据。
Association 表:
- 存储学生和课程之间的关系。包括 student_id（指向 Students 表的外键）、course_id（指向 Courses 表的外键）和 grade（成绩）。
- 如果每个学生选择了 10 门课程，那么这个表将有 10（学生）×10（课程）= 100 行数据。

存储量计算

根据你描述的每个表的字段数和假设的数据量，我们可以计算出总存储量：

Students 表的存储量:
- 每个学生有 4 个字段 (id, sid, name, major)。
- 对于 10 个学生，总存储量 = 4 × 10 = 40。
Courses 表的存储量:
- 每个课程有 5 个字段 (id, cid, title, credit, time)。
- 对于 10 门课程，总存储量 = 5 × 10 = 50。
Association 表的存储量:
- 每个关联有 4 个字段 (id, student_id, course_id, grade)。
- 对于 100 个关联（10 个学生 × 10 门课程），总存储量 = 4 × 100 = 400。

总存储量

将所有表的存储量相加： \(Total=40(StudentsTable)+50(CoursesTable)+400(AssociationTable)=490\)

该问题在 Association 中通过两个外键来建立学生和课程之间的关联，展示了关系型数据库中的基本应用（外键将在之后学习）。在本节课中，我们将学习如何在这些数据表上进行所需操作。

使用配置¶

环境配置¶

(1) 针对Ubuntu

我的环境是macOS上使用OrbStack部署的Ubuntu虚拟机，虚拟机配置是：

Bash
huluobo@ubuntu22:/Users/huluobo$ hostnamectl
 Static hostname: ubuntu22
       Icon name: computer-container
         Chassis: container
      Machine ID: 651f1a31e0b74021b49881774c72a2e9
         Boot ID: e4a36d541ce04c06b998f8ce16f5a8a7
  Virtualization: lxc
Operating System: Ubuntu 22.04.4 LTS
          Kernel: Linux 6.10.6-orbstack-00249-g92ad2848917c
    Architecture: arm64

Note

hostnamectl 是一个用于查询和设置主机名以及查看系统信息的命令，在许多基于 Linux 的操作系统（包括 Ubuntu）中可用。它属于 systemd 工具套件的一部分，提供了一个简单的命令行接口来管理主机名及获取一些基本的系统信息。

使用 hostnamectl 的常见用途：查看系统信息

静态主机名
动态主机名
操作系统版本
内核版本
硬件架构
虚拟化技术

安装mysql：

Bash
sudo apt update
sudo apt upgrade
sudo apt install mysql-server

检验是否安装成功：

Bash
mysql --version

如安装成功，它会显示这样的界面：

Bash
huluobo@ubuntu22:/Users/huluobo$ mysql --version
mysql  Ver 8.0.39-0ubuntu0.22.04.1 for Linux on aarch64 ((Ubuntu))

进入mysql：

Bash
huluobo@ubuntu22:/Users/huluobo$ sudo mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.39-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

有没有感觉很熟悉？这种 mysql>的出现形式是不是让你回想起了在CLI中打开python？

进行用户管理（可选项）

在生产环境中，为了提高 MySQL 的安全性，我们有必要进行安全配置，可使用如下指令进行操作：

Bash
sudo mysql_secure_installation

这时会出现下列过程：

Bash
huluobo@ubuntu22:/Users/huluobo$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0

Skipping password set for root as authentication with auth_socket is used by default.
If you would like to use password authentication instead, this can be done with the "ALTER_USER" command.
See https://dev.mysql.com/doc/refman/8.0/en/alter-user.html#alter-user-password-management for more information.

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

移除不需要的默认用户（例如 root）；
为 root 用户设置密码，确保只有授权用户可以访问数据库；
MySQL 在安装后会自动创建一个无用户名无密码的匿名用户（Anonymous Account），默认情况下 MySQL 允许匿名用户连接到数据库服务器，以方便新用户快速上手。然而在生产环境中攻击者可能利用这个漏洞来尝试访问数据库并进行恶意操作，因此我们可以在该步骤中删除匿名用户；
禁止远程 root 登录，限制其只能在本地访问；
删除测试数据库：删除 MySQL 安装过程中创建的一些测试数据库。

(2) 针对macOS

本教程中，我们展示在 MacOS 上安装 MySQL 8 的详细步骤 (使用homebrew)

这里简单起见，我们介绍的是使用macOS下的包管理器homebrew安装的步骤，如果你的macOS目前并没有配置homebrew，那么可以参考这篇文档自行配置

安装 MySQL

Bash
brew install mysql

启动 MySQL 服务器

Bash
brew services start mysql

配置 MySQL 服务器

我们需要运行以下脚本配置 MySQL 服务器的安全性：

Bash
mysql_secure_installation

Tip

其实这是一个可选项，但是为了尽早体验和适应真实生产环境中的“用户管理”，笔者觉得在这里介绍下列配置是有必要的。

在这个过程中，你可以设置 root 的密码，配置一些选项以增强 MySQL 服务器的安全性。你会看到如下类似的输出：

SQL
Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 25
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.

All done!

用户设置¶

一个很需要注意的点是：在实际的生产环境中，我们不能做“root战神”，就算是很本地的环境，也应当在最开始的时候先创建一个“指定权限”的admin账户，然后使用admin进行相应操作

创建admin账户

首先我们创建一个 admin 用户并分配登录密码：

Bash
sudo mysql          # 以 root 用户登录

SQL
mysql> CREATE USER 'admin'@'%' IDENTIFIED BY '12345678';
Query OK, 0 rows affected (0.01 sec)

它表示允许用户 admin 从任意 IP 以密码 12345678 登录

这里值得注意的是：密码的分配需要符合一定的规则，这个规则在你刚安装好mysql的时候就已经设置好了（如果你有印象的话

当时为了便于演示，我选择的是low（数字0），即：最少要8位字符/数字，你也可以选择其他的形式😊

权限分配

新创建的用户是没有任何权限的（USAGE 表示没有任何权限），我们可以使用 SHOW GRANTS 进行权限查询：

SQL
mysql> SHOW GRANTS FOR 'admin'@'%';
+-----------------------------------+
| Grants for admin@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `admin`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)

为方便后续操作，我们为 admin 添加所有数据库和数据表的所有权限：

SQL
mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';
Query OK, 0 rows affected (0.02 sec)

用户登录

使用 exit 语句或者使用 Ctrl-D 即可退出登录：

SQL
mysql> exit
Bye

用命令行登录 admin：

SQL
mysql -u admin -p   # 之后输入密码即可登录

或者使用也可直接登录，

SQL
mysql -u admin -p12345678

但是这样会在 shell 的历史记录中留下密码的明文记录，因此不推荐在服务器中使用：

SQL
cat .bash_history   # .zsh_history 等