Что такое сложные запросы в sql

Основы Transact SQL: Сложные (многотабличные запросы)(Урок 5, часть 1)

В SQL сложные запросы являются комбинацией простых SQL-запросов. Каждый простой запрос в качестве ответа возвращает набор записей (таблицу), а комбинация простых запросов возвращает результат тех или иных операций над ответами на простые запросы.

В 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 можно сформулировать следующим образом:

SELECT A FROM T1

WHERE A оператор_сравнения ALL ( SELECT B FROM T2)

Здесь оператор_сравнения обозначает любой допустимый оператор сравнения. Данный запрос должен вернуть список всех тех значений столбца A, для которых оператор сравнения истинен для всех значений столбца B.

Запрос с квантором SOME, очевидно, имеет аналогичную структуру. Он должен вернуть список всех тех значений столбца A, для которых оператор сравнения истинен хотя бы для какого-нибудь одного значения столбца B.

Запрос: Список всех клиентов, проживающих в городах Казань или Елабуга.

WHERE IdCity = SOME ( SELECT IdCity FROM City WHERE CityName IN ( ‘Казань’ , ‘Елабуга’ ))

Предыдущий запрос может быть также реализован и с использованием оператора 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:

WHERE IdCity != ALL ( SELECT IdCity FROM City WHERE CityName IN ( ‘Казань’, ‘Елабуга’ ))

Задание для самостоятельной работы: Cформулируйте запрос, возвращающий список всех клиентов (с указанием фамилии и имени), совершивших заказ за определенный период времени.

Подзапросы, возвращающие набор записей

Подзапрос можно вставлять не только в операторы WHERE и HAVING, но и в оператор FROM.

SELECT t.столбец1, t.столбец2, . , t.столбецn

FROM (SELCT . ) t WHERE .

Здесь таблице, возвращаемой подзапросом в операторе FROM, присваивается псевдоним t, а внешний запрос выделяет столбцы этой таблицы и, возможно, записи в соответствии с некоторым условием, которое указано в операторе WHERE.

Связанные (коррелированные) подзапросы

Все приведенные до сих пор запросы не зависели от своих содержащих выражений, т. е. могли выполняться самостоятельно и представлять свои результаты для проверки. Связанный подзапрос (коррелированный), напротив, зависит от содержащего выражения, из которого он ссылается на один или более столбцов. В отличие от несвязанного подзапроса, который выполняется непосредственно перед выполнением содержащего выражения, связанный подзапрос выполняется по разу для каждой строки-кандидата (это строки, которые предположительно могут быть включены в окончательные результаты). Например, следующий запрос использует связанный подзапрос для подсчета количества заказов у каждого клиента. Затем основной запрос выбирает тех клиентов, у которых больше одного заказа.

FROM Customer c

Ссылка на c.idCust в самом конце подзапроса — это то, что делает этот подзапрос связанным. Чтобы подзапрос мог выполняться, основной запрос должен поставлять значения для с.IdCust. В данном случае основной запрос извлекает из таблицы Customer все строки и выполняет по одному подзапросу для всех клиентов, передавая в него соответствующий Id клиента при каждом выполнении. Если подзапрос возвращает значение большее одного, условие фильтрации выполняется и строка добавляется в результирующий набор.

SELECT IdProd, [Description]

WHERE EXISTS ( SELECT * FROM OrdItem oi WHERE oi.IdProd = p.IdProd)

При использовании оператора EXISTS подзапрос может возвращать ни одной, одну или много строк, а условие просто проверяет, возвращены ли в результате выполнения подзапроса строки (все равно сколько). Если взглянуть на блок SELECT подзапроса, можно увидеть, что он состоит из единственного литерала *. Для условия основного запроса имеет значение только факт наличия возвращенных строк, а что именно было возвращено подзапросом — не важно. Поэтому подзапрос может возвращать все, что вам вздумается, но все же при использовании EXISTS принято задавать SELECT *.

Для поиска подзапросов, не возвращающих строки, можно использовать оператор EXISTS совместно с оператором отрицания NOT. В частности чтобы предыдущий запрос возвращал все товары, которые ни разу не заказывались, его можно модифицировать следующим образом.

SELECT IdProd, [Description]

WHERE NOT EXISTS ( SELECT * FROM OrdItem oi WHERE oi.IdProd = p.IdProd)

Задание для самостоятельной работы: Cформулируйте запрос, возвращающий список всех заказов с суммарной стоимость более заданной величины.

Начало работы с Microsoft SQL Server 2005 (Урок 1)
- Создание таблиц в SQL Server 2005 (Урок 2)
- Создание ограничений в SQL Server 2005(Урок 3)
- Основы Transact SQL: Простые выборки данных (Урок 4, часть 1)
- Основы Transact SQL: Простые выборки данных (Урок 4, часть 2)
  - Фильтрация данных SQL Server
  - Агрегатные функции SQL
  - Запросы SQL с группировкой строк
  - Внутреннее и внешнее соединение при помощи оператора JOIN
  - Объединение наборов записей в SQL Server
  - Удаление записей из базы данных SQL
  Еще записи по теме
  - Изменение данных в SQL
  - Что такое VBA (Visual Basic for Applications)
  - Microsoft Sql Server 2005. Представления (Урок 7)
  - Основы Transact SQL: Сложные (многотабличные запросы)(Урок 5, часть 2)
  - Delphi 7, Вычисление заданной функции. (Урок 3)
  - Структура редактора VBA (Visual Basic for Applications)
  - Основы Transact SQL: Простые выборки данных (Урок 4, часть 1)
  Что такое сложные запросы в sql
  
  С помощью строки поиска вы можете вручную создавать SQL-запросы любой сложности для фильтрации событий.
  
  Чтобы сформировать SQL-запрос вручную:
  1. Перейдите в раздел События веб-интерфейса KUMA. Откроется форма с полем ввода.
  2. Введите SQL-запрос в поле ввода.
  3. Нажмите на кнопку .
  Отобразится таблица событий, соответствующих условиям вашего запроса. При необходимости вы можете отфильтровать события по периоду.
  
  Поддерживаемые функции и операторы
  - SELECT – поля событий, которые следует возвращать. Для SELECT в программе поддержаны следующие функции и операторы:
    - Функции агрегации: count, avg, max, min, sum .
    - Арифметические операторы: +, -, *, /, , =, !=, >=,
    - AND, OR, NOT, =, !=, >, >=,
    - IN
    - BETWEEN
    - LIKE
    - ILIKE
    - inSubnet
    - match (в запросах используется синтаксис регулярных выражений re2)
    - DESC – по убыванию.
    - ASC – по возрастанию.
    - SELECT * FROM `events` WHERE Type IN (‘Base’, ‘Audit’) ORDER BY Timestamp DESC LIMIT 250 Все события таблицы events с типом Base и Audit , отсортированные по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.
    - SELECT * FROM `events` WHERE BytesIn BETWEEN 1000 AND 2000 ORDER BY Timestamp ASC LIMIT 250 Все события таблицы events, для которых в поле BytesIn значение полученного трафика находится в диапазоне от 1000 до 2000 байт, отсортированные по столбцу Timestamp в порядке возрастания. Количество отображаемых в таблице строк – 250.
    - SELECT * FROM `events` WHERE Message LIKE ‘%ssh:%’ ORDER BY Timestamp DESC LIMIT 250 Все события таблицы events, которые в поле Message содержат данные, соответствующие заданному шаблону %ssh:% в нижнем регистре, и отсортированы по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.
    - SELECT * FROM `events` WHERE inSubnet(DeviceAddress, ‘10.0.0.1/24’) ORDER BY Timestamp DESC LIMIT 250 Все события таблицы events для хостов, которые входят в подсеть 10.0.0.1/24, отсортированные по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.
    - SELECT * FROM `events` WHERE match(Message, ‘ssh.*’) ORDER BY Timestamp DESC LIMIT 250 Все события таблицы events, которые в поле Message содержат текст, соответствующий шаблону ssh.* , и отсортированы по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.
    - SELECT max(BytesOut) / 1024 FROM `events` Максимальный размер исходящего трафика (КБ) за выбранный период времени.
    - SELECT count(ID) AS «Count», SourcePort AS «Port» FROM `events` GROUP BY SourcePort ORDER BY Port ASC LIMIT 250 Количество событий и номер порта. События сгруппированы по номеру порта и отсортированы по столбцу Port в порядке возрастания. Количество отображаемых в таблице строк – 250. Столбцу ID в таблице событий присвоено имя Count, столбцу SourcePort присвоено имя Port.
    Если вы хотите указать в запросе специальный символ, вам требуется экранировать его, поместив перед ним обратную косую черту (\).
    
    SELECT * FROM `events` WHERE match(Message, ‘ssh:\’connection.*’) ORDER BY Timestamp DESC LIMIT 250
    
    Все события таблицы events, которые в поле Message содержат текст, соответствующий шаблону ssh: ‘connection’ , и отсортированы по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.
    
    При переключении на конструктор параметры запроса, введенного вручную в строке поиска, не переносятся в конструктор: вам требуется создать запрос заново. При этом запрос, созданный в конструкторе, не перезаписывает запрос, введенный в строке поиска, пока вы не нажмете на кнопку Применить в окне конструктора.
    
    После обновления KUMA до версии 1.6 при фильтрации событий с помощью SQL-запроса, содержащего условие inSubnet, может возвращаться ошибка Code: 441. DB::Exception: Invalid IPv4 value . В таких случаях необходимо на серверах хранилища (на каждой машине кластера ClickHouse) в файле /opt/kaspersky/kuma/clickhouse/cfg/config.d/users.xml в разделе profiles → default добавить директиву true .
    
    Что такое сложные запросы в sql
    
    Reg.ru: домены и хостинг
    
    Крупнейший регистратор и хостинг-провайдер в России.
    
    Более 2 миллионов доменных имен на обслуживании.
    
    Продвижение, почта для домена, решения для бизнеса.
    
    Более 700 тыс. клиентов по всему миру уже сделали свой выбор.
    
    Бесплатный Курс «Практика HTML5 и CSS3»
    
    Освойте бесплатно пошаговый видеокурс
    
    по основам адаптивной верстки
    
    на HTML5 и CSS3 с полного нуля.
    
    Фреймворк Bootstrap: быстрая адаптивная вёрстка
    
    Пошаговый видеокурс по основам адаптивной верстки в фреймворке Bootstrap.
    
    Научитесь верстать просто, быстро и качественно, используя мощный и практичный инструмент.
    
    Верстайте на заказ и получайте деньги.
    
    Бесплатный тренинг «PHP для Создания Сайтов: Введение»
    
    Что нужно знать для создания PHP-сайтов?
    
    Ответ здесь. Только самое важное и полезное для начинающего веб-разработчика.
    
    Узнайте, как создавать качественные сайты на PHP всего за 2 часа и 27 минут!
    
    —> Бесплатный курс «Сайт-Визитка За 15 уроков»
    
    Создайте свой сайт за 3 часа и 30 минут.
    
    После просмотра данного видеокурса у Вас на компьютере будет готовый к использованию сайт, который Вы сделали сами.
    
    Вам останется лишь наполнить его нужной информацией и изменить дизайн (по желанию).
    
    —> Бесплатный курс «Основы HTML и CSS»
    
    Изучите основы HTML и CSS менее чем за 4 часа.
    
    После просмотра данного видеокурса Вы перестанете с ужасом смотреть на HTML-код и будете понимать, как он работает.
    
    Вы сможете создать свои первые HTML-страницы и придать им нужный вид с помощью CSS.
    
    Бесплатный курс «Сайт на WordPress»
    
    Хотите освоить CMS WordPress?
    
    Получите уроки по дизайну и верстке сайта на WordPress.
    
    Научитесь работать с темами и нарезать макет.
    
    Бесплатный видеокурс по рисованию дизайна сайта, его верстке и установке на CMS WordPress!
    
    Бесплатный курс «Основы работы с JavaScript»
    
    Хотите изучить JavaScript, но не знаете, как подступиться?
    
    После прохождения видеокурса Вы освоите базовые моменты работы с JavaScript.
    
    Развеются мифы о сложности работы с этим языком, и Вы будете готовы изучать JavaScript на более серьезном уровне.
    
    *Наведите курсор мыши для приостановки прокрутки.
    
    БД MySQL (сложные запросы, агрегатные функции, оценка производительности)
    
    В этом уроке мы поговорим о следующих моментах, касающихся работы с БД MySQL: вы узнаете, как составлять сложные запросы, как использовать агрегатные функции, объединения таблиц и как оценивать производительность запросов.
    
    Связи в БД
    
    Связи в БД — это ассоциативное отношение между сущностями (таблицами). В первую очередь связи позволяют избегать избыточности данных.
    
    Избыточность же — это переполнение таблиц повторяющимися данными.
    
    Для начала поговорим о виртуальных связях таблиц. Что представляет собой такая связь?
    
    Таблица User_docs подчинена таблице Users, поэтому в ней есть ссылка на таблицу Users (user_id_ref).
    
    У одного пользователя может быть как один, так и много документов. Поэтому мы выносим документы в отдельную таблицу, чтобы не повторялись данные по самому пользователю. Связь таблиц User и User_docs — “один-ко-многим”.
    
    Внимание! Впредь, если подразумевается, что некоторые данные могут дублироваться, стоит их выносить в отдельную таблицу.
    
    Запрос из двух таблиц
    
    Функциональность MySQL не ограничивается запросом вида SELECT * FROM table. Это самый простой запрос. Такого запроса достаточно, если весь необходимый набор данных содержится в одной таблице. Но мы учимся правильно проектировать БД, поэтому и запросы у нас будут несколько сложнее и функциональнее.
    
    Предлагаю данный момент разобрать на примерах Интернет-каталога.
    
    Допустим, у нас задача, реализация каталога продукции в сети Интернет. Что для этого нужно сделать? Для начала спроектируем базу данных. Для этого нужно определиться с основными сущностями будущей БД. Первая и основная сущность — это Продукт. Создадим таблицу Products:
```
CREATE TABLE Products ( Product_id INT(10) auto_increment, Group_id_ref INT(10), Product_name CHAR (128), Product_desc TEXT, Product_articul CHAR(32), Product_price DECIMAL(14,2), PRIMARY KEY (product_id) );
```
    В этой таблице мы будем хранить наши продукты. Как вы заметили, я заранее добавил в таблицу поле Group_id_ref. Это поле привязывает продукт к конкретной группе. Создадим таблицу групп товаров:
```
CREATE TABLE Product_groups ( Group_id INT(10) auto_increment, Group_name CHAR(128), Group_desc TEXT, PRIMARY KEY (Group_id) );
```
    Кроме того, часто встречается ситуация, когда товары имеют дополнительные свойства, такие как Цвет, Размер и пр.
    
    Добавим таблицу Colors:
```
CREATE TABLE Colors ( Color_id INT(10) auto_increment, Color_name CHAR(64), Color_desc TEXT, PRIMARY KEY (Color_id) );
```
    И таблицу Sizes (Размеры):
```
CREATE TABLE Sizes ( Size_id INT(10) auto_increment, Size_name CHAR(64), Size_desc TEXT, PRIMARY KEY (Size_id) );
```
    Теперь мы можем хранить все наши данные по Продукту. Заполним таблицы тестовыми данными.
```
INSERT INTO Product_groups VALUES ('', 'Мужские костюмы', 'Костюмы, тройки, Смокинги'); INSERT INTO Colors VALUES ('', 'Черный', 'Узор в елочку'); INSERT INTO Colors VALUES ('', 'Белый', 'Белоснежный'); INSERT INTO Sizes VALUES ('', '48', '48 - российский'); INSERT INTO Sizes VALUES ('', '50', '50 - российский'); INSERT INTO Products VALUES ('', 1, 'Костюм «DS221»', 'Элегантный костюм, подходит как для работы, так и для вечернего убранства', 'Артикул_1', 12000);
```
    Теперь мы имеем все данные для одного продукта. Но ведь не всегда у всех товаров должны быть все возможные реквизиты цвета и размера. Иногда бывают костюмы маломерки, иногда наоборот.
    
    Добавим таблицы, связывающие товары с реквизитами:
```
CREATE TABLE Product_values ( Record_id INT(10) auto_increment, Product_id_ref INT(10), Value_id_ref INT(10), Value_type INT(2), /* Тип реквизита (1–цвет, 2–размер) */ PRIMARY KEY (Record_id) );
```
    В этой таблице мы будем хранить реквизиты для каждого продукта. Добавим тестовые данные:
```
INSERT INTO Product_values VALUES ('', 1, 1, 1); INSERT INTO Product_values VALUES ('', 1, 1, 2);
```
    Теперь наш тестовый продукт имеет два реквизита: Цвет и Размер.
    
    Поясню, как так получилось. Для этого рассмотрим таблицу Product_values. В этой таблице нет никаких текстовых записей, присутствуют только идентификаторы.
    
    — Record_id – уникальный идентификатор нашей таблицы. В прошлой статье я указывал на необходимость этого поля.
    
    — Product_id_ref – ссылка на продукт. Собственно “_ref” и указывает на то, что это ссылка — reference. Идентификатор товара в таблице Products (мы учимся связывать именно с помощью идентификаторов).
    
    — Value_id_ref – Ссылка на реквизиты товара.
    
    — Value_type – Тип реквизита. 1- цвет, 2- размер и пр., если у вас таковые будут.
    
    Давайте посмотрим, как построить запрос, чтобы получить наши данные. Сначала получим список групп. Обычно в каталогах дерево продуктов начинается именно с групп.
```
SELECT * FROM Product_groups
```
    Тут все просто. При помощи Group_id мы формируем ссылку на список товаров в группе. Формировать ссылку можно как в запросе, так и в скрипте, на котором написан ваш каталог.
```
SELECT p.product_id, p.product_name, p.product_desc, p.product_price, g.group_name FROM Products p, Product_groups g WHERE p.group_id_ref = g.group_id Для получения списка товаров в конкретной группе добавляем AND g.group_id = 1 /*Идентификатор группы*/
```
    Результат выборки выглядит так:
    
    В каталоге на сайте такую выборку можно использовать в списке товаров. Product_id используем для формирования ссылки на конкретный товар.
    
    Для конкретного товара запрос будет похожим, за исключением того, что мы укажем p.Product_id = 1.
    
    Немного поясню, что такое «р.» в данном запросе. Для СУБД запрос вида:
```
SELECT product_name FROM Products WHERE product_id = 1
```
```
SELECT Products.product_name FROM Products WHERE Products.product_id = 1
```
    То есть всегда поле указывается с таблицей. В принципе, имя таблицы можно не писать, если поля ВО ВСЕХ(!) таблицах запроса именуются по-разному.
    
    Но такой идеальной ситуации, как правило, не бывает и логичнее указывать не имя таблицы а ее алиас.
```
. FROM Products p, Product_groups g.
```
    В этом случае p – это Products, а g – это Product_groups. Теперь в запросе нет необходимости писать имя таблицы целиком, достаточно описать только алиас.
```
SELECT p.product_name FROM Products p WHERE p.product_id = 1
```
    Внимание! В громоздких запросах алиасы значительно ускоряют написание. Так же такой подход к написанию запроса более корректен.
    
    Итак, для конкретного товара запрос будет таковым:
```
SELECT p.product_id, p.product_name, p.product_desc, p.product_price, g.group_name FROM Products p, Product_groups g WHERE p.product_id = 1 AND p.group_id_ref = g.group_id
```
    Теперь получим реквизиты товара. Список расцветок получаем запросом:
```
SELECT c.color_name, c.color_id, c.color_desc FROM Product_values v, Colors c WHERE v.product_id_ref = 1 /* ид товара */ AND c.color_id = v.value_id_ref /* ссылка на расцветку */ AND v.value_type = 1 /* тип реквизита цвет */
```
    Подобным запросом получим и размеры.
```
SELECT s.size_name, s.size_id, s.size_desc FROM Product_values v, Sizes s WHERE v.product_id_ref = 1 /* ид товара */ AND s.size_id = v.value_id_ref /* ссылка на размер */ AND v.value_type = 1 /* тип реквизита размер */
```
    Немного поясню запрос.
    
    v.product_id_ref = 1 — мы ищем записи в таблице реквизитов по идентификатору нашего товара.
    
    v.value_type = 1 — указываем тип реквизита. С типами нужно заранее определиться и, при добавлении товара, добавлять реквизит с соответствующим типом.
    
    s.size_id = v.value_id_ref — объединяем таблицы реквизитов и размеров по идентификатору реквизита. Делается это для того, чтобы по id получить наименование и описание реквизита.
    
    Запросы с JOIN
    
    JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Входит в раздел FROM операторов SELECT, UPDATE или DELETE. Используется при связке двух или более таблиц.
```
SELECT c.color_name, c.color_id, c.color_desc FROM Product_values v JOIN Colors c ON c.color_id = v.value_id_ref WHERE v.product_id_ref = 1 /* ид товара */ AND v.value_type = 1 /* тип реквизита цвет */
```
    Такое объединение выдаст нам набор записей, в котором данные таблицы Colors присутствуют в таблице Product_values. То есть только те записи, которые удовлетворяют условию c.color_id = v.value_id_ref.
    
    Но бывают такие случаи, когда нам нужно получить все данные из одной таблицы и только те данные из второй таблицы, которые присутствуют в первой. Рассмотрим на примере.
    
    Допустим, для товаров мы будем хранить фото. Создадим таблицу для фотографий.
```
CREATE TABLE Product_photos ( photo_id INT(10) auto_increment, product_id_ref INT(10), photo_path CHAR(128), /* Имя файла фото */ is_main INT(1), /* Основное — 1, иначе - 0 */ PRIMARY KEY (photo_id) );
```
    Представим условие, что не у всех товаров есть фото и напишем запрос для получения списка товаров с фото.
```
SELECT p.product_id, p.product_name, p.product_desc, ph.photo_path FROM Products p LEFT JOIN Product_photos ph ON ph.product_id_ref = p.product_id AND ph.is_main = 1
```
    Результат выборки следующий:
    
    Как мы видим, у товара нет фотографии. NULL означает пусто.
    
    Но, когда мы в скриптовом языке (PHP и пр.) будем выводить список, и в тег img попадет пустое значение, фото в браузере будет потеряно.
    
    Модифицируем запрос для того, чтобы избежать этого:
```
SELECT p.product_id, p.product_name, p.product_desc, IFNULL(ph.photo_path, 'empty.jpg') photo_path FROM Products p LEFT JOIN Product_photos ph ON ph.product_id_ref = p.product_id AND ph.is_main = 1
```
    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 COUNT(p.product_id) cnt, g.group_name FROM Products p, Product_groups g WHERE p.group_id_ref = g.group_id GROUP BY p.group_id_ref
```
    Запрос выведет нам список групп и количество товаров в каждой:
    
    Остальные агрегатные функции работают аналогично, и запросы выглядят идентично:
```
SELECT SUM(p.product_price) summ, g.group_name FROM Products p, Product_groups g WHERE p.group_id_ref = g.group_id GROUP BY p.group_id_ref
```
    Запрос выведет нам список групп и общую стоимость товаров в каждой.
    
    Внимание! Агрегатные функции используются только в блоке SELECT. Если мы хотим добавить агрегатную функцию в блок WHERE, нужно использовать команду HAVING.
```
SELECT g.group_name FROM Products p, Product_groups g HAVING COUNT(*) > 1 GROUP BY p.group_id_ref
```
    Запрос выведет имена тех групп, в которых более одного товара. Таким же образом пишутся запросы с условием других агрегатных функций.
    
    Оценка производительности запросов
    
    Тут все настолько просто, насколько сложно. Для оценки производительности необходимо перед запросом добавить EXPLAIN EXTENDED.
    
    Тогда, при выполнении запроса, мы получим план запроса. Для простых запросов данная процедура не требуется, поэтому рассматривать производительность необходимо только на крупных запросах.
```
EXPLAIN EXTENDED SELECT p.product_id, p.product_name, p.product_desc, ph.photo_path FROM Products p LEFT JOIN Product_photos ph ON ph.product_id_ref = p.product_id AND ph.is_main = 1
```
    Я преднамеренно убрал все индексы из запроса, чтобы план показал, что запрос неэффективен.
    
    Значения полей possible_keys, key, key_len и ref не заполнены. Такой результат нас не устраивает. Поэтому добавим индексы на колонки Product_photos.product_id_ref и Products.product_id.
    
    Внимание! Не стоит перегружать таблицу индексами. От того, что таблица будет вся проиндексирована, запрос не будет выполняться быстрее. К тому же размер индекса будет сопоставим с размерами таблицы.
    
    Итог
    
    В данной статье мы изучили:
    
    — Связи в БД
    — Запросы из двух и более таблиц
    — Запросы с JOIN
    — Агрегатные функции
    — Оценку производительности запросов
    
    Текущего набора знаний вполне достаточно, чтобы делать большие интернет проекты с использованием БД. Для себя вы можете доработать БД индексами и триггерами.
    
    Материал подготовил Владимир Миняйлов специально для сайта CodeHarmony.ru
    
    Исходники:
```
CREATE TABLE Products ( Product_id INT(10) auto_increment, Group_id_ref INT(10), Product_name CHAR (128), Product_desc TEXT, Product_articul CHAR(32), Product_price DECIMAL(14,2), PRIMARY KEY (product_id), INDEX (Group_id_ref) ); CREATE TABLE Product_groups ( Group_id INT(10) auto_increment, Group_name CHAR(128), Group_desc TEXT, PRIMARY KEY (Group_id) ); CREATE TABLE Colors ( Color_id INT(10) auto_increment, Color_name CHAR(64), Color_desc TEXT, PRIMARY KEY (Color_id) ); CREATE TABLE Sizes ( Size_id INT(10) auto_increment, Size_name CHAR(64), Size_desc TEXT, PRIMARY KEY (Size_id) ); CREATE TABLE Product_values ( Record_id INT(10) auto_increment, Product_id_ref INT(10), Value_id_ref INT(10), Value_type INT(2), /* Тип реквизита (1–цвет, 2–размер) */ PRIMARY KEY (Record_id), INDEX(product_id_ref) ); CREATE TABLE Product_photos ( photo_id INT(10) auto_increment, product_id_ref INT(10), photo_path CHAR(128), /* Имя файла фото */ is_main INT(1), /* Основное — 1, иначе - 0 */ PRIMARY KEY (photo_id), INDEX(product_id_ref) ); /* Группы товаров */ INSERT INTO Product_groups VALUES ('', 'Мужские костюмы', 'Костюмы, тройки, Смокинги'); /* Расцветки */ INSERT INTO Colors VALUES ('', 'Черный', 'Узор в елочку'); INSERT INTO Colors VALUES ('', 'Белый', 'Белоснежный'); /* Размеры */ INSERT INTO Sizes VALUES ('', '48', '48 - российский'); INSERT INTO Sizes VALUES ('', '50', '50 - российский'); /* Товары */ INSERT INTO Products VALUES ('', 1, 'Костюм «DS221»', 'Элегантный костюм, подходит как для работы, так и для вечернего убранства', 'Артикул_1', 12000); /* Реквизиты товаров */ INSERT INTO Product_values VALUES ('', 1, 1, 1); INSERT INTO Product_values VALUES ('', 1, 1, 2);
```
    P.S. Хотите углубить свои знания и навыки? Присмотритесь к премиум-урокам по различным аспектам сайтостроения, включая SQL и работу с БД, а также к бесплатному курсу по созданию своей CMS-системы на PHP с нуля.
    
    Понравился материал и хотите отблагодарить?
    Просто поделитесь с друзьями и коллегами!
    
    Что такое сложные запросы в sql
    
    Народ, подскажите, а что значить простые и сложные запросы?
    Вот пишут в вакансиях состовление простых запросов?
    Простые это где по одной таблице нужно пройтись? А сложные это уже по двум и более?
    
    Re: Что значит простые и сложные запросы?
    
    От: Softwarer http://softwarer.ru
    
    Дата: 06.08.12 07:59
    
    Оценка:
    
    Здравствуйте, Gokol, Вы писали:
    
    G>Вот пишут в вакансиях состовление простых запросов?
    
    Скорее всего, хватит знания SQL на уровне умения написать SELECT * FROM TABLE WHERE />
    G>Простые это где по одной таблице нужно пройтись? А сложные это уже по двум и более?
    
    Сложность — понятие субъективное, то, что для одного сложный запрос, для другого — рабочая рутина. Обычно под «сложным» подразумевают либо тривиально большой запрос, либо запрос по неким сложноформулируемым условиям, которые не очень понятно как вообще выразить средствами SQL. Ну скажем вот такая разминка для мозгов (хотя и не то чтобы сложный запрос): выведите запросом календарь на текущий год в общепринятом формате (то есть с разбивкой по месяцам и неделям, каждое число в колонке соответствующего дня недели).
    
    Re[2]: Что значит простые и сложные запросы?
    
    От: MasterZiv
    
    Дата: 06.08.12 10:53
    
    Оценка: +1
    
    On 08/06/2012 11:59 AM, Softwarer wrote:
    
    > мозгов (хотя и не то чтобы сложный запрос): выведите запросом календарь на
    > текущий год в общепринятом формате (то есть с разбивкой по месяцам и неделям,
    > каждое число в колонке соответствующего дня недели).
    
    Это — не сложный запрос, это — дебильный запрос. Запрос, который не надо
    писать. А если писать для разминки, то это тоже бесполезно, потому что ничего
    не даст. Тут нарушается модель реляционная, результат не лежит в её плоскости.
    
    Если уж так хочется, то пусть есть в одной таблице список всех дней,
    в другой — список праздничных дней в данном году. В третей — список
    дней недели, которые являются выходными.
    
    Ну и надо вывести тот же календарь, но с выходными и праздн. днями.
    
    year, month, day, day_of_week, is_free_day
    
    DAY
    (year int, month int, day int )- PK.
    day_of_week int
    
    WEEKENDS
    (day_of_week int ) — PK
    is_free_day boolean
    
    HOLIDAYS
    (month int, day int)- PK.
    
    Posted via RSDN NNTP Server 2.1 beta
    Re[3]: Что значит простые и сложные запросы?
    
    От: Softwarer http://softwarer.ru
    
    Дата: 06.08.12 12:21
    
    Оценка: +1 -2
    
    Здравствуйте, MasterZiv, Вы писали:
    
    Мастер, я готов и дальше делать скидку на постоянно преследующие Вас по жизни обиды и неудачи, но советую всё же научиться контролировать своё поведение, в противном случае отделаться от них окажется ещё сложнее.
    
    Re[4]: Что значит простые и сложные запросы?
    
    От: MasterZiv
    
    Дата: 06.08.12 21:07
    
    Оценка:
    
    > Мастер, я готов и дальше делать скидку на постоянно преследующие Вас по жизни
    > обиды и неудачи, но советую всё же научиться контролировать своё поведение, в
    > противном случае отделаться от них окажется ещё сложнее.
    
    А с обидами и неудачами у меня всё ОК, и при чём тут вообще моё поведение ?
    Человек спросил — я ответил.
    
    Posted via RSDN NNTP Server 2.1 beta
    Re[4]: Что значит простые и сложные запросы?
    
    От: MasterZiv
    
    Дата: 06.08.12 21:08
    
    Оценка:
    
    On 08/06/2012 04:21 PM, Softwarer wrote:
    
    > Мастер, я готов и дальше делать скидку на постоянно преследующие Вас по жизни
    
    А, скидку не надо. Вообще никогда никому.
    
    Posted via RSDN NNTP Server 2.1 beta
    Re: Что значит простые и сложные запросы?
    
    От: os24ever
    
    Дата: 27.08.12 19:53
    
    Оценка:
    
    G>Народ, подскажите, а что значить простые и сложные запросы?
    
    Думаю, имеются в виду джойны, за которыми следуют другие джойны, за ними третьи и так далее. Или что-то вроде такого
    
    Автор: vsb
    Дата: 27.04.12
    Re: Что значит простые и сложные запросы?
    
    От: MasterZiv
    
    Дата: 27.08.12 23:00
    
    Оценка:
    
    > Народ, подскажите, а что значить простые и сложные запросы?
    
    Ничего не значит.
    
    > Вот пишут в вакансиях состовление простых запросов?
    
    Те кто пишут скорее всего не представляют, что это такое.
    
    > Простые это где по одной таблице нужно пройтись? А сложные это уже по двум и более?
    > Что значит простые и сложные запросы? Оценить
    
    Специально для тебя:
    
    select * from AAAA where * from AAAA where name like ‘Pet%’;
    
    Всё остальное — сложные.
    Типа того
    Похожие публикации: