Многотабличные запросы. Группировка данных. Запросы на создание

И обновление данных

 

Многотабличные запросы

Связывание таблиц

Конструкция <связка_таблиц> в разделе FROM реализует один из наиболее сложных методов задания источника данных. С помощью нее можно связать данные двух и более таблиц в единый набор данных, указав критерии связывания. Синтаксис конструкции <связка_таблиц>следующий (слайд 2).

Конструкция <тип_связывания> описывает тип связывания двух таблиц. Исходная таблица указывается слева от конструкции <тип_связывания> (<левая_таблица>), а справа указывается зависимая таблица (<правая_таблица>).

Общий синтаксис конструкции <тип_связывания> представлен на слайде.

Как видно, обязательным в конструкции является ключевое слово JOIN.

Конструкция ON <условие_связывания> задает логическое условие связывания двух таблиц. Допустимы операторы сравнения (например, =, <, >, <=, >=,!-, <>). Чаще всего используется оператор равенства.

В примере, показанном на слайде, устанавливается связь между таблицами «Учебный_план» и «Дисциплина» по столбцу ID_Дисциплина, имеющемуся в каждой из таблиц.

 

Ключевое слово INNER

Этот тип связи используется по умолчанию. Указание сочетания INNER JOIN равносильно указанию только ключевого слова JOIN. В качестве кандидатов на включение в результат запроса рассматриваются пары строк, удовлетворяющие критерию связывания в обеих таблицах. Затем строки из левой таблицы, для которых не имеется пары в связанной таблице, в результат не включаются. Также не включаются в результат и строки правой таблицы, для которых нет соответствующей строки в левой таблице.

В приведенном примере (слайд 3) выполняется выборка данных из таблиц «Дисциплины» и «Учебный_план» с помощью запроса SELECT. Таблицы связаны по ключевому полю ID_Дисциплина, имеющемуся в каждой из них. Для каждой строки таблицы «Учебный_план» ищется строка с совпадающим значением поля ID_Дисциплина в таблице «Дисциплины». Все строки таблицы «Учебный_план», для которых нет строк с соответствующим значением поля ID_Дисциплина, игнорируются и не включаются в конечный результат. Аналогично, не включаются в результат все строки таблицы «Дисциплины», для которых нет соответствующей строки в таблице «Учебный_план» (что, однако, невозможно для данного примера, так как столбец ID_Дисциплина таблицы «Учебный_план» связан внешним ключом со столбцом ID_Дисциплина таблицы «Дисциплины»).

 

18.1.3. Ключевое слово LEFT [ OUTER ]

При использовании ключевого слова LEFT в результат будут включены все строки левой таблицы, независимо от того, есть для них соответствующая строка в правой таблице или нет. В случае отсутствия строки в правой таблице для столбцов правой таблицы, включенных в результат выборки, устанавливается значение NULL. В приведенном примере (слайд 4) иллюстрируется использование ключевого слова LEFT [OUTER] для выборки данных.

Как видно, по сравнению с использованием ключевого слова INNER, в результат запроса добавлена строка из таблицы «Дисциплины», которая удовлетворяет сформулированному условию отбора, но для которой не существует соответствующей строки в таблице «Учебный_план». В столбцах Семестр и Отчетность (относящихся к таблице «Учебный_план») для этих строк установлено значение NULL.

 

Ключевое слово RIGHT [ OUTER ]

При использовании этого ключевого слова в результат будут включены все строки правой таблицы, независимо от того, есть ли для них соответствующая строка в левой таблице. Для соответствующих столбцов левой таблицы, включенных в запрос, устанавливается значение NULL. Приведем пример такого запроса (слайд 5).

Этот пример основывается на тех же данных, что и предыдущий, но связь таблиц устанавливается в обратном порядке.

 

Ключевое слово FULL [ OUTER ]

При использовании ключевого слова FULL в результат будут включены все строки как правой, так и левой таблицы. Применение ключевого слова FULL [OUTER] можно рассматривать как одновременное применение ключевых слов LEFT [OUTER] и RIGHT[OUTER].

 

Раздел WHERE может быть использован для связывания таблиц. В этом случае условие связывания должно присоединяться к логическому выражению с помощью логической операции AND (логическое умножение).

Рассмотрим пример, уточняющий один из представленных выше, и перенесем условие связывания в логическое выражение (слайд 6).

Результат выполнения обоих запросов одинаков:

Использование только условия связывания в разделе WHERE аналогично связыванию ключевым словом INNER в разделе FROM.

Аналогом использования ключевых слов LEFT OUTER JOIN является указание в разделе WHERE условия с помощью символов *=.

Аналогом использования ключевых слов RIGHT OUTER JOIN является указание условия с помощью символов =*.

Следует отметить, что при использовании специальных ключевых слов INNER | {LEFT | RIGHT | FULL } [OUTER ] данные представляются по-иному, чем при указании условия WHERE. Скорость выполнения запроса в первом случае оказывается выше, поскольку организуется связывание данных, тогда как при использовании конструкции WHERE происходит их фильтрация. При выполнении запросов на небольших наборах данных это не играет существенной роли, поэтому удобнее обращаться к конструкции WHERE из-за наглядности и простоты синтаксиса этого варианта, но при построении сложных запросов, выполняющих обработку тысяч строк, все же лучше использовать конструкцию связывания.

Раздел GROUP BY

Раздел GROUP BY позволяет выполнять группировку строк таблиц по определенным критериям. Для каждой группы можно выполнить специальные функции агрегирования, которые применяются ко всем строкам в группе. Одним из примеров использования раздела GROUP BY является суммирование однотипных значений.

Синтаксис раздела GROUP BY следующий (слайд 7).

При использовании группировки (раздела GROUP BY) на раздел SELECT накладываются дополнительные ограничения. В непосредственном виде разрешается указание только имен столбцов, перечисленных в разделе GROUP BY, то есть тех столбцов, по которым осуществляется группировка. Значения других столбцов не могут быть выведены в непосредственном виде, так как обычно каждая группа содержит множество строк, а в результате выборки для каждой группы должно быть указано единственное значение. Поэтому, чтобы вывести значения столбцов, не задающих критерии группировки, необходимо использовать функции агрегирования.

Аргумент <условие_группировки>определяет условие группировки. Обычно в качестве условия группировки указывается имя столбца, однако в общем случае разрешается использование и выражений, включающих ссылки на столбцы.

Функции агрегирования позволяют выполнять статистическую обработку данных, подсчитывая количество, сумму, среднее значение и другие величины для всего набора данных. Во многих функциях агрегирования допускается использование ключевых слов ALL и DISTINCT. Ключевое слово ALL выполняет агрегирование всех строк исходного набора данных. При указании ключевого слова DISTINCT будет выполняться агрегирование только уникальных строк. Все повторяющиеся строки будут проигнорированы. По умолчанию выполняется агрегирование всех строк, то есть используется ключевое слово ALL. Далее приведены описания некоторых функций агрегирования.

• AVG () - вычисляет среднее значение для указанного столбца; если группировка не используется, то вычисляет среднее по всему столбцу (слайд 8);

• COUNT () - подсчитывает количество строк в группе (при выполнении группировки) или количество строк результата запроса. Параметр <выражение> в простейшем случае представляет собой имя столбца. Если обрабатываемая строка в соответствующем столбце содержит значение не NULL, то счетчик будет увеличен на единицу. Указание символа (*) предписывает считать общее количество строк независимо от того, содержат они значения NULL или нет (слайд 9);

• МАХ() - возвращает максимальное значение в указанном диапазоне; может использоваться как в обычных запросах, так и в запросах с группировкой (слайд 10);

• MIN() - возвращает минимальное значение в указанном диапазоне (слайд 10);

• SUM() - выполняет обычное суммирование значений в указанном диапазоне; в качестве такого диапазона может рассматриваться группа или весь набор строк (без использования раздела GROUP BY) (слайд 11).

 

Теперь вновь обратимся к разделу SELECT и приведем пример группировки значений таблицы «Учебный_план». Произведем группировку строк по семестрам (столбец Семестр) и подсчитаем общую нагрузку в часах за каждый семестр ( слайд 12 )

В первом столбце выведен номер семестра. Это единственный столбец исходной таблицы, который можно включать в запрос непосредственно, т.к. по нему осуществляется группировка. Во втором столбце с помощью функции агрегирования SUM была получена сумма значений столбца Количество_часов. Функции агрегирования работают со всеми строками группы, возвращая единственное значение для всех этих строк.

Рассмотрим теперь запрос, подсчитывающий количество экзаменов в каждом семестре (слайд 12).

Предложение группировки может содержать ключевое слово ALL. Назначение этого слова следующее. Нередко при выполнении группировки используется раздел WHERE, то есть группировка должна выполняться не над всеми строками, а лишь над определенной частью строк. Результатом такого подхода может явиться то, что одна или более групп не будет содержать ни одной строки. Если группа не содержит ни одной строки, то по умолчанию эта группа не включается в результат выборки. Однако в некоторых ситуациях все же требуется, чтобы были выведены все группы, в том числе и не содержащие ни одной строки. Для этого и необходимо указывать в разделе GROUP BY ключевое слово ALL. В этом случае будет выводиться список всех групп, но для групп, не содержащих строк, не будут выполняться функции агрегирования.

Рассмотрим это на примере. Для начала выполним группировку без использования ключевого слова ALL, но с вертикальной фильтрацией (с помощью раздела WHERE) – в таблице «Учебный_план» посчитаем для каждого семестра количество дисциплин с нагрузкой более 60 часов (слайд 13).

В полученном результате отсутствуют данные для 5, 6 и 7 семестров. Это означает, что дисциплин, удовлетворяющих поставленному условию, в семестрах нет.

Добавим в раздел GROUP BY ключевое слово ALL. Результат представлен на слайде.

Для отбора в результат запроса групп, соответствующих некоторому условию, используется раздел HAVING. Синтаксис раздела совпадает с синтаксисом раздела WHERE.

 

Раздел COMPUTE

Этот раздел предназначен для выполнения групповых операций над содержимым столбцов выборки. Групповые операции задаются с помощью функций агрегирования. Результат агрегирования выводится в отдельной строке после всех данных столбца.

Синтаксис раздела COMPUTE следующий (слайд 14).

Аргумент <столбец_агрегирования>должен содержать имя агрегируемого столбца. Этот столбец должен быть включен в результат выборки. Ключевое слово BY указывает, что результат вычисления следует сгруппировать. Следуемый за этим ключевым словом аргумент <столбец_группировки> содержит имя столбца, по которому будет производиться группировка. Результат необходимо предварительно отсортировать по этому столбцу, то есть столбец должен быть указан в разделе ORDER BY. Приведем простой пример применения раздела COMPUTE для вычисления количества дисциплин, читаемых в семестре, и общей суммы часов (слайд 14).

 

18.4. Раздел INTO. Использование команды SELECT...INTO

При указании этой конструкции результат выполнения запроса будет сохранен в новой таблице. Синтаксис раздела INTO следующий (слайд 15).

Аргумент <имя_новой_таблицы> определяет имя таблицы, в которую будут вставлены результаты.

При выполнении запроса SELECT...INTO автоматически создается новая таблица с нужной структурой и в нее заносится полученный набор строк. При этом в базе данных не должно существовать таблицы, имя которой совпадает с именем таблицы, указанной в команде SELECT...INTO. Если необходимо быстро создать таблицу со структурой, позволяющей сохранить результат выполнения запроса, то лучшим выходом будет использование команды SELECT...INTO.

Синтаксис команды SELECT...INTO представлен на слайде (слайд 15).

Приведенный вариант синтаксиса далеко не исчерпывает все возможности вставки данных с помощью команды SELECT...INTO. Допускаются практическивсе варианты синтаксиса запроса SELECT, то есть можно выполнять группировку, сортировку, объединение и т. д.

Рассмотрим назначение аргументов команды.

<имя_столбца> [[AS] <псевдоним_столбца>]. Аргумент <имя_столбца> задает имя столбца таблицы, который будет включен в результат. Указанный столбец должен принадлежать одной из таблиц, перечисленных в списке FROM {<имя_исходной_таблицы> [,..., n]}. Если столбцы, принадлежащие разным таблицам, имеют одинаковые имена, то для столбцов необходимо использовать псевдонимы. В противном случае произойдет попытка создать таблицу со столбцами, имеющими одинаковые имена, что приведет к ошибке, и выполнение запроса будет прервано. Указание псевдонимов также обязательно для столбцов, значения в которых формируются на основе вычисления выражений (по умолчанию такие столбцы не имеют никакого имени, что недопустимо для таблицы) и когда пользователь хочет задать столбцам в создаваемой таблице новые имена (отличные от исходных). Имя псевдонима задается с помощью параметра <псевдоним_колонки>.

INTO <имя_новой_таблицы>. Аргумент <имя_новой_таблицы> содержит имя создаваемой таблицы. Это имя должно быть уникальным в пределах базы данных.

FROM {<имя_исходной_таблицы> [,..., n]}. В простейшем случае конструкция FROM содержит список исходных таблиц. В более сложных запросах с помощью этой конструкции определяются условия связывания двух и более таблиц.

С помощью команды SELECT...INTO, например, можно разделить таблицу «Студенты» на две, выделив в отдельную таблицу «Контакты» адреса и телефоны, а затем удалив эти столбцы из таблицы «Студенты».

Теперь можно строить запросы для новой таблицы (слайд 16).

Построим внешний ключ для таблицы «Контакты», обеспечив связь с таблицей «Студенты» и модифицируем запрос для таблицы «Контакты». Результат запроса можно видеть на слайде (слайд 16).