Сложные запросы используемые в MySQL
Всего лишь пару лет назад, в проектах, которые предусматривали работу с базами данных и построением статистики, основным изобилием используемых SQL-запросов, преобладало в основном множество запросов, ориентированных на стандартную выборку данных и нечасто можно было увидеть другие, которые безо всяких сомнений можно было бы отнести к “эксклюзиву”. Хотя сложность запроса и зависит от количества используемых таблиц, но если мы всего лишь возьмем и выведем данные полей трех или более таблиц имеющих стандартное объединение, то явная сложность такого запроса не выйдет за пределы стандартной.
В данной статье по мере возможности будут рассматриваться те запросы, примеры которых мне найти не удалось и которые, по моему мнению, не относятся к классу простых.
Сравнение данных за две даты
Хотя данная статистика из рода задач довольно редко встречаемых, но все-таки необходимость в ее получении иногда существует. И получить такую статистику ничуть не сложнее других.
Работать мы будем с двумя таблицами, структура которых представлена ниже:
Структура таблицы products
CREATE TABLE IF NOT EXISTS `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ShopID` int(11) NOT NULL, `Name` varchar(150) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;
Структура таблицы statistics
CREATE TABLE IF NOT EXISTS `statistics` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ProductID` bigint(20) NOT NULL, `Orders` int(11) NOT NULL, `Date` date NOT NULL DEFAULT '0000-00-00', PRIMARY KEY (`id`), KEY `ProductID` (`ProductID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ;
Дело в том, что стандарт языка SQL допускает использование вложенных запросов везде, где разрешается использование ссылок на таблицы. Здесь вместо явно указанных таблиц, благодаря использованию псевдонимов, будут применяться результирующие таблицы вложенных запросов с имеющейся связью один – к – одному. Результатом каждой результирующей таблицы будут данные о количестве произведенных заказов некоего товара за определенную дату, полученные путем выполнения запроса на выборку данных из таблицы statistics по требуемым критериям. Иными словами мы свяжем таблицу statistics саму с собой. Пример запроса:
SELECT stat1.Name, stat1.Orders, stat1.Date, stat2.Orders, stat2.Date FROM (SELECT statistics.ProductID, products.Name, statistics.Orders, statistics.Date FROM products JOIN statistics ON products.id = statistics.ProductID WHERE DATE(statistics.date) = '2014-09-04') AS stat1 JOIN (SELECT statistics.ProductID, statistics.Orders, statistics.Date FROM statistics WHERE DATE(statistics.date) = '2014-09-12') AS stat2 ON stat1.ProductID = stat2.ProductID
В итоге имеем такой результат:
+------------------------+----------+------------+----------+------------+ | Name | Orders1 | Date1 | Orders2 | Date2 | +------------------------+----------+------------+----------+------------+ | Процессоры Pentium II | 1 | 2014-09-04 | 1 | 2014-09-12 | | Процессоры Pentium III | 1 | 2014-09-04 | 10 | 2014-09-12 | | Оптическая мышь Atech | 10 | 2014-09-04 | 3 | 2014-09-12 | | DVD-R | 2 | 2014-09-04 | 5 | 2014-09-12 | | DVD-RW | 22 | 2014-09-04 | 18 | 2014-09-12 | | Клавиатура MS 101 | 5 | 2014-09-04 | 1 | 2014-09-12 | | SDRAM II | 26 | 2014-09-04 | 12 | 2014-09-12 | | Flash RAM 8Gb | 8 | 2014-09-04 | 7 | 2014-09-12 | | Flash RAM 4Gb | 18 | 2014-09-04 | 30 | 2014-09-12 | +------------------------+----------+------------+----------+------------+
Подстановка нескольких значений из другой таблицы
Необходимость в данном запросе не является повседневной, но возникает не совсем уж и редко. Самый распространенный пример, это обычная сетевая игра. Где создается сессия на два игрока. Соответственно в таблице с данными об играх имеются два поля с идентификаторами зарегистрированных игроков. Для того чтобы вывести информацию об имеющихся играх, мы не можем обойтись стандартным объединением таблицы с данными об игроках и таблицы об имеющихся играх. Так как мы имеем два поля с идентификаторами неких игроков. Но мы можем обратиться опять за помощью к псевдонимам таблиц.
Демонстрация данного запроса будет происходить на другом примере, а не на примере сетевой игры. Это чтобы не создавать заново все необходимые таблицы. В качестве данных возьмем таблицу products из примера “сравнение данных за две даты” и создадим еще одну недостающую таблицу replace_com, структура которой представлена ниже:
CREATE TABLE IF NOT EXISTS `replace_com` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sProductID` int(11) NOT NULL, `rProductID` int(11) NOT NULL, `Date` date NOT NULL DEFAULT '0000-00-00', PRIMARY KEY (`id`), KEY `sProductID` (`sProductID`,`rProductID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
Предположим, что у нас есть некий компьютерный салон и мы проводим модификации некоторых компьютерных составляющих, а все операции по замене комплектующих заносим в базу данных. В таблице replace_com интересующими нас полями являются: sProductID и rProductID. Где sProductID – идентификатор заменяемого модуля, а rProductID – идентификатор заменяющего модуля. Запрос, реализующий вывод данных о совершенных операциях выглядит следующим образом:
SELECT sProducts.Name AS sProduct, rProducts.Name AS rProduct, replace_com.Date FROM replace_com JOIN products AS sProducts ON sProducts.id = replace_com.sProductID JOIN products AS rProducts ON rProducts.id = replace_com.rProductID
Результирующая таблица данных:
+-----------------------+------------------------+------------+ | sProduct | rProduct | Date | +-----------------------+------------------------+------------+ | Процессоры Pentium II | Процессоры Pentium III | 2014-09-15 | | Flash RAM 4Gb | Flash RAM 8Gb | 2014-09-17 | | DVD-R | DVD-RW | 2014-09-18 | +-----------------------+------------------------+------------+
Вывод статистики с накоплением по дате
Предположим, что у нас имеется склад с некими товарами. Товары периодически поступают, и нам бы хотелось видеть в отчете остатки товаров по дням. Поскольку данные о наличии товаров необходимо накапливать, то мы введем пользовательскую переменную. Но есть одно небольшое “но”. Мы не можем использовать в запросе переменные пользователя и группировку данных одновременно (вернее можем, но в итоге получим, не то, что ожидаем), но мы можем использовать вложенный запрос, вместо явно указанной таблицы. Данные в таблице будут предварительно сгруппированы по дате. И уже затем на основе этих данных мы произведем расчет статистики с накоплением.
На первом этапе требуется установить переменную и присвоить ей нулевое значение:
SET @cvalue = 0
В следующем запросе, мы созданную ранее переменную и применим:
SELECT products.Name AS Name, (@cvalue := @cvalue + Orders) as Orders, Date FROM (SELECT ProductID AS ProductID, SUM(Orders) AS Orders, DATE(date) AS Date FROM statistics WHERE ProductID = '1' GROUP BY date) AS statistics JOIN products ON statistics.ProductID = products.id
+-----------------------+--------+------------+ | Name | Orders | Date | +-----------------------+--------+------------+ | Процессоры Pentium II | 1 | 2014-09-04 | | Процессоры Pentium II | 2 | 2014-09-12 | | Процессоры Pentium II | 4 | 2014-09-14 | | Процессоры Pentium II | 6 | 2014-09-15 | +-----------------------+--------+------------+
Получить используемую в примерах базу данных можно здесь.
MySQL — сложные запросы
Привет. Стал я тут писать одно Web-приложение и столкнулся с тем что на вывод одной таблице у меня получается около 4 запрос в БД. Если делать мини сервис с мелкой посещаемостью, то нагрузка как бы не о чем. А вот если людей будет уже около 2000 тысяч хотя бы в сутки, то запросы растут с геометрической прогрессией.
Чтобы не нагружать мой сервер, решил я более глубоко погрузится в MySQL и выяснил, что можно составлять сложные запросы к Базе Данных(БД). Т.е. было 4 select к БД, а стал один с вложенными запросами и join`ами.
К тому же сложные запросы MySQL помогут сократить код логики. Раньше было 4 запроса и соответственно получали 4 массива, каждый нужно было обойти, придать ему нужный вид, дальше объединить его с другими. А сейчас получается один запрос и один массив, все.
Задача
- Даллар США (это название валюты на русском).
- USD (это название валюты на английском).
- Текущая дата.
- Значение валюты.
- Вчерашняя дата.
- Значение валюты.
- Колонка разность (отянть вчера от сегодня).
И так представим, что БД у нас построена по правилам «Трех нормальных форм». Т.е. 1 и 2 пункт из списка выше хранится в одной таблице, 3-6 хранятся данные в другой таблице. А 7 пункт вообще вычисляется средствами MySql.
Сам код запроса
Запрос будет выглядеть следующем образом.
SELECT r.id_name_currency, r.value, yesterday.value, r.date, yesterday.date, pc.name_currency_en, pc.name_currency_ru, (yesterday.value - r.value) FROM parser_all_exchange_rates r JOIN ( SELECT rr.date, rr.value, rr.id_name_currency FROM parser_all_exchange_rates rr WHERE rr.id_name_banks = 233 AND rr.date = CURRENT_DATE() - INTERVAL 1 DAY ) yesterday JOIN parser_name_currencies pc ON r.id_name_currency = pc.id WHERE r.id_name_banks = 233 AND r.date = CURRENT_DATE() AND yesterday.id_name_currency = r.id_name_currency
В начале для легкости понимания лучше разбить задачу на блоки.
Пишем отдельные селекты для получения тех данных, которые нужны из разных таблиц, и только после этого начинаем их объединять. В дальнейшем с приобретением опыта, вы сами поймете когда это случится, будите уже писать сразу сложный запрос без разбивания его на блоки.
Нюансы
А как же без них.
FROM parser_all_exchange_rates r, ( SELECT rr.date, rr.value, rr.id_name_currency FROM parser_all_exchange_rates rr WHERE rr.id_name_banks = 233 AND rr.date = CURRENT_DATE() - INTERVAL 1 DAY ) yesterday INNER JOIN parser_name_currencies pc ON r.id_name_currency = pc.id
Если посмотреть на код выше, то после from идет указатель на имя таблицы, в которой мы ищем информацию, затем идет в скобках следующая таблица, после скобок ей присваивается имя yesterday и затем указываем join. Если написать так, то будет синтаксическая ошибка.
Join можно перенести на верх и вставить его перед скобками и поставить запятую, то все отработает, но это все равно не верно. Нужно писать более универсально. Т.е. т.к. в первом примере.
Необязательно писать INNER JOIN, можно просто JOIN. СУБД по умолчанию выполнить именно внутреннее соединение.
Будут вопросы пишите в комментариях.
6 SQL-запросов, о которых должен знать каждый дата-инженер
Знание продвинутого синтаксиса SQL необходимо и новичку, и опытному дата-инженеру или аналитику данных.
В связи с бурным ростом объема данных все более важным становится умение очень быстро их анализировать.
Объем данных на этом графике показан в зеттабайтах.
1 зеттабайт = 1 триллион гигабайтов Есть много очень вместительных нереляционных хранилищ, которые отлично выполняют свою работу, поддерживая массовое горизонтальное масштабирование с низкими затратами. Однако они не заменяют высококачественные хранилища на основе SQL, а лишь дополняют их.
Высококачественными и очень надежными для относительно естественного моделирования данных их делают ACID-свойства SQL.
Я и сам дата-инженер, давно использую SQL и знаю, как важно быстрее писать сложные запросы. И продвинутый синтаксис SQL будет здесь очень кстати.
В примерах использованы данные таблицы bill («Счет»):
Нарастающий итог
На практике часто приходится подсчитывать нарастающий итог по таблице, т. е. как меняется промежуточная сумма каждый раз при добавлении нового значения.
Нарастающим итогом называется сумма значений во всех ячейках столбца до следующей ячейки в этом конкретном столбце.
Вот запрос на эту сумму:
SELECT id,month
, Amount
, SUM(Amount) OVER (ORDER BY id) as total_sum
FROM bill А вот как будет выглядеть результат:
Обобщенные табличные выражения
Обобщенные табличные выражения используются ради большего удобства для восприятия человеком сложных запросов, требующих соединения, и подзапросов.
Фактически это временный именованный результирующий набор данных, на который можно ссылаться внутри оператора SELECT , INSERT , UPDATE или DELETE .
Рассмотрим простой запрос:
SELECT *
FROM bill
WHERE id in
(SELECT DISTINCT id
FROM id
WHERE country = "US"
AND status = "Y"
) Представьте, что мы задействуем этот подзапрос многократно в последующем запросе. Не проще ли использовать его как временную таблицу? Именно эту задачу и решают обобщенные табличные выражения.
WITH idtempp as (
SELECT id as id
FROM id
WHERE country = "US"
AND status = "Y"
)
SELECT *
FROM bill
WHERE id in (SELECT id from idtempp) Упорядочение данных
Дата-инженерам и аналитикам данных очень часто приходится упорядочивать значения по каким-либо параметрам, например зарплате, затратам и т. д. И это экономит много времени при поиске точного запроса.
SELECT
id,
Amount,
RANK() OVER (ORDER BY Amount desc)
FROM bill В этом запросе набор данных упорядочен по столбце amount («Сумма»).
Вместо RANK() используется также DENSE_RANK() . Он аналогичен, но не пропускает следующее по порядку значение, если у двух строк одинаковое значение.
Добавление подытогов
Наличие промежуточного итога (подытога) помогает оценить данные в контексте общего итога.
Это расширенная версия оператора GROUP BY : здесь есть возможность добавления к данным промежуточных и общих итогов.
SELECT
Type,
id,
SUM (Amount) AS total_amount
FROM bill
GROUP BY Type,id WITH ROLLUP Примечание: это запрос в MySQL. Для других синтаксис свертки может отличаться.
Здесь в запросе строка со значениями null и для типа, и для идентификатора — это итог. Есть также подытоги со значениями null только в столбце идентификатора: это 4-я и предпоследняя строки.
Временные функции
Временные функции позволяют легко менять данные без использования огромных операторов case .
В следующем примере временная функция применяется для преобразования типа в род. Это можно было сделать с помощью встроенного в запрос оператора case , но тогда было бы неудобно читать.
CREATE TEMPORARY FUNCTION get_gender(type varchar) AS (
CASE WHEN type = "M" THEN "male"
WHEN type = "F" THEN "female"
ELSE "n/a"
END
)
SELECT
name,
get_gender(Type) as gender
FROM bill Дисперсия и среднеквадратическое отклонение
Для получения этих значений есть специальные агрегатные функции: VARIANCE , VAR_POP и VAR_SAMP . Они группируют данные и используются для определения дисперсии, дисперсии группы и дисперсии выборки набора данных по отдельности.
SELECT
VARIANCE(amount) AS var_amount,
VAR_POP(amount) AS var_pop_amount,
VAR_SAMP(amount) AS var_samp_amount,
STDDEV_SAMP(amount) as stddev_sample_amount,
STDDEV_POP(amount) as stddev_pop_amount,
FROM bill VAR_POP — дисперсия совокупности;
VAR_SAMP — дисперсия выборки;
STDDEV_SAMP — среднеквадратическое отклонение для выборки;
STDDEV_POP — среднеквадратическое отклонение для совокупности.
Это были основные SQL-команды, которые я постоянно использовал, работая дата-инженером, и которые пришлись очень кстати при решении многих бизнес-задач.
Stats подтверждает, что экосистема инструментов SQL, которая включает в себя все: от Excel и Tableau до SparkSQL — используется в более чем 60 % организаций. Это настоящий подвиг для SQL, особенно учитывая его возраст.
Уверен, что и вам как дата-инженеру эти команды будут полезны.
- Как подключить базу данных MySQL к сайту на PHP
- SQL в науке о данных
- Руководство по анализу данных с SQL
Что такое сложный запрос mysql
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 с нуля.
Понравился материал и хотите отблагодарить?
Просто поделитесь с друзьями и коллегами!