SQLite3初级使用
(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, The',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,Y[,...]) 返回表达式的最小值。
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,Y[,...])
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() 返回受上一语句影响的行数。
---------------------------------------------------------------------
************************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,
name TEXT NOT NULL COLLATE NOCASE,
phone TEXT NOT NULL DEFAULT 'UNKNOWN',
email TEXT NOT NULL DEFAULT '' COLLATE NOCASE,
UNIQUE (name,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(格式, 日期/时间, 修正符, 修正符, ...)
它可以用以下的符号对日期和时间进行格式化：
%d 月份, 01-31
%f 小数形式的秒，SS.SSS
%H 小时, 00-23
%j 算出某一天是该年的第几天，001-366
%m 月份，00-12
%M 分钟, 00-59
%s 从1970年1月1日到现在的秒数
%S 秒, 00-59
%w 星期, 0-6 (0是星期天)
%W 算出某一天属于该年的第几周, 01-53
%Y 年, YYYY
%% 百分号
strftime()的用法举例如下：
例11用圆点作为日期的分隔附，并把时间转换为当地的时区的时间。
select strftime('%Y.%m.%d
%H:%M:%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();
劝君更尽一杯酒，与尔同销万古愁。
