Sqlite3：增 删 改 查

条件介绍

1）已存在一个数据库AddressBook.db 以及 其中的一张表telephone，表的详细内容参考Sqlite3的入门操作

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;
    // 将指针p移至单链表L的首元结点（单链表带有头结点）
	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_attribute(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;
}

static int print_column_attribute(const char name[][128], const int type[50], int len)
{
	for (int i = 0; i < len; i++)
	{
		char str[128] = { 0 };
		switch (type[i])
		{
		case SQLITE3_TEXT:
			sprintf(str, "%s(TEXT)", name[i]);
			break;
		case SQLITE_INTEGER:
			sprintf(str, "%s(INT)", name[i]);
			break;
		case SQLITE_BLOB:
			sprintf(str, "%s(BLOB)", name[i]);
			break;
		case SQLITE_FLOAT:
			sprintf(str, "%s(FLOAT)", name[i]);
			break;
		case SQLITE_NULL:
			sprintf(str, "%s(NULL)", name[i]);
			break;
		}
		printf("%-22s ", str);
	}
	printf("\n");
	return 0;
}

static int get_row_val(sqlite3_stmt *stmt, ELEMTYPE *elem)
{
	int byte = 0;
	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);
	}
	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_attribute(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;
			get_row_val(stmt, &elem);
			list_create_r(&L, elem);
		}
		sqlite3_finalize(stmt);
		stmt = NULL;
	}

	/* 打印列头 */
	print_column_attribute(name, col_type, len);

	/* 打印结果集 */
	list_traverse(L);
	list_destroy(&L);
	sqlite3_close(db);

	return 0;
}

运行结果：

数据类型

在Sqlite3中如下数据类型的宏定义：

1）SQLITE_INTEGER（整型）

2）SQLITE_FLOAT（浮点型）

3）SQLITE_BLOB（二进制数据类型）

4）SQLITE_NULL（NULL值）

5）SQLITE3_TEXT（string类型）

在Sqlite3中的根据列类型查询列值的API

函数名	备注
sqlite3_column_blob	BLOB result
sqlite3_column_double	REAL result
sqlite3_column_int	32-bit INTEGER result
sqlite3_column_int64	64-bit INTEGER result
sqlite3_column_text	UTF-8 TEXT result
sqlite3_column_text16	UTF-16 TEXT result
sqlite3_column_value	The result as an unprotected sqlite3_value object.
sqlite3_column_bytes	Size of a BLOB or a UTF-8 TEXT result in bytes
sqlite3_column_bytes16	Size of UTF-16 TEXT in bytes
sqlite3_column_type	Default datatype of the result

查询select语句中的列名和类型

select * from telephone; 会查询整个结果集，此时仅把列名和类型取出来就行了，故拿一行解析就可以了。

--> select * from telephone limit 0,1;

// 获取列属性函数
static int get_column_attribute(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;
}

参考引用