How To Create Table With Date In Mysql

In this article, I am going to explain the MySQL CREATE TABLE statement with examples. The following syntax contains basic statements to create a table in MySQL.

CREATE TABLE 'schemaname'.'tablename'(

column_1 datatype ( length ) NOT NULL | DEFAULT | UNIQUE ,

. . . ,

Primary key ,

Foreign key

) ENGINE = storage_engine ;

The Create table command has the following aspects. It is described under the sections:

Table Name
Column data type and attributes
Primary key and foreign keys

Table Name: tblname

The table name must be specified as <DBName>.<TableName> to create a table in a specific database. This command assumes that the database name specified in the create table command does exist. If you do not specify the database name, then it returns the following error.

ERROR 1046 (3D000): No database selected

See the following image:

MySQL create table example: Database not selected error

Column data types and Attributes

The list of the columns must be followed by its data type and table constraint. The column name must be separated by the comma (,). You must specify the column name in the following format:

"Column_name" data_type(length) [table_constraint] [table_option]

data_type:

It represents the data type of the column. MySQL has the following three main categories of the data type.

Numeric Datatypes
Text data type
Date and Time data types

Below is the list of the numeric data type.

Data type Name	Normal Range	Unsigned Range
TINYINT()	-128 to 127 UNSIGNED.	0 to 255
SMALLINT()	-32768 to 32767	0 to 65535
MEDIUMINT()	-8388608 to 8388607 UNSIGNED.	0 to 16777215
INT( )	-2147483648 to 2147483647	0 to 4294967295
BIGINT( )	-9223372036854775808 to 9223372036854775807	0 to 18446744073709551615

Below is the list of Text data types.

Data type name	Type	Range
CHAR( )	fixed string	255 characters
VARCHAR( )	Variable string	255 characters
TINYTEXT	string	255 characters
TEXT	string	65535 characters
MEDIUMTEXT	string	16777215 characters
LONGTEXT	string	4294967295 characters

Below is the list of date and time data types

Data type Name	Format
DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MM:SS
TIMESTAMP	YYYYMMDDHHMMSS
TIME	HH:MM:SS

Table constraints

You can use any of the following table constraints.

NOT NULL: Ensures that the value of the column must not be null
CHECK: Before inserting data in the table, it evaluates the condition specified in the CHECK constraint. If the condition fails, then the insert statement fails
DEFAULT: Default values of the column. If you do not specify the value of the column in the insert statement, the query inserts the value specified in the DEFAULT constraint

Primary and Foreign keys

Once columns are defined, you can create primary key and foreign keys using the following keywords

PRIMARY KEY: It's a unique index and must be defined as NOT NULL. A table can have only one primary key. The PRIMARY KEY is placed first in the create table statement
FOREIGN KEY: MySQL supports the foreign keys. A table can have more than one foreign key that references the primary key of different tables

MySQL Create Table example

If you want to create a table using MySQL Workbench, you must configure a new connection. To do that, open MySQL workbench, and on the Welcome screen, click on "MySQL connections." See the following image:

MySQL create table example: Welcome screen

In "Setup New Connection" dialog box, provide the desired name of the connection, Hostname or IP Address of the MySQL database server, port, user name, and password and click on OK. See the following image:

MySQL create table example: Configure new connection

Execute the following query to create a new table named "tblEmployees" in the "Employees" database.

CREATE TABLE ` employees ` . ` tblemployee ` (

` Employee_ID ` INT NOT NULL AUTO_INCREMENT ,

` Employee_Name ` VARCHAR ( 45 ) NOT NULL ,

` Employee_Department_ID ` INT NOT NULL ,

` Employee_Grade_ID ` INT NOT NULL DEFAULT A ,

` Employee_Salary ` INT NOT NULL ,

PRIMARY KEY ( ` Employee_ID ` ) ,

INDEX ` FK_Department_ID_idx ` ( ` Employee_Department_ID ` ASC ) VISIBLE ,

CONSTRAINT ` FK_Department_ID `

FOREIGN KEY ( ` Employee_Department_ID ` )

REFERENCES ` employees ` . ` department ` ( ` Department_ID ` )

ON DELETE RESTRICT

ON UPDATE CASCADE ) ;

Following are the details of the columns:

The ID of the Employee is saved in the employee_id column. It is a primary key of the table and has an auto-increment column; hence you do not have to specify the value for this column explicitly. When you insert the data in the table, MySQL generates a sequential integer for the employee_id column
The Name of the employee is saved in the employee_name column. The data type of the column is varchar(), and the length is 45. You cannot insert a NULL value in the employee_name column
The department ID of the employee is saved in the employee_department_id column. The data type of this column is INTEGER. It's a foreign key column that references the department_id column of the tbldepartment table. If any row is updated in the tbldepartment table, the values in tblemployee updates automatically (ON UPDATE CASCADE), and the delete operation on the tbldepartment is restricted (ON DELETE RESTRICT)
The grade of the employee is saved in the employee_garde column. The data type of this column is varchar and length is 2. The DEFAULT constraint has been created on this column. If we do not specify any value for this column, MySQL inserts the "A" as a default value
The salary of the employee is saved in the employee_salary column. The data type of the column is INTEGER

View the table definition using MySQL Workbench

To view the table from MySQL workbench, Expand Employees database from Left pan expand tables. Under Tables, you can see the table "tblEmployees" has been created. See the following image:

MySQL create table example: View table in MySQL workbench

To view the table definition, execute the following command in the query editor window.

Describe employees . tblemployee

Following is the output:

MySQL create table example: View table definition in MySQL workbench

How to view the table definition using MySQL Command-line

To view the table definition using the command-line tool, open the MySQL command-line tool, and enter the password to login to the MySQL database.

MySQL create table example: Open MySQL command-line

Select the employees database. Execute the following query

Output:

MySQL create table example: connect to the employee database

View the table definition by executing the following command.

Describe employees . tblemployee

Following is the output:

MySQL create table example:View table definition using query.

Summary

In this article, I have explained about the MySQL create table statement with examples. I have covered the create table syntax and how to view the definition of the table using MySQL workbench and MySQL command-line tool.

Author
Recent Posts

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com