扶凯

转载本站文章请注明，转载自：扶凯[http://www.php-oa.com]

本文链接: http://www.php-oa.com/2009/12/21/perl-sqlite.html

Perl 使用SQLite 和使用 MySQL　一样容易.非常喜欢这种风格.

Perl 使用 SQLite

SQLite 模块定义的“数据库”是存在于单个文件中的,把单个文件仿真为一个数据库.这个还是相当方便的. 当我们在使用 Perl 的 DBI 连接时,dbname 参数指定为该文件名.

不像大多数的关系型数据库,在 DBD::SQLite 定义的数据库不是服务器/客户短架构,没有服务器,它直接与文件交换数据. 多个进程可以同时从一个文件中读取数据（此时用 SELECTs 命令）,但是只有一个进程能向文件中写数据（当一个进程写数据的时候其它的进程被挂起）.

SQLite 支持事务功能,也就是,你可以向多个表做出一系列改动,但是只要你不向 SQLite 提交这些改动,更新就不会写到文件中.

SQLite 定义的数据库里面没有数据类型这个概念.不管你在创建一个表的时候指定的是什么数据类型,以后你可以在其中放入任何类型的数值（包括字符型,数字型,日期型,二进制对象 blob）.实际上,创建表的时候你甚至可以不指定数据类型. CREATE TABLE people ( id, name, age); SQLite 只有在要比较数据的时候,如用 WHERE 子句或对某些值进行排序,才会考虑数据类型.SQLite 在比较数据的时候,只考虑被比较对象的类型,而不管被比较对象所在列的其它数据是什么类型.像 Perl 一样,SQLite 只能识别字符型和数字型.两个数值总是以浮点类型进行比较,两个字符串直接比较.当不同类型的数据比较的时候,数字总是比字符小.

只有一种情况 SQLite 才会关心你为某一列申明的数据类型（需要创建一个值自增加的列的时候）.你可以把这列的类型指定为：“INTEGER PRIMARY KEY”. 详细使用见下面的教程.

SQLite支持8位长的字符编码,但是不识别ASCII中的NULL符“\0”.唯一的变通方法就是在你存储数据之前自行编码,然后在取出数据之后再手工解码,就象URL编码或Base64编码方式一样.这甚至可以用在BLOB字段里面.

use DBI;
$dbh = DBI->connect("dbi:SQLite:dbname=./info.db",'','',{
        RaiseError => 1,
        AutoCommit => 0
        }
    );
# 在perl中,SQLite建表
$dbh->do(<<EOF);
CREATE TABLE IF NOT EXISTS ip (
        id      INTEGER NOT NULL PRIMARY KEY,
        name    VARCHAR(255) NOT NULL,
        UNIQUE  (id)
        )
EOF

# 使用perl来更新SQLite的表内容
$dbh->do("alter table ip add column age varchar(1024) null");

# 插入数据
$dbh->do( "insert into name values( 1, 2, 'myname' )");
if ($dbh->err()) {
    die "$DBI::errstr\n";
}
$dbh->commit();

# 查询数据
my $dbconn = $dbh->prepare("select * from ip");
$dbconn->execute();
while (my @row_ary = $dbconn->fetchrow_array){
    my ($cc,$bb,$dd) = @row_ary;
}
$dbh->disconnect();

可以使用　  $dbh->do("PRAGMA cache_size = 800000");　来开启 800M 的内存 cache.默认只使用了 2M

 

SQLite 维护

创建数据库
直接在命令中键入

sqlite3 test.db

即可创建了一个名为test.db的数据库

显示所有表的结构属性
要想见到所有数据库中的信息,表、索引、视图等等

SQLite> .mode col
SQLite> .headers on
SQLite> select type, name, tbl_name, sql from SQLite_master order by type;

导出和备份数据
常用有三个命令来做这些事，如下

• .output [filename],导出到文件中,如果该文件不存在,则自动创建
• .dump 导出数据命令
• .output stdout 返回输出到屏幕(进行其他操作)

例如:

SQLite>.output bakup.sql
SQLite>.dump
SQLite>.output stdout

也可以直接在 shell 下使用 sqlite 的命令来做这个操作，先切换到你的 SQLite 的目录

sqlite3 test.db .dump > test.sql

从 SQL 中导入数据
我们导入数据时，只要使用.read 命令加 SQL 的文件就行了

SQLite>.read bakup.sql

如果要导入整个数据库的话,需要进入你　SQLite　文件夹的目录

sqlite3 test.db < test.sql

其他的SQLite工具
SQLite Database Browser (http://SQLitebrowser.sourceforge.net)
SQLite Control Center (http://bobmanc.home.comcast.net/SQLitecc.html)
SQLiteManager (www.sqlabs.net/SQLitemanager.php)

SQLite 相关的 SQL 操作

(14. SQLite 创建表结构

创建使用的方法和普通的数据库是一样的，使用 create 的语句,如果要定义字段的默认值，可以直接 name text default 'fukai' 这种方式,当把主键设为Integer时,则该主键为自动增长.

CREATE [TEMP|TEMPORARY] TABLE table_name (column_definitions [, constraints]);

关键字TEMP、TEMPORARY表示创建的是临时表

SQLite 中确保唯一性可以用关键字 UNIQUE

CREATE TABLE contacts ( id INTEGER PRIMARY KEY,
name TEXT NOT NULL COLLATE NOCASE,
phone TEXT NOT NULL DEFAULT 'UNKNOWN',
UNIQUE (name,phone) );

SQLite 表结构修改语句

ALTER TABLE table { RENAME TO name | ADD COLUMN column_def }
例：

SQLite> ALTER TABLE contacts
ADD COLUMN email TEXT NOT NULL DEFAULT '' COLLATE NOCASE;
SQLite> .schema contacts
CREATE TABLE contacts ( id INTEGER PRIMARY KEY,
name TEXT NOT NULL COLLATE NOCASE,
phone TEXT NOT NULL DEFAULT 'UNKNOWN',
email TEXT NOT NULL DEFAULT '' COLLATE NOCASE,
UNIQUE (name,phone) );

SQLite 查询语句

SELECT DISTINCT heading FROM tables WHERE predicate
GROUP BY columns HAVING predicate
ORDER BY columns LIMIT count,offset;

SQLite限定和排序
这个常用来设置取多少行和跳过多少行,可以用LIMIT和OFFSET保留字限定结果集的大小和范围。LIMIT指定返回记录的最大数量。OFFSET指定偏移的记录数。

SELECT * FROM food_types LIMIT 1 OFFSET 1 ORDER BY id;

获取最后一次插入的主键:

select last_insert_rowid();

SQLite 实现不存在才插入
这样给 INSERT 修改成 REPLACE 就能实现，注意重复的检查需要建索引

REPLACE  INTO leftover ( domain, hour, day, url, file, status) VALUES ('t','t','201','tt.gz', 't.gz','new'); 

 

SQLite 的 SELECT 的子句

编号	子句	操作	输入
1	FROM	Join	List of tables
2	WHERE	Restriction	Logical predicate
3	ORDER BY		List of columns
4	GROUP BY	Restriction	List of columns
5	HAVING	Restriction	Logical predicate
6	SELECT	Restriction	List of columns or expressions
7	DISTINCT	Restriction	List of columns
8	LIMIT	Restriction	Integer value
9	OFFSET	Restriction	Integer value

对 SQLite 速度进行了一下简单的测试

查5k日志中的ip地址信息

1.建表后默认的速度 SQLite的查找速度用时

real 3m53.812s

2.建索引后

SQLite> create index ipbegin_index on ip(ipbegin);
SQLite> create index ipend_index on ip(ipend);

SQLite的查找速度用时(这个太奇怪了反到慢了)

real 9m53.663s

2.使用SQLite来建复合索引 SQLite的查找速度用时(这个太奇怪了反到慢了)

2m8.845s

SQLite 插入时使用事件

SQLite的数据库本质上来讲就是一个磁盘上的文件,所以一切的数据库操作其实都会转化 为对文件的操作,而频繁的文件操作将会是一个很好时的过程,会极大地影响数据库存取的速度.如果写入大量的数据时,记的要使用事件,我插入6W条数据.不 使用事件要用10分钟,使用事件只有3秒.

BEGIN;
.read ip.sql
COMMIT;

相关资料和来源引用：
SQLite权威指南