SQLite3使用笔记

dongjincai

popucui

SQLite是一个轻量级的基于文件的数据库，开发、测试和迁移都很方便。关于SQLite、MySQL和NoSQL的对比，看这里的一篇文章就够了。最近需要更新一个软件的数据库，之前也自学过一些，这次正好巩固运用一下。

安装

sqlite3的安装简单，共分两步，第一步去这里下载，第二步解压缩，就能用了。如果使用的是Mac OS X或者Linux，一般都自带的有。

语法

sqlite不区分大小写，所以DROP和drop效果一样，例外的情况要记住，有一些命令大小写意义不同，比如GLOB和glob。所以，尽管sqlite不区分大小写，不管是sqlite shell还是使用API，最好区分大小写。

注释分为两种，行注释’–’，块注释’/* comment out code here */’。

命令同样分为两种：点命令（dot command）和普通命令（statements）。区别在于，点命令以点号开头’.’，小写；而普通命令大写，通常以SELECT、UPDATE、DROP、DELETE以及ALTER等开头，以分号结尾’;’。

--点命令
.help --显示帮助
.show --显示设置参数当前值

        echo: off
         eqp: off
explain: auto
     headers: off
mode: list
   nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:

--普通命令，注意它们都是以分号结尾，大写开头
CREATE TABLE browse_dgv(key, value);
DROP TABLE browse_dgv;

数据库操作

在命令行输入sqlite3，就会进入sqlite shell：

cuijie@popucui-mba:~/Downloads% sqlite3
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .database --这时候输入.database,可以看到已经有一个数据库名叫main
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main

要打开一个数据库，只需要.open test.db就行，注意test.db必须是真实合法的sqlite3 db文件。此外，另一种方式是在命令行输入sqlite3 test.db，这样进入sqlite shell之后，就有一个名为main的数据库，连接到test.db。

cuijie@popucui-mba:~/Downloads/% sqlite3 test.db
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite> .database
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /Users/cuijie/Downloads/test.db

有的时候，你可能需要在一个会话同时打开多个database，可以这样：

sqlite> ATTACH DATABASE 'test.db' as 'foo';
sqlite> .database
seq  name             file
---  ---------------  -------------------------------
0    main             /Users/cuijie/Downloads/test.db
2    foo              /Users/cuijie/Downloads/test.db
sqlite> DETACH DATABASE foo; --从DETACH这个名字，就能猜出来，它的作用和ATTACH相反
sqlite> .database
seq  name             file
---  ---------------  -------------------------------
0    main             /Users/cuijie/Downloads/test.db

数据表操作

有.database显示已打开的数据库，那么自然也有.table显示当前会话中数据库所包含的数据表。

sqlite> .table
dgv   foo1  foo2
sqlite> .open hg19_na331_20170122.db
sqlite> .table
browse_bac                   browse_omim_genes
browse_cytoband              browse_omim_phenotypes
browse_dgv                   browse_par
browse_fish                  browse_refseq
browse_genomicSegmentalDups  info
browse_ncRNA

此外，还可以新建，删除数据表：

CREATE TABLE browse_dgv(key, value); --注意，不指定数据类型的话，默认为TEXT，数据类型一直没提，其实有编程常识的容易理解，主要包含NUMERIC，VARCHAR等。
DROP TABLE browse_dgv;
ALTER TABLE browse_dgv RENAME TO old_dgv; --此处给数据表重命名，此外sqlite还支持对数据表添加新的列。
UPDATE info SET value  = '3.13.0' WHERE key='sqlite-app-version';
/* 以上数更新数据表，此外还支持DELETE INSERT等  */

导入导出数据

有很多时候，从文件导入数据，导入的文件类型可以是csv或tsv。这里以tsv（tab分割的文本文件）为例说明。

DROP TABLE browse_dgv;
CREATE TABLE browse_dgv(
  dgv_id varchar(255),
chr varchar(25),
start int,
stop int,
  varType varchar(255),
reference text,
  pubmedid int,
  method varchar(255),
sample int,
  gain int,
  loss int,
  TotalGLInv int,
  Frequency int
);
CREATE INDEX r_dgv on browse_dgv (chr,start,dgv_id);

.headers off --因为创建数据表时，表头已经定义好，为避免重复，这里关闭表头
.mode tabs --我的文件是tab分割，所以这里改成tabs
.import 'hg19_dgv_2016-05-15_v3.txt' browse_dgv

修改完数据表之后，需要导出数据库到文件:

.save  hg19_na331_20170122.db