sqlite数据存储（1）

franket

发布于 2021-09-15 19:57:33

5460

发布于 2021-09-15 19:57:33

文章被收录于专栏：技术杂记技术杂记

前言

数据的 输入输出 ，加工存储 是计算系统所有关心的范畴

当数据量小，数据结构简单的时候，可以直接使用扁平的文件结构来存储

但是当数据量大，结构复杂的时候，操作普通扁平文件的效率就变得低下了(时间开销大，更容易出错，维护成本高)

于是有必要将数据的存储这项事务独立分离出来，进行模块化和针对性的解决，数据库就应运而生了

目前的数据库主要分 关系型 和 非关系型 两大类，网上资料多如牛毛，我就不进行赘述了

SQLite 是一个开源的进程内库，实现了自给自足、无服务端、零配置、事务性的 SQL 数据库引擎

SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine

因为轻量的特性，它被大量地使用在了各类嵌入式系统中

Think of SQLite not as a replacement for Oracle but as a replacement for fopen()

这里分享一下使用C语言操作 SQLite 的相关基础

Tip: 当前的最新版本为 SQLite Release 3.16.2 On 2017-01-06，手动维护的相关基础可以参看之前的 《SQLite 基础》，C 语言的API介绍可以参看 C-language Interface Specification for SQLite，相关的函数列表可以参看 List Of Functions

概要

代码示例

要求

1.表结构：SID NAME SCORE
2.用sqlite实现增、删、改、查
3.用文字界面手动输入

1:insert 2:delete 3:update 4:query 0:exit

1:input 0:back

2: 1 delete   0:back
   delete ID:

代码示例

sqlite.c

#include <stdio.h> //printf,scanf,sprintf,fprintf,stderr 相关的函数声明在此文件中
#include <sqlite3.h> //sqlite3_open,sqlite3_errmsg,sqlite3_close,sqlite3_exec 相关的函数声明在此文件中

#define NAMESIZE 20 
#define SQLSIZE 1024


int callback(void *data,int argc,char **argv,char **colname) //定义一个回调函数，用于对SQL 语句的执行结果进行逐条处理，主要作用是进行显示
{
  int i=0;
  for(i=0;i<argc;i++)printf("%10s",colname[i]); //先打印每个列名
  printf("\n");
  for(i=0;i<argc;i++)printf("%10s",argv[i]); //再打印返回集中的每一项
  printf("\n");
  return 0;
}



int showall(sqlite3 *db)  //查询所有记录的操作
{
  int res=-1,option=-1;
  char sql[SQLSIZE],*errmsg=NULL;
  
  printf("Please take follow action:\n%-10s%-10s\n","0.back","1.showall"); //子项提示
  scanf("%d",&option); //获取选择
  if(0 == option) 
  {
    printf("back to main menu...\n");
    return res;
  }
  else if(1 == option)
  {
    sprintf(sql,"select * from students;"); //构建查询所有信息的SQL语句
    printf("ready to process ...\n[ %s ]\n",sql);
    if (0 != sqlite3_exec(db,sql,callback,0,&errmsg)) //执行查询所有信息的SQL语句
    {
      fprintf(stderr,"Can't show all from table students:%s\n",sqlite3_errmsg(db)); 
    }
    else printf("Select successfully...\n");
  }
  else  printf("error option %d\nplease reinput...\n",option);
  
  res=0;
  return res;
}


int query(sqlite3 *db) //查询操作
{
  
  int res=-1,option=-1,sid=0;
  char sql[SQLSIZE],*errmsg=NULL;
  
  printf("Please take follow action:\n%-10s%-10s\n","0.back","1.query"); //子项提示
  scanf("%d",&option);
  if(0 == option) 
  {
    printf("back to main menu...\n");
    return res;
  }
  else if(1 == option)
  {
    printf("Please input SID of the student:\n%-10s\n","SID");
    scanf("%d",&sid);
    sprintf(sql,"select * from students where sid=%d;",sid); //构建进行查询的SQL语句
    printf("ready to process ...\n[ %s ]\n",sql);
    if (0 != sqlite3_exec(db,sql,callback,0,&errmsg)) //执行查询语句
    {
      fprintf(stderr,"Can't select from table students:%s\n",sqlite3_errmsg(db)); 
    }
    else printf("Select successfully...\n");
  }
  else  printf("error option %d\nplease reinput...\n",option);
  
  res=0;
  return res;
}

int update(sqlite3 *db) //更新操作
{
  int res=-1,option=-1,sid=0;
  char name[NAMESIZE],sql[SQLSIZE],*errmsg=NULL;
  float score=0;

  printf("Please take follow action:\n%-10s%-10s\n","0.back","1.update"); //子项提示
  scanf("%d",&option);

  if(0 == option) 
  {
    printf("back to main menu...\n");
    return res;
  }
  else if(1 == option) 
  {
    printf("Please input SID,NAME,SCORE of the student to be update:\n%-10s%-10s%-10s\n","SID","NAME","SCORE"); //提示输入更新的相关信息
    scanf("%d%s%f",&sid,name,&score); 
    sprintf(sql,"update students set name='%s',score=%5.2f where sid=%d;",name,score,sid); //构建进行更新的SQL语句
    printf("ready to process ...\n[ %s ]\n",sql);
    if (0 != sqlite3_exec(db,sql,0,0,&errmsg)) //执行更新语句
    {
      fprintf(stderr,"Can't update table students:%s\n",sqlite3_errmsg(db)); 
    }
    else printf("Update successfully...\n");
  }
  else  printf("error option %d\nplease reinput...\n",option);
  
  res=0;
  return res;
}

int delete(sqlite3 *db) //删除操作
{
  int res=-1,option=-1,sid=0;
  char sql[SQLSIZE],*errmsg=NULL;
  
  printf("Please take follow action:\n%-10s%-10s\n","0.back","1.delete");
  scanf("%d",&option); //子项提示
  if(0 == option)  //0项退出
  {
    printf("back to main menu...\n");
    return res;
  }
  else if(1 == option) 
  {
    printf("Please input SID of the student:\n%-10s\n","SID");
    scanf("%d",&sid);
    sprintf(sql,"delete from students where sid=%d;",sid); //构建删除的SQL语句
    printf("ready to process ...\n[ %s ]\n",sql);
    if (0 != sqlite3_exec(db,sql,0,0,&errmsg)) //执行删除语句
    {
      fprintf(stderr,"Can't delete from table students:%s\n",sqlite3_errmsg(db)); 
    }
    else printf("Delete successfully...\n");
  }
  else  printf("error option %d\nplease reinput...\n",option);
  
  res=0;
  return res;
  
}

int insert( sqlite3 *db) //插入操作
{
  int res=-1,option=-1,sid=0;
  char name[NAMESIZE],sql[SQLSIZE],*errmsg=NULL;
  float score=0;

  printf("Please take follow action:\n%-10s%-10s\n","0.back","1.insert"); //子项提示
  scanf("%d",&option); //获取输入

  if(0 == option)  //0选项进行退出
  {
    printf("back to main menu...\n");
    return res; 
  }
  else if(1 == option)  //1选项进行插入
  {
    printf("Please input SID,NAME,SCORE of the student:\n%-10s%-10s%-10s\n","SID","NAME","SCORE"); //提示输入
    scanf("%d%s%f",&sid,name,&score); //获取输入
    sprintf(sql,"insert into students(sid,name,score) values(%d,'%s',%5.2f);",sid,name,score); //构建插入的SQL语句
    printf("ready to process ...\n[ %s ]\n",sql);
    if (0 != sqlite3_exec(db,sql,0,0,&errmsg)) //执行插入操作
    {
      fprintf(stderr,"Can't insert into table students:%s\n",sqlite3_errmsg(db)); 
    }
    else printf("Insert successfully...\n");
  }
  else  printf("error option %d\nplease reinput...\n",option);
  
  res=0;
  return res;
}




int main()
{
  sqlite3 *db=NULL; 
  char *errmsg=NULL,*sql=NULL;
  char *dbname="test.db";
  int res=-1,option=-1; //定义变量，进行初始化

  if(0 != sqlite3_open(dbname,&db)) //打开数据库，如果没有就创建
  {
    fprintf(stderr,"Can't open database %s :%s\n",dbname,sqlite3_errmsg(db)); //打开失败则返回错误信息
    sqlite3_close(db);
    return res;
  }
  else printf("Open database %s successfully...\n",dbname);

  sql="create table students (sid int primary key,name varchar(20),score real )"; //构建SQL语句，在数据库中创建表

  if (0 != sqlite3_exec(db,sql,0,0,&errmsg)) //执行SQL语句
  {
    fprintf(stderr,"Can't create table students:%s\n",sqlite3_errmsg(db)); 
  }
  else printf("Create table students successfully...\n");


  while(1)
  {

    printf("Please take follow action:\n%-10s%-10s%-10s%-10s%-10s%-10s\n","0.exit","1.insert","2.delete","3.update","4.query","5.showall"); //显示主菜单
    scanf("%d",&option); //获取选项
    if(0 == option)  //如果选择退出，就进行退出
    {
      printf("ready to exit...\n");
      break;
    }
    else if(1 == option) insert(db); 
    else if(2 == option) delete(db);
    else if(3 == option) update(db);
    else if(4 == option) query(db);
    else if(5 == option) showall(db); //进入对应的处理过程
    else printf("error option %d\nplease reinput...\n",option); //如果不在其列，就提示重新输入
  }


  if(0 != sqlite3_close(db)) //关闭数据库
  {
    fprintf(stderr,"Can't close database %s :%s\n",dbname,sqlite3_errmsg(db));
    return res;
  }
  else printf("Close database %s successfully...\n",dbname);
  res=0;
  return res;
}

本文系转载，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

sqlite