Презентация Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6) онлайн
На нашем сайте вы можете скачать и просмотреть онлайн доклад-презентацию на тему Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6) абсолютно бесплатно. Урок-презентация на эту тему содержит всего 33 слайда. Все материалы созданы в программе PowerPoint и имеют формат ppt или же pptx. Материалы и темы для презентаций взяты из открытых источников и загружены их авторами, за качество и достоверность информации в них администрация сайта не отвечает, все права принадлежат их создателям. Если вы нашли то, что искали, отблагодарите авторов - поделитесь ссылкой в социальных сетях, а наш сайт добавьте в закладки.
Оцените презентацию от 1 до 5 баллов!
- Тип файла:ppt / pptx (powerpoint)
- Всего слайдов:33 слайда
- Для класса:1,2,3,4,5,6,7,8,9,10,11
- Размер файла:174.86 kB
- Просмотров:89
- Скачиваний:0
- Автор:неизвестен
Слайды и текст к этой презентации:
№1 слайд
Содержание слайда: Introduction to Databases and SQL
Лекция 6
№2 слайд
Содержание слайда: Темы занятия
Подзапросы
Группировка при выборке
Агрегатные функции
Фильтрация групп
Представления (Views)
№3 слайд
Содержание слайда: Исходные таблицы
№4 слайд
Содержание слайда: Подзапросы
Подзапрос (subquery) – инструкция выборки, которая содержится внутри другой инструкции выборки.
Обычно подзапросы применяют в WHERE-части.
*) подзапрос может содержаться в инструкциях вставки, обновления или удаления данных – но об этом позже.
№5 слайд
Содержание слайда: Что может возвращать подзапрос?
Произвольная выборка (несколько строк и колонок). Операция EXISTS (NOT EXISTS) может проверить, что выборка не пустая (или пустая).
Набор однотипных значений. Удобно использовать в сочетании с операций IN (NOT IN).
Одно значение (или ничего). Это значение можно использовать в операциях сравнения.
№6 слайд
Содержание слайда: Подзапросы в WHERE – пример 1
Выведем сотрудников с зарплатой < 1400. Подзапрос возвращает из Salaries набор чисел (PersonID). Мы используем этот набор как основу для операции IN:
SELECT FirstName, LastName FROM Persons
WHERE ID IN (SELECT PersonID FROM Salaries
WHERE Salary < 1400)
*) эту задачу можно решить, используя JOIN.
№7 слайд
Содержание слайда: Подзапросы в WHERE – пример 2
SELECT FirstName, LastName FROM Persons
WHERE Department=(SELECT ID FROM Departments
WHERE Name='Quality assurance')
Важно: если подзапрос вернёт более одного значения, то будет ошибка при выполнении запроса. Если вернёт ровно одно значение – всё ОК. Если ничего не вернёт (то же самое, что NULL) – тоже всё ОК, ибо любая операция сравнения с NULL возможна, но всегда равна FALSE.
№8 слайд
Содержание слайда: Связанный подзапрос
Связанный подзапрос (correlated subquery) использует в работе информацию внешнего запроса.
Например, проверяет своё условие WHERE, используя колонки таблицы из внешнего запроса.
№9 слайд
Содержание слайда: Связанный подзапрос – пример
Выведем людей из таблицы Persons, для которых нет соответствующей информации в таблице Departments:
SELECT CONCAT(P.FirstName, ' ', P.LastName)
FROM Persons AS P
WHERE NOT EXISTS (SELECT * FROM Departments
WHERE ID = P.Department)
№10 слайд
Содержание слайда: Подзапрос после FROM
Подзапрос можно использовать не только в WHERE-части, но и после FROM. В этом случае получается выборка из результатов подзапроса. Синтаксис T-SQL требует в этом случае дать подзапросу псевдоним.
SELECT T.ID FROM (SELECT * FROM Persons) AS T
№11 слайд
Содержание слайда: Группировка при выборке
Группировка при выборке – разбивка строк набора данных на непересекающиеся группы.
В одну группу входят все строки с одинаковым значением указанного поля (или комбинации полей; или выражения, построенного с использованием полей).
После группировки выборка работает не со множеством строк, а со множеством групп!
№12 слайд
Содержание слайда: Группировка – простейший пример
Разобьём строки таблицы Persons на группы по значениям поля Department и выведем эти значения:
SELECT Department FROM Persons
GROUP BY Department
№13 слайд
Содержание слайда: Группировка – пример 2
Разобьём строки Persons на группы по значениям в полях FirstName и Department:
-- комбинация (Olga, QA) встречается два раза
SELECT FirstName, Department FROM Persons
GROUP BY FirstName, Department
№14 слайд
Содержание слайда: Группировка – пример 3
Создадим группы в зависимости от длины FirstName:
-- встроенная функция LEN() - длина строки
-- (без учёта концевых пробелов)
SELECT LEN(FirstName) FROM Persons
GROUP BY LEN(FirstName)
№15 слайд
Содержание слайда: Группировка – выбираемые колонки
Случай 1 – группировка по колонкам, но без выражений. В этом случае после SELECT можно упоминать колонки группировки и записывать выражения с ними.
Случай 2 – группировка с выражением. После SELECT применяем это же выражение, но не отдельные колонки, входящие в него.
№16 слайд
Содержание слайда: Группировка – выбираемые колонки
SELECT A, B FROM T GROUP BY A, B -- ОК
SELECT A FROM T GROUP BY A, B -- ОК
SELECT A + B FROM T GROUP BY A, B -- ОК
SELECT A + B FROM T GROUP BY A + B -- ОК
SELECT A, C FROM T GROUP BY A, B -- NOT ОК!
SELECT A, B FROM T GROUP BY A + B -- NOT ОК!
№17 слайд
Содержание слайда: Группировка – выбираемые колонки
А как быть с колонками, по которым не проводилась группировка? Их тоже можно упоминать, но только в составе агрегатных функций.
№18 слайд
Содержание слайда: Агрегатные функции
Агрегатные функции выполняют вычисление на наборе значений и возвращают одиночное значение.
Основные агрегатные функции в T-SQL:
AVG() -- среднее значение
MIN() -- минимум
MAX() -- максимум
SUM() -- сумма
COUNT() -- количество элементов
№19 слайд
Содержание слайда: Агрегатные функции – синтаксис
Функция AVG() возвращает среднее арифметическое набора значений (NULL пропускаются). Если набор пуст, функция AVG() возвращает NULL.
AVG(col) -- вычисления по колонке col
AVG(ALL col) -- полный аналог AVG(col)
AVG(DISTINCT col) -- выбрасываем дубликаты из col
У функций MIN(), MAX(), SUM() синтаксис аналогичный.
№20 слайд
Содержание слайда: Агрегатные функции – синтаксис
Функция COUNT() используется в двух форматах.
Первый формат аналогичен другим агрегатным функциям и требует указания колонки. Считается число значений в колонке, которые не NULL.
Второй формат – это COUNT(*). Считает общее число строк в таблице или группе, включая повторяющиеся значения и NULL.
№21 слайд
Содержание слайда: Использование агрегатных функций
Основной вариант – при группировке. Аргументом функции может быть любая колонка набора. Агрегатная функция вычисляется на значениях из каждой группы:
SELECT Department, MAX(ID) AS MaxID FROM Persons
GROUP BY Department
№22 слайд
Содержание слайда: Использование агрегатных функций
Агрегатные функции можно применять и без группировки, в обычном SELECT. Но тогда запрещено выбирать обычные колонки (можно константы):
SELECT MAX(ID) AS MaxRow,
COUNT(*) AS AllCount,
1 AS Const FROM Persons
№23 слайд
Содержание слайда: Группировка и сортировка
Предложение GROUP BY можно использовать совместно с предложением ORDER BY. Сортировать можно по колонкам и выражениями, допустимым после SELECT:
SELECT Department FROM Persons
GROUP BY Department
ORDER BY Department DESC
№24 слайд
Содержание слайда: Группировка и фильтрация
GROUP BY можно применить вместе с WHERE. Строки вначале фильтруются, затем происходит группировка:
SELECT Department FROM Persons
WHERE FirstName <> 'Sergey'
GROUP BY Department
№25 слайд
Содержание слайда: Фильтрация групп
Как быть, если надо отфильтровать не строки исходного набора, а уже сформированные группы?
В этом случае следует использовать предложение HAVING с предикатом, записанное сразу после GROUP BY.
№26 слайд
Содержание слайда: Фильтрация групп – пример
Сгруппируем строки по полю Department и оставим те группы, где Department <> 'DV':
SELECT Department FROM Persons
GROUP BY Department
HAVING Department <> 'DV'
№27 слайд
Содержание слайда: Фильтрация групп
Сила HAVING в том, что его предикат может содержать агрегатные функции, вычисленные по строкам группы.
Пример: выведем группы с количеством строк больше 1:
SELECT Department FROM Persons
GROUP BY Department
HAVING COUNT(*) > 1
№28 слайд
Содержание слайда: Фильтрация, группировка, сортировка
В инструкции SELECT отдельные предложения должны быть записаны в указанном порядке:
SELECT . . .
FROM . . .
WHERE . . .
GROUP BY . . .
HAVING . . .
ORDER BY . . .
№29 слайд
Содержание слайда: Представления
Представление (view) – это виртуальная таблица, которая представляет собой именованный запрос.
Можно сказать, что представление – это синоним к запросу.
Изменение данных в реальной таблице немедленно отражается в содержимом всех представлений, которые построены с использованием этой таблицы.
№30 слайд
Содержание слайда: Преимущества представлений
Дополнительный уровень абстракции – представления скрывают сложность запросов от «внешнего мира».
Дополнительная защита данных – пользователю можно дать права на работу с представлением, но не с таблицами, входящими в него.
СУБД может оптимизировать запрос в представлении, так как этот запрос зафиксирован в момент создания представления.
№31 слайд
Содержание слайда: Создание представления
Для создания представления используется инструкция CREATE VIEW. Указывается имя представления и запрос:
CREATE VIEW view_Persons
AS
SELECT P.ID, P.FirstName, P.LastName, D.Name
FROM Persons AS P
JOIN Departments AS D
ON P.Department = D.ID
№32 слайд
Содержание слайда: Использование представления
После создания представление можно использовать в выборках как обычную таблицу:
SELECT * FROM view_Persons
№33 слайд
Содержание слайда: Изменение и удаление представлений
Для изменения представления используется инструкция ALTER VIEW. Так как суть представления – некий запрос, то изменение подразумевает указание нового запроса.
Удаление определённого представления выполняется инструкцией DROP VIEW.
Скачать все slide презентации Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6) одним архивом:
