Лекция 5. При объединении строк из различных таблиц можно отображать и выполнять операции над данными с помощью тех же операторов

Тема: Связь таблиц

При объединении строк из различных таблиц можно отображать и выполнять операции над данными с помощью тех же операторов, которые используются для одиночных таблиц. Однозначное соответствие или естественная связь осуществляется путем установки соответствия между одинаковыми значениями, находящимися в одинаковых столбцах разных таблиц, т.к. каждая таблица может содержать не более 1024 столбцов, то обойти это ограничение можно путем объединения данных из нескольких таблиц, сформировав одну виртуальную строку. Наиболее распространены объединения по равенству:

Например, запрос ставит в соответствие преподавателю учебные предметы, которые он ведет:

SELECT TEACHERS.TFAM, PREDMET.PNAME

FROM TEACHERS, PREDMET

WHERE TEACHERS.TNUM = PREDMET.TNUM

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

Декартово произведение таблиц

Если при объединении строк не установить соответствие с помощью столбцов, то к каждой строке первой таблицы будут по очереди добавлены все строки второй таблицы.

SELECT STUDENTS.* USP.*

FROM STUDENTS, USP

Тета-соединение таблиц

Для исключения всех столбцов, по которым проводится соответствие, делается композиция таблиц (см. пример №1).

Соединение таблиц с дополнительными условиями

SELECT TEACHERS.TFAM, PREDMET.PNAME

FROM TEACHERS, PREDMET

WHERE TEACHERS.TNUM = PREDMET.TNUM

AND TEACHERS.TFAM BETWEEN ‘K’ AND ‘C’

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

SELECT TEACHERS.TFAM, USP.OCENKA

FROM TEACHERS, PREDMET, USP

WHERE TEACHERS.TNUM = PREDMET.TNUM

AND PREDMET.PNUM = USP.PNUM

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

Столбцы, участвующие в объединении не обязательно объявлять первичными и внешними ключами. SQL Server позволяет связывать таблицы не имеющие первичных и внешних ключей.

Использование внешних объединений

Внешние объединения используют для того, чтобы выбирать из таблиц строки, имеющие несоответствующие значения.

При левом внешнем объединении возвращаются несоответствующие строки из таблицы, указанной слева от оператора сравнения, а при правом внешнем объединении из таблицы, указанной справа.

*= – левое внешнее объединение.

=* – правое.

Во внешних объединениях позволяется использовать не только знак равенства, но и другие знаки отношений.

SELECT STUDENTS.SFAM, STUDENTS.SNUM,

USP.PNUM, USP.SNUM

FROM STUDENTS, USP

WHERE STUDENTS.SNUM *>USP.SNUM

Объединение двух копий одиночной таблицы

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

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

SELECT FIRST.SFAM, SECOND.SFAM,

FIRST.STIP

FROM STUDENTS FIRST, STUDENTS SECOND

WHERE FIRST.STIP = SECOND.STIP

Например. Если учебный предмет может вести только один преподаватель, то в таблице PREDMET нужно проверять это условие:

SELECT FIRST.PNUM, FIRST.TNUM

SECOND.PNUM, SECOND.TNUM

FROM PREDMET FIRST, PREDMET SECOND

WHERE FIRST.PNUM = SECOND.PNUM

AND FIRST.TNUM <> SECOND.TNUM

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

В SQL предусматривается создание объединения, которое включает и различные таблицы и псевдонимы одиночной таблицы.

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

SELECT PREDMET.PNAME, FIRST.SNUM, SECOND.SNUM

FROM USP FIRST.USP SECOND.PREDMET

WHERE FIRST.PNUM = SECOND.PNUM

AND PREDMET.PNUM = FIRST.PNUM

AND FIRST.SNUM < SECOND.SNUM

Объединение результатов запроса

с помощью директивы UNION

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

Например для получения списка всех студентов и преподавателей, фамилии которых заключены между буквами ‘K’ и ‘C’ используем запрос:

SELECT SFAM, SIMA, SOTCH

FROM STUDENTS

WHERE SFAM BETWEEN ‘K’ AND ‘C’

UNION

SELECT TFAM, TIMA, TOTCC

FROM TEACHERS

WHERE TFAM BETWEEN ‘K’ AND ‘C’

Нельзя использовать агрегатные функции в предложении SELECT запроса в объединении. Добавление ключевого слова ALL в директиву UNION позволяет выводить повторяющиеся строки из обеих таблиц. Можно объединить до 16 операторов SELECT, поместив между ними оператор UNION. Для определения порядка директив UNION используют круглые скобки. Если во всех директивах используется ключевое слов ALL, то скобки вообще не нужны.

Иногда возникает необходимость вставлять константы и выражения в предложение SELECT. Например,

SELECT ‘Студент ’, SFAM

FROM STUDENTS

UNION

SELECT ‘Преподаватель’, TFAM

FROM TEACHERS

(оператор → директива)

Использование ранговых переменных

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

SELECT A.SFAM, B.PNUM, B.OCENKA

FROM STUDENTS A, USP B

WHERE A.SNUM = B.SNUM

/* запрос выводит фамилию студента, оценка и код предмета */

Создание синонимов таблиц

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

Например, пользователь с идентификатором CHER может создать синоним для таблицы STUDENTS, принадлежащей пользователю SA:

CREATE SYNONYM FIRST FOR SA.STUDENTS

После этого пользователь может работать с таблицей FIRST также как со своими собственниками.

Для того, чтобы создать единое имя для всех пользователей, создается общий синоним.

CREATE PUBLIC SYNONYM STUDENTS FOR STUDENTS

В основном, общие синонимы создаются администратором БД.

Общие и другие синонимы могут удаляться командой DROP SYNONYM. Обычные синонимы удаляются владельцами, а общие администратором.

DROP SYNONYM FIRST

Исходная таблица остается без изменений.

1) Вывести из таблиц студенты и успеваемость

ФИО студента, его код, оценку и код предмета, используя ранговые переменные.

2) Вывести из таблиц предмет и учителя

ФИО преподавателя и предмет, который он ведет.

3) Вывести из таблиц предмет, студенты и успеваемость

ФИО студента, оценку и название предмета.

Дата добавления: 2014-01-20