1.16. SQLite Where clause

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.

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 ¶

You can also use the 比较或逻辑运算符 Specify conditions, such as >, <, =, LIKE, NOT, and so on. 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 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 