【回眸】香橙派zero2 嵌入式数据库SQLite-CSDN博客

本文链接：https://blog.csdn.net/tianbutian_/article/details/148065751

前言

还有2个项目没更新完...披星戴月更新中...

SQLite介绍

基于嵌入式的数据库主要有：SQLite，Firebird，Berkeley DB，eXtremeDB
Firebird 是关系型数据库，功能强大，支持存储过程，SQL兼容等
SQLite 关系型数据库，体积小，支持ACID事务
Berkeley DB 并没有数据库服务器的概念，他的程序直接链接到应用程序中
eXtremeDB 是内存数据库，运行效率高

安装SQLite3

下载SQLite

SQLite Download Pagehttps://www.sqlite.org/download.html

把下载的文件sqlite-autoconf-3490200.tar.gz 通过Mobaxterm上传到开发板

使用 tar xvf sqlite-autoconf-3490200.tar.gz 命令解压

cd sqlite-autoconf-3490200 进入文件夹
./configure --prefix=/usr/local 配置安装路径在/usr/local

make 编译//比较久10分钟

sudo make install

安装成功后，运行sqlite3 进入SQL命令操作流程

SQLite 使用

创建数据库

方式1：

1 . sqlite3 进入数据库

sqlite3

2 . .open test .db

.open test.db

3 . .quit

.quit

数据库退出后在命令当前路径创建数据库 test .db

方法二：

sqlite3 test .db // 在命令运行当前窗口创建数据库 test .db

在数据库命令下

.databases 列出当前打开的数据库

.quit 退出

创建一张表格

sqlite3

create table stu2(id Integer,name char,score Integer);
create table stu(id Integer,name char,score Integer);

插入数据

insert into stu2 values(19104040123,'dd',99);
insert into stu2 values(19104040123,"pjy",100); 
insert into stu2(name,score) values("yzx",98);

insert into stu values(19104040123,'dd',99);
insert into stu values(19104040123,"pjy",100); 
insert into stu(name,score) values("yzx",98);

查看数据库的记录

select * from stu2;
select * from stu;

select * from stu; //查询所有字段的结果
select name,score from stu; //查询数据库中部分字段的内容

删除一条记录

delete from stu where id = 19104040123;

更改一条记录

update stu set name = 'pjy' where id = 19104040123;

删除一张表

drop table stu;

增加一列（性别）

alter table stu add column sex char;

SQLite编程操作

增加一个db，C语言编程

openSQLite.c

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

int main(char argc,char **argv)
{
    sqlite3 *db;
    int ret;
    if(argc < 2)
    {
        printf("Usage:%s xxx.db\n",argv[0]);
        return -1;
    }
    ret = sqlite3_open(argv[1],&db);
    if (ret == SQLITE_OK)
    {
        printf("open %s success\n",argv[1]);
    }
    else
    {
        printf("error:%s,%d\n",sqlite3_errmsg(db),ret);
        return -1;
    }
    sqlite3_close(db);
    printf("close db\n");
    return 0;
}

gcc openSQLite.c -lsqlite3
./a.out new.db

创建一个表（sqlite3_exec API）

sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg)

sqlite3*            : open 打开的数据库
const char* sql,    : 执行的sql功能语句
*callback,          : sql语句对应的回调函数
void* data,         : 传递给回调函数的 指针参数
char **errmsq       : 错误信息

execSQLite.c

#include <stdio.h>
#include <sqlite3.h>
int callback(void *arg, int column_size, char *column_value[], char*column_name[])
{
int i;
printf("arg=%s\n",(char *)arg);
for(i=0;i<column_size;i++){
printf("%s = %s\n", column_name[i], column_value[i]);
}
printf("=======================\n");
return 0;
}

int main(char argc,char**argv)
{
    sqlite3 *db;
    int ret;
    if(argc < 2)
    {
        printf("Usage:%s xxx.db\n,argv[0]");
        return -1;
    }
    ret = sqlite3_open(argv[1],&db);
    if (ret == SQLITE_OK)
    {
        printf("open %s success\n",argv[1]);
    }
    else
    {
        printf("error:%s,%d\n",sqlite3_errmsg(db),ret);
        return -1;
    }
    //int sqlite3_exec(sqlite3*,const char *sql,int (*callback)(void*,int,char**,char**),void *,char **errmsg);
    sqlite3_exec(db, "select * from stu;",callback, "content of sql:",&errorMes);


    sqlite3_close(db);
    printf("close db\n");
    return 0;
}

创建表及记录

createSQLite.c

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
    int i;
    for (i = 0; i < argc; i++)
    {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}
int main(int argc, char *argv[])
{
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;
    char *sql;
    /* Open database */
    rc = sqlite3_open("test.db", &db);
    if (rc)
    {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        exit(0);
    }
    else
    {
        fprintf(stderr, "Opened database successfully\n");
    }
    /* Create SQL statement */
    sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
          "VALUES (1, 'Paul', 32, 'California', 20000.00 ); "
          "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
          "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "
          "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)"
          "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );"
          "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)"
          "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
    /* Execute SQL statement */
    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else
    {
        fprintf(stdout, "Records created successfully\n");
    }
    sqlite3_close(db);
    return 0;
}

SELECT 操作

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **azColName)
{
    int i;
    fprintf(stderr, "%s: ", (const char *)data);
    for (i = 0; i < argc; i++)
    {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}
SQLdataToLink()
    LinkToSQL() int main(int argc, char *argv[])
{
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;
    char *sql;
    const char *data = "Callback function called";
    /* Open database */
    rc = sqlite3_open("test.db", &db);
    /* Create SQL statement */
    sql = "SELECT * from COMPANY";
    /* Execute SQL statement */
    rc = sqlite3_exec(db, sql, callback, (void *)data, &zErrMsg);
    sqlite3_close(db);
    return 0;
}

UPDATE 操作

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **azColName)
{
    int i;
    fprintf(stderr, "%s: ", (const char *)data);
    for (i = 0; i < argc; i++)
    {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}
int main(int argc, char *argv[])
{
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;
    char *sql;
    const char *data = "Callback function called";
    /* Open database */
    rc = sqlite3_open("test.db", &db);
    if (rc)
    {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        exit(0);
    }
    else
    {
        fprintf(stderr, "Opened database successfully\n");
    }
    /* Create merged SQL statement */
    sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; "
          "SELECT * from COMPANY";
    /* Execute SQL statement */
    rc = sqlite3_exec(db, sql, callback, (void *)data, &zErrMsg);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else
    {
        fprintf(stdout, "Operation done successfully\n");
    }
    sqlite3_close(db);
    return 0;
}

DELETE操作

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **azColName)
{
    int i;
    fprintf(stderr, "%s: ", (const char *)data);
    for (i = 0; i < argc; i++)
    {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}
int main(int argc, char *argv[])
{
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;
    char *sql;
    const char *data = "Callback function called";
    /* Open database */
    rc = sqlite3_open("test.db", &db);
    if (rc)
    {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        exit(0);
    }
    else
    {
        fprintf(stderr, "Opened database successfully\n");
    }
    /* Create merged SQL statement */
    sql = "DELETE from COMPANY where ID=2; "
          "SELECT * from COMPANY";
    /* Execute SQL statement */
    rc = sqlite3_exec(db, sql, callback, (void *)data, &zErrMsg);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else
    {
        fprintf(stdout, "Operation done successfully\n");
    }
    sqlite3_close(db);
    return 0;
}