Mariadb 简明教程

MariaDB - Where Clause

WHERE 子句过滤各种语句,例如 SELECT、UPDATE、DELETE 和 INSERT。它们提供了用于指定动作的条件。它们通常出现在语句中的表名之后,其条件随之而来。WHERE 子句本质上就像一个 if 语句。

WHERE clauses filter various statements such as SELECT, UPDATE, DELETE, and INSERT. They present criteria used to specify action. They typically appear after a table name in a statement, and their condition follows. The WHERE clause essentially functions like an if statement.

查看下面给出的 WHERE 子句的一般语法 −

Review the general syntax of WHERE clause given below −

[COMMAND] field,field2,... FROM table_name,table_name2,... WHERE [CONDITION]

注意 WHERE 子句的以下特性 −

Note the following qualities of the WHERE clause −

  1. It is optional.

  2. It allows any condition to be specified.

  3. It allows for the specification of multiple conditions through using an AND or OR operator.

  4. Case sensitivity only applies to statements using LIKE comparisons.

WHERE 子句允许使用以下运算符 −

The WHERE clause permits the use of the following operators −

Operator

= !=

> <

>= ⇐

WHERE 子句可以在命令提示符或 PHP 脚本中使用。

WHERE clauses can be utilized at the command prompt or within a PHP script.

The Command Prompt

在命令提示符下,只需使用标准命令 −

At the command prompt, simply use a standard command −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> SELECT * from products_tbl WHERE product_manufacturer = 'XYZ Corp';
+-------------+----------------+----------------------+
| ID_number   | Nomenclature   | product_manufacturer |
+-------------+----------------+----------------------+
| 12345       | Orbitron 4000  | XYZ Corp             |
+-------------+----------------+----------------------+
| 12346       | Orbitron 3000  | XYZ Corp             |
+-------------+----------------+----------------------+
| 12347       | Orbitron 1000  | XYZ Corp             |
+-------------+----------------+----------------------+

使用 AND 条件查看示例 −

Review an example using the AND condition −

SELECT *
FROM products_tbl
WHERE product_name = 'Bun Janshu 3000';
AND product_id <= 344;

此示例结合 AND 和 OR 条件

This example combines both AND and OR conditions

SELECT *
FROM products_tbl
WHERE (product_name = 'Bun Janshu 3000' AND product_id < 344)
OR (product_name = 'Bun Janshu 3000');

PHP Scripts Using Where Clause

在使用 WHERE 子句的操作中使用 mysql_query() 函数 −

Employ the mysql_query() function in operations using a WHERE clause −

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

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

   $sql = 'SELECT product_id, product_name, product_manufacturer, ship_date
      FROM products_tbl
      WHERE product_manufacturer = "XYZ Corp"';

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

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

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Product ID :{$row['product_id']} <br> ".
         "Name: {$row['product_name']} <br> ".
         "Manufacturer: {$row['product_manufacturer']} <br> ".
         "Ship Date: {$row['ship_date']} <br> ".
         "--------------------------------<br>";
   }

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

当数据检索成功时,您将看到以下输出 −

On successful data retrieval, you will see the following output −

Product ID: 12345
Nomenclature: Orbitron 4000
Manufacturer: XYZ Corp
Ship Date: 01/01/17
----------------------------------------------
Product ID: 12346
Nomenclature: Orbitron 3000
Manufacturer: XYZ Corp
Ship Date: 01/02/17
----------------------------------------------
Product ID: 12347
Nomenclature: Orbitron 1000
Manufacturer: XYZ Corp
Ship Date: 01/02/17
----------------------------------------------
mysql> Fetched data successfully