Оператор 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 и Mexico. Чтобы получить все строки из обеих таблиц, можно использовать ключевое слово ALL
следующим образом:
SELECT country
FROM Employee
UNION ALL
SELECT country FROM Contractor;
Подзапрос
Оператор SELECT
внутри оператора SELECT
называется подзапросом. Этот подзапрос может применяться для выбора столбца или в условных предложениях. Подзапрос также может быть вложен в другой запрос типа INSERT
, UPDATE
или DELETE
.
Следующий ниже запрос возвращает множество пользователей, которые находятся в 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
сообщает нам, сколько строк сканируется в запросе, выполняемом для идентификации, и это число должно быть уменьшено для лучшей производительности запроса, поскольку это минимизирует передачу данных.