MySQL - Getting Started MySQL - Database MySQL - Data Types MySQL - CREATE Table MySQL - DROP Table MySQL - ALTER Table MySQL - INSERT INTO Table MySQL - SELECT FROM Table MySQL - Select using WHERE clause MySQL - WHERE clause with AND, OR and NOT operators MySQL - UPDATE Table MySQL - DELETE FROM Table MySQL - Alias MySQL - ORDER BY and LIMIT MySQL - INNER JOIN MySQL - LEFT JOIN MySQL - RIGHT JOIN MySQL - UNION MySQL - Aggregate Functions - COUNT, MIN, MAX, AVG and SUM MySQL - GROUP BY MySQL - HAVING MySQL - Subquery MySQL - EXISTS MySQL - ANY and ALL MySQL - INSERT INTO table using SELECT statement MySQL - CASE statement MySQL - IF Control Flow Function

MySQL - CREATE Table

MySQL

← Prev Next →

In this tutorial we will learn to create tables in MySQL.

We have already learned how to create database in the previous tutorial. So, open the terminal, login to MySQL server and run the USE command to start using a database.

What is a database table?

A database table is a place where we store our data in rows and columns format.

Creating a table

To create a table we use the CREATE TABLE table_name command and pass some column names and required data types.

Points to note!

Table contains a primary key column denoted by PRIMARY KEY.
To ensure that a column stores unique values we use UNIQUE KEY constraint.
If we want to set default value for a column to null we write DEFAULT NULL. This means if we don't provide any value for the column then NULL will be saved.
If we want to set default value we write DEFAULT 'VALUE1'. This menas if we don't provide any value for the column then 'VALUE1' will be saved.
If it is mandatory to set a value for a given column then we set it to NOT NULL.
For integer data type column we can also add AUTO_INCREMENT attribute. This will help in increasing the value in the column by 1 everytime we insert a new row in the table.

PRIMARY KEY helps to uniquely identify a row in a table. No two rows in a table can have the same primary key value.

Creating Employee Table

In the following example the employee table has 6 columns.

employeeid ID of the employee and it is the primary key.
firstname The first name of the employee.
lastname The last name of the employee.
score Total points the employee has scored.
lastmodified The date time when the employee details were last modified.
created The date time when the employee account was created.

Lets go ahead and create the employee table.

CREATE TABLE `employee` (
    `employeeid` VARCHAR(20) NOT NULL,
    `firstname` VARCHAR(100) NOT NULL,
    `lastname` VARCHAR(100) NOT NULL,
    `score` INT DEFAULT 0,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`employeeid`),
    UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8;

If we run the above SQL query we will get the following output.

Query OK, 0 rows affected (0.03 sec)

In the above SQL query the table employee is using InnoDB storage engine. The default character set used is utf8.

Syntax for the UNIQUE KEY contraint is as follows.

UNIQUE KEY symbol (`column_name`)

In the above SQL query to create the employee table we are using the following UNIQUE KEY constraint.

UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`)

The symbol format used in the above example is columnName_tableName_UNIQUE. You can use any other format that you wish to follow for the UNIQUE KEY symbol.

Let us now explore each column of the employee table.

Column	Type	Description
employeeid	VARCHAR(20)	This column can save variable length string and max 20 characters. The column has `NOT NULL` attribute that means we have to set some value for this column.
firstname	VARCHAR(100)	This column can save variable length string of max length 100 characters. This is also set as `NOT NULL` meaning we have to set some value for this column.
lastname	VARCHAR(100)	This column can save variable length string of max length 100 characters similar to the firstname column. And we have to set some value for this column as it is set as `NOT NULL`.
score	INT	This column can hold integer value. It has an attribute `DEFAULT 0` which means if we don't give any value then 0 will be automatically saved for this column.
lastmodified	DATETIME	This column will save date time value in the format `'YYYY-MM-DD HH-MM-SS'`. Click here for Date time data type. It has an attribute `DEFAULT NULL` which means if we don't give any value then `NULL` will be saved in this column.
created	DATETIME	Similar to lastmodified column this will save date time value in the format `'YYYY-MM-DD HH-MM-SS'`. It has the attribute `NOT NULL` which means we have to provide some value.

Lets try creating another table and this time we will use the auto increment attribute for the primary key of the table.

Creating Comments Table

The comments table has the following 4 columns.

commentid ID of the comment and it is the primary key.
commentbody This is the body of the comment.
lastmodified The date time when the comment was last modified.
created The date time when the comment was created.

CREATE TABLE `comments` (
    `commentid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `commentbody` VARCHAR(200) NOT NULL,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`commentid`),
    UNIQUE KEY `commentid_comments_UNIQUE` (`commentid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8;

The primary key for the comments table is commentid.

The data type of this column is BIGINT and it is UNSIGNED meaning it can take only positive integer value.

The commentid column is set to NOT NULL meaning a value is required.

We are also using the AUTO_INCREMENT attribute for the commentid column which means we don't have to specify integer value for this column when inserting data into the comments table.

Because of the AUTO_INCREMENT attribute an integer value will be inserted automatically and everytime the value will be increased by 1.

← Prev Next →