Группировка и агрегаты.

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

Max() Возвращает максимальное значение из набора.

Min() Возвращает минимальное значение из набора.

Avg() Возвращает среднее значение набора.

Sum() Возвращает сумму значений из набора.

Count() Возвращает количество значений в наборе.

SELECT MAX(price),

MIN(price),

AVG(price),

SUM(price),

COUNT(*)

FROM realization;

Мало кого заинтересуют необработанные данные; тем, кто занимается анализом, потребуются обработанные данные, приведенные к виду, наиболее соответствующему их нуждам.

Необходимо понять, что такое группировка:

Группировка может быть по какому-либо столбцу. Например, группировка по ФИО будет выглядеть следующим образом:

1. Это самая простая группировка:

SELECT customer, SUM(count*price)

FROM realization

GROUP BY customer;

Этот запрос выявит все группировки по клиентам и посчитает итог по каждой группировке. В данном случае итог – это сумма вычисляемого столбца count*price.

Здесь добавлен новый блок GROUP BY.

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

SELECT customer, product, SUM(count*price)

FROM realization

GROUP BY customer, product;

3. Если мы хотим увидеть не только окончательные итоги, но и промежуточные итоги, то необходимо использовать предложение with rollup в блоке group by:

SELECT customer, product, SUM(count*price)

FROM realization

GROUP BY customer, product with rollup;

4. При группировке данных тоже можно применять условия фильтрации к данным после

формирования групп. Этот тип условий фильтрации должен располагаться в блоке having.

SELECT customer, SUM(count*price)

FROM realization

GROUP BY customer

HAVING SUM(count*price)>5000;

Фильтр WHERE воздействует на данные до группировки, а HAVING– после создания групп. В блок having можно включить агрегатные функции, не перечисленные в блоке select, в блок WHERE нельзя включать агрегатные функции.

Связи между таблицами.

До сих пор мы работали с одной таблицей. Теперь посмотрим, как получать данные из нескольких таблиц. Большинство запросов все же обращены к двум, трем или даже более таблицам.

Чтобы получить информацию из нескольких таблиц их необходимо соединить. Соединений бывает несколько:

1. Внутреннее соединение

2. Внешнее соединение

3. Левое соединение

4. Правое соединение

5. Кросс-соединение

Для иллюстрации этих соединений рассмотрим две таблицы:

Обратите внимание, что есть отдел маркетинга, в таблице Отделы, в этом отделе никто не работает и есть некто Лисин, который работает в отделе, который не существует.

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

SELECT *

FROM сотрудники INNER JOIN отделы

ON Сотрудники.ID_Отдел = Отделы.ID;

Левое соединение. Левой таблицей считается таблица, которая в запросе встречается первая, вторая таблица будет правой. При левом соединении мы получим все записи из левой таблицы, независимо от того есть ли соответствующая запись в правой таблице. Если соответствующей записи не будет, мы получим в результате NULL значение. Запрос будет выглядеть следующим образом:

SELECT *

FROM сотрудники LEFT JOIN отделы

ON Сотрудники.ID_Отдел = Отделы.ID;

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

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

SELECT *

FROM сотрудники RIGHT JOIN отделы

ON Сотрудники.ID_Отдел = Отделы.ID;

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

SELECT *

FROM сотрудники FULL OUTER JOIN отделы

ON Сотрудники.ID_Отдел = Отделы.ID;

Кросс объединение. Соответствует операции произведения в реляционной алгебре. Условие связи отсутствует и мы получим всевозможные соответствия между двумя таблицами.

SELECT *

FROM сотрудники CROSS JOIN отделы ;

Рассмотрим пример.

Схема будет следующая:

Запрос для схемы:

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- Schema Lessons

-- -----------------------------------------------------

-- -----------------------------------------------------

-- Schema Lessons

-- -----------------------------------------------------

CREATE SCHEMA IF NOT EXISTS `Lessons` DEFAULT CHARACTER SET utf8 ;

USE `Lessons` ;

-- -----------------------------------------------------

-- Table `Lessons`.`teachers`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Lessons`.`teachers` (

`idteacher` INT NOT NULL,

`FIO` VARCHAR(100) NULL,

`DATA` DATE NULL,

PRIMARY KEY (`idteacher`))

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `Lessons`.`courses`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Lessons`.`courses` (

`idcourses` INT NOT NULL,

`title` VARCHAR(100) NULL,

`hour_count` INT NULL,

PRIMARY KEY (`idcourses`))

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `Lessons`.`lesson`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Lessons`.`lesson` (

`idlesson` INT NOT NULL,

`data` DATE NULL,

`class` CHAR(3) NULL,

`teachers_idteacher` INT NOT NULL,

`courses_idcourses` INT NOT NULL,

PRIMARY KEY (`idlesson`),

INDEX `fk_lesson_teachers_idx` (`teachers_idteacher` ASC),

INDEX `fk_lesson_courses1_idx` (`courses_idcourses` ASC),

CONSTRAINT `fk_lesson_teachers`

FOREIGN KEY (`teachers_idteacher`)

REFERENCES `Lessons`.`teachers` (`idteacher`)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_lesson_courses1`

FOREIGN KEY (`courses_idcourses`)

REFERENCES `Lessons`.`courses` (`idcourses`)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Заполним таблицу данными. Запрос на внесение данных:

INSERT INTO `lessons`.`courses` (`idcourses`, `title`, `hour_count`) VALUES ('1', 'WEB-мастеринг. Основы серверного программирования.', '100');

INSERT INTO `lessons`.`courses` (`idcourses`, `title`, `hour_count`) VALUES ('2', 'Web-маркетинг', '106');

INSERT INTO `lessons`.`courses` (`idcourses`, `title`, `hour_count`) VALUES ('3', 'Разработка WEB-приложений ASR.net', '134');

INSERT INTO `lessons`.`courses` (`idcourses`, `title`, `hour_count`) VALUES ('4', 'Базы данных', '146');

INSERT INTO `lessons`.`courses` (`idcourses`, `title`, `hour_count`) VALUES ('5', 'Основы С++', '96');

INSERT INTO `lessons`.`courses` (`idcourses`, `title`, `hour_count`) VALUES ('6', 'Современные языки программирования', '86');

INSERT INTO `lessons`.`teachers` (`idteacher`, `FIO`, `DATA`) VALUES ('1', 'Иванов Иван Иванович', '1982-10-12');

INSERT INTO `lessons`.`teachers` (`idteacher`, `FIO`, `DATA`) VALUES ('2', 'Петров Петр Петрович', '1970-12-12');

INSERT INTO `lessons`.`teachers` (`idteacher`, `FIO`, `DATA`) VALUES ('3', 'Лосев Игорь Викторович', '1975-08-08');

INSERT INTO `lessons`.`teachers` (`idteacher`, `FIO`, `DATA`) VALUES ('4', 'Чуркин Петр Иванович', '1991-01-13');

INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('1', '2016-11-07', '301', '1', '1');

INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('2', '2016-11-07', '302', '2', '1');

INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('3', '2016-11-07', '303', '3', '2');

INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('4', '2016-11-08', '301', '1', '2');

INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('5', '2016-11-08', '302', '2', '1');

INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('6', '2016-11-08', '303', '3', '4');

INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('7', '2016-11-09', '301', '1', '1');

INSERT INTO `lessons`.`lesson` (`idlesson`, `data`, `class`, `teachers_idteacher`, `courses_idcourses`) VALUES ('8', '2016-11-09', '302', '2', '5');

Требуется получить:

1. Кто из преподавателей читал курс 30 сентября

SELECT teachers.FIO FROM teachers

INNER JOIN lesson ON teachers.idteacher = lesson.teachers_idteacher

WHERE lesson.data = '2016-11-07';

2. Кто из преподавателей читает курсы WEB

SELECT distinct teachers.FIO, courses.title FROM teachers

INNER JOIN lesson ON teachers.idteacher = lesson.teachers_idteacher

INNER JOIN courses ON courses.idcourses = lesson. courses_idcourses

WHERE courses.title LIKE '%web%';

3. Какие курсы читает преподаватель Иванов

SELECT distinct courses.title FROM courses

INNER JOIN lesson ON courses.idcourses = lesson. courses_idcourses

INNER JOIN teachers ON teachers.idteacher = lesson.teachers_idteacher

WHERE teachers.FIO = 'Иванов Иван Иванович';

4. Когда последний раз преподаватели вели занятия

SELECT teachers.FIO, max(lesson.Data) FROM teachers

INNER JOIN lesson ON teachers.idteacher = lesson.teachers_idteacher

GROUP BY teachers.FIO;

5. Если есть преподаватели, которые курсы еще не читали, то записи о них отсутствуют в таблице уроков. Но что делать, если нам они тоже нужны в результате? Тогда соединение в запросе будет не внутренним, а левым:

SELECT teachers.FIO, max(lesson.Data) FROM teachers

LEFT JOIN lesson ON teachers.idteacher = lesson.teachers_idteacher

GROUP BY teachers.FIO;