SQL-2016新特性详解
版权申诉
196 浏览量
更新于2024-08-19
收藏 273KB PDF 举报
"这篇PDF文件详细介绍了SQL-2016标准相较于SQL-2003的改进和提升，由多位在数据库领域的专家共同撰写，包括来自IBM、Oracle Corporation等公司的作者。文章讨论了SQL的新特性，统计信息，以及作者的个人资料。此文件可在ResearchGate上找到并查看详细内容。"
SQL-2016是SQL语言的一个重要更新，它在SQL-2003的基础上进行了多方面的改进和增强，旨在提高数据管理和查询的效率、安全性和灵活性。以下是一些关键的提升点：
1. **窗口函数**：SQL-2016引入了更强大的窗口函数，使得在聚合操作中进行复杂的行级计算成为可能。例如，RANK()、ROW_NUMBER()和LAG()、LEAD()函数允许开发人员在数据集内进行排序和计算，无需进行子查询或自连接。
2. **JSON支持**：随着半结构化数据的增加，SQL-2016引入了对JSON的支持，包括JSON数据类型和一系列处理JSON文档的函数，如JSON_VALUE、JSON_QUERY和JSON_MODIFY，这使得在关系型数据库中存储和检索非结构化数据更为便捷。
3. **安全性增强**：新的标准提升了SQL的安全性，包括细粒度的权限控制，如行级别的安全性（Row-Level Security）和动态数据屏蔽（Dynamic Data Masking），这些功能使得数据保护更加完善，能更好地满足隐私法规要求。
4. **性能优化**：SQL-2016引入了新的查询优化技术，比如并行查询优化，这允许数据库引擎更有效地利用多核处理器，加快复杂查询的执行速度。
5. **聚合函数增强**：标准增加了新的聚合函数，如STRING_AGG，允许将多个字符串值合并成一个，这对于报告和分析非常有用。
6. **Temporal Tables**：SQL-2016引入了时间点和系统版本控制的临时表，这种表格记录了数据的历史变化，便于审计和回溯操作。
7. **改进的XML支持**：XML处理能力得到加强，包括XML索引和XML路径语言（XQuery）的扩展，提升了对XML数据的处理效率和灵活性。
8. **增强的全文搜索**：支持更复杂和灵活的全文搜索语法，使得在文本数据中查找信息更为高效。
9. **改进的JOIN操作**：优化了JOIN操作，特别是对于大型数据集的处理，提升了性能。
这些改进使得SQL-2016成为一个更为现代、功能丰富的数据库查询语言，能够应对日益复杂的业务需求和数据挑战。对于数据库管理员、开发人员和数据分析师来说，理解并掌握这些新特性是提升工作效率和提供更高质量服务的关键。
binary strin gs like BLOB) to carry JSON values. Since
there is no standard JSON query language yet, the SQL
standard defines a path language for navigation within
JSON data and a set of SQL built-in functions and
predicates for querying within a JSON document.
We will ill ustrate the SQL/JSON
5
path language and
SQL/JSON operators in the following sections. Due to
space restrictions, we cannot cover all SQ L/JSON
features. For a detailed description of the SQL/JSON
functionality the interested reade r is referred to [15].
2.1 Querying JSON in SQL
2.1.1 Sample data
Our examples will use the table T shown below:
ID JCOL
111 { "Name" : "John Smith",
"address" : {
"streetAddress": "21 2nd Street",
"city": "New York",
"state" : "NY",
"postalCode" : 10021 },
"phoneNumber" : [
{ "type" : "home",
"number" : "212 555-1234" },
{ "type" : "fax",
"number" : "646 555-4567" } ] }
222 { "Name" : "Peter Walker",
"address" : {
"streetAddress": "111 Main Street",
"city": "San Jose",
"state" : "CA",
"postalCode" : 95111 },
"phoneNumber" : [
{ "type" : "home",
"number" : "408 555-9876" },
{ "type" : "office",
"number" : "650 555-2468" } ] }
333 { "Name" : "James Lee" }
In T, the column JCOL contains JSON data stored in a
character string.
Curly braces { } enclose J SON objects. A JSON
object has zero or more comma-separated key/value
pairs, called members. The key is a character string
before a colon; the value is a JSON value placed after a
colon. For example, in each row, the outermost JSON
object has a key called "Name" with varying values in
each row.
5
The support for JSON is specified in Foundation [2] and
not in a separate p art (as is done, e.g., for SQL/XML [9]).
Still, the moniker SQL/JSON is associated with the JSON-
specific functionality in SQL.
Square brackets [ ] enclose JSON arrays. A JSON
array is an ordered, comma-separated list of JSON
values. In the first and second rows, the key called
"phoneNumber" has a value which is a JSON array.
This illustrates how JSON objects and arrays can be
nested arbitrarily.
Scalar JSON values are character strings, numbers, and
the literals true, false and null.
The sample data is fairly homogeneous, but this is not
a requirement of JSON. For example, the elements of
an array do not need to be of the same type, and
objects in different r ows do not have to have the same
keys.
2.1.2 IS J SON predicate
The IS JSON predicate is used to verify that an SQL
value contains a syntactically correct JSON value. For
example, this predicate can be used in a column check
constraint, like this:
CREATE TABLE T (
Id INTEGER PRIMARY KEY,
Jcol CHARACTER VARYING ( 5000 )
CHECK ( Jcol IS JSON ) )
The preceding might have been used to create the table
T, insuring that the value of Jcol is valid JSON in all
rows of T .
In the absence of such a constraint, one could use IS
JSON as a filter to locate valid JSON data, like this:
SELECT * FROM T WHERE Jcol IS JSON
2.1.3 SQL/JSON path expressions
The r emaining SQL/JSON operators to query JSON
use the SQL/JSON path la nguage. It is used to
navigate within a JSON value to its components. It is
similar to XPath for XML and also somewhat si milar
to object/array navigation in the JavaScript language.
A path expression is composed of a sequence of path
steps; each step can be associated with a set of
predicates.
2.1.4 JSON_EXISTS predicate
JSON_EXISTS is used to determine if an SQL/JSON
path expression has any matches in a JSON document.
For example, this query finds the IDs of the rows with
a key called “address”:
SELECT Id
FROM T
WHERE JSON_EXISTS ( Jcol,
'strict $.address' )
The example works as follows. The first argument to
JSON_EXISTS, Jcol, sp ecifies the context item
(JSON value) on which JSON_EXISTS operates. The
keyword strict selects the strict mode; the
52 SIGMOD Record, June 2018 (Vol. 47, No. 2)
剩余10页未读，继续阅读
2020-06-22 上传
2022-06-23 上传
2022-07-01 上传
2024-12-02 上传
NobiGo
- 粉丝: 232
- 资源: 14
我的内容管理
展开
