Documentdb Sql 简明教程

DocumentDB SQL - Operators

运算符是主要在 SQL WHERE 子句中用来执行运算（如比较和算术运算）的保留字或字符。DocumentDB SQL 还支持各种标量表达式。最常用的表达式是 binary and unary expressions 。

An operator is a reserved word or a character used primarily in an SQL WHERE clause to perform operation(s), such as comparisons and arithmetic operations. DocumentDB SQL also supports a variety of scalar expressions. The most commonly used are binary and unary expressions.

目前支持以下 SQL 运算符，并且可以在查询中使用它们。

The following SQL operators are currently supported and can be used in queries.

SQL Comparison Operators

以下是 DocumentDB SQL 语法中所有比较运算符的列表。

Following is a list of all the comparison operators available in DocumentDB SQL grammar.

S.No.

Operators & Description

1

= Checks if the values of two operands are equal or not. If yes, then condition becomes true.

2

!= Checks if the values of two operands are equal or not. If values are not equal then condition becomes true.

3

<> Checks if the values of two operands are equal or not. If values are not equal then condition becomes true.

4

> Checks if the value of left operand is greater than the value of right operand. If yes, then condition becomes true.

5

< Checks if the value of left operand is less than the value of right operand. If yes, then condition becomes true.

6

>= Checks if the value of left operand is greater than or equal to the value of right operand. If yes, then condition becomes true.

7

⇐ Checks if the value of left operand is less than or equal to the value of right operand. If yes, then condition becomes true.

SQL Logical Operators

以下是 DocumentDB SQL 语法中所有逻辑运算符的列表。

Following is a list of all the logical operators available in DocumentDB SQL grammar.

S.No.

Operators & Description

1

AND The AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause.

2

BETWEEN The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.

3

IN The IN operator is used to compare a value to a list of literal values that have been specified.

4

OR The OR operator is used to combine multiple conditions in an SQL statement’s WHERE clause.

5

NOT The NOT operator reverses the meaning of the logical operator with which it is used. For example, NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.

SQL Arithmetic Operators

以下是 DocumentDB SQL 语法中所有算术运算符的列表。

Following is a list of all the arithmetic operators available in DocumentDB SQL grammar.

S.No.

Operators & Description

1

+ Addition − Adds values on either side of the operator.

2

- Subtraction − Subtracts the right hand operand from the left hand operand.

3

* Multiplication − Multiplies values on either side of the operator.

4

/ Division − Divides the left hand operand by the right hand operand.

5

% Modulus − Divides the left hand operand by the right hand operand and returns the remainder.

在这个示例中我们也会考虑相同的文档。以下是 AndersenFamily 文档。

We will consider the same documents in this example as well. Following is the AndersenFamily document.

{
   "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 文档。

Following is the SmithFamily document.

{
   "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 文档。

Following is the WakefieldFamily document.

{
   "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 子句中比较运算符的使用。

Let’s take a look at a simple example in which a comparison operator is used in WHERE clause.

comparison operator

在这个查询中，在 WHERE 子句中指定了 (WHERE f.id = "WakefieldFamily") 条件，它将检索 id 等于 WakefieldFamily 的文档。

In this query, in WHERE clause, the (WHERE f.id = "WakefieldFamily") condition is specified, and it will retrieve the document whose id is equal to WakefieldFamily.

SELECT *
FROM f
WHERE f.id = "WakefieldFamily"

执行上述查询时，它会返回 WakefieldFamily 的完整 JSON 文档，如下面的输出所示。

When the above query is executed, it will return the complete JSON document for WakefieldFamily as shown in the following output.

[
   {
      "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 的儿童数据。

Let’s take a look at another example in which the query will retrieve the children data whose grade is greater than 5.

SELECT *
FROM Families.children[0] c
WHERE (c.grade > 5)

执行上述查询后，将按照输出中所示检索以下子文档。

When the above query is executed, it will retrieve the following sub document as shown in the output.

[
   {
      "familyName": "Merriam",
      "givenName": "Jesse",
      "gender": "female",
      "grade": 6,

      "pets": [
         {
            "givenName": "Charlie Brown",
            "type": "Dog"
         },

         {
            "givenName": "Tiger",
            "type": "Cat"
         },

         {
            "givenName": "Princess",
            "type": "Cat"
         }
      ]
   }
]