ITPub博客

首页 > 数据库 > 数据库开发技术 > Sqlite嵌入式数据库的安装、建库、建表、更新表结构以及数据导入导出等等详细过程记录

Sqlite嵌入式数据库的安装、建库、建表、更新表结构以及数据导入导出等等详细过程记录

原创 数据库开发技术 作者:mchdba 时间:2014-08-27 21:14:46 2 删除 编辑

简介:

   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 xvfz sqlite-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.3 2014-04-03 16:53:12

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> .exit

[root@mysqlvm2 ~]#

 

为了使用新版本,我们这里用自己的全路径或者在设置PATH路径

[root@mysqlvm2 ~]# /root/sqlite-autoconf-3080403/sqlite3

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite>

 

3,创建数据库和表

3.1,默认登录

默认登录时,临时操作的是临时内存库,等退出的时候,创建的临时库以及表以及数据会被释放的,使用.databases命令展示的时候会看到file列下面是null的没有数据库记录,如下所示:

[root@localhost sqlite-autoconf-3080403]# sqlite3

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

Connected to a transient in-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(id int);

sqlite> insert into t1 select 1;

sqlite> insert into t1 select 2;

sqlite> select * from t1;

1

2

sqlite> .table

t1

sqlite>

我们创建了一张表t1,并且录入了数据,退出后重新登录,表t1已经不存在了,如下所示:

[root@localhost sqlite-autoconf-3080403]# sqlite3

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

Connected to a transient in-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.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

sqlite> .schema

sqlite> CREATE TABLE test_auto_incre(id INT AUTO_INCREMENT primary key,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE  );

sqlite> .schema

CREATE TABLE test_auto_incre(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) );

sqlite> .tables

test_auto_incre

sqlite>

sqlite> exit

   ...> ;

Error: near "exit": syntax error

sqlite>

用.exit或者.quit退出来,或者使用CTRL+D 按钮退出sqlite命令行操作界面。
此时在sqlite3的安装根目录下,你就会看到tim库的文件夹,一般通常以库名字命名,如下所示

[root@localhost sqlite-autoconf-3080403]# ll -t

total 11152

-rw-r--r-- 1 root root     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@localhost sqlite-autoconf-3080403]# sqlite3 tim

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

sqlite> .tables

test_auto_incre

sqlite>  create table test(id integer default 0, username text);

sqlite> .tables

test             test_auto_incre

sqlite>

 

3.4,插入数据记录

sqlite> inser into test(id,username)values(1,'test');

Run Time: real 0.000 user 0.000000 sys 0.000000

Error: near "inser": syntax error

sqlite> insert into test(id,username)values(1,'test');

Run Time: real 0.102 user 0.000000 sys 0.002000

sqlite> select * from test;

id          username 

----------  ----------

1           test     

Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> insert into test(id,username)values(2,'tman');

Run Time: real 0.070 user 0.001000 sys 0.000000

sqlite> select * from test;

id          username 

----------  ----------

1           test     

2           tman     

Run Time: real 0.000 user 0.000000 sys 0.000000

 

3.3,删除数据

sqlite> delete from test where id=1;

Run Time: real 0.081 user 0.000000 sys 0.002000

sqlite> select * from test;

id          username 

----------  ----------

2           tman     

Run Time: real 0.001 user 0.001000 sys 0.000000

sqlite>

 

3.4,更新数据表记录

sqlite> update test set username='tmanupdate' where id=2;

sqlite>

sqlite>

sqlite> select * from test;

2|tmanupdate

sqlite>

查询SQllite的VIRTUAL Table碰到问题:

CREATE VIRTUAL TABLE fts_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 ADD COLUMN address varchar(20) ;

Run Time: real 0.068 user 0.000000 sys 0.000000

sqlite> 

4.2 删除列

添加一列address,然后删除它

sqlite> create table test(id integer default 0, username text);

sqlite> select * from test;

sqlite> insert into test select 1,'a';

sqlite> insert into test select 2,'b';

sqlite> alter table test add column address varchar(20);

sqlite> insert into test select 3,'c','caddre';

sqlite> select * from test;

1|a|

2|b|

3|c|caddre

sqlite>

sqlite> alter table test drop column address;

Error: near "drop": syntax error

sqlite>

不识别drop操作标示符,怎么办?去官网看到如下信息:

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

Sqlite 其实是不支持drop column的方法来删除列的

(1).根据原表创建一张新表

create table test_tmp(id integer primary key, username text default ‘’);这个表没有列address了。

(2).从原来表把数据录入新临时表,

sqlite> insert into test_tmp select id,username from test;

sqlite> select * from test_tmp;

1|a

2|b

3|c

sqlite>

(3).删除原表

sqlite> drop table if exists test;

(4).将新临时表重名为旧表的名称

sqlite> alter table test_tmp rename to test;

sqlite> select * from test;

1|a

2|b

3|c

sqlite>

这样就实现了,删除一个字段address,得到了我们想要的drop column的目的了。
PS:不建议create table test_tmp as select id, username from test;的方式建立临时表,因为这样的话,主键约束就不存在了。

 

4.3,索引管理

创建普通索引

sqlite> create index idx_username on test(username);

创建复合索引

sqlite> alter table test add column addr varchar(60) not null default '';

sqlite> create index idx_addr on test(addr,username);

创建唯一键索引

sqlite> create unique index idx_unaddr on test(addr);

测试是不是真的唯一约束,添加addr字段值都为’a1’,第一次成功,第二次失败,查看执行结果,如下:

sqlite> insert into test select 4,'a','a1';

sqlite> insert into test select 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(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) );

 

4.5 查看所有表结构

方法一:

sqlite> .schema

CREATE TABLE "test"(id integer primary key, username text default ‘’, addr varchar(60) not null default '');

CREATE INDEX idx_username on test(username);

CREATE INDEX idx_addr on test(addr,username);

CREATE UNIQUE INDEX idx_unaddr on test(addr);

CREATE TABLE test_auto_incre(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) );

sqlite>

方法二:

sqlite> .header on –加上显示的表头列名

sqlite> select * from sqlite_master;

type|name|tbl_name|rootpage|sql

table|test|test|4|CREATE TABLE "test"(id integer primary key, username text default ‘’, addr varchar(60) not null default '')

index|idx_username|test|2|CREATE INDEX idx_username on test(username)

index|idx_addr|test|5|CREATE INDEX idx_addr on test(addr,username)

index|idx_unaddr|test|6|CREATE UNIQUE INDEX idx_unaddr on test(addr)

table|test_auto_incre|test_auto_incre|7|CREATE TABLE 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_master where name="test_auto_incre";

sqlite> .header on –加上显示的表头列名

sqlite> select * from sqlite_master where name="test_auto_incre";

type|name|tbl_name|rootpage|sql

table|test_auto_incre|test_auto_incre|7|CREATE TABLE test_auto_incre(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) )

sqlite>

方法二:使用.schema命令

sqlite> .schema test_auto_incre

CREATE TABLE test_auto_incre(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) );

sqlite>

sqlite> .schema test

CREATE TABLE "test"(id integer primary key, username text default ‘’, addr varchar(60) not null default '');

CREATE INDEX idx_username on test(username);

CREATE INDEX idx_addr on test(addr,username);

CREATE UNIQUE INDEX idx_unaddr on test(addr);

sqlite>

 

4.8 创建虚拟表

CREATE VIRTUAL TABLE fts_message_table_0_tmp 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));

 

4.9 创建视图

CREATE TABLE test(id INT AUTO_INCREMENT,msg_to VARCHAR(20), TYPE CHAR(1), create_date DATE,PRIMARY KEY (id) );

INSERT INTO test(msg_to,TYPE,create_date) SELECT 'm1','a','2014-08-18 12:12:23';

INSERT INTO test(msg_to,TYPE,create_date) SELECT 'm1','b','2014-08-18 13:12:23';

INSERT INTO test(msg_to,TYPE,create_date) SELECT 'm1','c','2014-08-18 10:12:23';

 

INSERT INTO test(msg_to,TYPE,create_date) SELECT 'm2','b','2014-08-20 12:12:23';

INSERT INTO test(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 FILE into TABLE
建立了Sqlite数据库后就要建立表输入数据了,多数时候数据量较大,手动输入不可能,必须用导入语句,导入逗号分隔csv格式数据,首先建立一个表,比如test.db中建表test,如果表已经存在,可以利用命令".schema"查看该表结构,比如:
sqlite>.schema test,结果得到test表的结构,因为要导入的数据必须具有相似的结构,所以必须明了目标表的结构。

sqlite> .schema test

CREATE TABLE test(id integer default 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 output to FILENAME


sqlite> .output a.txt

然后输入sql语句, 查询出要导的数据. 查询后,数据不会显示在屏幕上,而直接写入文件.

结束后,输入

sqlite> .output stdout

将输出重定向至屏幕,如下所示:

[root@localhost sqlite-autoconf-3080403]#

[root@localhost sqlite-autoconf-3080403]# sqlite3 ti

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

sqlite>

sqlite> .output test.sql

sqlite> select * from test;

sqlite> .exit

[root@localhost sqlite-autoconf-3080403]# more test.sql

1|a1

2|a2

3|a3

4|a4

[root@localhost sqlite-autoconf-3080403]#

 

5.3 使用5.2导出的文件恢复test表

先登录删除test表

[root@localhost sqlite-autoconf-3080403]# sqlite3 ti

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage 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

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26230597/viewspace-1259145/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Happy is the man who is living by his hobby.

注册时间:2011-09-05

  • 博文量
    147
  • 访问量
    3740056