SQL- Data Types

By | October 2, 2021
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.

Vamware

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(sized) 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(sized) It is similar to FLOAT, and used to hold the decimal number.
DECIMAL(sized) In SQL to be more precise about the Decimal numbers we use the DECIMAL data type
DEC(sized) 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:

Leave a Reply

Your email address will not be published. Required fields are marked *