Internet

Data type percentage in sql. Standard SQL Data Types

Names.

Operators

Basic concepts of SQL

Every SQL statement starts with a verb, i.e. keyword A that describes the action performed by the operator. Typical verbs are SELECT (select), CREATE (create), INSERT (add), DELETE (delete), COMMIT (complete). The verb is followed by one or more sentences. The sentence describes the data that the operator works with or contains clarifying information about the action performed by the operator. Each clause also begins with a keyword such as WHERE (where), FROM (from where), INTO (where to), and HAVING (having). Some sentences in the operator are mandatory, while others are not. The specific structure and content of the offer may change. Many sentences contain table or column names; some of them may contain additional keywords, constants and expressions.

The ANSI/ISO standard defines keywords that are used as verbs and in operator sentences. According to the standard, these keywords cannot be used to name database objects such as tables, columns, and users.

Each object in the database has a unique name. Names are used in SQL statements and indicate on which database object the operator should perform the action. The ANSI/ISO standard defines that tables, columns, and users have names. Many implementations of SQL also support additional named objects such as stored procedures, named primary key-foreign key relationships, and data entry forms.

According to the ANSI/ISO standard, SQL names must be between 1 and 18 characters long, start with a letter, and not contain spaces or special punctuation characters. In the SQL2 standard, the maximum number of characters in a name has been increased to 128.

The full table name consists of the name of the owner of the table and its own name, separated by a dot (.). For example, the fully qualified name of the Students table, owned by a user named Admin, is as follows:

If a column name is specified in a statement, SQL determines which of the tables specified in the same statement contains given column. However, if you want to include two columns from different tables but with the same name in the statement, you must specify fully qualified column names that uniquely identify their location. A fully qualified column name consists of the name of the table containing the column and the column name (simple name) separated by a dot (.). For example, the fully qualified name of the StName column from the Students table is: Students.StName

The ANSI/ISO standard defines data types that can be used to represent information in relational database data. The data types available in the SQL1 standard are only a minimal set and are supported in all commercial DBMS. The following are the data types defined in the SQL1 and SQL2 standards:

CHAR (length), CHARACTER (length) Fixed length character strings

VARCHAR(length), CHAR VARYING(length), CHARACTER VARYING(length) Variable length character strings*

NCHAP(length), NATIONAL CHAR(length), NATIONAL CHARACTER(length) Constant length localized character strings*

NCHAR VARYING(length), NATIONAL CHAR VARYING(length),

NATIONAL CHARACTER VARYING(length) Variable length strings of localized characters*

INTEGER, INT Integers

SMALLINT Small integers

BIT(length) Bit strings of constant length*

BIT VARYNG(length) Variable length bit strings*

NUMERIC(precision, degree) , DECIMAL(precision, degree),

DEC(precision, degree) Scalable integer (decimal) numbers

FLOAT(precision) Floating point numbers

REAL Low precision floating point numbers

DOUBLE PRECISION Floating point numbers high precision

DATE Calendar date*

TIME(precision) Time

TIME STAMP(accuracy) Date and time*

INTERVAL Time interval*

SQL1 uses the following data types:

1. Character strings of constant length. Columns that have this data type typically store names of people and companies, addresses, descriptions, and so on.

2. Whole numbers. Columns that have this data type typically store data about accounts, quantities, ages, and so on. Integer columns are often also used to store identifiers, such as a customer, employee, or order ID.

3. Scalable integers. Columns of this type store numbers that have fractional part that need to be calculated accurately, such as exchange rates and percentages. In addition, such columns often store) monetary values.

4. Numbers with a floating point. Columns of this type are used to store values that can be approximated, such as weights and distances. Floating point numbers can represent a larger range of values than decimal numbers, but calculations can introduce rounding errors.

In addition to the data types defined in the SQL1 standard, most commercial DBMSs have many additional data types, most of which are included in the SQL2 standard. The most important ones are listed below:

1. Character strings of variable length. Almost all DBMSs support the VARCHAR data type, which allows you to store character strings whose length varies within a certain range. The SQL1 standard defined strings of constant length, which are right-padded with spaces.

2. Monetary values. Many DBMSs support the MONEY or CURRENCY data type, which is usually stored as a decimal or floating point number. The presence of a separate data type for representing monetary values allows them to be formatted correctly when displayed on the screen.

3. Date and time. Support for date/time values is also widespread in various DBMSs, although the way it is implemented varies quite a bit. As a rule, various operations can be performed on the values of this data type. The SQL2 standard includes the definition of the DATE, TIME, TIMESTAMP, and INTERVAL data types, including support for time zones and the ability to specify precision, time representations (such as tenths or hundredths of a second).

4. Boolean data. Some DBMS explicitly support boolean values (TRUE or FALSE).

Literature: I.F. Astakhova, A.P. Tolstobrov, V.M. Melnikov SQL in examples and tasks. Tutorial: New knowledge, 2002

Today in the world there are a huge number of technologies and tools designed to store information. Databases are one of the most common means. Various control systems are used to work with them. This method of storage 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 at once that explain the essence of the concept of "data type". Each of them has one common meaning. By convention, a data type can be defined as a group of data characterized by its values—numeric, character, and so on—as well as the operations that can be applied to the values in question. Scope of use various types data is very versatile. Data can be used not only to store information, but also in programming to solve problems. When developing programs, the practice of using native types data with a specific set of operations. At the core user information always lie the underlying 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 the volumes of information to ensure the convenience of their processing. Today, all existing data types are based on two basic ones: character and numeric. Based on these data, a modern classification was developed, which includes pointers, integer, logical, floating point and string information types. All of the above is fully covered by the SQL classification. However, for modern systems database management there are special add-ons. These include MySQL and Oracle.

Basic data types

The data types that are used when creating table attributes that meet SQL language standards can be divided into 4 classes: string values, fractions, integer values, time and date values.

String data type

The first data type allows you to store any information represented as characters. In this case, we are talking about special characters and letters, which together will be processed in any SQL query like strings. 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 line, you can set maximum size in bytes - 255.

- VAR CHAR (size) - by analogy with 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 the required amount of memory is allocated here 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, the memory for storing the value will be allocated according to the specified parameter.

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

- BLOB - this type is similar to the TEXT type. It allows you to save textual information in the database, the volume of which can be up to 65,535 characters. However, in practice, this type can be used to store drawings, sound information, electronic documents and others.

- MEDIUM TEXT - this type was developed on the basis of the TEXT type. Due to the size increased to 16777215 characters, this type allows you to store more data.

- MEDIU MBLOB - used to store electronic documents in the database, the size of which does not exceed 16777215 characters.

- LONG TEXT - by functionality similar to the 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 define a list of different values. This type allows you to specify 65535 values. Strings of this type can take only one of all the values specified in the set. In the case when the addition of values that are not in the specified list will be carried out, 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 with multiple elements of the given arguments.

Table with fractional data types

fractional type SQL data used to store floating point numbers. As a rule, in practice, data of this type characterize various financial indicators. Depending on the required accuracy, one of the following types can be used.

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 as strings. It is used in bank calculations, where the accuracy of the fractional part can reach 8 or 10 digits.

The first two types in this area cannot be used. Storing financial indicators in the form of strings can greatly facilitate the solution of many practical problems. When conducting operations and solving financial issues, SQL data type conversion is of great importance. Developers must take into account the type of information storage and processing methods so that the data remains unchanged at all times.

Integer type 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) - responsible for storing integer data in the range [-231;231-1].
TINYINT (size) - used to store numbers in the range -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 save a lot of memory and at the same time reduce server time when executing the necessary SQL queries. The data types and their range determine the amount of memory required for storage. Developers need to be aware that using large attribute ranges can increase memory costs. You should clearly analyze the tasks to be solved and identify situations in which the approximate range of data is known and the condition for using signed numbers is defined. If there is a small range of used arguments, and all numbers will be positive, then it will be more correct to use the unsigned type, for which the UNSIGNED attribute is intended.

Time and date data types

The data types of time and date are of great interest when studying SQL fundamentals. The use of the following types can provide additional benefits in the case of developing systems whose operation is directly dependent 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 character, with the exception of numbers, can be used as a separator.
TIME - allows you to enter temporary values in a table cell. Values are specified in the "hh:mm:ss" format.
DATE TIME - this type combines the functions of the two previous ones. 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 as the number of seconds that have passed since midnight on 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 data types 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 data type. 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 is a special pointer

In some cases, when filling the database, a situation arises when, when adding a record to the table, there is no need to enter information in all columns. To do this, you must use a special null assignment pointer or NULL. It uses the SQL language as an aid. The data types of the columns, which do not have to be populated, are specified with an operator to allow the inclusion of null values when tables are created. Also, the NULL operator, when using the optional NOT prefix, can be used to indicate that all values must be filled. The NULL pointer has no type. It simply points in the database tables to empty value. For this reason, it may well be combined with one of the above data types.

DEFINITION OF THE DATA STRUCTURE

Questions:

1. SQL data types defined by the standard.. 1

2. Data types used in SQL Server. 3

3. Expressions and variables. five

4. SQL Control Structures.. 6

5. Basic objects of the SQL server database structure. 7

SQL data types defined by the standard

Data is a collection of information stored in a database as one of several different types. Data types set the basic rules for the data contained in a particular table column, including the amount of memory allocated to them.

The SQL language has six scalar data types defined by the standard. Them short description presented in the table.

Character data

Character data consists of a sequence of characters included in the character set defined by the creators of the DBMS. Since character sets are specific to different dialects of the SQL language, the list of characters that can be part of data values of a character type is also implementation dependent. The most commonly used character sets are ASCII and EBCDIC. The following format is used to define character type data:

<символьный_тип>::=

( CHARACTER [ VARYING][length] | [length])

When defining a column with a character data type, the length parameter is used to specify the maximum number of characters that can be placed in this column (the default value is 1). A character string can be defined as having a fixed or variable (VARYING) length. If a string is defined with a fixed length of values, then when fewer characters are entered, the value is padded to the specified length with spaces added to the right. If a string is defined with a variable length of values, then if you enter fewer characters into it, only the characters entered will be stored in the database, which will allow you to achieve some savings external memory.

bit data

bit type data is used to define bit strings, i.e. sequences of binary digits (bits), each of which can have a value of either 0 or 1 . Bit type data is defined using the following format:

<битовый_тип>::=

BIT [length]

Exact numbers

Precise numeric data type used to determine numbers that have an exact representation, i.e. numbers consist of digits, an optional decimal point, and an optional sign character. Precise numeric type data is determined by the precision and length of the fractional part. The precision specifies the total number of significant decimal digits of a number, which includes the length of both the integer part and the fractional part, but excluding the decimal point itself. The scale indicates the number of fractional decimal places in the number.

<фиксированный_тип>::=

(NUMERIC[precision[,scale]]|(DECIMAL|DEC)

[precision[, scale]]

| (INTEGER|INT)| SMALLINT)

Types NUMERIC And DECIMAL designed to store numbers in decimal format. The default length of the fractional part is zero, and the default precision is implementation dependent. The INTEGER (INT) type is used to store large positive or negative integers. The SMALLINT type is for storing small positive or negative integers; in this case, the consumption of external memory is significantly reduced.

Rounded numbers

Type of rounded numbers used to describe data that cannot be accurately represented in a computer, in particular real numbers. Rounded or floating point numbers are represented in scientific notation, in which the number is written using the mantissa multiplied by a certain power of ten (exponent), for example: 10E3, +5.2E6, -0.2E-4 . To define data of a real type, the following format is used:

<вещественный_тип>::=

( FLOAT [precision]| REAL |

DOUBLE PRECISION)

Parameter accuracy specifies the number of significant digits of the mantissa. The precision of the REAL and DOUBLE PRECISION types is implementation specific.

date and time

Data type "date/time" is used to determine points in time with some specified accuracy. The SQL standard supports the following format:

<тип_даты/времени>::=

(DATE | TIME[precision]|

TIMESTAMP[precision])

The DATE data type is used to store calendar dates, which include the fields YEAR (year), MONTH (month), and DAY (day). The TIME data type is for storing timestamps that include the HOUR (hours), MINUTE (minutes), and SECOND (seconds) fields. The TIMESTAMP data type is for storing date and time together. The precision parameter specifies the number of fractional decimal places that determines the precision of storing the value in the SECOND field. If this parameter is omitted, it defaults to zero for TIME columns (i.e., whole seconds are stored), while for TIMESTAMP fields, it defaults to 6. The presence of the WITH TIME ZONE keyword determines the use of the TIMEZONE HOUR and TIMEZONE fields MINUTE, this sets the hour and minutes of the zone time shift with respect to the universal coordinate time (Greenwich Mean Time).

Data of type INTERVAL is used to represent periods of time.

The concept of a domain

Domain is a set of valid values for one or more attributes. If a database table or several tables have columns that have the same characteristics, you can describe the type of such a column and its behavior through a domain, and then map each of the identical columns to a domain name. The domain defines all potential values that can be assigned to an attribute.

The SQL standard allows you to define a domain using next statement:

<определение_домена>::=

CREATE DOMAIN domain_name

datatype

[DEFAULT value]

[ CHECK(valid_values)]

Each domain that you create is given a name, a data type, a default value, and a set of valid values. It should be noted that the above operator format is incomplete. Now, when creating a table, you can specify a domain name instead of a data type.

Removing domains from the database is done using the operator:

DROP DOMAIN domainname [ RESTRICT |

If you specify the CASCADE keyword, any table columns created using the domain being dropped will be automatically modified and described as containing data of the type specified in the definition of the domain being dropped.

An alternative to domains in the environment SQL Server are user-defined data types.

Data Types Used in SQL Server

System data types

One of the highlights of the table creation process is defining the data types for its fields. The data type of a table field determines the type of information that will be placed in this field. The concept of a data type in SQL Server is fully adequate to the concept of a data type in modern programming languages. SQL Server supports a large number of different data types: text, numeric, binary (see table).

Let's bring short review SQL Server data types.

used to store character information. character data types, which include CHAR (length), VARCHAR (length), NCHAR (length), NVARCHAR (length). The last two are for storing Unicode characters. The maximum length value is limited to 8000 characters (4000 for Unicode characters).

Storage of large character data (up to 2 GB) is carried out using the text data types TEXT and NTEXT.

TO integer data types include INT (INTEGER), SMALLINT, TINYINT, BIGINT. To store data of an integer type, 4 bytes (range from -231 to 231-1), 2 bytes (range from -215 to 215-1), 1 byte (range from 0 to 255) or 8 bytes (range from -263) are used, respectively. to 263-1). Objects and expressions of integer type can be used in any mathematical operations.

Numbers that include a decimal point are called non-integer numbers. Non-integer data divided into two types - decimal And approximate.

TO decimal data types include DECIMAL [(precision[,scale])] or DEC and NUMERIC [(precision[,scale])]. The DECIMAL and NUMERIC data types allow you to define the floating-point precision format yourself. The precision parameter specifies maximum amount digits of input data of this type (before and after the decimal point in the sum), and the scale parameter is the maximum number of digits located after the decimal point. In normal mode, the server allows you to enter a maximum of 28 digits used in the DECIMAL and NUMERIC types (from 2 to 17 bytes).

TO approximate data types include FLOAT (15 digit precision, 8 bytes) and REAL (7 digit precision, 4 bytes). These types represent data in floating point format, i.e. numbers are represented using the mantissa and exponent, which ensures the same precision of calculations no matter how small or large the value is.

To store information about date and time data types such as DATETIME and SMALLDATETIME are intended, which use 8 and 4 bytes to represent dates and times, respectively.

MONEY and SMALLMONEY data types make it possible to store money-type information; they provide precision to 4 decimal places and use 8 and 4 bytes, respectively.

BIT data type allows you to store one bit, which takes the value 0 or 1.

The SQL Server environment implements a number of special data types.

Data type TIMESTAMP used as an indicator of a row version change within the database.

Data type UNIQUEIDENTIFIER used to store globally unique identification numbers.

Data type SYSNAME is for object identifiers.

Data type SQL_VARIANT allows you to store values of any of the data types supported by SQL Server except TEXT, NTEXT, IMAGE, and TIMESTAMP.

Data type TABLE, like temporary tables, provides storage for a set of rows intended for further processing. The TABLE data type can only be used to define local variables and return custom functions values.

Data type CURSOR is needed to work with objects such as cursors, and can only be required for variables and parameters of stored procedures. SQL Server cursors are a mechanism for exchanging data between a server and a client. The cursor allows client applications to work with full set data, but only with one or more lines.

Last update: 07/12/2017

When you create a table, you must specify a specific data type for all of its columns. The data type determines what values can be stored in the column, how much they will take up memory space.

The T-SQL language provides many different types. Depending on the nature of the values, all of them can be divided into groups.

Numeric Data Types

BIT : stores the value 0 or 1. In fact, it is an analogue of the boolean type in programming languages. It takes 1 byte.

TINYINT : stores numbers from 0 to 255. Occupies 1 byte. Good for storing small numbers.

SMALLINT : stores numbers from -32768 to 32767. Occupies 2 bytes

INT : stores numbers from -2147483648 to 2147483647. Occupies 4 bytes. The most used type for storing numbers.

BIGINT : Stores very large numbers from -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807 that take up 8 bytes of memory.

DECIMAL : stores fixed precision numbers. It takes from 5 to 17 bytes, depending on the number of numbers after the decimal point.

This type can take two parameters precision and scale: DECIMAL(precision, scale) .

The precision parameter represents the maximum number of digits that the number can store. This value must be between 1 and 38. The default is 18.

The scale parameter represents the maximum number of digits that a number after the decimal point can contain. This value must be between 0 and the value of the precision parameter. It defaults to 0.

NUMERIC : This type is similar to the DECIMAL type.

SMALLMONEY : stores fractional values from -214 748.3648 to 214 748.3647. Designed to store money. Takes 4 bytes. Equivalent to DECIMAL(10,4) .

MONEY : Stores fractional values from -922337203685477.5808 to 922337203685477.5807. Represents monetary values and occupies 8 bytes. Equivalent to DECIMAL(19,4) .

FLOAT : stores numbers from -1.79E+308 to 1.79E+308. It takes from 4 to 8 bytes depending on the fractional part.

May be defined as FLOAT(n) , where n represents the number of bits that are used to store the decimal part of the number (the mantissa). By default n = 53.

REAL : stores numbers from -340E+38 to 3.40E+38. Takes 4 bytes. Equivalent to FLOAT(24) .

Numeric column examples: Salary MONEY, TotalWeight DECIMAL(9,2), Age INT, Surplus FLOAT

Data Types Representing Date and Time

DATE : Stores dates from 0001-01-01 (January 1, 0001) to 9999-12-31 (December 31, 9999). Takes 3 bytes.

TIME : stores the time in the range from 00:00:00.0000000 to 23:59:59.9999999. It takes from 3 to 5 bytes.

May be of the form TIME(n) , where n represents the number of digits from 0 to 7 in fractional seconds.

DATETIME : stores dates and times from 01/01/1753 to 12/31/9999. Occupies 8 bytes.

DATETIME2 : Stores dates and times ranging from 01/01/0001 00:00:00.0000000 to 12/31/9999 23:59:59.9999999. It takes from 6 to 8 bytes depending on the time precision.

May be of the form DATETIME2(n) , where n represents the number of digits from 0 to 7 in fractional seconds.

SMALLDATETIME : stores dates and times between 01/01/1900 and 06/06/2079, which are the nearest dates. Takes 4 bytes.

DATETIMEOFFSET : Stores dates and times in the range 0001-01-01 to 9999-12-31. Stores detailed time information accurate to 100 nanoseconds. Takes 10 bytes.

Common date formats:

yyyy-mm-dd- 2017-07-12

dd/mm/yyyy - 12/07/2017

mm-dd-yy - 07-12-17

In this format, two-digit numbers from 00 to 49 are treated as dates in the range 2000-2049. And the numbers from 50 to 90 as a range of numbers 1950 - 1999.

Month dd, yyyy - July 12, 2017

Common time formats:

hh:mi am/pm - 1:21 pm

hh:mi:ss - 1:21:34

hh:mi:ss:mmm - 1:21:34:12

hh:mi:ss:nnnnnnn - 1:21:34:1234567

String data types

CHAR : stores a string between 1 and 8,000 characters long. Allocates 1 byte for each character. Not suitable for many languages because it stores non-Unicode characters.

The number of characters a column can store is passed in parentheses. For example, for a CHAR(10) column, 10 bytes will be allocated. And if we store a string of less than 10 characters in a column, then it will be padded with spaces.

VARCHAR : stores a string. 1 byte is allocated for each character. You can specify a specific length for a column, from 1 to 8,000 characters, such as VARCHAR(10) . If the string must have more than 8000 characters, then the MAX size is set, and up to 2 GB can be allocated for storing the string: VARCHAR(MAX) .

Not suitable for many languages because it stores non-Unicode characters.

Unlike the CHAR type, if a 5-character string is stored in a VARCHAR(10) column, exactly five characters will be stored in the column.

NCHAR : Stores a Unicode string between 1 and 4,000 characters. 2 bytes are allocated for each character. For example, NCHAR(15)

NVARCHAR : Stores a Unicode string. 2 bytes are allocated for each character. You can set a specific size from 1 to 4,000 characters: . If the string must have more than 4000 characters, then the MAX size is set, and up to 2 GB can be allocated for storing the string.

The other two types, TEXT and NTEXT, are deprecated and therefore not recommended. Instead, VARCHAR and NVARCHAR are used, respectively.

Examples of defining string columns:

Email VARCHAR(30), Comment NVARCHAR(MAX)