MySQL Data types and Attributes Example

1. Date and Time Data Types:

a) DATE:

   create table student(
    id int(10) primary key auto_increment,
    name varchar(20) not null,
    date_of_birth date // Date datatype  column
   );

   insert into student(name,date_of_birth)values('Jahid','1990-01-21'); // date format YYYY-MM-DD or YYYYMMDD
   

b) DATETIME

   create table attendance(
    id int(10) primary key auto_increment,
    student_id int(10),
    in_datetime datetime, //Datetime datatype column
    out_datetime datetime //Datetime datatype collumn
   );

   insert into attendance(student_id,`in`,`out`)values(211,'2015-10-01 09:57:12','2015-10-01 13:02:00');
    //datetime format YYYY-MM-DD HH:MM:SS
   

c) TIME

   create table attendance(
    id int(10) primary key auto_increment,
    student_id int(10),
    time_in time, //time datatype column
    time_out time,//time datatype  column
   
   );    
 insert into attendance(student_id,time_in,time_out)values(211,'09:57:12','13:02:00');
  // time format HH:MM:SS
  

d) TIMESTAMP

   create table student(
    id int(10) primary key auto_increment,
    name varchar(20) not null,    
    date_of_birth date, // Date datatype  column
    entry_datetime timestamp //timestamp datatype  column
   );
   insert into student(name, date_of_birth,entry_datetime)values('Jahid','1990-01-21');
   //timestamp format same as datetime example: YYYY-MM-DD HH:MM:SS
   

2. Numeric Data Types

a. BOOL, BOOLEAN

 create table student_result_status(
    id int(10) primary key auto_increment,
    student_id(10) not null,    
    fail tinyint(1) default 0 // boolean datatype column
   );

insert into student_result_status(student_id,fail)values(2);// pass

insert into student_result_status(student_id,fail)values(2,1);// fail

Double

create table student_gpa(
  id int(10) primary key auto_increment,
  student_id int(10),
  gpa double
);

insert into student_gpa(student_id,gpa)values(2,3.45);