运算符是保留字或主要用于SQL WHERE子句的字符,用于执行操作,例如比较和算术运算. DocumentDB SQL还支持各种标量表达式.最常用的是二进制和一元表达式.
目前支持以下SQL运算符,可以在查询中使用.
SQL比较运算符
以下是DocumentDB SQL语法中可用的所有比较运算符的列表.
S.No. | 运营商&说明 |
---|---|
1 | = 检查两个操作数的值是否相等.如果是,则条件成立. |
2 | != 检查两个操作数的值是否相等.如果值不相等则条件成立. |
3 | <> 检查两个操作数的值是否相等.如果值不相等则条件成立. |
4 | > 检查左操作数的值是否大于右操作数的值.如果是,则条件成立. |
5 | < 检查左操作数的值是否小于右操作数的值.如果是,则条件成立. |
6 | > = 检查左操作数的值是否大于或等于右操作数的值.如果是,则条件成立. |
7 | < = 检查左操作数的值是否小于或等于右操作数的值.如果是,则条件成立. |
SQL逻辑运算符
以下是DocumentDB SQL语法中可用的所有逻辑运算符的列表.
S.No. | 运算符&说明 |
---|---|
1 | AND AND运算符允许在SQL语句的WHERE子句中存在多个条件. |
2 | BETWEEN BETWEEN运算符用于搜索值给定最小值和最大值时,它们在一组值内. |
3 | IN IN运算符用于将值与已指定的文字值列表进行比较. |
4 | 或 OR运算符用于组合SQL语句的WHERE子句中的多个条件. |
5 | NOT NOT运算符会反转使用它的逻辑运算符的含义.例如,NOT EXISTS,NOT BETWEEN,NOT IN等.这是一个否定运算符. |
SQL算术运算符
以下是DocumentDB SQL语法中可用的所有算术运算符的列表.
S.No. | 运算符&说明 |
---|---|
1 | + 加法 : 在运算符的任一侧添加值. |
2 | - 减法 : 从左手操作数中减去右手操作数. |
3 | * 乘法 : 将运算符两侧的值相乘. |
4 | / 分部 : 将左手操作数除以右手操作数. |
5 | % 模数 : 将左手操作数除以右手操作数并返回余数. |
我们将考虑该示例中的相同文档也是如此.以下是 AndersenFamily 文件.
{ "id": "AndersenFamily", "lastName": "Andersen", "parents": [ { "firstName": "Thomas", "relationship": "father" }, { "firstName": "Mary Kay", "relationship": "mother" } ], "children": [ { "firstName": "Henriette Thaulow", "gender": "female", "grade": 5, "pets": [ { "givenName": "Fluffy", "type": "Rabbit" } ] } ], "location": { "state": "WA", "county": "King", "city": "Seattle" }, "isRegistered": true }
以下是 SmithFamily 文件.
{ "id": "SmithFamily", "parents": [ { "familyName": "Smith", "givenName": "James" }, { "familyName": "Curtis", "givenName": "Helen" } ], "children": [ { "givenName": "Michelle", "gender": "female", "grade": 1 }, { "givenName": "John", "gender": "male", "grade": 7, "pets": [ { "givenName": "Tweetie", "type": "Bird" } ] } ], "location": { "state": "NY", "county": "Queens", "city": "Forest Hills" }, "isRegistered": true }
以下是 WakefieldFamily 文件.
{ "id": "WakefieldFamily", "parents": [ { "familyName": "Wakefield", "givenName": "Robin" }, { "familyName": "Miller", "givenName": "Ben" } ], "children": [ { "familyName": "Merriam", "givenName": "Jesse", "gender": "female", "grade": 6, "pets": [ { "givenName": "Charlie Brown", "type": "Dog" }, { "givenName": "Tiger", "type": "Cat" }, { "givenName": "Princess", "type": "Cat" } ] }, { "familyName": "Miller", "givenName": "Lisa", "gender": "female", "grade": 3, "pets": [ { "givenName": "Jake", "type": "Snake" } ] } ], "location": { "state": "NY", "county": "Manhattan", "city": "NY" }, "isRegistered": false }
让我们看一个在WHERE子句中使用比较运算符的简单示例.
在此查询中,在WHERE子句中指定了(WHERE f.id ="WakefieldFamily")条件,它将检索id等于WakefieldFamily的文档.
SELECT * FROM f WHERE f.id = "WakefieldFamily"
执行上述查询时,它将返回WakefieldFamily的完整JSON文档,如以下输出所示.
[ { "id": "WakefieldFamily", "parents": [ { "familyName": "Wakefield", "givenName": "Robin" }, { "familyName": "Miller", "givenName": "Ben" } ], "children": [ { "familyName": "Merriam", "givenName": "Jesse", "gender": "female", "grade": 6, "pets": [ { "givenName": "Charlie Brown", "type": "Dog" }, { "givenName": "Tiger", "type": "Cat" }, { "givenName": "Princess", "type": "Cat" } ] }, { "familyName": "Miller", "givenName": "Lisa", "gender": "female", "grade": 3, "pets": [ { "givenName": "Jake", "type": "Snake" } ] } ], "location": { "state": "NY", "county": "Manhattan", "city": "NY" }, "isRegistered": false, "_rid": "Ic8LAJFujgECAAAAAAAAAA==", "_ts": 1450541623, "_self": "dbs/Ic8LAA==/colls/Ic8LAJFujgE=/docs/Ic8LAJFujgECAAAAAAAAAA==/", "_etag": "00000500-0000-0000-0000-567582370000", "_attachments": "attachments/" } ]
让我们看看另一个例子,其中查询将检索等级大于5的子数据.
SELECT * FROM Families.children[0] c WHERE (c.grade > 5)
执行上述查询时,它将检索输出中显示的以下子文档.
[ { "familyName": "Merriam", "givenName": "Jesse", "gender": "female", "grade": 6, "pets": [ { "givenName": "Charlie Brown", "type": "Dog" }, { "givenName": "Tiger", "type": "Cat" }, { "givenName": "Princess", "type": "Cat" } ] } ]