close

Вход

Забыли?

вход по аккаунту

?

5.3

код для вставкиСкачать
5.3 Оператор SELECT. Выбор данных из двух и более таблиц. Подзапросы. Пример. Оператор SELECT Оператор SELECT является фактически самым важным для пользователя и самым сложным оператором SQL. Он предназначен для выборки данных из таблиц, т.е. он, собственно, и реализует одно их основных назначение базы данных - предоставлять информацию пользователю. Оператор SELECT всегда выполняется над некоторыми таблицами, входящими в базу данных.
Результатом выполнения оператора SELECT всегда является таблица. Таким образом, по результатам действий оператор SELECT похож на операторы реляционной алгебры. Любой оператор реляционной алгебры может быть выражен подходящим образом сформулированным оператором SELECT. Синтаксис: SELECT [DISTINCT] <список_выбора>
FROM <имя_таблицы>, ...
[ WHERE <условие выбора> ]
[ GROUP BY <имя_столбца>,... ]
[ HAVING <условие> ]
[ORDER BY <имя_столбца> [ASC | DESC],... ]
Выборка данных из двух и более таблиц Одна из наиболее важных особенностей SELECT - способность использования связей между различными таблицами, а также вывода содержащейся в них информации. Операция, которая приводит к соединению из двух таблиц всех пар строк, для которых выполняется заданное условие, называется соединением таблиц. Декартово произведение таблиц Соединение таблиц - частный случай декартового произведения. Декартово произведение таблиц - таблица, состоящая из всех возможных пар строк таблиц. Все столбцы:
SELECT * FROM faculty, department Выбор отдельных столбцов: SELECT faculty.name, faculty.facPK, department.facFK, department.name FROM faculty, department;
Соединение таблиц по равенству Соединение по равенству, в отличие от декартового произведения, позволяет соединить только те пары строк, которые действительно взаимосвязаны друг с другом. Вывод столбцов разных таблиц Этот вид запросов характерен тем, что фраза WHERE содержит только условие соединения, а список фразы SELECT содержит имена столбцов из различных таблиц.
Запрос: вывести название кафедр и номера их групп
SELECT name, num FROM department, group WHERE depPK=depFK
Когда запрос соединяет несколько таблиц, может возникнуть неоднозначность при ссылках на столбцы с одинаковыми именами из разных таблиц. Для разрешения этой неоднозначности во фразах SELECT и WHERE (как и в некоторых других фразах) имена столбцов необходимо уточнять именами таблиц Запрос: вывести названия факультетов и их кафедр SELECT faculty.name, department.name FROM faculty, department WHERE faculty.facPK=department.facFK Вывод столбцов с условием отбора Вариант, когда отбираются строки одной таблицы, а условие задается с участием другой
Запрос: вывести названия кафедр факультета информатики SELECT department.name as "кафедры факультета информатики" FROM faculty, department WHERE faculty.facPK=department.facFK AND lower(faculty.name)='информатика';
Запрос: вывести фамилии доцентов кафедру программирования SELECT teacher.name as "доценты кафедру программирования" FROM department, teacher WHERE department.depPK=teacher.dapFK AND lower(department.name)='программирование' AND lower(teacher.post)='доцент';
Синонимы таблиц Синонимы таблиц часто используются для задания более лаконичного имени таблицы, по которому можно сослаться на нее в любых других местах запроса.
Запрос: вывести названия кафедр, на которых имеются группы с рейтингом больше 50 SELECT DISTINCT d.name FROM department d, group g WHERE d.dapPK=g.depFK AND g.rating>50; Запросы по трем и более таблицам SQL позволяет формулировать запросы, которые предполагают использование трех и более таблиц. Методика соединения такая же, как и для двух таблиц. Запрос: вывести названия тех кафедр факультета информатики, на которых работают профессора SELECT DISTINCT department.name FROM faculty, department, teacher WHERE faculty.facPK=department.facFK AND
department.depPK=teacher.depFK AND
lower(faculty.name)="информатика" AND
lower(teacher.post)="профессор"; Процедура составления многотабличного запроса 1. Определить множество таблиц, необходимых для ответа на запрос. В это множество должны входить таблицы, на столбцах которых сформулированы условия, а также те столбцы, которые необходимо вывести. Наз. базовые таблицы запроса. 2. В структуре взаимосвязанных таблиц найти путь, соединяющий базовые таблицы (путь вычисления запроса). В результате получаем перечень таблиц, необходимых для формулировки запроса (таблицы запроса). 3. Во фразе FROM перечислить необходимые таблицы.
4. Во фразе WHERE соединить таблицы запроса и при необходимости задать условие отбора строк в базовых таблицах запроса. 5. Во фразе SELECT перечислить выводимые таблицы. Соединение таблиц по неравенству Запрос: вывести названия кафедр, которые не расположены в одном корпусе с деканатом факультета информатики SELECT DISTINCT d.name as "названия кафедр" FROM faculty f, department d WHERE d.building <> f.building AND lower(f.name)="информатика"; Самосоединение таблицы Чтобы произвести соединение таблицы со своей копией, необходимо указать во фразе FROM имя одной и той же таблицы два или большее количество раз, а во фразе WHERE - условие их соединения. Различным вхождениям одной и той же таблицы приписываются различные синонимы, и именно по этим синонимам производится обращение к столбцам. Запрос: вывести номера групп, рейтинг которых превышает рейтинг группы 504 пятого курса SELECT searched.num FROM group given, group searched WHERE given.num=504 AND given.year=5 AND
searched.rating>given.rating; Самосоединение и другие соединения Таблица может соединяться одновременно со своей копией и другими таблицами. Запрос: вывести пары преподавателей, которые читают одинаковые дисциплины в один и тот же день с указанием дисциплины и дня SELECT DISTINCT t1.name as ФИО_1, t2.name as ФИО_2, s.name as предмет, l1 as день
FROM lecture l1, lecture l2, teacher t1, teacher t2, subject s
WHERE l1.sbjFK=l2.sbjFK AND lower(l1.day)=lower(l2.day) AND l1.tchFK=t1.tchPK AND l2.tchFK=t2.tchPK AND l1.sbjFK=s.sbjPK AND t1.Name<t2.Name; Внешние соединения таблиц Внешние соединения возвращают строки, которые удовлетворяют условию соединения, а также те строки одной из таблиц, для которых в другой не нашлось удовлетворяющих условию соединения строк. В oracle синтаксис внешнего соединения имеет следующие два вида: таблица.столбец оператор_сравнения таблица.столбец(+)
таблица.столбец(+) оператор_сравнения таблица.столбец
Когда в результате должны быть включены все строки таблицы, указанной в левой части, следует использовать первый вариант. Такое соединение называется левым внешним соединением. Если необходимо, чтобы в результате присутствовали все строки таблицы в правой части, следует использовать второй вариант, в этом случае говорят о правом внешнем соединении. Запрос: вывести фамилии всех преподавателей с указанием их кафедры, если она есть SELECT d.name as "кафедра", t.name as "преподаватель" FROM department d, teacher t WHERE d.depPK(+)=t.depFK; Запрос: вывести названия всех кафедр с указанием фамилий преподавателей, если они есть SELECT d.name as "кафедра", t.name as "преподаватель" FROM department d, teacher t WHERE d.depPK=t.depFK(+) Соединения с использованием фразы FROM
Рассмотренные типы и способы соединения таблиц можно осуществлять и с помощью фразы FROM. В ней можно не только перечислить имена таблиц, участвующих в запросе, но и указать их соединение, для чего могут использоваться три различные конструкции. Уточненное соединение - явное задание условия соединения. Таблица [INNER | {FULL | LEFT | RIGHT} [OUTER]] JOIN таблица {ON условие | USING (список_столбцов)} Естественное соединение выполняется по равенству значений всех пар одноименных столбцов таблиц и не требует задания каких-либо условий. Таблица NATURAL [INNER | {FULL | LEFT | RIGHT} [OUTER]] JOIN таблица Перекрестное соединение эквивалентно декартовому произведению таблиц Таблица CROSS JOIN таблица Внутреннее соединение В операторе JOIN внутреннее соединение указывается ключевым словом INNER (когда две таблицы, можно не писать). Если не совпадают имена столбцов, участвующих во внутреннем соединении, или соединение производится не по равенству значений, следует использовать уточненное соединение с фразой ON. Запрос: вывести названия факультетов и их кафедр, если они имеют одинаковый фонд финансирования. SELECT faculty.name, department.name FROM faculty JOIN department ON faculty.facPK=department.facFK AND faculty.fund=department.fund; Внешнее соединение Запрос: вывести все факультеты и кафедры с указанием пар факультет/кафедра, имеющих одинаковые фонды финансирования и расположенные в одном корпусе
SELECT faculty.name, department.name FROM faculty FULL JOIN department ON facuty.fund=department.fund AND faculty.building=department.building; SELECT faculty.name, department.name FROM faculty FULL JOIN department USING (fund, building); Соединение трех и более таблиц Порядок соединений уточняется круглыми скобками. Использование скобок важно в связи с тем, что результат нескольких внешних соединений зависит от порядка их выполнения - например, последовательное внешнее соединение таблиц факультетов, кафедр и преподавателей не обязательно равно последовательному внешнему соединению таблиц преподавателей, кафедр и факультетов. Подзапросы SQL позволяет вкладывать одни запросы в другие, используя тем самым, результаты вычисления одних запросов в других запросах. Вложенный запрос - это заключенный в круглые скобки запрос, присутствующий во фразах WHERE, HAVING, FROM и т.д. Вложенный запрос еще называют подзапросом или дочерним запросом, а запрос, внешний к вложенному, наз. родительским или внешним. Подзапросы разделяются на два вида - простые и связанные (коррелированные)
Простые подзапросы Простым вложенным запросом называется такой, результат которого не зависит от внешнего запроса. Такие запросы обрабатываются системой "снизу вверх" - первым обрабатывается подзапрос самого нижнего уровня, полученное множество значений используется в подзапросе более высокого уровня и т.д. Вложение с использованием оператора сравнения Подзапрос возвращает единственное значение. Если больше одного - ошибка. Используются любые операторы сравнения. Запрос: вывести названия всех кафедр, расположенных в том же корпусе, что и факультет информатики SELECT name FROM department WHERE building =(SELECT building FROM faculty WHERE lower(name)="информатика")
Использование одной таблицы во внешнем и вложенном запросе Внешний и вложенные подзапросы могут использовать одну и ту же таблицу Запрос: вывести фамилии преподавателей, имеющих большую ставку, чем Андреев SELECT name FROM teacher WHERE salary > (SELECT salary FROM teacher WHERE upper(name)="Андреев");
Вложенный запрос в операторе IN
IN проверяет вхождение элемента во множество. Использование подзапроса в качестве второго операнда IN позволяет избежать ограничения на единственность значения, возвращаемого подзапросом. Запрос: Вывести названия факультетов, расположенных в тех же корпусах, что и кафедры программирования и лингвистики SELECT name FROM faculty WHERE building IN (SELECT building FROM department WHERE lower(name)="программирование" OR lower(name)="лингвистика"); Коррелированные подзапросы Коррелированным вложенным запросом называется такой, результат которого зависит от результата внешнего запроса. Подзапрос является коррелированным, когда в нем указан столбец таблицы внешнего запроса. Такое обращение к столбцам родительского запроса называется внешней ссылкой. Если быть точнее, внешняя ссылка - это имя столбца одной из таблиц фразы FROM внешнего запроса, но не входящего ни в одну из таблиц фразы FROM подзапроса. Запросы с коррелированными подзапросами обрабатываются следующим образом: 1. Выбирается строка внешнего запроса 2. Вычисляется подзапрос. При этом в качестве значения внешней ссылки используется значение соответствующего столбца выбранной строки внешнего запроса. 3. Результат подзапроса возвращается во внешний запрос, где проверяется на соответствие условию. Если сравнение истинно, текущая строка внешнего запроса включается в результирующую таблицу. 4. Выбирается следующая строка внешнего запроса и указанные действия повторяются до перебора всех его строк. Таким образом, коррелированный подзапрос вычисляется заново для каждой строки внешнего запроса. Этим он отличается от простого подзапроса, который вычисляется только один раз. В коррелированных подзапросах используется все то, что и в обычных. Запрос: вывести названия факультетов, кафедру которых расположены в корпусе 3 SELECT name FROM faculty WHERE 3 IN (SELECT building FROM department WHERE faculty.facPK=department.facFK); Во фразе WHERE подзапроса ссылаемся на столбец facPK таблицы faculty внешнего запроса, что делает запрос коррелированным. Подзапросы в SELECT Запрос: вывести средние значения фондов финансирования факультетов и кафедр SELECT (SELECT AVG(fund) FROM faculty) as "avg_fac", (SELECT AVG(fund) FROM department) as "avg_dep" FROM dual Подзапросы в FROM Запрос: вывести средний фонд финансирования факультетов и среднюю ставку преподавателей SELECT fac.avgfund, tch.avgsal FROM (SELECT AVG(fund) as avgfund FROM faculty) fac (SELECT AVF(salary) as avgsal FROM teacher) tch Оператор EXISTS Проверяет, является ли пустым множество. Простой подзапрос Запрос: если имеется хотя бы одна группа с рейтингом более 50, вывести номера и рейтинги всех групп SELECT num, rating FROM group WHERE EXISTS (SELECT * FROM group WHERE rating>50); Коррелированный подзапрос Запрос: вывести названия и фонды финансирования факультетов, на которых имеется хотя бы одна кафедра SELECT name, fund FROM faculty WHERE EXISTS (SELECT * FROM department WHERE department.facFK=faculty.facPK)
NOT EXISTS Запрос: вывести фамилии преподавателей, не имеющих занятий в расписании SELECT name FROM teacher WHERE NOT EXISTS (SELECT * FROM lecture WHERE lecture.tchFK=teacher.PK)
ANY, SOME, ALL ANY (SOME) - сравнение единственного значения левого операнда с множеством значений правого. Запрос: вывести названия факультетов, фонды финансирования которых меньше фонда финансирования хотя бы одной из кафедр факультета информатики SELECT name FROM faculty WHERE fund<some (SELECT department.fund FROM department, falulty
WHERE department.facFK=faculty.facPK AND upper(faculty.name)="информатика"); ALL - значение истина, если все члены множества правого операнда удовлетворяют условию сравнения Запрос: вывести номера групп, рейтинги которых ниже рейтинга любой из групп пятого курса кафедру программирования SELECT num FROM group WHERE rating<ALL (SELECT rating FROM group g, department d
WHERE g.depFK=d.depPK AND lower(d.name)="программирование" AND g.year=5); Филипп Андон, Валерий Резниченко "Язык запросов SQL" 
Документ
Категория
Без категории
Просмотров
105
Размер файла
443 Кб
Теги
1/--страниц
Пожаловаться на содержимое документа