grammar
Here is the general syntax for SQL SELECT statements that use the WHERE clause to read data from a data table:
SELECT field1, field2,... fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2.....Copy the code- You can use one or more tables in a query, using commas, separations, and WHERE statements to specify the query criteria.
- You can specify any condition in the WHERE clause.
- You can specify one OR more conditions using AND OR OR.
- The WHERE clause can also be used with SQL DELETE or UPDATE commands.
- The WHERE clause is similar to the if condition in the programming language and reads the specified data based on the field values in the MySQL table.
The following is a list of operators that can be used in the WHERE clause.
The examples in the following table assume that A is 10 and B is 20
| The operator | describe | The instance |
|---|---|---|
| = | Equal sign, checks if two values are equal, and returns true if they are | Return false. |
| The < > and! = | Not equal: Checks if two values are equal, and returns true if they are not | (A ! = B) returns true. |
| > | Greater than checks if the value on the left is greater than the value on the right, and returns true if the value on the left is greater than the value on the right | A > B returns false. |
| < | The less than sign checks whether the value on the left is less than the value on the right, and returns true if the value on the left is less than the value on the right | (A < B) returns true |
| > = | The greater-than or equal sign checks whether the value on the left is greater than or equal to the value on the right, and returns true if the value on the left is greater than or equal to the value on the right | (A >= B) returns false. |
| < = | The less than or equal sign checks whether the value on the left is less than or equal to the value on the right, and returns true if the value on the left is less than or equal to the value on the right | (A <= B) returns true. |
The WHERE clause is useful if we want to read the specified data in a MySQL table.
Using a primary key as a conditional query for a WHERE clause is very fast.
If a given condition does not have any matching records in the table, the query does not return any data.
Reads data from a command prompt
SELECT * from runoob_tbl; SELECT * from runoob_tbl;
The instance
The following example will read all records in the ruNOob_tbL table where the ruNOob_author field value is Sanjay:
SQL SELECT WHERE clause
SELECT * from runoob_tbl WHERE runoob_author=’ ‘;
Output result:
The string comparison of MySQL’s WHERE clause is case insensitive. You can use the BINARY keyword to specify that string comparisons in the WHERE clause are case sensitive.
Examples are as follows:
BINARY keyword
mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author=’runoob.com’; Empty set (0.01 SEC) mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author=’RUNOOB.COM’; Empty set (0.01 SEC) mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author=’RUNOOB.COM’; +———–+—————+—————+—————–+ | runoob_id | runoob_title | runoob_author | Submission_date | + — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — — + | 3 | | | RUNOOB.COM JAVA tutorial The 2016-05-06 | | | | 4 learning Python RUNOOB.COM | | + — — — — — 2016-03-06 — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — — + 2 rows in set (0.01sec)
The BINARY keyword is case sensitive, so runoob_author=’runoob.com’ has no data.
Read the data using a PHP script
You can retrieve data using the PHP function mysqli_query() and the same SQL SELECT command with the WHERE clause.
This function is used to execute SQL commands and then output all the queried data through the PHP function mysqli_fetch_array().
The instance
The following example will return a record from the RUnoob_tbL table with the runoob_author field value RUNOOB.COM:
MySQL > select * from ‘WHERE’;
<? php $dbhost = ‘localhost’; $dbuser = ‘root’; $dbpass = ‘123456’; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(‘ connection failed: ‘. Mysqli_error ($conn)); Mysqli_query ($conn, “set names utf8”); $SQL = ‘SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl WHERE runoob_author=”RUNOOB.COM”‘; mysqli_select_db( $conn, ‘RUNOOB’ ); $retval = mysqli_query( $conn, $sql ); if(! $retval) {die(‘ cannot read data: ‘.mysqli_error ($conn)); } echo ‘<h2> ‘; Echo ‘< table border = “1” > < tr > < td > tutorial ID < / td > < td > title < / td > < / td > < td > the author submit date < / td > < td > < / tr >’; while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC)) { echo “<tr><td> {$row[‘runoob_id’]}</td> “. “<td>{$row[‘runoob_title’]} </td> “. “<td>{$row[‘runoob_author’]} </td> “. “<td>{$row[‘submission_date’]} </td> “. “</tr>”; } echo ‘</table>’; Mysqli_free_result ($retval); mysqli_close($conn); ? >
The following output is displayed:
