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:
| 序号 | 函数 & 描述 |
|---|---|
| 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 库的版本。 |
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