adplus-dvertising

SQL data types

last news

SQL data types

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 :
Rédigé par ESSADDOUKI Mostafa
ESSADDOUKI
The education of the 21st century opens up opportunities to not merely teach, but to coach, mentor, nurture and inspire.