How to create Table in MySQL or MariaDB Schema or Database - MySQL Developer Tutorial

How to create Table in MySQL Schema or MySQL Database :


Let's say that you are working as MySQL Developer, MySQL Architect or MySQL DBA. An Excel file is provided to you with some information related to Customer with below columns

First Name,
Last Name,
Age,
Phone Number,
Data of Birth,
Sex

and you are asked to create Table in MySQL or MariaDB Schema or Database and save the data in newly created Table.


Create Table Syntax: 

We can use create statement to create Table in MySQL Database. Below is Create Statement


Create Table Table_Name
(
Column_OneName DataType(Size if required),
Column_TwoName DataType(Size if required),
Column_ThreeName DataType(Size if required),
Column_FourName DataType(Size if required)
)

If we would like to create table with our above requirement, our Create Statement with be

CREATE TABLE `customer` (
  `idcustomer` int(11) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL   
) ;

Before you write your create table statement, I suggest you to study the data type for each of the column. You will choose the data type for each of the column depending upon the data you would like to save in that column.

Here is little details what data types we have used for customer table columns and what type of data it will save.

VARCHAR(50) for First Name and Last Name : It will let us store characters and the max number will be 50.
TinyInt for Age: It will let us store integer value between 0 to 255.
CHAR(9) for PhoneNumber : Char will let us store characters, as we know that we need to store 9 Characters, we used Char(9).
Date for DOB: In Date of Birth column, we will be able to store date as we have data type= date.
Char(1) for Sex: As we would like to store M or F for Sex, Char is going to let us store single character.



Demo video : How to create table in MySQL or MariaDB Schema / Database

No comments:

Post a Comment