SQLITE入门至精通

来源：互联网发布：windows10安装不了软件编辑：程序博客网时间：2024/05/15 07:52

菜鸟入门
1。从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

   8)把表结构输出，同时索引也会输出
     .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内建函数表

算术函数

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()

oh,还有就是看到有人说，好像成批插入的时候，启动事务，比不启动事务快n倍
还有就是尽量使用参数化的SQL,估计和商用DB一样能够自动Prepare.

===========

sqlite可以在shell/dos command底下直接执行命令：

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;

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错误代码。

多个应用程序或一个应用程序的多个实例可以同时访问同一个数据库文件吗？

多个进程可同时打开同一个数据库。多个进程可以同时进行SELECT 操作，但在任一时刻，只能有一个进程对数据库进行更改。

SQLite使用读、写锁控制对数据库的访问。（在Win95/98/ME等不支持读、写锁的系统下，使用一个概率性的模拟来代替。）但使用时要注意：如果数据库文件存放于一个NFS文件系统上，这种锁机制可能不能正常工作。这是因为fcntl() 文件锁在很多NFS上没有正确的实现。在可能有多个进程同时访问数据库的时候，应该避免将数据库文件放到NFS上。在Windows上，Microsoft的文档中说：如果使用 FAT 文件系统而没有运行 share.exe守护进程，那么锁可能是不能正常使用的。那些在Windows上有很多经验的人告诉我：对于网络文件，文件锁的实现有好多Bug，是靠不住的。如果他们说的是对的，那么在两台或多台Windows机器间共享数据库可能会引起不期望的问题。

我们意识到，没有其它嵌入式的 SQL 数据库引擎能象 SQLite这样处理如此多的并发。SQLite允许多个进程同时打开一个数据库，同时读一个数据库。当有任何进程想要写时，它必须在更新过程中锁住数据库文件。但那通常只是几毫秒的时间。其它进程只需等待写进程干完活结束。典型地，其它嵌入式的SQL数据库引擎同时只允许一个进程连接到数据库。

但是，Client/Server数据库引擎（如 PostgreSQL, MySQL, 或 Oracle）通常支持更高级别的并发，并且允许多个进程同时写同一个数据库。这种机制在Client/Server结构的数据库上是可能的，因为总是有一个单一的服务器进程很好地控制、协调对数据库的访问。如果你的应用程序需要很多的并发，那么你应该考虑使用一个Client/Server结构的数据库。但经验表明，很多应用程序需要的并发，往往比其设计者所想象的少得多。

当SQLite试图访问一个被其它进程锁住的文件时，缺省的行为是返回 SQLITE_BUSY。可以在C代码中使用 sqlite3_busy_handler() 或 sqlite3_busy_timeout() API 函数调整这一行为。

在SQLite数据库中如何列出所有的表和索引？

如果你运行 sqlite3 命令行来访问你的数据库，可以键入 “.tables”来获得所有表的列表。或者，你可以输入 “.schema” 来看整个数据库模式，包括所有的表的索引。输入这些命令，后面跟一个LIKE模式匹配可以限制显示的表。

在一个 C/C++ 程序中（或者脚本语言使用 Tcl/Ruby/Perl/Python 等）你可以在一个特殊的名叫SQLITE_MASTER 上执行一个SELECT查询以获得所有表的索引。每一个SQLite数据库都有一个叫 SQLITE_MASTER的表，它定义数据库的模式。 SQLITE_MASTER 表看起来如下：

CREATE TABLE sqlite_master (
type TEXT,
name TEXT,
tbl_name TEXT,
rootpage INTEGER,
sql TEXT
);
对于表来说，type 字段永远是 ''''table''''，name 字段永远是表的名字。所以，要获得数据库中所有表的列表，使用下列SELECT语句：

SELECT name FROM sqlite_master
WHERE type=''''table''''
ORDER BY name;
对于索引，type 等于 ''''index'''', name 则是索引的名字，tbl_name 是该索引所属的表的名字。不管是表还是索引，sql字段是原先用 CREATE TABLE 或 CREATE INDEX 语句创建它们时的命令文本。对于自动创建的索引（用来实现 PRIMARY KEY 或 UNIQUE 约束），sql字段为NULL。

SQLITE_MASTER 表是只读的。不能对它使用 UPDATE、INSERT 或 DELETE。它会被 CREATE TABLE、CREATE INDEX、DROP TABLE 和 DROP INDEX 命令自动更新。

临时表不会出现在 SQLITE_MASTER 表中。临时表及其索引和触发器存放在另外一个叫 SQLITE_TEMP_MASTER的表中。SQLITE_TEMP_MASTER 跟 SQLITE_MASTER 差不多，但它只是对于创建那些临时表的应用可见。如果要获得所有表的列表，不管是永久的还是临时的，可以使用类似下面的命令：

SELECT name FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type=''''table''''
ORDER BY name

在SQLite中，VARCHAR字段最长是多少？

SQLite不强制 VARCHAR 的长度。你可以在SQLITE中声明一个 VARCHAR(10)，SQLite还是可以很高兴地允许你放入500个字符。并且这500个字符是原封不动的，它永远不会被截断。

SQLite支持二进制大对象吗？

SQLite 3.0 及以后版本允许你在任何列中存储 BLOB 数据。即使该列被声明为其它类型也可以。

在SQLite中，如何在一个表上添加或删除一列？

SQLite有有限地 ALTER TABLE 支持。你可以使用它来在表的末尾增加一列，可更改表的名称。如果需要对表结构做更复杂的改变，则必须重新建表。重建时可以先将已存在的数据放到一个临时表中，删除原表，创建新表，然后将数据从临时表中复制回来。

如，假设有一个 t1 表，其中有 "a", "b", "c" 三列，如果要删除列 c ，以下过程描述如何做:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

在数据库中删除了很多数据，但数据库文件没有变小，是Bug吗？

不是。当你从SQLite数据库中删除数据时，未用的磁盘空间将会加入一个内部的“自由列表”中。当你下次插入数据时，这部分空间可以重用。磁盘空间不会丢失，但也不会返还给操作系统。

如果删除了大量数据，而又想缩小数据库文件占用的空间，执行 VACUUM 命令。 VACUUM将会从头重新组织数据库。这将会使用数据库有一个空的“自由链表”，数据库文件也会最小。但要注意的是，VACUUM 的执行会需要一些时间（在SQLite开发时，在Linux上，大约每M字节需要半秒种），并且，执行过程中需要原数据库文件至多两倍的临时磁盘空间。

对于 SQLite 3.1版本，一个 auto-vacumm 模式可以替代 VACUUM 命令。可以使用 auto_vacuum pragma 打开。

SQLITE_SCHEMA error是什么错误？为什么会出现该错误？

当一个准备好的（prepared）SQL语句不再有效或者无法执行时，将返回一个 SQLITE_SCHEMA错误。发生该错误时，SQL语句必须使用 sqlite3_prepare() API来重新编译. 在SQLite 3 中, 一个SQLITE_SCHEMA 错误只会发生在用sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API 执行 SQL时。而不会发生在使用 sqlite3_exec()时。在版本2中不是这样。

准备好的语句失效的最通常原因是：在语句准备好后，数据库的模式又被修改了。另外的原因会发生在：

数据库离线：DETACHed.
数据库被 VACUUMed
一个用户存储过程定义被删除或改变。
一个 collation 序列定义被删除或改变。
认证函数被改变。
在所有情况下，解决方法是重新编译并执行该SQL语句。因为一个已准备好的语句可以由于其它进程改变数据库模式而失效，所有使用sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API 的代码都应准备处理SQLITE_SCHEMA 错误。下面给出一个例子：

    int rc;
    sqlite3_stmt *pStmt;
    char zSql[] = "SELECT .....";

    do {
      /* Compile the statement from SQL. Assume success. */
      sqlite3_prepare(pDb, zSql, -1, &pStmt, 0);

      while( SQLITE_ROW==sqlite3_step(pStmt) ){
        /* Do something with the row of available data */
      }

      /* Finalize the statement. If an SQLITE_SCHEMA error has
      ** occured, then the above call to sqlite3_step() will have
      ** returned SQLITE_ERROR. sqlite3_finalize() will return
      ** SQLITE_SCHEMA. In this case the loop will execute again.
      */
      rc = sqlite3_finalize(pStmt);
    } while( rc==SQLITE_SCHEMA );

如何在字符串中使用单引号('''')？

SQL 标准规定，在字符串中，单引号需要使用逃逸字符，即在一行中使用两个单引号。在这方面 SQL 用起来类似 Pascal 语言。SQLite尊循标准。如：

INSERT INTO xyz VALUES(''''5 O''''''''clock'''');

Sqlite中如何返回本地化当前时间？
在做ClinicOS的时候遇到一个问题，在保存病历登记时间时，我使用了“CURRENT_TIMESTAMP”，但这有个问题，它返回的是UTC Time，这对我们中国人没啥用，一直希望能想办法将它转为localtime。今天刚好有空，所以去查了查Sqlite的Mail List，果然也有人遇到了这个问题，我从一篇名为《translate time comparison statement》（http://www.mail-archive.com/sqlite-users@sqlite.org /msg12350.html）中看到这样的回复：

Mark Wyszomierski wrote:

You may wantWHERE julianday(date(''''now'''')) - julianday(date(arrival_date)) > 7
Mark,

You should still use the ''''localtime'''' modifier on the ''''now'''' value if yourtimestamps are local time since ''''now'''' always returns UTC times.
WHERE julianday(date(''''now'''', ''''localtime'''')) - julianday(date(arrival_date)) > 7

嘿嘿，看来如果想得到一个符合本机区域设置的当前时间，必须用date函数来转换，

但date只函数只返回当前日期，而我需要的是返回当前日期及时间，所以这里把它换成datetime函数，即：

datetime(CURRENT_TIMESTAMP,''''localtime'''')

以下是sqlite
下测试的输出信息：

sqlite
> select CURRENT_TIMESTAMP;

2006-06-18 09:23:36

sqlite
> select datetime(CURRENT_TIMESTAMP,''''localtime'''');

2006-06-18 17:23:44

sqlite
>

SQLITE分页

刚开始的时候没注意语法
后来才发现，原来用SQLite分页是世界上最简单的。
如果我要去11-20的Account表的数据
Select * From Account Limit 9 Offset 10;
以上语句表示从Account表获取数据，跳过10行，取9行

嗯，我觉得这个特性足够让很多的web中型网站使用这个了。

也可以这样写 select * from account limit10,9和上面的的效果一样。
这种写法MySQL也支持。

SQLite适用的范围

SQLite不同于其他大部分的SQL数据库引擎,因为它的首要设计目标就是简单化:

易于管理
易于使用
易于嵌入其他大型程序
易于维护和配置
许多人喜欢SQLite因为它的小巧和快速. 但是这些特性只是它的部分优点, 使用者还会发现SQLite是非常稳定的. 出色的稳定性源于它的简单, 越简单就越不容易出错. 除了上述的简单、小巧和稳定性外, 最重要的在于SQLite力争做到简单化.

简单化在一个数据库引擎中可以说是一个优点,但也可能是个缺点, 主要决定于你想要做什么. 为了达到简单化,SQLite省略了一些人们认为比较有用的特性, 例如高并发性、严格的存取控制、丰富的内置功能、存储过程、复杂的SQL语言特性、 XML以及Java的扩展, 超大的万亿级别的数据测量等等.如果你需要使用上述的这些特性并且不介意它们的复杂性, 那么SQLite也许就不适合你了.SQLite没有打算作为一个企业级的数据库引擎,也并不打算和Oracle或者PostgreSQL竞争.

仅凭经验来说SQLite适用于以下场合: 当你更看中简单的管理、使用和维护数据库, 而不是那些企业级数据库提供的不计其数的复杂功能的时候，使用SQLite是一个比较明智的选择. 事实也证明, 人们在许多情况下已经清楚的认识到简单就是最好的选择.

SQLite最佳试用场合
网站

作为数据库引擎SQLite适用于中小规模流量的网站(也就是说, 99.9%的网站).SQLite可以处理多少网站流量在于网站的数据库有多大的压力. 通常来说,如果一个网站的点击率少于100000次/天的话,SQLite是可以正常运行的. 100000次/天是一个保守的估计, 不是一个准确的上限.事实证明, 即使是10倍的上述流量的情况下SQLite依然可以正常运行.

嵌入式设备和应用软件

因为SQLite数据库几乎不需要管理, 因此对于那些无人值守运行或无人工技术支持的设备或服务,SQLite是一个很好的选择. SQLite能很好的适用于手机, PDA, 机顶盒, 以及其他仪器. 作为一个嵌入式数据库它也能够很好的应用于客户端程序.

应用程序文件格式

SQLite作为桌面应用程序的本地磁盘文件格式取得了巨大成功.例如金融分析工具、CAD 包、档案管理程序等等.一般的数据库打开操作需要调用sqlite3_open()函数，并且标记一个显式本地事务的起始点（BEGIN TRANSACTION）来保证以独占的方式得到文件的内容. 文件保存将执行一个提交（COMMIT）同时标记另一个显式本地事务起始点.这种事务处理的作用就是保证对于应用程序数据文件的更新是原子的、持久的、独立的和一致的.

数据库里可以加入一些临时的触发器,用来把所有的改变记录在一张临时的取消/重做日志表中. 当用户按下取消/重做按钮的时候这些改变将可以被回滚. 应用这项技术实现一个无限级的取消/重做功能只需要编写很少的代码.

替代某些特别的文件格式

许多程序使用fopen(), fread(), 或 fwrite()函数创建和管理一些自定义的文件用来保存数据. 使用SQLite替代这些自定义的文件格式将是一种很好的选择.

内部的或临时的数据库

对于那些有大量的数据需要用不同的方式筛选分类的程序,相对于编写同样功能的代码, 如果你把数据读入一个内存中的SQLite数据库, 然后使用连接查询和ORDER BY子句按一定的顺序和排列提取需要的数据, 通常会更简单和快速. 按照上述的方法使用内嵌的SQLite数据库将会使程序更富有灵活性,因为添加新的列或索引不用重写任何查询语句.

命令行数据集分析工具

有经验的SQL用户可以使用SQLite命令行程序去分析各种混杂的数据集. 原是数据可以从CSV（逗号分隔值文件）文件中导入, 然后被切分产生无数的综合数据报告. 可能得用法包括网站日志分析, 运动统计分析, 编辑规划标准, 分析试验结果.

当然你也可以用企业级的客户端/服务器数据库来做同样的事情. 在这种情况下使用SQLite的好处是:SQLite的部署更为简单并且结果数据库是一个单独的文件, 你可以把它存储在软盘或者优盘或者直接通过email发给同事.

在Demo或测试版的时候作为企业级数据库的替代品

如果你正在编写一个使用企业级数据库引擎的客户端程序,使用一个允许你连接不同SQL数据库引擎的通用型数据库后台将是很有意义的.其更大的意义在于将SQLite数据库引擎静态的连接到客户端程序当中，从而内嵌SQLite作为混合的数据库支持.这样客户端程序就可以使用SQLite数据库文件做独立的测试或者验证.

数据库教学

因为SQLite的安装和使用非常的简单(安装过程几乎忽略不计,只需要拷贝SQLite源代码或sqlite.exe可执行文件到目标主机, 然后直接运行就可以) 所以它非常适合用来讲解SQL语句.同学们可以非常简单的创建他们喜欢的数据库, 然后通过电子邮件发给老师批注或打分.对于那些感兴趣怎样实现一个关系型数据库管理系统（RDBMS）的高层次的学生, 按照模块化设计且拥有很好的注释和文档的SQLite源代码,将为他们打下良好的基础. 这并不是说SQLite就是如何实现其他数据库引擎的精确模型, 但是很适合学生们了解SQLite是如何快速工作的,从而掌握其他数据库系统的设计实现原则.

试验SQL语言的扩展

SQLite简单且模块化的设计使得它可以成为一个用来测试数据库语言特性或新想法的优秀的原型平台.

哪些场合适合使用其他的关系型数据库管理系统（RDBMS）
客户端/服务器程序

如果你有许多的客户端程序要通过网络访问一个共享的数据库,你应当考虑用一个客户端/服务器数据库来替代SQLite.SQLite可以通过网络文件系统工作, 但是因为和大多数网络文件系统都存在延时,因此执行效率不会很高. 此外大多数网络文件系统在实现文件逻辑锁的方面都存在着bug(包括Unix 和windows).如果文件锁没有正常的工作, 就可能出现在同一时间两个或更多的客户端程序更改同一个数据库的同一部分, 从而导致数据库出错.因为这些问题是文件系统执行的时候本质上存在的bug, 因此SQLite没有办法避免它们.

好的经验告诉我们, 应该避免在许多计算机需要通过一个网络文件系统同时访问同一个数据库的情况下使用SQLite.

高流量网站

SQLite通常情况下用作一个网站的后台数据库可以很好的工作. 但是如果你的网站的访问量大到你开始考虑采取分布式的数据库部署, 那么你应当毫不犹豫的考虑用一个企业级的客户端/服务器数据库来替代SQLite.

超大的数据集

当你在SQLite中开始一个事务处理的时候(事务处理会在任何写操作发生之前产生,而不是必须要显示的调用BEGIN...COMMIT), 数据库引擎将不得不分配一小块脏页(文件缓冲页面)来帮助它自己管理回滚操作.每1MB的数据库文件SQLite需要256字节. 对于小型的数据库这些空间不算什么, 但是当数据库增长到数十亿字节的时候,缓冲页面的尺寸就会相当的大了. 如果你需要存储或修改几十GB的数据, 你应该考虑用其他的数据库引擎.

高并发访问

SQLite对于整个数据库文件进行读取/写入锁定.这意味着如果任何进程读取了数据库中的某一部分, 其他所有进程都不能再对该数据库的任何部分进行写入操作. 同样的,如果任何一个进程在对数据库进行写入操作, 其他所有进程都不能再读取该数据库的任何部分. 对于大多数情况这不算是什么问题.在这些情况下每个程序使用数据库的时间都很短暂, 并且不会独占, 这样锁定至多会存在十几毫秒. 但是如果有些程序需要高并发,那么这些程序就需要寻找其他的解决方案了