S QL Command Categories
SQL is essential for database management.
It uses four main command categories.
These are DDL, DML, DCL, and TCL.
Each has a specific purpose, from defining database structure to
controlling access and ensuring data consistency.
SQL Command Summary
DDL
Defines structure. CREATE, ALTER, DROP.
DML
Manipulates data. INSERT, UPDATE, DELETE.
DCL
Controls access. GRANT, REVOKE.
TCL
Manages transactions. COMMIT, ROLLBACK.
Data Definition L anguage (DDL )
Databas e Structure Key Actions
DDL defines the structure of the database. It focuses on DDL commands-
database objects. create,
These include tables, indexes, and views. alter, and
delete database objects.
They provide the blueprint for your database.
DDL Commands with Examples
CREATE TABLE
Creates a new table. Example: CREATE TABLE Employees (ID INT, Name VARCHAR(255));
ALTER TABLE
Modifies an existing table. Example: ALTER TABLE Employees ADD COLUMN Salary
DECIMAL(10, 2);
DROP TABLE
Deletes a table. Example: DROP TABLE Employees;
CREATE INDEX
Creates an index. Example: CREATE INDEX NameIndex ON Employees (Name);
Data Manipulation L anguage (DML )
Data Management Core Functions
DML manipulates data within database objects. It focuses on Commands:
the actual data. insert,
update,
delete, and
retrieve data.
They manage records in the database.
DML Commands with Examples
INSERT
Adds new rows. Example: INSERT INTO Employees (ID, Name, Salary) VALUES
(1, 'John Doe', 50000.00);
UPDATE
Modifies rows. Example: UPDATE Employees SET Salary = 55000.00 WHERE ID =
1;
DELETE
Removes rows. Example: DELETE FROM Employees WHERE ID = 1;
SELECT
Retrieves data. Example: SELECT * FROM Employees WHERE Salary > 50000.00;
Data Control L anguage (DCL )
Acces s Control Security Focus
DCL controls access to the database. It manages privileges Commands grant or revoke access rights. This ensures data
and permissions. security.
DCL Commands with
E xamples
GRANT
Grants privileges. Example: GRANT SELECT, INSERT ON Employees
TO 'user1'@'localhost';
RE VOKE
Removes privileges. Example: REVOKE INSERT ON Employees FROM
'user1'@'localhost';
Transaction Control Language (TCL)
Transaction Management Key Actions
TCL manages transactions. It ensures data consistency. Commands control execution of SQL statements. They
manage transaction states.
TCL Commands with
Examples
BEGIN TRANSACTION
Starts a transaction. Example: START TRANSACTION;
COMMIT
Saves changes. Example: COMMIT;
ROLLBACK
Undoes changes. Example: ROLLBACK;
SAVEPOINT
Sets a rollback point. Example: SAVEPOINT update1;