Основы SQL. Часть 3.

[Gudaus]

Чтобы понять дальнейший материал, создадим свою базу данных по продаже всякой всячины. В ней будут 3 таблицы.

1. tblSaler. ФИО продавца, город, дата рождения, личный номер.
2. tblProduct. Наименование товара, артикул, количество на складе, цена за 1 шт.
3. tblSales. Личный номер продавца, артикул проданного товара и количество.

Спойлер: Создание

CREATE TABLE IF NOT EXISTS tblSaler (
ID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
BirthDay DATE,
City VARCHAR(255),
Name VARCHAR(64),
SurName VARCHAR(64),
Family VARCHAR(64) );

CREATE TABLE IF NOT EXISTS tblProduct (
ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
Article VARCHAR(255),
Storage INT,
Name VARCHAR(255),
Price FLOAT(24) );

CREATE TABLE IF NOT EXISTS tblSales (
ID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
SalerID MEDIUMINT UNSIGNED NOT NULL,
ProductArticle VARCHAR(255),
SalesCount INT,
DealDay DATE );

Спойлер: Заполним данными

INSERT  tblProduct(Article,Storage,Name,Price)  VALUES
( '27-E-415AAT','78000','Ручка красная шариковая','4.50'),
( '27-E-416AAT','95000','Ручка синяя шариковая','4.50'),
( '19-RT-ML57','1800','Подшипник шариковый D16 сталь','150'),
( 'DD-10-GH6G7','285','Микрофон USB Blue Yeti','18000'),
( 'YH-7GY-190D','-145','Карандаш коричневый ТМ 18см','3.75'),
( '725-MZ-18K','947','Шапка лыжная чёрная спортивная','250'),
( '17-5Е-29LN','6710','Кружка белая дед мороз 2018','460'),
( 'G5-PO6-QT56','14520','Розетка серая 380 Вольт электроплита','1340'),
( 'CV-078-SH12','230','Штаны суконные красные зимние','900'),
( 'CV-078-SH13','230','Штаны суконные синие зимние','900'),
( 'OL-60-22','-5','Ноутбук','39000');

INSERT  tblSaler(BirthDay,City,Name,SurName,Family)  VALUES
( '1980-11-30','Москва','Иван','Петрович','Хренников'),
( '1996-04-22','Париж','Ефим','Соломонович','Якин'),
( '1976-03-16','Мурманск','Сергей','Иванович','Чубчайкин'),
( '1980-11-19','Париж','Фёдор','Игнатьевич','Феристов'),
( '1994-07-23','Житомир','Никита','Владимирович','Смирнов');

INSERT  tblSales(SalerID,ProductArticle,SalesCount,DealDay)  VALUES
( '1','19-RT-ML57','50','2018-01-20'),
( '3','CV-078-SH13','450','2018-03-15'),
( '1','DD-10-GH6G7','50','2017-11-26'),
( '5','OL-60-22','15','2017-12-24'),
( '5','YH-7GY-190D','1300','2017-12-24'),
( '4','27-E-415AAT','21000','2018-01-24'),
( '2','G5-PO6-QT56','130','2018-01-20'),
( '1','CV-078-SH13','150','2018-03-17'),
( '3','19-RT-ML57','36','2017-11-22'),
( '2','17-5Е-29LN','74','2017-09-26'),
( '4','725-MZ-18K','125','2017-10-05'),
( '5','725-MZ-18K','67','2017-09-26') ;

Подзапрос​

Напоминает решение задачи в несколько действий.
Чаще всего синтаксис выглядит так
SELECT <поля> FROM <таблица> WHERE <условие>(SELECT <поля> FROM <таблица>).
Запрос в скобках называется внутренним, вне скобок - внешним.
SQL сперва выполнит запрос в скобках(внутренний) и сформирует временную таблицу с результатом. Затем исполнит внешний запрос, после чего удалит временную таблицу.
По сути мы делаем одну выборку из другой.
Вложенных запросов может быть сколько угодно, хотя увлекаться всё же не стоит. Дело в том, что подобная структура сложна для понимания, очень легко запутаться во вложениях. Сидишь, бывало, и пытаешься понять: что берётся, зачем, откуда.

Пример: нам надо понять, как зовут продавца, продавшего товара на наибольшую сумму.
Имена хранятся в tblSaler. Чтобы вытащить имя, нам надо знать личный номер. Личный номер и число продаж есть в tblSales, а вот цена в tblProduct.
Алгоритм решения задачи:

1. Из tblSales и tblProduct вытаскиваем личный номер и максимальную сумму. По сумме определяем победителя. Это внутренний запрос.
2. Убираем сумму, оставляем только номер. Это ещё один внутренний запрос.
3. Из tblSaler получаем ФИО по личному номеру. Это внешний запрос. Заодно добавим сумму, мы же её считали ранее.

Обратите внимание, что запрос из п.2 - внутренний по отношению к запросу из п.3. А по отношению к запросу из п.1 он является внешним.
Начнём.

SELECT tblSales.SalerID, SUM(tblProduct.Price*tblSales.SalesCount) AS Total
FROM tblProduct, tblSales
WHERE tblSales.ProductArticle = tblProduct.Article
GROUP BY tblSales.SalerID
ORDER BY Total DESC
LIMIT 1 ;

Мы посчитали суммы продаж, выбрали лучшего. Но у нас в выводе 2 поля - сумма и SalerID. Сумма не нужна, как бы её отбросить? Для этого оформим вышенаписанное в подзапрос.

SELECT TempTable1.SalerID FROM(
SELECT tblSales.SalerID, SUM(tblProduct.Price*tblSales.SalesCount) AS Total
FROM tblProduct, tblSales
WHERE tblSales.ProductArticle = tblProduct.Article
GROUP BY tblSales.SalerID
ORDER BY Total DESC
LIMIT 1) as TempTable1) ;

Мы взяли результат, записали во временную(производную) таблицу TempTable1. В ней 2 поля, сумма и SalerID. А затем сделали SELECT нужного поля из TempTable1.
Обойтись без именования производной таблицы никак, в противном случае получите ошибку
Every derived table must have its own alias

Но погодите! Это ещё не всё. Нам же надо ФИО узнать. Снова оформляем получившийся вывод в подзапрос.

SELECT * FROM tblSaler WHERE  tblSaler.ID = (
SELECT TempTable1.SalerID FROM(
SELECT tblSales.SalerID, SUM(tblProduct.Price*tblSales.SalesCount) AS Total
FROM tblProduct, tblSales
WHERE tblSales.ProductArticle = tblProduct.Article
GROUP BY tblSales.SalerID
ORDER BY Total DESC
LIMIT 1) as TempTable1) ;

А на сколько же он продал? Нет, даже не так. На сколько продал каждый из них?
Нам из временной таблицы понадобятся 2 поля(SalerID и tblProduct.Price*tblSales.SalesCount), а в предыдущем запросе я брал одно. Делаем по аналогии, только в этот раз проще. Не придётся убирать сумму.
Подсчитаем суммы продаж.

SELECT tblSales.SalerID, SUM(tblProduct.Price*tblSales.SalesCount) AS Total
FROM tblProduct, tblSales
WHERE tblSales.ProductArticle = tblProduct.Article
GROUP BY tblSales.SalerID

Есть SalerID и сумма Total. А теперь просто возьму и присоединю результат слева к tblSaler. Могу так сделать? Конечно, имею полное моральное право.

SELECT * FROM (
SELECT tblSales.SalerID, SUM(tblProduct.Price*tblSales.SalesCount) AS Total
FROM tblProduct, tblSales
WHERE tblSales.ProductArticle = tblProduct.Article
GROUP BY tblSales.SalerID) as TempTable1
LEFT JOIN
tblSaler
ON tblSaler.ID = TempTable1.SalerID

Без именования результата подзапроса в TempTable1 не обойтись из-за ON tblSaler.ID = TempTable1.SalerID. SQL иначе не поймёт по какому полю мы делаем JOIN.

UNION​

Склеивает 2 таблицы в одну. Берёт строки первой, записывает, затем добавляет строки второй. Понятно, что у таблиц должны быть поля совместимого типа и в таком же количестве. Дубликаты удаляются.
Синтаксис в общем виде прост <запрос1> UNION <запрос2>
Существует ещё UNION ALL.
Если простой UNION при склейке 2 таблиц удаляет дубликаты, то UNION ALL - нет. Сравните результат и сами всё поймёте.

SELECT * FROM tblSaler
UNION
SELECT * FROM tblSaler

Соединили 2 одинаковые таблицы минус дубликаты.

А вот вариант без удаления дублей.

SELECT * FROM tblSaler
UNION ALL
SELECT * FROM tblSaler

При этом ID является PRIMARY KEY! Формально не имеет права дублироваться, однако INION ALL ограничение игнорирует.
То есть с объединением UNION ALL надо быть осторожным. Можно запросто сотворить нечто, что в дальнейшем при работе выдаст ошибки.

А по какому принципу дубликаты определяет UNION? Он же их отсеивает, на что смотрит?
Создадим ещё одну таблицу для наглядности и заполним данными.

Спойлер: Создание tblSaler2

CREATE TABLE IF NOT EXISTS tblSaler2 (
ID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
BirthDay DATE,
City VARCHAR(255),
Name VARCHAR(64),
SurName VARCHAR(64),
Family VARCHAR(64) );

INSERT  tblSaler2(BirthDay,City,Name,SurName,Family)  VALUES
( '1980-11-30','Москва','Иван','Петрович','Хренников'),
( '1996-04-22','Волгоград','Ефим','Соломонович','Якин'),
( '1970-11-18','Ростов','Василий','Евгеньевич','Шариков');

Первая запись идентична записи из tblSaler, во второй изменён город, третья уникальна.

SELECT * FROM tblSaler
UNION
SELECT * FROM tblSaler2

Первая запись определилась как дубликат и не внеслась. 2 и 3 с точки зрения UNOIN не дубликаты. Отсюда вывод - UNION определяет дубликат у записи как полное совпадение всех полей. И ему тоже неинтересно что будет с PRIMARY KEY.

TRIM​

Формат: TRIM( <BOTH | LEADING | TRAILING> <символы для удаления> FROM <исходная строка> )
Считывает строку и при нахождении определённых символов просто удаляет их исходя из условия.

1. LEADING. Символы удаляются с начала строки.
2. TRAILING. Символы удаляются с конца строки.
3. BOTH. Символы удаляются с начала и с конца.

Если не указать никаких аргументов, то просто потрутся повторные идущие подряд пробелы.
Примеры:
select TRIM(LEADING 'aa' FROM 'aabbcc');


select TRIM(LEADING 'aa' FROM 'aaаbbcc');

На сей раз осталась одна буква 'a', поскольку мы удаляем 'aa', то есть по 2 штуки за раз.

select TRIM(BOTH 'a' FROM 'aaаbbcca');


select TRIM(TRAILING 'ab' FROM 'aaаbbccabab');


select TRIM(TRAILING 'ab' FROM 'aaаbbccababk');

На сей раз удаления не произошло, так как строка оканчивается на 'k'

А сейчас без аргументов, просто трём пробелы.
select TRIM(' wifi ');


Различают также LTRIM ( удаляет пробелы слева, то есть с начала строки) и RTRIM (с конца строки).

Рекомендуемые ресурсы:

• Учебник Моисеенко.
• Упражнения на SQL-EX. Можно напрячься и получить сертификат(заранее предупреждаю - будет сложно).
• Учебник Мартина Грабера "Введение в SQL".
• Толковый форум sql.ru
• SQL Tutorial
• Построение БД онлайн. Используется как тренировочный полигон для обкатки решений. Не на рабочем сервере же это делать, верно?

Все права на статью принадлежат Dev-CS.ru TEAM. При копировании материала активная ссылка обязательна.
Основы SQL Часть 1.
Основы SQL Часть 2.

 

[Gudaus]

Перенесено в общий раздел.