2.1. Создание простых запросов

Учебная база данных

Подавляющая часть примеров в этом курсе заимствована из документации компании Oracle при использовании свободно распространяемых для обучения СУБД Oracle Express 11g и SQL Developer. В этой лекции не приводятся результаты выполнения запросов. Большинство примеров включено в Практикум к Главе 1 настоящего курса.

В поставляемом комплекте в БД имеется схема HR («Управление кадрами»), которая содержит таблицы DEPARTAMENTS (содержит информацию о подразделениях организации), EMPLOYEES (содержит информацию о служащих данной организации), LOCATIONS (содержит адреса размещения подразделений и сотрудников), …

Подразделение (DEPARTAMENTS)

Служащий (EMPLOYEES)

Размещение (LOCATIONS)

В настоящей лекции запросы разработаны в приложении SQL Developer, как показано на Рис. 1 ниже.

Выборка данных из таблицы

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

Для реализации запроса на выборку данных в SQL предназначена команда SELECT. Команда SELECT состоит из частей, которые носят название предложений. Каждое предложение преследует определенные цели: определить требуемые данные, определить таблицы, участвующие в запросе, определить критерий поиска (условия запроса), упорядочить выборку и т.п. Так предложение SELECT определяет список выражений (в простейшем случае список колонок), которые должны быть вычислены на каждой строке, а предложение FROM - список таблиц, которые участвуют в выборке. Следует помнить, что результатом выполнения команды SELECT является таблица или табличное выражение (результирующее множество).

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

SELECT DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID, MANAGER_ID 
FROM DEPARTMENTS;

Можно использовать * в списке имен колонок, для того чтобы не перечислять все колонки в таблице:

SELECT *
FROM DEPARTMENTS;

Результат будет таким же, как и в первом случае: будет выбрана и показана целиком таблица DEPARTMENTS.

Выборка заданных колонок

Мы уже видели, как с помощью команды SELECT можно получить табличное выражение. SQL позволяет строить выражение-колонку, как вариант табличного выражения, состоящего из одной колонки (или нескольких). Чтобы построить такое выражение, необходимо указать в списке предложения SELECT имена тех колонок, которые требуются. Чтобы построить список подразделений организации вам необходимо знать значение только двух колонок DNAME и DEPNO:

SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM DEPARTMENTS;

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

Чтобы получить список наименований подразделений, вам необходима только одна колонка DEPARTMENT_NAME:

SELECT DEPARTMENT_NAME FROM DEPARTMENTS;

Выборка заданных строк

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

Пусть требуется получить список сотрудников конкретного подразделения, например, с номером 60:

SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

Предложение WHERE имеет отношение ко всем строкам таблицы и содержит условие поиска DEPARTMENT_ID = 60, которое предписывает вывести строки таблицы EMPLOYEES, содержащие в колонке DEPARTMENT_ID значение равное 60.

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

SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEES 
WHERE JOB_ID =' IT_PROG' AND SALARY >= 5000;

При выполнении операций сравнения значений колонок могут быть использованы любые операторы сравнения: равно (=), не равно (!=, <>), больше (>), меньше (<), больше или равно (>=, !>), меньше или равно (<=, !>).

Условия поиска объединяются с помощью логических связок AND (И), OR (ИЛИ) и NOT (НЕ). Логическая связка AND позволяет выбирать строки соответствующие всем условиям поиска одновременно. OR дает возможность выбирать строки, соответствующие любому из нескольких условий поиска.

SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEES 
WHERE JOB_ID =' IT_PROG' OR SALARY >= 5000;

Для того чтобы выбирать строки, не удовлетворяющие некоторому условию, вы должны использовать логическую связку NOT. Допустим, что нам требуется список сотрудников, работающих в подразделении с номером 80 и не имеющих позицию SA_MAN.

SELECT LAST_NAME, FIRST_NAME, JOB_ID 
FROM EMPLOYEES 
WHERE JOB_ID <> 'SA_MAN' AND DEPARTMENT_ID = 80;

Как вы видите, можно комбинировать AND, OR или NOT в критериях поиска, чтобы получить необходимую вам информацию.

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

Помимо перечисленных средств построения критериев поиска SQL предлагает дополнительно целый ряд логических связок. Эти связки могут быть получены в результате комбинирования AND, OR или NOT, но их использование значительно упрощает формулировку запроса.

Например, вы можете производить поиск в заданном диапазоне значений колонки, посредством указания границ интервала значений. Для этого предназначен оператор BETWEEN ... END. Перечислим всех сотрудников, у которых зарплата находится между 5000 и 10000 у.е.

SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEES 
WHERE SALARY BETWEEN 5000 END 10000;

Оператор BETWEEN можно сконструировать с помощью логической связки AND.

SQL также предоставляет вам средства для манипулирования с множествами значений. Оператор IN дает возможность выбирать строки, значения некоторого атрибута которых лежат в заданном списке (множестве) значений. Допустим, вам нужно получить список сотрудников подразделений, номера которых 20, 30, 40

SELECT LAST_NAME, FIRST_NAME, JOB_ID 
FROM EMPLOYEES 
WHERE DEPARTMENT_ID IN (20,30,40);

Оператор IN можно смоделировать с помощью логической связки OR.

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

Пусть необходим список сотрудников, имеющих в наименованиях должностей которых присутствует слово «CLERK».

SELECT LAST_NAME, FIRST_NAME, JOB_ID 
FROM EMPLOYEES 
WHERE JOB_ID LIKE '%CLERK%';

Оператор LIKE позволяет искать строки по заданному шаблону. Символ подчеркивания ‘_‘ означает пропуск одной позиции в шаблоне, знак процента ‘%’ задает любую подстроку. Этот оператор также можно построить с помощью AND, OR и NOT.

Упорядочение строк

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

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

SELECT LAST_NAME, FIRST_NAME, JOB_ID, SALARY 
FROM EMPLOYEES 
WHERE DEPARTMENT_ID = 80
ORDER BY  SALARY;

Предложение ORDER BY приводит к сортировке по возрастанию значения в колонке SALARY. Допускается сортировка строк по нескольким колонкам, причем можно задавать последовательность сортировки: по возрастанию значений (умолчание) и по уменьшению значений (DESC). Пусть нужно выдать список сотрудников по должностям в порядке уменьшения их окладов.

SELECT LAST_NAME, FIRST_NAME, JOB_ID, SALARY 
FROM EMPLOYEES 
WHERE DEPARTMENT_ID = 80
ORDER BY  JOB_ID, SALARY DESC;

Подавление строк дубликатов

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

SELECT JOB_ID
FROM EMPLOYEES;

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

SELECT DISTINCT JOB_ID
FROM EMPLOYEES;

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

2.2. Комбинирование данных в запросах

Соединение таблиц

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

Задача соединения двух таблиц возникает уже при поиске ответа на вопрос, в каком подразделении работает служащий по фамилии Russell. Просматривая логическую структуру БД, вы увидите, что наименование подразделения находится в таблице DEPARTMENTS, а идентификация служащего в таблице EMPLOYEES.

Для того чтобы выполнить соединение двух таблиц необходимо, чтобы в обеих таблицах имелось, по крайней мере, по колонке, определенной на одном и том же домене. Это требование позволяет согласовать по значению этих колонок данные в обеих таблицах. В нашем случае такой колонкой для соединения является колонка DEPARTMENT_ID в таблицах DEPARTMENTS и EMPLOYEES. Для таблицы EMPLOYEES она является внешним ключом, хотя он явно не задан для поддержки его средствами СУБД. Соответствующий запрос имеет вид

SELECT LAST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE LAST_NAME = 'Russell'
 AND EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID; -условия соединения

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

Соединять можно отдельные строки, части таблиц или таблицы целиком. Если соединить таблицы EMPLOYEES и DEPARTAMENTS по колонке DEPARTMENT_ID, можно получать различную информацию, например, список служащих по отделам, упорядоченный по возрастанию зарплаты

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

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

SELECT DISTINCT JOB_ID, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS 
WHERE EMPLOYEES.DEPARTMENT_ID <> DEPARTMENTS.DEPARTMENT_ID;

Следует помнить, что соединение таблиц без явного указания условий соединения даст вам декартово произведение исходных таблиц. Например, для получения декартова произведения таблиц EMPLOYEES и DEPARTMENTS вы можете написать такой запрос

SELECT LAST_NAME, SALARY
FROM    EMPLOYEES, DEPARTMENTS;

Использование в таблицах колонок с неопределенными значениями при выполнении соединений может привести к тому, что часть информации не будет представлена в результирующем множестве. Это происходит потому, что кардинальное число доменов колонок соединения не совпадают (например, имеется несколько строк с NULL-значениями). В этом случае SQL предлагает использовать специальный тип соединения - внешнее соединение (outer join). Внешнее соединение приводит к появлению в результирующей таблице строк одной исходной таблицы, если в другой таблице не существует соответствующей строки, удовлетворяющей условию соединения.

В диалекте Oracle SQL существует несколько видов внешних соединений [см. Лекцию 4].

Подзапросы

SQL позволяет строить сложные запросы как комбинации простых, т.е. допускает вложение команд SELECT друг в друга. Предложение WHERE допускает использование операции сравнения множеств и, следовательно, другое предложение SELECT в качестве компонента условия поиска. Такие условия поиска называются подзапросом, а сами запросы - вложенными запросами. Подзапросы можно использовать для динамического построения условий поиска.

Пусть вам необходимо составить список служащих с такой же позицией, как и у Russell

SELECT LAST_NAME, JOB_ID
FROM EMPLOYEES
WHERE JOB_ID = 
(SELECT JOB_ID
FROM EMPLOYEES
WHERE LAST_NAME = ' Russell ');

При обработке этого вложенного запроса сначала обрабатывается подзапрос, что дает JOB_ID = 'SA_MAN', а затем выполняется сам запрос с условием поиска JOB_ID = 'SA_MAN'.

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

SELECT LAST_NAME
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEES);

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

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

В SQL существует оператор EXISTS (проверяет существование непустого результирующего множества), который рекомендуется использовать только с подзапросом. Отметим, что предложение ORDER BY не используется с подзапросами.

Язык SQL допускает составные подзапросы, т. е. подзапросы, состоящие из нескольких вложенных команд SELECT. Некоторых других аспектов использования подзапросов мы коснемся в Лекции 2.

Объединение, вычитание и пересечение таблиц

В SQL существует специальное предложение UNION, которое позволяет объединять две таблицы в одну, как это можно сделать с обычными множествами. Каждая из объединяемых таблиц представляет собой результат выполнения команды SELECT. При выполнении объединения требуется, чтобы колонки объединяемых таблиц были совместимы по объединению. Это значит, что число колонок в командах SELECT было одинаковым, а их тип и длина совпадали. Также должно соблюдаться соответствие по неопределенным значениям. Если колонка в одной из таблиц не может иметь нуль-значений, то к соответствующей колонке другой таблицы нужно применить условие NOT NULL. К UNION в целом может быть добавлено предложение ORDER BY, которое сортирует результирующую таблицу. Поэтому в списке имен сортировки не могут быть указаны имена колонок. Для указания колонки сортировки используется ее порядковый номер в результирующем множестве.

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

SELECT LAST_NAME, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20
UNION
SELECT LAST_NAME, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
ORDER BY 3;

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

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

В диалекте SQL Oracle 11g предусмотрены еще две операции над результирующими множествами.

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