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.

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:

Code:

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

Output:

 

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:

Code:

ALTER TABLE Emp DROP Emp_Contact;

Output:

Code:

select * from `emp`

Output:

Code:

ALTER TABLE Emp MODIFY COLUMN Emp_AdmDateYear;

Output:

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:

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:

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:

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:

Likewise, we can ignore a whole statement:

Code:

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

Output:

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:

To ignore more than one statement:

Code:

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

Output:

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:

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:

Code:

select * from `emp_data`

Output:

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