Data manipulation language

from Wikipedia, the free encyclopedia

The Data Manipulation Language ( DML ; German  data processing language ) is the part of a database language that is used to write, read, change and delete data. DML is the data processing or data processing language of a database and includes the formulation of queries .

In some cases, very different versions of the DML have been designed for earlier and current systems. Examples:

  • In the historical IMS databases, the standalone DML called DL / I is a language for other computer programs. Example:
CALL PLITDLI(FOUR, 'GHU ', DB_PCB, IO_AREA, SSA1)

DELETE FROM bestellungen WHERE bestellstatus IS NULL
SELECT postleitzahl, stadt FROM kunden ORDER BY postleitzahl

While in the first twenty years of database technology the DML was mainly used for programming, today emphasis is placed on direct use by users. So z. In most systems, for example, the SQL-DML can also be used interactively as a command language. Today, knowledge of internal technical memory structures is usually no longer necessary in order to be able to formulate corresponding processing commands. In this case one speaks of descriptive (descriptive) languages.

Special position of the query

The language elements for data query (in SQL , these are the key words SELECT, JOIN, WHEREetc.) sometimes a separate category because of their special status Data Query Language (DQL, "data query language "), more rarely, Data Retrieval Language assigned (DRL). However, these classifications are unusual and not standardized. The assignment to the DML category can be explained by the fact that when a query is made, the data is rarely delivered in its original form, but is mostly "manipulated" (filtered, sorted, etc.).

SQL

In the practically important Structured Query Language , the syntax is as follows: 

   INSERT INTO Relation [( Attribut+ )] VALUES ( ( Konstante+ ) )+
   INSERT INTO Relation [( Attribut+ )] SFW-Block
   UPDATE Relation SET (Attribut=Ausdruck)+ [WHERE Where-Klausel]
   MERGE INTO Relation USING Quelle ON Join-Klausel
       WHEN MATCHED UPDATE SET (Attribut=Ausdruck)+
       WHEN NOT MATCHED [BY TARGET] INSERT (Attributliste) VALUES (Ausdruckliste)
       [WHEN NOT MATCHED BY SOURCE DELETE]
   DELETE FROM Relation [WHERE Where-Klausel]
   TRUNCATE Relation

With INSERT explicitly constructed tuples or the results of an SFW block can be inserted into a relation. More than one line can be processed at a time.

  • Expression from the

UPDATE statement can in particular also refer to the attribute to be manipulated, such as in 

UPDATE Personal SET Gehalt=Gehalt*2 WHERE Abteilung='EDV'
  • If the WHERE clause is omitted from DELETE, all tuples are deleted, but not the relation schema.

The TRUNCATE statement completely empties a table and, in contrast to it, DELETE FROM Tablealso sets any indexes (the data structure on which the index is based is completely emptied) and auto-increment values ​​to the default values. It should be noted that TRUNCATEwith some DBMS, such as MSSQL, no triggers are triggered.

Examples: 

INSERT INTO Student (MatrNr, Name) VALUES (27123, 'Meier')
Adds a line with the given values ​​for the columns MatrNr and Name in the Student table .
INSERT INTO Student (MatrNr, Name) VALUES (27124, 'Schulz'), (27125, 'Schmidt')
Adds two rows with the specified values ​​for the MatrNr and Name columns to the Student table .
INSERT INTO Student VALUES (27126, 'Schmidt')

With the INSERT statement, the first bracket with the attribute name can also be omitted and the values ​​can be inserted directly with values ​​(). However, the values ​​must then be specified in the same order as in the table definition. In addition, values ​​must be provided for all columns of the table. 

INSERT INTO Student (MatrNr, Name) SELECT MatrNr, Name FROM Student_alt
Loads all students from the Student_old table into the Student table .
UPDATE Student SET Name = 'Meier' WHERE MatrNr = 27124
Changes the value of the Name column in the Student table for a specific MatrNr .
DELETE FROM Student
Deletes all rows from the Student table .
DELETE FROM Student WHERE MatrNr = 27124
Deletes the line with the MatrNr 27124 from the Student table .
TRUNCATE TABLE Student

Empties the Student table and sets any existing auto-increment value to the defined standard (usually 1).

Other language elements of the database