简单了解sqlite数据库
初战SQLite数据库
0x00 关系型数据库
介绍
关系型数据库：是指采⽤了关系模型来组织数据的数据库，关系模型只包含单一的数据结构——关系。在用户看来，关系模型中的数据的逻辑结构就是一张扁平的二维表，想要弄清楚这个模型，就需要一定的集合代数的知识
-
域（Di）：是一组相同数据类型的值的集合
-
笛卡尔积（Di × Dj）：是域上的一种集合运算
笛卡尔积可以表示为一张二维表，表中的每一行对应一个元组，每一列的值表示一个域
-
关系：D1 × D2 × ... × Dn的子集称作在域 D1，D2，...，Dn上的关系，表示为R(D1, D2, ..., Dn)
-
关系模式：对关系的描述
关系模式通过可以简记为 R(U) 或 R(A1, A2, ..., An) ，其中R为关系名，A1, A2, ..., An为属性名
我们继续说回关系数据库上面来，关系型数据库也有型和值之分
- 关系数据库的型也称为关系数据库模式，是对关系数据库的描述。关系数据库模式包括若干域的定义，以及在这些域上定义的若干关系模式
- 关系数据库的值是这些关系模式在某一时刻对应的关系的集合，通常就成为关系数据库
值得一提的是，通过上面的讲解，我们知道了在关系数据模型中实体与实体间的联系都用表来表示，即表为关系数据的逻辑模型。但是在关系数据库的物理组织中，有的关系数据库管理系统中的一个表对应一个操作系统文件，将物理数据组织交给操作系统完成；有的关系数据库管理系统从操作系统那里申请了若干个大的文件，自己划分文件空间，组织表、索引等存储结构，并进行存储管理
我们用通俗的语句总结一下关系模型中的常用概念：
- 关系：⼀张⼆维表，每个关系都具有⼀个关系名，也就是表名
- 元组：⼆维表中的⼀⾏，在数据库中被称为记录
- 属性：⼆维表中的⼀列，在数据库中被称为字段，属性必须为同一个域下的值
- 域：属性的取值范围，也就是数据库中某⼀列的取值限制
- 关键字：⼀组可以唯⼀标识元组的属性，数据库中常称为主键，由⼀个或多个列组成
- 关系模式：指对关系的描述。其格式为：
关系名(属性1, 属性2, ..., 属性N)，在数据库中被称为表结构
ACID 原则
数据库事务必须具备 ACID 特性， ACID 分别是Atomic原⼦性， Consistency ⼀致性， Isolation 隔离性， Durability 持久性
它们描述了对分布式数据库的一致性需求，同时付出了可用性的代价
- Atomicity：每次操作是原子的，要么成功，要么不执行；
- Consistency：数据库的状态是一致的，无中间状态；
- Isolation：各种操作彼此互相不影响；
- Durability：状态的改变是持久的，不会失效
优点
- 容易理解：二维表结构十分贴近逻辑世界
- 使用方便：通⽤的SQL语⾔使得操作关系型数据库⾮常⽅便
- 易于维护：拥有完整性约束
缺点
- 性能欠佳：表查询的效率较低
- 难以横向扩展：只能新增一个表
主流关系型数据库
Oracle | MsSQL | MySQL | PostgreSQL | DB2 | Access | SQLite | MariaDB（MySQL的一个分支）
0x01 非关系型数据库
Tips：Nosql泛指非关系型数据库
介绍
非关系型数据库：指⾮关系型的，分布式的，且⼀般不保证遵循ACID原则的数据存储系统。⾮关系型数据库以键值对存储，且结构不固定，每⼀个元组可以有不⼀样的字段，每个元组可以根据需要增加⼀些⾃⼰的键值对，不局限于固定的结构，可以减少⼀些时间和空间的开销。
优点
- 用户可以根据需求添加自己需要的字段
- 适用于
SNS(Social Networking Services)，即功能的增加，往往意味着数据结构巨⼤变动的场景
缺点
- 只适合存储⼀些较为简单的数据，不适合存取需要较为复杂查询的数据
- 不适合持久存储海量数据
分类
面向高性能并发读写的key-value数据库
特点：是一种以键值对存储数据的一种数据库，类似于Java中的map
作用：具有极高的并发读写性能
代表：Redis
面向海量数据访问的面向文档数据库
特点：⽂档存储通常使⽤内部表⽰法，可以直接在应⽤程序中处理，主要是JSON
作用：可以在海量的数据中快速的查找数据
代表：MongoDB
面向搜索数据内容的搜索引擎
特点：是专⻔⽤于搜索数据内容的NoSQL数据库管理系统
作用：主要是⽤于对海量数据进⾏近实时的处理和分析处理，可⽤于机器学习和数据挖掘
代表：Solr
面向可扩展性的分布式数据库
特点：它是以列为单位来存储数据的，可以解决传统数据库存在可扩展性上的缺陷
作用：这类数据库可以适应数据量的增加以及数据结构的变化，将数据存储在记录中，能够容纳⼤量动态列。由于列名和记录键不是固定的，并且由于记录可能有数⼗亿列，因此可扩展性存储可以看作是⼆维键值存储
代表：HBase
关系型数据库与非关系型数据库的比较
| 比较值 | 关系型数据库 | 非关系型数据库 |
|---|---|---|
| 成本 | 需要花费大量成本 | 简单易部署 开源/价值便宜 |
| 查询速度 | 数据存储在硬盘中，查询速度较慢 | 数据存储在缓存中，查询速度较快 |
| 存储格式 | 只支持基础类型 | 可以是各种格式 |
| 扩展性 | 有类似于join的多表查询机制导致扩展较为困难 | 数据之间没有耦合性，非常容易水平扩展 |
| 持久存储 | 使用，所以海量数据存储只能是关系型数据库 | 不使用 |
| 数据一致性 | 数据强一致性 | 数据最终一致性，不支持事务处理 |
0x02 Windows环境中SQLite的安装
之前接触过Mysql，所以这里就不选择Mysql进行学习。本来是想学使用人数最多的Oracle的，但是5GB的安装包直接劝退，所以就选择学习SQLite这个较为容易安装的数据库，其实用Docker都是几条命令的事，但是还是想自己先装一遍试试
环境：Windows10
安装
进入官网下载压缩包
将压包中的文件集中放到一个文件夹下
配置
C:\Users\hikki
λ path=%path%;E:\sqlite # 注: 通过命令将路径加入用户环境变量
C:\Users\hikki
λ sqlite3
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
其实在没有配环境变量的时候，我就发现电脑里面好像可以直接执行这个命令，然后用everything查了一下
无语了，早知道不装了
设计介绍
不是常见的CS架构的DBMS，SQLite引擎不是一个应用程序与之通信的独立进程，即不需要使用IP加端口号进行连接
SQLite库链接到程序中，并成为它的一个组成部分。这个库也可被动态链接。应用程序经由编程语言内的直接API调用来使用SQlite的功能，这在减少数据库访问延迟上有积极作用，因为在一个单一进程中的函数调用比跨进程通信更有效率。
如此设计就导致了我们使用数据库管理软件只需要指定一个数据库文件就能连接，我们以Navicat为例在下方演示
连接数据库管理软件
-
创建一个db文件
-
进行连接
0x03 SQLite基础教程
① - SQLite命令
SQLite编程人员所使用的简单而有效的命令：SQLite点命令，它们以.开头但不以;结束，在SQLite命令行下可以执行，但不支持在Navicat中使用
.help命令
通过该命令，可以显示各种重要的 SQLite 点命令的列表
具体解释详见：SQLite 命令 | 菜鸟教程 (runoob.com)
.show命令
通过该命令，可以查看 SQLite 命令提示符的默认设置
.quit命令
通过该命令，可以退出命令行模式
美化输出格式
通过以下命令来可以美化输出格式
sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>
② - SQLite语法
- SQLite大小写不敏感，但是部分关键字大小写敏感
- 单行注释：
-- - 多行注释：
/* */ - SQLite语句均以
;结束
③ - 数据类型
SQLite是一个弱类型语言，它使用一个更普遍的动态类型系统。在 SQLite 中，值的数据类型与值本身是相关的，而不是与它的容器相关。
SQLite存储类
| 存储类 | 描述 |
|---|---|
| NULL | 值是一个 NULL 值。 |
| INTEGER | 值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。 |
| REAL | 值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。 |
| TEXT | 值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。 |
| BLOB | 值是一个 blob 数据，完全根据它的输入存储。 |
由于SQLite采用的是动态数据类型，所以SQLite存储类并不具有强制性。但是为了最大化SQLite和其它数据库引擎之间的数据类型兼容性，SQLite提出了"类型亲缘性(Type Affinity)"的概念。这个我们可以这样理解，在表字段被声明之后，SQLite都会根据该字段声明时的类型为其选择一种亲缘类型，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式，除非亲缘类型不匹配或无法转换当前数据到该亲缘类型，这样SQLite才会考虑其它更适合该值的类型存储该值。
SQLite 亲和(Affinity)类型
| 亲和类型 | 描述 |
|---|---|
| TEXT | 数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。 |
| NUMERIC | 当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。 |
| INTEGER | 对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时。 |
| REAL | 其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。 |
| NONE | 不做任何的转换，直接以该数据所属的数据类型进行存储。 |
字段的亲缘性是根据该字段在声明时被定义的类型来决定的，具体的规则可以参照以下列表。需要注意的是以下列表的顺序，即如果某一字段类型同时符合两种亲缘性，那么排在前面的规则将先产生作用。
- 如果类型字符串中包含"INT"，那么该字段的亲缘类型是INTEGER。
- 如果类型字符串中包含"CHAR"、"CLOB"或"TEXT"，那么该字段的亲缘类型是TEXT，如VARCHAR。
- 如果类型字符串中包含"BLOB"，那么该字段的亲缘类型是NONE。
- 如果类型字符串中包含"REAL"、"FLOA"或"DOUB"，那么该字段的亲缘类型是REAL。
- 其余情况下，字段的亲缘类型为NUMERIC。
Boolean数据类型
SQLite 没有单独的 Boolean 存储类。相反，布尔值被存储为整数 0（false）和 1（true）
Date和Time数据类型
SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值
| 存储类 | 日期格式 |
|---|---|
| TEXT | 格式为 "YYYY-MM-DD HH:MM:SS.SSS" 的日期。 |
| REAL | 从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数。 |
| INTEGER | 从 1970-01-01 00:00:00 UTC 算起的秒数。 |
④ - 库操作
之前操作Navicat的时候，大家应该注意到了，SQLite没有数据库这个概念，而是通过一个文件代表一个数据库的方式来存储数据
创建数据库
方法一：cmd输入
语句：sqlite3 DatabaseName.db
如果当前目录下DatabaseName.db文件存在，则直接使用里面的数据；否则，将创建DatabaseName.db文件
方法二：进入sqlite3命令行模式后
语句：.open DatabaseName.db
如果当前目录下DatabaseName.db文件存在，则相当于Mysql中的use DatabaseName；否则，将创建DatabaseName.db文件，并且执行use DatabaseName
检查数据库
可以通过.database命令检查新建的数据库是否保存在数据库列表中
导出 / 导入数据库中的数据
导出：sqlite3 testDB.db .dump > testDB.sql
导入：sqlite3 testDB.db < testDB.sql
附加数据库
附加一个数据库，相当于当前可用使用这个数据库下所有的表，如果该数据库文件不存在，则会新建这个文件
语句：ATTACH DATABASE '新的数据库文件.db' as '别名';
sqlite> select * from COMPANY;
Run Time: real 0.000 user 0.000000 sys 0.000000
Error: no such table: COMPANY
sqlite> .databases
main: E:\sqlite\db\test02.db r/w
sqlite> attach database 'test01.db' as 'newtest';
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> .database
main: E:\sqlite\db\test02.db r/w
newtest: E:\sqlite\db\test01.db r/w
sqlite> select * from COMPANY;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
Run Time: real 0.003 user 0.000000 sys 0.000000
分离数据库
分离一个数据库，将导致这个数据库中的表将不再被运行访问，你不能分离main或temp数据库
语句：DETACH DATABASE '别名';
sqlite> .databases
main: E:\sqlite\db\test02.db r/w
newtest: E:\sqlite\db\test01.db r/w
sqlite> detach database 'newtest';
Run Time: real 0.001 user 0.000000 sys 0.000000
sqlite> .databases
main: E:\sqlite\db\test02.db r/w
⑤ - 表操作
创建表
CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
查看所有表
命令行语句语句：.table
编程语句： SELECT tbl_name FROM sqlite_master WHERE type = 'table';
sqlite> .table
COMPANY DEPARTMENT
sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';
tbl_name
----------
DEPARTMENT
COMPANY
查看表结构
命令行语句：.schema TABLE_NAME
编程语句：SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'TABLE_NAME';
sqlite> CREATE TABLE COMPANY(
...> ID INT PRIMARY KEY NOT NULL,
...> NAME TEXT NOT NULL,
...> AGE INT NOT NULL,
...> ADDRESS CHAR(50),
...> SALARY REAL
...> );
sqlite> .tables
COMPANY
sqlite> .schema COMPANY
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
sql
--------------------------------------------------------
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
)
删除表
语句：DROP TABLE database_name.table_name;
sqlite> .tables
COMPANY newdb.COMPANY newdb.DEPARTMENT
sqlite> drop table COMPANY;
sqlite> .tables
newdb.COMPANY newdb.DEPARTMENT
如不指定数据库，则会默认删除main数据库中的表
如果出现同名表，也只会删除指定数据库中的表
⑥ - 数据操作
填充数据
语句
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
实例
sqlite> .schema COMPANY
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
...> VALUES (1, 'Paul', 32, 'California', 20000.00 );
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
...> VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
查询数据
获取指定字段：SELECT column1, column2, columnN FROM table_name;
获取所有字段：SELECT * FROM table_name;
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM COMPANY;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
sqlite> SELECT ID, NAME FROM COMPANY;
ID NAME
-- -----
1 Paul
2 Allen
Distinct 关键字：用于消除重复数据
sqlite> SELECT AGE FROM COMPANY ORDER BY AGE;
AGE
---
20
22
23
24
24
25
25
27
sqlite> SELECT DISTINCT AGE FROM COMPANY ORDER BY AGE;
AGE
---
20
22
23
24
25
27
修改数据
语句
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
实例
sqlite> SELECT * FROM COMPANY;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
sqlite> UPDATE COMPANY SET AGE = '20', NAME = 'H1kki' WHERE ID = 1;
sqlite> SELECT * FROM COMPANY;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 H1kki 20 California 20000.0
2 Allen 25 Texas 15000.0
删除数据
语句：DELETE FROM table_name WHERE [condition];
sqlite> SELECT * FROM COMPANY;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 H1kki 20 California 20000.0
2 Allen 25 Texas 15000.0
sqlite> DELETE FROM COMPANY WHERE ID = 2;
sqlite> SELECT * FROM COMPANY;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 H1kki 20 California 20000.0
⑦ - 运算符
算数运算符
假设变量 a=10，变量 b=20
| 运算符 | 描述 | 实例 |
|---|---|---|
| + | 加法 - 把运算符两边的值相加 | a + b 将得到 30 |
| - | 减法 - 左操作数减去右操作数 | a - b 将得到 -10 |
| * | 乘法 - 把运算符两边的值相乘 | a * b 将得到 200 |
| / | 除法 - 左操作数除以右操作数 | b / a 将得到 2 |
| % | 取模 - 左操作数除以右操作数后得到的余数 | b % a will give 0 |
比较运算符
假设变量 a=10，变量 b=20
| 运算符 | 描述 | 实例 |
|---|---|---|
| == | 检查两个操作数的值是否相等，如果相等则条件为真。 | (a == b) 不为真。 |
| = | 检查两个操作数的值是否相等，如果相等则条件为真。 | (a = b) 不为真。 |
| != | 检查两个操作数的值是否相等，如果不相等则条件为真。 | (a != b) 为真。 |
| <> | 检查两个操作数的值是否相等，如果不相等则条件为真。 | (a <> b) 为真。 |
| > | 检查左操作数的值是否大于右操作数的值，如果是则条件为真。 | (a > b) 不为真。 |
| < | 检查左操作数的值是否小于右操作数的值，如果是则条件为真。 | (a < b) 为真。 |
| >= | 检查左操作数的值是否大于等于右操作数的值，如果是则条件为真。 | (a >= b) 不为真。 |
| <= | 检查左操作数的值是否小于等于右操作数的值，如果是则条件为真。 | (a <= b) 为真。 |
| !< | 检查左操作数的值是否不小于右操作数的值，如果是则条件为真。 | (a !< b) 为假。 |
| !> | 检查左操作数的值是否不大于右操作数的值，如果是则条件为真。 | (a !> b) 为真。 |
逻辑运算符
| 运算符 | 描述 |
|---|---|
| AND | AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。 |
| BETWEEN | BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。 |
| EXISTS | EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。 |
| IN | IN 运算符用于把某个值与一系列指定列表的值进行比较。 |
| NOT IN | IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。 |
| LIKE | LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。 |
| GLOB | GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较，GLOB与LIKE 不同之处在于，它是大小写敏感的。 |
| NOT | NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。 |
| OR | OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。 |
| IS NULL | NULL 运算符用于把某个值与 NULL 值进行比较。 |
| IS | IS 运算符与 = 相似。 |
| IS NOT | IS NOT 运算符与 != 相似。 |
| || | 连接两个不同的字符串，得到一个新的字符串。 |
| UNIQUE | UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。 |
位运算符
假设变量 A=60（00111100），变量 B=13（00001101）
| 运算符 | 描述 | 实例 |
|---|---|---|
| & | 如果同时存在于两个操作数中，二进制 AND 运算符复制一位到结果中。 | (A & B) 将得到 12，即为 0000 1100 |
| | | 如果存在于任一操作数中，二进制 OR 运算符复制一位到结果中。 | (A | B) 将得到 61，即为 0011 1101 |
| ~ | 二进制补码运算符是一元运算符，具有"翻转"位效应，即0变成1，1变成0。 | (~A ) 将得到 -61，即为 1100 0011，一个有符号二进制数的补码形式。 |
| << | 二进制左移运算符。左操作数的值向左移动右操作数指定的位数。 | A << 2 将得到 240，即为 1111 0000 |
| >> | 二进制右移运算符。左操作数的值向右移动右操作数指定的位数。 | A >> 2 将得到 15，即为 0000 1111 |
⑧ - 子句
WHERE子句
SQLite的 WHERE 子句用于指定从一个表或多个表中获取数据的条件，格式如下：
SELECT column1, column2, columnN
FROM table_name
WHERE [condition]
当满足给定的条件时，则从表中返回特定的值
如：当使用SELECT语句时，只有满足条件的行会被输出；当使用DELETE语句时，只有满足条件的行会被删除
sqlite> SELECT * FROM COMPANY;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 H1kki 20 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
sqlite> SELECT * FROM COMPANY WHERE ID=1 OR ID=2;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 H1kki 20 California 20000.0
2 Allen 25 Texas 15000.0
LIKE子句（使用了LIKE运算符的WHERE子句）
SQLite 的 LIKE 运算符是用来匹配通配符指定模式的文本值，与LIKE一起使用的通配符有两个：
%：代表0个，1个或多个字符_：代表1个字符
注意：LIKE运算符是大小写不敏感的
常用案例
| 语句 | 描述 |
|---|---|
| WHERE SALARY LIKE '200%' | 查找以 200 开头的任意值 |
| WHERE SALARY LIKE '%200%' | 查找任意位置包含 200 的任意值 |
| WHERE SALARY LIKE '_00%' | 查找第二位和第三位为 00 的任意值 |
| WHERE SALARY LIKE '2_%_%' | 查找以 2 开头，且长度至少为 3 个字符的任意值 |
| WHERE SALARY LIKE '%2' | 查找以 2 结尾的任意值 |
| WHERE SALARY LIKE '_2%3' | 查找第二位为 2，且以 3 结尾的任意值 |
| WHERE SALARY LIKE '2___3' | 查找长度为 5 位数，且以 2 开头以 3 结尾的任意值 |
sqlite> SELECT * FROM COMPANY;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 H1kki 20 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
sqlite> SELECT * FROM COMPANY WHERE SALARY LIKE '_0000.0';
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 H1kki 20 California 20000.0
3 Teddy 23 Norway 20000.0
7 James 24 Houston 10000.0
GLOB子句（使用了GLOB运算符的WHERE子句）
SQLite 的 GLOB 运算符相当于大小写敏感的 LIKE 运算符，与GLOB一起使用的通配符有两个：
*：代表0个，1个或多个字符?：代表1个字符
sqlite> SELECT * FROM COMPANY;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 H1kki 20 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
8 james 24 Houston 10000.0
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'J%';
ID NAME AGE ADDRESS SALARY
-- ----- --- ------- -------
7 James 24 Houston 10000.0
8 james 24 Houston 10000.0
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'J*';
ID NAME AGE ADDRESS SALARY
-- ----- --- ------- -------
7 James 24 Houston 10000.0
LIMIT子句 / OFFSET子句
SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量，格式如下：
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法：
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]
LIMIT用于限制输出的数量，而OFFEST用于指定偏移量，即初始位置，如果OFFSET为1，则从第2条数据开始输出，OFFEST只能与LIMIT搭配使用，不能单独使用
sqlite> SELECT * FROM COMPANY;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 H1kki 20 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
8 james 24 Houston 10000.0
sqlite> SELECT * FROM COMPANY LIMIT 3;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 H1kki 20 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 1;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
⑨ - 排序与分组
ORDER BY
SQLite 的 ORDER BY 子句是用来基于一个或多个列按升序或降序顺序排列数据
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
ASC表示升序，DESC表示降序
sqlite> SELECT * FROM COMPANY ORDER BY age;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 H1kki 20 California 20000.0
6 Kim 22 South-Hall 45000.0
3 Teddy 23 Norway 20000.0
7 James 24 Houston 10000.0
8 james 24 Houston 10000.0
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
sqlite> SELECT * FROM COMPANY ORDER BY salary DESC;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
5 David 27 Texas 85000.0
4 Mark 25 Rich-Mond 65000.0
6 Kim 22 South-Hall 45000.0
1 H1kki 20 California 20000.0
3 Teddy 23 Norway 20000.0
2 Allen 25 Texas 15000.0
7 James 24 Houston 10000.0
8 james 24 Houston 10000.0
GROUP BY
SQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组，即将相同的数据分到一组，常用来与统计函数结合使用得到统计数据
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
sqlite> SELECT count(ID), AGE FROM COMPANY GROUP BY AGE;
count(ID) AGE
--------- ---
1 20
1 22
1 23
2 24
2 25
1 27
年龄为24岁的和25岁的分别由两人
Having子句
HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件
下面列出HAVING子句在SELECT查询语句中的位置
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
在一个查询中，HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。
sqlite> SELECT count(ID), AGE FROM COMPANY GROUP BY AGE;
count(ID) AGE
--------- ---
1 20
1 22
1 23
2 24
2 25
1 27
sqlite> SELECT count(ID), AGE FROM COMPANY GROUP BY AGE HAVING count(ID) > 1;
count(ID) AGE
--------- ---
2 24
2 25
⑩ - SQLite常用函数
| 序号 | 函数 & 描述 |
|---|---|
| 1 | SQLite COUNT 函数 SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。 |
| 2 | SQLite MAX 函数 SQLite MAX 聚合函数允许我们选择某列的最大值。 |
| 3 | SQLite MIN 函数 SQLite MIN 聚合函数允许我们选择某列的最小值。 |
| 4 | SQLite AVG 函数 SQLite AVG 聚合函数计算某列的平均值。 |
| 5 | SQLite SUM 函数 SQLite SUM 聚合函数允许为一个数值列计算总和。 |
| 6 | SQLite RANDOM 函数 SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。 |
| 7 | SQLite ABS 函数 SQLite ABS 函数返回数值参数的绝对值。 |
| 8 | SQLite UPPER 函数 SQLite UPPER 函数把字符串转换为大写字母。 |
| 9 | SQLite LOWER 函数 SQLite LOWER 函数把字符串转换为小写字母。 |
| 10 | SQLite LENGTH 函数 SQLite LENGTH 函数返回字符串的长度。 |
| 11 | SQLite sqlite_version 函数 SQLite sqlite_version 函数返回 SQLite 库的版本。 |
⑪ - SQLite索引
索引（Index）是一种特殊的查找表，数据库搜索引擎用来加快数据检索。简单地说，索引是一个指向表中数据的指针
原理
参考：sqlite索引的原理 - 花老🐯 - 博客园 (cnblogs.com)
数据库有两列Col1、Col2。在存储时，按照列Col1组织各行，比如Col1已二叉树方式组织。如果查找col1中的某一个值，利用二叉树进行二分查找，不需要遍历整个数据库。而这样一来列Col2就是乱序的。为了解决这个问题，为Col2建立了索引，即把Col2也按照某种数据结构（这里是二叉树）组织起来。这样子查找列Col2时只需要进行二分查找即可。
创建索引
-
单列索引：
CREATE INDEX index_name ON table_name (column_name);单列索引是一个只基于表的一个列上创建的索引
-
唯一索引：
CREATE UNIQUE INDEX index_name on table_name (column_name);使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中
-
组合索引：
CREATE INDEX index_name on table_name (column1, column2);组合索引是基于一个表的两个或多个列上创建的索引
-
隐式索引：隐式索引是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束
删除索引
语法：DROP INDEX index_name;
使用索引进行查询
SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);
0x04 SQLite高级教程
说白了就是为SQL注入实战做准备的基础知识，并不是一些数据库操作的高级技巧
① - UNION
SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行
为了使用 UNION，每个 SELECT 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但它们不必具有相同的长度
为了方便演示，我们先介绍一个小知识，就是如果执行SELECT 1,2,3,4,5，它将创建一个如下的表
sqlite> SELECT 1,2,3,4,5;
1 2 3 4 5
- - - - -
1 2 3 4 5
接下来介绍UNION子句，我对它的理解如下，A表 UNION B表，它们构成的虚拟表，表头是A表的表头，数据为A表的数据后跟随B表的数据，我们直接演示一遍来说明
sqlite> SELECT 1,2,3,4,5;
1 2 3 4 5
- - - - -
1 2 3 4 5
sqlite> SELECT 6,7,8,9,10;
6 7 8 9 10
- - - - --
6 7 8 9 10
sqlite> SELECT 1,2,3,4,5 UNION SELECT 6,7,8,9,10;
1 2 3 4 5
- - - - --
1 2 3 4 5
6 7 8 9 10
sqlite> SELECT 1,2,3,4,5 WHERE 1=2 UNION SELECT 6,7,8,9,10;
1 2 3 4 5
- - - - --
6 7 8 9 10
② - ORDER BY
ORDER BY子句后可以接数字代表排序的列的位置，如
sqlite> SELECT * FROM COMPANY ORDER BY 3;
ID NAME AGE ADDRESS SALARY
-- ----- --- ---------- -------
1 H1kki 20 California 20000.0
6 Kim 22 South-Hall 45000.0
3 Teddy 23 Norway 20000.0
7 James 24 Houston 10000.0
8 james 24 Houston 10000.0
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
即，根据第三个字段，也就是AGE字段进行排序，但是如果超出了表的列数，则会报错
sqlite> SELECT * FROM COMPANY ORDER BY 6;
Error: 1st ORDER BY term out of range - should be between 1 and 5
③ - sqlite_master表
sqlite> .schema sqlite_master
CREATE TABLE sqlite_master (
type text,
name text,
tbl_name text,
rootpage integer,
sql text
);
这是一张数据库的伴生表，该表会自动创建，是用来存储数据库的元信息的，如：表(table), 索引(index), 视图(view), 触发器(trigger)
字段说明
| 字段 | 说明 |
|---|---|
| type | 记录项目的类型，如table、index、view、trigger |
| name | 记录项目的名称，如表名、索引名等 |
| tbl_name | 记录所从属的表名，如索引所在的表名。对于表来说，该列就是表名本身 |
| rootpage | 记录项目在数据库页中存储的编号。对于视图和触发器，该列值为0或者NULL |
| sql | 记录创建该项目的SQL语句 |
作用
通过sql字段可以查询所有表的创建语句，进而得到所有表名和其字段名
sqlite> select group_concat(sql) from sqlite_master;
group_concat(sql)
------------------------------------------------------------------------------------
CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
),CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
)
④ - 扩展函数
介绍一些攻击常用到的函数
group_concat(column_name)：将这个字段的所有数据拼接到一个数据中，使用,分隔sqlite_version()：存储sqlite的版本信息length()：输出数据长度substr(data, start, length)：截取字符串（start最小值为1）randomblob(1000000000)：用来代替sleep()函数
⑤ - 写shell的原理
前提
必须知道WEB路径和拥有写入权限
原理
-
在附加数据库的时候，如果数据库文件不存在，则会创建数据库文件，且新建文件后缀和位置可控
-
数据库存储的数据会以明文的方式保存在文件中
步骤
第一步：附加数据库，指定保存数据库的文件和后缀
我们已知网站绝对路径为D:\Server\phpstudy\PHPTutorial\WWW
则执行语句：ATTACH DATABASE 'D:\\Server\\phpstudy\\PHPTutorial\\WWW\\sqliteShell.php' AS test ;
第二步：创建一个在附加数据库中的表格
执行语句：create TABLE test.exp (shell text) ;
第三步：在表格中插入需要远程执行的代码，这里就以<?php phpinfo();?>为例
执行语句：insert INTO test.exp VALUES ('<?php phpinfo();?>');
0x05 与PHP联动
实现了一个登录框的功能
<?php
# 命名空间
namespace core;
# 引入系统类: 基于PDO实现，需要引入三个类
use \PDO, \PDOStatement, \PDOException;
class MyPDO
{
# 属性
private $pdo;
private $fetch_mode;
public $error;
# 构造方法
public function __construct()
{
# fetch_mode不能在初始化的时候实现，需要在得到PDOStatement类对象后设置
$this->fetch_mode = isset($drivers[PDO::ATTR_DEFAULT_FETCH_MODE]) ? $drivers[PDO::ATTR_DEFAULT_FETCH_MODE] : PDO::FETCH_ASSOC;
# 连接认证
try {
$this->pdo = @new PDO('sqlite:test.db');
} catch (PDOException $e) {
$this->my_exception($e);
echo "数据库连接失败! ";
exit(0);
}
}
# 保存错误信息
private function my_exception(PDOException $e)
{
# 属性记录错误
$this->error['file'] = $e->getFile();
$this->error['line'] = $e->getLine();
$this->error['error'] = $e->getMessage();
}
# 新增用户
public function add_user($username, $password)
{
# 加载
$pre_sql = "insert into user values(:user, :pass, 'user');";
$stmt = $this->pdo->prepare($pre_sql);
if (!$stmt) {
die('预处理指令执行失败！');
}
# 绑定
$stmt->bindValue(':user', $username);
$stmt->bindValue(':pass', $password);
# 执行
return $stmt->execute();
}
# 登录判定
public function is_login($username, $password)
{
# 加载
$pre_sql = "select * from user where username=:user and password=:pass limit 1";
$stmt = $this->pdo->prepare($pre_sql);
if (!$stmt) {
die('预处理指令执行失败！');
}
# 绑定
$stmt->bindValue(':user', $username);
$stmt->bindValue(':pass', $password);
# 执行
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row['username'] === $username) {
return $row['status'];
} else {
return null;
}
}
}
0x06 实战打靶
懒得搭建环境了，直接做墨者靶场：SQL手工注入漏洞测试(SQLite数据库)_SQL注入
测试漏洞点
测试回显位
爆表名和字段名
查询数据
解密出数据，前台登录试试
登录成功，拿到key
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· 单线程的Redis速度为什么快？
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 展开说说关于C#中ORM框架的用法！