WHERE Clause
To conditionally select data from a table, a WHERE clause can be added to the SELECT statement. Run the query SELECT * FROM People WHERE ID = 'tjones':
+--------+-----------+------------+----+------------+ | ID | NAME_LAST | NAME_FIRST | MI | POSITION | +--------+-----------+------------+----+------------+ | tjones | Jones | Tyler | R | Technician | +--------+-----------+------------+----+------------+1 row in set (0.00 sec) mysql>
Note the single quotes around the WHERE Field value. If the WHERE Field value is a number, do not provide quotes.
INCORRECT:
With the WHERE Clause, these conditions can be used:
=
!= or <>
>
<
>=
<=
BETWEEN
LIKE
IS NOT NULL
IS NULL
REGEXP Condition
Equal
Not equal
Greater than
Less than
Greater than or equal
Less than or equal
Between an inclusive range
Explained below
address is not null
address is null
regular expression
LIKE Condition
The LIKE Condition is used to specify a search for a pattern in a column. The syntax is thus: SELECT fieldname1 FROM tablename WHERE fieldname2 LIKE pattern. A "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.
SELECT * FROM People WHERE NAME_LAST LIKE 'S%' to return records of people with a last name that starts with an 'S':
+--------+-----------+------------+----+----------+ | ID | NAME_LAST | NAME_FIRST | MI | POSITION | +--------+-----------+------------+----+----------+ | bsmith | Smith | Barbara | N | Clerk | | nsmith | Smith | Nancy | B | Manager | +--------+-----------+------------+----+----------+2 rows in set (0.00 sec) mysql>
SELECT * FROM People WHERE POSITION LIKE '%n' to return persons with a position that ends with a 'n'.
+--------+-----------+------------+----+------------+ | ID | NAME_LAST | NAME_FIRST | MI | POSITION | +--------+-----------+------------+----+------------+ | tjones | Jones | Tyler | R | Technician | +--------+-----------+------------+----+------------+1 row in set (0.00 sec) mysql>
SELECT * FROM People WHERE ID LIKE '%smi%' to return persons with an ID that contains the pattern 'smi'.
+--------+-----------+------------+----+----------+ | ID | NAME_LAST | NAME_FIRST | MI | POSITION | +--------+-----------+------------+----+----------+ | bsmith | Smith | Barbara | N | Clerk | | nsmith | Smith | Nancy | B | Manager | +--------+-----------+------------+----+----------+2 rows in set (0.01 sec) mysql>