MySQL is a popular relational database management system used by developers worldwide. It uses Structured Query Language (SQL) to interact with databases. SQL commands can be broadly categorized into Data Manipulation Language (DML), Data Definition Language (DDL), and several other types. In this blog post, we'll explore these categories and provide examples to help you understand their usage.

Data Manipulation Language (DML)
DML commands are used to manipulate data stored in database tables. These commands allow you to insert, update, delete, and retrieve data.

1. INSERT
The INSERT command is used to add new rows to a table.

Example

INSERT INTO employees (name, position, salary) 
VALUES ('Alex Brad', 'Software Engineer', 75000);

2. SELECT
The SELECT command is used to retrieve data from a table.

Example

SELECT name, position FROM employees;

3. UPDATE
The UPDATE command is used to modify existing data in a table.

Example

UPDATE employees 
SET salary = 80000 
WHERE name = 'John De';

4. DELETE
The DELETE command is used to remove rows from a table.

Example

DELETE FROM employees 
WHERE name = 'John De'; #it can be id, or other columns

Data Definition Language (DDL)
DDL commands are used to define and manage database schema. These commands allow you to create, modify, and delete database objects such as tables, indexes, and views.

1. CREATE
The CREATE command is used to create new database objects.

Example

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(50),
    salary DECIMAL(10, 2)
);

2. ALTER
The ALTER command is used to modify existing database objects.

Example

ALTER TABLE employees 
ADD COLUMN hire_date DATE;

3. DROP
The DROP command is used to delete database objects.

Example

DROP TABLE employees;

4. TRUNCATE
The TRUNCATE command is used to delete all rows from a table, but the table structure remains.

Example

TRUNCATE TABLE employees;

Data Control Language (DCL)
DCL commands are used to control access to data in the database. These commands include GRANT and REVOKE.

1. GRANT
The GRANT command is used to give users access privileges to the database.

Example

GRANT SELECT, INSERT ON employees TO 'user'@'localhost';

2. REVOKE
The REVOKE command is used to remove access privileges from users.

Example

REVOKE SELECT, INSERT ON employees FROM 'user'@'localhost';

Transaction Control Language (TCL)
TCL commands are used to manage transactions in the database. These commands include COMMIT, ROLLBACK, and SAVEPOINT.

1. COMMIT
The COMMIT command is used to save all changes made in the current transaction.

Example

START TRANSACTION;
UPDATE employees SET salary = 85000 WHERE name = 'Jane Doe';
COMMIT;

2. ROLLBACK
The ROLLBACK command is used to undo changes made in the current transaction.

Example

START TRANSACTION;
UPDATE employees SET salary = 90000 WHERE name = 'Jane Doe';
ROLLBACK;

3. SAVEPOINT
The SAVEPOINT command is used to set a savepoint within a transaction, allowing partial rollbacks.

Example

START TRANSACTION;
UPDATE employees SET salary = 90000 WHERE name = 'Jane Doe';
SAVEPOINT sp1;
UPDATE employees SET salary = 95000 WHERE name = 'Jane Doe';
ROLLBACK TO sp1;
COMMIT;

Conclusion
Understanding the different types of SQL commands in MySQL is crucial for effective database management. DML commands allow you to manipulate data, DDL commands help define and manage the database schema, DCL commands control access to the data, and TCL commands manage transactions. By mastering these commands, you can perform a wide range of database operations efficiently and securely.