盐城seo网站优化软件凡科网站建站教程
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);
}