Data types are used to represent a kind of data that can be stored in the database. The data type is the set of representable values. It is also known as an attribute which specifies a data type of the object. Each column, variable and expression has the associated data type in the SQL code when the table was created.
Important points about SQL data types
- Relational database providers do not support all types of data. For example, the Oracle database does not support DATETIME and MySQL does not support the CLOB data type. So when designing the database schema and writing SQL queries, be sure to check whether the data types are supported or not.
- The data types listed here do not include all types of data; These are the most commonly used data types. Some providers of relational databases have their data types which may not be listed here. For example, Microsoft SQL Server has many data types, but since other preferred database providers do not support them, they are not listed here.
- Each relational database provider has its maximum size for different types of data and you don't need to remember that. The idea is to know what type of data to use in a specific scenario.
SQL data types
The SQL developer must decide the type of data to store in each column when creating the table. The data type is the guide SQL uses to understand the type of data expected in each column. It also indicates how SQL will interact with the stored data.
SQL data types mainly classified into six categories for each database.
- String data types
- Numerics data types
- Date and time
- Binary data types such as binary, varbinary, etc.
- Unicode character string data types such as nchar, nvarchar, ntext, etc.
- Other types of data such as clob, blob, XML, cursor, table, etc.
1. Numerics data types
| Data type |
Start from |
To |
| bit |
0 |
1 |
| tinyint |
0 |
255 |
| int |
-2,147,483,648 |
2,147,483,647 |
| decimal |
-10^38 +1 |
10^38 -1 |
| numeric |
-10^38 +1 |
10^38 -1 |
| float |
-1.79E + 308 |
1.79E + 308 |
| real |
-3.40E + 38 |
3.40E + 38 |
2. Date and time
| Data type |
Description |
| DATE |
Stores date in YYYY-MM-DD format |
| TIME |
Stores the time in HH:MI:SS format |
| DATETIME |
Stores date and time information in YYYY-MM-DD HH:MI:SS format |
| TIMESTAMP |
Stores the number of seconds since the Unix era (Timestamp) (‘1970-01-01 00:00:00’ UTC) |
| YEAR |
Stores the year in 2-digit or 4-digit format. Range 1901 to 2155 in 4-digit format. Range 70 to 69, representing 1970 to 2069. |
3. SQL Character and String data types
| Data types |
Description |
| CHAR |
Fixed length with a maximum length of 8000 characters |
| VARCHAR |
Variable length storage with a maximum length of 8000 characters |
| VARCHAR(max) |
Variable-length storage with the maximum number of characters provided, not supported in MySQL |
| TEXT |
Variable length storage with a maximum size of 2 GB of data |
4. Binary SQL data types
| Data types |
Description |
| BINARY |
Fixed length with a maximum length of 8000 bytes |
| VARBINARY |
Variable length storage with a maximum length of 8000 bytes |
| VARBINARY(max) |
Variable length storage with the maximum number of bytes provided |
| IMAGE |
Variable length storage with a maximum size of 2 GB of binary data |
5. Other SQL data types
| Type de données |
Description |
| CLOB |
Large character objects that can hold up to 2 GB |
| BLOB |
For large binary objects |
| XML |
To store XML data |
| JSON |
To store JSON data |
MySQL RDBMS
1. String data types
| Data types |
Description |
| Varchar(size) |
It is used to specify a variable length string that can contain numbers, letters and special characters. Its maximum size is between 0 and 65535 characters. |
| Char(size) |
It is used to specify a fixed length string that can contain numbers, letters and special characters. By default, it can contain 1 character. Its maximum size is between 0 and 255 characters. |
| VARBINARY(size) |
It is as similar as VARCHAR(), and the only difference is that it stores binary byte strings. The size parameter specifies the maximum column in bytes |
| Binary(size) |
It is used to store strings of binary bytes. The default value is 1 and its size parameter specifies the length of the column in bytes. |
| TINYTEXT |
It contains a string with a maximum value of 255 characters. |
| TEXT(size) |
It is used to store a string with a maximum length of 255 characters, similar to CHAR(). |
| LONGTEXT |
It contains a string with a maximum value of 4,294,967,295 characters. |
| ENUM(val1,val2,….) |
It is used when a string object has only one value, chosen from a list of possible values. You can list up to 65,535 values in the ENUM list. If a value is inserted, it does not appear in a list and the empty value will be inserted. The values are sorted in order when entered. |
| SET(val1,val2,……) |
It is used to specify the string that can contain 0 or more values, chosen from a list of possible values. At the same time, 64 values can be listed. |
| BLOB(size) |
It is used for large binary objects that can contain up to 65,535 bytes. |
2. Numerics data types
| Data types |
Description |
| BIT(size) |
Used for a bit value type. Size is used to specify the number of bits. The range is 1-64. By default, the value is 1. |
| INT(size) |
Used for the whole value. The range is from -2147483648-2147483647. The size parameter specifies the maximum display width of 255. |
| INTEGER(size) |
It is similar to INT (size). |
| FLOAT(size,d) |
Used for a floating point number. The size parameter specifies the total number of digits. d is used to define the number digits after the decimal point. |
| Float(p) |
Used for a floating point and double number. If the value of p is between 0 and 24, the data becomes floating and if the value of p is between 25 and 53, the data becomes double. |
| DOUBLE(size,d) |
It is similar to FLOAT (size, d). |
| DECIMAL(size,d) |
Used to specify a fixed point number. The maximum value size can contain 65, and by default its value will be 10 and d may contain a maximum value of 30, and by default the value is 0. |
| BOOL |
Used to specify Boolean values. 0 is considered false and the remaining non-zero values are true. |
3. Date and Time data types
| Data types |
Description |
| DATE |
Used to specify the date format. In MySQL, the format is YYYY-MM-DD. The range is from "1000-01-01" to "9999-12-31". |
| DATETIME |
Used to specify the combination of date and time. The format is YYYY-MM-DD hh:mm:ss. The range is from "1000-01-01 00:00:00" to "9999-12-31 23:59:59". |
| TIMESTAMP |
Used to specify the time stamp. The format is YYYY-MM-DD hh:mm:ss. The supported range is "1970-01-01 00:00:01" UTC to "2038-01-09 03:14:07" UTC. |
| TIME |
Used to specify the time format. The format is hh:mm:ss. The range is from “-838:59:59” to “838:59:59”. |
| YEAR |
Used to specify the year in four-digit format. The range is from 1901 to 2155 and 0000. |
Partager ce cours avec tes amis :
