C语言与sqlite3入门_const char *sql = "drop table if exists cars;" cma

1 sqlite3数据类型

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

2 sqlite3指令

sqlite3 test.db # 打开数据库， 没有就创建

.databases #查看所有的数据库位置。
1
2
3

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
);
1
2
3
4
5
6
7

3.2 删除表drop

DROP TABLE COMPANY;
1

3.3 插入数据insert into

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

或者

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

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

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

3.4 查询select from

SELECT ID, NAME, SALARY FROM COMPANY ;
1

3.5 where子句

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

逻辑与或子句

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

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

SELECT * FROM COMPANY WHERE AGE IS NOT NULL
1

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

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

in
年龄为25或27的记录

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

年龄不为25且不为27

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

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

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

3.6 修改数据update

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

3.7 删除数据delete

DELETE FROM COMPANY WHERE ID = 7;
1

3.8 排序Order By

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

SELECT
   select_list
FROM
   table
ORDER BY
    column_1 ASC,
    column_2 DESC;
1
2
3
4
5
6
7

3.9 分组GROUP BY

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

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

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
);
1
2
3
4
5
6
7

主键约束：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;
}
1
2
3
4
5
6
7
8

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)

1
2
3
4
5
6
7
8
9
10
11
12
13

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

cmake .. & make
# 再执行test
./test
1
2
3

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

make
./test
1
2

5 创建或打开数据库

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

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

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

关闭

sqlite3_close(db);
1

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

#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;

}
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

6 sqlite3_exec

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

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

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){}
1
2
3
4
5
6

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;
}

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
49
50
51
52
53

7 sqlite3_prepare

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

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

sqlite3_stmt *pstmt;
1

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即可 */
);
1
2
3
4
5
6
7

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

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);
1
2
3
4

用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
2
3
4

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);
1
2
3
4
5
6

7.3 sqlite3_step

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

rc = sqlite3_step(pstmt);
1

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

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));
}
1
2
3
4
5
6

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);
1
2
3

7.5 sqlite3_reset

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

int sqlite3_reset(sqlite3_stmt *pStmt);
1

这么做的目的就是在比如插入多个数据，绑定了一个人的数据，然后需要绑定第二个的时候需要清空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);
1
2
3

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

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

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

#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;
}

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

参考

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