Data Definition Statement

In this section we will describe you the syntax of most of the Data Definition statements supported by MySQL. These statements are given below :

Creating a Database

For creating a database the command syntax :
CREATE DATABASE [IF NOT EXISTS] <db_name>

By the command CREATE DATABASE we can create the database with the given name. But using this command you have the CREATE privilege for the database. And if you are trying to create a database, which already exists and you are not using the IF NOT EXIST option then you get an error. But if you are using this option then you will not get the error but the database is not created again.

Here is the video tutorial of "How to create database in MySQL using command prompt?":

Example for Creating a new Database :

mysql> CREATE DATABASE IF NOT EXISTS Employee;
Query OK, 1 row affected (0.02 sec)

In the following example we are trying to create a database with the same name but without IF NOT EXISTS option that gives you an error message. Example :

mysql> CREATE DATABASE Employee;
ERROR 1007 (HY000): Can't create database 'employee'; database exists

In the following example we are trying to create a database with the same name with the IF NOT EXISTS option that gives you only a warning but the database is not created again. Example :

mysql> CREATE DATABASE IF NOT EXISTS Employee;
Query OK, 1 rows affected, 1 warning (0.25 sec)

If you get this message that means you have successfully created the database. Now we want to see how many databases are available on the system. We can do this by SHOW statement Example :

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employee           |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.23 sec)

Creating a Table

Syntax for creating a table a little more complex than creating a database.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <table_name>[(create_definition,...)] [table_options] [select_statement]

IF NOT EXISTS option works like the same as it does in CREATE DATABASE statement. But TEMPORARY option is used to define that the table has been created will available only until the current connection to the database is open. When the connection is disconnected even by accidentally, table will be deleted.

There are a variety of options are available that we can put inside the create_definition brackets Example :
<column_name> <data type> [NOT NULL | NULL] [DEFAULT <default_value>][AUTO_INCREMENT] PRIMARY KEY (<index_column_name>,...)

The column_name is used to define the field or column of the table of each record. And each column name has data type also.
The NOT NULL/NULL option is used to define that the field is require the data or not.
The DEFAULT option is used to set the default value (which the database uses in lieu of input data).
An integer column can have the AUTO-INCREMENT attributes. The AUTO-INCREMENT option is used to automatically set counts up when NULL is input into the column?s field.
PRIMARY KEY option is used to indicate which column of fields, will be used to form an index for faster access to the table?s records.

Before issuing the CREATE TABLE statement we have to issue USE db_name command. Example of Create Table Command :

mysql> use employee
Database changed
mysql> CREATE TABLE Emp(
    -> Eid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Ename VARCHAR(20), City VARCHAR(15),
    -> Designation VARCHAR(20), Salary INT);
Query OK, 0 rows affected (0.83 sec)

For verifying about the table creation we can issue the SHOW TABLES command. This command returns the list of tables are available in selected database.

mysql> SHOW TABLES;
+--------------------+
| Tables_in_employee |
+--------------------+
| emp                |
+--------------------+
1 row in set (0.00 sec)

Altering the Database

The general syntax for altering the database is :
ALTER {DATABASE} [db_name] alter_specification [alter_specification]......

By ALTER DATABASE command you can change the overall characteristics of a database. And these characteristics are stored in db.opt file in the database directory. But for using ALTER DATABASE command you have ALTER privilege on the database. The following example increase the size of one file that available in the database. Example :

mysql> ALTER DATABASE Emp
    -> MODIFY FILE
    -> (NAME = test1,
    -> SIZE = 20MB);

Altering the Table

The general syntax for altering the table is :
ALTER TABLE tbl_name alter_specification [, alter_specification] ...

By the ALTER TABLE command you can change the structure of an existing table. This commands allows you add, modify or delete the columns, create or destroy the indexes, rename the columns or the table itself. For using the ALTER TABLE command you need the ALTER, INSERT and CREATE privileges for the tables. Here is some examples are given below that helps you to understand the different alter specification for altering the table.

The first example is used to rename the table name by the command ALTER TABLE old_name RENAME TO new_name;

mysql> show tables;
+--------------------+
| Tables_in_employee |
+--------------------+
| emp                |
| employee_data      |
+--------------------+
2 rows in set (0.05 sec)
mysql> ALTER TABLE employee_data RENAME TO employe;
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+--------------------+
| Tables_in_employee |
+--------------------+
| emp                |
| employe            |
+--------------------+
2 rows in set (0.00 sec)

The following example is used to add a new column in existing table by the command ALTER TABLE tbl_name ADD column_name column_type; and for multiple columns we can use ALTER TABLE tbl_name ADD (column_1 column_type, column_2 column_type);

mysql> DESCRIBE Emp;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| Eid         | int(10) unsigned | NO   | PRI |         | auto_increment |
| Ename       | varchar(20)      | YES  |     |         |                |
| City        | varchar(15)      | YES  |     |         |                |
| Designation | varchar(20)      | YES  |     |         |                |
| Salary      | int(11)          | YES  |     |         |                |
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)
mysql> ALTER TABLE Emp
    -> ADD Age numeric(3);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESCRIBE Emp;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| Eid         | int(10) unsigned | NO   | PRI |         | auto_increment |
| Ename       | varchar(20)      | YES  |     |         |                |
| City        | varchar(15)      | YES  |     |         |                |
| Designation | varchar(20)      | YES  |     |         |                |
| Salary      | int(11)          | YES  |     |         |                |
| Age         | decimal(3,0)     | YES  |     |         |                |
+-------------+------------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

The following example is used to modify the column definition in existing table by the command ALTER TABLE tbl_name MODIFY column_name column_type; and for multiple columns we can use ALTER TABLE tbl_name MODIFY (column_1 column_type, column_2 column_type);

mysql> DESCRIBE Emp;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| Eid         | int(10) unsigned | NO   | PRI |         | auto_increment |
| Ename       | varchar(20)      | YES  |     |         |                |
| City        | varchar(15)      | YES  |     |         |                |
| Designation | varchar(20)      | YES  |     |         |                |
| Salary      | int(11)          | YES  |     |         |                |
| Age         | decimal(3,0)     | YES  |     |         |                |
+-------------+------------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
mysql> ALTER TABLE Emp MODIFY Ename VARCHAR(22) NOT NULL;
Query OK, 1 row affected (0.28 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> DESCRIBE Emp;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| Eid         | int(10) unsigned | NO   | PRI |         | auto_increment |
| Ename       | varchar(22)      | NO   |     |         |                |
| City        | varchar(15)      | YES  |     |         |                |
| Designation | varchar(20)      | YES  |     |         |                |
| Salary      | int(11)          | YES  |     |         |                |
| Age         | decimal(3,0)     | YES  |     |         |                |
+-------------+------------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

The following example is used to drop a column in existing table by the command ALTER TABLE tbl_name DROP COLUMN column_name;

mysql> DESCRIBE Emp;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| Eid         | int(10) unsigned | NO   | PRI |         | auto_increment |
| Ename       | varchar(22)      | NO   |     |         |                |
| City        | varchar(15)      | YES  |     |         |                |
| Designation | varchar(20)      | YES  |     |         |                |
| Salary      | int(11)          | YES  |     |         |                |
| Age         | decimal(3,0)     | YES  |     |         |                |
+-------------+------------------+------+-----+---------+----------------+
6 rows in set (0.06 sec)
mysql> ALTER TABLE Emp
    -> DROP COLUMN Age;
Query OK, 1 row affected (0.24 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> DESCRIBE Emp;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| Eid         | int(10) unsigned | NO   | PRI |         | auto_increment |
| Ename       | varchar(22)      | NO   |     |         |                |
| City        | varchar(15)      | YES  |     |         |                |
| Designation | varchar(20)      | YES  |     |         |                |
| Salary      | int(11)          | YES  |     |         |                |
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

Dropping the Database

The general syntax for dropping the database command is :
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASE command is used to drop the all tables in the given database and deletes the database also. But for using the DROP DATABASE statement you need the DROP privilege on the database. If you are not using IF EXISTS option and the database is not available which you want to drop then it occurs the error but if you are using this option then it doesn't occur the error. Example :

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| emp1               |
| employee           |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> DROP DATABASE IF EXISTS emp1;
Query OK, 0 rows affected (0.10 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employee           |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.01 sec)

Dropping the Table

The general syntax for dropping the table command is :
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name

DROP TABLE statement is used to remove one or more tables in the database but for this you must have the DROP privilege for each table. By this command all the data of table and the definition also has been removed. If you are not using IF EXISTS option and any table name in the argument list do not exists the MySQL returns an error with the name of non existing tables it was unable to drop. But it drops the all tables of the list that do exist. By using TEMPORARY keyword, the statement drops only temporary tables, the statement does not end an ongoing transaction and it does not check the access right because the temporary table is visible only to the client that creates it, that?s why the access right checking is not is not necessary. If you are not using the TEMPORARY keyword then the DROP TABLE command automatically commits the current active transaction. Example :

mysql> SHOW TABLES;
+--------------------+
| Tables_in_employee |
+--------------------+
| emp                |
| employe            |
+--------------------+
2 rows in set (0.00 sec)
mysql> DROP TABLE IF EXISTS employe;
Query OK, 0 rows affected (0.56 sec)
mysql> SHOW TABLES;
+--------------------+
| Tables_in_employee |
+--------------------+
| emp                |
+--------------------+
1 row in set (0.01 sec)

Rename the Table

The general syntax of rename the table command is :
RENAME TABLE tbl_name TO new_tbl_name, tbl_name2 TO new_tbl_name2 ...

By this statement we can rename the one or more tables. The rename operation is work automatically that means no other thread can access any of the tables while the rename process is running. Example :

mysql> SHOW TABLES;
+--------------------+
| Tables_in_employee |
+--------------------+
| emp                |
| employee_data      |
+--------------------+
2 rows in set (0.01 sec)
mysql> RENAME TABLE employee_data TO EMP1;
Query OK, 0 rows affected (0.14 sec)
mysql> SHOW TABLES;
+--------------------+
| Tables_in_employee |
+--------------------+
| emp                |
| emp1               |
+--------------------+
2 rows in set (0.00 sec)

Data Definition Statement