/* 项目
名称：用c语言动态操作sqilite3
数据库 * * 项目成员：张双喜 * * 编译环境：gcc * * 项目
功能： * 1、动态创建表 * 2、动态实现对表的基本操作（增、删、改、查） * 3、清空表数据、
删除表（附加
功能） * * 项目总结： * 1、涉及的技术： * * 1、int
sqlite3_exec(
sqlite3*,const char *
sql,
sqlite_callback,void*,char**); * 欲实现动态操作
数据库，必须动态构造
sql语句，构造
sql语句可以用strcat
函数，逐步拼接，直至完整构造出
sql语句。 * * 2、欲实现简单
菜单选项
效果，须分离出实现每个子
功能的
函数，在switch中，选择的
调用 * * 3、 * * 2、意外收获： * * 1、想输入一条带有空格的字符串，不能用scanf
函数，应为scanf
函数遇到空格、换行、回车
自动结束输入，所以scanf不能达到预期
效果 * ，
解决该问题可以使用gets
函数，但是gets是个危险
函数，c语言不提倡使用，在使用gets
函数前，一定要清空缓冲区，否则。会出现 * 意想不到的输入结果，这就是先前没有清空缓冲区造成的。 * * 2、linux c 清空缓冲区的问题： * 个人见解： * 经测试，linux c 使用fflush(stdin)无法清空缓冲区，使用fflush(stdin)后，再使用gets
函数，gets还是会从缓冲区中读取东西， * 因为缓冲区经fflush(stdin)没有被清空； * 经过在网上找资料，得知setbuf(stdin,NULL);这样一个
函数，经测试，清理缓冲区成功。 * * 简言之：linux c 清空缓冲区的
函数：setbuf(stdin,NULL); * * 3、编程中问题及其
解决方法： * * 1、更新
数据库update正确使用
方法： * update table_name set name = 'new_name',sex = 'new_sex' where id = id_val; //是正确的 * 上句中的,不能换成and， * 即： * update table_name set name = 'new_name' and sex = 'new_sex' where id = id_val; //是
错误的 * * 2、字符串的问题： * char * p = "hello world!"; //该字符串是只读的不能
修改字符串，不能改写，不能strcat(p,"jack"); * * char str[] = "hello world!" //这样可以通过指针或不通过指针对字符串进行任意操作 * char *p; * p = str; * * 3、字符串清理零： * char str[10]; * * memset(str,sizeof(str)); * * * * * 张双喜 * 2010.9.29 14:39 * * * */ #include <st
dio.h> #include <stdlib.h> #include <string.h> #include "
sqlite3.h" /*该结构体是方便对表内数据操作*/ typedef struct table_column { char column_name[100]; //列名 char column_type[200]; //列的数据类型 }COLUMN; void db_create(
sqlite3 * db,char * pTable_name,int column_num,COLUMN column[]); int rscallback(void * p,int column,char ** column_val,char ** column_name); void db_show(
sqlite3 * db,char * pTable_name); void db_insert(
sqlite3 * db,COLUMN column[]); void db_update(
sqlite3 * db,int column_Num,COLUMN column[]); void db_delete(
sqlite3 * db,char * pTable_name); void db_empty(
sqlite3 * db,char * pTable_name); void db_drop(
sqlite3 * db,char *pTable_name); int main(int argc,char* argv[]) {
sqlite3 * db; int empty = 1; int ret = 0; char * errmsg = NULL; int choice = -1; char table_name[20]; char * pTable_name; int column_num; int i; COLUMN column[10]; pTable_name = table_name; ret =
sqlite3_open("student.db",&db); if(ret !=
sqlITE_OK) { perror("slqite3_open"); exit(1); } while(choice != 0) { printf("please input your choise:/n"); printf("------------------------------------------------------------------/n"); printf("|0.exit|1.create|2.show|3.insert|4.update|5.delete|6.empty|7.drop|/n"); printf("------------------------------------------------------------------/n"); scanf("%d",&choice); switch(choice) { case 0: break; case 1: printf("we will create a table for you,please input the name of your table:/n"); scanf("%s",pTable_name); printf("please input the number of column:/n"); scanf("%d",&column_num); printf("please input column_name column_type:/n"); for(i = 0; i < column_num; i++) { scanf("%s %s",column[i].column_name,column[i].column_type); } db_create(db,table_name,column_num,column); break; case 2: db_show(db,table_name); break; case 3: db_insert(db,column); break; case 4: db_update(db,column); break; case 5: db_delete(db,table_name); break; case 6: db_empty(db,table_name); break; case 7: db_drop(db,table_name); break; default: printf("your choice is not exist!/n"); // printf("please input a number again:/n"); // scanf("%d",&choice); exit(0); } } return 0; } void db_create(
sqlite3 * db,COLUMN column[]) { int ret,i; char * errmsg = NULL; char
sql[200]; char * p
sql; p
sql =
sql; strcpy(p
sql,"create table "); strcat(p
sql,pTable_name); strcat(p
sql,"("); strcat(p
sql,column[0].column_name); strcat(p
sql," "); strcat(p
sql,column[0].column_type); strcat(p
sql," primary key autoincrement"); strcat(p
sql,","); for(i = 1; i < column_num-1; i++) { strcat(p
sql,column[i].column_name); strcat(p
sql," "); strcat(p
sql,column[i].column_type); strcat(p
sql,"); } strcat(p
sql,column[column_num-1].column_name); strcat(p
sql,column[column_num-1].column_type); strcat(p
sql,");"); printf("/n
sqlite > %s/n/n",p
sql); ret =
sqlite3_exec(db,p
sql,NULL,&errmsg); if(ret !=
sqlITE_OK) { printf("error:%d:%s/n",ret,errmsg); exit(1); } return; } int rscallback(void * p,char ** column_name) { int i; *(int *)p = 0; for(i = 0; i < column; i++) { printf("%s > %s/t/t",column_name[i],column_val[i]); } printf("/n"); return 0; } void db_show(
sqlite3 * db,char * pTable_name) { int ret = 0; char * errmsg = NULL; int empty = 1; char
sql[200]; char * p
sql; printf("/n"); p
sql =
sql; strcpy(p
sql,"select * from "); strcat(p
sql,pTable_name); strcat(p
sql,";"); ret =
sqlite3_exec(db,rscallback,&empty,errmsg); exit(1); } if(empty) { printf("the table is empty!/n"); } printf("/n"); return; } void db_insert(
sqlite3 * db,COLUMN column[]) { int ret = 0; char * errmsg = NULL; char
sql[200]; char * p
sql; char tmp[20]; int i; p
sql =
sql; strcpy(p
sql,"insert into "); strcat(p
sql,"("); for(i = 1; i < column_num-1; i++) { strcat(p
sql,"); strcat(p
sql," "); } strcat(p
sql,") "); strcat(p
sql,"values("); for(i = 1; i < column_num-1; i++) { printf("please input %s/n",column[i].column_name); if(strcmp(column[i].column_type,"text") == 0) { strcat(p
sql,"/'"); memset(tmp,sizeof(tmp)); scanf("%s",tmp); strcat(p
sql,"/'"); strcat(p
sql,"); } else { memset(tmp,"); strcat(p
sql," "); } } printf("please input %s/n",column[column_num-1].column_name); if(strcmp(column[column_num-1].column_type,"text") == 0) { scanf("%s",tmp); strcat(p
sql,"/'"); strcat(p
sql,");"); } else { scanf("%s",");"); } printf("/n
sqlite > %s/n/n",p
sql); ret =
sqlite3_exec(db,errmsg); exit(1); } return; } void db_update(
sqlite3 * db,COLUMN column[]) { int ret = 0; char * errmsg = NULL; char
sql[200]; char * p
sql; char new_val[20]; char new_id[3]; int i; p
sql =
sql; strcpy(p
sql,"update "); strcat(p
sql," set "); for(i = 1; i < column_num-1; i++) { strcat(p
sql," = "); if(strcmp(column[i].column_type,"text") ==0) { strcat(p
sql,"/'"); memset(new_val,sizeof(new_val)); printf("please input a new %s/n",column[i].column_name); scanf("%s",new_val); strcat(p
sql,"); } else { memset(new_val,"); } } strcat(p
sql,column[i].column_name); strcat(p
sql," = "); if(strcmp(column[column_num-1].column_type,"text") ==0) { strcat(p
sql,"/'"); memset(new_val,sizeof(new_val)); printf("please input a new %s/n",column[i].column_name); scanf("%s",new_val); strcat(p
sql," "); } else { memset(new_val," "); } strcat(p
sql,"where id = "); printf("please input the id that you want to change its value:/n"); scanf("%s",new_id); strcat(p
sql,";"); printf("
sqlite > %s/n/n",p
sql); ret =
sqlite3_exec(db,errmsg); exit(1); } return; } void db_delete(
sqlite3 * db,char * pTable_name) { int ret = 0; char * errmsg = NULL; char
sql[200]; char * p
sql; char tmp_name[20]; char tmp_id[3]; p
sql =
sql; strcpy(p
sql,"delete from "); strcat(p
sql," where id = "); printf("please input a id that you want to delete:/n"); scanf("%s",tmp_id); strcat(p
sql,";"); printf("/n
sqlite > %s/n/n",errmsg); exit(1); } return; } void db_empty(
sqlite3 * db,char * pTable_name) { int ret = 0; char * errmsg = NULL; char
sql[200]; char * p
sql; p
sql =
sql; strcpy(p
sql,errmsg); exit(1); } return; } void db_drop(
sqlite3 * db,"drop table "); strcat(p
sql,errmsg); exit(1); } return; }
