Sqlite3 的 shell 使用

目录 Content
[hide]

SQLite 是一款轻量级数据库系统,使用时很简单,只有一个文件,而在Win平台shell主程序也只有一个文件,使用很是方便。本文记录 LT 探索 SQLite 的过程。

一、SQLite 的使用

首先需要获取SQLite的程序 sqlite3,在Windows下可以直接下载官方的二进制文件包Precompiled Binaries for Windows,解压到相关路径,并将此路径包含在系统变path中。在Debian下,直接 “sudo apt-get install sqlite3” 进行安装。具体使用可参考官方详细文档 “Command Line Shell For SQLite“,对数据库的具体操作可参考SQL语法 SQL Syntax

1.建立数据库

使用 sqlite3 filename 命令行就可以创建或者打开名称为” filename”的文件,若要退出,在 > 后面输入 .quit 即可。这里我们以创建 test 为例。

D:\>sqlite3 test
SQLite version 3.8.7.4 2014-12-09 01:34:36
Enter ".help" for usage hints.
sqlite>

2.创建表

使用 create 命令创建表。比如我们要创建一个产品表,名字为 product ,然后存储其属性:

  • type :产品名称,文本类型。
  • para1 :生产参数1,浮点数类型。
  • para2 :生产参数2,浮点数类型。
  • para3 :生产参数3,浮点数类型。

那么使用命令

create table product(type TEXT, para1 REAL, para2 REAL, para3 REAL);

可以使用 .tables 显示数据库包含的表

D:\>sqlite3 test
SQLite version 3.8.7.4 2014-12-09 01:34:36
Enter ".help" for usage hints.
sqlite> create table product(type TEXT, para1 REAL, para2 REAL, para3 REAL);
sqlite> .tables
product
sqlite>

3.插入数据

使用 insert 指令插入数据。比如要插入一个产品如下:

  • type : SN001
  • para1 : 30.00
  • para2 : 10.00
  • para3 : 20.00

可使用如下命令

insert into product values('SN001', 30.00, 10.00, 20.00);

4.查询数据

使用 SELECT 指令查询数据,比如要查询表product所有记录,可使用下面命令:

select * from product;

即可显示表的内容

D:\>sqlite3 test
SQLite version 3.8.7.4 2014-12-09 01:34:36
Enter ".help" for usage hints.
sqlite> create table product(type TEXT, para1 REAL, para2 REAL, para3 REAL);
sqlite> .tables
product
sqlite> insert into product values('SN001', 30.00, 10.00, 20.00);
sqlite> insert into product values('SN002', 35.00, 11.01, 20.01);
sqlite> select * from product;
SN001|30.0|10.0|20.0
SN002|35.0|11.01|20.01
sqlite>

5.查询指定数据

在 select 里使用 where 参数,比如查询产品 SN001 的参数

select * from product where type='SN001';

效果如下:

sqlite> select * from product where type='SN001';
SN001|30.0|10.0|20.0
sqlite>

6.查询时间戳

前面的实例只是实现了插入数据到表中,但是在实际使用中,时间戳是一个非常有用的数据,使用 CURRENT_TIMESTAMP,返回格式是 “YYYY-MM-DD HH:MM:SS”。如果在创建表时,可以加上时间戳这一列。对于已经存在的表,追加时间戳并不可行,不然会报错“Cannot add a column with non-constant default ”。

重新创建一个表 product2,

使用 “alter table 表名 add 字段名 类型” 命令可以追加字段。下面指令向表 product 中追加 timestamp 的字段。

create table product1 (type TEXT, para1 REAL, para2 REAL, para3 REAL, timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);

但是不能向前面那样插入数据了,不然会报错

sqlite> create table product1 (type TEXT, para1 REAL, para2 REAL, para3 REAL, timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
sqlite> insert into product1 values('SN001', 30.00, 10.00, 20.00);
Error: table product1 has 5 columns but 4 values were supplied

改用命令

insert into product1 (type, para1, para2, para3)  values ('SN001', 30.00, 10.00, 20.00);

即可,添加两个产品,效果如下:

sqlite> insert into product1 (type, para1, para2, para3)  values ('SN001', 30.00, 10.00, 20.00);
sqlite> insert into product1 (type, para1, para2, para3)  values ('SN002', 35.00, 11.01, 20.01);
sqlite> select * from product1;
SN001|30.0|10.0|20.0|2014-12-17 09:50:25
SN002|35.0|11.01|20.01|2014-12-17 09:51:53
sqlite>

但是时间是UTC时间,并非当地时间,改用 (datetime(‘now’,’localtime’)) 替代 CURRENT_TIMESTAMP , 例如再创建表 product3

sqlite> insert into product3 (type, para1, para2, para3)  values ('SN001', 30.00, 10.00, 20.00);
sqlite> insert into product3 (type, para1, para2, para3)  values ('SN002', 35.00, 11.01, 20.01);
sqlite> select * from product3;
SN001|30.0|10.0|20.0|2014-12-17 17:56:15
SN002|35.0|11.01|20.01|2014-12-17 17:56:26
sqlite>

就是当前的时间了!

将前面的创建的product 和 product1 删除,然后把 product3改名为 product

sqlite> .tables
product   product1  product3
sqlite> drop table product;
sqlite> drop table product1;
sqlite> alert table product3 rename to product;
sqlite> .tables
product
sqlite> select * from product;
SN001|30.0|10.0|20.0|2014-12-17 17:56:15
SN002|35.0|11.01|20.01|2014-12-17 17:56:26
sqlite>

7.重新整理

按照前面的过程,我们重新整理下加入时间戳字段的表的建立方式。为了后面使用,我们重新建立一个新的数据库文件data.db,并建立表product,然后存入3个产品数据,每次存入时间相差2分钟左右。

D:\>sqlite3 data.db
SQLite version 3.8.7.4 2014-12-09 01:34:36
Enter ".help" for usage hints.
sqlite> create table product (type TEXT, para1 REAL, para2 REAL, para3 REAL, timestamp NOT NULL DEFAULT  (datetime('now','localtime')));
sqlite> insert into product(type, para1, para2, para3)  values ('SN001', 30.00,10.00, 20.00);
sqlite> insert into product(type, para1, para2, para3)  values ('SN002', 29.01,15.00, 18.00);
sqlite> insert into product(type, para1, para2, para3)  values ('SN003', 35.10,10.01, 20.0111);
sqlite> select * from product;
SN001|30.0|10.0|20.0|2014-12-17 18:22:30
SN002|29.01|15.0|18.0|2014-12-17 18:25:18
SN003|35.1|10.01|20.0111|2014-12-17 18:27:39
sqlite>

8.查询特定时间段数据

有了时间戳,查询特定时间段数据成为可能

sqlite> select * from product;
SN001|30.0|10.0|20.0|2014-12-17 18:22:30
SN002|29.01|15.0|18.0|2014-12-17 18:25:18
SN003|35.1|10.01|20.0111|2014-12-17 18:27:39
sqlite> select * from product where timestamp <'2014-12-17 18:26';
SN001|30.0|10.0|20.0|2014-12-17 18:22:30
SN002|29.01|15.0|18.0|2014-12-17 18:25:18
sqlite> select * from product where timestamp > '2014-12-17 18:26';
SN003|35.1|10.01|20.0111|2014-12-17 18:27:39
sqlite> select * from product where timestamp > '2014-12-17 18:23' AND timestamp < '2014-12-17 18:26';
SN002|29.01|15.0|18.0|2014-12-17 18:25:18
sqlite>

二.图形化工具

1. SQLite Administrator

使用 SQLiteadmin 时,要注意新建数据库的格式。sqlite有两种格式:v2 和 v3 区别。

扩展阅读

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.