SQL Data Types

  • SQL data types vary from one data base (for example Oracle) to another one (for example MYSQL).
  • Mainly these data types are used, While we use SQL create Statement.
  • Below are the data types available in MySQL Data Base, some of these data types may be as same as other data bases data types.
AUTO_INCREMENT
INT
INTEGER
FLOAT
DOUBLE
CHAR
VARCHAR
BLOB
DATE
DATETIME
BIGINT
BINARY
BIT
BLOB DATA TYPE
BOOLEAN
CHAR BYTE
DEC
DECIMAL
DOUBLE PRECISION
ENUM
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
SET DATA TYPE
SMALLINT
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYTEXT
VARBINARY
YEAR DATA TYPE

Let us learn only very important and common data types across all the databases for now , which would be more than sufficient to write SQL Queries.

AUTO_INCREMENT

The AUTO_INCREMENT attribute is used to generate a unique id’S for new rows.
Let us check this with an example

Examples:

Let us first create a table called Banana with AUTO_INCREMENT data type.

below you can see the ID is auto incremented from 1 to 4.


INTEGER (size in numeric )

  • This type is a synonym for INT.
  • all the numeric values are stored using integer data type in SQL queries.

BIGINT : data type is used to hold a very big integer value.

  • The signed range is -9223372036854775808 to 9223372036854775807.
  • The unsigned range is 0 to 18446744073709551615.

CHAR ( 0 to 255 characters ):

  • Data type is used to store a fixed-length string.
  • if you specify the size of CHAR data type more than 255 characters then it will throw below error.

VARCHAR( size of Text )

  • stands for Variable length of characters.
  • VARCHAR values are stored using as many characters as are needed, plus
    one byte to record the length (two bytes for columns that are declared
    with a length longer than 255).

DATETIME:

  • this data type used to display both date and time together.
  • It supports the range between ‘1000-01-0100:00:00’ and ‘9999-12-31 23:59:59’.
  • MySQL displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format, but allows you to assign values to
    DATETIME columns using either strings or numbers.

BLOB:

  • It stands for Binary Large Objects.
  • It is used for storing data with the maximum length of 65,535 (216 – 1) bytes.

BOOL, Boolean

  • data types are used to store TRUE or False data.
  • A value of zero (0) is considered false.
  • Non-zero(1) values are considered true:

TEXT Data Type:

  • It allows us to store a TEXT column with a maximum length of 65,535 (216 – 1) characters.

Let us create a table that contains some of these data types, i am leaving other data types for you to practice by yourself.