1)sql的指令格式

所有的sql指令都是以分号（;）结尾的。如果遇到两个减号（--）则代表注解，sqlite3会略过去。

2)建立资料表

假设我们要建一个名叫film的资料表，只要键入以下指令就可以了：
create table film(title,length,year,starring);
这样我们就建立了一个名叫film的资料表，里面有name、length、year、starring四个字段。
这个create table指令的语法为：
create table table_name(field1,field2,field3,...);
table_name是资料表的名称，fieldx则是字段的名字。sqlite3与许多sql数据库软件不同的是，它不在乎字段属于哪一种资料型态：sqlite3的字段可以储存任何东西：文字、数字、大量文字（blub），它会在适时自动转换。

3)建立索引

如果资料表有相当多的资料，我们便会建立索引来加快速度。好比说：
create index film_title_index on film(title);
意思是针对film资料表的name字段，建立一个名叫film_name_index的索引。这个指令的语法为
create index index_name on table_name(field_to_be_indexed);
一旦建立了索引，sqlite3会在针对该字段作查询时，自动使用该索引。这一切的操作都是在幕后自动发生的，无须使用者特别指令。

4)加入一笔资料

接下来我们要加入资料了，加入的方法为使用insert into指令，语法为：
insert into table_name values(data1,data2,data3,...);
例如我们可以加入
insert into film values ('Silence of the Lambs,The',118,1991,'Jodie Foster');insert into film values ('Contact',153,1997,'Jodie Foster');insert into film values ('Crouching Tiger,Hidden Dragon',120,2000,'Yun-Fat Chow');insert into film values ('Hours,114,2002,'Nicole Kidman');
如果该字段没有资料，我们可以填NULL。

5)查询资料

讲到这里，我们终于要开始介绍sql最强大的select指令了。我们首先简单介绍select的基本句型：
select columns from table_name where expression;
最常见的用法，当然是倒出所有数据库的内容：
select * from film;
如果资料太多了，我们或许会想限制笔数：
select * from film limit 10;
或是照着电影年份来排列：
select * from film order by year limit 10;
或是年份比较近的电影先列出来：
select * from film order by year desc limit 10;
或是我们只想看电影名称跟年份：
select title,year from film order by year desc limit 10;
查所有茱蒂佛斯特演过的电影：
select * from film where starring='Jodie Foster';
查所有演员名字开头叫茱蒂的电影('%' 符号便是 sql 的万用字符）：
select * from film where starring like 'Jodie%';
查所有演员名字以茱蒂开头、年份晚于1985年、年份晚的优先列出、最多十笔，只列出电影名称和年份：
select title,year from film where starring like 'Jodie%' and year >= 1985 order by year desc limit 10;
有时候我们只想知道数据库一共有多少笔资料：
select count(*) from film;
有时候我们只想知道1985年以后的电影有几部：
select count(*) from film where year >= 1985;
（进一步的各种组合，要去看sql专书，不过你大概已经知道sql为什么这么流行了：这种语言允许你将各种查询条件组合在一起──而我们还没提到「跨数据库的联合查询」呢！）

6)如何更改或删除资料

了解select的用法非常重要，因为要在sqlite更改或删除一笔资料，也是靠同样的语法。
例如有一笔资料的名字打错了：
update film set starring='Jodie Foster' where starring='Jodee Foster';
就会把主角字段里，被打成'Jodee Foster'的那笔（或多笔）资料，改回成Jodie Foster。
delete from film where year < 1970;
就会删除所有年代早于1970年（不含）的电影了。

7)其他sqlite的特别用法

sqlite可以在shell底下直接执行命令：
sqlite3 film.db "select * from film;"
输出 HTML 表格：
sqlite3 -html film.db "select * from film;"
将数据库「倒出来」：
sqlite3 film.db ".dump" > output.sql
利用输出的资料，建立一个一模一样的数据库（加上以上指令，就是标准的sql数据库备份了）：
sqlite3 film.db < output.sql
在大量插入资料时，你可能会需要先打这个指令：
begin;
插入完资料后要记得打这个指令，资料才会写进数据库中：
commit;

8)sqlite3支持数据类型

NULL
INTEGER
REAL
TEXT
BLOB
但实际上，sqlite3也接受如下的数据类型：
smallint 16 位元的整数。
interger 32 位元的整数。
decimal(p,s) p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值 ，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。
float 32位元的实数。
double 64位元的实数。
char(n) n 长度的字串，n不能超过 254。
varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。
graphic(n) 和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。 这个形态是为了支援两个字元长度的字体，例如中文字。
vargraphic(n) 可变长度且其最大长度为 n 的双字元字串，n不能超过 2000。
date 包含了 年份、月份、日期。
time 包含了 小时、分钟、秒。
timestamp 包含了 年、月、日、时、分、秒、千分之一秒。

9)sqlite 分页

如果我要去11-20的Account表的数据
Select * From Account Limit 9 Offset 10;
以上语句表示从Account表获取数据，跳过10行，取9行
嗯，我觉得这个特性足够让很多的web中型网站使用这个了。
也可以这样写 select * from account limit10,9和上面的的效果一样。
这种写法MysqL也支持。

10)sqlite 建立自动增长字段

简短回答：声明为 INTEGER PRIMARY KEY 的列将会自动增长。
长一点的答案： 如果你声明表的一列为 INTEGER PRIMARY KEY，那么， 每当你在该列上插入一NULL值时， NULL自动被转换为一个比该列中最大值大1的一个整数，如果表是空的， 将会是1。 (如果是最大可能的主键 9223372036854775807，那个，将键值将是随机未使用的数。） 如，有下列表：
CREATE TABLE t1(
a INTEGER PRIMARY KEY,
b INTEGER
);
在该表上，下列语句
INSERT INTO t1 VALUES(NULL,123);
在逻辑上等价于：
INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);
有一个新的API叫做 sqlite3_last_insert_rowid()， 它将返回最近插入的整数值。
注意该整数会比表中该列上的插入之前的最大值大1。 该键值在当前的表中是唯一的。但有可能与已从表中删除的值重叠。要想建立在整个表的生命周期中唯一的键值，需要在 INTEGER PRIMARY KEY 上增加AUTOINCREMENT声明。那么，新的键值将会比该表中曾能存在过的最大值大1。如果最大可能的整数值在数据表中曾经存在过，INSERT将 会失败， 并返回sqlITE_FULL错误代码。

11)sqlite内建函数表

算术函数
abs(X)
返回给定数字表达式的绝对值。
max(X,Y[,...])
返回表达式的最大值。
min(X,...])
返回表达式的最小值。
random(*)
返回随机数。
round(X[,Y])
返回数字表达式并四舍五入为指定的长度或精度。
字符处理函数
length(X)
返回给定字符串表达式的字符个数。
lower(X)
将大写字符数据转换为小写字符数据后返回字符表达式。
upper(X)
返回将小写字符数据转换为大写的字符表达式。
substr(X,Y,Z)
返回表达式的一部分。
randstr()
quote(A)
like(A,B)
确定给定的字符串是否与指定的模式匹配。
glob(A,B)
条件判断函数
coalesce(X,...])
ifnull(X,Y)
nullif(X,Y)
集合函数
avg(X)
返回组中值的平均值。
count(X)
返回组中项目的数量。
max(X)
返回组中值的最大值。
min(X)
返回组中值的最小值。
sum(X)
返回表达式中所有值的和。
其他函数
typeof(X)
返回数据的类型。
last_insert_rowid()
返回最后插入的数据的ID。
sqlite_version(*)
返回sqlite的版本。
change_count()
返回受上一语句影响的行数。
last_statement_change_count()
|xGv00|fcc8d4de8197f69fde70263fb4d52380

5、sqlite 学习笔记

(1)创建数据库

在命令行中切换到sqlite.exe所在的文件夹
在命令中键入sqlite3 test.db;即可创建了一个名为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)在DOS中，键入Ctrl+C,退出数据库，Unix中，使用Ctrl+D

(6)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;
这样就能看到所有数据库中的信息，表、索引、视图等等

(7)导出数据

.output [filename],导出到文件中,如果该文件不存在，则自动创建
.dump 导出数据命令
.output stdout 返回输出到屏幕(进行其他操作)
eg.
sqlite>.output Acuzio.sql
sqlite>.dump
sqlite>.output stdout
这样就可以把数据导入到Acuzio.sql中

(8)导入数据

导入数据使用.read命令
eg.
如导入(7)中的数据
sqlite>.read Acuio.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)备份二进制格式数据库

vacuum:释放掉已经被删除的空间(数据和表等被删除，不会被清空空间)
sqlite3 test.db VACUUM
cp test.db test.backup

(12)获取数据库信息

如果想获得物理数据库结构的信息，可以去sqlite网站上下载sqlite Analyzer工具
使用: sqlite3_analyzer test.db

(13)其他的sqlite工具

sqlite Database Browser ( http://sqlitebrowser.sourceforge.net/)
sqlite Control Center ( http://bobmanc.home.comcast.net/sqlitecc.html)
sqliteManager ( www.sqlabs.net/sqlitemanager.php)

(14)执行语句

sqlite 与其他数据库不同，它是以(;)来执行语句，而不是(go).

(15)sqlite注释

(--)或(/* */)
eg.
-- This is a comment on one line
/* This is a comment spanning
two lines */

(16)创建表结构

CREATE [TEMP|TEMPORARY] TABLE table_name (column_definitions [,constraints]);
关键字TEMP、TEMPORARY表示创建的是临时表

(17)在sqlite中有5种基本类型

Integer/Real/Text/Blob/Null

(18)确保唯一性

可以用关键字UNIQUE
eg.
CREATE TABLE contacts ( id INTEGER PRIMARY KEY,
name TEXT NOT NULL COLLATE NOCASE,
phone TEXT NOT NULL DEFAULT 'UNKNOWN',
UNIQUE (name,phone) );

(19)修改表

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,
email TEXT NOT NULL DEFAULT '' COLLATE NOCASE,phone) );

(20)查询

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

(21)Limit和Offset关键字

Limit 指返回记录的最大行数
Offset 指跳过多少行数据

(22)连接

SELECT heading FROM LEFT_TABLE join_type RIGHT_TABLE ON join_condition;
eg.
SELECT * FROM A INNER JOIN B ON A.a=B.a;
SELECT * FROM A LEFT JOIN B ON A.a=B.a;
SELECT * FROM A NATURAL JOIN B ON A;
SELECT * FROM A CROSS JOIN B ON A;
Remark:当连接条件建立在相同名字的行上,我们可以简单的用关键字(using)
eg.
SELECT * FROM A INNER JOIN B USING(a);

(23)别名(AS)

eg.
SELECT f.name,t.name FROM foods f,food_types t
WHERE f.type_id=t.id LIMIT 10;
SELECT e.name AS Episode,COUNT(f.id) AS Foods
FROM foods f
JOIN foods_episodes fe on f.id=fe.food_id
JOIN episodes e on fe.episode_id=e.id
GROUP BY e.id
ORDER BY Foods DESC
LIMIT 10;

(24)子查询(IN)

SELECT COUNT(*) FROM foods WHERE type_id
IN (SELECT id FROM food_types WHERE name='Bakery' OR name='Cereal');

(25)混合查询

关键字:UNION,INTERSECT,EXCEPT
混合查询必须满足以下条件:
--关系结果必须有相同的行数
--混合查询只能使用一个order by
eg.
SELECT f.* FROM foods f
INNER JOIN
(SELECT food_id,count(food_id) as count FROM foods_episodes
GROUP BY food_id
ORDER BY count(food_id) DESC LIMIT 10) top_foods
ON f.id=top_foods.food_id
INTERSECT
SELECT f.* FROM foods f
INNER JOIN foods_episodes fe ON f.id = fe.food_id
INNER JOIN episodes e ON fe.episode_id = e.id
WHERE e.season BETWEEN 3 and 5
ORDER BY f.name;

(26)更新数据

插入数据
(insert)INSERT INTO table (column_list) VALUES (value_list);
insert语句用于单表操作,他能在同一时刻插入一条或多条数据
更新数据
(update)UPDATE table SET update_list WHERE predicate;
更新操作不能用于约束条件为唯一的字段
删除数据
(delete)DELETE FROM table WHERE predicate;

(27)查看表的详细信息

.schema
eg.
.schema User (User为表)

(28)sqlite内建3种比较方式(通过COLLATE关键字进行定义这一行的比较方式)

BINARY(默认),他通过使用C函数--memcmp()，一个字节一个字节的进行比较
这种方式很好的适用于西方的语言,如English
NOCASE,是在英语中通过26个ASCII字符进行比较的
eg.'JERRY'和'Jerry'被认为是一样的
REVERSE,更多的用于测试!
eg.
CREATE TABLE [User] (
[id] VARCHAR(40) DEFAULT '' PRIMARY KEY,
[usrName] VARCHAR(50) COLLATE NOCASE DEFAULT '',
[usrPwd] VARCHAR(50) DEFAULT '',)

(29)sqlite有5中原始的数据类型

Integer/Real/Text/Blob/Null

(30)typeof()函数将返回一个他代表的存储类型(sqlite支持的)

(31)事务

BEGIN…COMMIT/ROLLBACK

(32)sqlite插入有单引号（'）的字符串

使用双单引号即可，例如：
INSERT INTO xyz VALUES('5 O''clock');
插入数据库的是：5 0'clock。

(33)sqlite中的时间日期函数

sqlite包含了如下时间/日期函数：
datetime().......................产生日期和时间
date()...........................产生日期
time()...........................产生时间
strftime().......................对以上三个函数产生的日期和时间进行格式化
datetime()的用法是：datetime(日期/时间,修正符,修正符...)
date()和time()的语法与datetime()相同。
在时间/日期函数里可以使用如下格式的字符串作为参数：
YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
其中now是产生现在的时间。
举例（写这个笔记的时间是2006年10月17日晚8点到10点，测试环境：sqlite 2.8.17，WinXP，北京时间）：
例1.
select datetime('now');
结果：2006-10-17 12:55:54
例2.
select datetime('2006-10-17');
结果：2006-10-17 12:00:00
例3.
select datetime('2006-10-17 00:20:00','+1 hour','-12 minute');
结果：2006-10-17 01:08:00
例4.
select date('2006-10-17','+1 day','+1 year');
结果：2007-10-18
例5.
select datetime('now','start of year');
结果：2006-01-01 00:00:00
例6.
select datetime('now','start of month');
结果：2006-10-01 00:00:00
例7.
select datetime('now','start of day');
结果：2006-10-17 00:00:00
例8.
select datetime('now','+10 hour','start of day','+10 hour');
结果：2006-10-17 10:00:00
例9.
select datetime('now','localtime');
结果：2006-10-17 21:21:47
例10.
select datetime('now','+8 hour');
结果：2006-10-17 21:24:45
例3中的+1 hour和-12 minute表示可以在基本时间上（datetime函数的第一个参数）增加或减少一定时间。
例5中的start of year表示一年开始的时间。
从例8可以看出，尽管第2个参数加上了10个小时，但是却被第3个参数“start of day”把时间归零到00:00:00，随后的第4个参数在00:00:00 的基础上把时间增加了10个小时变成了10:00:00。
例9把格林威治时区转换成本地时区。
例10把格林威治时区转换成东八区。
strftime()函数可以把YYYY-MM-DD HH:MM:SS格式的日期字符串转换成其它形式的字符串。
strftime()的语法是strftime(格式,日期/时间,...)
它可以用以下的符号对日期和时间进行格式化：
&percnt;d 月份,01-31
&percnt;f 小数形式的秒，SS.SSS
&percnt;H 小时,00-23
&percnt;j 算出某一天是该年的第几天，001-366
&percnt;m 月份，00-12
&percnt;M 分钟,00-59
&percnt;s 从1970年1月1日到现在的秒数
&percnt;S 秒,00-59
&percnt;w 星期,0-6 (0是星期天)
&percnt;W 算出某一天属于该年的第几周,01-53
&percnt;Y 年,YYYY
&percnt;&percnt; 百分号
strftime()的用法举例如下：
例11用圆点作为日期的分隔附，并把时间转换为当地的时区的时间。
select strftime('&percnt;Y.&percnt;m.&percnt;d &percnt;H:&percnt;M:&percnt;S','now','localtime');
结果：2006.10.17 21:41:09
例11用圆点作为日期的分隔附，并把时间转换为当地的时区的时间。

(34)sqlite数据库中删除数据，但数据库文件没有变小

当你从sqlite数据库中删除数据时， 未用的磁盘空间将会加入一个内部的“自由列表”中。 当你下次插入数据时，这部分空间可以重用。磁盘空间不会丢失， 但也不会返还给操作系统。 如果删除了大量数据，而又想缩小数据库文件占用的空间，执行 VACUUM 命令。 VACUUM 将会从头重新组织数据库。这将会使用数据库有一个空的“自由链表”， 数据库文件也会最小。但要注意的是，VACUUM 的执行会需要一些时间（在sqlite开发时，在Linux上，大约每M字节需要半秒种），并且， 执行过程中需要原数据库文件至多两倍的临时磁盘空间。 对于 sqlite 3.1版本，一个 auto-vacumm 模式可以替代 VACUUM 命令。 可以使用 auto_vacuum pragma 打开。 C#里面实现是: mycon = new sqliteConnection("Data Source=test.db3;Version=3;New=True;"); mycon.Open(); sqliteCommand com = mycon.CreateCommand(); com.CommandText = "vacuum"; com.ExecuteNonQuery();