Sqlite 简明教程
SQLite - WHERE Clause
SQLite WHERE 子句用于在从一个表或多个表中获取数据时指定条件。
SQLite WHERE clause is used to specify a condition while fetching the data from one table or multiple tables.
如果给定的条件得到满足，即为真，则从表中返回特定值。你必须使用 WHERE 子句来筛选记录并仅获取必需的记录。
If the given condition is satisfied, means true, then it returns the specific value from the table. You will have to use WHERE clause to filter the records and fetching only necessary records.
WHERE 子句不仅用于 SELECT 语句，还用于 UPDATE、DELETE 语句等，这些将在后面的章节中介绍。
The WHERE clause not only is used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which will be covered in subsequent chapters.
Syntax
以下是有 WHERE 子句的 SQLite SELECT 语句的基本语法。
Following is the basic syntax of SQLite SELECT statement with WHERE clause.
SELECT column1, column2, columnN
FROM table_name
WHERE [condition]Example
使用 Comparision or Logical Operators 可以指定一个条件，例如 >、<、=、LIKE、NOT 等。考虑具有以下记录的 COMPANY 表 -
You can specify a condition using Comparision or Logical Operators such as >, <, =, LIKE, NOT, etc. Consider COMPANY table with 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 逻辑运算符使用法的简单示例。以下 SELECT 语句列出所有 AGE 大于或等于 25 AND 并且 salary 大于或等于 65000.00 的记录。
Following is a simple examples showing the usage of SQLite Logical Operators. Following SELECT statement lists down all the 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 NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0以下 SELECT 语句列出了 AGE 大于或等于 25 OR 薪水大于或等于 65000.00 的所有记录。
Following SELECT statement lists down all the records where AGE is greater than or equal to 25 OR salary is 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 语句列出所有 AGE 不为 NULL 的记录，即所有记录，因为没有一条记录的 AGE 等于 NULL。
Following SELECT statement lists down all the records where AGE is not NULL, which means all the records because none of the record has 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 语句列出所有 NAME 以“Ki”开头的记录，后续部分无关紧要。
Following SELECT statement lists down all the records where NAME starts with 'Ki', does not matter what comes after 'Ki'.
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0以下 SELECT 语句列出所有 NAME 以“Ki”开头的记录，后续部分无关紧要。
Following SELECT statement lists down all the records where NAME starts with 'Ki', does not matter what comes after 'Ki'.
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0以下 SELECT 语句列出 AGE 值为 25 或 27 的所有记录。
Following SELECT statement lists down all the records where AGE value is either 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以下 SELECT 语句列出 AGE 值既不为 25 也不为 27 的所有记录。
Following SELECT statement lists down all the records where AGE value is neither 25 nor 27.
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 语语列出 AGE 值在 25 和 27 之间的记录。
Following SELECT statement lists down all the records where AGE value is in 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以下 SELECT 语句使用 SQL 子查询，子查询找到所有 AGE 字段具有 SALARY > 65000 的记录，然后 WHERE 子句与 EXISTS 运算符一起用于列出外部查询中的 AGE 在子查询返回的结果中存在的记录 -
Following SELECT statement makes use of SQL sub-query, where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with EXISTS operator to list down all the records where AGE from the outside query exists in the result returned by the sub-query −
sqlite> SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
AGE
----------
32
25
23
25
27
22
24以下 SELECT 语句使用 SQL 子查询，其中子查询找到所有 AGE 字段具有 SALARY > 65000 的记录，然后 WHERE 子句与 > 运算符一起用于列出外部查询中的 AGE 大于子查询返回的结果中的 AGE 的记录。
Following SELECT statement makes use of SQL sub-query where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with > operator to list down all the records where AGE from the outside query is greater than the age in the result returned by the sub-query.
sqlite> SELECT * FROM COMPANY
WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0