Relational Database Management Systems - Design and Use of Relational Databases in Chemistry - page 32

MySQL is an open-source RDBMS. It conforms in part to the SQL 92/99
standards. The MySQL company sells and support the MySQL RDBMS.
There are other companies that support MySQL and offer products that
use MySQL as the underlying database. Tharun Kumar Allu describes a
small molecule chemical database cartridge extension for MySQL. 6
4.2 Standard SQL
SQL was first standardized by ANSI in 1986. Updated standards are
referred to as SQL92, SQL99, and SQL2003. Most RDBMS conform to the
SQL92 standard, including some features from later standards. Most
also provide additional features not addressed in standard SQL at all.
Sometimes the differences among the RDBMS are simply minor syntax
differences, but sometimes there are more fundamental differences.
No RDBMS conforms exactly and completely to any SQL standard.
For this reason, topics on SQL almost always concentrate on one particu-
lar RDBMS. One notable exception documents every SQL command and
details differences across different RDBMS. 7 While that topic discusses
differences in detail, this topic discusses concepts of RDBMS in general,
so that any RDBMS could be used to implement the methods described
here. When specific SQL examples are given however, the SQL statement
uses PostgreSQL syntax. Many times this is identical to the syntax for
other RDBMS, or there is only a minor difference.
Some of the more advanced methods described in this topic require
a more speciic use of the RDBMS. The choice made for this topic is
PostgreSQL. In cases where a particular feature of PostgreSQL is used,
a note is added to alert the reader. For example, the array data type in
SQL2003 is implemented in PostgreSQL very differently than in Oracle.
The list _ matches function described in a later chapter of this topic
returns an array of integers that denote which atoms in a structure match
a substructure query. The integration of this function into SQL would be
handled quite differently in PostgreSQL, Oracle and MySQL.
4.3 A Sampling of Differences
It is not feasible to detail every difference among the various RDBMS.
There are several commonly encountered differences that merit some
attention here.
The text data type can be defined using the keyword text, accord-
ing to the SQL99 standard. This is implemented in both PostgreSQL and
Oracle, although one rarely sees this in Oracle. Instead, the varchar or
varchar2 keyword is used. In PostgreSQL, the character varying or
text keyword is common, as well as varchar . At first glance, this differ-
ence may seem minor: changing one keyword to another is a simple thing