22.11.28 增删改

 完成数据库的增删改

ubuntu@ubuntu:~/yuyu/yu/14/3$ gcc 01_sqlite3.c -lsqlite3
ubuntu@ubuntu:~/yuyu/yu/14/3$ ./a.out
database open success
create table success

--------------------------
------------1. 增---------
------------2. 删---------
------------3. 改---------
------------4. 查---------
-----------5. 退出--------
--------------------------
请输入>>>3
输入任意字符清屏>>>^C
ubuntu@ubuntu:~/yuyu/yu/14/3$ cat 01_sqlite3.c 
#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>

//1. 增
void do_insert(sqlite3* db)
{
	char sql[128] = "";
	int x = 0;
	char y[20] = "";
	float z = 0;
	char *errmsg = NULL;
	printf("请输入学生信息：学号， 姓名， 成绩\n");
	scanf("%d %s %f",&x, y, &z);
	while(getchar() != 10);
    sprintf(sql,"INSERT INTO stu VALUE (%d, '%s', %f)", x, y, z); 

	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
		return ;
	}
	printf("插入成功\n");
}

//2. 删
void do_delete(sqlite3* db)
{
	char sql[128] = "";
	int x = 0;
	char *errmsg = NULL;
	printf("请输入要删除的学号\n");
	scanf("%d",&x);
	while(getchar() != 10);
	sprintf(sql,"DELETE FROM stu WHERE x = %d", x);
	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
		return ;
	}
	printf("删除成功\n");
	
}

//3. 改
void do_update(sqlite3* db)
{
	char sql[128] = "";
		 int x = 0;
		 char y[20] = "";
		 float z = 0;
	char *errmsg = NULL;
	printf("请输入要改的学号，姓名，成绩\n");
	scanf("%d %s %f",&x, y, &z);
	while(getchar() != 10);
	if(strcmp(y,"name") == 0)
	{
		sprintf(sql,"UPDATE stu SET z = %f WHERE z = %s", z,y);
	}
	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
		return ;
	}
	printf("更改成功\n");
}



int main(int argc, const char *argv[])
{
	//创建并打开数据库
	sqlite3 *db = NULL;
	if(sqlite3_open("./my.db", &db) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_open:%s\n", __LINE__, sqlite3_errmsg(db));
		fprintf(stderr, "line:%d sqlite3_open:%d\n", __LINE__, sqlite3_errcode(db));
		return -1;
	}
	printf("database open success\n");

	//创建一张表格
	//sql语句，在数据库中怎么写，在代码中就怎么写，不确定sql语句是否是正确的，
	//可以将该sql语句在数据库中执行一遍，看看是不是正确的；
	char sql[128] = "create table if not exists stu (id int,name char,score float);";
	//char* ptr = "create table stu (id int, name char, score float);";
	char* errmsg = NULL;

	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
		return -1;
	}
	printf("create table success\n");

	char c = 0;
	while(1)
	{
		system("clear");  //清屏
		printf("--------------------------\n");
		printf("------------1. 增---------\n");
		printf("------------2. 删---------\n");
		printf("------------3. 改---------\n");
		printf("------------4. 查---------\n");
		printf("-----------5. 退出--------\n");
		printf("--------------------------\n");

		printf("请输入>>>");
		c = getchar();
		while(getchar()!=10);

		switch(c)
		{
		case '1':       //增 ----->  全字段插入
			do_insert(db);
			break;
		case '2':       //删 ------> 通过id号去删除
			do_delete(db);
			break;
		case '3':       //改 ------> 通过id号修改
			//do_update();
			break;
		case '4':
			//do_select();
			break;
		case '5':
			goto END;
		default:
			printf("输入错误, 请重新输入\n");
		}

		printf("输入任意字符清屏>>>");
		while(getchar()!=10);
		}


END:
	//关闭数据库
	if(sqlite3_close(db) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_open:%s\n", __LINE__, sqlite3_errmsg(db));
		fprintf(stderr, "line:%d sqlite3_open:%d\n", __LINE__, sqlite3_errcode(db));
		return -1;
	}
	printf("database close success\n");

	return 0;
}