SQLite3 数据库学习（一）：数据库和 SQLite 基础

参考引用

• SQL 必知必会
• SQLite 权威指南（第二版）
• 关系型数据库概述

1. 数据库基础

1.1 什么是数据库

• 数据库（database）：保存有组织的数据的容器（通常是一个文件或一组文件）

  • 可以将其想象为一个文件柜，此文件柜是一个存放数据的物理位置，不管数据是什么以及如何组织的

• 数据库软件应称为 DBMS（数据库管理系统），数据库是通过 DBMS 创建和操纵的容器

  • 在很大程度上说，数据库究竟是文件还是别的什么东西并不重要，因为你并不直接访问数据库，而是使用 DBMS，它替你访问数据库

1.2 为什么需要数据库？

因为应用程序需要保存用户的数据，比如 Word 需要把用户文档保存起来，以便下次继续编辑或者拷贝到另一台电脑。要保存用户的数据，一个最简单的方法是把用户数据写入文件

• 但是，随着应用程序的功能越来越复杂，数据量越来越大，如何管理这些数据就成了大问题

  • 读写文件并解析出数据需要大量重复代码
  • 从成千上万的数据中快速查询出指定数据需要复杂的逻辑

  如果每个应用程序都各自写自己的读写数据的代码，一方面效率低，容易出错，另一方面，每个应用程序访问数据的接口都不相同，数据难以复用

• 所以，数据库作为一种专门管理数据的软件就出现了。应用程序不需要自己管理数据，而是通过数据库软件提供的接口来读写数据。至于数据本身如何存储到文件，那是数据库软件的事情，应用程序自己并不关心

1.3 基本概念

1.3.1 表

• 表（table）：某种特定类型数据的结构化清单

  • 在你将资料放入自己的文件柜时，并不是随便将它们扔进某个抽屉就完事了，而是在文件柜中创建文件，然后将相关的资料放入特定的文件中
  • 在数据库领域中，这种文件称为表。表可以保存顾客清单、产品目录，或者其他信息清单
  • 存储在表中的数据是同一种类型的数据或一个清单
    • 不应该将顾客的清单与订单的清单存储在同一个数据库表中，因为这样做将使以后的检索和访问很困难，应该创建两个表，每个清单一个表

• 表名

  • 数据库中的每个表都有一个名字，用来标识自己
  • 此名字是唯一的，这表示数据库中没有其他表具有相同的名字，表名的唯一性取决于多个因素，如数据库名和表名等的结合，这表示在不同的数据库中可以使用相同的表名

• 模式（schema）：关于数据库和表的布局及特性的信息

  • 这些特性定义了数据在表中如何存储，如：可以存储什么样的数据，数据如何分解，各部分信息如何命名等

1.3.2 列和数据类型

• 列（column）：表中的一个字段，所有表都是由一个或多个列组成的

  • 理解列的最好办法是将数据库表想象为一个网格，网格中每一列存储着一条特定的信息
  • 例如，在顾客表中，一个列存储着顾客编号，另一个列存储着顾客名

• 数据类型（datatype）：所容许的数据的类型

  • 每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据种类
    • 例如，可以防止在数值字段中录入字符值
  • 数据类型还可帮助正确地排序数据，并在优化磁盘使用方面起到重要作用

1.3.3 行

• 行（row）：表中的一个记录
  • 表中的数据是按行存储的，所保存的每个记录存储在自己的行内
  • 例如，顾客表可以每行存储一个顾客，表中的行数为记录的总数

1.3.4 键

1、主键

• 在关系型数据库中，一张表中的每一行数据被称为一条记录
  • 一条记录就是由多个字段组成的。例如，下表 students 的两行记录

• 主键（primary key）：一列（或一组列），其值能够唯一区分表中每个行

  • 能够通过某个字段唯一区分出不同的记录，这个字段被称为主键
  • 例如，假设上表把 name 字段作为主键，那么通过名字小明或小红就能唯一确定一条记录

• 业务场景中选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。作为主键最好是完全业务无关的字段，一般把这个字段命名为 id，常见的可作为 id 字段的类型有：

  • 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样就完全不用担心主键重复，也不用自己预先生成主键
  • 全局唯一 GUID 类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID 算法通过网卡 MAC 地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的
  • 每个行都必须具有一个主键值（主键列不允许 NULL 值）

  通常，自增类型的主键就能满足需求。上表 students 中定义的主键也是 BIGINT NOT NULL AUTO_INCREMENT 类型

2、联合主键

• 关系型数据库还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键

  • 对于联合主键，允许一列有重复，只要不是所有主键列都重复即可

• 如果把上表的 id_num 和 id_type 这两列作为联合主键，那么上面的 3 条记录都是允许的，因为没有两列主键组合起来是相同的

  • 没有必要的情况下，尽量不使用联合主键，因为它给关系表带来了复杂度的上升

主键的最好习惯

• 不更新主键列中的值
• 不重用主键列的值
• 不在主键列中使用可能会更改的值

3、外键

一对多

• 当用主键唯一标识记录时，就可以在 students 表中确定任意一个学生的记录

• 还可以在 classes 表中确定任意一个班级记录

• 但是如何确定 students 表的一条记录，例如，id=1 的小明，属于哪个班级呢？
  • 由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为 “一对多”，即一个 classes 的记录可以对应多个 students 表的记录
  • 为了表达这种一对多的关系，需要在 students 表中加入一列 class_id，让它的值与 classes 表的某条记录相对应
  • 在 students 表中，通过 class_id 的字段，可以把数据与另一张表关联起来，这种列称为外键 (下图红框列)

• 这样，就可以根据 class_id 这个列直接定位出一个 students 表的记录应该对应到 classes 的哪条记录

  • 小明的 class_id 是 1，因此，对应的 classes 表的记录是 id=1 的一班
  • 小红的 class_id 是 1，因此，对应的 classes 表的记录是 id=1 的一班
  • 小白的 class_id 是 2，因此，对应的 classes 表的记录是 id=2 的二班

• 外键并不是通过列名实现的，而是通过定义外键约束实现的

  ALTER TABLE students
  ADD CONSTRAINT fk_class_id    // 外键约束名称，可随意指定
  FOREIGN KEY (class_id)        // 指定 class_id 作为外键
  REFERENCES classes (id)       // 指定这个外键将关联到 classes 表的 id 列（即 classes 表的主键）
  DROP FOREIGN KEY fk_class_id  // 删除外键约束 fk_class_id，删除外键约束并没有删除外键这一列
  

多对多

• 通过一个表的外键关联到另一个表，可以定义出一对多关系。有些时候，还需要定义 “多对多” 关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系
  • 多对多关系是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系

• 通过中间表 teacher_class 可知 teachers 到 classes 的关系

  • id=1 的张老师对应 id=1，2 的一班和二班
  • id=2 的王老师对应 id=1，2 的一班和二班
  • id=3 的李老师对应 id=1 的一班
  • id=4 的赵老师对应 id=2 的二班

• 同理可知 classes 到 teachers 的关系

  • id=1 的一班对应 id=1，2，3 的张老师、王老师和李老师
  • id=2 的二班对应 id=1，2，4 的张老师、王老师和赵老师

一对一

• 一个表的记录对应到另一个表的唯一一个记录
  • 例如，students 表的每个学生可以有自己的联系方式，如果把联系方式存入另一个表 contacts，就可以得到一个 “一对一” 关系
  • 实际上，一对一关系准确地说，是 contacts 表一对一对应 students 表

• 还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能
  • 例如，把一个大的用户表拆分为用户基本信息表 user_info 和用户详细信息表 user_profiles，大部分时候，只需要查询 user_info 表，并不需要查询 user_profiles 表，这样就提高了查询速度

1.3.5 索引

• 在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引

  • 索引是关系数据库中对某一列或多个列的值进行预排序的数据结构
  • 通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这就大大加快了查询速度

• 例如，对于 students 表，如果要经常根据 score 列进行查询，就可以对 score 列创建索引

  ALTER TABLE students
  ADD INDEX idx_score (score);
  //ADD INDEX idx_name_score (name, score);  // 索引名称是任意的，索引如果有多列，可以在括号里依次写上
  

唯一索引

• 在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，假设 students 表的 name 不能重复

  ALTER TABLE students
  ADD UNIQUE INDEX uni_name (name);  // 通过 UNIQUE 关键字就添加了一个唯一索引
  

1.4 数据模型

1.4.1 层次模型

• 层次模型就是以 “上下级” 的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树

1.4.2 网状模型

• 网状模型把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网

1.4.3 关系模型

• 关系模型把数据看作是一个二维表格，任何数据都可以通过行号 + 列号来唯一确定，它的数据模型看起来就是一个 Excel 表
  • 基于关系模型的关系型数据库成为主流，因为相比层次模型和网状模型，关系模型理解和使用起来最简单

• 现有主流的关系型数据库
  • 商用数据库，例如：Oracle，SQL Server，DB2 等
  • 开源数据库，例如：MySQL，PostgreSQL 等
  • 桌面数据库，以微软 Access 为代表，适合桌面应用程序使用
  • 嵌入式数据库，以 SQLite 为代表，适合手机应用和桌面程序

1.5 数据类型

• 通常来说，BIGINT 能满足整数存储的需求，VARCHAR(N) 能满足字符串存储的需求，这两种类型是使用最广泛的
  • 下表中列举了最常用的数据类型，各数据库厂商还会支持特定的数据类型，例如 JSON
    

2. SQL 介绍

• SQL（发音为字母 S-Q-L 或 sequel）是结构化查询语言（Structured Query Language）的缩写

  • SQL 是一种专门用来与数据库通信的语言

• SQL 的优点

  • SQL不是某个特定数据库供应商专有的语言
    • 几乎所有重要的 DBMS 都支持 SQL
  • SQL 简单易学
    • 它的语句全都是由描述性很强的英语单词组成，而且这些单词的数目不多
  • SQL 是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作

• SQL 语言定义 3 种操作数据库的能力

  • DDL：Data Definition Language
    • DDL 允许用户定义数据，也就是创建表、删除表、修改表结构这些操作
    • 通常，DDL 由数据库管理员执行
  • DML：Data Manipulation Language
    • DML 为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作
  • DQL：Data Query Language
    • DQL 允许用户查询数据，这也是通常最频繁的数据库日常操作

任意两个 DBMS（数据库管理系统）实现的 SQL 都不完全相同，无法完全直接移植

3. SQLite 介绍

3.1 简介

• SQLite 是一款开源的、嵌入式关系型数据库

  • 它没有独立运行的进程，它与所服务的应用程序在应用程序进程空间内共生共存
  • 它的代码与应用程序代码也是在一起的，或者说嵌入其中作为托管它的程序的一部分
  • 程序只需要做自己的事，管理自己的数据，不需要详细了解 SQLite 是如何工作的

• 数据库服务器和客户端运行在同一个进程中的好处是：不需要网络配置或管理

  • 不用担心防火墙或者地址解析，不用浪费时间管理复杂的授权和权限
  • 可以减少网络调用相关的消耗，简化数据库管理，使程序更容易部署

• 从下图可知，一个 Perl 脚本、一个标准 C/C++ 程序和一个 Apache 托管的 PHP 脚本都使用 SQLite

  • 这三种都有嵌入在它们进程空间的 SQLite，这样做不仅可以使这些进程运行各自的代码，也可以成为独立的数据库服务器
  • 此外，尽管每个进程中都有独立的数据库服务器，但它们依然可以操作同一个数据库文件，因为 SQLite 利用操作系统功能来完成数据的同步和加锁

3.2 体系结构

• 接口

  • 接口处于栈的顶端，由 SQLite C API 组成
  • 程序、脚本语言还有与 SQLite 交互的库文件最终都是通过它与 SQLite 交互的

• 编译器

  • 编译过程从词法分析器 (Tokenizer) 和语法分析器 (Parser) 开始。它们协同处理文本形式的结构化查询语句 (Structured Query Language，SQL)，分析其语法有效性，然后转化为底层能更方便处理的层次化数据结构
  • SQLite 词法分析器是手动编码实现，它的语法分析器是由 SQLite 特定的语法分析生成器 Lemon 产生

• 虚拟机

  • 架构栈的中心部分是虚拟机，也叫做虚拟数据库引 (Virtual DataBase Engine，VDEB)
  • VDBE 是 SQLite 的核心，它之前的所有模块都是用于创建 VDBE 程序的，它之后的所有模块都是用于执行 VDBE 序的，每次执行一条指令

• 后端

  • 后端由 B-tree、页缓存 (page cache) 以及操作系统接口组成
  • B-tree 将页面组织成树状结构，这种组织结构很适合搜索，页面就是树的叶子
  • pager (SQLite 的一种数据结构) 帮助 B-tree 管理页面，它负责传输

3.3 性能和局限性

3.3.1 性能

• 在对单表进行查询时平均而言，SQLite 与其他数据库一样快。简单的 SELECT、INSERT 和 UPDATE 是相当快速的。SQLite 通常要快其他数据库，因为它在处理一个事务开始，或者一个查询计划的产生方面开销较小，并且没有调用服务器的网络或认证以及权限协商的开销
• 但是随着查询变大变复杂，查询时间使得网络调用或者事务处理开销相形见绌，SQLite 将会与其他数据库一样，这时一些大型的设计复杂的数据库开始发挥作用了

3.3.2 局限性

• 并发
  • SQLite 的锁机制是粗粒度的，它允许多个读，但是一次只允许一个写
  • 写锁会在写期间排他地锁定数据库，其他人在此期间不能访问数据库
• 网络
  • 虽然 SQLite 数据库可以通过网络文件系统共享，但是与这种文件系统相关的潜在延时会导致性能受损
  • 如果文件系统的锁实现不当，可能允许两个客户端同时修改同一个数据库文件，这必然会导致数据库出错

并发性要求低、数据小于 1TB、本地存储的应用场景下可以考虑使用 SQLite

3.4 SQLite 安装

3.4.1 SQLite on Windows

• Qt 编译安装 SQLite

1. 源码下载
   

2. 在 Qt 中新建 C 项目
   

3. 将第 1 步下载的源码解压后全部拷贝到第 2 步中创建的 C 项目中（与 main.c 同级），并删除原有 main.c 文件（因为源码 shell.c 中也有 main 函数，会产生冲突）
   

4. 勾选下图所示选项
   

5. 编译成功如下
   

• 安装数据库查看工具 Sqlite Expert
  • Sqlite Expert

3.4.2 SQLite on Linux

• 安装 SQLite

  $ sudo apt-get install sqlite3
  

• 运行 SQLite
  

• 安装数据库查看工具 SQLite Browser

  $ sudo apt install sqlitebrowser -y
  

linux 下通过 gcc 编译 sqlite3

• gcc -o sqlite3 sqlite3.c shell.c -lpthread -ldl

linux下通过arm-linux-gcc交叉编译sqlite3

• arm-linux-gcc -o sqlite3 sqlite3.c shell.c -lpthread -ldl