SQLite classic ...login
简体中文（ZH-CN）English（EN）繁体中文（ZH-TW）日本語（JA）한국어（KO）Melayu（MS）Français（FR）Deutsch（DE）
SQLite classic tutorial
←
→
author：php.cn  update time：2022-04-13 17:05:02

SQLite where clause


SQLite's WHERE clause is used to specify conditions for obtaining data from one table or multiple tables.

If the given condition is met, that is, true, a specific value is returned from the table. You can use the WHERE clause to filter records and get only the ones you need.

The WHERE clause is not only used in SELECT statements, it can also be used in UPDATE, DELETE statements, etc., which we will learn in subsequent chapters.

Syntax

The basic syntax of SQLite’s SELECT statement with WHERE clause is as follows: 

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

Example

You can also useComparison or logical operatorsSpecify conditions, such as >, <, =, LIKE, NOT, etc. Suppose that the COMPANY table has the following records: -----------------------------------------------------------------------19# 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 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 SEL below The ECT statement lists the AGE All records greater than or equal to 25

or salary greater than or equal to 65000.00:

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
ID Name --------#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


##
←
→

php.cn

HomeHomeCourseCourseArticleArticleQ&AQ&ADictionaryDictionaryManualManualDownloadDownloadsearchsearchAPPDownload APPDownload