sqliteSQLite入门笔记

先来一段客套话

  SQLite,是一款轻型的数据库,是遵从ACID的关联式数据库管理连串,它的宏图目的是嵌入式的,而且近日曾经在许多嵌入式产品中动用了它,它占用资源足够的低,在嵌入式设备中,可能只必要几百K的内存就够了。它亦可协理Windows/Linux/Unix等等主流的操作系统,同时能够跟很多程序语言相结合,比如
Tcl、C#、PHP、Java等,还有ODBC接口,同样比起Mysql、PostgreSQL那五款开源世界有名的数据库管理连串来讲,它的处理速度比他们都快。SQLite第四个Alpha版本诞生于2000年3月。
至今已经有12个年头,SQLite也迎来了一个版本 SQLite 3已经昭示。

sqlite 1

它的Logo里头的那条毛,推断即使讲明它够轻的情趣。

1 工具

 

  最原始的工具当然是命令形式的工具,也有图形界面的,FireFox的SQLite
Manager,SQLiteSpy,VS也行,往日找到一个免费的接近还足以的忘了叫什么名字了。找了多少个都不佳用干脆原始一点用命令格局算了,可以到官网里下,有个Shell的老大就是命令行工具了。

 

  输入“.help”就会弹出一大堆内置的吩咐,各样命令的功能如下

命令名

命令说明

.help

列出所有内置命令。

.backup DBNAME FILE

备份指定的数据库到指定的文件,缺省为当前连接的main数据库。

.databases

列出当前连接中所有attached数据库名和文件名。

.dump TABLENAME …

以SQL文本的格式DUMP当前连接的main数据库,如果指定了表名,则只是DUMP和表名匹配的数据表。参数TABLENAME支持LIKE表达式支持的通配符。

.echo ON|OFF

打开或关闭显示输出。

.exit

退出当前程序。

.explain ON|OFF

打开或关闭当前连接的SELECT输出到Human Readable形式。

.header(s) ON|OFF

在显示SELECT结果时,是否显示列的标题。

.import FILE TABLE

导入指定文件的数据到指定表。

.indices TABLENAME

显示所有索引的名字,如果指定表名,则仅仅显示匹配该表名的数据表的索引,参数TABLENAME支持LIKE表达式支持的通配符。

.log FILE|off 

打开或关闭日志功能,FILE可以为标准输出stdout,或标准错误输出stderr。

.mode MODE TABLENAME

设置输出模式,这里最为常用的模式是column模式,使SELECT输出列左对齐显示。其他模式有csv,html,inser,line,list,tabs,tcl

.nullvalue STRING 

使用指定的字符串代替NULL值的显示。

.output FILENAME 

将当前命令的所有输出重定向到指定的文件。

.output stdout 

将当前命令的所有输出重定向到标准输出(屏幕)。

.quit 

退出当前程序。 

.read FILENAME 

执行指定文件内的SQL语句。

.restore DBNAME FILE 

从指定的文件还原数据库,缺省为main数据库,此时也可以指定其它数据库名,被指定的数据库成为当前连接的attached数据库。

.schema TABLENAME

显示数据表的创建语句,如果指定表名,则仅仅显示匹配该表名的数据表创建语句,参数TABLENAME支持LIKE表达式支持的通配符。

.separator STRING

改变输出模式和.import的字段间分隔符。

.show

显示各种设置的当前值。

.tables TABLENAME

列出当前连接中main数据库的所有表名,如果指定表名,则仅仅显示匹配该表名的数据表名称,参数TABLENAME支持LIKE表达式支持的通配符。

.width NUM1 NUM2 …

在MODE为column时,设置各个字段的宽度,注意:该命令的参数顺序表示字段输出的顺序。

.stats ON|OFF

开启或关闭统计表。

.timeout MS

尝试已MS毫秒开启锁了的表

.trace FILE|off

在没个表达式运行时输出它

.vfsname AUX

打印VFS堆栈的名称

.timer ON|OFF

开启或关闭CPU的计时测量

 

假诺在cmd处开启SQLite的话 用以下命令

  sqlite ‘DB file fullname’

万一一贯打开SQLite3.exe的话,要打开某个数据库,则是用添加某个数据到近来一而再的款式

  ATTACH DATABASE ‘DB file fullname’ AS DB name;

那句注意前边有个分行,同样两句的文件名都无法有普通话,否则会并发打不开的谬误。

 

2 数据类型

 

  秉承了读书程序语言的习惯,熟习了条件之后就精通数据类型了。

  SQLite的数据类型实质上只有多样,

    NULL:只有一个值,就是NULL

    INTEGER:无符号整形。

    REAL:浮点数。

    TEXT:字符串。

    BLOB:Blob数据。

  那里要极度说美素佳儿(Friso)(Nutrilon)下,SQLite采取的是动态数据类型,那一点与其他体系的数据库差距,其余类其他数据库是在概念字段时设定了字段的数据类型,将来该字段只好存放设定类型的数额,而SQLite则不然,比如定义了一个INTEGER类型的字段col1,也足以正常地往该字段存放一串字符串:”ABCDEFGI…..”。但是若要考虑到要把库转移到其余数据库中,那照旧中规中矩地填上相应品种的数量好一些。

  只有那三种数据类型给我的首先感觉到是不够用,其实早已够用了。因为SQLite有个亲缘性的定义。当数码插入时,该字段的数码将会优先使用亲缘类型作为该值的仓储形式,除非亲缘类型不般配或不可能转移当前数码到该亲缘类型,那样SQLite才会考虑其余更符合该值的类型存储该值。亲缘类型有弹指间三种

  •          TEXT
  •          NUMBERIC
  •          INTEGER
  •          REAL
  •          NONE

亲缘性类型转换有以下规则

  1. 比方类型字符串中包括”INT”,那么该字段的血肉类型是INTEGER。
  2. 若果类型字符串中包蕴”CHAR”、”CLOB”或”TEXT”,那么该字段的直系类型是TEXT,如VARCHAR。
  3. 如果类型字符串中包罗”BLOB”,那么该字段的情深意重类型是NONE。
  4. 即使类型字符串中包含”REAL”、”FLOA”或”DOUB”,那么该字段的血肉类型是REAL。
  5. 其余意况下,字段的亲情类型为NUMERIC。

各个讲明类型及亲缘类型涉及如下表

表的字段类型或者转换的类型

亲缘类型

使用的规则

INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8

INTEGER

1

CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB

TEXT

2

BLOB
no datatype specified

NONE

3

REAL
DOUBLE
DOUBLE PRECISION
FLOAT

REAL

4

NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME

NUMERIC

5

 

 

除此以外提一下就是自增列,在其余数据库里都有一个自增列,在SQLite里即使要安装一列为自增列时,则须求把它的花色设为INTEGER而且把它当作主键。

 

3 运算符

 

3.1运算符介绍

SQLite援助以下二元运算符,上面则是从最高到低于的先期级排序

||

*    /    %

+    -

<<   >>   &    |

<    <=   >    >=

=    ==   !=   <>   IS   IS NOT   IN   LIKE   GLOB   MATCH   REGEXP

AND   

OR

协助的一元运算符有

-    +    ~    NOT

小心一下,上提到的二元运算符中,||
是用于字符串的总是,不是 +
,假使要延续五个字符串”Hello”和”World”

SELECT “Hello”+”World” --结果是0.0
SELECT “Hello”||”World” --结果是”HelloWorld”

GLOB运算符与LIKE类似,不过GLOB是分别轻重缓急写的

MATCH运算符用在全文检索中。例如那两句

SELECT title, body FROM pages WHERE pages MATCH 'world'; 
SELECT title, body FROM pages WHERE title MATCH 'world';

那两句,前一个 MATCH 左侧写了表名,后一个写的是列名。后一个仅搜索 title
列,前一个是寻找全体列(docid 列以外)。

MATCH 右边的表明式协助模糊查询、匡助指定列查询、协理 AND/OR/NEAR/NOT
等运算,例如

SELECT title, body FROM pages WHERE pages MATCH 'hel*'; 
SELECT title, body FROM pages WHERE pages MATCH 'title:hello'; 
SELECT title, body FROM pages WHERE pages MATCH 'hello AND world'; 
SELECT title, body FROM pages WHERE pages MATCH '(hello NEAR world) OR (program AND language)';

但要注意只好出现两遍 MATCH 判断,WHERE title MATCH ‘hello’ AND body
MATCH ‘world’ 是特其余,可以改作 WHERE pages MATCH ‘title:hello AND
body:world’。

REGEXP运算符是用户函数regexp()的一个与众不一致的象征符号。缺省气象下regexp()函数不被定义,所以选取REGEXP运算符会报错。当运行时存在用户定义的“regexp”函数的概念,则调用该函数以完结REGEXP运算符成效。

3.2 相比较运算符的条条框框

上面提到一些相比较运算符,它有以下规则

  •         存储格局为NULL的数值低于其他存储类型的值。
  •         
    存储格局为INTEGER和REAL的数值低于TEXT或BLOB类型的值,固然同为INTEGER或REAL,则根据数值规则举行比较。
  •         
    存储格局为TEXT的数值小于BLOB类型的值,如若同为TEXT,则基于文本规则(ASCII值)进行相比较。
  •         
    若是是八个BLOB类型的数值进行比较,其结果为C运行时函数memcmp()的结果。

 

4 表达式

 

4.1 条件表明式

 

该表达式的语法规则如下:
    1). CASE x WHEN w1 THEN r1
WHEN w2 THEN r2 ELSE r3 END
    2). CASE WHEN x=w1 THEN r1
WHEN x=w2 THEN r2 ELSE r3 END

 

 

4.2 更换表明式**

该表明式的语法规则如下:
    CAST(expr AS target_type)

目的项目则是TEXT,REAL,INTEGER,NUMBERIC那三种亲缘类型的内部之一,规则如下

目标类型

转换规则描述

TEXT

如果转换INTEGER或REAL类型的值到TEXT类型直接转换即可,就像C/C++接口函数sqlite3_snprintf所完成的工作。

REAL

如果转换TEXT类型的值到REAL类型,在该文本的最前部,将可以转换为实数的文本转换为相应的实数,其余部分忽略。其中该文本值的前导零亦将被全部忽略。如果该文本值没有任何字符可以转换为实数,CAST表达式的转换结果为0.0。

INTEGER

如果转换TEXT类型的值到INTEGER类型,在该文本的最前部,将可以转换为整数的文本转换为相应的整数,其余部分忽略。其中该文本值的前导零亦将被全部忽略。如果该文本值没有任何字符可以转换为整数,CAST表达式的转换结果为0。
如果转换将一个实数值转换为INTEGER类型,则直接截断实数小数部分。如果实数过大,则返回最大的负整数:-9223372036854775808。

NUMERIC  

如果转换文本值到NUMERIC类型,则先将该值强制转换为REAL类型,只有在将REAL转换为INTEGER不会导致数据信息丢失以及完全可逆的情况下,SQLite才会进一步将其转换为INTEGER类型。

固然expr是NULL那么结果也是NULL

 

 5 函数

 

5.1聚合函数

   SQLite中匡助的聚合函数在很多其余数据库中也如出一辙接济,那里还索要更进一步求证的是,对于有着聚合函数而言,distinct重在字可以视作函数参数字段的内置属性,以便在展开计算时疏忽到持有重复的字段值,如count(distinct
x)。

函数

说明

avg(x)

该函数返回在同一组内参数字段的平均值。对于不能转换为数字值的String和BLOB类型的字段值,如’HELLO’,SQLite会将其视为0。avg函数的结果总是浮点型,唯一的例外是所有的字段值均为NULL,那样该函数的结果也为NULL。

count(x|*)  

count(x)函数返回在同一组内,x字段中值不等于NULL的行数。count(*)函数返回在同一组内的数据行数。

group_concat(x[,y])

该函数返回一个字符串,该字符串将会连接所有非NULL的x值。该函数的y参数将作为每个x值之间的分隔符,如果在调用时忽略该参数,在连接时将使用缺省分隔符","。再有就是各个字符串之间的连接顺序是不确定的。  

max(x)

该函数返回同一组内的x字段的最大值,如果该字段的所有值均为NULL,该函数也返回NULL。

min(x)

该函数返回同一组内的x字段的最小值,如果该字段的所有值均为NULL,该函数也返回NULL。

sum(x)

该函数返回同一组内的x字段值的总和,如果字段值均为NULL,该函数也返回NULL。如果所有的x字段值均为整型或者NULL,该函数返回整型值,否则就返回浮点型数值。最后需要指出的是,如果所有的数据值均为整型,一旦结果超过上限时将会抛出"integer overflow"的异常。

total(x)

该函数不属于标准SQL,其功能和sum基本相同,只是计算结果比sum更为合理。比如当所有字段值均为NULL时,和sum不同的是,该函数返回0.0。再有就是该函数始终返回浮点型数值。该函数始终都不会抛出异常。

 

5.2 内置函数

除此以外SQLite中还有一对内置函数,如下表

 

函数

说明

abs(X)

该函数返回数值参数X的绝对值,如果X为NULL,则返回NULL,如果X为不能转换成数值的字符串,则返回0,如果X值超出Integer的上限,则抛出"Integer Overflow"的异常。

changes()

该函数返回最近执行的INSERT、UPDATE和DELETE语句所影响的数据行数。我们也可以通过执行C/C++函数sqlite3_changes()得到相同的结果。

coalesce(X,Y,…)  

返回函数参数中第一个非NULL的参数,如果参数都是NULL,则返回NULL。该函数至少2个参数。

ifnull(X,Y)

该函数等同于两个参数的coalesce()函数,即返回第一个不为NULL的函数参数,如果两个均为NULL,则返回NULL。

length(X)  

如果参数X为字符串,则返回字符的数量,如果为数值,则返回该参数的字符串表示形式的长度,如果为NULL,则返回NULL。

lower(X)

返回函数参数X的小写形式,缺省情况下,该函数只能应用于ASCII字符。

ltrim(X[,Y])  

如果没有可选参数Y,该函数将移除参数X左侧的所有空格符。如果有参数Y,则移除X左侧的任意在Y中出现的字符。最后返回移除后的字符串。

max(X,Y,…)

返回函数参数中的最大值,如果有任何一个参数为NULL,则返回NULL。

min(X,Y,…)

返回函数参数中的最小值,如果有任何一个参数为NULL,则返回NULL。

nullif(X,Y)

如果函数参数相同,返回NULL,否则返回第一个参数。  

random()  

返回整型的伪随机数。

replace(X,Y,Z)  

将字符串类型的函数参数X中所有子字符串Y替换为字符串Z,最后返回替换后的字符串,源字符串X保持不变。

round(X[,Y])

返回数值参数X被四舍五入到Y刻度的值,如果参数Y不存在,缺省参数值为0。  

rtrim(X[,Y])  

如果没有可选参数Y,该函数将移除参数X右侧的所有空格符。如果有参数Y,则移除X右侧的任意在Y中出现的字符。最后返回移除后的字符串。

substr(X,Y[,Z])

返回函数参数X的子字符串,从第Y位开始(X中的第一个字符位置为1)截取Z长度的字符,如果忽略Z参数,则取第Y个字符后面的所有字符。如果Z的值为负数,则从第Y位开始,向左截取abs(Z)个字符。如果Y值为负数,则从X字符串的尾部开始计数到第abs(Y)的位置开始。 

total_changes()

该函数返回自从该连接被打开时起,INSERT、UPDATE和DELETE语句总共影响的行数。我们也可以通过C/C++接口函数sqlite3_total_changes()得到相同的结果。

trim(x[,y])

如果没有可选参数Y,该函数将移除参数X两侧的所有空格符。如果有参数Y,则移除X两侧的任意在Y中出现的字符。最后返回移除后的字符串。 

upper(X)

返回函数参数X的大写形式,缺省情况下,该函数只能应用于ASCII字符。

typeof(X)

返回函数参数数据类型的字符串表示形式,如"Integer、text、real、null"等。

 

 

5.3 日期和岁月函数

 

SQLite接济以下多种日期时间函数

 

date(timestring, modifier, modifier,
…)

 

time(timestring, modifier, modifier,
…)

 

datetime(timestring, modifier,
modifier, …)

 

julianday(timestring, modifier,
modifier, …)

 

strftime(format, timestring, modifier,
modifier, …)

 

以上二种函数中timestring的格式如下所示

 

  1. YYYY-MM-DD
  2. YYYY-MM-DD HH:MM
  3. YYYY-MM-DD HH:MM:SS
  4. YYYY-MM-DD HH:MM:SS.SSS
  5. YYYY-MM-DDTHH:MM
  6. YYYY-MM-DDTHH:MM:SS
  7. YYYY-MM-DDTHH:MM:SS.SSS
  8. HH:MM
  9. HH:MM:SS
  10. HH:MM:SS.SSS
  11. now
  12. DDDDDDDDDD

 

格式5~7中,那个加粗的“T”是根据ISO-8061标准分割日期和时间的分隔符,格式7~8中唯有时间部分,日期部分全都默许设为2000-01-01,格式11的now是指当前UTC时间,格式12是用以julianday的。

 

Modifier修改符如下所示

 

  1. NNN days
  2. NNN hours
  3. NNN minutes
  4. NNN.NNNN seconds
  5. NNN months
  6. NNN years
  7. start of month
  8. start of year
  9. start of day
  10. weekday N
  11. unixepoch
  12. localtime
  13. utc

 

修改符unixepoc只用于DDDDDDDDDD格式的timestring前面,它会使得DDDDDDDDDD解释成非一般性的儒略日,而是一个光阴戳——从1970年始的秒数。

 

修改符localtime是一旦它左边的时日是UTC的情状下把它调整成当地时间。

 

修改符utc则与localtime相反,它是把左手的年华当左当地时间并把它转换成UTC。

 

上面的假名”N”是指加减的日子或时刻值,正则加,负则减。修改符的依次极为主要,SQLite将会根据从左到右的次第依次执行修改符。

 

 

 

strftime函数的格式消息

 

格式

说明

%d

day of month: 00

%f

fractional seconds: SS.SSS

%H

hour: 00-24

%j

day of year: 001-366

%J

Julian day number

%m

month: 01-12

%M

minute: 00-59

%s

seconds since 1970-01-01

%S

seconds: 00-59

%w

day of week 0-6 with Sunday==0

%W

week of year: 00-53

%Y

year: 0000-9999

%%

%

 

其它八个函数等价的strftime如下

 

date(…)   strftime(‘%Y-%m-%d’, …) 

 

time(…)   strftime(‘%H:%M:%S’, …) 

 

datetime(…)   strftime(‘%Y-%m-%d %H:%M:%S’, …) 

 

julianday(…)   strftime(‘%J’, …) 

 

 

 

  date函数的缺省格式为:”YYYY-MM-DD”,time函数的缺省格式为:”HH:MM:SS”,datetime函数的缺省格式为:”YYYY-MM-DD
HH:MM:SS”。Julianday函数的缺省格式为公元前4714年5月24日格林威治正午起经过的运气。

 

 

6 SQL

 

有关SQLite的SQL语句,提一下就是它的分页查询,与MySQL的同一方便,用一个Limit

SELECT * FROM tableName Limit number1,number2

 

number1是开局的rowid,number2是自number1始读的行数。

 

其余还列出两条SQL语句,分别是查库里的表名和表里头字段的新闻

select name from sqlite_master where type='table';
PRAGMA table_info([tablename])

 

7 连接到.NET

 

 

最终到介绍.NET的主次连接SQLite了。

要引用的dll文件能够从官网上下载,它的选择方法跟连SQL
Server的一样,也是Connection,Command,DataReader那些类。连接字符串最简易的形式是这么

Data Source=db file fullname;

 

有关连接字符串的其他参数,如下表所示:

 

 

 

Parameter

Values

Required

Default

Data Source

This may be a file name, the string ":memory:", or any supported URI (starting with SQLite 3.7.7).

Y

 

Version

3

N

3

UseUTF16Encoding

True
False

N

False

DateTimeFormat

Ticks – Use the value of DateTime.Ticks.
ISO8601 – Use the ISO-8601 format. Uses the "yyyy-MM-dd HH:mm:ss.FFFFFFFK" format for UTC DateTime values and "yyyy-MM-dd HH:mm:ss.FFFFFFF" format for local DateTime values).
JulianDay – The interval of time in days and fractions of a day since January 1, 4713 BC.
UnixEpoch – The whole number of seconds since the Unix epoch (January 1, 1970).
InvariantCulture – Any culture-independent string value that the .NET Framework can interpret as a valid DateTime.
CurrentCulture – Any string value that the .NET Framework can interpret as a valid DateTime using the current culture.

N

ISO8601

DateTimeKind

Unspecified – Not specified as either UTC or local time.
Utc – The time represented is UTC.
Local – The time represented is local time.

N

Unspecified

BaseSchemaName

Some base data classes in the framework (e.g. those that build SQL queries dynamically) assume that an ADO.NET provider cannot support an alternate catalog (i.e. database) without supporting alternate schemas as well; however, SQLite does not fit into this model. Therefore, this value is used as a placeholder and removed prior to preparing any SQL statements that may contain it.

N

sqlite_default_schema

BinaryGUID

True – Store GUID columns in binary form
False – Store GUID columns as text

N

True

Cache Size

{size in bytes}

N

2000

Synchronous

Normal – Normal file flushing behavior
Full – Full flushing after all writes
Off – Underlying OS flushes I/O’s

N

Full

Page Size

{size in bytes}

N

1024

Password

{password} – Using this parameter requires that the CryptoAPI based codec be enabled at compile-time for both the native interop assembly and the core managed assemblies; otherwise, using this parameter may result in an exception being thrown when attempting to open the connection.

N

 

Enlist

Y – Automatically enlist in distributed transactions
N – No automatic enlistment

N

Y

Pooling

True – Use connection pooling
False – Do not use connection pooling

N

False

FailIfMissing

True – Don’t create the database if it does not exist, throw an error instead
False – Automatically create the database if it does not exist

N

False

Max Page Count

{size in pages} – Limits the maximum number of pages (limits the size) of the database

N

0

Legacy Format

True – Use the more compatible legacy 3.x database format
False – Use the newer 3.3x database format which compresses numbers more effectively

N

False

Default Timeout

{time in seconds}
The default command timeout

N

30

Journal Mode

Delete – Delete the journal file after a commit
Persist – Zero out and leave the journal file on disk after a commit
Off – Disable the rollback journal entirely

N

Delete

Read Only

True – Open the database for read only access
False – Open the database for normal read/write access

N

False

Max Pool Size

The maximum number of connections for the given connection string that can be in the connection pool

N

100

Default IsolationLevel

The default transaciton isolation level

N

Serializable

Foreign Keys

Enable foreign key constraints

N

False

Flags

Extra behavioral flags for the connection. See the SQLiteConnectionFlags enumeration for possible values.

N

Default

SetDefaults

True – Apply the default connection settings to the opened database.
False – Skip applying the default connection settings to the opened database.

N

True

ToFullPath

True – Attempt to expand the data source file name to a fully qualified path before opening.
False – Skip attempting to expand the data source file name to a fully qualified path before opening.

N

True

 

 

 

骨子里那篇有局地摘自
Stephen_Liu 的SQLite学习手册,望其有怪莫怪。文中要是有怎么样说错的说漏的还请各位大侠批评指正。最终用一个总是完成那篇博文SQLiteHelper

网站地图xml地图