树莓派SQLite3的安装和操作简述

MySQL占用内存太大，而SQLite是一款轻量级零配置数据库，非常适合在树莓派和其他嵌入式系统中使用。SQLite文档详细资料丰富，本文不会详细解释SQLite数据库操作的方方面面，只能结合具体场景按需说明。本文介绍的SQLite技巧也可以在其他平台使用，并不局限于树莓派。

安装 SQLite

1
2
3
4

sudo apt-get update
sudo apt-get install sqlite sqlite3
#如果需要的话还可以顺便安装 PHP 相关组件
sudo apt-get install php5-fpm php5-sqlite

使用示例

1.创建表和插入数据

新建一个名为insert.sql文件，文件具体内容如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
 
CREATE TABLE temps(
    name TEXT DEFAULT 'RPi.CPU',
    tdatetime DATETIME DEFAULT (datetime('now', 'localtime')),
    temperature NUMERIC NOT NULL
);
     
INSERT INTO temps
VALUES('RPi.CPU', datetime('now', 'localtime', '-3 hours'), 40.1);
 
INSERT INTO temps(name, tdatetime, temperature)
VALUES('RPi.CPU', datetime('now', 'localtime', '-2 hours'), 40.2);
 
INSERT INTO temps(tdatetime, temperature)
VALUES(datetime('now', 'localtime', '-1 hours'), 40.3);
 
INSERT INTO temps(temperature)
VALUES(40.4);
 
COMMIT;

【1】创建表，表中包括3个字段，分别为name，tdatetime和 temperature。
【2】name DEFAULT ‘RPi.CPU’，name字段的默认值为’RPi.CPU’，SQLite中字符串被单引号包裹。
【3】tdatetime DATETIME DEFAULT (datetime(‘now’, ‘localtime’)), tdatetime字段默认值为当前时间。
【4】datetime(‘now’, ‘localtime’)中的localtime表示本时区时间，如果没有该参数则为格林尼治时间。
【5】DEFAULT (datetime(‘now’, ‘localtime’)), 括号绝对不能少。DEFault中的表达式一定要被括号包裹。
【6】采用多种不同的插入方法，第一种不含字段名称，第二种包含字段名称，第三种，由于创建表格时有默认值，可以使用更简洁的插入方法，例如

1

INSERT INTO temps(temperature) VALUES(40.4);

2.创建表和插入数据

创建一个名为create-table.sh脚本，具体内容如下

1
2
3
4
5

#!/bin/sh
rm -f cpu.db
echo 开始插入数据
sqlite3 cpu.db < insert.sql
echo 插入完成

【1】数据库名称为cpu.db
【2】sqlite3 cpu.db < insert.sql 把insert.sql脚本插入到数据库中，insert.sql包括两个步骤，建立表并向表中插入数据。
【3】修改执行权限 chmod a+x create-tabel.sh，然后执行./create-table.sh
【4】SQLite也有命令行模式，但是命令没有历史查询功能，写错了还要重新写一遍，所有shell脚本指令更利于修改和尝试。

3.查询内容

【简单查询】
新建一个名为show.sh的脚本，具体内容如下

1
2
3

#!/bin/sh
DBNAME="cpu.db"
sqlite3 $DBNAME "SELECT * FROM temps;"

【1】数据库名称为cpu.db。
【2】sqlite3 $DBNAME “select * from temps;” 查询表中所有记录。
【3】修改执行权限 chmod a+x show.sh，然后执行./show.sh
【4】执行结果如下，从结果来看插入的时间间隔一个小时，符合预期效果。

1
2
3
4

RPi.CPU|2014-08-02 17:27:47|40.1
RPi.CPU|2014-08-02 18:27:47|40.2
RPi.CPU|2014-08-02 19:27:47|40.3
RPi.CPU|2014-08-02 20:27:47|40.4

【修改时间顺序】——时间倒序输出

1
2
3

#!/bin/sh
DBNAME="cpu.db"
sqlite3 $DBNAME "select * from temps ORDER BY tdatetime DESC;"

【1】ORDER BY tdatetime DESC 以tdatetime降序排列。
【2】输出结果

1
2
3
4

RPi.CPU|2014-08-02 20:27:47|40.4
RPi.CPU|2014-08-02 19:27:47|40.3
RPi.CPU|2014-08-02 18:27:47|40.2
RPi.CPU|2014-08-02 17:27:47|40.1

【限制时间】——返回最近3个小时的数据

1
2
3
4
5

#!/bin/sh
DBNAME="cpu.db"
sqlite3 $DBNAME "SELECT * FROM temps
                where tdatetime > datetime('now', 'localtime', '-3 hours')
                ORDER BY tdatetime ASC;"

【1】datetime(‘now’, ‘localtime’, ‘-3 hours’) 表示当前时间3个小时之前的时间点，一定要加上localtime参数
【2】where tdatetime > datetime(‘now’, ‘localtime’, ‘-3 hours’) 限制条件3个小时之前到现在。
【3】输出结果如下，特别说明操作的时间约为22:05。

1
2

RPi.CPU|2014-08-02 19:27:47|40.3
RPi.CPU|2014-08-02 20:27:47|40.4

【限制时间】——查询某个时间段内数据

1
2
3
4
5

#!/bin/sh
DBNAME="cpu.db"
sqlite3 $DBNAME "SELECT * FROM temps
                where tdatetime > datetime('2014-08-02 19:00:00') and
                         tdatetime <= datetime('2014-08-02 20:00:00');"