Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 7487 / 958 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2

Лекция 5: Выборка данных. Общее построение предложения SELECT и фразы FROM и WHERE

Пример

Пример использования для краткости формулировки:

SELECT ename, dname
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
;

Выше этот же запрос приводился без псевдонимов, с указанием фактических имен таблиц во фразе WHERE.

Пример использования для повышения ясности текста:

SELECT employee.ename, department.dname
FROM   emp employee, dept department
WHERE  employee.deptno = department.deptno
;
Когда псевдонимы обязательны

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

"Выдать список сотрудников, принятых на работу позже президента":

SELECT subordinate.ename
FROM   emp subordinate, emp topmanager
WHERE  topmanager.job = 'PRESIDENT'
AND    subordinate.hiredate > topmanager.hiredate
;

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

Подзапрос в качестве источника данных

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

SELECT ename, dname
FROM   emp e, ( SELECT * FROM dept WHERE loc <> 'NEW YORK' ) d
WHERE  e.deptno = d.deptno
;

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

Подзапрос в качестве источника данных разрешен не только в предложении SELECT, но и в INSERT, UPDATE и DELETE.

Указание для таблицы пробной выборки строк

Обычно указанный во фразе FROM источник поставляет для последующей обработки полное множество своих строк. Версия 11 позволила указать для таблицы-источника неполную выборку строк. В этом случае в работу поступят не все строки, а случайная их выборка. В стандарте SQL:2003 подобная возможность указывается через уточнение TABLESAMPLE, и в Oracle она воспроизведена с некоторыми вольностями.

Ниже приводится несколько примеров способов указать такую выборку.

Отбор примерно 10% всех имеющихся сотрудников:

SELECT ename, sal FROM emp SAMPLE ( 10 );
SELECT COUNT ( * ) FROM emp SAMPLE ( 10 );

Упражнение. Проверьте, что оба запроса выше будут возвращать новые и новые результаты (на деле они будут заметно повторяться, но только в силу небольшого размера таблицы EMP).

Внутренняя техника основывается на применении для отбора строк псевдослучайной последовательности чисел. Поэтому обычно повторение такого запроса будет выдавать все разные и разные строки.

Если по каким-то причинам желательно выборку зафиксировать и сделать одинаковой при повторениях запроса (допустим, неполную выборку строк желательно воспроизводить без изменений), в текст запроса добавляется конкретная "затравка" (seed) для вычисления внутренней псевдослучайной последовательности, например:

SELECT ename, sal FROM emp SAMPLE ( 10 ) SEED ( 3 );

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

SELECT COUNT ( * ) FROM emp SAMPLE BLOCK ( 85 );

В качестве источника может выступать не обязательно фактическая таблица, но и представление данных (view; см. ниже), однако когда его формулировка не содержит обобщений. Частичную выборку не запрещено указать и в многотабличных запросах:

SELECT ename, dname
FROM   emp SAMPLE ( 10 ), dept
WHERE  emp.deptno = dept.deptno
;

Указание частичной выборки похоже на выборку с отсевом строк WHERE ROWNUM <= n (см. ниже), притом что последняя менее трудоемка. Однако при употреблении условия с ROWNUM речь идет об отборе произвольных n строк ("квотированной выборке"), а в запросе с SAMPLE — о примерной доле общего количества. Вдобавок отбор с SAMPLE в состоянии дать более представительную выборку строк.

Специальный синтаксис для запроса-соединения

Если предложение SELECT представляет собой особый случай соединения (join) источников данных, то во фразе FROM могут использоваться другие синтаксические построения. Их употребление раскрыто ниже, в разделе, относящемся к этой операции.

Физический адрес ROWID строки в таблице

Если во фразе FROM в качестве источника данных приведена хранимая таблица, то на этапе извлечения ее строк СУБД определяет для них физические адреса. Они становятся доступны в остальных частях запроса посредством "псевдостолбца" (на деле — функции без параметров) ROWID.

Физический адрес ROWID представлен типом ROWID, под которым может сохраняться в БД. Он содержит четыре компоненты: номер объекта (используется для таблицы из объектов), номер файла, номер блока и номер строки в блоке. Он расходует 10 байт, но SQL*Plus выдает его значение в формате BASE64 18-ю символами:

SQL> COLUMN dump(rowid) FORMAT A40
SQL> SELECT ROWID, DUMP ( ROWID ), loc FROM dept;
ROWID              DUMP(ROWID)                              LOC
------------------ ---------------------------------------- ---------
AAAR3qAAEAAAACHAAA Typ=69 Len=10: 0,1,29,234,1,0,0,135,0,0  NEW YORK
AAAR3qAAEAAAACHAAB Typ=69 Len=10: 0,1,29,234,1,0,0,135,0,1  DALLAS
AAAR3qAAEAAAACHAAC Typ=69 Len=10: 0,1,29,234,1,0,0,135,0,2  CHICAGO
AAAR3qAAEAAAACHAAD Typ=69 Len=10: 0,1,29,234,1,0,0,135,0,3  BOSTON

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

Еще реже находит себе применение другой "псевдостолбец" — UROWID, имеющий аналогичный смысл и вычисляемый при извлечении строк из таблиц с индексной организацией хранения и с внешним хранением.

Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет