An article explaining how to skillfully use the MySQL WHERE clause

藏色散人

Release： 2023-04-01 06:30:01

forward

1810 people have browsed it

This article brings you relevant knowledge about MySQL, which mainly introduces the use of MySQL WHERE clause. Friends who are interested can take a look at it together. I hope it will be helpful to everyone.

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.

Grammar

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.....

Copy after login

You can use a Or multiple tables, 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

##OperatorDescriptionExample=Equal sign, checks whether the two values are equal, and returns true if they are equal(A = B) Return false. <>, !=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.

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

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 kxdang_tbl:

Example

The following example will read all records in the kxdang_tbl table where the kxdang_author field value is Sanjay:

SQL SELECT WHERE clause

SELECT * from kxdang_tbl WHERE kxdang_author='菜鸟教程';

Copy after login

Output result:

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:

BINARY keyword

mysql> SELECT * from kxdang_tbl WHERE BINARY kxdang_author=&#39;kxdang.com&#39;;
Empty set (0.01 sec)
 
mysql> SELECT * from kxdang_tbl WHERE BINARY kxdang_author=&#39;RUNOOB.COM&#39;;
+-----------+---------------+---------------+-----------------+
| kxdang_id | kxdang_title  | kxdang_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | JAVA 教程   | RUNOOB.COM    | 2016-05-06      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

Copy after login

The example uses the

BINARY keyword, which is case-sensitive, so kxdang_author The query condition of ='kxdang.com' has no data.

Use PHP script to read data

You can use the PHP function mysqli_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 mysqli_fetch_array().

Example

The following example will return records using the kxdang_author field value as RUNOOB.COM from the kxdang_tbl table:

MySQL WHERE clause test:

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

Copy after login

Recommended learning: "

MySQL Video Tutorial"

The above is the detailed content of An article explaining how to skillfully use the MySQL WHERE clause. For more information, please follow other related articles on the PHP Chinese website!