webdonsk.ruКак сделать сложный запрос в sql
Добавил пользователь Morpheus Обновлено: 10.09.2024
Аннотация: В лекции обсуждаются вопросы построения и применения подзапросов при извлечении и изменении данных.
Подзапросы
Язык SQL разрешает использовать в других операторах языка DML подзапросы , которые являются внутренними запросами, определяемыми оператором SELECT .
Подзапрос - очень мощное средство языка SQL. Он позволяет строить сложные иерархии запросов, многократно выполняемые в процессе построения результирующего набора или выполнения одного из операторов изменения данных ( DELETE , INSERT , UPDATE ).
Условно подзапросы иногда подразделяют на три типа, каждый из которых является сужением предыдущего:
- табличный подзапрос , возвращающий набор строк и столбцов;
- подзапрос строки , возвращающий только одну строку, но, возможно, несколько столбцов (такие подзапросы часто используются во встроенном SQL );
- скалярный подзапрос , возвращающий значение одного столбца в одной строке.
Подзапрос позволяет решать следующие задачи:
- определять набор строк, добавляемый в таблицу на одно выполнение оператора INSERT ;
- определять данные, включаемые в представление, создаваемое оператором CREATE VIEW ;
- определять значения, модифицируемые оператором UPDATE ;
- указывать одно или несколько значений во фразах WHERE и HAVING оператора SELECT ;
- определять во фразе FROM таблицу как результат выполнения подзапроса ;
- применять коррелированные подзапросы . Подзапрос называется коррелированным, если запрос, содержащийся в предикате, имеет ссылку на значение из таблицы (внешней к данному запросу), которая проверяется посредством данного предиката.
Hекоторые СУБД (например, СУБД Oracle ) позволяют на основе подзапроса создавать новые таблицы с помощью оператора CREATE TABLE .
Простым примером использования подзапроса может служить следующий оператор:
В случае если подзапрос не выберет ни одной строки, то предикат будет равен UNKNOWN , что большинством СУБД интерпретируется как FALSE .
Стандарт определяет запись предиката в форме " значение оператор подзапрос ". Однако некоторые СУБД также позволяют записывать предикат в форме, указывающей подзапрос слева от оператора сравнения.
Очень часто с подзапросами используются агрегирующие функции, предоставляющие возможность сформулировать условие типа "больше, чем среднее по группе".
Если результатом подзапроса становится группа строк (это случается всегда, когда условие не гарантирует уникальности значения проверяемого предикатом внутреннего запроса), то следует использовать оператор IN , осуществляющий выбор одного значения из указываемого множества .
В этом случае предикат принимает значение TRUE , если хотя бы одно из значений, возвращаемых подзапросом , удовлетворяет условию.
Однако применение оператора IN имеет и некоторые смысловые недостатки: в запросе четко не определяется, сколько строк должны быть результатом выполнения запроса. При построении отношений для реальной модели данных это может приводить к некоторой неоднозначности и зависимости от самих данных. В противном случае, если модель данных предполагает в качестве постоянного результата подзапроса наличие только одной строки и, соответственно, использует оператор сравнения = , а структура данных позволяет ввод значений, когда в результате подзапроса будет более одной строки, то при использовании такого SQL -оператора в какой-то момент времени может проявиться ошибка.
Если в запросе участвуют более двух таблиц, то для большей наглядности имена полей иногда квалифицируют именами таблиц, указывая их через точку. Стандарт позволяет не квалифицировать имя поля именем таблицы в том случае, если не возникает неоднозначности ( поле сначала ищется в таблице, указанной фразой FROM текущего запроса, затем внешнего запроса и т.д.).
Очень часто вместо записи оператора SELECT с использованием подзапроса можно применять соединения. Однако на практике большинство СУБД подзапросы выполняют более эффективно. Тем не менее, при проектировании комплекса программ с критичными требованиями по быстродействию, разработчик должен проанализировать план выполнения SQL -оператора для конкретной СУБД .
Наиболее продвинутые СУБД , такие как Oracle , предоставляют ряд SQL -операторов, позволяющих оценить производительность выполнения конкретного оператора языка SQL , а также определить уровень оптимизации, применяемый для данного оператора.
Подзапрос может быть указан как в предикате, определяемом фразой WHERE , так и в предикате по группам, определяемом фразой HAVING .
У SQL несколько диалектов, которые служат производными основного языка. Ситуация напрямую зависит от используемой БД. На практике чаще всего выделяют:
- MySQL;
- Postgre;
- Microsoft SQL Server.
Запрос SQL – объект, имеющий классификацию по различным категориям. Это помогает понять, для каких целей используется та или иная команда.
Языки запросов
Можно легко запутаться, если не изучить разделение БД на различные крупные категории. Их называют языками. Выделяют следующие варианты:
Других языковых представлений базы данных не предусматривают. Далее будут рассмотрены ключевые операции и операторы.
Классификация – основное
Речь идет о следующих вариантах:
- Insert – данные в таблице добавляются (новые строки);
- Select – выбор;
- Update – обновление сведений;
- Delete – удаление строчки.
Простые запросы
Создание
Первое, с чем предстоит столкнуться каждому – это с созданием таблиц. Чтобы получить новую базу, используется операция Create table. Параметрами послужат количество колонок и их типы.
Можно создать табличку в виде своеобразного календаря. Путь в ней будут следующие элементы:
- id – номер месяца;
- days – количество дней в выбранном периоде;
- name – как называется месяц.
В конечном итоге кодовая комбинация получит следующий вид:
Create table calendar (id int, name varchar (10), days int);
Здесь используются целочисленные типы, а также предельная длина строчки name составляет 10 символов.
При создании таблицы обязательно делать колонку primary key, в которой значения будут уникальными. Обычно таковой служит ID. Рассмотренный пример позволяет присвоить соответствующий параметр колонке name. Все названия месяцев сами по себе уникальные.
Ввод информации
Существуют различные варианты ввода нового материала. Но ключевой командой является Insert. В первом случае названия колонок не указываются. Значения принимаются в порядке, прописанном в таблице:
Insert into calendar values (1, ‘January’,31);
Этот вариант не подходит, если пользователь в будущем захочет добавить несколько новых строк. Все старые запросы перестанут работать. Во избежание соответствующей проблемы лучше прибегать ко второму варианту. В нем полностью прописываются названия колонок:
Если пользователь не указывает ту или иную колонку, система присвоит ей значение Null или то, что было задано по умолчанию.
Вывод на экран
Следующая команда необходима, когда хочешь посмотреть, что написано в БД. Выводит на дисплей запрошенную строчку или всю таблицу целиком.
Фильтрование
На экран выведутся все строки с расходами на продукты. Очень удобно, особенно при работе с большими БД.
Логика и математика
Создана таблица, хранящая информацию о самых популярных мировых музыкальных альбомах за все времена. На экран требуется вывести только рок-композиции с продажами не более 40 000 000 штук. Для этого предстоит использовать Where с логическими операторами And/or и математическими сравнительными знаками:
- In – значение в столбце сравнивается с несколькими возможными. Присваивается true, если хотя бы один из вариантов совпал.
- Between – используется для проверки нахождения значения в определенном промежутке.
- Like – поиск по заданным шаблонам.
А вот примеры, которые позволяют лучше понять запрос SQL (работа с табличкой по продажам музыкальных альбомов):
Команда имеет инверсию. Достаточно написать перед логическим выражением Not.
Удаление
Если создана таблица с большим количеством строк, можно внести некие корректировки в нее. Довольно простой операцией является команда Delete. Достаточно указать, от чего конкретно требуется избавиться:
А вот для избавления от всех сведений в таблице с ее сохранением требуется воспользоваться Truncate:
Если требуется избавиться от всего архива безвозвратно, применяется Drop.
Функции
Запрос SQL имеет очень много функций, используемых для совершения тех или иных операций. Вот самые активно задействованные из них:
- Count() – возвращение числа строк;
- Sum() – возврат сумм всех полей со значениями числового характера;
- Avg() – среднее значение;
- Min()/Max() – показывает минимальные/максимальные сведения.
Наглядные примеры будут рассматриваться по таблице Albom, упомянутой выше по тексту:
- Select max (released) From albom; — год самого старого выпущенного альбома.
- Select name, avg(vozrast) from albom; Group by name – позволяет показать имя и среднее значение.
Но и это еще не все. Есть так называемые сложные запросы, задействованные при использовании результатов проведенных вычислений или для работы сразу с несколькими табличками.
Сложные запросы
На примере работы с таблицей по музыкальным альбомам: требуется вывести на экран исполнителя, год выпуска, а также альбом, который является самым старым из всех.
Далее запросы требуется объединить при помощи Where. Получится запись типа:
Соединение
Есть два виртуальных хранилища информаций: разработчики и выпущенные игры. Первая называется igry и включает в себя столбец razrabotchick_id. Типы соответствующих строк во втором случае – foreign_key. Связующее звено между двумя архивами.
При желании отобразить все материалы об игрушке, включая ее разработчика, требуется подключение второй таблицы. Делается это посредством Inner Join:
Join используется по-разному. К примеру, для присвоения псевдонима.
Псевдонимы
Подобная команда делает таблицу более красивой и удобной.
Корректировка
Вложенные запросы – не единственное, о чем должен знать человек, работающий с электронными материалами и их хранилищем. Корректировка информации тоже является сложной командой. Включает в себя ключевую фразу Update, вписанную в команду.
Ее использование подразумевает:
- выбор базы, в которой есть поле, подлежащее корректировке;
- ввод новых значений;
- использование Where для обозначения определенного места в табличке.
Теперь жанр данного сериала – фэнтэзи.
Исключающая выборка
Теперь ясно, как выглядят запрос SQL в том или ином случае. Это основные примеры, которые помогают понять использование различных операторов и команд. По каждому диалекту Structured Query Language интересующие сведения уточняются индивидуально.
Оператор языка SQL SELECT - основная строительная конструкция для создания любого, простого или сложного запроса к базе данных. Без него, как и без фундамента для постройки, невозможно получить ни одну выборку данных из базы. На этом уроке мы узнаем, как построить запрос для получения
- простой выборки данных без условий (выбор всех строк во всех столбцах или всех строк в определённых столбцах);
- выборки данных с одним или несколькими условиями (выбор определённых строк), которые заданы в секции WHERE с помощью предикатов и дополительный операторов;
- как использовать оператор SELECT в подзапросах .
SELECT для выбора столбцов таблицы
Запрос с оператором SELECT для выбора всех столбцов таблицы имеет следующий синтаксис:
То есть для выбора всех столбцов таблицы после слова SELECT нужно ставить звёздочку.
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .
Работать будем с базой данных фирмы - Company1. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными - в файле по этой ссылке .
Пример 1. Итак, есть база данных фирмы - Company1. В ней есть таблица Org (Структура фирмы) и Staff (Сотрудники). Требуется выбрать из таблиц все столбцы. Соответствующий запрос для выбора всех столбцов из таблицы Org выглядит следующим образом (на MS SQL Server - с предваряющей конструкцией USE company1;):
Этот запрос вернёт следующее (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):
Запрос для выбора всех столбцов из таблицы Staff выглядит следующим образом (на MS SQL Server - с предваряющей конструкцией USE company1;):
Этот запрос вернёт следующее:
Для выбора определённых столбцов таблицы нам потребуется вместо звёздочки перечислить через запятую названия всех столбцов, которые требуется выбрать:
Пример 2. Пусть требуется из таблицы Org выбрать столбцы Depnumb и Deptname, в которых содержатся данные соответственно о номерах отделов фирмы и об их названиях. Запрос для получения такой выборки будет следующим (на MS SQL Server - с предваряющей конструкцией USE company1;):
А из таблицы Staff нужно выбрать столбцы Dept, Name, Job, в которых содержатся соответственно данные о номере отдела, в котором трудится сотрудник, его имени и должности (на MS SQL Server - с предваряющей конструкцией USE company1;):
SELECT и WHERE для выбора строк таблицы
Для выбора определённых строк таблицы вместе с оператором SELECT уже потребуется ключевое слово WHERE, указывающее на некоторое значение или несколько значений, содержащиеся в интересующих нас строках. Наиболее простые условия задаются при помощи операторов сравнения и равенства ( , =), а также ключевого слова IS. Условий может быть несколько, тогда они перечисляются с использованием ключевого слова AND. Запросы для выбора строк имеют следующий синтаксис:
Этот запрос вернёт следующие данные:
Этот запрос вернёт следующие данные:
Пример 5. Пусть нужно выбрать из таблицы Staff идентификаторы и имена тех сотрудников, размер комиссии которых - неопределённый. Для этого в секции WHERE перед указанием значения столбца Comm - NULL нужно ставить не знак равенства, а слово IS (на MS SQL Server - с предваряющей конструкцией USE company1;):
Этот запрос вернёт следующие данные:
Для указания значений в строках, которые требуется выбрать, используются и знаки сравнения.
Пример 6. Выберем из таблицы имена, размеры заработные платы и число лет, проработанных в фирме, сотрудников, которые работают в фирме более девяти лет (на MS SQL Server - с предваряющей конструкцией USE company1;):
Запрос вернёт следующие строки:
Использование SELECT и предикатов IN, OR, BETWEEN, LIKE
Предикаты - слова IN, OR, BETWEEN, LIKE в секции WHERE - также позволяют выбрать определённые диапазоны значений (IN, OR, BETWEEN) или значения в строках (LIKE), которые требуется выбрать из таблицы. Запросы с предикатами IN, OR, BETWEEN имеют следующий синтаксис:
SELECT ИМЯ_СТОЛБЦА FROM ИМЯ_ТАБЛИЦЫ WHERE ЗНАЧЕНИЕ ПРЕДИКАТ (IN, OR, BETWEEN) (ЗНАЧЕНИЯ, УКАЗЫВАЮЩИЕ ДИАПАЗОН)
Запросы с предикатом LIKE имеют следующий синтаксис:
Пример 7. Пусть требуется выбрать из таблицы Staff имена, должности и число отработанных лет сотрудников, работающих в отделах с номерами 20 или 84. Это можно сделать следующим запросом (на MS SQL Server - с предваряющей конструкцией USE company1;):
Результат выполнения запроса:
На сайте есть подробный урок об использовании предиката IN.
Пример 8. Пусть теперь требуется выбрать из таблицы Staff те же данные, что и в предыдущем примере. Запрос со словом OR аналогичен запросу со словом IN и перечислением интересующих значений в скобках. Запрос будет следующим (на MS SQL Server - с предваряющей конструкцией USE company1;):
Пример 9. Выберем из той же таблицы имена, должности и число отработанных лет сотрудников, зарплата которых между 15000 и 17000 включительно (на MS SQL Server - с предваряющей конструкцией USE company1;):
Результат выполнения запроса:
На сайте есть подробный урок об использовании предиката BETWEEN.
Предикат LIKE используется для выборки тех строк, в значениях которых встречаются символы, указанные после предиката между апострофами (').
Пример 10. Выберем из той же таблицы имена, должности и число отработанных лет сотрудников, имена которых начинаются с буквы S и состоят из 7 символов (на MS SQL Server - с предваряющей конструкцией USE company1;):
Символ подчёркивания (_) означает любой символ. Результат выполнения запроса:
Пример 11. Выберем из той же таблицы имена, должности и число отработанных лет сотрудников, имена которых начинаются с буквы S и содержат любые другие буквы в любом количестве (на MS SQL Server - с предваряющей конструкцией USE company1;):
Символ процентов (%) означает любое количество символов. Результат выполнения запроса:
На сайте есть подробный урок об использовании предиката LIKE.
Значения, указанные с использованием предикатов IN, OR, BETWEEN, LIKE можно инвертировать при помощи слова NOT. Тогда запрашиваемые данные будут иметь противоположный смысл. Если мы используем NOT IN (20, 84), то будут выведены данные сотрудников, которые работают во всех отделах, кроме имеющих номера 20 и 84. С использованием NOT BETWEEN 15000 AND 17000 можно получить данные сотрудников, зарплата которых не входит в интервал от 15000 до 17000. Запрос с NOT LIKE выведет данные сотрудников, чьи имена не начинаются или не содержат символов, указанных с NOT LIKE.
Написать SQL запросы с SELECT и предикатами IN, NOT IN, BETWEEN самостоятельно, а затем посмотреть решения
Есть база данных "Театр". Таблица Play содержит данные о постановках. Таблица Team - о ролях актёров. Таблица Actor - об актёрах. Таблица Director - о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).
Пример 12. Вывести список актёров, которые не разу не были утверждены на главную роль. В таблице team данные о главных ролях содержатся в столбце mainteam. Если роль - главная, то в соответствующей строке отмечено 'Y'.
Пример 13. Вывести список актеров, которые играли во всех спектаклях WilliamShakespeare. Данные об авторах содержается в таблице play в столбце author.
Пример 14. Вывести спектакли, в которых средний возраст актеров от 20 до 30 (использовать BETWEEN, Group by, Having, AVG, перекрестное соединение таблиц (CROSS JOIN), удобнее без слова JOIN, а с перечислением таблиц через запятую).
SELECT и ORDER BY - сортировка (упорядочение) строк
Разобранные до сих пор запросы SQL SELECT возвращали строки, которые могли быть расположены в любой последовательности. Однако часто требуется отсортировать строки по порядку номеров, алфавиту и другим признакам. Для этого служит ключевое словосочетание ORDER BY. Такие запросы имеют следующий синтаксис:
SELECT ИМЯ_СТОЛБЦА FROM ИМЯ_ТАБЛИЦЫ WHERE УСЛОВИЕ ORDER BY СТОЛБЕЦ, ПО КОТОРОМУ СОРТИРУЮТСЯ РЕЗУЛЬТАТЫ
Пример 15. Вновь база данных Company1. Пусть требуетя выбрать из таблицы Staff сотрудников, работающих в отделе с номером 84 и отсортировать (упорядочить) записи по числу отработанных лет в возрастающем порядке (на MS SQL Server - с предваряющей конструкцией USE company1;):
Слово ASC указывает, что порядок сортировки - возрастающий. Это слово не обязательно, так как возрастающий порядок сортировки применяется по умолчанию. Результат выполнения запроса:
Пример 16. Пусть требуетя выбрать те же данные, что и в предыдущем примере, но отсортировать (упорядочить) записи по числу отработанных лет в убывающем порядке (на MS SQL Server - с предваряющей конструкцией USE company1;):
Слово DESC указывает, что порядок сортировки - убывающий. Результат выполнения запроса:
SELECT и DISTINCT - удаление дубликатов строк
Когда для значений строк таблицы не задано условие уникальности, в результатах запроса могут встретиться одинаковые строки. Часто требуется вывести лишь уникальные строки. Это делается при помощи выражения DISTINCT после оператора SELECT.
Пример 17. Пусть требуетcя узнать, какие существуют отделы и какие должности среди отделов, номера которых меньше 30. Это можно сделать при помощи следующего запроса (на MS SQL Server - с предваряющей конструкцией USE company1;):
Результат выполнения запроса:
Оператор SELECT в подзапросах SQL
До сих пор мы разбирали конструкции SQL с оператором SELECT, в которых условия, по котором выбираются данные, и сами выбираемые данные содержатся в одной и той же таблице базы данных. На практике часто бывает, что данные, которые надо выбрать, содержатся в одной таблице, а условия - в другой. Здесь на помощь приходят подзапросы: значения условия отбора возвращаются из другого запроса (вложенного запроса), начинающегося также с SELECT. Запросы с подзапросами могут выдавать как одну, так и несколько строк.
Пример 18. Все те же таблицы Org и Staff. Пусть требуетcя узнать, в каком подразделении работает сотрудник с идентификационным номером 16, и где это подразделение расположено. Но информация о подразделениях хранится в таблице Org, а информация о сотрудниках - в таблице Staff. Это можно сделать при помощи следующего запроса с подзапросом, в котором внешний SELECT обращается к таблице Org, а внутренний SELECT - к таблице Staff:
SELECT Division, Location FROM Org WHERE Deptnumb = ( SELECT Dept FROM Staff WHERE align="justify">Пример 19. Пусть теперь требуетcя узнать, в каких подразделениях (без дублирования) работают сотрудники с заработной платой менее 13000. Для этого в секции WHERE внешнего SELECT (запрос к таблице Org) задаётся условие, принимающее диапазон значений (IN), а внутренний SELECT (к таблице Staff) как раз возвращает требуемый диапазон значений:
Знание продвинутого синтаксиса SQL необходимо и новичку, и опытному дата-инженеру или аналитику данных.
В связи с бурным ростом объема данных все более важным становится умение очень быстро их анализировать.
Источник: Statista
Объем данных на этом графике показан в зеттабайтах.
Есть много очень вместительных нереляционных хранилищ, которые отлично выполняют свою работу, поддерживая массовое горизонтальное масштабирование с низкими затратами. Однако они не заменяют высококачественные хранилища на основе SQL, а лишь дополняют их.
Высококачественными и очень надежными для относительно естественного моделирования данных их делают ACID-свойства SQL.
Я и сам дата-инженер, давно использую SQL и знаю, как важно быстрее писать сложные запросы. И продвинутый синтаксис SQL будет здесь очень кстати.
Изображение автора
Нарастающий итог
На практике часто приходится подсчитывать нарастающий итог по таблице, т. е. как меняется промежуточная сумма каждый раз при добавлении нового значения.
Нарастающим итогом называется сумма значений во всех ячейках столбца до следующей ячейки в этом конкретном столбце.
Вот запрос на эту сумму:
А вот как будет выглядеть результат:
Изображение автора
Обобщенные табличные выражения
Обобщенные табличные выражения используются ради большего удобства для восприятия человеком сложных запросов, требующих соединения, и подзапросов.
Фактически это временный именованный результирующий набор данных, на который можно ссылаться внутри оператора SELECT , INSERT , UPDATE или DELETE .
Рассмотрим простой запрос:
Представьте, что мы задействуем этот подзапрос многократно в последующем запросе. Не проще ли использовать его как временную таблицу? Именно эту задачу и решают обобщенные табличные выражения.
Упорядочение данных
Дата-инженерам и аналитикам данных очень часто приходится упорядочивать значения по каким-либо параметрам, например зарплате, затратам и т. д. И это экономит много времени при поиске точного запроса.
Вместо RANK() используется также DENSE_RANK() . Он аналогичен, но не пропускает следующее по порядку значение, если у двух строк одинаковое значение.
Добавление подытогов
Наличие промежуточного итога (подытога) помогает оценить данные в контексте общего итога.
Это расширенная версия оператора GROUP BY : здесь есть возможность добавления к данным промежуточных и общих итогов.
Изображение автора
Примечание: это запрос в MySQL. Для других синтаксис свертки может отличаться.
Здесь в запросе строка со значениями null и для типа, и для идентификатора — это итог. Есть также подытоги со значениями null только в столбце идентификатора: это 4-я и предпоследняя строки.
Временные функции
Временные функции позволяют легко менять данные без использования огромных операторов case .
В следующем примере временная функция применяется для преобразования типа в род. Это можно было сделать с помощью встроенного в запрос оператора case , но тогда было бы неудобно читать.
Дисперсия и среднеквадратическое отклонение
Для получения этих значений есть специальные агрегатные функции: VARIANCE , VAR_POP и VAR_SAMP . Они группируют данные и используются для определения дисперсии, дисперсии группы и дисперсии выборки набора данных по отдельности.
VAR_POP — дисперсия совокупности;
VAR_SAMP — дисперсия выборки;
STDDEV_SAMP — среднеквадратическое отклонение для выборки;
STDDEV_POP — среднеквадратическое отклонение для совокупности.
Это были основные SQL-команды, которые я постоянно использовал, работая дата-инженером, и которые пришлись очень кстати при решении многих бизнес-задач.
Stats подтверждает, что экосистема инструментов SQL, которая включает в себя все: от Excel и Tableau до SparkSQL — используется в более чем 60 % организаций. Это настоящий подвиг для SQL, особенно учитывая его возраст.
Читайте также: