Skip to main content

Understanding SQL in MySQL: From DDL to DCL

 

📦 MySQL is a widely-used relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage and manipulate data. SQL is divided into several categories, each serving a specific purpose: DDL, DML, DQL, TCL, and DCL.



1. Data Definition Language (DDL)

DDL commands are used to define and manage database structures such as tables, indexes, and schemas. The main DDL commands include CREATE, ALTER, DROP, and TRUNCATE.


⭐ CREATE: This command creates a new database object.


CREATE TABLE students (

id INT PRIMARY KEY,

name VARCHAR(100),

age INT

);


⭐ ALTER: This command modifies an existing database object.


ALTER TABLE students ADD COLUMN email VARCHAR(100);


⭐ DROP: This command deletes an existing database object.


DROP TABLE students;


⭐ TRUNCATE: This command removes all records from a table without deleting the table itself.


TRUNCATE TABLE students;


2. Data Manipulation Language (DML)

DML commands are used to manipulate data within database objects. The primary DML commands are INSERT, UPDATE, and DELETE.


⭐ INSERT: This command adds new records to a table.


INSERT INTO students (id, name, age) VALUES (1, 'John Doe', 20);


⭐ UPDATE: This command modifies existing records in a table.


UPDATE students SET age = 21 WHERE id = 1;


⭐ DELETE: This command removes records from a table.


DELETE FROM students WHERE id = 1;


3. Data Query Language (DQL)

DQL is primarily used to query and retrieve data from databases. The main DQL command is SELECT.


⭐ SELECT: This command retrieves records from one or more tables.


SELECT * FROM students;


4. Transaction Control Language (TCL)

TCL commands manage transactions within a database to ensure data integrity. The primary TCL commands are COMMIT, ROLLBACK, and SAVEPOINT.


⭐ COMMIT: This command saves all changes made during the current transaction.


COMMIT;


⭐ ROLLBACK: This command undoes changes made during the current transaction.


ROLLBACK;


⭐ SAVEPOINT: This command sets a point within a transaction to which you can later roll back.


SAVEPOINT savepoint1;


5. Data Control Language (DCL)

  • DCL commands manage access permissions to the database. The main DCL commands are GRANT and REVOKE.


⭐ GRANT: This command gives specific privileges to users.


GRANT SELECT, INSERT ON students TO 'username';


⭐ REVOKE: This command removes specific privileges from users.


REVOKE SELECT, INSERT ON students FROM 'username';


Comments

Post a Comment

Popular posts from this blog

SQL vs NoSQL

  📦 SQL vs NoSQL 📑 🌟 SQL (Structured Query Language) :Organized and follows strict rules (like books on specific shelves). ▶ Think of it like a well-organized library where every book is placed neatly on a specific shelf. ▶ The shelves are labeled with categories, and each book has a specific spot. ▶ You have a strict rule about how books are organized, and every book follows this rule. 🌟 NoSQL (Not Only SQL) : Flexible and can be organized in different ways (like books on shelves, in piles, or in boxes). ▶ Now, imagine a different kind of library where books are placed in different ways. ▶ Some books are on shelves, some are in piles, and some are in boxes. ▶ There are no strict rules about where to put the books. You can organize them in whatever way makes sense at the time. 🎨 Use Cases: 🎢 SQL: Good for situations where you need to keep things very organized and follow rules, like keeping track of a catalog of books in a library. 🎡 NoSQL: Good for situations where you ne...
Post a Comment
Read more

Logging in SpringBoot

  Hey Connections 👋 Let's learn and grow together 📈 🌟 What is logging ??? ➡ loggers are like reporters for your application. They keep track of what's happening while your application runs, like noting down important events, errors, or just general information. 📩There are different Logging Levels ⤵ 🌆 Events: Loggers record events, like when someone signs in or a new feature is added. ❌ Errors: They also note down errors, like when something goes wrong or an unexpected problem occurs. ⚠ Warnings: Sometimes, they give warnings if they see something that could be a problem later, like a traffic jam forming up ahead. 👨‍💻 Debugging: And if you need to figure out why something isn't working as expected, loggers can help by showing detailed information, like a detective piecing together clues. These logging levels help developers and operators understand the severity and importance of different log messages and prioritize them accordingly when troubleshooting or monitor...
Post a Comment
Read more