SQLite operator

What SQLite operator is?

Operator is a reserved word or character, WHERE clause to perform operations is mainly used for SQLite statement, such as comparison and arithmetic.

Operator is used to specify the SQLite statement of condition and connecting a plurality of conditions in the statement.

• Arithmetic operators

• Comparison

• Logical Operators

• Bitwise Operators

SQLite arithmetic operators

Suppose the variable a = 10, the variable b = 20, then:

Examples

Here 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;
2 5 = 12%

SQLite comparison

Suppose the variable a = 10, the variable b = 20, then:

Examples

Suppose 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

The following example demonstrates the usage of various SQLite comparison.

Here, we usethe WHERE clause, which will be covered in a separate chapter in the back, but now you need to understand, the WHERE clause of the SELECT statement is used to set a conditional statement.

The following SELECT statement lists all the records SALARY is greater than 50,000.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

The following SELECT statement lists SALARY 20,000.00 equal to all of the records:

sqlite> SELECT * FROM COMPANY WHERE SALARY = 20000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0

The following SELECT statement lists the SALARY is not equal to all records of 20,000.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

The following SELECT statement lists the SALARY is not equal to all records of 20,000.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

The following SELECT statement lists the SALARY is greater than the sum of all records of 65,000.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

SQLite logical operators

Here is a list of logical operators SQLite all.

Examples

Suppose 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

The following example demonstrates the usage of SQLite logical operators.

The following SELECT statement lists AGE 25and greater than or equal greater than or equal wages for all records 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

The following SELECT statement lists AGE 25or greater than or equal greater than or equal wages for all records 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

The following SELECT statement lists the AGE is not NULL for all the records, all the records showed, it means there is no record of AGE is 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

Character following SELECT statement lists NAME to 'Ki' all records began, 'Ki' is no limit after:

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0

Character following SELECT statement lists NAME to 'Ki' all records began, 'Ki' is no limit after:

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 the records AGE value of 25 or 27:

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

The following SELECT statement lists the values ​​AGE is neither 25 nor 27 of 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

The following SELECT statement lists the values ​​AGE all records 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

The following SELECT statement uses SQL subqueries, subquery find SALARY> AGE field for all records with 65,000, behind the WHERE clause is used in conjunction with the EXISTS operator, lists outer query AGE presence in the sub-query returns results of all the records:

sqlite> SELECT AGE FROM COMPANY 
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY> 65000);
AGE
----------
32
25
twenty three
25
27
twenty two
twenty four

The following SELECT statement uses SQL subqueries, subquery find SALARY> AGE field for all records with 65,000, behind the WHERE clause and> operators are used together, the results of the query returns a list of the outer query is greater than the sub-AGE All records of the 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 Bitwise Operators

Bitwise operators acting on the bit, and bit by bit operation. Truth table & and | as follows:

Suppose if A = 60, and B = 13, now in a binary format, which is as follows:

A = 0011 1100

B = 0000 1101

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

A & B = 0000 1100

A | B = 0011 1101

~ A = 1100 0011

The following table lists the languages ​​supported by the SQLite bit operators. Suppose the variable A = 60, the variable B = 13, then:

Examples

The following example demonstrates SQLite bitwise usage:

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);
(2 << 60) = 240

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