sql 逻辑运算符_SQL AND，OR，NOT – SQL逻辑运算符

sql 逻辑运算符

SQL provides logical operators which helps in filtering the result set based on some condition. SQL logical operators that we will be discussing are AND, OR and NOT. These three are the most commonly used logical operators in SQL queries.

SQL提供逻辑运算符，该运算符有助于根据某些条件过滤结果集。 我们将要讨论SQL逻辑运算符是AND ， OR和NOT 。 这三个是SQL查询中最常用的逻辑运算符 。

1. SQL AND运算符 (1. SQL AND Operator)

SQL AND operator are used when we want to combine multiple conditions as part of the WHERE clause. The result set will be filtered based on the satisfaction of both the condition. So, if both the conditions are true then only the result will be filtered. To combine multiple conditions, we can use more than one AND as part of the WHERE clause.

当我们要将多个条件合并为WHERE子句的一部分时，将使用SQL AND运算符。 结果集将基于两个条件的满足条件进行过滤。 因此，如果两个条件都成立，则仅结果将被过滤。 为了组合多个条件，我们可以在WHERE子句中使用多个AND。

1.1）SQL单一AND运算子范例 (1.1) SQL single AND operator example)

SQL AND operator syntax is:

SQL AND运算符的语法为：

SELECT column(s) FROM table_name WHERE condition1 AND condition2;

As mentioned in the syntax above for combining two conditions we can use one AND operator.

如以上用于组合两个条件的语法中所述，我们可以使用一个AND运算符。

We will now try to understand one AND operator through some example.

现在，我们将通过一些示例来尝试理解一个AND运算符。

Let’s consider the following Student table for example purpose.

让我们考虑以下学生表格作为示例。

RollNo	StudentName	StudentGender	StudentAge	StudentPercent
1	George	M	14	85
2	Monica	F	13	88
3	Jessica	F	14	84
4	Tom	M	13	78

卷号	学生姓名	学生性别	学生年龄	学生百分比
1个	乔治	中号	14	85
2	莫妮卡	F	13	88
3	杰西卡（Jessica）	F	14	84
4	汤姆	中号	13	78

Scenario: Get the percentage of students whose age is more than 12 years and gender is female.

方案 ：获取年龄在12岁以上且性别为女性的学生所占的百分比。

SELECT StudentPercent FROM Student WHERE StudentAge>12 AND StudentGender = "F";

Output:

输出：

StudentPercent
88
84

学生百分比
88
84

In the example above, we have used one AND operator to combine two conditions, StudentAge is greater than 12 and StudentGender is equal to “F”.

在上面的示例中，我们使用了一个AND运算符来组合两个条件，StudentAge大于12，StudentGender等于“ F”。

Let’s try to see an example for multiple AND operator.

让我们尝试查看多个AND运算符的示例。

1.2）SQL多重AND运算子范例 (1.2) SQL multiple AND operator example)

SELECT column(s) FROM table_name WHERE condition1 AND condition2 AND condition3…AND conditionN;

As mentioned in the syntax above for combining more than two conditions we can use more than one AND operator.

如以上用于组合两个以上条件的语法中所述，我们可以使用多个AND运算符。

We will now try to understand more than one AND operator through some example. Let’s consider the earlier defined Student table for example purpose.

现在，通过一些示例，我们将尝试了解多个AND运算符。 让我们以先前定义的Student表为例。

Scenario: Get the percentage of students whose age is more than 12 years and gender are female and the percentage is more than 80.

场景 ：获取年龄在12岁以上且性别是女性且该比例超过80岁的学生所占的百分比。

SELECT StudentPercent FROM Student WHERE StudentAge>12 AND StudentGender = "F" AND StudentPercent>80;

Output:

输出：

StudentPercent
85
88
84

学生百分比
85
88
84

In the example above, we have used one AND operator to combine three conditions, StudentAge is greater than 12, StudentGender is equal to “F” and StudentPercent is greater than 80.

在上面的示例中，我们使用了一个AND运算符来组合三个条件，StudentAge大于12，StudentGender等于“ F”，StudentPercent大于80。

2. SQL OR运算符 (2. SQL OR Operator)

OR operator is used when we want to combine multiple conditions as part of the WHERE clause. The result set will be filtered based on satisfaction of at least one of the conditions. So, if at least one of the conditions are true than only the result will be filtered. To combine multiple conditions, we can use more than one OR as part of the WHERE clause.

当我们要将多个条件合并为WHERE子句的一部分时，将使用OR运算符。 将基于至少一个条件的满足来过滤结果集。 因此，如果至少有一个条件为真，则仅对结果进行过滤。 为了组合多个条件，我们可以在WHERE子句中使用多个OR。

2.1）SQL单或运算符示例 (2.1) SQL single OR operator example)

Syntax:

语法 ：

SELECT column(s) FROM table_name WHERE condition1 OR condition2;

As mentioned in the syntax above for combining two conditions we can use one OR operator.
We will now try to understand one OR operator through some example. Let’s reuse the earlier defined Student table for example purpose.

如以上用于组合两个条件的语法中所述，我们可以使用一个OR运算符。
现在，我们将通过一些示例来尝试理解一个OR运算符。 让我们重用先前定义的Student表作为示例。

Scenario: Get the percentage of students whose age is more than 12 years or gender are female.

方案 ：获取年龄在12岁以上或性别为女性的学生所占的百分比。

Query:

查询 ：

SELECT StudentPercent FROM Student WHERE StudentAge>12 OR StudentGender = "F";

Output:

输出 ：

StudentPercent
85
88
84
78

学生百分比
85
88
84
78

In the example above, we have used one OR operator to combine two conditions, StudentAge is greater than 12 and StudentGender is equal to “F”.

在上面的示例中，我们使用了一个OR运算符来组合两个条件，StudentAge大于12，StudentGender等于“ F”。

Let’s try to understand for multiple OR operator.

让我们尝试了解多个OR运算符。

2.2）SQL多重或运算符示例 (2.2) SQL multiple OR operator example)

Syntax:

语法 ：

SELECT column(s) FROM table_name WHERE condition1 OR condition2 OR condition3 ... OR conditionN;

As mentioned in the syntax above for combining more than two conditions we can use more than one OR operator.

如以上用于组合两个以上条件的语法中所述，我们可以使用多个OR运算符。

We will now try to understand more than one OR operator through some example.

现在，通过一些示例，我们将尝试了解多个OR运算符。

Scenario: Get the percentage of students whose age is more than 12 years or gender is female or percentage is more than 80.

方案 ：获取年龄在12岁以上或性别是女性或在80岁以上的学生所占的百分比。

Query:

查询 ：

SELECT StudentPercent FROM Student WHERE StudentAge>12 OR StudentGender = "F" OR StudentPercent>80;

In the example above, we have used one OR operator to combine three conditions, StudentAge is greater than 12, StudentGender is equal to “F” and StudentPercent is greater than 80.

在上面的示例中，我们使用了一个OR运算符来组合三个条件，StudentAge大于12，StudentGender等于“ F”，StudentPercent大于80。

3. SQL NOT运算符 (3. SQL NOT Operator)

SQL NOT operator is used when we want to filter result set when the condition is not satisfied in the WHERE clause.

当我们要过滤WHERE子句中不满足条件的结果集时，使用SQL NOT运算符。

Let’s try to understand NOT operator in detail with some examples.

让我们尝试通过一些示例来详细了解NOT运算符。

3.1）SQL NOT运算符示例 (3.1) SQL NOT operator example)

Syntax:

语法 ：

SELECT column(s) FROM table_name WHERE NOT condition;

As mentioned in the syntax above we use NOT operator along with WHERE clause. We will now try to understand NOT operator through some example.

如以上语法中所述，我们将NOT运算符与WHERE子句一起使用。 现在，我们将通过一些示例来理解NOT运算符。

Scenario: Get the percentage of students whose gender is not female.

方案 ：获取性别不是女性的学生百分比。

Query:

查询 ：

SELECT StudentPercent FROM Student WHERE NOT StudentGender = "F";

Output:

输出 ：

StudentPercent
85
78

学生百分比
85
78

In the example above, we have used NOT operator to identify if the gender of the student is not female.

在上面的示例中，我们使用了NOT运算符来识别学生的性别是否不是女性。

4. SQL逻辑运算符组合示例 (4. SQL Logical Operators Combination Example)

We have tried to understand the three logical operators individually. But, these three operators can also be used in combination. Let’s try to understand different combinations.

我们试图分别理解这三个逻辑运算符。 但是，这三个运算符也可以组合使用。 让我们尝试了解不同的组合。

We will consider the below-mentioned Employee table for example purpose for all the combinations.

我们将以下提及的Employee表作为所有组合的示例用途。

EmpId	EmpName	EmpAge	EmpGender	EmpState
1	Tony	27	M	Texas
2	Anjali	32	F	California
3	Jason	34	M	London
4	Salma	33	F	Texas
5	Anuj	26	M	California

EmpId	EmpName	EmpAge	EmpGender	EmpState
1个	托尼	27	中号	德州
2	安贾利	32	F	加利福尼亚州
3	杰森	34	中号	伦敦
4	萨尔玛	33	F	德州
5	阿努吉	26	中号	加利福尼亚州

4.1）SQL AND OR示例 (4.1) SQL AND OR Example)

Scenario: Get the name of male employees whose state is London or Texas.
Query:

方案 ：获取州或伦敦的男性雇员的姓名。
查询 ：

SELECT EmpName FROM Employee WHERE EmpGender = "M" AND (EmpState = "London" OR EmpState = "Texas");

Output:

输出 ：

EmpName
Tony
Jason

EmpName
托尼
杰森

4.2）SQL AND NOT示例 (4.2) SQL AND NOT Example)

Scenario: Get the name of the employee whose age is above 30 and gender is not male.
Query:

场景 ：获取年龄在30岁以上且性别不是男性的员工的姓名。
查询 ：

SELECT EmpName FROM Employee WHERE EmpAge > 30 AND NOT EmpGender = "M";

Output:

输出 ：

EmpName
Anjali
Salma

EmpName
安贾利
萨尔玛

4.3）SQL OR NOT示例 (4.3) SQL OR NOT Example)

Scenario: Get the name of the employee whose age is above 30 or gender is not male.
Query:

场景 ：获取年龄在30岁以上或性别不是男性的员工的姓名。
查询 ：

SELECT EmpName FROM Employee WHERE EmpAge > 30 OR NOT EmpGender = "M";

Output:

输出 ：

EmpName
Anjali
Jason
Salma

EmpName
安贾利
杰森
萨尔玛

That’s all for SQL logical operators. I hope you got good enough ideas through examples of SQL AND, OR and NOT operators.

这就是SQL逻辑运算符的全部内容。 我希望您通过SQL AND，OR和NOT运算符的示例获得足够好的主意。

翻译自: https://www.journaldev.com/19147/sql-and-or-not-sql-logical-operators

sql 逻辑运算符