DEV Community

Ayas Hussein
Ayas Hussein

Posted on

Understanding DML, DDL, DCL,TCL SQL Commands in MySQL

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);

Enter fullscreen mode Exit fullscreen mode

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

Example

SELECT name, position FROM employees;

Enter fullscreen mode Exit fullscreen mode

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

Example

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

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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)
);

Enter fullscreen mode Exit fullscreen mode

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

Example

ALTER TABLE employees 
ADD COLUMN hire_date DATE;

Enter fullscreen mode Exit fullscreen mode

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

Example

DROP TABLE employees;
Enter fullscreen mode Exit fullscreen mode

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

Example

TRUNCATE TABLE employees;

Enter fullscreen mode Exit fullscreen mode

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';

Enter fullscreen mode Exit fullscreen mode

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

Example

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

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)