linux 下SQLITE3.5的安装与使用

1.源码安装步骤如下：
1)wget http://www.sqlite.org/sqlite-3.5.6.tar.gz
2)tar -xzvf sqlite-3.5.6.tar.gz
3)cd sqlite-3.5.6
4)./configure --disable-tcl ##加上这个选项则不需要TCL,否则在2.4内核上编译通不过
5)make
6)make install

得到的几个文件在：/usr/local/lib  libsqlite3.a  libsqlite3.la  libsqlite3.so  libsqlite3.so.0  libsqlite3.so.0.8.6  pkgconfig

/usr/local/include  /usr/local/bin

2.测试是否安装成功

# cd /usr/lcoal/sqlite-3.5.6

# ./sqlite3 text.db

提示错误如下：

./***: error while loading shared libraries: libsqlite3.so.0: cannot open shared object file: No such file or directory

 

 

讹谬起因：

这个起因即便在利用过程厉行的时候找不到libsqlite3.so.0库文件的路径。

处理措施：

措施

修正Linux系统文件/etc/ld.so.conf,增加sqlite库文件位置 /usr/local/lib 到此文件里面。然后重起可能运行ldconfig 重新读取搭配。

然后搞定了！！！

 

下面实例完整通过。

 

#include <stdlib.h>
#include "sqlite3.h" 
#define _DEBUG_

int main( void )
{
　　　sqlite3 *db=NULL;
　　　char *zErrMsg = 0;

　　　int rc;

///open(create) database
　　　rc = 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 table
　　　　char *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 table2
　　　　sql = " CREATE TABLE Phonebook(\
      Username VARCHAR(20) PRIMARY KEY,\
      Phonenubmer VARCHAR(20)\
      );" ;
　　　　sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );


///insert database
sql = "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 two
sql = "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 database
sql = "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 ; i<( nrow + 1 ) * ncolumn ; i++ )
printf( "azResult[%d] = %s\n", i , azResult[i] );

///select database
sql = "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 ; i<( nrow + 1 ) * ncolumn ; i++ )
printf( "azResult[%d] = %s\n", i , azResult[i] );
/*
///delete database
sql = "DELETE FROM Call_Record WHERE Username = \'GG\' ;" ;
sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );

///update database
char 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 ; i<( nrow + 1 ) * ncolumn ; i++ )
printf( "azResult[%d] = %s\n", i , azResult[i] );


///free azResult
sqlite3_free_table( azResult );

///close db
sqlite3_close(db);
return 0;

}