Structured Query Language (SQL) is a specialized programming language. Like other popular relational databases, SQL is used by all programs and users to access data in OceanBase Database. Even though some platforms and tools allow users to access databases directly through interfaces or graphical user interfaces (GUIs), the underlying access is still based on SQL.
History of SQL
In June 1970, Dr. E. F. Codd from the San Jose, California laboratory of IBM published a paper titled "A Relational Model of Data for Large Shared Data Banks" in the Association for Computing Machinery (ACM) journal and first introduced the concept of the relational model.
In 1974, D. D. Chamberlin and R. F. Boyce from the same laboratory developed a standardized language called SEQUEL (Structured English QUEry Language) for the relational database system SystemR developed by IBM. In November 1976, they published the new version of SQL, called SEQUEL/2, in the IBM Journal of R&D. In 1980, the language was renamed SQL.
In 1979, Oracle released the first commercial SQL implementation, and IBM also implemented SQL in its DB2 and SQL/DS database systems.
Today, SQL has become the standard language for relational database management systems (RDBMS).
SQL standards
In October 1986, the American National Standards Institute (ANSI) adopted SQL as the standard language for RDBMS and named it ANSI X3.135-1986. Later, the International Organization for Standardization (ISO) also adopted SQL as an international standard.
In 1989, ANSI adopted and used the SQL standard language defined in the ANSI X3.135-1989 report and named it ANSI SQL 89. This standard replaced the previous ANSI X3.135-1986 version.
Here 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.
Most of the SQL standards mentioned above are based on the fundamental and core aspects of SQL 92. OceanBase Database currently follows the SQL 92 standard and supports some important features from subsequent SQL standards.
SQL execution
SQL is the interface for accessing relational databases such as OceanBase, Oracle, and MySQL. All SQL statements are commands for operating on databases.
Typically, SQL can be divided into the following five parts:
Data Query Language (DQL): Also known as a data retrieval language, DQL is used to retrieve data from tables and describe how to return the data to programs for output. DQL does not modify the data stored in the database.
Data Manipulation Language (DML): DML is used to modify the data stored in the database, including adding, modifying, and deleting data.
Transaction Control Language (TCL): TCL ensures the integrity and consistency of the database. All Data Manipulation Language (DML) statements within the same transaction either succeed or fail together.
Data Control Language (DCL): DCL consists of commands for controlling data access permissions. It allows you to control the access permissions of specific accounts to specific database resources.
Data Definition Language (DDL): DDL is used to define, modify, and delete resources in the database, such as creating and dropping tables.
Portability of SQL
SQL is the standard language for accessing databases. All major relational databases support SQL, so programs written in SQL are portable. Typically, only minor modifications are needed to port a program from one relational database to another.
Conventions
Bold indicates graphical user interface (GUI) elements associated with operations or terms defined in text or vocabulary.
Keywords, identifiers, and parameters are case-insensitive. For readability and identification, these words are written in uppercase.
The way SQL statements end varies in different programming environments. In this document, a semicolon (;) is used to indicate the end of an SQL statement.
Inline codeindicates code referenced in the document.To highlight important information, the document will bold terms such as "Note," "Important," and "Caution."
Optional parameters in the document are enclosed in square brackets, for example, [-n, -quiet].