3.1. Создание сложных запросов

Использование функций в запросах

Запросы с использованием арифметических функций

SQL поддерживает полный набор арифметических операций и математических функций для построения арифметических выражений над колонками БД. (+, -, *, /, ABS, LN, SQRT и т.д.). Список основных встроенных математических функций дан ниже.

Математические функции SQL

ABS(X) - Возвращает абсолютное значение числа Х

ACOS(X) - Возвращает арккосинус числа Х

ASIN(X) - Возвращает арксинус числа Х

ATAN(X) - Возвращает арктангенс числа Х

COS(X) - Возвращает косинус числа Х

EXP(X) - Возвращает экспоненту числа Х

LN(X) - Возвращает натуральный логарифм числа Х

MOD(X,Y) - Возвращает остаток от деления Х на Y

ROUND(X ,n) - Округляет число Х до числа с n знаками после десятичной точки

SIN(X) - Возвращает синус числа Х

SQRT(X) - Возвращает квадратный корень числа Х

TAN(X) - Возвращает тангенс числа Х

LOG(a, X) - Возвращает логарифм числа Х по основанию А

SINE(X) - Возвращает гиперболический синус числа Х

COSE(X) - Возвращает гиперболический косинус числа Х

TANE(X) - Возвращает гиперболический тангенс числа Х

TRANC(X, n) - Усекает число Х до числа с n знаками после десятичной точки

POWER(A,X) - Возвращает значение А, возведенное в степень Х

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

SELECT LAST_NAME, FIRST_NAME, SALARY, COMMISSION_PCT, SALARY + COMMISSION_PCT* SALARY
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID;

Арифметическое выражение SALARY + COMMISSION_PCT* SALARY выводится как новая колонка в результирующей таблице, которая вычисляется в результате выполнения запроса. Такие колонки называют еще производными (вычисляемыми) атрибутами или полями.

Если значение колонки COMMISSION_PCT равна NULL, то сумма в производной колонке то же будет равна NULL. Для того, чтобы в производной атрибут имел значение SALARY в случае если значение COMMISSION_PCT не определено, можно воспользоваться условным CASE-выражением и встроенной логической функцией IS NULL.

Синтаксис условного CASE-выражения имеет вид:

CASE
  WHEN условное-выражение1 THEN выражение-результат1
  WHEN условное-выражение2 THEN выражение-результат2
  …
  WHEN условное-выражение N THEN выражение-результат N
  [ ELSE выражение-результат ]
END

Проверки происходят сверху вниз, пока первое по порядку условное-выражение I не станет TRUE. Тогда проверки прекратятся, и результатом CASE будет значение выражения-результат I.

Модифицированный запрос будет выглядеть как показано ниже.

SELECT LAST_NAME, FIRST_NAME, SALARY, CASE WHEN COMMISSION_PCT IS NULL THEN SALARY ELSE SALARY + COMMISSION_PCT*SALARY END
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID;

Запросы с использованием строковых функций

SQL предоставляет вам широкий набор функций для манипулирования со строковыми данными. Список основных функций для обработки строковых данных приведен ниже.

Функции SQL для обработки строк

CHR(N) - Возвращает символ ASCII кода для десятичного кода N

ASCII(S) - Возвращает десятичный ASCII код первого символа строки

INSTR(S2, S1, pos [, n]) - Возвращает позицию строки S1 в строке S2, большую или равную pos. n – число вхождений.

LENGTH(S) - Возвращает длину строки

LOWER(S) - Заменяет все символы строки на прописные

INITCAP(S) - Устанавливает первый символ каждого слова в строке на заглавный, а остальные на прописные.

NVL(X,Y) - Если Х есть NULL, то возвращает в Y либо строку, либо число, либо дату в зависимости от исходного типа Y

SUBSTR(S,pos,[,len]) - Выделяет в строке S подстроку длиной len, начиная с позиции pos

UPPER(S) - Преобразует прописные буквы в строке на заглавные буквы

LPAD(S,N [,A]) - Возвращает строку S, дополненную слева символами А до числа символов N. Символ наполнитель по умолчанию — пробел.

LPAD(S,N [,A]) - Возвращает строку S, дополненную справа символами А до числа символов N. Символ наполнитель по умолчанию — пробел.

LTRIM(S,[,S1]) - Возвращает усеченную слева строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку — шаблон S1 (по умолчанию — пробел).

RTRIM(S,[,S1]) - Возвращает усеченную справа строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку — шаблон S1 (по умолчанию — пробел).

TRANSLATE(S,S1,S2) - Возвращает строку S, в которой все вождения строки S1 замещены строкой S2. Если S1<>S2, то символы, которым нет соответствия, исключаются из результирующей строки.

REPLACE(S,S1,[,S2]) - Возвращает строку S, для которой все вхождения подстроки S1 замещены на подстроку S2. Если S2 не указано, то все вхождения подстроки S1 удаляются из результирующей строки S.

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

SELECT INITCAP (LAST_NAME)
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID;

Запросы, использующие логические и специальные функции

SQL обеспечивает набор логических и специальных функций для обработки значений колонок.

Логические и специальные функции

S IS NULL / S IS NOT NULL - Возвращает значение TRUE, если значение параметра S является неопределенным, в противном случае – FALSE. И наоборот.

DECODE(E,S1,R1,S2,R2, ...,[def]) - Если Е соответствует S_i , то возвращается R_i, в противном - def или NULL, если умолчание не задано.

TO_NUMBER(S) - Возвращает результат преобразования строки S в аргумент типа NUMBER.

TO_CHAR(X[,F]) - Возвращает результат преобразования строки S в аргумент типа DATE согласно заданному формату даты F.

TO_DATE(S[,F]) - Возвращает результат преобразования значения параметра S символьного типа в аргумент типа DATE.

Пример использования логической функции IS NULL приведен выше.

В качестве примера использования функции DECODE приведем запрос, вычисляющий список служащих с указанием их определенного руководителя. Если руководитель не указан, то выводится по умолчанию “не имеет”.

SELECT LAST_NAME, DECODE (MANAGER_ID, 100, 'King',  'не имеет')
FROM EMPLOYEES
ORDER BY LAST_NAME;

Запросы, требующие обработки даты

SQL имеет набор функций для манипулирования колонками с типом date. Список основных функций обработки даты и времени приведен ниже.

Функции обработки даты и времени

ROUND(D [,F}) - Округляет значение даты D согласно заданному шаблону.

TRUNC(D [,F]) - Усекает значение даты D согласно заданному шаблону.

NEXT_DAY(D, S) - Возвращает дату дня, который является первым днем, более поздним, чем текущая дата с названием S.

SYSDATE - Возвращает текущую дату и время.

ADD_MONTHS(d, x) - Возвращает дату, полученную в результате прибавления к дате d одного или нескольких месяцев. Количество месяцев задается параметров х, причем х может быть отрицательным.

LAST _DAY(d) - Возвращает последнее число месяца, указанного в дате d.

MONTHS_BETWEEN(dl, d2) - Возвращает количество месяцев между двумя датами dl и d2 с учетом знака как dl-d2, возвращаемое число является дробным.

CURRENT_TIMESTAMP - Возвращает текущее время дата со значениями часового пояса.

Когда вы выбираете колонку HIRE_DATE, она выводится в стандартном формате DD.MM.YY (03.10.04). Выполнение запроса

SELECT LAST_NAME, HIDE_DATE 
FROM EMPLOYEES   
WHERE DEPARTMENT_ID =80;

приведет к построению следующего результирующего множества (фрагмент)

LAST_NAME 		HIDE_DATE
Russell 		01.10.04
Partners		05.01.05
Errazuriz		10.03.05
…

В Oracle существует DUAL. Таблица DUAL - это таблица в схеме SYS, содержащая только одну запись. Эта таблица часто используется для тестирования результатов некоторых запросов. Так запрос

SELECT SYSDATE 
FROM dual;

покажет текущую дату.

SYSDATE 
06.07.19

Запрос

SELECT SYSDATE d, 
ADD_MONTHS(SYSDATE, 3) d1, ADD_MONTHS(SYSDATE, -3) d2 
FROM dual;

покажет

D		D1		D2
06.07.19	06.10.19	06.04.19

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

SELECT LAST_NAME, HIRE_DATE, TRUNC (MONTHS_BETWEEN (SYSDATE, HIRE_DATE))  
FROM EMPLOYEES;

Функция SYSDATE всегда возвращает текущую дату. В этом примере показано, как используется функции для работы с переменными типа дата.

3.2. Использование агрегатных функций в запросах

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

Агрегатные функции

AVG(X) = AVG(ALL X); AVG(DISTINCT X) - Вычисляет среднее значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты

COUNT(*); COUNT(X) = COUNT(ALL X); COUNT(DISTINCT X) - Вычисляет число итемов. При указании * всегда возвращается число строк в таблице. Указание DISTINCT подавляет дубликаты

MAX(X) = MAX(ALL X); MAX(DISTINCT X) - Вычисляет максимальное значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты

MIN(X) = MIN(ALL X); MIN(DISTINCT X) - Вычисляет минимальное значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты

SUM(X) = SUM(ALL X); SUM(DISTINCT X) - Вычисляет сумму значений аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты

STDDEV([DISTINCT|ALL] X) - Вычисляет стандартное отклонение на множестве значений аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты

VARIANCE([DISTINCT|ALL] X) - Вычисляет квадрат дисперсии

Стандартом ANSI SQL не предполагается использование аргументов любого допустимого СУБД типа. Однако последнее часто имеет место на практике.

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

SELECT SUM(SALARY)
FROM EMPLOYEES;

вы узнаете итоговую сумму зарплаты по организации, а из запроса

SELECT AVG(SALARY), STDDEV (SALARY)
FROM EMPLOYEES;

среднюю зарплату по организации и ее разброс (дисперсию).

Однако наиболее часто требуется подобная итоговая информация не для таблицы в целом, а для определенных наборов (групп) строк таблицы.

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

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

SELECT DEPARTMENT_ID, MIN(SALARY), MAX(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

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

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

SELECT DEPARTMENT_NAME, JOB_ID, SUM(SALARY), COUNT(*), AVG(SALARY)
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES. DEPARTMENT_ID =DEPARTMENTS. DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME, JOB_ID;

Функции SUM( ), COUNT( ), AVG( ) - вычисляют суммы, число строк в группе и среднее значение в группе строк.

В SQL можно задавать условия поиска для группы строк. Для этого в команде SELECT существует предложение HAVING, которое должно следовать за предложением GROUP BY. HAVING задает условие поиска для группы строк.

Допустим, что вам необходимо получить ответ на вопрос, что и в предыдущем примере, но при этом каждая группа должна состоять не менее чем из двух сотрудников

SELECT DEPARTMENT_NAME, JOB_ID, SUM(SALARY), COUNT(*), AVG(SALARY)
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES. DEPARTMENT_ID =DEPARTMENTS. DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME, JOB_ID
HAVING COUNT(*)>=2;

Условие поиска в предложении HAVING исключает из результирующей таблицы группы, содержащие менее двух работников. Некоторые другие аспекты использования предложения GROUP BY и HAVING будут обсуждены в следующей Части руководства.

3.3. Представления или виртуальные таблицы

SQL предоставляет вам возможность создавать и использовать так называемые, виртуальные таблицы. Виртуальная таблица или представление является таблицей, которой физически нет в БД, но которая существует в представлении пользователя о логической структуре данных. Некоторым исключением являются материализованные представления (MATERIALIZED VIEW). Виртуальная таблица не содержит фактических данных, а реализуется как запрос к существующим таблицам БД. Таким образом, виртуальную таблицу можно рассматривать как поименованный запрос, который порождает таблицу для использования другими запросами (являются средством именования часто используемых команд SELECT). Виртуальные таблицы упрощают доступ к данным за счет замены сложных запросов более простыми и обеспечивают независимость и защиту данных. Виртуальная таблица является объектом реляционной БД.

Виртуальные таблицы можно определять с помощью других виртуальных таблиц. Однако, в определении представления не может быть использовано предложение ORDER BY. В некоторых реализациях SQL не допустимо выполнение обновлений на виртуальных таблицах, определенных на нескольких базовых таблицах, а также содержащих предложения GROUP BY, HAVING, опцию DISTINCT и функции агрегирования (так называемые виртуальные таблицы только для чтения (read-only veiw)).

В схеме HR существует представление EMP_DETAILS_VIEW содержащее сведения о сотрудниках организации из нескольких таблиц БД.

Используя данное представление вместо запроса

SELECT LAST_NAME, SALARY, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
ORDER BY DEPARTMENT_NAME, SALARY;

можно написать запрос

SELECT LAST_NAME, SALARY, DEPARTMENT_NAME
FROM EMP_DETAILS_VIEW
ORDER BY DEPARTMENT_NAME, SALARY;

Результирующее множество у этих двух запросов будет одинаковым.

Резюме

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

Для манипулирования с данными в реляционных базах данных был предложен специальный язык манипулирования данными - SQL. Команды этого языка позволяют определять таблицы, индексы, колонки и осуществлять выборки из них.

Предусмотрен набор встроенных функций для операций над колонками таблиц. Строки таблиц можно группировать, сортировать.

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

Синтаксис команды SELECT

SELECT [ALL/DISTINCT] выражение [ AS имя] [, выражение [AS имя] …]

FROM имя_таблицы/имя_представления [корреляционное_имя] [,имя_таблицы/имя_представления [корреляционное_имя]]

WHERE условие_поиска

GROUP BY имя_колонки/целочисленная_константа [,имя_колонки| целочисленная_константа …]

HAVING условие_поиска

ORDER BY имя_колонки/целочисленная_константа [ASC/DESC] [,имя_колонки|целочисленная_константа [ASC/DESC] …]

FOR UPDATE OF имя_колонки [,имя_колонки …]

Синтаксис команды UNION

Команда SELECT

UNION [ALL]

Команда SELECT

ORDER BY целочисленная_константа [ASC/DESC] [,целочисленная_константа [ASC|DESC] …]

Синтаксис команды INTERSECT

Команда SELECT

INTERSECT [ALL]

Команда SELECT

ORDER BY целочисленная_константа [ASC/DESC] [,целочисленная_константа [ASC|DESC] …]

Синтаксис команды MINUS

Команда SELECT

MINUS [ALL]

Команда SELECT

ORDER BY целочисленная_константа [ASC/DESC] [,целочисленная_константа [ASC|DESC] …]