Data manipulation language
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)
- In SQL , in addition to Data Definition (DDL) and Data Control Language (DCL), it is available in the form of English command clauses. Examples:
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
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.).
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.
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).