SQLite operators

What are SQLite operators? The

operator is a reserved word or character, mainly used to perform operations in the WHERE clause of SQLite statements, such as comparisons and arithmetic operations.

Operators are used to specify conditions in SQLite statements and connect multiple conditions in a statement.

Arithmetic operators
Comparison operators
Logical operators
Bitwise operators

SQLite arithmetic operators

Assume variable a=10, variable b=20, then:

Operator	Description	Example
+	Addition - operator Adding the values on both sides	a + b will give 30
-	Subtraction - left operand minus right operand	a - b will get -10
*	Multiplication - multiply the values on both sides of the operator	a * b will get 200
/	Division - Dividing the left operand by the right operand	b / a will give 2
%	Modulo - the remainder obtained after dividing the left operand by the right operand	b % a will give 0

Example

The following is a simple example of SQLite arithmetic operators:

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

SQLite comparison operator

Assume variable a=10, variable b=20, then:

Operator#===!=<>><>=<=!<!>

Example

Assume that the COMPANY table has the following records:

ID ID name ------------------------------------------------------------------------------------------------—
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 . 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 H ouston 10000.0

The following examples demonstrate the usage of various SQLite comparison operators.

Here, we use the WHERE clause, which will be explained in a separate chapter later, but for now you need to understand that the WHERE clause is used to set the conditions of the SELECT statement statement.

The following SELECT statement lists all records with SALARY greater than 50,000.00:

##sqlite> SELECT * FROM COMPANY WHERE SALARY > 50000;

ID SALARY
---------- -------- -------- -------- -------- --
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0

The following SELECT statement lists the SELECT statements All records where ALARY is equal to 20,000.00:

SQLite & GT; Select*from Company where salary = 20000;

ID name Age address salary ##:4 ---------------------------------------------------— ## 1 Paul 32 California 20000.0
3 Teddy 23 NORWAY 20000.0

The following SELECT statement lists all records where SALARY is not equal to 20,000.00:

##sqlite> SELECT * FROM COMPANY WHERE SALARY != 20000;

ID ALARY
---------- ---------- ---------- ---------- ----------
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

The following SELECT statement lists all records where SALARY is not equal to 20,000.00:

##sqlite> SELECT * FROM COMPANY WHERE SALARY <> 20000;

ID 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 H ouston 10000.0

The following SELECT statement lists all records with SALARY greater than or equal to 65,000.00:

##sqlite> SELECT * FROM COMPANY WHERE SALARY >= 65000;

ID SALARY

---------- ---------- ---------- ---------- ------- ---
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0

SQLite logical operators

The following is a list of all logical operators in SQLite.

Description	Example
Checks whether the values of the two operands are equal, if so the condition is true.	(a == b) is not true.
Checks whether the values of the two operands are equal. If they are equal, the condition is true.	(a = b) is not true.
Checks whether the values of the two operands are equal. If they are not equal, the condition is true.	(a != b) is true.
Checks whether the values of the two operands are equal. If they are not equal, the condition is true.	(a <> b) is true.
Checks whether the value of the left operand is greater than the value of the right operand, if so, the condition is true.	(a > b) is not true.
Checks whether the value of the left operand is less than the value of the right operand, 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, if so, the condition is true.	(a >= b) is not true.
Check whether the value of the left operand is less than or equal to the value of the right operand, if so, the condition is true.	(a <= b) is true.
Checks whether the value of the left operand is not less than the value of the right operand, if so, the condition is true.	(a !< b) is false.
Checks whether the value of the left operand is not greater than the value of the right operand, if so, the condition is true.	(a !> b) is true.

Operator	Description
AND	AND operator allows The presence of multiple conditions in the WHERE clause of the statement.
BETWEEN	The BETWEEN operator is used to search for a value within a range of values within a given minimum and maximum range.
EXISTS	The EXISTS operator is used to search for the presence of rows in a specified table that meet certain conditions.
IN	The IN operator compares a value to a specified list of values.
NOT IN	The opposite of the IN operator, used to compare a value with values that are not in a specified list.
LIKE	The LIKE operator compares a value to similar values using the wildcard operator.
GLOB	The GLOB operator compares a value to similar values using the 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, etc. It is a negation operator.
OR	The OR operator is used to combine multiple conditions in the WHERE clause of a SQL statement.
IS NULL	The NULL operator compares a value to a NULL value.
IS	The IS operator is similar to =.
IS NOT	IS NOT operator is similar to !=.
\|\|	Connect two different strings to get a new string.
UNIQUE	The UNIQUE operator searches every row in the specified table for uniqueness (no duplicates).

Example

Assume that the COMPANY table has the following records:

The following example demonstrates the usage of SQLite logical operators.

The following SELECT statement lists all records where AGE is greater than or equal to 25 and salary is greater than or equal to 65000.00:

sqlite> SELECT * FROM COMPANY WHERE AGE > ;= 25 AND SALARY >= 65000;
ID ----------------------------------------------------------------------------------------—
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0

#- The SELECT statement lists all records with AGE greater than or equal to 25

salary greater than or equal to 65000.00:

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

ID --- ----------

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

The following SELECT statement lists all records whose AGE is not NULL. The result displays all records, which means that no record has an AGE equal to NULL:

sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
ID NAME AGE ADDRESS SALARY
----------- ----------- ---------- ---- ------ ----------
1 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 SELECT statement lists all records whose NAME starts with 'Ki'. The characters after 'Ki' are not restricted:

##sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki %';

ID ---- ----------
6 Kim 22 South-Hall 45000.0

The following SELECT statement lists all records whose NAME starts with 'Ki', The characters after 'ki' do not limit the characters:

## Sqlite & GT; Select*from Company where name glob 'ki*'; ---- ---------- ---------- ---------- ----------

6 Kim 22 South-Hall 45000.0

The following SELECT statement lists all records with an AGE value of 25 or 27:

sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

ID ------ ----------

2 David 27 Texas 85000.0

The following SELECT statement lists all records whose AGE value is neither 25 nor 27:

sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
ID name Age address salary
----------------------------------------------------------------------------------- -----------------1#1 PAUL 32 California 20000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 SOUTH-HALL 45000.0 ## 7 James 24 HOUSTON 1000000.0
## This ##The following SELECT statement lists all records with AGE 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

The following SELECT statement uses a SQL subquery. The subquery searches for all records with AGE fields in SALARY > 65000. The following WHERE clause is used with the EXISTS operator to list the AGEs in the outer query that exist in All records 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

The following SELECT statement Use SQL subquery. The subquery finds all records with AGE field that are SALARY > 65000. The following WHERE clause is used with the > operator to list the AGE in the outer query that is greater than the results returned by the subquery. All records of age:

sqlite> SELECT * FROM COMPANY

WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 CALIFORNIA 20000.0

# Sqlite bit operator

The operating symbol is used to be bit, and the operation will be performed one by one. The truth table & and | are as follows:

##p0011

Suppose if A = 60, and B = 13, now in binary format, they look like this:

A = 0011 1100

B = 0000 1101

------------------

A&B = 0000 1100

A|B = 0011 1101

~A = 1100 0011

The following table lists the bit operators supported by the SQLite language. Assume variable A=60 and variable B=13, then:

q	p & q	p \| q
0	0	0
1	0	1
1	1	1
0	0	1

##~The two's complement operator is a unary operator with " Flip" bit effect. (~A ) will get -61, which is 1100 0011, 2's complement, signed binary number. <<Binary left shift operator. The value of the left operand is shifted left by the number of bits specified by the right operand. A << 2 will get 240, which is 1111 0000##>>##Example

Operator	Description	Instance
&	The binary AND operator copies one bit to the result if both operands are present at the same time.	(A & B) will get 12, which is 0000 1100
\|	If present in any operand, binary OR operation character is copied into the result.	(A \| B) will get 61, which is 0011 1101


Binary right shift operator. The value of the left operand is shifted to the right by the number of bits specified by the right operand.	A >> 2 will get 15, which is 0000 1111

The following example demonstrates SQLite bit operations Usage of symbols:

sqlite> .mode line

sqlite> select 60 | 13;

60 | 13 = 61

sqlite> select 60 & 13;
60 & 13 = 12

sqlite> select 60 ^ 13;
10 * 20 = 200

sqlite> select (~60);
(~ 60) = -61

sqlite> select (60 << 2);
(60 << 2) = 240

sqlite> select (60 >> ; 2);
(60 >> 2) = 15

← SQLite Select statement

SQLite expression →

submit

SQLite operators

What are SQLite operators? The

SQLite arithmetic operators

Example

Assume variable a=10, variable b=20, then:

Example

SQLite logical operators

Example

php.cn