AND & OR

落日映苍穹つ 2022-12-29 09:28 134阅读 0赞

AND 和 OR 运算符用于基于一个以上的条件对记录进行过滤。

## AND 和 OR 运算符 ##

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。

如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

## 原始的表 (用在例子中的)： ##

<table style="margin:10px 0px 0px; padding:0px; border:1px solid rgb(170,170,170); border-collapse:collapse; width:709px"> 
 <tbody style="margin:0px; padding:0px; border:0px"> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <th style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:baseline; background-color:rgb(213,213,213)"> LastName</th> 
   <th style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:baseline; background-color:rgb(213,213,213)"> FirstName</th> 
   <th style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:baseline; background-color:rgb(213,213,213)"> Address</th> 
   <th style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:baseline; background-color:rgb(213,213,213)"> City</th> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> Adams</td> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> John</td> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> Oxford Street</td> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> London</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> Bush</td> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> George</td> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> Fifth Avenue</td> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> New York</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> Carter</td> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> Thomas</td> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> Changan Street</td> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> Beijing</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px"> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> Carter</td> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> William</td> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> Xuanwumen 10</td> 
   <td style="margin:0px; padding:5px 15px 5px 5px; border:1px solid rgb(170,170,170); vertical-align:text-top; background-color:rgb(239,239,239)"> Beijing</td> 
  </tr> 
 </tbody> 
</table>

## AND 运算符实例 ##

使用 AND 来显示所有姓为 "Carter" 并且名为 "Thomas" 的人：

SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'

### 结果： ###

## OR 运算符实例 ##

使用 OR 来显示所有姓为 "Carter" 或者名为 "Thomas" 的人：

SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

### 结果： ###

## 结合 AND 和 OR 运算符 ##

我们也可以把 AND 和 OR 结合起来（使用圆括号来组成复杂的表达式）:

SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William')
    AND LastName='Carter'

### 结果： ###