首页 > 代码库 > Sqlite嵌入式数据库的安装、建库、建表、更新表结构以及数据导入导出等等详细过程记录
Sqlite嵌入式数据库的安装、建库、建表、更新表结构以及数据导入导出等等详细过程记录
简介：
SQLite 是实现了SQL 92标准的一个大子集的嵌入式数据库.其以在一个库中组合了数据库引擎和接口,能将所有数据存储于单个文件中而著名.我觉得SQLite的功能一定程度上居于MySQL 和PostgreSQL之间.尽管如此,在性能上面,SQLite常常快2-3倍 (甚至更多).这利益于其高度调整了的内部架构,因为它除去了服务器端到客户端和客户端到服务器端的通信。
而令人印象深刻的特点是你可将你的整个数据库系统放在其中.利用非常高效的内存组织,SQLite只需在很小的内存中维护其很小的尺寸,远远比其它任何数据库系统都小.这些特点使得其成为在需要高效地应用数据库的任务中一个非常方便的工具.
SQLite优势：
一 . 除了速度和效率,SQLite还有其它好多的优势使得其能成为许多任务中一个理想的解决方案.因为SQLite的数据库都是简单文件,因此无须一个管理队伍花时间来构造复杂的权限结构来保护用户的数据库.因为权限通过文件系统自动进行.这也同时意味着(数据库空间的大小只与环境有关,与本身无关)无段特殊的规则来了解用户磁盘空间.用户可以从创建他们想要的任意多的数据库和对其对这些数据库的绝对控制权而得到好处.
二 . 数据库就是一个文件的事实使用SQLite可以轻易地在服务器间移动.SQLite也除去了需要大量内存和其它系统资源的伺候进程.即使当数据库在大量地使用时也是如此.
1，安装
下载地址： Wget http://www.sqlite.org/2014/sqlite-autoconf-3080403.tar.gz
开始安装：
tar xvfzsqlite-autoconf-3080403.tar.gz
cd sqlite-autoconf-3080403
./configure --prefix=/usr/local
make
make install
2，登录
不过因为一般centos的linux系统自带了版本比较低的sqlite，所以你不安装，直接运行sqlite3就可以登录进去：
[root@mysqlvm2 ~]# sqlite3
SQLite version 3.3.6.32014-04-03 16:53:12
Enter ".help" forinstructions
Enter SQL statements terminatedwith a ";"
sqlite> .exit
[root@mysqlvm2 ~]#
为了使用新版本，我们这里用自己的全路径或者在设置PATH路径
[root@mysqlvm2 ~]#/root/sqlite-autoconf-3080403/sqlite3
SQLite version 3.8.4.32014-04-03 16:53:12
Enter ".help" forusage hints.
Connected to a transientin-memory database.
Use ".open FILENAME"to reopen on a persistent database.
sqlite>
3，创建数据库和表
3.1，默认登录
默认登录时，临时操作的是临时内存库，等退出的时候，创建的临时库以及表以及数据会被释放的，使用.databases命令展示的时候会看到file列下面是null的没有数据库记录，如下所示：
[root@localhostsqlite-autoconf-3080403]# sqlite3
SQLite version 3.8.4.32014-04-03 16:53:12
Enter ".help" forusage hints.
Connected to a transientin-memory database.
Use ".open FILENAME"to reopen on a persistent database.
sqlite> .databases
seq name file
--- --------------- ----------------------------------------------------------
0 main
sqlite>
sqlite> .tables
sqlite> create table t1(idint);
sqlite> insert into t1 select1;
sqlite> insert into t1 select2;
sqlite> select * from t1;
1
2
sqlite> .table
t1
sqlite>
我们创建了一张表t1，并且录入了数据，退出后重新登录，表t1已经不存在了，如下所示：
[root@localhostsqlite-autoconf-3080403]# sqlite3
SQLite version 3.8.4.32014-04-03 16:53:12
Enter ".help" forusage hints.
Connected to a transientin-memory database.
Use ".open FILENAME"to reopen on a persistent database.
sqlite> .table
sqlite> .tables
sqlite> select * from t1;
Error: no such table: t1
sqlite>
3.2，创建数据库
直接登录时候带上数据库名tim，就表示建库tim，注意:这时tim数据库确实已经创建好了注意:这时test.db数据库确实已经创建好了但是这时还是看不到这个数据库，还是个临时的，所以要输入SQL命令 ，等表格创建完后关闭sqlite3退出来的时候，就可以看到这个库了。
[root@localhost ~]# sqlite3 tim
SQLite version 3.8.4.32014-04-03 16:53:12
Enter ".help" forusage hints.
sqlite> .schema
sqlite> CREATE TABLEtest_auto_incre(id INT AUTO_INCREMENT primary key,msg_to VARCHAR(20), TYPECHAR(1), create_date DATE );
sqlite> .schema
CREATE TABLE test_auto_incre(idINT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARYKEY (id) );
sqlite> .tables
test_auto_incre
sqlite>
sqlite> exit
...> ;
Error: near "exit":syntax error
sqlite>
用.exit或者.quit退出来，或者使用CTRL+D 按钮退出sqlite命令行操作界面。
此时在sqlite3的安装根目录下，你就会看到tim库的文件夹，一般通常以库名字命名，如下所示
[root@localhostsqlite-autoconf-3080403]# ll -t
total 11152
-rw-r--r-- 1 rootroot 3072 Aug 27 16:52 tim
再次登录进去查看已经存在的库以及表：
sqlite> .databases
seq name file
--- --------------- ----------------------------------------------------------
0 main /root/sqlite-autoconf-3080403/tim
sqlite>
sqlite> .tables
test test_auto_incre
sqlite>
3.3，建表
[root@localhostsqlite-autoconf-3080403]# sqlite3 tim
SQLite version 3.8.4.32014-04-03 16:53:12
Enter ".help" forusage hints.
sqlite> .tables
test_auto_incre
sqlite> create tabletest(id integer default 0, username text);
sqlite> .tables
test test_auto_incre
sqlite>
3.4，插入数据记录
sqlite> inser intotest(id,username)values(1,‘test‘);
Run Time: real 0.000 user0.000000 sys 0.000000
Error: near "inser":syntax error
sqlite> insert intotest(id,username)values(1,‘test‘);
Run Time: real 0.102 user0.000000 sys 0.002000
sqlite> select * from test;
id username
---------- ----------
1 test
Run Time: real 0.000 user0.000000 sys 0.000000
sqlite> insert intotest(id,username)values(2,‘tman‘);
Run Time: real 0.070 user0.001000 sys 0.000000
sqlite> select * from test;
id username
---------- ----------
1 test
2 tman
Run Time: real 0.000 user0.000000 sys 0.000000
3.3，删除数据
sqlite> delete from testwhere id=1;
Run Time: real 0.081 user0.000000 sys 0.002000
sqlite> select * from test;
id username
---------- ----------
2 tman
Run Time: real 0.001 user0.001000 sys 0.000000
sqlite>
3.4，更新数据表记录
sqlite> update test setusername=‘tmanupdate‘ where id=2;
sqlite>
sqlite>
sqlite> select * from test;
2|tmanupdate
sqlite>
查询SQllite的VIRTUAL Table碰到问题：
CREATE VIRTUAL TABLEfts_message_table_0 USING fts3
(usernameid INTEGER DEFAULT 0,MesLocalID INTEGER, CreateTime INTEGER DEFAULT 0, Message TEXT,
reservedInt INTEGER DEFAULT 0,reservedText TEXT, PRIMARY KEY(usernameid,MesLocalID,CreateTime),tokenize=one_or_binary_tokenizer);
sqlite> select *from fts_message_table_0 where fts_message_table_0 match ‘27‘;
Error: unknown tokenizer:one_or_binary_tokenizer
sqlite>
4，修改表结构
4.1 添加一个字段
sqlite> .timer on
sqlite> ALTER TABLE test ADDCOLUMN address varchar(20) ;
Run Time: real 0.068 user0.000000 sys 0.000000
sqlite>
4.2 删除列
添加一列address，然后删除它
sqlite> create table test(idinteger default 0, username text);
sqlite> select * from test;
sqlite> insert into testselect 1,‘a‘;
sqlite> insert into testselect 2,‘b‘;
sqlite> alter table test addcolumn address varchar(20);
sqlite> insert into testselect 3,‘c‘,‘caddre‘;
sqlite> select * from test;
1|a|
2|b|
3|c|caddre
sqlite>
sqlite> alter table test dropcolumn address;
Error: near "drop":syntax error
sqlite>
不识别drop操作标示符，怎么办？去官网看到如下信息：
SQLite supports a limited subsetof ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename atable or to add a new column to an existing table. It is not possible to renamea column, remove a column, or add or remove constraints from a table.
Sqlite 其实是不支持drop column的方法来删除列的
(1).根据原表创建一张新表
create table test_tmp(id integerprimary key, username text default ‘’);这个表没有列address了。
(2).从原来表把数据录入新临时表，
sqlite> insert into test_tmpselect id,username from test;
sqlite> select * fromtest_tmp;
1|a
2|b
3|c
sqlite>
(3).删除原表
sqlite> drop table if existstest;
(4).将新临时表重名为旧表的名称
sqlite> alter table test_tmprename to test;
sqlite> select * from test;
1|a
2|b
3|c
sqlite>
这样就实现了，删除一个字段address，得到了我们想要的drop column的目的了。
PS：不建议createtable test_tmp as select id, username from test;的方式建立临时表，因为这样的话，主键约束就不存在了。
4.3，索引管理
创建普通索引
sqlite> create indexidx_username on test(username);
创建复合索引
sqlite> alter table test addcolumn addr varchar(60) not null default ‘‘;
sqlite> create index idx_addron test(addr,username);
创建唯一键索引
sqlite> create unique indexidx_unaddr on test(addr);
测试是不是真的唯一约束，添加addr字段值都为’a1’，第一次成功，第二次失败，查看执行结果，如下：
sqlite> insert into testselect 4,‘a‘,‘a1‘;
sqlite> insert into testselect 5,‘a‘,‘a1‘;
Error: UNIQUE constraint failed:test.addr
sqlite>
报错了，验证了，唯一约束idx_undaddr已经生效。
PS： Sqlite不支持聚集索引，android默认需要一个_id字段，这保证了你插入的数据会按“_id”的整数顺序插入，这个integer类型的主键就会扮演和聚集索引一样的角色。所以不要再在对于声明为：INTEGER PRIMARY KEY的主键上创建索引。
4.4，自增主键
CREATE TABLE test_auto_incre(idINT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARYKEY (id) );
4.5 查看所有表结构
方法一：
sqlite> .schema
CREATE TABLE "test"(idinteger primary key, username text default ‘’, addr varchar(60) not null default‘‘);
CREATE INDEX idx_username ontest(username);
CREATE INDEX idx_addr ontest(addr,username);
CREATE UNIQUE INDEX idx_unaddron test(addr);
CREATE TABLE test_auto_incre(idINT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARYKEY (id) );
sqlite>
方法二：
sqlite> .header on –加上显示的表头列名
sqlite> select * fromsqlite_master;
type|name|tbl_name|rootpage|sql
table|test|test|4|CREATE TABLE"test"(id integer primary key, username text default ‘’, addrvarchar(60) not null default ‘‘)
index|idx_username|test|2|CREATEINDEX idx_username on test(username)
index|idx_addr|test|5|CREATEINDEX idx_addr on test(addr,username)
index|idx_unaddr|test|6|CREATEUNIQUE INDEX idx_unaddr on test(addr)
table|test_auto_incre|test_auto_incre|7|CREATETABLE test_auto_incre(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1),create_date DATE,PRIMARY KEY (id) )
index|sqlite_autoindex_test_auto_incre_1|test_auto_incre|8|
sqlite>
4.6 查看当前库下所有的表名字
sqlite> .table
test test_auto_incre
sqlite> .tables
test test_auto_incre
4.7 查看某一张表的结构，比如test_auto_incre表：
方法一：查询系统表
select * from sqlite_masterwhere name="test_auto_incre";
sqlite> .header on –加上显示的表头列名
sqlite> select * fromsqlite_master where name="test_auto_incre";
type|name|tbl_name|rootpage|sql
table|test_auto_incre|test_auto_incre|7|CREATETABLE test_auto_incre(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1),create_date DATE,PRIMARY KEY (id) )
sqlite>
方法二：使用.schema命令
sqlite> .schematest_auto_incre
CREATE TABLE test_auto_incre(idINT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARYKEY (id) );
sqlite>
sqlite> .schema test
CREATE TABLE "test"(idinteger primary key, username text default ‘’, addr varchar(60) not nulldefault ‘‘);
CREATE INDEX idx_username ontest(username);
CREATE INDEX idx_addr ontest(addr,username);
CREATE UNIQUE INDEX idx_unaddron test(addr);
sqlite>
4.8 创建虚拟表
CREATE VIRTUAL TABLEfts_message_table_0_tmp USING fts3(usernameid INTEGER DEFAULT 0, MesLocalIDINTEGER, CreateTime INTEGER DEFAULT 0, Message TEXT, reservedInt INTEGERDEFAULT 0, reservedText TEXT, PRIMARYKEY(usernameid,MesLocalID,CreateTime));
4.9 创建视图
CREATE TABLE test(id INTAUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY(id) );
INSERT INTOtest(msg_to,TYPE,create_date) SELECT ‘m1‘,‘a‘,‘2014-08-18 12:12:23‘;
INSERT INTOtest(msg_to,TYPE,create_date) SELECT ‘m1‘,‘b‘,‘2014-08-18 13:12:23‘;
INSERT INTOtest(msg_to,TYPE,create_date) SELECT ‘m1‘,‘c‘,‘2014-08-18 10:12:23‘;
INSERT INTOtest(msg_to,TYPE,create_date) SELECT ‘m2‘,‘b‘,‘2014-08-20 12:12:23‘;
INSERT INTOtest(msg_to,TYPE,create_date) SELECT ‘m3‘,‘c‘,‘2014-08-19 12:12:23‘;
CREATE VIEW TEST_VIEW SELECT MSG_TO,CREATE_DATE FROM test;
5 Sqlite数据导出导入操作
5.1 数据导入.import命令
命令：.import FILE TABLE Import data from FILEinto TABLE
建立了Sqlite数据库后就要建立表输入数据了，多数时候数据量较大，手动输入不可能，必须用导入语句，导入逗号分隔csv格式数据，首先建立一个表，比如test.db中建表test，如果表已经存在，可以利用命令".schema"查看该表结构，比如：
sqlite>.schema test，结果得到test表的结构，因为要导入的数据必须具有相似的结构，所以必须明了目标表的结构。
sqlite> .schema test
CREATE TABLE test(id integerdefault 0, username text);
sqlite>
比如test表具有下面的数据
1|a1
2|a2
另一个csv文本文件为 test.csv，内容如下：
3,a3
4,a4
注意1: 不要忘了开头的点.import
注意2: 这条语句不能用分号结束. 非SQL不需要分号结束.
注意3: 需要查看默认的分隔符separator. 必须一致，查看分隔符使用命令.show，如果不一致可能导致sqlite字段分割错误，利用”.separator”命令转换sqlite默认分隔符，比如.separator ","这一句就将分隔符改变为逗号，与预导入数据一致才能顺利导入，如下所示：
sqlite> .show
echo:off
eqp: off
explain: off
headers: off
mode:list
nullvalue: ""
output: stdout
separator: "|"
stats: off
width:
sqlite>
分隔符不是”|”，而文件test.csv的分隔符是”,”，所以要先转换分隔符，在导入：
sqlite> .separator ","
sqlite> .table
t2 test test_auto_incre
sqlite> .import test.csv test
导入成功，查看结果
sqlite> select * from test;
1,a1
2,a2
3,a3
4,a4
sqlite> .separator "|"
sqlite> select * from test;
1|a1
2|a2
3|a3
4|a4
sqlite>
这样表test就多了从csv文件导入的两行记录，sqlite最新版本已经默认使用事件，因此海量数据导入也很轻松高效，同样的方法可以导入海量txt文本文件。
5.2，数据导出.output命令
命令： .output FILENAME Send outputto FILENAME
sqlite> .output a.txt
然后输入sql语句, 查询出要导的数据. 查询后,数据不会显示在屏幕上,而直接写入文件.
结束后,输入
sqlite> .output stdout
将输出重定向至屏幕，如下所示：
[root@localhostsqlite-autoconf-3080403]#
[root@localhostsqlite-autoconf-3080403]# sqlite3 ti
SQLite version 3.8.4.32014-04-03 16:53:12
Enter ".help" forusage hints.
sqlite>
sqlite> .output test.sql
sqlite> select * from test;
sqlite> .exit
[root@localhostsqlite-autoconf-3080403]# more test.sql
1|a1
2|a2
3|a3
4|a4
[root@localhostsqlite-autoconf-3080403]#
5.3 使用5.2导出的文件恢复test表
先登录删除test表
[root@localhostsqlite-autoconf-3080403]# sqlite3 ti
SQLite version 3.8.4.32014-04-03 16:53:12
Enter ".help" forusage hints.
sqlite>
select * from test;
1|a1
2|a2
3|a3
4|a4
sqlite> delete from test;
sqlite> select * from test;
sqlite>
再导入test.sql文件的数据，如下所示
sqlite> .show
echo:off
eqp: off
explain: off
headers: off
mode:list
nullvalue: ""
output: stdout
separator: "|"
stats: off
width:
sqlite> .import test.sql test
sqlite> select * from test;
1|a1
2|a2
3|a3
4|a4
sqlite>
查询数据，显示导入表test成功。
参考文档： http://www.w3cschool.cc/sqlite/sqlite-installation.html
Sqlite嵌入式数据库的安装、建库、建表、更新表结构以及数据导入导出等等详细过程记录