SQlite数据库

SQlite数据库

1.SQLite简介

轻量化，易用的嵌入式数据库，用于设备端的数据管理，可以理解成单点的数据库。传统服务器型数据库用于管理多端设备，更加复杂

SQLite是一个无服务器的数据库，是自包含的。这也称为嵌入式数据库，这意味着数据库引擎作
为应用程序的一部分运行。
MySQL需要运行服务器，MySQL将需要客户端和服务器架构通过网络进行交互。

SQLite的优点	SQLite缺点	MySQL的优点	MySQL的缺点
1.基于文件，易于设置和使用	1.缺乏用户管理和安全功能	1.使用方便，提供了许多与数据库相关的功能	1.需要一些技术专业知识来设置
2.适合基础开发和测试，轻松携带	2.不容易扩展	2.良好的安全功能，易于扩展，适用于大型数据库	2.与传统SQL相比，语法略有不同
3.使用标准SQL语法进行微小更改，使用方便	3.不适合大数据库，无法定制	3.提供良好的速度和性能，提供良好的用户管理和多种访问控制

基于嵌入式的数据库主要有：SQLite，Firebird，Berkeley DB，eXtremeDB

Firebird 是关系型数据库，功能强大，支持存储过程，SQL兼容等

SQLite 关系型数据库，体积小，支持ACID事务

Berkeley DB 并没有数据库服务器的概念，他的程序直接链接到应用程序中

eXtremeDB 是内存数据库，运行效率高

2.SQLite数据库安装

下载方式：

安装方式一：

sudo apt-get -y install sqlite

安装方式二：

https://www.sqlite.org/download.html

获取到安装包后：

1. 把下载的文件sqlite-autoconf-3390000.tar.gz上传到开发板
2. tar xvf sqlite-autoconf-3390000.tar.gz 解压
3. cd sqlite-autoconf-3390000 进入文件夹
4. ./configure --prefix=/usr/local 配置安装路径在/usr/local
5. make 编译//比较久10分钟
6. sudo make install 安装

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

3.SQLite的命令用法

3.1.创建一个数据库

方式一：

1. sqlite3 进入数据库
2. .open test.db
3. .quit
数据库退出后在命令当前路径创建数据库test.db

方式二：

sqlite3 test.db //在命令运行当前窗口创建数据库test.db
在数据库命令下
.databases 列出当前打开的数据库
.quit 退出

3.2.创建一张表格

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

3.3.插入一条记录

insert into stu values(1,'huang',99);
insert into stu2 values(2,"gang",100); ''和""都行
insert into stu(name,score) values("huanggang",98); 插入部分字段内容

3.4.查看数据库的记录

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

3.5删除一条记录

delete from stu where id = 1;

---

3.6更改一条记录

update stu set name = 'huangg' where id = 1;

3.7删除一张表

drop table stu;

3.8增加一列

alter table stu add column sex char;

4.SQLite的编程操作

4.1打开/创建数据库的C接口

下面的 C 代码段显示了如何连接到一个现有的数据库。如果数据库不存在，那么它就会被创建，最后将返回一个数据库对象。

sqlite3_open(const char *filename, sqlite3 **ppDb)
该例程打开一个指向 SQLite 数据库文件的连接，返回一个用于其他 SQLite 程序的数据库连接对象。sqlite3_close(sqlite3*)
该例程关闭之前调用 sqlite3_open() 打开的数据库连接。所有与连接相关的语句都应在连接关闭之前完
成。
如果还有查询没有完成，sqlite3_close() 将返回 SQLITE_BUSY 禁止关闭的错误消息。const char *sqlite3_errmsg(sqlite3*);
sqlite3_errcode() 通常用来获取最近调用的API接口返回的错误代码.

对应的宏如下图

示例代码：

demo1

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
int main(int argc, char* argv[])
{sqlite3 *db;char *zErrMsg = 0;int rc;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");}sqlite3_close(db);
}

demo2

#include <stdio.h>
#include <sqlite3.h>
int main(char argc, char **argv)
{sqlite3 *db;sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg)int ret;if(argc < 2){printf("Usage: %s xxx.db\n",argv[0]);return -1;}if( (ret = sqlite3_open(argv[1],&db)) == SQLITE_OK){printf("open %s success\n",argv[1]);}else{printf("error:%s,%d\n",sqlite3_errmsg(db),ret);if(ret == 14){printf("permission den\n");}return -1;}sqlite3_close(db);printf("done\n");return 0;
}

4.2创建表的C接口

下面的 C 代码段将用于在先前创建的数据库中创建一个表：

先看这个API

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

sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char
**errmsg)
该例程提供了一个执行 SQL 命令的快捷方式，SQL 命令由 sql 参数提供，可以由多个 SQL 命令组成。第一个参数： sqlite3 是打开的数据库对象
第二个参数：sql语句
第三个参数：sqlite_callback 是一个回调
第四个参数：传递给回调的数据
第五个参数：将被返回用来获取程序生成的任何错误。

callback（回调）

int callback(void *arg, int column_size, char *column_value[], char*column_name[])第一个参数：void *arg：是sqlite3_exec函数的第四个参数
第二个参数：column_size：数据库的字段数
第三个参数：column_value[]：列的值
第四个参数：column_name：字段名字说明：返回值必须返回0，这样数据库有多少条数据，这个回调函数就会被调用多少次

代码demo1

#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;//必须返回0，这样数据库中有多少条数据,这个回调函数就会被调用多少次
}
int main(char argc, char **argv)
{sqlite3 *db;char *errorMes = NULL;int ret;if(argc < 2){printf("Usage: %s xxx.db\n",argv[0]);return -1;}if( (ret = sqlite3_open(argv[1],&db)) == SQLITE_OK){printf("open %s success\n",argv[1]);}else{printf("error:%s,%d\n",sqlite3_errmsg(db),ret);if(ret == 14){printf("permission den\n");}return -1;}//sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char**errmsg)sqlite3_exec(db, "select * from stu;",callback, "content of sql:",&errorMes);//errorMes may sigment error!sqlite3_close(db);printf("done\n");return 0;
}

实现一条建立表格的指令

#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");4.4.3 插入数据的C接口下面的 C 代码段显示了如何在上面创建的 COMPANY 表中创建记录：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(stdout, "Opened database successfully\n");}/* Create SQL statement */sql = "CREATE TABLE COMPANY(" \"ID INT PRIMARY KEY NOT NULL," \"NAME TEXT NOT NULL," \"AGE INT NOT NULL," \"ADDRESS CHAR(50)," \"SALARY REAL );";/* 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, "Table created successfully\n");}sqlite3_close(db);return 0;
}

4.3插入数据的c接口

下面的 C 代码段显示了如何在上面创建的 COMPANY 表中创建记录：

#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;
}

4.4SELECT操作

在我们开始讲解获取记录的实例之前，让我们先了解下回调函数的一些细节，这将在我们的实例使用

到。这个回调提供了一个从 SELECT 语句获得结果的方式。它声明如下：

typedef int (*sqlite3_callback)(
void*, /* sqlite3_exec()的第四个参数传递的内容 */
int, /* 列 */
char**, /* 键值对的值 */
char** /* 键值对的键 */
);

如果上面的回调在 sqlite_exec() 程序中作为第三个参数，那么 SQLite 将为 SQL 参数内执行的每个

SELECT 语句中处理的每个记录调用这个回调函数。

下面的 C 代码段显示了如何从前面创建的 COMPANY 表中获取并显示记录：

#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;
}

4.5UPDATE操作

下面的 C 代码段显示了如何使用 UPDATE 语句来更新任何记录，然后从 COMPANY 表中获取并显示更新的记录：

#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;
}

4.6 DELETE操作

下面的 C 代码段显示了如何使用 DELETE 语句删除任何记录，然后从 COMPANY 表中获取并显示剩余的记录：

#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;
}

5.对上述的操作做一个融合

如下

#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;char *sql2;char *sql3;char *sql4;char *sql5;/* 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(stdout, "Opened database successfully\n");}/* Create SQL statement */sql = "CREATE TABLE COMPANY(""ID INT PRIMARY KEY NOT NULL,""NAME TEXT NOT NULL,""AGE INT NOT NULL,""ADDRESS CHAR(50),""SALARY REAL );";/* insert SQL statement */sql2 = "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 );";sql3 = "SELECT * from COMPANY";sql4 = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; ""SELECT * from COMPANY";sql5 = "DELETE from COMPANY where ID=2; ""SELECT * from COMPANY";/* 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, "Table created successfully\n");}/* Execute SQL statement */rc = sqlite3_exec(db, sql2, callback, 0, &zErrMsg);if (rc != SQLITE_OK){fprintf(stderr, "SQL error: %s\n", zErrMsg);sqlite3_free(zErrMsg);}else{fprintf(stdout, "Records created successfully\n");}rc = sqlite3_exec(db, sql3, callback, 0, &zErrMsg);if (rc != SQLITE_OK){fprintf(stderr, "SQL error: %s,rc=%d\n", zErrMsg, rc);sqlite3_free(zErrMsg);}rc = sqlite3_exec(db, sql4, callback, 0, &zErrMsg);if (rc != SQLITE_OK){fprintf(stderr, "SQL error: %s\n", zErrMsg);sqlite3_free(zErrMsg);}else{fprintf(stdout, "Operation done successfully\n");}rc = sqlite3_exec(db, sql5, callback, 0, &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;
}

6.把链表的数据写进数据库

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>struct Test
{int data;struct Test *next;
};struct Test *insertFromhead(struct Test *head, struct Test *new)
{struct Test *point = head;if (head == NULL){head = new;return head;}while (point->next != NULL){point = point->next;}point->next = new;return head;
}struct Test *creatLink(struct Test *head)
{struct Test *new = NULL;int i;int num;printf("please create link\n");scanf("%d", &num);for (i = 0; i < num; i++){new = (struct Test *)malloc(sizeof(struct Test));new->next = NULL;printf("please input NO %d Link data\n", i + 1);scanf("%d", &(new->data));head = insertFromhead(head, new);}return head;
}void printLink(struct Test *head)
{struct Test *point = head;while (point != NULL){printf("%d ", point->data);point = point->next;}putchar('\n');
}int callback(void *arg, int column_size, char *column_value[], char *column_name[])
{int i;struct Test *head = NULL;head = (struct Test *)arg;while (head != NULL){for (i = 0; i < column_size; i++){printf("%s=%s\n", column_name[i], column_value[i]);}printf("=================================\n");head = head->next;}return 0;
}void save_SQL(struct Test *head)
{struct Test *point = head;sqlite3 *db = NULL;char *errmsg = NULL;char buf[128] = {0};int ret = 0;ret = sqlite3_open("link.db", &db);if (ret){fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));exit(0);}else{fprintf(stdout, "Opened database successfully\n");}sprintf(buf, "create table base (ID Integer)");ret = sqlite3_exec(db, buf, NULL, NULL, &errmsg);if (ret != SQLITE_OK){printf("create table error:%s,ret=%d\n", errmsg, ret);}while (point != NULL){memset(buf, 0, 128);sprintf(buf, "insert into base values (%d)", point->data);sqlite3_exec(db, buf, NULL, NULL, NULL);point = point->next;}sqlite3_close(db);
}void read_SQL(struct Test *head)
{sqlite3 *db = NULL;char *errmsg = NULL;char buf[128] = {0};int ret = 0;ret = sqlite3_open("link.db", &db);if (ret){fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));exit(0);}else{fprintf(stdout, "Opened database successfully\n");}sprintf(buf, "select * from base");ret = sqlite3_exec(db, buf, callback, (void *)head, &errmsg);if (ret != SQLITE_OK){printf("create table error:%s,ret=%d\n", errmsg, ret);}sqlite3_close(db);
}int main()
{struct Test *head = NULL;head = creatLink(head);printLink(head);// 把链表得数据插入数据库save_SQL(head);// 查询数据库中得数据read_SQL(head);return 0;
}

read_SQL(struct Test *head)
{
sqlite3 *db = NULL;
char *errmsg = NULL;
char buf[128] = {0};

int ret = 0;ret = sqlite3_open("link.db", &db);
if (ret)
{fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));exit(0);
}
else
{fprintf(stdout, "Opened database successfully\n");
}sprintf(buf, "select * from base");ret = sqlite3_exec(db, buf, callback, (void *)head, &errmsg);
if (ret != SQLITE_OK)
{printf("create table error:%s,ret=%d\n", errmsg, ret);
}sqlite3_close(db);
}