MySQL-WHERE子句

MySQL-WHERE子句 (MySQL - WHERE Clause)

We have seen the SQL SELECT command to fetch data from a MySQL table. We can use a conditional clause called the WHERE Clause to filter out the results. Using this WHERE clause, we can specify a selection criteria to select the required records from a table.

我们已经看到了SQL SELECT命令从MySQL表中获取数据。 我们可以使用称为WHERE子句的条件子句来过滤结果。 使用此WHERE子句，我们可以指定选择条件以从表中选择所需的记录。

句法 (Syntax)

The following code block has a generic SQL syntax of the SELECT command with the WHERE clause to fetch data from the MySQL table −

以下代码块具有SELECT命令的通用SQL语法和WHERE子句，可从MySQL表中获取数据-

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

• You can use one or more tables separated by a comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.

  您可以使用WHERE子句来使用一个或多个用逗号分隔的表来包含各种条件，但是WHERE子句是SELECT命令的可选部分。

• You can specify any condition using the WHERE clause.

  您可以使用WHERE子句指定任何条件。

• You can specify more than one condition using the AND or the OR operators.

  您可以使用AND或OR运算符指定多个条件。

• A WHERE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.

  WHERE子句可以与DELETE或UPDATE SQL命令一起使用，也可以指定条件。

The WHERE clause works like an if condition in any programming language. This clause is used to compare the given value with the field value available in a MySQL table. If the given value from outside is equal to the available field value in the MySQL table, then it returns that row.

WHERE子句的作用类似于任何编程语言中的if条件 。 该子句用于将给定值与MySQL表中可用的字段值进行比较。 如果外部的给定值等于MySQL表中的可用字段值，则它将返回该行。

Here is the list of operators, which can be used with the WHERE clause.

这是可与WHERE子句一起使用的运算符列表。

Assume field A holds 10 and field B holds 20, then −

假设字段A持有10，字段B持有20，则-

Operator	Description	Example
=	Checks if the values of the two operands are equal or not, if yes, then the condition becomes true.	(A = B) is not true.
!=	Checks if the values of the two operands are equal or not, if the values are not equal then the condition becomes true.	(A != B) is true.
>	Checks if the value of the left operand is greater than the value of the right operand, if yes, then the condition becomes true.	(A > B) is not true.
<	Checks if the value of the left operand is less than the value of the right operand, if yes then the condition becomes true.	(A < B) is true.
>=	Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes, then the condition becomes true.	(A >= B) is not true.
<=	Checks if the value of the left operand is less than or equal to the value of the right operand, if yes, then the condition becomes true.	(A <= B) is true.

操作员	描述	例
=	检查两个操作数的值是否相等，如果是，则条件为真。	(A = B)不正确。
！=	检查两个操作数的值是否相等，如果值不相等，则条件为真。	(A！= B)为真。
>	检查左操作数的值是否大于右操作数的值，如果是，则条件变为true。	(A> B)不正确。
<	检查左操作数的值是否小于右操作数的值，如果是，则条件为true。	(A <B)是真的。
> =	检查左操作数的值是否大于或等于右操作数的值，如果是，则条件为true。	(A> = B)不正确。
<=	检查左操作数的值是否小于或等于右操作数的值，如果是，则条件为true。	(A <= B)是正确的。

The WHERE clause is very useful when you want to fetch the selected rows from a table, especially when you use the MySQL Join. Joins are discussed in another chapter.

当您想从表中获取选定的行时，WHERE子句非常有用，尤其是当您使用MySQL Join时 。 连接将在另一章中讨论。

It is a common practice to search for records using the Primary Key to make the search faster.

使用主键搜索记录以加快搜索速度是一种常见的做法。

If the given condition does not match any record in the table, then the query would not return any row.

如果给定条件与表中的任何记录都不匹配，则查询将不返回任何行。

从命令提示符中获取数据 (Fetching Data from the Command Prompt)

This will use the SQL SELECT command with the WHERE clause to fetch the selected data from the MySQL table – tutorials_tbl.

这将使用带有WHERE子句SQL SELECT命令从MySQL表– tutorials_tbl中获取所选数据。

例 (Example)

The following example will return all the records from the tutorials_tbl table for which the author name is Sanjay.

下面的示例将返回tutorials_tbl表中所有记录的作者姓名为Sanjay 。

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl WHERE tutorial_author = 'Sanjay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      3      | JAVA Tutorial  |      Sanjay     |    2007-05-21   |      
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

Unless performing a LIKE comparison on a string, the comparison is not case sensitive. You can make your search case sensitive by using the BINARY keyword as follows −

除非对字符串执行LIKE比较，否则比较不区分大小写。 您可以使用BINARY关键字使搜索区分大小写，如下所示：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl \
   WHERE BINARY tutorial_author = 'sanjay';
Empty set (0.02 sec)

mysql>

使用PHP脚本获取数据 (Fetching Data Using a PHP Script)

You can use the same SQL SELECT command with the WHERE CLAUSE into the PHP function mysql_query(). This function is used to execute the SQL command and later another PHP function mysql_fetch_array() can be used to fetch all the selected data. This function returns a row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.

您可以在WHERE CLAUSE中使用相同SQL SELECT命令到PHP函数mysql_query()中 。 该函数用于执行SQL命令，以后可以使用另一个PHP函数mysql_fetch_array()来获取所有选定的数据。 此函数以关联数组和/或数字数组的形式返回一行。 如果没有更多行，此函数将返回FALSE。

例 (Example)

The following example will return all the records from the tutorials_tbl table for which the author name is Sanjay −

以下示例将返回tutorials_tbl表中所有记录的作者姓名为Sanjay的记录 -

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $sql = 'SELECT tutorial_id, tutorial_title, 
      tutorial_author, submission_date
      FROM tutorials_tbl
      WHERE tutorial_author = "Sanjay"';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
      "Title: {$row['tutorial_title']} <br> ".
      "Author: {$row['tutorial_author']} <br> ".
      "Submission Date : {$row['submission_date']} <br> ".
      "--------------------------------<br>";
   } 

   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

翻译自: https://www.tutorialspoint.com/mysql/mysql-where-clause.htm