Latest web development tutorials

SQLite commonly used functions

SQLite has many built-in functions for processing the string or numeric data. Here are some useful SQLite built-in functions, and all functions are not case-sensitive, which means you can use these functions lowercase or uppercase or mixed form. For more details, please see the official SQLite documentation:

序号函数 & 描述
1SQLite COUNT 函数
SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。
2SQLite MAX 函数
SQLite MAX 聚合函数允许我们选择某列的最大值。
3SQLite MIN 函数
SQLite MIN 聚合函数允许我们选择某列的最小值。
4SQLite AVG 函数
SQLite AVG 聚合函数计算某列的平均值。
5SQLite SUM 函数
SQLite SUM 聚合函数允许为一个数值列计算总和。
6SQLite RANDOM 函数
SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。
7SQLite ABS 函数
SQLite ABS 函数返回数值参数的绝对值。
8SQLite UPPER 函数
SQLite UPPER 函数把字符串转换为大写字母。
9SQLite LOWER 函数
SQLite LOWER 函数把字符串转换为小写字母。
10SQLite LENGTH 函数
SQLite LENGTH 函数返回字符串的长度。
11SQLite sqlite_version 函数
SQLite sqlite_version 函数返回 SQLite 库的版本。

Before we begin to explain the function of these examples, assume COMPANY table has the following records:

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

SQLite COUNT function

SQLite COUNT aggregate function is used to calculate the number of rows in a database table. The following are examples:

sqlite> SELECT count (*) FROM COMPANY;

The above SQLite SQL statement will produce the following results:

count (*)
----------
7

SQLite MAX function

SQLite MAX aggregate function allows us to choose a column maximum. The following are examples:

sqlite> SELECT max (salary) FROM COMPANY;

The above SQLite SQL statement will produce the following results:

max (salary)
-----------
85000.0

SQLite MIN function

SQLite MIN aggregate functions allows us to select a column minimum. The following are examples:

sqlite> SELECT min (salary) FROM COMPANY;

The above SQLite SQL statement will produce the following results:

min (salary)
-----------
10000.0

SQLite AVG function

SQLite AVG aggregate function calculates the average of a column. The following are examples:

sqlite> SELECT avg (salary) FROM COMPANY;

The above SQLite SQL statement will produce the following results:

avg (salary)
----------------
37142.8571428572

SQLite SUM function

SQLite SUM aggregate function allows to calculate the sum of a numeric column. The following are examples:

sqlite> SELECT sum (salary) FROM COMPANY;

The above SQLite SQL statement will produce the following results:

sum (salary)
-----------
260,000.0

SQLite RANDOM function

SQLite RANDOM function returns a pseudo-random integer -9223372036854775808 and +9223372036854775807 in between. The following are examples:

sqlite> SELECT random () AS Random;

The above SQLite SQL statement will produce the following results:

Random
-------------------
5876796417670984050

SQLite ABS function

SQLite ABS function returns the absolute value of the numerical parameters. The following are examples:

sqlite> SELECT abs (5), abs (-15), abs (NULL), abs (0), abs ( "ABC");

The above SQLite SQL statement will produce the following results:

abs (5) abs (-15) abs (NULL) abs (0) abs ( "ABC")
---------- ---------- ---------- ---------- ----------
5150 0.0

SQLite UPPER function

SQLite UPPER function to convert a string to uppercase letters. The following are examples:

sqlite> SELECT upper (name) FROM COMPANY;

The above SQLite SQL statement will produce the following results:

upper (name)
-----------
PAUL
ALLEN
TEDDY
MARK
DAVID
KIM
JAMES

SQLite LOWER function

SQLite LOWER function converts a string to lowercase. The following are examples:

sqlite> SELECT lower (name) FROM COMPANY;

The above SQLite SQL statement will produce the following results:

lower (name)
-----------
paul
allen
teddy
mark
david
kim
james

SQLite LENGTH function

SQLite LENGTH function returns the length of the string. The following are examples:

sqlite> SELECT name, length (name) FROM COMPANY;

The above SQLite SQL statement will produce the following results:

NAME length (name)
---------- ------------
Paul 4
Allen 5
Teddy 5
Mark 4
David 5
Kim 3
James 5

SQLite sqlite_version function

SQLite sqlite_version function returns the version of the SQLite library. The following are examples:

sqlite> SELECT sqlite_version () AS 'SQLite Version';

The above SQLite SQL statement will produce the following results:

SQLite Version
--------------
3.6.20