- SQL
- SELECT
- AS
- WHERE
- INSERT
- CASE
- IF
- IFNULL
- COALESCE
- DISTINCT
- IN
- BETWEEN
- LIKE
- REGEXP
- IS NULL
- ORDER BY
- LIMIT
- Агрегатные функции
- GROUP BY
- HAVING
- Подзапросы
- EXISTS
- Неявное соединение таблиц
- Inner Join
- Outer Join
- UNION
- VIEW
SQL - декларативный язык структурированных запросов, с помощью которого пишутся специальные запросы (скрипты, инструкции) к базе данных для получения данных и манипулирования ими.
Язык SQL представляет собой набор операторов, которые делятся на определенные группы со своими назначениями. В сокращенном виде эти группы называются DDL, DML, DCL и TCL.
Data Definition Language (DDL) – это группа операторов определения данных. Другими словами, с помощью операторов, входящих в эту группы, мы определяем структуру базы данных и работаем с объектами этой базы, т.е. создаем, изменяем и удаляем их.
В эту группу входят следующие операторы:
- CREATE – используется для создания объектов базы данных;
- ALTER – используется для изменения объектов базы данных;
- DROP – используется для удаления объектов базы данных.
Data Manipulation Language (DML) – это группа операторов для манипуляции данными. С помощью этих операторов мы можем добавлять, изменять, удалять и выгружать данные из базы, т.е. манипулировать ими.
В эту группу входят самые распространённые операторы языка SQL:
- SELECT – осуществляет выборку данных;
- INSERT – добавляет новые данные;
- UPDATE – изменяет существующие данные;
- DELETE – удаляет данные.
Data Control Language (DCL) – группа операторов определения доступа к данным. Иными словами, это операторы для управления разрешениями, с помощью них мы можем разрешать или запрещать выполнение определенных операций над объектами базы данных.
Transaction Control Language (TCL) – группа операторов для управления транзакциями. Транзакция – это команда или блок команд (инструкций), которые успешно завершаются как единое целое, при этом в базе данных все внесенные изменения фиксируются на постоянной основе или отменяются, т.е. все изменения, внесенные любой командой, входящей в транзакцию, будут отменены.
Одна из основных функций SQL — получение данных из СУБД. Для построения всевозможных запросов к базе данных используется оператор SELECT. Он позволяет выполнять сложные проверки и обработку данных.
Общая структура запроса
SELECT [DISTINCT | ALL] поля_таблиц
[FROM список_таблиц]
[WHERE условия_на_ограничения_строк]
[GROUP BY условия_группировки]
[HAVING условия_на_ограничения_строк_после_группировки]
[ORDER BY порядок_сортировки [ASC | DESC]]
[LIMIT ограничение_количества_записей]- DISTINCT используется для исключения повторяющихся строк из результата
- ALL (по умолчанию) используется для получения всех данных, в том числе и повторений
- FROM перечисляет используемые в запросе таблицы из базы данных
- WHERE условный оператор, который используется для ограничения строк по какому-либо условию
- GROUP BY используется для группировки строк
- HAVING применяется после группировки строк для фильтрации по значениям агрегатных функций
- ORDER BY используется для сортировки. У него есть два параметра:
- ASC (по умолчанию) используется для сортировки по возрастанию
- DESC по убыванию
- LIMIT используется для ограничения количества строк для вывода
Псевдонимы используются для представления столбцов или таблиц с именем отличным от оригинального. Это может быть полезно для улучшения читабельности имён и создания более короткого наименования столбца или таблицы.
Например, если в таблице есть столбец good_type_id, то можно переименовать его просто в id, для того, чтобы сделать его более коротким и удобным в использовании в будущем.
Для создания псевдонимов используется оператор AS:
SELECT
good_type_id AS id
FROM
GoodTypes;- можно выводить любые строки и числа вместо столбцов:
SELECT
"Hello world", 1;- для того, чтобы вывести все данные из таблицы Company, можно использовать символ «*», который буквально означает «все столбцы»:
SELECT
*
FROM
Company;- можно вывести любой столбец, определённый в таблице, например, town_to из таблицы Trip:
SELECT
town_to
FROM
Trip;- можно вывести несколько столбцов. Для этого их нужно перечислить через запятую:
SELECT
member_name, status
FROM
FamilyMembers;- иногда возникают ситуации, в которых нужно получить только уникальные записи, для этого можно использовать DISTINCT. Например, выведем список городов без повторений, в которые летали самолеты:
SELECT
DISTINCT town_to
FROM
Trip;Ситуация, когда требуется сделать выборку по определенному условию, встречается очень часто, для этого в операторе SELECT существует параметр WHERE, после которого следует условие для ограничения строк. Если запись удовлетворяет этому условию, то попадает в результат, иначе отбрасывается.
Общая структура запроса с оператором WHERE
SELECT поля_таблиц FROM список_таблиц
WHERE условия_на_ограничения_строк
[логический_оператор другое_условия_на_ограничения_строк];Операторы сравнения служат для сравнения 2 выражений, их результатом может являться ИСТИНА (1), ЛОЖЬ (0) и NULL.
Результат сравнения с NULL является NULL. Исключением является оператор эквивалентности.
| Оператор | Описание |
|---|---|
| = | Оператор - равенство |
| <=> | Оператор - эквивалентность Аналогичный оператору равенства, с одним лишь исключением: в отличие от него, оператор эквивалентности вернет ИСТИНУ при сравнении NULL <=> NULL |
| <> или != |
Оператор - неравенство |
| < | Оператор - меньше |
| <= | Оператор - меньше или равно |
| > | Оператор - больше |
| >= | Оператор - больше или равно |
Для добавления новых записей в таблицу предназначен оператор INSERT.
Общая структура запроса с оператором INSERT
INSERT INTO имя_таблицы [(поле_таблицы, ...)]
VALUES (значение_поля_таблицы, ...)
| SELECT поле_таблицы, ... FROM имя_таблицы ...В описанной структуре запроса необязательные параметры указаны в квадратных скобках. Вертикальной чертой обозначен альтернативный синтаксис.
Значения можно вставлять перечислением с помощью слова values, перечислив их в круглых скобках через запятую или c помощью оператора select. Таким образом, добавить новые записей можно следующими способами:
INSERT INTO Goods (good_id, good_name, type)
VALUES (5, 'Table', 2);INSERT INTO Goods VALUES (5, 'Table', 2);INSERT INTO Goods
SELECT
good_id, good_name, type
FROM
Goods
where
good_name = 2;Следует помнить, что первичный ключ таблицы является уникальным значением и добавление уже существующего значения приведет к ошибке.
При добавлении новой записи с уникальными индексами выбор такого уникального значения может оказаться непростой задачей. Решением может быть дополнительный запрос, направленный на выявление максимального значения первичного ключа для генерации нового уникального значения.
INSERT INTO Goods
SELECT
COUNT(*) + 1, 'Table', 2
FROM
Goods;В SQL введен механизм его автоматической генерации. Для этого достаточно снабдить первичный ключ good_id атрибутом AUTO_INCREMENT. Тогда при создании новой записи в качестве значения good_id достаточно передать NULL или 0 — поле автоматически получит значение, равное максимальному значению столбца good_id, плюс единица.
CREATE TABLE Goods (
good_id INT NOT NULL AUTO_INCREMENT
...
);INSERT INTO Goods VALUES (NULL, 'Table', 2);- ПРАКТИЧЕСКОЕ ЗАДАНИЕ №1
- создать таблицу с мобильными телефонами, используя графический интерфейс MySQL Workbench;
- заполнить таблицу данными;
- вывести название, производителя и цену для товаров, количество которых превышает две штуки;
- вывести весь ассортимент товаров марки “Samsung”.
Функция CASE проверяет истинность набора условий и в зависимости от результата проверки может возвращать тот или иной результат. Эта функция принимает следующую форму:
CASE
WHEN условие_1 THEN результат_1
WHEN условие_2 THEN результат_2
.................................
WHEN условие_N THEN условие_N
[ELSE альтернативный_результат]
ENDВозьмем для примера следующую таблицу Products:
CREATE TABLE Products
(
Id INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(30) NOT NULL,
Manufacturer VARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price DECIMAL NOT NULL
);Выполним запрос к этой таблице и используем функцию CASE:
SELECT ProductName, ProductCount,
CASE
WHEN ProductCount = 1
THEN 'Товар заканчивается'
WHEN ProductCount = 2
THEN 'Мало товара'
WHEN ProductCount = 3
THEN 'Есть в наличии'
ELSE 'Много товара'
END AS Category
FROM Products;Функция IF в зависимости от результата условного выражения возвращает одно из двух значений. Общая форма функции выглядит следующим образом:
IF(условие, значение_1, значение_2)Если условие, передаваемое в качестве первого параметра, верно, то возвращается первое значение, иначе возвращается второе значение. Например:
SELECT ProductName, Manufacturer,
IF(ProductCount > 3, 'Много товара', 'Мало товара')
FROM Products;Функция IFNULL проверяет значение некоторого выражения. Если оно равно NULL, то функция возвращает значение, которое передается в качестве второго параметра:
IFNULL(выражение, значение)Например, возьмем следующую таблицу
CREATE TABLE Clients
(
Id INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Phone VARCHAR(20) NULL,
Email VARCHAR(20) NULL
);
INSERT INTO Clients (FirstName, LastName, Phone, Email)
VALUES ('Tom', 'Smith', '+36436734', NULL),
('Bob', 'Simpson', NULL, NULL);И применим при получении данных функцию IFNULL:
SELECT FirstName, LastName,
IFNULL(Phone, 'не определено') AS Phone,
IFNULL(Email, 'неизвестно') AS Email
FROM Clients;COALESCE(выражение_1, выражение_2, выражение_N)Например, выберем из таблицы Clients пользователей и в контактах у них определим либо телефон, либо электронный адрес, если они не равны NULL:
SELECT FirstName, LastName,
COALESCE(Phone, Email, 'не определено') AS Contacts
FROM Clients;То есть в данном случае возвращается телефон, если он определен. Если он не определен, то возвращается электронный адрес. Если и электронный адрес не определен, то возвращается строка "не определено".
- ПРАКТИЧЕСКОЕ ЗАДАНИЕ №2
- создать таблички "goods" и “sales”. Заполнить их данными;
- сгруппировать значения количества в 3 сегмента — меньше 100, 100-300 и больше 300, используя CASE;
- создать таблицу “orders”, заполнить ее значениями. Показать “полный” статус заказа, используя COALESCE.
К примеру, разные товары могут иметь одних и тех же производителей, и, допустим, у нас следующая таблица товаров:
CREATE TABLE Products
(
Id INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(30) NOT NULL,
Manufacturer VARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price DECIMAL NOT NULL
);
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)
VALUES
('iPhone X', 'Apple', 3, 71000),
('iPhone 8', 'Apple', 3, 56000),
('Galaxy S9', 'Samsung', 6, 56000),
('Galaxy S8', 'Samsung', 2, 46000),
('Honor 10', 'Huawei', 3, 26000);Выберем всех производителей:
SELECT Manufacturer FROM Products;Однако при таком запросе производители повторяются.
Теперь применим оператор DISTINCT для выборки уникальных значений:
SELECT DISTINCT Manufacturer FROM Products;Также мы можем задавать выборку уникальные значения по нескольким столбцам:
SELECT DISTINCT Manufacturer, ProductCount FROM Products;В данном случае для выборки используются столбцы Manufacturer и ProductCount. Из пяти строк только для двух строк эти столбцы имеют повторяющиеся значения. Поэтому в выборке будет 4 строки.
Оператор IN определяет набор значений, которые должны иметь столбцы:
WHERE выражение [NOT] IN (выражение)Выражение в скобках после IN определяет набор значений. Этот набор может вычисляться динамически на основании, например, еще одного запроса, либо это могут быть константные значения. Например, выберем товары, у которых производитель либо Samsung, либо Xiaomi, либо Huawei:
SELECT * FROM Products
WHERE Manufacturer IN ('Samsung', 'Xiaomi', 'Huawei');Оператор NOT, наоборот, позволяет выбрать все строки, столбцы которых не имеют определенных значений:
SELECT * FROM Products
WHERE Manufacturer NOT IN ('Samsung', 'Xiaomi', 'Huawei');⬆️В начало Оператор BETWEEN определяет диапазон значений с помощью начального и конечного значения, которому должно соответствовать выражение:
WHERE выражение [NOT] BETWEEN начальное_значение AND конечное_значениеНапример, получим все товары, у которых цена от 20 000 до 50 000 (начальное и конечное значения также включаются в диапазон):
SELECT * FROM Products
WHERE Price BETWEEN 20000 AND 50000;Если надо, наоборот, выбрать те строки, которые не попадают в данный диапазон, то добавляется оператор NOT:
SELECT * FROM Products
WHERE Price NOT BETWEEN 20000 AND 50000;Также можно использовать более сложные выражения. Например, получим товары по совокупной стоимости (цена * количество):
SELECT * FROM Products
WHERE Price * ProductCount BETWEEN 90000 AND 150000;⬆️В начало Оператор LIKE принимает шаблон строки, которому должно соответствовать выражение.
WHERE выражение [NOT] LIKE шаблон_строкиДля определения шаблона могут применяться ряд специальных символов подстановки:
| Символ | Описание |
|---|---|
| % | соответствует любой подстроке, которая может иметь любое количество символов, при этом подстрока может и не содержать ни одного символа. Например, выражение WHERE ProductName LIKE 'Galaxy%' соответствует таким значениям как "Galaxy Ace 2" или "Galaxy S7" |
| _ | соответствует любому одиночному символу. Например, выражение WHERE ProductName LIKE 'Galaxy S_' соответствует таким значениям как "Galaxy S7" или "Galaxy S8". |
Применим оператор LIKE:
SELECT * FROM Products
WHERE ProductName LIKE 'iPhone%';REGEXP позволяет задать регулярное выражение, которому должно соответствовать значение столбца. В этом плане REGEXP представляет более изощренный и комплексный способ фильтрации, нежели оператор LIKE. REGEXP имеет похожий синтаксис:
WHERE выражение [NOT] REGEXP регулярное выражениеРегулярное выражение может принимать следующие специальные символы:
| Символ | Описание |
|---|---|
| ^ | указывает на начало строки |
| $ | указывает на конец строки |
| . | соответствует любому одиночному символу |
| [символы] | соответствует любому одиночному символу из скобок |
| [начальный_символ-конечный_символ] | соответствует любому одиночному символу из диапазона символов |
| | | отделяет два шаблона строки, и значение должно соответствовать одну из этих шаблонов |
Примеры REGEXP:
WHERE ProductName REGEXP 'Phone';строка должна содержать "Phone", например, iPhone X, Nokia Phone N, iPhone
WHERE ProductName REGEXP '^Phone';строка должна начинаться с "Phone", например, Phone 34, PhoneX
WHERE ProductName REGEXP 'Phone$';строка должна заканчиваться на "Phone", например, iPhone, Nokia Phone
WHERE ProductName REGEXP 'iPhone [78]';строка должна содержать либо iPhone 7, либо iPhone 8
WHERE ProductName REGEXP 'iPhone [6-8]';строка должна содержать либо iPhone 6, либо iPhone 7, либо iPhone 8
Например, найдем товары, названия которых содержат либо "Phone", либо "Galaxy":
SELECT * FROM Products
WHERE ProductName REGEXP 'Phone|Galaxy';⬆️В начало Оператор IS NULL позволяет выбрать все строки, столбцы которых имеют значение NULL:
SELECT * FROM Products
WHERE ProductCount IS NULL;С помощью добавления оператора NOT можно, наоброт, выбрать строки, столбцы которых не имеют значения NULL:
SELECT * FROM Products
WHERE ProductCount IS NOT NULL;Например, упорядочим выборку из таблицы Products по столбцу Price:
SELECT * FROM Products
ORDER BY Price;Также можно производить упорядочивание данных по псевдониму столбца, который определяется с помощью оператора AS:
SELECT ProductName, ProductCount * Price AS TotalSum
FROM Products
ORDER BY TotalSum;В качестве критерия сортировки также можно использовать сложное выражение на основе столбцов:
SELECT ProductName, Price, ProductCount
FROM Products
ORDER BY ProductCount * Price;По умолчанию данные сортируются по возрастанию, однако с помощью оператора DESC можно задать сортировку по убыванию.
SELECT ProductName, ProductCount
FROM Products
ORDER BY ProductCount DESC;По умолчанию вместо DESC используется оператор ASC, который сортирует по возрастанию:
SELECT ProductName, ProductCount
FROM Products
ORDER BY ProductCount ASC;При сортировке сразу по нескольким столбцам все эти столбцы указываются через запятую после оператора ORDER BY:
SELECT ProductName, Price, Manufacturer
FROM Products
ORDER BY Manufacturer, ProductName;Здесь строки сначала сортируются по столбцу Manufacturer по возрастанию. Затем если есть две строки, в которых столбец Manufacturer имеет одинаковое значение, то они сортируются по столбцу ProductName также по возрастанию. Но опять же с помощью ASC и DESC можно отдельно для разных столбцов определить сортировку по возрастанию и убыванию:
SELECT ProductName, Price, Manufacturer
FROM Products
ORDER BY Manufacturer ASC, ProductName DESC;LIMIT [offset,] rowcountЕсли оператору LIMIT передается один параметр, то он указывает на количество извлекаемых строк. Если передается два параметра, то первый параметр устанавливает смещение относительно начала, то есть сколько строк нужно пропустить, а второй параметр также указывает на количество извлекаемых строк.
Например, выберем первые три строки:
SELECT * FROM Products
LIMIT 3;Теперь используем второй параметр и укажем смещение, с которой должна происходить выборка:
SELECT * FROM Products
LIMIT 2, 3;В данном случае пропускаются две первые строки и извлекаются следующие 3 строки.
Как правило, оператор LIMIT используетс вместе с оператором ORDER BY:
SELECT * FROM Products
ORDER BY ProductName
LIMIT 2, 3;В MySQL есть следующие агрегатные функции:
-
AVG: вычисляет среднее значение
-
SUM: вычисляет сумму значений
-
MIN: вычисляет наименьшее значение
-
MAX: вычисляет наибольшее значение
-
COUNT: вычисляет количество строк в запросе
Все агрегатные функции принимают в качестве параметра выражение, которое представляет критерий для определения значений. Зачастую, в качестве выражения выступает название столбца, над значениями которого надо проводить вычисления.
Выражения в функциях AVG и SUM должно представлять числовое значение (например, столбец, который хранит числовые значения). Выражение в функциях MIN, MAX и COUNT может представлять числовое или строковое значение или дату.
Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL.
Функция Avg возвращает среднее значение на диапазоне значений столбца таблицы.
Например, пусть есть следующая таблица товаров Products:
CREATE TABLE Products
(
Id INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(30) NOT NULL,
Manufacturer VARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price DECIMAL NOT NULL
);
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
VALUES
('iPhone X', 'Apple', 3, 76000),
('iPhone 8', 'Apple', 2, 51000),
('iPhone 7', 'Apple', 5, 32000),
('Galaxy S9', 'Samsung', 2, 56000),
('Galaxy S8', 'Samsung', 1, 46000),
('Honor 10', 'Huawei', 5, 28000),
('Nokia 8', 'HMD Global', 6, 38000)Найдем среднюю цену товаров из базы данных:
SELECT AVG(Price) AS Average_Price FROM ProductsДля поиска среднего значения в качестве выражения в функцию передается столбец Price. Для получаемого значения устанавливается псевдоним Average_Price, хотя в принципе устанавливать псевдоним необязательно.
На этапе выборки можно применять фильтрацию. Например, найдем среднюю цену для товаров определенного производителя:
SELECT AVG(Price) FROM Products
WHERE Manufacturer='Apple'Также можно находить среднее значение для более сложных выражений. Например, найдем среднюю сумму всех товаров, учитывая их количество:
SELECT AVG(Price * ProductCount) FROM ProductsФункция Count вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT(*) подсчитывает число строк в выборке:
SELECT COUNT(*) FROM ProductsВторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:
SELECT COUNT(Manufacturer) FROM ProductsФункции Min и Max вычисляют минимальное и максимальное значение по столбцу соответственно. Например, найдем минимальную и максимальную цены среди товаров:
SELECT MIN(Price), MAX(Price) FROM ProductsДанные функции также игнорируют значения NULL и не учитывают их при подсчете.
Функция Sum вычисляет сумму значений столбца. Например, подсчитаем общее количество товаров:
SELECT SUM(ProductCount) FROM ProductsТакже вместо имени столбца может передаваться вычисляемое выражение. Например, найдем общую стоимость всех имеющихся товаров:
SELECT SUM(ProductCount * Price) FROM ProductsПо умолчанию все вышеперечисленных пять функций учитывают все строки выборки для вычисления результата. Но выборка может содержать повторяющие значения. Если необходимо выполнить вычисления только над уникальными значениями, исключив из набора значений повторяющиеся данные, то для этого применяется оператор DISTINCT.
SELECT COUNT(DISTINCT Manufacturer) FROM ProductsПо умолчанию вместо DISTINCT применяется оператор ALL, который выбирает все строки:
SELECT COUNT(ALL Manufacturer) FROM ProductsВ данном случае мы видим, что производители могут повторяться в таблице, так как некоторые товары могут иметь одних и тех же производителей. Поэтому чтобы подсчитать количество уникальных производителей, необходимо использовать оператор DISTINCT.
Так как ALL неявно подразумевается при отсутствии DISTINCT, то его можно не указывать.
Объединим применение нескольких функций:
SELECT COUNT(*) AS ProdCount,
SUM(ProductCount) AS TotalCount,
MIN(Price) AS MinPrice,
MAX(Price) AS MaxPrice,
AVG(Price) AS AvgPrice
FROM ProductsОператоры GROUP BY и HAVING позволяют сгруппировать данные. Они употребляются в рамках команды SELECT:
SELECT столбцы
FROM таблица
[WHERE условие_фильтрации_строк]
[GROUP BY столбцы_для_группировки]
[HAVING условие_фильтрации_групп]
[ORDER BY столбцы_для_сортировки]Оператор GROUP BY определяет, как строки будут группироваться.
Например, сгруппируем товары по производителю
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
GROUP BY ManufacturerПервый столбец в выражении SELECT - Manufacturer представляет название группы, а второй столбец - ModelsCount представляет результат функции Count, которая вычисляет количество строк в группе.
И если в выражении SELECT производится выборка по одному или нескольким столбцам и также используются агрегатные функции, то необходимо использовать выражение GROUP BY. Так, следующий пример работать не будет, так как он не содержит выражение группировки:
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM ProductsОператор GROUP BY может выполнять группировку по множеству столбцов. Так, добавим группировку по количеству товаров:
SELECT Manufacturer, ProductCount, COUNT(*) AS ModelsCount
FROM Products
GROUP BY Manufacturer, ProductCountСледует учитывать, что выражение GROUP BY должно идти после выражения WHERE, но до выражения ORDER BY:
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
WHERE Price > 30000
GROUP BY Manufacturer
ORDER BY ModelsCount DESC⬆️В начало Оператор HAVING позволяет выполнить фильтрацию групп, то есть определяет, какие группы будут включены в выходной результат.
Использование HAVING во многом аналогично применению WHERE. Только если WHERE применяется для фильтрации строк, то HAVING - для фильтрации групп.
Например, найдем все группы товаров по производителям, для которых определено более 1 модели:
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
GROUP BY Manufacturer
HAVING COUNT(*) > 1В одной команде также можно сочетать выражения WHERE и HAVING:
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
WHERE Price * ProductCount > 80000
GROUP BY Manufacturer
HAVING COUNT(*) > 1;То есть в данном случае сначала фильтруются строки: выбираются те товары, общая стоимость которых больше 80000. Затем выбранные товары группируются по производителям. И далее фильтруются сами группы - выбираются те группы, которые содержат больше 1 модели.
Если при этом необходимо провести сортировку, то выражение ORDER BY идет после выражения HAVING:
SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
WHERE Price * ProductCount > 80000
GROUP BY Manufacturer
HAVING SUM(ProductCount) > 2
ORDER BY Units DESC;Здесь группировка идет по производителям, и также выбирается количество моделей для каждого производителя (Models) и общее количество всех товаров по всем этим моделям (Units). В конце группы сортируются по количеству товаров по убыванию.
| Номер | Операция |
|---|---|
| 1 | FROM, включая JOINs |
| 2 | WHERE |
| 3 | GROUP BY |
| 4 | HAVING |
| 5 | Функции WINDOW |
| 6 | SELECT |
| 7 | DISTINCT |
| 8 | UNION |
| 9 | ORDER BY |
| 10 | LIMIT и OFFSET |
- ПРАКТИЧЕСКОЕ ЗАДАНИЕ №3
- Ознакомьтесь с ТАБЛИЦЕЙ;
- Создайте таблицу;
- Отсортируйте поле “зарплата” (salary) в порядке убывания и возрастания;
- Выведите 5 максимальных зарплат (salary);
- Найдите количество сотрудников по специальности “Рабочий” (post) в возрасте от 24 до 42 лет;
- Найдите количество специальностей;
- Выведите специальности, у которых средний возраст сотрудника меньше 44 лет;
- Если не ID, то какое поле можно добавить, чтобы использовать его в качестве первичного ключа.
Подзапросы представляют выражения SELECT, которые встроены в другие запросы SQL. Рассмотрим простейший пример применения подзапросов.
Например, создадим таблицы для товаров и заказов:
CREATE TABLE Products
(
Id INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(30) NOT NULL,
Manufacturer VARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price DECIMAL NOT NULL
);
CREATE TABLE Orders
(
Id INT AUTO_INCREMENT PRIMARY KEY,
ProductId INT NOT NULL,
ProductCount INT DEFAULT 1,
CreatedAt DATE NOT NULL,
Price DECIMAL NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products(Id) ON DELETE CASCADE
);Таблица Orders содержит данные о купленным товарам из таблицы Products.
Добавим в таблицы некоторые данные:
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)
VALUES ('iPhone X', 'Apple', 2, 76000),
('iPhone 8', 'Apple', 2, 51000),
('iPhone 7', 'Apple', 5, 42000),
('Galaxy S9', 'Samsung', 2, 56000),
('Galaxy S8', 'Samsung', 1, 46000),
('Honor 10', 'Huawei', 2, 26000),
('Nokia 8', 'HMD Global', 6, 38000);
INSERT INTO Orders (ProductId, CreatedAt, ProductCount, Price)
VALUES
(
(SELECT Id FROM Products WHERE ProductName='Galaxy S8'),
'2018-05-21',
2,
(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
),
(
(SELECT Id FROM Products WHERE ProductName='iPhone X'),
'2018-05-23',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone X')
),
(
(SELECT Id FROM Products WHERE ProductName='iPhone 8'),
'2018-05-21',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone 8')
);При добавлении данных в таблицу Orders как раз используются подзапросы. Например, первый заказ был сделан на товар Galaxy S8. Соответственно в таблицу Orders нам надо сохранить информацию о заказе, где поле ProductId указывает на Id товара Galaxy S8, поле Price - на его цену. Но на момент написания запроса нам может быть неизвестен ни Id покупателя, ни Id товара, ни цена товара. В этом случае можно выполнить подзапрос в виде
(SELECT Price FROM Products WHERE ProductName='iPhone 8')Подзапрос выполняет команду SELECT и заключается в скобки. В данном же случае при добавлении одного товара выполняется два подзапроса. Каждый подзапрос возвращает одно скалярное значение, например, числовой идентификатор.
В примере выше подзапросы выполнялись к другой таблице, но могут выполняться и к той же, для которой вызывается основной запрос. Например, найдем товары из таблицы Products, которые имеют минимальную цену:
SELECT * FROM Products
WHERE Price = (SELECT MIN(Price) FROM Products);Или найдем товары, цена которых выше средней:
SELECT * FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);Подзапросы бывают коррелирующими и некоррелирующими. В примерах выше команды SELECT фактически выполняли один подзапрос для всех строк, извлекаемых командой. Например, подзапрос возвращает минимальную или среднюю цену, которая не изменится, сколько бы мы строк не выбирали в основном запросе. То есть результат подзапроса не зависел от строк, которые выбираются в основном запросе. И такой подзапрос выполняется один раз для всего внешнего запроса.
Но также можно использовать и коррелирующие подзапросы (correlated subquery), результаты которых зависят от строк, которые выбираются в основном запросе.
Например, выберем все заказы из таблицы Orders, добавив к ним информацию о товаре:
SELECT CreatedAt, Price,
(SELECT ProductName FROM Products
WHERE Products.Id = Orders.ProductId) AS Product
FROM Orders;В данном случае для каждой строки из таблицы Orders будет выполняться подзапрос, результат которого зависит от столбца ProductId. И каждый подзапрос может возвращать различные данные.
Коррелирующий подзапрос может выполняться и для той же таблицы, к которой выполняется основной запрос. Например, выберем из таблицы Products те товары, стоимость которых выше средней цены товаров для данного производителя:
SELECT ProductName,
Manufacturer,
Price,
(SELECT AVG(Price) FROM Products AS SubProds
WHERE SubProds.Manufacturer=Prods.Manufacturer) AS AvgPrice
FROM Products AS Prods
WHERE Price >
(SELECT AVG(Price) FROM Products AS SubProds
WHERE SubProds.Manufacturer=Prods.Manufacturer);Здесь определено два коррелирующих подзапроса. Первый подзапрос определяет спецификацию столбца AvgPrice. Он будет выполняться для каждой строки, извлекаемой из таблицы Products. В подзапрос передается производитель товара и на его основе выбирается средняя цена для товаров именно этого производителя. И так как производитель у товаров может отличаться, то и результат подзапроса в каждом случае также может отличаться.
Второй подзапрос аналогичен, только он используется для фильтрации извлекаемых из таблицы Products. И также он будет выполняться для каждой строки.
Чтобы избежать двойственности при фильтрации в подзапросе при сравнении производителей (SubProds.Manufacturer=Prods.Manufacturer) для внешней выборки установлен псевдоним Prods, а для выборки из подзапросов определен псевдоним SubProds.
Следует учитывать, что коррелирующие подзапросы выполняются для каждой отдельной строки выборки и выполнение таких подзапросов может замедлять выполнение всего запроса в целом.
В выражении SELECT мы можем вводить подзапросы четырьмя способами:
- В условии в выражении WHERE
- В условии в выражении HAVING
- В качестве таблицы для выборки в выражении FROM
- В качестве спецификации столбца в выражении SELECT
Рассмотрим некоторые из этих случаев. Например, получим все товары, у которых цена выше средней:
SELECT * FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)Чтобы получить нужные товары, нам вначале надо выполнить подзапрос на получение средней цены товара: SELECT AVG(Price) FROM Products.
Нередко подзапросы применяются вместе с оператором IN, который выбирает из набора значений. И подзапрос как раз может предоставить требуемый набор значений. Например, выберем все товары из таблицы Products, на которые есть заказы в таблице Orders:
SELECT * FROM Products
WHERE Id IN (SELECT ProductId FROM Orders)То есть подзапрос в данном случае выбирает все идентификаторы товаров из Orders, затем по этим идентификаторам извлекаютя товары из Products.
Добавив оператор NOT, мы можем выбрать те товары, на которые нет заказов в таблице Orders:
SELECT * FROM Products
WHERE Id NOT IN (SELECT ProductId FROM Orders)Стоит отметить, что это не самый эффективный способ для извлечения связанных данных из других таблиц, так как для сведения данных из разных таблиц можно использовать оператор JOIN, который рассматривается в следующем разделе.
При использовании в операторах сравнения подзапросы должны возвращать одно скалярное значение. Но иногда возникает необходимость получить набор значений. Чтобы при использовании в операторах сравнения подзапрос мог возвращать набор значений, перед ним необходимо использовать один из операторов: ALL, SOME или ANY.
При использовании ключевого слова ALL условие в операции сравнения должно быть верно для всех значений, которые возвращаются подзапросом. Например, найдем все товары, цена которых меньше чем у любого товара фирмы Apple:
SELECT * FROM Products
WHERE Price < ALL(SELECT Price FROM Products WHERE Manufacturer='Apple')Если бы мы в данном случае опустили бы ключевое слово ALL, то мы бы столкнулись с ошибкой.
Допустим, если данный подзапрос возвращает значения vаl1, val2 и val3, то условие фильтрации фактически было бы аналогично объединению этих значений через оператор AND:
WHERE Price < val1 AND Price < val2 AND Price < val3В тоже время подобный запрос гораздо проще переписать другим образом:
SELECT * FROM Products
WHERE Price < (SELECT MIN(Price) FROM Products WHERE Manufacturer='Apple')Как работает оператор ALL:
- x > ALL (1, 2) эквивалентно x > 2
- x < ALL (1, 2) эквивалентно x < 1
- x = ALL (1, 2) эквивалентно (x = 1) AND (x = 2)
- x <> ALL (1, 2) эквивалентно x NOT IN (1, 2)
Операторы ANY и SOME условие в операции сравнения должно быть истинным для хотя бы одного из значений, возвращаемых подзапросом. По своему действию оба этих оператора аналогичны, поэтому можно применять любой из них. Например, в следующем случае получим товары, которые стоят меньше самого дорогого товара компании Apple:
SELECT * FROM Products
WHERE Price < ANY(SELECT Price FROM Products WHERE Manufacturer='Apple')И также стоит отметить, что данный запрос можно сделать проще, переписав следующим образом:
SELECT * FROM Products
WHERE Price < (SELECT MAX(Price) FROM Products WHERE Manufacturer='Apple')Как работает оператор ANY (а также SOME):
- x > ANY (1, 2) эквивалентно x > 1
- x < ANY (1, 2) эквивалентно x < 2
- x = ANY (1, 2) эквивалентно x IN (1, 2)
- x <> ANY (1, 2) эквивалентно (x <> 1) OR (x <> 2)
Результат подзапроса может представлять отдельный столбец в выборке. Например, выберем все заказы и добавим к ним информацию о названии товара:
SELECT *,
(SELECT ProductName FROM Products WHERE Id=Orders.ProductId) AS Product
FROM OrdersВ команде INSERT подзапросы могут применяться для определения значения, которое вставляется в один из столбцов:
INSERT INTO Orders (ProductId, CreatedAt, ProductCount, Price)
VALUES
(
(SELECT Id FROM Products WHERE ProductName='Galaxy S8'),
'2018-05-23',
2,
(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
)В команде UPDATE подзапросы могут применяться:
- В качестве устанавливаемого значения после оператора SET
- Как часть условия в выражении WHERE
Так, увеличим в таблице Orders количество купленных товаров компании Apple на 2:
UPDATE Orders
SET ProductCount = ProductCount + 2
WHERE ProductId IN (SELECT Id FROM Products WHERE Manufacturer='Apple');Или установим для заказа цену товара, полученную в результате подзапроса:
UPDATE Orders
SET Price = (SELECT Price FROM Products WHERE Id=Orders.ProductId) + 3000
WHERE Id=1;В команде DELETE подзапросы также применяются как часть условия. Так, удалим все заказы на Galaxy S8:
DELETE FROM Orders
WHERE ProductId=(SELECT Id FROM Products WHERE ProductName='Galaxy S8');Оператор EXISTS проверяет, возвращает ли подзапрос какое-либо значение. Как правило, этот оператор используется для индикации того, что как минимум одна строка в таблице удовлетворяет некоторому условию. Поскольку возвращения набора строк не происходит, то подзапросы с подобным оператором выполняются довольно быстро.
Применение оператора имеет следующий формальный синтаксис:
WHERE [NOT] EXISTS (подзапрос)Например, найдем все товары из таблицы Products, на которые есть заказы в таблице Orders:
SELECT * FROM Products
WHERE EXISTS
(SELECT * FROM Orders WHERE Orders.ProductId = Products.Id)Если мы хотим узнать, наоброт, есть ли в таблице строки, которые НЕ удовлетворяют условию, то можно использовать операторы NOT EXISTS. Например, найдем все товары из таблицы Products, на которые не было заказов в таблице Orders:
SELECT * FROM Products
WHERE NOT EXISTS
(SELECT * FROM Orders WHERE Products.Id = Orders.ProductId)Стоит отметить, что для получения подобного результата можно было бы использовать и опеатор IN:
SELECT *
FROM Products
WHERE Id NOT IN (SELECT ProductId FROM Orders)Но поскольку при применении EXISTS не происходит выборка строк, то его использование более оптимально и эффективно, чем использование оператора IN.
Нередко возникает необходимость в одном запросе получить данные сразу из нескольких таблиц. Для сведения данных из разных таблиц мы можем использовать разные способы. Рассмотрим не самый распространный, однако довольно простой способ, который представляет неявное соединение таблиц.
Допустим, у нас есть следующие таблицы, которые связаны между собой связями:
CREATE TABLE Products
(
Id INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(30) NOT NULL,
Manufacturer VARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price DECIMAL NOT NULL
);
CREATE TABLE Customers
(
Id INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
Id INT AUTO_INCREMENT PRIMARY KEY,
ProductId INT NOT NULL,
CustomerId INT NOT NULL,
CreatedAt DATE NOT NULL,
ProductCount INT DEFAULT 1,
Price DECIMAL NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products(Id) ON DELETE CASCADE,
FOREIGN KEY (CustomerId) REFERENCES Customers(Id) ON DELETE CASCADE
);Здесь таблицы Products и Customers связаны с таблицей Orders связью один ко многим. Таблица Orders в виде внешних ключей ProductId и CustomerId содержит ссылки на столбцы Id из соответственно таблиц Products и Customers. Также она хранит количество купленного товара (ProductCount) и и по какой цене он был куплен (Price). И кроме того, таблицы также хранит в виде столбца CreatedAt дату покупки.
Пусть эти таблицы будут содержать следующие данные:
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)
VALUES ('iPhone X', 'Apple', 2, 76000),
('iPhone 8', 'Apple', 2, 51000),
('iPhone 7', 'Apple', 5, 42000),
('Galaxy S9', 'Samsung', 2, 56000),
('Galaxy S8', 'Samsung', 1, 46000),
('Honor 10', 'Huawei', 2, 26000),
('Nokia 8', 'HMD Global', 6, 38000);
INSERT INTO Customers(FirstName) VALUES ('Tom'), ('Bob'),('Sam');
INSERT INTO Orders (ProductId, CustomerId, CreatedAt, ProductCount, Price)
VALUES
(
(SELECT Id FROM Products WHERE ProductName='Galaxy S8'),
(SELECT Id FROM Customers WHERE FirstName='Tom'),
'2018-05-21',
2,
(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
),
(
(SELECT Id FROM Products WHERE ProductName='iPhone X'),
(SELECT Id FROM Customers WHERE FirstName='Tom'),
'2018-05-23',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone X')
),
(
(SELECT Id FROM Products WHERE ProductName='iPhone X'),
(SELECT Id FROM Customers WHERE FirstName='Bob'),
'2018-05-21',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone X')
);Теперь соединим две таблицы Orders и Customers:
SELECT * FROM Orders, Customers;При такой выборке каждая строка из таблицы Orders будет соединяться с каждой строкой из таблицы Customers. То есть, получится перекрестное соединение. Например, в Orders три строки, а в Customers то же три строки, значит мы получим 3 * 3 = 9 строк. Такой способ соединения применялся при решении ПРАКТИЧЕСКОГО ЗАДАНИЯ №2.
Едва ли это тот результат, который хотелось бы видеть. Тем более каждый заказ из Orders связан с конкретным покупателем из Customers, а не со всеми возможными покупателями.
Чтобы решить задачу более корректно, необходимо использовать выражение WHERE и фильтровать строки при условии, что поле CustomerId из Orders соответствует полю Id из Customers:
SELECT * FROM Orders, Customers
WHERE Orders.CustomerId = Customers.Id;Теперь объединим данные по трем таблицам Orders, Customers и Proucts. То есть получим все заказы и добавим информацию по клиенту и связанному товару:
SELECT Customers.FirstName, Products.ProductName, Orders.CreatedAt
FROM Orders, Customers, Products
WHERE Orders.CustomerId = Customers.Id AND Orders.ProductId=Products.Id;Так как здесь нужно соединить три таблицы, то применяются как минимум два условия. Ключевой таблицей остается Orders, из которой извлекаются все заказы, а затем к ней подсоединяется данные по клиенту по условию Orders.CustomerId = Customers.Id и данные по товару по условию Orders.ProductId=Products.Id
В данном случае названия таблиц сильно увеличивают код, но мы его можем сократить за счет использования псевдонимов таблиц:
SELECT C.FirstName, P.ProductName, O.CreatedAt
FROM Orders AS O, Customers AS C, Products AS P
WHERE O.CustomerId = C.Id AND O.ProductId=P.Id;Если необходимо при использовании псевдонима выбрать все столбцы из определенной таблицы, то можно использовать звездочку:
SELECT C.FirstName, P.ProductName, O.*
FROM Orders AS O, Customers AS C, Products AS P
WHERE O.CustomerId = C.Id AND O.ProductId=P.Id;Более распространенный подход соединения данных из разных таблиц представляет применение оператора JOIN. Общий формальный синтаксис применения оператора INNER JOIN:
SELECT столбцы
FROM таблица1
[INNER] JOIN таблица2
ON условие1
[[INNER] JOIN таблица3
ON условие2]После оператора JOIN идет название второй таблицы, из которой надо добавить данные в выборку. Перед JOIN может использоваться необязательное ключевое слово INNER. Его наличие или отсутствие ни на что не влияет. Затем после ключевого слова ON указывается условие соединения. Это условие устанавливает, как две таблицы будут сравниваться. В большинстве случаев для соединения применяется первичный ключ главной таблицы и внешний ключ зависимой таблицы.
Возьмем таблицы с данными из прошлой темы и, используя JOIN, выберем все заказы и добавим к ним информацию о товарах:
SELECT Orders.CreatedAt, Orders.ProductCount, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId;Поскольку таблицы могут содержать столбцы с одинаковыми названиями, то при указании столбцов для выборки указывается их полное имя вместе с именем таблицы, например, "Orders.ProductCount".
Используя псевдонимы для таблиц, можно сократить код:
SELECT O.CreatedAt, O.ProductCount, P.ProductName
FROM Orders AS O
JOIN Products AS P
ON P.Id = O.ProductId;Также можно присоединять данные сразу из нескольких таблиц. Например, добавим к заказу информацию о покупателе из таблицы Customers:
SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId
JOIN Customers ON Customers.Id=Orders.CustomerId;Благодаря соединению таблиц мы можем использовать их столбцы для фильтрации выборки или ее сортировки:
SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId
JOIN Customers ON Customers.Id=Orders.CustomerId
WHERE Products.Price > 45000
ORDER BY Customers.FirstName;Условия после ключевого слова ON могут быть более сложными по составу:
SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId AND Products.Manufacturer='Apple'
JOIN Customers ON Customers.Id=Orders.CustomerId
ORDER BY Customers.FirstName;В данном случае выбираем все заказы на товары, производителем которых является Apple.
При использовании оператора JOIN следует учитывать, что процесс соединения таблиц может быть ресурсоемким, поэтому следует соединять только те таблицы, данные из которых действительно необходимы. Чем больше таблиц соединяется, тем больше снижается производительность.
Выше мы обсудили Inner Join или внутреннее соединение таблиц. Но также в MySQL мы можем использовать и так называемое внешнее соединение или Outer Join. В отличие от Inner Join внешнее соединение возвращает все строки одной или двух таблиц, которые участвуют в соединении.
Outer Join имеет следующий формальный синтаксис:
SELECT столбцы
FROM таблица1
{LEFT|RIGHT} [OUTER] JOIN таблица2 ON условие1
[{LEFT|RIGHT} [OUTER] JOIN таблица3 ON условие2]...Перед оператором JOIN указывается одно из ключевых слов LEFT или RIGHT, которые определяют тип соединения:
- LEFT: выборка будет содержать все строки из первой или левой таблицы
- RIGHT: выборка будет содержать все строки из второй или правой таблицы
Также перед оператором JOIN может указываться ключевое слово OUTER, но его применение необязательно. Далее после JOIN указывается присоединяемая таблица, а затем идет условие соединения.
Например, соединим таблицы Orders и Customers:
SELECT FirstName, CreatedAt, ProductCount, Price, ProductId
FROM Orders LEFT JOIN Customers
ON Orders.CustomerId = Customers.IdТаблица Orders является первой или левой таблицей, а таблица Customers - правой таблицей. Поэтому, так как здесь используется выборка по левой таблице, то вначале будут выбираться все строки из Orders, а затем к ним по условию Orders.CustomerId = Customers.Id будут добавляться связанные строки из Customers.
По вышеприведенному результату может показаться, что левостороннее соединение аналогично INNER Join, но это не так. Inner Join объединяет строки из дух таблиц при соответствии условию. Если одна из таблиц содержит строки, которые не соответствуют этому условию, то данные строки не включаются в выходную выборку. Left Join выбирает все строки первой таблицы и затем присоединяет к ним строки правой таблицы. К примеру, возьмем таблицу Customers и добавим к покупателям информацию об их заказах:
#INNER JOIN
SELECT FirstName, CreatedAt, ProductCount, Price
FROM Customers JOIN Orders
ON Orders.CustomerId = Customers.Id;
#LEFT JOIN
SELECT FirstName, CreatedAt, ProductCount, Price
FROM Customers LEFT JOIN Orders
ON Orders.CustomerId = Customers.Id;В случае с LEFT JOIN MySQL выбирает сначала всех покупателей из таблицы Customers, затем сопоставляет их с заказами из таблицы Orders через условие Orders.CustomerId = Customers.Id. Однако не у всех покупателей есть заказы. В этом случае покупателю для соответствующих столбцов устанавливаются значения NULL.
SELECT FirstName, CreatedAt, ProductCount, Price
FROM Customers RIGHT JOIN Orders
ON Orders.CustomerId = Customers.Id;Теперь будут выбираться все строки из Orders (из правой таблицы), а к ним уже будет присоединяться связанные по условию строки из таблицы Customers.
Используем левостороннее соединение для добавления к заказам информации о пользователях и товарах:
SELECT Customers.FirstName, Orders.CreatedAt,
Products.ProductName, Products.Manufacturer
FROM Orders
LEFT JOIN Customers ON Orders.CustomerId = Customers.Id
LEFT JOIN Products ON Orders.ProductId = Products.Id;И также можно применять более комплексные условия с фильтрацией и сортировкой. Например, выберем все заказы с информацией о клиентах и товарах по тем товарам, у которых цена больше 45000, и отсортируем по дате заказа:
SELECT Customers.FirstName, Orders.CreatedAt,
Products.ProductName, Products.Manufacturer
FROM Orders
LEFT JOIN Customers ON Orders.CustomerId = Customers.Id
LEFT JOIN Products ON Orders.ProductId = Products.Id
WHERE Products.Price > 45000
ORDER BY Orders.CreatedAt;Или выберем всех пользователей из Customers, у которых нет заказов в таблице Orders:
SELECT FirstName FROM Customers
LEFT JOIN Orders ON Customers.Id = Orders.CustomerId
WHERE Orders.CustomerId IS NULL;Также можно комбинировать Inner Join и Outer Join:
SELECT Customers.FirstName, Orders.CreatedAt,
Products.ProductName, Products.Manufacturer
FROM Orders
JOIN Products ON Orders.ProductId = Products.Id AND Products.Price > 45000
LEFT JOIN Customers ON Orders.CustomerId = Customers.Id
ORDER BY Orders.CreatedAt;Вначале по условию к таблице Orders через Inner Join присоединяется связанная информация из Products, затем через Outer Join добавляется информация из таблицы Customers.
Оператор UNION позволяет обединить две однотипных выборки. Эти выборки могут быть из разных таблиц или из одной и той же таблицы. Формальный синтаксис объединения:
SELECT_выражение1
UNION [ALL] SELECT_выражение2
[UNION [ALL] SELECT_выражениеN]Например, пусть в базе данных будут две отдельные таблицы для клиентов банка (таблица Customers) и для сотрудников банка (таблица Employees):
CREATE TABLE Customers
(
Id INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
AccountSum DECIMAL
);
CREATE TABLE Employees
(
Id INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL
);
INSERT INTO Customers(FirstName, LastName, AccountSum)
VALUES
('Tom', 'Smith', 2000),
('Sam', 'Brown', 3000),
('Mark', 'Adams', 2500),
('Paul', 'Ins', 4200),
('John', 'Smith', 2800),
('Tim', 'Cook', 2800);
INSERT INTO Employees(FirstName, LastName)
VALUES
('Homer', 'Simpson'),
('Tom', 'Smith'),
('Mark', 'Adams'),
('Nick', 'Svensson');Здесь мы можем заметить, что обе таблицы, несмотря на наличие различных данных, могут характеризоваться двумя общими атрибутами - именем (FirstName) и фамилией (LastName). Выберем сразу всех клиентов банка и его сотрудников из обеих таблиц:
SELECT FirstName, LastName
FROM Customers
UNION SELECT FirstName, LastName FROM Employees;Здесь из первой таблицы выбираются два значения - имя и фамилия клиента. Из второй таблицы Employees также выбираются два значения - имя и фамилия сотрудников. То есть при объединении количество выбираемых столбцов и их тип совпадают для обеих выборок.
При этом названия столбцов объединенной выборки будут совпадать с названия столбцов первой выборки. И если мы захотим при этом еще произвести сортировку, то в выражениях ORDER BY необходимо ориентироваться именно на названия столбцов первой выборки:
SELECT FirstName AS FName, LastName
FROM Customers
UNION SELECT FirstName, LastName
FROM Employees
ORDER BY FName DESC;В данном случае каждая выборка имеет по столбцу FName из первой выборки. Тем не менее при сортировке будет учитываться и значение столбца FirstName из второй выборки.
Если же в одной выборке больше столбцов, чем в другой, то они не смогут быть объединены. Например, в следующем случае объединение завершится с ошибкой:
SELECT FirstName, LastName, AccountSum
FROM Customers
UNION SELECT FirstName, LastName
FROM Employees;Если оба объединяемых набора содержат в строках идентичные значения, то при объединении повторяющиеся строки удаляются. Например, в случае с таблицами Customers и Employees сотрудники банка могут быть одновременно его клиентами и содержаться в обеих таблицах. При объединении в примерах выше всех дублирующиеся строки удалялись. Если же необходимо при объединении сохранить все, в том числе повторяющиеся строки, то для этого необходимо использовать оператор ALL:
SELECT FirstName, LastName
FROM Customers
UNION ALL SELECT FirstName, LastName
FROM Employees
ORDER BY FirstName;Объединять выборки можно и из одной и той же таблицы. Например, в зависимости от суммы на счете клиента нам надо начислять ему определенные проценты:
SELECT FirstName, LastName, AccountSum + AccountSum * 0.1 AS TotalSum
FROM Customers WHERE AccountSum < 3000
UNION SELECT FirstName, LastName, AccountSum + AccountSum * 0.3 AS TotalSum
FROM Customers WHERE AccountSum >= 3000;В данном случае если сумма меньше 3000, то начисляются проценты в размере 10% от суммы на счете. Если на счете больше 3000, то проценты увеличиваются до 30%.
- ПРАКТИЧЕСКОЕ ЗАДАНИЕ №4
- Дана ТАБЛИЦА;
- Вывести на экран количество машин каждого цвета для машин марок BMW и LADA;
- Вывести на экран марку авто и количество AUTO не этой марки;
- Написать запрос, который вернет строки из таблицы test_a, id которых нет в таблице test_b, НЕ используя ключевого слова NOT.
Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.
Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.
Представления могут основываться как на таблицах, так и на других представлениях, т.е. могут быть вложенными (до 32 уровней вложенности).
- ПРАКТИЧЕСКОЕ ЗАДАНИЕ №5
- Создайте представление, в которое попадут автомобили стоимостью до 25 000 долларов;
- Измените в существующем представлении порог для стоимости: пусть цена будет до 30 000 долларов (используя оператор ALTER VIEW);
- Создайте представление, в котором будут только автомобили марки “Шкода” и “Ауди”.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent