Mysql 简明教程
MySQL - WHERE Clause
MySQL WHERE Clause
我们知道 SQL SELECT 命令用于从 MySQL 表中获取记录。除此之外，我们还可以将称为 WHERE Clause 的条件子句与 SELECT 语句结合使用来过滤结果。使用此 WHERE 子句，我们可以指定选择条件以从表中选择所需的记录。
We know that the SQL SELECT command is used to fetch records from a MySQL table. In addition to that, we can also use a conditional clause called the WHERE Clause in conjunction with the SELECT statement to filter out the results. Using this WHERE clause, we can specify a selection criteria to select the required records from a table.
WHERE 子句的工作方式类似于任何编程语言中的 if condition 。此子句用于将给定值与 MySQL 表中可用的字段值进行比较。如果外部的给定值等于 MySQL 表中可用的字段值，则它会返回该行。
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.
Operators Used in WHERE Clause
以下是可用在 WHERE 子句中的比较运算符的列表。
Here is the list of comparison operators, which can be used with the WHERE clause.
-
=: Checks if the values of the two operands are equal or not, if yes, then the condition becomes true.
-
!=: Checks if the values of the two operands are equal or not, if the values are not equal then the condition becomes 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.
-
<: Checks if the value of the left operand is less than the value of the right operand, if yes then the condition becomes 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.
-
⇐: 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.
此外，WHERE 子句还可包含逻辑运算符，如 AND、OR 和 NOT。
Along with these, the WHERE clause can also contain logical operators, like AND, OR and NOT.
-
AND: If an AND operator is used in WHERE Clause with two conditions, the query will return true only if both the conditions are satisfied.
-
OR: If an OR operator is used in WHERE Clause with two conditions, the query will return true only if either of the conditions are satisfied.
-
NOT: If a NOT operator is used in WHERE Clause with a condition, the query will return true only if the table records does not satisfy the condition.
Fetching Data Using Where Clause
当您想要从表中获取所选行时，WHERE 子句非常有用，尤其是在使用 MySQL Join 时。联接将在另一章中讨论。
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.
如果给定条件与表中的任何记录都不匹配，则该查询将不会返回任何行。
If the given condition does not match any record in the table, then the query would not return any row.
Syntax
以下是带有 WHERE 子句的 SELECT 命令的通用 SQL 语法，用于从 MySQL 表中获取数据：
Following is the generic SQL syntax of the SELECT command with the WHERE clause to fetch data from the MySQL table −
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.
-
You can specify any condition using the WHERE clause.
-
You can specify more than one condition using the AND or the OR operators.
-
A WHERE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.
Example
首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表 −
Firstly, let us create a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);以下查询将 7 条记录插入到上述创建的表中：
The following query inserts 7 records into the above-created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );执行以下查询以获取 CUSTOMERS 表的所有记录：
Execute the below query to fetch all the records of CUSTOMERS table −
Select * From CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
现在，让我们使用 MySQL WHERE 子句结合 SELECT 语句，获取 AGE 大于 23 的 CUSTOMERS：
Now, let us fetch the CUSTOMERS whose AGE is greater than 23 using the MySQL WHERE clause in conjunction with SELECT statement −
Select * From CUSTOMERS Where AGE > 23;WHERE Clause Using a Client Program
除了使用 MySQL Where 子句来从表中获取选定的行，我们还可以使用 PHP、Node.js、Java 和 Python 等客户端程序来实现相同的结果。
Besides using MySQL Where clause to fetch the selected rows from a table, we can also use client programs like PHP, Node.js, Java, and Python to achieve the same result.