Data Definition Language in SQL With Example

Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners. Also, if you are interested in content writing, you can mail us at tutorialwing@gmail.com.

In this post, we will be discussing on how to create DDL Queries. DDL stands for data definition language. We will write queries that will definitely execute on MySQL as the database provider, but if you plan to execute the queries in other database provides like Sybase, Microsoft’s SQL Server you may or may not have to database provides like Sybase, Microsoft’s SQL Server you may or may not have to make changes to the queries.

We will go through different (data definition commands) or ddl commands –

  1. CREATE Statement
  2. ALTER Statement
  3. DROP Statement
  4. RENAME Statement
  5. TRUNCATE Statement

1. CREATE Statement

The CREATE TABLE command defines each column of the table uniquely. Each column has at least three attributes: column name, data type and size.

Syntax:

CREATE TABLE <TABLE NAME> (<COLUMMN NAME> <DATA TYPE> (<SIZE>), (<COLUMN NAME> <DATA TYPE>) (SIZE));

Before we create a table we need to select in which database we want the table to be created.

Example 1:

Let’s create an employee table, but before that lets execute the command to select the database.

mysql> CREATE table employee(eid char(5) primary key, fname varchar(15) not null, lname varchar(15) not null,
       DOB date not null, address varchar(30), sex char, salary decimal(10,2), dnum integer not null);

Once you execute the above query you will get below result.
Query OK, 0 rows affected (0.00 sec)

Example 2:

Now let’s create a Department table which has the usage of primary key, foreign key, unique, not null constraints.

mysql> CREATE table department (dno integer primary key, dname varchar (15) not null unique, 
        mgreid char (5) not null, foreign key (mgreid) references employee (eid));

Query OK, 0 rows affected (0.00 sec)

Example 3:

Creating a composite key with two fields

mysql> CREATE table dept_location (dnum integer not null, dlocation varchar (15) not null, primary key (dnum, dlocation), foreign key (dnum) references department (dno) );

Query OK, 0 rows affected (0.00 sec)

Example 4:

Usage of SET, auto_increment and default constraint.

mysql> create table studentDetails (studid int(4) auto_increment primary key, name varchar(15) 
       not null, gender set (‘M’, ‘F’) not null, city varchar(15) default ‘Pune’);

Query OK, 0 rows affected (0.01 sec)

2. ALTER Statement

If you are adding constraints to the existing table, then you have to use ALTER statement as you are altering or changing the structure of the already existing table.

Syntax

ALTER TABLE <table_name>
ADD <column_name datatype>;

Or,

ALTER TABLE <table_name>
CHANGE <old_column_name> <new_column_name>;

Or,

ALTER TABLE <table_name>
DROP COLUMN <column_name>;
Example 1:

Adding primary key constraint to an existing table.

mysql> ALTER table customer ADD primary key (custid);
Example 2:

Adding foreign key constraint to an existing table.

mysql> ALTER table employee ADD constraint fkdum foreign key(dnum) references department(dno);
Example 3:

Adding a new column in an existing table.

mysql> ALTER table customer ADD email varchar (25) null;
Example 4:

Adding a new column in an existing table and specifying its position using AFTER clause.

mysql> ALTER table customer ADD email varchar(25) null after custname;

The email field will be added next to custname field.

Example 5:

Renaming the table.

mysql> ALTER table customer RENAME to customerDetails;

Or

mysql> RENAME table customer to customerDetails;

 

3. DROP Statement

Drop statement is used to destroy a database or a table. Dropping a table means all the rows present will be deleted and the table structure will be removed from the database. More often only database admins or the developer with full database rights will have permissions to execute this command.

Syntax

DROP table table_name;
Example 1:

Remove the database.

mysql> drop database employeedb;
Example 2:

Remove the table

mysql> drop table employeemaster;

4. RENAME Statement

RENAME command is used to rename an object e.g. database table.

Syntax

RENAME TABLE <old_name> TO <new_name>;
Example 1
mysql> RENAME TABLE emp TO employee;

It renames table emp to employee.

5. TRUNCATE Statement

TRUNCATE command is used to all rows from table permanently.

TRUNCATE command is same as DELETE but it does not generate any rollback data.

Syntax

TRUNCATE TABLE <table_name>;
Example 1
TRUNCATE TABLE employee;

That’s it for Data Definition Language.

Support Us

If you like Tutorialwing and would like to contribute, you can email an article on any educational topic at tutorialwing@gmail.com. We would love to publish your article. See your article on Tutorialwing and help others with your knowledge. Follow Facebook, LinkedIn, Google+, Twitter, Youtube for latest updates.
Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners