Sql 简明教程
SQL - AND and OR Conjunctive Operators
操作符是主要用于对数据执行各种运算（如加法 (+)、减法 (-) 或比较 (==)）的 SQL 保留字。
Operators are reserved words primarily used in SQL to perform various operations on data, like addition (+), subtraction (-), or comparison (==).
连接运算符特别用于布尔逻辑，在 SQL 语句中组合两个条件。最常见的连接运算符是：AND (&&)，如果两个条件都为真则返回真；OR (||)，如果至少一个条件为真则返回真。
Conjunctive operators, specifically used in boolean logic, combines two conditions in an SQL statement. The most common conjunctive operators are: AND (&&), which returns true if both conditions are true, and OR (||), which returns true if at least one condition is true.
The SQL AND Operator
如果 SQL AND 的两个操作数都计算为真，则它会返回 true 或 1 。我们可使用它来组合 SQL 语句 WHERE 子句中的两个条件。
The SQL AND returns true or 1, if both its operands evaluates to true. We can use it to combine two conditions in the WHERE clause of an SQL statement.
Syntax
SQL AND 运算符与 WHERE 子句的基本语法如下：
The basic syntax of the SQL AND operator with a WHERE clause is as follows −
WHERE [condition1] AND [condition2];其中 condition1, condition2 是我们要应用于查询的条件。
Where, condition1, condition2 are the conditions we want to apply to the query.
可以使用 AND 运算符组合 N 个条件。对于要由 SQL 语句执行的操作（无论是事务还是查询），所有指定条件（由 AND 运算符分隔）都必须为 TRUE。
You can combine N number of conditions using the AND operator. For an action to be taken by the SQL statement, whether it be a transaction or a query, all the specified conditions (separated by the AND operator) must be TRUE.
Example
假设我们使用 CREATE TABLE 语句在 MySQL 数据库中创建了一个名为 CUSTOMERS 的表，如下所示：
Assume we have created a table with name CUSTOMERS in MySQL database using the CREATE TABLE statement as shown below −
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)
);以下查询使用 INSERT 语句将值插入此表：
Following query inserts values into this table using the INSERT statement −
INSERT INTO CUSTOMERS 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);获得的表如下所示：
The table obtained is as shown below −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
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 表中提取 ID、NAME 和 SALARY 字段，其中 salary 大于 2000，并且 age 小于 25 岁 -
Following is an example which would fetch the ID, NAME and SALARY fields from the CUSTOMERS table, where the salary is greater than 2000 and the age is less than 25 years −
SELECT ID, NAME, SALARY FROM CUSTOMERS
WHERE SALARY > 2000 AND AGE < 25;Multiple AND Operators
您还可以在 SQL 查询中使用多个“AND”运算符，以将多个条件（或表达式）组合在一起。与“AND”运算符组合的条件从左到右进行评估。如果任何条件评估为假，则整个复合条件将为假，并且记录将不包括在结果集中。
You can also use multiple 'AND' operators in an SQL query to combine multiple conditions (or, expressions) together. Conditions combined with the 'AND' operators are evaluated from left to right. If any of the condition evaluate to false, the entire compound condition will be false and the record will not be included in the result set.
Example
在以下查询中，我们正在从 CUSTOMERS 表中选择所有记录，其中客户的名称以“K”开头，客户的年龄大于或等于 22，并且其薪水低于 3742 -
In the following query, we are selecting all records from the CUSTOMERS table where the name of the customer starts with 'K', the age of the customer is greater than or equal to 22, and their salary is less than 3742 −
SELECT * FROM CUSTOMERS
WHERE NAME LIKE 'k%' AND AGE >= 22 AND SALARY < 3742;AND with Other Logical Operators
“AND”运算符可以与其他逻辑运算符结合使用，以从数据库表中筛选记录。
The "AND" operator can be used in combination with other logical operators to filter records from a database table.
在 SQL 中使用多个逻辑运算符时，运算顺序很重要。可以使用括号来控制运算顺序，并确保按正确的顺序计算条件。
When using multiple logical operators in SQL, the order of operations is important. Parentheses can be used to control the order of operations and ensure that the conditions are evaluated in the correct order.
此外，使用过多的逻辑运算符或复杂表达式会对查询性能产生负面影响，因此在处理大型数据集时，仔细考虑 WHERE 子句的设计非常重要。
Additionally, using too many logical operators or complex expressions can negatively impact query performance, so it’s important to carefully consider the design of the WHERE clause when working with large datasets.
Example
在这里，我们正在将 AND 运算符与 NOT 运算符相结合以创建 NAND 运算。如果至少一个输入条件为假，则“NAND”运算返回真，如果两个输入条件都为真，则返回假。
In here, we are combining the AND operator with the NOT operator to create a NAND operation. The 'NAND' operation returns true if at least one of the input conditions is false, and false if both input conditions are true.
在以下查询中，我们正在从 CUSTOMERS 表中选择所有记录，其中条件（salary 大于 4500 且 age 小于 26）为假。“NOT”运算符否定整个条件，“AND”运算符组合两个条件 -
In the following query, we are selecting all records from the CUSTOMERS table where the condition (salary is greater than 4500 and the age is less than 26) is false. The "NOT" operator negates the entire condition, and the "AND" operator combines two conditions −
SELECT * FROM CUSTOMERS
WHERE NOT (SALARY > 4500 AND AGE < 26);AND with UPDATE Statement
我们可以在 UPDATE 语句的 WHERE 子句中使用 AND 运算符来修改符合某些条件的表中的行。
We can use the AND operator in the WHERE clause of the UPDATE statement to modify the rows in a table that meet certain criteria.
Syntax
以下是使用 AND 运算符和 UPDATE 语句的语法 -
Following is the syntax of using the AND operator with the UPDATE statement −
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition1 AND condition2 AND ...;其中， table_name 是我们要更新的表的名称， column1, column2, 等。是我们想要修改的列， value1, value2, 等。是我们想要为这些列设置的新值。
Where, table_name is the name of the table we want to update, column1, column2, etc. are the columns we want to modify, and value1, value2, etc. are the new values we want to set for those columns.
Example
在以下查询中，我们使用 UPDATE 语句将所有年龄大于 27 的客户的薪水更新为“55000” -
In the following query, we are updating the salary of all the customers whose age is greater than 27 and updating it to '55000' using UPDATE statement −
UPDATE CUSTOMERS SET SALARY = 55000 WHERE AGE > 27;Output
我们得到以下结果。我们可以观察到，1 个客户的工资已经修改 -
We get the following result. We can observe that the salary of 1 customer has been modified −
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0Verification
为了验证表中是否反映了更改，我们可以使用 SELECT 语句输出表。以下是如何在 CUSTOMERS 表中显示记录的查询 -
To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables. Following is the query to display the records in the CUSTOMERS table −
SELECT * FROM CUSTOMERS;该表显示如下：
The table is displayed as follows −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
55000.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 |
正如我们在上表中看到的，'Ramesh' 的薪水已更新为 '55000'，因为他的年龄是 32，即大于 27。
As we can see in the above table, the salary of 'Ramesh' has been updated to '55000' because his age is 32 i.e. greater than 27.
The SQL OR Operator
如果其至少一个操作数计算为真，则 OR 运算符返回真，否则返回假。我们可以在 SQL 语句的 WHERE 子句中使用 OR 运算符组合两个条件。
The OR operator returns true if at least one its operands evaluates to true, and false otherwise. We can combine two conditions in an SQL statement’s WHERE clause using the OR operator.
Syntax
OR 运算符与 WHERE 子句的基本语法如下 -
The basic syntax of the OR operator with a WHERE clause is as follows −
WHERE [condition1] OR [condition2];其中， condition1, condition2 是我们想要应用于查询的条件。每个条件都由 OR 运算符分隔。
Where, condition1, condition2 are the conditions we want to apply to the query. Each condition is separated by the OR operator.
你可以使用 OR 运算符组合任意数量的条件。对于 SQL 语句要执行的动作（无论是事务还是查询），用 OR 运算符分隔的条件中至少有一个必须为 TRUE。
You can combine N number of conditions using the OR operator. For an action to be taken by the SQL statement, whether it be a transaction or query, at least of the conditions separated by the OR operator must be TRUE.
Example
以下查询从 CUSTOMERS 表中获取 ID、NAME 和 SALARY 字段，其中 salary 大于 2000 或 age 小于 25 岁 −
The following query fetches the ID, NAME and SALARY fields from the CUSTOMERS table, where the salary is greater than 2000 OR the age is less than 25 years −
SELECT ID, NAME, SALARY FROM CUSTOMERS
WHERE SALARY > 2000 OR AGE < 25;Multiple OR Operators
在 SQL 中，通常使用多个 OR 运算符将多个条件或表达式组合在一起。当使用多个 OR 运算符时，任何满足至少一个条件的行都将包含在结果集中。
In SQL, it is common to use multiple OR operators to combine multiple conditions or expressions together. While using multiple OR operators, any rows that meet at least one of the conditions will be included in the result-set.
Example
在以下查询中，我们从 CUSTOMERS 表中选择所有记录，其中客户的名称以“l”结尾，或客户的工资大于 10560，或其年龄小于 25 −
In the following query, we are selecting all records from the CUSTOMERS table where either the name of the customer ends with 'l', or the salary of the customer is greater than 10560, or their age is less than 25 −
SELECT * FROM CUSTOMERS
WHERE NAME LIKE '%l' OR SALARY > 10560 OR AGE < 25;OR with AND Operator
我们还可以在 SQL 中一起使用 AND 和 OR 运算符，以便在 WHERE 子句中组合多个条件，以筛选符合指定条件的行。
We can also use AND and OR operators together in SQL to combine multiple conditions in a WHERE clause to filter rows that meets the specified criteria.
Syntax
以下是同时使用 AND 和 OR 运算符的语法 −
Following is the syntax for using the AND and OR operators together −
WHERE (condition1 OR condition2) AND condition3;其中 condition1, condition2, and condition3 表示我们要使用 AND 和 OR 运算符组合的条件。括号对前两个条件进行分组，并使用 OR 运算符将它们组合在一起。该运算的结果使用 AND 运算符与第三个条件组合在一起。
Where, condition1, condition2, and condition3 represent the conditions that we want to combine with the AND and OR operators. The parentheses group the first two conditions and combine them with the OR operator. The result of that operation is combined with the third condition using the AND operator.
Example
在以下查询中，我们检索“CUSTOMERS”表中的所有行，其中客户的年龄等于 25 岁，或者工资小于 4500 并且姓名为 Komal 或 Kaushik。括号控制求值顺序，以便首先应用 OR 运算符，然后应用 AND 运算符 −
In the following query, we are retrieving all rows from the "CUSTOMERS" table where the age of the customer is equal to 25 or the salary is less than 4500 and the name is either Komal or Kaushik. The parentheses control the order of evaluation so that the OR operator is applied first, followed by the AND operator −
SELECT * FROM CUSTOMERS
WHERE (AGE = 25 OR SALARY < 4500)
AND (NAME = 'Komal' OR NAME = 'Kaushik');OR with DELETE Statement
我们还可以将 OR 运算符与 DELETE 语句一起使用，以删除满足多个条件之一的行。
We can also use the OR operator with the DELETE statement to delete rows that meet any one of the (multiple) conditions.
Syntax
以下是使用 OR 运算符和 DELETE 语句的语法 −
Following is the syntax of using OR operator with DELETE statement −
DELETE FROM table_name
WHERE column1 = 'value1' OR column2 = 'value2';Example
在以下查询中，我们从 CUSTOMERS 表中删除记录，其中客户的年龄等于 25 岁或其工资小于 2000 −
In the following query, we are deleting the records from the CUSTOMERS table where either the age of the customer equals 25 or their salary is less than 2000 −
DELETE FROM CUSTOMERS WHERE AGE = 25 OR SALARY < 2000;Verification
为了验证表中是否反映了更改，我们可以使用 SELECT 语句输出表。以下是如何在 CUSTOMERS 表中显示记录的查询 -
To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables. Following is the query to display the records in the CUSTOMERS table −
SELECT * FROM CUSTOMERS;该表显示如下：
The table is displayed as follows −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |