Выборка данных. Оператор select (dql)

 

Одной из основных задач, которую решают базы данных – это эффективный поиск необходимых данных. Универсальным подходом для решения этой задачи является применение специального оператора языка SQL - SELECT. Этот оператор достаточно сложный, имеет множество возможностей. Теоретической основой этого оператора является реляционная алгебра, которая доказывает возможность получения с помощью конечного набора операций любых возможных наборов данных.

Приведем несколько примеров запросов к таблицам базы данных, демонстрирующих различные возможности языка SQL и различные операции реляционной алгебры. Результаты будем показывать из различных вариантов реализации учебного проекта баз данных (при выполнении на различных СУБД). 

Вспомним, что главным отличием синтаксиса команд SQL для PostgreSQL является заключение в кавычки имен таблиц, столбцов и пр. В следующих примерах будем приводить текст запроса в стиле MS SQL Server или MySQL. В случаях более серьезной разницы в записи запросов, будем приводить его текст для каждого СУБД в отдельности.

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

Распечатать ФИО всех студентов, зарегистрированных в базе данных: SELECT FIOStudent FROM Students;

Рис. 23. Результат выполнения запроса для MS SQL Server.

 

Запрос 2. Операция селекции. Осуществляется горизонтальная выборка – в результат попадают только записи, удовлетворяющие условию. 

Распечатать ФИО студентов группы 902.

SELECT * FROM Students WHERE NumGroup='902';

Рис. 24. Результат выполнения запроса для MySQL.

 

Запрос 3. Операции соединения. Здесь следует выделить декартово произведение и на его основе соединение по условию, а также естественное соединение (по одноименным полям или равенству полей с одинаковым смыслом). 

Распечатать список зачетов и экзаменов, которые будут сдавать студенты группы 901 в первом семестре.

С помощью декартового произведения и соединения по условию данный запрос запишется так:

SELECT TitleSubject, Zach_Exam FROM Sessions, Subjects 

               WHERE Sessions.NumGroup='901' AND 

     Sessions.idSubject=Subjects.idSubject AND 

     Sessions.NumSemestr=1;

В этом запросе впервые мы выбираем информацию из нескольких таблиц. В случае использования одноименных полей следует дополнить их именами таблиц согласно схеме ИмяТаблицы.ИмяПоля. В случае же записи для СУБД PostgreSQL каждое из имен должно быть записано в кавычках:

SELECT "TitleSubject", "Zach_Exam" FROM "Sessions", "Subjects" 

  WHERE "Sessions"."NumGroup"='901' AND 

  "Sessions"."idSubject"="Subjects"."idSubject" AND 

  "Sessions"."NumSemestr"=1;

 

Рис. 25. Результат выполнения запроса для PostgreSQL.

 

Этот же запрос с помощью операции внутреннего соединения имеет следующий вид (соединение производится по равенству одноименных атрибутов idSubject таблиц Sessions и Subjects):

SELECT TitleSubject, Zach_Exam FROM Sessions INNER JOIN Subjects 

  ON Sessions.idSubject=Subjects.idSubject

  WHERE Sessions.NumGroup='901' AND Sessions.NumSemestr=1; 

Запрос 4. Операция объединения. Теоретико-множественные операции часто можно записать с помощью логических операций, примененных в конструкции WHERE запроса. Например, нужно получить список зачетов и экзаменов, которые сдают студенты 901 или 902 групп в 1 семестре. Таким образом, нужно объединить два множества, соответствующие двум разным группам. Объединение можно задать с помощью логического ИЛИ.

SELECT NumGroup, TitleSubject, Zach_Exam FROM 

   Sessions INNER JOIN Subjects 

   ON Sessions.idSubject=Subjects.idSubject

   WHERE Sessions.NumSemestr=1 AND

   (Sessions.NumGroup='901' OR Sessions.NumGroup='902'); 

Аналогичный результат будет получен с помощью объединения результатов двух запросов (подзапросов) с одинаковой структурой результата:

(SELECT NumGroup, TitleSubject, Zach_Exam FROM Sessions 

INNER JOIN Subjects ON Sessions.idSubject=Subjects.idSubject

WHERE Sessions.NumSemestr=1 AND Sessions.NumGroup='901') 

UNION 

(SELECT NumGroup, TitleSubject, Zach_Exam FROM Sessions 

INNER JOIN Subjects ON Sessions.idSubject=Subjects.idSubject  

WHERE Sessions.NumSemestr=1 AND Sessions.NumGroup='902');

 

Рис. 26. Результат выполнения запроса для MS SQL Server.

 

Запрос 5. Операция пересечения. В простых случаях эту операцию можно описать с помощью логической операции AND. В более сложных случаях эта операция определяется чаще всего с помощью подзапроса и ключевого слова EXISTS, которое показывает наличие похожего элемента во множестве, которое задается подзапросом. 

Найти тех преподавателей, которым должны сдавать зачеты или экзамены в первом семестре студенты 901 и 902 групп. Отметим необходимость применения здесь операции переименования (AS) для того, чтобы различить два экземпляра таблицы Sessions (из основного запроса и подзапроса).

SELECT FIOTeacher FROM Teachers INNER JOIN Sessions 

  ON Teachers.idTeacher=Sessions.idTeacher

  WHERE Sessions.NumSemestr=1 AND Sessions.NumGroup='901' 

  AND EXISTS (SELECT * FROM Sessions as s1 

  WHERE s1.idTeacher=Sessions.idTeacher AND s1.NumSemestr=1    AND s1.NumGroup='902');

Рис. 27. Результат выполнения запроса для MySQL.

 

Запрос 6. Операция разности. Эта операция также определяется часто с помощью подзапроса с ключевым словом NOT EXISTS, которое показывает отсутствие элемента во множестве, задаваемом подзапросом. Приведем аналогичный предыдущему пример. 

Найти тех преподавателей, которым должны сдавать зачеты или экзамены в первом семестре студенты 901 группы, но не студенты из 902 группы. 

SELECT FIOTeacher FROM Teachers INNER JOIN Sessions 

  ON Teachers.idTeacher=Sessions.idTeacher

  WHERE Sessions.NumSemestr=1 AND Sessions.NumGroup='901' 

  AND NOT EXISTS (SELECT * FROM Sessions as s1 

  WHERE s1.idTeacher=Sessions.idTeacher AND s1.NumSemestr=1 

  AND s1.NumGroup='902');

 

Рис. 28. Результат выполнения запроса для PostgreSQL.

 

Запрос 7. Операция группировки. Эта операция связана со своеобразной сверткой таблицы по полям группировки. Помимо полей группировки результат запроса может содержать итоговые агрегирующие функции по группам (COUNT, SUM, AVG, MAX, MIN).

Найти итоговое количество зачетов и экзаменов, которые должны сдавать студенты различных групп в 1 семестре. 

Операция группировки здесь будет применяться к таблице Sessions. Полем группировки является номер группы. Агрегирующим полем является количество строк с заданной группой и номером семестра.

SELECT NumGroup, COUNT(*) AS kolvo FROM Sessions 

            WHERE NumSemestr=1 GROUP BY NumGroup;

Рис. 29. Результат выполнения запроса для MS SQL Server.

 

В случае, когда при группировке имеется условие отбора на группу, используется конструкция HAVING. Например, в предыдущем примере нам нужно вывести только те группы, в которых количество зачетов-экзаменов равно 3.

SELECT NumGroup, COUNT(*) AS kolvo FROM Sessions 

  WHERE NumSemestr=1 GROUP BY NumGroup HAVING COUNT(*)=3;

 

В СУБД MySQL возможно в условии в конструкции HAVING использовать псевдоним агрегирующего столбца kolvo.

 

Запрос 8. О перация сортировки. Вывести всех преподавателей, которым сдают студенты зачеты-экзамены в первом семестре, в порядке убывания количества зачетов-экзаменов. Для этого следует сначала выбрать нужные элементы таблицы Sessions, затем осуществить естественное соединение полученной таблицы с таблицей Teachers, после чего производится группировка записей в результате запроса и последующая сортировка.

SELECT FIOTeacher, COUNT(*) AS kolvo FROM sessions 

  INNER JOIN teachers 

  ON sessions.teachers_idTeacher=teachers.idTeacher

  WHERE sessions.NumSemestr=1 

  GROUP BY FIOTeacher ORDER BY kolvo;

 

Рис. 30. Результат выполнения запроса для MySQL.

 

Запрос 9. Операция деления. Это самая нетривиальная операция реляционной алгебры, которая обычно применяется тогда, когда требуется найти все записи первой таблицы, которые соединяются естественным образом со всеми записями второй таблицы. Например, нам требуется найти тех преподавателей, которым должны сдать в первом семестре зачеты-экзамены студенты всех групп факультета. Запрос получается достаточно сложный и он связан с выполнением двух операций разности (первая разность - из всевозможных комбинаций групп и преподавателей вычитаются реальные комбинации этих полей, т.е. результатом становятся всевозможные нереальные пары, вторая разность – выбираются преподаватели, которые в нереальных парах не присутствуют). 

SELECT FIOTeacher FROM Teachers WHERE idTeacher IN 

(SELECT DISTINCT s0.idTeacher FROM Sessions AS s0 

      WHERE NumSemestr=1 AND 

      NOT EXISTS (SELECT DISTINCT s1.idTeacher, s2.NumGroup

                  FROM Sessions AS s1, Sessions AS s2 

                  WHERE s1.NumSemestr=1 AND s2.NumSemestr=1 

                  AND NOT EXISTS (SELECT * FROM Sessions                       AS s3 WHERE s3.idTeacher=s1.idTeacher AND                       s3.NumGroup=s2.NumGroup) 

                  AND s1.idTeacher=s0.idTeacher)); 

 

Рис. 31. Результат выполнения запроса для PostgreSQL.

 

Разберем этот запрос по частям. Все возможные пары «преподаватель» «группа» получаются с помощью подзапроса:

SELECT DISTINCT s1.idTeacher, s2.NumGroup

           FROM Sessions AS s1, Sessions AS s2             WHERE s1.NumSemestr=1 AND s2.NumSemestr=1 добавлением к нему условия: 

NOT EXISTS (SELECT * FROM Sessions AS s3 

WHERE s3.idTeacher=s1.idTeacher AND s3.NumGroup=s2.NumGroup) 

 

из всевозможных пар вычитаются реальные пары, т.е. в результате полу-

чаем все возможные нереальные пары «преподаватель»-«группа». Результат этого подзапроса внедряется в другой подзапрос, получающий тех преподавателей, коды которых не присутствуют в этом списке. Далее подключением таблицы Teachers получаем их ФИО. 

 

 

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

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

CREATE VIEW Student_Session 

AS 

SELECT FIOStudent, TitleSubject FROM Students INNER JOIN Sessions 

  ON Students.NumGroup=Sessions.NumGroup INNER JOIN Subjects 

  ON Subjects.idSubject=Sessions.idSubject 

  WHERE NumSemestr=1;

 

Далее обратимся к этому представлению как к таблице. Например, найти те зачеты-экзамены, которые должен сдать студент Иванов:

SELECT TitleSubject FROM Student_Session 

                WHERE FIOStudent LIKE 'Иванов%';