All Products
Search
Document Center

SQL overview

Last Updated: Jun 18, 2021

Structured Query Language (SQL) is a programming language that is used for specific purposes. All the programs and users can use SQL to access data in ApsaraDB for OceanBase in the same way as access to other current popular relational databases. Even if some platforms and tools allow users to directly access the database through interfaces or UIs, the underlying layers of these platforms and tools still use SQL to access databases.

SQL history

In June 1970, Dr. E. F. Codd in the laboratory of IBM in San Jose, California published the paper A Relational Model of Data for Large Shared Data Banks in the Association for Computing Machinery (ACM) journal. He proposed the concept of the relational model.

In 1974, D.D.Chamberlin and R.F.Boyce in the same laboratory developed a set of Structured English Query Language (SEQUEL) in the relational database system SystemR that was developed by IBM. In IBM Journal of R&D of November 1976, they published a new version of SQL that was named SEQUEL/2. In 1980, SEQUEL/2 was renamed as SQL.

In 1979, Oracle first provided SQL for commercial use and IBM also implemented SQL in the DB2 and SQL/DS database systems.

Nowadays, SQL has become the standard language for relational database management systems (RDBMSs).

SQL standards

In October 1986, the American National Standards Institute (ANSI) adopted SQL as the standard language for RDBMSs and named the language ANSI X3. 135-1986. The International Organization for Standardization (ISO) also adopted SQL as the international standard.

In 1989, ANSI adopted the SQL standard language that was defined in the ANSI X3.135-1989 report, and named the language ANSI SQL 89. This standard replaced the previous version ANSI X3.135-1986.

The following list provides a brief history for 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

Nowadays, the content in most of mentioned SQL standards is actually the basic and core part of SQL-92. ApsaraDB for OceanBase also complies with the SQL-92 standard.

Running of SQL

SQL is an interface that is used to access a relational database, such as the interfaces of ApsaraDB for OceanBase an Oracle, and MySQL. All the SQL statements are the instructions for databases.

Generally, SQL includes the following five parts:

  1. Data Query Language (DQL): also known as the data retrieval language. It is used to retrieve data from tables and describe how to return the data to programs for output. DQL does not change the data content that is stored in databases.

  2. Data Manipulation Language (DML): changes the data content that is stored in databases. This indicates that the language adds, modifies, and deletes data.

  3. Transaction Control Language (TCL): ensures the integrity and consistency of databases. The DML statements in the same transaction must be all successful or fail.

  4. Data Control Language (DCL): the commands that control the permissions to access data. This language controls the permission of a specified account to access the specified database resources.

  5. Data Definition Language (DDL): defines, modifies, and deletes the database resources, such as creating and deleting tables.

Portability of SQL

SQL is a standard language for accessing databases. All the major relational databases support SQL. Therefore, all the programs that are written in SQL are portable. Typically, you can migrate a program from one relational database to the other relational database only after you make only a few modifications.

Vocabulary conventions

  • Bold indicates the graphical user interface (GUI) elements associated with operations or the terms that are defined in text or a vocabulary.
  • Reserved words, keywords, identifiers, and parameters are not case-sensitive. These items are written in uppercase to facilitate reading and identification.

  • The ways in which SQL statements are terminated vary based on the programming environments. In this document, a semicolon (;) is used to identify the end of an SQL statement.

  • Inline code indicates the code that is referenced in the document.
  • To highlight the important information, the text, such as description, notes, and importance, are written in bold in this document.

  • The text of optional parameters in this document is enclosed in square brackets, such as [-n, -quiet].