sqlite3数据库 的使用

一、使用数据库进行对学生信息的增删改查；

#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"

// 插入学生信息记录
void do_insert(sqlite3 *db)
{
	int id;
	printf("input id: ");
	scanf("%d", &id);
	getchar();

	char name[32] = {0};
	printf("input name: ");
	scanf("%s", name);
	getchar();

	int score;
	printf("input score: ");
	scanf("%d", &score);
	getchar();

	char sql[128] = {0};
	sprintf(sql, "insert into stu(id, name, score) values(%d, '%s', %d);", id, name, score);
	char *errmsg = NULL;
	int ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	if (ret != SQLITE_OK) {
		printf("sqlite3_exec error: %s\n", errmsg);
		return;
	}
	printf("do insert done\n");
}

// 通过id更新学生成绩
void do_update(sqlite3 *db)
{
	int id;
	printf("input id: ");
	scanf("%d", &id);
	getchar();

	int score;
	printf("input score: ");
	scanf("%d", &score);

	char sql[128] = {0};
	sprintf(sql, "update stu set score = %d where id = %d;", score, id);

	char *errmsg = NULL;
	int ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	if (ret != SQLITE_OK) {
		printf("sqlite3_exec error: %s\n", errmsg);
		return;
	}
	printf("do update done\n");
}

int callback(void *para, int f_num, char **f_value, char **f_name)
{
	for (int i = 0; i < f_num; i++) {
		printf("%s ", f_value[i]);
	}
	printf("\n");
	return 0;
}

// 查询所有学生信息记录
void do_query(sqlite3 *db)
{
	char sql[128] = "select * from stu;";
	char *errmsg = NULL;
	int ret = sqlite3_exec(db, sql, callback, NULL, &errmsg);
	if (ret != SQLITE_OK) {
		printf("sqlite3_exec error: %s\n", errmsg);
		return;
	}
	printf("do query done\n");
}

void do_query1(sqlite3 *db)
{
	char sql[128] = "select * from stu;";
	char *errmsg = NULL;
	char **resultp = NULL;
	int nrow, ncolumn;
	int ret = sqlite3_get_table(db, sql, &resultp, &nrow, &ncolumn, &errmsg);
	if (ret != SQLITE_OK) {
		printf("sqlite3_exec error: %s\n", errmsg);
		return;
	}

	int index = ncolumn;
	for (int i = 0; i < nrow; i++) {
		for (int j = 0; j < ncolumn; j++) {
			printf("%s ", resultp[index++]);
		}
		printf("\n");
	}
	printf("do query1 done\n");
}

void do_delete(sqlite3 *db)
{
	int id;
	printf("input id: ");
	scanf("%d", &id);
	getchar();

	char sql[128] = {0};
	sprintf(sql, "delete from stu where id = %d;", id);

	char *errmsg = NULL;
	int ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	if (ret != SQLITE_OK) {
		printf("sqlite3_exec error:%s\n", errmsg);
		return;
	}
	printf("do delete done\n");
}

int main()
{
	sqlite3 *db = NULL;

	// 打开数据库（不存在则创建数据库）
	int ret = sqlite3_open("student.db", &db);
	if (ret != SQLITE_OK) {
		printf("sqlite3_open error: %s\n", sqlite3_errmsg(db));
		return -1;
	}

	// 创建学生信息表
	char *errmsg = NULL;
	char sql[128] = "create table stu(id integer primary key not null, name text not null, score integer);";
	ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	if (ret != SQLITE_OK) {
		printf("sqlite3_exec error: %s\n", errmsg);
	}

	while (1) {
		printf("\n******************************************\n");
		printf("1:insert 2:update 3:qury 4:delete 5:exit");
		printf("\n******************************************\n");

		printf("input:");
		int input;
		scanf("%d", &input);
		getchar();

		switch (input) {
			case 1:
				do_insert(db);
				break;
			case 2:
				do_update(db);
				break;
			case 3:
				do_query1(db);
				break;
			case 4:
				do_delete(db);
				break;
			case 5:
				printf("exit\n");
				ret = sqlite3_close(db);
				if (ret != SQLITE_OK) {
					printf("sqlite3_close error\n");
				}
				exit(0);
			default:
				printf("input error\n");
		}
	}

	return 0;
}