MySQL WHERE clause

We know that the SQL SELECT statement is used to read the data from the MySQL table.

If you want to conditionally select data from a table, you can add a WHERE clause to the SELECT statement.

Grammar

The following is a general syntax for SQL SELECT statements to read data from a data table using a WHERE clause:

SELECT  from table_name1, table_name2 ... [WHERE condition1 [and [OR]] condition2 .....

• In a query statement you can use one or more tables, separate the tables with commas (,), and use the where statement to set the query criteria.
• You can specify any condition in the WHERE clause.
• You can specify one or more conditions by using and OR OR.
• The WHERE clause can also be applied to the SQL DELETE or UPDATE command.
• The WHERE clause is similar to the IF condition in a program language, and reads the specified data according to the field values in the MySQL table.

The following is a list of operators that can be used in the WHERE clause.

The example in the following table assumes A is 20 b

operator	Description	Example
=	Equals, detects if two values are equal, returns true if equal	(A = B) returns FALSE.
<>! =	does not equal, detects whether two values are equal if not equal returns True	(A! = B) returns TRUE.
>	Greater than sign, detects if the left value is greater than the right value, and returns True if the left value is greater than the right value	(A > B) returns FALSE.
<	Less than sign, detects if the left value is less than the right value, and returns True if the left value is less than the right value	(A < B) returns TRUE.
>=	Greater than equals sign, detects if the left value is greater than or equal to the right value, if the left value is greater than or equal to the right value returns True	(A >= B) returns false.
<=	Less than equals sign, detects if the left value is less than or equal to the right value, if the left value is less than or equal to the right value returns True	(A <= B) returns True.

The WHERE clause is useful if we want to read the specified data in the MySQL data table again.

A conditional query that uses a primary key as a WHERE clause is very fast.

If a given condition does not have any matching records in the table, then the query does not return any data.

reading data from the command prompt

We will use the WHERE clause in the SQL SELECT statement to read the data in MySQL data table tutorials_tbl:

Instance

The following instance reads all records in the TUTORIALS_TBL table that have the Tutorial_author field value of Sanjay:

Root@host# MySQL-U root-p password; Enter Password:*******MySQL>  Usetutorials;DatabaseChangedmysql> SELECT *  fromTutorials_tblWHERETutorial_author='Sanjay';+-------------+----------------+-----------------+-----------------+|tutorial_id|Tutorial_title|Tutorial_author|Submission_date|+-------------+----------------+-----------------+-----------------+|           3 |JAVA Tutorial|Sanjay|  -- to- +      |+-------------+----------------+-----------------+-----------------+1Rowsinch Set(0.01sec) MySQL>

Unless you use like to compare strings, the string comparison of the WHERE clause of MySQL is case insensitive. You can use the BINARY keyword to set the string comparison of where clauses to be case-sensitive.

The following example

Root@host# MySQL-U root-p password; Enter Password:*******MySQL>  Usetutorials;DatabaseChangedmysql> SELECT *  fromTutorials_tblWHERE BINARYTutorial_author='Sanjay'; EmptySet(0.02sec) MySQL>

reading data using PHP scripts

You can use the PHP function's mysql_query () and the same SQL SELECT command with the WHERE clause to get the data.

The function is used to execute the SQL command, and then the PHP function mysql_fetch_array () to output the data for all queries.

Instance

The following instance returns records from the TUTORIALS_TBL table that use the Tutorial_author field value of Sanjay:

<?PHP$dbhost= ' localhost:3036 ';$dbuser= ' Root ';$dbpass= ' Rootpassword ';$conn=mysql_connect($dbhost,$dbuser,$dbpass);if(!$conn ){   die(' Could not connect: '.Mysql_error());}$sql=SELECT tutorial_id, Tutorial_title, Tutorial_author, submission_date from Tutorials_tbl W Here tutorial_author= "Sanjay"‘;mysql_select_db(' Tutorials ');$retval=mysql_query($sql,$conn );if(!$retval ){   die(' Could not get data: '.Mysql_error());} while($row=Mysql_fetch_array($retval,Mysql_assoc)) {    Echo"Tutorial ID: {$row[' tutorial_id ']}         <br> ". "Title: {$row[' Tutorial_title ']}         <br> ". "Author: {$row[' Tutorial_author ']}         <br> ". "Submission Date: {$row[' Submission_date ']}         <br> ". "--------------------------------<br>";} Echo"Fetched Data successfully\n";Mysql_close($conn);?>

Original address: http://www.manongjc.com/mysql/mysql_where.html

Related reading:

• MySQL UPDATE Query
• MySQL DELETE Statement
• MySQL LIKE clause
• MySQL ORDER BY
• Use of Mysql Join
• MySQL NULL Value Processing

MySQL WHERE clause