MySQL DDL (Data Definition Language) is used to define data in database server. DDL statements explains, how the pattern of data should be there in the database schema table. MySQL DDL statements are classified into the following types:

By using Create command of MySQL DDL commands, new table can be defined in the database server and the table name can be any character followed by size. This command will access the create privilege for the Database. And the table name should be unique in the database.

table_name => Any accurate table. column_name => The operation that can be performed on a column in the table.

By viewing the below example, new table can be created by using create command. [sql]mysql> create table student(stu_id int,stu_name varchar(255),fee int); Query OK, 0 rows affected (0.30 sec) [/sql] In the above example a table student is defined with column names stu_id, stu_name, and fee with size(255).

Alter command performs adding of column or modifying column size and is divided into two types.

1. Adding
2. Modify

Using add command new columns can be included in the current table.

Table_name => Any accurate table name. Column_name =>The operation that can be performed on a column in the table.

By viewing the below example, the table can be altered by utilizing alter adding command. [sql]mysql> alter table student add(fee int); Query OK, 0 rows affected (0.64 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>desc student; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | stu_id | int(11) | YES | | NULL | | | stu_name | varchar(255) | YES | | NULL | | | stu_result | varchar(255) | YES | | NULL | | | branch | varchar(255) | YES | | NULL | | | fee | int(11) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)[/sql] The above example tells that, how to include new column_name fee with size in a table.

The structure of an existing table object can be modified using modify command.

table_name => Any accurate table. column_name => The operation that can be performed on a column in the table.

By viewing the below example, the concept of modify command can be easily understood. [sql]mysql> desc student; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | stu_id | int(11) | YES | | NULL | | | stu_name | varchar(255) | YES | | NULL | | | stu_result | varchar(255) | YES | | NULL | | | branch | varchar(250) | YES | | NULL | | | fee | int(11) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table student modify branch varchar(255); Query OK, 0 rows affected (0.50 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | stu_id | int(11) | YES | | NULL | | | stu_name | varchar(255) | YES | | NULL | | | stu_result | varchar(255) | YES | | NULL | | | branch | varchar(255) | YES | | NULL | | | fee | int(11) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)[/sql] The above example tells that, how to Modify the column_name branch with size in a table.

Drop command of MySQL DDL Commands, can be performed on existing database. The following are the actions performed by drop command.

1. Dropping a column from the table
2. Droping a table from the database

Table_name => Any accurate table. Column_name => The operation that  can be performed on a column in the table.

By viewing the below example, the concept of drop command can be understood easily. [sql]mysql> desc student; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | stu_id | int(11) | YES | | NULL | | | stu_name | varchar(255) | YES | | NULL | | | stu_result | varchar(255) | YES | | NULL | | | branch | varchar(255) | YES | | NULL | | | fee | int(11) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table student drop column fee; Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | stu_id | int(11) | YES | | NULL | | | stu_name | varchar(255) | YES | | NULL | | | stu_result | varchar(255) | YES | | NULL | | | branch | varchar(255) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)[/sql] The above example tells that, how to drop a column_name fee from the table.

table_name => Any accurate table.

By viewing the below example, the concept of drop command can be understood easily. [sql]mysql> desc student; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | stu_id | int(11) | YES | | NULL | | | stu_name | varchar(255) | YES | | NULL | | | stu_result | varchar(255) | YES | | NULL | | | branch | varchar(255) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql>drop table student; Query OK, 0 rows affected (0.48 sec) mysql> select * from student; ERROR 1146 (42S02): Table 'employee.student' doesn't exist[/sql] The above example tells that, how to drop all the columns from the table student.

Rename command of MySQL DDL Commands will rename the existing table name. i.e. rename old to new table name.

table_name => Any accurate table.

By viewing the below example, the concept of rename command can be understood easily. [sql]mysql> select * from employee; +--------+--------+-----------+-------+------------+ | emp_id | ename | job |salary | commission | +--------+--------+-----------+-------+------------+ | 1001 | kate | manager | 14000 | 10 | | 1002 | jack | scaleman | 13000 | 20 | | 1003 | maddie | business | 14000 | 30 | | 1004 | madd | business | 14000 | NULL | | 1005 | winni | marketing | 14500 | NULL | +--------+--------+-----------+-------+------------+ 5 rows in set (0.00 sec) mysql> rename table employee to employee1; Query OK, 0 rows affected (0.24 sec) mysql> select * from employee1; +--------+--------+-----------+-------+------------+ | emp_id | ename | job |salary | commission | +--------+--------+-----------+-------+------------+ | 1001 | kate | manager | 14000 | 10 | | 1002 | jack | scaleman | 13000 | 20 | | 1003 | maddie | business | 14000 | 30 | | 1004 | madd | business | 14000 | NULL | | 1005 | winni | marketing | 14500 | NULL | +--------+--------+-----------+-------+------------+ 5 rows in set (0.00 sec)[/sql] The above example tells that, how to Rename previous table name to new table name. i.e, from employee to employee1. 

Truncate command of MySQL DDL Commands is utilized to erase all rows from the current table in the database.

By viewing the below example, the concept of truncate command can be understood easily. [sql]mysql> select * from employee; +--------+--------+-----------+-------+------------+ | emp_id | ename | job |salary | commission | +--------+--------+-----------+-------+------------+ | 1001 | kate | manager | 14000 | 10 | | 1002 | jack | scaleman | 13000 | 20 | | 1003 | maddie | business | 14000 | 30 | | 1004 | madd | business | 14000 | NULL | | 1005 | winni | marketing | 14500 | NULL | +--------+--------+-----------+-------+------------+ 5 rows in set (0.00 sec) mysql> truncate table employee; Query OK, 0 rows affected (0.37 sec)[/sql] The above example tells that, how to delete all the existing data from the table employee.

• MySQL DDL statements are used to define date in database server.
• Create will creates the table,alter will modifies a table, drop will drops the table/column.
• Rename replaces the table name, truncate will delete entire rows.