🗊Презентация Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6)

Категория: Информатика
Нажмите для полного просмотра!
Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №1Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №2Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №3Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №4Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №5Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №6Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №7Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №8Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №9Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №10Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №11Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №12Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №13Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №14Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №15Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №16Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №17Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №18Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №19Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №20Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №21Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №22Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №23Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №24Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №25Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №26Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №27Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №28Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №29Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №30Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №31Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №32Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6), слайд №33
Скачать

Содержание ▲

Вы можете ознакомиться и скачать презентацию на тему Introduction to databases and SQL. Подзапросы. Группировка при выборке. Агрегатные функции. Фильтрация групп. (Лекция 6). Доклад-сообщение содержит 33 слайдов. Презентации для любого класса можно скачать бесплатно. Если материал и наш сайт презентаций Mypresentation Вам понравились – поделитесь им с друзьями с помощью социальных кнопок и добавьте в закладки в своем браузере.

Слайды и текст этой презентации

Слайд 1


Introduction to Databases and SQL Лекция 6
Описание слайда:
Introduction to Databases and SQL Лекция 6

Слайд 2


Темы занятия Подзапросы Группировка при выборке Агрегатные функции Фильтрация групп Представления (Views)
Описание слайда:
Темы занятия Подзапросы Группировка при выборке Агрегатные функции Фильтрация групп Представления (Views)

Слайд 3


Исходные таблицы
Описание слайда:
Исходные таблицы

Слайд 4


Подзапросы Подзапрос (subquery) – инструкция выборки, которая содержится внутри другой инструкции выборки. Обычно подзапросы применяют в WHERE-части. *) подзапрос может содержаться в инструкциях вставки, обновления или удаления данных – но об этом позже.
Описание слайда:
Подзапросы Подзапрос (subquery) – инструкция выборки, которая содержится внутри другой инструкции выборки. Обычно подзапросы применяют в WHERE-части. *) подзапрос может содержаться в инструкциях вставки, обновления или удаления данных – но об этом позже.

Слайд 5


Что может возвращать подзапрос? Произвольная выборка (несколько строк и колонок). Операция EXISTS (NOT EXISTS) может проверить, что выборка не пустая (или пустая). Набор однотипных значений. Удобно использовать в сочетании с операций IN (NOT IN). Одно значение (или ничего). Это значение можно использовать в операциях сравнения.
Описание слайда:
Что может возвращать подзапрос? Произвольная выборка (несколько строк и колонок). Операция 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.
Описание слайда:
Подзапросы в 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.
Описание слайда:
Подзапросы в WHERE – пример 2 SELECT FirstName, LastName FROM Persons WHERE Department=(SELECT ID FROM Departments WHERE Name='Quality assurance') Важно: если подзапрос вернёт более одного значения, то будет ошибка при выполнении запроса. Если вернёт ровно одно значение – всё ОК. Если ничего не вернёт (то же самое, что NULL) – тоже всё ОК, ибо любая операция сравнения с NULL возможна, но всегда равна FALSE.

Слайд 8


Связанный подзапрос Связанный подзапрос (correlated subquery) использует в работе информацию внешнего запроса. Например, проверяет своё условие WHERE, используя колонки таблицы из внешнего запроса.
Описание слайда:
Связанный подзапрос Связанный подзапрос (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)
Описание слайда:
Связанный подзапрос – пример Выведем людей из таблицы 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
Описание слайда:
Подзапрос после 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
Описание слайда:
Группировка – простейший пример Разобьём строки таблицы 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
Описание слайда:
Группировка – пример 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)
Описание слайда:
Группировка – пример 3 Создадим группы в зависимости от длины FirstName: -- встроенная функция LEN() - длина строки -- (без учёта концевых пробелов) SELECT LEN(FirstName) FROM Persons GROUP BY LEN(FirstName)

Слайд 15


Группировка – выбираемые колонки Случай 1 – группировка по колонкам, но без выражений. В этом случае после SELECT можно упоминать колонки группировки и записывать выражения с ними. Случай 2 – группировка с выражением. После SELECT применяем это же выражение, но не отдельные колонки, входящие в него.
Описание слайда:
Группировка – выбираемые колонки Случай 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 ОК!
Описание слайда:
Группировка – выбираемые колонки 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() -- количество элементов
Описание слайда:
Агрегатные функции Агрегатные функции выполняют вычисление на наборе значений и возвращают одиночное значение. Основные агрегатные функции в 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() синтаксис аналогичный.
Описание слайда:
Агрегатные функции – синтаксис Функция AVG() возвращает среднее арифметическое набора значений (NULL пропускаются). Если набор пуст, функция AVG() возвращает NULL. AVG(col) -- вычисления по колонке col AVG(ALL col) -- полный аналог AVG(col) AVG(DISTINCT col) -- выбрасываем дубликаты из col У функций MIN(), MAX(), SUM() синтаксис аналогичный.

Слайд 20


Агрегатные функции – синтаксис Функция COUNT() используется в двух форматах. Первый формат аналогичен другим агрегатным функциям и требует указания колонки. Считается число значений в колонке, которые не NULL. Второй формат – это COUNT(*). Считает общее число строк в таблице или группе, включая повторяющиеся значения и NULL.
Описание слайда:
Агрегатные функции – синтаксис Функция COUNT() используется в двух форматах. Первый формат аналогичен другим агрегатным функциям и требует указания колонки. Считается число значений в колонке, которые не NULL. Второй формат – это COUNT(*). Считает общее число строк в таблице или группе, включая повторяющиеся значения и NULL.

Слайд 21


Использование агрегатных функций Основной вариант – при группировке. Аргументом функции может быть любая колонка набора. Агрегатная функция вычисляется на значениях из каждой группы: SELECT Department, MAX(ID) AS MaxID FROM Persons GROUP BY Department
Описание слайда:
Использование агрегатных функций Основной вариант – при группировке. Аргументом функции может быть любая колонка набора. Агрегатная функция вычисляется на значениях из каждой группы: 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
Описание слайда:
Использование агрегатных функций Агрегатные функции можно применять и без группировки, в обычном 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
Описание слайда:
Группировка и сортировка Предложение 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
Описание слайда:
Группировка и фильтрация GROUP BY можно применить вместе с WHERE. Строки вначале фильтруются, затем происходит группировка: SELECT Department FROM Persons WHERE FirstName <> 'Sergey' GROUP BY Department

Слайд 25


Фильтрация групп Как быть, если надо отфильтровать не строки исходного набора, а уже сформированные группы? В этом случае следует использовать предложение HAVING с предикатом, записанное сразу после GROUP BY.
Описание слайда:
Фильтрация групп Как быть, если надо отфильтровать не строки исходного набора, а уже сформированные группы? В этом случае следует использовать предложение HAVING с предикатом, записанное сразу после GROUP BY.

Слайд 26


Фильтрация групп – пример Сгруппируем строки по полю Department и оставим те группы, где Department <> 'DV': SELECT Department FROM Persons GROUP BY Department HAVING Department <> 'DV'
Описание слайда:
Фильтрация групп – пример Сгруппируем строки по полю 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
Описание слайда:
Фильтрация групп Сила HAVING в том, что его предикат может содержать агрегатные функции, вычисленные по строкам группы. Пример: выведем группы с количеством строк больше 1: SELECT Department FROM Persons GROUP BY Department HAVING COUNT(*) > 1

Слайд 28


Фильтрация, группировка, сортировка В инструкции SELECT отдельные предложения должны быть записаны в указанном порядке: SELECT . . . FROM . . . WHERE . . . GROUP BY . . . HAVING . . . ORDER BY . . .
Описание слайда:
Фильтрация, группировка, сортировка В инструкции SELECT отдельные предложения должны быть записаны в указанном порядке: SELECT . . . FROM . . . WHERE . . . GROUP BY . . . HAVING . . . ORDER BY . . .

Слайд 29


Представления Представление (view) – это виртуальная таблица, которая представляет собой именованный запрос. Можно сказать, что представление – это синоним к запросу. Изменение данных в реальной таблице немедленно отражается в содержимом всех представлений, которые построены с использованием этой таблицы.
Описание слайда:
Представления Представление (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
Описание слайда:
Создание представления Для создания представления используется инструкция 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
Описание слайда:
Использование представления После создания представление можно использовать в выборках как обычную таблицу: SELECT * FROM view_Persons

Слайд 33


Изменение и удаление представлений Для изменения представления используется инструкция ALTER VIEW. Так как суть представления – некий запрос, то изменение подразумевает указание нового запроса. Удаление определённого представления выполняется инструкцией DROP VIEW.
Описание слайда:
Изменение и удаление представлений Для изменения представления используется инструкция ALTER VIEW. Так как суть представления – некий запрос, то изменение подразумевает указание нового запроса. Удаление определённого представления выполняется инструкцией DROP VIEW.

Скачать

Похожие презентации
Mypresentation.ru
Загрузить презентацию