Top Posts
Java Database Connectivity with MySQL
ISDN: Integrated Services Digital Network
Network Interface
Differences between the OSI and TCP/IP model
TCP/IP Model
What is Routing? | Types of Routing |...
Routing algorithm
Python List
Python Operators
Life cycle of a thread in Java
TECHARGE
  • HOME
  • BLOGS
  • TUTORIALS
    • ALL TUTORIALS
    • PROGRAMMING TUTORIALS
      • JAVA TUTORIALS
      • C++ TUTORIAL
      • C PROGRAMMING TUTORIALS
      • PYTHON TUTORIAL
      • KNOWLEDGE MANAGEMENT TUTORIALS
      • DATA STRUCTURE AND ALGORITHM TUTORIALS
      • PROGRAMMING EXAMPLES
        • CPP EXAMPLES
        • JAVA EXAMPLES
        • C++ GRAPHICS PROGRAM
    • PROJECTS
      • PYTHON PROJECTS
      • SWIFT PROJECT
    • PPROGRAMMING QUIZ
    • DBMS TUTORIALS
    • COMPUTER NETWORK TUTORIALS
    • COMPUTER NETWORK SECURITY TUTORIALS
    • E COMMERCE TUTORIALS 
    • AWS TUTORIAL
    • INTERNET OF THINGS
    • CHEATSHEET
  • MORE
    • JOBS AND INTERNSHIPS
    • INTERVIEW PREPARATION
    • TECH BOOK
    • TECH NEWS
    • UNIVERSITY PAPERS
    • MNC TWEETS
    • THINKECO INITIATIVES
    • CONTACT US
  • WRITE +
  • ABOUT US
  • HIRE US
SQL Tutorial

SQL Datatypes

by anupmaurya April 7, 2022
written by anupmaurya 0 comment 672 views

Data types tells about the nature of the data that can be stored in the database table.

SQL data types can be broadly divided into following categories.

  1. Numeric data types such as int, tinyint, bigint, float, real etc.
  2. Date and Time data types such as Date, Time, Datetime etc.
  3. Character and String data types such as char, varchar, text etc.
  4. Unicode character string data types, for example nchar, nvarchar, ntext etc.
  5. Binary data types such as binary, varbinary etc.
  6. Miscellaneous data types – clob, blob, xml, cursor, table etc.

*NOTE:Not all data types are supported by every relational database vendors. For example, Oracle database doesn’t support DATETIME and MySQL doesn’t support CLOB data type.

Let’s have a deep look on all the above datatypes

SQL Character and String Data Types

Table of Contents

    • SQL Character and String Data Types
    • SQL Numeric Data Types
    • SQL Date and Time Data Types
    • SQL Binary Data Types
    • SQL Unicode Character and String Data Types
    • SQL Miscellaneous Data Types
  • MySQL Data Types
      • MySQL String Data Types
  • MySQL Numeric Data Types
    • MySQL Date and Time Data Types
DatatypeDescription
CHARFixed length with maximum length of 8,000 characters
VARCHARVariable length storage with maximum length of 8,000 characters
VARCHAR(max)Variable length storage with provided max characters, not supported in MySQL
TEXTVariable length storage with maximum size of 2GB data

SQL Numeric Data Types

DatatypeFromTo
bit01
tinyint0255
smallint-32,76832,767
int-2,147,483,6482,147,483,647
bigint-9,223,372,036, 854,775,8089,223,372,036, 854,775,807
decimal-10^38 +110^38 -1
numeric-10^38 +110^38 -1
float-1.79E + 3081.79E + 308
real-3.40E + 383.40E + 38

SQL Date and Time Data Types

DatatypeDescription
DATEStores date in the format YYYY-MM-DD
TIMEStores time in the format HH:MI:SS
DATETIMEStores date and time information in the format YYYY-MM-DD HH:MI:SS
TIMESTAMPStores number of seconds passed since the Unix epoch (‘1970-01-01 00:00:00’ UTC)
YEARStores year in 2 digit or 4 digit format. Range 1901 to 2155 in 4-digit format. Range 70 to 69, representing 1970 to 2069.

SQL Binary Data Types

DatatypeDescription
BINARYFixed length with maximum length of 8,000 bytes
VARBINARYVariable length storage with maximum length of 8,000 bytes
VARBINARY(max)Variable length storage with provided max bytes
IMAGEVariable length storage with maximum size of 2GB binary data

SQL Unicode Character and String Data Types

DatatypeDescription
NCHARFixed length with maximum length of 4,000 characters
NVARCHARVariable length storage with maximum length of 4,000 characters
NVARCHAR(max)Variable length storage with provided max characters
NTEXTVariable length storage with maximum size of 1GB data

SQL Miscellaneous Data Types

DatatypeDescription
CLOBCharacter large objets that can hold up to 2GB
BLOBFor binary large objects
XMLfor storing xml data
JSONfor storing JSON data

That’s all for a quick roundup on SQL data types.

Now have a look on the Datatypes that are used in MySQL

MySQL Data Types

A list of data types used in MySQL database. This is based on MySQL 8.0.

MySQL String Data Types

CHAR(Size)It is used to specify a fixed length string that can contain numbers, letters, and special characters. Its size can be 0 to 255 characters. Default is 1.
VARCHAR(Size)It is used to specify a variable length string that can contain numbers, letters, and special characters. Its size can be from 0 to 65535 characters.
BINARY(Size)It is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column length in the bytes. Default is 1.
VARBINARY(Size)It is equal to VARCHAR() but stores binary byte strings. Its size parameter specifies the maximum column length in bytes.
TEXT(Size)It holds a string that can contain a maximum length of 255 characters.
TINYTEXTIt holds a string with a maximum length of 255 characters.
MEDIUMTEXTIt holds a string with a maximum length of 16,777,215.
LONGTEXTIt holds a string with a maximum length of 4,294,967,295 characters.
ENUM(val1, val2, val3,…)It is used when a string object having only one value, chosen from a list of possible values. It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank value will be inserted.
SET( val1,val2,val3,….)It is used to specify a string that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values at one time in a SET list.
BLOB(size)It is used for BLOBs (Binary Large Objects). It can hold up to 65,535 bytes.

MySQL Numeric Data Types

BIT(Size)It is used for a bit-value type. The number of bits per value is specified in size. Its size can be 1 to 64. The default value is 1.
INT(size)It is used for the integer value. Its signed range varies from -2147483648 to 2147483647 and unsigned range varies from 0 to 4294967295. The size parameter specifies the max display width that is 255.
INTEGER(size)It is equal to INT(size).
FLOAT(size, d)It is used to specify a floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal point is specified by d parameter.
FLOAT(p)It is used to specify a floating point number. MySQL used p parameter to determine whether to use FLOAT or DOUBLE. If p is between 0 to24, the data type becomes FLOAT (). If p is from 25 to 53, the data type becomes DOUBLE().
DOUBLE(size, d)It is a normal size floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal is specified by d parameter.
DECIMAL(size, d)It is used to specify a fixed point number. Its size parameter specifies the total number of digits. The number of digits after the decimal parameter is specified by d parameter. The maximum value for the size is 65, and the default value is 10. The maximum value for d is 30, and the default value is 0.
DEC(size, d)It is equal to DECIMAL(size, d).
BOOLIt is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true.

MySQL Date and Time Data Types

DATEIt is used to specify date format YYYY-MM-DD. Its supported range is from ‘1000-01-01’ to ‘9999-12-31’.
DATETIME(fsp)It is used to specify date and time combination. Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from ‘1000-01-01 00:00:00′ to 9999-12-31 23:59:59’.
TIMESTAMP(fsp)It is used to specify the timestamp. Its value is stored as the number of seconds since the Unix epoch(‘1970-01-01 00:00:00’ UTC). Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC.
TIME(fsp)It is used to specify the time format. Its format is hh:mm:ss. Its supported range is from ‘-838:59:59’ to ‘838:59:59’
YEARIt is used to specify a year in four-digit format. Values allowed in four digit format from 1901 to 2155, and 0000.
Share
3
FacebookTwitterLinkedinRedditWhatsappTelegramEmail
anupmaurya

Hey there, My name is Anup Maurya. I was born with love with programming and works at TCS. One of best global (IT) services and consulting company as System Administrator . I also love graphics designing. It's my pleasure to have you here.

previous post
SQL Tutorial
next post
Mobile phone brands by country of origin

You may also like

SQL Commands

SQL Tutorial

MySQL Cheatsheet

SQL Tutorial

  • SQL Introduction
  • SQL Datatypes
  • SQL Commands

Recent Posts

  • Java Database Connectivity with MySQL

    May 29, 2022
  • ISDN: Integrated Services Digital Network

    May 28, 2022
  • Network Interface

    May 28, 2022

EDUCATIONAL

  • PyScript: Python in the Browser

  • Best Fake Email Generators (Free Temporary Email Address)

  • How to Find Out Who Owns a Domain Name

  • Mobile phone brands by country of origin

  • Best way to use google search you won’t believe exist

  • 10 mostly asked questions related to WhatsApp

  • Top 8 Programming Languages That Will Rule in 2022

  • Google Cloud Platform

  • Best Online Code Editors For Web Developers

  • How to Write a Synopsis for Project Work

CHEATSHEET

  • Git and Github 2022 Cheat Sheet

  • ReactJs Cheatsheet

  • Linux Commands Cheat Sheet

  • C Programming language Cheatsheet

  • Scala Cheatsheet

  • MySQL Cheatsheet

  • Javascript Cheatsheet

PROJECTS

  • Python Rock Paper Scissors Game

  • Currency Converter in Python

  • Alarm clock GUI application with tkinter

  • Print emojis using python without any module

  • Country Date and Time using Python

  • Covid-19 Tracker Application Using Python

  • Python | GUI Calendar using Tkinter

  • Python: Shutdown Computer with Voice

  • Python GUI Calculator using Tkinter

  • Convert an Image to ASCII art using Python

  • Python YouTube Downloader with Pytube

  • Tic-Tac-Toe using Python

TUTORIALS

  • JAVA TUTORIAL
  • COMPUTER NETWORK
  • DBMS TUTORIAL
  • E-COMMERCE TUTORIAL
  • KNOWLEDGE MANAGEMENT
  • C++ PROGRAMMING
  • COMPUTER NETWORK SECURITY
  • AMAZON WEB SERVICES

TECH NEWS

  • PyScript: Python in the Browser

  • HalloApp is a secure alternative to WhatsApp, made by two early WhatsApp employees

  • 5+ Best Humanoid Robots In The World

  • Windows 11 Now Official, Brings Fresh UI, Centrally-Placed Start Menu

TERMS & POLICY

  • PRIVACY POLICY
  • TERMS AND CONDITIONS

COMPILERS

  • JAVA COMPILER
  • PYTHON COMPILER
  • JS COMPILER
  • C++ COMPILER
  • C COMPILER

JOBS AND INTERNSHIPS

  • TCS off-campus hiring 2022 for software engineers- 2019, 2020, & 2021 Batches

    February 27, 2022
  • Deloitte Recruitment For Any Graduates as Learning Operations Associate Analyst

    February 18, 2022
  • HP Recruitment For Tech Support Intern Position

    February 16, 2022
  • EY Hiring- PAS Global Immigration Advanced Analyst

    February 14, 2022
  • Amazon Recruitment Drive for Virtual Customer Support Associate Position

    February 12, 2022

@2019-21 - All Right Reserved. Designed and Developed by Techarge

TECHARGE
  • HOME
  • BLOGS
  • TUTORIALS
    • ALL TUTORIALS
    • PROGRAMMING TUTORIALS
      • JAVA TUTORIALS
      • C++ TUTORIAL
      • C PROGRAMMING TUTORIALS
      • PYTHON TUTORIAL
      • KNOWLEDGE MANAGEMENT TUTORIALS
      • DATA STRUCTURE AND ALGORITHM TUTORIALS
      • PROGRAMMING EXAMPLES
        • CPP EXAMPLES
        • JAVA EXAMPLES
        • C++ GRAPHICS PROGRAM
    • PROJECTS
      • PYTHON PROJECTS
      • SWIFT PROJECT
    • PPROGRAMMING QUIZ
    • DBMS TUTORIALS
    • COMPUTER NETWORK TUTORIALS
    • COMPUTER NETWORK SECURITY TUTORIALS
    • E COMMERCE TUTORIALS 
    • AWS TUTORIAL
    • INTERNET OF THINGS
    • CHEATSHEET
  • MORE
    • JOBS AND INTERNSHIPS
    • INTERVIEW PREPARATION
    • TECH BOOK
    • TECH NEWS
    • UNIVERSITY PAPERS
    • MNC TWEETS
    • THINKECO INITIATIVES
    • CONTACT US
  • WRITE +
  • ABOUT US
  • HIRE US