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 的二班
    
  
  
• 外键并不是通过列名实现的，而是通过界说外键约束实现的
  
  1. ALTER TABLE students
  2. ADD CONSTRAINT fk_class_id    // 外键约束名称，可随意指定
  3. FOREIGN KEY (class_id)        // 指定 class_id 作为外键
  4. REFERENCES classes (id)       // 指定这个外键将关联到 classes 表的 id 列（即 classes 表的主键）
  5. 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 列创建索引
  
  1. ALTER TABLE students
  2. ADD INDEX idx_score (score);
  3. //ADD INDEX idx_name_score (name, score);  // 索引名称是任意的，索引如果有多列，可以在括号里依次写上

  复制代码

唯一索引

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

• 源码下载
  
  
  
  
• 在 Qt 中新建 C 项目
  
  
  
  
• 将第 1 步下载的源码解压后全部拷贝到第 2 步中创建的 C 项目中（与 main.c 同级），并删除原有 main.c 文件（由于源码 shell.c 中也有 main 函数，会产生冲突）
  
  
  
  
• 勾选下图所示选项
  
  
  
  
• 编译乐成如下
  
  
  
  

• 安装数据库检察工具 Sqlite Expert
  
  
  
  • Sqlite Expert
    
  
  

3.4.2 SQLite on Linux

• 安装 SQLite
  
  1. $ sudo apt-get install sqlite3

  复制代码
• 运行 SQLite
  
  
  
  
• 安装数据库检察工具 SQLite Browser
  
  1. $ 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
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。