MySQL CREATE TABLE

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type[SIZE] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]
);

A table is a collection of data that contains a set of horizontal rows and vertical columns. It requires a name of table which is unique in a database, fields name and their data types.

CREATE TABLE statement is used to create a new table in a database

IF NOT EXISTS is used to create table only if there are no existing table with the name

NOT NULL is being used to create a record without NULL value

NULL is being used to create a record NULL value

AUTO_INCREMENT tells to add the next available number to the id field

PRIMARY KEY is used to define a column as a primary key. You can use multiple columns separated by a comma to define a primary key.

Example 1 :

 
 CREATE TABLE `employee` (
  `emp_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(30) NOT NULL,
  `date_of_join` date NOT NULL,
  `salary` int(6) NOT NULL,
  `location` varchar(20) NOT NULL,
  `emp_status` int(1) NOT NULL DEFAULT 1,
   PRIMARY KEY(emp_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;