一.下载地址
官网地址: 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 error3.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 005.对象及语法例子
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 原文链接：https://www.f2er.com/sqlite/202054.html
