SQLite数据库

最新推荐文章于 2021-02-05 19:44:12 发布

转载最新推荐文章于 2021-02-05 19:44:12 发布 · 268 阅读

文章标签：

#数据库

嵌入式专栏收录该内容

2 篇文章

订阅专栏

List item

SQLite简介：
SQLite是一款轻量级的开源的嵌入式数据库，由D.Richard Hipp在2000年发布。SQLite使用方便，性能出众，广泛应用于消费电子、医疗、工业控制、军事等各种领域。
原文
 Linux平台上SQLite数据库教程（一）——终端使用篇
SQLite SQL语句

1、sqlite命令

创建或打开一个数据库：sqlite3 dbname.db（所在目录必须具有读写权限）

版本查看：sqlite3 -version

创建数据库：sqlite3 xxxx.db

退出sqlite命令行：.quit或.exit或.q

列出当前显示格式的配置：.show

查看数据库中的表：.tables

显示表结构：.schema

退出数据库：ctrl+d

.dump：生成整个数据库的脚本在终端显示
.output stdout：将输出打印到屏幕
.output filename：导出数据库到SQL文件

.read filename：从SQL文件导入数据库

.output filename.csv：格式化输出数据到CSV格式

.import [filename.csv ] newtable：从CSV文件导入数据到表中
sqlite3 [database] .dump > [filename]：备份数据库
实例：sqlite3 mytable.db .dump > backup.sql
sqlite3 [database ] < [filename ]：恢复数据库
实例：sqlite3 mytable.db < backup.sql

2、字段类型

NULL: 空值
INTEGER: 整数,依据值的大小可以依次被存储为1,2,3,4,5,6,7,8个字节
REAL: 所有值都是浮动的数值,被存储为8字节的IEEE浮动标记序号
TEXT: 文本，值为文本字符串,使用数据库编码存储(TUTF-8, UTF-16BE or UTF-16-LE).
BLOB: 值是BLOB数据,如何输入就如何存储,不改变格式

SQL语句中部分的带双引号或单引号的文字被定义为文本，如果文字没带引号并没有小数点或指数则被定义为整数，如果文字没带引号但有小数点或指数则被定义为实数，如果值是空则被定义为空值，BLOB数据使用符号X'ABCD'来标识。

smallint 16位的整数。
interger 32位的整数。
decimal(p,s) 精确值p是指全部有几个十进制数,s是指小数点后可以有几位小数。如果没有特别指定，则系统会默认为p=5 s=0 。
float 32位元的实数。
double 64位元的实数。
char(n) n 长度的字串，n不能超过 254。
varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。
graphic(n) 和 char(n) 一样，不过其单位是两个字节， n不能超过127。这个形态是为了支持两个字节长度的字体，如中文字。
vargraphic(n) 可变长度且其最大长度为n的双字元字串，n不能超过2000
date 包含了年份、月份、日期。
time 包含了小时、分钟、秒。
timestamp 包含了年、月、日、时、分、秒、千分之一秒。

3、表操作

建立表

create table table_name(field type1,field type1,….);

table_name是要创建数据表的名称，field是数据库表内字段名字，type则是字段类型。例如：

CREATE TABLE student(

ID INTEGER PRIMARY KEY,

LastName varchar(255),

FirstName varchar(255)

);

删除表

DROP TABLE tableName;

查看表

SELECT * FROM tablename WHERE someField = ‘value’ COLLATE NOCASE;

4、字段操作

A、向表中添加新记录

insert into tabelname values (value1, value2,…);

实例：

insert into people values(1,‘A’,10);

insert into people values(2,‘B’,13);

insert into people values(3,‘C’,9);

insert into people values(4,‘C’,15);

insert into people values(5,NULL,NULL);

字符串要用单引号括起来

B、查询表中所有记录

select * from tablename ;

实例： select * from people;

按某个字段查询表中没有重复的条目

SELECT distinct someField FROM table

C、按指定条件查询表中记录

select * from tablename where field=value；

实例：

在表中搜索名字是A的项所有信息

select * from people where name=‘A’;

在表中搜索年龄>=10并且<=15的项的所有信息

select * from people where age>=10 and age<=15;

在表中搜索名字是C的项，显示其name和age

select name,age from people where name=‘C’;

显示表中的前2项所有信息

select * from people limit 2;

显示以年龄排序表中的信息

select * from people order by age;

D、按指定条件删除表中记录

delete from where

实例：

删除表中名字是’C’的项

delete from pople where name=‘C’;

E、更新表中记录

update set , … where ;

实例：

将表中年龄是15并且ID是4项，名字改为CYG

update people set name=‘CYG’ where id=4 and age=15;

用一张表TableB里的一个字段fieldB内容给另外一张表TableA里的一个字段fieldA赋值:

UPDATE TableA SET fieldA = TableB.fieldB

如果是同一张表TableA中，用一个字段field1的值给表中的另外一个字段field2值赋值:

UPDATE TableA SET field2 = field1

如果需要把一个字符串(‘someString’)和一个字段field1的值进行连接，然后赋值给一个字段field2：

UPDATE TableA SET field2 = field1 || ‘someString’

F、在表中添加字段

alter table add column ;

实例：

在people表中添加一个addr字段

alter table people add column addr;

G、删除表中的一个字段

删除people表中字段addr，操作流程如下：

将people表重命名为temp

重新创建people表

将temp表中的相应字段内容复制到people表中

删除temp表

SQL语句如下:

alter table people rename to temp;

create table people(id,name,age);

insert into people select id,name,age from temp;

drop table temp;

H、表的导入

把一张表TableA里的数据导入到另外一张表TableB中（两张表中的结构和字段必须一样）：

INSERT INTO TableB SELECT * FROM TableA

5、分组统计

CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));

GROUP BY 进行分组统计数据，命令如下：

SELECT NAME, SUM(SALARY) SALARY_SUM, COUNT(1) COUNT_NUM FROM COMPANY GROUP BY NAME;

6、排序

ORDER BY 进行排序，命令如下：

SELECT NAME, SUM(SALARY) SALARY_SUM, COUNT(1) COUNT_NUM FROM COMPANY GROUP BY NAME ORDER BY SALARY_SUM ASC;

7、从excel表中导入数据

A、将Excel之中存储的数据另存为csv的格式bookroom.csv，注意不要带表头，只要数据就行

B、根据要导入的表属性创建表

create table bookroom(id integer, roomname nvarchar(20), mapfilename nvarchar(20));

C、设置数据的分隔符

.separator ‘,’;

D、将数据导入表

.import bookroom.csv bookroom

.import <输入文件名> <插入表名>

E、查阅插入的数据

select * from bookroom;

五、SQLite数据库编程

SQLite数据库编程最常用到的是sqlite3 *类型。从数据库打开开始，sqlite就要为sqlite3 *类型准备好内存，直到数据库关闭，整个过程都需要用到sqlite3 *类型。当数据库打开时开始，sqlite3 *类型的变量就代表了要操作的数据库，即句柄。

1、sqlite3_open

int sqlite3_open(char *path,sqlite3 **db);

功能：打开sqlite数据库

path:数据库文件路径(如果不存在，则创建)

db:指向sqlite句柄的指针

返回值:如果是SQLITE_OK则表示操作正常。其他的返回值参考sqlite3.h文件定义的宏。

2、sqlite3_close

int sqlite3_close(sqlite3 *db);

功能：关闭sqlite数据库

放回值：成功返回0，失败返回错误码

3、sqlite3_errmsg

const char *sqlite3_errmsg(sqlite3 *db);

返回值：返回错误信息

4、执行SQL语句

typedef int (*sqlite3_callback)(void *, int ,char **, char **);

int sqlite3_exec(sqlite3 *db , const char *sql ,

sqlite3_callback callback ,void *arg, char **errmsg );

db参数是前面sqlite3_open函数得到的指针

sql参数是一条字符串格式sql语句，以\0结尾。

callback参数是回调函数，当这条语句执行之后,sqlite3会去调用你提供的这个函数。

arg参数是可以传递的指针参数，传到回调函数里面，如果不需要传递指针给回调函数，可以填NULL。

errmsg 参数是错误信息。sqlite3里面有很多固定的错误信息。执行sqlite3_exec后，如果执行失败可以查阅这个指针。

返回值：成功返回0，失败返回错误码

如果调用sqlite3_exec失败，printf("%s\n",errmsg)可以得到一串字符串错误信息。

sqlite3_callback  callback和void  *arg都可以填NULL。NULL表示不需要回调。比如insert操作，delete操作，就没有必要使用回调。而当做select时，就要使用回调，因为sqlite3把数据查出来，得通过回调告诉你查出了什么数据。

typedef int (*sqlite3_callback)(void *para,int n_column,char **column_value,char **column_name);

para参数：传入的特殊指针(比如类指针、结构指针)，然后操作指针指向的数据。

n_column：是记录有多少个字段(即记录有多少列)。

char  **column_value：是个关键值，查出来的数据都保存在这里，它实际上可以看做是一个一维的指针数组，每个元素都是一个char *值，是一个字段的内容(用字符串来表示，以\0结尾)。

char  **column_nam：跟column_value是对应的，表示这个字段的字段名称 。

程序实例sqlite3_callback.c：

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

#define MAX 100

typedef int (*sqlite3_callback)(void *, int , char **, char **);

int show_table_info(void arg, int n_column, char column_value, char column_name)
{
int i = 0;
for(i = 0; i < n_column; i++)
{
printf("%s\t", column_name[i]);
}
printf("\n********************************\n");
for(i = 0; i < n_column; i++)
{
printf("%s\t", column_value[i]);
}
printf("\n\n");
return 0;
}

int exec_sql_string(char *sql_string, sqlite3 *db)
{
char *errmsg;
if(sqlite3_exec(db, sql_string, show_table_info, NULL, &errmsg) != 0)
{
fprintf(stderr, “Fail to exec sql(%s) : %s.\n”, sql_string,errmsg);
return -1;
}
return 0;
}

int main(int argc,char *argv[])
{
sqlite3 *db;
int result;
char sql_buf[MAX];

if(argc < 2)
{
    fprintf(stderr, "usage : %s argv[1].\n", argv[0]);
    exit(EXIT_FAILURE);
}

result = sqlite3_open(argv[1], &db);
if(result != SQLITE_OK)
{
    fprintf(stderr, "Fail to sqlite3_open %s : %s.\n", argv[1], sqlite3_errmsg(db));
    exit(EXIT_FAILURE);
}
while(1)
{
    printf("sqlite >");
    fgets(sql_buf, sizeof(sql_buf), stdin);
    sql_buf[strlen(sql_buf) - 1] = '\0';
    if(strncmp(sql_buf, ".quit", 5) == 0)
       break;
    exec_sql_string(sql_buf,db);
}

result = sqlite3_close(db);
if(result != 0)
{
    fprintf(stderr, "Fail to sqlite3_open %s : %s.\n", argv[1], sqlite3_errmsg(db));
    exit(EXIT_FAILURE);
}
exit(EXIT_SUCCESS);

}
编译：gcc -o sqlite3_callback sqlite3_callback.c -lsqlite3

5、不使用回调函数执行SQL语句

int sqlite3_get_table(sqlite3 *db, const char *sql, char ***resultp,

int *nrow, int *ncolumn,char **errmsg);

功能：执行sql操作

db : 数据库句柄

sql : sql语句

resultp : 用来指向sql执行结果的指针

nrow : 满足条件的记录的数目

ncolumn : 每条记录包含的字段数目

从第0索引到第ncolumn-1索引都是字段的名称

从第ncolumn索引开始，后面都是字段的值

errmsg : 错误信息指针的地址

返回值：成功返回0，失败返回错误码

程序实例sqlite3_nocallback.c：

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

#define MAX 100

int exec_sql_string(char *sql_string, sqlite3 *db)
{
char *errmsg, **dbResult;
int nRow, nColumn;
int result, i, j, index;

result = sqlite3_get_table(db, sql_string, &dbResult, &nRow, &nColumn, &errmsg);
if(0 != result)
{
    fprintf(stderr, "Fail to exec sql(%s) : %s.\n", sql_string, errmsg);
    return -1;
}
//字段名字
for(j = 0; j < nColumn; j++)
{
    printf("%s\t", dbResult[j]);
}
printf("\n");
index = nColumn;//从它开始是字段对应的值
for(i = 0; i < nRow; i++)//查询到总共记录个数
{
    for(j = 0; j < nColumn; j++)
    {
        printf("%s\t", dbResult[index]);
        index++;
    }
    printf("\n");
}        
//释放查询结果所分配的内存
sqlite3_free_table(dbResult);
return 0;

}

int main(int argc, char *argv[])
{
sqlite3 *db;
int result;
char sql_buf[MAX];
if(argc < 2)
{
fprintf(stderr, “usage : %s argv[1].\n”, argv[0]);
exit(EXIT_FAILURE);
}
result = sqlite3_open(argv[1], &db);
if(result != SQLITE_OK)
{
fprintf(stderr, “Fail to sqlite3_open %s : %s.\n”, argv[1], sqlite3_errmsg(db));
exit(EXIT_FAILURE);
}
while(1)
{
printf(“sqlite >”);
fgets(sql_buf, sizeof(sql_buf), stdin);
sql_buf[strlen(sql_buf) - 1] = ‘\0’;

    if(strncmp(sql_buf,".quit",5) == 0)
        break;

    exec_sql_string(sql_buf, db);
}
result = sqlite3_close(db);
if(result != 0)
{
    fprintf(stderr, "Fail to sqlite3_open %s : %s.\n", argv[1], sqlite3_errmsg(db));
    exit(EXIT_FAILURE);
}
exit(EXIT_SUCCESS);

}
编译： gcc -o sqlite3_nocallback sqlite3_nocallback.c -lsqlite3