一.下载地址
官网地址: http://sqlite.org/download.html,目前最新版本已经到version 3.7.11。
win版下载exe可直接使用
二.概况
本机测试的是3.7.10的windows版本
1.创建数据库
dos下进入存放sqlite3.exe路径,执行 F:>sqlite3.exe kenyon.db 如果不存在kenyon.db则新建,否则打开。该命令执行后结果是放在内存中的,执行后显示
SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
执行完后当继续创建表时,系统才会创建kenyon.db的文件
2.DDL操作
--Sqlite表字段与传统DBMS数据库不一样,可以不定义字段类型,只需标注字段名称,当然不推荐
--没有truncate概念
sqlite> create table test(id,name);
sqlite> drop table test;
sqlite> create table test2 as select * from test;
sqlite> truncate table test2;
Error: near "truncate": syntax error
3.DML操作
sqlite> insert into test values (1,'kenyon');
sqlite> insert into test values (2,'china');
sqlite> select *from test;
1|kenyon
2|china
sqlite> insert into test2 select * from test;
sqlite> select * from test2;
id name
---- -------------
1 kenyon
2 china
sqlite> update test set id = 1 where name = 'kenyon';
sqlite> delete from test where id = 1;
4.索引
--索引不是很智能,哪怕全表扫描,用到该索引字段时也会用到索引信息
sqlite> create index test_index on test(id);
sqlite> .explain on
sqlite> update test set id = 1;
sqlite> explain select *from test where id = 1;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 Integer 1 1 0 00
2 Goto 0 16 0 00
3 OpenRead 0 2 0 2 00
4 OpenRead 1 3 0 keyinfo(1,BINARY) 00
5 SeekGe 1 13 1 1 00
6 IdxGE 1 13 1 1 01
7 IdxRowid 1 2 0 00
8 Seek 0 2 0 00
9 Column 1 0 3 00
10 Column 0 1 4 00
11 ResultRow 3 2 0 00
12 Next 1 6 0 00
13 Close 0 0 0 00
14 Close 1 0 0 00
15 Halt 0 0 0 00
16 Transaction 0 0 0 00
17 VerifyCookie 0 5 0 00
18 TableLock 0 2 0 test 00
19 Goto 0 3 0 00
5.对象及语法例子
table:
create table tab_name(field1,field2..fieldn);
view:
create view v_test as select * from test where id = 1;
trigger:
create trigger tri_test after update on test for each row
begin
insert into test2 values (9090,'kenyon_test');
end;
index:
create index index_name on table_name(field_name);
6.其他用例
a 退出 .quit 或 .exit
b 帮助 .help
c 查看当前参数 .show
......
7.备份与恢复
--备份
F:\sqlite>sqlite3.exe kenyon.db ".dump">kenyon_bak.sql
--恢复
F:\sqlite>sqlite3.exe kenyon.db < kenyon_bak.sql
之前的备份文件内容:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(id,name);
INSERT INTO "test" VALUES(1,'kenyon');
INSERT INTO "test" VALUES(2,'china');
CREATE TABLE test2(id,name);
INSERT INTO "test2" VALUES(1,'kenyon');
INSERT INTO "test2" VALUES(2,'china');
CREATE INDEX test_index on test(id);
COMMIT;
8.隐藏系统表
sqlite_master --记录数据库的DDL语句,如table,view,trigger和index等
sqlite_temp_master --记录临时表(temporary table)信息,退出后临时表会被drop
9.暂时不支持
a.不支持right outer join && full outer join,但支持left outer join
b.不完全支持alter table,只支持rename table和add column,其他如drop column,add constraint暂不支持
c.不完全支持trigger,支持for each row,但不支持for each statement
d.view只读,不支持写
e.不支持grant和revoke
10.客户端工具
感觉sqlite expert和sqlite develop还是不错的,expert的最新版本3.4.11。下载地址 http://www.sqliteexpert.com/download.html,
有收费版和免费版区分,收费版多了加密支持,以及lua和pascal语言支持,其他一样
11.总结
Sqlite总体来讲是一个易上手的优质轻量级数据库,拥有事务,锁,游标,savepoint等一般DBMS数据库的特性,更令人惊艳的是支持WAL(write ahead log)模式,对于轻量级DB来说优势还是比较明显的,当然也会有一些局限性,如高并发的读写,SQL标准不全等.
参考:http://sqlite.org/docs.html