In today's world, there is a largethe 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 method of storage assumes that all data is clearly structured and stored in special tables. They, in turn, consist of columns-attributes of a certain data type.
Data type - what is it?
Today there are several definitions,explaining the concept of the term "data type". However, each of them has one common meaning. Therefore, the data type can be conditionally designated as a data group characterized by its values (symbolic, numerical, etc.), as well as the operations applied to the values considered.
The scope of data types is multifaceted.They are used not only for storing information, but also for programming for various tasks. When designing programs, it is common practice to develop and use your own data types with a certain set of operations. However, the basic types of data always lie at the heart of the user. The SQL standard is also based on the use of the most common basic types, but with a number of specific additions.
Classification of data types
Grouping of data by their type arose long ago andwas 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.
On their basis, a modern classification was developed, including pointers, logical, integer, numerical with floating point and string data types. SQL-the classification fully covers all of the above. However, for some modern DBMS there are additional add-ins. These include Oracle and MySQL.
Basic data types
Used when creating table attributes that conform to the standards of the SQL language, the data types are divided into 4 classes:
- string values;
- fractions;
- integer-valued values;
- date and time values.
String data type
The first group of values allows you to store any data represented as characters.
It can be special characters, numbers andletters, which in their totality will be treated as strings in any SQL query. Data types, the table with the enumeration of which is presented below, form the first group.
CHAR (size) | Used to store lines. The parameter in brackets allows you to fix the length of the stored string. The maximum size in bytes that can be set for a string is 255. |
VARCHAR (size) | Similar to the previous type, it allows you to storestrings of up to 255 characters. 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 consisting of 5 symbols, 6 bytes of memory are required. In the first case, the memory for the value will be selected according to the specified parameter. |
TINY TEXT | Used to store string data up to 255 characters in length. |
TEXT | Used to store text information, the size of which does not exceed 65,535 letters. |
Blob | The data type considered is similar to the TEXT type andallows you to save in the database text information, the volume of which can reach 65,535 characters. But in practice it is used to store sound data, pictures, electronic documentation, etc. |
MEDIUM TEXT | It was developed on the basis of TEXT type, but it allows storing more data due to the increased size to 16 777 215 letters or symbols. |
MEDIUM BLOB | Used to save in the database electronic documents, the size of which does not exceed the mark of 16 777 215 characters. |
LONG TEXT | Functionally similar to the previous types, but with an increased memory capacity of up to 4 gigabytes. |
LONG BLOB | Allows you to put in the database data of large volumes (4,294,967,295 characters). |
ENUM (a, b, c, etc.) | A special data type used forset a list of possible values. Allows you to specify 65535 values. The rows of the type in question can take a single value from the ones specified in the set. In the event that values that are not present in the specified list are added, null values will be written to the table. |
SET | Specifies a set of valid values.Unlike the previous type, it is used to hold 64 parameters, which can be initialized by any or several elements from the specified arguments. |
Table of fractional data types
Fractional SQL data types are used for storagenumbers with a floating point. In practice, as a rule, different financial indicators are set. Depending on the required accuracy, one of the following three is used:
FLOAT (size, d) | Allows to contain fractional numbers of specified accuracy d. |
DOUBLE (size, d) | Used to store fractional numbers with binary precision. |
DECIMAL (size, d) | Storing fractional values in the form of strings. |
For example, in banking calculations, the accuracy of the fractional part reaches a value of 8 or 10 symbols. The first two types can not be used in this area.
Storing financial indicators as stringsgreatly facilitates the solution of many problems. However, when resolving financial issues or performing various SQL operations, converting data types is of great importance. Developers must always take into account the type of storage and processing methods, so that the data will always remain unchanged.
Integer data type
Integers are a single group of numbers that form one of the main classes. Integer SQL data types are based on using the base type INTEGER with some extension of its properties.
INT (size) | Storage of integer values forming the range [-231; 231-1 |
TINYINT (size) | Serves to store numbers in the range from -128 to 127 |
SMALLINT (size) | Characterized by an increased range of stored values in the amount of -32 768 to 32 767 |
MEDIUMINT (size) | Used to store numbers from -223 up to 223-1 |
BIGINT (size) | Covers a range of integer values, starting at -263 and finishing 263-1 |
By selecting the correct data type, you can significantlySave memory and reduce server time costs when the necessary SQL queries are executed. Data types, or rather their range, determine the amount of storage space required.
Therefore, database developers are important to remember,that the use of large ranges for attributes entails an increase in memory costs. It is necessary to clearly analyze the problems to be solved and to identify cases where the approximate range is known and the condition for the use of numbers with a sign is determined. 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 studying the basics of SQL, the date and time data types are of particular interest.
The use of the following types provides additional advantages in the development of systems, the work of which depends on time indicators.
On DATE | The main purpose is to store the date in the formatYEAR-MONTH-DAY ("YYYY-MM-DD" or "uuuu-mm-dd"). Usually the values are separated by "-", however, any character, except digits, can be used as a separator. |
TIME | Allows to enter temporary values in the table cell. All values are specified by 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, counted by the number of seconds that have elapsed since midnight 1.01.1970 and up to the specified value. |
YEAR (M) | Used to store annual values in two- or four-digit format. |
What else is necessary to know?
All these types of data are systematized in detail by Microsoft. SQL data types are developed in more detail.
For example, the firm details how muchBytes are allocated when using each type. Having studied the available information, developers are easier to design the structure of tables and the entire database based on the hardware capabilities of the server.
A special pointer is NULL
Sometimes when the database is full,The situation when, when adding an entry to a table, there is no need to enter information into all columns. For this, a special null pointer is used - NULL, which as an aiduses the SQL language. The column data types, which need not be filled in, are specified when creating tables, with an operator that allows the inclusion of null values. In the other case, the operator NULL with an additional prefix NOT can be used to indicate mandatory completion of all values.
Pointer NULL It does not have a type, but simply points to an empty value in the database tables. Therefore, it can be combined with any of the above types.
