Теория для лабораторной работы №2 - "Полное исследование команды SELECT"

Страницы работы

Содержание работы

Лабораторная работа №2

по курсу «Базы данных»

Полное исследование команды SELECT

План проведения работы:

1.  Создать две-три таблицы, состоящие из не менее 10 элементов.

2.  Использование функций и операторов SQL:

2.1. Агрегирующие  функции.

2.2. Символьные операторы.

2.3. Операторы сравнения.

2.4. Логические операторы.

2.5. Операторы работы со множествами.

3.  Применение иерархических подзапросов к таблице.

4.  Группировка строк таблицы.

5.  Сортировка строк.

6.  Использование соединений (екви, авто, внешних).

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

8.  Использование подзапросов.

9.  Использование коррелированных подзапросов.

10.Описать план проведения работы, предоставить экран выполнения.

 

Теоретическое введение

Полное исследование команды SELECT

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

Требования к использованию:

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

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

Системная привилегия SELECT ANY позволяет просматривать данные из любой таблицы или любой базовой таблицы представления.

 

Синтаксис:

 


В примерах используются таблицы EMP, DEPT пользователя SCOTT стандартно генерируемые Oracle при инсталляции. Их содержимое приведено в приложении.

 

 

DISTINCT

Возвращает только одну пару дублирующихся строк из всего их множества.

 

 

ALL

Возвращает все строки из всех таблиц, представлений, записанных в клаузе FROM.

 

 

table.*

view.*

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

 

 

c_alias

обеспечивает другое имя (синоним) для названия колонки. Синоним может быть использован только в клаузе ORDER BY.

 

 

schema

используется, если схема schema содержит выбранную таблицу или представление.

 

 

table view

имя таблицы или представления из которых выбираются данные.

 

 

dblink

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

 

 

subquery

подзапрос. Oracle выполняет подзапрос и использует строки, полученные в результате этого подзапроса как представление в клаузе FROM. Подзапрос не может запрашивать таблицу, которая используется в той же самой клаузе FROM, что и подзапрос.

 

 

WITH READ ONLY

определяет, что подзапрос не может быть изменён.

 

 

t_alias

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

 

 

WHERE

выбирает строки, для которых условие истинно. Если эта клауза опущена, то Oracle возвращает все строки из таблиц, представлений, указанных в клаузе FROM.

 

 

START WITH CONNECT BY

возвращает строки в иерархическом порядке.

 

 

GROUP BY

выполняет операцию expr для каждой строки и возвращает единственную строку с суммарной информацией для каждой группы.

 

 

HAVING

возвращает те группы строк, для которых заданное условие истинно.

UNION

UNION ALL

INTERSECT

MINUS

комбинирует строки, возвращённые двумя операторами SELECT используя операции над множествами. Для ссылок на колонки должны использоваться синонимы этих колонок.

ORDER BY

сортировка строк.

 

expr

сортирует строки по значениям тех столбцов, названия которых указаны в expr

 

position

сортирует строки по значения тех столбцов, номера которых указаны в position

 

ASC DESC

сортировка по возрастанию или убыванию. ASC – по умолчанию.

FOR UPDATE

запрещает другим пользователям изменять строки, возвращённые оператором SELECT.

 

OF

замыкает только строки, указанной таблицы.

Агрегирующие функции

Агрегирующие функции предназначены для обработки групп строк в таблице.

Некоторые агрегирующие функции используют следующие опции:

DISTINCT – заставляет функцию рассматривать только уникальные значения аргумента.

ALL – заставляет функцию рассматривать все значения аргумента, включая дубликаты (задано по умолчанию).

Пример.

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

SELECT AVG(deptno) «Average» FROM EMP

Average

----------

22.142857

 

SELECT AVG(DISTINCT deptno) «Average» FROM EMP

Average

----------

20

 

Функция

Синтаксис

Пояснение

Пример

AVG

AVG([DISTINCT|ALL] n)

 

возвращает среднее арифметическое столбца n

SELECT AVG(sal) "Average" FROM emp

 

   Average

-----------------

2077.21429

MAX

MAX([DISTINCT|ALL] expr)

возвращает максимальное значение выражения expr

SELECT MAX(sal) "Maximum" FROM emp

 

   Maximum

-----------------

   5004

MIN

MIN([DISTINCT|ALL] expr)

возвращает минимальное значение выражения expr

SELECT MIN(hiredate) "Minimum Date"            FROM emp

 

Minimum Date

----------------------

17-DEC-80

COUNT

COUNT({* |                     [DISTINCT|ALL] expr})

 

возвращает количество строк

1)

SELECT COUNT(*) "Total" FROM emp

 

        Total

---------------

          18

2)

SELECT COUNT(job) "Count" FROM emp

 

Count

----------

14

3)

SELECT COUNT(DISTINCT job) "Jobs" FROM emp

 

Jobs

----------

5

STDDEV

STDDEV([DISTINCT| ALL] x)

возвращает стандартное отклонение x

SELECT STDDEV(sal) "Deviation" FROM emp

 

Deviation

-----------------

1182.50322

SUM

SUM([DISTINCT|ALL] n)

 

возвращает сумму значений столбца n

SELECT SUM(sal) "Total" FROM emp

 

     Total

-------------

     29081

VARIANCE

VARIANCE(              [DISTINCT|ALL]x)

возвращает рассогласование x, используя следующую формулу:

SELECT VARIANCE(sal) "Variance" FROM emp  

 

Variance

-----------------

1389313.87

 

Символьные операторы

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

Оператор

Пояснение

Пример

||

конкатенация строк

SELECT 'Name is ' || ename         FROM emp

Результатом конкатенации двух символьных строк является также символьная строка. Если конкатенируемые строки имеют тип CHAR, то результирующая строка также будет иметь тип CHAR и соответственно её длина лимитируется 255 символами. Если хотя бы одна из конкатенируемых строк имеет тип VARCHAR2, то результирующая строка будет иметь тип VARCHAR2 и её длина лимитируется 2000 символами.

Операторы сравнения

Операторы сравнения используются в условиях, которые сравнивают одно выражение с другим. Результатом сравнения является одно из значений: TRUE, FALSE, или UNKNOWN.

Оператор

Пояснение

Пример

=

проверка на равенство

SELECT  * FROM emp   WHERE sal = 1500

!=

проверка на неравенство

SELECT  * FROM emp   WHERE sal != 1500

> 

< 

проверка на «больше чем» и «меньше чем»

SELECT * FROM emp WHERE sal > 1500

 

SELECT * FROM emp WHERE sal < 1500

>=

<=

проверка на «больше или равно» и «меньше или равно»

SELECT * FROM emp WHERE sal >= 1500

 

SELECT * FROM emp WHERE sal =< 1500

IN

проверка на «равенство любому значению из».

Эквивалент «=ANY»

SELECT * FROM emp WHERE job IN ('CLERK','ANALYST')

 

SELECT * FROM emp WHERE sal IN               (SELECT sal        FROM emp WHERE deptno = 30)

 

NOT IN

Эквивалент «!=ANY».

Вычисляет значение FALSE, если сравниваемый операнд не входит в заданное множество.

SELECT * FROM emp WHERE job NOT IN ('CLERK','ANALYST')

 

SELECT * FROM emp WHERE sal NOT IN               (SELECT sal     FROM emp         WHERE deptno = 30)

ANY

Сравнивает значение с каждым значением в заданном списке, если хотя бы одно из значений из списка удовлетворяет заданному отношению, то возвращает эту строку. Используется вместе с операторами !=, >, <, <=, >=

SELECT *          FROM emp WHERE sal = ANY (SELECT sal FROM emp WHERE deptno = 30)

ALL

Сравнивает значение с каждым значением в заданном списке, если все значения из списка удовлетворяют заданному отношению, то возвращает эту строку. Используется вместе с операторами !=, >, <, <=, >=

SELECT *          FROM emp WHERE sal> = ANY (SELECT sal FROM emp WHERE deptno = 30)

[NOT] BETWEEN x AND y

проверка [не] принадлежности заданному интервалу

SELECT *          FROM emp WHERE sal          BETWEEN 2000               AND 3000

EXISTS

TRUE, если подзапрос вернул хотя бы одну строку

SELECT dname, deptno FROM dept WHERE EXISTS

(SELECT * FROM emp WHERE dept.deptno = emp.deptno)

IS [NOT] NULL

проверка на [не] NULL

SELECT dname, deptno FROM emp

WHERE comm IS NULL

 

Оператор LIKE

Оператор LIKE используется в операторах сравнения символьных строк для создания шаблонов.

Синтаксис:

где

char1 –сравниваемая строка. Имеет тип CHAR или VARCHAR2;

NOT – логический оператор отрицания;

char2 – шаблон. Имеет тип CHAR или VARCHAR2 и может содержать специальные символы % и _;

ESCAPE – определяет единственный символ как ESC-символ.

Пример.

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

SELECT sal

      FROM emp

      WHERE ename LIKE 'SM%'

Специальные символы, используемые Oracle в шаблонах:

·  _  - знак подчёркивания. Используется для определения любого одного символа.

·  % - знак процента. Определяет множество любых символов либо не одного.

Опция ESCAPE определяет ESC-символ. Если ESC-символ появляется в шаблоне перед специальными символами ‘%’ или ‘_’, то Oracle интерпретирует специальный символ как простой символ.

Пример.

Найти служащих, используя шаблон ‘A_B’:

SELECT ename

      FROM emp

      WHERE ename LIKE '%A\_B%' ESCAPE '\'

Логические операторы

 

Оператор

Пояснение

Пример

NOT

логическое отрицание

SELECT * FROM emp WHERE NOT (job IS NULL)

 

SELECT * FROM emp WHERE NOT (sal BETWEEN 1000 AND 2000)

AND

логическое умножение

SELECT * FROM emp  WHERE job = 'CLERK'     AND deptno = 10

OR

логическое сложение

SELECT *  FROM emp WHERE job = 'CLERK'     OR deptno = 10

 

Операторы работы со множествами

 

Оператор

Результат

UNION

все строки, возвращаемые каждым из запросов

UNION ALL

все строки, возвращаемые каждым из запросов, включая все повторения

INTERSECT

все строки, которые встречаются как в первом запросе, так и во втором

MINUS

все строки, которые встречаются только в  первом запросе и не встречаются во втором

 

Примеры.

В примерах будут использованы следующие таблицы:

Таблица ORDERS_LIST1:

 

PART                            

----------                        

SPARKPLUG                   

FUEL PUMP                    

FUEL PUMP                    

TAILPIPE

Таблица ORDERS_LIST2:

 

PART

----------

CRANKSHAFT

TAILPIPE

TAILPIPE

 

Пример. UNION

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

SELECT part

      FROM orders_list1

UNION

SELECT part

      FROM orders_list2

 

PART

----------

SPARKPLUG

FUEL PUMP

TAILPIPE

CRANKSHAFT

 

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

Рассмотрим таблицы LIST1 и LIST2:

Таблица LIST1:

 

PART                           PARTNUM             

----------                       --------------- 

SPARKPLUG                    123

FUEL PUMP                       34

TAILPIPE                          129

Таблица LIST2:

 

PART                              DATE_IN

----------                          -------------   

CRANKSHAFT                10/24/98

TAILPIPE                             9/9/99

Как видно из определения этих таблиц, они имеют разные столбцы. Для того, чтобы полностью объединить эти две таблицы, необходимо в команде объединения:

1)  для таблицы LIST1 создать фиктивный столбец DATE_IN типа DATE. Это достигается командой TO_DATE(NULL);

2)  для таблицы LIST2 создать фиктивный столбец PART_NUM типа NUMBER. Это достигается командой TO_NUMBER(NULL).

Следующий пример показывает как можно обойти ограничение на равенство схем операндов объединения:

SELECT part, partnum, to_date(null) «date_in»

      FROM list1

UNION

SELECT part, to_null(null)»partnum», date_in

      FROM list2

 

PART

PARTNUM

DATE_IN

SPARKPLUG

123

 

FUEL PUMP

34

 

TAILPIPE

129

 

TAILPIPE

 

09/09/99

CRANKSHAFT

 

10/24/98

 
Пример. UNION ALL

Следующий пример комбинирует результаты с помощью оператора UNION ALL, который дублирует повторяющиеся строки:

SELECT part

      FROM orders_list1

UNION ALL SELECT part

      FROM orders_list2

 

PART

----------

SPARKPLUG

FUEL PUMP

FUEL PUMP

TAILPIPE

CRANKSHAFT

TAILPIPE

TAILPIPE

 

Пример. INTERSECT

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

SELECT part

      FROM orders_list1

INTERSECT

SELECT part

      FROM orders_list2

 

PART

----------

TAILPIPE

 

Пример. MINUS

Следующий пример комбинирует результаты с помощью оператора MINUS, который возвращает только те строки, которые возвращаются только первым запросом и не возвращаются вторым.

SELECT part

      FROM orders_list1

 

MINUS SELECT part

      FROM orders_list2

 

PART

----------

SPARKPLUG

FUEL PUMP

Иерархические запросы

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

 

START WITH

указывает корневую строку(строки) иерархии.

CONNECT BY

специфицирует отношение между родительскими строками и дочерними.

WHERE

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

Oracle для построения иерархии использует следующие шаги:

1.  Oracle выбирает корневую строку(строки) иерархии. Эти строки указываются в клаузе START WITH.

2.  Oracle выбирает дочерние строки для каждой корневой строки. Каждая дочерняя строка должна удовлетворять условию в клаузе CONNECT BY и может иметь не более одной корневой строки.

3.  Oracle последовательно генерирует дочерние строки. Oracle сначала выбирает потомков строк, возвращённых во 2м шаге, затем потомков этих потомков и т.д. Oracle всегда выбирает потомков, анализируя условие в клаузе CONNECT BY.

4.  Если запрос содержит клаузу WHERE, Oracle удаляет все строки из иерархии, которые не удовлетворяют условию в этой клаузе.

5.  Oracle возвращает строки в порядке, показанном на рисунке:

ROOT

 

 

 


Ограничения на построения иерархий:

·  оператор SELECT, выполняющий иерархический запрос не может также выполнять соединение.

·  если в иерархическом запросе используется клауза ORDER BY, Oracle сортирует строки в порядке, отличном показанном на рисунке.

Клауза START WITH

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

Клауза CONNECT BY

Определяет отношение между родительскими и дочерними строками. Эта клауза содержит условие, определяющее это отношение. Это условие должно использовать оператор PRIOR для ссылки на родительскую строку.

Две формы использования оператора PRIOR:

      PRIOR expr comparison_operaror expr

      expr comparison_operaror PRIOR expr

Для нахождения потомка родительской строки, Oracle вычисляет expr, стоящее перед PRIOR, для родительской строки и другой expr для каждой строки в таблице. Строки, для которых условие истинно – потомки родительской строки. Клауза CONNECT BY может содержать другие условия для дальнейшей фильтрации строк, возвращённых запросом. Клауза CONNECT BY не может содержать подзапрос.

Oracle выдаёт ошибку, если одна и та же строка является одновременно как родителем, так и потомком другой строки.

Пример.

Следующая клауза CONNECT BY определяет иерархическое отношение, в котором значение номера служащего (EMPNO) родительской строки равно значению номера руководителя (MGR) дочерней строки:

CONNECT BY PRIOR empno=mgr

Пример.

В следующей клаузе CONNECT BY, оператор PRIOR применяется только к значению  EMPNO. Для вычисления этого условия, Oracle вычисляет  значения EMPNO для родительской строки и MGR,SAL, и COMM – значения для дочерней строки:

CONNECT BY PRIOR empno=mgr AND sal>comm

В этом примере потомок должен иметь номер руководителя (MGR), равным номеру служащего (EMPNO) родительской строки и должен иметь оклад (SAL) выше, чем его комиссионные (COMM).

Псевдостолбец LEVEL

Оператор SELECT, выполняющий иерархический запрос может использовать псевдостолбец LEVEL, возвращающий значение 1 для корня дерева, 2 для потомка корня дерева и т.д.

Число уровней, возвращаемых иерархическим запросом  ограничивается размером доступной памяти.

Пример.

Следующий оператор SELECT показывает всех служащих в иерархическом порядке. Корневая строка определена для служащих, работающих на должности ‘PRESIDENT’. Дочерние строки определены для тех служащих, которые имеют номер служащего родительской строки такой же как и номер их руководителя.

SELECT LPAD(‘’,2*(LEVEL-1)) || ename org_chart,empno,mgr,job

                         FROM emp

                     START WITH job=’PRESIDENT’

                     CONNECT BY PRIOR empno=mgr

Оператор LPAD применяется для сдвига дочерней записи относительно её родительской.

 

ORG_CHART

EMPNO

MGR

JOB

KING

7839

 

PRESIDENT

    JONES

7566

7839

MANAGER

        SCOTT

7788

7566

ANALYST

            ADAMS

7876

7788

CLERK

        FORD

7902

7566

ANALYST

             SMITH

7369

7902

CLERK

    BLAKE

7698

7839

MANAGER

        ALLEN

7499

7698

SALESMAN

        WARD

7521

7698

SALESMAN

        MARTIN

7654

7698

SALESMAN

        TURNER

7844

7698

SALESMAN

        JAMES

7900

7698

CLERK

    CLARK

7782

7839

MANAGER

        MILLER

7934

7782

CLERK

 

Следующий оператор аналогичный предыдущему за исключением того, что он не выводит служащих с должностью ‘ANALYST’.

SELECT LPAD(‘’,2*(LEVEL-1)) || ename org_chart,empno,mgr,job

                         FROM emp

                    WHERE  job!=’ANALYST’

                     START WITH job=’PRESIDENT’

                     CONNECT BY PRIOR empno=mgr

 

ORG_CHART

EMPNO

MGR

JOB

KING

7839

 

PRESIDENT

    JONES

7566

7839

MANAGER

            ADAMS

7876

7788

CLERK

             SMITH

7369

7902

CLERK

    BLAKE

7698

7839

MANAGER

        ALLEN

7499

7698

SALESMAN

        WARD

7521

7698

SALESMAN

        MARTIN

7654

7698

SALESMAN

        TURNER

7844

7698

SALESMAN

        JAMES

7900

7698

CLERK

    CLARK

7782

7839

MANAGER

        MILLER

7934

7782

CLERK

 

Oracle не выводит аналитиков SCOTT и FORD, однако он выводит служащих, подчиняющихся им.

 

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

SELECT LPAD(‘’,2*(LEVEL-1)) || ename org_chart,empno,mgr,job

                         FROM emp

                     START WITH job=’PRESIDENT’

                     CONNECT BY PRIOR empno=mgr AND LEVEL<=2

 

ORG_CHART

EMPNO

MGR

JOB

KING

7839

 

PRESIDENT

    JONES

7566

7839

MANAGER

    BLAKE

7698

7839

MANAGER

    CLARK

7782

7839

MANAGER

 

Клауза GROUP BY

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

Если оператор SELECT использует клаузу GROUP BY, то список вывода может только содержать следующие типы выражений:

·  константы

·  аггрегирующие функции

·  функции USER, UID и SYSDATE

·  выражения, идентичные указанным в клаузе GROUP BY

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

Пример.

Вывести минимальный и максимальный размер жалования для каждого департамента.

SELECT deptno,MIN(sal),MAX(sal)

                FROM emp

                 GROUP BY deptno

 

DEPTNO

MIN(SAL)

MAX(SAL)

10

1300

5000

20

800

3000

30

950

2850

 

Пример.

Вывести минимальный и максимальный размер жалования для клерков в каждом департаменте:

SELECT deptno,MIN(sal),MAX(sal)

                FROM emp

                 WHERE job=’CLERK’

                 GROUP BY deptno

 

DEPTNO

MIN(SAL)

MAX(SAL)

10

1300

1300

20

800

1100

30

950

950

 

Клауза HAVING

Используется для ограничения вывода групп строк, определённых в клаузе GROUP BY. Oracle обрабатывает клаузы WHERE, GROUP BY и HAVING следующим образом:

1.  Если оператор содержит клаузу WHERE, Oracle удаляет все строки, не удовлетворяющих условию в этой клаузе.

2.  Oracle вычисляет и формирует группы в соответствии с клаузой GROUP BY.

3.  Oracle удаляет все группы, не удовлетворяющих условию в клаузе HAVING.

Пример.

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

SELECT deptno,MIN(sal),MAX(sal)

                FROM emp

                WHERE job=’CLERK’   

                GROUP BY deptno

                 HAVING MIN(sal)<1000   

 

DEPTNO

MIN(SAL)

MAX(SAL)

20

800

1100

30

950

950

 

Клауза ORDER BY

Без клаузы ORDER BY нельзя гарантировать, что один и тот же запрос, выполненный несколько раз возвратит строки в одном и том же порядке. Эта клауза используется для упорядочивания выбранных в запросе строк. Клауза специфицирует выражения, позиции и синонимы выражений в списке вывода оператора. Oracle возвращает строки в соответствии с их значениями для этих выражений.

Можно указывать несколько выражений в клаузе ORDER BY. Oracle сначала сортирует строки в соответствии с их значениями для первого выражения. Строки с одинаковыми значениями для первого выражения затем сортируются в соответствии с их значениями для второго выражения и т.д.

Пример.

Выбрать всю информацию о продавцах (SALESMAN)  в порядке убывания их комиссионных:

SELECT *

           FROM emp

           WHERE job=’SALESMAN’

           ORDER BY comm DESC

Пример.

Выбрать служащих из таблицы EMP в порядке возрастания номера департамента и убывания их жалования.

SELECT ename,deptno,sal

FROM emp

           ORDER BY 2 ASC, 3 DESC

ENAME

DEPTNO

SAL

MILLER

10

13000

KING

10

5000

CLARK

10

2450

SCOTT

20

3000

FORD

20

3000

JONES

20

2975

ADAMS

20

1100

SMITH

20

800

BLAKE

30

2850

ALLEN

30

1600

TURNER

30

1500

WARD

30

1250

MARTIN

30

1250

JAMES

30

950

 

Клауза FOR UPDATE

Клауза FOR UPDATE «замыкает» выбранные запросом строки. Если пользователь выбрал строку для изменения, то другие пользователи не смогут замкнуть или изменить её в текущей транзакции этого пользователя. Клауза FOR UPDATE сигнализирует, что пользователь намерен вставить, изменить или удалить строки, возвращённые запросом.

Клауза FOR UPDATE не может использоваться вместе с конструкциями:

·  DISTINCT

·  GROUP BY

·  множественными операторами

·  агрегирующими функциями

Таблицы, замкнутые в клаузе FOR UPDATE должны находиться в этой же базе данных.

Если строка, выбранная для изменения в данный момент замкнута другим пользователем, Oracle ожидает её освобождения, затем замыкает её и передаёт управление текущему пользователю. Можно использовать опцию NOWAIT, для того чтобы сообщить Oracle, что необходимо прервать оператор без ожидания если строка всегда замкнута.

FOR UPDATE OF

Выражение в клаузе OF только определяет какие строки таблиц замыкаются. Если опустить эту клаузу, то Oracle замкнёт строки из всех таблиц в запросе.

Пример.

Следующий оператор замыкает строки в таблице EMP с клерками, работающими в Нью-Йорке и замыкает строки в таблице DEPT с отделами в Нью-Йорке,  в которых работают клерки:

SELECT empno,sal,comm

                FROM emp,dept

                WHERE job=’CLERK’  

                AND loc=’NEW YORK’

                FOR UPDATE

Пример.

Следующий оператор замыкает только строки в таблице EMP с клерками, работающими в Нью-Йорке и не замыкает строки в таблице DEPT:

SELECT empno,sal,comm

                FROM emp,dept

                WHERE job=’CLERK’ AND loc=’NEW YORK’

                FOR UPDATE OF emp

Соединения

Соединение – это запрос, который комбинирует строки из двух или более таблиц или представлений. Oracle выполняет соединение всякий раз, когда много таблиц появляется в клаузе FROM. Список вывода может содержать любые столбцы из любых этих таблиц. Если любые две из этих таблиц имеют общий столбец, необходимо определить все ссылки на этот столбец через запрос с синонимами имени таблицы для избежания двусмысленности.

Условие соединения

Большинство запросов соединения содержат  условие в клаузе WHERE, которое сравнивает два столбца, каждый из которых находится в разных таблицах. Для выполнения соединения, Oracle комбинирует пары строк,  для которых условие соединения истинно.

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

Екви-соединения

Екви-соединения – соединения с условием, содержащим оператор равенства. Екви-соединения комбинируют строки, которые имеют равные значения для определённого столбца.

Пример.

Это соединение возвращает имя и занимаемую должность каждого служащего и номер и название отдела в котором он работает:

SELECT ename,job,dept.deptno,dname

               FROM emp,dept 

               WHERE  emp.deptno=dept.deptno

ENAME

JOB

DEPTNO

DNAME

SMITH

CLERK

20

RESEARCH

ALLEN

SALESMAN

30

SALES

WARD

SALESMAN

30

SALES

JONES

MANAGER

20

RESEARCH

MARTIN

SALESMAN

30

SALES

BLAKE

MANAGER

30

SALES

CLARK

MANAGER

10

ACCOUNTING

SCOTT

ANALYST

20

RESEARCH

KING

PRESIDENT

10

ACCOUNTING

TURNER

SALESMAN

30

SALES

ADAMS

CLERK

20

RESEARCH

JAMES

CLERK

30

SALES

FORD

ANALYST

20

RESEARCH

MILLER

CLERK

10

ACCOUNTING

 

Пример.

Следующее экви-соединение возвращает имя, должность, номер отдела и имя отдела для всех клерков:               

SELECT ename,job,dept.deptno,dname

               FROM emp,dept

               WHERE  emp.deptno=dept.deptno

                     AND job=’CLERK’

 

ENAME

JOB

DEPTNO

DNAME

SMITH

CLERK

20

RESEARCH

ADAMS

CLERK

20

RESEARCH

JAMES

CLERK

30

SALES

MILLER

CLERK

10

ACCOUNTING

 

Авто соединения

Авто соединение – соединение таблицы само в себя. Эта таблица дважды появляется в клаузе FROM и определяется синонимами, которые используются для определения столбцов в условии соединения. Для выполнения этого соединения Oracle комбинирует и возвращает строки таблицы, которые удовлетворяют условию соединения.

Пример.

Показать имена каждого служащего вместе с именем его руководителя:

SELECT e1.ename||' работает на '||e2.ename"Служащие и их Менеджеры"

       FROM emp e1,emp e2

       WHERE e1.mgr=e2.empno

Служащие и их Менеджеры

--------------------------------------

SMITH работает на FORD

ALLEN работает на ВLAКЕ

WARD работает на BLAKE

JONES работает на KING

MARTIN работает на BLAKE

BLAKE работает на KING

CLARK работает на KING

SCOTT работает на JONES

TURNER работает на BLAKE

ADAMS работает на SCOTT

JAMES работает на BLAKE

FORD работает на JONES

MILLER работает на CLARK

 

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

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

Внешние соединения

Внешние соединения расширяют результат простого соединения. Внешнее соединение возвращает все строки, которые удовлетворяют условию соединения и те строки из одной таблицы, для которых нет строк из другой, удовлетворяющие условию соединения. Для написания запроса, который выполняет внешнее соединение таблиц A и B и возвращает все строки из А применяют оператор внешнего соединения (+) ко всем столбцам таблицы B в условии соединения. Для всех строк в А, не имеющих пары в B, Oracle возвращает NULL.

Пример.

Показать имя, должность, номер отдела и имя отдела для всех клерков, используя внешнее соединение.

SELECT ename,job,dept.deptno,dname

             FROM emp,dept

             WHERE emp.deptno(+)=dept.deptno

ENAME

JOB

DEPTNO

DNAME

CLARK

MANAGER

10

ACCOUNTING

KING

PRESIDENT

10

ACCOUNTING

MILLER

CLERK

10

ACCOUNTING

SMITH

CLERK

20

RESEARCH

ADAMS

CLERK

20

RESEARCH

FORD

ANALYST

20

RESEARCH

SCOTT

ANALYST

20

RESEARCH

JONES

MANAGER

20

RESEARCH

ALLEN

SALESMAN

30

SALES

BLAKE

MANAGER

30

SALES

MARTIN

SALESMAN

30

SALES

JAMES

CLERK

30

SALES

TURNER

SALESMAN

30

SALES

WARD

SALESMAN

30

SALES

 

 

40

OPERATIONS

В этом внешнем соединении Oracle возвращает строку, содержащую отдел OPERATIONS даже, если в нём никто не работает. Oracle возвращает NULL для остальных столбцов.

 

Подзапросы

Подзапрос – это форма команды SELECT, которая появляется внутри другого SQL-оператора.Оператор, содержащий подзапрос называется родительским. Строки, возвращённые подзапросом используются родительским оператором.

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

Подзапрос также может содержать в себе подзапрос. Oracle не ограничивает уровень вложенности подзапросов.

Пример.

Определить служащих, работающих в одном отделе вместе с Тейлором:

SELECT ename,deptno

             FROM ename

             WHERE   deptno=(SELECT deptno

                                                       FROM emp

                                                        WHERE ename=’TAYLOR’ )

 

Коррелированные подзапросы

Коррелированный подзапрос – подзапрос, который вычисляется для каждой строки, сгенерированной родительским оператором.

Используется для ответа на запросы, содержащие несколько частей, ответы на которые зависят от значения в каждой строке, обрабатываемой родительским оператором. Например, коррелированный подзапрос можно использовать для определения какие служащие зарабатывают больше, чем  средняя зарплата для его отдела. В этом случае, коррелированный подзапрос вычисляет среднюю зарплату для каждого отдела:

SELECT deptno,ename,sal

                FROM emp x

                WHERE sal>(SELECT AVG(sal)

                                                FROM emp

                                                 WHERE x.deptno=deptno)

                ORDER BY deptno    

 

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

Oracle выполняет коррелированный подзапрос, если имеется ссылка в подзапросе на столбец таблицы, содержащейся в родительском операторе.

 

Похожие материалы

Информация о работе