Atitit sql之道艾龙著 attilax 1. Ddl dml 3 2. Crud 3 3. 高级sql 3 3.1. Merge 3 3.2. 数据库翻页 limit offset系列

{\displaystyle \left.{\begin{array}{rl}\scriptstyle {\mathtt {UPDATE~clause}}&\{{\mathtt {UPDATE\ country}}\\\scriptstyle {\mathtt {SET~clause}}&\{{\mathtt {SET\ population=~}}\overbrace {\mathtt {population+1}} ^{\mathtt {expression}}\\\scriptstyle {\mathtt {WHERE~clause}}&\{{\mathtt {WHERE\ \underbrace {{name=}\overbrace {'USA'} ^{expression}} _{predicate};}}\end{array}}\right\}{\scriptstyle {\texttt {statement}}}}

A chart showing several of the SQL language elements that compose a single statement

The SQL language is subdivided into several language elements, including:

· Clauses, which are constituent components of statements and queries. (In some cases, these are optional.)[19]

· Expressions, which can produce either scalar values, or tables consisting of columns and rows of data

· Predicates, which specify conditions that can be evaluated to SQLthree-valued logic (3VL) (true/false/unknown) or Boolean truth valuesand are used to limit the effects of statements and queries, or to change program flow.

· Queries, which retrieve the data based on specific criteria. This is an important element of SQL.

· Statements, which may have a persistent effect on schemata and data, or may control transactions, program flow, connections, sessions, or diagnostics.

· SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.

· Insignificant whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.

sql语言被细分为几个语言元素，其中包括：

· 条款这是语句和查询的组成部分。(在某些情况下，这些是可选的)[19]

· 表达它可以产生标量值，或表由柱和行数据

· 谓词指定可对sql进行评估的条件。三值逻辑(3VL)(真实/假/未知)或布尔型真值并且用于限制语句和查询的效果，或者更改程序流。

· 查询检索基于特定标准的数据。这是一个重要的元素SQL...

· 陈述它可能会对模式和数据产生持久影响，或者可能控制交易程序流、连接、会话或诊断。

· sql语句还包括分号(“；”)声明终结者。虽然每个平台都不需要它，但它被定义为sql语法的标准部分。

· 无意义空白通常忽略sql语句和查询，从而更容易格式化sql代码以便于可读性。

5. 连接 join

6. 聚合

7. 错误处理

8. 标准库函数

8.1. 加密解密与二进制转换函数

8.2. 数学系列

8.3. 字符串系列

8.4. 日期运算系列

8.5. 控制流程函数条件判断函数；

8.6. 5 搜索函数

8.7. 6 加密函数

8.8. 7 信息函数

8.9. 8 其他函数

8.10. 9 聚合函数

8.11. 系统信息函数；

8.12. 格式化函数；

9. 数据库报表系统

10. 数据库Sql编程，存储过程sp，视图触发器

10.1. Sp存储过程

10.2. Udf自定义函数

10.3. Trigger触发器

10.4. View视图

10.5. 游标

11. Sql标准化

11.1. 1. Sql标准化的历史3

11.2. 1.1. Sql92标准4

11.3. 1.2. Sql99标准4

11.4. 1.3. SQL:2003为例，它包括以下9个部分 5

11.5. 1.4. Sql2006标准6

11.6. 1.5. Sql2008标准7

11.7. 1.6. SQL:2011 7

11.8. href="https://en.wikipedia.org/wiki/SQL:2016" title="SQL:2016" sql：2016

2016年

sql：2016

添加行模式匹配、多态表函数、json。

11.9. 历年的sql标准化表

年	名称	化名	评论意见
一九八六年	SQL	SQL	首先由ansi形式化。
一九八九年	SQL	FIPS127-1	添加完整性约束的小修订，采用了作为127-1。
一九九二年	SQL	SQL2，FIPS 127-2	主要修订(Iso 9075)，入门级SQL采用了作为127-2。
1999年	sql：1999	n.SQL3	添加正则表达式匹配，递归查询(如：传递闭包)触发器支持程序和控制流语句、非标量类型(数组)以及一些面向对象的特性(例如结构化类型那就是。支持将sql嵌入到java中(sql/OLB()反之亦然(sql/JRT)
2003年	sql：2003		介绍XML相关特性(sql/xml)窗口函数标准化序列，以及具有自动生成值的列(包括标识列)。
2006年	sql：2006		iso/iec 9075-14：2006定义了sql可以使用xml的方法。它定义了在sql数据库中导入和存储xml数据的方法，并在数据库中操作它，并以xml形式发布xml和常规sql数据。此外，它允许应用程序将查询集成到它们的sql代码中，XQuery由世界网联合会发布的xml查询语言(W3C)，同时访问普通sql数据和xml文档。[31]
2008年	sql：2008		用外部游标定义进行命令行顺序。添加代替触发器，截断语句，[32]取条款。
2011年	sql：2011		添加时间数据(句点)[33](更多信息：时态数据库#历史那就是。增强窗口函数还有取条款。[34]
2016年	sql：2016		添加行模式匹配、多态表函数、json。

感兴趣的缔约方可以从iso购买sql标准文档，[35]iec或a

12. sql标准分为九个部分。

Interested parties may purchase SQL standards documents from ISO,[35]IEC or ANSI. A draft of SQL:2008 is freely available as a zip archive.[36]

The SQL standard is divided into nine parts.

· ISO/IEC 9075-1:2016 Part 1: Framework (SQL/Framework). It provides logical concepts.

· ISO/IEC 9075-2:2016 Part 2: Foundation (SQL/Foundation). It contains the most central elements of the language and consists of bothmandatory and optional features.

· ISO/IEC 9075-3:2016 Part 3: Call-Level Interface (SQL/CLI). It defines interfacing components (structures, procedures, variable bindings) that can be used to execute SQL statements from applications written in Ada, C respectively C++, COBOL, Fortran, MUMPS, Pascal or PL/I. (For Java see part 10.) SQL/CLI is defined in such a way that SQL statements and SQL/CLI procedure calls are treated as separate from the calling application's source code. Open Database Connectivity is a well-known superset of SQL/CLI. This part of the standard consists solely ofmandatory features.

· ISO/IEC 9075-4:2016 Part 4: Persistent stored modules (SQL/PSM). It standardizes procedural extensions for SQL, including flow of control, condition handling, statement condition signals and resignals, cursors and local variables, and assignment of expressions to variables and parameters. In addition, SQL/PSM formalizes declaration and maintenance of persistent database language routines (e.g., "stored procedures"). This part of the standard consists solely of optionalfeatures.

· Part-6: Support for JavaScript Object Notation (JSON). In 2017 ISO/IEC published a first technical report about the effort to integrate the data type JSON into the SQL standard. Please consider that technical reports reflects the current state of the discussion and are not part of the standard.

· ISO/IEC 9075-9:2016 Part 9: Management of External Data (SQL/MED). It provides extensions to SQL that define foreign-data wrappers and datalink types to allow SQL to manage external data. External data is data that is accessible to, but not managed by, an SQL-based DBMS. This part of the standard consists solely of optional features.

· ISO/IEC 9075-10:2016 Part 10: Object language bindings (SQL/OLB). It defines the syntax and semantics of SQLJ, which is SQL embedded in Java (see also part 3). The standard also describes mechanisms to ensure binary portability of SQLJ applications, and specifies various Java packages and their contained classes. This part of the standard consists solely of optional features. Unlike SQL/OLB JDBC defines anAPI and is not part of the SQL standard.[citation needed]

· ISO/IEC 9075-11:2016 Part 11: Information and definition schemas(SQL/Schemata). It defines the Information Schema and Definition Schema, providing a common set of tools to make SQL databases and objects self-describing. These tools include the SQL object identifier, structure and integrity constraints, security and authorization specifications, features and packages of ISO/IEC 9075, support of features provided by SQL-based DBMS implementations, SQL-based DBMS implementation information and sizing items, and the values supported by the DBMS implementations.[37] This part of the standard contains both mandatory and optional features.

· ISO/IEC 9075-13:2016 Part 13: SQL Routines and types using the Java TM programming language (SQL/JRT). It specifies the ability to invoke static Java methods as routines from within SQL applications ('Java-in-the-database'). It also calls for the ability to use Java classes as SQL structured user-defined types. This part of the standard consists solely of optional features.

· ISO/IEC 9075-14:2016 Part 14: XML-Related Specifications (SQL/XML). It specifies SQL-based extensions for using XML in conjunction with SQL. The XML data type is introduced, as well as several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in an SQL database.[31] This part of the standard consists solely of optional features.[citation needed]

ISO/IEC 9075 is complemented by ISO/IEC 13249 SQL Multimedia and Application Packages. This closely related but separate standard is developed by the same committee. It defines interfaces and packages based on SQL. The aim is a unified access to typical database applications like text, pictures, data mining or spatial data.

· ISO/IEC 13249-1:2016 Part 1: Framework

· ISO/IEC 13249-2:2003 Part 2: Full-Text

· ISO/IEC 13249-3:2016 Part 3: Spatial

· ISO/IEC 13249-5:2003 Part 5: Still image

· ISO/IEC 13249-6:2006 Part 6: Data mining

· ISO/IEC 13249-7:2013 Part 7: History

· ISO/IEC 13249-8:xxxx Part 8: Metadata Registry Access MRA (work in progress)

12.1. Alternatives[edit]

A distinction should be made between alternatives to SQL as a language, and alternatives to the relational model itself. Below are proposed relational alternatives to the SQL language. See navigational database andNoSQL for alternatives to the relational model.

· .QL: object-oriented Datalog

· 4D Query Language (4D QL)

· iso/iec 9075-1：2016部分第1部分：框架(sql/framework)。它提供逻辑概念。

· iso/iec 9075-2：2016部分2：基础(sql/foundation)。它包含了语言中最核心的元素，并且包含强制性和可选特征。

· iso/iec 9075-3：2016部分3：呼叫级接口(sql/cli那就是。它定义了接口组件(结构、程序、变量绑定)，这些组件可以用于从ada、c、c+、cobol、fortran、流行性腮腺炎、pascal或pl/i中编写的应用程序中执行sql语句。(对于java而言，参见第10部分)。sql/cli定义方式是sql语句和sql/cli过程调用被视为与调用应用程序的源代码分离的。开放数据库连接是sql/cli的一个众所周知的超集。该标准的这一部分仅由强制性特征。

· iso/iec 9075-4：2016部分第4部分：持久性存储模块(sql/psm那就是。它规范了sql的程序扩展，包括控制流、状态处理、语句条件信号和子参数、游标和局部变量以及变量和参数的表达式赋值。此外，sql/psm正式声明了持久化数据库语言例程的声明和维护(例如“存储过程”)。该标准的这一部分仅由任选特征。

· 第六部分：支持javascript对象表示法(Json).。2017，iso/iec发布了关于将数据类型json集成到sql标准中的第一项技术报告。请考虑技术报告反映了讨论的现状，而不是标准的一部分。

· iso/iec 9075-9：2016部分9：外部数据管理(sql/med那就是。它提供了对sql的扩展，该扩展定义了外部数据包装器和数据链类型，以允许sql管理外部数据。外部数据是一个可访问但未管理的sql数据库dbms的数据。该标准的这一部分仅由任选特征。

· iso/iec 9075-10：2016部分第10部分：对象语言绑定(sql/OLB那就是。它定义了语法和语义SQLJ这是java嵌入的java(参见第3部分)。该标准还描述了确保SQLJ应用程序二进制可移植性的机制，并指定了各种java包及其包含的类。该标准的这一部分仅由任选特征。与sql/OLB不同。JDBC定义API也不是sql标准的一部分。[所需引文]

· iso/iec 9075-11：2016部分11：信息和定义模式(sql/图式那就是。它定义了信息架构和定义架构，提供了一套通用的工具来实现sql数据库和对象的自描述。这些工具包括sql对象标识符、结构和完整性约束、安全和授权规范、iso/iec 9075的功能和包、支持sql数据库实现提供的特性、基于sql的dbms实现信息和大小参数项以及dbms实现所支持的值。[37]该标准的这一部分包含强制性和可选特征。

· iso/iec 9075-13：2016第13部分：使用java tm编程语言编写sql例程和类型(sql/JRT那就是。它指定了将静态java方法作为例程调用sql应用程序(“数据库-数据库”)的能力。它还要求使用java类作为sql结构化用户定义类型的能力。该标准的这一部分仅由任选特征。

· iso/iec 9075-14：2016部分14：与xml相关的规范(sql/xml那就是。它指定了基于sql的扩展，用于使用xml与sql一起使用。|||XML介绍了数据类型，以及几个例程、函数和xml到sql数据类型映射来支持sql数据库中xml的操作和存储。[31]该标准的这一部分仅由任选特征。[所需引文]

iso/iec 9075由iso/iec 13249补充sql多媒体和应用软件包.。这一密切相关但又独立的标准由同一委员会制定。它定义了基于sql的接口和包。其目的是统一访问典型数据库应用程序，如文本、图片、数据挖掘或空间数据...

· iso/iec 13249-1：2016部分第1部分：框架

· iso/iec 13249-2：2003部分2：全文

· iso/iec 13249-3：2016部分3：空间

· iso/iec 13249-5：2003部分第5部分：静止图像

· iso/iec 13249-6：2006部分6：数据挖掘

· iso/iec 13249-7：2013部分7：历史

· iso/iec 13249-8：xxxx部分8：元数据注册表访问 MRA(正在进行中)

12.2. 备选方案[编辑]

应该区分作为语言的sql替代物

· gress)

12.3. Alternatives[edit]

· .QL: object-oriented Datalog

· 4D Query Language (4D QL)

· BQL: a superset that compiles down to SQL

· Datalog: critics suggest that Datalog has two advantages over SQL: it has cleaner semantics, which facilitates program understanding and maintenance, and it is more expressive, in particular for recursive queries.[38]

· HTSQL: URL based query method

· IBM Business System 12 (IBM BS12): one of the first fully relational database management systems, introduced in 1982

· ISBL

· jOOQ: SQL implemented in Java as an internal domain-specific language

· Java Persistence Query Language (JPQL): The query language used by the Java Persistence API and Hibernate persistence library

· LINQ: Runs SQL statements written like language constructs to query collections directly from inside .Net code.

· Object Query Language

· QBE (Query By Example) created by Moshè Zloof, IBM 1977

· Quel introduced in 1974 by the U.C. Berkeley Ingres project.

· Tutorial D

· XQuery

应该区分作为语言的sql替代物和关系模型本身的替代方法。下面是sql语言中的关系式替代方案。见导航数据库和NoSQL对于关系模型的替代方法。

· n..QL：面向对象的电子邮件

· 四维查询语言(4d QL)

· n.BQL：将编译到sql的超集

· n.Datalog评论家们认为n.Datalog与sql有两个优点：它具有更干净的语义，这有助于程序理解和维护，而且它更具有表现力，尤其是递归查询。[38]

· n.HTSQL基于url的查询方法

· ibm业务系统12(Ibm BS12)：第一个完全关系数据库管理系统之一，于1982引入

· n.ISBL

· JOOQ：sql在java中实现为内部域特定语言

· java持久性查询语言(JPQL)：java持久性api使用的查询语言和冬眠持久性库

· 林克：运行sql语句，如语言构造那样直接从内部查询集合。.net密码。

· 对象查询语言

· QBE(以实例查询由ibm 1977公司创建的

· 奎尔1974由伯克利大学伯克利分校项目介绍。

· 教程d

· XQuery

13. Sql性能优化与explain

14. Sql解析与美化

14.1. 源码高亮

14.2. Sql解析

15. 常用的数据库算法

15.1. Join算法

15.2. Groupby算法

15.3. 聚合union算法

16. Atitit 业务逻辑的未来在于sql

界面gui的dsl就是h5了。。

数据库就是sql了。。

中间的业务逻辑dsl，一直群星着陆，目前还没有个标准化统一的。。目前貌似主流java为主。Sql，工作流图形化dsl，脚本js 等都部分的分站了部分的业务逻辑。。

为什么业务逻辑的dsl如此的繁复。。不像sql一样很早就统一了。。Gui方面的今年也基本统一了。。现在就差业务方面的dsl了。。

我想，可能是业务系统绝大部分要操作数据库。。这导致了很难发明堪比sql的dsl，而且又足够通用的dsl。。。

17. sql扩展 plsql tsql

18. 关联知识点

19. sql-wikipedia

This article is about the database language. For the IATA code, see San Carlos Airport (California).

"SEQUEL" redirects here. For the topic of the word, see sequel. For other uses, see sequel (disambiguation).

SQL
Paradigm	Multi-paradigm:declarative
Family	Programming language
Designed by	Donald D. Chamberlin Raymond F. Boyce
Developer	ISO/IEC
First appeared	1974; 44 years ago
Typing discipline	Static, strong
OS	Cross-platform
File formats	File format details
Filename extension	.sql
Internet media type	application/sql[1][2]
Developed by	ISO/IEC
Initial release	1986
Latest release	SQL:2016 (December 2016; 1 year ago)
Type of format	Database
Standard	ISO/IEC 9075
Open format?	Yes
Major implementations
Many
Dialects
· SQL-86 · SQL-89 · SQL-92 · SQL:1999 · SQL:2003 · SQL:2006 · SQL:2008 · SQL:2011 · SQL:2016
Influenced by
Datalog
Influenced
CQL, LINQ, SOQL, PowerShell,[3] JPQL, jOOQ,N1QL
· Structured Query Language at Wikibooks

SQL(/ˌɛs.kjuːˈɛl/ ( listen)S-Q-L,[4] /ˈsiːkwəl/ "sequel"; Structured Query Language)[5][6][7][8] is adomain-specific language used in programming and designed for managing data held in a relational database management system(RDBMS), or for stream processing in a relational data stream management system (RDSMS). In comparison to older read/write APIs like ISAM orVSAM, SQL offers two main advantages: first, it introduced the concept of accessing many records with one single command; and second, it eliminates the need to specify how to reach a record, e.g. with or without an index.

Originally based upon relational algebra and tuple relational calculus, SQL consists of many types of statements,[9] which may be informally classed as sublanguages, commonly: a data query language (DQL),[a] a data definition language (DDL),[b] a data control language (DCL), and a data manipulation language (DML)[c].[10] The scope of SQL includes data query, data manipulation (insert, update and delete), data definition (schemacreation and modification), and data access control. Although SQL is often described as, and to a great extent is, a declarative language (4GL), it also includes procedural elements.

SQL was one of the first commercial languages for Edgar F. Codd'srelational model, as described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks".[11] Despite not entirely adhering to the relational model as described by Codd, it became the most widely used database language.[12][13]

SQL became a standard of the American National Standards Institute(ANSI) in 1986, and of the International Organization for Standardization(ISO) in 1987.[14] Since then, the standard has been revised to include a larger set of features. Despite the existence of such standards, most SQL code is not completely portable among different database systems without adjustments.

19.1. Contents

· 1History

· 2Design

· 3Syntax

· 4Procedural extensions

· 5Interoperability and standardization

· 6Alternatives

· 7Distributed SQL processing

· 8Criticisms

· 8.1Orthogonality and completeness

· 8.2Nulls

· 9See also

· 10Notes

· 11References

· 12External links

19.2. History[edit]

SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce after learning about the relational model from Ted Codd[15] in the early 1970s.[16] This version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, System R, which a group at IBM San Jose Research Laboratory had developed during the 1970s.[16]

Chamberlin and Boyce's first attempt of a relational database language was Square, but it was difficult to use due to subscript notation. After moving to the San Jose Research Laboratory in 1973, they began work on SEQUEL.[15]The acronym SEQUEL was later changed to SQL because "SEQUEL" was atrademark of the UK-based Hawker Siddeley aircraft company.[17]

In the late 1970s, Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Codd, Chamberlin, and Boyce, and developed their own SQL-based RDBMS with aspirations of selling it to the U.S. Navy, Central Intelligence Agency, and other U.S. governmentagencies. In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) forVAX computers. By the year 1986, ANSI and ISO standard groups officially adopted the standard "Database Language SQL" language definition. New versions of the standard were published in 1996, 1999, 2003, 2006, 2008, 2011, and most recently, 2016. [15]

After testing SQL at customer test sites to determine the usefulness and practicality of the system, IBM began developing commercial products based on their System R prototype including System/38, SQL/DS, and DB2, which were commercially available in 1979, 1981, and 1983, respectively.[18]

19.3. Design[edit]

SQL deviates in several ways from its theoretical foundation, the relational model and its tuple calculus. In that model, a table is a set of tuples, while in SQL, tables and query results are lists of rows: the same row may occur multiple times, and the order of rows can be employed in queries (e.g. in the LIMIT clause).

Critics argue that SQL should be replaced with a language that strictly returns to the original foundation: for example, see The Third Manifesto.

19.4. Syntax[edit]