SQLite学习笔记，主要是语法相关，主要来源于菜鸟教程：

https://www.runoob.com/sqlite/sqlite-tutorial.html

数据库基础概念

数据库的模型

  • 层次模型
  • 网状模型
  • 关系模型
  • 面向对象模型
  • 半结构化模型

由于关系模型在很长一段时间内成为主流的数据模型，所以我们也习惯性将数据库类型分为两类，关系型数据库和非关系型数据库。

关系型数据库

这是我们目前至今主流的数据库类型，其对应的数据存储模型就是关系型模型，数据以表格形式存储，字段关联数据。

二维表结构是非常贴近逻辑世界的一个概念，它更容易理解，这是关系型数据库能够成为主流的其中一个重要原因。通过 SQL 进行表与表之间的联接查询非常的方便自然。

缺点也是很显而易见的，海量数据下，对一张表的查询会显得很力不从心，就是因为数据的存储不具备特殊的数据结构，例如有些非关系型数据库的数据存储结构是类似树的结构，就使得查询上具有天然的优势。

所以个人认为，虽然现在是关系型数据库的天下，但相信以后会出现一些优秀的非关系型数据库取代传统的关系型数据库。因为以后必然是大数据的时代，那么海量数据下，传统的关系型数据的效率问题就会被逐渐放大。

什么是索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

索引分为：聚集索引、非聚集索引、唯一索引等。
一张表可以有多个唯一索引和非聚集索引，但最多只能有一个聚集索引。 - 索引可以包含多列。
合理的创建索引能够提升查询语句的执行效率，但降低了新增、删除操作的速度，同时也会消耗一定的数据库物理空间。

如何描述多对多的关系

系型数据库中描述多对多的关系,需要建立第三张数据表。
比如学生选课,需要在学生信息表和课程信息表的基础上,再建立选课信息表,该表中存放学生Id和课程Id。

内联接、左外联接、右外联接

  • (Inner Join）：匹配2张表中相关联的记录。
  • (Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示。
  • (Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示。在判定左表和右表时，要根据表名出现在Outer Join的左右位置关系。

  • 约定

  • INNER JOIN 内联查询

    示例：

  • LIFT JOIN 左连接查询

    示例：

  • RIGHT JOIN 右连接查询

    示例：(sqlite 不支持right join & full join)

  • **CROSS JOIN **

    示例：

https://zhuanlan.zhihu.com/p/29234064

sql语句中join on和where

![](https://cdn.jsdelivr.net/gh/echomlv/kangimages/imgs/20200401231243.png)

https://my.oschina.net/jun24bryant/blog/787375

SQLite简介

为什么要用SQLite

SQLite架构

SQLite安装

目前，几乎所有版本的 Linux 操作系统都附带 SQLite。所以，只要使用下面的命令来检查您的机器上是否已经安装了 SQLite。

1
2
3
sqlite3
SQLite version 3.7.15.2  2013-01-09  11:53:05  Enter  ".help"  for instructions Enter SQL statements terminated with a ";"
sqlite

如果没有看到上面的结果，那么就意味着没有在 Linux 机器上安装 SQLite。

SQLite下载地址

http://www.sqlite.org/download.html

Linux下通过源码安装

1
2
3
4
5
tar xvzf sqlite-autoconf-3310100.tar.gz
cd sqlite-autoconf-3310100
./configure --prefix=/usr/local
make
make install

Windows下安装

下载：
sqlite-dll-win64-x64-3310100.zip
sqlite-tools-win32-x86-3310100.zip

解压至同一目录下，并添加至环境变量

DB Browser for SQLite

简介

（1）项目地址：http://sqlitebrowser.org/
（2）DB Browser for SQLite 是一个高品质，可视化，开放源码的工具。用于创建，设计和编辑 SQLite 兼容的数据库文件。
（3）它适用于希望创建数据库，检索和编辑数据的用户和开发人员。它采用了熟悉的电子表格一样的界面，你不需要学习复杂的 SQL 命令。
（4）支持各种操作系统：Windows、MacOS、Linux

功能

（1）创建数据库文件
（2）创建、定义、修改和删除表
（3）创建、定义和删除索引
（4）浏览、编辑、添加和删除记录
（5）搜索记录
（6）导入和导出为文本记录
（7）将表导出成 CSV 文件，或从 CSV 文件导入表
（8）将表导出成数据库存储文件，或从数据库存储文件导入表
（9）SQL 查询以及错误检查
（10）检查 SQL 命令的错误日志

https://www.hangge.com/blog/cache/detail_1375.html

下载安装

https://sqlitebrowser.org/dl/

SQLite命令

SQLite常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#查看命令清单
.help

#退出
.quit
.exit

#开启/关闭头部信息
.header ON | OFF

#显示各种设置的当前值
.show

#设置输出模式
.mode MODE
#MODE可以是:
   csv 逗号分隔符
   column 左对齐的列
   html，html的<table>代码
   intert，TABLE表的SQL插入语句
   line, 每行一个
   list, 由 .separator 字符串分隔的值
   table, 由 Tab 分隔的值
   tcl, TCL列

SQLite语法

大小写敏感性

有个重要的点值得注意，SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。

注释

SQLite 注释是附加的注释，可以在 SQLite 代码中添加注释以增加其可读性，他们可以出现在任何空白处，包括在表达式内和其他 SQL 语句的中间，但它们不能嵌套。

SQL 注释以两个连续的 “-“ 字符（ASCII 0x2d）开始，并扩展至下一个换行符（ASCII 0x0a）或直到输入结束，以先到者为准

您也可以使用 C 风格的注释，以 “/“ 开始，并扩展至下一个 “/“ 字符对或直到输入结束，以先到者为准。SQLite的注释可以跨越多行。

sqlite>.help -- 这是一个简单的注释

SQLite 存储类

SQLite常用命令行操作

创建数据库

1
2
3
4
5
6
7
8
9
10
11
#创建
sqlite3 test.db

#查看
sqlite> .databases

#导出数据库内容到文本文件
sqlite3 test.db .dump >testdb.sql

#从文本文件恢复
sqlite3 testdb.db < testdb.sql

附加数据库

假设这样一种情况，当在同一时间有多个数据库可用，您想使用其中的任何一个。SQLite 的 ATTACH DATABASE 语句是用来选择一个特定的数据库，使用该命令后，所有的 SQLite 语句将在附加的数据库下执行。

1
ATTACH DATABASE 'test.db' AS 'test'

分离数据库

SQLite的 DETACH DTABASE 语句是用来把命名数据库从一个数据库连接分离和游离出来，连接是之前使用 ATTACH 语句附加的。如果同一个数据库文件已经被附加上多个别名，DETACH 命令将只断开给定名称的连接，而其余的仍然有效。您无法分离 main 或 temp 数据库。

如果数据库是在内存中或者是临时数据库，则该数据库将被摧毁，且内容将会丢失。

1
DETACH DATABASE 'testtest'

创建表

SQLite 的 CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建基本表，涉及到命名表、定义列及每一列的数据类型。

1
2
3
4
5
6
7
sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

删除表

SQLite 的 DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。

使用此命令时要特别注意，因为一旦一个表被删除，表中所有信息也将永远丢失。

1
2
3
4
5
#确认表已经存在
sqlite>.tables

#删除company表
sqlite>DROP TABLE COMPANY

insert语句

SQLite 的 INSERT INTO 语句用于向数据库的某个表中添加新的数据行。

1
2
3
4
5
6
#向company表中添加内容
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 );

select语句

SQLite 的 SELECT 语句用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集。

1
2
3
4
5
6
7
8
#获取company id 和 name字段
sqlite> SELECT id, name FROM COMPANY;

#获取company表中所有字段
sqlite> SELECT * FROM COMPANY;

#设置输出列的宽度
sqlite> .width 10,20,20

SQLite运算符

算术运算符

1
2
3
4
sqlite> select 10 + 20;
10 + 20
----------
30

比较运算符

1
sqlite> SELECT * FROM COMPANY WHERE SALARY >= 10000;

逻辑运算符

1
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

位运算

SQLite子句

SQLite Where子句

SQLite Update子句

SQLite Like子句

不区分大小写:

SQLite Glob子句

区分大小写：

SQLite Limit子句

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

offset 指定开头要跳过多少行，如offset为3，则跳过1~3行；

SQLite Order By子句

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

SQLite Having子句

SQLite Distinct关键字

SQLite约束

SQLite NULL 值

SQLite Trigger(触发器)

实例
让我们假设一个情况，我们要为被插入到新创建的 COMPANY 表（如果已经存在，则删除重新创建）中的每一个记录保持审计试验；
为了保持审计试验，我们将创建一个名为 AUDIT 的新表。每当 COMPANY 表中有一个新的记录项时，日志消息将被插入其中；

往company表中添加新记录：

SQLite 索引（Index）

  • 创建索引

  • 索引类型

  • 删除索引

  • 什么情况下要避免使用索引？

  • 示例

SQLite Indexed By

  • 示例

SQLite Alter 命令

  • 实例：

SQLite 视图（View）

  • 实例：

SQLite 事务（Transaction）

SQLite 子查询

  • 实例：

SQLite Autoincrement

SQLite 日期 & 时间

  • 时间字符串(timestring)

  • 修饰符（Modifier）

  • 格式化

  • 实例

    • 当前时间（格林尼治时间GMT,约等于UTC）

    • 当前时间 (本地)

    • 当前时间（unix时间戳）

SQLite 常用函数

SQLite API C/C++接口

函数原型: sqlite3_open()

1
2
3
4
int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

注意：
* 不管打开是否出错，都需要调用sqlite_close()关闭；
* 如果 filename 参数是 NULL 或 ‘:memory:’，那么 sqlite3_open() 将会在 RAM 中创建一个内存数据库；
* 如果该名称的文件不存在，sqlite3_open() 将创建一个新的命名为该名称的数据库文件并打开；

函数原型：sqlite3_exec()

1
2
3
4
5
6
7
int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

回调函数：

注意：如果提供 了errmsg，用来创建错误消息的内存是在堆上分布的。 故在调用后，应该检查一下是否为null值，如果有错误发生，使用sqlite3_free()释放errmsg占用的内存。

实例：连接数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
//test_open.c
#include <stdio.h>
#include <stdlib.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);
}

编译：

1
2
3
#最初使用这种方式，发现找不到头文件
gcc -o test_open test_open.c

1
2
#然后将头文件拷贝到目录下，改成#include "sqlite3.h",
#提示找不到引用
1
2
#然后发现应该加 -l sqlite3, 发现依旧出错
gcc -o test_open test_open.c -l sqlite3
使用`find -name "libsqlite3.so"`发现为找到相关库；

最后解决方案,是没有安装sqlite-devel,安装：

1
2
sudo yum install sqlite-devel

实例：创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#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 操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
#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;
}

实例：UPDATE操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
#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 merged SQL statement */
   sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
         "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;
}

实例：Delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
#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 merged SQL statement */
   sql = "DELETE from COMPANY where ID=2; " \
         "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;
}

SQLite线程安全和并发

并发问题

https://jin-yang.github.io/post/sqlite-introduce.html

线程安全

![](https://cdn.jsdelivr.net/gh/echomlv/kangimages/imgs/20200410095442.png)

Rollback journal 和 WAL

  • Rollback journal

  • WAL

SQLite database lock错误

  • 说明

  • 可能出现database lock错误的原因

参考引用

SQLite C tutorial
SQLite教程
图解 SQL 里的各种 JOIN
SQLite 线程安全和并发
SQLite WAL 模式简单介绍
SQLite Error Database is Locked- How to resolve?