SQL and Basic Commands in SQL
What is SQL?
SQL stands for Structured Query Language. SQL is used to store, access, and manipulate data stored in a database. In the database, data is stored in the form of tables and each table has a well-defined schema. There are many Relational Database Management Systems(RDBMS) that use SQL with some minor changes. Examples of RDBMS are :
MySQL, T-SQL, Oracle SQL, PostgreSQL, Microsoft SQL Server, MariaDB
Basic Commands in SQL
There are 5 basic types of SQL commands:
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Query Language (DQL)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
Now, let's understand these types in detail.
Data Definition Language (DDL)
In DDL, there are commands related to the structure of the table like creating the table, altering the schema of the table, or deleting the table from the database. The commands under the DDL section are:
- CREATE: To create the database and its objects like table, view, trigger, and stored procedure.
- RENAME: To rename the objects of the database like table, view
- ALTER: To change the structure or schema of the objects of the database like adding/removing a column in a table, changing the datatype of a column.
- Truncate: To delete the data stored inside the table and keep the schema of the table unaffected.
- DROP: To drop/delete the object of the database like table, view, trigger, and stored procedure.
Data Manipulation Language (DML)
In DML, there are commands related to storing, updating, or deleting the data stored in the table. The commands under the DML section are:
- INSERT: To insert data/record in the table.
- UPDATE: To update the data/record available in the table.
- DELETE: To delete the data/record available in the table.
Data Query Language (DQL)
In DQL, there is SELECT command which is used to fetch/retrieve the data from the database object like a table or, view.
Data Control Language (DCL)
In DCL, there are commands which are used to manage database administration-related roles like granting/revoking permission to access a database object like a table or, view. The commands under the DCL section are:
- GRANT: To allow a user to access database objects and/or perform operations on those objects like reading, updating, or deleting operations.
- REVOKE: To remove granted permission related to a database object from a user.
Transaction Control Language (TCL)
In TCL, there are commands which are used to manage transactions like insertion, updation, or, deletion of data in a table. TCL commands are used with DML commands (INSERT, UPDATE, DELETE) and used to make the changes permanent. The commands under the TCL section are:
- COMMIT: To permanently save a transaction in the database.
- ROLLBACK: To restore the database to the previous COMMIT stage. ROLLBACK is also used with SAVEPOINT to jump to a SAVEPOINT in a transaction.
- SAVEPOINT: To create a checkpoint that is used to temporarily save a transaction so that you can rollback to that point whenever necessary.
These are the basic commands in SQL which are used to perform various operations in SQL databases.
- Amit Sangwan
- Mar, 20 2022