这是我参与11月更文挑战的第6天，活动详情查看：2021最后一次更文挑战
概述
本篇学习笔记主要学习了SQLite中的和函数相关的知识，主要是一些简单函数的使用方法。
限定和排序相关的注意事项
上一篇学习笔记我们学习了查询数据库的时候limit和offset相关的操作，下面学习了这两个关键字有关的注意事项和order by关键字的学习。
在上一篇学习笔记中我们使用了limit来限制所要获取数据的数量，通过offset来指定偏移量。而有时候我们还希望能够指定根据某一个或者某几个列进行排序。此时就会用到order by命令。order by命令的语法和select的语法很相似：也是order by后面跟随以逗号分隔的一系列字段，每个字段都可以配合排序方向 -- asc(默认的，升序)或者desc(降序).
下面的命令演示了从new_contacts表中查询出全部数据，并且将查询的数据按照name降序排列，同时按照id升序排列，代码如下:
sq-> select * from new_contacts order by name desc,id asc;
id name phone email address
----- -------------------- ---------- ---------- ----------
1 zyf 1356789098 no address
4 zyf UNKNOW no address
6 zfy UNKNOW no address
22 Piec UNKNOW no address
21 pie pie UNKNOW no address
13 Pie accc UNKNOW no address
10 Pie (Blackberry) Pie UNKNOW no address
8 Peach Schnapps UNKNOW no address
23 Pcie UNKNOW no address
9 Mackinaw Peaches UNKNOW no address
7 Guacamole Dip UNKNOW no address
3 eee UNKNOW no address
2 ddd UNKNOW no address
5 ddd 12321321 no address
20 acb dPIE 2312312321 sfsfsf@ema
12 ac Pie Pie UNKNOW no address
11 abc Pie UNKNOW no address
14 aaaccc Die UNKNOW no address
可以看到，查询到的结果首先会按照name字段进行降序排序，再次基础上，有一些name相同的数据就会按照id升序进行排列，最终的结果是符合我们命令的要求的。
注意:
- 当我们同时使用
limit命令和offset命令的时候，我们可以省略offset关键字。比如limit 2 offset 1，我们可以写成limit 1,2,下面的命令演示了从数据表中查询两条数据，偏移量为1:
--包含limit和offset关键字
sq-> select * from new_contacts limit 2 offset 1;
id name phone email address
----- -------------------- ---------- ---------- ----------
2 ddd UNKNOW no address
3 eee UNKNOW no address
--只使用limit关键字
sq-> select * from new_contacts limit 1,2;
id name phone email address
----- -------------------- ---------- ---------- ----------
2 ddd UNKNOW no address
3 eee UNKNOW no address
可以看到：省略offset关键字后的查询结果和加上offset关键字的结果是一样的。
最主要的就是需要注意：limit后面需要首先跟上偏移量，再跟上限制的条数。
之所以这里要这样写，是因为在SQLite中，使用缩写时，offset总是优先于limit，并且offset依赖于limit，也就是说：我们可以只使用limit而不使用offset，但是不能只使用offset而不使用limit，如下所示:
--只使用limit而不使用offset
sq-> select * from new_contacts limit 2;
id name phone email address
----- -------------------- ---------- ---------- ----------
1 zyf 1356789098 no address
2 ddd UNKNOW no address
--只使用offset而不使用limit
sq-> select * from new_contacts offset 1;
Error: near "1": syntax error
- 另外需要注意的是：在SQLite命令中，
limit和offset总是在最后面的，否则将会出错，如下所示:
sq-> --将limit不放在最后的情况
sq-> select * from new_contacts limit 2 order by name;
Error: near "order": syntax error
sq-> select * from new_contacts limit 1,2 where id < 10;
Error: near "where": syntax error
可以看到，上面的两条命令我们将limit关键字放在了查询命令的中间的部分，就会出现错误。
函数(Function)
简单函数演示
SQLite提供了多种内置的函数和聚合，可以用在不同的字句中。函数的种类包括:
- 数学函数，如计算绝对值的
abs()等。 - 字符串格式函数，将字符串的值转换为大写或小写的
upper()和lower()，计算字符串长度的length()等。 - 聚合函数，例如
sum(),avg()函数等。
下面的代码演示了上面提到的三个函数的用法:
sq-> select abs(-1),upper('hello'),lower('WORLD');
abs(-1) upper('hello') lower('WORLD')
-------------------- -------------------- --------------------
1 HELLO world
可以看到，上面的命令分别获得了-1的绝对值为1，然后将hello转换为大写HELLO，最后将WORLD转换为小写world。
使用函数注意事项
- 函数名不区分大小写，例如
upper()和UPPER()是同一个函数，如下所示:
sq-> select UPPER('he'),UPper('llo');
UPPER('he') UPper('llo')
-------------------- --------------------
HE LLO
- 函数可以接收字段值作为参数，如下所示:
--原始查询结果
sq-> select id,name from new_contacts limit 2;
id name
-------------------- --------------------
14 aaaccc Die
11 abc Pie
--使用函数查询结果
sq-> select id,upper(name) from new_contacts limit 2;
id upper(name)
-------------------- --------------------
14 AAACCC DIE
11 ABC PIE
可以看到，我们将查询出来的name字段执行了转换为大写的操作，和原来的数据相比，数据都转换为了大写。
- 因为函数可以是任意表达式的一部分，所以函数也可以用在
where字句中。
比如在我们的数据表中，我们使用glob达到like相似的效果:
--使用like查询name以p或者P开头字符串
sq-> select name from new_contacts where name like 'p%';
name
--------------------
Pcie
Peach Schnapps
Pie (Blackberry) Pie
Pie accc
pie pie
Piec
--使用函数和glob达到上面的查询语句的效果
sq-> select name from new_contacts where upper(name) glob 'P*';
name
--------------------
Pcie
Peach Schnapps
Pie (Blackberry) Pie
Pie accc
pie pie
Piec
下面的代码演示了length()的效果:
--查询某个字段的数据的长度
sq-> select name,length(name) from new_contacts;
name length(name)
-------------------- --------------------
aaaccc Die 10
abc Pie 7
ac Pie Pie 10
acb dPIE 8
ddd 3
ddd 3
eee 3
Guacamole Dip 13
Mackinaw Peaches 16
Pcie 4
Peach Schnapps 14
Pie (Blackberry) Pie 20
Pie accc 8
pie pie 7
Piec 4
zfy 3
zyf 3
zyf 3
--将length()放在where字句中
sq-> select name from new_contacts where length(name) < 4;
name
--------------------
ddd
ddd
eee
zfy
zyf
zyf
聚合函数演示
聚合是一类特殊的函数，它从一组记录中计算聚合值。标准的聚合函数包括sum()，avg(),count(),min(),max()。
count()
下面的代码演示了从数据表中查询出name的长度小于4的数据的数量:
sq-> select count(*) from new_contacts where length(name) < 4;
count(*)
--------------------
6
sum()计算指定的数据的和
下面的代码计算了表中所有人的年龄总和。
sq-> select id,name,age from new_contacts;
id name age
-------------------- -------------------- --------------------
1 zyf 1
2 ddd 2
3 eee 3
4 zyf 4
5 ddd 5
6 zfy 6
7 Guacamole Dip 7
8 Peach Schnapps 8
9 Mackinaw Peaches 9
10 Pie (Blackberry) Pie 10
11 abc Pie 11
12 ac Pie Pie 12
13 Pie accc 13
14 aaaccc Die 14
20 acb dPIE 20
21 pie pie 21
22 Piec 22
23 Pcie 23
--计算年龄总和
sq-> select sum(age) from new_contacts;
sum(age)
--------------------
191
avg()计算平均数
下面的代码计算了表中所有人的年龄平均数:
sq-> select avg(age) from new_contacts;
avg(age)
--------------------
10.6111111111111
通过上面的数据进行计算可以得出最终的平均数即为10.611.
min()获得最小值
下面的代码演示了获取数据表中年龄最小的数据：
sq-> select min(age) from new_contacts;
min(age)
--------------------
1
max()获得最大值
下面的代码演示了获取数据表中年龄最大的数据:
sq-> select max(age) from new_contacts;
max(age)
--------------------
23
其它
聚合不仅可以聚合字段，也可以聚合任何表达式，包括函数，例如下面的代码计算了所有name的总长度和平均值。
sq-> --总长度
sq-> select sum(length(name)) from new_contacts;
sum(length(name))
--------------------
139
--平均值
sq-> select avg(length(name)) from new_contacts;
avg(length(name))
--------------------
7.72222222222222
聚合可以在select字句中操作，它们不仅可以对from字句进行选中的行的值进行计算，也可以对where字句选中行的值进行计算。
--查询出name长度小于4的数据
sq-> select name,length(name) from new_contacts where length(name) < 4;
name length(name)
-------------------- --------------------
ddd 3
ddd 3
eee 3
zfy 3
zyf 3
zyf 3
--对上面查询出的数据使用聚合函数
sq-> select avg(length(name)) from new_contacts where length(name) < 4;
avg(length(name))
--------------------
3.0
