SQLite入门笔记

优先来同样截外套话

  SQLite,是如出一辙暂缓轻型的数据库,是遵守ACID的关联式数据库管理网,它的计划性目标是嵌入式的,而且目前曾于许多嵌入式产品被行使了其,它占用资源异常的亚,在嵌入式设备中,可能仅仅需要几百K的内存就足够了。它能支持Windows/Linux/Unix等等主流的操作系统,同时会和多程序语言相结合,比如
Tcl、C#、PHP、Java等,还有ODBC接口,同样于由Mysql、PostgreSQL这片暂缓开源世界闻名的数据库管理网来讲,它的处理速度比他们还快。SQLite第一个Alpha版本诞生于2000年5月。
至今已经发生12单年头,SQLite也迎来了一个本子 SQLite 3已经披露。

图片 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数据。

  这里而格外说明一下,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年11月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地图