SQL

1. Основные понятия реляционных баз данных
2. Отношение, реляционная модель
3. Создание таблицы
4. Вставка записи в таблицу
5. Выборка данных, вычисляемые столбцы, математические функции IF
6. Выборка данных, операторы BETWEEN, IN
7. Выборка данных с сортировкой ORDER BY
8. Выборка данных, оператор LIKE
9. Выбор уникальных элементов столбца DISTINCT и GROUP BY
10. Выборка данных, групповые функции SUM и COUNT
11. Выборка данных, групповые функции MIN, MAX и AVG
12. Вычисления по таблице целиком
13. Выборка данных по условию, групповые функции
14. Выборка данных по условию, групповые функции, WHERE и HAVING

Основные понятия реляционных баз данных

Реляционная модель была разработана в конце 1960-х годов Е.Ф.Коддом . Она определяет способ представления данных (структуру данных), методы защиты данных (целостность данных), и операции, которые можно выполнять с данными (манипулирование данными). Эта модель лежит в основе всех реляционных баз данных до настоящего времени.

Основные принципы реляционных баз данных:

• все данные на концептуальном уровне представляются в виде объектов, заданных в виде строк и столбцов, называемых отношением, более распространенное название – таблица;
• в пересечение строки и столбца таблицы можно занести только одно значение;
• все операции выполняются над целыми отношениями и результатом этих операций является отношение.

Пример отношения:

На примере таблицы Сотрудник рассмотрим терминологию реляционных баз данных:

• отношение – это структура данных целиком, набор записей (в обычном понимании – таблица) , в примере –это Сотрудник;
• кортеж – это каждая строка , содержащая данные (более распространенный термин – запись ), например, <001, Борин С.А, 234-01-23, программист>, все кортежи в отношении должны быть различны;
• мощность – число кортежей в таблице (проще говоря, число записей), в данном случае 3, мощность отношения может быть любой (от 0 до бесконечности), порядок следования кортежей - неважен;
• атрибут – это столбец в таблице (более распространенный термин – поле ), в примере – Табельный номер, Фамилия И.О., Телефон, Должность)
• размерность – это число атрибутов в таблице, в данном случае – 4;
• размерность отношения должна быть больше 0, порядок следования атрибутов существенен;
• домен атрибута – это допустимые значения (неповторяющиеся), которые можно занести в поле , например для атрибута Должность домен – {инженер, программист}.

Отношение, реляционная модель

База данных, в том числе и реляционная, используется для формального описания некоторой предметной области реального мира, например, склада, учебного процесса и пр. Обязательным этапом перед созданием базы данных является ее проектирование (этот процесс разбирается в следующих модулях).

В первом модуле будем рассматривать простейшие предметные области, информацию о которых можно описать в виде одной таблицы. Каждая такая таблица ассоциируется с неким информационным объектом или событием реального мира – человеком, документом, посещением и т.д.

Пример.

Рассмотрим некоторый склад, на котором хранятся книги. Известно название книги, ее автор, количество экземпляров на складе и ее цена.

Всю эту информацию можно представить в виде таблицы, состоящей из 4 столбцов (приведено только 4 записи, на самом деле их значительно больше):

Перед созданием таблицы в базе данных необходимо описать ее структуру. Для этого выполняется следующая последовательность шагов:

1. Дать таблице имя, пусть она будет называться book, вот некоторые правила для выбора имен таблиц:

• может включать английские буквы, цифры и знак подчеркивания, должно начинаться с буквы;
• имя должно быть уникальным в пределах базы данных.

Также рекомендуется:

• чтобы имя было существительным в единственном числе;
• имя должно быть понятным и соответствовать тому объекту, который оно описывает;
• имя должно быть как можно короче, максимум до 10 символов.

Важно. Имена таблиц являются регистрозависимыми из-за операционной системы на которой работает stepik, то есть имя book и Book – разные имена. Рекомендуется для записи имен таблиц использовать только строчные (маленькие) буквы.

2. Определить структуру таблицы, из каких атрибутов(столбцов, полей) она будет состоять, в нашем случае это:

• title – поле для хранения названия книги;
• author – поле с фамилией автора книги ;
• priсe – цена книги;
• amount – количество книг.

Правила по выбору имени поля информационного объекта:

• может включать английские буквы, цифры и знак подчеркивания, должно начинаться с буквы;
• имя поля должно быть уникальным в пределах таблицы.

Рекомендации по выбору имени поля информационного объекта:

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

3. Включить ключевое поле book_id, которое является ОБЯЗАТЕЛЬНЫМ ЭЛЕМЕНТОМ каждой реляционной таблицы. Ключевое поле является уникальным для каждой записи, однозначно определяет запись и в дальнейшем будет использоваться для связей с другими таблицами.

Рекомендации по именованию ключевых полей:

• имя должно состоять из двух частей: начинаться с названия таблицы, которой поле принадлежит, затем через подчеркивание необходимо указать id.

Таким образом, наша таблицаbookбудет выглядеть следующим образом:

Создание таблицы

Для создания таблицы используется SQL-запрос. В нем указывается какая таблица создается, из каких атрибутов(полей) она состоит и какой тип данных имеет каждое поле, при необходимости указывается описание полей (ключевое поле и т.д.). Его структура :

• ключевые слова : CREATE TABLE
• имя создаваемой таблицы;
• открывающая круглая скобка «(»;
• название поля и его описание, которое включает тип поля и другие необязательные характеристики;
• запятая;
• название поля и его описание;
• ...
• закрывающая скобка «)».

Пример. Создадим таблицу genre следующей структуры:

Созданная таблица - пустая.

Рекомендации по записи SQL запроса

• Ключевые слова: SQL не является регистрозависимым языком (CREATE и create - одно и тоже ключевое слово).
• Ключевые слова SQL и типы данных рекомендуется записывать прописными (большими) буквами.
• Имена таблиц и полей - строчными (маленькими) буквами.
• SQL-запрос можно писать на нескольких строках.
• В конце SQL-запроса ставится точка с запятой (хотя если Вы пишете один запрос, это необязательно).

Вставка записи в таблицу

Для занесения новой записи в таблицу используется SQL запрос, в котором указывается в какую таблицу, в какие поля заносить новые значения. Структура запроса:

• ключевые слова INSERT INTO (ключевое слово INTO можно пропустить);
• имя таблицы, в которую добавляется запись;
• открывающая круглая скобка «(»;
• список полей через запятую, в которые следует занести новые данные;
• закрывающая скобка «)»;
• ключевое слово VALUES;
• открывающая круглая скобка «(»;
• список значений через запятую, которые заносятся в соответствующие поля, при этом текстовые значения заключаются в кавычки, числовые значения записываются без кавычек, в качестве разделителя целой и дробной части используется точка;
• закрывающая скобка «)».

Пример. В таблицу, состоящую из двух столбцов добавим новую строку, при этом в поле1 заносится значение1, в поле2 - значение2.

INSERT INTO таблица(поле1, поле2) 
VALUES (значение1, значение2);

В результате выполнения запроса новая запись заносится в конец обновляемой таблицы.

При составлении списка полей и списка значений необходимо учитывать следующее:

1. количество полей и количество значений в списках должны совпадать;
2. должно существовать прямое соответствие между позицией одного и того же элемента в обоих списках, поэтому первый элемент списка значений должен относиться к первому столбцу в списке столбцов, второй – ко второму столбцу и т.д.;
3. типы данных элементов в списке значений должны быть совместимы с типами данных соответствующих столбцов таблицы ( целое число можно занести в поле типа DECIMAL, обратная операция - недопустима);
4. новые значения нельзя добавлять в поля, описанные как PRIMARY KEY AUTO_INCREMENT;
5. рекомендуется заполнять все поля записи, если же поле пропущено, значение этого поля зависит от установленных по умолчанию значений, если значения не установлены - на данной платформе вставляется пустое значение (NULL).

Пример

Вставим новую запись в таблицу genre, созданную на предыдущем шаге ( в первых двух строках показана структура таблицы, далее - ее содержимое):

Запрос:

INSERT INTO genre (name_genre) VALUES ('Роман');

Задание

Решение:

INSERT book (title,author,price,amount)
VALUES ('Мастер и Маргарита', 'Булгаков М.А.', 670.99,3);
SELECT * from book

Решение для нескольких строк:

INSERT book (title, author, price, amount)
VALUES ('Белая гвардия', 'Булгаков М.А.',540.50,5);
INSERT book (title, author, price, amount)
VALUES ('Идиот', 'Достоевский Ф.М.', 460.00,10);
INSERT book (title, author, price, amount)
VALUES('Братья Карамазовы', 'Достоевский Ф.М.',799.01,2);

Выборка данных, вычисляемые столбцы, математические функции IF

В SQL реализована возможность заносить в поле значение в зависимости от условия. Для этого используется функция 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;

Результат:

+-----------------------+--------+--------+---------+
| title                 | amount | price  | sale    |
+-----------------------+--------+--------+---------+
| Мастер и Маргарита    | 3      | 670.99 | 335.495 |
| Белая гвардия         | 5      | 540.50 | 378.350 |
| Идиот                 | 10     | 460.00 | 322.000 |
| Братья Карамазовы     | 2      | 799.01 | 399.505 |
| Стихотворения и поэмы | 15     | 650.00 | 455.000 |
+-----------------------+--------+--------+---------+

Цена по скидке должна отображаться с двумя знаками после запятой, добавим в запрос округление:

SELECT title, amount, price, 
    ROUND(IF(amount<4, price*0.5, price*0.7),2) AS sale
FROM book;

Результат:

+-----------------------+--------+--------+--------+
| title                 | amount | price  | sale   |
+-----------------------+--------+--------+--------+
| Мастер и Маргарита    | 3      | 670.99 | 335.50 |
| Белая гвардия         | 5      | 540.50 | 378.35 |
| Идиот                 | 10     | 460.00 | 322.00 |
| Братья Карамазовы     | 2      | 799.01 | 399.51 |
| Стихотворения и поэмы | 15     | 650.00 | 455.00 |
+-----------------------+--------+--------+--------+

Пример

Усложним вычисление скидки в зависимости от количества книг. Если количество книг меньше 4 – то скидка 50%, меньше 11 – 30%, в остальных случаях – 10%. И еще укажем какая именно скидка на каждую книгу.

Запрос:

SELECT title, amount, price,
    ROUND(IF(amount < 4, price * 0.5, IF(amount < 11, price * 0.7, price * 0.9)), 2) AS sale,
    IF(amount < 4, 'скидка 50%', IF(amount < 11, 'скидка 30%', 'скидка 10%')) AS Ваша_скидка
FROM book;

Результат:

+-----------------------+--------+--------+--------+-------------+
| title                 | amount | price  | sale   | Ваша_скидка |
+-----------------------+--------+--------+--------+-------------+
| Мастер и Маргарита    | 3      | 670.99 | 335.50 | скидка 50%  |
| Белая гвардия         | 5      | 540.50 | 378.35 | скидка 30%  |
| Идиот                 | 10     | 460.00 | 322.00 | скидка 30%  |
| Братья Карамазовы     | 2      | 799.01 | 399.51 | скидка 50%  |
| Стихотворения и поэмы | 15     | 650.00 | 585.00 | скидка 10%  |
+-----------------------+--------+--------+--------+-------------+

Задание

Исходная таблица:

Решение:
SELECT
    author,
    title,
    CASE
        WHEN author = 'Булгаков М.А.' THEN ROUND(price * 1.10, 2)
        WHEN author = 'Есенин С.А.' THEN ROUND(price * 1.05, 2)
        ELSE price
    END AS new_price
FROM
    book;

Результат:

Выборка данных, операторы BETWEEN, IN

Логическое выражение после ключевого слова WHERE может включать операторы BETWEEN и IN. Приоритет у этих операторов такой же как у операторов сравнения, то есть они выполняются раньше, чем NOT, AND, OR.

Оператор BETWEEN позволяет отобрать данные, относящиеся к некоторому интервалу, включая его границы.

Пример

Выбрать названия и количества тех книг, количество которых от 5 до 14 включительно.

Запрос:

SELECT title, amount 
FROM book
WHERE amount BETWEEN 5 AND 14;

Результат:

+---------------+--------+
| title         | amount |
+---------------+--------+
| Белая гвардия | 5      |
| Идиот         | 10     |
+---------------+--------+

Этот запрос можно реализовать по-другому, результат будет точно такой же.

SELECT title, amount 
FROM book
WHERE amount >= 5 AND amount <=14;

Оператор IN позволяет выбрать данные, соответствующие значениям из списка.

Пример

Выбрать названия и цены книг, написанных Булгаковым или Достоевским.

Запрос:

SELECT title, price 
FROM book
WHERE author IN ('Булгаков М.А.', 'Достоевский Ф.М.');

Результат:

+--------------------+--------+
| title              | price  |
+--------------------+--------+
| Мастер и Маргарита | 670.99 |
| Белая гвардия      | 540.50 |
| Идиот              | 460.00 |
| Братья Карамазовы  | 799.01 |
+--------------------+--------+

Этот запрос можно реализовать по-другому, результат будет точно такой же.

SELECT title, price 
FROM book
WHERE author = 'Булгаков М.А.' OR author = 'Достоевский Ф.М.';

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

При выборке можно указывать столбец или несколько столбцов, по которым необходимо отсортировать отобранные строки. Для этого используются ключевые слова ORDER BY, после которых задаются имена столбцов. При этом строки сортируются по первому столбцу, если указан второй столбец, сортировка осуществляется только для тех строк, у которых значения первого столбца одинаковы. По умолчанию ORDER BY выполняет сортировку по возрастанию. Чтобы управлять направлением сортировки вручную, после имени столбца указывается ключевое слово ASC (по возрастанию) или DESC (по убыванию).

Столбцы после ключевого слова ORDER BY можно задавать:

• названием столбца;
• номером столбца;
• именем столбца (указанным после AS).

Пример

Вывести название, автора и цены книг. Информацию отсортировать по названиям книг в алфавитном порядке.

Запрос:

SELECT title, author, price
FROM book
ORDER BY title;

Результат:

+-----------------------+------------------+--------+
| title                 | author           | price  |
+-----------------------+------------------+--------+
| Белая гвардия         | Булгаков М.А.    | 540.50 |
| Братья Карамазовы     | Достоевский Ф.М. | 799.01 |
| Идиот                 | Достоевский Ф.М. | 460.00 |
| Мастер и Маргарита    | Булгаков М.А.    | 670.99 |
| Стихотворения и поэмы | Есенин С.А.      | 650.00 |
+-----------------------+------------------+--------+

Аналогичный результат получится при использовании запроса:

SELECT title, author, price
FROM book
ORDER BY 1;

Пример

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

Запрос:

SELECT author, title, amount AS Количество
FROM book
WHERE price < 750
ORDER BY author, amount DESC;

Результат:

+------------------+-----------------------+------------+
| author           | title                 | Количество |
+------------------+-----------------------+------------+
| Булгаков М.А.    | Белая гвардия         | 5          |
| Булгаков М.А.    | Мастер и Маргарита    | 3          |
| Достоевский Ф.М. | Идиот                 | 10         |
| Есенин С.А.      | Стихотворения и поэмы | 15         |
+------------------+-----------------------+------------+

Можно использовать другие варианты записи запроса:

SELECT author, title, amount AS Количество
FROM book
WHERE price < 750
ORDER BY author, Количество DESC;

SELECT author, title, amount AS Количество
FROM book
WHERE price < 750
ORDER BY 1, 3 DESC;

Важно! Если названия столбцов заключены в кавычки, то при использовании их в сортировке, необходимо записывать их БЕЗ КАВЫЧЕК.

Задание

Вывести автора и название книг, количество которых принадлежит интервалу от 2 до 14 (включая границы). Информацию отсортировать сначала по авторам (в обратном алфавитном порядке), а затем по названиям книг (по алфавиту).

Результат:

+------------------+--------------------+
| author           | title              |
+------------------+--------------------+
| Достоевский Ф.М. | Братья Карамазовы  |
| Достоевский Ф.М. | Идиот              |
| Булгаков М.А.    | Белая гвардия      |
| Булгаков М.А.    | Мастер и Маргарита |
+------------------+--------------------+

Выборка данных, оператор LIKE

Оператор LIKE используется для сравнения строк. В отличие от операторов отношения равно (=) и не равно (<>), LIKE позволяет сравнивать строки не на полное совпадение (не совпадение), а в соответствии с шаблоном. Шаблон может включать обычные символы и символы-шаблоны. При сравнении с шаблоном, его обычные символы должны в точности совпадать с символами, указанными в строке. Символы-шаблоны могут совпадать с произвольными элементами символьной строки.

Пример 1

Вывести названия книг, начинающихся с буквы «Б».

Запрос:

SELECT title 
FROM book
WHERE title LIKE 'Б%';
/* эквивалентное условие 
title LIKE 'б%'
*/

Результат:

+-------------------+
| title             |
+-------------------+
| Белая гвардия     |
| Братья Карамазовы |
+-------------------+

Пояснение

Пример 2

Вывести название книг, состоящих ровно из 5 букв.

Запрос:

SELECT title FROM book 
WHERE title LIKE "_____"

Результат:

+-------+
| title |
+-------+
| Идиот |
| Поэмы |
+-------+

Пояснение

Пример 3

Вывести книги, название которых длиннее 5 символов:

Запрос:

SELECT title FROM book 
WHERE title LIKE "______%";
/* эквивалентные условия 
title LIKE "%______"
title LIKE "%______%"
*/

Результат:

+-----------------------+
| title                 |
+-----------------------+
| Мастер и Маргарита    |
| Белая гвардия         |
| Братья Карамазовы     |
| Стихотворения и поэмы |
| Дети полуночи         |
| Лирика                |
| Капитанская дочка     |
+-----------------------+

Пояснение

Пример 4

Вывести названия книг, которые содержат букву "и" как отдельное слово, если считать, что слова в названии отделяются друг от друга пробелами и не содержат знаков препинания.

Запрос:

SELECT title FROM book 
WHERE   title LIKE "_% и _%" /*отбирает слово И внутри названия */
    OR title LIKE "и _%" /*отбирает слово И в начале названия */
    OR title LIKE "_% и" /*отбирает слово И в конце названия */
    OR title LIKE "и" /* отбирает название, состоящее из одного слова И */

Результат:

+-----------------------+
| title                 |
+-----------------------+
| Мастер и Маргарита    |
| Стихотворения и поэмы |
+-----------------------+

Пример 5

Вывести названия книг, которые состоят ровно из одного слова, если считать, что слова в названии отделяются друг от друга пробелами .

Запрос:

SELECT title FROM book 
WHERE title NOT LIKE "% %";    

Результат:

+--------+
| title  |
+--------+
| Идиот  |
| Лирика |
| Поэмы  |
+--------+

Задание

Вывести название и автора тех книг, название которых состоит из двух и более слов, а инициалы автора содержат букву «С». Считать, что в названии слова отделяются друг от друга пробелами и не содержат знаков препинания, между фамилией автора и инициалами обязателен пробел, инициалы записываются без пробела в формате: буква, точка, буква, точка. Информацию отсортировать по названию книги в алфавитном порядке.

Результат:

+-----------------------+-------------+
| title                 | author      |
+-----------------------+-------------+
| Капитанская дочка     | Пушкин А.С. |
| Стихотворения и поэмы | Есенин С.А. |
+-----------------------+-------------+

Решение:

SELECT title, author
FROM book
WHERE title LIKE '_% %'
    AND (author LIKE '% С.%' OR author LIKE "%С.%") 
ORDER BY title ASC;

Выбор уникальных элементов столбца DISTINCT и GROUP BY

Чтобы отобрать уникальные элементы некоторого столбца используется ключевое слово DISTINCT, которое размещается сразу после SELECT.

Пример

Выбрать различных авторов, книги которых хранятся в таблице book.

Запрос:

SELECT DISTINCT author
FROM book;

Результат:

+------------------+
| author           |
+------------------+
| Булгаков М.А.    |
| Достоевский Ф.М. |
| Есенин С.А.      |
+------------------+

С помощью GROUP BY можно выбрать уникальные элементы столбца, по которому осуществляется группировка. Результат будет точно такой же как при использовании DISTINCT.

Запрос:

SELECT  author
FROM book
GROUP BY author;

Т.е. запрос

select title,Count(title)
 from book
 group by author
 

Запрос SELECT COUNT(title) FROM book GROUP BY author будет подсчитывать количество записей в столбце title для каждого уникального автора в таблице book.

Выборка данных, групповые функции SUM и COUNT

При группировке над элементами столбца, входящими в группу можно выполнить различные действия, например, просуммировать их или найти количество элементов в группе.

Подробно рассмотрим, как осуществляется группировка данных по некоторому столбцу и вычисления над группой на следующем примере:

SELECT author, sum(amount), count(amount)
FROM book
GROUP BY author;

1. В таблице book определяются строки, в которых в столбце author одинаковые значения:

Получили 3 различные группы:

• группа I объединяет две записи, у которых в столбце author значение Булгаков М.А.;
• группа II объединяет три записи, у которых в столбце author значение Достоевский Ф.М.;
• группа III объединяет одну запись, у которой в столбце author значение Есенин С.А.

2. Вместо каждой группы в результирующий запрос включается одна запись. Запись как минимум включает значение столбца, по которому осуществляется группировка (в нашем случае это author):

3. Дальше можно выполнить вычисления над элементами КАЖДОЙ группы в отдельности, например, посчитать общее количество экземпляров книг каждого автора. Для этого используется групповая функция SUM(), а в скобках указывается столбец, по которому нужно выполнить суммирование ( в нашем случае amount):

4. Также можно посчитать, сколько записей относится к группе. Для этого используется функция COUNT(), в скобках можно указать ЛЮБОЙ столбец из группы, если группа не содержит пустых значений (ниже приведен пример, в котором показано, как работает COUNT(), если в группе есть пустые значения):

Пример

Посчитать, сколько экземпляров книг каждого автора хранится на складе.

Запрос:

SELECT author, SUM(amount)
FROM book
GROUP BY author;

Результат:

+------------------+-------------+
| author           | SUM(amount) |
+------------------+-------------+
| Булгаков М.А.    | 8           |
| Достоевский Ф.М. | 23          |
| Есенин С.А.      | 15          |
+------------------+-------------+

Пример

Посчитать, сколько различных книг каждого автора хранится на складе.

Только для этого примера в таблицу book добавлена запись с пустыми значениями в столбцах amount и price:

+---------+-----------------------+------------------+--------+--------+
| book_id | title                 | author           | price  | amount |
+---------+-----------------------+------------------+--------+--------+
| 1       | Мастер и Маргарита    | Булгаков М.А.    | 670.99 | 3      |
| 2       | Белая гвардия         | Булгаков М.А.    | 540.50 | 5      |
| 3       | Идиот                 | Достоевский Ф.М. | 460.00 | 10     |
| 4       | Братья Карамазовы     | Достоевский Ф.М. | 799.01 | 3      |
| 5       | Игрок                 | Достоевский Ф.М. | 480.50 | 10     |
| 6       | Стихотворения и поэмы | Есенин С.А.      | 650.00 | 15     |
| 7       | Черный человек        | Есенин С.А.      | Null   | Null   |
+---------+-----------------------+------------------+--------+--------+

Запрос:

/* чтобы проверить запрос, добавьте в таблицу строку */
INSERT INTO book (title, author, price, amount) VALUES ('Черный человек','Есенин С.А.', Null, Null);

SELECT author, COUNT(author), COUNT(amount), COUNT(*)
FROM book
GROUP BY author;

Результат:

+------------------+---------------+---------------+----------+
| author           | COUNT(author) | COUNT(amount) | COUNT(*) |
+------------------+---------------+---------------+----------+
| Булгаков М.А.    | 2             | 2             | 2        |
| Достоевский Ф.М. | 3             | 3             | 3        |
| Есенин С.А.      | 2             | 1             | 2        |
+------------------+---------------+---------------+----------+

Из таблицы с результатами запроса видно, что функцию COUNT() можно применять к любому столбцу, в том числе можно использовать и *, если таблица не содержит пустых значений. Если же в столбцах есть значения Null, (для группы по автору Есенин в нашем примере), то

• COUNT(*) — подсчитывает все записи, относящиеся к группе, в том числе и со значением NULL;
• COUNT(имя_столбца) — возвращает количество записей конкретного столбца (только NOT NULL), относящихся к группе.

Задание

Посчитать, количество различных книг и количество экземпляров книг каждого автора , хранящихся на складе. Столбцы назвать Автор, Различных_книг и Количество_экземпляров соответственно.

Исходная таблица:

Решение:

SELECT 
    author AS Автор, 
    COUNT(title) AS Различных_книг, 
    SUM(amount) AS Количество_экземпляров
FROM 
    book
GROUP BY 
    author;

Результат:

Выборка данных, групповые функции MIN, MAX и AVG

К групповым функциям SQL относятся: MIN(), MAX() и AVG(), которые вычисляют минимальное, максимальное и среднее значение элементов столбца, относящихся к группе.

Пример

Вывести минимальную цену книги каждого автора

Запрос:

SELECT author, MIN(price) AS min_price
FROM book
GROUP BY author;

Результат:

+------------------+-----------+
| author           | min_price |
+------------------+-----------+
| Булгаков М.А.    | 540.50    |
| Достоевский Ф.М. | 460.00    |
| Есенин С.А.      | 650.00    |
+------------------+-----------+

Задание

Вывести фамилию и инициалы автора, минимальную, максимальную и среднюю цену книг каждого автора . Вычисляемые столбцы назвать Минимальная_цена, Максимальная_цена и Средняя_цена соответственно.

SELECT author, 
    MIN(price) AS Минимальная_цена,
    MAX(price) AS Максимальная_цена,
    AVG(price) AS Средняя_цена
FROM book
GROUP BY author

Вычисления по таблице целиком

Групповые функции позволяют вычислять итоговые значения по всей таблице. Например, можно посчитать общее количество книг на складе, вычислить суммарную стоимость и пр. Для этого после ключевого слова SELECT указывается групповая функция для выражения или имени столбца, а ключевые слова GROUP BY опускаются.

Пример

Посчитать количество экземпляров книг на складе.

Запрос:

SELECT SUM(amount) AS Количество
FROM book;

Результат:

+------------+
| Количество |
+------------+
| 46         |
+------------+ 

Результатом таких запросов является единственная строка с вычисленными по таблице значениями.

Пример

Посчитать общее количество экземпляров книг на складе и их стоимость .

Запрос:

SELECT SUM(amount) AS Количество, 
    SUM(price * amount) AS Стоимость
FROM book;

Результат:

+------------+-----------+
| Количество | Стоимость |
+------------+-----------+
| 46         | 26267.50  |
+------------+-----------+

Задание

Вывести цену самой дешевой книги, цену самой дорогой и среднюю цену уникальных книг на складе. Названия столбцов Минимальная_цена, Максимальная_цена, Средняя_цена соответственно. Среднюю цену округлить до двух знаков после запятой.

Пояснение. В задании нужно посчитать среднюю цену уникальных книг на складе, а не среднюю цену всех экземпляров книг.

Решение:

select 
MIN(price) as Минимальная_цена,
MAX(price) as Максимальная_цена,
ROUND(AVG(price),2) as Средняя_цена
from book

Выборка данных по условию, групповые функции

В запросы с групповыми функциями можно включать условие отбора строк, которое в обычных запросах записывается после WHERE. В запросах с групповыми функциями вместо WHERE используется ключевое слово HAVING , которое размещается после оператора GROUP BY.

Пример

Найти минимальную и максимальную цену книг всех авторов, общая стоимость книг которых больше 5000.

Запрос:

SELECT author,
    MIN(price) AS Минимальная_цена, 
    MAX(price) AS Максимальная_цена
FROM book
GROUP BY author
HAVING SUM(price * amount) > 5000; 

Результат:

+------------------+------------------+-------------------+
| author           | Минимальная_цена | Максимальная_цена |
+------------------+------------------+-------------------+
| Достоевский Ф.М. | 460.00           | 799.01            |
| Есенин С.А.      | 650.00           | 650.00            |
+------------------+------------------+-------------------+

Также в запросах с группировкой можно сортировать данные.

Пример

Найти минимальную и максимальную цену книг всех авторов, общая стоимость книг которых больше 5000. Результат вывести по убыванию минимальной цены.

Запрос:

SELECT author,
    MIN(price) AS Минимальная_цена, 
    MAX(price) AS Максимальная_цена
FROM book
GROUP BY author
HAVING SUM(price * amount) > 5000 
ORDER BY Минимальная_цена DESC;

Результат:

+------------------+------------------+-------------------+
| author           | Минимальная_цена | Максимальная_цена |
+------------------+------------------+-------------------+
| Есенин С.А.      | 650.00           | 650.00            |
| Достоевский Ф.М. | 460.00           | 799.01            |
+------------------+------------------+-------------------+

Задание

Вычислить среднюю цену и суммарную стоимость тех книг, количество экземпляров которых принадлежит интервалу от 5 до 14, включительно. Столбцы назвать Средняя_цена и Стоимость, значения округлить до 2-х знаков после запятой.

Решение:
SELECT 
    ROUND(AVG(price),2) as Средняя_цена,
    ROUND(SUM(price*amount),2) as Стоимость
from book
where amount between 5 and 14

Основа таблицы:

Выборка данных по условию, групповые функции, WHERE и HAVING

WHERE и HAVING могут использоваться в одном запросе. При этом необходимо учитывать порядок выполнения SQL запроса на выборку на СЕРВЕРЕ:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Сначала определяется таблица, из которой выбираются данные (FROM), затем из этой таблицы отбираются записи в соответствии с условием WHERE, выбранные данные агрегируются (GROUP BY), из агрегированных записей выбираются те, которые удовлетворяют условию после HAVING. Потом формируются данные результирующей выборки, как это указано после SELECT ( вычисляются выражения, присваиваются имена и пр. ). Результирующая выборка сортируется, как указано после ORDER BY.

Важно! Порядок ВЫПОЛНЕНИЯ запросов - это не порядок ЗАПИСИ ключевых слов в запросе на выборку. Порядок записи (синтаксис запроса) остается таким же, как рассматривался ранее в курсе. Порядок ВЫПОЛНЕНИЯ нужен для того, чтобы понять, почему, например, в WHERE нельзя использовать имена выражений из SELECT. Просто SELECT выполняется компилятором позже, чем WHERE, поэтому ему неизвестно, какое там выражение написано.

Пример

Вывести максимальную и минимальную цену книг каждого автора, кроме Есенина, количество экземпляров книг которого больше 10.

SELECT author,
    MIN(price) AS Минимальная_цена,
    MAX(price) AS Максимальная_цена
FROM book
WHERE author <> 'Есенин С.А.'
GROUP BY author
HAVING SUM(amount) > 10;

Результат:

+------------------+------------------+-------------------+
| author           | Минимальная_цена | Максимальная_цена |
+------------------+------------------+-------------------+
| Достоевский Ф.М. | 460.00           | 799.01            |
+------------------+------------------+-------------------+

Другим способом решения примера является запрос:

SELECT author,
    MIN(price) AS Минимальная_цена,
    MAX(price) AS Максимальная_цена
FROM book
GROUP BY author
HAVING SUM(amount) > 10 AND author <> 'Есенин С.А.';

Не смотря на то что результат будет одинаковым, так делать не рекомендуется. «Потому что как написано - запрос сначала выбирает всех авторов, потом выводит данные, рассчитывая минимальное и максимальное значение цены для каждого, и только после всего убирает Есенина. Можно убрать Есенина в данном случае раньше и не использовать ресурсы базы для расчета его минимального и максимального значения, как это сделано в первом варианте. На небольшой базе быстродействия не ощутить, но если выполнять такое на продуктивной, то второй вариант значительно проигрывает...»

Задание

Посчитать стоимость всех экземпляров каждого автора без учета книг «Идиот» и «Белая гвардия». В результат включить только тех авторов, у которых суммарная стоимость книг (без учета книг «Идиот» и «Белая гвардия») более 5000 руб. Вычисляемый столбец назвать Стоимость. Результат отсортировать по убыванию стоимости.

select 
    author,
    SUM(price*amount) as Стоимость
from book
where title<>"Идиот" and title<>"Белая гвардия"
group by author
having Стоимость > 5000
order by author desc