前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >sqlite数据存储(1)

sqlite数据存储(1)

作者头像
franket
发布2021-09-15 19:57:33
5440
发布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.用文字界面手动输入
代码语言:javascript
复制
1:insert 2:delete 3:update 4:query 0:exit

1:input 0:back

2: 1 delete   0:back
   delete ID:

代码示例

sqlite.c

代码语言:javascript
复制
#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 删除。

本文系转载前往查看

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

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 前言
  • 概要
    • 代码示例
      • 要求
      • 代码示例
相关产品与服务
对象存储
对象存储(Cloud Object Storage,COS)是由腾讯云推出的无目录层次结构、无数据格式限制,可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限,无需分区管理,适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档