 |
 |
 |
Module Reference |  |
 |
 |
|
|
|
Module Reference | |
|
|
The WHERE clause is used to specify a selection criterion.
The syntax of the where clause is:
SELECT column FROM table WHERE column operator value
With the WHERE clause, the following operators can be used:
Operator Description -------- ----------- = Equal <> Not equal > Greater than < Less than >= Greater than or equal <= Less than or equal LIKE Pattern match (described later) IS Used for comparison to NULL IS NOT Used for comparison to NULL
In some versions of SQL the <> operator may be written as != but not in web.database. Note that the equals operator in SQL is = not == as it is in Python.
The = and <> operators cannot be used to compare NULL values because a field cannot be equal to nothing. Instead the IS and IS NOT operators should be used.
To select only the people whose last name are Smith, we add a WHERE clause to the SELECT statement:
SELECT * FROM Person WHERE LastName='Smith'
Person table:
+----------+-----------+--------+-------------+ | LastName | FirstName | Number | DateOfBirth | +----------+-----------+--------+-------------+ | 'Smith' | 'John' | 10 | 1980-01-01 | | 'Doe' | 'John' | 3 | 1981-12-25 | +----------+-----------+--------+-------------+
Result set:
+----------+-----------+--------+-------------+ | LastName | FirstName | Number | DateOfBirth | +----------+-----------+--------+-------------+ | 'Smith' | 'John' | 10 | 1980-01-01 | +----------+-----------+--------+-------------+
Note that we have used single quotes around the conditional values in the examples.
SQL uses single quotes around text values (some database systems will also accept double quotes, not web.database). Numeric values should not be enclosed in quotes.
For text values:
This is correct:
SELECT * FROM Person WHERE LastName='Smith'
This is wrong:
SELECT * FROM Person WHERE LastName=Smith
For numeric values:
This is correct:
SELECT * FROM Person WHERE Number>10
This is wrong:
SELECT * FROM Person WHERE Number>'10'
The LIKE condition is used to specify a search for a pattern in a column.
SELECT column FROM table WHERE column LIKE pattern
A % sign can be used to define wildcards (missing letters in the pattern).
The following SQL statement will return people with first names that start with an 'O':
SELECT * FROM Person WHERE FirstName LIKE 'O%'
The following SQL statement will return people with first names that end with an 'a':
SELECT column FROM table WHERE FirstName LIKE '%a'
The following SQL statement will return people with first names that contain the pattern 'la':
SELECT column FROM table WHERE FirstName LIKE '%la%'
You can use as many % characters as you need in the pattern to match zero or more characters. If you need to have an actual % characters in the pattern you will need to escape it like this
.
%
The following SQL statement will return values that end with a % character.
SELECT column FROM table WHERE Percentage LIKE '%\%'
web.database does not support the BETWEEN condition since the same thing can be achieved using comparison operators.
|
|
|
Module Reference | |
|
|