- •Новосибирская государственная академия экономики и управления
- •«Базы данных»
- •Новосибирск 2000
- •Введение
- •1. Простые запросы
- •2. Многотабличные запросы
- •3. Подзапросы
- •4. Операторы exists и not exists
- •5. Встроенные функции
- •6. Фразы group by и having
- •7. Встроенные функции и подзапросы
- •8. Операции реляционной алгебры
- •9. Операции изменения данных
- •10. Совместное использование sql с языками обработки данных
- •11. Определение представлений данных
- •12. Ограничения на запросы и обновление представлений данных
2. Многотабличные запросы
Возможность связывать элементы данных вне границ одной таблицы важна для любого языка баз данных. В реляционной алгебре эту функцию выполняет операция соединения. Хотя значительная часть SQL основана непосредственно на реляционном исчислении, SQL связывает данные разных таблиц аналогично тому, как это делает операция соединения реляционной алгебры. Сейчас мы покажем, как это делается. Рассмотрим запрос:
Запрос: Каковы специальности рабочих, назначенных на здание 435?
Данные, необходимые для ответа, находятся в двух таблицах: WORKER и ASSIGNMENT. Для решения в SQL требуется перечислить обе таблицы в команде FROM и задать специальный тип условия WHERE:
SELECT SKILL_TYPE
FROM WORKER, ASSIGNMENT
WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID
AND BLDG_ID = 435
Что здесь происходит? Мы должны рассмотреть два этапа обработки системой данного запроса.
1. Как обычно, сначала обрабатывается фраза FROM. Однако в этом случае, поскольку в команде указаны две таблицы, система создает декартово произведение строк этих таблиц. Это означает, что создается (логически) одна большая таблица, состоящая из столбцов обеих таблиц, в которой каждая строка одной таблицы спарена с каждой строкой другой таблицы. В нашем примере, поскольку в таблице WORKER пять столбцов, а в таблице ASSIGNMENT четыре столбца, в декартовом произведении, созданном командой FROM, будет девять столбцов. Общее число строк декартова произведения равно m * n, где m - число строк таблицы WORKER; а n - число строк таблицы ASSIGNMENT. Поскольку в таблице WORKER 7 строк, а в таблице ASSIGNMENT 19 строк, то декартово произведение будет содержать 7х19 или 133 строки. Если в команде FROM перечислено более двух таблиц, то создается декартово произведение всех таблиц, указанных в команде.
Декартово произведение. Результат объединения каждой строки одной таблицы с каждой строкой другой таблицы.
2. После создания гигантской реляционной таблицы система, как и прежде, применяет команду WHERE. Каждая строка таблицы, созданной командой FROM. проверяется на выполнение условия WHERE. Строки, не удовлетворяющие условию, исключаются из рассмотрения. Затем к оставшимся строкам применяется фраза SELECT.
Фраза WHERE в нашем запросе содержит два условия:
1. WORKER. WORKER_ID = ASSIGNMENT.WORKER_ID
2. BLDG_ID = 435
Первое из этих условий - условие соединения. Обратите внимание, что поскольку обе таблицы WORKER и ASSIGNMENT содержат столбец с именем WORKER_ID, их декартово произведение будет содержать два столбца с таким именем. Для того чтобы различать их, мы помещаем перед именем столбца имя исходной таблицы, отделяя его точкой.
Первое условие означает, что в любой выбранной строке значение столбца WORKER_ID из таблицы WORKER должно совпадать со значением столбца WORKER_ID из таблицы ASSIGNMENT. В действительности мы соединяем две таблицы по WORKER_ID. Все строки, в которых значения этих двух столбцов не равны, исключаются из таблицы произведения. В точности то же самое происходит при выполнении операции естественного соединения реляционной алгебры. (Однако, некоторое отличие от естественного соединения все же есть: язык SQL автоматически не удаляет лишний столбец WORKER_ID). Полное соединение этих двух таблиц с дополнительным условием BLDG_ID = 435 представлено на рис. 1. Применение команды SELECT даст, в конце концов, следующий результат запроса:
SKILL TYPE
Штукатур
Кровельщик
Электрик
Рис. 1. Соединение таблиц WORKER и ASSIGNMENT
Теперь мы покажем, как в SQL присоединить таблицу к ней самой.
Запрос: Перечислить работников, указав имена их менеджеров.
SELECT А.WORKER_NAME, B.WORKER_NAME
FROM WORKER A, WORKER В
WHERE B.WORKER_ID = A.SUPV_ID
Фраза FROM в этом примере создает две «копии» таблицы WORKER, давая им псевдонимы А и В. Псевдоним - это альтернативное имя, данное таблице. Затем копии А и В таблицы WORKER соединяются командой WHERE на основании условия равенства WORKER_ID в В и SUPV_ID в А. Таким образом, каждая строка из А присоединяется к строке В, содержащей информацию о менеджере строки А (рис.2).
Рис. 2. Соединение двух копий таблицы WORKER
Выбирая из каждой строки два имени работника, мы получим требуемый список:
А.NAME В.NAME
М.Фарадей Х.Колумб
К.Немо Г.Риковер Р.Гаррет Р.Гаррет
П.Мэйсон П.Мэйсон Г.Риковер Г.Риковер Х.Колумб Х.Колумб Дж.Барристер П.Мэйсон
Псевдоним. Альтернативное имя, данное таблице.
A.WORKER_NAME представляет работника, a B.WORKER_NAME представляет менеджера. Обратите внимание, что некоторые работники - сами себе менеджеры, что следует из выполненного в их строках равенства WORKER_ID - SUPV_ID.
В SQL можно за один раз связать более двух таблиц:
Запрос: Перечислить работников, назначенных на здания офисов.
SELECT WORKER_NAME
FROM WORKER, ASSIGNMENT, BUILDING
WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND
TYPE = 'Офис'
Результат:
NAME
М.Фарадей
К.Немо
Р.Гаррет
П.Мэйсон
Г.Риковер
Дж.Барристер
Обратите внимание, что если имя столбца (например, WORKER_ID или BLDG_ID) встречается более, чем в одной таблице, то для избежания неопределенности мы должны перед именем столбца указать имя исходной таблицы. Но если имя столбца встречается только в одной таблице, как TYPE в нашем примере, то никакой неопределенности нет, поэтому имя таблицы указывать не нужно.
Команды SQL этого запроса создают одну таблицу из трех реляционных таблиц базы данных. Первые две таблицы соединяются по WORKER_ID, после чего к полученной таблице присоединяется третья таблица по BLDG_ID. Условие
TYPE = 'Офис'
команды WHERE приводит к исключению всех строк, кроме строк, относящихся к офисным зданиям. Это соответствует требованиям запроса.
