使用控制台提取数据

了解如何使用控制台查看和下载 Oracle NoSQL Database Cloud Service 表数据。

本文包含以下主题：

查看表数据

了解如何从 NoSQL 控制台查看 Oracle NoSQL Database Cloud Service 表中的数据。

要查看表数据，请执行以下操作：

从基础结构控制台访问 NoSQL 控制台。请参阅从基础结构控制台访问服务。
NoSQL 控制台会列出租户中的所有表。要查看表详细信息，请单击表名。

此时将打开“表”页。
在“表”页中，选择浏览数据选项卡。
默认情况下，查询文本会填充一个 SQL 查询，该查询将从表中检索所有记录。对于 Oracle NoSQL 语句，可以使用任何有效的 SQL 修改此查询。您可能会收到一个错误，指出您的语句不完整或有故障。请参阅在 OCI 控制台中调试 SQL 语句错误，了解 OCI 控制台中的可能错误以及如何修复它们。有关 SQL 查询示例，请参阅开发人员指南。
选择 Execute 。

表数据显示在查询结果下。
要查看已执行的 SQL 查询的查询执行计划，请选择显示执行计划。详细的查询执行计划将显示在新窗口中。

使用 SQL 命令提取数据

可以使用 SQL SELECT 语句从 NoSQL 表中提取数据。

从表中提取所有行

可以从表中选择列。为此，请在语句的 SELECT 后列出所需表列的名称。在 FROM 子句之后给出表的名称。要从子表检索数据，请使用点表示法，例如 parent.child 。要选择所有表列，请使用星号 (*) 通配符。SELECT 语句还可以基于现有列的值包含计算表达式。

示例 1：从表 BaggageInfo 中选择所有数据。

SELECT * FROM BaggageInfo

解释：BaggageInfo 方案具有一些固定的静态字段和 JSON 列。静态字段包括票证号码、全名、性别、联系人电话和确认号码。包信息以 JSON 格式存储，并填充一组文档。

输出（仅为简洁起见显示结果的一行）：

{"ticketNo":1762330498104,"fullName":"Michelle Payne","gender":"F","contactPhone":"575-781-6240","confNo":"RL3J4Q",
"bagInfo":[{
   "bagArrivalDate":"2019-02-02T23:59:00Z",
   "flightLegs":[
      {"actions":[
           {"actionAt":"SFO","actionCode":"ONLOAD to IST","actionTime":"2019-02-02T12:10:00Z"},
           {"actionAt":"SFO","actionCode":"BagTag Scan at SFO","actionTime":"2019-02-02T11:47:00Z"},
           {"actionAt":"SFO","actionCode":"Checkin at SFO","actionTime":"2019-02-02T10:01:00Z"}],
           "estimatedArrival":"2019-02-03T01:00:00Z",
           "flightDate":"2019-02-02T12:00:00Z",
           "flightNo":"BM318",
           "fltRouteDest":"IST",
           "fltRouteSrc":"SFO"},
      {"actions":[
           {"actionAt":"IST","actionCode":"ONLOAD to ATH","actionTime":"2019-02-03T13:06:00Z"},
           {"actionAt":"IST","actionCode":"BagTag Scan at IST","actionTime":"2019-02-03T12:48:00Z"},
           {"actionAt":"IST","actionCode":"OFFLOAD from IST","actionTime":"2019-02-03T13:00:00Z"}],
           "estimatedArrival":"2019-02-03T12:12:00Z",
           "flightDate":"2019-02-02T13:10:00Z",
           "flightNo":"BM696",
           "fltRouteDest":"ATH",
           "fltRouteSrc":"IST"},
      {"actions":[
           {"actionAt":"JTR","actionCode":"Offload to Carousel at JTR","actionTime":"2019-02-03T00:06:00Z"},
           {"actionAt":"ATH","actionCode":"ONLOAD to JTR","actionTime":"2019-02-03T00:13:00Z"},
           {"actionAt":"ATH","actionCode":"OFFLOAD from ATH","actionTime":"2019-02-03T00:10:00Z"}],
           "estimatedArrival":"2019-02-03T00:12:00Z",
           "flightDate":"2019-2-2T12:10:00Z",
           "flightNo":"BM665",
           "fltRouteDest":"JTR",
           "fltRouteSrc":"ATH"}],
     "id":"79039899186259",
     "lastActionCode":"OFFLOAD",
     "lastActionDesc":"OFFLOAD",
     "lastSeenStation":"JTR",
     "lastSeenTimeGmt":"2019-02-02T23:59:00Z",
     "routing":"SFO/IST/ATH/JTR",
     "tagNum":"17657806247861"}
]}

示例 2：要从表 BaggageInfo 中选择特定列，请在 SELECT 语句中以逗号分隔列表的形式包含列名。

SELECT fullName, contactPhone, gender FROM BaggageInfo

解释：要显示三个静态字段的值 - 全名、电话号码和性别。

输出：

{"fullName":"Lucinda Beckman","contactPhone":"364-610-4444","gender":"M"}
{"fullName":"Adelaide Willard","contactPhone":"421-272-8082","gender":"M"}
{"fullName":"Raymond Griffin","contactPhone":"567-710-9972","gender":"F"}
{"fullName":"Elane Lemons","contactPhone":"600-918-8404","gender":"F"}
{"fullName":"Zina Christenson","contactPhone":"987-210-3029","gender":"M"}
{"fullName":"Zulema Martindale","contactPhone":"666-302-0028","gender":"F"}
{"fullName":"Dierdre Amador","contactPhone":"165-742-5715","gender":"M"}
{"fullName":"Henry Jenkins","contactPhone":"960-428-3843","gender":"F"}
{"fullName":"Rosalia Triplett","contactPhone":"368-769-5636","gender":"F"}
{"fullName":"Lorenzo Phil","contactPhone":"364-610-4444","gender":"M"}
{"fullName":"Gerard Greene","contactPhone":"395-837-3772","gender":"M"}
{"fullName":"Adam Phillips","contactPhone":"893-324-1064","gender":"M"}
{"fullName":"Doris Martin","contactPhone":"289-564-3497","gender":"F"}
{"fullName":"Joanne Diaz","contactPhone":"334-679-5105","gender":"F"}
{"fullName":"Omar Harvey","contactPhone":"978-191-8550","gender":"F"}
{"fullName":"Fallon Clements","contactPhone":"849-731-1334","gender":"M"}
{"fullName":"Lisbeth Wampler","contactPhone":"796-709-9501","gender":"M"}
{"fullName":"Teena Colley","contactPhone":"539-097-5220","gender":"M"}
{"fullName":"Michelle Payne","contactPhone":"575-781-6240","gender":"F"}
{"fullName":"Mary Watson","contactPhone":"131-183-0560","gender":"F"}
{"fullName":"Kendal Biddle","contactPhone":"619-956-8760","gender":"F"}

示例 3：从表 stream_acct 中选择所有数据。

SELECT * FROM stream_acct

解释：stream_acct 方案具有一些固定的静态字段和 JSON 列。

输出（仅为简洁起见显示结果的一行）：

{"acct_id":1,"profile_name":"AP","account_expiry":"2023-10-18T00:00:00.0Z",
"acct_data":{
[{
   "showName": "At the Ranch",
   "showId": 26,
   "showtype": "tvseries",
   "genres": ["action", "crime", "spanish"],
   "numSeasons": 4,
   "seriesInfo": [{
      "seasonNum": 1,
      "numEpisodes": 2,
      "episodes": [{
         "episodeID": 20,
         "episodeName": "Season 1 episode 1",
         "lengthMin": 85,
         "minWatched": 85,
         "date": "2022-04-18"
      },
      {
         "episodeID": 30,
         "lengthMin": 60,
         "episodeName": "Season 1 episode 2",
         "minWatched": 60,
         "date": "2022 - 04 - 18 "
      }]
   },
   {
      "seasonNum": 2,
      "numEpisodes": 2,
      "episodes": [{
         "episodeID": 40,
         "episodeName": "Season 2 episode 1",
         "lengthMin": 50,
         "minWatched": 50,
         "date": "2022-04-25"
      },
      {
         "episodeID": 50,
         "episodeName": "Season 2 episode 2",
         "lengthMin": 45,
         "minWatched": 30,
         "date": "2022-04-27"
      }]
   },
   {
      "seasonNum": 3,
      "numEpisodes": 2,
      "episodes": [{
         "episodeID": 60,
         "episodeName": "Season 3 episode 1",
         "lengthMin": 50,
         "minWatched": 50,
         "date": "2022-04-25"
      },
      {
         "episodeID": 70,
         "episodeName": "Season 3 episode 2",
         "lengthMin": 45,
         "minWatched": 30,
         "date": "2022 - 04 - 27 "
      }]
   }]
},
{
   "showName": "Bienvenu",
   "showId": 15,
   "showtype": "tvseries",
   "genres": ["comedy", "french"],
   "numSeasons": 2,
   "seriesInfo": [{
      "seasonNum": 1,
      "numEpisodes": 2,
      "episodes": [{
         "episodeID": 20,
         "episodeName": "Bonjour",
         "lengthMin": 45,
         "minWatched": 45,
         "date": "2022-03-07"
      },
      {
         "episodeID": 30,
         "episodeName": "Merci",
         "lengthMin": 42,
         "minWatched": 42,
         "date": "2022-03-08"
      }]
   }]
}]}}

筛选表中的数据

可以通过在 WHERE 子句中指定筛选条件来筛选查询结果。通常，过滤器条件由通过逻辑运算符 AND 或 OR 连接的一个或多个比较表达式组成。还支持以下比较运算符：=、！=、>、>=、< 和 <=。

示例 1：查找乘客行李的标签号以及给定预订编号 FH7G1W 的乘客全名。

SELECT bag.fullName, bag.bagInfo[].tagNum FROM BaggageInfo bag
WHERE bag.confNo="FH7G1W"

解释：提取与给定保留编号对应的标记编号。

输出：

{"fullName":"Rosalia Triplett","tagNum":"17657806215913"}

注意：为了更好地理解，下面显示了包含所有静态字段和 bagInfo JSON 的数据行。

   {
   "ticketNo": 1762344493810,
   "fullName": "Adam Phillips",
   "gender": "M",
   "contactPhone": "893-324-1064",
   "confNo": "LE6J4Z",
   "bags": [
     {
       "id": "79039899165297",
       "tagNum": "17657806255240",
       "routing": "MIA/LAX/MEL",
       "lastActionCode": "OFFLOAD",
       "lastActionDesc": "OFFLOAD",
       "lastSeenStation": "MEL",
       "flightLegs": [
         {
           "flightNo": "BM604",
           "flightDate": "2019-02-01T01:00:00",
           "fltRouteSrc": "MIA",
           "fltRouteDest": "LAX",
           "estimatedArrival": "2019-02-01T03:00:00",
           "actions": [
             {
               "actionAt": "MIA",
               "actionCode": "ONLOAD to LAX",
               "actionTime": "2019-02-01T01:13:00"
             },
             {
               "actionAt": "MIA",
               "actionCode": "BagTag Scan at MIA",
               "actionTime": "2019-02-01T00:47:00"
             },
             {
               "actionAt": "MIA",
               "actionCode": "Checkin at MIA",
               "actionTime": "2019-02-01T23:38:00"
             }
           ]
         },
         {
           "flightNo": "BM667",
           "flightDate": "2019-01-31T22:13:00",
           "fltRouteSrc": "LAX",
           "fltRouteDest": "MEL",
           "estimatedArrival": "2019-02-02T03:15:00",
           "actions": [
             {
               "actionAt": "MEL",
               "actionCode": "Offload to Carousel at MEL",
               "actionTime": "2019-02-02T03:15:00"
             },
             {
               "actionAt": "LAX",
               "actionCode": "ONLOAD to MEL",
               "actionTime": "2019-02-01T07:35:00"
             },
             {
               "actionAt": "LAX",
               "actionCode": "OFFLOAD from LAX",
               "actionTime": "2019-02-01T07:18:00"
             }
           ]
         }
       ],
       "lastSeenTimeGmt": "2019-02-02T03:13:00",
       "bagArrivalDate": "2019.02.02T03:13:00"
     }
   ]
  }

 {: .infoboxnote}

示例 2：上次看到具有给定预订编号 FH7G1W 的行李在哪里？此外，获取行李的标签号。

SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenStation
FROM BaggageInfo bag WHERE bag.confNo="FH7G1W"

解释：bagInfo 是 JSON，并且填充了文档数组。可以提取特定预订号码的全名和最后查看的车站。

输出：

{"fullName":"Rosalia Triplett","tagNum":"17657806215913",
"lastSeenStation":"VIE"}

示例 3：为票号为 1762340579411 的乘客选择行李（标记和上次查看时间）的详细信息。

SELECT bag.ticketNo, bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenStation
FROM BaggageInfo bag where bag.ticketNo=1762320369957

解释：bagInfo 是 JSON，并且填充了文档数组。可以提取特定票证编号的全名、标记编号和上次查看的站。

输出：

{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],
"lastSeenStation":["JTR","JTR"]}

示例 4：获取姓氏、帐户到期日以及用户使用 acct_id 1 观看的节目。

SELECT account_expiry, acct.acct_data.lastName, acct.acct_data.contentStreamed[].showName FROM stream_acct acct WHERE acct_id=1

解释：acct_data 是 JSON，并且填充了文档数组。将提取特定帐户 ID 的姓氏、帐户到期日期和显示名称。

输出：

{"account_expiry":"2023-10-18T00:00:00.0Z","lastName":"Phillips","showName":["At the Ranch","Bienvenu"]}

使用控制台提取数据

查看表数据

使用 SQL 命令提取数据

相关主题