MySQL
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.
USE
A database table is a place where we store our data in rows and columns format.
To create a table we use the CREATE TABLE table_name command and pass some column names and required data types.
CREATE TABLE table_name
Points to note!
PRIMARY KEY
UNIQUE KEY
DEFAULT NULL
DEFAULT 'VALUE1'
NOT NULL
AUTO_INCREMENT
PRIMARY KEY helps to uniquely identify a row in a table. No two rows in a table can have the same primary key value.
In the following example the employee table has 6 columns.
employee
employeeid
firstname
lastname
score
lastmodified
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.
InnoDB
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.
columnName_tableName_UNIQUE
Let us now explore each column of the employee table.
DEFAULT 0
'YYYY-MM-DD HH-MM-SS'
NULL
Lets try creating another table and this time we will use the auto increment attribute for the primary key of the table.
The comments table has the following 4 columns.
commentid
commentbody
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.
comments
The data type of this column is BIGINT and it is UNSIGNED meaning it can take only positive integer value.
BIGINT
UNSIGNED
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.