В SQL реализованы операторы преобразования данных и встроенные функции, предназначенные для работы со значениями столбцов и\или константами выражений. Использование этих операторов допустимо в запросах везде, где допустимы выражения.
Числовые и символьные константы
Несмотря на то, что SQL работает с данными понятий строк и столбцов таблиц имеется возможность применения значений и выражений построенных с использованием встроенных функций, констант, имен столбцов определяемых как своего рода виртуальные столбцы. Они помещаются в списке столбцов и могут сопровождаться псевдонимами. Если в запросе вместо спецификации столбца SQL обнаруживает число, то оно интерпретируется как числовая константа. Символьные константы должны указываться в одинарных кавычках.
Пример:
Select ‘Фамилия’, surname, ‘Имя’, name, 100
From student;
Арифметические операции для преобразования числовых данных
Можно использовать следующие арифметические операции: унарный минус, плюс, минус, умножить, разделить. Например:
Select surname, name, -(stipend*kurs)/2 as result
From student
Where kurs = 4 and stipend >0;
Операция конкатенации строк
Операция конкатенации || позволяет соединять значения двух или более столбцов символьного типа или символьных констант в одну строку. Пример:
Select surname || ‘_’|| name
From student;
Агрегирование и групповые функции
Агрегирующие функции позволяют получать из таблицы сводную информацию, выполняя операции над группой строк таблицы. Для задания в SELECT-запросе агрегирующих операций используют следующие ключевые слова:
1. Count – определяет количество строк или значений поля выбранных посредством запроса и не являющихся null-значениями.
|
|
2. Sum – вычисляет арифметическую сумму всех выбранных значений поля.
3. Avg – вычисляет среднее значение для всех выбранных значений поля.
4. Min – вычисляет наименьшее из всех выбранных значений поле.
5. Max – вычисляет наибольшее их всех выбранных значений поле.
В SELECT-запросе агрегирующие функции используются аналогично именам полей, при этом имена полей используются в качестве аргументов этих функций. Пример: определить среднее значение поля mark по всем записям таблицы exam_mark:
Select avg(mark)
From exam_mark;
Посчитать количество строк в таблице:
Select count(*)
From exam_mark;
Аргументы distinct и all позволяют соответственно исключать и включать дубликаты обрабатываемых функцией count значений.
Предложение Group by позволяет группировать записи в подмножества определяемые значениями какого-либо поля применять агрегирующие функции уже не ко всем записям таблицы, а раздельно к каждой сформированной группе. Пример: найти максимальное значение оценки полученной в результате экзаменов каждым студентом.
Select student_id, max(mark)
From exam_mark
Group by student_id;
В конструкции Group by для группирования может быть использовано более одного столбца.
Select student_id, max(mark), subject_id
From exam_mark
Group by student_id, subject_id;
Таким образом, предложение Group be не только устанавливает столбцы, по которым осуществляется группирование, но и указывает порядок разбиения столбцов на группы.
При необходимости часть сформированных с помощью Group by групп может быть исключена с помощью предложение Having. Предложение Having определяет критерий по которому в группы следует включать выходные данные по аналогии с предложением Where? Которое осуществляет это для отдельных строк. Пример:
|
|
Select subj_name, max(hour)
From subject
Group by subject_name
Having max(hour)>=72;
Замечание: в условии, задаваемом предложением Having, указывают только поля или выражения, которые на выходе имеют единственное значение для каждой выводимой группы.
Упорядочение выходных полей
Записи в реляционной БД не упорядочены. Однако в результате выполнения запроса данные можно упорядочить. Для этого используется оператор Order by, который позволяет упорядочивать выводимые записи в соответствии со значениями одного или нескольких выбранных столбцов. При этом можно задать возрастающую (asc) или убывающую (desc) последовательность сортировки. Пример: выбрать все данные из таблицы, упорядочив их по названию предмета.
Select *;
From subject
Order by subj_name;
Упорядочить выводимый список предметов обучения по значениям семестра, а внутри семестра по наименованию предмета.
Select *
From subject
Order by semester, subj_name;
Предложение Order by может использоваться для упорядочивания групп записей, при этом оператор Order by в запросе всегда должен быть последним.
Select subj_name, semester, max(hour)
From subject
Group by semester, subj_name
Order by semester;
При упорядочении вместо наименований столбцов можно указывать их номера, однако в данном случае это номера столбцов указанные при определении выходных данных в запросе, а не номера столбцов в таблице.
Например: упорядочить данные по полю семестр по убыванию.
Select subj_id, semester
From subject
Order by 2 desc;
Если в поле которое используется для упорядочения существует null-значение, то все они размещаются в конце или предшествуют всем остальным значениям этого поля.
|
|
Вложенные подзапросы
SQL позволяет использовать одни запросы внутри других, т.е. запросы вкладываются друг в друга. Пример: известна фамилия студента (Петров), но неизвестно значение его идентификатора. Чтобы извлечь все данные об оценках этого студента нужно записать следующий запрос:
Select *
From exam_marks
Where student_id =
(Select student_id
From student
Where surname = ‘Петров’);
Алгоритм работы запроса SQL со связанным подзапросом:
1. Выбирается строка из таблицы, имя которой указано во внешнем запросе;
2. Выполняется подзапрос и полученное значение применяется для анализа этой строки в условии предложения Where внешнего запроса;
3. По результату оценки этого условия принимается решение о включении или не включении строки в состав выходных данных;
4. Процедура повторяется для следующей строки таблицы внешнего запроса.
В некоторых случаях для гарантии получения единственного значения выполнения подзапроса используется distinct. Одним из видов функций, которые автоматически возвращает в результате единственное значение для любого количества строк, являются агрегирующие функции. Оператор IN также широко применяется в подзапросах. Он задает список значений с которыми сравниваются другие значения для определения истинности задаваемого этим оператором предиката. Пример: данные об оценках всех студентов из Воронежа.
Select *
From exam_marks
Where student_id in
(Select student_id
From student
Where city = ‘Воронеж’);
Подзапросы можно применять внутри предложения Having. Пример: определить количество предметов обучения с оценкой превышающей среднее значение оценки студента с идентификатором 301.
Select count distinct(subj_id), mark
From exam_marks
Group by mark
Having mark >
(Select avg(mark)
From exam_marks
Where student_id = 301);
Формирование связанных подзапросов
При использовании подзапросов во внутреннем запросе можно ссылаться на таблицу, имя которой указано в предложении From внешнего запроса. В этом случае такой связанный подзапрос выполняется по одному разу для каждой строки таблицы основного запроса. Пример: выбрать сведения обо всех предметах обучения, по которым проводился экзамен 20 января 2012 года:
Select *
From subject as su
Where ‘20/01/2012’ in
(Select exam_date
From exam_marks as ex
Where su.subj_id = ex.subj_id);
В некоторых СУБД для выполнения этого запроса может потребоваться преобразование значения даты в символьный тип. В приведенном запросе ex и su являются псевдонимами, т.е. специально вводимыми именами, которые могут быть использованы вместо настоящих имен. Эту же операцию можно записать при помощи соединения таблиц.
Select su.subj_id, subj_name, hour, semester
From subject as su, exam_marks as ex
Where su.subj_id = ex.subj_id
And exam_date = #20/01/2012
Можно использовать подзапросы связывающие таблицу со своей собственной копией. Пример: найти студентов, которые получают стипендию выше средней на курсе.
Select distinct student_id, surname, name, stripend
From student as E1
Where stipend >
(Select avg(stipend)
From student as E2
Where E1.kurs = E2.kurs);
Select distinct student_id, surname, name, stripend
From student as E1,
(Select kurs, avg(stipend) as avg_stipend
From student as E2
Group by E2.kurs) as E3
Where E1.stipend > avg_stipend
And E1.kurs = E3.kurs;
Второй запрос будет выполнен гораздо быстрее т.к. в первом запросе агрегирующая функция AVG выполняется над таблицей указанной в подзапросе для каждой строки внешнего запроса. Во втором варианте вторая таблица алиас E2 обрабатывается агрегирующей функцией один раз в результате чего формируется вспомогательная таблица алиас E3 со строками которой затем соединяются строки первой таблицы (алиас E1).
Связанные подзапросы с предложением Having
Пример: по данным из таблицы exam_marks определить сумму полученных студентами оценок сгруппировав значения оценок по датам экзаменов и исключить те дни когда число студентов сдавших в течение дня экзамены было меньше 10.