SQL (Structured Query Language) serves as the query language for interacting with relational databases. It enables the storage, manipulation, and retrieval of data from a database. Commands written in SQL to perform specific tasks are referred to as SQL commands. These commands function as written instructions to communicate with the database.
SQL commands facilitate a wide range of tasks, including table creation, data insertion into tables, table modification, the establishment of relationships between tables, and even setting user permissions within the database.
These commands are grouped into 5 categories
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
- Data Query Language (DQL)
Note: For this article, MySQL will be the preferred database used in examples and demonstrations.
DDL: Data Definition Language
Data Definition Language commands define the database structure or schema. It is a set of SQL commands used to create, modify, and delete database structures.
Commands include:
CREATE, ALTER, DROP, TRUNCATE, RENAME
CREATE:
This command is used in creating the database and its associated objects. Some objects of the database include Table, View, Stored Procedure, Trigger, etc
Examples
CREATE DATABASE Students_DB
CREATE TABLE table_name (
column_name1 data_type(size),
column_name2 data_type(size)
);
There are also some advanced use cases of the create statement, where it can be used to create views, stored procedures, etc.
Note: These are advanced SQL concepts, so if you aren’t feeling adventurous at the moment, you can safely ignore these concepts.
CREATE VIEW view_name
AS
SELECT column1 [,column2 ] from table_name
CREATE PROCEDURE
procedure_name()
BEGIN
SELECT * FROM Table_name
END
ALTER
This is used to alter the structure of the database or its objects
The alter command changes the structure of the database objects. This command can be used to add a new attribute.
ALTER TABLE table_name ADD (
column_name1 data_type (size),
column_name2 data_type (size)
);
Also, to modify existing attributes/columns added to a table, the alter command comes in handy
Syntax
ALTER TABLE table_name MODIFY (
column_name new_data_type(new_size)
);
ALTER TABLE Students MODIFY (
level char(30)
);
To drop a column, the alter command can also perform such an operation
ALTER TABLE table_name DROP COLUMN column_name;
To rename an existing attribute/column name, we can also make use of the alter command
ALTER TABLE Table_name
RENAME COLUMN Old_column_name TO New_column_name;
DROP
This command removes/deletes a database or table
DROP DATABASE Students_DB
DROP TABLE Students
TRUNCATE
This command will delete all the records/rows present in a table. The database table isn’t removed, only the records stored
TRUNCATE TABLE Students
RENAME
Renames an object existing in a database.
RENAME old_table_name TO new_table_name
RENAME Employees_Info TO Employees
DML: Data Manipulation Language
These commands are responsible for performing all types of data manipulation. It allows the user to modify a database table by inserting, modifying/updating, and deleting its data.
Rollbacks(reverse of query operations) to DML statements are possible if the operations we carried out were made out of error.
DML Commands include
INSERT, UPDATE, DELETE
INSERT
This command is used to insert new records into a database table
INSERT INTO Employees (emp_id, name, department)
VALUES (“rec64”, “John”, “Finance);
UPDATE
This command modifies or updates records stored in a table
UPDATE Employees SET name = “Mark”
WHERE emp_id = “bbc54”;
DELETE
Removes one, more, or all rows/records in a table
DELETE FROM Employees;
The above command removes all records of a company’s employees from the Employees table.
To remove a record based on a condition
DELETE FROM Employees WHERE name = “John”;
DQL: Data Query Language
Data Query Language (DQL) is used to fetch/retrieve data from the database. It uses only one command: the SELECT clause
SELECT column_1 [,column_2] FROM table_name
SELECT * FROM Employees
SELECT name, department FROM Employees
DCL: Data Control Language
Protects information in a table from unauthorized access. With a DCL command, a user can either be enabled or disabled from accessing information from a database or its objects. This command is used in managing roles and permissions.
Syntax
GRANT object_privileges ON table_name
TO user_name1[, user_name2]
Example
GRANT SELECT, UPDATE ON Students TO ‘Aviator’@localhost
Here, the database administrator is granting only select and update privileges to a database user with the name Aviator. This user is only allowed to perform select and update queries on the Students table but other queries such as alter and delete are restricted.
GRANT ALL ON Students TO ‘Aviator’@localhost
Here, the database user named Aviator is granted all privileges on the database table named Students. The user can create, update, alter, and delete records from a database table named Students.
REVOKE
REVOKE object_privileges ON table_name
FROM user_name1[, user_name2]
Used for taking back permission granted to a user.
REVOKE UPDATE ON Students FROM ‘Aviator’@localhost
If a database administrator wants to take back privileges and permission granted to a user, the revoke command is used.
With the above example, the user named Aviator is being revoked of the update privileges granted to them. In this case, they won’t be able to perform update queries on the database table named Students again.
TCL: Transaction Control Language
TCL manages all operations related to transactions in a database. They make it possible to roll back or commit changes to the database.
TCL commands can only be used with DML commands like INSERT, DELETE, and UPDATE.
Commands under TCL include
COMMIT, ROLLBACK, SAVEPOINT
COMMIT
Saves all transactions in a database. With this command, all changes made during a transaction are made permanent.
DELETE FROM Students WHERE registration_number = 20;
COMMIT;
ROLLBACK
This command is used to undo transactions that have not already been committed/saved into the database. All changes made during a transaction are undone. This could be because of an error or mistake made during the commit process of a transaction.
DELETE FROM Students WHERE registration_number = 20;
ROLLBACK;
SAVEPOINT
It is used to roll a transaction back to a certain point without having the entire transaction rolled back.
During the process of making a commit into the database, several savepoints can be created at each stage, such as savepoint_1, and savepoint_2.
When we are ready to move to a particular savepoint to inspect what we have done, we could reach that point using the savepoint command.
SAVEPOINT savepoint_name
SAVEPOINT savepoint_1
Conclusion
In this tutorial, you learned the different types of SQL commands and the categories in which they are grouped. I do hope this article gave you some understanding of SQL commands and would serve as a helpful guide as you continue to learn about SQL.
Top comments (0)