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: