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