SQL- Data Types

    The SQL data type signifies, which type of data would be stored in a specific column, this mean each column has a specific data type. When we create a table then only, we are able to set the data type of each column or when we create a new one. It’s the Job of the SQL developer to decide what type of data will be stored in each column when a table created, and the data type set some rules related to what type of data is supposed to enter in that specific column, and based on data type we have different types of operators to perform operations. Syntax to set a Data type:

    column_name Data Type

    SQL Data Types

    In SQL we have 3 Major Data Types: string, numerical, and date & time .

    SQL String Data Types

    Data Type Description
    CHAR(size) CHAR stands for character and with this datatype, we can contain any string of length size . And the size varies from 0 to 255.
    VARCHAR(size) It defines a string which length varies from 0 to 65535 characters.
    BINARY(size) It defines a string which stores binary character of length size.
    VARBINARY(size) Similar to Binary(), but it can hold maximum length.
    TINYBLOB Hold Binary Large Objects of max length 255 bytes
    MEDIUMBLOB It can hold Binary Large Objects of maximum 16,777,215 bytes
    BLOB() Hold Binary Large Objects up to 65,535 bytes of data
    TINYTEXT It can hold a string length of 255 characters
    TEXT(size) It can hold a string of length 2,147,483,647 characters.
    MEDIUMTEXT It can hold a string of maximum 16,777,215 characters
    LONGTEXT It can hold a string of maximum 4,294,967,295 characters
    ENUM(val1, val2, val3…..val65535) It can hold multiple values in an Enumerated list.
    SET(val1,val2,val3,…… val64) It makes a set of possible values and a set can hold 64 values.

    SQL Numeric data types

    Data type Description
    BIT( size ) It can hold bit-value and the size can vary from 1 to 64.
    TINYINT( size ) It is used to hold small size integer value and its range vary from -128 to 127
    BOOL It holds boolean values 0 for False and any non-zero number for True
    BOOLEAN Similar to BOOL
    SMALLINT( size ) It is also used to hold small size integers and its range vary from -32768 to 32767.
    MEDIUMINT( size ) It is used to hold medium size integer and its range vary from 8388608 to 8388607
    INT( size ) It is used to hold integer values and its range vary from -2147483648 to 2147483647
    INTEGER( size ) Similar to INT.
    BIGINT( size ) It is used to hold large integer values and its size varies from -9223372036854775808 to 9223372036854775807
    FLOAT( size , d ) Float is used to hold the decimal numbers, here the size specifies the number of digits and d specify the total numbers after decimal points.
    DOUBLE( size , d ) It is similar to FLOAT, and used to hold the decimal number.
    DECIMAL( size , d ) In SQL to be more precise about the Decimal numbers we use the DECIMAL data type
    DEC( size , d ) Similar to the DECIMAL data type

    SQL Date and Time

    Data type Description
    DATE It is used to hold Date in YYYY-MM-DD format.
    DATETIME It can be used to hold date and time simultaneously in YYYY-MM-DD hh:mm:ss
    TIMESTAMP It stores the number of seconds since the Unix Epoch ('1970-01-01 00:00:00' UTC)
    TIME It is used to hold time in hh:mm:ss format.
    YEAR It is used to hold year in YYYY format.

    SQL Data-Type Quick Summary

    • A data type signifies the type of data which would reside in that column.
    • In SQL we have 3 major types of Data Types, Numeric, String and Date & Time.
    • In General, we do not use all the data types, we mostly use common ones like INT, CHAR, VARCHAR, TEXT, DATE, TIME, etc.

    People are also reading: