C语言与sqlite3入门

1 sqlite3数据类型

1. NULL 空
2. INTEGER 整形
3. REAL 浮点
4. TEXT 文本
5. BLOB binary large object二进制对象，一般存图像，声音，自定义结构体

2 sqlite3指令

sqlite3 test.db # 打开数据库， 没有就创建.databases #查看所有的数据库位置。

3 sqlite3的sql语法

3.1 创建表create

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

3.2 删除表drop

DROP TABLE COMPANY;

3.3 插入数据insert into

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

或者

INSERT INTO COMPANY(id, name, age, address, salary) VALUES (7, 'James', 24, 'Houston', 10000.00 );

用第二种更严谨一些
假如没有设置值，为NULL或者0

INSERT INTO COMPANY(ID, name, age) VALUES (8, '张三', 11);

3.4 查询select from

SELECT ID, NAME, SALARY FROM COMPANY ;

3.5 where子句

select * from与where name like 'a%'都是会查询所有表的内容，一般禁用。

逻辑与或子句

 SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000

不为空
查询age不为空的记录

SELECT * FROM COMPANY WHERE AGE IS NOT NULL

模糊查询
所有名字以ki开头的

SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';

in
年龄为25或27的记录

SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

年龄不为25且不为27

SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

子查询
年龄大于所有65000薪水以上员工年龄的记录

SELECT * FROM COMPANY   WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

3.6 修改数据update

UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;

3.7 删除数据delete

DELETE FROM COMPANY WHERE ID = 7;

3.8 排序Order By

其实默认就是升序，ASC是升序，DESC就是降序。

SELECTselect_list
FROMtable
ORDER BYcolumn_1 ASC,column_2 DESC;

3.9 分组GROUP BY

比如可以按照年龄来分组，看看不同年龄的平均薪资。

SELECT AGE, avg(SALARY) from  COMPANY GROUP BY AGE;

3.10 约束

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

主键约束：PRIMARY KEY，ID作为主键，不能有重复值，一般也不能为NULL。

NOT NULL: 不为空，该列不能有NULL

CEHCK 添加修改记录时，需要符合check条件。

DEFAULT 设置默认值。

4 c语言执行sqlite3

4.1 下载c源码

打开 c源码sqlite3下载页面 下载其中的source code源码，下第一个就好。

放入项目的sqlite文件夹中，除开我已经创建的两个数据库，项目结果应该长这样。

随便写一个c,获取sqlite3版本

#include <stdio.h>
#include "sqlite3.h"
int main(void)
{printf("%s\n", sqlite3_libversion());return 0;
}

4.2 cmake编译运行

在CMakeLists.txt中写

cmake_minimum_required (VERSION 3.5)project(test)
add_definitions("-Wall -g")include_directories (sqlite)add_executable(${PROJECT_NAME} ${PROJECT_SOURCE_DIR}/test.c ${PROJECT_SOURCE_DIR}/sqlite/sqlite3.c)
target_link_libraries (${PROJECT_NAME} pthread dl)add_executable(sqliteShell ${PROJECT_SOURCE_DIR}/sqlite/shell.c ${PROJECT_SOURCE_DIR}/sqlite/sqlite3.c)
target_link_libraries (sqliteShell pthread dl)

写好后cd打开build文件夹执行cmake和makefile

cmake .. & make
# 再执行test
./test

假如没有修改文件结构(增删文件/修改文件位置)，只是修改了文件内容。
再次编译代码不需要再次运行cmake了，运行make即可

make
./test

5 创建或打开数据库

sqlite3_open函数，打开数据库，没有就创建一个。

第一个参数就是数据库位置，相对位置是相对于程序执行时的位置，不是c文件所在位置
第二个是双指针的通道

sqlite3 *db = NULL;
int rc = sqlite3_open("../test.db", &db);

关闭

sqlite3_close(db);

如下方就可以创建一个表，并且插入一些值来使用

#include <stdio.h>
#include "sqlite3.h"
int main(void) 
{sqlite3 *db = NULL;char *err_msg = NULL;int rc = sqlite3_open("../test.db", &db);if(rc != SQLITE_OK){fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));sqlite3_close(db);return 1;}const char *sql = "DROP TABLE IF EXISTS Cars;" "CREATE TABLE Cars(Id INT, Name TEXT, Price INT);" "INSERT INTO Cars VALUES(1, 'Audi', 52642);" "INSERT INTO Cars VALUES(2, 'Mercedes', 57127);" "INSERT INTO Cars VALUES(3, 'Skoda', 9000);" "INSERT INTO Cars VALUES(4, 'Volvo', 29000);" "INSERT INTO Cars VALUES(5, 'Bentley', 350000);" "INSERT INTO Cars VALUES(6, 'Citroen', 21000);" "INSERT INTO Cars VALUES(7, 'Hummer', 41400);" "INSERT INTO Cars VALUES(8, 'Volkswagen', 21600);";rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg);if(rc != SQLITE_OK){fprintf(stderr, "SQL error: %s\n", err_msg);sqlite3_free(err_msg);sqlite3_close(db);return 1;}sqlite3_close(db);return 0;}

6 sqlite3_exec

sqlite3_exec是执行sql语句的函数，算是最重要的函数。

sqlite3*, //数据库
const char* sql,//sql语句
*callback, //回调
void* data, //回调的参数  
char **errmsq //错误信息

const char * = "SELECT * FROM car";
char* dataName = "test";
char *err_msg = NULL;
int rc = sqlite3_exec(db, sql, callback, dataName, &err_msg);int callback(void* para, int columnCount, char** columnValue, char** columnName){}

callback在每查询到一行数据的时候就调用一次，所以每次得到的是一行数据。

其中callback只能自己传一个参数，但是自身有4个参数。

1. para 传来的参数
2. columnCount 列数
3. columnValue 一维的字符串数组，保存的是每一列数据。
4. columnName 一维字符串数组，列名。

比如下方获取所有的test.db中的数据，打印出来。

#include <stdio.h>
#include "sqlite3.h"int callback(void *, int, char **, char **);int main(void)
{sqlite3 *db = NULL;char *err_msg = NULL;int rc = sqlite3_open("../test.db", &db);if (rc != SQLITE_OK) {fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));sqlite3_close(db);return 1;}const char *sql = "SELECT * FROM Cars";char* dataName = "test";rc = sqlite3_exec(db, sql, callback, dataName, &err_msg);if (rc != SQLITE_OK ) {fprintf(stderr, "Failed to select data\n");fprintf(stderr, "SQL error: %s\n", err_msg);sqlite3_free(err_msg);sqlite3_close(db);return 1;}sqlite3_close(db);return 0;
}int callback(void* para, int columenCount, char** columnValue, char** columnName)
{for (int i = 0; i < columenCount; ++i){printf("%s = %s\n", columnName[i], (columnValue[i] ? columnValue[i] : "NULL"));}printf("\n");return 0;
}

7 sqlite3_prepare

exec使用起来简单，它在执行的过程中，有一个编译再执行的过程。
假如有多个insert语句，exec需要每inset一次都需要编译一次，效率低。
对于结构相同的语句，我们是否可以先编译，于是有了一个prepare，先编译，再插入变量执行。

最开始有一个控制变量sqlite3_stmt的句柄，其中stmt的全称应该是statement。

sqlite3_stmt *pstmt;

7.1 sqlite3_prepare_v2

先需要准备一个模板

int sqlite3_prepare_v2(sqlite3 *db,            /* 数据库通道 */const char *zSql,       /* sql语句 */int nByte,              /* sql语句长度，一般填入-1自动计算 */sqlite3_stmt **ppStmt,  /* 准备语句的控制权柄 */const char **pzTail     /* sql语句超出了nByte后存放位置，一般把nByte设置足够大，这个设置为NULL即可 */
);

下面是一个模板的代码，将插入语句设置为模板，其中需要插入的内容用？代替

sqlite3_stmt *pStmt = NULL;
char *pzTail = NULL;
const char *sql = "INSERT INTO person(name, age, sex) VALUES(?,?,?);";
rc = sqlite3_prepare_v2(pdb, sql, strlen(sql), &pStmt, pzTail);

用prepare语句后可以先编译。

7.2 sqlite3_bind

这个函数就是设置插入值。

有三个函数，用于插入不同类型值。

int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_doubule(sqlite3_stmt*, int, double);
int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int, void(*)(void*));
int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int, void(*)(void*))

1. 句柄handle
2. 需要赋值的是sql语句中的第几个参数，从1开始。
3. 插入值，text和blob是指针。
4. 插入值长度(strlen/sizeof)。
5. 绑定blob类型的析构函数，一般可以设置为NULL

那么现在我们需要插入值来形成一个sql语句就可以这么写

const char * name = "iceylia";
age = 100;
sex = "未知";
sqlite3_bind_text(pstmt, 1, name, strlen(name), NULL);
sqlite3_bind_int(pstmt, 2, age);
sqlite3_bind_text(pstmt, 3, sex, strlen(sex), NULL);

7.3 sqlite3_step

插入值后执行sql语句，用sqlite3_step

rc = sqlite3_step(pstmt);

返回值有两个需要注意的返回值

1. SQLITE_DONE: 表示执行完毕
2. SQLITE_ROW: 当使用select语句时，会得到多个数据，每次只能读取一行的值，

比如获取表中所有参数，需要多次使用sqlite3_step获取列。

const char *sql = "SELECT * FROM Cars;";
const char *pzTail;
rc = sqlite3_prepare_v2(db, sql, -1, &pStmt, &pzTail);
while(sqlite3_step(pStmt)==SQLITE_ROW){printf("id = %d\n", sqlite3_column_int(pStmt, 0));
}

7.4 sqlite3_column

上面的示例代码中使用了sqlite3_column，这是获取查询到的数据的函数

同样有三个，第二个参数是列号，从0开始。

int sqlite3_column_int(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);

7.5 sqlite3_reset

将bind绑定的值全部取消清楚，方便重新绑定

int sqlite3_reset(sqlite3_stmt *pStmt);

这么做的目的就是在比如插入多个数据，绑定了一个人的数据，然后需要绑定第二个的时候需要清空statement。

8 读写blob型数据

读入
建表时定义一个blob，插入时用statement插入

const char *newSql = "INSERT INTO Images(Data) VALUES(?)";
rc = sqlite3_prepare_v2(db, newSql, -1, &pStmt, NULL);
sqlite3_bind_blob(pStmt, 1, &data, sizeof(data), NULL);

读取,也用statement读取，用column读取。

myData *pData = (myData*)sqlite3_column_blob(pStmt, 0);

下面是一个完整的读取写入代码。

#include <stdio.h>
#include "sqlite3.h"
#include <string.h>typedef struct
{int value1;double value2;
} myData;int main(void)
{sqlite3 *db = NULL;char *err_msg = NULL;int rc = sqlite3_open("../test.db", &db);if (rc != SQLITE_OK){fprintf(stderr, "Cannot open database: %s\n",sqlite3_errmsg(db));sqlite3_close(db);return 1;}const char *sql = "DROP TABLE IF EXISTS Images;""CREATE TABLE Images(Id INTEGER PRIMARY KEY, Data BLOB);";rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg);if (rc != SQLITE_OK){fprintf(stderr, "Failed to select data\n");fprintf(stderr, "SQL error: %s", err_msg);sqlite3_free(err_msg);sqlite3_close(db);return 1;}sqlite3_stmt *pStmt = NULL;const char *newSql = "INSERT INTO Images(Data) VALUES(?)";rc = sqlite3_prepare_v2(db, newSql, -1, &pStmt, NULL);myData data = {100, 0.156};sqlite3_bind_blob(pStmt, 1, &data, sizeof(data), SQLITE_STATIC);rc = sqlite3_step(pStmt);if (rc != SQLITE_DONE){printf("execution failed: %s", sqlite3_errmsg(db));}sqlite3_finalize(pStmt);char *sql2 = "SELECT Data FROM Images WHERE Id = 1";pStmt = NULL;rc = sqlite3_prepare_v2(db, sql2, -1, &pStmt, NULL);if (rc != SQLITE_OK ) {fprintf(stderr, "Failed to prepare statement\n");fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));sqlite3_close(db);return 1;} rc = sqlite3_step(pStmt);int bytes = 0;if (rc == SQLITE_ROW){bytes = sqlite3_column_bytes(pStmt, 0);}myData *pData = (myData*)sqlite3_column_blob(pStmt, 0);printf("bytes: %d, %d, %lf\n", bytes, pData->value1, pData->value2);rc = sqlite3_finalize(pStmt);   sqlite3_close(db);return 0;
}

参考

C语言操作SQLite3简明教程
深入理解SQLite3之sqlite3_exec及回调函数
玩转SQLite-11：C语言高效API之sqlite3_prepare系列函数