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;
}