1.特性
1)ACID事物
2)零配置--无需安装与管理配置
6)足够小,大致3w行C代码,250k
8)简单,轻松的API
9)良好注释的源代码,并且有着90%以上的测试覆盖率
10)包含TCL绑定,可以通过wrapper支持其他语言的绑定
11)独立:没有额外依赖
12)Source完全的open,你可以用于任何用途,甚至出售
2.数据类型
CREATE TABLE ex(
a VARCHAR(10),可变长度的字符串
b NVARCHAR(15),
c TEXT,文本型
d INTEGER,整型
e FLOAT,浮点型
f BOOLEAN,布尔型
i TIMESTAMP,0); font-weight:normal; word-spacing:0px">
j NUMERIC(10,5),0); font-weight:normal; word-spacing:0px">
k VARYING CHARACTER(24),0); font-weight:normal; word-spacing:0px">
l NATIONAL VARYING CHARACTER(16)
NULL 空值
3.支持的sql
BEGIN TRANSACTION
END TRANSACTION
commentCOMMIT TRANSACTION
CREATE INDEX
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DETACH DATABASE
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
COPY
DELETEDETACH DATABASE
DROP INDEX
DROP TABLE
DROP TRIGGER
DROP VIEW
EXPLAIN
expression
INSERT
ON CONFLICT clause
PRAGMA
REPLACE
ROLLBACK TRANSACTION
SELECT
UPDATE
DROP TABLE
DROP TRIGGER
DROP VIEW
EXPLAIN
expression
INSERT
ON CONFLICT clause
PRAGMA
REPLACE
ROLLBACK TRANSACTION
SELECT
UPDATE
4.数据库的简单使用:
1)创建数据库
在命令提示符下:
sqlite> ;
- [carl@Fedorasqlite]$sqlite3testsql.db
- sqliteversion3.7.112012-03-2011:35:50
- Enter".help"forinstructions
- Entersqlstatementsterminatedwitha";"
- sqlite>.quit
- [carl@Fedorasqlite]$ls
- [carl@Fedorasqlite]$sqlite3testsql.db
- sqliteversion3.7.112012-03-2011:35:50
- Enter".help"forinstructions
- Entersqlstatementsterminatedwitha";"
- sqlite>;
- sqlite>.quit
- [carl@Fedorasqlite]$ls
- testsql.db
- [carl@Fedorasqlite]$
[carl@Fedora sqlite]$ sqlite3 testsql.db sqlite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter sql statements terminated with a ";" sqlite> .quit [carl@Fedora sqlite]$ ls [carl@Fedora sqlite]$ sqlite3 testsql.db sqlite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter sql statements terminated with a ";" sqlite> ; sqlite> .quit [carl@Fedora sqlite]$ ls testsql.db [carl@Fedora sqlite]$
2)创建表
create table testtable(column1 varchar(10),column2 int);
不要忘了加分号,代表一条语句输入完毕
testtable是要创建的表名。create和table是关键字,column1 column2是两个表项。
varchar(10) int是类型。虽然sqlite是无类型的,但是创建表的时候指定一个类型,可以增强可移植性。
操作如下:
操作如下:
- [carl@Fedorasqlite]$sqlite3testsql.db
- sqliteversion3.7.112012-03-2011:35:50
- Enter".help"forinstructions
- Entersqlstatementsterminatedwitha";"
- sqlite>createtabletesttable(column1varchar(10),column2int);
- sqlite>.tab
- testtable
- sqlite>select*fromsqlite_master
- ...>;
- table|testtable|testtable|2|CREATETABLEtesttable(column1varchar(10),column2int)
- sqlite>
[carl@Fedora sqlite]$ sqlite3 testsql.db sqlite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter sql statements terminated with a ";" sqlite> create table testtable(column1 varchar(10),column2 int); sqlite> .tab testtable sqlite> select * from sqlite_master ...> ; table|testtable|testtable|2|CREATE TABLE testtable(column1 varchar(10),column2 int) sqlite>
3)向表中插入一条记录
insert into testtable values("column1 is string",10);
插入完毕,哎呀,column1 is string这明显超出了varchar(10)类型指定的长度了,让我们查看一下表中的内容现在怎么样了吧。看下节------->
4)查询表中内容
select * from testtable;
此条语句可列出表testtable中的所有内容。
让我们先看一下上面的插入语句结果如何
sqlite> insert into testtable values("column1 is string,this is longer",10); sqlite> select * from testtable; column1 is string|10 column1 is string,this is longer|10 sqlite>
看起来第一列的长度完全不受限制
sqlite> .quit [carl@Fedora sqlite]$ sqlite3 testsql.db sqlite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter sql statements terminated with a ";" sqlite> select * from testtable; column1 is string|10 column1 is string,this is longer|10 sqlite>
6)查询一个数据库中所有的表名
sqlite数据库中有一个系统建立的表,sqlite_master,上面第2)小节也有调用,查询这个表就可以得到数据库中所有的sqlite> create table testtable2(column1 int); sqlite> insert into testtable2 values(30); sqlite> select * from testtable2; 30 sqlite> select * from sqlite_master; table|testtable|testtable|2|CREATE TABLE testtable(column1 varchar(10),column2 int) table|testtable2|testtable2|3|CREATE TABLE testtable2(column1 int) sqlite>
关于此表,官网FAQ定义如下:
CREATE TABLE sqlite_master(type TEXT,name TEXT,tal_name TEXT,rootpage INTEGER,sql TEXT);
7)sqlite的输出格式
默认的输出格式是“列表”。在列表模式下,每条查询结果记录被写在一行中并且每列之间以一个字符串分隔符隔开,默认的分隔符为管道符号"|"。sqlite支持的输出格式包括:csv column html insert line list tabs tcl
具体使用方法如下:
- [carl@Fedorasqlite]$sqlite3testsql.db
- sqliteversion3.7.112012-03-2011:35:50
- Enter".help"forinstructions
- Entersqlstatementsterminatedwitha";"
- sqlite>.modelist
- sqlite>select*fromtesttable
- ...>;
- column1isstring|10
- column1isstring,thisislonger|10
- sqlite>.modecsv
- sqlite>select*fromtesttable;
- "column1isstring",10
- "column1isstring,10
- sqlite>.modecolumn
- sqlite>select*fromtesttable;
- column1isstring10
- column1isstring10
- sqlite>.modehtml
- sqlite>select*fromtesttable;
- <TR><TD>column1isstring</TD>
- <TD>10</TD>
- </TR>
- <TR><TD>column1isstring,thisislonger</TD>
- <TD>10</TD>
- </TR>
- sqlite>.modeinsert
- sqlite>select*fromtesttable;
- INSERTINTOtableVALUES('column1isstring',10);
- INSERTINTOtableVALUES('column1isstring,thisislonger',10);
- sqlite>.modeline
- sqlite>select*fromtesttable;
- column1=column1isstring
- column2=10
- column1=column1isstring,thisislonger
- column2=10
- sqlite>.modetabs
- sqlite>select*fromtesttable;
- column1isstring10
- column1isstring,thisislonger10
- sqlite>.modetcl
- sqlite>select*fromtesttable;
- "column1isstring""10"
- "column1isstring,thisislonger""10"
- sqlite>.outputoutput.txt//输出到文件
- sqlite>select*fromtesttable;
- sqlite>.exit
- [carl@Fedorasqlite]$catoutput.txt
- "column1isstring""10"
- "column1isstring,thisislonger""10"
- [carl@Fedorasqlite]$
[carl@Fedora sqlite]$ sqlite3 testsql.db sqlite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter sql statements terminated with a ";" sqlite> .mode list sqlite> select * from testtable ...> ; column1 is string|10 column1 is string,this is longer|10 sqlite> .mode csv sqlite> select * from testtable; "column1 is string",10 "column1 is string,10 sqlite> .mode column sqlite> select * from testtable; column1 is string 10 column1 is string 10 sqlite> .mode html sqlite> select * from testtable; <TR><TD>column1 is string</TD> <TD>10</TD> </TR> <TR><TD>column1 is string,this is longer</TD> <TD>10</TD> </TR> sqlite> .mode insert sqlite> select * from testtable; INSERT INTO table VALUES('column1 is string',10); INSERT INTO table VALUES('column1 is string,this is longer',10); sqlite> .mode line sqlite> select * from testtable; column1 = column1 is string column2 = 10 column1 = column1 is string,this is longer column2 = 10 sqlite> .mode tabs sqlite> select * from testtable; column1 is string 10 column1 is string,this is longer 10 sqlite> .mode tcl sqlite> select * from testtable; "column1 is string" "10" "column1 is string,this is longer" "10" sqlite> .output output.txt //输出到文件 sqlite> select * from testtable; sqlite> .exit [carl@Fedora sqlite]$ cat output.txt "column1 is string" "10" "column1 is string,this is longer" "10" [carl@Fedora sqlite]$
8)查看数据库中所有的表
除了上面第6)小节介绍的sqlite_master外,还有另一种方法,如下sqlite> .tables testtable testtable2 sqlite>
9)查看所有的表的创建语句
sqlite> .schema CREATE TABLE testtable(column1 varchar(10),column2 int); CREATE TABLE testtable2(column1 int); sqlite> .schema testtable CREATE TABLE testtable(column1 varchar(10),column2 int); sqlite> .schema testtable2 CREATE TABLE testtable2(column1 int); sqlite>
10)删除记录
sqlite> select * from testtable2; 30 sqlite> delete from testtable2 where column1=30; sqlite> select * from testtable2; sqlite>
11)数据库的导入与导出
即备份一个一模一样的数据库,首先看一下一个新的命令sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE testtable(column1 varchar(10),column2 int); INSERT INTO testtable VALUES('column1 is string',10); INSERT INTO testtable VALUES('column1 is string,10); CREATE TABLE testtable2(column1 int); COMMIT; sqlite>
可以得到一个用来创建当前数据库的原子操作集合。我们可以利用这个集合来创建一个新的一模一样的数据库
- [carl@Fedorasqlite]$sqlite3copiedsql.db
- sqliteversion3.7.112012-03-2011:35:50
- Enter".help"forinstructions
- Entersqlstatementsterminatedwitha";"
- sqlite>.tab
- sqlite>.readdump.sql
- sqlite>.tab
- testtabletesttable2
- sqlite>select*fromtesttable;
- column1isstring|10
- column1isstring,thisislonger|10
- sqlite>select*fromtesttable2;
- sqlite>.quit
- [carl@Fedorasqlite]$
[carl@Fedora sqlite]$ sqlite3 copiedsql.db sqlite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter sql statements terminated with a ";" sqlite> .tab sqlite> .read dump.sql sqlite> .tab testtable testtable2 sqlite> select * from testtable; column1 is string|10 column1 is string,this is longer|10 sqlite> select * from testtable2; sqlite> .quit [carl@Fedora sqlite]$
以上也是从网上查出并加以总结,水平有限,欢迎交流。
下一篇sqlITE文章sqlite之我见--C/C++ API接口介绍中,我会介绍一下一些常用的C/C++ API接口。
水平有限,如果有朋友发现错误,欢迎留言交流。