MySQL Предложение WHERE: AND, OR, IN, NOT IN Пример запроса
Маркус Аллен
⚡ Умное резюме
MySQL WHERE Clause filters rows before they reach the result set, applying precise criteria to SELECT, UPDATE, and DELETE statements. Understanding AND, OR, IN, NOT IN, and comparison operators lets beginners retrieve exactly the records a query requires.
Что такое пункт WHERE в MySQL?
Предложение WHERE in MySQL — это ключевое слово, используемое для указания точных критериев данных или строк, которые будут затронуты указанным оператором SQL. Предложение WHERE может использоваться с такими операторами SQL, как INSERT, UPDATE, SELECT и DELETE, для фильтрации записей и выполнения различных операций с данными.
Мы рассмотрели, как запросить данные из база данных используя оператор SELECT из предыдущего руководства. Инструкция SELECT вернула все результаты из запрошенной таблицы базы данных.
There are, however, times when we want to restrict the query results to a specified condition. The WHERE clause in SQL comes in handy in such situations, and it works the same way in a DELETE or UPDATE query as it does in a SELECT query.
Синтаксис предложения WHERE
The basic syntax of the WHERE clause is as follows.
SELECT * FROM tableName WHERE condition;
ВОТ
- «ВЫБРАТЬ * ИЗ имени таблицы» это стандарт Оператор SELECT
- "ГДЕ" is the keyword that restricts the result set, and "состояние" is the filter applied to the results. The filter may be a range, a single value or a sub query.
Suppose we want a member’s details from the members table, given membership number 1. The script below achieves that.
SELECT * FROM `members` WHERE `membership_number` = 1;
Executing this script in MySQL верстак on the “myflixdb” produces the result below.
| membership_number | full_names | gender | date_of_birth | physical_address | postal_address | contct_number | |
|---|---|---|---|---|---|---|---|
| 1 | Janet Jones | Female | 21-07-1980 | First Street Plot No 4 | Private Bag | 0759 253 542 | janetjones@yagoo.cm |
A single condition is rarely enough. The sections below combine WHERE with логические операторы.
Предложение WHERE в сочетании с – И ЛОГИЧЕСКИЕ Operaтор
With the AND logical operator, a row is returned only if ALL specified criteria are met.
Suppose we want all the movies in category 2 that were released in 2008. The script below achieves that.
SELECT * FROM `movies` WHERE `category_id` = 2 AND `year_released` = 2008;
Executing the above script against the “myflixdb” produces the following result.
| movie_id | title | director | year_released | category_id |
|---|---|---|---|---|
| 2 | Forgetting Sarah Marshal | Nicholas Stoller | 2008 | 2 |
AND narrows a result set. OR widens it.
Предложение WHERE в сочетании с – OR ЛОГИЧЕСКИЕ Operaтор
With the OR operator, a row is returned if ANY of the specified criteria is met.
The following script gets all the movies in either category 1 or category 2.
SELECT * FROM `movies` WHERE `category_id` = 1 OR `category_id` = 2;
The result set is shown below.
| movie_id | title | director | year_released | category_id |
|---|---|---|---|---|
| 1 | Pirates of the Caribean 4 | Rob Marshall | 2011 | 1 |
| 2 | Forgetting Sarah Marshal | Nicholas Stoller | 2008 | 2 |
Chaining many OR conditions becomes hard to read. The IN keyword solves that.
Предложение WHERE в сочетании с – IN Ключевое слово
The WHERE clause, when used with the IN keyword, only affects rows whose values match the supplied list. IN reduces the number of OR clauses you would otherwise write.
Следующие MySQL WHERE IN query gives rows where membership_number is either 1, 2 or 3.
SELECT * FROM `members` WHERE `membership_number` IN (1,2,3);
The result set is shown below.
| membership_number | full_names | gender | date_of_birth | physical_address | postal_address | contct_number | |
|---|---|---|---|---|---|---|---|
| 1 | Janet Jones | Female | 21-07-1980 | First Street Plot No 4 | Private Bag | 0759 253 542 | janetjones@yagoo.cm |
| 2 | Janet Smith Jones | Female | 23-06-1980 | Melrose 123 | NULL | NULL | jj@fstreet.com |
| 3 | Robert Phil | Male | 12-07-1989 | 3rd Street 34 | NULL | 12345 | rm@tstreet.com |
NOT IN performs the opposite filter.
Предложение WHERE в сочетании с – НЕ В Ключевое слово
With the NOT IN keyword, the WHERE clause excludes every row whose value matches the supplied list.
The following query gives rows where membership_number is NOT 1, 2 or 3.
SELECT * FROM `members` WHERE `membership_number` NOT IN (1,2,3);
The result set is shown below.
| membership_number | full_names | gender | date_of_birth | physical_address | postal_address | contct_number | |
|---|---|---|---|---|---|---|---|
| 4 | Gloria Williams | Female | 14-02-1984 | 2nd Street 23 | NULL | NULL | NULL |
WHERE also filters values by size and equality, using comparison operators.
Предложение WHERE в сочетании с – СРАВНЕНИЕ OperaTORs
The less than (<), equal to (=), greater than (>) and not equal to (<>) comparison operators can all be used with the WHERE clause.
= Равно
Следующий скрипт получает всех членов женского пола из таблицы участников, используя оператор сравнения «равно».
SELECT * FROM `members` WHERE `gender` = 'Female';
The result set is shown below.
| membership_number | full_names | gender | date_of_birth | physical_address | postal_address | contct_number | |
|---|---|---|---|---|---|---|---|
| 1 | Janet Jones | Female | 21-07-1980 | First Street Plot No 4 | Private Bag | 0759 253 542 | janetjones@yagoo.cm |
| 2 | Janet Smith Jones | Female | 23-06-1980 | Melrose 123 | NULL | NULL | jj@fstreet.com |
| 4 | Gloria Williams | Female | 14-02-1984 | 2nd Street 23 | NULL | NULL | NULL |
> Больше
Следующий скрипт получает все платежи, превышающие 2,000, из таблицы платежей.
SELECT * FROM `payments` WHERE `amount_paid` > 2000;
The result set is shown below.
| payment_id | membership_number | payment_date | description | amount_paid | external_reference_number |
|---|---|---|---|---|---|
| 1 | 1 | 23-07-2012 | Movie rental payment | 2500 | 11 |
| 3 | 3 | 30-07-2012 | Movie rental payment | 6000 | NULL |
<> Not Equal To
Следующий скрипт получает все фильмы, идентификатор категории которых не равен 1.
SELECT * FROM `movies` WHERE `category_id` <> 1;
The result set is shown below.
| movie_id | title | director | year_released | category_id |
|---|---|---|---|---|
| 2 | Forgetting Sarah Marshal | Nicholas Stoller | 2008 | 2 |
| 5 | Daddy's Little Girls | NULL | 2007 | 8 |
| 6 | Angels and Demons | NULL | 2007 | 6 |
| 7 | Davinci Code | NULL | 2007 | 6 |
| 9 | Honey mooners | John Schultz | 2005 | 8 |
Quick Reference: WHERE Clause OperaTORs
The operators most often paired with WHERE are summarised below. Full definitions appear in the MySQL operator reference.
| оператор | Смысл | Example condition |
|---|---|---|
| = | Равно | `gender` = ‘Female’ |
| <> | Не равно | `category_id` <> 1 |
| > / | Greater than / less than | `amount_paid` > 2000 |
| И / ИЛИ | All criteria / any criteria | `category_id` = 2 AND `year_released` = 2008 |
| IN / NOT IN | Matches / excludes a list | `membership_number` IN (1,2,3) |
| IS NULL / IS NOT NULL | Tests for missing values | `postal_address` IS NULL |
⚠️ Примечание: NULL never matches = or <>. Use IS NULL or IS NOT NULL when a column may hold no value.
Brain Teaser: Find Movies Returned Late
Suppose we want the rented movies that were not returned by the 25/06/2012 cut-off date. Combining WHERE with the less than operator and AND achieves that.
SELECT * FROM `movierentals` WHERE `return_date` < '2012-06-25' AND movie_returned = 0;
Выполнение приведенного выше сценария в MySQL Workbench дает следующие результаты.
| reference_number | transaction_date | return_date | membership_number | movie_id | movie_returned |
|---|---|---|---|---|---|
| 14 | 21-06-2012 | 24-06-2012 | 2 | 2 | 0 |
