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
VALUES (‘Alex Brad’, ‘Software Engineer’, 75000);
2. SELECT
The SELECT command is used to retrieve data from a table.
Example
3. UPDATE
The UPDATE command is used to modify existing data in a table.
Example
SET salary = 80000
WHERE name = ‘John De’;
4. DELETE
The DELETE command is used to remove rows from a table.
Example
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
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
ADD COLUMN hire_date DATE;
3. DROP
The DROP command is used to delete database objects.
Example
4. TRUNCATE
The TRUNCATE command is used to delete all rows from a table, but the table structure remains.
Example
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
2. REVOKE
The REVOKE command is used to remove access privileges from users.
Example
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
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
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
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.
