SQLite’s WHERE Clause is used to specify the conditions for getting data from one or more tables.
Returns a specific value from the table if the given condition, that is, true, is met. You can use the
WHERE
Clause to filter records and get only the records you need.
You can also use the 比较或逻辑运算符 Specify conditions, such as >, <, =, LIKE, NOT, and so on. Hypothetical The following example demonstrates Below. Below. Below. Below. The following SELECT statement lists all records with a value of 25 or 27 for AGE: Below. Below. Below. Below.
WHERE
Clauses can not only be used in
SELECT
Statement, it can also be used in the
UPDATE
、
DELETE
Statements, and so on, which we will learn in the following chapters. 1.16.1. Grammar ¶
SQLite
With
WHERE
Of the clause
SELECT
The basic syntax of the statement is as follows:SELECT column1, column2, columnN
FROM table_name
WHERE [condition]
1.16.2. Example ¶
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
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
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
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
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
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
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
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
SELECT
Statement lists the
AGE
All records with values between 25 and 27sqlite> 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
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
SELECT
Statement use
SQL
Subquery, which finds all records with AGE fields for SALARY > 65000, followed by
WHERE
Clause is used with the > operator to list all records where the AGE in the external query is 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