Основы SQL. Отношение (таблица)

1.

1
ОТНОШЕНИЕ (ТАБЛИЦА)

2.

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

3.

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

4.

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

5.

Пример отношения:
атрибут – это столбец в таблице (более
распространенный термин – поле ), в примере –
Табельный номер, Фамилия И.О., Телефон,
Должность)
размерность – это число атрибутов в таблице, в
данном случае – 4; размерность отношения должна
быть больше 0, порядок следования атрибутов
существенен;
домен атрибута – это допустимые значения
(неповторяющиеся), которые можно занести в поле ,
например для атрибута Должность домен –
{инженер, программист}.
5

6.

Задание
Для таблицы Сотрудник отметьте верные ячейки
(В каждом столбце и строке - один правильный ответ).
Домен
Атрибут
Мощность
Кортеж
Размер Отноность шение
4
002, Иванов И.И.,
234- 12-01, инженер
{###-##-##},
где # - цифра от 0 до 9
Фамилия И.О.
3
Сотрудник
6

7.

Реляционная модель
Перед созданием таблицы в базе данных
необходимо описать ее структуру. Для этого
выполняется
следующая
последовательность
шагов:.
Дать таблице имя,
Правила для выбора имен таблиц:
может включать английские буквы, цифры и
знак подчеркивания, должно начинаться с буквы;
имя должно быть уникальным в пределах базы
данных.
7

8.

Реляционная модель
Определить
структуру
таблицы,
из
каких
атрибутов(столбцов, полей) она будет состоять
Правила по выбору имени поля информационного
объекта:
может включать английские буквы, цифры и знак
подчеркивания, должно начинаться с буквы;
имя поля должно быть уникальным в пределах
таблицы.
Рекомендации по выбору имени поля
информационного объекта:
имя должно быть понятным и соответствовать тем
данным, которые хранятся в поле;
имя может состоять из нескольких слов, тогда слова
разделяются подчеркиванием, после подчеркивания
8
слово пишется с маленькой буквы.
o

9.

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

10.

Пример:
Пусть таблица будет называться book
Она будет состоять атрибутов(столбцов, полей):
title – поле для хранения названия книги;
author – поле с фамилией автора книги ;
priсe – цена книги;
amount – количество книг.
Включим ключевое поле book_id
10

11.

Выбор типов данных для полей
После описания структуры таблицы необходимо
выбрать типы данных для каждого поля.
Основные типы данных SQL:
Тип данных Описание
Пример
INT
Целое число, могут принимать значения -567
от -2 147 483 648 до 2 147 483 647
INTEGER
1205
DECIMAL
NUMERIC
Вещественное число,
в скобках указывается максимальная
длина числа (включает символы слева и
справа от десятичной запятой)
и количество знаков после запятой.
Можно использовать оба этих типа, они
эквивалентны, принимают значения в
диапазоне -1038+1 до 1038-1.
DECIMAL(4,1)
NUMERIC(6,3)
34.6
-3.294
11

12.

Тип данных Описание
Пример
Дата в формате ГГГГ-ММ-ДД
26 июля 2020 года
DATE
2020-07-26
3 января 2021 года
2021-01-03
Строка длиной не более 255
символов,
в скобках указывается
VARCHAR
максимальная
длина строки, которая может
пример
храниться в поле
описание
VARCHAR(10)
Примечание:
Для описания ключевого поля используйте описание
INTEGER AUTO_INCREMENT PRIMARY KEY
Это значит, что в поле будут заноситься различные
целые числа, при этом они будут автоматически
12
генерироваться (каждая следующая строка будет
иметь значение ключа на 1 больше предыдущего).

13.

Пример:
Определим тип данных для каждого поля
таблицы book:
book_id
title
author
priсe
amount
VARCHAR(30)
DECIMAL(8,2)
INT
VARCHAR(50)
INTEGER AUTO_INCREMENT PRIMARY KEY
13

14.

Задание
Значения
Слово
Предложение
Абзац
sql
mySql
data
5.12
-0.99
10.12
Сопоставьте значения и типы
данных, с помощью которых их
можно описать.
Тип данных
INT
DECIMAL(5,2)
VARCHAR(11)
DECIMAL(7,1)
VARCHAR(5)
-34078
55
0
56.1
456.1
3000
14

15.

15
ЗАПРОСЫ НА СОЗДАНИЕ

16.

Создание базы данных
Для создания базы данных используется SQLзапрос, содержащий команду CREATE DATABASE
<DATABASENAME>;.
Замените <DATABASENAME> названием вашей
базы данных. Оно не может содержать пробелы.
Запрос:
CREATE DATABASE us_states;
16

17.

Выбор базы данных
Когда база данных создана, нужно ее выбрать,
чтобы
начать
редактирование.
Введите
команду
USE
us_states;.
Вы
увидите
сообщение Database changed, которое уведомляет,
что
сейчас
активной
базой
данных
является us_states.
17

18.

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

19.

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

20.

Пример:
Создадим таблицу genre следующей структуры:
Поле
genre_id
name_genre
Тип, описание
INTEGER AUTO_INCREMENT PRIMARY KEY
VARCHAR(30)
Запрос:
CREATE TABLE genre(
genre_id INTEGER AUTO_INCREMENT PRIMARY KEY,
name_genre VARCHAR(30)
);
!
Созданная таблица - пустая.
20

21.

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

22.

Вставка записи в таблицу
Для занесения новой записи в таблицу
используется SQL запрос, в котором указывается в
какую таблицу, в какие поля заносить новые
значения.
Пример.
В таблицу, состоящую из двух столбцов добавим
новую строку, при этом в поле1 заносится
значение1, в поле2 - значение2.
INSERT INTO таблица(поле1, поле2)
VALUES (значение1, значение2);
!
В результате выполнения запроса новая запись
заносится в конец обновляемой таблицы.
22

23.

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

24.

Вставка записи в таблицу
При составлении списка полей и списка значений
необходимо учитывать следующее:
количество полей и количество значений в списках должны
совпадать;
должно существовать прямое соответствие между позицией
одного и того же элемента в обоих списках, поэтому первый
элемент списка значений должен относиться к первому
столбцу в списке столбцов, второй – ко второму столбцу и т.д.;
типы данных элементов в списке значений должны быть
совместимы с типами данных соответствующих столбцов
таблицы ( целое число можно занести в поле типа DECIMAL,
обратная операция - недопустима);
новые значения нельзя добавлять в поля, описанные
как PRIMARY KEY AUTO_INCREMENT;
24

25.

Пример 1:
Вставим новую запись в таблицу genre:
genre_id
name_genre
INTEGER AUTO_INCREMENT PRIMARY KEY
VARCHAR(30)
1
Роман
Запрос:
INSERT INTO genre (name_genre)
VALUES ('Роман');
25

26.

Пример 2:
Вставим новую запись в таблицу book:
book_id
title
author
price
amount
INTEGER
AUTO_INCREMENT
PRIMARY KEY
VARCHAR(50)
VARCHAR(30)
DECIMAL(8,2)
INT
1
Мастер и
Маргарита
Булгаков М.А.670.99
3
Запрос:
INSERT INTO book (title, author, price, amount )
VALUES ('Мастер и Маргарита', 'Булгаков М.А.', 670.99, 3);
26

27.

Пример 3:
Вставим новые записи в таблицу book:
book_id
title
author
price
INTEGER
AUTO_INCREMENT
PRIMARY KEY
VARCHAR(50)
VARCHAR(30)
DECIMAL(8,2) INT
Мастер и
Булгаков М.А.
Маргарита
Белая гвардия Булгаков М.А.
Достоевский
Идиот
Ф.М.
1
2
3
amount
670.99
3
540.50
5
460.00
10
Запрос:
INSERT INTO book (title, author, price, amount )
VALUES ('Белая гвардия', 'Булгаков М.А.', 540.50, 5),
('Идиот', 'Достоевский Ф.М.', 460.00, 10),
('Братья Карамазовы', 'Достоевский Ф.М.', 799.01, 2)
27

28.

28
ОПЕРАТОРЫ SQL ДЛЯ
ПРОСМОТРА И УДАЛЕНИЯ

29.

Операторы для просмотра
Есть возможность посмотреть, какие БД существуют, какие
таблицы в них присутствуют, и какие столбцы эти таблицы
содержат. Для этого в SQL существует несколько операторов:
show databases — показать все имеющиеся БД,
show tables — показать список таблиц текущей БД
(предварительно
ее
надо
выбрать
с
помощью
оператора use),
describe имя_таблицы
столбцов указанной таблицы.
—
показать
описание
29

30.

Оператор для удаления
Оператор — drop позволяет удалять таблицы и БД.
Чтобы удалить таблицу, нужно написать:
drop table имя_таблицы;
(предварительно нужно выбрать БД для работы)
Пример:
drop table topics;
/* будет удалена таблица topics;
*/
Чтобы удалить БД нужно написать:
drop database имя_базы данных;
Пример:
drop database forum;
/* будет удалена БД форум;
*/
30

31.

31
ЗАПРОСЫ НА ВЫБОРКУ

32.

Выборка всех данных из таблицы
Для того чтобы отобрать все данные из таблицы
используется SQL запрос следующей структуры:
ключевое слово SELECT;
символ « *» ;
ключевое слово FROM;
имя таблицы.
SQL запрос, который выберет все записи из
таблицы genre :
Запрос:
SELECT * FROM genre;
32

33.

Выборка отдельных столбцов
Для того чтобы отобрать данные из определенных
столбцов таблицы используется SQL запрос
следующей структуры:
• ключевое слово SELECT ;
• список столбцов таблицы через запятую;
• ключевое слово FROM ;
• имя таблицы.
Выбрать названия книг и их количества из
таблицы book .
Запрос:
SELECT title, amount FROM book;
33

34.

Выборка столбцов и присвоение им
новых имен
Для
того
чтобы
отобрать
данные
из
определенных столбцов таблицы и одновременно
задать столбцам новые имена используется SQL
запрос следующей структуры:
ключевое слово SELECT ;
имя столбца;
ключевое слово AS ;
новое название столбца (можно русскими
буквами);
запятая;
имя столбца;
....
34
ключевое слово FROM ;
имя таблицы.

35.

Выборка столбцов и присвоение им
новых имен
Например:
Выбрать все названия книг и их количества из
таблицы book, для столбца title задать новое
имя Название.
Запрос:
SELECT title AS Название, amount
FROM book;
35

36.

Выборка данных с созданием
вычисляемого столбца
С помощью SQL запросов можно осуществлять
вычисления по каждой строке таблицы с
помощью вычисляемого столбца. Для него в
списке полей после оператора SELECT
указывается выражение и задается имя.
Выражение может включать имена столбцов,
константы, знаки операций, встроенные
функции.
Результатом является таблица, в которую
включены все данные из указанных
после SELECT столбцов, а также новый столбец,
в каждой строке которого вычисляется заданное
выражение.
36

37.

Выборка данных с созданием
вычисляемого столбца
Например:
Вывести всю информацию о книгах, а также для
каждой
позиции
посчитать
ее
стоимость
(произведение цены на количество). Вычисляемому
столбцу дать имя total .
Запрос:
SELECT title, author, price, amount,
price * amount AS total
FROM book;
37

38.

Выборка данных, вычисляемые
столбцы, математические функции
В SQL реализовано множество математических
функций для работы с числовыми данными:
Функция
Описание
Пример
возвращает наименьшее
целое число, большее
или равное x
CEILING(4.2)=5
CEILING(x)
(округляет до целого
CEILING(-5.8)=-5
числа в большую
сторону)
округляет
значение x до k знаков ROUND(4.361)=4
ROUND(x, k) после запятой,
ROUND(5.86592,1)=
если k не указано –
5.9
38
x округляется до целого

39.

Выборка данных, вычисляемые
столбцы, математические функции
Функция
Описание
Пример
FLOOR(x)
возвращает наибольшее
целое число, меньшее или
FLOOR(4.2)=4
равное x
FLOOR(-5.8)=-6
(округляет до целого числа
в меньшую сторону)
POWER(x, y)
возведение x в степень y
POWER(3,4)=81.0
SQRT(x)
квадратный корень из x
SQRT(4)=2.0
SQRT(2)=1.41...
DEGREES(x)
конвертирует значение x из DEGREES(3) =
радиан в градусы
171.8...
39

40.

Выборка данных, вычисляемые
столбцы, математические функции
Функция
Описание
RADIANS(x)
конвертирует значение x из
RADIANS(180)=3.14...
градусов в радианы
ABS(x)
модуль числа x
PI()
pi = 3.1415926...
Пример
ABS(-1) = 1
ABS(1) = 1
40

41.

Выборка данных, вычисляемые
столбцы, математические функции
Для каждой книги из таблицы book вычислим
налог на добавленную стоимость (имя столбца tax),
который включен в цену и составляет k = 18%, а
также цену книги (price_tax) без него. Формулы
для вычисления:
Запрос:
SELECT title, price,
(price*18/100)/(1+18/100) AS tax,
price/(1+18/100) AS price_tax
FROM book;
41

42.

Выборка данных, вычисляемые
столбцы, математические функции
Запрос:
SELECT title, price,
(price*18/100)/(1+18/100) AS tax,
price/(1+18/100) AS price_tax
FROM book;
? Что плохо?
42

43.

Выборка данных, вычисляемые
столбцы, математические функции
Запрос:
SELECT title, price,
ROUND((price*18/100)/(1+18/100),2) AS tax,
ROUND(price/(1+18/100),2) AS price_tax
FROM book;
43

44.

Выборка данных, вычисляемые
столбцы, логические функции
В SQL реализована возможность заносить в поле
значение в зависимости от условия. Для этого
используется функция IF:
IF(логическое_выражение, выражение_1,
выражение_2)
Все три параметра IF() являются обязательными.
Допускается использование вложенных функций,
вместо выражения_1 или выражения_2 может
стоять новая функция IF.
44

45.

Выборка данных, вычисляемые
столбцы, логические функции
Пример
Для каждой книги из таблицы book установим
скидку следующим образом: если количество книг
меньше 4, то скидка будет составлять 50% от цены,
в противном случае 30%. Цена по скидке должна
отображаться с двумя знаками после запятой
Запрос:
SELECT title, amount, price,
ROUND (IF(amount<4, price*0.5, price*0.7),2) AS sale
FROM book;
45

46.

Выборка данных, вычисляемые
столбцы, логические функции
Запрос:
SELECT title, amount, price,
ROUND (IF(amount<4, price*0.5, price*0.7),2) AS sale
FROM book;
46

47.

Выборка данных, вычисляемые
столбцы, логические функции
Пример
Усложнить вычисление скидки в зависимости от
количества книг. Если количество книг меньше 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;
47

48.

Выборка данных, вычисляемые
столбцы, логические функции
Запрос:
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;
48

49.

Выборка данных по условию
С помощью запросов можно включать в итоговую
выборку не все строки исходной таблицы, а только те,
которые отвечают некоторому условию. Для этого после
указания таблицы, откуда выбираются данные,
задается ключевое слово WHERE и логическое
выражение, от результата которого зависит будет ли
включена строка в выборку или нет. Если условие –
истина, то строка(запись) включается в выборку, если
ложь – нет.
Логическое выражение может включать операторы
сравнения (равно «=», не равно «<>», больше «>»,
меньше «<», больше или равно«>=», меньше или равно
«<=»).
49

50.

Выборка данных по условию
Пример
Вывести название и цену тех книг, цены которых
меньше 600 рублей.
Запрос:
SELECT title, price
FROM book
WHERE price < 600;
50

51.

Выборка данных, логические операции
Логическое
выражение
после
ключевого
слова WHERE кроме операторов сравнения
и
выражений
может
включать
логические
операции (И «and», ИЛИ «or», НЕ «not») и круглые
скобки,
изменяющие
приоритеты
выполнения
операций.
Приоритеты операций:
круглые скобки
умножение (*), деление (/)
сложение (+), вычитание (-)
операторы сравнения (=, >, <, >=, <=, <>)
NOT
AND
51
OR

52.

Выборка данных, логические операции
Пример
Вывести название, автора и цену тех книг, которые написал
Булгаков, ценой больше 600 рублей
Запрос:
SELECT title, author, price
FROM book
WHERE price > 600 AND author = 'Булгаков М.А.';
Пример
Вывести название, цену тех книг, которые написал Булгаков
или Есенин, ценой больше 600 рублей
Запрос:
SELECT title, author, price
FROM book
WHERE (author = 'Булгаков М.А.' OR author = 'Есенин С.А.')
52
AND price > 600;

53.

Выборка данных, операторы BETWEEN, IN
Логическое выражение после ключевого
слова WHERE может включать
операторы BETWEEN и IN. Приоритет у этих операторов
такой же как у операторов сравнения, то есть они
выполняются раньше, чем NOT, AND, OR.
Оператор BETWEEN позволяет отобрать данные,
относящиеся к некоторому интервалу, включая его
границы.
Пример
Выбрать названия и количества тех книг, количество
которых от 5 до 14 включительно.
Запрос:
SELECT title, amount
FROM book
WHERE amount BETWEEN 5 AND 14;
53

54.

Выборка данных, операторы BETWEEN, IN
Оператор
IN
позволяет выбрать
соответствующие значениям из списка.
данные,
Пример
Выбрать названия и цены книг, написанных
Булгаковым или Достоевским.
Запрос:
SELECT title, price
FROM book
WHERE author IN ('Булгаков М.А.', 'Достоевский Ф.М.');
54

55.

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

56.

Выборка данных с сортировкой
Логический порядок операций для запроса SQL на выборку данных
с сортировкой следующий:
FROM
WHERE
SELECT
ORDER BY
Поскольку сортировка выполняется позже SELECT, для
указания столбцов, по которым выполняется сортировка,
можно использовать имена, присвоенные им
после SELECT, а также порядковый номер столбца в
перечислении.
56

57.

Выборка данных с сортировкой
Пример
Вывести автора, название и количество книг, в
отсортированном в алфавитном порядке по автору и по
убыванию количества, для тех книг, цены которых
меньше 750 рублей.
Запрос:
SELECT author, title, amount AS Количество
FROM book
WHERE price < 750
ORDER BY author, amount DESC;
или
SELECT author, title, amount AS Количество
FROM book
WHERE price < 750
ORDER BY 1, 3 DESC;
57

58.

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

59.

Выборка данных, оператор LIKE
Символ -
шаблон
Описание
%
SELECT * FROM book WHERE
Любая строка,
author LIKE '%М.%'
содержащая
выполняет поиск и выдает все
ноль или более
книги, инициалы авторов
символов
которых содержат «М.»
_
Любой
(подчерки- одиночный
символ
вание)
Пример
SELECT * FROM book WHERE
title LIKE 'Поэм_'
выполняет поиск и выдает все
книги, названия которых либо
«Поэма», либо «Поэмы» и пр.
59

60.

Выбор уникальных элементов столбца
1 способ:
Чтобы отобрать уникальные элементы некоторого
столбца используется ключевое слово DISTINCT,
которое размещается сразу после SELECT
Пример
Выбрать различных авторов, книги которых хранятся в
таблице book.
Запрос:
SELECT DISTINCT author
FROM book;
60

61.

Выбор уникальных элементов столбца
2 способ:
Использование оператора GROUP BY, который
группирует данные при выборке, имеющие одинаковые
значения в некотором столбце.
Столбец, по которому осуществляется группировка,
указывается после GROUP BY .
Пример
Выбрать различных авторов, книги которых хранятся в
таблице book.
Запрос:
SELECT author
FROM book
GROUP BY author;
61

62.

62
ЗАПРОСЫ
ГРУППОВЫЕ ОПЕРАЦИИ

63.

Выборка данных, групповые функции
SUM и COUNT
При
группировке
над
элементами
столбца,
входящими в группу можно выполнить различные
действия, например, просуммировать их или найти
количество элементов в группе.
Пример
Рассмотрим, как осуществляется группировка данных по
некоторому столбцу и вычисления над группой на
следующем примере:
Запрос:
SELECT author, sum(amount), count(amount)
FROM book
GROUP BY author;
63

64.

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

65.

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

66.

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

67.

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

68.

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

69.

Выборка данных, функция COUNT
(группа содержит пустые значения)
Пример
Посчитать, сколько различных книг каждого автора
хранится на складе.
Запрос:
SELECT author, COUNT(author), COUNT(amount),
COUNT(*)
FROM book
GROUP BY author;
! Если столбец указан в SELECT БЕЗ применения
групповой функции, то он обязательно должен быть
указан и в GROUP BY. Иначе получим ошибку.
69

70.

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

71.

Групповые функции MIN, MAX и AVG
MIN(), MAX() и AVG() вычисляют минимальное,
максимальное и среднее значение элементов столбца,
относящихся к группе.
Пример
Вывести минимальную цену книги каждого автора
Запрос:
SELECT author, MIN(price) AS min_price
FROM book
GROUP BY author;
71

72.

Выборка данных c вычислением,
групповые функции
В качестве аргумента групповых функций
SQL
может использоваться не только столбец, но и любое
допустимое в SQL арифметическое выражение.
Пример
Вывести суммарную стоимость книг каждого автора.
Запрос:
SELECT author, SUM(price * amount) AS Стоимость
FROM book
GROUP BY author;
72

73.

Выборка данных c вычислением,
групповые функции
Пример
Найти среднюю цену книг каждого автора, округлить до
сотых . Назвать поле Средняя цена.
Запрос:
SELECT author,
ROUND(AVG(price),2) AS Средняя_цена
FROM book
GROUP BY author;
73

74.

Вычисления по таблице целиком
Групповые функции позволяют вычислять итоговые
значения по всей таблице.
Пример
Посчитать количество экземпляров книг на складе.
Запрос:
SELECT SUM(amount) AS Количество
FROM book;
! Результатом таких запросов
является единственная
строка с вычисленными по
таблице значениями.
74

75.

Выборка данных по условию, групповые
функции
В запросах с групповыми функциями
вместо WHERE используется ключевое слово HAVING ,
которое размещается после оператора GROUP BY.
Пример
Найти минимальную и максимальную цену книг всех
авторов, общая стоимость книг которых больше 5000.
Запрос:
SELECT author,
MIN(price) AS Минимальная_цена,
MAX(price) AS Максимальная_цена
FROM book
GROUP BY author
HAVING SUM(price * amount) > 5000;
75

76.

Выборка данных по условию, групповые
функции, WHERE и HAVING
WHERE и HAVING могут использоваться в одном
запросе.
Пример
Вывести максимальную и минимальную цену книг
каждого автора, кроме Есенина, количество
экземпляров книг которого больше 10.
SELECT author,
MIN(price) AS Минимальная_цена,
MAX(price) AS Максимальная_цена
FROM book
WHERE author <> 'Есенин С.А.'
GROUP BY author
HAVING SUM(amount) > 10;
76

77.

77
ВЛОЖЕННЫЕ ЗАПРОСЫ

78.

Вложенный запрос, возвращающий одно
значение
Вложенный запрос, возвращающий одно значение,
может
использоваться
в
условии
отбора
записей WHERE как обычное значение совместно с
операциями =, <>, >=, <=, >, <.
Пример
Вывести информацию о самых дешевых книгах,
хранящихся на складе.
SELECT title, author, price, amount
FROM book
WHERE price = (
SELECT MIN(price)
FROM book
);
78

79.

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

80.

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

81.

Вложенный запрос, оператор IN
Пример
Вывести информацию о книгах тех авторов, общее
количество экземпляров книг которых не менее 12.
SELECT title, author, amount, price
FROM book
WHERE author IN ( SELECT author
FROM book
GROUP BY author
HAVING SUM(amount) >= 12 );
81

82.

Вложенный запрос, операторы ANY и ALL
Вложенный запрос, возвращающий несколько
значений одного столбца, можно использовать для
отбора
записей
с
помощью
операторов ANY и ALL совместно с операциями
отношения (=, <>, <=, >=, <, >).
Операторы ANY и ALL используются в SQL для
сравнения некоторого значения с результирующим
набором вложенного запроса, состоящим из одного
столбца.
!
Операторы ALL и ANY можно использовать
только с вложенными запросами.
82

83.

Вложенный запрос, операторы ANY и ALL
Пример
Вывести информацию о тех книгах, количество которых
меньше самого маленького среднего количества книг
каждого автора.
SELECT title, author, amount, price
FROM book
WHERE amount < ALL ( SELECT AVG(amount)
FROM book
GROUP BY author
);
83

84.

Вложенный запрос, операторы ANY и ALL
Пример
Вывести информацию о тех книгах, количество которых
меньше самого большого среднего количества книг
каждого автора.
SELECT title, author, amount, price
FROM book
WHERE amount < ANY ( SELECT AVG(amount)
FROM book
GROUP BY author
);
84

85.

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

86.

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

87.

Вложенный запрос после SELECT
Пример
Вывести информацию о книгах, количество экземпляров
которых отличается от среднего количества экземпляров
книг на складе более чем на 3, а также указать среднее
значение количества экземпляров книг.
? Что плохо?
? Какая функция позволяет округлить «вниз»?
87

88.

Вложенный запрос после SELECT
SELECT title, author, amount,
FLOOR((SELECT AVG(amount)
FROM book))
AS Среднее_количество
FROM book
WHERE abs(amount - (SELECT AVG(amount)
FROM book)) >3;
88

89.

89