Data Manipulation Language
Encyclopedia
A data manipulation language (DML) is a family of syntax elements similar to a computer programming language
used for inserting, deleting and updating data in a database
. Performing read-only queries of data is sometimes also considered a component of DML.
A popular data manipulation language is that of Structured Query Language (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:
The purely read-only
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.
Data manipulation languages are divided into two types, procedural programming
and declarative programming
.
Each SQL DML statement is a declarative command. The individual SQL statements are declarative, as opposed to imperative
, in that they describe the program's purpose, rather than describing the procedure for accomplishing it.
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.
Programming language
A programming language is an artificial language designed to communicate instructions to a machine, particularly a computer. Programming languages can be used to create programs that control the behavior of a machine and/or to express algorithms precisely....
used for inserting, deleting and updating data in a database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
. Performing read-only queries of data is sometimes also considered a component of DML.
A popular data manipulation language is that of Structured Query Language (SQL), which is used to retrieve and manipulate data
Data
The term data refers to qualitative or quantitative attributes of a variable or set of variables. Data are typically the results of measurements and can be the basis of graphs, images, or observations of a set of variables. Data are often viewed as the lowest level of abstraction from which...
in a relational database
Relational database
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
. Other forms of DML are those used by IMS
Information Management System
IBM Information Management System is a joint hierarchical database and information management system with extensive transaction processing capabilities.- History :...
/DLI, CODASYL
CODASYL
CODASYL is an acronym for "Conference on Data Systems Languages". This was a consortium formed in 1959 to guide the development of a standard programming language that could be used on many computers...
databases, such as IDMS
IDMS
IDMS is primarily a network database management system for mainframes. It was first developed at B.F. Goodrich and later marketed by Cullinane Database Systems...
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
Data Definition Language
A data definition language or data description language is a syntax similar to a computer programming language for defining data structures, especially database schemas.-History:...
. 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
Verb
A verb, from the Latin verbum meaning word, is a word that in syntax conveys an action , or a state of being . In the usual description of English, the basic form, with or without the particle to, is the infinitive...
. In the case of SQL, these verbs are:
-
SELECT ... FROM ... WHERE ...Select (SQL)The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command... -
INSERT INTO ... VALUES ...Insert (SQL)An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES... -
UPDATE ... SET ... WHERE ...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:... -
DELETE FROM ... WHERE ...Delete (SQL)In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
The purely read-only
SELECTSelect (SQL)The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
query statement is classed with the 'SQL-data' statements and so is considered by the standard to be outside of DML. The SELECT ... INTOSelect (SQL)The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
form is considered to be DML because it manipulates (i.e. modifies) data. In common practice though, this distinction is not made and SELECTSelect (SQL)The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
is widely considered to be part of DML.Most SQL database implementations extend their SQL capabilities by providing imperative
Imperative programming
In computer science, imperative programming is a programming paradigm that describes computation in terms of statements that change a program state...
, i.e., procedural, languages. Examples of these are Oracle
Oracle Database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
's PL/SQL
PL/SQL
PL/SQL is Oracle Corporation's procedural extension language for SQL and the Oracle relational database...
and DB2
IBM DB2
The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
'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
Ansi
Ansi is a village in Kaarma Parish, Saare County, on the island of Saaremaa, Estonia....
, 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
Procedural programming can sometimes be used as a synonym for imperative programming , but can also refer to a programming paradigm, derived from structured programming, based upon the concept of the procedure call...
and declarative programming
Declarative programming
In computer science, declarative programming is a programming paradigm that expresses the logic of a computation without describing its control flow. Many languages applying this style attempt to minimize or eliminate side effects by describing what the program should accomplish, rather than...
.
Each SQL DML statement is a declarative command. The individual SQL statements are declarative, as opposed to imperative
Imperative programming
In computer science, imperative programming is a programming paradigm that describes computation in terms of statements that change a program state...
, in that they describe the program's purpose, rather than describing the procedure for accomplishing it.
Data manipulation languages were initially only used within computer program
Computer program
A computer program is a sequence of instructions written to perform a specified task with a computer. A computer requires programs to function, typically executing the program's instructions in a central processor. The program has an executable form that the computer can use directly to execute...
s, but with the advent of SQL have come to be used interactively by database administrators.
See also
- CRUD
- Data definition languageData Definition LanguageA data definition language or data description language is a syntax similar to a computer programming language for defining data structures, especially database schemas.-History:...
- Data control languageData Control LanguageA data control language is a syntax similar to a computer programming language used to control access to data stored in a database. In particular, it is a component of Structured Query Language .Examples of DCL commands include:...
- Query languageQuery languageQuery languages are computer languages used to make queries into databases and information systems.Broadly, query languages can be classified according to whether they are database query languages or information retrieval query languages...