MySQL where child clause

MySQL WHERE clause

We know that we use the SQL SELECT statement to read data from a MySQL table.

To conditionally select data from a table, add a WHERE clause to the SELECT statement.

Syntax

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

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

You can use one or more tables in the query statement. Use commas (,) to separate the tables, and use the WHERE statement to set query conditions.
You can specify any condition in the WHERE clause.
You can use AND or OR to specify one or more conditions.
The WHERE clause can also be applied to SQL DELETE or UPDATE commands.
The WHERE clause is similar to the if condition in programming language, reading the specified data based on the field value 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

##<>, !=Not equal, check whether the two values are equal, if they are not equal, return true(A != B) Return true. >The greater than sign checks whether the value on the left is greater than the value on the right. If the value on the left is greater than the value on the right, it returns true( A > B) returns false. <Less than sign, detects 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. >=Greater than or equal 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. <=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 specified data in the MySQL data table, the WHERE clause is very useful.

Using the primary key as a conditional query in the WHERE clause is very fast.

If the given criteria does not have any matching records in the table, the query will 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 the MySQL data table php_tbl:

Example

The following example will read all records in the user table where the user_author field value is PHP:

mysql> SELECT * from user WHERE user_author='PHP';

Image 9.jpg

The string comparison of MySQL's WHERE clause is not case-sensitive. You can use the BINARY keyword to make string comparisons in the WHERE clause case-sensitive.

The following example

root@host# mysql -u root -p password;
Enter password:****
mysql> use demo;
Database changed
mysql> SELECT * from user \
          WHERE BINARY user_author='php';
Empty set (0.02 sec)
mysql>

example uses the BINARY keyword, which is case-sensitive, so the query condition for user_author='php' has no data of.

Use PHP script to read data

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

This function is used to execute SQL commands and then output all queried data through the PHP function mysql_fetch_array().

Example

The following example will return records using the user_author field value as PHP from the php_tbl table:

<?php
header("Content-Type: text/html;charset=utf-8");

$dbhost = 'localhost';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = 'root';          // mysql用户名密码       
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
// 读取 user_author 为 user.COM 的数据
$sql = 'SELECT user_id, user_title, 
        user_author, submission_date
        FROM user
        WHERE user_author="PHP"';
 
mysqli_select_db( $conn, 'demo' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>PHP中文网 MySQL WHERE 子句测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
    echo "<tr><td> {$row['user_id']}</td> ".
         "<td>{$row['user_title']} </td> ".
         "<td>{$row['user_author']} </td> ".
         "<td>{$row['submission_date']} </td> ".
         "</tr>";
}
echo '</table>';
// 释放内存
mysqli_free_result($retval);
mysqli_close($conn);
?>

Image 10.jpg