SQLite 数据库介绍

SQLite 是一个开源的、内嵌式的关系型数据库，第一版诞生于2000年5月
SQLite不需要单独的服务器进程或系统来运行（无服务器）

  • SQLite 特性
    • 零配置
    • 可移植
    • 紧凑
    • 简单
    • 灵活
    • 可靠
    • 易用

SQLite 数据库类型

​ 一般数据采用固定的静态数据类型，而SQLite采用的是动态数据类型，会根据存入值自动判断。
SQLite 具有以下5种基本数据类型：

  • integer：带符号的整形(最多64位),根据值的大小存储为1、2、3、4、6或8字节
  • real：8字节表示的浮点类型
  • text：字符类型，支持多种编码(如 UTF-8、UTF-16)，大小无限制
  • blob：任意类型的数据，无大小限制。 BLOB 二进制大对象，使用二进制保存数据
  • null：表示空值

SQLite没有单独的布尔值存储类。相反，布尔值以整数0（假）和1（真）的形式存储。

SQLite没有单独的存储日期和/或时间的存储类，但SQLite可以将日期和时间存储为TEXT、REAL或INTEGER值

序号 存储类和日期格式
1 TEXT 日期格式为”YYYY-MM-DD HH:MM:SS.SSS”
2 REAL 自公元前4714年11月24日格林威治时间中午以来的天数
3 INTEGER 自1970-01-01 00:00:00 UTC以来的秒数

SQLite存储类比数据类型略为广泛。例如，INTEGER存储类包括6种不同长度的整数数据类型

SQLite 注释

  • sqlite使用 -- 来表示注释，延伸到包括下一个换行符结束

SQLite 命令

命令不需要加 ;

  • 使用.sqlite3 进入sqlite3数据库

    1
    2
    3
    4
    5
    6
    $ sqlite3
    SQLite version 3.28.0 2019-04-16 19:49:53
    Enter ".help" for usage hints.
    Connected to a transient in-memory database.
    Use ".open FILENAME" to reopen on a persistent database.
    sqlite>

  • 使用.help查看命令

    1
    sqlite>.help

  • .show命令，来查看 SQLite 命令提示符的默认设置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    sqlite>.show -- 显示当前设置选项  
         echo: off -- 回显: 关闭  
      explain: off -- 解释: 关闭  
      headers: off -- 表头: 关闭  
         mode: column -- 输出模式: 列模式  
    nullvalue: "" -- 空值替代字符: 空字符串  
       output: stdout -- 输出至: 标准输出  
    separator: "|" -- 分隔符: 竖线("|")  
        width: -- 显示宽度: 未设置

  • 格式化输出

    1
    2
    3
    4
    qlite>.header on -- 开启表头  
    sqlite>.mode column -- 切换到列模式  
    sqlite>.timer on -- 开启计时器 
    sqlite>.width 10, 20, 10 -- 设置输出列宽度

    之后输出就会带有相关信息

SQLite 语句

所有的SQLite语句都以关键字SELECT、INSERT、UPDATE、DELETE、ALTER、DROP等开头，并且所有语句都以分号（;）结尾。

  • SELECT：用于从数据库中检索数据。可以指定要检索的列、表、条件等信息。示例：

    1
    SELECT column1, column2 FROM table_name WHERE condition;

  • INSERT：用于向数据库表中插入新数据。可以指定要插入的值和插入的表格。示例：

    1
    INSERT INTO table_name (column1, column2) VALUES (value1, value2);

  • UPDATE：用于更新数据库表中已有的数据。可以指定要更新的列、新值以及更新的条件。示例：

    1
    UPDATE table_name SET column1 = value1 WHERE condition;

  • DELETE：用于从数据库表中删除数据。可以指定要删除的行以及删除的条件。示例：

    1
    DELETE FROM table_name WHERE condition;

  • ALTER：用于修改数据库表结构，如增加、修改或删除列。示例：

    1
    ALTER TABLE table_name ADD column_name datatype;

  • DROP：用于删除数据库表或索引。示例：

    1
    DROP TABLE table_name;
创建数据库

sqlite3 命令用于创建新的SQLite数据库。您不需要特殊权限来创建数据库。
在关系型数据库管理系统中，数据库名称应该是唯一的。

1
2
3
$sqlite3 DatabaseName.db
# 如果你想创建一个名为<testDB.db>的新数据库，那么SQLITE3语句应该是如下所示
$sqlite3 testDB.db

创建数据库后，你可以使用以下SQLite命令 .databases 在数据库列表中验证它

1
2
3
sqlite>.databases
main: C:\Users\发发发宝贝\Downloads\sqlite-tools-win-x64-3480000\app.db r/w 
# 在给定的输出中，显示了连接到名为"main"的数据库文件，路径为"C:\Users\发发发宝贝\Downloads\sqlite-tools-win-x64-3480000\app.db"，并且具有读写权限。

也可以使用语句查询数据库信息

1
2
3
4
SELECT * FROM pragma_database_list;
seq  name  file
---  ----  -----------------------------------------------------
0    main   C:\Users\发发发宝贝\Downloads\sqlite-tools-win-x64-			   3480000\app.db
退出控制台

.quit 命令以及以下方式退出SQLite提示符

1
sqlite>.quit
导出数据库数据

.dump命令在命令提示符下使用以下SQLite命令将完整数据库导出到文本文件中。

1
2
$sqlite3 testDB.db .dump > testDB.sql
# 以上命令将把 testDB.db 数据库中的所有内容转换为SQLite语句，并将其倾倒到ASCII文本文件 testDB.sql 中。
导入数据库
1
$sqlite3 testDB.db < testDB.sql
选择特定数据库

如果有多个可用的数据库，并且你想一次只使用其中任意一个。可以用ATTACH DATABASE 语句用于选择特定的数据库

1
2
sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';
-- 如果该数据库没创建，这个命令会创建数据库，否则只是将数据库文件名与逻辑数据库别名关联

不能使用存在别名，不然会报错

1
2
sqlite> ATTACH DATABASE 'testDB.db' as 'main';
Error: database TEMP is already in use
分离数据库

在SQLite中，DETACH DATABASE是一个用于分离数据库（Detach Database）的SQL命令，该命令允许您将一个数据库从当前连接中分离，使得可以在不关闭数据库连接的情况下操作其他数据库。

1
DETACH DATABASE 'database_name';

在执行DETACH命令后，被分离的数据库将从当前连接中移除，但数据库文件本身并没有被删除，只是与当前连接分离

示例：

1
2
3
4
5
6
-- 连接到两个数据库：main和test  
ATTACH DATABASE '/path/to/first_database.db' AS main;  
ATTACH DATABASE '/path/to/second_database.db' AS test;  

-- 分离名为'test'的数据库  
DETACH DATABASE 'test';
创建表

可以使用CREATE TABLE语句来创建表格。下面是一个简单的示例,名为”users”的表格，包含id、name和age三个列

1
2
3
4
5
6
7
8
9
10
11
12
-- 连接第一个数据库，并指定别名为main  
ATTACH DATABASE '/path/to/first_database.db' AS main;  

-- 连接第二个数据库，并指定别名为test  
ATTACH DATABASE '/path/to/second_database.db' AS test; 

-- 在test数据库下创建users表
CREATE TABLE test.users (  
    id INTEGER PRIMARY KEY,  
    name TEXT,  
    age INTEGER  
);

在上面的CREATE TABLE语句中：

  • users是表格的名称。
  • id是INTEGER类型的列，设置为PRIMARY KEY，表示主键。
  • name是TEXT类型的列，存储用户的名称。
  • age是INTEGER类型的列，存储用户的年龄。

.tables 验证您的表是否成功创建，该命令将列出附加数据库中的所有表

1
2
sqlite> .tables
test.users
删除表

DROP TABLE 删除表

1
DROP TABLE test.users;
向数据库表中添加新的数据行

INSERT INTO语句的两种基本语法

  • 给指定字段添加指定值

    1
    2
    INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
    VALUES (value1, value2, value3,...valueN);

  • 给所有字段添加值

    1
    INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

使用另一张表格的内容查询数据填充到表格数据内，前提是另一个表格具有一组必要的字段，这些字段用于填充第一个表格

1
2
3
4
INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];
查询表

SELECT语句的基本语法

1
2
SELECT column1, column2, columnN FROM table_name;
SELECT * FROM table_name;
条件查询 - where
1
2
3
SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]
多条件查询 -where … and & or
1
2
3
SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];
1
2
3
SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]
修改表数据
1
2
3
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

删除表数据

1
2
DELETE FROM table_name
WHERE [condition];
模糊查询
  • 百分号（%）代表零个、一个或多个数字或字符。
  • 下划线（_）代表一个数字或字符。这些符号可以组合使用。
序号 语句和描述
1 WHERE SALARY LIKE ‘200%’ 查找任何以200开头的值
2 WHERE SALARY LIKE ‘%200%’ 查找任何位置包含200的值
3 WHERE SALARY LIKE ‘_00%’ 查找以00为第二和第三个位置的值
4 WHERE SALARY LIKE ‘2_%_%’ 查找以2开头且至少为3个字符长度的值
5 WHERE SALARY LIKE ‘%2’ 查找以2结尾的值
6 WHERE SALARY LIKE ‘_2%3’ 查找以第二个位置为2且以3结尾的值
7 WHERE SALARY LIKE ‘2___3’ 查找以2开头以3结尾的五位数值 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or 
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'
模糊查询 - GLOB子句

GLOB 运算符用于使用通配符匹配模式，仅针对文本值进行匹配。如果搜索表达式可以与模式表达式匹配，GLOB运算符将返回true，即1。与LIKE运算符不同，GLOB区分大小写，并且遵循UNIX的语法来指定以下通配符。

  • 星号（*）
  • 问号（?）

星号（*）表示零个或多个数字或字符。问号（?）表示一个数字或字符。

序号 语句和描述
1 WHERE SALARY GLOB ‘200*’ 查找以200开头的任何值
2 WHERE SALARY GLOB ‘*200*‘ 查找任何位置包含200的值
3 WHERE SALARY GLOB ‘?00*’ 查找第二和第三个位置包含00的任何值
4 WHERE SALARY GLOB ‘2??’ 查找以2开头且至少为3个字符长的任何值
5 WHERE SALARY GLOB ‘*2’ 查找以2结尾的任何值
6 WHERE SALARY GLOB ‘?2*3’ 查找第二个位置为2且以3结尾的任何值
7 WHERE SALARY GLOB ‘2???3’ 查找以2开头且以3结尾的五位数字中的任何值 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT FROM table_name
WHERE column GLOB 'XXXX*'
or 
SELECT FROM table_name
WHERE column GLOB '*XXXX*'
or
SELECT FROM table_name
WHERE column GLOB 'XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '????'
限制返回数据量 - limit

LIMIT 子句用于限制SELECT语句返回的数据量

1
2
3
SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]

当LIMIT子句与OFFSET子句一起使用时的语法如下：

1
2
3
SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]
升序或降序排序

ORDER BY 子句用于根据一个或多个列对数据进行升序或降序排序。

1
2
3
4
SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC]; -- 默认ASC 降序
数据分组排列

GROUP BY 语句与SELECT语句合作，将相同的数据分组排列。
GROUP BY语句在SELECT语句的WHERE语句之后，在ORDER BY语句之前

1
2
3
4
5
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
HAVING 子句

HAVING 子句允许您指定筛选条件，以过滤出最终结果中显示的分组结果。WHERE 子句用于选定列上的条件，而 HAVING 子句用于由 GROUP BY 子句创建的分组上的条件。

在查询中，HAVING子句必须跟在GROUP BY子句之后，并且如果使用了ORDER BY子句，HAVING子句也必须位于其之前。下面是SELECT语句的语法，包括HAVING子句。

1
2
3
4
5
6
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

案例：显示名称计数少于2的记录

1
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;
去重 - DISTINCT关键字

DISTINCT 关键字与SELECT语句一起使用，用于消除所有重复的记录，只提取唯一的记录。

1
2
3
SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]
PRAGMA

PRAGMA 命令是用于控制SQLite环境中的各种环境变量和状态标志的特殊命令。可以读取PRAGMA值，并根据需要进行设置

  • 查询当前PRAGMA值

    1
    PRAGMA pragma_name;

  • 设置PRAGMA 新值

    1
    PRAGMA pragma_name = value;

自动清理 - auto_vacuum Pragma

auto_vacuum pragma获取或设置自动清理模式。

1
2
PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = 1;
序号 Pragma值和描述
1 0或NONE 自动清理被禁用。这是默认模式，这意味着除非使用VACUUM命令手动清理数据库文件，否则其大小将不会缩小。
2 1或FULL 自动清理已启用且完全自动化，允许数据库文件随着从数据库中删除的数据而缩小。
3 2或INCREMENTAL 自动清理已启用，但必须手动激活。在此模式下，保留引用数据，但空闲页只是放在空闲列表中。可以随时使用 incremental_vacuum pragma 恢复这些页面。

cache_size Pragma

cache_size 保留词可以获取或临时设置内存页面缓存的最大大小

1
2
3
PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;
-- 页面数（ pages ）的值表示缓存中的页面数。内置页面缓存的默认大小为2,000页，最小大小为10页

case_sensitive_like Pragma

case_sensitive_like Pragma控制内置LIKE表达式的大小写敏感性。默认情况下，此Pragma的值为false，这意味着内置的LIKE运算符忽略字母的大小写

1
PRAGMA case_sensitive_like = [true|false];

count_changes Pragma

count_changes pragma用于获取或设置数据操作语句（如INSERT、UPDATE和DELETE）的返回值。

1
2
PRAGMA count_changes;
PRAGMA count_changes = [true|false];
sqlite 约束

以下是SQLite中常用的约束：

  • NOT NULL约束 - 确保一列不能有NULL值。

    1
    2
    3
    CREATE TABLE COMPANY(
       ID INT NOT NULL,
    );

  • DEFAULT约束 - 在未指定时为列提供默认值。

    1
    2
    3
    CREATE TABLE COMPANY(
       SALARY  REAL  DEFAULT 50000.00
    );

  • UNIQUE约束 - 确保列中的所有值都不同。

    1
    2
    3
    CREATE TABLE COMPANY(
       AGE  INT  NOT NULL UNIQUE,  -- 值唯一
    );

  • PRIMARY Key - 在数据库表中唯一标识每一行/记录。

    1
    2
    3
    CREATE TABLE COMPANY(
       ID INT PRIMARY KEY,
    );

  • CHECK约束 - 确保列中的所有值满足一定条件。

    1
    2
    3
    CREATE TABLE COMPANY(
       SALARY  REAL  CHECK(SALARY > 0)
    );

  • 在 SQLite 中，要使用外键约束，需要确保以下条件

    1
    PRAGMA foreign_keys = ON;

    表需要使用 CREATE TABLE 语句来创建，并且在定义列时使用 FOREIGN KEY 子句来指定外键约束。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    PRAGMA foreign_keys = ON;  

    CREATE TABLE department (  
        dept_id INTEGER PRIMARY KEY,  
        name TEXT  
    );  

    CREATE TABLE employee (  
        emp_id INTEGER PRIMARY KEY,  
        name TEXT,  
        dept_id INTEGER,  
        FOREIGN KEY (dept_id) REFERENCES department(dept_id)  
    );
修改表结构，表约束
添加列
1
ALTER TABLE students ADD COLUMN age INTEGER;
更改列
1
ALTER TABLE students ALTER COLUMN age TEXT;
删除列
1
ALTER TABLE students DROP COLUMN age;
改表名
1
ALTER TABLE students RENAME TO new_students;
删除表约束

在 SQLite 中，可以通过 ALTER TABLE 语句来删除约束

假设有一个表 students，其中有一个约束名为 unique_email_constraint，该约束要求 email 列的值在表中是唯一的。要删除这个约束，可以使用以下 SQL 语句：

1
ALTER TABLE students DROP CONSTRAINT unique_email_constraint;
添加表约束

添加主键约束

1
ALTER TABLE students ADD PRIMARY KEY (student_id);

添加唯一约束

1
ALTER TABLE students ADD CONSTRAINT unique_email_constraint UNIQUE (email);
多表查询

SQL定义了三种主要类型的连接（Join）操作：

  • The CROSS JOIN（交叉连接）

    1
    2
    3
    SELECT *   
    FROM table1  
    CROSS JOIN table2;

  • The INNER JOIN（内连接）

    1
    2
    3
    SELECT table1.column1, table2.column2  
    FROM table1  
    INNER JOIN table2 ON table1.key = table2.key;

  • The OUTER JOIN（外连接）

    • 左外连接（LEFT OUTER JOIN）
    1
    2
    3
    SELECT table1.column1, table2.column2  
    FROM table1  
    LEFT OUTER JOIN table2 ON table1.key = table2.key;
    • 右外连接（RIGHT OUTER JOIN）
    1
    2
    3
    SELECT table1.column1, table2.column2  
    FROM table1  
    RIGHT OUTER JOIN table2 ON table1.key = table2.key;
    • 全外连接（FULL OUTER JOIN）
    1
    2
    3
    SELECT table1.column1, table2.column2  
    FROM table1  
    FULL OUTER JOIN table2 ON table1.key = table2.key;
UNION语句

在 SQLite 中，UNION 语句用于联合多个 SELECT 查询的结果集，将它们合并为一个结果集并去除重复的行。UNION 操作要求每个 SELECT 查询都具有相同数量和类型的列，结果集会按照第一个 SELECT 查询返回的列顺序组合。

1
2
3
4
5
SELECT column1, column2  
FROM table1  
UNION  
SELECT column1, column2  
FROM table2;
1
2
3
4
5
SELECT name, age  
FROM students  
UNION  
SELECT name, age  
FROM teachers;
SQLite 触发器

在 SQLite 中，触发器（Trigger）是在某个特定事件发生时自动执行的一系列 SQL 语句。可以使用触发器来实现数据完整性约束、数据自动更新、日志记录等功能。SQLite支持BEFORE和AFTER两种类型的触发器，在执行触发器之前或之后触发。

1
2
3
4
5
CREATE TRIGGER trigger_name   
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name  
BEGIN  
    -- 触发时执行的 SQL 语句  
END;
1
2
3
4
5
6
7
CREATE TRIGGER update_info_trigger   
AFTER INSERT ON students  
BEGIN  
    UPDATE teachers   
    SET last_inserted_id = NEW.id   
    WHERE teacher_id = NEW.teacher_id;  
END;
SQLite 索引

SQLite索引是一种数据结构，用于在SQLite数据库表中加快数据的检索速度。索引可以提高数据检索的速度，特别是在大型数据库表中。SQLite支持多种类型的索引，包括B-tree索引、Hash索引和Full-text索引等。

  • CREATE INDEX

CREATE INDEX命令是用于在SQLite数据库表中创建索引的SQL语句。索引的作用是提高数据检索的速度，特别是在大型数据表中。在SQLite中，索引可以在一个或多个列上创建。

1
2
3
CREATE INDEX index_name ON table_name (column1, column2, ...);
-- 如果要在一个名为"users"的表上创建一个名为"name_index"的索引，索引列为"name"，则可以使用以下语句：
CREATE INDEX name_index ON users (name);
  • index_name：索引的名称
  • table_name：要创建索引的表名
  • column1, column2, …：要在哪些列上创建索引