C语言实现数据的增删改查
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <ctype.h>
void show_table(sqlite3 *db, char *zErrMsg);
void insert_data(sqlite3 *db, char *zErrMsg);
void update_data(sqlite3 *db, char *zErrMsg);
void delete_data(sqlite3 *db, char *zErrMsg);
void union_find(sqlite3 *db, char *zErrMsg);
static int callback(void *data, int argc, char **argv, char **azColName);
int main(int argc, char **argv)
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
sqlite3_open("/home/sumu/test.db", &db);
int flag = 0;
while(1)
{
printf("1.遍历数据表\n");
printf("2.插入数据\n");
printf("3.更新数据\n");
printf("4.删除数据\n");
printf("5.联合查询\n");
printf("6.退出\n");
printf("请输入选项:");
scanf("%d", &flag);
getchar();
switch(flag)
{
case 1:
show_table(db, zErrMsg);
break;
case 2:
insert_data(db, zErrMsg);
break;
case 3:
update_data(db, zErrMsg);
break;
case 4:
delete_data(db, zErrMsg);
break;
case 5:
union_find(db, zErrMsg);
break;
case 6:
printf("退出程序!\n");
sqlite3_close(db);
exit(0);
default:
printf("输入错误,请重新输入!\n");
break;
}
}
sqlite3_close(db);
}
void show_table(sqlite3 *db, char *zErrMsg)
{
char *sql = (char *)malloc(1024);
char *data = (char *)malloc(1024);
char *table = (char *)malloc(1024);
char *wdata = (char *)malloc(1024);
printf("select data(%%s) from table(%%s) where (%%s)\n");
printf("输入表名:\n");
fgets(table, 1024, stdin);
table[strcspn(table, "\n")] = '\0';//去掉最后的回车
printf("输入你要查询的数据名:[多个数据用逗号分隔](*:表示所有数据)\n");
fgets(data, 1024, stdin);
data[strcspn(data, "\n")] = '\0';//去掉最后的回车
if(strncmp(data, "*",1) == 0)
{
sprintf(sql, "select * from %s", table);
}
else
{
printf("输入条件:[列名 = 值](输入null为不添加条件)\n");
fgets(wdata, 1024, stdin);
wdata[strcspn(wdata, "\n")] = '\0';//去掉最后的回车
if(strcmp(wdata, "null") == 0)
{
sprintf(sql, "select %s from %s", data ,table);
}
else
{
sprintf(sql, "select %s from %s where %s", data, table,wdata);
}
}
printf("sql语句:%s\n", sql);
int ret = sqlite3_exec(db, sql, callback, (void*)"查询数据", &zErrMsg);
if( ret )
{
fprintf(stderr, "查询数据失败: %s\n", sqlite3_errmsg(db));
}
free(sql);free(data);free(table);free(wdata);
}
void insert_data(sqlite3 *db, char *zErrMsg)
{
char *sql = (char *)malloc(1024);
char *table = (char *)malloc(1024);
char *data = (char *)malloc(1024);
printf("insert into table(%%s) values(%%s)\n");
printf("输入表名:\n");
fgets(table, 1024, stdin);
table[strcspn(table, "\n")] = '\0';//去掉最后的回车
for (char *p = table; *p != '\0'; p++) {
*p = tolower(*p);
}
if(strcmp(table, "student") == 0)
{
printf("id(int),name(varchar),age(float),sex(varchar),score(float)\n");
printf("值(用逗号分隔):\n");
fgets(data, 1024, stdin);
data[strcspn(data, "\n")] = '\0';//去掉最后的回车
sprintf(sql, "insert into %s(id,name,age,sex,score) values(%s)", table, data);
}
else if(strcmp(table, "teacher") == 0)
{
printf("id(int),name(varchar),age(float),sex(varchar),salary(float)\n");
printf("值(用逗号分隔):\n");
fgets(data, 1024, stdin);
data[strcspn(data, "\n")] = '\0';//去掉最后的回车
sprintf(sql, "insert into %s(id,name,age,sex,salary) values(%s)", table, data);
}
int ret = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
if( ret )
{
fprintf(stderr, "查询数据失败: %s\n", sqlite3_errmsg(db));
}
free(sql);free(data);free(table);
}
void update_data(sqlite3 *db, char *zErrMsg)
{
char *sql = (char *)malloc(1024);
char *table = (char *)malloc(1024);
char *wdata = (char *)malloc(1024);
char *wdata2 = (char *)malloc(1024);
printf("update table(%%s) set (%%s) where (%%s)\n");
printf("输入表名:\n");
fgets(table, 1024, stdin);
table[strcspn(table, "\n")] = '\0';//去掉最后的回车
for (char *p = table; *p != '\0'; p++) {
*p = tolower(*p);
}
if(strcmp(table, "student") == 0)
{
printf("id(int),name(varchar),age(float),sex(varchar),score(float)\n");
}
else if(strcmp(table, "teacher") == 0)
{
printf("id(int),name(varchar),age(float),sex(varchar),salary(float)\n");
}
printf("输入要修改的列名和值(列名 = 值):\n");
fgets(wdata, 1024, stdin);
wdata[strcspn(wdata, "\n")] = '\0';//去掉最后的回车
printf("wdata:%s\n", wdata);
printf("输入条件:[列名 = 值]\n");
fgets(wdata2, 1024, stdin);
wdata2[strcspn(wdata2, "\n")] = '\0';//去掉最后的回车
sprintf(sql, "update %s set %s where %s", table, wdata, wdata2);
printf("sql语句:%s\n", sql);
int ret = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
if( ret )
{
fprintf(stderr, "更新数据失败: %s\n", sqlite3_errmsg(db));
}
free(sql);free(table);free(wdata);free(wdata2);
}
void delete_data(sqlite3 *db, char *zErrMsg)
{
char *sql = (char *)malloc(1024);
char *table = (char *)malloc(1024);
char *wdata = (char *)malloc(1024);
printf("delete from table(%%s) where (%%s)\n");
printf("输入表名:\n");
fgets(table, 1024, stdin);
table[strcspn(table, "\n")] = '\0';//去掉最后的回车
for (char *p = table; *p != '\0'; p++) {
*p = tolower(*p);
}
if(strcmp(table, "student") == 0)
{
printf("id(int),name(varchar),age(float),sex(varchar),score(float)\n");
}
else if(strcmp(table, "teacher") == 0)
{
printf("id(int),name(varchar),age(float),sex(varchar),salary(float)\n");
}
printf("输入条件:[列名 = 值](输入null为删除所有数据)\n");
fgets(wdata, 1024, stdin);
wdata[strcspn(wdata, "\n")] = '\0';//去掉最后的回车
if(strcmp(wdata, "null") == 0)
{
sprintf(sql, "delete from %s", table);
}
else
{
sprintf(sql, "delete from %s where %s", table, wdata);
}
printf("sql语句:%s\n", sql);
int ret = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
if( ret )
{
fprintf(stderr, "删除数据失败: %s\n", sqlite3_errmsg(db));
}
free(sql);free(table);free(wdata);
}
void union_find(sqlite3 *db, char *zErrMsg)
{
char *sql = (char *)malloc(1024);
char *table1 = (char *)malloc(1024);
char *table2 = (char *)malloc(1024);
char *data1 = (char *)malloc(1024);
char *data2 = (char *)malloc(1024);
printf("select data(%%s) from table(%%s) where (%%s)\n");
printf("输入表名1:\n");
fgets(table1, 1024, stdin);
table1[strcspn(table1, "\n")] = '\0';//去掉最后的回车
for (char *p = table1; *p != '\0'; p++) {
*p = tolower(*p);
}
printf("输入表名2:\n");
fgets(table2, 1024, stdin);
table2[strcspn(table2, "\n")] = '\0';//去掉最后的回车
for (char *p = table2; *p != '\0'; p++) {
*p = tolower(*p);
}
printf("输入条件1:[列名 = 值]\n");
fgets(data1, 1024, stdin);
data1[strcspn(data1, "\n")] = '\0';//去掉最后的回车
printf("输入条件2:[列名 = 值]\n");
fgets(data2, 1024, stdin);
data2[strcspn(data2, "\n")] = '\0';//去掉最后的回车
sprintf(sql, "select * from %s where %s union select * from %s where %s", table1, data1, table2, data2);
printf("sql语句:%s\n", sql);
int ret = sqlite3_exec(db, sql, callback, (void*)"查询数据", &zErrMsg);
if( ret )
{
fprintf(stderr, "查询数据失败: %s\n", sqlite3_errmsg(db));
}
free(sql);free(table1);free(table2);free(data1);free(data2);
}
static int callback(void *data, int argc, char **argv, char **azColName)
{
int i;
fprintf(stderr, "%s:\n", (const char*)data);
for(i=0; i<argc; i++){
printf("%s = %s\t", azColName[i], argv[i] ? argv[i] : "NULL");
//azColName是列名,argv是每一行的列值,argc是列数
//循环打印每一行的列名和值
}
printf("\n");
return 0;
}