章节大纲

  • 常规

    全部折叠 展开全部

  • SQL方言参考/介绍

    前言

    SQL（结构化查询语言）是现存最古老的编程语言之一，其最早的版本可追溯至1969年。尽管SQL自1986年起已成为标准化语言，但现实中却存在大量不同的实现方式。这些实现之间或多或少存在差异，使得开发能够在多个SQL服务器间通用的应用程序变得尤其困难。

    本维基教科书是一部针对多种SQL语言方言的简明对比参考手册。它列举了一些常见的任务和问题，并分别给出在多个主流SQL服务器中的解决方法。在可能的情况下，书中力求强调通用的解决方案；在无法统一的情况下，则列出最佳实践方法。

    本书的两个主要目标是简洁与完整。显而易见的信息（例如某个函数或查询在所有实现中都具有相同行为的情况）将不会被列出；但如果某些实现存在重要（即便是细微的）差异，而这些差异对开发者来说可能构成重大陷阱，那么这类信息将一定被记录。

    Preamble

    SQL (Structured Query Language) is one of the oldest programming languages in existence, first versions of which date back to 1969. Unfortunately, despite SQL being standardized since 1986, a lot of different implementations exist. They deviate more or less from each other, making developing applications that would work with a range of different SQL servers particularly difficult.

    This wikibook is a compact comparative reference for several SQL language dialects. It lists particular common tasks and problems resolved in terms of several popular SQL server implementations. When possible, it tries to emphasize an universal solution. When it's not possible, it tries to list best practices.

    Two main goals for the book are compactness and completeness. Obvious information, e.g. that a particular function or query does the same thing on all implementations, will not be listed. However, when some implementation(s) have important (even minor) deviations that can be a major pitfall for developers, such information definitely should be listed.

    谁应该使用这本书？

    目标读者包括：

    • 已掌握某一种SQL方言的开发者，想要立即开始为其他SQL实现进行开发（这是一个快速入门的方法）。

    • 正在将现有项目从一种SQL实现移植到另一种或多种SQL实现的开发者。

    • 希望自己的应用程序能在多个SQL服务器上兼容运行的开发者（对开源项目开发者特别有用，他们希望确保应用能运行在各种平台上）。

    • 需要支持多种SQL服务器但不想记住每种服务器所有查询方式和细节的系统管理员。

    • 希望根据项目需求评估哪种SQL服务器最适合的项目经理或首席开发者。

    Who should use this book

    Target audience is:

    • Developers who know a single dialect of SQL and want to develop for other SQL implementation right away (it's a simple way to).
    • Developers who port a ready project from one SQL implementation to another or a bunch of SQL implementations.
    • Developers who want their applications to be portable on a set of SQL servers (it's particularly useful for developers of open source projects that want to ensure that their application can be used on a variety of platforms).
    • System administrators who have to support a wide range of SQL servers and don't want to memorize all possible queries and nuances for every server.
    • Project managers or lead developers that want to estimate what SQL server is best for their project, given a list of requirements.

    谁不应该使用这本书？

    本书不是一本SQL通用学习课程，也不是一本详尽的用户手册——它是一本快速且紧凑的参考书，前提是读者已掌握SQL的基本概念，并知道自己需要查找什么内容。书中讨论的都是相对进阶的主题以及SQL实现中的一些细微但重要的差异，而这些通常并非初学者或入门学习者所需关注的内容。

    Who shouldn't use this book

    This book is not a general learning course, not a comprehensive manual - it's a quick and compact reference that assumes that the reader knows basic concepts of SQL and precisely what is needed. The book discusses fairly advanced topics and minor, but important differences in SQL implementations that beginners / learners generally don't have to worry about.

    技术说明

    只要可能，本书都会引用公开发布的网络文档，便于读者查阅具体细节。因此页面中通常充满了外部链接。

    由于各实现的全称较长，不便频繁书写，因此我们将使用常见的**缩写（加粗显示）**来区分不同的SQL方言。同时，对于每个实现，我们仅在本页提供一次维基百科链接，而不会在每处都添加。

    本书中的许多页面都包含较长的对比表格。建议安装对比表格扩展插件，以便更舒适地阅读这些表格。

    Technical notes

    Always where it is possible, a book tries to reference a web-published documentation, so a reader can always check particular detail, so a page is usually full of external documentation links.

    As the full names of implementations are pretty long and inconvenient to spell out thoroughly every time, we'll use common abbreviations (shown in bold) to distinguish SQL dialects. Also, we only provide links to corresponding Wikipedia articles here, not on every occasion.

    Many pages in this book include comparison tables that can be pretty long. It is recommended to install a comparison table extension to view these tables comfortably.

    本书涵盖的SQL实现

    本书涵盖了多种SQL方言。需要注意的是，对于每种实现，仅描述其最新稳定版本。

    • 标准SQL：包括多个版本（SQL-86、SQL-89、SQL-92、SQL:1999、SQL:2003、SQL:2011）。本书会列出所有主流实现的常见做法，尤其强调某个解决方案适用于的SQL标准版本（例如，如果某功能自X版本起成为标准，则会说明该方案自X版本起可用）。

    • DB2（IBM DB2）

    • Firebird（Firebird 2.5）：一个开源的关系数据库管理系统，源自2000年7月25日Borland发布的InterBase源码。Firebird试图最大程度地接近SQL标准。

    • MonetDB（MonetDB V11）：一个开源的列式存储数据库。

    • MySQL（MySQL 5.0）

    • MSSQL（Microsoft SQL Server 2008）：微软开发的专有RDBMS，主要面向企业市场。最初源于Sybase SQL Server，但后期已基本重写。文档结构清晰，可通过MSDN轻松查阅。

    • Oracle（Oracle Database 11g2）

    • PostgreSQL（PostgreSQL 13）

    • SQLite

    • Virtuoso（运行于Virtuoso Universal Server的OpenLink Virtuoso）

    • Linter

     
     
     
     

    SQL implementations covered in this book

    Several SQL dialects are covered in this book. Note that in all cases of particular implementations, only the latest stable version is described.

    • Standard. There are several versions of SQL standard (SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2011). The book would list all popular practices to do a job in all implementations, particularly emphasizing SQL versions that a particular solution works for (i.e. if some feature became standard since version X, it would be stated that such solution works since version X).
    • DB2 (IBM DB2).
    • Firebird (Firebird 2.5). An open-source RDBMS forked from sources of InterBase that were released by Borland on July 25, 2000. Firebird differs from Interbase and tries to be close to SQL standard as much as possible
    • MonetDB An open-source column-store. (MonetDB. V11)
    • MySQL (MySQL 5.0)
    • MSSQL (Microsoft SQL Server 2008). A proprietary RDBMS produced by Microsoft, targeting enterprise market. Original codebase was derived from Sybase SQL Server, but was mostly rewritten. Documentation is available in MSDN in a well-structured form, quite easy to reference.
    • Oracle (Oracle Database 11g2)
    • PostgreSQL (PostgreSQL 13)
    • SQLite.
    • Virtuoso (OpenLink Virtuoso running on Virtuoso Universal Server).
    • Linter

  • 数据结构定义

  • 函数和表达式

  • 选择查询

  • 编写查询

  • 事务

    SQL version Feature Standard
    SQL:2011    		 DB2 Firebird Ingres Linter MSSQL MySQL
    Vers. 5.x    		 MonetDB Oracle
    Vers. 11.x    		 PostgreSQL SQLite Virtuoso
    ? Start START TRANSACTION
    [transaction characteristics]    		 Implicit SET TRANSACTION ? ? BEGIN TRAN[SACTION] BEGIN [WORK]
    START [TRANSACTION]    		 START TRANSACTION
    BEGIN TRANSACTION    		 Implicit BEGIN [WORK|TRANSACTION]
    START TRANSACTION    		 BEGIN [TRANSACTION] Implicit
    ? Commit COMMIT [WORK] COMMIT [WORK] COMMIT [WORK] [EXEC SQL] COMMIT [WORK] ? COMMIT [WORK]
    COMMIT TRAN[SACTION] [transaction_name]    		 COMMIT [WORK] COMMIT [WORK] [AND [NO] CHAIN] COMMIT [WORK] COMMIT [WORK|TRANSACTION] COMMIT [TRANSACTION]
    END [TRANSACTION]    		 COMMIT WORK
    ? Rollback whole transaction ROLLBACK [WORK] ROLLBACK [WORK] ROLLBACK [WORK] [EXEC SQL] ROLLBACK [WORK] ? ROLLBACK [WORK]
    ROLLBACK TRAN[SACTION]    		 ROLLBACK [WORK] ROLLBACK [WORK] [AND [NO] CHAIN] ROLLBACK [WORK] ROLLBACK [WORK|TRANSACTION] ROLLBACK [TRANSACTION] ROLLBACK WORK
    ? Define a savepoint x, while inside a transaction SAVEPOINT x SAVEPOINT x ON ROLLBACK RETAIN CURSORS SAVEPOINT x SAVEPOINT x ? SAVE TRAN[SACTION] x SAVEPOINT x SAVEPOINT x SAVEPOINT x SAVEPOINT x SAVEPOINT x N/A
    ? Rollback to given savepoint x ROLLBACK [WORK] TO SAVEPOINT x ROLLBACK [WORK] TO SAVEPOINT x ROLLBACK [WORK] TO [SAVEPOINT] x [EXEC SQL] ROLLBACK [WORK] TO x ? ROLLBACK TRAN[SACTION] x ROLLBACK [WORK] TO [SAVEPOINT] x ROLLBACK [WORK] [AND [NO] CHAIN] TO SAVEPOINT x ROLLBACK [WORK] TO [SAVEPOINT] x ROLLBACK [WORK|TRANSACTION] TO [SAVEPOINT] x ROLLBACK [TRANSACTION] TO [SAVEPOINT] x N/A
    ? Release (forget) savepoint x RELEASE SAVEPOINT x RELEASE [TO] SAVEPOINT x RELEASE SAVEPOINT x N/A ? N/A RELEASE SAVEPOINT x RELEASE SAVEPOINT x N/A RELEASE [SAVEPOINT] x RELEASE [SAVEPOINT] x N/A
    ? Prepare transaction named id for two-phase commit ? ? N/A ? ? ? ? N/A ? PREPARE TRANSACTION id N/A ?
    ? Commit prepared transaction named id ? ? N/A ? ? ? ? N/A ? COMMIT PREPARED id N/A ?
    ? Rollback prepared transaction named id ? ? N/A ? ? ? ? N/A ? ROLLBACK PREPARED id N/A ?
      Start Commit Rollback Prepare Execute prepared
    Linter Implicit
    • a COMMIT statement is executed
    • any DDL statement is executed
    • any statement is executed in AUTOCOMMIT mode
    • a ROLLBACK statement is executed
    • a user process is terminated abnormally or disconnects without COMMIT/ROLLBACK

  • 过程语言

  • SQL/XML

    ISO 发布物 (ISO/IEC 9075-14) 是 SQL 标准的一部分。它以前被称为 SQLX 或 SQL/XML。它定义了 XML 数据类型以及作用于此数据类型的函数，还有在此数据类型中创建和处理 XML 对象（元素、属性等）的函数。Oracle 将该数据类型称为 XMLType。

    The ISO publication (ISO/IEC 9075-14) is part of the SQL standard. Formerly it was named SQLX or SQL/XML. It defines the data type XML and functions acting on this data type as well as functions creating and handling XML objects (elements, attributes, ...) within this data type. Oracle denotes the data type XMLType.

    Function Description DB2 SQLite MonetDB MySQL PostgreSQL Firebird OpenLink Virtuoso Oracle MSSQL
    Version Software Version(s) Supported V9 N/A V11+ Planned 8.3+ N/A 3.5-4.x 9-10x Proprietary XML Extensions
    XMLElement() Create an XML Element XMLElement() N/A xmlelement() Planned xmlelement() N/A XMLElement() XMLElement() .query()[1]
    XMLForest() Create an XML Fragment from passed-in components. XMLForest() N/A xmlforest() xmlforest() Planned (patch exists) N/A XMLForest() XMLForest() FOR XML clause[2]
    XMLColAttVal() Create an XML fragment and then expands the resulting XML so that each XML fragment has the name "column" with the attribute "name"   N/A N/A Planned Planned N/A XMLColAttVal() XMLColAttVal() FOR XML clause[2]
    ExtractValue() Takes as arguments an XML instance and an XPath expression and returns a scalar value of the resultant node.   N/A N/A Planned Planned N/A ExtractValue() ExtractValue() .value()[3]
    XMLTransform() Takes as arguments an XML instance and an XSL style sheet, which is itself a form of XML instance. It applies the style sheet to the instance and returns an XML.   N/A N/A Planned Planned N/A XMLTransform() XMLTransform() N/A[4]
    XMLSequence() Takes input and returns either a varray of the top-level nodes in the XML, or an XMLSequence type an XML document for each row of the cursor.   N/A xmlsequence() Planned Planned N/A XMLSequence() XMLSequence() .nodes()[5]
    XMLConcat() Takes as input a series of XML instances, concatenates the series of elements for each row, and returns the concatenated series. XMLConcat N/A N/A Planned Planned (patch exists) N/A XMLConcat() XMLConcat() N/A[6]
    UpdateXML() Takes as arguments an XML instance and an XPath-value pair, and returns an XML instance with the updated value.   N/A N/A Planned Planned N/A UpdateXML() UpdateXML() .modify()[7]

    注释

    1. ↑ MSSQL 2005 及以上版本的 xml 数据类型的 .query() 方法 实现了该功能。

    2. ↑ MSSQL 2000 及以上版本的 SELECT 语句中的 FOR XML 子句 实现了类似功能。

    3. ↑ MSSQL 2005 及以上版本的 xml 数据类型的 .value() 方法 实现了该功能。

    4. ↑ 在 MSSQL 2005 及以上版本中，可以使用 xml 数据类型和 SQLCLR 创建模拟该功能的函数或存储过程。

    5. ↑ MSSQL 2005 及以上版本的 xml 数据类型的 .nodes() 方法 实现了该功能。

    6. ↑ 在 MSSQL 2005 及以上版本中，可以使用字符串连接运算符 +，结合将 xml 数据类型实例显式转换为字符类型，以模拟该功能。

    7. ↑ MSSQL 2005 及以上版本的 xml 数据类型的 .modify() 方法 使用 XML DML 来实现此功能。

    Notes

    1. ↑ The MSSQL 2005 and higher xml data type .query() method performs this function.
    2. ↑ Jump up to:a b The MSSQL 2000 and higher FOR XML clause of the SQL SELECT statement performs a similar function.
    3. ↑ The MSSQL 2005 and higher xml data type .value() method performs this function.
    4. ↑ In MSSQL 2005 and higher the xml data type and SQLCLR can be used to create functions/procedures that simulate this functionality.
    5. ↑ The MSSQL 2005 and higher xml data type .nodes() method performs this function.
    6. ↑ In MSSQL 2005 and higher the + string concatenation operator can be used in conjunction with explicit conversions of xml data type instances to character data types to simulate this function.
    7. ↑ The MSSQL 2005 and higher xml data type .modify() method performs this function using XML DML.

  • 管理

    虽然这个主题不是直接关于 SQL 语言的，但它足够接近，可以将关于经常执行的管理任务的信息收集在一起。

    数据库转储

    数据库转储涉及创建一个文件，该文件存储来自所需数据库/表的所有数据（行）和此数据的模式。最常见的是，它通过一系列 SQL 语句来完成，结合使用 CREATE DATABASE / CREATE TABLE 语句来重新创建模式，并使用 INSERT 语句来重新填充行——因此，通常可以使用命令行 SQL 客户端通过简单地执行查询来直接导入生成的文件。然而，有时需要其他输出格式，并且某些数据库（至少是 MSSQL）缺乏从命令行转储客户端将数据库序列化为 SQL 语句的能力。

    While this topic is not directly about SQL language, it is sufficiently close to gather information about frequently undertaken administrative tasks together.

    Dumping database

    Dumping the database involves creation of a file that stores all the data (rows) and the schema for this data from the desired databases/tables. Most usually it's done as a series of SQL statements, combining CREATE DATABASE / CREATE TABLE statements to recreate the schema and INSERT statements to refill the rows — thus, usually the resulting file can be directly imported using command-line SQL client by simple execution of queries. However, sometimes other output formats are desired and some databases (at least MSSQL) lack the ability to serialize the database as SQL statements from command-line dumper client.

    SQL version Feature Standard
    SQL:2011    		 DB2 Firebird Ingres Linter MSSQL MySQL MonetDB Oracle
    Vers. 11.x    		 PostgreSQL SQLite Virtuoso
    ? Basic invocation ? ? ? copydb dbname options ? bcp database out file options mysqldump options database msqldump -d database ? pg_dump options database sqlite3 dbfile .dump ?
    ? Authentication to target server ? ? ? -uusername -P ? -U username -P password -S host -uusername -ppassword -hhost -Pport -uusername ? -Uusername -Wpassword N/A ?
    ? Save dump into file ? ? ? ? ? in basic invocation -rfile / --result-file=file redirect msqldump output ? -ffile / --file=file .output file
    .once file    		 ?
    ? Dump only schema ? ? ? ? ? N/A -d / --no-data msqldump -d "database" -D ? -s / --schema-only .schema ?
    ? Add DROP schema at start ? ? ? -add_drop ? N/A   N/A ? -c N/A ?
    ? Prevent dumping of access privileges ? ? ? ? ? N/A N/A N/A ? -x / --no-privileges / --no-acl N/A ?
    ? Dump only data ? ? ? ? ? default -n -t / --no-create-db --no-create-info -t tablename ? -s / --data-only N/A ?
    ? Dump data as tab-separated ? ? ? ? ? default --tab=path / -Tpath ? ? ? .mode tabs ?
    ? Dump data as INSERT statements ? ? ? ? ? N/A default default ? -d .mode insert ?
    ? Dump everything as XML file ? ? ? ? ? N/A --xml / -X N/A ? N/A N/A ?
    ? Ordering of data ? ? ? ? ? -h "ORDER (column, ...)" --order-by-primary ? ? ? ? ?

    恢复数据库

    命令行 SQL 查询

    此页面包含可能很大且复杂的比较表格。

    虽然完全可以查看它们的完整状态，但建议安装一个比较表格扩展，该扩展允许选择感兴趣的特定列进行比较。

    Restoring database

    Command-line SQL query

    This page includes comparison tables which can be big and complex.

    While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

    SQL version Feature Standard
    SQL:2011    		 DB2 Firebird Ingres Linter MSSQL MySQL MonetDB Oracle
    Vers. 11.x    		 PostgreSQL SQLite Virtuoso
    ? Basic invocation ? ? ? sql options database ? sqlcmd options mysql options [database] mclient -d database ? psql options [database] sqlite3 filename options ?
    ? Authentication to target server ? ? ? -uusername -Ppassword -Ggroup -Rrole ? -S host -d database -U username -P password -uusername -ppassword -hhost -Pport ? ? -Uusername -Wpassword -hhost -pport N/A ?
    ? Execute query from command line and exit ? ? ? N/A ? -Q query -e query -sstatement ? -c query query ?
    ? Execute query from command line and continue with interactive prompt ? ? ? N/A ? -q query N/A -i ? N/A N/A ?
    ? Input file[1] ? ? ? N/A ? -i file N/A file ? -f file -init file ?
    ? Output to file instead of stdout[2] ? ? ? N/A ? -o file N/A ? ? -o file N/A ?
    ? Copy output also to file ? ? ? N/A ? N/A --tee=file ? ? -L file N/A ?

    References

    1. ↑ Can be also done using shell < redirection
    2. ↑ Can be also done using shell > redirection

  • 参考

    Standards

    • SQL-86
    • SQL-89
    • SQL-92 — full text is available
    • SQL:1999 — can be purchased from ISO
    • SQL:2003 — can be purchased from ISO
    • working draft of SQL:2008, downloadable from Whitemarsh Information Systems Corporation.
    • SQL:2008 — can be purchased from ISO
    • SQL:2011 — can be purchased from ISO

    Official product documentations

    Single implementation references and cheat sheets

    Comparisons

    Migration