c语言与sqlite3入门
- 1 sqlite3数据类型
- 2 sqlite3指令
- 3 sqlite3的sql语法
- 3.1 创建表create
- 3.2 删除表drop
- 3.3 插入数据insert into
- 3.4 查询select from
- 3.5 where子句
- 3.6 修改数据update
- 3.7 删除数据delete
- 3.8 排序Order By
- 3.9 分组GROUP BY
- 3.10 约束
- 4 c语言执行sqlite3
- 4.1 下载c源码
- 4.2 cmake编译运行
- 5 创建或打开数据库
- 6 sqlite3_exec
- 7 sqlite3_prepare
- 7.1 sqlite3_prepare_v2
- 7.2 sqlite3_bind
- 7.3 sqlite3_step
- 7.4 sqlite3_column
- 7.5 sqlite3_reset
- 8 读写blob型数据
- 参考
1 sqlite3数据类型
- NULL 空
- INTEGER 整形
- REAL 浮点
- TEXT 文本
- 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就是降序。
SELECT
select_list
FROM
table
ORDER BY
column_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个参数。
- para 传来的参数
- columnCount 列数
- columnValue 一维的字符串数组,保存的是每一列数据。
- 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*))
- 句柄handle
- 需要赋值的是sql语句中的第几个参数,从1开始。
- 插入值,text和blob是指针。
- 插入值长度(strlen/sizeof)。
- 绑定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);
返回值有两个需要注意的返回值
- SQLITE_DONE: 表示执行完毕
- 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系列函数