WHERE子句用于指定一个筛选标准。
WHERE子句
WHERE子句可以添加至SELECT语句以便有条件地从表中选择数据。
语法:
SELECT column FROM table WHERE column operator value |
下面的运算符可以同WHERE子句一起使用:
Operator | Description |
= | Equal |
<> | Not equal |
> | Greater than |
< | Less than |
>= | Greater than or equal |
<= | Less than or equal |
BETWEEN | Between an inclusive range |
LIKE | Search for a pattern |
注意:在一些SQL版本中 <> 运算符可能被写成 !=
---------------------------------
使用WHERE 子句
我们给SELECT语句添加一个WHERE 子句来选择只有居住于"Sandnes"城市的人。
SELECT * FROM Persons WHERE City='Sandnes' |
"Persons" 表
LastName | FirstName | Address | City | Year |
---|---|---|---|---|
Hansen | Ola | Timoteivn 10 | Sandnes | 1951 |
Svendson | Tove | Borgvn 23 | Sandnes | 1978 |
Svendson | Stale | Kaivn 18 | Sandnes | 1980 |
Pettersen | Kari | Storgt 20 | Stavanger | 1960 |
返回结果:
LastName | FirstName | Address | City | Year |
---|---|---|---|---|
Hansen | Ola | Timoteivn 10 | Sandnes | 1951 |
Svendson | Tove | Borgvn 23 | Sandnes | 1978 |
Svendson | Stale | Kaivn 18 | Sandnes | 1980 |
----------------------------------
使用引号
注意我们已经在条件值周围使用单引号
SQL 使用单引号包括文本值 (多数数据库系统也允许双引号)。数字则不必附上引号。
文本值:
This is correct: SELECT * FROM Persons WHERE FirstName='Tove' This is wrong: SELECT * FROM Persons WHERE FirstName=Tove |
数字值:
This is correct: SELECT * FROM Persons WHERE Year>1965 This is wrong: SELECT * FROM Persons WHERE Year>'1965' |
----------------------------------
LIKE 条件
LIKE 条件用于指定搜索列的模式。
语法:
SELECT column FROM table WHERE column LIKE pattern |
"%" 是用于定义位于模式的前后通配符
使用 LIKE
下面的SQL语句将返回first names以 O 开头的人。
SELECT * FROM Persons WHERE FirstName LIKE 'O%' |
下面的SQL语句将返回first names以 a 结尾的人。
SELECT * FROM Persons WHERE FirstName LIKE '%a' |
下面的SQL语句将返回 first names 包含 la 的人。
SELECT * FROM Persons WHERE FirstName LIKE '%la%' |