linux嵌入式--数据库开发（Sqlite）

Sqlite安装

sudo apt-get install sqlite3

SQLite基本命令

【1】系统命令
以‘.’开头命令
.quit
.help
.exit
【2】sql命令
基本的sql命令以‘;’结尾
创建一张表stu：create table stu(id Integer,name char,score Integer);
插入一条完整记录：insert into stu values(1001,‘zhangsan’,80);
指定字段插入：insert into stu (id,name)values(1002,“lisi”);
查询记录：select [字段名] from [表名] where [条件]；

    1-- 创建一张表
        create table stuinfo(id integer, name text, age integer, score float);
    
    2-- 插入一条记录
        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 stuinfo where id=1003 and name='zhangsan';

    5-- 更新一条记录
        update stuinfo set age=20 where id=1003;
        update stuinfo set age=30, score = 82 where id=1003;

    6-- 删除一张表
        drop table stuinfo;

    7-- 增加一列
        alter table stuinfo add column sex char;

    8-- 删除一列
        create table stu as select id, name, score from stuinfo;
        drop table stuinfo;
        alter table stu rename to stuinfo;

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

sqlite c语言API操作

sqlite3 数据库 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(sqlite3db);
功能：得到错误信息的描述

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

查询回调函数：
int (*callback)(void* arg,int ncolumns ,char** f_value,char** f_name), /* Callback function */
功能：查询语句执行之后，会回调此函数
参数：arg 接收sqlite3_exec 传递来的参数
ncolumns 列数
f_value　列的值得地址
f_name 列的名称
返回值：0，

查询：
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);

学生信息管理表程序

 1 #include<stdio.h>
  2 #include<sqlite3.h>
  3 #include<stdlib.h>
  4 
  5 
  6 #define DATABASE "student.db"
  7 
  8 
  9 int do_insert(sqlite3* db){
 10     int id;
 11     char name[32]={};
 12     int score;
 13     char sql[128]={};
 14     char* errmsg;
 15     
 16     printf("inout id:");
 17     scanf("%d",&id);
 18     getchar();
 19     
 20     printf("input name:");
 21     scanf("%s",name);
 22     getchar();
 23     
 24     printf("input score:");
 25     scanf("%d",&score);
 26     getchar();
 27     
 28     sprintf(sql,"insert into stu values(%d,'%s',%d);",id,name,score);
 29 
 30     if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
 31         printf("%s\n",errmsg);
 32     }else{
 33         printf("insert done\n");
 34     }
 35     return 0;
36 }
 37 int do_update(sqlite3* db){
 38     int id;
 39     char name[32];
 40     int score;
 41     char *errmsg;
 42     char sql[128];
 43     printf("input id:");
 44     scanf("%d",&id);
 45     getchar();
 46     
 47     printf("input new score:");
 48     scanf("%d",&score);
 49     getchar();
 50     
 51     sprintf(sql,"update stu set score=%d ehere id=%d;",score,id);
 52     if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
 53         printf("%s\n",errmsg);
 54     }else{
 55         printf("update done\n");
 56     }
 57     return 0;
 58 }
 59 int do_delete(sqlite3* db){
 60     int id;
 61     char name[32];
 62     char sql[128];
 63     char* errmsg;
 64     printf("input delete id:");
 65     scanf("%d",&id);
 66     getchar();
 67     printf("input delete name");
68     scanf("%s",name);
 69     getchar();
 70     sprintf(sql,"delete from stu where id=%d and name='%s';",id,name);
 71     if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
 72         printf("%s\n",errmsg);
 73     }else{
 74         printf("delete done\n");
 75     }
 76     return 0;
 77 }
 78 int calback(void *para, int f_num, char **f_value, char **f_name){
 79     int i;
 80     for(int i=0;i<f_num;i++){
 81         printf("%s",f_value[i]);
 82     }
 83     putchar(10);
 84     puts("+++++++++++++++++++");
 85     return 0;
 86 }
 87 
 88 int do_quary(sqlite3* db){
 89     char sql[128];
 90     char* errmsg;
 91 
 92     sprintf(sql,"select * from stu;");
 93     if(sqlite3_exec(db,sql,calback,NULL,&errmsg)!=SQLITE_OK){
 94         printf("%s\n",errmsg);
 95     }else{
 96         printf("quary done\n");
 97     }
 98 
 99 }
100 
101 int  main(){
102     sqlite3* db;
103     char* errmsg;
104     if(sqlite3_open(DATABASE,&db)!=SQLITE_OK){
105         printf("%s\n",sqlite3_errmsg(db));
106     }else{
107         printf("open database success\n");
108     }
109 
110     if(sqlite3_exec(db,"create table stu(id Integer,name char,score Integer);",NULL,NULL,&errmsg)!=SQLITE_OK){
111 
112         printf("%s\n",errmsg);
113     }else{
114 
115         printf("create table success\n");
116     }
117 
118 
119     int cmd;
120     while(1){
121         printf("******************************************************\n");
122         printf("input cmd:1:insert 2:update 3:delete 4:quary 5:quit\n");
123         printf("******************************************************\n");
124 
125         printf("input cmd:");
126         scanf("%d",&cmd);
127         getchar();
128 
129         switch(cmd){
130             case 1:
131                 do_insert(db);
132                 break;
133             case 2:
134                  do_update(db);
135                 break;
136             case 3:
137                 do_delete(db);
138                 break;
139             case 4:
140                 do_quary(db);
141                 break;
142             case 5:
143                 sqlite3_close(db);
144                 exit(1);
145             default:
146                 printf("input error\n");
147 
148         }
149     }
150 
151 }