Sqlite3：增删改查

eiSouthBoy'sBolg / 2023-08-21 / 原文

条件介绍

1）已存在一个数据库AddressBook.db 以及其中的一张表telephone

2）telephone表格式：

Name     TEXT     NOT NULL
PhoneNum CHAR(11) NOT NULL
Birthday TEXT
Nation   TEXT DEFAULT 'China'

insert

目的：新增两行记录

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <stdbool.h>
#include <sqlite3.h>

int main(int argc, char **argv)
{
	sqlite3 *db = NULL;
	char *err_msg = NULL;
	int rc = 0;
	const char *database_name = "AddressBook.db";

	rc = sqlite3_open(database_name, &db);
	if (rc)
	{
		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return 1;

	}
	char sql[512] = { 0 };
	snprintf(sql, sizeof(sql), "insert telephone(Name,PhoneNum,Birthday,Nation) value('ZhaoLiu','10015','1993-01-01 12:00:00','US');"
							   "insert telephone(Name,PhoneNum,Birthday,Nation) value('QianMing','10018','1994-01-01 12:00:00','UK');");
	rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg);
	if (rc)
	{
		fprintf(stderr, "SQL error: %s\n", err_msg);
		sqlite3_free(err_msg);
		sqlite3_close(db);
		return 1;
	}
	sqlite3_close(db);
	return 0;
}

验证结果是否新增2行：

delete

目的：删除1行，这一行中 Name=QianMing

int main(int argc, char **argv)
{
	sqlite3 *db = NULL;
	char *err_msg = NULL;
	int rc = 0;
	const char *database_name = "AddressBook.db";

	rc = sqlite3_open(database_name, &db);
	if (rc)
	{
		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return 1;

	}
	char sql[512] = { 0 };
	snprintf(sql, sizeof(sql), "delete from telephone where name='QianMing'");
	rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg);
	if (rc)
	{
		fprintf(stderr, "SQL error: %s\n", err_msg);
		sqlite3_free(err_msg);
		sqlite3_close(db);
		return 1;
	}
	sqlite3_close(db);

	return 0;
}

验证是否删除成功：

update

目的：更改 WangWu 的 Birthday 为 1992-01-01 12:00:00

📌 有一个小问题：表中没有设置主键，对于update or delete会有影响。不过本实例操作没有影响，毕竟只有三行不一样的数据

代码：

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <stdbool.h>
#include <sqlite3.h>

int main(int argc, char **argv)
{
	sqlite3 *db = NULL;
	char *err_msg = NULL;
	int rc = 0;
	const char *database_name = "AddressBook.db";

	rc = sqlite3_open(database_name, &db);
	if (rc)
	{
		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return 1;

	}
	char sql[512] = { 0 };
	snprintf(sql, sizeof(sql), "update telephone set Birthday='1992-01-01 12:00:00' where name='WangWu'");
	rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg);
	if (rc)
	{
		fprintf(stderr, "SQL error: %s\n", err_msg);
		sqlite3_free(err_msg);
		sqlite3_close(db);
		return 1;
	}
	sqlite3_close(db);
	return 0;
}

验证是否修改成功：

select

目的：查询指定数据库中表的内容

方式一

#include <stdio.h>
#include <string.h>
#include <sqlite3.h>

/* 对于select查询结果集进行处理 */
int callback(void *NotUsed, int num_of_column, char **column_value, char **column_name)
{
	NotUsed = NULL;
	for (int i = 0; i < num_of_column; ++i)
	{
		printf("%s = %s\n", column_name[i], (column_value[i] ? column_value[i] : "NULL"));
	}
	printf("\n");
	return 0;
}

int main(int argc, char **argv)
{
	sqlite3 *db = NULL;
	char *err_msg = NULL;
	int rc = 0;
	const char *database_name = "AddressBook.db";

	rc = sqlite3_open(database_name, &db);
	if (rc)
	{
		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return 1;

	}
	const char *sql_select = "select * from telephone;";
	rc = sqlite3_exec(db, sql_select, callback, NULL, &err_msg);
	if (rc != SQLITE_OK)
	{
		fprintf(stderr, "SQL error: %s\n", err_msg);
		sqlite3_free(err_msg);
		sqlite3_close(db);
		return 1;
	}

	sqlite3_close(db);
	return 0;
}

运行结果

方式二

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <stdbool.h>
#include <sqlite3.h>

typedef struct
{
	char *name;
	char *phonenum;
	char *birthday;
	char *nation;
}PERSON_T;

typedef PERSON_T ELEMTYPE;

typedef struct LNODE_T
{
	ELEMTYPE data;
	struct LNODE_T *next;
}LNODE_T, *LINK_LIST_T;

static bool list_init(LINK_LIST_T *L)
{
	*L = (LNODE_T *)malloc(sizeof(LNODE_T)); // 带头结点的链表
	if (NULL == *L)
		return false;
	memset(*L, 0, sizeof(LNODE_T));
	(*L)->next = NULL;
	return true;
}

static void list_destroy(LINK_LIST_T *L)
{
	LNODE_T *temp = NULL;
	while (*L)
	{
		temp = *L;
		*L = (*L)->next;
		free(temp->data.name);
		free(temp->data.phonenum);
		free(temp->data.birthday);
		free(temp->data.nation);
		free(temp);
	}
}

static int list_create_r(LINK_LIST_T *L, ELEMTYPE elem)
{
	// 尾插法需要将指针移至最后一个结点
	LNODE_T *r = *L;
	while (r->next)
	{
		r = r->next;
	}
	LNODE_T *p = (LNODE_T *)malloc(sizeof(LNODE_T));
	memset(p, 0, sizeof(LNODE_T));
	p->data = elem;
	p->next = NULL;
	r->next = p;
	return 0;
}

static int list_traverse(LINK_LIST_T L)
{
	LNODE_T *p = NULL;
	for (p = L->next; p != NULL; p = p->next)
	{
		fprintf(stdout, "%-22s %-22s %-22s %-22s\n", p->data.name, p->data.phonenum, p->data.birthday, p->data.nation);
	}
	return 0;
}

static int get_column_type(sqlite3 *db, const char *sql, char name_arr[][128], int type_arr[], int *len)
{
	sqlite3_stmt *stmt = NULL;
	sqlite3_prepare(db, sql, -1, &stmt, NULL);
	if (stmt)
	{
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			int num_of_col = sqlite3_column_count(stmt);
			*len = num_of_col;
			for (int i = 0; i < num_of_col; i++)
			{
				int type = sqlite3_column_type(stmt, i);
				const char *name = sqlite3_column_name(stmt, i);
				type_arr[i] = type;
				snprintf(name_arr[i], sizeof(name_arr[i]), "%s", name);
			}
		}
		sqlite3_finalize(stmt);
		stmt = NULL;
	}

	return 0;
}

int main(int argc, char **argv)
{
	sqlite3 *db = NULL;
	char *err_msg = NULL;
	int rc = 0;
	const char *database_name = "AddressBook.db";

	rc = sqlite3_open(database_name, &db);
	if (rc)
	{
		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return 1;

	}

	/* 获取表头 */
	sqlite3_stmt *stmt = NULL;
	char sql[512] = {0};
	char name[50][128] = {0};
	int col_type[50] = {0}, len = 0;
	snprintf(sql, sizeof(sql), "select * from telephone limit 0,1;");
	get_column_type(db, sql, name, col_type, &len);

	/* 获取结果集 */
	snprintf(sql, sizeof(sql), "select * from telephone;");
	sqlite3_prepare(db, sql, -1, &stmt, NULL);
	LINK_LIST_T L;
	list_init(&L);
	if (stmt)
	{
		int byte = 0;
		int num_of_col = sqlite3_column_count(stmt);
		while (sqlite3_step(stmt) == SQLITE_ROW)
		{
			ELEMTYPE elem;
			byte = sqlite3_column_bytes(stmt, 0);
			if (byte)
			{
				elem.name = (char *)malloc((byte+1)*sizeof(char));
				memset(elem.name, 0, (byte + 1) * sizeof(char));
				char *name = (char *)sqlite3_column_text(stmt, 0);
				memcpy(elem.name, name, byte);
				;
			}
			byte = sqlite3_column_bytes(stmt, 1);
			if (byte)
			{
				elem.phonenum = (char *)malloc((byte + 1) * sizeof(char));
				memset(elem.phonenum, 0, (byte + 1) * sizeof(char));
				char *phonenum = (char *)sqlite3_column_text(stmt, 1);
				memcpy(elem.phonenum, phonenum, byte);
				;
			}
			byte = sqlite3_column_bytes(stmt, 2);
			if (byte)
			{
				elem.birthday = (char *)malloc((byte + 1) * sizeof(char));
				memset(elem.birthday, 0, (byte + 1) * sizeof(char));
				char *birthday = (char *)sqlite3_column_text(stmt, 2);
				memcpy(elem.birthday, birthday, byte);
				;
			}
			byte = sqlite3_column_bytes(stmt, 3);
			if (byte)
			{
				elem.nation = (char *)malloc((byte + 1) * sizeof(char));
				memset(elem.nation, 0, (byte + 1) * sizeof(char));
				char *nation = (char *)sqlite3_column_text(stmt, 3);
				memcpy(elem.nation, nation, byte);
				;
			}
			list_create_r(&L, elem);
		}
		sqlite3_finalize(stmt);
		stmt = NULL;
	}
	/* 打印结果集 */
	for (int i = 0; i < len; i++)
	{
		printf("%-22s ", name[i]);
	}
	printf("\n");
	list_traverse(L);
	list_destroy(&L);

	sqlite3_close(db);
	return 0;
}

运行结果：