建立网站需要多少钱,动易如何做网站,网页效果图制作,建设工程包括哪些参考引用 SQL 必知必会SQLite 权威指南#xff08;第二版#xff09;关系型数据库概述 1. 数据库基础
1.1 什么是数据库 数据库#xff08;database#xff09;#xff1a;保存有组织的数据的容器#xff08;通常是一个文件或一组文件#xff09; 可以将其想象为一个文… 参考引用 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 表的一条记录例如id1 的小明属于哪个班级呢 由于一个班级可以有多个学生在关系模型中这两个表的关系可以称为 “一对多”即一个 classes 的记录可以对应多个 students 表的记录为了表达这种一对多的关系需要在 students 表中加入一列 class_id让它的值与 classes 表的某条记录相对应在 students 表中通过 class_id 的字段可以把数据与另一张表关联起来这种列称为外键 (下图红框列) 这样就可以根据 class_id 这个列直接定位出一个 students 表的记录应该对应到 classes 的哪条记录 小明的 class_id 是 1因此对应的 classes 表的记录是 id1 的一班小红的 class_id 是 1因此对应的 classes 表的记录是 id1 的一班小白的 class_id 是 2因此对应的 classes 表的记录是 id2 的二班 外键并不是通过列名实现的而是通过定义外键约束实现的 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 的关系 id1 的张老师对应 id12 的一班和二班id2 的王老师对应 id12 的一班和二班id3 的李老师对应 id1 的一班id4 的赵老师对应 id2 的二班 同理可知 classes 到 teachers 的关系 id1 的一班对应 id123 的张老师、王老师和李老师id2 的二班对应 id124 的张老师、王老师和赵老师
一对一
一个表的记录对应到另一个表的唯一一个记录 例如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 表 基于关系模型的关系型数据库成为主流因为相比层次模型和网状模型关系模型理解和使用起来最简单 现有主流的关系型数据库 商用数据库例如OracleSQL ServerDB2 等开源数据库例如MySQLPostgreSQL 等桌面数据库以微软 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 种操作数据库的能力 DDLData Definition Language DDL 允许用户定义数据也就是创建表、删除表、修改表结构这些操作通常DDL 由数据库管理员执行 DMLData Manipulation Language DML 为用户提供添加、删除、更新数据的能力这些是应用程序对数据库的日常操作 DQLData 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 LanguageSQL)分析其语法有效性然后转化为底层能更方便处理的层次化数据结构SQLite 词法分析器是手动编码实现它的语法分析器是由 SQLite 特定的语法分析生成器 Lemon 产生 虚拟机 架构栈的中心部分是虚拟机也叫做虚拟数据库引 (Virtual DataBase EngineVDEB)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 $ sudo apt-get install sqlite3运行 SQLite 安装数据库查看工具 SQLite Browser $ sudo apt install sqlitebrowser -ylinux 下通过 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
