文档章节

2010年SQLite学习笔记之二(2)

j
 jpch
发布于 2013/12/11 14:21
字数 3417
阅读 80
收藏 5

创建数据库文件: 
   >SQLite3 d:/test.db 
回车
   
就生成了一个test.dbd盘。
   
这样同时也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能够自动找到的目录
   
剩下的就很简单了~~

3SQL语法 
   
由于以前用SQLServer或者ISeries,所以DDL的语法很汗颜
   1)
创建一个单个Primary Keytable
   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 Keytable
 
   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深入------常见问题

如何建立自动增长字段?

简短回答:声明为 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 表是只读的。不能对它使用 UPDATEINSERT  DELETE。 它会被 CREATE TABLECREATE INDEXDROP 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是什么错误?为什么会出现该错误?

当一个准备好的(preparedSQL语句不再有效或者无法执行时, 将返回一个 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。今天刚好有空,所以去查了查SqliteMail List,果然也有人遇到了这个问题,我从一篇名为《translate time comparison statement》(http://www.mail-archive.com/sqlite-users@sqlite.org /msg12350.html)中看到这样的回复:


本文转载自:http://blog.csdn.net/littletigerat/article/details/5314664

j
粉丝 1
博文 82
码字总数 13889
作品 0
东城
私信 提问
一个小时内学习 SQLite 数据库

1. 介绍 SQLite 是一个开源的嵌入式关系数据库,实现自包容、零配置、支持事务的SQL数据库引擎。 其特点是高度便携、使用方便、结构紧凑、高效、可靠。 与其他数据库管理系统不同,SQLite 的...

红薯
2012/05/11
54.3K
59
SQLite学习手册(命令行工具)

工欲善其事,必先利其器。学好SQLite的命令行工具,对于我们学习SQLite本身而言是非常非常有帮助的。最基本的一条就是,它让我们学习SQLite的过程更加轻松愉快。言归正传吧,在SQLite的官方下...

柠檬酷
2015/07/14
171
0
一个小时内学习 SQLite 数据库

1. 介绍 SQLite 是一个开源的嵌入式关系数据库,实现自包容、零配置、支持事务的SQL数据库引擎。 其特点是高度便携、使用方便、结构紧凑、高效、可靠。 与其他数据库管理系统不同,SQLite 的...

曾沙
2012/11/27
1K
0
学习iPhone开发中 sqlite3的使用

由于我主要负责我们小组项目数据库模块的部分所以这几天都一直在研究在iphone中最为常用的一个简单数据库sqlite,自己也搜集很多资料,因此在这里总结一下这几天的学习成果: 1.Sqlite操作简明教...

慎道
2011/03/27
1K
1
SQLite学习手册(临时文件)

一、简介: 尽管SQLite的数据库是由单一文件构成,然而事实上在SQLite运行时却存在着一些隐含的临时文件,这些临时文件是出于不同的目的而存在的,对于开发者而言,它们是透明的,因此在开发...

涩女郎
2015/08/26
36
0

没有更多内容

加载失败,请刷新页面

加载更多

java通过ServerSocket与Socket实现通信

首先说一下ServerSocket与Socket. 1.ServerSocket ServerSocket是用来监听客户端Socket连接的类,如果没有连接会一直处于等待状态. ServetSocket有三个构造方法: (1) ServerSocket(int port);...

Blueeeeeee
今天
6
0
用 Sphinx 搭建博客时,如何自定义插件?

之前有不少同学看过我的个人博客(http://python-online.cn),也根据我写的教程完成了自己个人站点的搭建。 点此:使用 Python 30分钟 教你快速搭建一个博客 为防有的同学不清楚 Sphinx ,这...

王炳明
昨天
5
0
黑客之道-40本书籍助你快速入门黑客技术免费下载

场景 黑客是一个中文词语,皆源自英文hacker,随着灰鸽子的出现,灰鸽子成为了很多假借黑客名义控制他人电脑的黑客技术,于是出现了“骇客”与"黑客"分家。2012年电影频道节目中心出品的电影...

badaoliumang
昨天
15
0
很遗憾,没有一篇文章能讲清楚线程的生命周期!

(手机横屏看源码更方便) 注:java源码分析部分如无特殊说明均基于 java8 版本。 简介 大家都知道线程是有生命周期,但是彤哥可以认真负责地告诉你网上几乎没有一篇文章讲得是完全正确的。 ...

彤哥读源码
昨天
15
0
jquery--DOM操作基础

本文转载于:专业的前端网站➭jquery--DOM操作基础 元素的访问 元素属性操作 获取:attr(name);$("#my").attr("src"); 设置:attr(name,value);$("#myImg").attr("src","images/1.jpg"); ......

前端老手
昨天
7
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部