Sqlite全面学习（1）_SQL

Sqlite全面学习（一）

PS一句：最终还是选择CSDN来整理发表这几年的知识点，该文章平行迁移到CSDN。因为CSDN也支持MarkDown语法了，牛逼啊！

【工匠若水 http://blog.csdn.net/yanbober】

官网 SQLite是一款轻型的数据库，是关系型数据库（RDBMS）管理系统，它包含在一个相对小的C库中。目前在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix/Android/IOS等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。

Sqlite命令分类：

（DDL）数据定义语言：

CMD	Description
CREATE	创建一个新的表，一个表的视图，或者数据库中的其他对象。
ALTER	修改数据库中的某个已有的数据库对象，比如一个表。
DROP	删除整个表，或者表的视图，或者数据库中的其他对象。

（DML）数据操作语言：

CMD	Description
INSERT	创建一条记录。
UPDATE	修改记录。
DELETE	删除记录。

（DQL）数据查询语言：

CMD	Description
SELECT	从一个或多个表中检索某些记录。

Sqlite点命令

先看一张Windows下的截图：
这里写图片描述

这是在Windows的cmd下运行sqlite3命令（如何安装和配置烦请自行google | baidu），然后依据提示运行.help的打印（只截图一部分）。

可以发现sqlite的help列出来了所有sqlite支持的点命令，也可以发现，点命令不需要已”;”结尾。

我们对上面的.help命令进行翻译大致如下：

CMD	Description
`.backup ?DB? FILE`	备份DB数据库（默认是”main”）到FILE文件。
`.bail ON/OFF`	发生错误后停止。默认为OFF。
`.databases`	列出附加数据库的名称和文件。
`.dump ?TABLE?`	以SQL文本格式转储数据库。如果指定了TABLE表，则只转储匹配LIKE模式的TABLE表。
`.echo ON/OFF`	开启或关闭echo命令。
`.exit`	退出SQLite提示符。
`.explain ON/OFF`	开启或关闭适合于EXPLAIN的输出模式。如果没有带参数，则为EXPLAIN on，及开启EXPLAIN。
`.header(s) ON/OFF`	开启或关闭头部显示。
`.help`	显示消息。
`.import FILE TABLE`	导入来自FILE文件的数据到TABLE表中。
`.indices ?TABLE?`	显示所有索引的名称。如果指定了TABLE表，则只显示匹配LIKE模式的TABLE表的索引。
`.load FILE ?ENTRY?`	加载一个扩展库。
`.log FILE/off`	开启或关闭日志。FILE文件可以是stderr（标准错误）/stdout（标准输出）。
`.mode MODE`	设置输出模式，MODE可以是下列之一：csv 逗号分隔的值；column 左对齐的列；html HTML的`<table>`代码；insert TABLE表的SQL插入（insert）语句；line 每行一个值；list 由 .separator字符串分隔的值；tabs 由Tab分隔的值；tcl TCL列表元素。
`.nullvalue STRING`	在NULL值的地方输出STRING字符串。
`.output FILENAME`	发送输出到FILENAME文件。
`.output stdout`	发送输出到屏幕。
`.print STRING...`	逐字地输出STRING字符串。
`.prompt MAIN CONTINUE`	替换标准提示符。
`.quit`	退出SQLite提示符。
`.read FILENAME`	执行FILENAME文件中的SQL。
`.schema ?TABLE?`	显示CREATE语句。如果指定了TABLE表，则只显示匹配LIKE模式的TABLE表。
`.separator STRING`	改变输出模式和.import所使用的分隔符。
`.show`	显示各种设置的当前值。
`.stats ON/OFF`	开启或关闭统计。
`.tables ?PATTERN?`	列出匹配LIKE模式的表的名称。
`.timeout MS`	尝试打开锁定的表MS微秒。
`.width NUM NUM`	为”column”模式设置列宽度。
`.timer ON/OFF`	开启或关闭CPU定时器测量。

Sqlite数据库的sqlite_master表

主表中保存数据库表的关键信息，并把它命名为sqlite_master。如要查看表概要，可如下操作：

sqlite>.schema sqlite_master

Sqlite语法规则

SQLite是遵循一套独特的称为语法的规则和准则。

SQLite是不区分大小写的，但也有一些命令是大小写敏感的，比如GLOB和glob在SQLite的语句中有不同的含义。

SQLite 注释是附加的注释，可以在 SQLite 代码中添加注释以增加其可读性，他们可以出现在任何空白处，包括在表达式内和其他 SQL 语句的中间，但它们不能嵌套。

SQL注释以两个连续的”-“字符开始，并扩展至下一个换行符或直到输入结束，以先到者为准。也可以以”/*"开始，并扩展至下一个 “*/” 字符对或直到输入结束，以先到者为准。SQLite的注释可以跨越多行。

SQLite语句以任何关键字开始，以”;”结束。

Sqlite数据类型

SQLite数据类型是一个用来指定任何对象的数据类型的属性。SQLite 中的每一列，每个变量和表达式都有相关的数据类型。您可以在创建表的同时使用这些数据类型。SQLite使用一个更普遍的动态类型系统。在SQLite中，值的数据类型与值本身是相关的，而不是与它的容器相关。

存储类

SQLite有5个原始的数据类型，被称为存储类。存储类这个词表明了一个值在磁盘上存储的格式，其实就是类型或数据类型的同义词。如下即是存储类：

存储类	Description
NULL	值是一个NULL值。
INTEGER	值是一个带符号的整数，根据值的大小存储在1、2、3、4、6 或8字节中。
REAL	值是一个浮点值，存储为8字节的IEEE浮点数字。
TEXT	值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE或UTF-16LE）存储。
BLOB	值是一个blob数据，完全根据它的输入存储。

SQLite通过值的表示法来判断其类型，下面就是SQLite的推理方法：

SQL语句中用单引号或双引号括起来的文字被指派为TEXT。
如果文字是未用引号括起来的数据，并且没有小数点和指数，被指派为INTEGER。
如果文字是未用引号括起来的数据，并且带有小数点或指数，被指派为REAL。
用NULL说明的值被指派为NULL存储类。
如果一个值的格式为X’ABCD’，其中ABCD为16进制数字，则该值被指派为BLOB。X前缀大小写皆可。

如下就是验证结果：
这里写图片描述

SQLite单独的一个字段可能包含不同存储类的值。

如下就是验证结果：
这里写图片描述

喜欢钻牛角尖的这时候指定开始BB了，temp这一列数据类型不同，咋样比较大小？咋样排序等等？

经过查阅资料发现，具有不同存储类的值可以存储在同一个字段中。可以被排序，因为这些值可以相互比较。有完善定义的规则来做这件事。不同存储类的值可以通过它们各自类的“类值”进行排序，定义如下：

NULL存储类具有最低的类值。一个具有NULL存储类的值比所有其它值都小(包括其它具有NULL存储类的值)。在NULL值之间，没有特别的可排序值。
INTEGER或REAL存储类值高于NULL，它们的类值相等。INTEGER值和REAL值通过其数值进行比较。
TEXT存储类的值比INTEGER和REAL高。数值永远比字符串的值低。当两个TEXT值进行比较时，其值大小由“排序法”决定。
BLOB存储类具有最高的类值。具有BLOB类的值大于其它所有类的值。BLOB值之间在比较时使用C函数memcmp()。

所以，当SQLite对一个字段进行排序时，首先按存储类排序，然后再进行类内的排序 (NULL类内部各值不必排序) 。

弱类型(manifest typing)

首先有如下SQL语句：

CREATE TABLE table_yanbo( x integer, y text, z real );INSERT INTO table_yanbo VALUES ('1', '1', '1');

这里的x、y和z这3个字段中存储的是INTEGER, TEXT和REAL类型。

再看下面例子：

CREATE TABLE table_yanbo(x, y, z);INSERT INTO table_yanbo VALUES ('1', '1', '1');

这里的x、y和z这3个字段中存储的是TEXT、TEXT和TEXT类型。

再看下面例子：

CREATE TABLE table_yanbo(x, y, z);INSERT INTO table_yanbo VALUES (1, 1.0, x'10');

这里的x、y和z这3个字段中存储的是INTEGER、REAL和BLOB类型。

通过上面几种写法你会发现，可以为SQLite的字段定义类型，但这不是必须的，你可以尽管违反类型定义。这是因为在任何情况下，SQLite都可以接受一个值并推断它的类型。

总之，SQLite的弱类型可表示为：

字段可以有类型。
类型可以通过值来推断。

类型亲和性介绍这两个规定如何相互关联。所谓类型亲和性就是在强类型(strict typing)和动态类型(dynamic typing)之间的平衡艺术。

类型亲和性(Type Affinity)

在SQLite中，字段没有类型或域。当给一个字段声明了类型，该字段实际上仅仅具有了该类型的亲和性。声明类型和类型亲和性是两回事。类型亲和性预定SQLite用什么存储类在字段中存储值。在存储一个给定的值时到底SQLite会在该字段中用什么存储类决定于值的存储类和字段亲和性的结合。

任何列可以存储任何类型的数据，但列的首选存储类是它的affinity。在SQLite3数据库中，每个表的列分配为以下类型的affinity之一：

Affinity	Description
TEXT	该列使用存储类NULL、TEXT或BLOB存储所有数据。
NUMERIC	该列可以包含使用所有五个存储类的值。
INTEGER	与带有NUMERIC affinity的列相同，在CAST表达式中带有异常。
REAL	与带有NUMERIC affinity的列相似，不同的是，它会强制把整数值转换为浮点表示。
NONE	带有affinity NONE的列，不会优先使用哪个存储类，也不会尝试把数据从一个存储类强制转换为另一个存储类。

下表列出了当创建SQLite3表时可使用的各种数据类型名称，同时也显示了相应的应用Affinity：

数据类型	Affinity
INT、NTEGER、TINYINT、SMALLINT、MEDIUMINT、BIGINT、UNSIGNED BIG INT、INT2、INT8	INTEGER
CHARACTER(20)、VARCHAR(255)、VARYING CHARACTER(255)、NCHAR(55)、NATIVE CHARACTER(70)、NVARCHAR(100)、TEXT、CLOB	TEXT
BLOB、no datatype specified	NONE
REAL、DOUBLE、DOUBLE PRECISION、FLOAT	REAL
NUMERIC、DECIMAL(10,5)、BOOLEAN、DATE、DATETIME	NUMERIC

Boolean数据类型

SQLite没有单独的Boolean存储类，布尔值被存储为整数 0（false）和 1（true）。

Date与Time数据类型

SQLite没有一个单独的用于存储日期和/或时间的存储类，但SQLite 能够把日期和时间存储为TEXT、REAL或 INTEGER值。您可以以任何上述格式来存储日期和时间，并且可以使用内置的日期和时间函数来自由转换不同格式。

存储类	日期格式
TEXT	格式为”YYYY-MM-DD HH:MM:SS.SSS”的日期。
REAL	从公元前4714年11月24日格林尼治时间的正午开始算起的天数。
INTEGER	从1970-01-01 00:00:00 UTC算起的秒数。

字段类型和亲和性

首先，每个字段都具有一种亲和性。共有五种亲和性：NUMERIC、INTEGER、REAL、TEXT和NONE。一个字段的亲和性由它预声明的类型决定。所以，当你为字段声明了类型，从根本上说是为字段指定了亲和性。SQLite按下面的规则为字段指派亲和性：

默认的，一个字段默认的亲和性是NUMERIC。如果一个字段不是INTEGER、TEXT、REAL或NONE的，那它自动地被指派为NUMERIC亲和性。
如果为字段声明的类型中包含了’INT’(无论大小写)，该字段被指派为INTEGER亲和性。
如果为字段声明的类型中包含了’CHAR’、’CLOB’或’TEXT’(无论大小写)，该字段被指派为TEXT亲和性。如’VARCHAR’包含了’CHAR’，所以被指派为TEXT亲和性。
如果为字段声明的类型中包含了’BLOB’(无论大小写)，或者没有为该字段声明类型，该字段被指派为NONE亲和性。

注意：如果没有为字段声明类型，该字段的亲和性为NONE，在这种情况下，所有的值都将以它们本身的(或从它们的表示法中推断的)存储类存储。如果你暂时还不确定要往一个字段里放什么内容，或准备将来修改，用NONE亲和性是一个好的选择。但SQLite默认的亲和性是NUMERIC。例如，如果为一定字段声明了类型JUJYFRUIT，该字段的亲和性不是NONE，因为SQLite不认识这种类型，会给它指派默认的NUMERIC亲和性。所以，与其用一个不认识的类型最终得到NUMERIC亲和性，还不如不为它指定类型，从而使它得到NONE亲和性。

亲和性和存储

亲和性对值如何存储到字段有影响，规则如下：

一个NUMERIC字段可能包括所有5种存储类。一个NUMERIC字段具有数字存储类的偏好(INTEGER和REAL)。当一个TEXT值被插入到一个NUMERIC字段，将会试图将其转化为INTEGER存储类；如果转化失败，将会试图将其转化为REAL存储类；如果还是失败，将会用TEXT存储类来存储。
一个INTEGER字段的处理很像NUMERIC字段。一个INTEGER字段会将REAL值按REAL存储类存储。也就是说，如果这个REAL值没有小数部分，就会被转化为INTEGER存储类。INTEGER字段将会试着将TEXT值按REAL存储；如果转化失败，将会试图将其转化为INTEGER存储类；如果还是失败，将会用TEXT存储类来存储。
一个TEXT字段将会把所有的INTEGER或REAL值转化为TEXT。
一个NONE字段不试图做任何类型转化。所有值按它们本身的存储类存储。
没有字段试图向NULL或BLOB值转化——如无论用什么亲和性。NULL和BLOB值永远都按本来的方式存储在所有字段。

这些规则初看起来比较复杂，但总的设计目标很简单，如果你需要，SQLite会尽量模仿其它的关系型数据库。也就是说，如果你将SQLite看成是一个传统数据库，类型亲和性将会按你的期望来存储值。如果你声明了一个INTEGER字段，并向里面放一个整数，就会按整数来存储。如果你声明了一个具有TEXT, CHAR或VARCHAR类型的字段并向里放一个整数，整数将会转化为TEXT。可是，如果你不遵守这些规定，SQLite也会找到办法来存储你的值。

如下例子展示了亲和性是如何工作的：
这里写图片描述

存储类和类型转换

关于存储类，需要关注的另一件事是存储类有时会影响到值如何进行比较。特别是SQLite有时在进行比较之前，会将值在数字存储类(INTEGER和REAL)和TEXT之间进行转换。为进行二进制的比较，遵循如下规则：

当一个字段值与一个表达式的结果进行比较，字段的亲和性会在比较之前应用于表达式的结果。
当两个字段值进行比较，如果一个字段拥有INTEGER或NUMERIC亲和性而另一个没有，NUMERIC亲和性会应用于非NUMERIC字段的TEXT值。
当两个表达式进行比较，SQLite不做任何转换。如果两个表达式有相似的存储类，则直接按它们的值进行比较；否则按类值进行比较。

总结

这里主要介绍了Sqlite的一些基本概念和数据类型的特性。关于Sqlite其他内容接下来文章继续介绍。

【工匠若水 http://blog.csdn.net/yanbober】