SQL Data Types are an important component of the attributes present in the tables, as well as the queries used to retrieve data from those tables. SQL data types define attributes based on the categories to which they belong.
Data types are mainly classified into three categories for every database.
- String Data types
- Numeric Data types
- Date and Time Data types
String Data types:
String data types are used to store text or character data such as names, addresses, or descriptions. VARCHAR, CHAR, and TEXT are examples of common string data types.
| Data Type | Description |
| CHAR(Size) | This data type is used to store fixed-length character strings. It takes up a fixed amount of space in the database, regardless of the length of the actual string. |
| VARCHAR(Size) | This data type is used to store variable-length character strings. It takes up only the amount of space needed to store the actual string. |
| BINARY(Size) | This data type is used to store binary data, such as images or audio files. |
| 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. |
| TINYTEXT | It holds a string with a maximum length of 255 characters. |
| MEDIUMTEXT | It holds a string with a maximum length of 16,777,215. |
| LONGTEXT | It holds a string with a maximum length of 4,294,967,295 characters. |
| ENUM(val1, val2, val3,…) | When a string object has only one value from a list of possible values, it is used. It is an ENUM list with 65535 values. If you enter a value that does not exist in the list, a blank value is inserted. |
Numeric Data types:
Numeric data types are used to store numeric data like integers or decimal values. INT, BIGINT, DECIMAL, and FLOAT are examples of common numeric data types.
| Data Type | Description |
| BIT(Size) | This data type is used to store bit values (either 0 or 1), and the size parameter specifies how many bits are stored per value. The default size is 1. |
| INT(size) | It is used to represent an integer value. Its signed range is -2147483648 to 2147483647, and its unsigned range is 0 to 4294967295. The size parameter specifies the maximum display width, which is set to 255. |
| FLOAT(size, d) | It specifies a floating point number. The total number of digits is specified by the size parameter. The d parameter specifies the number of digits after the decimal point. |
| FLOAT(p) | It specifies a floating point number. The p parameter was used by MySQL to determine whether to use FLOAT or DOUBLE. If p is between 0 and 24, the data type is changed to FLOAT (). If p is between 25 and 53, the data type is 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. |
| BOOL | It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true. |
Date and Time Data types:
Date/time data types are used to store date and time values such as birthdays, creation dates, and deadlines. DATE, TIME, DATETIME, and TIMESTAMP are examples of common date/time data types.
| Data Type | Description |
| DATE | This data type is used for storing dates in the format YYYY-MM-DD. The supported range of values is from ‘1000-01-01’ to ‘9999-12-31’. |
| DATETIME(fsp) | This data type is used for storing date and time combinations in the format YYYY-MM-DD hh:mm:ss. The fsp parameter specifies the number of decimal places for the fractional seconds (with a range of 0 to 6). The supported range of values 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’ |
| YEAR | This data type is used for storing years in a four-digit format (e.g. 2023). The supported range of values is from 1901 to 2155, as well as the special value 0000 (which represents “0000-00-00” in a DATE or DATETIME column). |
Note: also read about SQL: create command
Follow Me
Please follow me to read my latest post on programming and technology if you like my post.
https://www.instagram.com/coderz.py/
https://www.facebook.com/coderz.py
Staying up to the mark is what defines me. Hi all! I’m Rabecca Fatima a keen learner, great enthusiast, ready to take new challenges as stepping stones towards flying colors.
Leave a Comment