SQLite一些技巧总结.docx
- 文档编号:7584688
- 上传时间:2023-01-25
- 格式:DOCX
- 页数:22
- 大小:19.27KB
SQLite一些技巧总结.docx
《SQLite一些技巧总结.docx》由会员分享,可在线阅读,更多相关《SQLite一些技巧总结.docx(22页珍藏版)》请在冰豆网上搜索。
SQLite一些技巧总结
判断数据库是否为空:
selectcount(*)fromsqlite_master
sqlite的两个问题:
update速度和并发操作
tmStart=time(0);
db.execDML("begintransaction;");
for(i=0;i sprintf(traTabSQL,"updatetraTabsettrDes='%s'wheretrNo=%d;", recDesStr, i); db.execDML(traTabSQL); } db.execDML("committransaction;"); tmEnd=time(0); update的花了很长时间,单条记录大概1024字节,也用了事务,请问这是为啥呢。 还有个问题就是同时打开一个db文件,在一个地方做了更改,另外一个用户查询的结果没有变更? 如何查询记录条数? c语言中,可以通过如下方式获得: ret=sqlite3_get_table(db,"selectcount(*)fromtable1wherefield=1",&dbresult,&nrom,&ncol,&errmsg); recordnum=atoi(dbresult[1]); sqlite3_step后,如何知道查询返回的行数 sqlite3_prepare(db,"select*fromlist;",-1,&stat,0); sqlite3_step(stat); 调用sqlite3_data_count(stat);和sqlite3_column_count(stat);返回的都是列数。 azResult是一个二级指针,它的值是有sqlite3_get_table()这个函数生成,我可以定义另外一个二级指针来保存这个值,不用的时候再调用sqlite3_free_table()来释放它。 SQLITE里查询日期与日期相减 selectdatetime('2006-10-1700: 20: 00','+1hour','-12minute'); 结果: 2006-10-1701: 08: 00 selectdate('2006-10-17','+1day','+1year'); 结果: 2007-10-18 创建了一个新表后,若没有数据,怎样得到表里的字段名啊? 你用 constchar*sqlite3_column_database_name(sqlite3_stmt*,int); constvoid*sqlite3_column_database_name16(sqlite3_stmt*,int); constchar*sqlite3_column_table_name(sqlite3_stmt*,int); constvoid*sqlite3_column_table_name16(sqlite3_stmt*,int); constchar*sqlite3_column_origin_name(sqlite3_stmt*,int); constvoid*sqlite3_column_origin_name16(sqlite3_stmt*,int); 这个系列的函数就行,不然就从sqlite_master表中查一下 事务处理: sqlite3_exec(db,"BEGIN;",0,0,&zErrMsg); sqlite3_exec(db,"COMMIT;",0,0,&zErrMsg); intresult; result=sqlite3_exec(db,"begintransaction",0,0,&zErrorMsg);//开始一个事务 result=sqlite3_exec(db,"committransaction",0,0,&zErrorMsg);//提交事务 result=sqlite3_exec(db,"rollbacktransaction",0,0,&zErrorMsg);//回滚事务 在SQLite中如果想处理时间的应该使用字段的默认值就可以了 CURRENT_TIMEwillgeneratethecurrentlocaltimeinANSI/ISOtimeformat (HH: MM: SS).CURRENT_DATEwillgeneratethecurrentdate(inYYYY-MM-DDformat).CURRENT_TIMESTAMP willproduceacombinationofthesetwo(inYYYY-MM-DDHH: MM: SSformat).Forexample: CREATETABLEtimes(idint, timeNOTNULLDEFAULTCURRENT_DATE timeNOTNULLDEFAULTCURRENT_TIME, timeNOTNULLDEFAULTCURRENT_TIMESTAMP); INSERTINTOtimes (1); INSERTINTOtimes (2); SELECT*FROMStimes; iddatetimetimestamp ------------------------------------------ 12006-03-1523: 30: 252006-03-1523: 30: 25 22006-03-1523: 30: 402006-03-1523: 30: 40 Thesedefaultscomeinquitehandyfortablesthatneedtologortimestampevents. 时间处理: SELECT*FROMEIT_SCHEDULEWHERESERVICE_ID=? ANDTS_ID=? ANDON_ID =? ANDSTART_TIME_UTC_M=? ANDSTART_TIME_UTC_L>=? ORDERBYSTART_TIME_UTC_LLIMIT? 1 其中问号代表的值由sqlite3_bind_int()函数得到? 值传递是没有问题的。 就是结果变多了。 其中: START_TIME_UTC_M表示的是当天日期 SQLiteDBMS 如果你有着方面的需求你可以去尝试一下SQLiteDBMS,它是一个韩国的开源项目,实现了C/S模式的SQLite支持。 具体特性如下: TCP/IPServerdaemon EmbeddedHTTPserver HTTPrequest/XMLresponse Preparedstatement Multislavereplication ManageExtendedSQL Querycache WebDAV Monitoring SecuritySSL HTTPbaseauthentication Tablelevelaccesscontrol Library/Toolslibsqlited(C/C++) SQLiteDBMSClient(GUI) HTMLClient datetime(''now'')怎么显示当地时间? 呵呵! 我发现了: datetime('now','localtime'); 如何在VC下编译SQLITE 在projectsetting->link lib中加入sqlite3.lib #include响应的头文件即可 Sqlite数据库学习例子,方便新人。 #include #include #include #include staticint callback(void*NotUsed,intargc,char**argv,char**azColName) { inti; for(i=0;i if(argv[i]==NULL){ printf("%s=NULL\n",azColName[i]); }else{ printf("%s=%s\n",azColName[i],argv[i]); } } printf("\n"); return(0); } int main(intargc,char**argv) { sqlite3*db; char*zErrMsg=0; intrc; if(argc! =3){ fprintf(stderr,"Usage: %sDATABASESQL-STATEMENT\n",argv[0]); exit(EXIT_FAILURE); } rc=sqlite3_open(argv[1],&db); if(rc! =SQLITE_OK){ fprintf(stderr,"Can'topendatabase: %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(EXIT_FAILURE); } rc=sqlite3_exec(db,argv[2],callback,0,&zErrMsg); if(rc! =SQLITE_OK){ fprintf(stderr,"SQLerror: %s\n",zErrMsg); } sqlite3_close(db); exit(EXIT_SUCCESS); } /*实用sqlite3_exec()的例子*/ #include #include #include #include #include int main(intargc,char**argv) { sqlite3*db; intrc,len,i,cols,type; char*next; sqlite3_stmt*st; if(argc! =3){ fprintf(stderr,"Usage: %sDATABASESQL-STATEMENT\n",argv[0]); exit(EXIT_FAILURE); } rc=sqlite3_open(argv[1],&db); if(rc! =SQLITE_OK){ fprintf(stderr,"Can'topendatabase: %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(EXIT_FAILURE); } len=strlen(argv[2]); rc=sqlite3_prepare(db,argv[2],len,&st,&next); if(rc! =SQLITE_OK){ fprintf(stderr,"Erroronsqlite3_prepare: %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(EXIT_FAILURE); } for(;;){ rc=sqlite3_step(st); if(rc==SQLITE_ROW){ cols=sqlite3_column_count(st); for(i=0;i type=sqlite3_column_type(st,i); switch(type){ caseSQLITE_INTEGER: printf("%s[%d]=%d\n",sqlite3_column_name(st,i), i,sqlite3_column_int(st,i)); break; caseSQLITE_FLOAT: printf("%s[%d]=%f\n",sqlite3_column_name(st,i), i,sqlite3_column_double(st,i)); break; caseSQLITE_TEXT: printf("%s[%d]='%s'\n",sqlite3_column_name(st,i), i,sqlite3_column_text(st,i)); break; caseSQLITE_BLOB: printf("%s[%d]=BLOB[%dbytes]\n", sqlite3_column_name(st,i), i,sqlite3_column_bytes(st,i)); break; caseSQLITE_NULL: printf("%s[%d]=NULL\n",sqlite3_column_name(st,i), i); break; default: printf("UNDEFINEDColumn[%d]\n",i); break; } } printf("\n"); }elseif(rc==SQLITE_DONE){ break; }else{ fprintf(stderr,"Erroronsqlite3_step: %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(EXIT_FAILURE); } } rc=sqlite3_finalize(st); if(rc! =SQLITE_OK){ fprintf(stderr,"Erroronsqlite3_finalize: %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(EXIT_FAILURE); } sqlite3_close(db); exit(EXIT_SUCCESS); } /*Anexampleusingwildcard&bind*/ #include #include #include #include #include #defineBUF_LEN(1000) #defineSQL_ST"SELECT*FROMlistWHEREnameLIKE? ;" /*assumetheDBincludesatablenamed'list'consistsof'name'andetc.*/ int main(intargc,char**argv) { sqlite3*db; intrc,len,i,cols,type; char*next,buf[BUF_LEN]; sqlite3_stmt*st; if(argc! =2){ fprintf(stderr,"Usage: %sDATABASE\n",argv[0]); exit(EXIT_FAILURE); } rc=sqlite3_open(argv[1],&db); if(rc! =SQLITE_OK){ fprintf(stderr,"Can'topendatabase: %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(EXIT_FAILURE); } len=strlen(SQL_ST); rc=sqlite3_prepare(db,SQL_ST,len,&st,&next); if(rc! =SQLITE_OK){ fprintf(stderr,"Erroronsqlite3_prepare: %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(EXIT_FAILURE); } printf("Pleaseinputnamepattern.\n"); if(fgets(buf,BUF_LEN,stdin)==NULL){ fprintf(stderr,"Somethingoccurredonstdin\n"); exit(EXIT_FAILURE); } len=strlen(buf); if(buf[len-1]=='\n'){ len--; buf[len]='\0'; } rc=sqlite3_bind_text(st,1,buf,len,SQLITE_TRANSIENT); if(rc! =SQLITE_OK){ fprintf(stderr,"Erroronsqlite3_bind_text: %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(EXIT_FAILURE); } for(;;){ rc=sqlite3_step(st); if(rc==SQLITE_ROW){ cols=sqlite3_column_count(st); for(i=0;i type=sqlite3_column_type(st,i); switch(type){ caseSQLITE_INTEGER: printf("%s[%d]=%d\n",sqlite3_column_name(st,i), i,sqlite3_column_int(st,i)); break; caseSQLITE_FLOAT: printf("%s[%d]=%f\n",sqlite3_column_name(st,i), i,sqlite3_column_double(st,i)); break; caseSQLITE_TEXT: printf("%s[%d]='%s'\n",sqlite3_column_name(st,i), i,sqlite3_column_text(st,i)); break; caseSQLITE_BLOB: printf("%s[%d]=BLOB[%dbytes]\n", sqlite3_column_name(st,i), i,sqlite3_column_bytes(st,i)); break; caseSQLITE_NULL: printf("%s[%d]=NULL\n",sqlite3_column_name(st,i), i); break; default: printf("UNDEFINEDColumn[%d]\n",i); break; } } printf("\n"); }elseif(rc==SQLITE_DONE){ break; }else{ fprintf(stderr,"Erroronsqlite3_step: %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(EXIT_FAILURE); } } rc=sqlite3_finalize(st); if(rc! =SQLITE_OK){ fprintf(stderr,"Erroronsqlite3_finalize: %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(EXIT_FAILURE); } sqlite3_close(db); exit(EXIT_SUCCESS); } /*InputdatafromCSVfile*/ /*Schema-CREATETABLElist(idINTEGERPRIMARYKEY,nameTEXT);*/ /*CSV-``id,name''*/ #include #include #include #include #include #defineBUF_LEN(1000) #defineSQL_INSERT"INSERTINTOlist(id,name)valueS(? ? );" int main(intargc,char**argv) { sqlite3*db; intrc,len,i,cols,type; char*next,buf[BUF_LEN]; sqlite3_stmt*st; char*err_msg; intid; if(argc<2){ fprintf(stderr,"Usage: %sdatabase\n",argv[0]); exit(EXIT_FAILURE); } rc=sqlite3_open(argv[1],&db); if(rc! =SQLITE_OK){ fprintf(stderr,"Can'topendatabase: %s\n",sqlite3_errmsg(db)); sqlite3_close(db); exit(EXIT_FAILURE); } rc=sqlite3_exec(db,"BEGINTRANSACTION",NULL,NULL,&err_msg); if(rc! =SQLITE_OK){ fprintf(stderr,"ErroronBEGINTRANSACTION: %s\n",err_msg); sqlite3_close(db); exit(EXIT_FAILURE); } len=strlen(SQL_INSERT); rc=sqlite3_prepare(db,SQL_INSERT,len,&st,&next); if(rc! =SQLITE_OK){ fprintf(stderr,"Erroronsqlite3_prepare: %s\n",sqlite3_errmsg(db)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLite 一些 技巧 总结