What is Data type?
Like any other programming language, SQl commands are distributed among various functions. Data types describes the nature of a data which tells the compiler or interpreter to use the data in its various forms. In context to SQL Data types , A data type defines the type of value stored in a column field : Integer data , character data , date and time , and so on.
SQL Data types :
Without Further delay lets jump to the topic and enjoy the topic-SQL Data types
A table in a database comprises of rows/record and columns , columns are always defined with its name , and its data type. Name and data type makes a column complete. A data type helps the user and Administrator to understand what type of data is expected inside the column, and also defines the data interaction with SQL.
Column: name + data type
In MySQL there are three different types of data types : number , text, date
Following table mentioned down below gives the descriptive information on Text data type:
Text Data Type:
| Data type | Description |
|---|---|
| CHAR(size) | It Holds a string (can contain letters, numbers, and special characters). (size) represents the fixed size of the string.It Can also store up to 255 characters |
| VARCHAR(size) | It has the same function as of CHAR(size) , but Holds a variable length string (can contain letters, numbers, and special characters). (size) this parameter stores the fixed size of the string. Can store up to 255 characters. Note: any (value >255 ) will be converted to a TEXT type |
| TINYTEXT | maximum length of 255 characters are to be stored . Type-“String “ |
| TEXT | Holds a string with a maximum length of 65,535 characters |
| BLOB | (Binary Large OBjects). Holds up to 65,535 bytes of data . set-{BLOB, MEDIUMBLOB, LONGBLOB} |
| MEDIUMTEXT | Holds a string with a maximum length of 16,777,215 characters |
| MEDIUMBLOB | Holds up to 16,777,215 bytes of data.It is used for Binary large objects. |
| LONGTEXT | string with a maximum length of 4,294,967,295 characters could be stored . |
| LONGBLOB | For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data |
| ENUM(x,y,z,etc.) | Note: The values are sorted in the order you enter them.
You enter the possible values in this format: ENUM(‘X’,’Y’,’Z’) |
| SET | Similar to ENUM except that SET may contain up to 64 list items and can store more than one |
Number data types:
| Data type | Description |
|---|---|
| TINYINT(size) | -128 to 127 [range of data]. . The size is mentioned to the right |
| SMALLINT(size) | -32768 to 32767 [range of data]. The maximum number of digits accommodated are mentioned to the left , and specified in parenthesis. |
| MEDIUMINT(size) | -8388608 to 8388607 normal. (size is mentioned in the parenthesis) |
| INT(size) | -2147483648 to 2147483647 normal. The maximum number of digits may be specified in parenthesis |
| BIGINT(size) | -9223372036854775808 to 9223372036854775807 normal. The maximum number of digits may be specified in parenthesis |
| FLOAT(size,d) | A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. ‘d’ parameter states the number of digits present to the right of the decimal point. |
| DOUBLE(size,d) | A large number with a floating decimal point. (size) :size parameter is same as of mentioned in TINYINT, SMALLINT ,and so on. The maximum number of digits to the right of the decimal point is specified in the d parameter |
| DECIMAL(size,d) | A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter |
Date data types:
| Data type | Description |
|---|---|
| DATE() | Format: YYYY-MM-DD Ex: 2018-03-07 The supported range is from ‘1000-01-01’ to ‘9999-12-31’ |
| DATETIME() | Combination of Date and time . Format: YYYY-MM-DD HH:MI:SS Ex:2018-03-07 02:03:56 The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ |
| TIMESTAMP() | TIMESTAMP values are stored with the same format of Datetime (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD HH:MI:SSThe supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC |
| TIME() | Format: HH:MI:SS Defines timeNote: The supported range is from ‘-838:59:59’ to ‘838:59:59’ |
| YEAR() | A year in two-digit or four-digit format.Note: Values allowed in four-digit format: 1670 to 2098. Values are also allowed in two-digit format: 70 to 89, representing years from 1970 to 2089 |
Trivia:
You might have noticed that In the Date data type, Both the TIMESTAMP and DATETIME has the same functions. So , why ‘2’ data types are required? Why not one ?
Let’s look at difference between TIMESTAMP and DATETIME:
- In MySQL5+,
TIMESTAMPvalue converts from current time to UTC and vice-versa whileDATETIMEdoes not do any conversion. TIMESTAMPdiffers with current timezone settings whileDATETIMEremains constant.TIMESTAMPdata can be indexed whileDATETIMEdata can not.- Queries with
DATETIMEwill not be cached , but queries withTIMESTAMPcan.
This is all about the data types in Python.