代码:
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
void db_insert(void);
void db_delete(void);
void db_update(void);
void db_select(void);
int callback(void *arg, int columns, char** column_text, char** column_name)
{
// void *arg: sqlite3_exec函数的第四个参数;
// int columns: 查询结果的列数;
// char** column_text: 指向表格中记录的内容;记录存储在指针数组中;
// char** column_name: 指向表格的列名;列名存储在指针数组中;
int count = *((int *)arg);
if(0 == count)
{
for(int i = 0; i < columns; i++)
{
printf("%s\t", column_name[i]);
}
printf("\n========================\n");
}
for(int i = 0; i < columns; i++)
{
printf("%s\t", column_text[i]);
}
printf("\n");
*((int *)arg) = count+1;
return 0;
}
sqlite3 *db = NULL;
char sql[128] = "";
char *errmsg = NULL;
int main(int argc, char const *argv[])
{
/*创建数据库*/
if(sqlite3_open("./dict.db", &db) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_open::%d::%s\n", sqlite3_errcode(db), sqlite3_errmsg(db));
return -1;
}
/*创建表格*/
strcpy(sql, "create table if not exists dict (id int primary key, english char, chinese char);");
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_exec::%d::%s __%d__\n", sqlite3_errcode(db), sqlite3_errmsg(db), __LINE__);
return -1;
}
int opcode = -1;
while(1)
{
printf("1::增\n");
printf("2::删\n");
printf("3::改\n");
printf("4::查\n");
printf("9::退出\n");
printf("请输入操作码::");
scanf("%d", &opcode);getchar();
switch(opcode)
{
case 1: /*增*/db_insert(); break;
case 2: /*删*/db_delete(); break;
case 3: /*改*/db_update(); break;
case 4: /*查*/db_select(); break;
case 9: /*退出*/goto END;
default : break;
}
printf("按任意键继续运行::");fflush(stdout);
while(getchar() != 10);
system("clear");
}
END:
/*删除数据表*/
strcpy(sql, "DROP TABLE dict;");
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_exec::%d::%s __%d__\n", sqlite3_errcode(db), sqlite3_errmsg(db), __LINE__);
return -1;
}
/*关闭数据库*/
if(sqlite3_close(db) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_close::%d::%s\n", sqlite3_errcode(db), sqlite3_errmsg(db));
return -1;
}
printf("系统退出...\n");
return 0;
}
void db_insert(void)
{
bzero(sql, sizeof(sql));
int data1 = -1; char data2[32] = ""; char data3[32] = "";
printf("示例::insert into 表名[dict] values(数据1[int], 数据2[char], 数据3[char]);\n");
printf("请输入插入SQL语句的参数::\n");
printf("数据1[int]>>>");scanf("%d", &data1);getchar();
printf("数据2[char]>>>");scanf("%s", data2);getchar();
printf("数据3[char]>>>");scanf("%s", data3);getchar();
sprintf(sql, "INSERT INTO dict VALUES (%d, \"%s\", \"%s\");", data1, data2, data3);
printf("您生成的sql语句::%s\n", sql);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_exec::%d::%s __%d__\n", sqlite3_errcode(db), sqlite3_errmsg(db), __LINE__);
return ;
}
return ;
}
void db_delete(void)
{
bzero(sql, sizeof(sql));
char data1[32] = "";
printf("示例::delete from 表名 where 字段=数值;\n");
printf("请输入删除SQL语句的参数::\n");
printf("where[字段=数值]>>>");scanf("%s", data1);getchar();
sprintf(sql, "DELETE FROM dict WHERE %s;", data1);
printf("您生成的sql语句::%s\n", sql);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_exec::%d::%s __%d__\n", sqlite3_errcode(db), sqlite3_errmsg(db), __LINE__);
return ;
}
return ;
}
void db_update(void)
{
bzero(sql, sizeof(sql));
char data1[32] = ""; char data2[32] = "";
printf("示例::update 表名 set 字段名=数值 where 字段=数值;\n");
printf("请输入修改SQL语句的参数::\n");
printf("set[字段名=数值]>>>");scanf("%s", data1);getchar();
printf("where[字段=数值]>>>");scanf("%s", data2);getchar();
sprintf(sql, "UPDATE dict SET %s WHERE %s;", data1, data2);
printf("您生成的sql语句::%s\n", sql);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_exec::%d::%s __%d__\n", sqlite3_errcode(db), sqlite3_errmsg(db), __LINE__);
return ;
}
return ;
}
void db_select(void)
{
char data1[32] = ""; char data2[32] = "";
printf("示例1::select * from 表名;\n");
printf("示例2::select * from 表名 where 限制条件;\n");
printf("示例3::select 字段名1, 字段名2 from 表名 where 限制条件;\n");
printf("请输入查询SQL语句的参数::\n");
printf("字段名1, 字段名2, ...[缺省为*]>>>");scanf("%s", data1);getchar();
printf("限制条件[缺省为空]>>>");fgets(data2, sizeof(data2), stdin);
if(0 == strcmp(data2, "\n"))
{
sprintf(sql, "SELECT %s FROM dict;", data1);
}
else
{
sprintf(sql, "SELECT %s FROM dict WHERE %s;", data1, data2);
}
printf("您生成的sql语句::%s\n", sql);
int count = 0;
if(sqlite3_exec(db, sql, callback, &count, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_exec::%d::%s __%d__\n", sqlite3_errcode(db), sqlite3_errmsg(db), __LINE__);
return ;
}
return ;
}