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 JOIN

    Simple 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.

SELECT JOIN SUBSTRING INSERT INTO users_auth VALUES (default, "root" , MD5("root"));

    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

INSERT IGNORE INTO prices VALUES(DEFAULT, 1, 1111, "Fr", 50, 50, 1, 1)

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

[ WHERE | WHERE CURRENT OF (*attachment only*) ];

DELETE FROM Peers; // will delete all contents of the Peers table. DELETE FROM FinR where day Like "20120415%"; // DELETE FROM prices WHERE ratesheet_id NOT IN (SELECT id FROM ratesheets);

ALTER

    Changing the default value for a column. To set a new default value for a column, use a command like: ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77 ; OR ALTER TABLE nases ALTER COLUMN zone SET DEFAULT "voip" ;

    Note that running this command does not affect existing rows in the table; the command will only change the default value for future INSERT commands. To remove any default value, use

    ALTER TABLE products ALTER COLUMN price DROP DEFAULT ;

    The command does the same thing as setting the default value to null. Since deleting a default value implicitly sets it to null, no errors will be reported if an existing default value is deleted.

    Function as the default value for the column. In this case, the timetracking column has a timestamp data type, which means that its default value can be set to the built-in now() function, i.e. when adding a new row to the column, the current date and time will be recorded ALTER TABLE timetracking ALTER COLUMN date_wd SET DEFAULT now();

    Adding a constraint. To add a constraint, the table syntax for defining that constraint is used. For example: ALTER TABLE products ADD CHECK (name ""); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

    To add a non-null constraint that cannot be written as a table constraint, use the syntax:

    ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

    The specified constraint will be enforced immediately, so the data in the table must satisfy it before adding the constraint.

Aggregation Functions

There are 5 aggregate functions in standard SQL:

    COUNT - the function returns the number of rows that meet certain criteria.,

    SUM - returns the sum (total) of the values ​​in a specific column. Column rows with NULL values ​​are ignored by the SUM function.

    AVG - average value in the column,

Aggregation functions are used as field names in a SELECT query clause, with one exception: field names are used as arguments. The SUM and AVG functions can only work with numeric fields. The COUNT, MAX, MIN functions work with both numeric and character fields. When applied to character fields, the MAX and MIN functions can work with the ASCII equivalents of the characters.

SELECT Count(Books.ID) AS [Number of Books] FROM Books;

Using CROUP BY allows you to apply aggregate functions to groups of records.

SELECT Count(Books.ID) AS [Number of Books] FROM Books GROUP BY [Writer];

Views (VIEW)

A VIEW is a data object that does not contain any data about its owner. It is a type of table whose contents are retrieved from other tables by running a query.

Base tables are tables that contain data. However, there is another type of table: - views (VIEW). Views are tables whose content is selected or obtained from other tables. They work in queries and DML statements just like main tables, but do not contain any data of their own. Views are like windows through which you view information that is actually stored in the underlying table.

    CREATE VIEW command. The view is created with the CREATE VIEW command. It consists of the words CREATE VIEW, the name of the view that needs to be created, the word AS (HOW), and then the query. Let's create the Londonstaff view: CREATE VIEW Londonstaff AS SELECT * FROM Salespeople WHERE city = "London";

    This view is used just like any other table. It can be queried, modified, inserted into, deleted from, and joined to other tables and views. Request for submission.

    Any query created for work in the database simplifies access to the necessary information. In the previous post I talked about common conditional statements. In this same post, I will talk about operators that will allow you to create queries that can provide more detailed information of interest, which, at the same time, is not so easy to find with queries with the AND, OR operators.
    One of the special operators is IN. This operator allows you to set the required range for displaying the required information. Let's return to the data on debtors

    Debtors

NumMonthYearSnameCityAddressDebt
0001 July2012 IvanovStavropolStavropolskaya, 150000
0002 December2019 KononovTatarZagorodnaya, 254684068
0003 May2013 YamshinMikhailovskSelskaya, 48165840
0004 August2012 PreneyStavropolCentral, 1646580
... ... ... ... ... ... ...
9564 March2015 UlievaDeminoInternational, 156435089
9565 October2012 PavlovaStavropolVokzalnaya, 3768059
9566 January2012 UryupaMikhailovskFontannaya, 1951238
9567 November2017 ValetovTatarVyezdnaya, 65789654

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.

Introduction

Using 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.
SQL statements for data manipulation

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 statement

The 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 Functions

One 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 order

The 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 data

There 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.

    «







2024 gtavrl.ru.