Editing Data manipulation language

{{Short description|Programming language for manipulating data}}
{{Other uses|Manipulation (disambiguation){{!}}Manipulation}}
{{Multiple issues|
{{Refimprove|date=June 2009}}
{{Cleanup|reason=the article focuses almost entirely on SQL.|date=June 2020}}
}}
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]] of a broader [[Database#Languages|database language]] such as [[SQL]], with the DML comprising some of the operators in the language.<ref>{{cite book
|title=Structured Query Language By Example - Volume I: Data Query Language
|first=Mark
|last=Chatham
|year=2012
|isbn=978-1-29119951-2
|page=[https://books.google.com/books?id=64MBBAAAQBAJ&pg=PA8 8]
|publisher=Lulu.com
}}</ref> Read-only selecting of data is sometimes distinguished as being part of a separate [[data query language]] (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]].<ref name="SQL92" >[[#SQL92|SQL92]]</ref> Other forms of DML are those used by [[Information Management System|IMS]]/DLI, [[CODASYL]] databases, such as [[IDMS]] and others.

==SQL==
In SQL, the data manipulation language comprises the ''SQL-data change'' statements,<ref name="SQL92, statements by function" /> which modify stored data but not the [[database schema|schema]] or database objects. Manipulation of persistent database objects, e.g., tables or [[stored procedure]]s, via the SQL schema statements,<ref name="SQL92, statements by function" >[[#SQL92|SQL92]] 4.22.2, SQL statements classified by function</ref> 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.<ref name="SQL92, statements by function" /> 

The ''SQL-data change'' statements are a subset of the ''SQL-data'' statements; this also contains the [[Select (SQL)|SELECT]] query statement,<ref name="SQL92, statements by function" /> 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,<ref name="Oracle, DML">{{cite web
  |title=Data Manipulation Language Statements
  |url=http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/sqlplsql.htm#i18503
  |publisher=[[Oracle Database|Oracle]]
  |quote=Data manipulation language (DML) statements ''query or manipulate'' data in existing schema objects.
}}</ref> so the DML consists of all ''SQL-data'' statements, not only the ''SQL-data change'' statements. The <code>[[Select (SQL)|SELECT ... INTO ...]]</code> 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:

* <code>[[Select (SQL)|SELECT ... FROM ... WHERE ...]]</code> (strictly speaking DQL)
** <code>[[Select (SQL)|SELECT ... INTO ...]]</code>
* <code>[[Insert (SQL)|INSERT INTO ... VALUES ...]]</code>
* <code>[[Update (SQL)|UPDATE ... SET ... WHERE ...]]</code>
* <code>[[Delete (SQL)|DELETE FROM ... WHERE ...]]</code>

For example, the command to insert a row into table '''employees''':
<syntaxhighlight lang="sql">
INSERT INTO employees (first_name, last_name, fname) VALUES ('John', 'Capita', 'xcapit00');
</syntaxhighlight>

==Variants==
Most SQL database implementations extend their SQL capabilities by providing [[Imperative programming|imperative]], i.e. procedural languages.  Examples of these are [[Oracle Database|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]],<ref name="SQL92" /> 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]] and [[declarative programming]].

Data manipulation languages were initially only used within [[computer program]]s, but with the advent of SQL have come to be used interactively by database administrators.

==See also==
* [[Create, read, update and delete|CRUD]]

===Statements===
* [[Select (SQL)]]
* [[Insert (SQL)]]
* [[Update (SQL)]]
* [[Delete (SQL)]]

===Related languages===
* [[Data control language]]
* [[Data definition language]]
* [[Data query language]]

==References==
{{Reflist}}
{{refbegin}}
* {{cite web
  |url=http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt 
  |title=The SQL92 standard
  |ref=SQL92
}}
{{refend}}

==External links==
* [https://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_1001.htm#i2099257 DML Commands in Oracle]

{{Database}}

[[Category:Data modeling]]
[[Category:SQL]]
[[Category:Articles with example SQL code]]