An Introduction to DDL, DML, and DCL Commands in MySQL: A Comprehensive Guide With Examples

MySQL is widely recognized as one of the most popular open-source relational database management systems. It holds immense significance in the realm of web development, data analytics, and beyond. Its adaptability and user-friendly nature have positioned it as the preferred choice for managing structured data. 

MySQL commands are classified into various types, primarily based on their purpose within the database. These types encompass Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL). A comprehensive understanding of these commands and their practical applications is essential for individuals involved in MySQL database operations. This article delves into each category, offering precise definitions and illustrative examples to enhance comprehension.

Data Definition Language (DDL)

DDL commands are utilized to establish, modify, or eliminate the framework of the database entities, such as tables, indexes, and schemas. These commands do not manipulate the actual data but rather determine the organization of data within the database. Some typical DDL commands are CREATE, ALTER, DROP, TRUNCATE, and RENAME.

DDL Command Examples

1. CREATE - This command is used to create a new table in the database.

CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
enrollment_date DATE
last_update CAST(GETDATE() AS DATE)
);

2. ALTER - Used to modify an existing table, such as adding a new column or changing a data type:

ALTER TABLE students ADD email VARCHAR(255);

3. DROP - Removes the existing table and its data:

DROP TABLE students;

4. TRUNCATE - Deletes all data from a table without removing the table itself:

TRUNCATE TABLE students;

5. RENAME - Changes the name of a table

RENAME TABLE students TO alumni;

Data Manipulation Language (DML)

DML commands are used for managing data within table objects. This includes inserting, updating, deleting, and selecting data. DML commands directly affect the data itself, making them essential for everyday database operations. The primary DML commands are INSERT, UPDATE, DELETE, and SELECT.

DML Command Examples

1. INSERT - Adds new rows of data to a table:

INSERT INTO students (name, enrollment_date) VALUES ('Author Name', '1984-09-01');

2. UPDATE - Modifies existing data in a table:

UPDATE students SET email = 'john.doe@example.com' WHERE student_id = 1;

3. DELETE - Removes rows from a table:

DELETE FROM students WHERE student_id = 1;

4. DELETE, multiple students - Removes multiple rows from a table:

DELETE FROM students WHERE student_id between 2 and 6;

5. SELECT - Retrieves all data from one table:

SELECT * FROM students;

6.  SELECT - Select data between the student_id:

SELECT * FROM students where student_id between 5 and 8;

Data Control Language (DCL)

DCL commands primarily revolve around managing permissions and controlling access to database objects. These commands play a vital role in upholding database security by limiting access exclusively to authorized users. The two commonly used DCL commands are GRANT and REVOKE.

DCL Command Examples

1. GRANT - Gives a user permission to perform specific actions on database objects:

GRANT SELECT, INSERT ON students TO 'author@email.com';

2. REVOKE - Removes specific permissions from a user:

REVOKE INSERT ON students FROM 'author@email.com';

Conclusion

Mastering the utilization of DDL, DML, and DCL commands in MySQL is crucial for proficiently creating and administering databases, manipulating data as required, and guaranteeing secure control over data access. A comprehensive understanding of these commands is essential for harnessing the complete capabilities of MySQL in any data-centric application or system.