The SQL Commands are instructions , using these commands we can communicate with the database to perform specific tasks, work, functions and queries with data.
Types of SQL Commands:
- DDL – Data Definition Language
- DML – Data Manipulation Language
- DCL – Data Control Language
- DQL – Data Query Language
- TCL – Transaction Control Language
1. Data Definition Language (DDL):
It is used to create and modify the structure of database objects in database.
Types of DDL commands:
- CREATE: It is used to creates objects in the database, like table, index, function, views, store procedure and triggers.
Syntax: CREATE TABLE table_A ( column1 datatype, column2 datatype, column3 datatype, ....);
- ALTER: It is used to alter the structure of the database.
Add New Column: ALTER TABLE Table_Name ADD New_Column_Name varchar(100); Modify Data type of old column: ALTER TABLE Table_Name ALTER COLUMN Column_Name Varchar(100);
- DROP: It is used to delete both the structure and record stored in the table.
Syntax: DROP TABLE Table_name;
- TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.
Syntax: TRUNCATE Table Table_name;
2. Data Manipulation Language(DML):
It is used to retrieve, modify, delete, insert and update data in database.
Types of DML commands:
- INSERT: The INSERT Statement is used to add new rows of data to a table.
Syntax: INSERT INTO Table_Name (Column1, Column2, Column3,.... ColumnN) VALUES (Value1, Value2, Value3, .... ValueN);
- UPDATE: This command is used to update existing data in table.
Syntax: UPDATE table_name SET column1= value1, column2= value2,...columnN = valueN WHERE condition
- DELETE: This command is used to delete particular record or all records from table.
Syntax: Delete particular record: DELETE From Table_Name Where [condition] Delete all records: DELETE From Table_Name
3. Data Control Language(DCL):
It is used to give user access privileges and other controls of the database system. Only database administrator’s & owner’s of the database object can provide and remove the privileges.
Types of DCL commands:
- GRANT: It is used to gives user’s access privileges to database.
Syntax: GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name} [WITH GRANT OPTION];
- REVOKE: It is used to cancel previously granted or denied permissions.
Syntax: REVOKE privilege_name ON object_name FROM {user_name |PUBLIC |role_name}
4. Transaction Control Language(TCL):
TCL commands are used to manage transactions in the database and perform with DML commands like INSERT, UPDATE and DELETE.
- COMMIT: It is used to permanently save any transaction into the database. Read more
Syntax: COMMIT;
- ROLLBACK: It is used to rollback the last transactions, if transactions not commit. Read more
Syntax: ROLLBACK;
- SAVEPOINT: It is used to rollback the transaction back to a certain point without rolling back the entire transaction. Read more
Syntax: SAVEPOINT
5. Data Query Language(DQL):
It is used to get some schema relation based on the query passed to it.
DQL Command:
- SELECT statement is used to query or retrieve data from a table in the database.
Syntax: With condition: SELECT expressions FROM Table_name WHERE conditions; Without condition: SELECT expressions FROM Table_name
Note: Select statement also comes under DML commands.
Hope you enjoy the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.
Recommended SQL Server Post: