MySQL - SPLessons

MySQL DDL

Chapter 9

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

MySQL DDL

MySQL DDL

shape Description


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:

shape Conceptual
figure

Create

shape Description

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.

shape Syntax

Create table <table_name>(<column_name1> datatype (size),<column_name2> datatype(size)………….);

table_name => Any accurate table.

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

shape Examples

By viewing the below example, new table can be created by using create command.

mysql> create table student(stu_id int,stu_name varchar(255),fee int);
Query OK, 0 rows affected (0.30 sec) 

In the above example a table student is defined with column names stu_id, stu_name, and fee with size(255).

Alter

shape Description

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

  1. Adding
  2. Modify

Adding

shape Description

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

shape Syntax

Alter table

ADD(<column_name1> datatype(size),<column_name2> datatype(size));

Table_name => Any accurate table name.

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

shape Examples

By viewing the below example, the table can be altered by utilizing alter adding command.

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)

The above example tells that, how to include new column_name fee with size in a table.

Modify

shape Description

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

shape Syntax

Alter table

modify(<column_name> datatype(size));

table_name => Any accurate table.

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

shape Examples

By viewing the below example, the concept of modify command can be easily understood.

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)

The above example tells that, how to Modify the column_name branch with size in a table.

Drop

shape Description

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

Dropping a column from the table

shape Syntax

Alter table drop column;

Table_name => Any accurate table.

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

shape Examples

By viewing the below example, the concept of drop command can be understood easily.

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)

The above example tells that, how to drop a column_name fee from the table.

Dropping a table from the database

shape Syntax

drop table <table name>;

table_name => Any accurate table.

shape Examples

By viewing the below example, the concept of drop command can be understood easily.

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

The above example tells that, how to drop all the columns from the table student.

Rename

shape Description

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

shape Syntax

Rename old <table_name> to new <table_name>;

table_name => Any accurate table.

shape Examples

By viewing the below example, the concept of rename command can be understood easily.

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)

The above example tells that, how to Rename previous table name to new table name. i.e, from employee to employee1. 

Truncate

shape Description

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

shape Syntax

Truncate table;

table_name => Any accurate table.

shape Examples

By viewing the below example, the concept of truncate command can be understood easily.

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)

The above example tells that, how to delete all the existing data from the table employee.

Summary

shape Key Points

  • 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.