Operator formats for changing and deleting data. Basic SQL Operators

02.07.2020 In contact with

SQL (StructuredQueryLanguage) is a universal computer language used to create, modify and manipulate data in relational databases. The SQL language is based on relational algebra and is a collection of operators.

There are 4 groups of operators. Consider a group of Data Manipulation Language (DML, SQL DML)

Data selection

Selecting data is the most common SQL operation. The SELECT statement is one of the most important statements in this language for selecting data. The syntax for this operator is as follows:

SELECT column FROM table

SELECT statements must contain the words SELECT and FROM; other keywords are optional.

The SELECT keyword is followed by information about which fields to include in the resulting dataset. An asterisk (*) denotes all fields in a table, for example:

The following syntax is used to select one column:

SELECT Company

An example of selecting multiple columns looks like this:

SELECT Company, Phone,Mail

The FROM keyword is used to specify the names of the tables from which records are fetched, for example:

SELECT * FROM Customers

This query will return all fields from the Customers table.

You can use the WHERE clause to filter the results returned by the SELECT statement (optional)

SELECT * FROM Products WHERE Category = 4

Various expressions can be used in the WHERE clause,

WHERE expression1 [(AND | OR) expression2 ...]

For example:

SELECT * FROM Products WHERE Category = 2 AND Postavshik> 10

SELECT Name, Price FROM Products WHERE Category = 3 OR Price< 50

You can use the following operators:

< Меньше

<= Меньше или равно

<>Not equal

> More

> = Greater than or equal

ORDER BY clause (optional) is used to sort the resulting dataset by one or more columns. The keywords ASC (ascending) or DESC (descending) are used to determine the sort order. By default, data is sorted in ascending order.

Data modification

In addition to retrieving data, SQL can be used to update and delete data, copy records to other tables, and perform other operations. Below we will look at the UPDATE, DELETE, and INSERT statements used to accomplish some of these tasks.

UPDATE statement

The UPDATE statement is used to change values in one or more columns of a table. The syntax for this operator is:

UPDATE table SET column1 = expression1 WHERE criteria

The expression in the SET clause can be a constant or the result of an evaluation. For example, to raise the prices of all products that cost less than $ 10, you can run the following query:

UPDATE Products SET Price = Price * 1.1 WHERE Price< 10

DELETE statement

To delete rows from tables, use the DELETE statement, whose syntax is:

DELETE FROM table WHERE criteria

Remove all products with a value less than 100:

DELETE FROM Products WHERE Price< 100

OperatorINSERT

To add records to tables, use the INSERT statement, the syntax of which is:

INSERT INTO table (VALUES (expression [, ...])

For example, to add a new customer to the Customers table, you can use the following query:

INSERT INTO Customers (CustomerID, CompanyName) VALUES (‘XYZ’, ‘XYZ Deli’)

An orator is a reserved word or character used primarily in WHEREs and gives an SQL statement to perform operation (s) such as comparisons and arithmetic operations.

Operators are used to define conditions in SQL and serve as joins for multiple conditions in a statement.

Arithmetic operators
Comparison Operators
Logical operators
Operators used to negate a condition

Arithmetic operators in SQL:

Suppose the variable a is equal to 10, and the variable b is equal to 20, then:

Comparison operators in SQL:

Suppose variable a is 10 and variable b is 20, then:

operator	Description	example
=	Checks if the values of two operands are equal or not, if yes, then the condition becomes true.	(a = b) is not true.
!=		(a! = b) is true.
<>	Checks if the values of two operands are equal or not, if the values are not equal, then the condition becomes true.	(a<>b) is true.
>	Checks if the value of the left operand is greater than the value of the right operand, if yes, then the condition becomes true.	(a> b) is not true.
<	Checks if the value of the left operand is less than the value of the right operand, if yes, then the condition becomes true.	(a< b) истинно.
> =	Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes, then the condition becomes true.	(a> = b) is incorrect.
<=	Checks if the value of the left operand is less than or equal to the value of the right operand, if yes, then the condition becomes true.	(a<= b) истинно.
	Checks if the value of the left operand is not less than the value of the right operand, if yes, then the condition becomes true.	(a!< b) неверно.
!>	Checks if the value of the left operand is not greater than the value of the right operand, if yes, then the condition becomes true.	(a!> b) is true.

Logical operators in SQL:

Here is a list of all the logical operators available in SQL.

operator	Description
ALL	The ALL operator is used to compare the value for all values in another set of values.
AND	The AND operator allows multiple conditions to exist in the SQL WHERE clause.
ANY	The ANY operator is used to compare any applicable value in a list according to conditions.
BETWEEN	The BETWEEN operator is used to find values that are within a set of values, given the minimum value and the maximum value.
EXIST	The EXISTS operator is used to find the presence of a row in a specified table that matches certain criteria.
IN	The IN operator is used to compare a value in a list of literal values that have been defined.
LIKE	The LIKE operator is used to compare similar values using wildcard operators.
NOT	The NOT operator changes the meaning of the logical operator with which it is used. For example: NOT EXISTS, NOT BETWEEN, NOT IN, etc. The operator denies this.
OR	The OR operator is used to combine multiple conditions in an SQL WHERE clause.
IS NULL	The NULL operator is used to compare a value with a NULL value.
UNIQUE	A single statement searches every row from the specified table for uniqueness (no duplicates).

Structure Query Language (SQL) was created as a result of the development of the relational data model and is now the de facto standard for relational database management systems. The SQL language is supported today by a huge number of different types of DBMS.

The name of the SQL language is usually pronounced by the letters "es-q-el". The mnemonic name "See-Quel" is sometimes used.

The SQL language provides the user (with minimal effort on his part) the following capabilities:

Create databases and tables with a complete description of their structure

Perform basic data manipulation operations: insert, modify, delete data

Perform both simple and complex queries.

SQL is relationally complete.

The structure and syntax of its commands are quite simple, and the language itself is universal, that is, the syntax and structure of its commands does not change when moving from one DBMS to another.

The SQL language has two main components:

Data Definition Language (DDL) for defining database structures and data access control

DML (Data Manipulation Language), designed to fetch and update data.

The SQL language is non-procedural, that is, when using it, it is necessary to indicate what information should be obtained, and not how it can be obtained. SQL commands are ordinary English words (SELECT, INSERT, etc.). Let's look at the SQL DML statements first:

SELECT - fetching data from the database

INSERT - inserting data into a table

UPDATE - updating data in a table

DELETE - deleting data from a table

SELECT statement

The SELECT statement performs operations equivalent to the following relational algebra operations: select, projection, and join.

The simplest SQL query using it looks like this:

SELECT col_name FROM tbl

The select keyword is followed by a comma-separated list of columns that will be returned from the query. The from keyword specifies which table (or view) to retrieve data from.

The result of a select query is always a table, which is called the result table. Moreover, the results of a query executed with the select statement can be used to create a new table. If the results of two queries against different tables have the same format, they can be combined into one table. Also, the table obtained as a result of the query can become the subject of further queries.

To select all columns and all rows of a table, just make the SELECT * FROM tbl;

Consider the Product table, which contains price information for various types of products:

The result of the query

SELECT * FROM Product;

will be the entire Product table.

You can select specific columns of a table using a query

SELECT col1, col2, ..., coln FROM tbl;

So, the query result

SELECT Type, Price FROM Product;

there will be a table

The list of columns in the select statement is also used when it is necessary to change the order of the columns in the resulting table:

In order to select only those table rows that satisfy certain restrictions, a special keyword where is used, followed by a boolean condition. If a record satisfies this condition, it is included in the result. Otherwise, such an entry is discarded.

For example, the selection of those goods from the Product table, the price of which satisfies the Price condition<3200, можно осуществить, используя запрос

SELECT * FROM Product where Price<3200;

Its result:

The condition can be compound and combined using the logical operators NOT, AND, OR, XOR, for example: where id_ Price> 500 AND Price<3500. Допускается также использование выражений в условии: where Price>(1 + 1) and string constants: where name = "autobalance".

Using the BETWEEN var1 AND var2 construct allows you to check if the values of any expression fall within the range var1 to var2 (including these values):

SELECT * FROM Product where Price BETWEEN 3000 AND 3500;

By analogy with the NOT BETWEEN operator, there is the NOT IN operator.

Column names specified in the SELECT clause can be renamed. For this, the AS keyword is used, which, however, can be omitted, since it is implicitly implied. For example, the request

SELECT Type AS model, Type_id AS num FROM Product where Type_id = 3

will return (alias names should be written without quotes):

The LIKE operator is for comparing a string against a pattern:

SELECT * FROM tbl where col_name LIKE "abc"

This query returns only those records that contain the string value abc in the col_name column.

Two wildcards are allowed in the pattern: "_" and "%". The first one replaces one arbitrary character in the template, and the second one replaces a sequence of arbitrary characters. So, "abc%" matches any string starting with abc, "abc_" - a 4-character string starting with abc, "% z" - an arbitrary string ending with z, and, finally, "% z%" - sequences of characters containing z.

You can find all records of the Product table in which the Type value begins with the letter "a" as follows:

SELECT * FROM Product where Type LIKE "a%";


	auto scales

If the search string contains a wildcard character, then you must specify the control character in the ESCAPE clause. This control character should be used in the pattern before the wildcard character, indicating that the latter should be treated like a regular character. For example, if in a certain field all values containing the character "_" are to be found, then the pattern "% _%" will result in all records from the table being returned. In this case, the template should be written as follows:

"% | _%" ESCAPE "|"

To check the value for compliance with the string "20%", you can use the following operator:

LIKE "20 #%" ESCAPE "#"

The IS NULL operator allows you to check the absence (presence) of NULL values in the table fields. Using the normal comparison operators in these cases may lead to incorrect results, as comparison with a NULL value results in UNKNOWN (unknown). Thus, the selection condition should look like this:

where col_name IS NULL, instead of where col_name = NULL.

The default selection result returns records in the same order in which they are stored in the database. If you need to sort records by one of the columns, you must use the ORDER BY clause, followed by the name of this column:

SELECT * FROM tbl ORDER BY col_name;

As a result of this query, records will be returned in ascending order of the value of the col_name attribute.

Records can be sorted by several columns. To do this, their names must be specified after the ORDER BY, separated by commas:

SELECT * FROM tbl ORDER BY col_name1, col_name2.

The records will be sorted by the col_name1 field; if there are several records with the same value in the col_name1 column, they will be sorted by the col_name2 field.

If you want to sort records in reverse order (for example, descending by date), you need to specify ORDER BY col_name DESC.

For direct sorting, there is the ASC keyword, which is the default.

If the result of the selection contains hundreds or thousands of records, their output and processing takes a significant amount of time.

Therefore, information is often paginated and presented to the user in chunks. Page navigation is used with the limit keyword followed by the number of records to display. The following query retrieves the first 10 records, while simultaneously performing a reverse sort on the col_name1 field:

SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10

To retrieve the next 10 records, the limit keyword is used with two values: the first indicates the position from which to display the result, and the second indicates the number of records to retrieve:

SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10,10

To retrieve the next 10 records, you must use the LIMIT 20, 10 construct.

And over the data of the tables.

The SQL language is called built-in since it contains the functions of a full-fledged development language, and is focused on data access, as a result of which it is included in the application development tools. SQL language standards support programming languages Pascal, Fortran, COBOL, С, etc.

Exists 2 methods of using embedded SQL:

static language use ( static SQL) - the program text contains calls to SQL functions, which are included in the executable module after compilation.
dynamic language use ( dynamic SQL) - dynamic construction of SQL function calls and their interpretation. for instance, you can refer to the data of the remote database during the execution of the program.

The SQL language (like other languages for working with databases) is intended for preparing and executing queries. As a result of executing a query of data from one or more tables, a set of records is obtained, which is called submission.

Definition 1

Representation Is a table that is formed as a result of a query.

Basic operators of the SQL query language

SQL statements are conventionally divided into 2 sublanguages:

Data Definition Language DDL;
Data manipulation language DML.

In the table, * are marked with specific operators language.

Let's take a look at the most important SQL statements.

Table creation statement:

The name of the table that is being created and the name of at least one column (field) are required operands. For the column name, you must specify the type of data that will be stored in it.

For individual fields, you can specify additional rules for controlling the values that are entered into them. For instance, NOT NULL indicates that the field cannot be empty and a value must be entered into it.

Example 1

To create a table books catalog of books, which contains fields:

type- type of book,

name- name of the book,

price- the price of the book

the operator might look like this:

Operator for changing the structure of a table:

When changing the structure of the table, you can add ( ADD), change ( MODIFY) or delete ( DROP) one or more columns of the table. The recording rules for this operator are the same as for the operator. CREATE TABLE... You do not need to specify to delete a column.

Example 2

To add to the table books fields number, which will store the number of books, you can write the operator:

Dropping table operator:

Example 3

For example, to drop an existing table named books just use the operator:

Index creation operator:

The operator creates an index on one or more columns of a given table to speed up query and search operations. Multiple indexes can be created for one table.

Optional option UNIQUE is responsible for ensuring the uniqueness of the values in all columns that are specified in the statement.

ASC sets automatic sorting of values in columns in ascending order (default), and DESC- in descending order.

Index drop operator:

View creation operator:

You can omit column names when creating a view. Then the column names from the query will be used, which is described by the corresponding operator SELECT.

View delete operator:

Record selection operator:

Operator SELECT makes selections and calculations on data from one or more tables. The result of executing the operator is a response table that contains ( ALL) or does not contain ( DISTINCT) lines that are repeated.

Operand FROM contains a list of tables from which records are taken to select data.

Record change operator:

New field values in records may not contain values ( NULL) or calculated according to an arithmetic expression.

New record insertion operator:

In the first record of the operator INSERT new records are entered with the specified values in the columns.

In the second statement of the operator INSERT new rows are introduced, selected from another table through a sentence SELECT.

Delete record operator:

As a result of executing the operator, rows are deleted from the specified table that satisfy the condition, which is defined by the optional operand WHERE... If the operand WHERE is not specified, then all table records are deleted.

Discipline: Databases

Language OperatorsSQL

The SQL language includes operators of various categories. Any SQL statement consists of reserved words and user-defined words according to established syntax rules. As with many programming languages, most of the language's operator components are not case sensitive. An exception to this rule, as usual, is character data, when specifying which it is necessary to remember the case and use the one that is necessary to represent the data.

For writing operators in the language, a free format is adopted, which makes it possible to make the SQL program more readable by means of indents and alignments.

each phrase in a statement must start on a new line;

the beginning of each phrase must be aligned with the beginning of the rest of the operator's phrases;

each part of the phrase should start on a new line with some indentation relative to the beginning of the entire phrase, which will allow you to highlight the subordinate parts;

some conventions apply for writing operators:

uppercase letters are used to write reserved words;

lowercase letters are used to write user-defined words;

the vertical bar "|" "indicates the need to select one of several values;

curly braces define a required element;

square brackets define an optional element;

the ellipsis "..." is used to indicate the optional possibility of repeating a construct, from zero to several times.

Data definition operators (Table 1) are used to describe the structures of the data used. This category includes the following statements: create table, drop table, alter table, create view, ALTER VIEW, DROP VIEW.

Table 1 ... Data Definition Operators

Operator Explanation

create table Create table

DROP table Drops a table

alter table Change table

CREATE VIEW Create a view

alter view Change view

drop view Delete view

Data manipulation operators, forming the next category of operators, are designed to fill tables with data and to update the information loaded in them. The following operators belong to this category: delete, insert, update (Table 2).

table 2 ... Data manipulation operators

OperatorExplanation

Delete Deletes one or more lines that match the conditions

filtering, from the base table

INSERT Inserts one row into the underlying table

update Updates the values of one or more columns in one or more

multiple lines matching filtering conditions

To retrieve information from the database, the query language is used, which in the SQL language is represented by one select statement (Table 3).

Table 3. Query language

Operator Explanation

select Selects rows; operator that allows you to form the resulting

table corresponding to the query

In addition to the indicated categories of operators, the purpose of which is not difficult to imagine, after reading the explanations in the tables, it is necessary to highlight two more: transaction control operators (Table 4) and data administration tools (Table 5).

Table 4. Transaction management

Operator Explanation

commit Complete transaction - complete information processing,

merged into a transaction

rollback Roll back a transaction - undo changes made during execution

the state of the database, mark it so that you can return to it in the future

Table 5 ... Data administration

Operator Explanation

ALTER DATABASE Modify the set of core objects in the database, restrictions on

the entire database

ALTER DBAREA Modify a previously created storage area

ALTER PASSWORD Change the password for the entire database

CREATE DATABASE Create a new database

CREATE DBAREA Create a new storage area and make it available for placement

DROP DATABASE Drops an existing database

DROP DBAREA Drops an existing storage area (if it does not currently contain

active data is located)

GRANT Grant access rights to a number of actions on some database object

REVOKE Revoke access rights to some object or some actions on

object

In commercial DBMSs, the set of basic operators has been expanded. Most DBMSs include statements for defining and deleting a stored procedure run index and statements for defining triggers.

It is customary to begin acquaintance with this language by considering the capabilities of the query language, which in the SQL language is represented by a single select statement, because this powerful operator, of course, is also the most complex one. In addition, in the future it will be interesting to see how it can be used in conjunction with data manipulation operators.

Selection operatorSELECT . Formation of queries to the database

The purpose of the select operator is to select and display data from one or several database tables. This extremely powerful, commonly used operator implements all the operations of relational algebra. One and the same request can be implemented in several ways, which may differ significantly in terms of execution time.

Select statement format:

SELECT * |<список полей>FROM<список таблиц>

The specified order of phrases in the select statement cannot be changed, but not all parts are required. Only select and from clauses are required clauses. All other parts of the operator can be used at the discretion of the programmer. Explanation:

□ Phrase select:

Keyword presence all(default) means that the resulting table includes all rows that meet the query conditions, which can lead to the appearance of the same rows in the resulting table;

Keyword distinct is intended to bring the table in accordance with the principles of the theory of relations, where it is assumed that there are no duplicate rows;

Symbol " * "defines a very common situation where all columns from the original query table are included in the result set.

□ In a phrase from a list of source query tables is set.

□ In a phrase where defines the selection conditions for the result rows or the conditions for joining the rows of the source tables, similar to the conditional join operation in relational algebra. The following predicates can be used as selection conditions:

Comparisons "=,<>, >, <, >=, <=" - для сравнения результатов вычисления двух выражений; более сложные выражения строятся с помощью логических операторов AND, OR, NOT; значения выражений вычисляются в порядке, который определяется приоритетом используемых операторов и наличием скобок в выражении;

betweenAandV- the predicate is true when the calculated value of the expression falls within the specified range (the predicate notbetweenaandV true when the compared value does not fall within the specified interval);

in- the predicate is true when the compared value is included in the set of specified values; in this case, the set of values can be specified by a simple enumeration or an inline subquery (the predicate not in is true when the value being compared is not included in the given set);

like and notlike- predicates, the meaning of which is opposite, require specifying a template with which a given value is compared; the predicate like is true when the compared value matches the pattern and false otherwise;

ISnull- a predicate used to determine the equality of the value of some attribute to an undefined value:

<имя атрибута> ISnull- takes on the value true if the specified attribute in the given string has an undefined value and the value is false, otherwise;

<имя атрибута> ISNOTnull- everything happens the other way around.

exist and notexist used in inline subqueries.

□ In a phrase groupby a list of grouping fields is set.

□ In a phrase having the predicates-conditions imposed on each group are set.

□ In a phrase orderby a list of fields for ordering the result is set, that is, a list of fields that determines the sort order in the resulting table.

The SQL standard defines the concept of a NULL value, which made it necessary to use three-valued logic, where all logical operations are performed in accordance with the truth table below (Table 6).

table 6 . Truth table

			A AND B
TRUE

1.1. Simple queries

Request 1

Display information about the departments of the university.

This task is reduced to fetching and displaying information from one table, and all its rows and all its columns are subject to display:

SELECT * FROM kafedra

The result of executing such a query will be a table containing information about all departments of the university:

Kod kaf	Name kaf	Nom_telef	Nom_Auditoria	Col_sotr
					Ivanov T.M.
	General mathematics				Makhov K L.
					Ross L.T.
					Firsov S.S.
	Applied Mathematics				Lyakhova I.T.

Request 2

Display the phone numbers of the departments of the university.

The result of such a query should contain only two columns: Name_ kaf and Nom_ telef, so the request itself should look like this:

SELECT Name_kaf, Nom_telef FROM kafedra

Resulting table:

Namekaf Nomjelef

Physicists 23-34-24

General Mathematics 23-65-43

Stories 23-78-72

Charts 23-99-77

Applied Mathematics 23-66-62

In the queries generated above, it was required to display all rows of the table specified in the from clause. If during the selection it is required to limit the number of displayed rows in accordance with some condition, then this can be achieved by using the where clause in the query. One or more row selection conditions can be included in the where clause.

Request 3

Display information about the graphics department.

SELECT * FROM kafedra WHERE Name_kaf = "Charts"

The response to such a request will contain only one line:

Kod.kaf Name_kaf NomjelefNom_Auditoria Col_sotr Zav_kaf

004 Charts 23-99-77 385 18 Firsov C.C.

Request 4

Display information about the departments of the university located on the first floor, taking into account the fact that the rooms of the classrooms on the first floor range from 1 to 99.

The request will look like this:

SELECT * FROM kafedra WHERE Nom_Auditoria BETWEEN 1 AND 99

Query result:

KodjcafName_kafNorn lelef Norn Audit oria Coi_sotr Zavkaf

002 General Mat- 23-65-43 003 22 Makhov K.L.

mathematics

In general, the rows in the resulting table are displayed in some sort of unordered state. It is not always convenient to view and analyze such material. The order by clause is used to sort rows by any column. It includes a comma-separated list of the column names by which you want to order the displayed information. This phrase should always appear last in the select statement and, if present, it becomes possible to sort rows in ascending (asc) or descending (desc) values of the specified column or combination of specified columns, regardless of whether these columns are present in the resulting table or not.

Request 5

Display information about the departments of the university in the form sorted by the columnName_ kafin ascending order.

The request will look like this:

SELECT * FROM kafedra ORDER BY Name_caf ASC

The result of this query:

Kod_kaf Name_kaf Nomjelef Nom_Auditoria Col_sotr Zav kaf

004 Schedules 23-E9-77 385 18 Firsov S.S.

003 Stories 23-78-72 465 16 Ross L.T.

002 General ma- 23-65-43 003 22 Makhov K.L.

subject matter

005 Applied 23-66-62 028 24 Lyakhova I.T.