In the modern world, there are a large number of tools and technologies designed to store information. One of the most common methods are databases, for which various control systems are used. This storage method assumes that all data is clearly structured and listed in special tables. They, in turn, consist of attribute columns of a particular data type.
Data type - what is it?
Today, there are several definitions that explain the concept of the term “data type”. However, each of them has one common meaning. Therefore, the data type can be arbitrarily designated as a group of data characterized by its values (symbolic, numeric, etc.), as well as operations applied to the values in question.
The scope of data types is multifaceted. They are used not only for storing information, but also in programming for solving various problems. When designing programs, the practice of developing and using custom data types with a specific set of operations is widespread. However, user data is always based on basic data types. The SQL standard is also based on the use of the most common base types, but with a number of specific additions.
Data Type Classification
Grouping of data by their type arose long ago and was caused by the need to structure information for more convenient processing. Currently, the basis of existing data types is formed by two: character and numeric.
Based on them, a modern classification was developed, including pointers, logical, integer, numeric, floating-point, and string data types. SQL - classification fully covers all of the above. However, for some modern DBMS there are additional add-ons. These include Oracle and MySQL.
Basic data types
Used when creating table attributes that meet SQL standards , data types are divided into 4 classes:
- string values
- fractions;
- integer values
- date and time values.
String data type
The first group of values allows you to store any data represented as characters.
These can be special characters, numbers and letters, which together will be treated as strings in any SQL query. Data types, a table with a listing of which is presented below, form the first group.
CHAR (size) | Used to store strings. The parameter in brackets allows you to fix the length of the stored string. The maximum size in bytes that can be specified for a string is 255. |
VARCHAR (size) | Similarly to the previous type, it allows storing strings up to 255 characters long. However, the difference from CHAR is that the required amount of memory is allocated to store the value of this type. That is, for a string of 5 characters, 6 bytes of memory are required. In the first case, the memory for the value will be allocated according to the specified parameter. |
TINY TECH | Used to store string data up to 255 characters in length. |
TEXT | Used to store textual information, the size of which does not exceed 65,535 letters. |
Blob | The data type under consideration is similar to the TECHT type and allows storing textual information in the database, the volume of which can reach 65,535 characters. But in practice it is used for storing audio data, drawings, electronic documentation, etc. |
MEDIUM TECH | It was developed on the basis of the TECHT type, but it allows you to store more data due to the increased size of up to 16,777,215 letters or characters. |
MEDIUM BLOB | It is used to save electronic documents in the database, the size of which does not exceed the mark of 16,777,215 characters. |
LONG TECH | Functionally similar to previous types, but with increased memory capacity up to 4 gigabytes. |
LONG BLOB | Allows you to put large volumes of data into the database (4,294,967,295 characters). |
ENUM (a, b, c, etc.) | A special data type used to specify a list of possible values. Allows you to specify 65535 values. Strings of the type in question can take a single value from those specified in the set. In the case when values will be added that are not present in the specified list, empty values will be written to the table. |
SET | Specifies a set of valid values. Unlike the previous type, it is used to contain 64 parameters that can be initialized by any or several elements of the given arguments. |
Fractional Data Type Table
Fractional SQL data types are used to store floating point numbers. In practice, as a rule, various financial indicators are set. Depending on the required accuracy, one of the three presented is used:
FLOAT (size, d) | Allows to contain fractional numbers of indicated accuracy d. |
DOUBLE (size, d) | Used to store fractional numbers with binary precision. |
DECIMAL (size, d) | Storing fractional values as strings. |
For example, in bank calculations, the accuracy of the fractional part reaches a value of 8 or 10 characters. The first two types cannot be involved in this area.
Storage of financial indicators in the form of lines greatly facilitates the solution of many problems. However, when solving financial issues or performing various SQL operations, data type conversion is of great importance. Developers must take into account the type of storage and processing methods so that the data always remains unchanged.
Integer data type
Integers are a separate group of numbers forming one of the main classes. Integer SQL data types are based on the use of the base type INTEGER with some extension of its properties.
INT (size) | Storage of integer values forming a range [-2 31 ; 2 31 -1] |
TINYINT (size) | Serves for storing numbers in the range from -128 to 127 |
SMALLINT (size) | It is characterized by an increased range of stored values in the size from -32 768 to 32 767 |
MEDIUMINT (size) | Used to store numbers from -2 2 3 to 2 2 3 -1 |
BIGINT (size) | Covers the range of integer values, starting from -2 63 to 2 63 -1 |
Choosing the right data type can significantly save memory and reduce server time costs when necessary SQL queries are executed. Data types, or rather their range, determine the amount of storage space required.
Therefore , it is important for database developers to remember that using large ranges for attributes entails an increase in memory costs. It is necessary to clearly analyze the tasks to be solved and identify cases where the approximate range is known and the condition for using signed numbers is defined. If the range of arguments used is small, and all numbers are positive, then it will be more correct to use the unsigned type formed by the UNSIGNED attribute.
Date and Time Data Types
When learning the basics of SQL, date and time data types are of particular interest.
The use of the following types provides additional advantages in the development of systems whose operation depends on time indicators.
DATE | The main purpose is to store the date in the format YEAR-MONTH-DAY (“YYYY-MM-DD” or “uuuu-mm-dd”). Typically, the values are separated by “-”, however, any character except digits can be used as a separator. |
TIME | Allows you to enter temporary values in a table cell. All values are specified in the format "hh: mm: ss" |
Datetime | Combines the functions of the previous two types. The storage format is as follows: "uuuu-mm-dd hh: mm: ss". |
TIMESTAMP | Saves the date and time, calculated in the number of seconds elapsed from midnight 01/01/1970 to the specified value. |
YEAR (M) | Used to store annual values in two- or four-digit format. |
What else do you need to know?
All these data types are systematized in detail by Microsoft. She developed SQL data types in more detail.
For example, the firm has detailed how much memory is allocated in bytes when using each type. Having studied the available information, it is easier for developers to design the structure of tables and the entire database based on the hardware capabilities of the server.
Special Pointer - NULL
Sometimes when filling the database, a situation arises when there is no need to enter information in all columns when adding a record to the table. For this, a special null value pointer is used - NULL , which uses the SQL language as an aid. The data types of the columns, which do not have to be filled, are specified when creating tables with an operator that allows the inclusion of empty values. Otherwise, the null operator with an optional prefix, NOT can be used to indicate that all values are required.
Null pointer It does not have a type, but simply indicates an empty value in the database tables. Therefore, it can be combined with any of the above types.