本章节讲述Android Sqlite数据库数据类型知识

   一、SQLite 数据类型是一个用来指定任何对象的数据类型的属性。SQLite 中的每一列，每个变量和表达式都有相关的数据类型。

您可以在创建表的同时使用这些数据类型。SQLite 使用一个更普遍的动态类型系统。在 SQLite 中，值的数据类型与值本身是相关的，而不是与它的容器相关。

 二、SQLite 存储类

每个存储在 SQLite 数据库中的值都具有以下存储类之一：

 三、SQLite 亲和(Affinity)类型

SQLite支持列的亲和类型概念。任何列仍然可以存储任何类型的数据，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式。SQLite目前的版本支持以下五种亲缘类型：

四、SQLite 亲和类型(Affinity)及类型名称

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

五、Boolean 数据类型

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

六、Date 与 Time 数据类型

SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。

 七、但实际上，sqlite3也接受如下的数据类型：

 smallint 16 位元的整数。

 interger 32 位元的整数。

 decimal(p,s) p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。

 float  32位元的实数。

 double  64位元的实数。

 char(n)  n 长度的字串，n不能超过 254。

 varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。

 graphic(n) 和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。

 vargraphic(n) 可变长度且其最大长度为 n 的双字元字串，n不能超过 2000

 date  包含了 年份、月份、日期。

 time  包含了 小时、分钟、秒。

 timestamp 包含了 年、月、日、时、分、秒、千分之一秒。

八、创建表例子

CREATE TABLE mytable(    

column1 VARCHAR(10),    

column2 NVARCHAR(15),   

column3 TEXT,    

column4 INTEGER,   

column5 FLOAT,   

column6 BOOLEAN,    

column7 CLOB,    

column8 BLOB,    

column9 TIMESTAMP,   

column10 NUMERIC(10,5),    

column11 VARYING CHARACTER (24),    

column12 NATIONAL VARYING CHARACTER(16)   

);

九、char、varchar、text和nchar、nvarchar、ntext的区别

1、CHAR。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。

2、VARCHAR。存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么“+1”呢？这一个字节用于保存实际使用了多大的长度。从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。

3、TEXT。text存储可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符。

4、NCHAR、NVARCHAR、NTEXT。这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来，nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。  

所以一般来说，如果含有中文字符，用nchar/nvarchar，如果纯英文和数字，用char/varchar。

十、sqlite3支持的函数

1.日期函数

datetime() ： 产生日期和时间

date()： 产生日期

time()：产生时间

strftime()：对以上3个函数产生的日期和时间进行格式化

 用法实例：

1、SELECT date('2011-9-9','+1 day','+1 year'); 结果是 2010-09-10

2、SELECT datetime('now'); 当前日期和时间

3、SELECT datetime('now', 'start of month'); 本月的第一天零点，也可以设置年和日的第一天

4、SELECT datetime('now','+1 hour','-12 minute'); 当前时间加48分钟

strftime()函数可以将YYYY-MM-DD HH:MM:SS格式的日期字符串转换为其它形式的字符串

%d：天数，01-31

%f ：小数形式的秒，SS.SSS

%H：小时

%j  ：某一天是该年的第几天，001-366

%m：月份，00-12

%M：分钟，00-59

%s：从1970到现在的秒数

%S：秒，00-59

%w：星期，0-6，0是星期天

%W：某天是该年的第几周，01-53

%Y：年，YYYY

%% 百分号

应用举例：

SELECT strftime('%Y.%m.%d %H:%M:%S','now','localtime');

2、【算术函数】

abs(X)：返回绝对值

max(X,Y[,...])：返回最大值

min(X,Y,[,...])：返回最小值

random(*)：返回随机数

round(X[,Y])： 四舍五入

3、【字符串处理函数】

length(x) ：返回字符串字符个数

lower(x) ：大写转小写

upper(x)：小写转大写

substr(x,y,Z)：截取子串

like(A,B)：确定给定的字符串与指定的模式是否匹配

4、【条件判断函数、集合函数、其它函数】

typeof(x)：返回数据的类型

last_insert_rowid()：返回最后插入的数据的ID

十一、date相关函数

1.对年月进行操作

SELECT DATE('2006-10-17','+1 day','+1 year');  2007-10-18  

2.获取时分

select time('now','localtime')    19:27:45

datetime 

3.直接对时间 2006-10-17 00:20:00 进行计算

SELECT DATETIME ('2016-10-17 00:20:00','+1 hour','-12 minute');

4.获取时间，并转换成本地时区时间

SELECT DATETIME ('now','localtime');  

5.+8 时区， 中国时区 和 localtime 效果在中国是一致的

SELECT DATETIME ('now','+8 hour');   

6.取现在时间，时区是本地时区，在此时间基础上 （对时间运算）

SELECT DATETIME ('now', 'localtime', '+2 hour' , '+20 minute' , '-20 second');  

7.一年开始（start of year）的时间

SELECT DATETIME ('now','localtime'，'start of year');   

8. 对计算完的时间，得到他那个月的第一天

SELECT DATETIME ('2016-10-17 00:20:00','+1 hour','-12 minute','start of month');  

9. 现在时间当天开始时间

SELECT DATETIME ('now','localtime'，'start of day');  

10. 计算机当前月份的最后一天

SELECT date(‘now’,’start of month’,’+1 month’,’-1 day’) 

11.　时间戳 1480593328（单位：秒） 表示的日期和时间

SELECT datetime('1480593328','unixepoch','localtime')

12. 两个日期时间之间相差多少秒（julianday）

SELECT julianday('now','localtime')*86400 - 

julianday('2004-01-01 02:34:56')*86400

13. 获取当前的时间的毫秒数(strftime ) 结果：1480593328000

SELECT  strftime ('%s','now') * 1000   

14. 获取当前时间并格式化 结果 2006.10.17 21:41:09

SELECT strftime('%Y.%m.%d %H:%M:%S','now','localtime');  

15.查询今天内所有的记录（day）

SELECT time>= DATETIME ('now','start of day','+0 day') 

AND time< DATETIME ('now','start of day','+1 day') from 表

16.查询昨天所有的记录（+day -day）

SELECT time>= DATETIME ('now','start of day','-1 day') 

AND time< DATETIME ('now','start of day','+0 day')

 from 表

17.查询本周所有记录（weekday） -6天的第一个星期一 到 周天 0点

SELECT time>=DATETIME ('now','start of day','-6 day','weekday 1') 

AND time<DATETIME ('now','start of day','+1 day','weekday 1') 

from 表  

18.查询本月所有记录（start of month）

SELECT Time>= DATETIME ('now','start of month','+0 month','-0 day') 

AND Time < datetime('now','start of month','+1 month','0 day')

 from 表

19.查询上一月所有记录（+ month -month）

SELECT Time>= DATETIME ('now','start of month','-1 month','-0 day')

 AND Time < DATETIME ('now','start of month','+0 month','-1 day') 

 from 表

20.计算今年十月份第一个星期二的日期（month weekday）

SELECT date('now','start of year','+9 months','weekday 2');

====解释，基本知识==========================

（1）时间/日期函数

DATETIME().………………….产生日期和时间

date()………………………产生日期

time()………………………产生时间

julianday(日期时间字符串, 修正符, 修正符, ……) 函数返回一个天数，从格林威治时间公元前4714年11月24号开始算起

strftime() ===============对以上三个函数产生的日期和时间进行格式化

其他四个函数都可以用 strftime() 函数来表示： 

date(…) -> strftime(“%Y-%m-%d”,…)

time(…) -> strftime(“%H:%M:%S”,…)

datetime(…) -> strftime(“%Y-%m-%d %H:%M:%S”,…)

julianday(…) -> strftime(“%J”,…)

（2）DATETIME ()的用法是：DATETIME (日期/时间, 时间时区格式 , 修正符， 修正符…)

（3）date()和time()的语法与DATETIME ()相同

在时间/日期函数里可以使用如下格式的字符串作为参数：

YYYY-MM-DD

YYYY-MM-DD HH:MM

YYYY-MM-DD HH:MM:SS

YYYY-MM-DD HH:MM:SS.SSS

HH:MM 代表2000-01-01日的时间，

HH:MM:SS 代表2000-01-01日的时间，

HH:MM:SS.SSS 代表2000-01-01日的时间，

now 表示产生现在的时间

DDDD.DDDD 使用格林威治时间(UTC）； 一个 Julian Day Numbers

（4）修正符，日期和时间可以使用下面的修正符来更改日期或时间： 

NNN days 

NNN hours 

NNN minutes 

NNN.NNNN seconds 

NNN months 

NNN years 

start of month 

start of year 

start of week 

start of day 

weekday N 返回下一个星期是N的日期和时间； 

unixepoch 返回从1970-01-01开始算起的秒数 

localtime 第十三个修正符表示返回本地时间 

utc

（5）strftime() 函数

可以把YYYY-MM-DD HH:MM:SS格式的日期字符串转换成其它形式的字符串。

的语法是strftime(格式, 日期/时间, 修正符, 修正符, …)

它可以用以下的符号对日期和时间进行格式化：

%d 月份, 01-31

%f 小数形式的秒，SS.SSS

%H 小时, 00-23

%j 算出某一天是该年的第几天，001-366

%m 月份，00-12

%M 分钟, 00-59

%s 从1970年1月1日到现在的秒数

%S 秒, 00-59

%w 星期, 0-6 (0是星期天)

%W 算出某一天属于该年的第几周, 01-53

%Y 年, YYYY

%% 百分号

strftime()的用法举例如下：

SELECT strftime(‘%Y.%m.%d %H:%M:%S’,’now’,’localtime’); 

结果：2006.10.17 21:41:09

 十二、SQLite最大的特点在于其数据类型为无数据类型(typelessness)。这意味着可以保存任何类型的数据到所想要保存的任何表的任何列中，无论这列声明的数据类型是什么。虽然在生成表结构的时候，要声明每个域的数据类型，但SQLite并不做任何检查。开发人员要靠自己的程序来控制输入与读出数据的类型。这里有一个例外，就是当主键为整型值时，如果要插入一个非整型值时会产生异常。虽然，SQLite允许忽略数据类型，但是，仍然建议在Create Table语句中指定数据类型，因为数据类型有利于增强程序的可读性。另外，虽然在插入或读出数据的时候是不区分类型的，但在比较的时候，不同数据类型是有区别的。