转自:http://blog.csdn.net/jarvischu/article/details/6714190
感谢作者。。。
1. sqlite3 安装
1.1. 下载sqlite3源码
www.sqlite3.org
下载 sqlite-autoconf-3070701.tar.gz
1.2. 解压
将下载的 sqlite-autoconf-3070701.tar.gz 解压,得到sqlite-autoconf-3070701 文件夹
1.3. 编译源码(参照解压文件夹下的install文件)
$ cd sqlite-autoconf-3070701 #进入文件夹
$ ./configure
$ make
$ sudo make install #注意一定要root权限
1.4. 查看安装情况
在/usr/local/include 下:sqlite3.h sqlite3ext.h
在/usr/local/bin 下:sqlite3
在/usr/local/lib 下:pkgconfig(文件夹) libsqlite3.a libsqlite3.la
libsqlite3.so libsqlite3.so.0 libsqlite3.so.0.8.6
2. sqlite3 c编程 之 环境搭建(Codeblocks)
2.1. 新建 c project
打开codeblocks,File--> new --> project-->console application-->c
2.2. 引入库
project-->build options --> linker setting
在linker library 下点击 add, 选择 /usr/local/lib/libsqlite3.so 文件
2.3. 添加.h文件
在程序开头#include <sqlite3.h>
2.4. 至此环境搭建完成。
在程序中,就可以使用sqlite3的c语言接口了
如:sqlite3_open()
sqlite3_close()
3. 通讯录程序
一个简单的通讯录程序,主要是为了练手,熟悉Slqite3的使用。
数据库:contact.db,只有一张表info,保存联系人信息(姓名,年龄,关系,手机号)
程序中bug较多,但却涵盖了sqlite3的数据库常用操作:创建数据库、创建表、增、删、改、查等。
- /************************************************************************
- * 名 称:contact_using_sqlite3.c
- * 功 能:一个简单的通讯录程序,主要是为了练习sqlite3的使用
- * 描 述:使用sqlite3,建立数据库contact,其中有表一张
- info(name varchar(10), age smallint, relation varchar(10), phone varchar(11))
- 包括如下功能:
- 1. 查看通讯录
- 2. 增加联系人
- 3. 删除联系人
- 4. 修改联系人信息
- 5. 查找联系人
- * 作 者:JarvisChu
- * 时 间:2011-8-22
- * 修 订:2011-8-24,完成修改和删除;
- ************************************************************************/
- #include <stdio.h>
- #include <stdlib.h>
- #include <string.h>
- #include <sqlite3.h>
- sqlite3* db = NULL; //the database
- //show the menu on the screen,and return user's choice
- int showMenu(){
- int choice = 0;
- while(1){
- printf("*****************************************************\n");
- printf(" Welcome \n");
- printf("1. Display All 2. Add Contact\n");
- printf("3. Delete Contact 4. Alter Contact\n");
- printf("5. Find Contact\n");
- printf("Your choice is:");
- scanf("%d",&choice);
- if(choice == 1 || choice == 2 ||\
- choice == 3 || choice == 4 ||\
- choice == 5){
- return choice;
- }
- else{
- printf("Wrong! Again!\n");
- }
- }
- return 0;
- }
- //show all records in db
- void displayAll(){
- int i = 0;
- int j = 0;
- int index = 0;
- int ret = 0;
- int row = 0;
- int column = 0;
- char* sql = NULL;
- char** resultSet = NULL; //store the query result
- sql = (char*)malloc(sizeof(char)*20);
- strcpy(sql,"select * from info;");
- ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
- if(ret != SQLITE_OK){
- fprintf(stderr,"select records err\n");
- }
- printf("There are %d Contact:\n",row);
- index = 0;
- for(i=0;i<=row;i++){
- for(j=0;j<column;j++){
- printf("%-11s",resultSet[index++]);
- }
- printf("\n");
- }
- sqlite3_free_table(resultSet);
- free(sql);
- }
- //add contact
- void addContact(){
- int ret = 0;
- char* name = NULL;
- int age = 0;
- char* relation = NULL;
- char* phone = NULL;
- char* sql = NULL;
- name = (char*)malloc(sizeof(char)*10);
- relation = (char*)malloc(sizeof(char)*10);
- phone = (char*)malloc(sizeof(char)*12);
- sql = (char*)malloc(sizeof(char)*64);
- printf("input (name age relation phone):");
- scanf("%s %d %s %s",name,&age,relation,phone);
- //printf("%s, %d, %s,%s\n",name,age,relation,phone);
- sprintf(sql,"insert into info values('%s',%d,'%s','%s');",name,age,relation,phone);
- //printf("%s\n",sql);
- ret = sqlite3_exec(db,sql,0,0,0);
- if(ret != SQLITE_OK){
- printf("failed!\n");
- }
- else{
- printf("ok!\n");
- }
- free(name);
- free(relation);
- free(phone);
- free(sql);
- }
- //find Contact
- void findContact(){
- int i,j,index;
- int ret = 0;
- int row = 0;
- int column = 0;
- char* name = NULL;
- char* sql = NULL;
- char** resultSet = NULL;
- name = (char*)malloc(sizeof(char)*10);
- printf("Input the name you want to find:");
- scanf("%s",name);
- sql = (char*)malloc(sizeof(char)*64);
- sprintf(sql,"select * from info where name = '%s'",name);
- ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
- if(ret != SQLITE_OK){
- fprintf(stderr,"select err:%s\n",sqlite3_errmsg(db));
- return;
- }
- index = 0;
- if(row>0){
- for(i=0;i<=row;i++){
- for(j=0;j<column;j++){
- printf("%-11s",resultSet[index++]);
- }
- printf("\n");
- }
- }
- else{
- printf("no such person!\n");
- }
- }
- //alertContact()
- void alterContact(){
- //first,find the contact info to be altered.
- int i,j,index;
- int ret = 0;
- int row = 0;
- int column = 0;
- int age = 0;
- char* name = NULL;
- char* relation = NULL;
- char* phone = NULL;
- char* sql = NULL;
- char** resultSet = NULL;
- name = (char*)malloc(sizeof(char)*10);
- printf("Input the name you want to alter:");
- scanf("%s",name);
- sql = (char*)malloc(sizeof(char)*128);
- sprintf(sql,"select * from info where name = '%s'",name);
- ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
- if(ret != SQLITE_OK){
- fprintf(stderr,"select err:%s\n",sqlite3_errmsg(db));
- return;
- }
- index = 0;
- if(row>0){
- for(i=0;i<=row;i++){
- for(j=0;j<column;j++){
- printf("%-11s",resultSet[index++]);
- }
- printf("\n");
- }
- sqlite3_free_table(resultSet);
- //exist ,then alter
- relation = (char*)malloc(sizeof(char)*10);
- phone = (char*)malloc(sizeof(char)*12);
- printf("input the new data (age relation phone):");
- scanf("%d %s %s",&age,relation,phone);
- //printf(" %d, %s,%s\n",name,age,relation,phone);
- sprintf(sql,"update info set age=%d,relation='%s',phone='%s' where name='%s';",age,relation,phone,name);
- //printf("%s\n",sql);
- ret = sqlite3_exec(db,sql,0,0,0);
- if(ret != SQLITE_OK){
- printf("failed!\n");
- }
- else{
- printf("ok!\n");
- }
- free(relation);
- free(phone);
- }
- else{
- printf("no such person!\n");
- }
- free(sql);
- free(name);
- }
- //delete Contact
- void deleteContact(){
- int ret = 0;
- char* name = NULL;
- char* sql = NULL;
- name = (char*)malloc(sizeof(char)*10);
- sql = (char*)malloc(sizeof(char)*64);
- printf("Input the name of contact you want to delete:");
- scanf("%s",name);
- sprintf(sql,"delete from info where name='%s';",name);
- //to be simple, there will be no warning if the contact does not exist
- ret = sqlite3_exec(db,sql,0,0,0);
- if(ret != SQLITE_OK){
- printf("delete err:%s",sqlite3_errmsg(db));
- }
- else{
- printf("ok!");
- }
- free(name);
- free(sql);
- }
- int main()
- {
- int ret = 0;
- int choice = 0;
- int ch = 0;
- char* errmsg = NULL;
- char* sql = NULL;
- //open the db if exist or create it
- ret = sqlite3_open("contact.db",&db);
- if(ret){
- fprintf(stderr,"Cannot open database:%s\n",sqlite3_errmsg(db));
- sqlite3_close(db);
- exit(1);
- }
- else{
- printf("Open database successfully...\n");
- }
- //create the table info if not exists
- sql = (char*)malloc(sizeof(char)*128);//
- //strcpy(sql,);
- //printf("Copy sql successfully\n");
- ret = sqlite3_exec(db,"create table if not exists info(\
- name varchar(10) primary key, \
- age smallint, \
- relation varchar(10), \
- phone varchar(11));",0,0,&errmsg);
- if(ret != SQLITE_OK){
- //printf("Create table error\n");
- fprintf(stderr,"Create table err:%s\n",sqlite3_errmsg(db));
- }
- //printf("Create table successfully\n");
- //insert some initial records,
- //it will cause a err if not the frist time,but that does not matter
- strcpy(sql,"insert into info values('zhu',22,'本人','15109217871');");
- ret = sqlite3_exec(db,sql,0,0,&errmsg);
- if(ret != SQLITE_OK){
- fprintf(stderr,"Insert record err:%s\n",sqlite3_errmsg(db));
- }
- free(sql);
- //printf("Insert record successfully\n");
- //main menu
- while(1){
- choice = showMenu(); //show the menu and get the user's choose
- switch(choice){
- case 1:
- displayAll(); //show all records in db
- break;
- case 2:
- addContact();
- break;
- case 3:
- deleteContact();
- break;
- case 4:
- alterContact();
- break;
- case 5:
- findContact();
- break;
- default:
- break;
- }
- //if back to main menu or not
- printf("\nBack to Menu 1(yes) / 0(no)?");
- scanf("%d",&ch);
- if(ch == 0){
- break;
- }
- //printf("\33[2J");
- system("clear");
- }
- sqlite3_close(db);
- return 0;
- }