Reg.ru: домены и хостинг

Крупнейший регистратор и хостинг-провайдер в России.

Более 2 миллионов доменных имен на обслуживании.

Продвижение, почта для домена, решения для бизнеса.

Более 700 тыс. клиентов по всему миру уже сделали свой выбор.

Перейти на сайт->

Бесплатный Курс "Практика HTML5 и CSS3"

Освойте бесплатно пошаговый видеокурс

по основам адаптивной верстки

на HTML5 и CSS3 с полного нуля.

Начать->

Фреймворк Bootstrap: быстрая адаптивная вёрстка

Пошаговый видеокурс по основам адаптивной верстки в фреймворке Bootstrap.

Научитесь верстать просто, быстро и качественно, используя мощный и практичный инструмент.

Верстайте на заказ и получайте деньги.

Получить в подарок->

Бесплатный курс "Сайт на WordPress"

Хотите освоить CMS WordPress?

Получите уроки по дизайну и верстке сайта на WordPress.

Научитесь работать с темами и нарезать макет.

Бесплатный видеокурс по рисованию дизайна сайта, его верстке и установке на CMS WordPress!

Получить в подарок->

*Наведите курсор мыши для приостановки прокрутки.


БД 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 с нуля.

Понравился материал и хотите отблагодарить?
Просто поделитесь с друзьями и коллегами!


Смотрите также:

PHP: Получение информации об объекте или классе, методах, свойствах и наследовании

PHP: Получение информации об объекте или классе, методах, свойствах и наследовании

CodeIgniter: жив или мертв?

CodeIgniter: жив или мертв?

Функции обратного вызова, анонимные функции и механизм замыканий

Функции обратного вызова, анонимные функции и механизм замыканий

Применение функции к каждому элементу массива

Применение функции к каждому элементу массива

Слияние массивов. Преобразование массива в строку

Слияние массивов. Преобразование массива в строку

Деструктор и копирование объектов с помощью метода __clone()

Деструктор и копирование объектов с помощью метода __clone()

Эволюция веб-разработчика или Почему фреймворк - это хорошо?

Эволюция веб-разработчика или Почему фреймворк - это хорошо?

Магические методы в PHP или методы-перехватчики (сеттеры, геттеры и др.)

Магические методы в PHP или методы-перехватчики (сеттеры, геттеры и др.)

PHP: Удаление элементов массива

PHP: Удаление элементов массива

Ключевое слово final (завершенные классы и методы в PHP)

Ключевое слово final (завершенные классы и методы в PHP)

50 классных сервисов, программ и сайтов для веб-разработчиков

50 классных сервисов, программ и сайтов для веб-разработчиков

Наверх