SQLite3
- 使用sqlite3引擎打开一个数据库文件,不存在则在当前目录下自动创建
sqlite3 文件名.sqlite - 关闭数据库文件:
.quit - 创建表,查看表 “.table”
create table 表名(列名1,列名2,…);//不存在则打印报错
create table if not exists 表名(列名1,列名2,…);//不打印信息 - 删除表 drop table 表名;
- 输入风格:当一个语句后不加";“则表示为未输入完,按回车后,终端不执行该语句,直到出现”;",才执行,当语句过长时我们可以进行分段输入。
- 变量
整型:integer 字符串类型:text 二进制类似:blob
主键:primary key不可重复
autoincrement:自增长
not null:不为空 - 插入数据:
insert into 表名 values(数值1,数值2,…); //对一行按顺序赋值
insert into 表名(列名1,列名2,…) values(数值1,数值2,…); //对某列进行赋值 - 查看表:
select * from 表名; //查看所有信息
select 列名 from 表名; //查看某列
select * from 表名 where 列名1的相应判断 and 列名2的相应判断(>、<等) or 列名3 like ‘关键字1 % 关键字2’; //"where"条件查询,"and/or"条件连接,“like” 表示字符串模糊匹配, "%“表示一个字符或多个字符的通配符,”_"表示一个字符。
select * from 表名 where 列名 not like ‘关键字__’; // not like 与like相反, "__ "一个下划线表示一个字符的通配 - 删除数据:
delete from 表名 where 列名判断 - 更新数据:
update 表名 set 列名1赋值 ,列名2赋值 where 列名2判断;
SQL3_C库操作
#include <sqlite3.h> //位于/usr/include/sqlite3.h,对于操作函数的声明,具体实现在动态库sqlite3,编译时要指定 -lsqlite3
- 打开数据库函数:
sqlite3 *db; //数据库文件的连接,类似于文件句柄
sqlite3_open(路径名, &db); - 创建表
char *sql; //字符串
sql = “create table if not exists 表名(列名变量的定义)”;
sqlite3_exec(连接符bd, sql, NULL, NULL, &errmsg);
对于sqlite3_exec 一般用于创建表操作,不用做查询操作
SQLITE_API 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 */
);
-
关闭表
sqlite3_close(db); -
查询表
sql = “select * from zhuce where count=? and passward=?”;
ret = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL); //参数3位sql大小,-1表示内部自动计算大小,参数4为stmt结构体,内存由内部进行申请,对通配符?进行填充数据。
if(ret != SQLITE_OK)
{
sqlite3_close(db);
}
sqlite3_bind_text(stmt, 1, d->count, -1, NULL);//填充
sqlite3_bind_text(stmt, 2, d->passward, -1, NULL);
//这个函数执行一次最多能够查询到一条记录
ret = sqlite3_step(stmt);
if(ret != SQLITE_ROW)
{
sqlite3_finalize(stmt);//释放stmt内存
sqlite3_close(db);
} -
提取一行中的某一列值
sprintf(&sql1, “select * from %s”, j->count);
ret = sqlite3_prepare_v2(db, &sql1, -1, &stmt, NULL);
if(ret != SQLITE_OK)
{
//错误处理
}
while(1)
{
ret = sqlite3_step(stmt);
if(ret != SQLITE_ROW)
{
break;
}
//列从0开始计数
count = (char *)sqlite3_column_text(stmt, 1);
sprintf(haoyou.dst_count, “%s”, count);
haoyou.status = HAOYOU_ITEM;
sendto(sd, xx, sizeof(xx), 0, (void *)addr, sizeof(*addr));
} -
赋值语句,sprintf(sql, “insert into %s(count) values(’%s’)”, str1, str2);
这里需注意:第一个%s无需加单引号,因为时命令语句,没有特定类型,直接替换,而第二个要加,因为第二个如果不加,当str2为123,则表示有可能为整数类型,会导致类型不一致。 -
sprintf注意事项
对于sqlite3的C语言操作语句字符串用sprintf格式进行赋值,例如sprintf(sql, “insert into stu values(%d, “%s”, %d)”,id,name,score); 其中字符串的占位符需加双引号。
代码示例:
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
int main(void)
{
int ret,i,j;
sqlite3 *db; //数据库文件的连接,类似于文件句柄
char sql[256]; //字符串
sqlite3_stmt *stmt;//状态指针
int nrow, ncolumn;//行列
//result存放读取的数据,error存放错误信息
char ** result,*error;
//1.打开数据库文件,不存在会新建
ret = sqlite3_open("Store_Monitor_SQL3.db", &db);
if(ret != SQLITE_OK)
{
fprintf(stderr,"can't open database:%s\n",sqlite3_errmsg(db));
exit(0);
}
//2.创建表
memset(sql,0,sizeof(sql));
sprintf(sql, "create table if not exists ObjInfo(no INTEGER primary key autoincrement,Name varchar(128),Num int,unitPrice float,totalPrice float)");
ret = sqlite3_exec(db, sql, NULL, NULL, &error);
if (ret != SQLITE_OK)
{
printf("表格创建失败");
getchar();
return;
}
//3.插入数据
memset(sql,0,sizeof(sql));
sprintf(sql, "insert into ObjInfo(Name,Num,unitPrice,totalPrice) values(\"%s\",2,5.2,10.4)", "sugar");
ret = sqlite3_exec(db, sql, NULL, NULL, &error);
if (ret != SQLITE_OK)
{
printf("表格插入1失败");
getchar();
return;
}
memset(sql,0,sizeof(sql));
sprintf(sql, "insert into ObjInfo(Name,Num,unitPrice,totalPrice) values(\"%s\",2, 3.2, 6.4)", "bread");
ret = sqlite3_exec(db, sql, NULL, NULL, &error);
if (ret != SQLITE_OK)
{
printf("表格插入2失败");
getchar();
return;
}
//4.读取数据
memset(sql,0,sizeof(sql));
sprintf(sql, "select * from ObjInfo");
ret = sqlite3_get_table(db, sql, &result, &nrow, &ncolumn, &error);
printf("row=%d,column=%d\n", nrow, ncolumn);
if (ret==SQLITE_OK)
{
//输出每一列的名字信息
//result前几个保存的是列的名字信息
for (j = 0; j < ncolumn; j++)
{
printf("%s\t", result[j]);
}
printf("\n");
for (i = 0; i < nrow;i++)//遍历行
{
for (j = 0; j < ncolumn; j++)
{
//显示数据
printf("%s\t", result[(i+1)*ncolumn+j]);
}
printf("\n");
}
}
//5.删除数据
memset(sql,0,sizeof(sql));
sprintf(sql, "delete from ObjInfo where name= \"bread\"");
ret = sqlite3_exec(db, sql, NULL, NULL, &error);
if (ret != SQLITE_OK)
{
printf("删除数据失败");
getchar();
return;
}
//6.读取数据
memset(sql,0,sizeof(sql));
sprintf(sql, "select * from ObjInfo");
ret = sqlite3_get_table(db, sql, &result, &nrow, &ncolumn, &error);
printf("row=%d,column=%d\n", nrow, ncolumn);
if (ret==SQLITE_OK)
{
//输出每一列的名字信息
//result前几个保存的是列的名字信息
printf("\nread again....\n");
for (j = 0; j < ncolumn; j++)
{
printf("%s\t", result[j]);
}
printf("\n");
for (i = 0; i < nrow;i++)//遍历行
{
for (j = 0; j < ncolumn; j++)
{
//显示数据
printf("%s\t", result[(i+1)*ncolumn+j]);
}
printf("\n");
}
}
//关闭数据库
sqlite3_close(db);
system("pause");
}
即在SQL3语句使用时sprintf对两种情况无法调用:
- 字符串中用单引号扩着
- 二进制数据(结构体)
最优方法:
使用上述介绍的查询方法,因为?通配符可以表示任何数据类型
其中sqlite3_prepare_v2函数声明为如下:
SQLITE_API int sqlite3_prepare_v2(
sqlite3 db, / Database handle */
const char zSql, / SQL statement, UTF-8 encoded /
int nByte, / Maximum length of zSql in bytes. */
sqlite3_stmt *ppStmt, / OUT: Statement handle */
const char *pzTail / OUT: Pointer to unused portion of zSql */
);
对stmt绑定函数的声明:
//二进制数据
SQLITE_API int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void()(void));
SQLITE_API int sqlite3_bind_double(sqlite3_stmt*, int, double);
SQLITE_API int sqlite3_bind_int(sqlite3_stmt*, int, int);
SQLITE_API int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
SQLITE_API int sqlite3_bind_null(sqlite3_stmt*, int);
//文本字符串类型
SQLITE_API int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void()(void));
SQLITE_API int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void()(void));
SQLITE_API int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
SQLITE_API int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);
返回值问题
#define SQLITE_OK 0 /* Successful result /
/ beginning-of-error-codes /
#define SQLITE_ERROR 1 / SQL error or missing database /
#define SQLITE_INTERNAL 2 / Internal logic error in SQLite /
#define SQLITE_PERM 3 / Access permission denied /
#define SQLITE_ABORT 4 / Callback routine requested an abort /
#define SQLITE_BUSY 5 / The database file is locked /
#define SQLITE_LOCKED 6 / A table in the database is locked /
#define SQLITE_NOMEM 7 / A malloc() failed /
#define SQLITE_READONLY 8 / Attempt to write a readonly database /
#define SQLITE_INTERRUPT 9 / Operation terminated by sqlite3_interrupt()/
#define SQLITE_IOERR 10 / Some kind of disk I/O error occurred /
#define SQLITE_CORRUPT 11 / The database disk image is malformed /
#define SQLITE_NOTFOUND 12 / Unknown opcode in sqlite3_file_control() /
#define SQLITE_FULL 13 / Insertion failed because database is full /
#define SQLITE_CANTOPEN 14 / Unable to open the database file /
#define SQLITE_PROTOCOL 15 / Database lock protocol error /
#define SQLITE_EMPTY 16 / Database is empty /
#define SQLITE_SCHEMA 17 / The database schema changed /
#define SQLITE_TOOBIG 18 / String or BLOB exceeds size limit /
#define SQLITE_CONSTRAINT 19 / Abort due to constraint violation /
#define SQLITE_MISMATCH 20 / Data type mismatch /
#define SQLITE_MISUSE 21 / Library used incorrectly /
#define SQLITE_NOLFS 22 / Uses OS features not supported on host /
#define SQLITE_AUTH 23 / Authorization denied /
#define SQLITE_FORMAT 24 / Auxiliary database format error /
#define SQLITE_RANGE 25 / 2nd parameter to sqlite3_bind out of range /
#define SQLITE_NOTADB 26 / File opened that is not a database file /
#define SQLITE_ROW 100 / sqlite3_step() has another row ready /
#define SQLITE_DONE 101 / sqlite3_step() has finished executing *
QT中使用SQL3
参考博文:
https://blog.csdn.net/weixin_41656968/article/details/80473137