Linux学习 Sqlite3基本操作

sqlite3 中文指南 https://www.runoob.com/sqlite/sqlite-syntax.html
sqlite3 C语言API https://www.sqlite.org/c3ref/funclist.html
若出现#include"sqlite3.h"无法调用，则缺少库文件，执行

	sudo apt-get install libsqlite3-dev

sqlite 3 常用shell指令

• 数据库安装

  • 离线安装
    sudo dpkg -i *.deb

  • 在线安装
    sudo apt install sqlite3

• 数据库命令：

  • 系统命令，以“.”开头

.exit
.quit		//退出
.table		//查看表
.schema		//查看表的结构

• sql语句，以 " ; " 结尾

1. 创建一张表
   create table [表名] （列名 数据类型，…）;
   create table stdinfo(id integer, name text, age integer, score float);

2. 插入一条记录
   insert into [表名] values(要插入的数据);
   insert into stuinfo values(1001, ‘zhangsan’, 18, 80);
   insert into stuinfo (id, name, score) values(1002, ‘lisi’, 90);

3. 查看数据类型
   select * from stuinfo;
   select * from stuinfo where score = 80;
   select * from stuinfo where score = 80 and name= ‘zhangsan’;
   select * from stuinfo where score = 80 or name=‘wangwu’;
   select name,score from stuinfo; 查询指定的字段
   select * from stuinfo where score >= 85 and score < 90;

4. 删除一条记录
   delete from [表名] where [限制条件];
   delete from stuinfo where id=1003 and name=‘zhangsan’;

5. 更新一条记录
   update [表名] set [要更新的值] where [限制条件];
   update stuinfo set age=20 where id=1003;
   update stuinfo set age=30, score = 82 where id=1003;

6. 删除一张表
   drop table [表名];
   drop table stuinfo;

7. 增加一列
   alter table [表名] add column [列名 数据类型];
   alter table stuinfo add column sex char;

8. 删除一列
   sqlite3 无法删除指定列，若想进行删除操作，需先将要保留的列复制到一个新table中，在将原table删除，把新生成的table更名原table名。
   create table stu as select id, name, score from stuinfo;
   drop table stuinfo;
   alter table stu rename to stuinfo;

9. 数据库设置主键：
   create table info(id integer primary key autoincrement, name vchar);

sqlite 3 C语言常用API

int sqlite3_open{
      const char *filename,   /* Database filename (UTF-8) */
      sqlite3 **ppDb          /* OUT: SQLite db handle */
     };
	功能：打开数据库
	参数：filename  数据库名称
	      ppdb      数据库句柄
	返回值：成功为0 SQLITE_OK ,出错 错误码

    int sqlite3_close(sqlite3* db);
    功能：关闭数据库
    参数：
    返回值：成功为0 SQLITE_OK ,出错 错误码

   const char *sqlite3_errmsg(sqlite3*db);
   功能：得到错误信息的描述

  int sqlite3_exec(
  		sqlite3* db,                                 	/* An open database */
  		const char *sql,                           		/* SQL to be evaluated */
  		int (*callback)(void* arg,int,char**,char**),  	/* Callback function */
  		void * arg,                                    /* 1st argument to callback */
  		char **errmsg                              	   /* Error msg written here */
  );
  功能：执行一条sql语句
  参数： db  数据库句柄
        sql sql语句
        callback  回调函数，只有在查询时，才传参
        arg      为回调函数传递参数
        errmsg  错误消息
  返回值：成功 SQLITE_OK

======================================================================================================
在每次调用SELECT函数时会使用callback函数。  
查询回调函数：
	int (*callback)(void* arg,int ncolumns ,char** f_value,char** f_name),  /* Callback function */
	功能：查询语句执行之后，会回调此函数
	参数：arg   接收sqlite3_exec 传递来的参数
	      ncolumns 列数
	      f_value　列的值得地址
	      f_name   列的名称
	返回值：0
另外一种查询Table 的方法，使用sqlite3_get_table函数，使用完毕后释放。
int sqlite3_get_table(
  sqlite3 *db,          /* An open database */
  const char *zSql,     /* SQL to be evaluated */
  char ***pazResult,    /* Results of the query */
  int *pnRow,           /* Number of result rows written here */
  int *pnColumn,        /* Number of result columns written here */
  char **pzErrmsg       /* Error msg written here */
);
void sqlite3_free_table(char **result);
查询
           

使用sqlite3 API创建table

可实现以下功能

• 创建新表
• 插入
• 更新
• 查询
• 删除

使用callback函数进行查询。

#include <stdio.h>
#include <sqlite3.h>
#include <string.h>

void create_newtable(sqlite3 *db)
{
   int rc;
   char sql[256] = {0};
   char *errmsg;
   char name[32]={0};
   char type[128]={0};
   char *ret;
   printf("input the new table name: ");
   scanf("%s", name);
   printf("input the table values and type:");
   getchar();
   scanf("%[^\n]",type);            //scanf读取带空格输入
   sprintf(sql,"create table %s (%s);", name, type);
   if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
   {
      printf("%s",errmsg);
   }
   printf("new table create done! \n");

}

void insert(sqlite3 *db)
{
   int rc;
   char sql[256] = {0};
   char *errmsg;
   char table[32]={0};
   char colums[64]={0};
   char values[64] = {0};
   char where[64] = {0};
   printf("choese table what to insert: ");
   scanf("%s", table);
   printf("choese columns:");
   getchar();
   scanf("%[^\n]%*c",colums);            //scanf读取带空格输入
   printf("values:");
   scanf("%[^\n]%*c",values);

   sprintf(sql,"insert into %s (%s) values(%s);", table, colums, values);
   if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
   {
      printf("%s",errmsg);
   }
   printf("insert done\n");
}


void update(sqlite3 *db)
{
   int rc;
   char sql[256] = {0};
   char *errmsg;
   char table[32]={0};
   char values[128] = {0};
   char where[64] = {0};
   printf("choese table what to update: ");
   scanf("%s", table);
   printf("choese values(type: colums1=values1,colums2=values2 ...):");
   getchar();
   scanf("%[^\n]%*c",values);
   printf("where? if not press enter: ");
   scanf("%[^\n]%*c",where);
   /* sql = "update [table] set colums = (values) where ;"*/
   sprintf(sql,"update %s set %s %s;", table, values, where);
   if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK )
   {
      printf("%s",errmsg);
   }
   printf("update done\n");

}

static int callback(void* arg,int ncolumns ,char** fvalue,char** fname)
{
   int i;
   for(i=0;i<ncolumns;i++)
   {
      printf("%s = %-10s", fname[i], fvalue[i]);
      //printf("%-10s", fvalue[i]);
   }
   printf("\n");
   return 0;
}

void check(sqlite3 *db)
{
   int rc;
   char sql[256] = {0};
   char *errmsg;
   char table[32]={0};
   printf("choese table: ");
   scanf("%s", table);
   getchar();
   /* 
      查询时需要使用 callback 函数
      sql = " select * from [table];"
      每次执行select函数都会启用callback
   */
   sprintf(sql,"select * from %s;", table);
   if(sqlite3_exec(db, sql, callback, NULL, &errmsg) != SQLITE_OK )
   {
      printf("%s",errmsg);
   }
   printf("check done\n");

}


void delete(sqlite3 *db)
{
   int rc;
   char sql[256] = {0};
   char *errmsg;
   char table[32]={0};
   char where[128] = {0};
   printf("choese table what to delete: ");
   scanf("%s", table);
   printf("where you want to delete:");
   getchar();
   scanf("%[^\n]%*c",where);
   /* sql = "delete from stuinfo where id=1003 and name='zhangsan';"*/
   sprintf(sql,"delete from %s where %s;", table, where);
   if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) < 0)
   {
      printf("%s",errmsg);
   }
   printf("delete done\n");
}

int main()
{
     sqlite3 * db;
     int rc;
     if((rc = sqlite3_open("stuinfo.db", &db) != SQLITE_OK))
     {
        printf("%s \n", sqlite3_errmsg(db));
     }
     
     while(1)
     {
         printf("*************\n");
         printf("1.创建新表 2.插入 3.更新 4.查询 5.删除 6.退出\n");
         printf("*************\n");
         printf("your command:");
         scanf("%d",&rc);
         getchar();

         switch (rc)
         {
         case 1:
            create_newtable(db);
            break;
         case 2: 
            insert(db);
            break;
         case 3:
            update(db);
            break;
         case 4:
            check(db);
            break;
         case 5:
            delete(db);
            break;
         case 6:
            sqlite3_close(db);
            printf("sqlite3 end! \n");
            return 0;
            break;
         default:
            printf("Input error, re-entry \n");
            break;
         }

     }
   
   return 0;

}

使用 sqlite3_get_table 进行查询

void check(sqlite3 *db)
{
   int rc;
   char sql[256] = {0};
   char *errmsg;
   char table[32]={0};
   char ** pazResult;
   int pnRow;
   int pnColumn;
   int index;
   
   printf("choese table: ");
   scanf("%s", table);
   getchar();
   /* 
      查询时需要使用 callback 函数
      sql = " select * from [table];"
      每次执行select函数都会启用callback
   */
   sprintf(sql,"select * from %s;", table);
   // if(sqlite3_exec(db, sql, callback, NULL, &errmsg) != SQLITE_OK )
   // {
   //    printf("%s",errmsg);
   // }
   if(sqlite3_get_table(db,sql,&pazResult, &pnRow, &pnColumn, &errmsg) != SQLITE_OK)
   {
      printf("sqlite3_get_table fail %s \n",errmsg);
   }

   index = pnColumn;
   for(int i=0;i<pnRow;i++)
   {
      for(int j=0;j<pnColumn;j++)
      {
         printf("%s=%-8s", pazResult[j], pazResult[index++]);
      }
      printf("\n");
   }
   sqlite3_free_table(pazResult);
   printf("check done\n");

}