WHERE clause

The following code block has the general syntax for the select query with the WHERE clause:

SELECT FIELD1, FIELD2, FIELD3,...FIELDn from TABLE_NAME1 ,TABLE_NAME2 [WHERE condition1 [AND [OR]] condition2...

The WHERE clause is an optional part of the SELECT command. You can use AND or OR to specify conditions. The WHERE clause can also be used with DELETE and UPDATE query, which we will discuss soon in this chapter.

Here is the list of operators used with the where clause for conditions. Let's take an example of a table schema to understand these operations. Let's create a table called users with schema mentioned here having id, first_name, last_name, address, city, state, zip, login_attempts, contact_number, email, username, and password:

CREATE TABLE `users` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `first_name` varchar(255),
 `last_name` varchar(255),
 `address` varchar(255),
 `city` varchar(50),
 `state` varchar(2),
 `zip` varchar(10),
`login_attempts` int(10),
 `contact_number` varchar(20),
 `email` varchar(191),
 `username` varchar(191),
 `password` varchar(255),
 PRIMARY KEY (`id`)
) ENGINE=InnoDB PRIMARY KEY (`id`) AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;