1 简介

SQLite是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。

SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库。

SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件。

1.1 sqlite 的优势

• 不需要一个单独的服务器进程或操作的系统（无服务器的）。
• SQLite 不需要配置，这意味着不需要安装或管理。
• 一个完整的 SQLite 数据库是存储在一个单一的跨平台的磁盘文件。
• SQLite 是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。
• SQLite 是自给自足的，这意味着不需要任何外部的依赖。
• SQLite 事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。
• SQLite 支持 SQL92（SQL2）标准的大多数查询语言的功能。
• SQLite 使用 ANSI-C 编写的，并提供了简单和易于使用的 API。
• SQLite 可在 UNIX（Linux, Mac OS-X, Android, iOS）和 Windows（Win32, WinCE, WinRT）中运行。

2 安装

sudo apt-get install sqlite3 libsqlite-dev -y

3 命令

• .exit 退出 sqlite3 提示符。
• .help 帮助。
• databases 列出数据库名称及其所依附的文件。
• .tables 查看所有表。
• read FILENAME 执行 FILENAME 文件中的 SQL。
• .show() 显示各种设置的当前值。
• .header on|off 打开/关闭头部显示。
• .dump >FILENAME.sql 导出数据库到 FILENAME.sql 中。
• test.db < FILENAME.sql 恢复。
• .schema tablename 查看表的完整信息。
• .mode column

sqlite3 xx.db < xx.sql

4 语句

以关键字开始，以 ; 结束。

5 数据类型

QLite采用的是动态数据类型，会根据存入值自动判断。

存储类	描述
NULL	NULL 值
INTEGER	带符号整数
REAL	浮点值 ，8字节
TEXT	文件字符串，UTF 编码存储
BLOB	blob 数据，根据输入存储

blob(binary large object) 是一个可以存储二进制文件的容器，主要用于存储二进制大对象，例如可以存储图片，音视频等文件。按照可存储容量大小不同来分类

数据类型	亲和类型
INT	INTEGER
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8
CHARACTER(20)	TEXT
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB
BLOB	BLOB
未指定类型
REAL	REAL
DOUBLE
DOUBLE PRECISION
FLOAT
NUMERIC	NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME

5.1 BOOLEAN

SQLite 没有单独的 Boolean 存储类。相反，布尔值被存储为整数 0（false）和 1（true）。

5.2 DATE 与 TIME

SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。

5.3 strict table

• 每个字段都必须指定类型
• 字段的类型只能是: INT, INTEGER, REAL, TEXT, BLOB, ANY 之一，其他类型皆不允许。
• 除了 ANY 类型之外，其他类型的字段只能存储指定类型的内容或者 NULL（假设该字段没有 NOT NULL 约束）。
• 组成 PRIMARY KEY 的字段默认会加上 NOT NULL 约束。
• ANY 类型的字段可以存储任何类型的数据（如果存储在 NOT NULL 约束，自然不能存储 NULL）。

CHAR 类型用于存储固定长度的字符串，例如 CHAR(10) 只能存储长度为 10 的字符串，如果存储的字符串长度不足 10 位，则 SQLite 将在字符串后面自动添加空格来填充。这种自动填充可能会导致一些问题，例如在比较字符串时可能需要注意。

TEXT 类型则用于存储可变长度的字符串，可以存储任意长度的字符串。在 SQLite 中，TEXT 类型的最大存储容量为 2³¹-1 字节（或约 2 GB）。

在大多数情况下，推荐使用 TEXT 类型，因为它具有更大的灵活性和可扩展性。只有在您确切知道存储数据的长度时才应使用 CHAR 类型。

需要注意的是，在 SQLite 中，不管是 CHAR 还是 TEXT 类型，它们都被视为字符串类型，因此不支持存储二进制数据。如果需要存储二进制数据，可以使用 BLOB 类型。

在选择使用 TEXT 还是 VARCHAR 类型时，应根据您的具体需求和数据存储需求来决定。如果您需要存储较长的文本数据，则可能需要使用 TEXT 类型；如果您需要存储较短的字符串，则可以使用 VARCHAR 类型。

6 语法

6.1 创建数据库

不存在则创建，存在则直接打开。

sqlite3 databasename.db
# .open 打开
sqlite>.open test.db

6.2 创建表

CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

• CREATE TABLE 语句后跟着表的唯一的名称或标识
• database_name.table_name：指定带有table_name的database_name
• column1_name: 字段名，或者键名
• datatype：字段类型
• PRIMARYKEY：主键，其值能唯一地标识表中的每一条记录

例子：

创建了一个 COMPANY 表，ID 作为主键，NOT NULL 的约束表示在表中创建纪录时这些字段不能为 NULL：

CREATE TABLE IF NOT EXISTS COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

联合主键

SQLite 支持使用联合主键，可以通过以下步骤来实现：

1. 创建一个带有多个列的表，其中需要定义联合主键列。

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    PRIMARY KEY (column1, column2)
);

2. 插入数据时，需要同时提供联合主键列的值。

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

3. 更新数据时，也需要同时提供联合主键列的值。

UPDATE table_name SET column3 = new_value WHERE column1 = value1 AND column2 = value2;

4. 查询数据时，可以使用联合主键列进行筛选。

SELECT * FROM table_name WHERE column1 = value1 AND column2 = value2;

注意事项：

• 联合主键列的值必须唯一，否则会出现冲突。
• 联合主键列的顺序很重要，不同顺序会产生不同的结果。
• 如果某一列的值为 NULL，那么该行数据将不会被包含在联合主键中。

自增

SQLite中可以使用AUTOINCREMENT或者INTEGER PRIMARY KEY来实现自增功能。

1. AUTOINCREMENT

AUTOINCREMENT用于创建一个自增的列。当插入新的数据时，SQLite会自动为该列赋予一个唯一的自增值。

例如，创建一个表格，其中id列为自增列：

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    age INTEGER
);

向表格中插入数据时，不需要指定id列的值，SQLite会自动为其分配一个唯一的自增值：

INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);

查询数据时，可以看到id列已经自动递增：

SELECT * FROM users;

id  name    age
1   Alice   25
2   Bob     30

2. INTEGER PRIMARY KEY

INTEGER PRIMARY KEY也可以实现自增功能。在创建表格时，将id列定义为INTEGER PRIMARY KEY类型，SQLite会自动为其分配一个自增值。

例如，创建一个表格，其中id列为自增列：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
);

向表格中插入数据时，不需要指定id列的值，SQLite会自动为其分配一个唯一的自增值：

INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);

查询数据时，可以看到id列已经自动递增：

SELECT * FROM users;

id  name    age
1   Alice   25
2   Bob     30

6.3 删除 表

DROP TABLE database_name.table_name;

6.4 插入记录

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

实例：

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

6.5 查询

SELECT column1, column2, columnN FROM table_name;
-- 查询所有字段
SELECT * FROM table_name;

列出所有在数据库中创建的表：

SELECT tbl_name FROM sqlite_master WHERE type = 'table';
-- 列出关于 COMPANY 表的完整信息
SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';

结果如下：

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
)

6.6 运算符

运算符是一个保留字或字符，主要用于 SQLite 语句的 WHERE 子句中执行操作，如比较和算术运算。

运算符用于指定 SQLite 语句中的条件，并在语句中连接多个条件。

1. 算数运算符

运算符	描述	实例
+	加法 - 把运算符两边的值相加	a + b 将得到 30
-	减法 - 左操作数减去右操作数	a - b 将得到 -10
*	乘法 - 把运算符两边的值相乘	a * b 将得到 200
/	除法 - 左操作数除以右操作数	b / a 将得到 2
%	取模 - 左操作数除以右操作数后得到的余数	b % a will give 0

select 10 + 20
-- 结果 10 + 20 = 30

2. 比较运算符

运算符	描述	实例
==	检查两个操作数的值是否相等，如果相等则条件为真。	(a == b) 不为真。
=	检查两个操作数的值是否相等，如果相等则条件为真。	(a = b) 不为真。
!=	检查两个操作数的值是否相等，如果不相等则条件为真。	(a != b) 为真。
<>	检查两个操作数的值是否相等，如果不相等则条件为真。	(a <> b) 为真。
>	检查左操作数的值是否大于右操作数的值，如果是则条件为真。	(a > b) 不为真。
<	检查左操作数的值是否小于右操作数的值，如果是则条件为真。	(a < b) 为真。
>=	检查左操作数的值是否大于等于右操作数的值，如果是则条件为真。	(a >= b) 不为真。
<=	检查左操作数的值是否小于等于右操作数的值，如果是则条件为真。	(a <= b) 为真。
!<	检查左操作数的值是否不小于右操作数的值，如果是则条件为真。	(a !< b) 为假。
!>	检查左操作数的值是否不大于右操作数的值，如果是则条件为真。	(a !> b) 为真。

示例：

SELECT * FROM COMPANY WHERE SALARY >= 65000;

3. 逻辑运算符

运算符	描述
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 运算符搜索指定表中的每一行，确保唯一性（无重复）。

实例：

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
-- 子查询
SELECT AGE FROM COMPANY
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
SELECT * FROM COMPANY
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

4. 位运算符

实例：

sqlite> .mode line
sqlite> select 60 | 13;
60 | 13 = 61

sqlite> select 60 & 13;
60 & 13 = 12

sqlite>  select  (~60);
(~60) = -61

sqlite>  select  (60 << 2);
(60 << 2) = 240

sqlite>  select  (60 >> 2);
(60 >> 2) = 15

6.7 表达式

1. 布尔表达式

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHING EXPRESSION;

例：

SELECT * FROM COMPANY WHERE SALARY = 10000;

2. 数值表达式

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

例：

SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
SELECT (15 + 6) AS ADDITION

3. 日期表达式

SELECT CURRENT_TIMESTAMP;

6.8 where 字句

WHERE 子句用于指定从一个表或多个表中获取数据的条件。

SELECT column1, column2, columnN
FROM table_name
WHERE [condition]

例子：

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

6.9 AND/OR 连接运算符

AND 和 OR 运算符用于编译多个条件来缩小在 SQLite 语句中所选的数据。这两个运算符被称为连接运算符。

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

6.10 update 更新记录

SQLite 的 UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行，否则所有的行都会被更新。

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

例子：

UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
-- 修改所有记录
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;

6.11 delete 删除记录

DELETE FROM table_name
WHERE [condition];

例子：

DELETE FROM COMPANY WHERE ID = 7;
-- 删除所有记录
DELETE FROM COMPANY;

6.12 like 子句

SQLite 的 LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，LIKE 运算符将返回真（true），也就是 1。这里有两个通配符与 LIKE 运算符一起使用：

1. 通配符

通配符	描述
%	代替零个或多个字符
_	字符列中任何一个字符
[list]	不在字符列中任何一个字符
[!list]	不在字符列中任何一个字符

2. 基本语法

SELECT column_list
FROM table_name
WHERE column LIKE 'XXXX%'

or

SELECT column_list
FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT column_list
FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT column_list
FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT column_list
FROM table_name
WHERE column LIKE '_XXXX_'

实例：

SELECT * FROM COMPANY WHERE AGE  LIKE '2%';
SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';

6.13 glob 子句

SQLite 的 GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，GLOB 运算符将返回真（true），也就是 1。与 LIKE 运算符不同的是，GLOB 是大小写敏感的，对于下面的通配符，它遵循 UNIX 的语法。

• *
• ?

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 '????'

实例：

SELECT * FROM COMPANY WHERE AGE  GLOB '2*';
SELECT * FROM COMPANY WHERE ADDRESS  GLOB '*-*';

6.14 limit

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

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

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

SQLite 引擎将返回从下一行开始直到给定的 OFFSET 为止的所有行。

6.15 Order By

ORDER BY 子句是用来基于一个或多个列按升序或降序顺序排列数据。

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

• ASC 默认值，从小到大，升序排列
• DESC 从大到小，降序排列

6.16 Group By

SQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。

在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

实例：

SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
SELECT NAME, SUM(SALARY)
         FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

6.17 Having

HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。

WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

实例：

-- 显示名称计数小于 2 的所有记录
SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

6.18 distinct

SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。

有可能出现一种情况，在一个表中有多个重复的记录。当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

7 高级

7.1 约束

约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。

约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表

常用约束：

• NOT NULL 约束：确保某列不能有 NULL 值。
• DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
• UNIQUE 约束：确保某列中的所有值是不同的。
• PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
• CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

NOT NULL、PRIMARY KEY

主键是表中的一个字段，唯一标识数据库表中的各行/记录。主键必须包含唯一值。主键列不能有 NULL 值。

在设计数据库表时，主键是很重要的。主键是唯一的 ID。

一个表只能有一个主键，它可以由一个或多个字段组成。当多个字段作为主键，它们被称为复合键。

如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

DEFAULT

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

UNIQUE

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

CHECK

CHECK 约束启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表。

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

7.2 Join

Join 子句用于结合两个或多个数据库中表的记录。JOIN 是一种通过共同值来结合两个表中字段的手段。

SQL 定义了三种主要类型的连接：

交叉连接 - CROSS JOIN

交叉连接的操作，它们都返回被连接的两个表所有数据行的笛卡尔积，返回到的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

例如，A={a,b},B={0,1,2} 则

AxB={<a,o>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>,}

BxA={<0,a>,<0,b>,<1,a>,<1,b>,<2,a>,<2,b>}

SELECT ... FROM table1 CROSS JOIN table2 ...

例子：

SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

内连接 - INNER JOIN

• 必须有等值条件；
• 内连接可能会丢失信息;（内连接是从结果表中删除与其他被连接表中没有匹配行的所有行）
• 重点：内连接，只查匹配行。

内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。

内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的。

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
SELECT * FROM table1 a,table2 b where a.id=b.id ;

为了避免冗余，并保持较短的措辞，可以使用 USING 表达式声明内连接（INNER JOIN）条件。这个表达式指定一个或多个列的列表：

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

自然连接（NATURAL JOIN）类似于 JOIN...USING，只是它会自动测试存在两个表中的每一列的值之间相等值：

SELECT ... FROM table1 NATURAL JOIN table2...

示例：

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

外连接 - OUTER JOIN

查询所有的数据时，就一定会用到外连接。

左外连接 = 内连接 + 左边表中失配的元组（其中，缺少的右边表中的属性值用NULL表示）

外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。

外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中。

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

为了避免冗余，并保持较短的措辞，可以使用 USING 表达式声明外连接（OUTER JOIN）条件。这个表达式指定一个或多个列的列表：

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

例子：

SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

7.3 UNIONS

UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。

为了使用 UNION，每个 SELECT 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但它们不必具有相同的长度。

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

例子：

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

UNION ALL

UNION ALL 运算符用于结合两个 SELECT 语句的结果，包括重复行。

适用于 UNION 的规则同样适用于 UNION ALL 运算符。

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

示例：

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION ALL
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

7.4 NULL

SQLite 的 NULL 是用来表示一个缺失值的项。表中的一个 NULL 值是在字段中显示为空白的一个值。

带有 NULL 值的字段是一个不带有值的字段。NULL 值与零值或包含空格的字段是不同的，理解这点是非常重要的。

7.5 别名

暂时把表或列重命名为另一个名字，这被称为别名。使用表别名是指在一个特定的 SQLite 语句中重命名表。重命名是临时的改变，在数据库中实际的表的名称不会改变。

列别名用来为某个特定的 SQLite 语句重命名表中的列。

表别名的基本语法如下：

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

列 别名的基本语法如下：

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

示例：

SELECT C.ID, C.NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

7.6 Alter 更新表

ALTER TABLE 命令不通过执行一个完整的转储和数据的重载来修改已有的表。您可以使用 ALTER TABLE 语句重命名表，使用 ALTER TABLE 语句还可以在已有的表中添加额外的列。

Sqlite中除了重命名表和在已有的表中添加列，ALTER TABLE 命令不支持其他操作。

ALTER TABLE database_name.table_name RENAME TO new_table_name;
ALTER TABLE database_name.table_name ADD COLUMN column_def...;

示例：

ALTER TABLE COMPANY RENAME TO OLD_COMPANY;
ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

7.7 Truncate Table

SQLite 中，并没有 TRUNCATE TABLE 命令，但可以使用 SQLite 的 DELETE 命令从已有的表中删除全部的数据。

当 SQLite 数据库中包含自增列时，会自动建立一个名为 sqlite_sequence 的表。这个表包含两个列：name 和 seq。name 记录自增列所在的表，seq 记录当前序号（下一条记录的编号就是当前序号加 1）。如果想把某个自增列的序号归零，只需要修改 sqlite_sequence 表就可以了。

DELETE FROM table_name;
DELETE FROM sqlite_sequence WHERE name = 'table_name';
-- 或者
UPDATE sqlite_sequence SET seq = 0 WHERE name = 'table_name';

7.8 子查询

子查询或称为内部查询、嵌套查询，指的是在 SQLite 查询中的 WHERE 子句中嵌入查询语句。

一个 SELECT 语句的查询结果能够作为另一个语句的输入值。

• 子查询必须用括号括起来。
• 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
• ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
• 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。
• BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

示例：

SELECT *
     FROM COMPANY
     WHERE ID IN (SELECT ID
                  FROM COMPANY
                  WHERE SALARY > 45000) ;

INSERT INTO COMPANY_BKP
     SELECT * FROM COMPANY
     WHERE ID IN (SELECT ID
                  FROM COMPANY) ;

UPDATE COMPANY
     SET SALARY = SALARY * 0.50
     WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
                   WHERE AGE >= 27 );

DELETE FROM COMPANY
     WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
                   WHERE AGE > 27 );

7.9 常用函数

SQLite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 SQLite 内置函数，且所有函数都是大小写不敏感。

1. COUNT()

COUNT 聚集函数是用来计算一个数据库表中的行数。

SELECT count(*) FROM COMPANY;

2. MAX()

MAX 聚合函数允许我们选择某列的最大值。

SELECT max(salary) FROM COMPANY;

3. MIN()

MIN 聚合函数允许我们选择某列的最小值。

SELECT min(salary) FROM COMPANY;

4. AVG()

AVG 聚合函数计算某列的平均值。

SELECT avg(salary) FROM COMPANY;

5. SUM()

SUM 聚合函数允许为一个数值列计算总和。

SELECT sum(salary) FROM COMPANY;

6.RANDOM()

RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。

SELECT random() AS Random;

7. ABS()

ABS 函数返回数值参数的绝对值。

SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");

8. UPPER()

UPPER 函数把字符串转换为大写字母。

SELECT upper(name) FROM COMPANY;

9. LOWER()

LOWER 函数把字符串转换为小写字母。

SELECT lower(name) FROM COMPANY;

10. LENGTH()

LENGTH 函数返回字符串的长度。

SELECT name, length(name) FROM COMPANY;

11. datetime()

以 YYYY-MM-DD HH:MM:SS 格式返回。

-- 计算给定 UNIX 时间戳 1092941466 的本地日期和时间：
SELECT datetime(1092941466, 'unixepoch', 'localtime');

8 C/C++ 编程接口

8.1 库安装

sudo apt install sqlite3 libsqlite3-dev -y

8.2 接口 API

1. 打开数据库

SQLITE_API int sqlite3_open(
    const char *filename,   // Database filename (UTF-8) 数据库库名
     sqlite3 **ppDb);       // OUT: SQLite db handle 句柄
                            // 返回值： 成功返回 SQLITE_OK
                            // 如果返回值为 SQLITE_OK，则表示成功打开数据库。
                            // SQLITE_ERROR、SQLITE_CANTOPEN、SQLITE_BUSY 等，表示打开数据库失败的原因。

示例：

#include <stdio.h>
#include <sqlite3.h>

int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;

   rc = sqlite3_open("test.db", &db);

   if( rc ){
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfully\n");
   }
   sqlite3_close(db);
}

2. 执行 sql 语句

解析并执行由 sql 参数所给的每个命令，直到字符串结束或者遇到错误为止。

typedef int (*sqlite3_callback)(void*,int,char**, char**);
SQLITE_API int sqlite3_exec(
				  sqlite3* db,                                  /* An open database */
				  const char *sql,                           /* SQL to be evaluated */
				  int (*callback)(void*,int,char**,char**),  /* Callback function */
				  void * arg,                                    /* 1st argument to callback */
				  char **errmsg                              /* Error msg written here */
				);
// db:          句柄，打开的数据库对象
// sql:         sql 语句
// callback:    回调函数，arg 是回调函数的第一参数
// errmsg:      被返回用来获取程序生成的任何错误。
// int : 成功返回 SQLITE_OK，失败返回 错误
//       SQLITE_OK：执行成功。
//       SQLITE_ERROR：SQL 语句执行失败。
//       SQLITE_BUSY：数据库忙，无法执行 SQL 语句。
//       SQLITE_MISUSE：使用了错误的 API 接口或者 API 接口的参数错误。
//       SQLITE_DONE：SQL 语句执行完成，但是没有返回任何数据。
//       其他错误码：表示执行过程中出现了其他错误，可以通过 sqlite3_errmsg 函数获取具体错误信息。

例子：（创建表）

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
   int i;
   for(i=0; i<argc; i++){
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zErrMsg = 0;
   int  rc;
   char *sql;

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stdout, "Opened database successfully\n");
   }

   /* Create SQL statement */
   sql = "CREATE TABLE COMPANY("  \
         "ID INT PRIMARY KEY     NOT NULL," \
         "NAME           TEXT    NOT NULL," \
         "AGE            INT     NOT NULL," \
         "ADDRESS        CHAR(50)," \
         "SALARY         REAL );";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
   if( rc != SQLITE_OK ){
   fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Table created successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

select 操作：

每找到一条记录自动执行一次回调函数

先看回调函数：

typedef int (*sqlite3_callback)(
void* arg,    /* Data provided in the 4th argument of sqlite3_exec() */
int argc,      /* The number of columns in row */
char** argv,   /* An array of strings representing fields in the row */
char** azCoLName    /* An array of strings representing column names */
);

• arg: sqlite3_exec() 的第四个参数。
• argc: 查询结果集合中的列数。
• argv: 指向当前查询结果集合的第一列的指针。
• azCoLName 指向当前查询结果中第一列所对应的列名的指针。

返回值：成功返回 0 ，失败返回 -1

例子：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   for(i=0; i<argc; i++){
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create SQL statement */
   sql = "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;

3. 关闭sqlite 连接

SQLITE_API int sqlite3_close(sqlite3*);

4. 非回调（同步）接口

执行SQL操作

SQLITE_API int sqlite3_get_table(
  sqlite3 *db,          /* An open database */
  const char *zSql,     /* SQL to be evaluated */
  char ***pazResult,    /* Results of the query */
  int *pnRow,           /* Number of result rows written here */
  int *pnColumn,        /* Number of result columns written here */
  char **pzErrmsg       /* Error msg written here */
);
// 释放 pazResult
SQLITE_API void sqlite3_free_table(char **result);

• db 数据库句柄
• zSql sql 语句
• pazResult 用来指向sql执行结果的指针
• pnRow 满足条件的记录的数目
• pnColumn 每条记录包含的字段数目
• pzErrmsg 错误信息指针的地址

返回值：成功返回值 0 ，失败返回错误码

示例：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

int main(void) {
    sqlite3 *db;
    char *errmsg;
    int nrows, ncols, i, j;
    char **result;

    // 打开数据库
    if (sqlite3_open("test.db", &db) != SQLITE_OK) {
        fprintf(stderr, "打开数据库失败: %s\n", sqlite3_errmsg(db));
        exit(1);
    }

    // 执行 SQL 查询
    if (sqlite3_get_table(db, "SELECT * FROM users", &result, &nrows, &ncols, &errmsg) != SQLITE_OK) {
        fprintf(stderr, "执行 SQL 查询失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        exit(1);
    }

    // 打印查询结果
    printf("查询结果:\n");
    for (i = 0; i <= nrows; i++) {
        for (j = 0; j < ncols; j++) {
            printf("%s\t", result[i * ncols + j]);
        }
        printf("\n");
    }

    // 释放查询结果
    sqlite3_free_table(result);

    // 关闭数据库
    sqlite3_close(db);

    return 0;
}