Data manipulation language

Data Manipulation Language (DML) is a family of computer languages used by computer programs and/or database users to insert, delete and update data in a database. Read-only querying, i.e. SELECT, of this data may be considered to be either part of DML or outside it, depending on the context.

Currently the most popular data manipulation language is that of SQL, which is used to retrieve and manipulate data in a Relational database. Other forms of DML are those used by IMS/DLI, CODASYL databases (such as IDMS), and others.

Data Manipulation Language comprises the 'SQL-data change' statements, which modify stored data but not the schema or database objects. Manipulation of persistent database objects (e.g. tables or stored procedures) via the 'SQL-schema' statements, rather than the data stored within them, is considered to be part of a separate Data Definition Language. In SQL these two categories are similar in their detailed syntax, data types, expressions etc., but distinct in their overall function.

Data Manipulation Languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are:

• SELECT ... FROM ... WHERE ...
• INSERT INTO ... VALUES ...
• UPDATE ... SET ... WHERE ...
• DELETE FROM ... WHERE ...

The purely read-only SELECT query statement is classed with the 'SQL-data' statements and so is considered by the standard to be outside of DML. The SELECT ... INTO form is considered to be DML because it manipulates (i.e. modifies) data. In common practice though, this distinction is not made and SELECT is widely considered to be part of DML.

Most SQL database implementations extend their SQL capabilities by providing imperative, i.e., procedural, languages. Examples of these are Oracle's PL/SQL and DB2's SQL PL.

Data manipulation languages tend to have many different flavors and capabilities between database vendors. There have been a number of standards established for SQL by ANSI, but vendors still provide their own extensions to the standard while not implementing the entire standard.

There are two types of data manipulation languages:

• Procedural
• Declarative

Each SQL DML statement is a declarative command. The individual SQL statements are declarative, as opposed to imperative, in that they describe what the program should accomplish, rather than describing how to go about accomplishing it.

Data manipulation languages were initially only used by computer programs, but (with the advent of SQL) have come to be used by people as well.

See also

• CRUD
• Data Definition Language
• Data Control Language
• Query Language

References

• "The SQL92 standard".