SQLite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 SQLite 内置函数，且所有函数都是大小写不敏感，这意味着您可以使用这些函数的小写形式或大写形式或混合形式。欲了解更多详情，请查看 SQLite 的官方文档：
| 序号 | 函数 & 描述 |
|---|---|
| 1 | SQLite COUNT 函数 SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。 |
| 2 | SQLite MAX 函数 SQLite MAX 聚合函数允许我们选择某列的最大值。 |
| 3 | SQLite MIN 函数 SQLite MIN 聚合函数允许我们选择某列的最小值。 |
| 4 | SQLite AVG 函数 SQLite AVG 聚合函数计算某列的平均值。 |
| 5 | SQLite SUM 函数 SQLite SUM 聚合函数允许为一个数值列计算总和。 |
| 6 | SQLite RANDOM 函数 SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。 |
| 7 | SQLite ABS 函数 SQLite ABS 函数返回数值参数的绝对值。 |
| 8 | SQLite UPPER 函数 SQLite UPPER 函数把字符串转换为大写字母。 |
| 9 | SQLite LOWER 函数 SQLite LOWER 函数把字符串转换为小写字母。 |
| 10 | SQLite LENGTH 函数 SQLite LENGTH 函数返回字符串的长度。 |
| 11 | SQLite sqlite_version 函数 SQLite sqlite_version 函数返回 SQLite 库的版本。 |
在我们开始讲解这些函数实例之前，先假设 COMPANY 表有以下记录：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span> <span class="lit">32</span> <span class="typ">California</span> <span class="lit">20000.0</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="lit">25</span> <span class="typ">Texas</span> <span class="lit">15000.0</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">23</span> <span class="typ">Norway</span> <span class="lit">20000.0</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">25</span> <span class="typ">Rich</span><span class="pun">-</span><span class="typ">Mond</span> <span class="lit">65000.0</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">27</span> <span class="typ">Texas</span> <span class="lit">85000.0</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="lit">22</span> <span class="typ">South</span><span class="pun">-</span><span class="typ">Hall</span> <span class="lit">45000.0</span> <span class="lit">7</span> <span class="typ">James</span> <span class="lit">24</span> <span class="typ">Houston</span> <span class="lit">10000.0</span> |
SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。下面是实例：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT count</span><span class="pun">(*)</span><span class="pln"> FROM COMPANY</span><span class="pun">;</span> |
上面的 SQLite SQL 语句将产生以下结果：
|
1 2 3 |
<span class="pln">count</span><span class="pun">(*)</span> <span class="pun">----------</span> <span class="lit">7</span> |
SQLite MAX 聚合函数允许我们选择某列的最大值。下面是实例：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT max</span><span class="pun">(</span><span class="pln">salary</span><span class="pun">)</span><span class="pln"> FROM COMPANY</span><span class="pun">;</span> |
上面的 SQLite SQL 语句将产生以下结果：
|
1 2 3 |
<span class="pln">max</span><span class="pun">(</span><span class="pln">salary</span><span class="pun">)</span> <span class="pun">-----------</span> <span class="lit">85000.0</span> |
SQLite MIN 聚合函数允许我们选择某列的最小值。下面是实例：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT min</span><span class="pun">(</span><span class="pln">salary</span><span class="pun">)</span><span class="pln"> FROM COMPANY</span><span class="pun">;</span> |
上面的 SQLite SQL 语句将产生以下结果：
|
1 2 3 |
<span class="pln">min</span><span class="pun">(</span><span class="pln">salary</span><span class="pun">)</span> <span class="pun">-----------</span> <span class="lit">10000.0</span> |
SQLite AVG 聚合函数计算某列的平均值。下面是实例：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT avg</span><span class="pun">(</span><span class="pln">salary</span><span class="pun">)</span><span class="pln"> FROM COMPANY</span><span class="pun">;</span> |
上面的 SQLite SQL 语句将产生以下结果：
|
1 2 3 |
<span class="pln">avg</span><span class="pun">(</span><span class="pln">salary</span><span class="pun">)</span> <span class="pun">----------------</span> <span class="lit">37142.8571428572</span> |
SQLite SUM 聚合函数允许为一个数值列计算总和。下面是实例：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT sum</span><span class="pun">(</span><span class="pln">salary</span><span class="pun">)</span><span class="pln"> FROM COMPANY</span><span class="pun">;</span> |
上面的 SQLite SQL 语句将产生以下结果：
|
1 2 3 |
<span class="pln">sum</span><span class="pun">(</span><span class="pln">salary</span><span class="pun">)</span> <span class="pun">-----------</span> <span class="lit">260000.0</span> |
SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。下面是实例：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT random</span><span class="pun">()</span><span class="pln"> AS </span><span class="typ">Random</span><span class="pun">;</span> |
上面的 SQLite SQL 语句将产生以下结果：
|
1 2 3 |
<span class="typ">Random</span> <span class="pun">-------------------</span> <span class="lit">5876796417670984050</span> |
SQLite ABS 函数返回数值参数的绝对值。下面是实例：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT abs</span><span class="pun">(</span><span class="lit">5</span><span class="pun">),</span><span class="pln"> abs</span><span class="pun">(-</span><span class="lit">15</span><span class="pun">),</span><span class="pln"> abs</span><span class="pun">(</span><span class="pln">NULL</span><span class="pun">),</span><span class="pln"> abs</span><span class="pun">(</span><span class="lit">0</span><span class="pun">),</span><span class="pln"> abs</span><span class="pun">(</span><span class="str">"ABC"</span><span class="pun">);</span> |
上面的 SQLite SQL 语句将产生以下结果：
|
1 2 3 |
<span class="pln">abs</span><span class="pun">(</span><span class="lit">5</span><span class="pun">)</span><span class="pln"> abs</span><span class="pun">(-</span><span class="lit">15</span><span class="pun">)</span><span class="pln"> abs</span><span class="pun">(</span><span class="pln">NULL</span><span class="pun">)</span><span class="pln"> abs</span><span class="pun">(</span><span class="lit">0</span><span class="pun">)</span><span class="pln"> abs</span><span class="pun">(</span><span class="str">"ABC"</span><span class="pun">)</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">5</span> <span class="lit">15</span> <span class="lit">0</span> <span class="lit">0.0</span> |
SQLite UPPER 函数把字符串转换为大写字母。下面是实例：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT upper</span><span class="pun">(</span><span class="pln">name</span><span class="pun">)</span><span class="pln"> FROM COMPANY</span><span class="pun">;</span> |
上面的 SQLite SQL 语句将产生以下结果：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">upper</span><span class="pun">(</span><span class="pln">name</span><span class="pun">)</span> <span class="pun">-----------</span><span class="pln"> PAUL ALLEN TEDDY MARK DAVID KIM JAMES</span> |
SQLite LOWER 函数把字符串转换为小写字母。下面是实例：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT lower</span><span class="pun">(</span><span class="pln">name</span><span class="pun">)</span><span class="pln"> FROM COMPANY</span><span class="pun">;</span> |
上面的 SQLite SQL 语句将产生以下结果：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">lower</span><span class="pun">(</span><span class="pln">name</span><span class="pun">)</span> <span class="pun">-----------</span><span class="pln"> paul allen teddy mark david kim james</span> |
SQLite LENGTH 函数返回字符串的长度。下面是实例：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT name</span><span class="pun">,</span><span class="pln"> length</span><span class="pun">(</span><span class="pln">name</span><span class="pun">)</span><span class="pln"> FROM COMPANY</span><span class="pun">;</span> |
上面的 SQLite SQL 语句将产生以下结果：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">NAME length</span><span class="pun">(</span><span class="pln">name</span><span class="pun">)</span> <span class="pun">----------</span> <span class="pun">------------</span> <span class="typ">Paul</span> <span class="lit">4</span> <span class="typ">Allen</span> <span class="lit">5</span> <span class="typ">Teddy</span> <span class="lit">5</span> <span class="typ">Mark</span> <span class="lit">4</span> <span class="typ">David</span> <span class="lit">5</span> <span class="typ">Kim</span> <span class="lit">3</span> <span class="typ">James</span> <span class="lit">5</span> |
SQLite sqlite_version 函数返回 SQLite 库的版本。下面是实例：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT sqlite_version</span><span class="pun">()</span><span class="pln"> AS </span><span class="str">'SQLite Version'</span><span class="pun">;</span> |
上面的 SQLite SQL 语句将产生以下结果：
|
1 2 3 4 5 |
<span class="typ">SQLite</span> <span class="typ">Version</span> <span class="pun">--------------</span> <span class="lit">3.6</span><span class="pun">.</span><span class="lit">20 from:<a href="http://www.runoob.com/sqlite/sqlite-functions.html">http://www.runoob.com/sqlite/sqlite-functions.html</a></span> |