MySQL DDL

Introduction to MySQL DDL

MySQL DDL defines the Data Definition Language which is a subgroup of SQL commands among four: DDL, DML, DCL,TCL. Since, Structured Query Language(SQL) is the basic language of database which 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 to express and alter the structure of database tables, schemas or objects.When the statements using DDL commands in MySQL are implemented then the instant effects are taken.

How DDL commands work in MySQL?

• The DDL commands works with the SQL query statements which are executed to perform and show desired results.The DDL commands once queried and run cannot be rolled back and does commit implicitly.
• Tables are the database objects that store the data records. These data interact with other table data and also forms a relationon a structural level. But the data stored requires some modifications from time to time. This is done by DDL and other subgroups of SQL in MySQL server.
• For working and usage of DDL queries the user needs to have required privileges such as CREATE or DROP in the database schemas. We will find DDL commands in all relational databases that are intended to manage and access the objects in database.

Examples to Implement MySQL DDL

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

1. CREATE Command

This DDL command is used to create any database with its 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:

 

Here, we have created the table above with column names and data types and indexes and keys. For integers we have used INT data type, for date 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

ALTER DDL command is applied to modify the structure of present database 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 databaseusing 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

Truncate DDL command is implemented to delete all the data rows from the database table that includes removing of all spaces assigned for those table records.

Code:

The syntax of TRUNCATE command is identical to 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 to add required comments to the data dictionary in the MySQL server.

Generally, the comments in MySQL are provided to describe the sections of statements or to avoid the execution of query statements.

The comments are categorized into two types in MySQL:

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

For explanation, let us apply a single line comment as follows and run 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 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:

As you can see only the second statement is executed and table data are displayed as output.

• Multi-Line Comments: This comments begins with /* and lasts 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:

SELECTPerson_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 database, table, apply any alterations, and if needed drop the database objects which helps to define the schemas and structures of the database.

Recommended Articles

This is a guide to MySQL DDL. Here we discuss an introduction to MySQL DDL, syntax, how does it work with examples for better understanding. You can also go through our other related articles to learn more –

1. Cursor In MySQL
2. Table In MySQL
3. MySQL Trigger
4. ROLLUP In MySQL