SQL standard introduction and SQL Introduction

SQL standard introduction and SQL Introduction

Press: This is based on the short articles I wrote a long time ago.

SQL is the abbreviation of Structured Query Language. Its predecessor is the SEQUEL Language used by the famous relational database prototype System R. As a standard language for accessing relational databases, SQL has been widely used since its publication. It is not only supported by famous large-scale commercial database products such as Oracle, DB2, Sybase, and SQL Server, many open-source database products, such as PostgreSQL and MySQL, also support SQL, and even some small products such as Access. In recent years, the booming NoSQL system initially declared that it no longer needed SQL, and later had to be corrected as Not Only SQL to embrace SQL.

The blue giant IBM has a significant impact on the formation and standardization of relational databases and SQL languages. The first version of SQL standard SQL86 is based on the System R manual. Unfortunately, IBM didn't have any idea of productization at first, But Oracle launched a commercial product supporting SQL in 1979. With the development of database technology and applications, it has become a realistic need to provide consistent languages for different relational database systems.

The organizations that have the greatest impact on SQL standards are naturally well-known database vendors, and specific producers are some non-profit organizations, such as ISO of the International Standardization Organization and ANSI of the American National Standards Committee. Countries usually develop their own national standards in accordance with the ISO and ANSI standards (many of the standards of the two institutions are similar. As a member of the ISO Standards Committee, China often translates Chinese versions of international standards. Standards in order to avoid using specific product terms, many terms are often abstracted, which increases the difficulty of reading and understanding. After being translated into Chinese, it is easier to get unsatisfactory words. For database system implementers and users, it is often better to directly read the English version. While formal standards are not as free as RFC can be obtained from the Internet, the draft standards are easier to find (e.g., http://www.jtc1sc32.org/doc ). There is no substantive difference between the draft standard to be approved and the final standard, meeting the needs of daily work.

The following is a brief history of SQL development:
1986, ANSI X3.135-1986, ISO/IEC 9075: 1986, SQL-86
1989, ANSI X3.135-1989, ISO/IEC 9075: 1989, SQL-89
1992, ANSI X3.135-1992, ISO/IEC 9075: 1992, SQL-92 (SQL2)
1999, ISO/IEC 9075: 1999, SQL: 1999 (SQL3)
2003, ISO/IEC 9075: 2003, SQL: 2003
2008, ISO/IEC 9075: 2008, SQL: 2008
2011, ISO/IEC 9075: 2011, SQL: 2011

Careful readers can find that, starting from SQL: 1999, the short horizontal line (-) in the standard abbreviation is replaced with a colon (:), and the standard year is changed to four digits. The reason for the previous modification is that the ISO standard adopts colons, while the ANSI standard always adopts a hyphen. The reason for the last modification is that the standard naming also encountered the 2000 issue.

SQL86 has only dozens of pages, SQL92 has about 500 pages, and SQL99 has more than 1000 pages. It can be seen that from SQL99, the SQL standard is very huge, and the content is all-encompassing. No one can grasp all the content of the standard. Take SQL: 2003 as an example. It consists of the following nine parts (the gap in the intermediate number is caused by the standard that has been occupied and then discarded ):

ISO/IEC9075-1: Framework (SQL/Framework)
ISO/IEC 9075-2: Foundation (SQL/Foundation)
ISO/IEC 9075-3: Call Level Interface (SQL/CLI)
ISO/IEC 9075-4: Persistent Stored Modules (SQL/PSM)
ISO/IEC 9075-9: Management of External Data (SQL/MED)
ISO/IEC 9075-10: Object Language Bindings (SQL/OLB)
ISO/IEC 9075-11: Information and Definition Schemas (SQL/Schemata)
ISO/IEC 9075-13: Java Routines and Types Using the Java Programming Language (SQL/JRT)
ISO/IEC 9075-14: XML-Related Specifications (SQL/XML)

A technical committee JTC1/SC32 jointly established by ISO and IEC. Due to the fact that the relational database market is very mature, the competition is not fierce enough, and the standards themselves become too large, SQL3 has been developed for a long period, and there are also a lot of controversy during the formulation period. For example, the article Is SQL a Real Standard Anymore? I doubt the SQL standardization process.

To understand the standard content, we recommend that you read SQL92 in a general way (because it involves the most basic and core content of SQL), and then read other standards incrementally. During each update of the standards, the members of the Committee will provide you with a better introduction document. For example, for the latest SQL: 2011, SIGMODRecord has a very good introduction: http://www.sigmod.org/publications/sigmod-record/1203/pdfs/10.industry.zemke.pdf

Finally, we will briefly introduce the compliance of SQL standards. Most people mention the SQL standard, and the content involved is actually the most basic or core part of sql92. SQL92 is classified, including entry level, excessive level, intermediate level, and full level. To verify the compliance of specific products with standards, NIST also launched a project to test the compliance of standards: http://itl.nist.gov/div897/ctg/ SQL _form.htm. However, the SQL standard contains too much content, and many features become increasingly unimportant for new SQL products. Since SQL99, the Compliance Definition in the standard is no longer classified, but changed to the core compatibility and feature compatibility. No organization has launched an authoritative SQL standard compliance test certification.