sqlite tour

[toc]

sqlite3 program

Mac OS 上已经预装了 sqlite3

1 2	$ which sqlite3 /usr/bin/sqlite3

进入 sqlite3 命令行环境，并创建表格

$sqlite3 ex1
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
sqlite> create table tabl2 (
   ...> f1 varchar(30) primary key,
   ...> f2 text,
   ...> f3 real
   ...> );
   
insert into tabl2 values ('Jack','male',12.6);
insert into tabl2 values ('Tome','male',12.6);
insert into tabl2 values ('Bob','male',12.6);
insert into tabl2 values ('Rose','fmale',12.6);

dot-command

点命令是 sqlite3 的设置输出命令，有一些规则需要遵循：

必须以 “.” 开头；
单行执行，不支持多行
不能在 SQL 中
不能够写注释

通过 .help 可以查看所有的点命令

注意：

sqlite 中 SQL 语句不区分大小写；
每个 SQL 语句都需要分号结束，如果没有分号，Enter 之后，sqlite3 会继续让你输入，知道遇到分号为止

dot-command 修改输出格式

sqlite> .tables
tabl2
sqlite> select * from tabl2;
Jack|male|12.6
Tome|male|12.6
Bob|male|12.6
Rose|fmale|12.6

.tables 查看当前数据库中的表， select from tabl2; 查看 tabl2 表中的数据，并且以 list* 方式展示出来。 sqlite3 program 能够以 8 种方式展示输出结果：

csv
column
html
insert
line
list
quote
tabs
tcl

通过 .mode [方式名字] 可以修改当前的展示模式

sqlite> select * from tabl2;
Jack|male|12.6
Tome|male|12.6
Bob|male|12.6
Rose|fmale|12.6
sqlite> .mode list
sqlite> select * from tabl2;
Jack|male|12.6
Tome|male|12.6
Bob|male|12.6
Rose|fmale|12.6
sqlite> .mode csv
sqlite> select * from tabl2;
Jack,male,12.6
Tome,male,12.6
Bob,male,12.6
Rose,fmale,12.6
sqlite> .mode html
sqlite> select * from tabl2;
<TR><TD>Jack</TD>
<TD>male</TD>
<TD>12.6</TD>
</TR>
<TR><TD>Tome</TD>
<TD>male</TD>
<TD>12.6</TD>
</TR>
<TR><TD>Bob</TD>
<TD>male</TD>
<TD>12.6</TD>
</TR>
<TR><TD>Rose</TD>
<TD>fmale</TD>
<TD>12.6</TD>
</TR>
sqlite> .mode tabs
sqlite> select * from tabl2;
Jack	male	12.6
Tome	male	12.6
Bob	male	12.6
Rose	fmale	12.6

默认是 list 模式，上面分别以 list、csv、html、tabs 模式进行展示。
除此之外，.separator 可以修改分隔符，比如我们以 “ ~ “ 作为分割

sqlite> .separator " ~ "
sqlite> select * from tabl2;
Jack ~ male ~ 12.6
Tome ~ male ~ 12.6
Bob ~ male ~ 12.6
Rose ~ fmale ~ 12.6
sqlite> .mode list
sqlite> select * from tabl2;
Jack|male|12.6
Tome|male|12.6
Bob|male|12.6
Rose|fmale|12.6

当然，如果通过 .mode 再次修改输出格式的话， .separator 的操作就会被重置，这一点需要注意。

line & column 模式

通过 .mode line 和 .mode column 可以将输出格式修改成行和列模式，实现如下：

行模式

sqlite> .mode line
sqlite> select * from tabl2;
   f1 = Jack
   f2 = male
   f3 = 12.6

   f1 = Tome
   f2 = male
   f3 = 12.6

   f1 = Bob
   f2 = male
   f3 = 12.6

   f1 = Rose
   f2 = fmale
   f3 = 12.6

列模式

sqlite> .mode column
sqlite> select * from tabl2;
Jack        male        12.6
Tome        male        12.6
Bob         male        12.6
Rose        fmale       12.6

通过 .header on/off 列模式可以将 header 部分展示出来

sqlite> .header on
sqlite> select * from tabl2;
f1          f2          f3
----------  ----------  ----------
Jack        male        12.6
Tome        male        12.6
Bob         male        12.6
Rose        fmale       12.6

修改列宽 .width col1W col2W … colnW

sqlite> .width 1 10
sqlite> .mode column
sqlite> select * from tabl2;
f  f2          f3
-  ----------  ----------
J  male        12.6
T  male        12.6
B  male        12.6
R  fmale       12.6
sqlite> .width 10 10 10
sqlite> select * from tabl2;
f1          f2          f3
----------  ----------  ----------
Jack        male        12.6
Tome        male        12.6
Bob         male        12.6
Rose        fmale       12.6

.width 后面跟着的数字分别表示第几行的宽度，如果没有就是没有限制

插入模式输出

有时候，我们需要在另一个表中插入与当前表相同的数据，那么久可以通过 insert 模式来实现

sqlite> .mode insert new_table
sqlite> select * from tabl2;
INSERT INTO new_table(f1,f2,f3) VALUES('Jack','male',12.599999999999999644);
INSERT INTO new_table(f1,f2,f3) VALUES('Tome','male',12.599999999999999644);
INSERT INTO new_table(f1,f2,f3) VALUES('Bob','male',12.599999999999999644);
INSERT INTO new_table(f1,f2,f3) VALUES('Rose','fmale',12.599999999999999644);

将查询结果写入文件

.output and .once 将 sqlite3 的标准输出重定向到指定文件中

$touch output.txt

sqlite> .output output.txt
sqlite> select * from tabl2;

$ cat output.txt
f1          f2          f3
----------  ----------  ----------
Jack        male        12.6
Tome        male        12.6
Bob         male        12.6
Rose        fmale       12.6

.once 用法与 .output 用法一致，只不过 .once 只会让 sliqte3 执行一次非标准输出

sqlite> .once output.txt
sqlite> select * from tabl2;
sqlite> select * from tabl2;
f1          f2          f3
----------  ----------  ----------
Jack        male        12.6
Tome        male        12.6
Bob         male        12.6
Rose        fmale       12.6

通过 ‘|’ 打开保存的文件

1 2	sqlite> .once '\|open -f' sqlite> select * from tabl2;

如图所示，会将结果存储到一个 txt 文件中，然后打开：

File I/O Function

以存储图片为例：

新建一张 images 表
在当前目录中添加一张图片， icon.png

readfile(‘icon.png’)

sqlite> create table images
   ...> (
   ...> name text,
   ...> type text,
   ...> img blob
   ...> );
sqlite> insert into images(name,type,img) values ('icon','png',readfile('icon.png'));
sqlite> select  * from images;
name        type        img
----------  ----------  ----------
icon        png         �PNG

读取数据库中的图片,writefile(x,y) 负责将图片写入与数据库相同的目录中

sqlite> select writefile('img.png',img) from images where name = 'icon';
writefile('img.png',img)
------------------------
0
26348

上面实现了 sqlite3 对图片的存储和读取，分别用到了 readfile() 和 writefile() 函数，图片格式为 blob