(SELECT * FROM ASSIGNMENT
WHERE START_DATE = '09.10')
Результат:
WORKER ID
Мы выделили часть SQL-выражения, важную для нашего обсуждения. Две фразы SELECT определяют две таблицы, которые явно не являются объединительно-совместимыми. Однако мы можем взять их пересечение, оговорив, что рассматриваются только те столбцы, которые есть в обеих таблицах. Мы указываем, какие столбцы рассматривать, в команде CORRESPONDING BY. В нашем случае рассматривается только столбец WORKER_ID. Система оставит из результатов выполнения команд SELECT только значения в столбце WORKER_ID, Затем она возьмет пересечение этих двух множеств, и это будет результат запроса. Таким образом, в результате получится список ИД тех работников, которые имеют специальность «штукатур» и получили работу, которая должна начинаться 9 октября - в точности это и требовалось.
Такой же подход используется для операторов UNION и EXCEPT. Фраза CORRESPONDING BY следует за оператором и перечисляет столбцы, общие для обеих рассматриваемых таблиц. Рассмотрим еще несколько примеров:
Запрос: Какие здания являются зданиями офисов или же на них назначен рабочий 14127
(SELECT * FROM BUILDING
WHERE TYPE = 'Офис')
UNION CORRESPONDING BY (BLDG_ID)
(SELECT * FROM ASSIGNMENT
WHERE WORKER_ID = 1412)
Результат:
BLDG_ID
Запрос: На какие здания офисов не назначен рабочий 1235?
(SELECT * FROM BUILDING
WHERE TYPE = 'Офис')
EXCEPT CORRESPONDING BY (BLDG_ID)
(SELECT * FROM ASSIGNMENT
WHERE WORKER_ID = 1235)
Результат:
BLDG ID
Вы, вероятно, заметили, что эти запросы относительно легко сформулировать. Мы просто определяем подходящие подмножества строк из двух таблиц, а затем применяем соответствующий оператор UNION, INTERSECT или EXCEPT. Это существенно расширяет практические возможности языка, поскольку многие люди считают, что подобные запросы проще формулировать, пользуясь именно таким подходом. Например, применим к последнему запросу «старый» подход SQL:
SELECT BLDG_ID
FROM BUILDING
WHERE TYPE = 'Офис' AND
NOT EXISTS (SELECT *
FROM ASSIGNMENT
WHERE BUILDING.BLDG_ID = ASSIGNMENT.BLDG_ID
AND WORKER_ID = 1235)
В этом решении использован коррелированный подзапрос и оператор NOT EXISTS. Естественно предположить, что большинству людей значительно сложнее сформулировать такое решение, чем решение с оператором EXCEPT. Тем не менее, обратите внимание, что запрос можно было бы реализовать с помощью старого синтаксиса SQL. Это не так просто.
Оператор JOIN (соединение). SQL2 содержит несколько операторов соединения в явной форме: естественное соединение, внутреннее соединение, несколько внешних соединений, объединительное соединение и кросс-соединение. Мы рассмотрим только естественное и внутреннее соединения.
Естественное соединение. Соответственно названию, естественное соединение имеет то же значение в SQL, что и в реляционной алгебре. Предположим, что мы хотим соединить таблицы WORKER и ASSIGNMENT. Мы можем использовать такой синтаксис:
WORKER NATURAL JOIN ASSIGNMENT
Результатом выполнения этой команды будет такая же таблица, которая получилась бы в результате выполнения выражения
SELECT P.WORKER_ID, WORKER_NAME, HRLY_RATE, SKILL_TYPE,
SUPV_ID, WORKER_ID, BLDG_ID, START_DATE, NUM_DAYS
FROM WORKER, ASSIGNMENT
WHERE P.WORKER_ID = ASSIGNMENT.WORKER_ID,
за исключением того, что первый столбец будет называться WORKER_ID, а не P.WORKER_ID. В общем случае естественное соединение, связывает две таблицы по всем их общим столбцам, но эти столбцы включаются в результирующую таблицу только один раз. В результирующей таблице сначала располагаются общие столбцы, затем столбцы первой таблицы, затем оставшиеся столбцы второй таблицы.
NATURAL JOIN. Оператор, соединяющий таблицы в том случае, если общие столбцы имеют равные значения.
JOIN USING. Предположим, у нас есть две таблицы А и В, и у них есть общие столбцы К, L, М и N. Предположим, что мы хотим соединить таблицы не по всем общим столбцам, а только по столбцам L и N. Тогда мы можем воспользоваться следующим выражением:
A JOIN В USING (L, N)
Это выражение даст тот же результат, что и SELECT-выражение, в котором в команде SELECT будут сначала перечислены столбцы L и N, за которыми следуют оставшиеся столбцы А и оставшиеся столбцы В. Фраза WHERE в таком выражении должна иметь вид
WHERE A.L = B.L AND A.N = B.N
JOIN USING. Операция, связывающая таблицы только по указанным общим столбцам, имеющим равные значения.
JOIN ON. Если мы хотим задать более общее условие соединения двух таблиц, то можем пользоваться этой формой. Например, предположим, что мы хотим присоединить таблицу WORKER к ней самой, связав WORKER ID с SUPV_ID, чтобы получить информацию о менеджере каждого работника. Воспользуемся следующим выражением:
WORKER W JOIN WORKER SUPV
ON W.SUPV_ID = SUPV.WORKER_ID
В этом примере мы создали две копии таблицы WORKER и дали им псевдонимы W и SUPV, соответственно. Фраза ON содержит условие равенства SUPV_ID копии W и WORKER_ID копии SUPV таблицы WORKER.
JOIN ON. Операция, связывающая таблицы при выполнении условия.
Теперь рассмотрим некоторые вариацвв предыдущего примера. Вместо того чтобы использовать существующую таблицу или представление данных в условии соединения, мы можем использовать таблицы, полученные в результате выполнения других операций. Проиллюстрируем этот подход несколькими примерами запросов.
Запрос: Выдать данные о назначении и работнике для каждого штукатура, начинающего работу 9 октября.
(SELECT * FROM WORKER WHERE SKILL_TYPE = 'Штукатур')
NATURAL JOIN
(SELECT * FROM ASSIGNMENT WHERE START_DATE = '09.10')
Возможно, вы заметили, что этот запрос почти идентичен тому, который использовался ранее для иллюстрации работы оператора INTERSECT. Разница состоит в том, что вместо того, чтобы просто получить список WORKER_ID, мы получим всю связанную с работником информацию, поскольку кортежи таблицы WORKER будут связаны с кортежами таблицы ASSIGNMENT.
Запрос: Выдать данные о назначении, работнике и здании для каждого штукатура, начинающего работу 9 октября и работающего на жилом доме.
(SELECT * FROM WORKER WHERE SKILL_TYPE = 'Штукатур')
NATURAL JOIN
(SELECT * FROM ASSIGNMENT WHERE START_DATE = '09.10')
NATURAL JOIN
(SELECT * FROM BUILDING WHERE TYPE - 'Жилой дом')
В этом случае WORKER_ID и BLDG_ID - столбцы, по которым выполняется соединение - будут стоять в таблице результатов первыми, за ними будут следовать оставшиеся столбцы таблиц WORKER, ASSIGNMENT, BUILDING (именно в таком порядке). Если в результате мы получаем избыточную информацию, то можем поместить все это выражение внутрь выражения SELECT FROM, указывающего нужные столбцы:
SELECT WORKER_ID, START_DATE, BLDG_ID, ADDRESS
FROM (SELECT * FROM WORKER WHERE SKILL_TYPE = 'Штукатур')
NATURAL JOIN
(SELECT * FROM ASSIGNMENT WHERE START_DATE = '09.10')
NATURAL JOIN
(SELECT * FROM BUILDING WHERE TYPE = 'Жилой дом')
9. Операции изменения данных
В SQL есть операции изменения данных INSERT, UPDATE и DELETE, позволяющие, соответственно, добавлять строки, изменять значения в строках и удалять строки определенной таблицы в базе данных. Каждую из операций мы обсудим отдельно.
INSERT. Операция INSERT (вставить) позволяет вводить в таблицу как отдельные строки путем указания значений каждого столбца, так и множества строк путем формулировки запроса, определяющего вводимые строки.
INSERT INTO ASSIGNMENT (WORKER_ID, BLDG_ID, START DATE)
VALUES (1284, 485, 13.05)
Это выражение вводит в таблицу ASSIGNMENT одну строку. Имена столбцов, для которых задаются соответствующие значения, перечислены в скобках после имени обновляемой таблицы. Поскольку мы опустили NUM_DAYS, в этот столбец будет помещено пустое значение.
Предположим, что мы создали новую таблицу с именем BUILDING_2, состоящую из столбцов BLDG_ID, TYPE и QLTY_LEVEL (УРОВ_КАЧЕСТВА), и хотим заполнить эту таблицу строками из таблицы BUILDING, имеющими STATUS 2. Тогда мы воспользуемся второй формой команды INSERT:
INSERT INTO BUILDING_2
SELECT BLDG_ID, TYPE, QLTY_LEVEL
FROM BUILDING
WHERE STATUS = 2
INSERT. Операция, добавляющая строки к таблице.
UPDATE. Операция UPDATE (изменить) всегда применяется ко всем строкам, удовлетворяющим условию выражения WHERE. Если мы хотим повысить на 5 процентов ставку каждого подчиненного начальника 1520, то нам потребуется следующее выражение:
UPDATE WORKER
SET HRLY_RATE = 1.05 * HRLY_RATE
WHERE SUPV_ID = 1520 -
Если фраза WHERE отсутствует, то операция применяется к каждой строке таблицы. Например, если бы мы хотели повысить на 5 процентов ставку каждого работника, то мы должны были бы просто опустить команду WHERE в выражении UPDATE.
UPDATE. Операция, изменяющая значения столбцов в строке.
DELETE. Операция DELETE (удалить) также применяется ко всем строкам, удовлетворяющим условию WHERE. Если фраза WHERE отсутствует. то удаляются все строки таблицы. Допустим, что все рабочие, чей начальник имеет индекс 1520, были уволены, и мы хотим удалить соответствующие строки из базы данных. Это сделает следующее выражение:
DELETE FROM WORKER
WHERE SUPV ID = 1520
DELETE. Операция, удаляющая строки из таблицы.