MySQL DDL Commands

Introduction

Data Definition Language (DDL) commands in MySQL are used to define and manage the structure of databases, tables, and other database objects. They allow you to create, modify, and delete database objects. In this article, we will explore the commonly used DDL commands in MySQL and provide examples to illustrate their usage.

CREATE DATABASE

The CREATE DATABASE command is used to create a new database. Here's an example:

CREATE DATABASE mydatabase;

CREATE TABLE

The CREATE TABLE command is used to create a new table. Here's an example:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

ALTER TABLE

The ALTER TABLE command is used to modify an existing table. It allows you to add, modify, or delete columns, constraints, and other table elements. Here's an example:

ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

DROP TABLE

The DROP TABLE command is used to delete an existing table. Here's an example:

DROP TABLE employees;

TRUNCATE TABLE

The TRUNCATE TABLE command is used to delete all records from an existing table while keeping the table structure intact. Here's an example:

TRUNCATE TABLE employees;

CREATE INDEX

The CREATE INDEX command is used to create an index on one or more columns of a table. Indexes improve the performance of search and retrieval operations. Here's an example:

CREATE INDEX idx_name ON employees (name);

DROP INDEX

The DROP INDEX command is used to remove an existing index from a table. Here's an example:

DROP INDEX idx_name ON employees;

Conclusion

MySQL DDL commands are essential for defining and managing the structure of databases and tables. In this article, we explored the CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX, and DROP INDEX commands. By mastering these commands, you can effectively create, modify, and delete database objects, ensuring the integrity and performance of your MySQL databases.