Creating Tables in MySQL

Tables can be created using CREATE TABLE statement and it actually has the following syntax.

CREATE TABLE [IF NOT EXISTS] `TableName` (`fieldname` dataType [optional parameters]) ENGINE = storage Engine;

HERE

• “CREATE TABLE” is the one responsible for the creation of the table in the database.
• “[IF NOT EXISTS]” is optional and only create the table if no matching table name is found.
• “`fieldName`” is the name of the field and “data Type” defines the nature of the data to be stored in the field.
• “[optional parameters]” additional information about a field such as ” AUTO_INCREMENT” , NOT NULL etc.

Example 1:-
CREATE TABLE IF NOT EXISTS `roles`
(
`id` INT (10) NOT NULL AUTO_INCREMENT,
`role_name` VARCHAR(150) NOT NULL,
`description` TEXT DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Example 2:-
CREATE TABLE IF NOT EXISTS `users`
(
`id` INT (10) NOT NULL AUTO_INCREMENT,
`role_id` INT (10) DEFAULT 0,
`name` VARCHAR(150) NOT NULL,
`email` VARCHAR(150) NOT NULL,
`description` TEXT DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE(`email`),
FOREIGN KEY (`role_id`) REFERENCES roles(`id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

If you have any query or suggestions, feel free to ask me via the comment section below.