MySQL占用内存太大，而SQLite是一款轻量级零配置数据库，非常适合在树莓派和其他嵌入式系统中使用。SQLite文档详细资料丰富，本文不会详细解释SQLite数据库操作的方方面面，只能结合具体场景按需说明。本文介绍的SQLite技巧也可以在其他平台使用，并不局限于树莓派。
安装 SQLite
sudo apt-get update sudo apt-get install sqlite sqlite3 #如果需要的话还可以顺便安装 PHP 相关组件 sudo apt-get install php5-fpm php5-sqlite
使用示例
1.创建表和插入数据
新建一个名为insert.sql文件，文件具体内容如下：
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】采用多种不同的插入方法，第一种不含字段名称，第二种包含字段名称，第三种，由于创建表格时有默认值，可以使用更简洁的插入方法，例如
INSERT INTO temps(temperature) VALUES(40.4);
2.创建表和插入数据
创建一个名为create-table.sh脚本，具体内容如下
#!/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的脚本，具体内容如下
#!/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】执行结果如下，从结果来看插入的时间间隔一个小时，符合预期效果。
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
【修改时间顺序】——时间倒序输出
#!/bin/sh DBNAME="cpu.db" sqlite3 $DBNAME "select * from temps ORDER BY tdatetime DESC;"
【1】ORDER BY tdatetime DESC 以tdatetime降序排列。
【2】输出结果
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个小时的数据
#!/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。
RPi.CPU|2014-08-02 19:27:47|40.3 RPi.CPU|2014-08-02 20:27:47|40.4
【限制时间】——查询某个时间段内数据
#!/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');"
【1】2014-08-02 19:00:00 年必须占4个数字，其他必须占2个数字，不足时使用0不足。
【2】此处不需要增加localtime参数，具体原因未知。
4.总结
【1】创建表格时可使用DEFAULT约束，增加默认值简化插入操作，避免空值。
【2】INSERT操作含有多种方法，根据实际情况选用。
【3】SQLite datetime函数需要指定localtime参数，指定本地时区。
5.参考资料
出处：http://blog.csdn.net/xukai871105
请问有pi 4的消息吗？
回楼上，树莓派4代最新海外消息如下
Latest Raspberry Pi 4 News
26/01/2017: The Raspberry Pi Foundation has announced Google will be helping integrate AI tools into Raspberry Pi, presumably coming out with the launch of Raspberry Pi 4.
The company is inviting developers to work with Google to introduce such features to the Raspberry Pi ecosystem and so has invited the Raspberry Pi community to give feedback via a survey.
“Hi, makers! Thank you for taking the time to take our survey,” Google wrote in its announcement. “We at Google are interested in creating smart tools for makers, and want to hear from you about what would be most helpful. As a thank you, we will share our findings with the community so that you can learn more about makers around the world.”
Whether developers would like to see facial or emotional recognition, speech-to-text translation or natural language processing, it looks likely Google is eager to help developers integrate such features into their Raspberry Pi innovations.
“The survey will help [Google] get a feel for the Raspberry Pi community, but it’ll also help us get the kinds of services we need,” the Raspberry Pi Foundation said.
It is encouraging everyone active in the community to fill out Google’s survey and help users get the tools they need.
Raspberry Pi 4 features
Although the Raspberry Pi Foundation has not revealed any details at all about the upcoming version, a number of sources have started throwing their predictions around.
Rasim Muratovic from RasmurTech speculates it’ll use Broadcom’s latest BCM4908 processor, which is the replacement for the Broadcom BCM2837 SoC, which features a 1.2 GHz 64-bit quad-core ARM Cortex-A53 processor. The Broadcom BCM4908 SoC brings with it a 28nm 1.8GHz ARM CPU.
Muratovic also reckons it’ll include 2GB RAM – a logical step up in speed and this would mean it could use Mate (Gnome 2) as its desktop environment by default rather than XFCE or LXDE. He thinks 4GB of RAM will be too much, although it’ll come as a bonus if it seems the Raspberry Pi Foundation makes the decision to ramp up the RAM substantially.
Many supporters of Raspberry Pi are calling for a USB 3.0 port for high-speed data transfer and better power management.
We expect Raspberry Pi 4 to take the same form factor as its previous iterations as it seems the Foundation likes to keep things familiar, just upgrading selected components.
As such, we expect to see the same design as its predecessors, including similar port configurations, with support to add peripheries to help creators make the most out of their micro computer.
Raspberry Pi 4 release date and availability
It’s expected that version 4 of the Raspberry Pi platform will arrive in February, as is the usual release schedule for the microcomputer.
However, other sources aren’t so optimistic and think Raspberry Pi 3 will be the only version of the platform available for at least three years, pushing back the Raspberry Pi 4 release date all the way back to 2019.
The reason for this distant launch date is because the company manufactured almost 600,000 Raspberry Pi 3 boards and so it needs to be at least close to shifting those before introducing a new one to the market – the overheads would just be too high for such a low cost device.
We’re not sure we can wait until 2019 for a new version of Raspberry Pi, so let’s at least hope the company announces some significant enhancements to the current version in the coming months.
Raspberry Pi 4 pricing
The biggest selling point of the Raspberry Pi is that it’s a fully-fledged computer that will set you back next to nothing in comparison to other computers on the market. The catch is that you’ll probably need to spend extra on peripheries, but those who like to tinker with their machines are presented with a decent enough spec for a fraction of the cost.
In keeping with the Raspberry Pi Foundation’s mission to provide everyone with an affordable computer, the Raspberry Pi 4 is very unlikely to cost more than the Raspberry Pi 3, which has a recommended retail price of £32.95.
Although, like its predecessor, it’s likely retailers will ship the microcomputer as part of a kit to ensure users can start creating their computers right from the box rather than buying up their own components to transform it into a PC.
http://www.itpro.co.uk/desktop-hardware/27763/raspberry-pi-4-raspberry-pi-gets-ai
请问有arm的sqlite3的node模块吗
你好，按照文中的方式尝试，总是报无法打开SQL,求教，谢谢
pi@raspberrypi:~/python-works/cpu-temp $ sudo sh ./create-table-only.sh
开始插入数据
: No such file-only.sh: 5: ./create-table-only.sh: cannot open create-table-only.sql
插入完成