Этот пост — мои заметки для https://courses.edx.org/courses/course-v1:Microsoft+DAT201x+2015_T4
Исходные файлы учебных материалов можно найти https://github. com/MicrosoftLearning/QueryingT-SQL
Я опускаю модули 1 и 2 из своих заметок.
Модуль 3. Запросы к нескольким таблицам с помощью объединений
Модуль 4. Использование операторов множества
- UNION возвращает результирующий набор различных строк, объединенных из всех операторов.
- UNION удаляет дубликаты при обработке запроса (влияет на производительность).
- UNION ALL сохраняет дубликаты во время обработки запроса.
- INTERSECT, чтобы возвращались только те строки, которые возвращаются обоими запросами.
- ЗА ИСКЛЮЧЕНИЕМ возврата строк из первого запроса, которые не возвращаются вторым запросом.
Модуль 5. Использование функций и агрегирование данных
- Скалярные функции возвращают одно значение на основе нуля или более входных параметров. например ЛТРИМ(…)
- Логические функции возвращают логические значения (true или false) на основе выражения или значения столбца. например ISNUMERIC(…), IIF(…) и ВЫБЕРИТЕ(…). Функция ВЫБОР возвращает значение в порядковой позиции, начинающейся с 1.
- Оконные функции используются для ранжирования строк по разделам или «окнам». К оконным функциям относятся RANK, DENSE_RANK, NTILE и ROW_NUMBER.
Слово "окно" здесь относится к набору строк. - Агрегатные функции используются для предоставления сводных значений для нескольких строк — например, общей стоимости продуктов или максимального количества товаров в заказе. Обычно используемые агрегатные функции включают SUM, COUNT, MIN, MAX, AVG, COUNT_BIG, GROUPING_ID и STDEV.
- GROUP BY с агрегатными функциями для возврата агрегатов, сгруппированных по одному или нескольким столбцам или выражениям.
- Все столбцы в предложении SELECT, которые не являются выражениями агрегатных функций, должны быть включены в предложение GROUP BY.
- Порядок, в котором столбцы или выражения перечислены в предложении GROUP BY, определяет иерархию группировки.
- Предложение HAVING для фильтрации групп, включенных в результаты запроса. Предложение WHERE вступает в действие перед предложением HAVING. Предложение WHERE исключает ненужные строки, а предложение HAVING удаляет ненужные группы.
Для документации обратитесь к https://msdn.microsoft.com/en-us/library/ms174318.aspx
Модуль 6. Использование подзапросов и APPLY
- Скалярные подзапросы возвращают одно значение. например ГДЕ customerId = (ВЫБЕРИТЕ MAX(customerId) FROM…)
- Многозначные подзапросы возвращают набор строк из одного столбца. например ГДЕ customerId IN (ВЫБЕРИТЕ customerId из…)
- Автономные запросы не связаны с внешним запросом и только передают значения внешнему запросу. Например, скалярные подзапросы и многозначные подзапросы.
- Коррелированные подзапросы ссылаются на объекты во внешнем запросе. См. документацию https://technet.microsoft.com/en-us/library/ms187638
- Оператор APPLY позволяет выполнять функцию с табличным значением для каждой строки в наборе строк, возвращаемом оператором SELECT. Концептуально этот подход аналогичен коррелированному подзапросу.
- CROSS APPLY возвращает совпадающие строки, подобно внутреннему соединению. OUTER APPLY возвращает все строки исходных результатов запроса SELECT со значениями NULL для строк, в которых не было найдено совпадений.
Модуль 7. Использование табличных выражений
- Представления — это объекты базы данных, которые инкапсулируют запросы SELECT.
- Временные таблицы имеют префикс # и хранятся во временной рабочей области (базе данных tempdb в SQL Server). Временные таблицы автоматически удаляются по окончании сеанса, в котором они были созданы. Чрезмерное использование временных таблиц может отрицательно сказаться на общей производительности сервера баз данных. В то время как глобальные временные таблицы создаются с символом ##.
- Табличные переменные имеют префикс @ и хранятся в памяти. Табличные переменные привязаны к пакету, в котором они созданы. Табличные переменные лучше всего работают с небольшими наборами данных. например DECLARE @varProduct таблица (…)
- Табличные функции (TVF) — это функции, которые возвращают таблицу набора строк. например CREATE FUNCTION …(…params) ВОЗВРАЩАЕТ ТАБЛИЦУ КАК…
- Производная таблица — это подзапрос, который генерирует набор строк из нескольких столбцов. Производная таблица должна иметь AS, например. SELECT … FROM (SELECT … ) AS tmp WHERE …
Или это может быть SELECT … FROM (SELECT…) AS tmp(output1, output2…) WHERE… - Общие табличные выражения (CTE) обеспечивают более интуитивно понятный синтаксис или определяют наборы строк, чем производные таблицы, и могут использоваться несколько раз в одном запросе. CTE можно использовать для определения рекурсивных запросов.
- Для выполнения рекурсии используйте общие табличные выражения (CTE). И если нам нужно выполнять рекурсивные запросы, CTE — это то, что нужно.
Модуль 8. Группировка наборов и сводные данные
- НАБОРЫ ГРУППИРОВКИ для определения пользовательских группировок.
- ROLLUP для включения промежуточных и общих итогов для иерархических группировок.
- CUBE, чтобы включить все возможные группировки.
- Функция GROUPING_ID указывает, использовался ли указанный столбец или выражение для создания строки. например ВЫБЕРИТЕ GROUPING_ID(…) ИЗ…
- PIVOT для переориентации набора строк путем создания нескольких столбцов из значений в одном столбце.
- UNPIVOT для переориентации нескольких столбцов в существующем наборе строк в один столбец.
Дополнительные ресурсы
- Предложение WITH — это способ написания подзапросов, делающий весь оператор более читабельным.