Menu
Is free
registration
home  /  Advice/ Formulate a complex query consisting of several. Query language

Formulate a complex query consisting of several. Query language

The main topics of the paragraph:

♦ three ways to search the Internet;
♦ search servers;
♦ language of search engine queries.

Three ways to search the Internet

Search engine query language

Group keywords, formed according to certain rules - using the query language, is called a query to the search server. The languages ​​used for queries to different search engines are very similar. You can learn more about this by visiting the "Help" section of the required search server. Let's consider the rules for forming queries using the example of the search engine Yandex.

Operator syntax What does operator mean Request example
space or & Logical AND (within a sentence) physiotherapy
&& Logical AND (within a document) recipes && (processed cheese)
| Logical OR photo | photography | snapshot | photographic image
+ Mandatory presence of a word in the found document + to be or + not to be
() Grouping words (technology | production) (cheese | cottage cheese)
~ Binary operator AND NOT (within a sentence) banks ~ law
~~
or
-
Binary operator AND NOT (within the document) Paris travel guide ~~ (agency | tour)
/ (n m) Distance in words (minus (-) - back, plus (+) - forward) vendors / 2 coffees
music / (- 2 4) education
vacancies - / + 1 students
“ ” Phrase search "Little Red Riding Hood"
Equivalent: red
/ + 1 hat
&& / (n m) Distance in sentences
(minus (-) - back,
plus (+) - forward)
bank && / 1 taxes

To get the best search results, there are a few simple rules to remember:

1. Do not search for information by only one keyword.
2. It is better not to enter keywords with an uppercase letter, as this may lead to the fact that the same words written with a lowercase letter will not be found.
3. If you don't get any results in your search, check your keywords for spelling errors.

Modern search engines provide the ability to connect a semantic analyzer to the generated query. With it, you can, by entering a word, select documents in which derivatives of this word are found in various cases, tenses, etc.

Questions and tasks

1. What are the three main ways to find information on the World Wide Web?
2. How do links to specific documents end up in search engines?
3. Formulate a complex query, consisting of several keywords, using the query language of the Yandex system.

What You Should Learn From Chapter 1

Exchange information with the server local network school computer class.
Send and receive emails by e-mail.
Order news from teleconferences.
Receive information from a Web page for which you know the address.
Search for information on the web using search programs.

Edited Date: Saturday, 29 June 2019

To find the information you are interested in, you need to indicate to the browser the address of the Web page on which it is located. This is the fastest and most reliable type of search. For quick access to the resource, it is enough to launch a browser and type the page address in the address bar.

The addresses of Web pages are given in special reference books, printed publications, and are heard on the air of popular radio stations and from TV screens.

If you do not know the address, then to find information on the Internet there are search engines, which contain information about Internet resources.

Each search engine has a large database of keywords associated with the Web pages on which they appear. To search for the server address with the information you are interested in, you must enter a keyword, a few words or a phrase in the search engine field. By doing so, you send search engine inquiry. Search results are returned as a list of Web page addresses that contain these words.

Typically, search engines consist of three parts: a robot, an index, and a query processing program.

A robot (Spider, Robot or Bot) is a program that visits web pages and reads (in whole or in part) their content.

Search engine robots differ in the individual scheme for analyzing the content of a web page.

Index is a data warehouse that contains copies of all pages visited by robots.

Indexes in each search engine differ in the volume and method of organizing the stored information. Databases of the leading search engines store information on tens of millions of documents, and their index is hundreds of gigabytes. Indexes are periodically updated and supplemented, so the results of the work of one search engine with the same query may differ if the search was made at different times.

A query processor is a program that, in response to a user request, "scans" the index for the information it needs and returns links to the documents it finds.

A set of links at the output of the system is distributed by the program in descending order from the highest degree of matching the link to the request to the smallest one.

In Russia, the most common search engines are:

    Rambler (www.rambler.ru);

    Yandex (www.yandex.ru);

    Mail (www.mail.ru).

There are many more search engines abroad. The most popular are:

    Alta Vista (www.altavista.com);

    Fast Search (www.alltheweb.com);

    Google(www.google.com).

Yandex is perhaps the best search engine in Russian Internet... This database contains about 200,000 servers and up to 30 million documents, which the system views within a few seconds. Using this system as an example, we will show how the search for information is carried out.

Search for information is set by entering a keyword into a special frame and clicking the "Find" button to the right of the frame.

Search results appear within seconds, and are ranked by importance - the most important documents are placed at the top of the list. In this case, the rank of the found document is determined by where the keyword is located in the document (it is more important in the title of the document than in any other place) and the number of mentions of the keyword (the more mentions, the higher the rank).

Thus, the sites located in the first places in the list are leading not from a content point of view, but practically, in relation to the frequency of the keyword. In this regard, one should not be limited to viewing the first ten sites suggested by the search engine.

The content of the site can be indirectly assessed by its short description posted by the Search engine under the address of the Site. Some unscrupulous authors of sites, in order to increase the likelihood of their Web page appearing in the first places of the Search engine, deliberately include in the document meaningless repetitions of the keyword. But as soon as the search engine finds such a "littered" document, it automatically excludes it from its database.

Even a ranked list of documents offered by a search engine in response to a key phrase or word can be nearly overwhelming. In this regard, Yandex (like other powerful Search Engines) provides an opportunity, within the first list, to select documents that more accurately reflect the purpose of the search, that is, to clarify or improve the search results. For example, a keyword is a list of 34,899 Web pages. Entering a lookup keyword into the Look in Found command shrinks the list to 750 pages, and typing another lookup word into the command shrinks the list to 130 Web pages.

They say that the Internet has it all. In fact, it is not so. Materials for posting on the network are prepared by real people, and therefore you can find there only what they saw fit to publish. However, the river is fed by streams, and, thanks to their work, today there are about two billion Web pages on the Internet. As a result, cataloging the resources available on the web has become a serious problem. Despite the fact that thousands of organizations are dealing with the problem, it is not only not getting closer to resolution, but is becoming more acute. The percentage of cataloged resources has been steadily declining. In recent years, this decline has become catastrophic. So, if in 1999 the percentage of cataloged resources approached 40%, then in just one next year it dropped to 25%.

The takeaway is simple: the Web space fills up faster than it gets organized.

Unfortunately, we have no reason to believe that anything can change for the better in the near future. As a result, finding information on the Internet can be considered one of the most difficult tasks.

To find the information you are interested in, you need to indicate to the browser the address of the Web page on which it is located. This is the fastest and most reliable type of search. For quick access to the resource, just launch a browser and type the page address in the address bar.

The addresses of Web pages are given in special reference books, printed publications, and are heard on the air of popular radio stations and from TV screens.

If you do not know the address, then to find information on the Internet there are search engines, which contain information about Internet resources.

Each search engine has a large database of keywords associated with the Web pages on which they appear. To search for the server address with the information you are interested in, you must enter a keyword, a few words or a phrase in the search engine field. Thus, you send a request to the search engine. Search results are returned as a list of Web page addresses that contain these words.

Typically, search engines have three parts: robot, index and request processing program.

A robot (Spider, Robot or Bot) is a program that visits web pages and reads (in whole or in part) their content.

Search engine robots differ in the individual scheme for analyzing the content of a web page.

Index is a data warehouse that contains copies of all pages visited by robots.

Indexes in each search engine differ in the volume and method of organizing the stored information. Databases of the leading search engines store information on tens of millions of documents, and their index is hundreds of gigabytes. Indexes are periodically updated and supplemented, so the results of the work of one search engine with the same query may differ if the search was made at different times.

A query processor is a program that, in response to a user request, "scans" the index for the information it needs and returns links to the documents it finds.

A set of links at the output of the system is distributed by the program in descending order from the highest degree of matching the link to the request to the smallest one.

In Russia, the most common search engines are:

  • Rambler (www.rambler.ru);
  • Yandex (www.yandex.ru);
  • Mail (www.mail.ru).

There are many more search engines abroad. The most popular are:

  • Alta Vista (www.altavista.com);
  • Fast Search (www.alltheweb.com);
  • Northern Light (www.northernlight.com).

Yandex is perhaps the best search engine on the Russian Internet. This database contains about 200,000 servers and up to 30 million documents, which the system views within a few seconds. Using this system as an example, we will show how the search for information is carried out.

Search for information is set by entering a keyword into a special frame and clicking the "Find" button to the right of the frame.

Search results appear within seconds, and are ranked by importance - the most important documents are placed at the top of the list. In this case, the rank of the found document is determined by where the keyword is located in the document (it is more important in the title of the document than in any other place) and the number of mentions of the keyword (the more mentions, the higher the rank).

Thus, the sites located in the first places in the list are leading not from a content point of view, but practically, in relation to the frequency of the keyword. In this regard, one should not be limited to viewing the first ten sites suggested by the search engine.

The content of the site can be indirectly assessed by its short description posted by the Search engine under the address of the Site. Some unscrupulous authors of sites, in order to increase the likelihood of their Web page appearing in the first places of the Search engine, deliberately include in the document meaningless repetitions of the keyword. But as soon as the search engine finds such a "littered" document, it automatically excludes it from its database.

Even a ranked list of documents offered by a search engine in response to a key phrase or word can be nearly overwhelming. In this regard, Yandex (like other powerful Search Engines) provides an opportunity, within the first list, to select documents that more accurately reflect the purpose of the search, that is, to clarify or improve the search results. For example, a keyword is a list of 34,899 Web pages. Entering a lookup keyword into the Look in Found command shrinks the list to 750 pages, and typing another lookup word into the command shrinks the list to 130 Web pages.

Search directories are a systematic collection (selection) of links to other Internet resources. Links are organized as thematic rubricator, npe, which is a hierarchical structure, moving through which you can find the information you need.

Let's take as an example the structure of the Yandex.

This is a general purpose directory, as it contains links to Internet resources in almost all possible directions. The following are highlighted in this directory themes:

  • Business and Economics;
  • Society and Politics;
  • Science and education;
  • Computers and communications;
  • References and links;
  • Home and family;
  • Entertainment and recreation;
  • Culture and art.

Each theme includes many subsections , and they, in turn, contain rubrics etc.

Suppose you are preparing an event for the Victory Day and want to find the words of the famous military song by Bulat Okudzhava “You hear the boots rumbling” on the Internet. The search can be organized as follows:

Yandex> Catalog> Culture and art> Music> Author's song

This search method is quite fast and efficient. At the end, you are offered just a few links, among which there are links to sites with songs of famous bards. It remains only to find an archive with the lyrics of B. Okudzhava's songs on the website and select the desired text in it.

Another example. Suppose you are going to buy a mobile phone and want to compare the characteristics of devices from different companies. The search could be carried out on the following headings of the catalog :.

Yandex> Catalog> Computers and communications> mobile connection> Mobile phones

Having received a limited number of links, you can quickly view them and select a phone by examining the characteristics of the firms and modifications of the devices.

Most search engines have the ability to search for keywords. This is one of the most common types of searches.

To search for keywords, you need to enter a word or several words that you want to search in a special window and click on the Find button.

The search engine will find in its database and show documents containing these words. There may be many such documents, but many in this case does not necessarily mean good.

Let's do some experiments with any of the search engines. Suppose that we decided to start an aquarium and we are interested in any information on this topic. At first glance, the simplest thing is to search for the word "aquarium". Let's check this, for example, in the Yandex search engine. The search result will be great amount pages - a huge number of links. Moreover, if you look more closely, among them there will be sites mentioning B. Grebenshchikov's group "Aquarium", shopping centers and informal associations with the same name, and much more that has nothing to do with aquarium fish.

It is easy to guess that such a search cannot satisfy even an unassuming user. Too much time will have to be spent trying to select among all the proposed documents those that relate to the subject we need, and even more so to familiarize ourselves with their contents.

You can immediately conclude that it is usually impractical to search for one word, because it is very difficult to determine the topic of a document, web page or site from one word. The exception is rare words and terms that are almost never used outside their subject area.

Having a certain set of the most common terms in desired area, you can use advanced search. In fig. shows the advanced search window in the Yandex search engine. In this mode, the capabilities of the query language are implemented as a form. Similar service, which includes dictionary filters, is offered by almost all search engines.

Let's try to clarify the search terms and enter the phrase "aquarium fish". The number of links will decrease by more than 20 times. This result suits us more, but all the same, among the proposed links we may find, for example, Russian souvenir sets of matchsticks with images of fish, and collections of screensavers for the Desktop of a computer, and catalogs of aquarium fish with photos, and stores of aquarium accessories. Obviously, we should continue to move towards clarifying the search conditions.

In order to make the search more productive, all search engines have a special query language with its own syntax. These languages ​​are similar in many ways. All of them are difficult to learn, but any search engine has a help system that will allow you to master the language you want.

Here are some simple rules for generating a query in the Yandex search engine.

The keywords in the query should be written in lowercase (small) letters.

This will ensure that all keywords are searched, not just those that start with an uppercase letter.

The search takes into account all forms of a word according to the rules of the Russian language, regardless of the form of the word in the query.

For example, if the word “know” was specified in the query, then the words “know”, “know”, etc. will also satisfy the search term.

To search for a stable phrase, you must enclose the words in quotation marks.

For example, "china tableware".

To search for an exact word form, an exclamation mark must be placed in front of the word.

For example, to search for the word "September" in the genitive case, write "! September".

To search within one sentence, the words in the query are separated by a space or by &

For example, "adventure romance" or "adventure & romance". Several words typed in the query, separated by spaces, mean that they must all be included in one sentence of the required document.

If you want to select only those documents that contain each word specified in the request, put a plus sign "+" in front of each of them. If, on the contrary, you want to exclude any words from the search result, put a minus "-" in front of this word. The signs "+" and "-" must be written through a space from the previous one and merged with the next word.

For example, the query “Volga-car” will find documents that contain the word “Volga” and no word “car”.

When searching for synonyms or words that are close in meaning, you can put a vertical bar "|" between words.

For example, by request “child | baby | baby ”documents with any of these words will be found.

Instead of one word in the query, you can substitute an entire expression. To do this, it must be enclosed in brackets.

For example, "(child | toddler | children | infant) + (care | upbringing)".

The "~" (tilde) character allows you to find documents with a sentence containing the first word but not the second.

For example, the query "books ~ store" will find all documents containing the word "books", next to which (within the sentence) there is no word "store".

If the operator is repeated once (for example, & or ~), the search is performed within the clause. The double operator (&&, ~~) searches within the document.

For example, the query "cancer ~~ astrology" will find documents with the word "cancer" that are not related to astrology.

Let's go back to the aquarium fish example. After reading several documents offered by the search engine, it becomes clear that the search for information on the Internet should not start with the selection of aquarium fish. An aquarium is a complex biological system, the creation and maintenance of which requires special knowledge, time and serious investment.

Based on the information received, a person performing a search on the Internet can radically change the strategy for further search by deciding to study the special literature related to the issue under study.

To search for literature or full-text documents, the following query is possible:

"+ (Aquarium | aquarist | aquarium hobbyist) + for beginners + (advice | literature) + (article | thesis | full-text) - (price | shop | shipping | catalog)".

After processing the request by the search engine, the result was very successful. Already the first links lead to the documents you are looking for.

Now you can summarize the search results, draw certain conclusions and decide on possible actions:

  • Stop further search, as for various reasons you cannot afford to maintain an aquarium.
  • Read the suggested articles and start setting up your aquarium.
  • Look for material about hamsters or budgerigars.
  1. What kind of search is the fastest and most reliable?
  2. Where can a user find the URLs of Web pages?
  3. What is the main purpose of a search engine?
  4. What are the parts of a search engine?
  5. What search engines do you know?
  6. What is the search technology for a search engine rubricator?
  7. What is the keyword search technology?
  8. When should a + or - be specified in the search criteria?
  9. What search criteria in Yandex are specified by the following phrase:

    (nanny | educator | governess) ++ (care | upbringing | supervision) ?

  10. What does double sign (~~ or ++) mean when generating a complex query?

Exercise.

Task 1. Search in directories.

Using the directory of the search engine, find the following information (as directed by the teacher):

  1. Lyrics of a popular music group
  2. Repertoire of the Mariinsky Theater this week
  3. Specifications latest model mobile phone a well-known company (of your choice)
  4. Recipe for cooking Ukrainian borscht with dumplings
  5. Long-term weather forecast for your area (at least 10 days)
  6. Photo of your favorite contemporary song artist
  7. The approximate cost of a multimedia computer (price)
  8. Information about secretarial vacancies in your area or city
  9. Horoscope of your zodiac sign for the current day

Based on the search results, compose a written report in Word: present the found, copied and formatted material in the document. Present the report to the teacher.

Task 2. Formation of a request for the exact title or quote.

You know the exact title of the document, for example "Hygienic Requirements for Personal Electronic Computing Machines and Organization of Work". Formulate your query to search the Internet full text document.

Save the search result in your folder. Present to the teacher.

Task 3. Formation of complex queries.

  • In any search engine, make a request to find information about the Russian bath. Eliminate service offers, bath product advertisements, and other advertisements. Concentrate your search on the effect of the Russian bath on the body.
  • Create a complex query to find information on caring for domestic cats. Exclude large cats (for example, lions) from your search, as well as offers to buy, sell, wallpaper photos, etc.
  • Fill out the query text and the search result in Word and present it to the teacher.
Task 4. Thematic search.

By all means known to you, search the Internet for information on the history of the development of computing. Search in different directions: historical setting, technology, personality. Format the search results in the form of a presentation. Use a multi-stage table of contents in the form of hyperlinks in your presentation.

Requests are written without escaping quotes, since MySQL, MS SQL and PostGree they are different.

SQL query: getting specified (required) fields from a table

SELECT id, country_title, count_people FROM table_name

We get a list of records: ALL countries and their population. The names of the required fields are separated by commas.

SELECT * FROM table_name

* denotes all fields. That is, there will be impressions EVERYTHING data fields.

SQL query: output records from a table excluding duplicates

SELECT DISTINCT country_title FROM table_name

We get a list of records: the countries where our users are located. There can be many users from one country. In this case, this is your request.

SQL query: displaying records from a table according to a specified condition

SELECT id, country_title, city_title FROM table_name WHERE count_people> 100000000

We get a list of records: countries where the number of people is more than 100,000,000.

SQL query: displaying records from a table with ordering

SELECT id, city_title FROM table_name ORDER BY city_title

We get a list of records: cities in alphabetical order. At the beginning of A, at the end of I.

SELECT id, city_title FROM table_name ORDER BY city_title DESC

We get a list of records: cities in reverse ( DESC) ok. At the beginning I, at the end A.

SQL query: counting the number of records

SELECT COUNT (*) FROM table_name

We get the number (number) of records in the table. In this case, there is NO list of records.

SQL query: output the required range of records

SELECT * FROM table_name LIMIT 2, 3

We get 2 (second) and 3 (third) records from the table. The request is useful when creating navigation on WEB pages.

SQL queries with conditions

Output of records from a table according to a given condition using logical operators.

SQL query: AND construction

SELECT id, city_title FROM table_name WHERE country = "Russia" AND oil = 1

We get a list of records: cities from Russia AND have access to oil. When the operator is used AND, then both conditions must match.

SQL query: OR construct

SELECT id, city_title FROM table_name WHERE country = "Russia" OR country = "USA"

We get a list of records: all cities from Russia OR USA. When the operator is used OR, then at least one condition must match.

SQL query: AND NOT construct

SELECT id, user_login FROM table_name WHERE country = "Russia" AND NOT count_comments<7

We get a list of records: all users from Russia AND who made NOT LESS 7 comments.

SQL query: IN (B) construct

SELECT id, user_login FROM table_name WHERE country IN ("Russia", "Bulgaria", "China")

We get a list of records: all users who live in ( IN) (Russia, or Bulgaria, or China)

SQL query: NOT IN construction

SELECT id, user_login FROM table_name WHERE country NOT IN ("Russia", "China")

We get a list of records: all users who do not live in ( NOT IN) (Russia or China).

SQL query: IS NULL construct (empty or NOT empty values)

SELECT id, user_login FROM table_name WHERE status IS NULL

We get a list of entries: all users where status is not defined. NULL is a separate topic and is therefore checked separately.

SELECT id, user_login FROM table_name WHERE state IS NOT NULL

We get a list of records: all users where status is defined (NOT ZERO).

SQL query: LIKE construct

SELECT id, user_login FROM table_name WHERE surname LIKE "Ivan%"

We get a list of records: users whose last name begins with the combination "Ivan". The% sign means ANY number of ANY characters. To find the% sign, you need to use the escaping "Ivan \%".

SQL query: BETWEEN construct

SELECT id, user_login FROM table_name WHERE salary BETWEEN 25000 AND 50000

We get a list of records: users who receive salaries from 25,000 to 50,000 inclusive.

There are a LOT of logical operators, so study the SQL server documentation in detail.

Complex SQL queries

SQL query: combining multiple queries

(SELECT id, user_login FROM table_name1) UNION (SELECT id, user_login FROM table_name2)

We get a list of records: users who are registered in the system, as well as those users who are registered on the forum separately. Multiple queries can be combined with the UNION operator. UNION acts like SELECT DISTINCT, that is, it discards duplicate values. To get absolutely all records, you need to use the UNION ALL operator.

SQL query: counting field values ​​MAX, MIN, SUM, AVG, COUNT

Output of one, the maximum value of the counter in the table:

SELECT MAX (counter) FROM table_name

Output of one, the minimum value of the counter in the table:

SELECT MIN (counter) FROM table_name

Displaying the sum of all counter values ​​in the table:

SELECT SUM (counter) FROM table_name

Displaying the average value of the counter in the table:

SELECT AVG (counter) FROM table_name

Displaying the number of counters in the table:

SELECT COUNT (counter) FROM table_name

Display of the number of counters in workshop No. 1, in the table:

SELECT COUNT (counter) FROM table_name WHERE office = "Shop # 1"

These are the most popular commands. It is recommended, where possible, to use SQL queries of this kind for calculating, since no programming environment can compare in data processing speed than the SQL server itself when processing its own data.

SQL query: grouping records

SELECT continent, SUM (country_area) FROM country GROUP BY continent

We get a list of records: with the name of the continent and with the sum of the areas of all their countries. That is, if there is a directory of countries where each country has its area recorded, then using the GROUP BY clause, you can find out the size of each continent (based on the grouping by continent).

SQL query: using multiple tables via alias

SELECT o.order_no, o.amount_paid, c.company FROM orders AS o, customer AS with WHERE o.custno = c.custno AND c.city = "Tyumen"

We get a list of records: orders from customers who live only in Tyumen.

In fact, with a properly designed database of this type, the query is the most frequent, therefore a special operator was introduced in MySQL, which works many times faster than the code written above.

SELECT o.order_no, o.amount_paid, z.company FROM orders AS o LEFT JOIN customer AS z ON (z.custno = o.custno)

Nested subqueries

SELECT * FROM table_name WHERE salary = (SELECT MAX (salary) FROM employee)

We get one record: information about the user with the maximum salary.

Attention! Nested subqueries are one of the bottlenecks in SQL servers. Together with their flexibility and power, they also significantly increase the load on the server. Which leads to a catastrophic slowdown in the work of other users. Cases of recursive calls with nested queries are very common. Therefore, I strongly recommend NOT to use nested queries, but to break them into smaller ones. Or use the above LEFT JOIN combination. In addition to this type of request, requests are an increased hotbed of security breaches. If you decide to use nested subqueries, then you need to design them very carefully and make the initial runs on copies of databases (test databases).

SQL queries changing data

SQL query: INSERT

Instructions INSERT allow you to insert records into a table. In simple words, create a line with data in the table.

Option number 1. The instruction is often used:

INSERT INTO table_name (id, user_login) VALUES (1, "ivanov"), (2, "petrov")

In the table " table_name"2 (two) users will be inserted at once.

Option number 2. It is more convenient to use the style:

INSERT table_name SET id = 1, user_login = "ivanov"; INSERT table_name SET id = 2, user_login = "petrov";

This has its advantages and disadvantages.

Main disadvantages:

  • Many small SQL queries run slightly slower than one large SQL query, but other queries will be queued for service. That is, if a large SQL query is executed for 30 minutes, then during all this time the rest of the queries will smoke bamboo and wait for their turn.
  • The request turns out to be more massive than the previous version.

Main advantages:

  • During small SQL queries, other SQL queries are not blocked.
  • Ease of reading.
  • Flexibility. In this option, you can not follow the structure, but add only the necessary data.
  • When forming archives in this way, you can easily copy one line and run it through the command line (console), thereby not restoring the whole ARCHIVE.
  • The writing style is similar to the UPDATE statement, which makes it easier to remember.

SQL query: UPDATE

UPDATE table_name SET user_login = "ivanov", user_surname = "Ivanov" WHERE id = 1

In the table " table_name"In the record with id = 1, the values ​​of the user_login and user_surname fields will be changed to the specified values.

SQL query: DELETE

DELETE FROM table_name WHERE id = 3

The record with id number 3 will be deleted in the table_name table.

  1. It is recommended to write all field names in small letters and, if necessary, separate them with a forced space "_" for compatibility with different programming languages ​​such as Delphi, Perl, Python and Ruby.
  2. Write SQL commands in CAPITAL letters for readability. Always remember that other people can read the code after you, and most likely you yourself after N amount of time.
  3. Name fields from the beginning of the noun, and then the action. For example: city_status, user_login, user_name.
  4. Try to avoid fallback words in different languages ​​that can cause problems in SQL, PHP or Perl, such as (name, count, link). For example: link can be used in MS SQL, but is reserved in MySQL.

This material is a short reference for everyday work and does not claim to be a super mega authoritative source, which is the primary source of SQL queries for a particular database.

By using query terms, you can search for specific items in an Access database. If the item matches all the conditions you entered, it will appear in the query results.

To add a condition to an Access query, open the query in a designer. Then define the fields (columns) to which this condition applies. If the required field is missing on the request form, double-click to add it. Then in the line Conditions enter a condition for it. For more information, see Query Overview.

A query condition is an expression that Access compares against the values ​​in query fields to determine whether to include records containing a particular value in the result. For example, = "Voronezh" is an expression that Access compares to the values ​​in the query text box. If the value of this field in a particular record is Voronezh, Access includes it in the query results.

Let's look at a few examples of commonly used conditions that you can use to create your own conditions. Examples are grouped by data type.

In this section

General information about request conditions

A condition is like a formula — it is a string that can include field references, operators, and constants. In Access, query conditions are also referred to as expressions.

The following table shows examples of conditions and describes how they work.

Conditions

Description

> 25 and<50

This condition applies to a numeric field such as Price or UnitsIn Stock. It allows you to display only those records in which the "Price" or "UnitsIn Stock" field contains value greater than 25 and less than 50.

DateDiff ("yyyy", [Date of Birth], Date ())> 30

This condition applies to a Date / Time field such as Date of Birth. Only records in which number of years betweendate of birthperson and current date is more than 30.

This condition can be applied to any type of field to display records in which the field value is NULL.

As you can see, conditions can vary significantly depending on the type of data in the field to which they apply and your requirements. Some conditions are simple and include only basic operators and constants. Other conditions are complex: they contain functions, special operators and links to fields.

This article lists several commonly used conditions for different types data. If the examples don't suit your needs, you may need to set your own terms. To do this, you must first familiarize yourself with complete list functions, operators, and special characters; and syntax for expressions that refer to fields and literals.

Let's find out where and how conditions can be added. To add conditions to a query, you need to open it in the Designer. After that, you should define the fields for which you want to set conditions. If the field is not already on the query design, add it by dragging it from the query design window onto the grid of fields, or by double-clicking the field (this automatically adds the field to the next empty column in the grid). Finally, put the conditions in the line Conditions.

Conditions specified for different fields in a row Conditions, are concatenated using the AND operator. In other words, the conditions specified in the City and Date of Birth fields are interpreted as follows:

City = "Voronezh" AND Date of Birth < DateAdd (" yyyy ", -40, Date ())

1. Fields "City" and "Date of Birth" include conditions.

2. This condition is met only by records in which the "City" field has the value "Voronezh".

3. This condition is met only by records of people who are at least 40 years old.

4. Only records that match both conditions will be included in the result.

What if you want only one of these conditions to be met? In other words, how can alternative conditions be introduced?

If you have alternative conditions, that is, there are two sets of independent conditions, of which only one must be true, use the lines Selection condition and Or on the form.

1. 1. Condition "City" is indicated in the line "Condition of selection".

2. 2. The condition "Date of Birth" is indicated in the line "or".

Conditions specified in strings Selection condition and or are combined using the OR operator as shown below.

City = "Chicago" OR Date of Birth< DateAdd("гггг", -40, Date())

If you need to set several alternative conditions, use the lines below the line or.

Before you start exploring the examples, please note the following:

Conditions for Text Fields, Memo Fields, and Hyperlink Fields

Note: Starting in Access 2013, text fields are named Short text and the Memo fields are Long text.

The following examples are for a CountryRegion field based on a table that stores contact information. The condition is set in the line Selection condition fields on the form.

The condition set for the Hyperlink field is applied by default to the display text that is specified in the field. To specify conditions for the final URL, use the expression HyperlinkPart... It has the following syntax: HyperlinkPart ([Table1]. [Field1], 1) = "http://www.microsoft.com/" where "Table1" is the name of the table that contains the hyperlink field, "Field1" is the hyperlink field itself, and "http://www.microsoft.com" is the URL you want to find.

Use this condition

Result request

Exactly match a specific meaning, such as "China"

Returns records in which the CountryRegion field contains the value China.

Does not match a specific value, such as "Mexico"

Not "Mexico"

Returns records in which the CountryRegion field is not Mexico.

Starts with a given string of characters, such as "C"

Returns records for all countries or regions that start with the letter "C", such as Slovakia and the United States.

Note: The asterisk ( * ) in an expression denotes any character string. It is also called a wildcard. For a list of these characters, see Access wildcard reference.

Do not start with a given string of characters, such as "C"

Returns records for all countries or regions whose names do not begin with the letter "C".

Like "* Korea *"

Returns records for all countries or regions whose names contain the string "Korea".

Not Like "* Korea *"

Returns records for all countries or regions whose names do not contain the string "Korea".

Ending given string, for example "ina"

Returns records of all countries or regions whose names end in "ina", such as "Ukraine" and "Argentina".

Do not end with the given string, eg "ina"

Not Like "* ina"

Returns records for all countries or regions that do not end in "ina", as in "Ukraine" and "Argentina".

Returns records in which this field contains no value.

Returns the records in which this field contains a value.

"" (straight quotes)

Returns records in which the field is empty (but not null). For example, sales records for another department might contain a blank value in the CountryRegion field.

Returns records in which the CountryRegion field has a non-empty value.

Contains null values ​​or empty strings

Returns records in which there is no value in the field or is empty.

Nonzero and non-empty

Is Not Null And Not ""

Returns records in which the CountryRegion field has a non-null value.

When sorting alphabetically, followed by a specific value, such as "Mexico"

> = "Mexico"

Returns records with the names of countries and regions from Mexico to the end of the alphabet.

Are within a certain range, for example, from A to G

Returns countries and regions whose names start with letters from "A" to "D".

Matches one of two values, such as "Slovakia" or "USA"

"Slovakia" Or "USA"

Returns records for the United States and Slovakia.

In ("France", "China", "Germany", "Japan")

Returns records for all countries or regions specified in the list.

Right ([CountryRegion], 1) = "a"

Returns records for all countries or regions whose names end with the letter "a".

Corresponds to the specified length

Len ([CountryRegion])> 10

Returns country or region records with names greater than 10 characters.

Match a given pattern

Returns country or region records that have five characters and begin with Liv, such as Libya and Lebanon.

Note: Symbols ? and _ in an expression denote one character. They are also called wildcards. Sign _ ? * _ % .

Conditions for Numeric Fields, Currency Fields, and Counter Fields

The following examples are for the UnitPrice field based on a table that stores product information. The condition is set in the line Selection condition fields on the request form.

To add entries that ...

Use it condition

Query Result

Exactly match a specific value, for example 1000

Returns records where the unit price of an item is $ 1,000.

Do not match the value, for example 10,000

Returns records in which the unit price of an item is not $ 10,000.

< 1000
<= 1000

Returns records in which the price of an item is less than 1000 ₽ (<1000). Второе выражение (<=1000) отображает записи, в которых цена не больше 1000 ₽.

>999,99
>=999,99

Returns records where the item price is more than $ 9.99 (> $ 999.99). The second expression displays records with a price of at least $ 999.99.

Returns records where the price of an item is $ 200 or $ 250.

> 499.99 and<999,99
or
Between 500 and 1000

Returns product records with prices ranging from $ 499.99 to $ 999.99 (not including these values).

<500 or >1000

Returns records in which the price of an item is not in the range of $ 500 to $ 1,000.

Contains one of the given values

In (200, 250, 300)

Returns records where the price of an item is $ 200, $ 250, or $ 300.

Returns product records that end in $ 4.99, such as $ 4.99, $ 14.99, $ 24.99, and so on.

Note: Signs * and % in an expression denote any number of characters. They are also called wildcards. Sign % cannot be used in the same expression with the character * as well as wildcard ? ... You can use wildcard % in an expression where there is a wildcard _ .

Returns records for which no value is entered in the UnitPrice field.

Returns records with a value in the UnitPrice field.

Conditions for Date / Time Fields

The following examples are for an OrderDate field based on a table that stores order information. The condition is set in the line Selection condition fields on the request form.

Recordings

Use this criterion

Query Result

Exactly match the value, for example 02.02.2006

Returns records of transactions that occurred on February 2, 2006. Be sure to place # signs before and after date values ​​so that Access can distinguish date values ​​from text strings.

Doesn't match a value like 02/02/2006

Not # 02.02.2006 #

< #02.02.2006#

To view transactions completed on or before a specific date, use the operator <= instead of operator < .

> #02.02.2006#

To view transactions completed on or after a specific date, use the operator >= instead of operator > .

> # 02.02.2006 # and<#04.02.2006#

Alternatively, to filter by a range of values, including end values, you can use the operator Between... For example, the expression Between # 02.02.2006 # and # 04.02.2006 # is identical to the expression> = # 02.02.2006 # and<=#04.02.2006#.

<#02.02.2006# or >#04.02.2006#

# 02.02.2006 # or # 03.02.2006 #

Contains one of several values

In (# 01.02.2006 #, # 01.03.2006 #, # 01.04.2006 #)

Returns records of transactions that occurred on February 1, 2006, March 1, 2006, or April 1, 2006.

DatePart ("m"; [SaleDate]) = 12

Returns records of transactions performed in December of any year.

DatePart ("q"; [SaleDate]) = 1

Returns records of transactions completed in the first quarter of any year.

Returns records of transactions completed today. If today's date is 02/02/2006, you will see records, in the "OrderDate" field of which is indicated 02/02/2006.

Returns records of transactions completed yesterday. If today's date is 2/2/2006, you see records for February 1, 2006.

Returns records of transactions that will be executed tomorrow. If today's date is 2/2/2006, you see records for February 3, 2006.

DatePart ("ww"; [SaleDate]) = DatePart ("ww"; Date ()) and Year ([SaleDate]) = Year (Date ())

Returns records of transactions completed in the current week. Week starts on Sunday and ends on Saturday.

Year ([SaleDate]) * 53 + DatePart ("ww"; [SaleDate]) = Year (Date ()) * 53 + DatePart ("ww"; Date ()) - 1

Returns records of transactions completed in the past week. Week starts on Sunday and ends on Saturday.

Year ([SaleDate]) * 53 + DatePart ("ww"; [SaleDate]) = Year (Date ()) * 53 + DatePart ("ww"; Date ()) + 1

Returns records of transactions to be completed next week. Week starts on Sunday and ends on Saturday.

Between Date () and Date () - 6

Returns records of transactions completed in the last 7 days. If today's date is 2/2/2006, you see records for the period January 24, 2006 through February 2, 2006.

Year ([SaleDate]) = Year (Now ()) And Month ([SaleDate]) = Month (Now ())

Returns records for the current month. If today's date is 2/2/2006, you see records for February 2006.

Year ([SaleDate]) * 12 + DatePart ("m"; [SaleDate]) = Year (Date ()) * 12 + DatePart ("m"; Date ()) - 1

Returns records for the last month. If today's date is 2/2/2006, you will see records for January 2006.

Year ([SaleDate]) * 12 + DatePart ("m"; [SaleDate]) = Year (Date ()) * 12 + DatePart ("m"; Date ()) + 1

Returns records for the next month. If today's date is 2/2/2006, you see records for March 2006.

Between Date () And DateAdd ("M", -1, Date ())

Monthly sales records. If today's date is 2/2/2006, you see records for the period January 2, 2006 through February 2, 2006.

Year ([SaleDate]) = Year (Now ()) And DatePart ("q"; Date ()) = DatePart ("q"; Now ())

Returns the records for the current quarter. If today's date is 2/2/2006, you will see records for the first quarter of 2006.

Year ([SaleDate]) * 4 + DatePart ("q"; [SaleDate]) = Year (Date ()) * 4 + DatePart ("q"; Date ()) - 1

Returns the records for the previous quarter. If today's date is 2/2/2006, you will see records for the last quarter of 2005.

Year ([SaleDate]) * 4 + DatePart ("q"; [SaleDate]) = Year (Date ()) * 4 + DatePart ("q"; Date ()) + 1

Returns records for the next quarter. If today's date is 2/2/2006, you will see records for the second quarter of 2006.

Year ([SaleDate]) = Year (Date ())

Returns records for the current year. If today's date is 2/2/2006, you will see records for 2006.

Year ([SaleDate]) = Year (Date ()) - 1

Returns records of transactions completed in the last year. If today's date is 2/2/2006, you will see records for 2005.

Year ([SaleDate]) = Year (Date ()) + 1

Returns transaction records for the next year. If today's date is 2/2/2006, you will see records for 2007.

Year ([SaleDate]) = Year (Date ()) and Month ([SaleDate])<= Month(Date()) and Day([ДатаПродажи]) <= Day (Date())

Returns transaction records that fall between January 1 of the current year and today's date. If today's date is 2/2/2006, you see records for the period January 1, 2006 through February 2, 2006.

Returns records of transactions completed to date.

Returns transaction records that will be completed after today.

Filter empty (or missing) values

Returns records that do not include a transaction date.

Filter for non-empty values

Returns records that contain the date of the transaction.

Conditions for "Yes / No" fields

As an example, the Clients table has a Boolean Activity field that shows the current activity of the client account. The table displays how the values ​​entered in the condition line of the boolean field are calculated.

Field value

Result

"Yes", "True", 1 or -1

Checked for Yes. Once entered, the value 1 or -1 is changed to True in the condition line.

"No", "False" or 0

Checked for "No". Once entered, the value 0 changes to False in the condition line.

No value (null)

Not verified

Any number other than 1, -1, or 0

There are no results if this is the only condition value in the field

Any character string other than Yes, No, True, or False

The request could not be completed due to a data type mismatch error.

Conditions for other fields

Attachments. In line Selection condition enter Is Null to include entries that do not contain attachments. Enter Is Not Null to include entries with attachments.

Lookup fields. There are two types of lookup fields: those that substitute values ​​from an existing data source (using a foreign key), and those that are based on the list of values ​​that were specified when they were created.

Lookup fields based on a list of values ​​are of a text data type and accept the same conditions as other text fields.

The conditions that you can use in a lookup field based on values ​​from an existing data source depend on the data type of the foreign key, not the type of data being substituted. For example, you might have a lookup field that displays the name of an employee, but uses a foreign key with a numeric data type. Since the field stores a number, not text, you can use conditions that are appropriate for numbers, such as >2 .

If you do not know the data type of the foreign key, you can view the source table in the Designer to determine it. For this:

    Find your original table in navigation area.

    Open the table in a Design View by doing one of the following:

    • Click the table and press the keys CTRL + ENTER.

      Right-click the table and select Constructor.

    The data type for each field is listed in the column Data type on a blank table.

Multi-valued fields. Data in multivalued fields is stored as hidden table rows that Access creates and populates to represent the field. In the Query Designer they are represented in list of fields using an expandable field. To set conditions for a multivalued field, you must specify them for one row of a hidden table. For this:

    Create a query containing a multivalued field and open it in a Design View.

    Expand a multi-valued field by clicking the plus symbol ( + ) next to it. If the field is already expanded, then minus ( - ). Below the field name, you will see a field that represents a single multi-valued field value. This field will have the same name as the multivalued field, but the line will be appended to it .Meaning.

    Drag the multivalued field and its value field to different columns on the design sheet. If you want the results to display only the full multivalued field, uncheck Show for a field of one value.

    Enter in the field Selection condition for a single-value field, conditions suitable for the data type that values ​​represent.

    Each value in a multivalued field will be evaluated individually based on the specified conditions. For example, suppose a multivalued field contains a list of numbers. If you specify conditions > 5 AND<3 , all records with at least one value greater than 5 will be displayed and one value less than 3.