这几天遇到了一个问题就是在insert以及update数据的时候,总是生成一个.db-journal文件,重新启动程序后,该文件消失,前面添加修改的数据没有成功保存。关于如何解决这个问题,网上也没有人给出详细答案,但经过几天的分析,试验,最后成功了。不知道对所有出现的这个问题都能应付的过来,解决的方法很简单,如下:程序中使用了sqlite3_stmt *stmt;然后通过rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);将sql语句解析到stmt中,然后对数据库操作完成之后,没有释放stmt,当我调用sqlite3_finalize(stmt);将stmt释放之后,上面的问题不在出现了。对此sqlite中还有一对匹配的用法。如下:sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );sqlite3_free_table( azResult ); 下面是一些sql语句的用法。sprintf(sql, "INSERT INTO \"Phonebook\" VALUES((select min(ID)-1 from Phonebook where ID>1 and ID-1 not in (select ID from Phonebook)), \'%s\', \'%s\');", p->list[1], p->list[2]); char sql1[100];sprintf(sql1, "select * from Phonebook where Username like \'%s%%\'order by Username;", buffer);上面是关于like匹配的用法,Username like '王%' 以及 Username like '王_' ,%用来匹配任意字符,_用来匹配一个字符。而且还要注意的是,c语言中 %的转义方式是%%,而不是\% 。 上面一句话是从csdn上问的结果,插入的数据,是按照ID号从小的开始插入。也就是将中间的空缺补上。#include #include #include "sqlite3.h" #define _DEBUG_int main( void ){sqlite3 *db=NULL;char *zErrMsg = 0;int rc;///open(create) databaserc = sqlite3_open("phonebook.db", &db);if( rc ){fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));sqlite3_close(db);return 0;}else printf("You have opened a sqlite3 database named zieckey.db successfully!\nCongratulations! Have fun ! ^-^ \n");///create tablechar *sql = " CREATE TABLE Call_Record(\ Status CHAR(10),\ Username VARCHAR(20),\ Phonenubmer VARCHAR(20),\ Time timestamp,\ PRIMARY KEY (Status, Username)\ );" ;//上面一句用来定义两个主键sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );///create table2sql = " CREATE TABLE Phonebook(\ Username VARCHAR(20) PRIMARY KEY,\ Phonenubmer VARCHAR(20)\ );" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );///insert databasesql = "INSERT INTO \"Call_Record\" VALUES('missed', 'Tom', '15908079860',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('answered', 'Jack', '15908079861',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('dialed','Merry', '15908079863',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('dialed', 'Zjf', '15908079864',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('dialed', 'Qcx', '15908079865',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('dialed', 'MM', '15908079866',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('answered', 'JJ', '15908079867',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('answered', 'GG', '15908079868',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('answered', 'DD', '15908079869',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('answered', 'Father', '15908079870',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('missed', 'Mather', '15908079871',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );///insert database table twosql = "INSERT INTO \"Phonebook\" VALUES('Tom', '15908079860');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('Jack', '15908079861');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('Merry', '15908079863');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('Zjf', '15908079864');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('Qcx', '15908079865');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('MM', '15908079866');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('JJ', '15908079867');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('GG', '15908079868');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('DD', '15908079869');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('Father', '15908079870');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('Mather', '15908079871');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );int nrow = 0, ncolumn = 0;char **azResult;///select databasesql = "SELECT * FROM Call_Record ";//sql = "select * from Phonebook order by Username"; //默认为升序asc//sql = "select * from Phonebook order by Username desc"; //降序//sql = "select * from Phonebook order by (case Username when \"\" then \'zzzzz\' else Username end);";//sql = "select * from Phonebook order by (case Username when null then \'zzzzz\' else Username end);";//上面一句是处理空值情况(null 与""),sqlserver默认空值为最小,使用case可以改变它查询的值。sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );int i = 0 ;printf( "row:%d column=%d \n" , nrow , ncolumn );printf( "\nThe result of querying is : \n" );for( i=0 ; iprintf( "azResult[%d] = %s\n", i , azResult[i] );///select databasesql = "SELECT * FROM Phonebook ";sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );printf( "row:%d column=%d \n" , nrow , ncolumn );printf( "\nThe result of querying is : \n" );for( i=0 ; iprintf( "azResult[%d] = %s\n", i , azResult[i] );/*///delete databasesql = "DELETE FROM Call_Record WHERE Username = \'GG\' ;" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );///update databasechar sql1[100];sprintf(sql1,"UPDATE Call_Record SET Phonenubmer = \'%s\' WHERE Username = \'%s\'", "10111", "MM");sqlite3_exec( db , sql1 , 0 , 0 , &zErrMsg );*////select database desc (jiangxupailie)///if no desc is default(asc)sql = "SELECT * FROM Call_Record order by Username desc";sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );printf( "\n\n\n\nrow:%d column=%d " , nrow , ncolumn );printf( "\nAfter deleting , the result of querying is : \n" );for( i=0 ; iprintf( "azResult[%d] = %s\n", i , azResult[i] );///free azResultsqlite3_free_table( azResult );///close dbsqlite3_close(db);return 0;}另外一种查询获取列值的方法: int i, j, rc, ncols; char *sql; char *tail; char buff[100]; sqlite3_stmt *stmt; ///about dababase select sql = "select * from Phonebook order by (case Username when \"\" then \'zzzzz\' else Username end);"; rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail); if (rc !=SQLITE_OK) { break; } rc = sqlite3_step(stmt); //取得数据表中一行的数据 ncols = sqlite3_column_count(stmt); //获取数据表有多少列 while(rc==SQLITE_ROW) { for (j = 0; j sprintf(buff,"%s",sqlite3_column_text(stmt, j)); //获取一行中每一列的数据 printf("%s\n",buff); } rc= sqlite3_step(stmt); //获取下一行数据 } sqlite3_finalize(stmt);