桌面应用开发需要掌握哪些数据库知识（下）？
在上一节中，我们使用 SQLite Expert 创建了一个数据库，并把这个数据库配置到工程中；我们还让 electron-builder 把这个数据库打包到安装包内，在用户初次运行应用时，把数据库文件拷贝到用户数据目录下；除了这些知识外，我们还介绍了如何使用 Knex 完成基本的数据库操作。
显然这些数据库知识对于一个复杂应用来说是不够的，本节将更深入地学习数据库知识，比如关系型数据库是如何描述数据之间的关系的，如何在事务中操作数据，如何分页检索数据等知识。
数据之间的关系
在一个复杂的应用系统中，往往需要用到很多表来存储数据，每个表都存储一类数据，比如：聊天会话数据会存储在一张表中，聊天消息数据会存储在另一张表中，聊天的用户信息会存储在第三张表中。
很显然这些表中的数据是有联系的，接下来我们就介绍一下关系型数据库中常见的几种数据关系。
1. 一对一关系
一对一关系是几种关系中最简单的关系，它表示一个表的数据与另一个表的数据行与行之间是一一对应的。
现实生活中这种一一对应的关系非常常见，比如：用户账户信息（用户名、密码等）与用户的身份信息（性别、身份证号等）就是一一对应的。
一个用户不能拥有多个账户，一个账户也不能被分配给多个用户，而且账号信息和身份信息也属于两类不同的信息，不适合放在同一张表内。
当然，有时候开发者也会因为某类信息的字段太多了而强行把字段分别存储在两张或多张表中，这些表里的数据关系都是一对一关系。
下面这段代码可以从两张一对一关系表中查询数据：
knex("user").leftJoin("userInfo", "user.id", "userInfo.userId").select("user.name", "userInfo.idNumber");其中，leftJoin 方法把两个表连接在一起，user 表中的 id 字段（是 user 表的主键）和 userInfo 表中的 userId 字段是关系字段（主键和外键），查询数据时，就是使用这两个关系字段完成数据检索的。
在向这两个表中插入数据时要保证这两个字段的内容是相同的，这样才能保证检索出的数据是一一对应的。比如新建一个用户时，在创建用户信息时，要把 userId 字段设置为某一个用户的 Id，也就是 user 表中某一行记录的主键值。如下代码所示：
let user = new ModelUser(); //这个对象的id是它的基类自动生成的，我们前面介绍过
user.userName = "abc";
user.password = "123";
await db("User").insert(user);
let userInfo = new ModelUserInfo(); //这个对象的id也是它的基类自动生成的
userInfo.userId = user.id;
userInfo.trueName = "元始天尊";
await db("UserInfo").insert(userInfo);上面的代码在向 UserInfo 表插入数据时就使用了 User 表中的某行记录的 ID，所以 User 表和 UserInfo 表存在两行有关系的数据。
如果两张表中存在多行这样有关的数据的话，我们使用 leftJoin 方法检索两张表里的数据时，也会返回多行数据。
leftJoin 会保证从左表（user）那里返回所有的行，即使在右表（userInfo）中没有匹配的行也会返回数据，比如检索到了 user.name，而 userInfo.idNumber 却是空值。
rightJoin 方法则正好相反。它会保证右表（userInfo）返回所有的数据，即使右表没有匹配的行也会返回数据。
不但一对一关系可以使用 leftJoin 或 rightJoin 方法检索关联的数据信息，其他几种关系也会使用这两个方法检索关联的信息。
2. 一对多关系
一对多关系是一个表中的某行数据对应另一个表中的多行数据，另一个表中的某行数据只对应第一张表中的一行数据。
比如，我们提到的聊天会话信息和聊天消息信息就是一种一对多关系。一个会话可以对应着多个聊天信息，但一个聊天信息不可能隶属于多个会话。在数据库中的表现形式就是，Message 表中可能有多行记录拥有同一个 ChatId。
3. 多对多关系
多对多关系是一个表中的某行数据对应另一个表中的多行数据，另一个表中的某行数据也对应第一张表中的多行数据。现实生活中老师和学生的关系就是多对多关系，一个学生可以有多个老师，一个老师也可以有多个学生。
在数据库中表述这种关系一般需要三张表，拿刚才的例子来说，老师信息存储在一张表中，学生信息存储在一张表中，还需要一张关系表，用来描述老师和学生的关系。这张关系表中，一般会有老师的 ID 和学生的 ID。这样就建立了老师和学生之间的多对多关系。
比如，要查询某位老师下的所有学生，可以先在老师表查询到老师的信息，再到关系表查询到所有学生的 ID，再使用这些学生的 ID 查询到所有学生的信息。查询某位学生的所有老师把这个操作反过来做即可。
4. 自关联关系
自关联关系往往用来描述树状结构的信息，比如菜单、部门等。比如：一级菜单、二级菜单、三级菜单......N 级菜单；一级部门、二级部门、三级部门......N 级部门。
我们在设计系统时往往不知道具体有多少层，具体的层级数量是用户在使用过程中确定的。即使我们知道有多少层，把每层的数据存储在一张表中也不是个好主意，我们应该把所有的数据都存储在一张表中，表中有两个字段非常关键：ID 和 ParentID，我们用这两个字段让表中的数据自己和自己进行关联，使用类似递归的方法遍历某个层级下的所有数据。
在事务中操作数据
数据库的事务是一个操作序列，包含了一组数据库操作指令。事务把这组指令作为一个整体向数据库提交操作请求，这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。
举个例子，当用户购买了一件商品后，你需要向数据库的订单表插入一行记录，同时需要向付款信息表插入一行记录（实际购物平台的实现逻辑要远比这个例子复杂得多），假设订单数据插入成功了，付款数据却由于种种原因没有插入成功，这种异常对于系统来说是不可容忍的。
事务就是为了解决这种问题的，下面我们看一下使用 Knex 提交事务的代码：
//src\renderer\Window\WindowMain\Contact.vue
let transaction = async () => {
try {
await db.transaction(async (trx) => {
let chat = new ModelChat();
chat.fromName = "聊天对象aaa";
chat.sendTime = Date.now();
chat.lastMsg = "这是此会话的最后一条消息";
chat.avatar = `https://pic3.zhimg.com/v2-306cd8f07a20cba46873209739c6395d_im.jpg?source=32738c0c`;
await trx("Chat").insert(chat);
// throw "throw a error";
let message = new ModelMessage();
message.fromName = "聊天对象";
message.chatId = chat.id;
message.createTime = Date.now();
message.isInMsg = true;
message.messageContent = "这是我发给你的消息";
message.receiveTime = Date.now();
message.avatar = `https://pic3.zhimg.com/v2-306cd8f07a20cba46873209739c6395d_im.jpg?source=32738c0c`;
await trx("Message").insert(message);
});
} catch (error) {
console.error(error);
}
};上面的代码就是把两个插入操作封装到了一个事务中，两个插入操作要么都成功执行，要么一个也不执行，你可以把throw "throw a error" 语句取消注释，观察一下数据库的数据更新情况。
db.transaction方法的回调函数中trx就是 Knex 为我们封装的数据库事务对象，我们接下来的数据库操作，都是使用这个对象完成的。
除了这种用法外，我们也可以用类似下面这样的代码完成事务操作：
await db("Message").insert(message).transacting(trx);总之，事务对象必须参与到具体的数据操作过程中。
分页查询数据
分页从数据库中获取数据是后端开发者经常要做的工作，前端开发者往往都是按照后端开发者提供的接口要求，从接口获取数据就可以了，然而开发桌面应用，往往会遇到这样的需求，需要从本地数据库中分页获取数据。下面就给出一个简单的实现代码，看看后端工程师都是如何完成这项工作的，代码如下：
//src\renderer\Window\WindowMain\Contact.vue
/**
* 当前是第几页
*/
let currentPageIndex = 0;
/**
* 每页数据行数
*/
let rowCountPerPage = 6;
/**
* 总页数(可能有小数部分)
*/
let pageCount = -1;
/**
* 获取某一页数据
*/
let getOnePageData = async () => {
let data = await db("Chat")
.orderBy("sendTime", "desc")
.offset(currentPageIndex * rowCountPerPage)
.limit(rowCountPerPage);
console.log(data);
};
/**
* 获取第一页数据
*/
let getFirstPage = async () => {
if (pageCount === -1) {
let { count } = await db("Chat").count("id as count").first();
count = count as number;
pageCount = count / rowCountPerPage;
}
currentPageIndex = 0;
await getOnePageData();
};
/**
* 获取下一页数据
*/
let getNextPage = async () => {
if (currentPageIndex + 1 >= pageCount) {
currentPageIndex = Math.ceil(pageCount) - 1;
} else {
currentPageIndex = currentPageIndex + 1;
}
await getOnePageData();
};
/**
* 获取上一页数据
*/
let getPrevPage = async () => {
if (currentPageIndex - 1 < 0) {
currentPageIndex = 0;
} else {
currentPageIndex = currentPageIndex - 1;
}
await getOnePageData();
};
/**
* 获取最后一页数据
*/
let getLastPage = async () => {
currentPageIndex = Math.ceil(pageCount) - 1;
await getOnePageData();
};这段代码有以下几点需要注意。
-
获取第一页数据的时候，我们初始化了总页数和当前页码数，总页数是通过数据库中的总行数除以每页行数得到的，这个值有可能包含小数部分。当前页码数是从零开始的整数。第一页时，它的值为 0。
-
获取下一页数据时，我们要判断当前页码数是不是到达了最后一页，如果没有，那么就把当前页码数加 1，考虑到总页数存在小数的可能，所以最后一页的当前页码数应为：
Math.ceil(pageCount) - 1。Math.ceil() 函数返回大于或等于一个给定数字的最小整数。Math.ceil(6.11)的结果为 7，Math.ceil(6)的结果为 6 -
获取上一页数据时，我们要判断当前页码数是不是小于 0，如果是，就把当前页码数置为 0，如果不是就把当前页码数减一。
-
获取最后一页数据时，直接把当前页码数置为
Math.ceil(pageCount) - 1即可。 -
每次获取数据我们都调用了
getOnePageData方法。这个方法中需要注意offset和limit方法的使用，offset方法是跳过 n 行的意思，limit方法是确保返回的结果中不多于 n 行的意思。当最后一页数据不足rowCountPerPage（值为 6）时，就返回数据库表中剩余的所有数据（在上述示例中，如果数据库表中有 40 行数据时，最后一页会返回 4 行数据），不会出错。 -
分页获取数据最好提供明确的排序规则：注意
orderBy的使用，常常还会有复杂的查询条件。 -
在实际的桌面应用中一般不会要求用户点击上一页、下一页等按钮分页获取数据，大部分情况都是根据滚动条滚动时的
触底或触顶事件来触发数据获取的逻辑，不过从数据库中读取数据的逻辑还是大同小异的，都是一页一页读取的。
总结
本节我们首先介绍了关系型数据库是如何描述数据和数据之间的关系的，其中包括一对一关系、一对多关系、多对多关系和自关联关系，在介绍这部分知识时，我们还介绍了如何使用 leftJoin 和 rightJoin 完成数据库的关联查询。
接着我们介绍了两个基本数据库操作知识，一个是使用事务操作数据，一个是分页查询数据，这都是后端开发者的基本技能，但前端开发者比较欠缺的知识。
相信你已经对使用 Knex 操作 SQLite 有了一个基本的认识，接下去我们将介绍如何为 Electron 应用开发原生模块，让你在客户端领域的“权力”大大提升。
源码
本节示例代码请通过如下地址自行下载：