SQL Commands Types cheat sheet

2016-10-11 2 min read Development Alexey Bogdanov

SQL language includes four types of command. In this tutorial I will describe them shortly.

Data Definition Language (DDL)

DDL is a set of commands for creating, deleting, renaming databases and tables.

CREATE     Creates a new database or a table.
ALTER      Modifies the structure of a database or a table.
DROP	   Deletes a database or a table.
TRUNCATE   Removes all table records, including allocated table spaces.

Data Manipulation Language (DML)

DML is a set of commands for inserting, deleting, updating and selecting data from the database.

SELECT  Retrieve data from the table.
INSERT  Insert data into a table.
UPDATE  Updates the existing data with new data within a table.
DELETE  Deletes the records from the table.
MERGE   Merge statements to INSERT new records or UPDATE  existing records depending on condition matches or not.

Data Control Language (DCL)

DCL is a set of commands for controlling permissions on the data (e.g. defining access rights)

GRANT    Gives privileges to a user for accessing database data.
REVOKE   Removes the given privileges.
ANALYZE  Recomputes various statistics(information about index, cluster, table, etc) that RDBMS uses to plan its database queries.
AUDIT    Tracks the occurrence of a specific SQL statement or all SQL statements during the user sessions.
COMMENT  Writes comment to the data table.

Transaction Control Language (TCL)

TCL is a set of commands for controlling transactions which either completes entirely or not at all.

SAVEPOINT        Defines a new save point and asks the server to remember the current state of the transaction within the current transaction.  It could be later used to Rollback the new changes.
COMMIT           All the elements of the transaction are completed and should now be made persistent and accessible to all concurrent and subsequent transactions.
ROLLBACK         Cancels all changes made to data by that SQL transaction.
SET TRANSACTION  Changes the Isolation level of the transaction (e.g. READ COMMITTED | SERIALIZABLE)