4.2.14. MySQL WHERE clause

We know that SQL SELECT statements are used to read data from the MySQL table.

To conditionally select data from a table, you can set the WHERE Clause added to the SELECT Statement.

Grammar ¶

The following is the use of the SQL SELECT statement WHERE Clause to read data from a data table:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

You can use one or more tables in the query, using commas, splits, and WHERE Statement to set the query conditions.
You can do it in the WHERE Any condition is specified in the.
You can specify one or more conditions using AND or OR.
The WHERE clause can also be applied to the SQL DELETE Or UPDATE Orders.
The WHERE clause is similar to the if condition in a program language, reading 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 that An is 10 and B is 20.

Operator	Description	Example
`=`	Equal sign, checks whether two values are equal, and returns true if they are equal	(a = B) returns false.
`<>` , `!=`	Not equal. Check whether the two values are equal. If not, return true.	(a! = B) returns true.
`>`	Greater than sign, checks whether 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.
`<`	Less than sign, check whether the value on the left is less than the value on the right, and return true if the value on the left is less than the value on the right	(a < B) returns true.
`>=`	Greater than or equal to sign, detects 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 detects 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.

If we want to read the specified data in the MySQL data table WHERE Clauses are very useful.

Use the primary key as the WHERE The conditional query for the clause is very fast.

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

Read data from a command prompt ¶

We will use the WHERE Clause to read the MySQL data table runoob_tbl Data in:

Example

The following example reads runoob_tbl In the table runoob_author The field value is Sanjay All records of:

SQL SELECT WHERE clause

SELECT\*fromrunoob_tblWHERErunoob_author='菜鸟教程';

Output result:

MySQL’s WHERE The string comparison of a clause is case-insensitive. You can use the BINARY keyword to set the WHERE The string comparison of clauses is case-sensitive.

The following is an example:

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';
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | JAVA 教程   | RUNOOB.COM    | 2016-05-06      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

The BINARY keyword is used in the instance, which is case-sensitive, so the query condition of runoob_author=’runoob.com’ has no data.

You can use the PHP function to read data using the PHP script. mysqli_query() And the same SQL SELECT belt. WHERE Clause to get the data.

This function is used to execute the SQL command, and then through the PHP function mysqli_fetch_array() To output data for all queries.

Example ¶

The following example will be derived from the runoob_tbl Return to use in the table runoob_author Records with field values of RUNOOB.COM:

MySQL WHERE clause test:

<?php
$dbhost = 'localhost';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");

// 读取 runoob_author 为 RUNOOB.COM 的数据
$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('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>菜鸟教程 MySQL WHERE 子句测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><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 output is as follows: