Sqlite3：增删改查

条件介绍

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