Mysql 简明教程
MySQL - AND Operator
MySQL AND Operator
在 MySQL 中，没有内置布尔类型。相反，布尔值使用数值数据类型表示，其中零被视为 false ，任何非零值被视为 true 。
In MySQL, there isn’t a built-in Boolean type. Rather, the Boolean values are represented using numeric data types, where zero is considered false and any non-zero value is considered true.
MySQL AND 运算符是一个逻辑运算符，可组合两个或多个布尔表达式并返回 1、0 或 NULL：
The MySQL AND operator is a logical operator that combines two or more Boolean expressions and returns 1, 0, or NULL:
A AND B此处， A 和 B 是运算数。
Here, A and B are operands.
-
The AND operator will return true (1) only if both A and B are non-zero and not Null.
-
If either A or B is false, the AND operator will return false (0).
-
If either A or B is NULL, the AND operator will return NULL.
下表演示了使用 AND 运算符组合真、假和空值的可能结果：
The following table below demonstrates the possible outcomes of using the AND operator to combine true, false, and null values:
Example
如果 A 和 B 均为非零且 NOT NULL，逻辑 AND 运算符返回 1 −
The logical AND operator returns 1 if both A and B are non-zero and NOT NULL −
SELECT 1 AND 1;Example
逻辑 AND 运算符在 A 或 B 为零，或 A 和 B 均为零时返回 0。
The logical AND operator returns 0 if either A or B is zero, or if both A and B are zero.
SELECT 1 AND 0, 0 AND 1, 0 AND 0, 0 AND NULL;AND Operator with WHERE
MySQL AND 运算符可与 WHERE 子句配合使用，仅检索满足所有指定条件的行。当使用 AND 运算符时，必须满足两个条件，才能将行包含在结果集中。否则，它将返回一个空集。
The MySQL AND operator can be used with the WHERE clause to retrieve only the rows that meet all the specified conditions. When the AND operator is used, both conditions must be true for a row to be included in the result set. Else, it returns an empty set.
Syntax
以下是 MySQL 中 AND 运算符与 WHERE 子句的语法 −
Following is the syntax of the AND operator with WHERE clause in MySQL −
SELECT column1, column2, ..., columnN
FROM table_name
[WHERE condition1 AND condition2 AND condition3 ...;]Example
首先，让我们使用以下查询创建名为 CUSTOMERS 的 MySQL 表 −
Firstly, let us create a MySQL table named CUSTOMERS using the below 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 rows 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 following query to fetch all the records from the CUSTOMERS table −
SELECT * FROM CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
现在，让我们从 ADDRESS 是“Hyderabad”且 AGE 是 22 的 CUSTOMERS 表中选择所有列。
Now, let us select all the columns from the CUSTOMERS table where the ADDRESS is 'Hyderabad' and AGE is 22.
SELECT * FROM CUSTOMERS
WHERE ADDRESS = "Hyderabad" AND AGE = 22;Example
逻辑 AND 运算符仅在由 AND 分开的条件都为 true 时返回记录。
The logical AND operator returns the records only if all the conditions separated by AND are true.
在以下查询中，我们为 AND 操作数之一提供了错误值。
In the following query, we are providing a false value to one of the AND operands.
SELECT * FROM CUSTOMERS
WHERE ADDRESS = "Kerala" AND AGE = 27;Multiple AND Operators
在 MySQL 中，我们可以在一个查询中使用多个 AND 运算符，以将多个条件或表达式组合在一起。与这些多个“AND”运算符组合的条件从左到右进行求值。如果任何条件求值为 false，则整个条件将为 false，并且不会将记录包含在结果集中。
In MySQL, we can use multiple AND operators in a query to combine multiple conditions or expressions together. Conditions combined with these multiple 'AND' operators are evaluated from left to right. If any of the conditions evaluate to false, the entire condition will be false and the record will not be included in the result set.
Example
在以下查询中，我们从 CUSTOMERS 表中选择所有 NAME 以“k”开头， AGE 大于或等于 22，而 SALARY 小于 3742 的记录。
In the following query, we are selecting all records from the CUSTOMERS table where the NAME starts with "k", AGE is greater than or equal to 22, and SALARY is less than 3742.
SELECT * FROM CUSTOMERS
WHERE NAME LIKE 'k%' AND AGE >= 22 AND SALARY < 3742;AND with UPDATE statement
在 MySQL 中，我们可以在 UPDATE 语句中使用 AND 运算符，以基于提供的多个条件从表中更新记录。
In MySQL, we can use the AND operator in an UPDATE statement to update records from a table based on provided multiple conditions.
Syntax
以下是 MySQL 中 AND 运算符与 UPDATE 语句的语法 −
Following is the syntax of the AND operator with the UPDATE statement in MySQL −
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition1 AND condition2 AND ...;Example
在以下查询中，我们正在更新 ID 为 5 且 ADDRESS 为“Hyderabad”的客户的 SALARY −
In the following query, we are updating the SALARY of CUSTOMERS whose ID is 5 and ADDRESS is 'Hyderabad' −
UPDATE CUSTOMERS
SET SALARY = 15000
WHERE ID = 6 AND ADDRESS = "Hyderabad";Output
以上查询的输出如下所示：
The output for the query above is produced as given below −
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0Verification
使用以下查询，我们可以验证 CUSTOMERS 的 SALARY 是否已更新：
Using the below query, we can verify whether the SALARY of CUSTOMERS is updated or not −
SELECT * FROM CUSTOMERS;正如我们可以在下面的 CUSTOMERS 表中看到的那样，ID 为 5 的客户的薪水已更新 −
As we can see the CUSTOMERS table below, the salary of customer with ID 5 has updated −
AND with DELETE Statement
在 MySQL 中，我们可以在 DELETE 语句中使用 AND 运算符，以基于多个条件从表中删除记录。
In MySQL, we can use the AND operator in a DELETE statement to remove records from a table based on multiple conditions.
Syntax
以下是 MySQL 中 AND 运算符与 DELETE 语句的语法 −
Following is the syntax of AND operator with the DELETE statement in MySQL −
DELETE FROM table_name
WHERE condition1 AND condition2 AND condition3 ...Example
在这个查询中，我们从 CUSTOMERS 表中删除其中 NAME 等于“Khilan”，ADDRESS 等于“Delhi”的记录 −
In this query, we are deleting records from the CUSTOMERS table where the NAME is equal to 'Khilan' and ADDRESS is equal to 'Delhi' −
DELETE FROM CUSTOMERS
WHERE NAME = "Khilan" AND ADDRESS = "Delhi";AND Operator Using a Client Program
除了使用 MySQL 查询执行 AND 运算符之外，我们还可以使用 Node.js、PHP、Java 和 Python 等客户端程序来实现相同的结果。
Besides using MySQL queries to perform the AND operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.