2024/4/26作业

 数据库增删改查

  1 #include <code.h>
  2 
  3 int do_select(sqlite3 *db);
  4 int select_callback(void *arg,int column,char **column_text,char**column_name);
  5 
  6 int do_insert(sqlite3 *db);
  7 int do_delete(sqlite3 *db);
  8 int do_update(sqlite3 *db);
  9 
 10 
 11 int main(int argc, const char *argv[])
 12 {
 13     sqlite3 *db;
 14     if(sqlite3_open("./sq.db",&db)!=SQLITE_OK)
 15     {
 16         fprintf(stderr,"sqlite3_open:%s,errcode:%d __%d__\n",\
 17                 sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
 18         return -1;
 19     }
 20     printf("sq.db open success! \n");
 21 
 22 
 23     char sql[128] = "create table if not exists stu (id int,name char,score float);";
 24     char *errmsg = NULL;
 25 
 26     if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
 27     {
 28         fprintf(stderr,"sqlite3_exec:%s __%d__\n",errmsg,__LINE__);
 29         return -1;
 30     }
 31     printf("创建表格成功\n");
 32     int choose;
 33 
 34     while(1)
 35     {
 36         system("clear");
 37         printf("--------------------\n");
 38         printf("------1、插入 ------\n");
 39         printf("------2、删除 ------\n");
 40         printf("------3、修改 ------\n");
 41         printf("------4、查询 ------\n");
 42         printf("------5、退出 ------\n");
 43         printf("--------------------\n");
 44         printf("请输入>>>>>");
 45         scanf("%d",&choose);
 46         while(getchar()!=10);
 47         switch(choose)
 48         {
 49         case 1:
 50             do_insert(db);
 51             break;
 52         case 2:
 53             do_delete(db);
 54             break;
 55         case 3:
 56             do_update(db);
 57             break;
 58         case 4:
 59             do_select(db);
 60             break;
 61         case 5:
 62             return 0;
 63         defalut:
 64             printf("输入错误！\n"); printf("%s",sql);
 65 
 66         }
 67 
 68         printf("按下回车清屏>>>>\n");
 69         while(getchar()!=10);
 70     }
 71 
 72 
 73     if(sqlite3_close(db)!=SQLITE_OK)
 74     {
 75         fprintf(stderr,"sqlite3_close:%s,errcode:%d __%d__\n",\
 76                 sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
 77         return -1;
 78     }
 79     printf("%s",sql);
 80     printf("sq.db close success! \n");
 81     return 0;
 82 }
 83 
 84 
 85 int do_insert(sqlite3 *db)
 86 {
 87     char sql[128] = "";
 88     char *errmsg = NULL;
 89 
 90     int id;
 91     char name[20];
 92     float score;
 93     printf("请按以下格式输入插入数据\nid name score\n");
 94     scanf("%d %s %f",&id,name,&score);
 95 
 96     sprintf(sql,"insert into stu values (%d,'%s',%f);",id,name,score);
 97 
 98     if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
 99     {
100         fprintf(stderr,"sqlite3_exec:%s __%d__\n",errmsg,__LINE__);
101         return -1;
102     }
103     printf("插入数据成功\n");
104     return 0;
105 }
106 
107 
108 int do_delete(sqlite3 *db)
109 {
110     char sql[128] = "";
111     char *errmsg = NULL;
112 
113     char limit[64];
114 
115     printf("请输入删除数据的限制条件>>>");
116     scanf("%s",limit);
117     sprintf(sql,"delete from stu where %s;",limit);
118     if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
119     {
120         fprintf(stderr,"sqlite3_exec:%s __%d__\n",errmsg,__LINE__);
121         return -1;
122     }
123     printf("删除数据成功\n");
124     return 0;
125 }
126 
127 int do_update(sqlite3 *db)
128 {
129     char sql[128];
130     char *errmsg = NULL;
131 
132     char update[64];
133     char limit[64];
134 
135     printf("请按照一下格式输入修改内容和限制条件\n<update>  <limiti>\n");
136 
137     scanf("%s %s",update,limit);
138 
139     sprintf(sql,"update stu set %s where %s;",update,limit);
140 
141     if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
142     {
143         fprintf(stderr,"sqlite3_exec:%s __%d__\n",errmsg,__LINE__);
144         return -1;
145     }
146     printf("修改数据成功\n");
147     return 0;
148 
149 }
150 
151 int do_select(sqlite3*db)
152 {
153     char sql[128] = "select *from stu;";
154     char *errmsg = NULL;
155     int flag = 0;
156 
157     if(sqlite3_exec(db,sql,select_callback,&flag,&errmsg)!=SQLITE_OK)
158     {
159         fprintf(stderr,"sqlite3_exec:%s __%d__\n",errmsg,__LINE__);
160         return -1;
161     }
162     printf("查询成功\n");
163     return 0;
164 
165 }
166 
167 int select_callback(void*arg,int column,char **column_text,char**column_name)
168 {
169     if(0==*(int*)arg)
170     {
171         for(int i=0;i<column;i++) 
172         {
173             printf("%s\t",column_name[i]);
174         }
175         putchar(10);
176         *(int*)arg = 1;
177     }
178 
179     for(int i= 0;i<column;i++)
180     {
181         printf("%s\t",column_text[i]);
182     }
183     putchar(10);
184     return 0;
185 }