Summary -
In this topic, we described about the Datatypes Reference with detailed example.
SQL datatypes that are categorized into different types as Numeric, String, Date and Time, Unicode character string, Binary, Miscellaneous Data types are listed as follows –
SQL Numeric data types are listed as follows -
| Data Type | Description | Syntax |
|---|---|---|
| BIT | A bit-value type.The number of bits that can hold is specified in size. | BIT(SIZE) |
| TINYINT | A very small integer. Uses one-byte memory. | TINYINT(SIZE) |
| TINYINT(SIZE) UNSIGNED | ||
| SMALLINT | A small integer. | SMALLINT(SIZE) |
| SMALLINT(SIZE) UNSIGNED | ||
| INT or INTEGER | An integer. Uses four bytes of memory. |
INT(SIZE) or INTEGER(SIZE) |
| INT(SIZE) UNSIGNED) or INTEGER(SIZE) UNSIGNED | ||
| BIGINT | A large integer. Uses 8-bytes of memory. | BIGINT(SIZE) |
| BIGINT(SIZE) UNSIGNED | ||
| DECIMAL | A flexi-point number. Uses 5 to 17 bytes of memory. | DECIMAL (SIZE, D) |
| DEC | Equals to DECIMAL | DEC (SIZE, D) |
| Numeric | Numeric types more than BIGINT. | NUMERIC(SIZE) |
| FLOAT | Floating-point types. The maximum value of n is 53. If the n value lies between 1 to 24, the float data type applied. If the n values lie between 25 to 53, double data is considered. Uses 4 to 8 bytes memory based on n value. Mantissa precision is 7 digits. |
FLOAT(n) |
| REAL | Floating-point data. Uses 4 bytes of memory. Mantissa precision is 15 digits. | REAL |
| DOUBLE | Norma size floating-point number. The total number of digits specified in SIZE. The number of digits specified after decimal point is specified in d. | DOUBLE (SIZE, D) |
| DOUBLE PRECISION | DOUBLE PRECISION (SIZE, D) |
SQL string data types are listed as follows -
| Data Type | Description | Syntax |
|---|---|---|
| CHAR | Fixed length character strings. | CHAR(n) |
| VARCHAR | Variable length character strings. | VARCHAR(n) |
| VARCHAR (max) | Variable length storage with provided max characters. This is not supported by MySQL. | VARCHAR (max) |
| TEXT | Variable-length storage of text. | TEXT |
SQL Date and Time data types are listed as follows -
| Datatype | Description | Syntax | Format |
|---|---|---|---|
| DATE | Stores date | DATE | YYYY-MM-DD |
| TIME | Stores time | TIME | HH:MI: SS |
| DATETIME | Stores date and time information in the format | DATETIME | YYYY-MM-DD HH:MI: SS |
| TIMESTAMP | Stores number of seconds passed | TIMESTAMP | YYYY-MM-DD HHLMI: SS UTC |
| YEAR | Stores year in 2-digit or 4-digit format. | YEAR | YYYY |
SQL Unicode Character and string data types are listed as follows -
| Datatype | Description | Syntax |
|---|---|---|
| NCHAR | Fixed length Unicode Character strings | NCHAR(n) |
| NVARCHAR | Variable length Unicode Character strings | NVARCHAR(n) |
| NVARCHAR (max) | Variable length storage with provided max Unicode characters. This is not supported by MySQL. | NVARCHAR (max) |
| TEXT | Variable-length storage of Unicode text. | TEXT |
SQL Binary data types are listed as follows –
| Datatype | Description | Syntax |
|---|---|---|
| BINARY | Fixed length binary data | BINARY(n) |
| VARBINARY | Variable length binary data | VARBINARY(n) |
| VARBINARY (max) | Variable length storage with provided max Unicode characters. This is not supported by MySQL. | VARBINARY (max) |
| IMAGE | Variable length storage | IMAGE |
SQL Miscellaneous data types are listed as follows -
| Datatype | Description | Description |
|---|---|---|
| CLOB | Character large objects | CLOB |
| BLOB | For binary large objects | BLOB |
| XML | for storing xml data | XML |
| JSON | for storing JSON data | JSON |