SQLite3数据操作入门
2013-05-29 15:58:30 
阿炯
常见使用(1)创建数据库
直接在命令中键入 sqlite3 test.db; 即可创建了一个名为test.db的数据库
(2)创建表
create table Test(Id Integer primary key, value text);
此时即可完成表的创建，当把主键设为Integer时，则该主键为自动增长，插入数据时，可直接使用如下语句:
insert into Test values(null,’Acuzio’);
(3)获取最后一次插入的主键:
select last_insert_rowid();
(4)显示行数
sqlite>.mode col
sqlite>.headers on
(5)SQLite Master Table Schema
—————————————————————–
Name Description
—————————————————————–
type The object’s type (table, index, view, trigger)
name The object’s name
tbl_name The table the object is associated with
rootpage The object’s root page index in the database (where it begins)
sql The object’s SQL definition (DDL)
eg.
sqlite> .mode col
sqlite> .headers on
sqlite> select type, name, tbl_name, sql from sqlite_master order by type;
这样就能看到所有数据库中的信息，表、索引、视图等等
(6)导出数据
.output [filename],导出到文件中,如果该文件不存在，则自动创建
.dump 导出数据命令
.output stdout 返回输出到屏幕(进行其他操作)
eg.
sqlite>.output bakup.sql
sqlite>.dump
sqlite>.output stdout
这样就可以把数据导入到Acuzio.sql中
(8)导入数据
导入数据使用.read命令
eg.
如导入(7)中的数据
sqlite>.read bakup.sql
(9)备份数据库
在切换到Sqlite文件夹
sqlite3 test.db .dump > test.sql
如果在数据库中
sqlite> .output file.sql
sqlite> .dump
sqlite> .exit
(10)导入数据库
在切换到Sqlite文件夹
sqlite3 test.db < test.sql
(11)其他的SQLite工具
SQLite Database Browser
SQLite Control Center
SQLiteManager
(12)创建表结构
CREATE [TEMP|TEMPORARY] TABLE TABLE_NAME (column_definitions [, constraints]);
关键字TEMP、TEMPORARY表示创建的是临时表。
(13)SQLite中确保唯一性可以用关键字 UNIQUE
CREATE TABLE contacts ( id INTEGER PRIMARY KEY,
name TEXT NOT NULL COLLATE NOCASE,
phone TEXT NOT NULL DEFAULT 'UNKNOWN',
UNIQUE (name,phone) );
(14)修改 SQLite 表结构
ALTER TABLE TABLE { RENAME TO name | ADD COLUMN column_def }
eg.
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) );
(15)SQLite 查询
SELECT DISTINCT heading FROM TABLES WHERE predicate
GROUP BY COLUMNS HAVING predicate
ORDER BY COLUMNS LIMIT COUNT,offset;
(16)SQLite 中取多少行和跳过多少行
Limit 指返回记录的最大行数
Offset 指跳过多少行数据
(17)从CSV文件导入数据一例
#***************************************
# createdb.pl dump1.txt dump2.txt
#***************************************
use DBI;
die "Usage: ./createdb.pl dump1.txt dump2.txt ..." unless $#ARGV >= 0;
＃建立数据库连接
my $dbh = DBI->connect( "dbi:SQLite:dbname=./dump.db" );
＃创建数据表 my $create_sth = $dbh->do( "CREATE TABLE arp_record(
ar_hrd INTEGER,
ar_pro INTEGER,
ar_hln INTEGER,
ar_pln INTEGER,
ar_op INTEGER,
ar_sha VARCHAR(17),
ar_spa INTEGER,
ar_tha VARCHAR(17),
ar_tpa INTEGER )" );
＃创建索引
my $index_sth = $dbh->do( "CREATE INDEX idx_ar_sha ON arp_record( ar_sha )" );
$index_sth = $dbh->do( "CREATE INDEX idx_ar_spa ON arp_record( ar_spa )" );
$index_sth = $dbh->do( "CREATE INDEX idx_ar_tha ON arp_record( ar_tha )" );
$index_sth = $dbh->do( "CREATE INDEX idx_ar_tpa ON arp_record( ar_tpa )" );
＃插入数据
my $insert_sth = $dbh->prepare( q{ INSERT INTO arp_record VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ? ) } );
＃详细的调试信息
DBI->trace( 1, 'dbitrace.log');
$dbh->{ AutoCommit } = 0;
my $input_name;
foreach $input_name (@ARGV) {
open ( CSV, "<$input_name" ) or die "Can't open file: $!";
while( <CSV> ) {
chomp;
my( $ar_hrd, $ar_pro, $ar_hln, $ar_pln, $ar_op, $ar_sha, $ar_spa, $ar_tha, $ar_tpa ) = split /,/;
$insert_sth->bind_param( 1, $ar_hrd );
$insert_sth->bind_param( 2, $ar_pro );
$insert_sth->bind_param( 3, $ar_hln );
$insert_sth->bind_param( 4, $ar_pln );
$insert_sth->bind_param( 5, $ar_op );
$insert_sth->bind_param( 6, $dbh->quote( $ar_sha ) );
$insert_sth->bind_param( 7, $ar_spa );
$insert_sth->bind_param( 8, $dbh->quote( $ar_tha) );
$insert_sth->bind_param( 9, $ar_tpa );
$insert_sth->execute or die $dbh->errstr;
}
}
$dbh->commit or die $dbh->errstr;
close ( CSV );
$dbh->disconnect;
exit;
导入导出成txt或csv
使用官方提供的sqlite3.exe工具来操作sqlite的数据库，也能提供较多的实用管理操作工具。
简单管理
sqlite3.exe d:\\data\\freeoa.db //假设数据是'd:\\data\\freeoa.db'
>.databases //显示所有数据库
>.tables //显示当前数据库的表
>.schema tablename; //显示表格结构
>.output d:\\me.sql //导出当前数据库的 sql语句
>.dump
>.import d:\\me.sql //导入 //与mysql的source相似
---------------
导入
命令: .import
sqlite> .import 文件名 表名
注1: 不要忘了开头的点
注2: 这条语句不能用分号结束,非SQL不需要分号结束.
注3: 需要查看默认的分隔符separator. 必须一致. 如果不一致可能导致sqlite字段分割错误。查看分隔符使用命令 .show , 如果不一致可直接修改, 比如：
sqlite>.separator ","
将分隔符转为逗号。
示例1：
将文件mytable.txt中的数据导入表 tab_xx. (mytable.csv中字段以逗号分割)
sqlite> .separator ","
sqlite> .import mytable.txt tab_xx
sqlite>
导入结束。
---------------
导出
实现方式: 将输出重定向至文件。
命令: .output
sqlite> .output mytable.txt
然后输入sql语句，查询出要导的数据。查询后，数据不会显示在屏幕上，而直接写入文件。
结束后，输入：
sqlite> .output stdout
将输出重定向至屏幕。
示例2：
将 tab_xx 中的数据导出到文件mytable.csv
sqlite> .output mytable.csv
sqlite> select * from tab_xx;
sqlite> .output stdout
至此才导出完毕，不过中文(utf8)是乱码，使用dump时其中的中文是正常的。
插入数据时使用事务
SQLite的数据库本质上来讲就是一个磁盘上的文件，所以一切的数据库操作其实都会转化为对文件的操作，而频繁的文件操作将会是一个很好时的过程，会极大地影响数据库存取的速度。如果写入大量的数据时，记的要使用事件，插入6W条数据。不使用事件要用10分钟，使用事务只有3秒。
BEGIN;
.READ ip.SQL
COMMIT;
对 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的查找速度用时(这个太奇怪了反到慢了)
real 2m8.845s