Understanding DML, DDL, DCL,TCL SQL Commands in MySQL
Published: 2024-06-11 22:39:21

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.