SQLite 3

使用工具：
SQLite Expert SQLite数据库管理工具，非常好用，类似我之前介绍的EMS SQL Manager 2007 for MySQL
System.Data.SQLite SQLite For ADO.NET驱动,类似我之前介绍的MySql.Data.dll
这2个工具都包括了SQLite的引擎
1。从http://www.sqlite.org/下载SQLite 3.3.4的版本
   为了方便，我把它解压了，就一个SQLite3.exe，放入Windows目录下。
   Cmd 进入命令行
   1)
   创建数据库文件:
   >SQLite3 d:\test.db 回车
   就生成了一个test.db在d盘。
   这样同时也SQLite3挂上了这个test.db
   2)
   用.help可以看看有什么命令
   >.help 回车即可
   3)可以在这里直接输入SQL语句创建表格 用;结束，然后回车就可以看到了
   4)看看有创建了多少表
   >.tables
   5)看表结构
   >.schema 表名
   6)看看目前挂的数据库
   >.database
   7)如果要把查询输出到文件
   >.output 文件名
   > 查询语句；
   查询结果就输出到了文件c:\query.txt
   把查询结果用屏幕输出
   >.output stdout
  把表结构输出，同时索引也会输出
     .dump 表名
   9)退出
   >.exit 或者.quit
2。从http://sqlite.phxsoftware.com/下载Ado.net驱动。
   下载了安装，在安装目录中存在System.Data.SQLite.dll
    我们只需要拷贝这个文件到引用目录，并添加引用即可对SQLite数据库操作了
   所有的Ado.net对象都是以SQLite开头的，比如SQLiteConnection
   连接串只需要如下方式
   Data Source=d:\test.db 或者DataSource=test.db--应用在和应用程序或者.net能够自动找到的目录
   剩下的就很简单了~~
3。SQL语法
   由于以前用SQLServer或者ISeries，所以DDL的语法很汗颜
   1)创建一个单个Primary Key的table
   CREATE TABLE  [Admin] (
[UserName] [nvarchar] (20)   PRIMARY KEY NOT NULL ,
[Password] [nvarchar] (50)   NOT NULL ,
[Rank] [smallint] NOT NULL ,
[MailServer] [nvarchar] (50)   NOT NULL ,
[MailUser] [nvarchar] (50)   NOT NULL ,
[MailPassword] [nvarchar] (50)   NOT NULL ,
[Mail] [nvarchar] (50)   NOT NULL
   ) ;
   2)创建一个多个Primary Key的table
   CREATE TABLE  [CodeDetail] (
[CdType] [nvarchar] (10)  NOT NULL ,
[CdCode] [nvarchar] (20)  NOT NULL ,
[CdString1] [ntext]   NOT NULL ,
[CdString2] [ntext]   NOT NULL ,
[CdString3] [ntext]   NOT NULL,
  PRIMARY KEY (CdType,CdCode)
   ) ;
   3)创建索引
   CREATE  INDEX [IX_Account] ON  [Account]([IsCheck], [UserName]);
   还可以视图等等。
4.还有很有用的SQL
  Select * from Sqlite_master
  Select datetime('now')
  Select date('now')
  Select time('now')
  以及很多函数，具体可以参考SQLite的wiki.
oh,还有就是看到有人说，好像成批插入的时候，启动事务，比不启动事务快n倍
还有就是尽量使用参数化的SQL,估计和商用DB一样能够自动Prepare.
SQL的指令格式
所以的SQL指令都是以分号（;）结尾的。如果遇到两个减号（--）则代表注解，sqlite3会略过去。
建立资料表
假设我们要建一个名叫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），它会在适时自动转换。
建立索引
如果资料表有相当多的资料，我们便会建立索引来加快速度。好比说：
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会在针对该字段作查询时，自动使用该索引。这一切的操作都是在幕后自动发生的，无须使用者特别指令。
加入一笔资料
接下来我们要加入资料了，加入的方法为使用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。
查询资料
讲到这里，我们终于要开始介绍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为什么这么流行了：这种语言允许你将各种查询条件组合在一起──而我们还没提到「跨数据库的联合查询」呢！）
如何更改或删除资料
了解select的用法非常重要，因为要在sqlite更改或删除一笔资料，也是靠同样的语法。
例如有一笔资料的名字打错了：
update film set starring='Jodie Foster' where starring='Jodee Foster';
就会把主角字段里，被打成'Jodee Foster'的那笔（或多笔）资料，改回成Jodie Foster。
delete from film where year < 1970;
就会删除所有年代早于1970年（不含）的电影了。
其他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;
**
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  包含了 年、月、日、时、分、秒、千分之一秒。
SQLite 分页
如果我要去11-20的Account表的数据
Select * From Account Limit 9 Offset 10;
以上语句表示从Account表获取数据，跳过10行，取9行
嗯，我觉得这个特性足够让很多的web中型网站使用这个了。
也可以这样写 select * from account limit10,9和上面的的效果一样。
这种写法MySQL也支持。
**
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错误代码。
**
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()
返回受上一语句影响的行数。
last_statement_change_count()
|xGv00|fcc8d4de8197f69fde70263fb4d52380
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)


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


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


(15)创建表结构


CREATE [TEMP|TEMPORARY] TABLE table_name (column_definitions [, constraints]);


关键字TEMP、TEMPORARY表示创建的是临时表


(16)在SQLite中有5种基本类型:
Integer/Real/Text/Blob/Null


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


(18)修改表
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) );


(19)查询
SELECT DISTINCT heading FROM tables WHERE predicate
GROUP BY columns HAVING predicate
ORDER BY columns LIMIT count,offset;


(20)Limit和Offset关键字
Limit 指返回记录的最大行数
Offset 指跳过多少行数据


(21)连接
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);


(22)别名(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;


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


(24)混合查询
关键字: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;


(25)更新数据
插入数据(insert)INSERT INTO table (column_list) VALUES (value_list);
insert语句用于单表操作,他能在同一时刻插入一条或多条数据


更新数据(update)UPDATE table SET update_list WHERE predicate;
更新操作不能用于约束条件为唯一的字段


删除数据(delete)DELETE FROM table WHERE predicate;


(26)查看表的详细信息(.schema)
eg.
.schema User (User为表)


(27)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 '',)


(28)SQLite有5中原始的数据类型
Integer/Real/Text/Blob/Null


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


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

2. SQLite插入有单引号（'）的字符串
使用双单引号即可，例如：
INSERT INTO xyz VALUES('5 O''clock');
插入数据库的是：5 0'clock。

3. 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用圆点作为日期的分隔附，并把时间转换为当地的时区的时间。

4. 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();