MySQL Server : Open Source Database : Create/Alter/Drop Table

by Anuj Verma · Published 20/01/2015 · Updated 26/11/2022

CREATE Table
Creating Column Definitions
Defining Table Types
Options and Attributes
CREATE Table Example
ALTER Table
DROP Table
Examples

It creates additional tables in the database using CREATE TABLE

First, we must specify the database we will be working with.
The USE command selects an active database among several databases under MySQL management.
Tables provide a structure for storing and securing the data.
All data exists within the structure of the tables, and tables are grouped inside of the database.
The CREATE TABLE statement includes appropriate column definitions and constraints to be used for the entity associated with the table.

CREATE Table Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
OR 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    select_statement
OR 
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
        [index_type]
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
        [index_type]
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
        [index_name] [index_type] (index_col_name,...)
        [index_type]
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
        [index_type]
  | [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)
column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string'] [reference_definition]
data_type:
    BIT[(length)]
  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | YEAR
  | CHAR[(length)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | BINARY[(length)]
  | VARBINARY(length)
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | MEDIUMTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | LONGTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | ENUM(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | SET(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | spatial_type
index_col_name:
    col_name [(length)] [ASC | DESC]
index_type:
    USING {BTREE | HASH}
reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION
table_options:
    table_option [[,] table_option] ...
table_option:
    {ENGINE|TYPE} [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | UNION [=] (tbl_name[,tbl_name]...)
select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (Some legal select statement)

Creating Column Definitions

<table element>::=
<column definition>
| {[CONSTRAINT <constraint name>] PRIMARY KEY 
(<column name> [{, <column name>}...])}
| {[CONSTRAINT <constraint name>] FOREIGN KEY [<index name>] 
(<column name> [{, <column name>}...]) <reference definition>}
| {[CONSTRAINT <constraint name>] UNIQUE [INDEX] [<index name>] 
(<column name> [{, <column name>}...])} 
| {{INDEX | KEY} [<index name>] (<column name> [{, <column name>}...])}
| {FULLTEXT [INDEX] [<index name>] (<column name> [{, <column name>}...])} 
<column definition>::=
<column name> <type> [NOT NULL | NULL] [DEFAULT <value>] 
[AUTO_INCREMENT]
[PRIMARY KEY] [COMMENT '<string>'] [<reference definition>]
<type>::=
<numeric data type>
| <string data type>
| <data/time data type> 
<reference definition>::=
REFERENCES <table name> [(<column name> [{, <column name>}...])]
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT }]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT }]
[MATCH FULL | MATCH PARTIAL]

Defining Table Types

One especially important MySQL table options allows us to define the type of table that you create in your table definition.
Each ENGINE or table type in MySQL is designed for specific purpose and is processed by associated storage engine.

Syntax :

CREATE TABLE table-name (... columns ... )

ENGINE = {MYISAM | INNODB | MEMORY | MERGE}

Table type	Description
MyISAM	This is default table type in MySQL. MyISAM tables support extensive indexing and are optimized for compression and speed. Unlike other table types, BLOB and TEXT columns can be indexed and null values are allowed in indexed columns. MyISAM tables are not transaction safe, and they do not support full foreign key functionality.
InnoDB	A transaction-safe table that is managed by the InnoDB handler. As a result, data is not stored in a .MYD file, but instead is managed in the InnoDB tablespace. InnoDB tables also support full foreign key functionality in MySQL, unlike other tables. In addition, the InnoDB handler supports automatic recovery and row-level locking. InnoDB tables do not perform as well as MyISAM tables.
MERGE	A virtual table that is made up of identical MyISAM tables. Data is not stored in the MERGE table, but in the underlying MyISAM tables. Changes made to the MERGE table definition do not affect the underlying MyISAM tables. MERGE tables can also be referred to as MRG_MyISAM tables.
MEMORY	A table whose contents are stored in memory, available only as long as the MySQL server is available. If the server crashes or is shut down, the data disappears. Because these types of tables are stored in memory, they are very fast and are good candidates for temporary tables. The keywords MEMORY and HEAP are synonymous.

Options and Attributes

A variety of options and additional attributes can be specified when a column is created. Table below lists the most important options. Note that many attributes are suitable only for particular data types.

MySQL Keyword	Meaning
NULL	The column may contain NULL values. (default)
NOT NULL	The value NULL is not permitted
DEFAULT xxx	The value xxx will be used as a default if value is not specified on add.
PRIMARY KEY	Defines the column as a primary key.
AUTO_INCREMENT	A incrementing sequential number is automatically input for integer values. The column should also be a NOT NULL and a PRIMARY KEY or a UNIQUE.
UNSIGNED	Integers are stored without a sign. Warning: calculations are then also made without a sign.
CHARACTER SET name [COLLATE sort]	For strings, specifies the character set and optionally the desired sort order.

CREATE Table Example

mysql> CREATE DATABASE javaskool_inventory;
Query OK, 1 row affected (0.21 sec)

mysql> USE javaskool_inventory;
Database changed
mysql> DROP TABLE IF EXISTS Passenger;
mysql> CREATE TABLE Passenger
    -> (
    -> PNR INTEGER,
    -> passenger_name VARCHAR(50) NOT NULL,
    -> passenger_address VARCHAR(50) NOT NULL,
    -> passenger_age SMALLINT NOT NULL,
    -> passenger_DOB DATETIME NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.33 sec)

mysql>

ALTER Table

ALTER Table Syntax

ALTER TABLE <table name>
<alter option> [{, <alter option>}...]
<alter option>::=
{ADD [COLUMN] <column definition> [FIRST | AFTER <column name>]}
| {ADD [COLUMN] (<table element> [{, <table element>}...])}
| {ADD [CONSTRAINT <constraint name>] PRIMARY KEY 
(<column name> [{, <column name>}...])}
| {ADD [CONSTRAINT <constraint name>] FOREIGN KEY [<index name>] 
(<column name> [{, <column name>}...]) <reference definition>}
| {ADD [CONSTRAINT <constraint name>] UNIQUE [<index name>] (<column name> [{, <column name>}...])}
| {ADD INDEX [<index name>] (<column name> [{, <column name>}...])}
| {ADD FULLTEXT [<index name>] (<column name> [{, <column name>}...])}
| {ALTER [COLUMN] <column name> {SET DEFAULT <value> 
| DROP DEFAULT}}
| {MODIFY [COLUMN] <column definition> [FIRST | AFTER <column name>]}
| {CHANGE [COLUMN] <column name> <column definition> [FIRST | AFTER <column name>]}
| {DROP [COLUMN] <column name>}
| {DROP PRIMARY KEY}| {DROP INDEX <index name>}
| {DROP FOREIGN KEY <constraint name>}
| {RENAME [TO] <new table name>}
| {ORDER BY <column name> [{, <column name>}...]}
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| {<table option> [<table option>...]}

ALTER Table Example

Add a column

A basic use of altering table is to add columns. In this demo, we are adding a column we forgot to add.

mysql> ALTER TABLE Passenger
    -> ADD COLUMN passenger_email varchar(30);
Query OK, 0 rows affected (1.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc passenger;
+-------------------+-------------+------+-----+---------+-----
| Field             | Type        | Null | Key | Default | Extr
+-------------------+-------------+------+-----+---------+-----
| PNR               | int(11)     | YES  |     | NULL    |
| passenger_name    | varchar(50) | NO   |     | NULL    |
| passenger_address | varchar(50) | NO   |     | NULL    |
| passenger_age     | smallint(6) | NO   |     | NULL    |
| passenger_DOB     | datetime    | NO   |     | NULL    |
| passenger_email   | varchar(30) | YES  |     | NULL    |
+-------------------+-------------+------+-----+---------+-----
6 rows in set (0.63 sec)

mysql>

Droping column

droping column which not required, as shown.

mysql> ALTER TABLE Passenger
    -> DROP passenger_email;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc passenger;
+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| PNR               | int(11)     | YES  |     | NULL    |       |
| passenger_name    | varchar(50) | NO   |     | NULL    |       |
| passenger_address | varchar(50) | NO   |     | NULL    |       |
| passenger_age     | smallint(6) | NO   |     | NULL    |       |
| passenger_DOB     | datetime    | NO   |     | NULL    |       |
+-------------------+-------------+------+-----+---------+-------+
5 rows in set (0.43 sec)

mysql>

Renaming Tables

An existing table can be renamed with another name, as shown.

CREATE TEMPORARY TABLE temp_tab1( column1 INT );
ALTER TABLE temp_tab1 RENAME temp_tab2;
DROP TABLE temp_tab1; -- Should give an error !
DROP TABLE temp_tab2; -- That works !!

DROP Table

To drop or delete a table, use the DROP TABLE statement.

Note:

The optional TEMPORARY keyword prevents the inadvertent loss of a permanent table.
When the IF EXISTS clause is used with a non-existent table, a warning is thrown instead of an error.
Warnings do not stop the execution of a multi-statement script in its tracks.
We can use the DROP statement to drop several tables.

DROP Table Syntax

DROP [TEMPORARY] TABLE [IF EXISTS] <table name> [{, <table name>}...]

DROP Table Example

DROP TABLE IF EXISTS reservation;

NOTE: A master table referenced in a foreign key cannot be dropped without first removing the reference.

Examples

Example

Creating Table in MySQL Server

mysql> create table ordertab
    -> (
    -> orderno int primary key,
    -> name varchar(20),
    -> orderdt date
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.40 sec)

mysql> insert into ordertab values(1,'PIZZA','2012-11-15');
Query OK, 1 row affected (0.15 sec)

mysql> insert into ordertab values(2,'Burger','2012-11-15');
Query OK, 1 row affected (0.15 sec)

mysql> select * from ordertab;
+---------+--------+------------+
| orderno | name   | orderdt    |
+---------+--------+------------+
|       1 | PIZZA  | 2012-11-15 |
|       2 | Burger | 2012-11-15 |
+---------+--------+------------+
2 rows in set (0.00 sec)

mysql> DESC ordertab;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| orderno | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| orderdt | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.19 sec)

mysql> SHOW CREATE TABLE ordertab \g
+----------+---------------------------------------------------------+
| Table    | Create Table
+----------+---------------------------------------------------------+
| ordertab | CREATE TABLE `ordertab` (
  `orderno` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `orderdt` date DEFAULT NULL,
  PRIMARY KEY (`orderno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------+
1 row in set (0.03 sec)

mysql>

More example with adding, removing modifying columns.

mysql> create table stud ( sid int, sname varchar(20), age int,
    -> email varchar(40)
    -> ) ;
Query OK, 0 rows affected (0.11 sec)

mysql> desc stud;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | int(11)     | YES  |     | NULL    |       | 
| sname | varchar(20) | YES  |     | NULL    |       | 
| age   | int(11)     | YES  |     | NULL    |       | 
| email | varchar(40) | YES  |     | NULL    |       | 
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table stud
    -> modify sid char(4);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stud;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | char(4)     | YES  |     | NULL    |       | 
| sname | varchar(20) | YES  |     | NULL    |       | 
| age   | int(11)     | YES  |     | NULL    |       | 
| email | varchar(40) | YES  |     | NULL    |       | 
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table stud
    -> add mobile bigint;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stud;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | char(4)     | YES  |     | NULL    |       | 
| sname  | varchar(20) | YES  |     | NULL    |       | 
| age    | int(11)     | YES  |     | NULL    |       | 
| email  | varchar(40) | YES  |     | NULL    |       | 
| mobile | bigint(20)  | YES  |     | NULL    |       | 
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table stud add saddress varchar(40) after sname;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stud;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sid      | char(4)     | YES  |     | NULL    |       | 
| sname    | varchar(20) | YES  |     | NULL    |       | 
| saddress | varchar(40) | YES  |     | NULL    |       | 
| age      | int(11)     | YES  |     | NULL    |       | 
| email    | varchar(40) | YES  |     | NULL    |       | 
| mobile   | bigint(20)  | YES  |     | NULL    |       | 
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table stud
    -> drop column mobile;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stud;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sid      | char(4)     | YES  |     | NULL    |       | 
| sname    | varchar(20) | YES  |     | NULL    |       | 
| saddress | varchar(40) | YES  |     | NULL    |       | 
| age      | int(11)     | YES  |     | NULL    |       | 
| email    | varchar(40) | YES  |     | NULL    |       | 
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql>

MySQL Server : Open Source Database : Create/Alter/Drop Table

You may also like...

Leave a Reply Cancel reply

Recent Posts

Recent Comments

MySQL Server : Open Source Database : Create/Alter/Drop Table

CREATE Table Syntax

Creating Column Definitions

Defining Table Types

Options and Attributes

CREATE Table Example

ALTER Table Syntax

ALTER Table Example

Add a column

Droping column

Renaming Tables

Note:

DROP Table Syntax

DROP Table Example

Example

Creating Table in MySQL Server

More example with adding, removing modifying columns.

You may also like...

MySQL Server : Open Source Database : MySQL Export / Import

MySQL Server : Open Source Database : Using Auto_Increment

MySQL Server : Open Source Database : MySQL Trigger

Leave a Reply Cancel reply

Recent Posts

Recent Comments