Ms sql server data types. SQL string data type. Data type SQL_VARIANT

Today in the world there are great amount technologies and means intended for storing information. Databases are one of the most common tools. To work with them they are used various systems management. This storage method assumes that all information is clearly structured and entered into special tables. Tables, in turn, consist of attribute columns of a certain type of information.

What is a data type?

Today, there are several definitions that explain the essence of the concept of “data type”. Each of them has one general meaning. Conventionally, a data type can be defined as a group of data characterized by its values ​​- numeric, symbolic, and so on - as well as operations that can be applied to the values ​​in question. The scope of use of various types of data is very multifaceted. Data can be used not only for storing information, but also for programming to solve given problems. When developing programs, the practice of using own types data from a certain set operations. At the core user information There are always basic data types. The SQL standard is based on the most common types of information, but with a number of specific additions.

Data types: classification

Grouping data by type appeared a long time ago. It was caused by the need to structure all volumes of information to ensure ease of processing. Today at the heart of everything existing types There are two basic data types: symbolic and numeric. Based on these data, it was developed modern classification, which includes pointer, integer, boolean, floating point, and string information types. All of the above are fully covered by the SQL classification. However for modern systems there are special add-ons for database management. These include MySQL and Oracle.

Basic Data Types

Data types that are used to create standards-compliant table attributes SQL language, can be divided into 4 classes: string values, fractions, integer values, time and date values.

String type data

The first data type allows you to store any information presented in the form of characters. In this case we're talking about about special characters and letters, which together will be processed in any SQL query like lines. Below are the data types that form the first group:

— CHAR (size) – this type is used to store strings. The parameter specified in brackets allows you to fix the length of the stored string. For a string, you can set the maximum size in bytes to 255.

— VAR CHAR (size) — similar to the previous type, this type allows you to store strings whose length does not exceed 255 characters. The main difference between this type and CHAR is that here the required amount of memory is allocated to store a value of this type. Thus, for a string that consists of 5 characters, 6 bytes of memory will be needed. In the first case, memory for storing the value will be allocated in accordance with the specified parameter.

— TINY TEXT - this type is used to store text information, the volume of which does not exceed 65535 characters.

— BLOB – this type is similar to the TEXT type. It allows you to save in the database text information, which can be up to 65,535 characters. However, in practice, this type can be used to store drawings, audio information, electronic documents and other things.

— MEDIUM TEXT – this type was developed based on the TEXT type. Due to the increased size to 16777215 characters, this type allows you to store more data.

— MEDIU MBLOB – used for storing electronic documents in the database, the size of which does not exceed 16777215 characters.

- LONG TEXT - by functionality similar to previous data types, but has a memory capacity increased to 4 GB.

— LONG BLOB – makes it possible to place large data in the database - 4294967295 characters.

- ENUM is a special data type that is used to specify a list of different values. This type allows you to specify 65535 values. Strings of this type can take only one of all values ​​specified in the set. In the case where values ​​will be added that are not in given list, empty values ​​will be written to the table.

- SET - this data type specifies a set of valid values. Unlike the previous type, it is used to contain 64 parameters that can be initialized by several elements of the given arguments.

Table with fractional data types

The SQL fractional data type is used to store floating point numbers. As a rule, in practice, data of this type characterize various financial indicators. Depending on the accuracy required, you can use one of several types below.

FLOAT (sized) – used to store fractional numbers of specified precision – d;

— DOUBLE (sized) – used to store fractions with binary precision;

— DECIMAL (size, d) – used to store fractional values ​​in the form of strings. It is used in bank calculations, where the accuracy of the fractional part can reach 8 or 10 digits.

The first two types cannot be used in this area. Storing financials in string form can make many decisions much easier. practical problems. When conducting transactions and solving financial issues, SQL data type conversion is of great importance. Developers should definitely consider the type of information storage and processing methods so that the data remains unchanged at all times.

Integer data

A separate group of numbers, forming one of the main classes, are integers. Integer data types are based on the base type INTEGER with some property extension.

• INT (size) – is responsible for storing integer data in the range [-231;231-1].
• TINYINT (size) – used to store numbers in the range from -128 to 127.
• SMALLINT (size) – characterized by a slightly increased range of stored data from -32768 to 32767.
• MEDIUMINT (size) – used to store numbers with dimensions from -223 to 223-1.
• BIGINT (size) – covers the range of integer values ​​from -263 to 263-1.

If you choose the right data type, you can significantly save memory and at the same time reduce the cost of server time when executing the necessary SQL queries. The data types and their range determine the amount of storage required. Developers should be aware that using large ranges for attributes may incur increased memory costs. You should clearly analyze the problems being solved and identify situations in which the approximate range of data 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 would be more correct to use an unsigned type, for which the UNSIGNED attribute is intended.

Time and date data types

Time and date data types are of great interest when studying SQL Basics. Using the types below can provide additional benefits in the case of developing systems whose operation directly depends on time indicators.

• DATE – the main purpose of this type is to store the date in the “year-month-day” format. Values ​​are usually separated by "-". However, sometimes any symbols except numbers can be used as a delimiter.
• TIME – allows you to enter time values ​​into a table cell. Values ​​are specified in the format “hh:mm:ss”.
• DATE TIME – this type combines the functions of the previous two. The data storage format in this case looks like this: “yyyy-mm-dd: hh:mm:ss”.
• TIME STAMP – this type stores the date and time, calculated by the number of seconds that have passed since midnight 01/01/1970 to the specified value.
• YEAR (M) – used to store annual values ​​in two- or four-digit format.

What else should you know? All types of data presented above have been systematized by Microsoft. She also developed SQL data types in more detail. So, for example, the form described how much memory is allocated when using each type of data. After studying all the available information, it should become easier for developers to design the structure of the database and tables based on the hardware capabilities of the server.

NULL- special index

In some cases, when populating a database, a situation arises when, when adding a record to a table, there is no need to enter information into all columns. To do this, you must use a special empty destination pointer or NULL. He is as aid uses SQL language. Column data types that are optional are specified with an enable clause when creating tables. Also the NULL operator when using additional console NOT can be used to indicate mandatory filling all values. A NULL pointer has no type. It simply points to an empty value in the database tables. For this reason, it may well be combined with one of the data types presented above.

SQL Data Types are divided into three groups:
— string;
— floating point (fractional numbers);
— whole numbers, date And time.

1. SQL Data Types string
   

   SQL Data Types	Description
   CHAR(size)	Fixed length strings (can contain letters, numbers, and Special symbols). The fixed size is indicated in parentheses. Up to 255 characters can be recorded
   VARCHAR(size)
   TINYTEXT	Can store a maximum of 255 characters.
   TEXT
   BLOB	Can store a maximum of 65,535 characters.
   MEDIUMTEXT
   MEDIUMBLOB	Can store a maximum of 16,777,215 characters.
   LONGTEXT
   LONGBLOB	Can store a maximum of 4,294,967,295 characters.
   ENUM(x,y,z,etc.)	Allows you to enter a list of valid values. You can enter up to 65535 values ​​in SQL Data Type ENUM list. If, when inserting, the value will not be present in the list ENUM, then we will get an empty value. You can enter possible values ​​in the following format: ENUM ("X", "Y", "Z")
   SET	SQL Data Type SET reminds ENUM except that SET can contain up to 64 values.

2. SQL Data Types floating point (fractional numbers) and integers
   

   SQL Data Types	Description
   TINYINT(size)	Can store numbers from -128 to 127
   SMALLINT(size)	Range -32,768 to 32,767
   MEDIUMINT(size)	Range -8,388,608 to 8,388,607
   INT(size)	Range -2,147,483,648 to 2,147,483,647
   BIGINT(size)	Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
   FLOAT(size,d)	Low precision floating point number.
   DOUBLE(size,d)	Double precision floating point number.
   DECIMAL(size,d)	A fractional number stored as a string.

3. SQL Data Types- Date and time
   

MySQL Data Types

MySQL Data Types are divided into the following types:

• Numerical data types
  

  Data types	Byte	From	Before
  TINYINT	1	-128	127
  SMALLINT	2	-32768	32767
  MEDIUMINT	3	-8388608	8388607
  INT	4	-2147483648	2147483647
  BIGINT	8	-9223372036854775808	9223372036854775807

• Data types date and time
  
• Character Data types
  

Oracle Data Types

Types Oracle data

• CHAR– fixed text strings up to 2000 bytes. Type value CHAR padded to the specified length with spaces.
• VARCHAR 2— text strings of variable length up to 4000 bytes.
• NUMBER— numerical data.
• DECIMAL- numerical data
• DATE- used to store dates.
• RAW- used to store binary data up to 2000 bytes.
• LONG- used to store text data up to 2 GB in length
• LONG RAW- used to store binary data up to 2 GB
• ROWID- used to store identifiers ROWID Database Oracle V special format(addresses of table rows).
• BLOB- Up to 4 GB of binary data is saved. Data of this type is stored outside the table, but in the table Oracle there are only pointers to objects
• CLOB, NCLOB— up to 4 GB of text data is saved. NCLOB is the data type NLS large fixed length ( NLS means National Language Set– a set for national languages ​​– and is used to work in Oracle in languages ​​other than English. In English, 1 byte is needed to store one character, and in some languages ​​of the world with large character sets (Japanese, Chinese, Korean), languages ​​where the text is read from right to left (Arabic, Hebrew) several bytes are required to store one character). Data of this type is stored outside the table, and the table contains only pointers to objects.
• BFILE- up to 4 GB of unstructured data is saved, and in files operating system(external files).

ANSI SQL the standard recognizes only text and numbers, while most commercial programs others use special types, such as DATE And TIME- almost in fact standard types. Some packages also support types such as MONEY And BINARY. Data types, recognized by ANSI, consist of strings of characters and various types of numbers, which can be classified as accurate numbers and approximate numbers.

CHARACTER(length) defines a specification of character strings, where length specifies the length of strings of a given type. Values ​​of this type must be enclosed in single quotes. Most implementations support variable length strings for data types VARCHAR And LONG VARCHAR(or simply LONG).

While a field like CHAR can always allocate memory for the maximum number of characters that can be stored in a field, field VARCHAR given any number of characters can only allocate a certain amount of memory to store the actual contents of the field, although SQL can install some extra space memory to keep track of the current field length. Field VARCHAR can be of any length, including an implementation-defined maximum. This maximum can vary from 254 to 2048 characters for VARCHAR and up to 16,000 characters for LONG. LONG typically used for text of an explanatory nature or for data that cannot be easily compressed into simple field values; VARCHAR can be used for any text string, whose length may vary.

Extracting and modifying fields VARCHAR- more complex, and therefore slower process, than extracting and modifying fields CHAR. In addition, some memory VARCHAR remains always unused to ensure that the entire length of the string is contained. When using such types, it should be possible for fields to be combined with other fields.

Exact numeric types are numbers, with or without a decimal point, that can be represented as [+|-]<целое без знака>[.<целое без знака>] and be specified as:

DECIMAL(precision [, scale])- the size argument has two parts: precision and scale. Scale cannot exceed precision. Precision indicates how many significant digits a number has. The scale specifies the maximum number of digits to the right of the decimal point. Scale = zero makes the field equivalent to an integer.

NUMERIC(precision [, scale])- same as DECIMAL except that the maximum decimal cannot exceed the precision argument

INTEGER- a number without a decimal point. Equivalent DECIMAL, but without the numbers to the right of the decimal point, i.e. with scale equal to 0. The size argument is not used (it is automatically set to an implementation-dependent value).

SMALLINT- same as INTEGER, except that, depending on the implementation, the default size may (or may not) be less than INTEGER.

Approximate Numeric Types are numbers in exponential (base 10) notation, represented as <литеральное значение точного числа>E<целое со знаком> and specified as follows:

FLOAT[(precision)]- floating point number. The size argument consists of a single number that specifies the minimum precision.

REAL- same as FLOAT except that no size argument is used. The precision is set to implementation-dependent by default.

DOUBLE PRECISION- same as REAL, except that the implementation-defined accuracy for DOUBLE PRECISION must exceed implementation-defined accuracy REAL.

Access data types

Types Access data are divided into the following groups:

• Text– maximum 255 bytes.
• Memo - up to 64000 bytes.
• Numerical— 1,2,4 or 8 bytes. For a numeric type, the field size may be as follows:
  • byte— integers from -0 to 255, occupies 1 byte when stored
  • whole— integers from -32768 to 32767, takes 2 bytes
  • long integer— integers from -2147483648 to 2147483647, takes 4 bytes
  • floating point— numbers accurate to 6 digits from –3.4*1038 to 3.4*1038, takes 4 bytes
  • floating point— numbers with precision from –1.797*10308 to 1.797*10308, takes 8 bytes
• Date Time— 8 bytes
• Monetary- 8 bytes, data on monetary amounts, stored with 4 decimal places.
• Counter is a unique long integer generated by Access when each new entry- 4 bytes.
• Logical— logical data 1 bit.
• OLE Object Field– up to 1 gigabyte, pictures, diagrams and other objects OLE from applications Windows. Objects OLE may be linked or embedded.
• Hyperlinks— a field in which hyperlinks are stored. A hyperlink can be either type UNC (standard format to indicate the path with inclusion network server files), or URL (address of an object, document, page or other type of object on the Internet or Intranet. The URL determines the protocol for access and the final address).
• Substitution Wizard— a field that allows you to select a value from another table Accesses or from a list of values ​​using a combo box. Most often used for key fields. Has the same size as the primary key, which is also a lookup field, usually 4 bytes. ( Primary key– one or more fields, the combination of values ​​of which uniquely identifies each record in the table Accesses. Does not allow undefined.Null. values ​​must always have a unique index. Serves to link a table with secondary keys of other tables).

SQL Server Data Types

Microsoft SQL Server SQL 2003. Also SQL Server supports additional data types, used to uniquely identify rows of data in a table and across multiple servers, such as UNIQUEIDENTIFIER , which is consistent with Microsoft's "breadth-first" hardware philosophy (i.e., deploying a database across multiple servers on Intel platforms), instead of “growing in height” (i.e. implementation on one huge powerful UNIX server or Windows Data Center Server).

Data types used in SQL Server:

• BIGINT( data type SQL2003: B1GINT)
  Stores signed and unsigned integers in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Occupies 8 bytes. See the INT type for IDENTITY property rules that also apply to the BIGINT type.
• BINARY[(n)] ( data type SQL2003: BLOB)
  Stores a fixed-length binary value from 1 to 8000 bytes. A BINARY value takes up n + 4 bytes.
• BIT ( data type SQL2003: BOOLEAN)
  Stores the values ​​1, 0, or NULL, which stands for "unknown". One byte can store up to 8 values ​​from columns of the BIT table type. One more byte can accommodate an additional 8 BIT values. BIT type columns cannot be indexed.
• CHAR[(n)] , CHARACTER[(n)] ( data type SQL2003: CHARACTER[(n)])
  Stores fixed-length character data from 1 to 8000 characters. Any unused space is filled with spaces by default. (Automatic padding can be disabled.) The type takes up n bytes.
• CURSOR ( data type SQL2003: missing)
  A special data type used to describe a cursor in the form of a variable or stored procedure parameter OUTPUT. The type cannot be used in a CREATE TABLE statement. The CURSOR type can be NULL.
• DATETIME (SQL2003 data type: TIMESTAMP)
  Stores a date and time value in the range from 01-01-1753 00:00:00 to 31-12-9999 23:59:59. Storage requires 8 bytes.
• DECIMAL (p. s) , DEC (p. s) , NUMERIC (p, s) ( data type SQL2003: DECIMAL (p, s) , NUMERIC (p. s))
  Stores decimals up to 38 digits long. The p and s values ​​determine the precision and scale, respectively. The default scale is 0. The space the value takes up is determined by the precision used.
  For precision 1-9, 5 bytes are used.
  For precision 10-19, 9 bytes are used.
  With a precision of 20-28, 13 bytes are used.
  With precision 29-39, 17 bytes are used.
  See the INT type for IDENTITY property rules that also apply to the DECIMAL type.
• DOUBLE PRECISION ( data type SQL2003: missing)
  Synonym for FLOAT(53) .
• FLOAT[(n)] ( data type SQL2003: FLOAT, FLOAT(n))
  Stores floating point values ​​in the range -1.79E + 308 to 1.79E + 308. The precision, determined by the and parameter, can vary from 1 to 53. To store 7 digits (n - from 1 to 24) requires 4 bytes. Values ​​greater than 7 digits occupy 8 bytes.
• IMAGE ( data type SQL2003: BLOB)
  Stores a binary value of variable length up to 2,147,483,647 bytes. This data type is often used to store graphics, sound and files such as MS Word documents and spreadsheets MS Excel. Values ​​of type IMAGE cannot be freely manipulated. IMAGE and TEXT columns have many restrictions on how they can be used. See the description of the TEXT type for a list of commands and functions that also apply to the IMAGE type.
• INT (data type SQL2003: INTEGER)
  Stores signed or unsigned integers in the range -2,147,483,648 to 2,147,483,647. Occupies 4 bytes. All integer data types, as well as types that store decimal fractions, support the IDENTITY property; identity is an automatically incremented row identifier. Refer to the “CREATE/ALTER TABLE Statement” section in Chapter 3.
• MONEY (data type SQL2003: missing)
  Stores currency values ​​in the range -922337203685477.5808 to 922337203685477.5807. The value takes 8 bytes.
• NCHAR(n) , NATIONAL CHAR(n) , NATIONAL CHARACTER(n) ( data type SQL2003: NATIONAL CHARACTER(n))
  Stores fixed-length UNICODE data of up to 4000 characters. Storage requires n*2 bytes.
• NTEXT, NATIONAL TEXT ( data type SQL2003:NCLOB)
  Stores text fragments in UNICODE format up to 1,073,741,823 characters long. See the description of the TEXT type for a list of commands and functions that also apply to the NTEXT type.
• NUMERIC(p, s) ( data type SQL2003: DECIMAL (p, s))
  Synonym for DECIMAL type. See the description of the INT type for rules related to the IDENTITY property.
• NVARCHAR(n) , NATIONAL CHAR VARYING(n) , NATIONAL CHARACTER VARYING(n) ( data type SQL2003: NATIONAL CHARACTER VARYING(n))
  Stores UNICODE data of variable length up to 4000 characters.
  The space taken up is calculated as twice the length of all characters inserted in the field (number of characters * 2).
  In SQL Server system parameter SET ANSI_PADDINGX for NCHAR and NVARCHAR fields is always ON.
• REAL , FLOAT(24) ( data type SQL2003: REAL)
  Stores floating point values ​​in the range -3.40E+38 to 3.40E+38. Occupies 4 bytes. The REAL type is functionally equivalent to the FLOAT(24) type.
• ROWVERSION ( data type SQL2003: missing)
  A unique number stored in a database that is updated whenever a row is updated. More earlier versions called TIMESTAMP.
• SMALLDATETIME( data type SQL2003: missing)
  Stores date and time in the range from '01-01-1900 00:00' to '06-06-2079 23:59' with minute precision. (Minutes are rounded down if the seconds value is 29.998 or less, otherwise they are rounded up.) The value occupies 4 bytes.
• SMALLINT ( data type SQL2003: SMALLINT)
  Stores signed or unsigned integers in the range -32,768 to 32,767. Occupies 2 bytes. See the description of the INT type for rules related to the IDENTITY property that also apply to this type.
• SMALLMONEY ( data type SQL2003: missing)
  Stores currency values ​​in the range from 214748.3648 to -214748.3647. The values ​​occupy 4 bytes.
• SQLVARIANT( data type SQL2003: missing)
  Stores values ​​related to other data types supported by SQL Server, excluding TEXT, NTEXT, ROWVERSION, and other SQL VARIANT values. Can store up to 8016 bytes of data, supported NULL values and DEFAULT. The SQL VARIANT type is used in columns, parameters, variables, and return values ​​of functions and stored procedures.
• TABLE ( data type SQL2003: missing)
  A special type that stores the resulting data set as a result of the last process. Used exclusively for procedural processing and cannot be used in CREATE TABLE statements. This data type reduces the need to create temporary tables in many applications. Can reduce the need for procedure recompilations, thereby speeding up the execution of stored procedures and user-defined functions.
• TEXT ( data type SQL2003: CLOB)
  Stores very large chunks of text up to 2,147,483,647 characters long. TECHNIC IMAGE values ​​are often much more difficult to manipulate than, say, VARCHAR values. For example, you cannot create an index on a column of type TEXT or IMAGE. TEXT values ​​can be manipulated using the DATALENGTH, PATINDEX, SUBSTRING, TEXTPTR, and TEXTVALID functions, as well as the READTEXT, SET TEXTSIZE, UPDATETEXT, and WRITETEXT commands.
• TIMESTAMP ( data type SQL2003: TIMESTAMP)
  Stores automatically generated binary number, which ensures uniqueness in the current database and therefore differs from the ANSI standard TIMESTAMP data type. The TIMESTAMP type takes up 8 bytes. Currently, instead of TIMESTAMP, it is better to use ROWVERSION values ​​to uniquely identify rows.
• TINYINT
  Stores unsigned integers in the range 0 to 255 and occupies 1 byte. See the description of the INT type for rules related to the IDENTITY property that also apply to this type.
• UNIQUEIDENTIFIER ( data type SQL2003: missing)
  Represents a value that is unique across all databases and all servers. Represented in the form xxxxxxxxx-xxxx-xxxx-xxxxx-xxxxxxxxxxxxxxxx, in which each "x" represents hexadecimal number in the range 0-9 or a - f. The only operations that can be performed on values ​​of this type are comparison and testing for NULL. Columns of this type can use constraints and properties, with the exception of the IDENTITY property.
• VARBINARY[(n)] ( data type SQL2003: BLOB)
  Represents a variable-length binary value, up to 8000 bytes. The space occupied is the size of the inserted data plus 4 bytes.
• VARCHARf(n)] , CHAR VARYING [(n)] , CHARACTER VARYING [(n)] ( data type SQL2003: CHARACTER VARYING (n))
  Stores fixed-length character data ranging in size from 1 to 8000 characters. The space taken up is the actual size of the entered value in bytes, not the value n.

PostgreSQL Data Types

Database PostgreSQL supports most data types SQL2003 plus a huge set of types for storing spatial and geometric data. PostgreSQL boasts a rich set of operators and functions specifically designed for geometric data types. This includes features such as rotation, intersection search, and scaling. IN PostgreSQL there is also support additional versions existing data types that are characterized by the fact that they take up less disk space than the corresponding original versions. For example, in PostgreSQL Several variants of the INTEGER type are offered for storing large and small numbers, respectively taking up more or less space.

• BJGSERJAL
• BIT ( data type SQL2003: BIT)
  Fixed length bit string.
• BIT VARYING(n) varbit(n) ( data type SQL2003: BIT VARYING)
  Denotes a variable length bit string of n bits.
• BOOL , BOOLEAN ( data type SQL2003: BOOLEAN)
  Stores a Boolean value (true/false/unknown). The recommended values ​​are the TRUE and FALSE keywords, although PostgreSQL allows multiple literal values ​​for "true": TRUE, t, true, y, yes, and 1. Valid values ​​for "false" are: FALSE, f, false, n, no, and 0.
• BOX ((xl, y I), (x2, y2)) ( data type SQL2003: missing)
  Stores values ​​that define a rectangular area on a plane. The values ​​occupy 32 bytes and are presented in the form ((xl, yl), (x2, y2)), which corresponds to the opposite corners of the rectangle (upper right and lower left, respectively). The outer parentheses are optional.
• BYTEA ( data type SQL2003: BINARY LARGE OBJECT)
  Raw, binary data used, for example, to store graphics, sound, and documents. This type of storage requires 4 bytes plus the actual size of the bit string.
• CHAR(n) , CHARA CTER(n) ( data type SQL2003: CHARACTER(n))
  Contains a fixed-length character string padded with spaces up to length n. Attempting to insert a value longer than n results in an error (unless the extra characters are spaces, in which case they are trimmed to be n characters long).
• CIDR(x.x.x.xZy) ( data type SQL2003: missing)
  Describes a network or host address in IP protocol version 4 format. The address occupies 12 bytes. Valid values are any allowed by the IPv4 protocol network addresses. In the CIDR type, the data is represented in the form x.x.x.x/y, where x.x.x.x is the IP address and y is the number of netmask bits. CIDR does not allow non-zero bits to the right of the zero bit of the netmask.
• CIRCLE x, y, r (SQL2003 data type: missing)
  Describes a circle on a plane. The values ​​occupy!’ 24 bytes and are presented in the form x, y, r. The values ​​* and y represent the coordinates of the circle's center, and r is the length of its radius. The values ​​for x, y, and r can be delimited by parentheses or curly braces if desired.
• DATE (SQL2003 data type: DATE)
  Stores a calendar date (year, day and month) without time of day. Occupies 4 bytes. Dates must be in the range 4713 BC. up to 32767 and. e. Resolution limit for type DATE, of course, one day.
• DATETIME (SQL2003 data type: T1MESTAMP)
  Stores a calendar date indicating the time of day.
• DECIMAL [(p, s)], NUMERIC [(p. s)] (SQL2003 data type: DECIMAL (PRECISION SCALE), NUMERIC (x, p))
  Stores accurate numeric values with precision (p) equal to 9 and scale (s), equal to zero, no upper limit.
• FLOAT4, REAL (SQL2003 data type: FLOAT(p))
  Stores floating-point values ​​with a precision of 8 or less and 6 decimal places.
• FLOAT8, DOUBLE PRECISION (SQL2003 data type: FLOAT(p), 7
• INET (x.x.x.x/y)
  Stores a network or host address in IP protocol version 4 format. The address takes 12 bytes. Valid values ​​are any network addresses allowed by the IPv4 protocol, x.x.x.x is the IP address, y is the number of network mask bits. The default netmask is 32. Unlike CIDR, INET allows non-zero bits to the right of the netmask.
• SMALLINT (SQL2003 data type: SMALLINT)
  Stores two-byte signed and unsigned integers in the range -32,768 to 32,767. Synonym: INT72.
• INTEGER (SQL2003 data type: INTEGER)
  Stores 4-byte signed or unsigned integers in the range -2,147,483,648 to 2,147,483,647. Synonym: 1NT4.
• INT8 (SQL2003 data type: missing)
  Stores 8-byte signed or unsigned integers in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
• INTERVAL(p) (SQL2003 data type: missing)
  Stores commonly used time interval values ​​in the range -178,000,000 to 178,000,000 years. Occupies 12 bytes. The most low resolution type INTERVAL is a microsecond. This type of date storage differs from the ANSI standard, which requires an interval specifier, such as INTERVAL YEAR TO MONTH.
• LINE ((xl, y I), (x2, y2)) (SQL2003 data type: missing)
  Stores information about a straight line on a plane, without endpoints. The values ​​occupy 32 bytes and are represented in the form ((xl, yl), (x2, y2)), which indicates the starting and ending point of the line. The parentheses in LINE type syntax are optional.
• LSEG ((xl, yl), (x2, y2)) (SQL2003 data type: missing)
  Stores a line segment (LSEG) on a plane, with endpoints. The values ​​occupy 32 bytes and are represented in the form ((xl, yl), (x2, y2)). The parentheses in the LSEG type syntax are optional. For those interested, a “segment” is what most people commonly call a line. For example, the lines on the playing field are actually segments.
• MACADDR (SQL2003 data type: none)
  Can store MAC address value network card computer. Occupies 6 bytes.
  The MACADDR type allows several forms of address corresponding to industrial
  standards, for example:
  08002В:010203
  08002В-010203
  0800.2В01.0203
  08-00-2В-01-02-03
  08:00:2B:01:02:03
• MONEY, DECIMAL(9,2) (SQL2003 data type: none)
  Stores currency values ​​in US format in the range -21474836.48 to 21474836.47.
• NUMERIC [(p, s)], DECIMAL[(p, s)] (SQL2003 data type: none)
  Stores precise numeric data with precision (p) and scale (s).
• OID (SQL2003 data type: missing)
  Stores unique identifiers objects.
• PATH ((xl, y), ... n), Path ((xl, yl), ... n) (SQL2003 data type: missing)
  Describes an open or closed geometric contour on a plane. The values ​​are presented in the form ((xl, yl), ... n) and occupy 4 + 32 bytes. Each pair of values ​​(x, y) corresponds to a contour point. Contours are either open, when the first and last points do not coincide, or closed, when the first and last points are the same. To indicate closed contours, the expression is enclosed in parentheses, and to indicate open contours, it is enclosed in square brackets.
• POINT(x,y) (SQL2003 data type: none)
  Stores a value describing a geometric point on a plane. Occupies 16 bytes. Values ​​are presented as (x, y). Point is the basis of other two-dimensional spatial data types supported in PostgreSQL. The parentheses in this type of syntax are optional.
• POLYGONE ((x1,y1), ... n) (SQL2003 data type: none)
  Stores a value describing a closed geometric contour on a plane. Occupies 4 + 32n bytes. The values ​​are presented in the form ((xl, yl), ... n). The POLYGONE type is essentially equivalent to the type used to store a closed path.
• SERIAL, SER1AL4 (SQL2003 data type: none)
  Stores an automatically incremented, unique integer ID value used for indexing and cross-referencing. This type stores up to 4 bytes of data (number range 1 to 2,147,483,647). Tables defined with this data type cannot be dropped directly. First you need to issue the DROP SEQUENCE command, and only after that issue the DROP TABLE command.
• SER1AL8, BIGSERIAL (SQL2003 data type: missing)
  Stores an automatically incrementing, unique integer ID value used for indexing and cross-referencing. This type stores up to 8 bytes of data (number range from 1 to 9,223,372,036,854,775,807). Tables defined with this data type cannot be dropped directly. First you need to issue the DROP SEQUENCE command, and only after that issue the DROP TABLE command.
• TEXT (SQL2003 data type: CLOB)
  Stores a large array of character strings of variable length up to 1 gigabyte. PostgreSQL automatically compresses TEXT strings, so disk space can be smaller than the size of the strings.
• TIME [(p)] (SQL2003 data type: TIME) Stores the time of day either without time zone (uses 8 bytes) or with the time zone in which the database server is located (uses 12 bytes). Valid range of values: 00:00:00.00 - 23:59:59.99. The smallest value is 1 microsecond. Please note that in most UNIX systems Time zone information is only available for dates from 1902 to 2038.
• TIMESPAN (SQL2003 data type: missing)
  Stores a value that represents a specific period of time. The most similar type to TIMESPAN in the ANSI standard is the INTERVAL type.
• TIMESTAMP [(p)] ( data type SQL2003: TIMESTAMP)
  Stored date and time with and without the time zone of the database server. The acceptable range of values ​​is from 4713 BC. e. up to N1,465,001 e. One TIMESTAMP value takes 8 bytes. The smallest value is 1 microsecond. Note that on most UNIX systems, time zone information is only available for dates from 1902 to 2038.
• TIMETZ ( data type SQL2003: TIME WITH TIMEZONE)
  Stores the time of day value, taking into account the time zone.
• VARCHAR(n) , CHARACTER VARYLNG(n) ( data type SQL2003: CHARACTER VARYING(n))
  Stores variable-length character strings up to n in length. Trailing spaces are not stored.

The data type determines what value the column can contain: integer data, character data, currency data, date and time data, binary string data, etc.

SQL Data Types

Each column in a database table must have a name and a data type.

The SQL developer must decide what type of data will be stored inside each column when creating the table. The data type is a guide for SQL to understand what type of data is expected within each column, and also determines how SQL will interact with the stored data.

List of MySQL data types

There are three main data types in MySQL: text, number and date.

Text Data Types

Numeric Data Types

UNSIGNED - additional parameter integer type. Typically an integer goes from negative to positive. Adding the UNSIGNED attribute will move this range up so that it starts at zero instead of a negative number.

In this publication we will look at data types in SQL. I would like to draw your attention to the fact that different DBMSs support different sets of data types, so this publication includes the most common data types in the SQL implementations of various DBMSs.

It is important to know that in order to design databases correctly and competently, the correctly selected data type in SQL can greatly facilitate the work of other developers. Therefore, I do not advise you to skip the section on data types and always, when you get acquainted with a new one, pay attention to the data types that the program supports and how it processes these data types.

Let's look at the data types that exist in the SQL language. Each table column (attribute) must have a data type for the values ​​that are stored in the column. The data type for a column is determined when the table is created, or better yet, when it is determined during the .

The SQL language divides data into five types:

1. SQL integer data type.
2. SQL real data type.
3. SQL date and time data types.
4. SQL string data type.
5. Unicode SQL string data type.

Let's see what each includes SQL data types and what values ​​they allow us to store.

Integer data typeSQL

Integer data type in SQL quite broad and depends on the implementation of the DBMS, below in the tables are just some typical examples of the SQL integer data type.

Once again I repeat that the set of integers data types in SQL depends entirely on the DBMS.

Real data typeSQL

SQL real data type, like integer type data depends entirely on the implementation of the DBMS.

String data typeSQL

There can be many more string data types in SQL; see the specification of your DBMS to find out what data type can be used for string values.

Unicode SQL string data type

Yes, some DBMSs allocate a string data type in Unicode encoding, since in Unicode encoded strings at least 8 bytes are spent on writing one character, which is why we allocate string data type in SQL with unicode encoding.

Binary data type inSQL

Binary data type in SQL used to store information in binary form(as a sequence of bytes).

I would like to draw your attention to the fact that each DBMS supports its own set of data types and dimensions; we looked at the basic data types of the SQL language, in next topic We'll look at the data types that SQLite3 supports.

Signed and unsigned data types in SQL

I would like to draw your attention to the fact that in some DBMSs, numeric data types can be divided into signed and unsigned data types. And it's perfect different types data.

For example, if you store id INTEGER UNSIGNED in one table, and id_table1 INTEGER SIGNED in another, then you will not be able to implement these two columns, since they have different data types. Be careful and always read data type descriptions when you start using a new DBMS.

Let's give an example practical application SIGNED and UNSIGNED data types. For example, MySQL has the TINYINT data type, which takes up one byte and is great for storing age, so a TINYINT column can store integers from 0 to 256 (two to the eighth power, one byte has 8 bits, and one bit can take two values: zero or one), if the column is unsigned, if the column is signed, then numbers from -128 to 127 can be written into it (one bit will go per sign).

SQL data types.

1. Literals

Literals are the simplest expressions that are values ​​of the corresponding types. Each data type has a way of representing literals of its type. SQL has the following literals:

character strings (example: "Character string"),

binary strings (example: X"1AFFD561"),

numbers (examples: 27 -863 0.173 .8582 -9572.5619 11.54E7 .94E194 93E-12 -29.629E27),

temporary (DATE "2005-12-07" TIME "HH:MI:SS" TIMESTAMP "YYYY-MM-DD HH:MI:SS"),

logical (TRUE, FALSE, UNKNOWN).

1. Operators and Expressions

Operators are language constructs that specify operations on data elements and return a new value as a result. The data elements used in statements are called operands or arguments. Operators are represented as special characters or keywords. For example, the multiplication operator is represented by an asterisk (*), and the null operator is represented by the IS NULL keyword.

There are two main types of operators. Unaries have only one operand and are usually represented in the following format:

operand operator

Binary operate on two operands and are represented as follows:

operand operator operand

In addition, there are special operators that use more than two operands. The type of an operator is determined by the type of arguments used and the type of result returned. The following types of operators are available:

string (||);

arithmetic;

brain teaser;

comparison predicates;

special predicates;

operators on sets (tables).