How to use MySQL where statement? where clause multiple conditional syntax

MySQL databaseHow to use where statement? where clause multiple conditional syntax

MySQL WHERE clause

We know to use SQL SELECT statement to read data from MySQL table.

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

grammar

The following is the general syntax of a SQL SELECT statement to read data from a 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, using commas between tables, Split, and use the WHERE statement to set query conditions.
• 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's DELETE or UPDATE commands.
• The WHERE clause is similar to the if condition in procedural languages, and reads the specified data according to 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 table below assume that A is 10 and B is 20

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

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

If the given criteria do not have any matching records in the table, the query returns no data.

read data from command prompt

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

Examples

The following example will read all records in the chenweiliang_tbl table where the value of the chenweiliang_author field is Sanjay:

String comparisons in MySQL's WHERE clause are case-insensitive.You can use the BINARY keyword to specify that string comparisons in the WHERE clause are case-sensitive.

The following example:

mysql> SELECT * from chenweiliang_tbl WHERE BINARY chenweiliang_author='chenweiliang.com';
Empty set (0.01 sec)
 
mysql> SELECT * from chenweiliang_tbl WHERE BINARY chenweiliang_author='chenweiliang.com';
+-----------+---------------+---------------+-----------------+
| chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3 | JAVA 教程 | chenweiliang.com | 2016-05-06 |
| 4 | 学习 Python | chenweiliang.com | 2016-03-06 |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

used in the example BINARY keyword, is case-sensitive, so chenweiliang_author='chenweiliang.com' The query condition is no data.

Read data using PHP script

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 the queried data through the PHP function mysqli_fetch_array().

Examples

The following example will return from the chenweiliang_tbl table using the chenweiliang_author field value as chenweiliang.com record of:

MySQL WHERE clause test:

<?
php
$dbhost = 'localhost:3306'; // 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");
 
// 读取 chenweiliang_author 为 chenweiliang.com 的数据
$sql = 'SELECT chenweiliang_id, chenweiliang_title, 
 chenweiliang_author, submission_date
 FROM chenweiliang_tbl
 WHERE chenweiliang_author="chenweiliang.com"';
 
mysqli_select_db( $conn, 'chenweiliang' );
$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, MYSQL_ASSOC))
{
 echo "<tr><td> {$row['chenweiliang_id']}</td> ".
 "<td>{$row['chenweiliang_title']} </td> ".
 "<td>{$row['chenweiliang_author']} </td> ".
 "<td>{$row['submission_date']} </td> ".
 "</tr>";
}
echo '</table>';
// 释放内存
mysqli_free_result($retval);
mysqli_close($conn);
?>