DML

Data Manipulation Language (DML) statements are used to query or manipulate data in existing schema objects.

DDL statements change database structures, whereas DML statements query or modify content. For example, the ALTER TABLE statement changes the structure of a table, whereas the INSERT statement adds one or more rows to a table.

DML statements are the most frequently used SQL statements. You can use them to perform the following operations:

• Use the SELECT statement to query data in one or more tables or views.

• Use the INSERT statement to add new data rows to a table or view by value insertion or query insertion.

• Use the UPDATE statement to change column values in an existing row of a table or view.

• Use the MERGE statement to update or insert a row into a table or a view based on the specified conditions.

• Use the DELETE statement to remove rows from a table or view.

• Use the EXPLAIN statement to view the execution plan for an SQL statement.

The following example uses DML statements to query the customer table, insert rows into customer, update a row, and then delete it.

CREATE TABLE customer (
    cust_id INT PRIMARY KEY,
    cust_name VARCHAR(50),
    cust_surname VARCHAR(50)
);
SELECT * FROM customer;
INSERT INTO customer VALUES(1234,'Tom','Jacy');
UPDATE customer SET cust_name = 'Tomy' WHERE cust_id = 11;
DELETE FROM customer WHERE cust_id = 11;

A transaction is a set of DML statements that make up a logical work unit. Unlike DDL statements, DML statements do not cause an implicit commit.

For example, a transfer transaction may involve three discrete operations: decreasing the savings account balance, increasing the checking account balance, and recording the transfer in the account history table. Such a transfer transaction can be called a transaction.