用sqlite实现学生成绩数据库的增删改查用sqlite实现学生成绩数据库的增删改查用sqlite实现学生成绩数据库的增删改查用...

#include <stdio.h> #include <stdlib.h> #include <string.h> #include <sqlite3.h> #define BUFFSIZE 256 #define OPTIONSIZE 20 char *zErrMsg = 0; char strDB[BUFFSIZE]; static void tableCreat(sqlite3 *db) { int ret; //create the table sprintf(strDB,"CREATE TABLE STUSCORE(\ ID INTEGER PRIMARY KEY ,\ STUID INTEGER,\ NAME VARCHAR(12),\ SCORE INTEGER\ );"); ret = sqlite3_exec(db, strDB ,0 , 0, &zErrMsg); if(ret!= 0) { printf("create the table failed!\n"); exit(-1); } printf("create the table success!\n"); } static void showTable(sqlite3 *db) { int nrow=1, ncolumn=1 ; char **azResult ; int i,j=0; strcpy(strDB, "SELECT * FROM STUSCORE") ; sqlite3_get_table(db, strDB, &azResult, &nrow, &ncolumn, &zErrMsg) ; printf("nrow : %d ncolumn :%d \n", nrow, ncolumn) ; for (i = 0; i < (nrow + 1) * ncolumn; i++) { j++; printf("%7s ", azResult[i]) ; if (j == ncolumn) { printf("\n"); j = 0; } } sqlite3_free_table(azResult); } static void search(sqlite3 *db) { char *stuidc = "STUID"; char *namec = "NAME"; char *scorec = "SCORE"; char **azResult; int nrow = 0, ncolumn = 0; char optionAttrib[OPTIONSIZE]; char optionValue[OPTIONSIZE]; int i = 0 ; int j = 0; printf("Welcome to search!\n"); while(1) { printf("Option(STUID,NAME,SCORE):"); scanf("%s",optionAttrib); if(!strncasecmp(stuidc,optionAttrib,sizeof(optionAttrib))) { printf("Option(num):"); scanf("%s",optionValue); break; } else if(!strncasecmp(namec,optionAttrib,sizeof(optionAttrib))) { printf("Option(name):"); scanf("%s",optionValue); break; } else if(!strncasecmp(scorec,optionAttrib,sizeof(optionAttrib))) { printf("Option(num(0~100)):"); scanf("%s",optionValue); break; } printf("input error!Please try again!\n"); } sqlite3_snprintf(sizeof(strDB), strDB, "SELECT * FROM STUSCORE WHERE %s = '%s'", optionAttrib,optionValue); sqlite3_get_table( db , strDB , &azResult , &nrow , &ncolumn , &zErrMsg ); printf("%s\n",sqlite3_errmsg(db)); printf("nrow : %d ncolumn :%d \n", nrow, ncolumn) ; for (i = 0; i < (nrow+1)*ncolumn; i++) { j++; printf("%7s ", azResult[i]) ; if (j == ncolumn) { printf("\n"); j=0; } } if(0 == nrow) { printf("there is no record you want!\n"); } sqlite3_free_table( azResult ); #ifdef _DEBUG_ printf("zErrMsg = %s \n", zErrMsg); #endif } static void _delete(sqlite3 *db) { char *zErrMsg = 0; char *stuidc = "STUID"; char *namec = "NAME"; char *scorec = "SCORE"; char **azResult; int nrow = 0, ncolumn = 0; memset(strDB,0,sizeof(strDB)); int i = 0 ; int j = 0; int IDnum; int ret; char optionAttrib[OPTIONSIZE]; char optionValue[OPTIONSIZE]; printf("Welcome to delete!\n"); while(1) { printf("Option(STUID,NAME,SCORE):"); scanf("%s",optionAttrib); if(!strncasecmp(stuidc,optionAttrib,sizeof(optionAttrib))) { printf("Option(num):"); scanf("%s",optionValue); break; } if(!strncasecmp(namec,optionAttrib,sizeof(optionAttrib))) { printf("Option(name):"); scanf("%s",optionValue); break; } if(!strncasecmp(scorec,optionAttrib,sizeof(optionAttrib))) { printf("Option(num(0~100)):"); scanf("%s",optionValue); break; } printf("input error!Please try again!\n"); } sqlite3_snprintf(sizeof(strDB), strDB, "SELECT * FROM STUSCORE WHERE %s = '%s'", optionAttrib,optionValue); ret = sqlite3_get_table( db , strDB , &azResult , &nrow , &ncolumn , &zErrMsg ); if(ret != 0) { printf("SELECT ERROR"); exit(-1); } printf("nrow : %d ncolumn :%d \n", nrow, ncolumn) ; for (i = 0; i < (nrow+1)*ncolumn; i++) { j++; printf("%7s ", azResult[i]) ; if (j == ncolumn) { printf("\n"); j=0; } } if(0 == nrow) { printf("there is no record you want!\n"); } else { printf("%d groups are there,choose the ID\n",nrow); scanf("%d",&IDnum); memset(strDB,0,sizeof(strDB)); sqlite3_snprintf(sizeof(strDB), strDB, "DELETE FROM STUSCORE WHERE ID = %d ;", IDnum); sqlite3_exec( db , strDB , 0 , 0 , &zErrMsg ); showTable(db); } } static void insert(sqlite3 *db) { int ret; int stuid; char name[20]; memset(name,0,sizeof(name)); int score; printf("Welcome to insert!\n"); printf("Option(stuid,name,score):"); scanf("%d%s%d",&stuid,name,&score); memset(strDB,0,sizeof(strDB)); sprintf(strDB, "INSERT INTO \"STUSCORE\" VALUES(NULL,%d,'%s',%d)",stuid,name,score); if((ret = sqlite3_exec(db,strDB,0,0,&zErrMsg))!= 0) { printf("Insert error\n"); exit(-1); } printf("Insert Success!\n"); } static sqlite3* open_db(const char *filename) { sqlite3 *db = NULL; int rc; rc = sqlite3_open(filename, &db); if(rc!=SQLITE_OK) { fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } return db; } static void modify(sqlite3 *db) { int ret; int stuid; char optionAttrib[OPTIONSIZE]; char optionValue[OPTIONSIZE]; printf("Welcome to modify"); showTable(db); printf("choose ID,Attrib and New Value\n"); scanf("%d%s%s",&stuid,optionAttrib,optionValue); memset(strDB,0,sizeof(strDB)); sqlite3_snprintf(sizeof(strDB), strDB, "UPDATE STUSCORE SET %s='%s' WHERE ID=%d ;", optionAttrib,optionValue,stuid); printf("%s",strDB); if((ret = sqlite3_exec(db,strDB,0,0,&zErrMsg))!= 0) { printf("modify error\n"); exit(-1); } printf("modify Success!\n"); showTable(db); } int main() { char option[OPTIONSIZE]; char optionValue[OPTIONSIZE]; sqlite3 *db; char* searchc = "search"; char* deletec = "delete"; char* insertc = "insert"; char* modifyc = "modify"; char* showTablec = "showTable"; char _switch[2]; db = open_db("STUSCORE.db"); while(1) { printf("Welcome to Student Score DataBase\n"); printf("Option(search|delete|insert|modify|showTable)"); scanf("%s",option); if(!strncasecmp(searchc,option,sizeof(option))) { search(db); } if(!strncasecmp(deletec,option,sizeof(option))) { _delete(db); } if(!strncasecmp(insertc,option,sizeof(option))) { insert(db); } if(!strncasecmp(modifyc,option,sizeof(option))) { modify(db);