IN and BETWEEN operators in SQL. Basic SQL operators The in operator in the sql language performs
The IN operator allows you to specify a list of values, either entered explicitly or using a subquery, and compare a value to that list in a WHERE or HAVING clause. In other words, you might ask, “Is there a value A in this list of values?”
SQL 2003 syntax(WHERE | HAVING | (AND | OR)) value IN ((compare_value1, compare_value2 [, ...] | subquery))
Keywords(WHERE HAVING (AND | OR)) value
May be used with either a WHERE clause or a HAVING clause. A comparison included in an IN clause can also be used in an AND or OR clause of a WHERE or HAVING clause with multiple conditions. The value parameter can be of any data type, but is typically the name of the column referenced by the transaction, or perhaps a host variable if the value is used programmatically.
An additional clause that forces a result set to be composed of values not included in the list.
IN ((calculated_value1, calculated_value2 [, ...] | subquery))
A list of comparative values is determined that will be used for comparison. Each comparison value must be of the same or compatible type as the original value. These values follow the standard rules for data types. For example, string values must be enclosed in quotes, but integer values must not. As an alternative to specifying specific values, you can write a subquery in parentheses that returns one or more values of a compatible data type.
In the following SQL Server example, we search the employee table in the HR database for all employees who live in the states of Georgia, Tennessee, Alabama, or Kentucky.
SELECT * FROM employee WHERE home_state IN ("AL", "GA", "TN", "KY");
We can also find in the employee table of the HR database all the employees who are mentioned as authors in the PUBS database.
SELECT * FROM employees WHERE emp_id IN (SELECT au_id FROM authors);
You can also use the NOT keyword to create a result set based on the absence of a value. In the following example, the company is headquartered in New York City and many employees come from neighboring states. We want to see all such employees.
SELECT * FROM employee WHERE home_state NOT IN ("NY", "NJ", "MA", "CT", "RI", "DE", "NH");
Note that Oracle, while fully supporting ANSI functionality, extends the IN operator to allow comparison of multiple arguments. For example, in Oracle it is acceptable to use the following statement: SELECT... WHERE...IN.
SELECT * FROM employee e WHERE (e.emp_id, e.emp_dept) IN ((242, "sales"), (442, "mfg"), (747, "mkt))
SQL (ˈɛsˈkjuˈɛl; English structured query language - “structured query language”) is a declarative programming language used to create, modify and manage data in a relational database.
Compliance with SQL standards for different databases:
SQL (Structured Query Language - structured query language). SQL is primarily an information logical language designed to describe stored data, to retrieve stored data, and to modify data.
SQL is not a programming language. As SQL has become more complex, it has become more of an application programming language, and users are now able to use visual query builders.
SQL is a case-insensitive language. Strings in SQL are enclosed in single quotes.
The SQL language is a collection of statements. SQL statements are divided into:
Data Definition Language (DDL) - schema description language in ANSI, consists of commands that create objects (tables, indexes, views, etc.) in the database (CREATE, DROP, ALTER, etc.).
Data manipulation operators (Data Manipulation Language, DML) are a set of commands that determine what values are presented in tables at any time (INSERT, DELETE, SELECT, UPDATE, etc.).
data access definition statements (Data Control Language, DCL) - consists of tools that determine whether to allow the user to perform certain actions or not (GRANT/REVOKE, LOCK/UNLOCK).
Transaction Control Language (TCL) operators
Unfortunately, these terms are not used universally across all implementations. They are emphasized by ANSI and are useful on a conceptual level, but most SQL programs do little to treat them separately, so they essentially become functional categories of SQL commands.
SELECT section JOINSimple JOIN (=intersection JOIN =INNER JOIN) - means to show only the common records of both tables. How records are considered shared is determined by the fields in the join expression. For example, the following entry: FROM t1 JOIN t2 ON t1. id = t2. id
means that records with the same id that exist in both tables will be shown.
LEFT JOIN (or LEFT OUTER JOIN) means to show all records from the left table (the one that comes first in the join expression) regardless of whether there are corresponding records in the right table. If there are no records in the right table, the empty value NULL is set.
RIGHT JOIN (or RIGHT OUTER JOIN) acts in contrast to LEFT JOIN - it shows all records from the right (second) table and only those that match from the left (first) table.
Other types of JOIN joins: MINUS - subtraction; FULL JOIN - complete join; CROSS JOIN - “Everyone with everyone” or the Cartesian product operation.
INSERT IGNORE If you specify the IGNORE keyword in an INSERT command with rows that have multiple values, then any rows that have duplicate PRIMARY or UNIQUE keys in that table will be ignored and not inserted. If you do not specify IGNORE, this insert operation aborts when it encounters a row that has a duplicate value for an existing key
The REPLACE command differs from INSERT only in that if there is a record in the table with the same value in the indexed field (unique index) as the new record, then the old record is deleted before adding the new one.
UPDATE< tablename>SET ( | ) .,. .< COLUMN name> = < VALUE expresslon>[WHERE< predlcate>| WHERE CURRENT OF< cursor name>(*only for attachment*) ] ; UPDATE peers SET zone= "voip" ; # update all rows in the zone column of the peers table UPDATE stat SET whp= "13x13x13" WHERE id = 1 ; UPDATE countries SET nm_ukr= ( SELECT del_countries. ukrainian FROM del_countries WHERE countries. nm_en= del_countries. english ) ;WordPress usage, setting: in the wp_posts table, delete all occurrences of the row
UPDATE wp_posts SET post_content = REPLACE (post_content, "" , "" ) ;DELETE FROM
Num | Month | Year | Sname | City | Address | Debt |
0001 | July | 2012 | Ivanov | Stavropol | Stavropolskaya, 1 | 50000 |
0002 | December | 2019 | Kononov | Tatar | Zagorodnaya, 254 | 684068 |
0003 | May | 2013 | Yamshin | Mikhailovsk | Selskaya, 48 | 165840 |
0004 | August | 2012 | Preney | Stavropol | Central, 16 | 46580 |
... | ... | ... | ... | ... | ... | ... |
9564 | March | 2015 | Ulieva | Demino | International, 156 | 435089 |
9565 | October | 2012 | Pavlova | Stavropol | Vokzalnaya, 37 | 68059 |
9566 | January | 2012 | Uryupa | Mikhailovsk | Fontannaya, 19 | 51238 |
9567 | November | 2017 | Valetov | Tatar | Vyezdnaya, 65 | 789654 |
Suppose you need to select all debtors of the city of Stavropol or Tatarka. By analogy with the previous entry, you would need to use the request
SELECT *
FROM Debtors
WHERE City = "Stavropol"
OR City = "Tatarka";
First of all, the resulting code is cumbersome. By using special operators, you can get more compact code.
SELECT *
FROM Debtors
WHERE City IN (“Stavropol”, “Tatarka”);
The result will be
Let's follow the logic of the program. With the SELECT, FROM and WHERE keywords. But then the IN operator appears. It sets the program a sequence of actions - it is necessary to view the database information contained in the "City" column. And to display you need to select the data “Stavropol” and “Tatarka”.
Let me consider an example in which you need to make a selection based on certain amounts of debt.
SELECT *
FROM Debtors
WHERE Debt IN (435089, 789654, 684068);
The result will be the following
Those. the IN operator scans the entire database for the presence of the specified information selection parameters.
This is not the case when using another special operator, BETWEEN. If the IN operator considered information with exclusively specified parameters, then the BETWEEN operator considered information between certain ranges. However, an analogy should not be drawn between the English translation of this operator and its actual purpose. If you specify BETWEEN 1 AND 5, this does not mean that the numbers 2, 3 and 4 will be true. This operator is simply perceived by SQL as a certain value that can be found among other values. In an example it will look like this.
SELECT *
FROM Debtors
WHERE Debts BETWEEN 30000 AND 100000;
The result will be
That is, SQL interpreted the BETWEEN statement as any value in the range from 30000 to 100000 for the Debts column.
In addition to specifying approximate ranges in numerical terms, you can specify alphabetical ranges, which display information containing the first letters of the specified range. But there is one interesting point here. Let's create the following request
SELECT *
FROM Debtors
WHERE Sname BETWEEN "I" AND "P";
Then the following data will be displayed
A logical question: “Why did debtors with the surnames Preni and Pavlova drop out of the list? After all, the first letters of their surnames are included in the specified range!” Letters are included, but last names are not. This is due to the fact that the SQL language in this kind of queries only accepts the length of the search strings that are specified. In other words, the length of the string "P" in the query is one character, but the length of the string "Preni" and "Pavlova" in the database is five and seven, respectively. But the surname “I Vanov” falls into the range, since the range begins with I, as a beginning, with a length of one character.
The SQL language standard was adopted in 1992 and is still in use today. It was this that became the standard for many. Of course, some manufacturers use their own interpretations of the standard. But any system still has the main components - SQL statements.
IntroductionUsing SQL statements, values and tables are managed and obtained for further analysis and display. They are a set of keywords by which the system understands what to do with the data.
Several categories of SQL statements are defined:
- defining database objects;
- manipulation of values;
- protection and control;
- session parameters;
- information about the database;
- static SQL;
- dynamic SQL.
INSERT. Inserts rows into an existing table. It can be used for one value or several, defined by a certain condition. For example:
table name (column name 1, column name 2)
VALUES(value 1, value 2).
To use the INSERT statement on multiple values, use the following syntax:
table name 1 (column name 1, column name 2)
SELECT column name 1, column name 2
FROM table name 2
WHERE table name 2.column name 1>2
This query will select all data from table 2 that is greater than 2 in column 1 and insert it into the first one.
UPDATE. As the name suggests, this SQL query statement updates data in an existing table based on a specific feature.
UPDATE table name 1
SET column name 2 = "Vasily"
WHERE table name 1.column name 1 = 1
This construction will fill with the value Vasily all lines in which it encounters the number 1 in the first column.
Data from the table. You can specify a condition or remove all lines.
DELETE FROM table name
WHERE table name.column name 1 = 1
The above query will remove from the database all data with the value one in the first column. Here's how you can clear the entire table:
SELECT statementThe main purpose of SELECT is to select data according to certain conditions. The result of his work is always a new table with selected data. The MS operator can be used in a variety of different queries. Therefore, along with it, you can consider other related keywords.
To select all data from a specific table, use the “*” sign.
FROM table name 1
The result of this query will be an exact copy of table 1.
And here a selection is made using the WHERE condition, which retrieves from table 1 all values greater than 2 in column 1.
FROM table name 1
WHERE table name 1.column name 1 > 2
You can also specify in the selection that only certain columns are needed.
SELECT table name 1.column name 1
FROM table name 1
The result of this query will be all rows with values from column 1. Using MS SQL statements, you can create your own table, replacing, calculating and substituting certain values on the fly.
table name 1.column name 1
table name 1.column name 2
table name 1.column name 3
table name 1.column name 2 * table name 1.column name 3 AS SUMMA
FROM table name 1
This seemingly complex query retrieves all values from Table 1, then creates new columns EQ and SUMMA. The first is filled with the “+” sign, and the second is the product of the data from columns 2 and 3. The result obtained can be presented in the form of a table to understand how it works:
When using the SELECT statement, you can immediately sort the data according to any criterion. The word ORDER BY is used for this.
table name 1.column name 1
table name 1.column name 2
table name 1.column name 3
FROM table name 1
ORDER BY column name 2
The resulting table will look like this:
That is, all rows were set in such an order that the values in column 2 were in ascending order.
Data can also be obtained from several tables. For clarity, you first need to imagine that there are two of them in the database, something like this:
Table "Employees"
Table "Salary"
Now we need to somehow connect these two tables to obtain common values. Using basic SQL statements you can do this like this:
Employees.Number
Employees.Name
Salary.Rate
Salary.Accrued
FROM Employees, Salary
WHERE Employees.Number = Salary.Number
Here we select from two different tables of values, combined by number. The result will be the following data set:
A little more about SELECT. Using Aggregate FunctionsOne of the main operators can perform some calculations when fetching. To do this, he uses certain functions and formulas.
For example, to get the number of records from the Employees table, you need to use the query:
SELECT COUNT (*) AS N
FROM Employees
The result is a table with one value and a column.
You can run a query like this and see what happens:
SUM(Salary. Accrued) AS SUMMA
MAX(Salary. Accrued) AS MAX
MIN(Salary. Accrued) AS MIN
AVG(Salary. Accrued) AS SRED
FROM Salary
The final table will be like this:
In this way, you can select the desired values from the database, calculating various functions on the fly.
Union, intersection and differences
Combine multiple queries in SQL
SELECT Employees.Name
FROM Employees
WHERE Employees.Number = 1
SELECT Employees.Name
FROM Employees, Salary
WHERE Salary.Number = 1
It should be borne in mind that with such a union, the tables must be compatible. That is, have the same number of columns.
SELECT statement syntax and processing orderThe first thing SELECT does is determine the area from which it will take data. The FROM keyword is used for this. If it is not indicated what exactly to choose.
Then the SQL WHERE clause may be present. With its help, SELECT runs through all the rows of the table and checks the data for compliance with the condition.
If the query contains GROUP BY, then the values are grouped according to the specified parameters.
Operators for comparing dataThere are several types. In SQL, comparison operators can test different types of values.
"=". Denotes, as you might guess, the equality of two expressions. For example, it has already been used in the examples above - WHERE Salary.Number = 1.
">". More sign. If the value of the left side of the expression is greater, then the logical TRUE is returned and the condition is considered satisfied.
«