【Android】数据存储全方案之SQLite常用SQL语句、函

作者：邹峰立，微博：zrunker，邮箱：zrunker@yahoo.com，微信公众号：书客创作，个人平台：www.ibooker.cc。

本文选自书客创作平台第7篇文章。阅读原文 。

书客创作

SQLite是一个轻型级、嵌入式、ACID关系型数据库（A-原子性，C-一致性，I-隔离性，D-持久性），第一个Alpha版本诞生于2000年5月，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用，如QQ，迅雷等，它占用资源非常少，在嵌入式设备中，可能只需要几百KB的内存就够了，完全可以把SQLite当作一个API来进行使用。

SQLite优点

SQLite数据库是D.Richard Hipp用C语言编写的开源嵌入式数据库，支持数据库大小2TB。它相对于其他数据库来说，除了轻量之外，还有很多优点，例如更加高效，它比一般的数据库要高效很多，因为它本身做了很多优化，而且对一些复杂事务不进行处理，所以相对于Oracle等数据库更加高效。而且它是绿色，零配置的数据库，可以作为一个可执行文件来使用。SQLite数据库只有一个文件，即该数据库下的所有表，视图，索引等都放在一个文件当中，所以在任意平台上都可以打开，不需要文件转换。它支持动态数据类型，动态数据类型是指SQLite在创建表的时候不需要设置字段数据字段类型。

动态数据类型

1. NULL：空值
2. INTEGER：带符号整数
3. REAL：浮点数字，存储为8-byte IEEE浮点数
4. TEXT：字符串文本
5. BLOB：二进制对象

SQLite缺点

但是SQLite也有自身的缺点，例如并发性，因为SQLite只有一个文件，当有一个线程进行数据库操作的时候，数据库文件将会被锁，导致其他线程无法操作数据库。它对网络文件的支持是非常弱的。SQLite只支持SQL的部分功能，并不能支持全部。

SQL使用

SQLite既然是一种数据库，那当然有会有相应的SQL语法，方法，函数等，但是它只支持SQL的部分功能，那么到底该如何使用SQL呢？

常用方法

1. CREATE：TABLE（表），VIEW（视图），INDEX（索引），TRIGGER（触发器-支持行触发器，不支持语句触发器）。
2. TRANSACTION：事务。
3. INSERT、SELECT、DELETE、UPDATE
4. DROP
5. ALTER：支持更改表名，增加列，不支持撤销列或更改列约束性条件。
6. ATTACH：附和数据库，可以理解为将两个数据库附和为一个数据库来进行使用。

约束条件

1. NOT NULL：非空
2. UNIQUE：唯一，即该字段数据不能重复。
3. PRIMARY KEY：主键，该字段既非空，又是唯一值。
4. CHECK：检查，会对插入值进行检测，例如CHECK(字段名>0)。
5. DEFAULT：默认值。

常用SQL方法

例如有一个表为user，该表中包含字段id，name，age。和一张表friend，该表包含字段id，gname，uid。

• group by ... (having ...)：分组

select * from user group by name having age > 30; 

• distinct：剔除重复

select distinct name,age from user;

• order by：排序，asc（升序），desc（倒序）

#按照id升序查询
select * from user order by id asc;
#安装age倒序查询
select * from user order by age desc;

• limit：限制查询条数

#5代表查询条数
select * from user limit 5;
#0代表开始查询行，5代表查询条数
select * from user limit 0,5;

• join：连接（交叉连接cross join，内连接inner join...on...，外连接left outer join...on...）

#交叉连接，表之间没有任何关系，如果一张表5行，另外一张表4行，查询出来的结果为20（4*5）行。
select * from user cross join friend;
#内连接
select * from user a inner join friend b on a.id = b.uid;
#外连接，没有全连接
select * from user a left outer join friend on a.id = b.uid;

• like/GLOB：模糊查询（glob大小写敏感，而like大小写不敏感，一般配合通配符进行使用）

#like %通配任意字符
select * from user where name like '%zrunker%';
#like _只通配单一字符
select * from user where name like '_zrunker_';
#GLOB *通配任意字符
select * from user where name GLOB '*zrunker*';
#GLOB ?只通配单一字符
select * from user where name GLOB '?zrunker?';

• in/between：范围查询

#查询age为20，21，22，23的数据
select * from user where age in(20,21,22,23);
#查询age为20到23之间的数据
select * from user where age between(20,23);

游标（Cursor）常用方法

// 获取总的数据项数
int getCount();
// 判断是否第一条记录
boolean isFirst();
// 判断是否最后一条记录
boolean isLast();
// 移动到第一条记录
boolean moveToFirst();
// 移动到最后一条记录
boolean moveToLast();
// 移动到指定记录
boolean move(int var1);
// 移动到下一条记录
boolean moveToNext();
// 移动到上一条记录
boolean moveToPrevious();
// 根据列名获取列索引
int getColumnIndexOrThrow(String var1) throws IllegalArgumentException;
// 获取指定列的int类型值
int getInt(int var1);
// 获取指定列的String类型值
String getString(int var1);

聚合函数

1. avg(字段)：求平均值，字段为非空数字，如果该字段为字符串，则当作0处理。
2. count(字段|* )：计数，如果为字段，就计算该字段非空的数量。如果是*就代表一个有多少行。
3. group_concat(字段[,|x])：将满足条件的字段拼接成一个字符串，默认是以‘，’分割，也可以自定义x(任意字符)进行分割。
4. max(字段)，min(字段)：最大值，最小值。
5. sum(字段)：求和，字段为整数时候，注意是否整数溢出。
6. total(字段)：求和，字段默认当作浮点数处理。

核心函数

// 查询插入最后一项的rowid，rowid是隐藏的字段。
last_insert_rowid();
// 求字段的绝对值
abs(字段);
// 查询第一个非空的字段值
coalesce(字段1，字段2...);
// 查询两个字段中非空的字段值
ifnull(字段1，字段2);
// 查询字段长度
length(字段);
// 字段值变成小写
lower(字段);
// 所有行在这一列中最大值，最小值
max(字段1，字段2...)，min(字段1，字段2...)
// 判断两个字段是否一样，如果一样就返回null，否则返回字段1
nullif(字段1，字段2);
// 字段是否符合x模式（'%a'/'_a'），符合返回1，否则返回0
like(x，字段);
// 取随机数
random();
// 替换，字段是否含有y，如果有就用y替换x
replace(字段,x,y);
// 四舍五入，x代表保留的小数位
round(字段[,x]);
// 拆分字符串，字符串x，开始拆分位置y（整数），拆分长度z（整数）。如果z不写就会从开始位置y到字符串最后一位
substr(字段x,y[,z]);
// changes查询最后一次改变的行数，total_changes查询总共改变的行数
changes()，total_changes();
// trim为去掉字段左右两边的字符x，如果不设置字符x，就去掉字段两边空格。同理，ltrim为去掉左边字符x，rtrim为去掉右边字符x。
trim(字段[,x])，ltrim(字段[,x])，rtrim(字段[,x]);
// 获取字段类型
typeof(字段);
// 字段值变成大写
upper(字段);
// 产生n位/n字节的blob数据，zeroblob插入0，randomblob随机字符。
zeroblob(n)，randomblob(n);
// 获取该字段相应格式的数据，如果字段值为字符串，就加上单引号，如果是数字，就直接显示数字。
quote(字段);
// 字符串y在字符串x中的位置
instr(x,y);

时间函数

• date(timestring,modifier,modifier,...)：日期

#查询当前日期，输出默认格式为yyyy-MM-dd
select date('now');
#当前日期+两天
select date('now','+2 days');
#查询这个月的最后一天日期
select date('now','start of month','+1 month','-1 day');

• time(timestring,modifier,modifier,modifier,...)：时间

#查询当前时间，输出默认格式为HH:mm:dd
select time('now');

• datetime(timestring,modifier,modifier,modifier,...)：日期时间

#查询当前日期时间，输出默认格式为yyyy-MM-dd HH:mm:dd
select datetime('now');

• julianday(timestring,modifier,modifier,modifier,...)：布罗利日期-一般用来计算两个日期的距离的天数

#查询当前日期到1992-10-13的天数
select julianday('now') - julianday('1992-10-13');

• strftime(format,timestring,modifier,modifier,modifier,...)：格式化日期时间

#查询当前日期，格式为年-月-日
select strftime('%Y-%m-%d','now');

json相关函数

• json(string)，json_object(...)：产生json对象

#查询json对象
select json('{"name":"zrunker","age":24}');
#查询json对象。参数：键，值
select json_object('name','zrunker','age',24);

• json_array(value1,value2,...)：产生json数组

#查询json数组
select json_array(json('{"name":"zrunker","age":24}'),json('{"name":"zrunker","age":24}'),50,'a');

• json_extract(json,path,...)：提取json对象具体字段值

#提取json对象中name值
select json_extract('{"name":"zrunker","age":24}','$.name');

• json_array _length(json)：查询json数组长度

#查询json数组长度，参数为json字符串，如果json字符串非json数组将返回0
select json_array_length('[0,1,2,3]');
#查询json字符串中one的json数组长度
select json_array_length('{"one":[0,1,2,3],"two":3}','$.one');

• json_insert(json,path,value,...)：插入json

#将json对象插入一个字段为birthday，值为1999-10-23。注意：插入只能为json中没有的字段才会有效果
select json_insert('{"name":"zrunker","age":24}','$.birthday','1999-10-23');

• json_replace(json,path,value,...)：替换json

#修改json对象中字段值，只能替换已存在的字段
select json_replace('{"name":"zrunker","age":24}','$.age',25);

• json_set(json,path,value,...)：重置json

#如果存在该字段进行修改，如果不存在该字段进行插入
select json_set('{"name":"zrunker","age":24}','$.age',25);
select json_set('{"name":"zrunker","age":24}','$.birthday','1999-10-23');

常见问题

1. AUTOINCREMENT field：设置自增字段，其中rowid默认也是自增
2. VARCHAR最大多少字符：在SQLite中就相对于TEXT，所以在SQLite中最大字符是没有限制的
3. round(9.95,1) = 9.9而不是10.0：原因是9.95实际上是以浮点数来表示，而浮点数是没有表示9.95，而是用9.949999...来表示，所以四舍五入之后为9.9。
4. insert很慢：每一个insert语句都是一个事务，所以insert执行会很慢。
5. column1="column1"无效：使用单引号匹配字符串。

优化

在数据库非常庞大，或者实际开发中有一定的要求，才会对数据库进行优化。

1. PRAGMA auto_vacuum=0|1：对数据库文件大小进行压缩，如果为1系统将会自动进行回收。
2. PRAGMA cache_size=5000：设置当前会话中cache的大小。
3. PRAGMA default_cache_size=5000：设置默认cache的大小。
4. PRAGMA page_size=bytes：设置每页显示字节数，默认4KB。
5. PRAGMA synchronous=FULL|NORMAL|OFF：设置数据库同步，默认设置OFF。
6. PRAGMA temp_store=MEMORY|FILE|DEFAULT：临时文件设置，内存/文件/默认。
7. TRANSACTION事务优化批量数据插入。
8. 索引：索引不能随意创建，创建太多反而会使查询速度变慢，如果建立索引字段为空，则该索引是无效的，所以一般将常用的字段建立索引。使用索引注意：如果使用like进行查询时，索引将不会触发，可以将查询条件改成>，=，<，>=，<=来表示，达到触发索引效果。在开发当中可以通过EXPLAIN QUERY PLAN SELECT...来查询是否使用索引index。

GitHub地址
阅读原文

---

微信公众号：书客创作