sqlite的终端命令行操作方式
- 常用命令:
- a) .q 退出
- b) .tables 查看创建了多少表
- c) .databases列出所有数据库
- d) .help帮助
- e) –表示注释
- 建表 create table table_name(field1,field2,…);
- 建索引 create index index_name on table_name(field1,…);
- 新建记录 insert into table_name values(data1,’data2’,….)
- 查询记录 select field1,field2…. From table_name where <condition> order by
- 更改记录 update table_name set field1=’data1’,field2=data2,… where <condition>
- 删除记录 delete from table_name where <condition>
- g) 导出、导入数据(需要在终端命令行)
sqlite的C/C++ API 使用 及 实例
- 2)执行 sql 语句
#include<stdio.h>
#include <stdlib.h>
#include "sqlite3.h"
#define _DEBUG_
intmain( void )
{
sqlite3 *db=NULL;
char *zErrMsg = 0;
intrc;
rc = sqlite3_open("zieckey.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.dbsuccessfully!\nCongratulations! Have fun ! ^-^ \n");
//创建一个表,如果该表存在,则不创建,并给出提示信息,错误信息存储在 zErrMsg 中
char *sql = " CREATE TABLE SensorData(
ID INTEGER PRIMARY KEY,
SensorID INTEGER,
SiteNum INTEGER,
Time VARCHAR(12),
SensorParameter REAL
);" ;
sqlite3_exec( db,sql,&zErrMsg );
#ifdef _DEBUG_
printf("zErrMsg = %s \n",zErrMsg);
#endif
//插入数据
sql = "INSERT INTO \"SensorData\" VALUES(NULL,1,'200605011206',18.9 );" ;
sqlite3_exec( db,&zErrMsg );
sql = "INSERT INTO \"SensorData\" VALUES(NULL,23,45,'200605011306',16.4 );" ;
sqlite3_exec( db,34,15.4 );" ;
sqlite3_exec( db,&zErrMsg );
int nrow = 0,ncolumn = 0;
char **azResult; //二维数组存放结果
//查询数据
sql = "SELECT * FROM SensorData ";
sqlite3_get_table( db,&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 ; i<( nrow + 1 ) * ncolumn ; i++ )
printf( "azResult[%d] = %s\n",i,azResult[i] );
//删除数据
sql = "DELETE FROM SensorData WHERE SensorID = 1 ;" ;
sqlite3_exec( db,&zErrMsg );
#ifdef _DEBUG_
printf("zErrMsg = %s \n",zErrMsg);
#endif
sql = "SELECT * FROM SensorData ";
sqlite3_get_table( db,&zErrMsg );
printf( "\n\n\n\nrow:%d column=%d ",ncolumn );
printf( "\nAfter deleting,the result of querying is : \n" );
for( i=0 ; i<( nrow + 1 ) * ncolumn ; i++ )
printf( "azResult[%d] = %s\n",azResult[i] );
//释放掉 azResult 的内存空间
sqlite3_free_table( azResult );
#ifdef _DEBUG_
printf("zErrMsg = %s \n",zErrMsg);
#endif
sqlite3_close(db);//关闭数据库
return 0;
}
运行:gcc sqlist_tst.c -g -o sqlist_tst -lsqlite3
链接:http://hi.baidu.com/yzh03106/blog/item/96d5eeef7b69ee3eacafd585.html