EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL DDL

MySQL DDL

By Aanchal SharmaAanchal Sharma

MySQL-DDL

Introduction to MySQL DDL

MySQL DDL defines the Data Definition Language, a subgroup of SQL commands among four: DDL, DML, DCL, and TCL. Since, Structured Query Language(SQL) is the basic language of a database that performs different operations and queries in the available MySQL database, including creating a database or table to dropping the same and others like updating, inserting, etc. The MySQL DDL gets involved with the schemas and explanations of database to display how the database data should exist in the server. The DDL commands are significant for expressing and altering the structure of database tables, schemas, or objects. The instant effects are taken when the statements using DDL commands in MySQL are implemented.

How do DDL commands work in MySQL?

The DDL commands work with the SQL query statements, executed to perform and show desired results. Once queried and run, the DDL commands cannot be rolled back and do commit implicitly.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Tables are the database objects that store the data records. These data interact with other table data and form a relation at a structural level. But the data stored requires some modifications from time to time. DDL and different subgroups of SQL in MySQL servers do this.

The user must have required privileges such as CREATE or DROP in the database schemas to work and use DDL queries. We will find DDL commands in all relational databases intended to manage and access the objects in the database.

Examples to Implement MySQL DDL

Let us explain the MySQL DDL commands each with an illustration and show the uses of these queries in the MySQL databases for different administrative tasks:

1. CREATE Command

This DDL command creates any database with different objects, such as tables, indexes, triggers, views, stored procedures, built-in functions, etc.

Code: The CREATE statement is written using the following syntax:

CREATE DATABASE DatabaseName;
CREATE TABLE TableName (Column1 Datatype1, Column2 Datatype2,…,ColumnNDatatypeN);

For example, we will create a table named ‘Emp’ with some fields and similar data types that are valid in MySQL and, respectively, a database named ‘EmpDB’ in MySQL server using the queries below:

Code:

CREATE DATABASE EmpDB;

Output:

CREATE Command

Code:

CREATE TABLE Emp (Emp_ID INT PRIMARY KEY AUTO_INCREMENT, Emp_NameVARCHAR(255), Emp_City VARCHAR(255), Emp_AdmDate DATE NOT NULL);

Output:

MySQL DDL - 2

 

We have created the table above with column names, data types, indexes, and keys. For integers, we have used INT data type; for a date, the type of values, we have DATE type; and for character strings with length values, are used as a data type for the Emp table.

2. ALTER Command

The ALTER DDL command modifies the present database structure and related tables.

With Alter query, we can add, alter or delete the present constraints on a table or columns on the table. The syntax is mentioned below:

Code:

ALTER TABLE TableName ADD ColumnNameData_Type;
ALTER TABLE TableName DROPColumnName;
ALTER TABLE TableName MODIFY COLUMNColumnNameData_Type;

Let us execute some queries with the above structures:

Code:

ALTER TABLE Emp ADD Emp_Contact INT NOT NULL;

Output:

ALTER

Code:

ALTER TABLE Emp DROP Emp_Contact;

Output:

MySQL DDL - 4

Code:

select * from `emp`

Output:

MySQL DDL - 5

Code:

ALTER TABLE Emp MODIFY COLUMN Emp_AdmDateYear;

Output:

MySQL DDL - 6

3. DROP Command

This MySQL command is used to remove the database objects. In simple words, to delete the table existing in your database using the drop query syntax:

Code:

DROP TABLE TableName;

Code:

DROP Table Emp;

Output:

DROP

We need to know that we should be careful while executing the DROP command because it results in the loss of data records that were warehoused in the table.

4. TRUNCATE Command

The Truncate DDL command is implemented to delete all the data rows from the database table, including removing all spaces assigned for those table records.

Code:

The syntax of the TRUNCATE command is identical to the DROP statement as follows:

TRUNCATE TABLE TableName;

Suppose we are using the above command in the query below:

Code:

TRUNCATE TABLE Emp;

Output:

TRUNCATE

The output says that the truncate will only remove the records inside the specific table, not the table in the database itself.

5. COMMENT Command

This Comment query is beneficial for adding required comments to the data dictionary in the MySQL server.

In MySQL, comments serve two main purposes: describing the sections of statements or avoiding the execution of query statements.

MySQL categorizes comments into two types:

  • Single Line Comments: The comment here begins with –. If we place texts between –and the end of the text line, the whole text will be unnoticed during the code execution.

For explanation, let us apply a single-line comment as follows and run it in the server to show the results:

Code:

--We are displaying all records from table Person:
SELECT * FROM Person;

Output:

COMMENT

Also, let us view an example to demonstrate the comment to remove the end line of a text using a single comment:

Code:

SELECT * FROM Person --WHERE City = “Delhi”;

Output:

MySQL DDL - 10

Likewise, we can ignore a whole statement:

Code:

--SELECT * FROM Person;
SELECT * FROM Employee;

Output:

MySQL DDL - 11

The execution only includes the second statement, displaying the table data as the output.

  • Multi-Line Comments: These comments begin with /* and last with */. If we include any text between these tags, then it will not be noticed in a specific statement.

Code:

/*selectingall columns from table Employee existing in the database: */
SELECT * FROM Employee;

Output:

Multi-Line

To ignore more than one statement:

Code:

/* SELECT * FROM Person;
SELECT * FROM Orders;
*/
SELECT * FROM Employee;

Output:

MySQL DDL - 13

To ignore any section of the statement given:

Code:

SELECT Person_ID, Employee_Name/*Salary, */ FROM Employee/*GROUP BY Person_ID*/ ORDER BY Employee_Name;

Output:

MySQL DDL - 14

6. RENAME Command

The Rename DDL command query allows renaming any database objects in the server if needed for any admin works. Sometimes we want to modify the present table name and rename it. For this, let us apply the succeeding syntax with ALTER DDL command:

Code:

ALTER TABLE TableName_ARENAME TO TableName_B;

Explanation with examples:

Code:

ALTER TABLE Emp RENAME TO Emp_Data;

Output:

MySQL DDL - 15

Code:

select * from `emp_data`

Output:

MySQL DDL - 16

Conclusion

The MySQL DDL commands allow privileges to the admin users to control the database activities and maintain the flow and access properly. The SQL language used in MySQL provides the managing queries to create a database or table, apply any alterations, and, if needed, drop the database objects, which helps to define the schemas and structures of the database.

Recommended Articles

We hope that this EDUCBA information on “MySQL DDL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Cursor In MySQL
  2. Table In MySQL
  3. MySQL Trigger
  4. ROLLUP In MySQL
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more