Structured Query Language, or SQL, is a standard language for managing and manipulating databases. It is used to perform tasks such as update data on a database, or retrieve data from a database. In this article, we will cover the basic SQL commands that are used to interact with databases.
DDL is used to define or modify the structure of database objects in the database. DDL commands include:
CREATE: This command is used to create the database or its objects like table, index, function, views, triggers, etc. For example, CREATE TABLE Students (ID INT, Name VARCHAR(20)); creates a new table named 'Students' with two columns, 'ID' and 'Name'.
ALTER: This command is used to alter the structure of the database. For example, ALTER TABLE Students ADD Email VARCHAR(50); adds a new column 'Email' to the 'Students' table.
DROP: This command is used to drop an object from the database. For example, DROP TABLE Students; deletes the 'Students' table from the database.
TRUNCATE: This command is used to remove all records from a table, including all spaces allocated for the records are removed. For example, TRUNCATE TABLE Students; removes all records from the 'Students' table.
DML is used to manipulate the data itself. DML commands include:
SELECT: This command is used to select data from a database. The data returned is stored in a result table. For example, SELECT * FROM Students; selects all records from the 'Students' table.
INSERT: This command is used to insert data into a table. For example, INSERT INTO Students (ID, Name) VALUES (1, 'John'); inserts a new record into the 'Students' table.
UPDATE: This command is used to update existing data within a table. For example, UPDATE Students SET Name = 'Jane' WHERE ID = 1; updates the name of the student with ID 1 to 'Jane'.
DELETE: This command is used to delete existing records from a table. For example, DELETE FROM Students WHERE ID = 1; deletes the student with ID 1 from the 'Students' table.
DCL is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. DCL commands include:
GRANT: This command is used to give a user's access privileges to a database. For example, GRANT SELECT, INSERT, UPDATE ON Students TO user1; gives 'user1' the permission to select, insert, and update records in the 'Students' table.
REVOKE: This command is used to take back permissions from a user. For example, REVOKE UPDATE ON Students FROM user1; takes away 'user1's permission to update records in the 'Students' table.
TCL commands deal with the transaction within the database. TCL commands include:
COMMIT: This command is used to save all the transactions to the database. For example, after inserting a new record into the 'Students' table, COMMIT; will save that transaction.
ROLLBACK: This command is used to undo transactions that haven't already been saved to the database. For example, if you insert a new record into the 'Students' table but then decide you don't want to keep that new record, ROLLBACK; will undo that insertion.
SAVEPOINT: This command is used to temporarily save a transaction so you can roll back to that point if needed. For example, SAVEPOINT savepoint1; creates a new savepoint named 'savepoint1'.
By understanding these basic SQL commands, you can begin to interact with databases by creating and modifying tables, manipulating data, controlling access, and managing transactions.