 WHERE (...) ожидает логическое условие — например, =, <, IN, и т. д.

 Чтобы выделить номер месяца из даты используется функция MONTH(дата).

Если определяется месяц для значений столбца date_first, то используется
запись MONTH(date_first). ИЛИ WHERE EXTRACT(month FROM date_first)
 Для вычитания двух дат используется функция DATEDIFF(date_last, date_first),
результатом которой является количество дней между дата_1 и дата_2. +1
 Чтобы выделить название месяца из даты используется функция
MONTHNAME(дата), возвращает название месяца на английском языке для
указанной даты. Например, MONTHNAME('2020-04-12')='April'.

ВЫБОРКА ДАННЫХ
Оператор LIKE, в отличие от операторов отношения равно (=) и не равно (<>), LIKE
позволяет сравнивать строки не на полное совпадение (не совпадение), а в
соответствии с шаблоном.

Символ-шаблон
Описание Пример

SELECT * FROM book WHERE author

Любая строка, LIKE '%М.%'
% содержащая ноль или выполняет поиск и выдает все книги,
более символов инициалы авторов которых содержат
«М.»

SELECT * FROM book WHERE title LIKE

'Поэм_'
_ (подчеркивание) Любой одиночный символ выполняет поиск и выдает все книги,
названия которых либо «Поэма», либо
«Поэмы» и пр.

Выборка данных с сортировкой

Используется ключевые слова ORDER BY, после которых задаются имена столбцов.
При этом строки сортируются по первому столбцу. Если указан второй столбец,
сортировка осуществляется только для тех строк, у которых значения первого
столбца одинаковы. По умолчанию ORDER BY выполняет сортировку по
возрастанию. Чтобы управлять направлением сортировки вручную, после имени
столбца указывается ключевое слово ASC (по возрастанию) или DESC (по
убыванию).
SELECT author, title, amount AS Количество
FROM book
WHERE price < 750
ORDER BY author, amount DESC;
Выбор уникальных элементов столбца
Чтобы отобрать уникальные элементы некоторого столбца используется ключевое
слово DISTINCT, которое размещается сразу после SELECT.
Другой способ – использование оператора GROUP BY, который группирует данные
при выборке, имеющие одинаковые значения в некотором столбце. Столбец, по
которому осуществляется группировка, указывается после GROUP BY .

С помощью GROUP BY можно выбрать уникальные элементы столбца, по которому

осуществляется группировка. Результат будет точно такой же как при использовании
DISTINCT.

Выборка данных по условию

После указания таблицы, откуда выбираются данные, задаётся ключевое слово
WHERE и логическое выражение, от результата которого зависит будет ли включена
строка в выборку или нет.

Логическое выражение может включать операторы сравнения (равно «=», не равно

«<>», больше «>», меньше «<», больше или равно«>=», меньше или равно «<=») и
выражения, допустимые в SQL.
SELECT title, price
FROM book
WHERE price < 600;

Выборка данных, групповые функции SUM и COUNT(считает, сколько записей

(строк) относится к группе.)
При группировке над элементами столбца, входящими в группу можно выполнить
различные действия, например, просуммировать их или найти количество элементов
в группе.
SELECT author, sum(amount), count(amount)
FROM book
GROUP BY author;
SELECT city, count(city) AS Количество
FROM trip
GROUP BY city
ORDER BY city
* LIMIT 1; - ограничения вывода записей, размещается после раздела ORDER
BY.
Выборка данных, вычисляемые столбцы, логические функции IF():
IF(логическое_выражение, выражение_1, выражение_2)
Функция вычисляет логическое_выражение, если оно истина – в поле заносится
значение выражения_1, в противном случае – значение выражения_2. Все три
параметра IF() являются обязательными.
Допускается использование вложенных функций,
вместо выражения_1 или выражения_2 может стоять новая функция IF.
Пример
Для каждой книги из таблицы book установим скидку следующим образом: если
количество книг меньше 4, то скидка будет составлять 50% от цены, в противном
случае 30%.
Запрос:
SELECT title, amount, price,
IF(amount<4, price*0.5, price*0.7) AS sale
FROM book;

СОЗДАНИЕ ТАБЛИЦЫ
CREATE TABLE book(
book_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(50),
author VARCHAR(30),
price DECIMAL(8, 2),
amount INT
);

ДОБАВЛЕНИЕ ЗАПИСЕЙ В ТАБЛИЦУ

INSERT INTO имя_таблицы(столбец_1, столбец_2, ..., столбец_N)
VALUES
('Лирика', 'Пастернак Б.Л.', 518.99, 2),
(значение_2_1, значение_2_2, ..., значение_2_N),
...
(значение_M_1, значение_M_2, ..., значение_M_N);

ВЛОЖЕННЫЕ ЗАПРОСЫ
Применяют для:
 сравнения выражения с результатом вложенного запроса;
 определения того, включено ли выражение в результаты вложенного запроса;
 проверки того, выбирает ли запрос определенные строки.

Вложенный запрос, возвращающий одно значение:

o может использоваться в условии отбора записей WHERE как обычное значение
совместно с операциями =, <>, >=, <=, >, <.
SELECT title, author, price, amount
FROM book
WHERE price = (
SELECT MIN(price)
FROM book
);
o может использоваться в выражениях как обычный операнд, например, к нему
можно что-то прибавить, вычесть и пр.
SELECT title, author, amount
 FROM book
WHERE ABS(amount - (SELECT AVG(amount) FROM book)) >3;

Вложенный запрос, может возвращать несколько значений одного столбца. Тогда

его можно использовать в разделе WHERE совместно с оператором IN.
WHERE имя_столбца IN (вложенный запрос, возвращающий один столбец)
Оператор IN определяет, совпадает ли значение столбца с одним из значений,
содержащихся во вложенном запросе. При этом логическое выражение после
WHERE получает значение истина. Оператор NOT IN выполняет обратное действие –
выражение истинно, если значение столбца не содержится во вложенном запросе.
SELECT title, author, amount, price
FROM book
WHERE author IN (
SELECT author
FROM book
GROUP BY author
HAVING SUM(amount) >= 12
);
Операторы ANY и ALL используются для сравнения некоторого значения с
результирующим набором вложенного запроса, состоящим из одного столбца. Тип
данных возвращаемого вложенного столбца должен совпадать с типом данных
столбца (или выражения), с которым происходит сравнение.
Операторы ALL и ANY можно использовать только с вложенными запросами.
При ANY в результирующую таблицу будут включены все записи, для которых
выражение со знаком отношения верно хотя бы для одного элемента
результирующего запроса. Как работает оператор ANY:

amount > ANY (10, 12) эквивалентно amount > 10

amount < ANY (10, 12) эквивалентно amount < 12
amount = ANY (10, 12) эквивалентно (amount = 10) OR (amount = 12), а также amount
IN (10,12)
amount <> ANY (10, 12) вернет все записи с любым значением amount, включая 10 и
12
При ALL в результирующую таблицу будут включены все записи, для которых
выражение со знаком отношения верно для всех элементов результирующего
запроса. Как работает оператор ALL:
amount > ALL (10, 12) эквивалентно amount > 12
amount < ALL (10, 12) эквивалентно amount < 10
amount = ALL (10, 12) не вернет ни одной записи, так как эквивалентно (amount = 10)
AND (amount = 12)
amount <> ALL (10, 12) вернет все записи кроме тех, в которых amount равно 10 или
12
SELECT title, author, amount, price
FROM book
WHERE amount < ALL (
 SELECT AVG(amount)
FROM book
GROUP BY author
);

Вложенный запрос после SELECT

В этом случае результат выполнения запроса выводится в отдельном столбце
результирующей таблицы. При этом результатом запроса может быть только одно
значение, тогда оно будет повторяться во всех строках. Также вложенный запрос
может использоваться в выражениях.
SELECT title, author, amount,
(
SELECT AVG(amount)
FROM book
) AS Среднее_количество
FROM book
WHERE abs(amount - (SELECT AVG(amount) FROM book)) >3;

ДОБАВЛЕНИЕ ЗАПИСЕЙ ИЗ ДРУГОЙ ТАБЛИЦЫ

В этом случае вместо раздела VALUES записывается запрос на выборку,
начинающийся с SELECT. В нем можно использовать WHERE, GROUP BY, ORDER
BY.
INSERT INTO book (title, author, price, amount)
SELECT title, author, price, amount
FROM supply;
Добавление записей, вложенные запросы
Занести из таблицы supply в таблицу book только те книги, названия которых
отсутствуют в таблице book.
Запрос:
INSERT INTO book (title, author, price, amount)
SELECT title, author, price, amount
FROM supply
WHERE title NOT IN (SELECT title FROM book);

ЗАПРОСЫ НА КОРРЕКТИРОВКУ ДАННЫХ

Запросы на обновление
UPDATE таблица SET поле = выражение(новое значение)
Уменьшить на 30% цену книг в таблице book.
UPDATE book
SET price = 0.7 * price;
SELECT * FROM book;
Можно изменять только часть записей в таблице. Через WHERE с условием отбора
строк для изменения.
Уменьшить на 30% цену тех книг в таблице book, количество которых меньше 5.
 UPDATE book
SET price = 0.7 * price
WHERE amount < 5;

SELECT * FROM book;

Запросы на обновление нескольких столбцов
UPDATE таблица SET поле1 = выражение1, поле2 = выражение2
UPDATE book
SET amount = amount - buy, buy = 0;
SELECT * FROM book;
SET столбец = IF(условие, выражение_1, выражение_2) в варажениях нельзя писать
=, возвращает значение, а не присваивание.
UPDATE book
SET buy = IF(buy > amount, amount, buy),
price = IF(buy = 0, price * 0.9, price);
SELECT * FROM book;

Запросы на обновление нескольких таблиц

 для столбцов, имеющих одинаковые имена, необходимо указывать имя
таблицы, к которой они относятся, например, [Link] – столбец price из
таблицы book, [Link] – столбец price из таблицы supply;
 все таблицы, используемые в запросе, нужно перечислить после ключевого
слова UPDATE;
 в запросе обязательно условие WHERE, в котором указывается условие при
котором обновляются данные.
Запросы на удаление
DELETE FROM таблица;
DELETE FROM таблица
WHERE условие;
DELETE FROM supply
WHERE title IN (
SELECT title
FROM book);
Запросы на создание таблицы на основе данных из другой таблицы.
Для этого используется запрос SELECT, результирующая таблица которого и будет
новой таблицей базы данных. При этом имена столбцов запроса становятся именами
столбцов новой таблицы:
CREATE TABLE имя_таблицы AS
SELECT ...
CREATE TABLE ordering AS
SELECT author, title, 5 AS amount
FROM book
WHERE amount < 4;
CREATE TABLE ordering AS
SELECT author, title,
 (
SELECT ROUND(AVG(amount))
FROM book
) AS amount
FROM book
WHERE amount < 4;

ГРУППИРОВКА ДАННЫХ по нескольким столбцам

В разделе GROUP BY можно указывать несколько столбцов, разделяя их запятыми.
Тогда к одной группе будут относиться записи, у которых равны значения столбцов,
входящих в группу.
SELECT name, number_plate, violation, count(*)
FROM fine
GROUP BY name, number_plate, violation;
В разделе GROUP BY нужно перечислять все НЕАГРЕГИРОВАННЫЕ столбцы (к
которым не применяются групповые функции) из SELECT.

СВЯЗИ МЕЖДУ ТАБЛИЦАМИ

Создание таблицы с внешними ключами
При создании таблицы, которая содержит внешние ключи необходимо учитывать,
что:
 внешний ключ и как связанное поле главной таблицы должна иметь
одинаковый тип данных;
 необходимо указать главную таблицу и столбец, по которому осуществляется
связь:
FOREIGN KEY (связанное_поле_зависимой_таблицы)
REFERENCES главная_таблица (связанное_поле_главной_таблицы)
По умолчанию любой столбец, кроме ключевого, может содержать значение NULL.
При создании таблицы это можно переопределить, используя ограничение NOT
NULL для этого столбца:
CREATE TABLE таблица (
столбец_1 INT NOT NULL,
столбец_2 VARCHAR(10)
);
В созданной таблице в столбец_1 не может содержать пустое значение,
а столбец_2 - может.
Для внешних ключей рекомендуется устанавливать ограничение NOT NULL (если
это совместимо с другими опциями, которые будут рассмотрены в следующем шаге).
CREATE TABLE book (
book_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(50),
 author_id INT NOT NULL,
price DECIMAL(8,2),
amount INT,
FOREIGN KEY (author_id) REFERENCES author (author_id)
);
SHOW COLUMNS FROM book;

Действия при удалении записи главной таблицы

С помощью выражения ON DELETE можно установить действия, которые
выполняются для записей подчиненной таблицы при удалении связанной строки из
главной таблицы. При удалении можно установить следующие опции:
 CASCADE: автоматически удаляет строки из зависимой таблицы при
удалении связанных строк в главной таблице.
 SET NULL: при удалении связанной строки из главной таблицы устанавливает
для столбца внешнего ключа значение NULL. (В этом случае столбец внешнего
ключа должен поддерживать установку NULL).
 SET DEFAULT похоже на SET NULL за тем исключением, что значение
внешнего ключа устанавливается не в NULL, а в значение по умолчанию для
данного столбца.
 RESTRICT: отклоняет удаление строк в главной таблице при наличии
связанных строк в зависимой таблице.
* при удалении автора из таблицы author, необходимо удалить все записи о книгах из таблицы book,
написанные этим автором. Данное действие необходимо прописать при создании таблицы.
Запрос:
CREATE TABLE book (
book_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(50),
author_id INT NOT NULL,
price DECIMAL(8,2),
amount INT,
FOREIGN KEY (author_id) REFERENCES author (author_id) ON
DELETE CASCADE
);

ЗАПРОСЫ НА ВЫБОРКУ, СОЕДИНЕНИЕ ТАБЛИЦ

Соединение INNER JOIN
Соединяет две таблицы. Оператор симметричен, порядок таблиц неважен.
SELECT
...
FROM
таблица_1 INNER JOIN таблица_2
ON условие ...
Вывести название книг и их авторов.
Запрос:
SELECT title, name_author
 FROM
author INNER JOIN book
ON author.author_id = book.author_id;
Поскольку поля author_id в таблицах book и author называются одинаково,
необходимо в запросах указывать полную ссылку на них
(book.author_id и author.author_id).

Внешнее соединение LEFT и RIGHT OUTER JOIN

Оператор внешнего соединения LEFT OUTER JOIN (можно
использовать LEFT JOIN) соединяет две таблицы. Порядок таблиц для оператора
важен, поскольку оператор не является симметричным.
SELECT
...
FROM
таблица_1 LEFT JOIN таблица_2
ON условие
...
Результат запроса формируется так:
1. в результат включается внутреннее соединение (INNER JOIN) первой и
второй таблицы в соответствии с условием;
2. затем в результат добавляются те записи первой таблицы, которые не вошли во
внутреннее соединение на шаге 1, для таких записей соответствующие поля
второй таблицы заполняются значениями NULL.
SELECT name_genre
FROM genre LEFT JOIN book
ON book.genre_id = genre.genre_id
WHERE book.genre_id is Null;
Перекрёстное соединение CROSS JOIN
Оператор перекрёстного соединения, или декартова произведения CROSS JOIN (в
запросе вместо ключевых слов можно поставить запятую между
таблицами) соединяет две таблицы. Порядок таблиц для оператора неважен,
поскольку оператор является симметричным. Его структура:
SELECT
...
FROM
таблица_1 CROSS JOIN таблица_2
...
или
SELECT
...
FROM
таблица_1, таблица_2
...
Результат запроса формируется так: каждая строка одной таблицы соединяется с
каждой строкой другой таблицы, формируя в результате все возможные сочетания
строк двух таблиц.

SELECT name_city, name_author, (DATE_ADD('2020-01-01', INTERVAL

FLOOR(RAND() * 365) DAY)) as Дата
FROM author, city
ORDER BY name_city asc, Дата desc;
Запросы на выборку из нескольких таблиц
Для каждой пары таблиц, включаемых в запрос, необходимо указать свой оператор
соединения. Наиболее распространенным является внутреннее соединение INNER
JOIN

SELECT title, name_author, name_genre, price, amount

FROM
author
INNER JOIN book ON author.author_id = book.author_id
INNER JOIN genre ON genre.genre_id = book.genre_id
WHERE price BETWEEN 500 AND 700;
Запросы для нескольких таблиц с группировкой
SELECT name_author, count(title) AS Количество
FROM
author INNER JOIN book
on author.author_id = book.author_id
GROUP BY name_author
ORDER BY name_author;

SELECT name_author, sum(amount) AS Количество

FROM
author LEFT JOIN book
on author.author_id = book.author_id
GROUP BY name_author
having Количество < 10 OR Количество IS NULL
ORDER BY Количество;

Запросы для нескольких таблиц со вложенными запросами

В запросах, построенных на нескольких таблицах, можно использовать вложенные
запросы. Вложенный запрос может быть включен: после ключевого слова SELECT,
после FROM и в условие отбора после WHERE (HAVING).
SELECT name_author, SUM(amount) as Количество
FROM
author INNER JOIN book
on author.author_id = book.author_id
GROUP BY name_author
HAVING SUM(amount) =
 (/* вычисляем максимальное из общего количества книг каждого
автора */
SELECT MAX(sum_amount) AS max_sum_amount
FROM
(/* считаем количество книг каждого автора */
SELECT author_id, SUM(amount) AS sum_amount
FROM book GROUP BY author_id
) query_in
);

SELECT name_author
FROM
author INNER JOIN book
on author.author_id = book.author_id
INNER JOIN genre ON genre.genre_id = book.genre_id
GROUP BY name_author
HAVING COUNT( DISTINCT(name_genre))=1;
Вложенные запросы в операторах соединения
Вложенные запросы могут использоваться в операторах соединения JOIN. При этом
им необходимо присваивать имя, которое записывается сразу после закрывающей
скобки вложенного запроса. Вложенный запрос может стоять как справа, так и слева
от оператора JOIN. Допускается использование двух запросов в операторах
соединения.
SELECT
...
FROM
таблица ... JOIN
(
SELECT ...
) имя_вложенного_запроса
ON условие
...
Операция соединение, использование USING()
При описании соединения таблиц в некоторых случаях вместо ON и следующего за
ним условия можно использовать оператор USING().
USING позволяет указать набор столбцов, которые есть в обеих объединяемых
таблицах. Если база данных хорошо спроектирована, а каждый внешний ключ имеет
такое же имя, как и соответствующий первичный ключ (например, genre.genre_id =
book.genre_id), тогда можно использовать предложение USING для реализации
операции JOIN.
Вариант с ON
SELECT title, name_author, author.author_id /* явно указать таблицу - обязательно */
FROM
 author INNER JOIN book
ON author.author_id = book.author_id;
Вариант с USING
SELECT title, name_author, author_id /* имя таблицы, из которой берется author_id,
указывать не обязательно*/
FROM
author INNER JOIN book
USING(author_id);

ЗАПРОСЫ КОРРЕКТИРОВКИ, СОЕДИНЕНИЕ ТАБЛИЦ

Запросы на обновление, связанные таблицы
В запросах на обновление можно использовать связанные таблицы:
UPDATE таблица_1
... JOIN таблица_2
ON выражение
...
SET ...
WHERE ...;
При этом исправлять данные можно во всех используемых в запросе таблицах.
UPDATE book
INNER JOIN author ON author.author_id = book.author_id
INNER JOIN supply ON [Link] = [Link]
and [Link] = author.name_author
SET [Link] = [Link] + [Link],
[Link] = 0
WHERE [Link] = [Link];
Запросы на добавление, связанные таблицы
Запросом на добавление можно добавить записи, отобранные с помощью запроса на
выборку, который включает несколько таблиц:
INSERT INTO таблица (список_полей)
SELECT список_полей_из_других_таблиц
FROM
таблица_1
... JOIN таблица_2 ON ...
...
INSERT INTO author (name_author)
SELECT [Link]
FROM
author
RIGHT JOIN supply on author.name_author = [Link]
WHERE name_author IS Null;
 select * from author;
Следующий шаг - добавить новые записи о книгах, которые есть в таблице supply и
нет в таблице book.

INSERT INTO book (title, author_id, price, amount)

SELECT title, author_id, price, amount
FROM
author
INNER JOIN supply ON author.name_author = [Link]
WHERE amount <> 0;

select * from book;

Запрос на обновление, вложенные запросы
UPDATE book
SET genre_id =
(
SELECT genre_id
FROM genre
WHERE name_genre = 'Роман'
)
WHERE book_id = 9; SELECT * FROM book;
Каскадное удаление записей связанных таблиц
Одним запросом удаляются связанные записи из главной и зависимой таблицы. В
нашем случае удалился автор Достоевский и все его книги.
DELETE FROM author
WHERE name_author LIKE "Д%";

DELETE FROM author

WHERE author_id IN (SELECT author_id
FROM book
GROUP BY author_id
HAVING sum(amount) < 20);

DELETE a FROM author a

INNER JOIN (
SELECT author_id FROM book
GROUP BY author_id
HAVING SUM(amount) < 20
)b
ON a.author_id = b.author_id;
Удаление записей главной таблицы с сохранением записей в зависимой
DELETE FROM genre
WHERE genre_id IN (SELECT genre_id
FROM book
GROUP BY genre_id
HAVING count(title)<3);
DELETE g FROM genre g
INNER JOIN (
SELECT genre_id
FROM book
GROUP BY genre_id
HAVING COUNT(title) < 3
)b
ON g.genre_id = b.genre_id;
Удаление записей, использование связанных таблиц
DELETE FROM таблица_1
USING
таблица_1
INNER JOIN таблица_2 ON ...
WHERE ...
Пример Удалить всех авторов из таблицы author, у которых есть
книги, количество экземпляров которых меньше 3. Из таблицы book
удалить все книги этих авторов.
Запрос:
DELETE FROM author
USING
author
INNER JOIN book ON author.author_id = book.author_id
WHERE [Link] < 3;
Запросы на основе трех и более связанных таблиц
Этот запрос строится на основе нескольких таблиц, для удобства нужно
определить фрагмент логической схемы базы данных, на основе которой строится
запрос. В нашем случае выбираются название книги из таблицы book и фамилия
клиента из таблицы client. Эти таблицы между собой непосредственно не связаны,
поэтому нужно добавить «связующие» таблицы buy и buy_book:

Для соединения этих таблиц используется INNER JOIN. Для удобства рекомендуется
связи описывать последовательно: client → buy → buy_book → book. А для
соединения использовать пару первичный ключ и внешний
ключ соответствующих таблиц. Например, соединение
таблиц client и buy осуществляется по условию client.client_id = buy.client_id.
Чтобы не усложнять схему, будем считать, что нам известен id Булгакова (это 1)
SELECT DISTINCT name_client
FROM
client
INNER JOIN buy ON client.client_id = buy.client_id
INNER JOIN buy_book ON buy_book.buy_id = buy.buy_id
 INNER JOIN book ON buy_book.book_id=book.book_id
WHERE title ='Мастер и Маргарита' and author_id = 1;

select buy_id, title, price, buy_book.amount

from buy
join client using(client_id)
join buy_book using(buy_id)
join book using(book_id)
where client.name_client='Баранов Павел'
order by buy_id, title