When we have to UPDATE, DELETE or SELECT a particular set of data we use SQL WHERE clause. The WHERE clause is an integral part of SQL as it provides the feature to add conditions to a query.
当我们必须UPDATE , DELETE或SELECT特定数据集时,我们使用SQL WHERE子句。 WHERE子句是SQL的组成部分,因为它提供了向查询添加条件的功能。
SQL Where子句 (SQL Where Clause)
SQL where clause is one of the most widely used SQL keyword because it’s part of most of the queries. Let’s see the general syntax of where clause in sql query.
SQL where子句是最广泛使用的SQL关键字之一,因为它是大多数查询的一部分。 让我们看看sql查询中where子句的一般语法。
SELECT column(s) FROM table_name WHERE condition;
In the syntax above the column data is retrieved based on the condition that is specified as part of WHERE condition.
在上面的语法中,基于指定为WHERE条件一部分的条件检索列数据。
Similarly we can use it with Update and Delete query as follows:
同样,我们可以将其与Update和Delete查询一起使用,如下所示:
update Employee set name = 'David' where id=1;
Delete from Employee where name = 'John';
SQL查询位置示例 (SQL Query Where Example)
Let’s try to understand the SQL WHERE command through some example. We have a sample table with below structure and data.
让我们尝试通过一些示例来了解SQL WHERE命令。 我们有一个示例表,其中包含以下结构和数据。
CustomerId | CustomerName | CustomerAge | CustomerGender |
---|---|---|---|
1 | John | 31 | M |
2 | Amit | 25 | M |
3 | Annie | 35 | F |
顾客ID | 顾客姓名 | 客户年龄 | 客户性别 |
---|---|---|---|
1个 | 约翰 | 31 | 中号 |
2 | 阿米特 | 25 | 中号 |
3 | 安妮 | 35 | F |
Now we want to select all the customer names who are Female. Below is the SQL query with where clause to get this data.
现在,我们要选择所有女性客户名称。 以下是带有where子句SQL查询,用于获取此数据。
SELECT CutomerName FROM Customer WHERE CustomerGender = 'F';
SQL Where子句运算符 (SQL Where Clause Operators)
Since where clause is to add conditions to the query, there are many operators that we can use with it. Let’s look into commonly used operators with sql query where clause.
由于where子句用于向查询添加条件,因此我们可以使用许多运算符。 我们来看一下带有sql query where子句的常用运算符。
Operator | Description | Example |
---|---|---|
= | Equal to | SELECT CustomerNameFROM Customer WHERE CustomerGender = ‘F’; |
!= , <> | Not equal to | SELECT CustomerName FROM Customer WHERECustomerGender != ‘M’; |
> | Greater than | SELECT CustomerNameFROM Customer WHERE CustomerAge > 32; |
< | Less than | SELECT CustomerName FROM Customer WHERECustomerAge < 30; |
>= | Greater than or equalto | SELECT CustomerNameFROM Customer WHERE CustomerAge >= 35; |
<= | Less than or equal to | SELECT CustomerName FROM Customer WHERECustomerAge <= 30; |
IN | Value is from thespecified list of values | SELECT CustomerNameFROM Customer WHERE CustomerGender IN (‘M’, ‘F’); |
BETWEEN | Between the specified range | SELECT CustomerName FROM Customer WHERECustomerAge BETWEEN (25,30); |
LIKE | Search for pattern | SELECT CustomerNameFROM Customer WHERE CustomerName like ‘Ann%’; |
操作员 | 描述 | 例 |
---|---|---|
= | 等于 | SELECT CustomerNameFROM Customer WHERE CustomerGender ='F'; |
!=,<> | 不等于 | 从客户WHERECustomerGender中选择客户名!='M'; |
> | 比...更棒 | 选择CustomerNameFROM Customer WHERE CustomerAge> 32; |
< | 少于 | 从客户WHERECustomerAge <30中选择客户名称; |
> = | 大于或等于 | 选择CustomerNameFROM Customer WHERE CustomerAge> = 35; |
<= | 小于或等于 | 从客户WHERECustomerAge <= 30中选择客户名称; |
在 | 值来自指定的值列表 | 选择CustomerNameFROM客户所在的CustomerGender输入(“ M”,“ F”); |
之间 | 在指定范围之间 | 从(25,30)之间的客户WHERECustomerAge中选择客户名称; |
喜欢 | 搜索图案 | SELECT CustomerNameFROM Customer WHERE CustomerName之类的“ Ann%”; |
Numeric and text are used as part of the WHERE clause values in different ways. To use Numeric no quotes should be used. For using a text value single quotes should be used.
数字和文本以不同方式用作WHERE子句值的一部分。 要使用数字,不应该使用引号。 对于使用文本值,应使用单引号。
That’s all for SQL where clause. Where clause in SQL query is used a lot, so you should understand it properly.
这就是SQL where子句的全部内容。 SQL查询中的where子句经常使用,因此您应该正确理解它。