Что такое сложные запросы
Основы Transact SQL: Сложные (многотабличные запросы)(Урок 5, часть 1)
Основы Transact SQL: Сложные (многотабличные запросы)
В SQL сложные запросы являются комбинацией простых SQL-запросов. Каждый простой запрос в качестве ответа возвращает набор записей (таблицу), а комбинация простых запросов возвращает результат тех или иных операций над ответами на простые запросы.
В SQL сложные запросы получаются из других запросов следующими способами:
Подзапросы
Подзапрос — это запрос на выборку данных, вложенный в другой запрос. Подзапрос всегда заключается в круглые скобки и выполняется до содержащего выражения. Внешний запрос, содержащий подзапрос, если только он сам не является подзапросом, не обязательно должен начинаться с оператора SELECT. В свою очередь, подзапрос может содержать другой подзапрос и т. д. При этом сначала выполняется подзапрос, имеющий самый глубокий уровень вложения, затем содержащий его подзапрос и т. д. Часто, но не всегда, внешний запрос обращается к одной таблице, а подзапрос — к другой. На практике именно этот случай наиболее интересен.
Простые подзапросы
Простые подзапросы характеризуются тем, что они формально никак не связаны с содержащими их внешними запросами. Это обстоятельство позволяет сначала выполнить подзапрос, результат которого затем используется для выполнения внешнего запроса. Кроме простых подзапросов, существуют еще и связанные (коррелированные) подзапросы, которые будут рассмотрены в следующем разделе.
Рассматривая простые подзапросы, следует выделить три частных случая:
Тип возвращаемой подзапросом таблицы определяет, как можно ее использовать и какие операторы можно применять в содержащем выражении для взаимодействия с этой таблицей. По завершении выполнения содержащего выражения таблицы, возвращенные любым подзапросом, выгружаются из памяти. Таким образом, подзапрос действует как временная таблица, областью видимости которой является выражение (т. е. после завершения выполнения выражения сервер высвобождает всю память, отведенную под результаты подзапроса).
Подзапросы, возвращающие единственное значение
Допустим, из таблицы Customer требуется выбрать данные обо всех клиентах из Казани. Это можно сделать с помощью следующего запроса.
WHERE IdCity = ( SELECT idCity FROM City WHERE CityName = ‘Казань’ )
В данном запросе сначала выполняется подзапрос (SELECT idCity FROM City WHERE CityName = ‘Казань’). Он возвращает единственное значение (а не набор записей, поскольку по полю City организовано ограничение уникальности) – уникальный идентификатор города Казань. Если сказать точнее, то данный подзапрос возвращает единственную запись, содержащую единственное поле. Далее выполняется внешний запрос, который выводит все столбцы таблицы Customer и записи, в которых значение столбца IdCity равно значению, полученному с помощью подзапроса. Таким образом, сначала выполняется подзапрос, а затем внешний запрос, использующий результат подзапроса.
Задание для самостоятельной работы: По аналогии с предыдущим примером сформулируйте запрос, возвращающий все заказы, в которых содержится заданный товар (по названию товара).
Подзапросы, возвращающие список значений из одного столбца таблицы
Подзапрос, вообще говоря, может возвращать несколько записей. Чтобы в этом случае в условии внешнего оператора WHERE можно было использовать операторы сравнения, требующие единственного значения, используются кванторы, такие как ALL (все) и SOME (или ANY) (некоторый).
Рассмотрим общий случай использования запросов с кванторами ALL и SOME. Пусть имеются две таблицы: T1, содержащая как минимум столбец A, и T2, содержащая, по крайней мере, один столбец B. Тогда запрос с квантором ALL можно сформулировать следующим образом:
WHERE A оператор_сравнения ALL ( SELECT B FROM T2)
Здесь оператор_сравнения обозначает любой допустимый оператор сравнения. Данный запрос должен вернуть список всех тех значений столбца A, для которых оператор сравнения истинен для всех значений столбца B.
Запрос с квантором SOME, очевидно, имеет аналогичную структуру. Он должен вернуть список всех тех значений столбца A, для которых оператор сравнения истинен хотя бы для какого-нибудь одного значения столбца B.
Запрос: Список всех клиентов, проживающих в городах Казань или Елабуга.
Предыдущий запрос может быть также реализован и с использованием оператора IN, который рассматривался в разделе “Фильтрация данных”.
WHERE IdCity IN ( SELECT IdCity FROM City WHERE CityName IN ( ‘Казань’, ‘Елабуга’ ))
Напомним — он проверяет вхождение элемента во множество, в качестве элемента может выступать имя столбца или скалярное выражение, а в качестве множества — явно заданный список значений или подзапрос. Использование подзапроса в качестве второго операнда IN также как и кванторы позволяет избежать ограничения на единственность значения, возвращаемого подзапросом.
С помощью оператора IN можно проверять не только наличие значения в наборе значений, но и его отсутствие. Делается это добавлением оператора отрицания NOT. Вот другой вариант предыдущего запроса:
WHERE IdCity NOT IN ( SELECT IdCity FROM City WHERE CityName IN ( ‘Казань’, ‘Елабуга’ ))
Этот запрос возвращает всех клиентов, кроме тех которые проживают в городах Казань и Елабуга.
Аналогичный запрос с использование квантора ALL:
Задание для самостоятельной работы: Cформулируйте запрос, возвращающий список всех клиентов (с указанием фамилии и имени), совершивших заказ за определенный период времени.
Подзапросы, возвращающие набор записей
Подзапрос можно вставлять не только в операторы WHERE и HAVING, но и в оператор FROM.
Здесь таблице, возвращаемой подзапросом в операторе FROM, присваивается псевдоним t, а внешний запрос выделяет столбцы этой таблицы и, возможно, записи в соответствии с некоторым условием, которое указано в операторе WHERE.
Связанные (коррелированные) подзапросы
Все приведенные до сих пор запросы не зависели от своих содержащих выражений, т. е. могли выполняться самостоятельно и представлять свои результаты для проверки. Связанный подзапрос (коррелированный), напротив, зависит от содержащего выражения, из которого он ссылается на один или более столбцов. В отличие от несвязанного подзапроса, который выполняется непосредственно перед выполнением содержащего выражения, связанный подзапрос выполняется по разу для каждой строки-кандидата (это строки, которые предположительно могут быть включены в окончательные результаты). Например, следующий запрос использует связанный подзапрос для подсчета количества заказов у каждого клиента. Затем основной запрос выбирает тех клиентов, у которых больше одного заказа.
WHERE 1 SELECT COUNT (*) FROM [Order] r WHERE r.IdCust = c.IdCust)
Связанные подзапросы часто используются с условиями сравнения (в предыдущем примере SELECT IdProd, [Description]
WHERE EXISTS ( SELECT * FROM OrdItem oi WHERE oi.IdProd = p.IdProd)
Для поиска подзапросов, не возвращающих строки, можно использовать оператор EXISTS совместно с оператором отрицания NOT. В частности чтобы предыдущий запрос возвращал все товары, которые ни разу не заказывались, его можно модифицировать следующим образом.
SELECT IdProd, [Description]
WHERE NOT EXISTS ( SELECT * FROM OrdItem oi WHERE oi.IdProd = p.IdProd)
Задание для самостоятельной работы: Cформулируйте запрос, возвращающий список всех заказов с суммарной стоимость более заданной величины.
Вложенные запросы (SQL Server)
В примерах из этой статьи используется база данных AdventureWorks2016. Образцы баз данных AdventureWorks можно скачать здесь.
Основы вложенных запросов
Вложенный запрос по-другому называют внутренним запросом или внутренней операцией выбора, в то время как инструкцию, содержащую вложенный запрос, называют внешним запросом или внешней операцией выбора.
Многие инструкции языка Transact-SQL, включающие подзапросы, можно записать в виде соединений. Другие запросы могут быть осуществлены только с помощью подзапросов. В языке Transact-SQL обычно не бывает разницы в производительности между инструкцией, включающей вложенный запрос, и семантически эквивалентной версией без вложенного запроса. Дополнительные сведения о том, как SQL Server обрабатывает запросы, см. в разделе Обработка инструкций SQL. Однако в некоторых случаях, когда проверяется существование, соединения показывают лучшую производительность. В противном случае для устранения дубликатов вложенный запрос должен обрабатываться для получения каждого результата внешнего запроса. В таких случаях метод работы соединений дает лучшие результаты.
Вложенный во внешнюю инструкцию SELECT запрос, имеет следующие компоненты:
Запрос SELECT вложенного запроса всегда заключен в скобки. Он не может включать предложения COMPUTE или FOR BROWSE и может включать предложение ORDER BY только вместе с предложением TOP.
Если таблица появляется только во вложенном запросе, а не во внешнем запросе, в этом случае столбцы данной таблицы не могут быть включены в выходные данные (список выборки внешнего запроса).
Инструкции, включающие вложенные запросы, обычно имеют один из следующих форматов:
В некоторых инструкциях языка Transact-SQL вложенный запрос может рассматриваться как отдельный запрос. Обычно результаты вложенного запроса подставляются во внешний запрос (хотя SQL Server может обрабатывать инструкции Transact-SQL с вложенными запросами и по-другому).
Существуют три основных типа подзапросов, которые:
Правила вложенных запросов
На вложенный запрос распространяются следующие ограничения:
Уточнение имен столбцов во вложенных запросах
В следующем примере столбец BusinessEntityID в предложении WHERE внешнего запроса неявно уточняется именем таблицы, используемой в предложении FROM внешнего запроса (Sales.Store). Ссылка на столбец CustomerID в списке выборки вложенного запроса уточняется именем таблицы с помощью предложения FROM вложенного запроса, то есть Sales.Customer.
Общее правило состоит в том, что имена столбцов в инструкции неявно уточняются именем таблицы, указанной в предложении FROM того же уровня вложенности. Если столбец не существует в таблице, на которую ссылается предложение FROM вложенного запроса, он неявно уточняется именем таблицы, указанной в предложении FROM внешнего запроса.
Вот как выглядит этот запрос с явно указанными неявными соглашениями:
Никогда не будет ошибочным явно указать имя таблицы; также всегда можно перекрыть неявные соглашения об именах таблиц полностью уточненными именами столбцов
Если столбец, на который есть ссылка во вложенном запросе, не существует в таблице, указанной в предложении FROM вложенного запроса, но существует в таблице, на которую ссылается предложение FROM внешнего запроса, запрос будет выполнен без ошибок. SQL Server неявно уточнит имя столбца во вложенном запросе с помощью имени таблицы внешнего запроса.
Множественные уровни вложенности
Каждый вложенный запрос, в свою очередь, может содержать один или более вложенных запросов. В инструкцию можно вложить любое количество вложенных запросов.
Следующий запрос осуществляет поиск сотрудников, занимающих должность менеджера по продажам.
Самый глубоко вложенный запрос возвращает идентификаторы указанных сотрудников. Запрос уровнем выше оперирует с полученными идентификаторами и возвращает контактные идентификаторы сотрудников. Наконец, во внешнем запросе по полученным контактным идентификаторам извлекаются имена сотрудников.
Этот запрос также можно выразить с помощью соединения:
Связанные вложенные запросы
Результат для нескольких запросов может быть получен путем выполнения одного вложенного запроса и подстановки полученного результата или результатов в предложение WHERE внешнего запроса. В запросах, содержащих коррелированные вложенные запросы (также называемые повторяющимися вложенными запросами), вложенный запрос зависит по значению от внешнего запроса. Это означает, что выполнение вложенного запроса повторяется по одному разу для каждой строки, которая может быть выбрана внешним запросом. Такой запрос получает по одной записи для имени и фамилии каждого сотрудника, который в таблице SalesPerson имеет сумму премиальных, равную 5000, с соответствующими идентификаторами сотрудников в таблицах Employee и SalesPerson.
Результатом является 0.00 ( Syed Abbas не получал премиальных, потому что не является менеджером по продажам), поэтому выполнение внешнего запроса приводит к следующему результату:
Коррелированные вложенные запросы могут также включать в предложение FROM функции с табличным значением, указывая для них в качестве аргументов столбцы таблиц из внешнего запроса. В этом случае для каждой строки внешнего запроса выполняется функция с табличным значением, как и в случае с вложенным запросом.
Типы вложенных запросов
Вложенные запросы могут быть указаны во многих местах:
Вложенные запросы с псевдонимами таблицы
Многие инструкции, где вложенный и внешний запросы ссылаются на одну и ту же таблицу, могут быть переформулированы как самосоединения (соединения таблицы с самой собой). Например, можно найти адреса сотрудников из конкретного региона с помощью вложенного запроса:
Можно также использовать самосоединение:
Псевдонимы таблиц e1 и e2 необходимы, так как соединенная сама с собой таблица выступает в двух ролях. Псевдонимы можно также использовать во вложенных запросах, где и внешний, и внутренний запросы ссылаются на одну и ту же таблицу.
При использовании явных псевдонимов таблицы понятно, что ссылка на Person.Address во вложенном запросе означает не то же, что и ссылка во внешнем запросе.
Вложенные запросы с ключевым словом IN
Результат вложенного запроса, в котором присутствует ключевое слово IN (или NOT IN ) — это список из нуля или более значений. После того как вложенный запрос вернул результат, он используется внешним запросом.
Следующий запрос ищет названия всех колес, произведенных компанией Adventure Works Cycles.
Эта инструкция выполняется в два шага. Сначала внутренний запрос возвращает номер идентификатора подкатегории по соответствию названию «Wheel» (17). Затем это значение подставляется во внешний запрос, который находит все названия изделий, имеющих соответствующие идентификаторы подкатегорий в столбце Production.Product.
Единственная разница в использовании соединения и вложенного запроса для этой и аналогичных задач заключается в том, что объединение позволяет включить в результат столбцы, содержащиеся в нескольких таблицах. Например: если нужно включить в результат название подкатегории, следует пользоваться соединением:
Следующий запрос ищет названия всех поставщиков, имеющих высокий кредитный рейтинг, у которых компания Adventure Works Cycles заказала как минимум 20 позиций, и средний срок поставки у которых не превышает 16 дней.
Выполняется внутренний запрос и возвращаются номера идентификаторов поставщиков, которые соответствуют определениям вложенного запроса. Затем выполняется внешний запрос. Обратите внимание, что в предложение WHERE как внутреннего, так и внешнего запроса может быть включено несколько условий.
При использовании соединения тот же запрос будет выражен так:
Соединение всегда может быть выражено в виде вложенного запроса. Вложенный запрос часто, но не всегда может быть выражен в виде соединения. Это происходит потому, что соединения симметричны: можно соединить таблицы A и B в любом порядке и получить одинаковый результат. Для вложенных запросов это не всегда справедливо.
Вложенные запросы с ключевым словом NOT IN
Вложенные запросы с ключевым словом NOT IN также возвращают список из нуля или более значений.
В следующем запросе выполняется поиск названий продуктов, не являющихся готовыми велосипедами.
Эту инструкцию нельзя преобразовать в соединение. Аналогичное соединение по неравенству имеет другой смысл: оно находит названия продуктов, которые принадлежат какой-либо подкатегории, отличной от готового велосипеда.
В следующем примере удваивается значение столбца ListPrice таблицы Production.Product. Вложенный запрос в предложении WHERE ссылается на таблицу Purchasing.ProductVendor для ограничения количества обновляемых строк таблицы Product только теми, у которых идентификатор BusinessEntity равен 1540.
Для ясности, если на одну и ту же таблицу ссылаются в других вложенных запросах, используйте псевдонимы целевой таблицы:
Вложенные запросы с операторами сравнения
Чтобы использовать подзапрос, начинающийся с немодифицированного оператора сравнения, необходимо достаточно хорошо знать свои данные и природу проблемы, чтобы быть уверенным, что вложенный запрос возвратит точно одно значение.
Однако если сотрудник Linda Mitchell работал более чем с одной территорией продаж, вы получите сообщение об ошибке. Вместо оператора сравнения = может использоваться формулировка IN (также может использоваться =ANY ).
Вложенные запросы, начинающиеся с немодифицированных операторов сравнения, часто включают агрегатные функции, потому что они возвращают одиночное значение. Например, следующая инструкция находит названия всех продуктов, у которых цена по прейскуранту больше, чем средняя цена по прейскуранту.
Так как вложенные запросы, начинающиеся с неизмененных операторов сравнения, должны возвращать одиночное значение, они не могут включать предложения GROUP BY или HAVING (за исключением случаев, когда достоверно известно, что предложение GROUP BY или HAVING возвратит одиночное значение). Например, следующий запрос находит продукты, оцененные выше, чем самый дешевый продукт, который находится в столбце ProductSubcategoryID 14.
Рассмотрим, например оператор сравнения >: >ALL будет означать «больше любого значения». Другими словами, это сравнение с максимальным значением. Например, >ALL (1, 2, 3) означает «больше 3». >ANY означает «больше по крайней мере одного значения», т. е. «больше минимума». Поэтому >ANY (1, 2, 3) означает «больше 1». Чтобы строка результата вложенного запроса с >ALL удовлетворяла условию, заданному внешним запросом, значение в столбце, для которого вводится вложенный запрос, должно быть больше каждого значения из списка, возвращаемого вложенным запросом.
Аналогичным образом, чтобы строка результата вложенного запроса с >ANY удовлетворяла условию, заданному внешним запросом, значение в столбце, для которого вводится вложенный запрос, должно быть больше хотя бы одного значения из списка, возвращаемого вложенным запросом.
Если вложенный запрос не возвращает значений, весь запрос не возвратит никаких значений.
Вот результирующий набор, возвращаемый любым из этих запросов:
Однако оператор <>ANY отличается от NOT IN :
Например, следующий запрос отобразит заказчиков, находящихся на территории, где не работает ни один менеджер по продажам.
В результат включены все заказчики, кроме тех, чьим территориям продаж соответствует NULL, так как любая территория, назначенная заказчику, обслуживается менеджером по продажам. Внутренний запрос находит все территории продаж, обслуживаемые менеджерами по продажам, а затем для каждой территории внешний запрос находит заказчиков, которые ей не принадлежат.
По этой же причине, если использовать NOT IN в этом запросе, в результат не войдет ни один из заказчиков.
Вложенные запросы с ключевым словом EXISTS
Вложенный запрос, созданный с помощью ключевого слова EXISTS, имеет следующий синтаксис:
WHERE [NOT] EXISTS (subquery)
Следующий запрос ищет названия всех продуктов, которые находятся в подкатегории Wheels:
Обратите внимание на то, что вложенные запросы, введенные с помощью ключевого слова EXISTS, отличаются от других вложенных запросов следующим образом.
Вложенные запросы с ключевым словом NOT EXISTS
Например чтобы найти имена продуктов, не находящихся в подкатегории wheels:
Вложенные запросы, используемые вместо выражения
Следующий пример показывает, как можно использовать это улучшение. Запрос находит цены на все горные велосипеды, их среднюю цену и разницу между средней ценой и ценой каждого горного велосипеда.
Что такое сложные запросы
Reg.ru: домены и хостинг
Крупнейший регистратор и хостинг-провайдер в России.
Более 2 миллионов доменных имен на обслуживании.
Продвижение, почта для домена, решения для бизнеса.
Более 700 тыс. клиентов по всему миру уже сделали свой выбор.
Бесплатный Курс «Практика HTML5 и CSS3»
Освойте бесплатно пошаговый видеокурс
по основам адаптивной верстки
на HTML5 и CSS3 с полного нуля.
Фреймворк Bootstrap: быстрая адаптивная вёрстка
Пошаговый видеокурс по основам адаптивной верстки в фреймворке Bootstrap.
Научитесь верстать просто, быстро и качественно, используя мощный и практичный инструмент.
Верстайте на заказ и получайте деньги.
Что нужно знать для создания PHP-сайтов?
Ответ здесь. Только самое важное и полезное для начинающего веб-разработчика.
Узнайте, как создавать качественные сайты на PHP всего за 2 часа и 27 минут!
Создайте свой сайт за 3 часа и 30 минут.
После просмотра данного видеокурса у Вас на компьютере будет готовый к использованию сайт, который Вы сделали сами.
Вам останется лишь наполнить его нужной информацией и изменить дизайн (по желанию).
Изучите основы HTML и CSS менее чем за 4 часа.
После просмотра данного видеокурса Вы перестанете с ужасом смотреть на HTML-код и будете понимать, как он работает.
Вы сможете создать свои первые HTML-страницы и придать им нужный вид с помощью CSS.
Бесплатный курс «Сайт на WordPress»
Хотите освоить CMS WordPress?
Получите уроки по дизайну и верстке сайта на WordPress.
Научитесь работать с темами и нарезать макет.
Бесплатный видеокурс по рисованию дизайна сайта, его верстке и установке на CMS WordPress!
Хотите изучить JavaScript, но не знаете, как подступиться?
После прохождения видеокурса Вы освоите базовые моменты работы с JavaScript.
Развеются мифы о сложности работы с этим языком, и Вы будете готовы изучать JavaScript на более серьезном уровне.
*Наведите курсор мыши для приостановки прокрутки.
БД MySQL (сложные запросы, агрегатные функции, оценка производительности)
В этом уроке мы поговорим о следующих моментах, касающихся работы с БД MySQL: вы узнаете, как составлять сложные запросы, как использовать агрегатные функции, объединения таблиц и как оценивать производительность запросов.
Связи в БД
Избыточность же — это переполнение таблиц повторяющимися данными.
Для начала поговорим о виртуальных связях таблиц. Что представляет собой такая связь?
Таблица User_docs подчинена таблице Users, поэтому в ней есть ссылка на таблицу Users (user_id_ref).
Внимание! Впредь, если подразумевается, что некоторые данные могут дублироваться, стоит их выносить в отдельную таблицу.
Запрос из двух таблиц
Функциональность MySQL не ограничивается запросом вида SELECT * FROM table. Это самый простой запрос. Такого запроса достаточно, если весь необходимый набор данных содержится в одной таблице. Но мы учимся правильно проектировать БД, поэтому и запросы у нас будут несколько сложнее и функциональнее.
Предлагаю данный момент разобрать на примерах Интернет-каталога.
В этой таблице мы будем хранить наши продукты. Как вы заметили, я заранее добавил в таблицу поле Group_id_ref. Это поле привязывает продукт к конкретной группе. Создадим таблицу групп товаров:
Кроме того, часто встречается ситуация, когда товары имеют дополнительные свойства, такие как Цвет, Размер и пр.
Добавим таблицу Colors:
И таблицу Sizes (Размеры):
Теперь мы можем хранить все наши данные по Продукту. Заполним таблицы тестовыми данными.
Теперь мы имеем все данные для одного продукта. Но ведь не всегда у всех товаров должны быть все возможные реквизиты цвета и размера. Иногда бывают костюмы маломерки, иногда наоборот.
Добавим таблицы, связывающие товары с реквизитами:
В этой таблице мы будем хранить реквизиты для каждого продукта. Добавим тестовые данные:
Теперь наш тестовый продукт имеет два реквизита: Цвет и Размер.
Поясню, как так получилось. Для этого рассмотрим таблицу Product_values. В этой таблице нет никаких текстовых записей, присутствуют только идентификаторы.
— Record_id – уникальный идентификатор нашей таблицы. В прошлой статье я указывал на необходимость этого поля.
— Value_id_ref – Ссылка на реквизиты товара.
— Value_type – Тип реквизита. 1- цвет, 2- размер и пр., если у вас таковые будут.
Давайте посмотрим, как построить запрос, чтобы получить наши данные. Сначала получим список групп. Обычно в каталогах дерево продуктов начинается именно с групп.
Тут все просто. При помощи Group_id мы формируем ссылку на список товаров в группе. Формировать ссылку можно как в запросе, так и в скрипте, на котором написан ваш каталог.
Результат выборки выглядит так:
В каталоге на сайте такую выборку можно использовать в списке товаров. Product_id используем для формирования ссылки на конкретный товар.
Для конкретного товара запрос будет похожим, за исключением того, что мы укажем p.Product_id = 1.
Немного поясню, что такое «р.» в данном запросе. Для СУБД запрос вида:
То есть всегда поле указывается с таблицей. В принципе, имя таблицы можно не писать, если поля ВО ВСЕХ(!) таблицах запроса именуются по-разному.
Но такой идеальной ситуации, как правило, не бывает и логичнее указывать не имя таблицы а ее алиас.
В этом случае p – это Products, а g – это Product_groups. Теперь в запросе нет необходимости писать имя таблицы целиком, достаточно описать только алиас.
Внимание! В громоздких запросах алиасы значительно ускоряют написание. Так же такой подход к написанию запроса более корректен.
Итак, для конкретного товара запрос будет таковым:
Теперь получим реквизиты товара. Список расцветок получаем запросом:
Подобным запросом получим и размеры.
Немного поясню запрос.
v.product_id_ref = 1 — мы ищем записи в таблице реквизитов по идентификатору нашего товара.
v.value_type = 1 — указываем тип реквизита. С типами нужно заранее определиться и, при добавлении товара, добавлять реквизит с соответствующим типом.
s.size_id = v.value_id_ref — объединяем таблицы реквизитов и размеров по идентификатору реквизита. Делается это для того, чтобы по id получить наименование и описание реквизита.
Запросы с JOIN
JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Входит в раздел FROM операторов SELECT, UPDATE или DELETE. Используется при связке двух или более таблиц.
Такое объединение выдаст нам набор записей, в котором данные таблицы Colors присутствуют в таблице Product_values. То есть только те записи, которые удовлетворяют условию c.color_id = v.value_id_ref.
Но бывают такие случаи, когда нам нужно получить все данные из одной таблицы и только те данные из второй таблицы, которые присутствуют в первой. Рассмотрим на примере.
Допустим, для товаров мы будем хранить фото. Создадим таблицу для фотографий.
Представим условие, что не у всех товаров есть фото и напишем запрос для получения списка товаров с фото.
Результат выборки следующий:
Как мы видим, у товара нет фотографии. NULL означает пусто.
Но, когда мы в скриптовом языке (PHP и пр.) будем выводить список, и в тег img попадет пустое значение, фото в браузере будет потеряно.
Модифицируем запрос для того, чтобы избежать этого:
IFNULL обрабатывает как раз значение NULL. Если значение пустое, можем подставить свое значение. В данном случае мы подставим «empty.jpg». Для корректного отображения на странице добавим на сайт изображение empty.jpg и теперь мы имеем красивый список.
Внимание! Старайтесь всегда обрабатывать значения NULL. Не стоит такого рода логику обрабатывать на клиентском приложении, запросами она обрабатывается значительно легче.
Теперь непосредственно про LEFT JOIN. Так называемое «левое объединение» выводит все данные основной таблицы и только те данные второй, которые удовлетворяют условию блока ON.
Есть также RIGHT и FULL JOIN. RIGHT, по сути, аналогичен LEFT, только запрос выведет все данные второй таблицы и те записи первой, которые удовлетворяют условию блока ON.
Можно всегда использовать LEFT, только менять местами таблицы.
FULL JOIN выведет все данные обеих таблиц, но практическую реализацию подобного запроса встретишь довольно редко.
Агрегатные функции
В этой части мы перейдем от простого использования запросов к извлечению значений из базы данных и определению, как вы можете использовать эти значения чтобы получить из них информацию.
Это делается с помощью агрегатных или общих функций, которые берут группы значений из поля и сводят их до одиночного значения. Вы узнаете, как использовать эти функции, как определить группы значений, к которым они будут применяться, и как определить, какие группы выбираются для вывода.
Запросы могут производить обобщенное групповое значение полей точно так же, как и значение одного поля. Это делается с помощью агрегатных функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Список этих функций:
COUNT — выводит количество полей, которые выбрал запрос;
SUM — выводит арифметическую сумму всех выбранных значений данного поля;
MAX — выводит наибольшее из всех выбранных значений данного поля;
MIN — выводит наименьшее из всех выбранных значений данного поля;
AVG — выводит усреднение всех выбранных значений данного поля.
При написании запросов с агрегатными функциями, необходимо научиться правильным образом организовать группировку (GROUP BY).
Пример запроса с группировкой:
Запрос выведет нам список групп и количество товаров в каждой:
Остальные агрегатные функции работают аналогично, и запросы выглядят идентично:
Запрос выведет нам список групп и общую стоимость товаров в каждой.
Внимание! Агрегатные функции используются только в блоке SELECT. Если мы хотим добавить агрегатную функцию в блок WHERE, нужно использовать команду HAVING.
Запрос выведет имена тех групп, в которых более одного товара. Таким же образом пишутся запросы с условием других агрегатных функций.
Оценка производительности запросов
Тут все настолько просто, насколько сложно. Для оценки производительности необходимо перед запросом добавить EXPLAIN EXTENDED.
Тогда, при выполнении запроса, мы получим план запроса. Для простых запросов данная процедура не требуется, поэтому рассматривать производительность необходимо только на крупных запросах.
Я преднамеренно убрал все индексы из запроса, чтобы план показал, что запрос неэффективен.
Значения полей possible_keys, key, key_len и ref не заполнены. Такой результат нас не устраивает. Поэтому добавим индексы на колонки Product_photos.product_id_ref и Products.product_id.
Внимание! Не стоит перегружать таблицу индексами. От того, что таблица будет вся проиндексирована, запрос не будет выполняться быстрее. К тому же размер индекса будет сопоставим с размерами таблицы.
Итог
В данной статье мы изучили:
— Связи в БД
— Запросы из двух и более таблиц
— Запросы с JOIN
— Агрегатные функции
— Оценку производительности запросов
Текущего набора знаний вполне достаточно, чтобы делать большие интернет проекты с использованием БД. Для себя вы можете доработать БД индексами и триггерами.
Материал подготовил Владимир Миняйлов специально для сайта CodeHarmony.ru
Исходники:
P.S. Хотите углубить свои знания и навыки? Присмотритесь к премиум-урокам по различным аспектам сайтостроения, включая SQL и работу с БД, а также к бесплатному курсу по созданию своей CMS-системы на PHP с нуля.
Понравился материал и хотите отблагодарить?
Просто поделитесь с друзьями и коллегами!