《Windows Azure Platform 系列文章目录》
The SQL API supports the following aggregate functions. SUM and AVG operate on numeric values, and COUNT, MIN, and MAX work on numbers, strings, Booleans, and nulls.
在本章节，我们将使用Azure Portal，创建1个Azure Cosmos DB。
1.首先，我们登录Azure China Portal: https://portal.azure.cn
2.选择Azure Cosmos DB
3.创建新的资源组，命名为cosmos-rg
创建新的account name：leicosmos
API设置为Core(SQL)
4.CosmosDB暂时不选择虚拟网络，图略。
注意：创建完毕后，cosmos DB并不会开始收费，因为我们没有分配RU
5.创建完毕后，我们选择该资源，点击Data Explorer
点击New Database，我们输入新的database id
如上图所示，RU可以分配在Database级别。这样Database下面的所有Collection都共享这个Database的RU
我们这里暂时不勾选Provision throughput。
注意：创建Database完毕后，cosmos DB并不会开始收费，因为我们没有分配RU
6.在上一步操作完毕后，我们点击Products，点击右键，New Collection
7.在右侧弹出的窗口中，输入下面的信息：
我们定义了Partition Key分区键为/country，
Throughput最小为400
定义了Unique Keys为/productionId
这个操作完毕后，cosmos DB已经开始收费，因为我们已经分配RU
8.创建完毕后，界面展示如下：
Database Level为Products
Collection Level为clothing
9.我们可以在界面上，插入新的Document。如下图：
10.我们插入第一条数据
{ "id": "1", "productId": "33218896", "country":"China", "category": "Women's Clothing", "manufacturer": "Contoso Sport", "description": "Quick dry crew neck t-shirt", "price": "14.99", "shipping": { "weight": 1, "dimensions": { "width": 6, "height": 8, "depth": 1 } } }
点击Save保存。
执行成功后，截图如下：
10.然后我们重复步骤9中的New Document，插入第二条数据
请注意：我们在步骤7中，创建新的Collection的时候，定义了Unique Key为productId
表示在同一个Partition Key中，productId不能重复
观察下面的数据中，"country":"China"，是与步骤9中的数据，保存在同一个Partition Key里
"productId": "33218896"，是与上面步骤9中的数据相同
{ "id": "2", "productId": "33218896", "country":"China", "category": "Women's Outerwear", "manufacturer": "Contoso", "description": "Black wool pea-coat", "price": "49.99", "shipping": { "weight": 2, "dimensions": { "width": 8, "height": 11, "depth": 3 } } }
执行Save操作，会出现约束错误
11.我们在修改productId为33218897。插入下面的值
{ "id": "2", "productId": "33218897", "country":"China", "category": "Women's Outerwear", "manufacturer": "Contoso", "description": "Black wool pea-coat", "price": "49.99", "shipping": { "weight": 2, "dimensions": { "width": 8, "height": 11, "depth": 3 } } }
执行成功
12.我们还可以插入其他值：
{ "id": "3", "productId": "33218898", "country":"US", "category": "Women's Outerwear", "manufacturer": "Contoso", "description": "Black wool pea-coat", "price": "29.99", "shipping": { "weight": 1, "dimensions": { "width": 9, "height": 4, "depth": 9 } } }
这样一共就插入了三个数据
Query
13.我们执行下面的语句：
SELECT * FROM Products c where c.id="1"
查询结果
[ { "id": "1", "productId": "33218896", "country": "China", "category": "Women's Clothing", "manufacturer": "Contoso Sport", "description": "Quick dry crew neck t-shirt", "price": "14.99", "shipping": { "weight": 1, "dimensions": { "width": 6, "height": 8, "depth": 1 } }, "_rid": "BekdAKyBIekBAAAAAAAAAA==", "_self": "dbs/BekdAA==/colls/BekdAKyBIek=/docs/BekdAKyBIekBAAAAAAAAAA==/", "_etag": "\"00001a00-0000-4200-0000-5cb6e4dd0000\"", "_attachments": "attachments/", "_ts": 1555490013 } ]
14.我们执行下面的语句
SELECT p.id, p.manufacturer, p.description FROM Products p WHERE p.id ="1"
执行结果：
[ { "id": "1", "manufacturer": "Contoso Sport", "description": "Quick dry crew neck t-shirt" } ]
FROM
15.我们执行下面的语句：
SELECT * FROM Products.shipping
执行结果：
[ { "weight": 1, "dimensions": { "width": 6, "height": 8, "depth": 1 } }, { "weight": 2, "dimensions": { "width": 8, "height": 11, "depth": 3 } }, { "weight": 1, "dimensions": { "width": 9, "height": 4, "depth": 9 } } ]
16.我们执行下面的语句
SELECT * FROM Products.shipping.weight
执行结果
[ 1, 2, 1 ]
WHERE
17.我们执行下面的语句
SELECT p.description FROM Products p WHERE p.id = "1"
执行结果
[ { "description": "Quick dry crew neck t-shirt" } ]
Order By clause
18.我们执行下面的语句
SELECT p.price, p.description, p.productId
FROM Products p
ORDER BY p.price ASC
执行结果：
[ { "price": "14.99", "description": "Quick dry crew neck t-shirt", "productId": "33218896" }, { "price": "29.99", "description": "Black wool pea-coat", "productId": "33218898" }, { "price": "49.99", "description": "Black wool pea-coat", "productId": "33218897" } ]
JOIN
19.我们执行下面的语句：
SELECT p.productId
FROM Products p
JOIN p.shipping
执行结果：
[ { "productId": "33218896" }, { "productId": "33218897" }, { "productId": "33218898" } ]
查询MAX
20.我们执行下面的语句
SELECT value MAX(c.productId) FROM clothing c
执行结果
[ "33218898" ]
