DDL (Data Definition Language) Commands – Table Related

Introduction

In the realm of database management, Data Definition Language (DDL) commands play a pivotal role. They are instrumental in defining, altering, and manipulating the structure of database tables. In this article, we will dive deep into DDL commands, explore their usage with examples, and unravel the power they hold in shaping the foundation of data storage and management.

• Understanding the Basics
• CREATE TABLE: Building Foundations
• ALTER TABLE: Making Modifications
• DROP TABLE: The Deletion Command
• SHOW TABLES: A Quick Glance
• DESC/DESCRIBE: Peek into Table Structure
• Conclusion
• FAQs

Understanding the Basics

Before we delve into the specifics of DDL commands, let's establish a foundational understanding of their purpose and components.

CREATE TABLE: Building Foundations

The CREATE TABLE command is used to create a new table in a database. It defines the table's structure, including field names, data types, and constraints. Here's a breakdown of its syntax:

CREATE TABLE tableName(
    fieldname datatype(size) constraint,
    fieldname datatype(size) constraint,
    ...
    fieldname datatype(size) constraint
);

In this command:

• tableName represents the name of the new table.
• fieldname refers to the name of a specific field within the table.
• datatype specifies the data type for the field (e.g., char, varchar, int, decimal, date).
• size defines the size or length of the data type.
• constraint (optional) can be Primary Key, Unique, or Not Null.

Example

CREATE TABLE Students(
    admNo int(5) primary key,
    Name varchar(30),
    Class varchar(10)
);

ALTER TABLE: Making Modifications

The ALTER TABLE command is your tool for making structural changes to an existing table. You can use it to add fields, drop fields, modify field data types, and more. Its syntax includes various subcommands:

• ADD fieldname datatype(size) constraint; To add a new field to an existing table, use this syntax:

ALTER TABLE tableName ADD fieldname datatype(size) constraint;

Example

ALTER TABLE Students ADD address varchar(30);

• ADD PRIMARY KEY(fieldname); To add a primary key constraint to an existing field, use this syntax:

ALTER TABLE tableName ADD PRIMARY KEY(fieldname);

Example

ALTER TABLE Library ADD PRIMARY KEY(bookNo);

• DROP fieldname; To delete a specific field from an existing table, use this syntax:

ALTER TABLE tableName DROP fieldname;

Example

ALTER TABLE Students DROP address;

• DROP PRIMARY KEY; To remove the primary key constraint from an existing table, use this syntax:

ALTER TABLE tableName DROP PRIMARY KEY;

Example

ALTER TABLE Students DROP PRIMARY KEY;

• MODIFY fieldname newdatatype(newsize); To modify the data type and size of an existing field, use this syntax:

ALTER TABLE tableName MODIFY fieldname newdatatype(newsize);

Example

ALTER TABLE Library MODIFY bookName varchar(50);

• MODIFY fieldname datatype(size) NOT NULL | NULL; This subcommand is used to change the nullability constraint of a field:

ALTER TABLE tableName MODIFY fieldname datatype(size) NOT NULL | NULL;

Example

ALTER TABLE Library MODIFY bookName varchar(50) NULL;

DROP TABLE: The Deletion Command

The DROP TABLE command is used when you want to delete a table entirely, along with all its associated structure and records.

DROP TABLE tablename;

Example

DROP TABLE Students;

SHOW TABLES: A Quick Glance

The SHOW TABLES command is a handy way to retrieve a list of all tables within the currently opened database.

SHOW TABLES;

DESC/DESCRIBE: Peek into Table Structure

To gain insights into the structure of a specific table, you can use either of these commands:

DESC tablename;

DESCRIBE tablename;

Example

DESC Students;

Conclusion

In the world of database management, DDL commands are the architects of data structures. They enable us to create tables, make modifications, and even demolish them when needed. Understanding how to wield these commands is essential for anyone working with databases, from database administrators to developers.

Whether you're creating a new table with specific constraints or altering an existing one to adapt to changing needs, DDL commands are your trusty companions in the database journey.

FAQs

1. What is the primary purpose of DDL commands? DDL (Data Definition Language) commands are primarily used to define and manage the structure of database objects, such as tables. They allow you to create, modify, and delete tables, as well as set constraints.

2. Can I use DDL commands to change the data within a table? No, DDL commands are focused on the structure of the database, not its data. To manipulate data, you would use Data Manipulation Language (DML) commands like INSERT, UPDATE, and DELETE.

3. What is the significance of constraints in CREATE TABLE commands? Constraints in the CREATE TABLE command define rules for the data within the table. For example, a PRIMARY KEY constraint ensures uniqueness, while a NOT NULL constraint ensures a field always has a value.

4. Are there any risks associated with using the DROP TABLE command? Yes, the DROP TABLE command permanently deletes a table and all its data. It should be used with caution, as there is no way to recover the data once a table is dropped.

5. How can I view the list of tables in my database using DDL commands? You can use the SHOW TABLES command to list all the tables in the currently opened database.