Computers, Database
SQL (data types): table
In today's world, there are a large number of tools and technologies designed to store information. One of the most common methods are databases, for the work with which various control systems are used. This method of storage assumes that all data is clearly structured and entered in special tables. They, in turn, consist of columns-attributes of a certain data type.
The data type is 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 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 and was caused by the need for structuring 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, numeric with a floating point and string data types. SQL 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 meet 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 and letters, 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 strings. 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, you can store 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 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. |
TEXT | Used to store text information, the size of which does not exceed 65,535 letters. |
Blob | This type of data is similar to the TEXT type and allows you to save textual information in the database, the volume of which can reach 65,535 characters. But in practice it is used for storing sound data, drawings, electronic documentation, etc. |
MEDIUM TEXT | It was developed on the basis of the 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 to specify a list of possible values. Lets you specify 65535 values. The rows of the type considered 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 contain 64 parameters, which can be initialized by any or several elements from the specified arguments. |
Fractional Data Type Table
Fractional SQL data types are used to store floating-point numbers. 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 precision of the fractional part reaches a value of 8 or 10 characters. The first two types can not be used in this area.
Storing financial indicators in the form of lines greatly 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 the use of the base type INTEGER with some extension of its properties.
INT (size) | Storage of integer values forming the range [-2 31 ; 2 31 -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 ranging in size from -2 2 3 to 2 2 3 -1 |
BIGINT (size) | It covers a range of integer values, starting with -2 63 and ending with 2 63 -1 |
By selecting the correct data type, you can save a lot of 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, it is important for database developers 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 whose work depends on time scales.
DATE | The main purpose is to store the date in the format YEAR-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 you 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 yearly 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 much memory in bytes is 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 populating a database , a situation arises 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 uses the SQL language as an aid. The column data types, which do not need to be filled in, are specified when creating the tables with an operator that allows the inclusion of null values. In the other case, the NULL operator With an additional prefix NOT can be used to specify the mandatory filling of all values.
The NULL pointer 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.
Similar articles
Trending Now