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. Their brief description is 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 the 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 in 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 exactly represented by a computer, such as 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 dependent.
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 with the following 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 a SQL Server environment 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 take a quick look at 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 the maximum number of digits of this type of input (before and after the decimal point in the sum), and the scale parameter specifies the maximum number of digits 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 values from user-defined functions.
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 operate on only one or more rows rather than the full set of data.
©2015-2019 site
All rights belong to their authors. This site does not claim authorship, but provides free use.
Page creation date: 2016-08-08
SQL data types.
|
Description | ||
|
String types |
Character string of constant length |
CHAR (number_of_characters) |
|
Variable length character string |
VARCHAR (number_of_characters) |
|
|
Large variable length character string | ||
|
big binary variable object | ||
|
Numeric Exact Types |
integer | |
|
Integer. Number of digits greater than or equal to INTEGER | ||
|
Integer. Number of digits less than or equal to INTEGER | ||
|
Fixed point number (number_digits - total number of characters, scale - decimal places) |
(NUMERIC | DECIMAL | DEC) (number of digits, scale) |
|
|
Numeric imprecise types |
floating point number | |
|
Floating point number. Number of digits greater than or equal to REAL |
DOUBLE PRECISION |
|
|
floating point number |
FLOAT (number of digits) |
|
|
Temporary types |
Date of. Contains year, month and day | |
|
Timestamp. Contains year, month, day, hour, minute and second |
TIMESTAMP (scale) |
|
|
Time. Contains hours, minutes and seconds (scale - number of decimal places in seconds). You can specify a time zone |
TIME (scale) |
|
|
Period. | ||
|
boolean type |
Takes boolean values |
Literals
Literals are simple expressions that are values of their respective types. Each data type has a way to represent the 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).
Operators and Expressions
Operators are language constructs that specify operations on data elements and return a new value as a result. Data elements used in operators 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 test operator is represented by the IS NULL keyword.
There are two main types of operators. Unarys have only one operand and are usually represented in the following format:
operand operator
Binary operators 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. There are operators of the following types:
string (||);
arithmetic;
brain teaser;
comparison predicates;
special predicates;
operators over sets (tables).
1.1. Character types
1) Strings of constant length
CHAR()– a string of text in a format defined by the developer. A natural number specifies strings.
In practice, the maximum number of characters ranges from 256 in MS SQL Server to 32767 in InterBase.
CHAR is treated as CHAR(1)
2) Variable length strings
VARCHAR|CHAR VARYING [()]– a string of text of variable length in a format defined by the developer. A natural number specifies the maximum string, but the table only has room for the actual length of the string.
3) Features of character types of a number of DBMS
In a number of DBMSs, for example, MS SQL Server, if CHAR allows a NULL value, then it is treated as VARCHAR.
In Oracle, for VARCHAR2 fields, you can reserve space in each block for future field updates by specifying the PCTFREE option.
1.2. Numeric types
1) Integer data types
INT is a number without a decimal point. The size depends on the specific implementation. Often this is 4 bytes.
SMALLINT- same as INT, but usually smaller. Often 2 bytes.
BIGINT- same as INT, but usually larger. It is 4 or more bytes.
2) Real numbers with a fixed point
DEC|)] is a decimal number with a fixed point.
The number has:
is the total number of significant decimal places,
— the maximum number of digits to the right of the decimal point.
3) Real floating point numbers
FLOAT is a floating point number expressed in exponential form in base 10. Specifies the maximum precision.
REAL– same as FLOAT, but the precision depends on the implementation.
DOUBE– is the same as REAL, but the accuracy may be greater in a particular implementation.
1.3. Dates and Time Types
DATE– date in yyyy-mm-dd (ISO), mm/dd/yyyy (ANSI) format.
TIME– time in hh.mm.ss (ISO), hh:mm am/pm (ANSI) format.
INTERVAL– date and time in yyyy-mm-dd-hh.mm.ss.nnnnn (ISO) format. (often TIMESTAMP).
Note:
Date and time types can be specified as string literals.
Date: 'yyyy-mm-dd', time: 'hh.mm.ss',
Interval: 'yyyy-mm-dd-hh.mm.ss.n…n'.
1.4. boolean type
BOOLEAN– boolean value (TRUE, FALSE, UNKNOWN).
For a correct understanding of the truth table in three-valued logic (3VL), we can conditionally assume that FALSE is 0, TRUE -1, and UNKNOWN is 0.5.
Then:
— The AND operator returns the smallest.
— The OR operator is the largest of the original values.
- NOT UNKNOWN = UNKNOWN.
2. Collections
Collections actually violate first normal form (1NF).
2.1. array
[()] ARRAY is a set of similar values.
Note:
Arrays were introduced in SQL:99.
Example:
For example, the definition of WeekDays Varchar(10) ARRAY allows you to store the name of all seven days of the week in one field.
A number of DBMS allow even multidimensional arrays. So in InterBase, up to 16 changes are possible, Clarion - 4.
2.2. multiset
[()] MULTISET– an unlimited set of values of the same type, allowing duplicates.
Values are created by the constructor - special functions.
Note:
Multisets were introduced in SQL:2003.
2.3. Anonymous string type
ROW ([()] , …)– a set of heterogeneous values, including nested ones.
Options can specify the sort order for string type fields and a number of other settings.
Example:
So, defining Address ROW(State Char(6), City Varchar(30), Street Varchar(50)) allows you to store a detailed address in a single field.
3. LOB types
CLOB (Character Large Object)– behave much like character strings, but they are not allowed to be used:
— In Primary Key, Unique, Foreign Key constraints.
— In comparisons other than pure equalities or inequalities, in the Order By and Group By sections.
BLOB (Binary Large Object)– a stream of bytes in a format in which the user can write them to the database column.
3.1. Problems with using LOBs
1) Storage issues
Storing LOB directly in tables along with other data breaks the optimizer's work based on the use of data pages whose size corresponds to the size of disk pages.
Therefore, LOBs are stored in separate areas (segments) of disk memory.
2) Update problems
Since the size of LOB objects can reach tens and hundreds of megabytes, it is impossible to store them entirely in buffers. Therefore, LOB type data is processed in parts, for example, groups of pages. Partially processed INSERT and UPDATE statements use special techniques to allow multiple calls to the same API function on the same field. Similarly, when reading data with SELECT and FETCH statements.
3) Transaction execution problems
To support transactions, most DBMS maintain a transaction log that records copies of data before and after modifications.
However, due to their large sizes, LOBs are not logged.
4) Network forwarding problems
Often the client and server are running on different computers, and sending LOBs over the network can interrupt everyone using the network at the time.
4. Different types
4.1. locator
A unique binary (four-bit) value (in OOP - a descriptor) stored in the database.
It is described in the main program and is valid until the end of the transaction.
Designed for manipulating LOB values (or arrays) on the client side. Instead of a LOB, a link to it is sent to the client.
You can declare: LOC: Integer AS LOCATOR.
4.2. XML
Values are essentially XML documents.
For this type, a number of operations are defined that provide access to the elements of the value of the XML type, transformations of similar data, and so on.
4.3. Datalink
Datalinks are part of SQL/MED 9075-9:2003.
Datalink is a special type of SQL designed to store URLs in a database, as well as a number of functions that can be used in SQL queries.
For features and supported functions, please visit:
Wiki.postgresql.org/wiki/DATALINK
Used in DB2, Oracle - to store data in an external BFile.
SQL Data Types are divided into three groups:
- string;
- floating point (fractional numbers);
- whole numbers, date of And time.
SQL Data Types string
| SQL Data Types | Description |
| CHAR(size) | Fixed-length strings (can contain letters, numbers, and special characters). The fixed size is given in brackets. Can write up to 255 characters |
| VARCHAR(size) | |
| TINYTEXT | Can store up to 255 characters. |
| TEXT | |
| BLOB | Can store a maximum of 65,535 characters. |
| MEDIUM TEXT | |
| MEDIUMBLOB | Can store a maximum of 16,777,215 characters. |
| LONG TEXT | |
| 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 the SQL Data Type ENUM list. If the value is not present in the list when inserting ENUM, then we get an empty value. You can enter possible values in this format: ENUM ("X", "Y", "Z") |
| SET | SQL SET data type resembles ENUM except that SET can contain up to 64 values. |
SQL Data Types floating point (fractional numbers) and integers
SQL Data Types- Date and time
Create a table in the database is produced by the command CREATE TABLE.
Syntax:
CREATE TABLE tbl_name [(create_definition,...)]
tbl_name- Specifies the name of the table to be created in the current database. If no database at the time the command is called CREATE TABLE was not accepted as current, then a command execution error will occur.
IF NOT EXISTS- If this parameter is specified and an attempt is made to create a table with a duplicate name (i.e. a table with the same name already exists in the current database), then the table will not be created and the error message will not appear. Otherwise, the table will not be created either, but the command will generate an error.
create_definition- Defines the internal structure of the created table (names and types of fields, keys, indexes, etc.)
Possible syntaxes create_definition:
col_name type
PRIMARY KEY (index_col_name, ...)
KEY(index_col_name,...)
INDEX(index_col_name,...)
UNIQUE(index_col_name,...)
FOREIGN KEY(index_col_name,...)
col_name- Specifies the name of a column in the generated table.
type- Sets the data type for the column col_name.
Possible parameter values type:
INT[(length)]
§ BIGINT[(length)]
DOUBLE[(length,decimals)]
§ FLOAT[(length,decimals)]
§ DECIMAL(length, decimals)
CHAR(length)
§ VARCHAR(length)
- Indicates whether the data column can contain a NULL value or not. If not specified, it defaults to NULL (ie, may contain NULL).
- Sets the default value for this column. When inserting a new record into the table with the command INSERT if the value for the col_name field was not explicitly specified, then the value is set default_value.
- When inserting a new record into the table, a field with this attribute will automatically receive a numeric value, 1 more than the largest value for this field at the current time. This feature is typically used to generate unique row IDs. The column for which the attribute is applied AUTO_INCREMENT, must be of integral type. A table can only have one column with the attribute AUTO_INCREMENT. This column must also be indexed. The number sequence for AUTO_INCREMENT starts at 1. These can only be positive numbers.
The following example creates a table users with 3 fields, where the first field is the unique ID of the post, the second field is the username, and the third field is the user's age:
create table Water_Area(
id int auto_increment,
name varchar(70),
constraint pk_Water_Area primary key nonclustered(id))
create table Station(
id_Water_Area int not null,
name varchar(20) not null,
Latitude float(4) null,
longitude float(4) null,
coordinates varchar(40) null,
coordinates_modern varchar(30) null,
comment varchar(70) null
constraint pk_Station primary key nonclustered(id))
Deleting a table.
The DROP TABLE command is used to drop a table.
DROP TABLE tbl_name [, tbl_name, ...]
tbl_name - The name of the table to be deleted.
IF EXISTS - If this parameter is specified, then no error will be raised when trying to delete a table that does not exist. Otherwise, a command execution error will occur.
RESTRICT and CASCADE Do not carry any functionality. Left to simplify the transfer of the program.
Drop Table Station
Table modification.
For modification, use the Alter Table command.
Syntax:
ALTER TABLE TableName1ADD | ALTER FieldName1FieldType [(nFieldWidth [, nPrecision])] ]] | FieldName]
The ALTER TABLE command changes the definition of a table in one of the following ways:
Adds a column
Adds an integrity constraint
Overrides a column (data type, size, default value)
Deletes a column
Modifies memory characteristics or other parameters
Enables, disables, or removes an integrity constraint or trigger.
alter table station
unique(id_Water_Area, name)
alter table station
add constraint fk_Station_Relation_Water_Area foreign key (id_Water_Area) references Water_Area (id)
