The best SQLite commonly used functions Tutorial In 2024, In this tutorial you can learn SQLite COUNT function,SQLite MAX function,SQLite MIN function,SQLite AVG function,SQLite SUM function,SQLite RANDOM function,SQLite ABS function,SQLite UPPER function,SQLite LOWER function,SQLite LENGTH function,SQLite sqlite_version function,
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 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 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 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 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 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 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 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 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 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 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 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