3. Операции над данными и null

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

Для строковых типов определены операции сравнения, конкатенации (соединения) и ряд встроенных функций, позволяющих определить длину строки, выделить подстроку и т.п.

Как указывалось ранее, в базах данных введено специальное значение NULL – пустота или неопределенное значение. Данное значение не является типизированным и является допустимым значением для любого типа данных.

Операции над значениями NULL имеют свою специфику:

• если хотя бы один из операндов любой арифметической операции имеет неопределенное значение NULL, то результат операции всегда принимает неопределенное значение NULL;

• аналогично, если хотя бы дин из операндов любой операции сравнения имеет неопределенное значение NULL, то результат операции всегда принимает неопределенное значение Unknown;

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

Для корректной проверки значения на равенство его неопределенному значению NULL в SQL определена специальная операция сравнения с NULL.

4. Выбор данных из базы

Получение практически любого количества данных из одной или нескольких таблиц выполняется с помощью единственной команды SELECT. В общем случае, результатом реализации команды SELECT является другая таблица. К этой новой (рабочей) таблице может быть снова применена операция SELECT и т.д., т.е. такие операции могут быть вложены друг в друга. Результаты отдельных команд SELECT могут быть объедены в одну таблицу операциями объединения (UNION), пересечения (INTERSECT) и вычитания (MINUS). Важно помнить, что при использовании операций объединения нескольких одиночных запросов сортировка результатов задается только для результирующей таблицы.

При описании синтаксиса SQL‑команд, используются следующие обозначения [13]:

• звездочка (*) для обозначения «все» – употребляется в обычном для программирования смысле, т.е. «все случаи, удовлетворяющие определению»;

• квадратные скобки ([]) – означают, что конструкции, заключенные в эти скобки, являются необязательными (т.е. могут быть опущены);

• фигурные скобки ({}) – означают, что конструкции, заключенные в эти скобки, должны рассматриваться как целые синтаксические единицы;

• многоточие (...) – указывает на то, что непосредственно предшествующая ему синтаксическая единица факультативно может повторяться один или более раз;

• прямая черта (|) – означает наличие выбора из двух или более возможностей. Например обозначение ASC|DESC указывает, можно выбрать один из терминов ASC или DESC; когда же один из элементов выбора заключен в квадратные скобки, то это означает, что он выбирается по умолчанию (так, [ASC]|DESC означает, что отсутствие всей этой конструкции будет восприниматься как выбор ASC);

• точка с запятой (;) – завершающий элемент предложений SQL;

• запятая (,) – используется для разделения элементов списков;

• прописные латинские буквы и символы – используются для написания конструкций языка SQL и должны (если это специально не оговорено) записываться в точности так, как показано;

• строчные буквы – используются для написания конструкций, которые должны заменяться конкретными значениями, выбранными пользователем, причем для определенности отдельные слова этих конструкций связываются между собой символом подчеркивания (_).

При вызове команды SELECT используются ключевые слова, которые позволяют указать условия для выбора нужных данных и (если требуется) их обработки:

SELECT

(выбрать) данные из указанных столбцов и (если необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями

FROM

(из) перечисленных таблиц, в которых расположены эти столбцы

WHERE

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

GROUP BY

(группируя по) указанному перечню столбцов с тем, чтобы получить для каждой группы единственное агрегированное значение, используя во фразе SELECT SQL-функции SUM (сумма), COUNT (количество), MIN (минимальное значение), MAX (максимальное значение) или AVG (среднее значение)

HAVING

(где) группы, сформированные в разделе GROUP BY, должны удовлетворять указанному перечню отбора групп

ORDER BY

(упорядочить по) указанному перечню столбцов результаты выбора данных. При этом упорядочение можно производить в порядке возрастания – ASC (ASCending) или убывания DESC (DESCending), а по умолчанию принимается ASC.

Команда SELECT имеет формат:

SELECT [[ALL] | DISTINCT]{ * | элемент_SELECT [,элемент_SELECT] ...} FROM источник_данных [псевдоним] [,источник_данных [псевдоним]] ... [WHERE фраза] [GROUP BY фраза]

[HAVING фраза] [ORDER BY столбец_ORDER_BY [ASC|DESC] [,столбец_ORDER_BY [ASC|DESC]] ...];

Элемент_SELECT – это одна из следующих конструкций:

[таблица.]* | значение | SQL_функция | системная_переменная

где значение – это:

[таблица.]столбец | (выражение) | константа | переменная

Синтаксис выражений имеет вид

( {[ [+] | - ] {значение | функция_СУБД} [ + | - | * | ** ]}... )

а синтаксис SQL_функций – одна из следующих конструкций:

{SUM|AVG|MIN|MAX|COUNT} ( [[ALL]|DISTINCT][таблица.]столбец ) {SUM|AVG|MIN|MAX|COUNT} ( [ALL] выражение ) COUNT(*)

Источник_данных – это один из следующих элементов: {базовая_таблица | представление | запрос}

Фраза WHERE включает набор условий для отбора строк:

WHERE [NOT] WHERE_условие [[AND|OR][NOT] WHERE_условие]...

где WHERE_условие – одна из следующих конструкций:

значение { = | <> | < | <= | > | >= } { значение | ( подзапрос ) } значение_1 [NOT] BETWEEN значение_2 AND значение_3 значение [NOT] IN { ( константа [,константа]... ) | ( подзапрос ) } значение IS [NOT] NULL [таблица.]столбец [NOT] LIKE 'строка_символов' [ESCAPE 'символ'] EXISTS ( подзапрос )

Кроме традиционных операторов сравнения (= | <> | < | <= | > | >=) в WHERE фразе используются условия BETWEEN (между), LIKE (похоже на), IN (принадлежит), IS NULL (не определено) и EXISTS (существует), которые могут предваряться оператором NOT (не). Критерий отбора строк формируется из одного или нескольких условий, соединенных логическими операторами:

AND

когда должны удовлетворяться оба разделяемых с помощью AND условия;

OR

когда должно удовлетворяться одно из разделяемых с помощью OR условий;

AND NOT

когда должно удовлетворяться первое условие и не должно второе;

OR NOT

когда или должно удовлетворяться первое условие или не должно удовлетворяться второе.

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

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

Синтаксис фразы GROUP BY имеет вид

GROUP BY [таблица.]столбец [,[таблица.]столбец] ...

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

Фраза HAVING включает набор условий для отбора групп, сформированных в фразе GROUP. Фраза аналогична фразе WHERE, однако в условиях применяются агрегирующие функции (COUNT, SUM, AVG, и т.п.), которые рассчитываются для каждой группы отдельно.

И, наконец, столбец_ORDER_BY – это

[таблица.]столбец | переменная | выражение.

Работа отдельной команды SELECT может быть описана следующей последовательностью шагов:

• Шаг 1. Формируется таблица A, как декартово произведение таблиц, указанных после ключевого слова FROM.

• Шаг 2. Формируется таблица B, в которую попадают строки таблицы A, удовлетворяющие условию, указанному после ключевого слова WHERE. Если ключевое слово WHERE пропущено, то в B попадают все строки из A.

• Шаг 3. Формируется таблица C, состоящая из строк таблицы B, но «сгруппированных» согласно фразе указанной после ключевого слова GROUP BY. Если фраза GROUP BY отсутствует, то все строки таблицы C принадлежат одной группе.

• ШАГ 4. Формируется таблица D, в которую попадают только те строки таблицы C, для групп которых выполняется условие, указанное после ключевого слова HAVING. Если ключевое слово HAVING пропущено, то в D попадают все строки из C.

• Шаг. 5. Формируется результирующая таблица, состоящая из столбцов, указанных после ключевого слова SELECT, на основе таблицы D. Сначала вычисляются скалярные значения столбцов. Если используются агрегирующие функции, то для каждой группы вычисляются их значения. В этом случае среди скалярных столбцов могут быть использованы только те, которые указаны после ключевого слова GROUP BY, т.е. которых для всех строк конкретной группы будут гарантированно одинаковые значения.

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

Пример 1. Запрос, результатом, которого является таблица, содержащая столбцы ФИО пассажира и паспортные данные:

SELECT Пассажиры.ФИО, Пассажиры.Паспортные_данные

FROM Пассажиры

Пример 2. Запрос, выводящий на экран содержимое таблицы «РЕЙСЫ»:

SELECT Рейсы.*

FROM Рейсы

Пример 3. Запрос, выводящий на экран список пунктов прибытия, упорядоченный по алфавиту:

SELECT Пункты.Пункт_прибытия

FROM Пункты

ORDER BY Пункты.Пункт_прибытия

Пример 4. Запрос, выводящий на экран упорядоченные по алфавиту фамилии пассажиров, серия паспорта у которых ‘X-AA’:

SELECT Пассажиры.ФИО

FROM Пассажиры

WHERE Пассажиры.Паспортные_данные LIKE ‘X-AA%’

ORDER BY Пассажиры.ФИО

В данном примере используется слово LIKE. Обычная форма "имя_столбца LIKE текстовая_константа" для столбца текстового типа позволяет отыскать все значения указанного столбца, соответствующие образцу, заданному "текстовой_константой". Символы этой константы интерпретируются следующим образом:

• символ _ (подчеркивание) – заменяет любой одиночный символ,

• символ % (процент) – заменяет любую последовательность из N символов (где N может быть нулем),

• все другие символы означают просто сами себя.

Пример 5. Запрос, выводящий на экран список рейсов (номер рейса) и пунктов прибытия этих рейсов:

SELECT Рейсы.№Рейса, Пункты.Пункт_прибытия

FROM Рейсы, Пункты

WHERE Рейсы.№Пункта = Пункты.№Пункта

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

Пример 5. Запрос, формирующий временную таблицу, содержащую фамилию пассажира, номер рейса, дату вылета и реальную стоимость билета с учетом скидки. В таблицу должны попасть только строки с реальной стоимостью билета менее 1000.

SELECT Пассажиры.ФИО, Рейсы.№Рейса, Авиабилеты.Дата_вылета, Рейсы.Цена_билета*(100-Пассажиры.Скидка%)/100 “Реальная_цена”

FROM Рейсы, Авиабилеты, Пассажиры

WHERE Рейсы.№Рейса = Авиабилета.№Рейса AND

Авиабилета.№Пассажира = Пассажиры.№Пассажира AND

Рейсы.Цена_билета*(100-Пассажиры.Скидка%)/100<1000

Данные для запроса размещаются в трех таблицах, поэтому все они указаны после ключевого слова FROM, и в разделе WHERE указаны два условия, соответствующие связям между таблицами. Добавлено условие по реальной цене билета. Реальная цена билета вычисляется через поля таблиц «РЕЙСЫ» и «ПАССАЖИРЫ».

Данный пример, помимо иллюстрации выполнения вычислений в запросе, показывает, как задать имя столбца в результирующей таблице. По умолчанию каждому столбцу задаётся имя, соответствующее имени, используемому в запросе. Таким образом, если бы для последнего столбца не было бы определено имя “Реальная цена”, он назывался бы “Рейсы.Цена_билета*(100-Пассажиры.Скидка%)/100”.

Пример 6. Запрос, формирующий список марок самолетов с указанием числа перелетов-рейсов, выполненных каждой маркой самолета:

SELECT Марки.Марка_Cамолета,

count(*) “Клоичество_перелетов”

FROM Марки, Рейсы, Авиабилеты

WHERE Рейсы.№Рейса = Авиабилета.№Рейса AND

Марки.№Марки = Рейсы.№Марки AND

GROUP BY Марки.Марка_Самолета

Данный пример демонстрирует применение агрегирующей функции подсчета числа строк с группировкой результатов. Как указывалось при описании алгоритма работы команды SELECT, в результирующей таблице возможно использовать скалярное поле Марки.Марка_Cамолета только потому, что оно указано в фразе GROUP BY. Указать другое поле невозможно.