A data manipulation language (DML) is a computer
programming language used for adding (inserting), deleting, and modifying (updating) data in a
database. A DML is often a
sublanguage
A sublanguage is a subset of a language. Sublanguages occur in natural language, computer programming language, and relational databases.
In natural language
In informatics, natural language processing, and machine translation, a sublanguage i ...
of a broader
database language such as
SQL, with the DML comprising some of the operators in the language.
Read-only selecting of data is sometimes distinguished as being part of a separate
data query language
Data Query Language (DQL) is part of the base grouping of SQL sub-languages. These sub-languages are mainly categorized into four categories: a data query language (DQL), a data definition language (DDL), a data control language (DCL), and a da ...
(DQL), but it is closely related and sometimes also considered a component of a DML; some operators may perform both selecting (reading) and writing.
A popular data manipulation language is that of
Structured Query Language (SQL), which is used to retrieve and manipulate
data in a
relational database.
[ SQL92] Other forms of DML are those used by
IMS Ims is a Norwegian surname. Notable people with the surname include:
* Gry Tofte Ims (born 1986), Norwegian footballer
* Rolf Anker Ims (born 1958), Norwegian ecologist
See also
* IMS (disambiguation) Ims is a Norwegian surname. Notable people wit ...
/DLI,
CODASYL
CODASYL, the Conference/Committee on Data Systems Languages, was a consortium formed in 1959 to guide the development of a standard programming language that could be used on many computers. This effort led to the development of the programming l ...
databases, such as
IDMS and others.
SQL
In SQL, the 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 procedure
A stored procedure (also termed proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data di ...
s, via the SQL schema statements,
[ SQL92 4.22.2, SQL statements classified by function] rather than the data stored within them, is considered to be part of a separate
data definition language (DDL). In SQL these two categories are similar in their detailed syntax, data types, expressions etc., but distinct in their overall function.
The ''SQL-data change'' statements are a subset of the ''SQL-data'' statements; this also contains the
SELECT query statement,
which strictly speaking is part of the DQL, not the DML. In common practice though, this distinction is not made and ''SELECT'' is widely considered to be part of DML,
so the DML consists of all ''SQL-data'' statements, not only the ''SQL-data change'' statements. The
SELECT ... INTO ...
form combines both selection and manipulation, and thus is strictly considered to be DML because it manipulates (i.e. modifies) data.
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 ...
(strictly speaking DQL)
**
SELECT ... INTO ...
*
INSERT INTO ... VALUES ...
*
UPDATE ... SET ... WHERE ...
*
DELETE FROM ... WHERE ...
For example, the command to insert a row into table employees:
INSERT INTO employees (first_name, last_name, fname) VALUES ('John', 'Capita', 'xcapit00');
Variants
Most SQL database implementations extend their SQL capabilities by providing
imperative, i.e. procedural languages. Examples of these are
Oracle's
PL/SQL and
IBM 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
The American National Standards Institute (ANSI ) is a private non-profit organization that oversees the development of voluntary consensus standards for products, services, processes, systems, and personnel in the United States. The organi ...
,
but vendors still provide their own extensions to the standard while not implementing the entire standard.
Data manipulation languages are divided into two types,
procedural programming
Procedural programming is a programming paradigm, derived from imperative programming, based on the concept of the ''procedure call''. Procedures (a type of routine or subroutine) simply contain a series of computational steps to be carried ...
and
declarative programming.
Data manipulation languages were initially only used within
computer programs, but with the advent of SQL have come to be used interactively by database administrators.
See also
*
CRUD
In computer programming, create, read, update, and delete (CRUD) are the four basic operations of persistent storage. CRUD is also sometimes used to describe user interface conventions that facilitate viewing, searching, and changing information ...
Statements
*
Select (SQL)
*
Insert (SQL)
*
Update (SQL)
An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.
The UPDATE statement has the following form:
:UPDATE ''table_name'' SET ''column_name' ...
*
Delete (SQL)
Related languages
*
Data control language
*
Data definition language
*
Data query language
Data Query Language (DQL) is part of the base grouping of SQL sub-languages. These sub-languages are mainly categorized into four categories: a data query language (DQL), a data definition language (DDL), a data control language (DCL), and a da ...
References
*
External links
DML Commands in Oracle
{{Database
Data modeling
SQL
Articles with example SQL code