Выборка с упорядочением

Если в результирующей выборке необходимо упорядочить значения по одному или нескольким полям, используется ключевое слово ORDER BY, после которого указывается список полей через запятую, по которым необходимо отсортировать результирующий набор данных.

Задание 4. Выбрать все книги Дюма А. и отсортировать по названиям.

SELECT * FROM Books WHERE Avtor=”Дюма А” ORDER BY Nazv;

SELECT * FROM Books WHERE Avtor NOT IN(“Л.Н.Толстой”; “А.П.Чехов”);

Задание 5. Выбрать все книги, изданные за период с 2000 по 2005 годы и отсортировать по авторам и названиям.

SELECT * FROM Books WHERE God_izd BETWEEN 2000 AND 2005 ORDER BY Avtor, Nazv;

Задание 5

Напишите аналогичные запросы к таблицам базы данных, выбранной в соответствии с вашим вариантом.

Контрольные вопросы

1. Какие операции сравнения применяются в запросах?

2. Какие операторы применяются в запросах? Как они работают?

3. Как упорядочить записи в запросе?

4. Как осуществляется упорядочивание данных в запросах?

Практическая работа № 10

Тема: «Построение запросов на выборку с использованием группировки записей».

Цель работы: Научиться писать запросы на выборку с использованием групповых операций.

Время выполнения: подготовка: 5 мин; выполнение: 210 мин; проверка: 10 мин; всего: 225 мин.

Указания к выполнению лабораторной работы

Группировка записей

Итоговые запросы – это запросы, которые позволяют подводить итоги по таблицам. Например:

1. посчитать количество сотрудников в организации и фонд заработной платы;

2. посчитать количество сотрудников и среднюю заработную плату по всем отделам организации;

3. посчитать количество сделок по каждому клиенту.

Итоги подводятся либо по всей таблице, либо по группам.

Для лучшего понимания итоговых запросов введем следующие определения:

1. Поле (поля) группирования – это одно поле или несколько полей, по которым данные объединяются в группы для подведения итогов. Например, в случае первого примера полей группирования не будет, т.к. итоги подводятся по всей таблице. В случае второго примера полем группирования будет поле НомерОтдела. В случае третьего примера полем группирования будет НомерКлиента.

2. Итоговое поле – это, как правило, числовое поле, по которому подводится итог.

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

Агрегатные функции

Обозначение	Описание
AVG ( [DISTINCT] expr)	Возвращает среднее значение аргумента expr. В качестве аргумента обычно выступает имя столбца. Необязательное слово distinct позволяет обрабатывать только уникальные значения столбца expr
COUNT ( )	Подсчитывает число записей и имеет несколько форм. Форма COUNT (выражение) возвращает число записей в таблице, поле выражение для которых не равно null. Форма count(*) возвращает общее число строк в таблице независимо от того, принимает какое-либо поле значение null или нет. Форма COUNT (DISTINCT выражение1, выражение2, ... ) позволяет использовать ключевое слово distinct, которое позволяет подсчитать только уникальные значения столбца
MIN ( [DISTINCT]expr)	Возвращает минимальное значение среди всех непустых значений выбранных строк в столбце expr. Необязательное слово distinct позволяет обрабатывать только уникальные значения столбца expr
MAX ( [DISTINCT] expr)	Возвращает максимальное значение среди всех непустых значений выбранных строк в столбце expr. Необязательное слово distinct позволяет обрабатывать только уникальные значения столбца expr
SUM ( [DISTINCT] expr)	Возвращает сумму величин в столбце expr. Необязательное слово distinct позволяет обрабатывать только уникальные значения столбца expr

Итоговые запросы выполняются с помощью оператора SELECT.

Синтаксис оператора в этом случае имеет следующий вид:

SELECT <СписокЭлементов> FROM <СписокТаблиц >

GROUP BY (<ПолеГруппир1, ПолеГруппир2,. . . > )

HAVING <УслНаГруппы >;

Где:

СписокЭлементов это список элементов через запятую, который может включать только следующие элементы:

1. имена полей группирования, которые должны присутствовать во фразе GROUP BY;

2. итоговые функции;

3. константы;

4. выражения из вышеперечисленных элементов.

СписокТаблиц это список таблиц, для которых выполняется операция подведения итогов.

ПолеГруппир1, ПолеГруппир2 – это поле(поля) группирования.

УслНаГруппы – условие, накладываемое на группы.

Фраза GROUP BY задает поле или поля группирования.

Фраза HAVING – задает условие, накладываемое на группы.

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

Например, даны две таблицы:

Отделы(*НомОтдела, НазваниеОтдела)

Сотрудники(*НомСотр, ФИО, Зарплата, НомОтдела)

где первичные ключи отмечены *(звездочками).

Требуется выполнить следующие запросы:

Запрос 1. Посчитать количество сотрудников в организации и общий фонд заработной платы по всей организации.

Решение. В этом случае итог подводится по всей таблице, поэтому фраза GROUP BY отсутствует. Запрос будет выглядеть так:

SELECT COUNT(*), SUM(Зарплата) FROM Сотрудники;

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

НомОтдела

Кол-во сотрудников

Средняя Зарплата

Решение. В данном случае итоги подводятся по таблице «Сотрудники». Требуется выполнить группирование данных по полю НомОтдела и подвести итоги по каждой группе. Запрос будет выглядеть так:

SELECT НомОтдела AS “Номер отдела”, COUNT(*) AS “Кол-во сотрудников”, AVG(Зарплата) AS “Средняя зарплата”

FROM Сотрудники

GROUP BY (НомОтдела);

Запрос 3. Изменим предыдущий запрос следующим образом.

Добавим в результирующий набор еще одно поле НазваниеОтдела.

Таким образом, требуется получить следующий результирующий набор:

НомОтдела

Название Отдела

Кол-во Сотрудников

Средняя Зарплата

Решение. Поле НазваниеОтдела находится в другой таблице. Поэтому требуется выполнить соединение таблиц по условию и над полученным соединением выполнить подведение итогов. Группировка будет выполняться по двум полям НомОтдела и

НазваниеОтдела и эти же поля указываются в списке оператора.

SELECT НомОтдела, НазваниеОтдела, COUNT(*), AVG(Зарплата)

FROM Сотрудники С INNER JOIN Отделы О ON С. НомОтдела = О. НомОтдела

GROUP BY (НомОтдела, НазваниеОтдела) ;

Запрос 4. Изменим предыдущий запрос следующим образом: пусть требуется выбрать bнформацию только о тех отделах, в которых более 10 сотрудников.

Решение. В данном запросе следует использовать фразу HAVING, которая позволяет использовать агрегатные функции в условии, которое накладывается на группы. Запрос будет выглядеть так:

SELECT НомОтдела, НазваниеОтдела,

COUNT(*), AVG(Зарплата)

FROM Сотрудники С INNER JOIN Отделы О ON С. НомОтдела = О. НомОтдела

GROUP BY (НомОтдела, НазваниеОтдела)

HAVING (COUNT(*)>10);

Использование ключевого слова distinct с функцией count( ) позволяет вернуть число уникальных значений НомОтдела в таблице Сотрудники, например:

SELECT COUNT(DISTINCT НомОтдела) FROM Сотрудники;

Задание 1. Найти количество сделок (поле order_ID) из таблицы Список заказов базы данных (orders) book из практической работы №5.

SELECT COUNT(order_ID) AS total FROM orders;

Задание 2. Извлечь из таблицы catalogs записи с максимальным значением поля cat_ID:

SELECT * FROM catalogs ORDER BY cat_ID DESC LIMIT 1;

Задание 3. Извлечь уникальные записи поля b_cat из таблицы books:

SELECT b_cat_ID FROM books

GROUP BY b_cat_ID

ORDER BY b_cat_ID;

При использовании group by возможно использование условия where:

Задание 4. Извлечь уникальные значения столбца b_cat_ID, большие двух из таблицы books.

SELECT b_cat_ID, COUNT(b_cat_ID) FROM books

WHEE b_cat_ID > 2

GROUP BY b_cat_ID

ORDER BY b_cat_ID;

Задание 5. Извлечь уникальные значения столбца b_cat_ID, имеющие значения большие 5.

SELECT b_cat_ID, COUNT(b_cat_ID) AS total FROM books

GROUP BY b_cat_ID

HAVING total > 5

ORDER BY b_cat_ID;

Задание 6. Создадать простой запрос на выборку к таблице books, который выводит максимальную и минимальную цены товарных позиций, присваивая им соответственно псевдонимы maximum и minimum:

SELECT MAX(b_price) AS maximum, MIN(b_price) AS minimum

FROM books;

Задание 7. Создадим простой запрос на выборку к таблице books, который выводит количество записей, соответствующих каждому из уникальных значений b_cat_ID. Для этого используем функцию count( ) вместе с выражением group by:

SELECT b_cat, COUNT(b_cat_ID) FROM books

GROUP BY b_cat_ID

ORDER BY b_cat_ID;

Задание 1. Выполните все рассмотренные выше запросы в СУБД MySQL.

Задание 2. Создайте запросы с группировкой для своего варианта базы данных.