Оператор SELECT в MySQL 8: полный обзор с примерами кода

Оператор SELECT используется для извлечения данных из одной или многочислен­ных таблиц:


Оглавление статьи[Показать]


SELECT поле1, поле2, поле3 from имя_таблицы 
   [WHERE предложение] 
   [GROUP BY {имя_столбца}]
   [HAVING условие_where] 
 [ORDER BY {имя_столбца} {ASC | DESC}, ...] [LIMIT{OFFSET M}{LIMIT N}]

Это универсальная синтаксическая конструкция, используемая для получения данных из одной таблицы:

  • поля поле1 и поле2 являются именами столбцов таблицы. Для извлечения всех столбцов из таблицы можно использовать выражение *;
  • имя_таблицы задает имя таблицы, из которой необходимо извлечь данные;
  • оператор WHERE может использоваться для задания любого условия в одном и нескольких столбцах;
  • функция GROUP BY используется с агрегатными функциями для группирова­ния результирующих наборов;
  • предложение HAVING необходимо после GROUP BY для фильтрации на осно­ве условий для группы строк или агрегатов. Если мы используем пред­ложение HAVING без GROUP BY, оно будет действовать аналогично оператору WHERE;
  • предложение ORDER BY используется для сортировки результирующих набо­ров таблицы по возрастанию или убыванию;
  • LIMIT используется для ограничения количества строк, возвращаемых опе­ратором SELECT.

Давайте рассмотрим каждый элемент оператора.

Оператор WHERE

Следующий ниже фрагмент кода содержит универсальную синтаксическую кон­струкцию для запроса SELECT с оператором WHERE:

SELECT поле1, поле2, поле3, ..., поле_N from имя_таблицы1, имя_таблицы2
   [WHERE условие1 [AND [OR]] условие2...

Оператор WHERE является необязательной частью запроса SELECT. Для указания условий можно использовать операторы AND или OR. Оператор WHERE также может использоваться с запросом DELETE и UPDATE, который мы обсудим в ближайшее вре­мя в последующей статье.

Ниже приведен список операторов, используемых с оператором WHERE для за­дания условий. Чтобы понять эти операции, давайте рассмотрим пример схемы таблицы. Мы создадим таблицу users с упомянутой здесь схемой, имеющей поля id, first_name, last_name, address, city, state, zip, login_attempts, contact_number, email, username и password:

CREATE TABLE `users` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `first_name` varchar(255),
   `last_name` varchar(255),
   `address` varchar(255),
   `city` varchar(50),
   `state` varchar(2),
   `zip` varchar(10),
   `login_attempts` int(10),
   `contact_number` varchar(20),
   `email` varchar(191),
   `username` varchar(191),
   `password` varchar(255),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB PRIMARY KEY (`id`) AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 
        COLLATE=utf8mb4_unicode_ci;

Равно и не равно

Оператор равенства (=) проверяет, равны ли значения двух полей. Если они со­впадают, то условие становится истинным, и оператор извлекает значение для дальнейшей обработки. Если они не совпадают, то условие должно содержать опе­ратор неравенства (<>). Оно будет извлекать данные на основе условия, которое не совпадает.

Например, следующий ниже запрос используется для получения всех записей с городом, соответствующим значению New York:

SELECT * FROM 'users' WHERE 'city' = 'New York';

Больше и меньше

Оператор больше, чем (>) проверяет, больше ли значение левого поля, чем зна­чение правого поля. Если да, то условие становится истинным. Оператор мень­ше, чем (<) проверяет, меньше ли значение левого поля, чем значение право­го поля. Мы также можем использовать операторы >/< и оператор равенства вместе.

Например, следующий ниже запрос используется для получения всех записей с количеством попыток входа в систему больше, чем 2:

SELECT * FROM 'users' WHERE 'login_attempts' > 2;

LIKE

Оператор LIKE предоставляет простой способ поиска записей в столбце с различ­ными шаблонами. В запросе можно использовать подстановочные символы для построения различных шаблонов. В основном используется два вида подстано­вочных символов. Давайте рассмотрим каждый из них на примере.

  • % (процент): используйте этот подстановочный символ для поиска ноль или более любых символов. Предположим, что мы хотим отыскать пользовате­лей, чье имя начинается с «a». Тогда мы можем применить этот подстано­вочный символ, как показано в приведенном ниже запросе.
    select * from users where username like 'a%';

    В случае если мы хотим найти пользователей, чье имя начинается с «a» и за­канчивается на «s», то запрос с подстановочным символом % будет таким:

    select * from user where username like 'a%s';
  • _ (подчеркивание): используйте этот подстановочный символ там, где нуж­но отыскать записи с шаблоном, где в том месте, где мы указали подчерки­вание (_), может иметься любой символ. Предположим, мы хотим отыскать пользователей, чье имя заканчивается на dmin, и мы не уверены в первом символе. Следовательно, приведенный ниже запрос будет искать результа­ты, где первый символ имени пользователя может быть любым, но он дол­жен заканчиваться на dmin.
    select * from users where username like '_dmin';

    Важно помнить, что он будет учитывать ровно один символ для одного под­черкивания. Следовательно, в этом случае пользователь с именем пользо­вателя как «aadmin» не будет рассматриваться, потому что в запросе указан всего один подстановочный символ с подчеркиванием.

IN/NOT IN

Оператор IN используется для сравнения нескольких значений в операторе WHERE. Например, следующий ниже запрос используется для поиска всех пользователей, имеющих город new york или chicago:

select * from users where city IN ('new york','chicago')

Оператор NOT IN работает наоборот, например чтобы найти всех пользователей, у которых нет ни города new york, ни города chicago, используется:

select * from users where city NOT IN ('new york','chicago');

BETWEEN

Оператор BETWEEN может использоваться в том случае, когда мы хотим получить записи, которые входят в определенный диапазон. Этот диапазон может быть лю­бым, таким как текст, даты или цифры. Предположим, мы хотим отыскать поль­зователей, дата создания записи о которых находится между 1 июля и 16 июля 2017 года. Тогда приведенный ниже запрос с предложением BETWEEN может нам помочь.

select * from users where created_at BETWEEN '2017-07-01 00:00:00' AND '2017-07-16 00:00:00';

Подобным образом мы можем также задавать диапазон в числах для поиска пользователей, которые принадлежат к этому конкретному диапазону. Например, если мы хотим получить студентов, чьи оценки находятся в диапазоне от 70 до 80, то оператор BETWEEN может быть применен для определения диапазона.

Предложение ORDER BY

Предложение ORDER BY помогает в получении записей в упорядоченном виде. Оно обеспечивает сортировку данных по определенному столбцу в порядке возрас­тания или убывания. По умолчанию сортировка выполняется в порядке возрас­тания, но мы также можем явно указать способ сортировки с помощью ключевых слов ASC и DESC. Если мы зададим ASC, то оно будет сортировать в порядке возрас­тания, в то время как ключевое слово DESC будет сортировать в порядке убывания. Ниже приведен запрос, который найдет всех пользователей и выведет их в поряд­ке возрастания по столбцу city.

SELECT * FROM users ORDER BY city ASC;

Кроме того, с помощью предложения ORDER BY можно сортировать в нескольких столбцах. Как показано в приведенном ниже запросе, где мы упорядочиваем по столбцам city и username, мы можем передать несколько столбцов.

SELECT * FROM users ORDER BY city, username;

Предложение LIMIT

Используя предложение LIMIT, мы можем получить только некоторое количество строк из больших блоков данных. Это помогает ограничить количество строк, возвращаемых в результирующем наборе. Предположим, что в таблице сущест­вуют тысячи строк, но нам требуется только 10 записей, тогда вместо извлечения тысяч записей это предложение помогает получить лишь 10 записей. Это дей­ствительно помогает настраивать производительность при поиске больших на­боров данных.

С предложением LIMIT можно передавать один или два аргумента. В случае двух аргументов один из них будет смещением, которое задает сдвиг первой возвра­щаемой строки от начала. В то время как второй аргумент будет количеством, ко­торое задает максимальное количество строк, которые будут возвращены. Этот аргумент должен быть нулевым или положительным. Взгляните на приведенный ниже запрос, где мы извлекли 10 записей из таблицы пользователей, начиная с 5-й строки.

SELECT * FROM users limit 5, 10;

Если с предложением LIMIT указать всего один аргумент, то аргумент будет счи­таться количеством строк. Например, следующий ниже запрос используется для извлечения 10 строк из таблицы пользователей:

SELECT * FROM users limit 10;

На данный момент мы видели получение данных из одной таблицы; если мы хотим получить данные из нескольких таблиц, используются ключевые слова JOIN и UNION.

Операции соединения SQL

Операция соединения используется для извлечения данных из нескольких таб­лиц. Например, если есть две таблицы order и customer и мы хотим получить дан­ные, то это можно сделать с помощью предложения JOIN.

Различные типы предложения JOIN следующие:

  • INNER JOIN: внутреннее соединение возвращает только те записи, значения которых совпадают в обеих таблицах;
  • CROSS JOIN: перекрестное соединение возвращает только те записи, которые имеют совпадающие значения в левой или правой таблице;
  • LEFT JOIN: левое соединение возвращает все записи из левой таблицы и толь­ко совпадающие записи из правой таблицы;
  • RIGHT JOIN: правое соединение возвращает все записи из правой таблицы и только совпадающие записи из левой таблицы.

На следующем ниже рисунке показан краткий пример для рассмотрения:

INNER JOIN

Внутреннее соединение возвращает записи, совпадающие в обеих таблицах. На­пример, ниже приведена таблица заказов order, используемая в приложениях электронной коммерции:

order_id

customer_id

order_amount

order_date

ship_id

1001

2

7

2017-07-18

3

1002

37

3

2017-07-19

1

1003

77

8

2017-07-20

2

 Далее приведем пример таблицы клиентов customer, используемой в приложе­ниях электронной коммерции, которая содержит данные о клиентах:

customer_id

name

country

city

postal_code

1

Alfreds Futterkiste

Germany

Berlin

12209

2

Ana Trujillo

Mexico

Mйxico D.F.

05021

3

Antonio Moreno

Mexico

Mйxico D.F.

05023

Следующий ниже запрос будет получать все записи заказа со сведениями о кли­енте. Поскольку идентификаторы клиентов 37 и 77 отсутствуют в таблице customer, будут получены только совпадающие строки, за исключением идентификаторов клиентов 37 и 77:

SELECT order.order_id, customer.name
FROM order
INNER JOIN customer ON order.customer_id = customer.customer_id;

 

LEFT JOIN

Левое соединение LEFT JOIN извлекает все записи из левой таблицы и только со­впадающие записи из правой таблицы. Если применить пример таблиц клиентов и заказов с левым соединением, то оно будет извлекать все записи из таблицы заказов order, даже если в правой таблице нет совпадений (customer). Чтобы получить все сведения о заказах для клиента, можно использовать следующий ниже запрос:

SELECT order.order_id, customer.name
FROM order
LEFT JOIN customer ON order.customer_id = customer.customer_id;

 

RIGHT JOIN

Правое соединение RIGHT JOIN извлекает все записи из правой таблицы и общие записи из левой таблицы. Если применить пример таблиц клиентов и заказов с правым соединением, то оно будет извлекать все записи из таблицы клиентов customer, даже если в левой таблице (order) нет совпадений. Чтобы получить все сведения о клиентах с заказом, можно использовать следующий ниже запрос:

SELECT order.order_id, customer.name
FROM order
RIGHT JOIN customer ON order.customer_id = customer.customer_id;

 

CROSS JOIN

Перекрестное соединение CROSS JOIN возвращает все записи, в которых есть совпа­дение в левой или правой записи таблицы. Если мы возьмем пример с таблицами заказов и клиентов, то оно вернет пять строк со сведениями о клиентах и заказах:

SELECT order.order_id, customer.name 
FROM order 
OUTER_JOIN customer ON order.customer_id = customer.customer_id;

UNION

Ключевое слово UNION используется для объединения результатов нескольких за­просов SELECT в одну таблицу. Каждый оператор SELECT, используемый в запросе UNION, должен иметь одинаковое количество столбцов, а для каждого столбца тре­буется одинаковый тип данных. Имена столбцов из первого запроса SELECT ис­пользуются в качестве столбцов для возвращаемых результатов.

Например, приведем пример таблицы сотрудников Employee:

employee_id

name

city

postal_code

country

1

Robert

Berlin

12209

Germany

2

Mac

Mexico D.F.

5021

Mexico

3

Patel

Mexico D.F.

5023

Mexico

 Далее приведем пример таблицы подрядчиков Contractor:

contractor_id

name

city

postal_code

country

1

Dave

Berlin

12209

Germany

2

Robert

Mexico D.F.

5021

Mexico

3

Patel

Mexico D.F.

5023

Mexico

Как показано в следующем ниже примере, имена столбцов из первого запроса SELECT используются в качестве столбцов для возвращаемых результатов:

 

SELECT 'Employee' As Type, name, city, country FROM Employee
UNION
SELECT 'Contractor', name, city, country FROM Contractor;

Следующий ниже пример объединения UNION удаляет повторяющиеся строки из результата; если мы не хотим удалять повторяющиеся строки, можно применить ключевое слово ALL:

SELECT country 
FROM Employee 
UNION SELECT country FROM Contractor;

Этот запрос возвращает две строки из предыдущей таблицы - Germany и Mex­ico. Чтобы получить все строки из обеих таблиц, можно использовать ключевое слово ALL следующим образом:

SELECT country 
FROM Employee 
UNION ALL 
      SELECT country FROM Contractor;

Подзапрос

Оператор SELECT внутри оператора SELECT называется подзапросом. Этот под­запрос может применяться для выбора столбца или в условных предложениях. Подзапрос также может быть вложен в другой запрос типа INSERT, UPDATE или DE­LETE.

Следующий ниже запрос возвращает множество пользователей, которые нахо­дятся в Ahmedabad, используя вложенный запрос:

SELECT firstName, lastName FROM users
WHERE city IN
   (SELECT city FROM cities WHERE city = 'Ahmedabad');

 

Оптимизация запросов SELECT

Запросы SELECT используются для получения данных на динамических веб­страницах, поэтому настройка этих операторов обеспечивает хорошую произ­водительность, что очень важно. Ниже приведены некоторые рекомендации по оптимизации запросов.

  • Убедитесь, что в таблицах есть индексы. Индексы всегда помогают ускорить фильтрацию и получение результатов. Индексы можно задавать в операто­ре WHERE запроса SELECT.
  • Индексы также минимизируют количество полных сканирований в боль­ших таблицах.
  • Настройка буферного пула InnoDB, кеша ключей MylSAM и кеша запросов MySQL помогает кешировать результаты, которые приведут к более быст­рым извлечениям повторяющихся результатов. Размер кеш-памяти можно настроить так, чтобы она обеспечивала более быстрый доступ, предостав­ляя результаты только из кеша.
  • Отрегулируйте размер и свойства используемых MySQL областей памя­ти, чтобы кешировать буферный пул InnoDB, кеш ключей MylSAM и кеш запросов MySQL. Это помогает выполнять повторные запросы SELECT быстрее.
  • Мы должны использовать оператор WHERE вместо HAVING, если мы не ис­пользуем предложение GROUP BY или другие агрегатные функции, такие как COUNT(), MIN(), MAX(), AVG() и т. д.
  • Используйте инструкцию EXPLAIN для анализа запроса с операторами WHERE, JOIN и индексами.

Теперь давайте рассмотрим, в чем заключается полезность инструкции EXPLAIN для оптимизации производительности запросов:

EXPLAIN SELECT * FROM `sales_order_item` i
INNER JOIN sales_order o ON i.order_id = o.entity_id
INNER JOIN catalog_product_entity p ON p.sku = i.sku
INNER JOIN customer_entity c ON c.entity_id = o.customer_id
WHERE i.order_id = 42

Приведенный ниже результат является выводом выполненного вопроса: 

*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: o
 partitions: NULL
 type: const
possible_keys: PRIMARY,SALES_ORDER_CUSTOMER_ID
 key: PRIMARY
 key_len: 4
 ref: const
 rows: 1
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
 id: 1
 select_type: SIMPLE
 table: c
 partitions: NULL
 type: const
possible_keys: PRIMARY
 key: PRIMARY
 key_len: 4
 ref: const
 rows: 1
 filtered: 100.00
 Extra: NULL
*************************** 4. row ***************************
 id: 1
 select_type: SIMPLE
 table: p
 partitions: NULL
 type: ref
possible_keys: CATALOG_PRODUCT_ENTITY_SKU
 key: CATALOG_PRODUCT_ENTITY_SKU
 key_len: 195
 ref: ecommerce.i.sku
 rows: 1
 filtered: 100.00
 Extra: Using index condition
 4 rows in set (0.01 sec)

В приведенных выше результатах possible_keys показывает, какие индексы применяются к этому запросу, а KEY говорит о том, какой из них был фактически использован. В нашем примере каждое соединение JOIN использует индексный ключ; путем создания индекса мы должны попытаться избежать значения NULL ключа. Поле ROWS сообщает нам, сколько строк сканируется в запросе, выполняе­мом для идентификации, и это число должно быть уменьшено для лучшей произ­водительности запроса, поскольку это минимизирует передачу данных.

Вас заинтересует / Intresting for you:

MyISAM: подсистема хранения ба...
MyISAM: подсистема хранения ба... 1677 просмотров Ирина Светлова Wed, 04 Aug 2021, 19:39:29
InnoDB: подсистема хранения ба...
InnoDB: подсистема хранения ба... 3207 просмотров Ирина Светлова Mon, 07 Jan 2019, 06:34:07
Использование MySQL в качестве...
Использование MySQL в качестве... 1585 просмотров Андрей Волков Tue, 01 Oct 2019, 05:41:51
Новые возможности базы данных ...
Новые возможности базы данных ... 217 просмотров Дэйзи ак-Макарова Wed, 04 Aug 2021, 04:13:52
Войдите чтобы комментировать

Обсудить эту статью

INFO: Вы отправляете сообщение как 'Гость'


apv аватар
apv ответил в теме #10112 4 мес. 15 ч. назад
Крутейший мануал по работе с оператором SELECT в SQL запросах для MySQL 8!!! Лучше всякой документации, написанной, как правило, сухим языком...