1.14.1. What is the SQLite operator? ¶
Operator is a reserved word or character that is used primarily for
SQLite
Statement of
WHERE
Clause, such as comparisons and arithmetic operations.
Operator is used to specify
SQLite
Statement and join multiple conditions in the statement.
Arithmetic operator
Comparison operator
Logical operator
Bit operator
1.14.2. SQLite arithmetic operator ¶
Suppose the variable axi10 and the variable baked 20, then:
Operator | Description | Example |
|---|---|---|
Addition-adds the values on both sides of the operator | A + b will get 30 | |
Subtraction-left Operand minus right Operand | A-b will get-10 | |
* | Multiplication-multiplies the values on both sides of the operator | A* b will get 200 |
/ | Division-left Operand divided by right Operand | B / a will get 2. |
% | The remainder obtained by dividing the left Operand by the right Operand. | B a will give 0 |
1.14.3. Example ¶
Here is a simple example of the SQLite arithmetic operator:
sqlite> .mode line
sqlite> select 10 + 20;
10 + 20 = 30
sqlite> select 10 - 20;
10 - 20 = -10
sqlite> select 10 * 20;
10 * 20 = 200
sqlite> select 10 / 5;
10 / 5 = 2
sqlite> select 12 % 5;
12 % 5 = 2
1.14.4. SQLite comparison operator ¶
Suppose the variable axi10 and the variable baked 20, then:
Operator | Description | Example |
|---|---|---|
== | Check whether the values of the two operands are equal, and if so, the condition is true. | (a = = b) is not true. |
= | Check whether the values of the two operands are equal, and if so, the condition is true. | (a = b) is not true. |
! = | Check whether the values of the two operands are equal, and if not, the condition is true. | (a! = b) true. |
< > | Check whether the values of the two operands are equal, and if not, the condition is true. | (a < > b) is true. |
> | Check whether the value of the left Operand is greater than that of the right Operand, and if so, the condition is true. | (a > b) not true. |
< | Check whether the value of the left Operand is less than the value of the right Operand, and if so, the condition is true. | (a < b) is true. |
> = | Check whether the value of the left Operand is greater than or equal to the value of the right Operand, and if so, the condition is true. | (a > = b) not true. |
< = | Check whether the value of the left Operand is less than or equal to the value of the right Operand, and if so, the condition is true. | (a < = b) is true. |
! < | Check that the value of the left Operand is not less than that of the right Operand, and if so, the condition is true. | (a! < b) is false. |
! > | Check that the value of the left Operand is not greater than that of the right Operand, and if so, the condition is true. | (a! > b) true. |
1.14.5. Example ¶
Hypothetical
COMPANY
The 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
The following examples demonstrate a variety of
SQLite
Compare the use of operators.
Here, we use the WHERE Clause, which will be explained in a separate chapter later, but now you need to understand
WHERE
Clause is used to set the
SELECT
The conditional statement of the statement.
Below.
SELECT
Statement lists the
SALARY
All records greater than 50000.00:
sqlite> SELECT * FROM COMPANY WHERE SALARY > 50000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
Below.
SELECT
Statement lists the
SALARY
All records equal to 20000.00:
sqlite> SELECT * FROM COMPANY WHERE SALARY = 20000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
Below.
SELECT
Statement lists the
SALARY
All records not equal to 20000.00:
sqlite> SELECT * FROM COMPANY WHERE SALARY != 20000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.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
Below.
SELECT
Statement lists the
SALARY
All records not equal to 20000.00:
sqlite> SELECT * FROM COMPANY WHERE SALARY <> 20000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.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
Below.
SELECT
Statement lists the
SALARY
All records greater than or equal to 65000.00:
sqlite> SELECT * FROM COMPANY WHERE SALARY >= 65000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
1.14.6. SQLite logical operator ¶
The following is
SQLite
A list of all logical operators in the
Operator | Description |
|---|---|
AND | The AND operator allows the existence of multiple conditions in the WHERE clause of a SQL statement. |
BETWEEN | The BETWEEN operator is used to search for values among a series of values within a given range of minimum and maximum values. |
EXISTS | The EXISTS operator is used to search for the existence of rows in a specified table that meets certain conditions. |
IN | The IN operator is used to compare a value with a series of values in a specified list. |
NOT IN | The opposite of the IN operator that compares a value with values that are not in a specified list. |
LIKE | The LIKE operator is used to compare a value with a similar value using a wildcard operator. |
GLOB | The GLOB operator is used to compare a value with a similar value using a wildcard operator. GLOB differs from LIKE in that it is case-sensitive. |
NOT | The NOT operator is the opposite of the logical operator used. Such as NOT EXISTS, NOT BETWEEN, NOT IN, and so on. It is the negative operator. |
OR | The OR operator is used to combine multiple conditions in the WHERE clause of a SQL statement. |
IS NULL | The NULL operator is used to compare a value with a NULL value. |
IS | The IS operator is similar to =. |
IS NOT | The IS NOT operator is similar to! =. |
|| | Concatenate two different strings to get a new string. |
UNIQUE | The UNIQUE operator searches each row in the specified table to ensure uniqueness (no repetition). |
1.14.7. Example ¶
Hypothetical
COMPANY
The 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
The following example demonstrates
SQLite
The use of logical operators.
Below.
SELECT
Statement lists the
AGE
Greater than or equal to 25 且 All records with a salary greater than or equal to 65000.00:
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
Below.
SELECT
Statement lists the
AGE
Greater than or equal to 25 或 All records with a salary greater than or equal to 65000.00:
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
Below.
SELECT
Statement lists the
AGE
Not for
NULL
All records, the result shows all records, which means that there is no record of
AGE
Equal to
NULL
:
sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
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
Below.
SELECT
Statement lists the
NAME
For all records that begin with ‘Ki’, there is no limit to the characters after’ Ki’:
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0
Below.
SELECT
Statement lists the
NAME
For all records that begin with ‘Ki’, there is no limit to the characters after’ Ki’:
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0
The following SELECT statement lists all records with a value of 25 or 27 for AGE:
sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
Below.
SELECT
Statement lists the
AGE
Is neither 25 nor 27 for all records:
sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
Below.
SELECT
Statement lists the
AGE
All records with values between 25 and 27
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
Below.
SELECT
Statement use
SQL
Subquery, which looks for SALARY > 65000 with
AGE
All records of the field, the following
WHERE
Clause vs.
EXISTS
Operator is used together to list the
AGE
All records that exist in the results returned by the subquery:
sqlite> SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
AGE
----------
32
25
23
25
27
22
24
Below.
SELECT
Statement use
SQL
Subquery, which looks for SALARY > 65000 with
AGE
All records of the field, the following
WHERE
Clause is used with the > operator to list the
AGE
All records greater than the age in the results returned by the subquery:
sqlite> SELECT * FROM COMPANY
WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
1.14.8. SQLite bit operator ¶
The bit operator acts on the bit and performs the operation bit by bit. Truth table & and| are as follows:
P | Q | P & Q | P| Q |
|---|---|---|---|
0 | 0 | 0 | 0 |
0 | 1 | 0 | 1 |
1 | 1 | 1 | 1 |
1 | 0 | 0 | 1 |
Suppose that if A = 60 and B = 13, now in binary format, they are as follows:
A = 0011 1100
B = 0000 1101
-----------------
A&B = 0000 1100
A|B = 0011 1101
~A = 1100 0011
Listed in the following table
SQLite
The bit operator supported by the language. Suppose the variable Aban 60 and the variable Bamboo 13, then:
Operator | Description | Example |
|---|---|---|
& | If it exists in both operands, the binary AND operator copies a bit to the result. | (a & B) will get 12, that is, 0000 1100 |
If it exists in any Operand, the binary OR operator copies a bit into the result. | (a | B) will get 61, that is, 0011 1101 | |
~ | The binary complement operator is a unary operator with a “flip” bit effect, that is, 0 becomes 1, 1 becomes 0. | (~ A) will get-61, that is, 1100 0011, a complement of signed binary numbers. |
< < | Binary left shift operator. The value of the left Operand moves the number of digits specified by the right Operand to the left. | A < < 2 will get 240, that is, 1111 0000 |
> > | Binary right shift operator. The value of the left Operand moves the number of digits specified by the right Operand to the right. | A > > 2 will get 15, that is 0000 1111 |
1.14.9. Example ¶
The following example demonstrates
SQLite
The use of bit operators:
sqlite> .mode line
sqlite> select 60 | 13;
60 | 13 = 61
sqlite> select 60 & 13;
60 & 13 = 12
sqlite> select (~60);
(~60) = -61
sqlite> select (60 << 2);
(60 << 2) = 240
sqlite> select (60 >> 2);
(60 >> 2) = 15