Section outline

  • 前言

    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