Word a complex request. Methodical instructions and tasks

Requests are written without shielding quotes, since Mysql, MS SQL. and Postgree they are different.

SQL Request: Obtaining the specified (necessary) fields from the table

SELECT ID, COUNTRY_TITLE, COUNT_PEOPLE FROM TABLE_NAME

We receive a list of records: all countries and their population. The name of the desired fields is indicated by commas.

SELECT * from table_name

* Indicates all fields. That is, there will be shows EVERYTHING Data fields.

SQL Request: Display Records from Table Excluding Duplicates

Select Distinct Country_Title from Table_Name

We receive a list of records: countries where our users are located. Users can be a lot of one country. In this case, this is your request.

SQL Request: Display Records from Table on a given Condition

SELECT ID, COUNTRY_TITLE, CITY_TITLE FROM TABLE_NAME WHERE COUNT_PEOPLE\u003e 100000000

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

SQL Request: Display Records from Application Table

SELECT ID, CITY_TITLE FROM Table_Name Order by city_title

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

SELECT ID, CITY_TITLE FROM TABLE_NAME ORDER by city_title desc

We receive a list of records: Cities in the opposite ( DESC.). At the beginning, I, at the end of 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 of the desired record range

Select * from Table_Name Limit 2, 3

We get 2 (second) and 3 (third) entry from the table. The request is useful when you create a navigation on the Web pages.

SQL requests with conditions

Display entries from the table for a given condition using logical operators.

SQL Request: Construction and (and)

SELECT ID, CITY_TITLE FROM TABLE_NAME WHERE COUNTRY \u003d "RUSSIA" AND OIL \u003d 1

We receive a list of records: Cities from Russia AND Have access to oil. When operator is used And., I must coincide both conditions.

SQL Request: Design OR (or)

SELECT ID, CITY_TITLE FROM TABLE_NAME WHERE COUNTRY \u003d "RUSSIA" OR COUNTRY \u003d "USA"

We receive a list of records: all cities from Russia OR USA. When operator is used Or., it should coincide at least one condition.

SQL query: design and not (and not)

SELECT ID, User_Login from Table_Name Where Country \u003d "Russia" and not count_comments<7

We receive a list of records: All users from Russia AND Made NOT LESS 7 comments.

SQL Request: In (B) Design

SELECT ID, User_Login from Table_Name Where Country In ("Russia", "Bulgaria", "China")

We receive a list of records: All users live in ( IN.) (Russia, or Bulgaria, or China)

SQL Request: NOT IN Design (not in)

SELECT ID, User_Login from Table_Name Where Country Not in ("Russia", "China")

We receive a list of records: All users who live are not in ( NOT IN.) (Russia or China).

SQL Request: IS NULL design (empty or not empty values)

SELECT ID, User_Login from Table_name WHERE STATUS IS NULL

We receive a list of records: All users, where Status is not defined. Null is a separate topic and therefore it is checked separately.

SELECT ID, User_Login from Table_Name Where State IS Not Null

We receive a list of records: All users, where Status is defined (not zero).

SQL Request: Like Design

SELECT ID, User_Login from Table_name Where Surname Like "Ivan%"

We receive a list of records: Users who have a surname begins with the combination "Ivan". The% sign means any number of any characters. To find a sign% you need to use the screening "Ivan \\%".

SQL query: Between design

SELECT ID, User_Login from Table_Name Where Salary Between 25000 and 50000

We receive a list of records: Users who receive salary from 25,000 to 50,000 inclusive.

Logic operators are very much, so you will study the SQL server documentation in detail.

Complex SQL requests

SQL query: combining multiple requests

(SELECT ID, User_Login from Table_name1) Union (Select ID, User_Login from Table_name2)

We receive a list of records: Users who are registered in the system, as well as those users who are registered on the forum separately. The Union operator can be combined several requests. Union acts like Select Distinct, that is, discarding repetitive values. To get absolutely all records, you need to use the UNION ALL operator.

SQL Request: Counting Max, Min, Sum, Avg, Count field values

Conclusion of one, maximum counter value in the table:

SELECT MAX (Counter) from table_name

Output one, minimum counter values \u200b\u200bin the table:

SELECT MIN (Counter) from table_name

The output of all the values \u200b\u200bof the meters in the table:

SELECT SUM (Counter) from table_name

The output of the average meter value in the table:

SELECT AVG (Counter) from table_name

The output of the number of meters in the table:

SELECT COUNT (Counter) from table_name

The output of the number of meters in the workshop number 1, in the table:

SELECT COUNT (Counter) from table_name where office \u003d "shop number 1"

These are the most popular teams. It is recommended where it is possible to use the SQL requests for counting this kind, since no programming environment is compared at 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 receive a list of records: with the name of the continent and with the sum of the squares of all their countries. That is, if there is a reference book of countries where each country has its area, then using the GROUP BY design, you can find out the size of each continent (based on grouping by continents).

SQL Request: Using Multiple Tables through Alias \u200b\u200b(Alias)

Select O.Order_no, O.amount_Paid, C.com ORDERS AS O, Customer AS with WHERE O.CUSTNO \u003d C.Custno and C.City \u003d "Tyumen"

We receive a list of records: orders from buyers who live only in Tyumen.

In fact, with a properly projected database of this type, the query is most frequent, so a special operator was introduced into MySQL, which works at times faster than the above-mentioned code.

Select O.Order_No, O.amount_paid, z.comPany from Orders AS O Left Join Customer AS Z ON (z.custno \u003d O.CUSTNO)

Nested subqueries

SELECT * from Table_Name Where Salary \u003d (SELECT MAX (Salary) from Employee)

We get one record: user information with maximum salary.

Attention! Nested subqueries are one of the most narrow seats in SQL servers. Together with its flexibility and power, they also significantly increase the load on the server. What leads to a catastrophic slowdown of other users. There are very frequent cases of recursive calls when attached queries. Therefore, I strongly recommend not to use invested requests, but split them into smaller. Or use the above-described Left Join combination. In addition to this type, requests are an elevated focus of security breach. If you decide to use nested subqueries, then it is necessary to design them very carefully and initial starts to do on database copies (test bases).

SQL Requests Changing Data

SQL Request: INSERT

Instruction Insert. allow you to insert records in the table. Simple words, create a line with data in the table.

Option number 1. Instruction is often used:

INSERT INTO TABLE_NAME (ID, USER_LOGIN) VALUES (1, "IVANOV"), (2, "Petrov")

In the table " table_Name."Will be inserted 2 (two) users immediately.

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

INSERT TABLE_NAME SET ID \u003d 1, User_Login \u003d "Ivanov"; INSERT TABLE_NAME SET ID \u003d 2, User_Login \u003d "Petrov";

This has its advantages and disadvantages.

Basic Disadvantages:

• Many small SQL queries are performed slightly slower than one big SQL query, but other requests will stand in the service queue. That is, if a large SQL query will be completed 30 minutes, then in all this time, the rest of the requests will smoke bamboo and wait for their turn.
• The request is massive than the previous option.

Main advantages:

• During small SQL requests, other SQL requests are not blocked.
• Convenience in reading.
• Flexibility. In this embodiment, you can not comply with the structure, but add only the necessary data.
• When forming similarly archives, you can easily copy one line and start it through the command line (console), thereby not restoring the entire archive.
• The record style is similar to the UPDATE instruction, which is easier to remember.

SQL Request: Update

Update Table_Name set user_login \u003d "Ivanov", user_surname \u003d "Ivanov" Where id \u003d 1

In the table " table_Name."In records with ID number \u003d 1, the values \u200b\u200bof the user_login and user_surname fields will be changed to the specified values.

SQL Request: Delete

Delete from Table_Name WHERE ID \u003d 3

The table_name table will be deleted with the number 3 ID.

1. All field names are recommended to write with small letters and if necessary, divide them through the forced space "_" for compatibility with different programming languages, such as Delphi, Perl, Python and Ruby.
2. SQL teams writing in large letters for readability. Remember always that after you can read the code and other people, and most likely you yourself through N the amount of time.
3. Call the fields from the beginning of the noun, and then action. For example: city_status, user_login, user_name.
4. Try to avoid backup words in different languages \u200b\u200bthat can cause problems in SQL, PHP or Perl languages, such as (Name, Count, Link). For example: Link can be used in MS SQL, but in MySQL reserved.

This material is a short certificate for everyday work and does not pretend to a super mega authoritative source, which is the source of SQL queries of a database.

DIRECT COMMANDER QUERY LANGUAGE QUICK LANGUAGE Allows you to create complex criteria for selecting objects: impose different conditions on the values \u200b\u200bof the fields and combine them using logical operators.

The query is entered in the text filter string at the bottom of any commander panel. Request using logical operators starts with a symbol = .

• Drawing up request
• Requests from several conditions
• Operators of requests

Drawing up request

A simple request consists of three parts:

Field Operator value

For example, query title ~ Sugari. It will show all ads who in the headline column contains the word "SUCHI".

Start typing a symbol = . At the same time, the field name icon appears in the input bar and operators can be selected from pop-up tips.

When entering the value, please note:

If the request is incorrect, the icon in the input row changes to and an error message appears.

Requests from several conditions

To compile combined queries, you can use Operators & (logical and) and | (logical or).

Conditions in the query are performed strictly left to right, but you can change the order using parentheses. Conditions enclosed in brackets have a priority over the standard sequence.

Example 1.

Words ~ Matches | Words ~ Sugar.

On this request, phrases are selected, in which there is a keyword "matches" or "sugar".

Example 2.

Words ~ Matches | Words ~ Sugar & Bet\u003e 1

According to this request, phrases are selected that respond to two conditions simultaneously:

Have a bet on searching more than 1.

Example 3.

Words ~ Matches | (Words ~ sugar & bid\u003e 1)

On this request, phrases are selected that correspond to at least one of the two conditions:

Operators of requests

Depending on the field in DCQL, the following types of operators are used.

Operator	Value	Example	Result
~	Contains	\u003d Geotargeting ~ Austra
!~	Does not contain	\u003d Geotargeting! ~ Austra
\u003d Moderation! ~ [Expects]
=	Equally / Coincident	\u003d Geotargeting \u003d Australia
\u003d Number \u003d.
!=	Not equally / does not coincide	\u003d Geotargeting! \u003d Australia
\u003d Number! \u003d
>	More	\u003d CTR\u003e 0.5
<	Less	\u003d Ctr.< 0.5
>=	More or equal	\u003d Bet\u003e \u003d 1
<=	Less either equal	\u003d Bet<= 1
&
|

Operator	Value	Example	Result
~	Contains	\u003d Geotargeting ~ Austra	An ad groups are selected, which in the Geotargeting column indicated "Australia" or "Austria"
\u003d \\ "Image Name \\" ~	Announcements are selected, in which the image name in the image column is the name containing the "Black" or "White" substring
\u003d Moderation ~ [accepted; Draft]	An ads are selected, in which the Moderation column shows the value "accepted" or "Chernovik"
!~	Does not contain	\u003d Geotargeting! ~ Austra	Ads of ads are selected, except for those who specified Australia's geotargeting or "Austria"
\u003d Title! ~ [Sugar matches]	Announcements are selected, in which the meaning in the column does not contain a substring "Matches" and "SUKHARI"
\u003d Moderation! ~ [Expects]	Announcements are selected, in which the moderation column indicates any value, except "expects".
=	Equally / Coincident	\u003d Geotargeting \u003d Australia	Only those groups are selected in which Australia's geotargeting is specified.
\u003d Number \u003d.	Announcements are selected with numbers 111111 and 222222.
!=	Not equally / does not coincide	\u003d Geotargeting! \u003d Australia	Groups are selected, except for those who specified Australia's geotargeting
\u003d Number! \u003d	Announcements are selected with numbers other than 111111 and 222222.
>	More	\u003d CTR\u003e 0.5	Phrases with CTR are selected greater than 0.5
<	Less	\u003d Ctr.< 0.5	The phrases with CTR are selected less than 0.5
>=	More or equal	\u003d Bet\u003e \u003d 1	Phrases are selected, whose stake on the search is more or equal to 1
<=	Less either equal	\u003d Bet<= 1	Phrases are selected, whose stake on the search is less or equal to 1
&	Logical "and" in complex requests	\u003d number ~ 123 & state \u003d \\ "go shows \\"	Announcements are selected, which in the room contain numbers 123 and are in the state "go shows"
|	Logical "or" in complex queries	\u003d Name ~ Matches | Name ~ Sugar.	Announcements are selected, in the title of which contains the word "matches" or the word "sugar"

Attention.

Operators > , >= , < and <= You can only be used for columns with numeric values.

\u003e\u003e Informatics: Internet search methods

§ 5. Ways to search for the Internet

The main topics of the paragraph:

♦ Three ways to search on the Internet;
♦ Search servers;
♦ Search engine queries.

Three ways to search online

Search engine queries

A group of keywords formed by certain rules - using the query language is called a request to the search server. Request languages \u200b\u200bfor different search servers are very similar. You can find out more about this by visiting the "Help" section of the desired search server. Consider the rules for the formation of requests on the example of the search engine Indech.

Syntax operator	What does the operator mean	Sample request
Space or &	Logical and (within the offer)	physiotherapy
&&	Logical and (within the document)	Recipes && (Melted Cheese)
|	Logical or	Photo | Photo | Snapshot | Photo image
+	Mandatory Word availability in a found document	+ be or + not be
()	Grouping words	(Technology | manufacture) (cheese | cottage cheese)
~	Binary operator and not (within the offer)	Banks ~ law
~~ or -	Binary operator and not (within the document)	Guide to Paris ~~ (Agency | Tour)
/ (n m)	Distance in words (minus (-) - back, plus (+) - forward)	Suppliers / 2 Coffee musical / (- 2 4) Education jobs - / + 1 students
“ ”	Search phrase	"Little Red Riding Hood" Equivalent: Red / + 1 Cap
&& / (n m)	Distance in Deals (minus (-) - back, plus (+) - Forward)	Bank && / 1 Taxes

To get the best search results, you need to remember a few simple rules:

Design of lesson Abstract lesson reference frame presentation lesson accelerative methods interactive technologies Practice Tasks and exercises self-test Workshop, trainings, cases, quests Home tasks Discussion issues Rhetorical questions from students Illustrations Audio, video clips and multimedia Photos, pictures, tables, Schemes of humor, jokes, jokes, Comics Proverbs, sayings, crosswords, quotes Supplements Abstracts Articles Chips for Curious Cheat Sheets Textbooks Basic and Additional Globes Other Terms Improving textbooks and lessons Fixing errors in the textbook Updating fragment in the textbook. Innovation elements in the lesson replacing outdated knowledge new Only for teachers Perfect lessons Calendar Plan for a year

Query language is an artificially created programming language used to make requests in databases and information systems.

In general, such query methods can be classified depending on whether they serve for a database or to search for information. The difference is that requests for similar services are made to receive actual answers to the questions raised, while the search engine is trying to find documents containing information related to the area of \u200b\u200binterest.

Database

Languages \u200b\u200bof database requests include the following examples:

• QL - object-oriented, belongs to the Datalog successor.
• Contextual (CQL) is a formal language view language for information and search engines (such as web indices or bibliographic directories).
• CQLF (CODYASYL) - for Codasyl-Type databases.
• Concept-oriented query language (COQL) - used in appropriate models (COM). It is based on the principles of data modeling Construpt and uses operations such as projection and de projection of multidimensional analysis, analytical operations and conclusions.
• DMX - used to models
• Datalog is a query language to deductive databases.
• Gellish English is a language that can be used for queries in Gellish English databases and allows you to conduct dialogs (queries and answers), and also serves for information modeling.
• HTSQL - translates HTTP requests to SQL.
• ISBL - Used for PRTV (one of the first relational database management systems).
• LDAP is a protocol for queries and directory services working on the TCP / IP protocol.
• MDX is needed for OLAP databases.

Search engines

The search query language, in turn, is aimed at finding data in search engines. It is characterized by the fact that frequently requests contain regular text or hypertext with additional syntax (for example, "and" / "or"). It differs significantly from standard similar languages, which are governed by strict rule syntax rules or contain positional parameters.

How are search queries classified?

There are three wide categories that cover most of the search queries: information, navigation and transactional. Although this classification was not fastened to theoretically, it is empirically confirmed by the presence of actual search engines.

Information requests are those that cover extensive topics (for example, any particular city or model of trucks), for which thousands of relevant results can be obtained.

Navigation - these are requests that are looking for one site or web page to a specific topic (for example, YouTube).

Transactional - reflect the intention of the user to perform a specific action, for example, make the purchase of a car or book a ticket.

Search engines often support the fourth type of query that is used much less frequently. These are the so-called connection requests that contain a report on the connectedness of an indexed web graph (the number of references to a specific URL, or how many pages are indexed from a specific domain).

How is the search for information?

Interesting features related to web search:

The average search query length was 2.4 words.

• About half of users guided one request, and a little less than a third of users did three or more unique requests one by one.
• Almost half of users browsed only the first one or two pages of the results obtained.
• Less than 5% of users use advanced search capabilities (for example, choosing any specific categories or search search).

Features of custom action

The study also showed that 19% of requests contained a geographical term (for example, names, postcodes, geographical objects, etc.). It is also worth noting that in addition short requests (that is, with several conditions), predictable schemes were also present, for which users change their search phrases.

It was also found that 33% of requests from one user are repeated, and in 87% of cases, the user will click on the same result. This suggests that many users use repeated requests to revise or re-find information.

Frequency distributions of requests

In addition, experts have been confirmed that frequency distributions of requests correspond to the power law. That is, a small part of keywords is observed in the largest list of queries (for example, more than 100 million), and they are most often used. The rest of the phrases within the framework of the same topics are applied less often and more individually. This phenomenon received the name of the Pareto principle (or "Rule 80-20"), and it allowed the search engines to use such optimization methods such as indexing or separating the database, caching and proactive load, and also made it possible to improve the search engine query language.

In recent years, it has been revealed that the average length of requests is steadily growing over time. So, the average request for english language He became longer. In this regard, Google has implemented an update called "Hummingbird" (in August 2013), which is capable of processing long search phrases with uncommunicable, "conversational" request language (like "where the nearest coffee house?").

For longer requests, their processing is used - they are divided into phrases formulated by the standard language, and answers are output to different parts separately.

Structured requests

Search engines that support and syntax use more advanced request languages. A user who is looking for documents covering several or faces can describe each of them by the logical characteristic of the word. In essence, the logical language of requests is a combination of certain phrases and punctuation marks.

What is an expanded search?

Language of "Yandex" and "Google" is capable of carrying out a more narrow-directional search subject to certain conditions. Advanced search can search for a part of the page name or header prefix, as well as in certain categories and list of names. It may also limit the search for pages containing certain words in the title or located in certain thematic groups. With the correct use of the query language, it can handle the parameters of the order more complex than the surface results of the issuance of most search engines, including on the specified user with the words with variable grades and similar spelling. When presenting the results of the extended search, the reference will be displayed to the corresponding page partitions.

Also this is the ability to search for all pages containing a certain phrase while under standard request. search engines Can not stop on any page of the discussion. In many cases, the query language can lead to any page located in Noindex tags.

In some cases, the correctly formed request allows you to find information containing a number. special characters and letters of other alphabets ( chinese characters eg).

How are query language symbols read?

The upper and lower registers, as well as some (scratuities and accents) are not taken into account in search. For example, search by keyword Citroen will not find pages containing the word "citroly". But some ligatures correspond to individual letters. For example, a search for "Aeroscrobing" will easily find pages containing "Ereskebing" (AE \u003d æ).

Many not alphabetically digital characters are constantly ignored. For example, it is impossible to find information on request containing a string | L | (The letter between two vertical stripes), despite the fact that this symbol is used in some conversion templates. The results will only have data from LT. Some characters and phrases are processed in different ways: the request "Credit (Finance)" will display articles with the words "Credit" and "Finance", ignoring brackets, even if there is an article with the exact name "Credit (Finance)".

There are many features that can be used using the query language.

Syntax

The "Yandex" and "Google" requests can use some punctuation marks to clarify the search. As an example, curled brackets can be brought - ((search)). The phrase concluded in them will be exposed to the whole, unchanged.

The phrase in allows you to decide on the search object. For example, the word in quotes will be recognized as used in a figurative sense or as fictional character, without quotes - as information more documentary.

In addition, all major search engines support the "-" symbol for logical "not", as well as / or. Exception - terms that cannot be separated using prefix or dash prefix.

Inaccurate compliance of the search phrase is marked with ~. For example, if you do not remember the exact formulation of the term or name, you can specify it in the search bar with the specified symbol, and you can get results having the maximum similarity.

Parameters of specialized search

There are also search parameters such as intitle, and incategory. They are filters displayed through a colon, in the form of "filter: query string". The query string may contain a desired term or phrase, or part or the full name of the page.

Function "intitle: request" gives priority to search results By title, but also shows the usual results on the title content. Several such filters can be used simultaneously. How to use this opportunity?

Request for the "intitle: Airport name" will issue all articles containing the title name of the airport. If you formulate it as "Intitle Parking: Airport name", then you will receive articles with the name of the airport in the title and with reference to the parking in the text.

Search by filter "Incategory: Category" works on the principle of initial issuing articles belonging to a specific group or list of pages. For example, search query According to the "Temples Incategory: History" will issue results on the history of temples. This feature can also be used as an extended by setting various parameters.

Using the query conditions, you can find specific items in Access database. If the item meets all the entered conditions, it will appear in the query results.

To add a condition to aCCESS request, Open this query in the designer. Then determine the fields (columns) to which this condition applies. If there is no desired field in the query form, add it using a double click. Then in the string Conditions Enter the condition for it. For more information, see Overview of Requests.

The query condition is an expression that Access compares with values \u200b\u200bin the query fields to determine whether to include records containing any value in the result. For example, \u003d "Voronezh" - This is an expression that Access compares with the values \u200b\u200bin the query text field. If the value of this field in a certain record is equal "Voronezh", Access includes it in the query results.

Consider several examples of frequently used conditions, on the basis of which you can create your own conditions. Examples are grouped by data types.

In this section

General information about requests

The condition is similar to the formula is a string that may include references to fields, operators and constants. In Access, the query conditions are also called expressions.

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

Conditions	Description
\u003e 25 And.<50	This condition is applied to a numerical field, such as the "price" or "units". It allows you to withdraw only those records in which the "price" or "unit" field contains value greater than 25 and less than 50.
DatedIFF ("GGYY", [Datnarbar], date ())\u003e 30	This condition is applied to the "Date / Time" field, such as "Datarban". The query results include only entries in which number of yearsdate of birth man and current date more than 30.
	This condition can be applied to the fields of any type to display entries in which the value of the field is NULL.

As you can see, the conditions can differ significantly from each other depending on the type of data in the field to which they apply and on 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 frequently used conditions for different types data. If examples do not meet your needs, you may have to set your own conditions. To do this, you must first get acquainted with full list functions, operators and special characters, as well as with the syntax of expressions that refer to fields and literals.

We learn where and how to add conditions. To add conditions to the request, you must open it in the designer. After that, determine the fields for which you want to set the conditions. If there are no fields on the query form, add it by dragging it from the query designer window on the fields of fields or double-clicking the field (in this case, the field is automatically added to the next empty column in the grid). Finally, enter the conditions in the string. Conditions.

Conditions set for different fields in the row ConditionsCombined using the Operator and. In other words, the conditions specified in the "City" and "Datarbar" fields are interpreted as follows:

City \u003d "Voronezh" and Date of Birth < DateAdd. (" yyyy. ", -40, date ())

1. Fields "City" and "Datarban" include conditions.

2. This condition corresponds only to the records in which the field "City" matters "Voronezh".

3. This condition corresponds only to the records of people who are at least 40 years old.

4. The result will include only those records that match both conditions.

What if required, only one of these conditions is required? In other words, how can I enter alternative conditions?

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

1. 1. The "City" condition is indicated in the "Selection Condition" string.

2. 2. The "Datarbar" condition is indicated in the "or" string.

Conditions specified in lines Selection condition and orCombined using the OR operator, as shown below.

City \u003d "Chicago" or datar< DateAdd("гггг", -40, Date())

If you want to set several alternative conditions, use strings under a string or.

Before learning examples, pay attention to the following:

Conditions for text fields, MEMO fields and hypersmille fields

Note: Starting with ACCESS 2013, text fields are called Short text, and Memo fields - Long text.

The following examples belong to the "Country Region" field based on the table in which contact information is stored. The condition is set in the string Selection condition Fields on the letterhead.

The condition specified for the "hyperlink" field is applied by default to the displayed text that is specified in the field. To set the conditions for the end URL, use the expression Hyperlinkpart.. He has the following syntax: HyperLinkPart ([Table1]. [Field1], 1) \u003d "http://www.microsoft.com/"where "Table1" is the name of the table containing the field of the hyperlink, the "field1" is the field of the hyperlink, and "http://www.microsoft.com" is the URL you want to find.

	Use this condition	Result Request
Accurately correspond to a certain value, for example "China"		Returns recordings in which the Country Region field contains the value of "China".
Do not correspond to a certain value, for example, "Mexico"	NOT "Mexico"	Returns recordings in which the "Mexico" field is not "Mexico".
Start from a given string of characters, for example, "C"		Returns records of all countries or regions whose names begin with the letter "C", such as Slovakia and the United States. Note: Symbol "Star" ( * ) Indicates any string of characters. It is also called a wildcard sign. For a list of such characters, see the References on wildcard information in the Access application.
Do not start with a specified string of characters, for example, "C"		Returns the records of all countries or regions whose names are not starting with the letter "C".
	Like "* Korea *"	Returns the records of all countries or regions, the names of which contain the Row "Korea".
	NOT LIKE "* Korea *"	Returns the records of all countries or regions, the names of which do not contain the string "Korea".
End of a given string, for example "In"		Returns the records of all countries or regions, the names of which end on "Ina", such as "Ukraine" and "Argentina".
Do not end in a given string, for example "In"	Not Like "* In"	Returns the records of all countries or regions, the names of which are not completed on "Ina", as in the names "Ukraine" and "Argentina".
		Returns the records in which this field does not contain values.
		Returns the records in which this field contains a value.
	"" (direct quotes)	Returns records in which the field has an empty value (but not the value of NULL). For example, sales records to another department may contain an empty value in the Country Region field.
		Returns the recordings in which the Country Region field has a non-empty value.
Contains zero values \u200b\u200bor empty strings		Returns recordings in which the value in the field is missing or is empty.
Non-zero and non-empty	IS NOT NULL AND NOT ""	Returns the records in which the "Country Region" field has a non-empty value, not equal to NULL.
When sorting in alphabetical order, follow a certain meaning, for example, "Mexico"	\u003e \u003d "Mexico"	Returns records with the names of countries and regions, starting with Mexico to the end of the alphabet.
Included in a certain range, for example from A to G		Returns countries and regions whose names begins with letters from "A" to "g".
Coincide with one of two values, such as "Slovakia" or "USA"	"Slovakia" or "USA"	Returns records for the USA and Slovakia.
	In ("France", "China", "Germany", "Japan")	Returns records of all countries or regions listed in the list.
	Right ([Country Region], 1) \u003d "A"	Returns the records of all countries or regions, the names of which are completed with the letter "A".
Correspond to a given length	Len ([Country Region])\u003e 10	Returns the records of countries or regions, the length of the name of which exceeds 10 characters.
Correspond to a given template		Returns the records of countries or regions, whose names consist of five characters and begin with Liv, such as Libya and Lebanon. Note: Symbols ? and _ In the expression denote one character. They are also called wildcard signs. Sign _ ? * _ % .

Conditions for numerical fields, fields with monetary values \u200b\u200band counters fields

The following examples belong to the PRESIDENCE field based on the table in which information about goods is stored. The condition is set in the string Selection condition Fields on the query form.

To add records that ...	Use it condition	Request result
Exactly correspond to a certain value, for example 1000		Returns recordings in which the price per unit of goods is 1000 ₽.
Do not correspond to the value, for example 10,000		Returns records in which the price per unit of goods is not equal to 10 000 ₽.
	< 1000 <= 1000	Returns recordings in which the price of goods is less than 1000 ₽ (<1000). Второе выражение (<=1000) отображает записи, в которых цена не больше 1000 ₽.
	>999,99 >=999,99	Returns recordings in which the price of goods is more than 999.99 ₽ (\u003e 999.99). The second expression displays the record, the price in which is not less than 999.99 ₽.
		Returns recordings in which the price of goods is equal to 200 or 250 ₽.
	\u003e 499.99 and.<999,99 or Between 500 and 1000	Returns records of products with prices ranging from 499.99 to 999.99 ₽ (not including these values).
	<500 or >1000	Returns recordings in which the price of goods is not in the range from 500 to 1000 ₽.
Contains one of the specified values	IN (200, 250, 300)	Returns recordings in which the price of goods is equal to 200, 250 or 300 ₽.
		Returns the recordings of goods, the price of which is completed by 4.99, for example, 4.99 ₽, 14.99 ₽, 24.99 ₽, etc. Note: Signs * and % The expression denote any number of characters. They are also called wildcard signs. Sign % You can not use in one expression with a symbol * , as well as with a wildcard ? . You can use wildcard sign % In the expression where there is a wildcard sign _ .
		Returns the records for which the value is not entered in the "Pricing" field.
		Returns recordings, in the field "Pricing" of which the value is indicated.

Conditions for the fields "Date / Time"

The following examples belong to the field "Datazak" based on the table in which information on orders is stored. The condition is set in the string Selection condition Fields on the query form.

Entries	Use this criterion	Request result
Exactly correspond to the value, for example 02.02.2006		Returns transaction records made on February 2, 2006. Be sure to place signs # before and after the date values \u200b\u200bso that Access can distinguish the dates from the text strings.
Do not match the value, such as 02.02.2006	# 02.02.2006 #
	< #02.02.2006#	To view transactions performed in a specific date or before it, use the operator <= Instead of operator < .
	> #02.02.2006#	To view transactions performed in a specific date or after it, use the operator >= Instead of operator > .
	\u003e # 02.02.2006 # and<#04.02.2006#	In addition, to filter by range of values, including end values, you can use the operator Between.. For example, the expression of between # 02.02.2006 # and # 04.02.2006 # Identically expression\u003e \u003d # 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 the records of transactions performed on February 1, 2006, March 1, 2006 or April 1, 2006
	DatePart ("M"; [Data sales]) \u003d 12	Returns the records of transactions performed in December of any year.
	DATEPART ("Q"; [Data sales]) \u003d 1	Returns the records of transactions performed in the first quarter of any year.
		Returns transaction records made today. If today's date is 02.02.2006, you will see records in the field of "Datazakaz" indicated on February 2, 2006
		Returns transaction records performed yesterday. If today's date is 02.02.2006, you will see records for February 1, 2006.
		Returns transaction records that will be completed tomorrow. If today's date is 02.02.2006, you will see records for February 3, 2006.
	DATEPART ("WW"; [DATEPART SALE]) \u003d DATEPART ("WW"; DATE ()) AND YEAR ([DATEPALLY]) \u003d YEAR (date ())	Returns transaction records performed for the current week. The week begins on Sunday and ends on Saturday.
	Year ([DataPart]) * 53 + datepart ("WW"; [DATEPARE SALE]) \u003d year (date ()) * 53 + datepart ("ww"; date ()) - 1	Returns transaction records performed last week. The week begins on Sunday and ends on Saturday.
	Year ([DATEPART]) * 53 + DatePart ("WW"; [DATEPARD]) \u003d year (date ()) * 53 + datepart ("WW"; date ()) + 1	Returns transaction records to be performed next week. The week begins on Sunday and ends on Saturday.
	Between Date () and date () - 6	Returns the records of transactions performed in the last 7 days. If today's date is 02.02.2006, you will see records for the period from January 24, 2006 to February 2, 2006.
	Year ([Data sales]) \u003d year (NOW ()) and month ([Data sales]) \u003d month (now ())	Returns records for the current month. If today's date is 02.02.2006, you will see records for February 2006.
	Year ([DATEPARD]) * 12 + datepart ("M"; [DATEPARE SALE]) \u003d year (date ()) * 12 + datepart ("M"; date ()) - 1	Returns records last month. If today's date is 02.02.2006, you will see records for January 2006.
	Year ([DATEPART]) * 12 + DatePart ("M"; [DATEPARE SALE]) \u003d year (date ()) * 12 + datepart ("M"; Date ()) + 1	Returns records for the next month. If today's date is 02.02.2006, you will see records for March 2006.
	Between Date () and Dateadd ("M", -1, Date ())	Records about sales for the month. If today's date is 02.02.2006, you will see records for the period from January 2, 2006 to February 2, 2006.
	Year ([Data sales]) \u003d year (now ()) and datepart ("q"; date ()) \u003d datepart ("q"; now ())	Returns records for the current quarter. If today's date is 02.02.2006, you will see records for the first quarter of 2006.
	Year ([DATEPART]) * 4 + datepart ("q"; [DATEPARE SALE]) \u003d year (date ()) * 4 + datepart ("q"; date ()) - 1	Returns records for the last quarter. If today's date is 02.02.2006, you will see records for the last quarter of 2005.
	Year ([DATEPARTAZH]) * 4 + datepart ("Q"; [DATEPARE SALE]) \u003d YEAR (date ()) * 4 + datepart ("q"; date ()) + 1	Returns records for the next quarter. If today's date is 02.02.2006, you will see records for the second quarter of 2006.
	Year ([Data sales]) \u003d year (date ())	Returns records for the current year. If today's date is 02.02.2006, you will see records for 2006.
	Year ([Data sales]) \u003d year (date ()) - 1	Returns transaction records last year. If today's date is 02.02.2006, you will see records for 2005.
	Year ([Data sales]) \u003d year (date ()) + 1	Returns the records of the transactions that will be completed next year. If today's date is 02.02.2006, you will see records for 2007.
	Year ([Data sales]) \u003d year (date ()) and month ([Data sales])<= Month(Date()) and Day([ДатаПродажи]) <= Day (Date())	Returns the records of transactions that occur for the period from January 1 of the current year to today's date. If today's date is 02.02.2006, you will see records for the period from January 1, 2006 to February 2, 2006.
		Returns the records of transactions performed until today.
		Returns transaction records to be completed after today.
Empty filter (or missing) values		Returns recordings in which the transaction date is not specified.
Filter of non-empty values		Returns the records in which the transaction date is specified.

Conditions for the fields "Yes / No"

As an example, in the "Clients" table there is a logical field "Activity", which shows the current activity of the client account. The table displays how the values \u200b\u200bentered in the string of the logic field conditions are calculated.

The value of the field	Result
"Yes", "Truth", 1 or -1	Checked for "Yes". After entering, the value of 1 or -1 changes to "Truth" in the conditions string.
"No", "Lie" or 0	Verified for the "No" value. After entering, the value of 0 changes to "false" in the line string.
No value (NULL)	Not verified
Any number other than 1, -1 or 0	No results if this is the only value of the condition in the field
Any string of characters other than "Yes", "No", "Truth" or "Lie"	Unable to request due to the error of data types.

Conditions for other fields

Investments. In line Selection condition Enter IS NULLTo enable records that do not contain attachments. Enter IS NOT NULLTo enable entries with attachments.

Fields of substitution. There are two types of substitution fields: those that substitute values \u200b\u200bfrom an existing data source (using an external key), and those that are based on the list of values \u200b\u200bspecified when creating them.

The substitution fields based on the list of values \u200b\u200bhave a text data type and take the same conditions as other text fields.

The conditions that can be used in the substitution field based on the values \u200b\u200bof the existing data source depend on the type of the external key data, and not the type of data substituted. For example, you may have a substitution field that displays the employee name, but uses an external key with a numeric data type. Since the number is stored, not the text, you can use the conditions that are suitable for numbers, such as >2 .

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

Find the source table in areas of navigation.

Open the table in the designer by making one of the following:

• Click the table and press the keys. Ctrl + input.

  Right-click the table and select Constructor.

The data type for each field is specified in the column. Data type On the table form.

Multivalued fields. Data in multi-valued fields is stored as the lines of the hidden table, which Access creates and fills to represent the field. In the request designer, they are presented in list of fields Using an expandable field. To set the conditions for a multi-valued field, you must specify them for one row of the hidden table. For this:

Create a request containing a multi-valued field and open it in the constructor.

Expand the multivalued field by clicking the plus symbol ( + ) Next to him. If the field is already expanded, minus is displayed ( - ). Under the field name, you will see a field representing one value of the multivalued field. This field will have the same name as a multi-valued field, but a string will be added to it. .Value.

Drag the multi-valued field and the field of its value into different columns on the form. If you want only a complete multi-valued field in the results, uncheck the checkbox Show For the field of one value.

Enter in the field Selection condition For a single field, the conditions suitable for the data type, which is values.

Each value in a multi-valued field will be assessed separately based on the specified conditions. For example, we assume that a list of numbers is stored in a multi-valued field. If you specify the conditions \u003e 5 and.<3 will be displayed all the records in which there is at least one value more than 5 and One value is less than 3.