Learn through the super-clean Baeldung Pro experience:
>> Membership and Baeldung Pro.
No ads, dark-mode and 6 months free of IntelliJ Idea Ultimate to start with.
1. Overview
In SQL, different types of commands are categorized based on their functionality. These categories include Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL). Additionally, understanding these categories helps in effectively managing and manipulating databases.
In this tutorial, we’ll explore each of these categories, detailing their commands and providing practical examples.
2. Data Definition Language (DDL)
DDL commands define and modify the structure of database objects such as tables, indexes, and schemas. These commands are essential for setting up and altering the database schema. DDL commands are used to create, modify, and delete database structures. Additionally, these commands enable database administrators and developers to manage and optimize databases effectively.
The following table shows some of the DDL commands and their examples:
| Command | Description | Usage Example |
|---|---|---|
| CREATE | Create a new database or its object (table, views, stored procedure, index, and triggers) | CREATE TABLE table_name (column1 data_type, column2 data_type, …); |
| ALTER | Modifies an existing database object | ALTER TABLE table_name ADD COLUMN column_name data_type; |
| DROP | Deletes an existing database object | DROP TABLE table_name; |
| TRUNCATE | Removes all records from a table including all space allocated for the records that are removed | TRUNCATE TABLE table_name; |
| RENAME | Renames a database object | RENAME TABLE old_table_name TO new_table_name; |
| COMMENT | Add comments to the data dictionary | COMMENT ON TABLE table_name IS ‘comment_text’; |
3. Data Manipulation Language (DML)
DML commands are used to manipulate data within existing database objects. These commands enable us to insert, update, delete, and merge data.
The following table shows some of the DML commands and their examples:
| Command | Description | Usage Example |
|---|---|---|
| SELECT | Retrieves data from one or more tables | SELECT column1, column2 FROM table_name WHERE condition; |
| INSERT | Adds new records to a table | INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …); |
| UPDATE | Modifies existing records in a table | UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; |
| DELETE | Removes records from a table | DELETE FROM table_name WHERE condition; |
| MERGE | Merges records into a table | MERGE INTO table_name …; |
| LOCK TABLE |
Control table concurrency | LOCK TABLE table_name IN lock_mode; |
| EXPLAIN PLAN | Describe the access path to data | EXPLAIN PLAN FOR SELECT * FROM table_name; |
| CALL | Call a PL/SQL or Java subprogram | CALL procedure_name(arguments); |
4. Data Control Language (DCL)
DCL commands are used to control access to data within the database. These commands grant or revoke privileges to users.
The following table shows some of the DCL commands and their examples:
| Command | Description | Usage Example |
|---|---|---|
| GRANT | Gives user access privileges to the database | GRANT privilege_type [(column_list)] ON [object_type] object_name TO user [WITH GRANT OPTION]; |
| REVOKE | Removes user access privileges from the database | REVOKE [GRANT OPTION FOR] privilege_type [(column_list)] ON [object_type] object_name FROM user [CASCADE]; |
5. Transaction Control Language (TCL)
TCL commands manage transactions within the database. These commands help ensure data integrity by allowing transactions to be committed or rolled back.
The following table shows some of the TCL commands and their examples:
| Command | Description | Usage Example |
|---|---|---|
| COMMIT | Saves all changes made during the transaction | COMMIT; |
| ROLLBACK | Rolls back a transaction | ROLLBACK; |
| SAVEPOINT | Creates a savepoint within the current transaction | SAVEPOINT savepoint_name; |
| RELEASE SAVEPOINT | Removes a savepoint | RELEASE SAVEPOINT savepoint_name; |
| SET TRANSACTION | Sets the properties for a transaction | SET TRANSACTION READ WRITE; |
| BEGIN TRANSACTION | Starts a new transaction | BEGIN TRANSACTION [transaction_name]; |
6. Conclusion
In this article, we’ve explored the different categories of SQL commands: DDL, DML, DCL, and TCL, and their respective functionalities. Understanding these categories is essential for effectively managing and manipulating databases.
With the appropriate commands, we can ensure data integrity, security, and efficient data operations.