|
|
я2НПКО Мекомп при МЧМ СССР
я2Введение в SQL
я2ORACLE
я2____________________________________________
я2Система управления реляционными базами данных
.
- i -
Содержание
1я2 Использование SQL
Введение - пример базы данных
2 Выбор данных из таблиц
3 Создание таблиц
5 Введение строк в таблицы
6 Выбор заданных строк и колонок из таблиц
9 Упорядочение строк по запросу
10 Предохранение от выбора строк-дубликатов
11 Запрос к нескольким таблицам - запросы JOIN
14 Арифметические операции и функции строк символов
15 Форматирование данных и арифметика
17 Вычисление функций групп строк
19 Подзапросы
20 Модификация хранимых данных
- команда UPDATE
- команда INSERT
- командa DELETE
22 Динамические изменения описания базы данных
27 Альтернативные виды данных
29 Независимость данных
32 Разделение данных и защита
.
- 1-1 -
я21 Использование SQL
Основные моменты
Данный пример показывает базовые концепции реляционной базы
данных и обеспечивает последовательность примеров, демонстрирую-
щих мощь языка реляционных данных SQL. В данном разделе вы узна-
ете как использовать SQL для:
* выборки (SELECT) строки из таблиц
* создания (CREATE) таблицы
* ввода (INSERT) строки в таблицы
Сперва давайте ближе рассмотрим структуру базы данных и оп-
ределим некоторые термины.
я2Базы данных и таблицы
я2Базы данных я0ORACLE состоят из я2таблиця0. Большинство примеров
в данном буклете будет использовать DEPT - пример таблицы, со-
держащей информацию об отделах компании, и использовать ЕМР,
пример таблицы, содержащей информацию о работниках данной компа-
нии.
я2Пример базы данных
Таблица DEPT
DEPTNO DNAME LOG
(отдел) назв отд) (место)
-------------------------------
10 ACCOUNTIG NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Таблица EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
(таб/н)(фамил)(должн) (стаж) (оклад) (комисс) (отдел)
----- ------ --------- ---- --------- -------- -------- ------
7369 SMITH CLERK 7902 17-DEC-80 800.00 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1,600.00 300.00 30
7521 WARD SALESMAN 7698 22-FEB-81 1,250.00 500.00 30
7566 JONES MANAGER 7839 02-APR-81 2,975.00 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1,250.00 1.400.00 30
7698 BLAKE MANAGER 7839 01-MAY-81 2,850.00 30
7782 CLARK MANAGER 7839 09-JUN-81 2,450.00 10
7788 SCOTT ANALYST 7566 09-NOV-81 3,000.00 20
7839 KING PRESIDENT 17-NOV-81 5,000.00 10
7844 TURNER SALESMAN 7698 08-SEP-81 1,500.00 0.00 30
7876 ADAMS CLERK 7788 23-SEP-81 1,100.00 20
7900 JAMES CLERK 7698 03-DEC-81 950.00 30
7902 FORD ANALYST 7566 03-DEC-81 3,000.00 20
7934 MILLER CLERK 7782 23-JAN-82 1,300.00 10
.
- 1-2 -
я2Колонки и строки
Каждая таблица состоит из я2колонок я0(вертикальных) и я2строкя0 (гори-
зонтальных). Таблица DEPT имеет три колонки (называемые DEPTNO,
DNAME и LOC) и 4 строки (одна для каждого отдела номер 10, 20,
30, 40).
я2Поля и значения
Строка состоит из я2полей. я0Каждое поле содержит я2значениея0 дан-
ных на пересечении строки и колонки. Например, в первой строке
таблицы DEPT значение 10 хранится в поле DEPTNO, значение
ACCOUNTING хранится в поле DNAME, значение NEW YORK - в поле
LOC.
(Примечание: Здесь и в последующих таблицах мы полагаем для удо-
бочитаемости формат колонки SQL - 999,999.99 для колонок SAL,
COMM и BUDGET, если специально не оговорен другой.)
.
- 2-1 -
я2Выбор данных из таблицы
я2Команда SELECT
Выборка данных из базы данных является наиболее распростра-
ненной операцией SQL. Обращение к базе данных называется я2 запро-
я2сомя0 и для его реализации вы должны использовать команду SELECT.
Базовая команда SELECT состоит из двух частей, носящих названия
я2клауз:
SELECT некоторые данные (имя (имена) колонки)
FROM таблица или некоторые таблицы (имя (имена) таблицы)
Клауза SELECT всегда вводится первой, а за ней следует
клауза FROM.
Давайте теперь рассмотрим данные в таблицах DEPT и EMP ис-
пользуя некоторые простые запросы SQL. Сперва мы перечислим все
колонки и все строки в таблице DEPT. Команды SQL, обеспечивающие
эти запросы, следующие:
SELECT DEPTNO,DNAME,LOG вводит пользователь
FROM DEPT; - " -
DEPTNO DNAME LOG вывод на дисплей
------ ---------- ----------
10 ACCOUNTIG NEW YORK |
20 RESEARCH DALLAS |
30 SALES CHICAGO | - результирующая таблица
40 OPERATIONS BOSTON |
я2SELECT *
В данном примере запроса мы перечисляем имена всех колонок
таблицы DEPT - DEPTNO, DNAME, LOC в вашей клаузе SELECT. Обычно
при задании я2 всех я0 колонок можно использовать звездочку (*) как
сокращение списка имен:
.
- 2-2 -
SELECT *
FROM EMP; ---- завершитель команды
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------ --------- ---- --------- -------- -------- ------
7369 SMITH CLERK 7902 17-DEC-80 800.00 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1,600.00 300.00 30
7521 WARD SALESMAN 7698 22-FEB-81 1,250.00 500.00 30
7566 JONES MANAGER 7839 02-APR-81 2,975.00 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1,250.00 1.400.00 30
7698 BLAKE MANAGER 7839 01-MAY-81 2,850.00 30
7782 CLARK MANAGER 7839 09-JUN-81 2,450.00 10
7788 SCOTT ANALYST 7566 09-NOV-81 3,000.00 20
7839 KING PRESIDENT 17-NOV-81 5,000.00 10
7844 TURNER SALESMAN 7698 08-SEP-81 1,500.00 0.00 30
7876 ADAMS CLERK 7788 23-SEP-81 1,100.00 20
7900 JAMES CLERK 7698 03-DEC-81 950.00 30
7902 FORD ANALYST 7566 03-DEC-81 3,000.00 20
7934 MILLER CLERK 7782 23-JAN-82 1,300.00 10
Для примеров данного буклета мы разделили большинство ко-
манд SQL на несколько строк и печатаем их большими буквами для
простоты чтения. Но если вы предпочитаете другие форматы, то мо-
жете набирать команды в одной строке большими или маленькими
буквами. Другими словами вы идентифицируете конец команды SQL
набрав точку с запятой (;) как последний символ последней стро-
ки. Когда ORACLE обнаружит ее, он запустит команду.
(Примечание: чтобы проще было ориентироваться в дальнейших
примерах ORACLE, обратитесь к таблицам DEPT и ЕМР, расположенным
в фольдинге на задней обложке данного буклета).
.
- 3-1 -
я2Создание таблицы
я2Команда CREATE TABLE
Перед тем как выбрать данные из базы данных, их нужно в нее
ввести, а перед этим нужно создать таблицу, в которой эти данные
будут храниться. Мы ввели следующую команду SQL для создания
таблицы DEPT:
CREATE TABLE DEPT (DEPTNO NUMBER (2), ---- имя таблицы
DNAME CHAR (14), ---- имя колонки
LOC CHAR (13)); ---- тип и длина данных
Table created (таблица создана) ---- вывод на дисплей
В команде CREATE TABLE сперва вы сообщаете ORACLE как наз-
вать вашу таблицу (DEPT). Далее вы задаете имена колонок таблицы
(DEPTNO, DNAME, LOC) и тип данных, которые каждая колонка содер-
жит. В создании данной таблицы, например, мы определили, что ко-
лонка DEPTNO содержит только цифровые данные (NUMBER), а колонки
DNAME и LOC любые символьные данные (CHAR) - буквы, числа или
знаки пунктуации и т.д. Наконец, вы задаете максимальную длину
любого значения, которое можно хранить в колонках. Например, в
команде CREATE TABLE, приведенной выше, задано, что длина имени
географического места (LOC) не должна превзойти 13 символов -
LOC CHAR(13).
Мы создали таблицу EMP при помощи следующей команды:
CREATE TABLE EMP (EMPNO NUMBER (4) NOT NULL, -- не пустая
ENAME CHAR (10),
JOB CHAR (9),
MGR NUMBER (4),
HIREDATE DATE, ---- тип данных даты
SAL NUMBER (7,2),
COMM NUMBER (7,2), ---- десятичные цифры
DEPTNO NUMBER (2));
я2Пустые значения не разрешены
В данной команде CREATE TABLE мы определили колонку EMPNO
таблицы ЕМР как непустую - NOT NULL. Это значит что каждая стро-
ка таблицы ЕМР я2должна я0содержать значение в поле EMPNO (ORACLE не
позволит вам ввести строку без этого значения). Другими словами
отсутствующие значения - называемые пустыми - НЕ допускаются в
этой колонке.
.
- 3-2 -
я2Атоматическая проверка данных
Спецификация SQL NOT NULL является одним из примеров того
как ORACLE проверяет значения данных при их вводе в базу данных,
чтобы убедиться, что они соответствуют я2условиям целостностия0, ко-
торые вы задали. Далее в этой главе вы увидите как задать что
значения в колонке должны быть типа UNIQUE. Спецификация UNIQUE
будет заставлять ORACLE автоматически предотвращать две строки с
одинаковыми значениями в одной колонке.
я2Тип данных даты
Колонка под названием HIREDATE определена для хранения дат.
я2Десятичные цифры
Колонки оклад (SAL) и комиссионные (COMM) определены как
цифровые данные (NUMBER) с максимальной длиной в 7 цифр, две из
которых находятся справа от десятичной точки - NUMBER(7,2).
.
- 4 -
я2Типы данных высокого уровня
Для обеспечения совместимости с SQL/DS фирмы IBM и DB2,
ORACLE имеет расширенный SQL для его большей функциональности и
дружелюбия. Одно такое расширение - это область типов данных ко-
лонок высокого уровня. ORACLE позволяет вам определять данные
как они появляются для вас я2внешне я0- как CHARACTER, NUMBER, DATE
или TIME. В противоположность этому SQL фирмы IBM требует, чтобы
вы определили данные так как они хранятся в компьютере я2внутренне
- как INTEGER, FLOATING POINT, DECIMAL и т.д. При работе с
ORACLE вам не нужно думать о внутреннем представлении данных в
памяти.
я2Сжатие данных
ORACLE не тратит зря память резервируя место для максималь-
ного количества символов, которое вы выделили для каждого поля.
Например, хотя и выделено для поля ENAME в строке 14 мест,
ORACLE мспользует только 4 места для WARD. Значения NULL - поля
COMM для работников, не получающих комиссионные, не занимают па-
мять вовсе.
.
- 6 -
я2Выбор заданных колонок и строк из таблиц
я2Выбор конкретной колонки
В первой паре примеров запросов мы выбрали все колонки и
все строки из таблицы. Если вы не хотите видеть все колонки таб-
лицы, то введите в клаузу SELECT имена отлько тех колонок, кото-
рые вам нужны.
SELECT DNAME, DEPTNO
FROM DEPT;
DNAME DEPTNO
----------- ------
ACCOUNTIG 10
RESEARCH 20
SALES 30
OPERATIONS 40
я2Управление последовательностью колонок
Как видите результат этого запроса - сама таблица, состоя-
щая из колонок и строк. Порядок, в котором вы перечислили колон-
ки, в клаузе SELECT, управляет порядком колонок в полученной
таблице. Когда вы задаете SELECT *, то последовательность появ-
ления колонок соответствует исходной.
я2Выбор заданных строк
В последнем примере вы видите как клауза SELECT позволяет
вам выбрать из таблицы заданные строки. Но что нужно сделать,
если вы захотите выбрать конкретные я2строкия0 из таблицы. Для этого
нужно ввести в команду SELECT клаузу WHERE.
SELECT *
FROM EMP
WHERE DEPTNO = 30; ------ клауза WHERE
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------ --------- ---- --------- -------- --------- ------
7499 ALLEN SALESMAN 7698 20-FEB-81 1,600.00 300.00 30
7521 WARD SALESMAN 7698 22-FEB-81 1,250.00 500.00 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1,250.00 1,400.00 30
7698 BLAKE MANAGER 7839 01-MAY-81 2,850.00 30
7844 TURNER SALESMAN 7698 08-SEP-81 1,500.00 0.00 30
7900 JAMES CLERC 7698 03-DEC-81 950.00 30
Клауза WHERE заставляет ORACLE искать данные в таблице и
выводить только те строки, которые удовлетворяют я2условиям поиска.
В примере выше ORACLE возвратит только те строки, ГДЕ номер от-
дела работника был равен 30 (WHERE DEPTNO = 30).
.
- 7-1 -
я2Несколько условий поиска
Иногда необходимо задать несколько условий поиска в клаузе
WHERE. Предположим, например, что вы хотите иметь список менед-
жеров вашей компании с окладом более 2800 долларов.
SELECT ENAME,JOB,SAL
FROM EMP
WHERE JOB = 'MANAGER'
AND SAL > 2800; ------ соединитель AND
ENAME JOB SAL
------ --------- ---------
JONES MANAGER 2,975.00
BLAKE MANAGER 2,850.00
Несколько условий поиска объединены словом AND (И)
(JOB='MANAGER' AND SAL>2800). Соединитель AND означает, что ваши
данные должны удовлетворять я2всем я0условиям поиска. Можно соеди-
нять по AND любое число условий.
я2Альтернативные условия поиска
В дополнение к возможности выбирать строки, соответствующие
я2всем я0условиям, можно выбирать и строки, соответствующие я2 любому
из нескольких условий.
SELECT ENAME,JOB,SAL
FROM EMP
WHERE JOB = 'MANAGER'
OR SAL > 2800; ------- соединитель OR
ENAME JOB SAL
------ --------- ---------
JONES MANAGER 2,975.00
BLAKE MANAGER 2,850.00
CLARK MANAGER 2,450.00
SCOTT ANALYST 3,000.00
KING PRESIDENT 5,000.00
FORD ANALYST 3,000.00
В этом примере мы соединили условия поиска словом OR (ИЛИ)
(JOB = 'MANAGER' OR SAL > 2800). OR значит, что если ваши данные
удовлетворяют я2одному я0из нескольких условий, то они будут выбраны.
.
- 7-2 -
я2Отрицательные условия поиска
Вы можете выбирать строки, я2не я0удовлетворяющие данному усло-
вию. Например, давайте перечислим всех менеджеров, которые не
работают в отделе 30.
SELECT ENAME,JOB,DEPTNO
FROM EMP
WHERE JOB = 'MANAGER'
AND DEPTNO != 30; --------- != означает не равно
ENAME JOB DEPTNO
------ --------- ---------
JONES MANAGER 20
CLARK MANAGER 10
Вы можете комбинировать AND, OR и NOT в одном запросе, чтобы
выбрать нужную вам информацию.
.
- 8-1 -
я2Поиск в диапазоне
Оператор BETWEEN позволяет вам выбирать строки в диапазоне,
который вы задали. Давайте перечислим всех работников, оклад ко-
торых между 1200 и 1400 долларов.
SELECT ENAME,SAL
FROM EMP
WHERE SAL BETWEEN 1200 AND 1400; ----- оператор BETWEEN
ENAME SAL
------ ---------
WARD 1,250.00
MARTIN 1,250.00
MILLER 1,300.00
я2Поиск значений в (IN) списке
Оператор IN дает возможность выбрать строки, содержащие за-
данные вами значения. Давайте перечислим все отделы, номера ко-
торых 10 или 30.
SELECT *
FROM DEPT
WHERE DEPTNO IN (10,30); ----- оператор IN
DEPTNO DNAME LOC
------- ---------- --------
10 ACCOUNTIG NEW YORK
30 SALES CHICAGO
Отметим, что мы заключили список значений в скобки - (10,30).
Для этого запроса мы смогли легко применить соединитель OR для
получения того же самого результата (WHERE DEPTNO = 10 OR 30).
.
- 8-2 -
я2Последовательности сопоставления символов
Можно также выбрать строки, соответствующие я2образцу я0симво-
лов или цифр, который вы зададите. Давайте перечислим всех ра-
ботников, имеющих в имени третье R.
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '__R%'; ----- оператор LIKE
ENAME
------
WARD
MARTIN
TURNER
FORD
В это примере используется оператор SQL LIKE для указанию
ORACLE выбрать все строки из таблицы EMP, в которых третья буква
в фамилии работника - R, то есть соответствует (LIKE) образцу,
который мы завели (__R%). Символ подчеркивания говорит об одной
позиции буквы, следовательно здесь пропущены две буквы, а знак
процента (%) задает любую строку без символов или с любым их ко-
личеством.
Операторы сравнения BETWEEN, IN и LIKE можно предварять
словом NOT (НЕ) и соединять с AND и OR для формирования сложных
клауз WHERE, предназначенных для выбора нужных строк.
.
- 9-1 -
я2Упорядочение строк по запросу
я2Упорядочивание строк
Во всех наших предыдущих примерах строки выводились на эк-
ран в порядке, заданном ORACLE. Вы можете управлять порядком
вывода строк на экран путем ввода клаузы ORDER BY (ПО) в конец
вашей команды SELECT. Например, если мы хотим список работников
отдела 30, упорядоченный по окладам.
SELECT SAL,JOB,ENAME
FROM EMP
WHERE DEPTNO = 30
ORDER BY SAL; ----- Клауза ORDER BY
SAL JOB ENAME
--------- --------- ------
950.00 CLERC JAMES
1,250.00 SALESMAN WARD
1,250.00 SALESMAN MARTIN
1,500.00 SALESMAN TURNER
1,600.00 SALESMAN ALLEN
2,850.00 MANAGER BLAKE
Клауза ORDER BY ведет сортировку строк по я2 увеличивающемуся
(малый оклад сперва) порядку. Но упорядочение не ограничивается
только одной последовательностью в одной колонке. Например, мы
можем перечислить всех работников по порядку работы и в нем по
окладу.
.
- 9-2 -
SELECT JOB,SAL,ENAME
FROM EMP
ORDER BY JOB, SAL, DESC; ---- порядок уменьшения (DESC)
JOB SAL ENAME
--------- --------- ------
ANALYST 3,000.00 SCOTT
ANALYST 3,000.00 FORD
CLERC 1,300.00 MILLER
CLERC 1,100.00 ADAMS
CLERC 950.00 JAMES
CLERC 800.00 SMITH
MANAGER 2,850.00 BLAKE
MANAGER 2,450.00 CLARK
PRESIDENT 5,000.00 KING
SALESMAN 1,600.00 ALLEN
SALESMAN 1,500.00 TURNER
SALESMAN 1,250.00 WARD
SALESMAN 1,250.00 MARTIN
Данные в колонке JOB поставлены в алфавитном порядке (по
умолчанию порядок для колонки CHAR, которую вы хотите упорядо-
чить ORDER BY) и работники в каждой группе упорядочены по окла-
дам в порядке уменьшения (SAL DESC).
.
- 10 -
я2Предохранение от выбора строк-дубликатов
Предположим, что вы хотите получить список различных работ
в таблице ЕМР.
SELECT JOB
FROM EMP
JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
Поскольку в вашем запросе нет клаузы WHERE, ORACLE возвра-
тит я2 все я0 значения в колонке JOB таблице ЕМР. Как видно, имеется
много дублирующих строк в колонке (это значит, что много работни-
ков имеют одну работу). Эти дублирующие значения можно исклю-
чить задавая в клаузе SELECT слово DISTINCT. Ключевое слово
DISTINCT мы используем в следующем запросе "показать разные
(DISTINCT) виды работ в таблице ЕМР".
SELECT DISTINCT JOB ----- ключевое слово DISTINCT
FROM EMP;
JOB
---------
CLERC
SALESMAN
MANAGER
ANALYST
PRESIDENT
Хотя в исходной таблице имеется 14 работ, мы оставили толь-
ко 5 типов.
.
- 11 -
я2Запрашивание нескольких таблиц
я2Запрос JOIN
До сих пор все наши примеры относились к работе с одной
таблицей (ЕМР или DEPT). Но что если информация, которую вы хо-
тите извлечь, находится в нескольких таблицах? Возможность
ORACLE я2join я0позволяет вам выбирать данные из двух или более таб-
лиц и объединять выбранные данные в одной общей таблице.
Предположим, что вы хотите знать, где работает работник по
фамилии ALLEN. Просматривая две таблицы в базе данных можно уви-
деть, что таблица ЕМР не содержит колонку LOC (местоположение),
а эта колонка находится в таблице DEPT. Однако обе таблицы имеют
колонку DEPTNO - номер отдела. Именно эти номера, содержащиеся в
обеих таблицах, позволят нам соотнести строки из таблицы ЕМР со
строками таблицы DEPT. Запрашивая таблицу ЕМР можно найти номер
отдела, где работает ALLEN.
SELECT ENAME, DEPTNO
FROM EMP
WHERE ENAME = 'ALLEN'
ENAME DEPTNO
----- ------
ALLEN 30
И запрашивая таблицу DEPT можно найти расположение отдела 30.
SELECT LOC
FROM DEPT
WHERE DEPTNO = 30
LOC
--------
CHICAGO
В результате использования этих двух запросов можно узнать,
что ALLEN работает в Чикаго.
. - 12 -
- 12 -
Но вы можете получить тот же самый результат используя
только один запрос JOIN. В этом запросе перечисляются названия
таблиц, которые вы запрашиваете в клаузе FROM и имена колонок
сравниваемых в обеих таблицах (то есть общие колонки) в клаузе
WHERE.
SELECT ENAME,LOC
FROM EMP,DEPT --- несколько таблиц
WHERE ENAME = 'ALLEN'
AND EMP.DEPTNO = DEPT.DEPTNO; --- условие соединения
ENAME LOC
------ --------
ALLEN CHICAGO
я2Условие соединения
я2Условия соединения я0 в клаузе WHERE определяет взаимосвязь
между таблицами EMP и DEPT. В вышеприведенном примере, например,
если DEPTNO в строке таблицы ЕМР я2совпадаетя0 ся2 я0 DEPTNO в строке
таблицы DEPT (EMP.DEPTNO=DEPT.DEPTNO), то строки можно соединить
вместе.
Таблица ЕМР Таблица DEPT
________________________________ _________________________
|EMPNO | ENAME | JOB | DEPTNO |--| DEPTNO | DNAME | LOC |
-------------------------------- -------------------------
| 7499 | ALLEN |SALESMAN| 30 | | 30 | SALES | CHICAGO|
-------------------------------- -------------------------
Соединение данных
Вдобавок к условиям соединения клауза WHERE содержит усло-
вие поиска ENAME='ALLEN', которое сообщает ORACLE выбрать только
строку с работником по имени ALLEN. Так что ORACLE соединяет
только эту строку из таблицы ЕМР, в которой содержится значение
30 в поле DEPTNO, со строкой из таблицы DEPT, в которой тоже со-
держится тот же номер 30. Колонки, перечисленные в клаузе SELECT,
приводят к выводу на экран только полей ENAME и DEPT.
.
- 13-1 -
Вы можете соединять отдельные строки (как в последнем при-
мере), части таблиц или таблицы целиком. Для перечисления назва-
ний отделов - информация, содержащаяся только в таблице DEPT,
вместе с другими данными о работниках давайте соединим строки из
таблицы ЕМР с таблицей DEPT и упорядочим результат.
SELECT DNAME,ENAME,JOB,SAL
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY DNAME,SAL DESC;
DNAME ENAME JOB SAL
------------ ------ --------- ---------
ACCOUNTIG KING PRESIDENT 5,000.00
ACCOUNTIG CLARK MANAGER 2,450.00
ACCOUNTIG MILLER CLERC 1,300.00
RESEARCH SCOTT ANALYST 3,000.00
RESEARCH FORD ANALYST 3,000.00
RESEARCH JONES MANAGER 2,975.00
RESEARCH ADAMS CLERC 1,100.00
RESEARCH SMITH CLERC 800.00
SALES BLAKE MANAGER 2,850.00
SALES ALLEN SALESMAN 1,600.00
SALES TURNER SALESMAN 1,500.00
SALES WARD SALESMAN 1,250.00
SALES MARTIN CLERC 950.00
я2Непроцедурные обработки наборов в единицу времени против
я2процедурных обработок записей в единицу времени
Даже этот простой пример дает понятие о силе SQL. С точки
зрения процедурного программирования одной записи в единицу вре-
мени работа, потребуется для соединения двух таблиц при выборе за-
данных полей из заданных строк и сортировки результата. Требуе-
мая программа становится даже более сложной при увеличении коли-
чества соединяемых таблиц. Это не проблема для ORACLE, поскольку
SQL работает по я2обработке наборовя0 я2в единицу времени непроцедурноя0.
Вы сообщаете ORACLE ЧТО вы хотите, а не как это получить.
.
- 13-2 -
я2Статические, основанные на указателях, DBMS против
я2динамических, основанных на значениях, DBMS
Иерархические и сетевые системы управления базами данных
хранят некоторую информацию типа я2значений я0и другого типа -я2 ука-
я2зателейя0. В сетевых системах, например, информация, что работник
SMITH имеет работу под названием CLERK, хранится как значение в
поле. Информация, что SMITH работает в отделе 20, будет хранить-
ся как я2указателья0 из записи SMITH на запись отдел 20. Только ин-
формация, хранимая в виде указателей, может быть использована
для сопоставления одной записи другой в сетевой или иерархичес-
кой базе данных. Более того, решение, хранить ли информацию в
виде указателей или значений, должно быть сделано при определе-
нии базы данных. Таким образом, сетевая или иерархическая сис-
тема имеет соотношения, я2основанные на указателяхя0, и предопреде-
лена как я2статическая.
В противоположность этому реляционные DBMS хранят все дан-
ные однотипным способом - как значения, хранимые в полях. Вы мо-
жете использовать любое значение для ассоциирования или соедине-
ния одной таблицы с другой и вы можете определить соотношения
между значениями когда вводите свой запрос, а НЕ когда создаете
свои таблицы. Короче, соотношения в реляционной базе данных явя-
лются я2 основанными на значенияхя0 и я2динамическимия0, что значит мак-
симальную гибкость представляемую вам ORACLE в ответ на ваши са-
мые неожиданные запросы.
.
- 14-1 -
я2Функции арифметических выражений и символьных строк
В то время как SQL фирмы IBM поддерживает 4 базовых арифме-
тических операций (+, -, *, /), ORACLE поддерживает полный набор
арифметических функцйй и функций манипулирования строками.
я2Арифметические выражения
Для создания арифметического выражения вы должны соединить
имена колонок и численные константы арифметическими операторами.
Перечислить имена, оклады, комиссионные и сумму окладов
я2плюс я0комиссионные для каждого продавца.
SELECT ENAME,SAL,CJMM,SAL+COMM ---- выражение суммы
FROM EMP
WHERE JOB = 'SALESMAN'
ENAME SAL COMM SAL+COMM
------ ---------- ---------- ----------
ALLEN 1,600.00 300.00 1,900.00
WARD 1,250.00 500.00 1,750.00
MARTIN 1,250.00 1,400.00 2,650.00
TURNER 1,500.00 0.00 1,500.00
Отметим, что наше арифметическое выражение (SAL + COMM) вы-
водится как новая колонка в результирующей таблице. Хотя такая
колонка как SAL + COMM не является реальной, то есть не храня-
щейся в базе данных, она материализовалась как результат запроса
- вы можете работать с ней как с реальной колонкой.
Примеры дополнительных арифметических операторов, поддержи-
ваемых ORACLE, перечислены ниже.
я2Функции символьных строк
Арифметические функции позволяют вам манипулировать числен-
ными данными; точно также функции символьных строк, такие как
описаны ниже, упрощают манипулирование символьными данными. Нап-
ример, вы можете использовать функцию SOUNDEX для поиска одина-
ково звучащих имен, пишущихся по-разному. Давайте найдем всех
работников, имена которых звучат как SCHMIDT.
SELECT ENAME
FROM EMP
WHERE SOUNDEX(ENAME) = SOUNDEX('SCHMIDT'); --- функция SOUNDEX
ENAME
------
SMITH
.
- 14-2 -
ORACLE поддерживает следующие арифметические операции, фун-
кции и функции символьных строк:
Арифметические операторы Строковые функции
----------------------------- -------------------------------
+ сложение | конкатенация
- вычитание DECODE транслировать
* умножение LENGTH длина строки
/ деление SUBSTR подстрока
POWER степень INSTR надстрока
ROUND округление UPPER большие буквы
TRUNC усечение LOWER маленькие буквы
ABS абсолютное значение SOUNDEX сопоставление по
звучанию
----------------------------- -------------------------------
Это только часть полного списка арифметических и строковых
функций ORACLE. (Смотри раздел по GROUP BY для обсуждения я2 груп-
я2повых функций я0ORACLE AVG, SUM, COUNT, MIN, MAX.)
.
- 15 -
я2Форматирование даты и арифметика даты
я2Форматирование даты
Вспомним, что когда мы создавали таблицу ЕМР, мы определили
колонку HIREDATE, как я2 тип данных датая0. Когда вы выбираете эту
колонку или любую колонку с типом данных дата, она выводится в
стандартном формате DD-MON-YY (например, 03-SEP-84). Вы можете
вывести на экран поля даты в разных форматах путем вызова форма-
тирующей функции ORACLE, TO_CHAR (имя колонки, 'рисунок'). Да-
вайте посмотрим на имя, работу и стаж всех работников в отделе
20 и выведем стаж в виде DY DD MON YYYY (день недели, день меся-
ца, месяц, год).
SELECT ENAME,JOB,TO_CHAR(HIREDATE,'DY DD MON YYYY') HIREDATE
FROM EMP ---------------
WHERE DEPTNO = 20 |
формат
ENAME JOB HIREDATE
----- -------- ---------------
SMITH CLERК WED 17 DEC 1980
JONES MANAGER THU 02 APR 1981
SCOTT ANALYST THU 09 DEC 1982
ADAMS CLERК WED 12 JAN 1983
FORD ANALYST THU 03 DEC 1981
При вызове функции TO_CHAR для изменения отображения на
дисплее даты стажа - TO_CHAR(HIREDATE ...) - мы описали рисунок
формата, который нужен нам - (... DY DD MON YYYY) - и по нему
была создана новая колонка.
Функция даты ORACLE предлагает широкий спектр форматирова-
ния. Здесь приведены некоторые примеры:
Картинка форматирования Форматированная дата
_________________________ _________________________________
Стандартный формат по умолчанию 17-DEC-80
DAY MONTH DD YYYY WEDNESDAY DECEMBER 17, 1980
Dy DD Mon YYYY Wed 17 Dec 1980
Day "the" ddth "of" Month YYYY Wednesday the 17th of December 1980
"The" DdSPTH "of" Month YyyySP The Seventeenth of December One
Thousand Nine Hundred Eighty
MM/DD/YY HH:MIPM 12/17/80 12:00AM
Отметим компоненту я2время я0в последнем отформатированном при-
мере. Колонка, которую вы определили как тип данных дата, содер-
жит год, месяц, день я2и я0время.
.
- 16 -
я2Числовая арифметика
Кроме форматирования полей данных по вашему желанию, можно
выполнить над числовыми полями арифметические операции.
Предположим, что вы просматриваете новых работников за 90
дней работы. Для вычисления данных по ним можно применить ариф-
метику ORACLE, например, вычислив даты просмотра работников от-
дела 20.
SELECT ENAME,HIREDATE,SYSDATE,HIREDATE + 90 --- прибавить 90
дней к дате
FROM EMP
WHERE HIREDATE + 90 > SYSDATE ----- SYSDATE = сегодняшней
дате
AND DEPTNO = 20;
ENAME HIREDATE SYSDATE HIREDATE + 90
------- --------- --------- -------------
SCOTT 09-DEC-82 04-MAR-83 09-MAR-83
ADAMS 12-JAN-83 04-MAR-83 12-APR-83
Ключевое слово SYSDATE всегда возвращает сеодняшнюю дату.
Выражение HIREDATE+90 в клаузе SELECT вычисляет новую дату и вы-
ражение HIREDATE+90 > SYSDATE в вашей клаузе WHERE приказывает
ORACLE возвратить строки только для работников, просмотр которых
был раньше чем за 90 дней от текущей даты. ORACLE может выпол-
нить следующие операции:
Функция Цель
_________________ ___________________________________________
ADD_MONTHS прибавить или вычесть из даты в месяцах
LAST_DAY переместить дату на последний день месяца
MONTHS_BETWEEN вычесть две даты, чтобы получить разность в
месяцах
NEXT_DAY переместить дату вперед на заданный день
недели
ROUND округлить данные по заданной точности
TRUNC отсечь данные до указанной точности
TO_DATE записывать даты в формате, отличном от формата
по умолчанию
Вы можете использовать поле данных в любой клаузе SQL,
SELECT, WHERE, ORDER BY и т.д., в которой можно использовать
числовые или символьные данные.
.
- 17-1 -
я2Вычисление функций для групп или строк
я2Групповые функции
Групповые функции - это еще один показатель мощи SQL. Эти
функции позволяют вам выбирать суммарную информацию из групп
строк. Предположим, что вы хотите собрать всех работников с од-
ним номером отдела в одну группу и найти максимальный оклад в
каждой группе.
SELECT DEPTNO,MAX(SAL) ----- функция МАХ
FROM EMP
GROUP BY DEPTNO; ----- клауза GROUP BY
DEPTNO MAX(SAL)
------ ----------
10 5,000.00
20 3,000.00
30 2,850.00
я2Клауза GROUP BY
В запросе GROUP BY каждая строка запроса представляет груп-
пу строк, хранимых в вашей таблице. Имя колонки, которую вы наз-
вали в клаузе GROUP BY, ( в данном примере DEPTNO) это то, что
вы хотите сгруппировать или выделить в категорию по строкам ва-
шей таблицы. В примере выше каждая строка таблицы ЕМР была поме-
щена в одну их трех групп, отличающихся номерами отделов - зна-
чениями поля DEPTNO, так что после того, как все строки в ЕМР
обработаны, все строки в группе имеют один номер отдела. В этой
точке ORACLE выполняет групповые функции, которые мы запросили
при помощи клаузы SELECT - MAX(SAL) - над каждой группой строк и
возвратит вычисленные результаты на дисплей.
Так же как с применением ORDER BY, вы можете группировать
любое количество колонок в единственном запросе.
Давайте скомбинируем использование групповых функций с зап-
росом JOIN. На этот раз мы сгруппируем по двум колонкам (GROUP
BY DNAME, JOB) и применим три новых функции: SUM - сложить зна-
чения выбранных полей, попавших в группу, заданную клаузой GROUP
BY; COUNT(*) - подсчитать число строк, попавших в каждую группу
и AVG - найти среднее арифметическое значение выбранных полей в
группе.
.
- 17-2 -
Наша цель при этом запросе узнать сколько работников рабо-
тает на каждой работе в каждом отделе, , например, сколько клер-
ков в группе исследования, какова сумма окладов и средний оклад.
SELECT DNAME,JOB,SUM(SAL), ----- функция SUM
COUNT(*), ----- функция COUNT(*)
AVG(SAL) ----- функция AVG
FROM EMP,DEPT
WHERE EMP.DEPT = DEPT.DEPTNO
GROUP BY DNAME,JOB; ----- 2 колонки GROUP BY
DNAME JOB SUM(SAL) COUNT(*) AVG(SAL)
------------ --------- --------- --------- ----------
ACCOUNTING CLERC 1,300.00 1 1,300.00
ACCOUNTING MANAGER 2,450.00 1 2,450.00
ACCOUNTING PRESIDENT 5,000.00 1 5,000.00
RESEARCH ANALYST 6,000.00 2 3,000.00
RESEARCH CLERC 1,900.00 2 950.00
RESEARCH MANAGER 2,975.00 1 2,975.00
SALES CLERC 950.00 1 950.00
SALES MANAGER 2,850.00 1 2,850.00
SALES SALESMAN 5,600.00 4 1,400.00
.
- 18 -
я2Процедурные против непроцедурных
Рассмотрим работу, которую выполняет программист при созда-
нии некоторого вывода, если он применяет процедурную, запись в
единицу времени, DBMS или стандартную файловую систему: сперва
он получает список работников и сортирует его по номерам отдела
и по работе. Далее, сумма, число работников и средний оклад вы-
числяются по номерам отделов и по работе. Наконец, название от-
дела нужно подставить для номера отдела в выводимой информации.
Каждая операция требует, чтобы программист читал записи, сохра-
нял промежуточные результаты, проверял ошибки и т.д. Непроце-
дурный подход SQL, набор в единицу времени, позволяет создавать
такие отчеты как предыдущий при помощи указания ORACLE только
того, ЧТО вы хотите: ORACLE автоматически генерирует процедуру,
определяя КАК извлечь данные из базы данных. Другие системы тре-
буют, чтобы вы строили процедуры сами в своей программе.
я2Клауза HAVING
Мы уже определили условия поиска для индивидуальных строк
при помощи клаузы WHERE и теперь можем использовать клаузу SQL
HAVING для задания условий поиска групп строк. Предположим, что
вы хотите получить ответ на запрос, подобный предыдущему, но при
условии, что каждая группа, выводимая по запросу должна содер-
жать не менее двух работников.
SELECT DNAME,JOB,SUM(SAL),COUNT(*),AVG(SAL)
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DNAME,JOB
HAVING COUNT(*) >= 2; ------ клауза HAVING
DNAME JOB SUM(SAL) COUNT(*) AVG(SAL)
------------ --------- ---------- --------- ----------
RESEARCH ANALYST 6,000.00 2 3,000.00
RESEARCH CLERC 1,900.00 2 950.00
SALES SALESMAN 5,600.00 4 1,400.00
Условие поиска в клаузе HAVING исключает из результата зап-
роса группы, содержащих менее двух работников - COUNT(*) >= 2.
.
- 19 -
я2Подзапросы
Одна из причин, по которым SQL так силен, в том, что вы мо-
жете построить сложные запросы из нескольких простых. Клауза
WHERE из одной запроса может содержать другой запрос, под назва-
нием я2подзапрося0. Вы можете использовать подзапросы для динамичес-
кого построения условий поиска для вашего главного запроса.
Предположим, что вы хотите составить список всех работников
с такой же работой как у работника JONES.
SELECT ENAME,JOB ----
FROM EMP |-- главный запрос
WHERE JOB = ----
(SELECT JOB ----
FROM EMP |-- подзапрос
WHERE ENAME = 'JONES'); ----
ENAME JOB
------ ------------
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
ORACLE обрабатывает подзапросы перед обработкой главного
запроса поскольку ему нужны их результаты для определения ре-
зультата главного запроса. Вторая команда SELECT в данном приме-
ре - подзапрос - возвращает значение MANAGER, как вы можете убе-
диться, если взглянете на профессию работника JONES в таблице
ЕМР. ORACLE использует это значение для динамического построения
условия поиска WHERE JOB = 'MANAGER', используемого первой ко-
мандой SELECT для возврата нужных нам строк.
Далее, давайте перечислим всех работников, кто получает
больше среднего оклада.
SELECT ENAME,SAL
FROM EMP
WHERE SAL >
(SELECT AVG(SAL)
FROM EMP);
ENAME SAL
------ ----------
JONES 2,975.00
BLAKE 2,850.00
CLARK 2,450.00
SCOTT 3,000.00
KING 5,000.00
FORD 3,000.00
Единственный запрос ORACLE можно сделать из нескольких под-
запросов. Подзапросы могут тоже быть составными, как и основные
запросы - они могут содержать условия поиска и запросы JOIN свои
собственные, например, и вы можете использовать их в командах
INSERT, UPDATE и DELETE (рассматриваемых в следующих секциях)
наряду с командами SELECT.
.
- 20-1 -
я2Модификация хранимых данных
я2Основные моменты
Как вы уже убедились одна команда SELECT позволяет выбрать
набор строк из одной или нескольких таблиц. SQL позволяет вам
добавлять или модифицировать строки по набору в единицу времени
достаточно просто при помощи следующих трех команд:
* команда UPDATE - изменяет значения, хранимые в полях
* команда INSERT - добавляет строки в таблицу
* команда DELETE - удаляет строки из таблицы
я2Команда UPDATE
Используя наш пример базы данных при повышении окладов всех
клерков на 100 долларов потребуется обновить или изменить значе-
ния некоторых данных (поля SAL) в некоторых строках (тех, в ко-
торых значение для JOB - CLERK) в таблице ЕМР. Команда UPDATE
состоит из клаузы UPDATE вместе с клаузой SET и опционно клаузой
WHERE.
UPDATE EMP ------ клауза UPDATE
SET SAL = SAL + 100 ------ клауза SET
WHERE JOB = 'CLERK';
4 records updated ----- вывод на дисплей
Клауза UPDATE называет таблицу, которую вы хотите модифици-
ровать (UPDATE EMP). Клауза SET "устанавливает" поле, которое
вам нужно модифицировать в некоторое значение (SET SAL = SAL +
100). В клаузе WHERE вы задаете набор строк для обновления
(WHERE JOB = 'CLERK').
Запрос подтвердит, что обновление имело место.
SELECT ENAME,JOB,SAL
FROM EMP
WHERE JOB = 'CLERК';
ENAME JOB SAL
------ ---------- ----------
SMITH CLERК 900.00
ADAMS CLERК 1,200.00
JAMES CLERК 1,050.00
MILLER CLERК 1,400.00
.
- 20-2 -
я2Команда INSERT
Ранее в этой главе вы увидели как использовать команду
INSERT для добавления строк в таблицу одна в единицу времени. Вы
можете использовать эту команду и для добавления набора строк в
таблицу: для копирования строк из одной таблицы в другую. Вы де-
лаете это используя запрос для определения набора строк, вводи-
мых в таблицу.
Предположим, что в нашем примере базы данных мы имеем
третью таблицу по имени PROMOTION. Эта таблица содержит многие
колонки такие же как в ЕМР: ENAME, JOB, SAL COMM. Мы хотим ввес-
ти в эту таблицу данные для всх продавцов, имеющих комиссионные
больше 25% из оклада путем копирования информации из таблицы ЕМР.
INSERT INTO PROMOTION (ENAME,JOB,SAL,COMM)
SELECT ENAME,JOB,SAL,COMM
FROM EMP
WHERE COMM > 0.25 * SAL;
2 records created.
Команда INSERT в данном примере использует подзапрос вместо
списка значений данных , которые нужно видеть в этой команде.
ORACLE вводит в таблицу PROMOTION строки и колонки, выбранные из
ЕМР в нашем подзапросе.
.
- 21 -
я2Команда DELETE
Поскольку в отделе 40 нет работников, давайте его удалим из
таблицы DEPT при помощи команды SQL DELETE.
DELETE FROM DEPT ----- ввод пользователя
WHERE DEPTNO = 40;
1 records deleted. ----- вывод на экран
Теперь можно использовать запрос, чтобы убедиться, что уда-
ление имело место.
SELECT *
FROM DEPT;
DEPTNO DNAME LOC
------ ------------ -----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
Клауза DELETE FROM называет таблицу, из которой вы хотите
удалить строку или набор строк (в данном случае DELETE FROM
DEPT). Клауза WHERE опционна (клауза DELETE FROM без нее будет
означать, что вы хотите удалить ВСЕ строки из таблицы), она за-
дает условия, при которых нужно удалять строки. В этом примере
мы использовали клаузу WHERE, чтобы сообщить ORACLE УДАЛИТЬ ИЗ
строки таблицы DEPT, у которых значение в поле DEPTNO равно 40.
я2SQL - язык для всех функций
Клаузы WHERE во всех командах демонстрируют как SQL исполь-
зует одинаковый синтаксис для обработки данных и для запросов. В
командах SELECT, UPDATE, INSERT, DELETE функционирование клаузы
WHERE идентично: она всегда опционна и используется для задания
условий, при которых нужно что-либо делать с данными, модифици-
ровать или обновлять. Далее вы увидите как SQL использует одина-
ковый синтаксис запросов для определения данных.
.
- 22 -
я2Динамические изменения в описании
я2базы данных
ORACLE поддерживает команды SQL, необходимые для динамичес-
кого изменения структуры базы данных. В данном разделе вы увиди-
те как можно применить SQL для:
* ALTER TABLE ADD - добавления новой колонки в существующую та-
блицу
* ALTER TABLE MODIFY - увличения существующей колонки
Давайте расширим нашу примерную базу данных так, что мы
сможем назначить работников для проектов также как назначали их
для отделов. Это изменение структуры базы данных требует нес-
кольких шагов. Сперва при помощи команды CREATE TABLE добавим
таблицу проектов в базу данных.
я2Создание таблицы PROJ
CREATE TABLE (PROJNO NUMBER(3) NOT NULL,
PNAME CHAR(5)<
BUDGET NUMBE(7,2));
Далее мы используем команду INSERT для помещения некоторых
данных в таблицу PROJ
я2Введение строк в таблицу PROJ
INSERT INTO PROJ VALUES (101, 'ALPHA', 96000)
INSERT INTO PROJ VALUES (101, 'BETA', 82000)
INSERT INTO PROJ VALUES (101, 'GAMMA', 96000)
Давайте просмотрим таблицу PROJ, чтобы увидеть, что мы имеем.
SELECT * FROM PROJ;
PROJNO PNAME BUDGET
------ ----- --------
101 ALPHA 96,000
102 BETA 82,000
103 GAMMA 15,000
В третьих, поскольку мы в конечном счете соединили таблицы
EMP и PROJ, давайте добавим колонку номеров проектов в таблицу
EMP, так что две таблицы разделят общую колонку. Мы используем
команду ALTER TABLE для добавления новой колонки в существующую
таблицу.
я2Добавление колонки PROJ в таблицу ЕМР
ALTER TABLE EMP ADD (PROJNO NUMBER(3));
Table altered.
В данной команде мы назвали таблицу, которую нужно изменить
(в данном случае ЕМР), колонку, которую нужно добавить в нее
(PROJNO), новый тип данных колонки и максимальную длину поля в
новой колонке - NUMBER(3).
.
- 23-1 -
Теперь мы можем просмотреть таблицу ЕМР и отметить, что
добавлено в каждую строку - новое поле PROJNO с начальным значе-
нием пустым.
SELECT *
FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO PROJNO
----- ------ --------- ----- --------- --------- ------- ------ ------
7369 SMITH CLERC 7902 17-DEC-80 900.00 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1,600.00 300.00 30
7521 WARD SALESMAN 7698 22-FEB-81 1,250.00 500.00 30
7566 JONES MANAGER 7839 02-APR-81 2,975.00 20
7654 MATRIN SALESMAN 7698 28-SEP-81 1,250.00 1,400.00 30
7698 BLAKE MANAGER 7839 01-MAY-81 2,850.00 30
7782 CLARK MANAGER 7839 09-JUN-81 2,450.00 10
7788 SCOTT ANALYST 7566 18-JUN-83 3,000.00 20
7839 KING PRESIDENT 17-NOV-81 5,000.00 0.00 10
7844 TURNER SALESMAN 7698 08-SEP-81 1,500.00 30
7876 ADAMS CLERC 7788 22-JUL-81 1,200.00 20
7900 JAMES CLERC 7698 03-DEC-81 1,050.00 30
7902 FORD ANALYST 7566 03-DEC-81 3,000.00 20
7934 MILLER CLERC 7782 23-JAN-82 1,400.00 10
После введения новой колонки в таблицу ЕМР, мы применим ко-
манду UPDATE для соотнесения работников с проектами. Давайте соот-
несем всех работников отдела 20 и всех продавцов проекту 101.
я2Соотнесение некоторых работников с проектом 101
UPDATE EMP
SET PROJNO = 101
WHERE DEPTNO = 20
OR JOB = 'SALESMAN';
9 records updated.
.
- 23-2 -
Давайте выведем на экран полученную таблицу ЕМР для уверен-
ности во введенных изменениях.
SELECT *
FROM EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO PROJNO
----- ----- --------- ----- --------- --------- -------- ------ ------
7369 SMITH CLERC 7902 17-DEC-80 900.00 20 101
7499 ALLEN SALESMAN 7698 20-FEB-81 1,600.00 300.00 30 101
7521 WARD SALESMAN 7698 22-FEB-81 1,250.00 500.00 30 101
7566 JONES MANAGER 7839 02-APR-81 2,975.00 20 101
7654 MARTIN SALESMAN 7698 28-SEP-81 1,250.00 1,400.00 30 101
7698 BLAKE MANAGER 7839 01-MAY-81 2,850.00 30
7782 CLARK MANAGER 7839 09-JUN-81 2,450.00 10
7788 SCOTT ANALYST 7566 18-JUN-81 3,000.00 20 101
7839 KING PRESIDENT 17-NOV-81 5,000.00 10
7844 TURNER SALESMAN 7698 08-SEP-81 1,500.00 0.00 30 101
7876 ADAMS CLERC 7788 22-JUL-81 1,200.00 20 101
7900 JAMES CLERC 7698 03-DEC-81 1,050.00 30
7902 FORD ANALYST 7566 03-DEC-81 3,000.00 20 101
7934 MILLER CLERC 7782 22-JAN-82 1,400.00 10
.
- 24 -
Давайте теперь поместим всх, кто не отнесен к данному проекту (то
есть WHERE PROJNO IS NULL), в проект 102.
я2Соотнесение других работников с проектом 102
UPDATE EMP
SET PROJNO = 102
WHERE PROJNO IS NULL;
5 records updated.
Другой запрос в таблицу ЕМР показывает, что все работники
теперь имеют значения в поле PROJNO.
SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO PROJNO
----- ------ --------- ----- --------- --------- -------- ------ ------
7369 SMITH CLERC 7902 17-DEC-80 900.00 20 101
7499 ALLEN SALESMAN 7698 20-FEB-81 1,600.00 300.00 30 101
7521 WARD SALESMAN 7698 22-FEB-81 1,250.00 500.00 30 101
7566 JONES MANAGER 7839 02-APR-81 2,975.00 20 101
7654 MARTIN SALESMAN 7698 28-SEP-81 1,250.00 1,400.00 30 101
7698 BLAKE MANAGER 7839 01-MAY-81 2,850.00 30 102
7782 CLARK MANAGER 7839 09-JUN-81 2,450.00 10 102
7788 SCOTT ANALYST 7566 18-JUN-83 3,000.00 20 101
7839 KING PRESIDENT 17-NOV-81 5,000.00 10 102
7844 TURNER SALESMAN 7698 08-SEP-81 1,500.00 0.00 30 101
7876 ADAMS CLERC 7788 22-JUL-81 1,200.00 20 101
7900 JAMES CLERC 7698 03-DEC-81 1,050.00 30 102
7902 FORD ANALYST 7566 03-DEC-81 3,000.00 20 101
7934 MILLER CLERC 7782 23-JAN-82 1,400.00 10 102
.
- 25 -
Обновление строк работников с номерами проектов завершает
модификацию нашей базы данных и позволяет нам соотносить работ-
ников с проектами так же как с отделами.
я2Присоединение таблицы ЕМР к таблице PROJ
SELECT ENAME,JOB,DEPTNO,PNAME
FROM EMP,PROJ
WHERE EMP,PROJNO = PROJ.PROJNO;
ENAME JOB DEPTNO PNAME
------ --------- ------ -----
SMITH CLERК 20 ALPHA
ALLEN SALESMAN 30 ALPHA
WARD SALESMAN 30 ALPHA
JONES MANAGER 20 ALPHA
MARTIN SALESMAN 30 ALPHA
SCOTT ANALYST 20 ALPHA
TURNER SALESMAN 30 ALPHA
ADAMS CLERК 20 ALPHA
FORD ANALYST 20 ALPHA
BLAKE MANAGER 20 BETA
CLARK MANAGER 10 BETA
KING PRESIDENT 10 BETA
JAMES CLERК 30 BETA
MILLER CLERК 10 BETA
я2Гибкость ORACLE через динамические изменения структуры данных
При помощи этой серии примеров мы продемонстрировали лег-
кость с которой можно динамически расширять и реструктурировать
базу данных ORACLE. Сперва мы создали новую таблицу. Затем доба-
вили в нее новую колонку. После этого поместили данные в эту ко-
лонку. И наконец мы слили новую и старую таблицу. Мы выполнили
все четыре операции с помощью простых команд SQL без вмешатель-
ства системы для загрузки и перезагрузки данных или реорганиза-
ции какой-то части базы данных.
.
- 26-1 -
я2Команда ALTER TABLE MODIFY
В дополнение к возможности добавлять новые колонки в сущес-
твующие таблицы, вы можете увеличить существующие колонки.
Отметим, например, что мы ввели значение 15000 для бюджета
в проекте 103. Предположим, что произошла ошибка и правильное
значение в действительности равно 105000. Мы будем вынуждены об-
новить строку проект 103 с правильным значением бюджета. Давайте
это сделаем.
UPDATE PROJ ----- ввел пользователь
SET BUDGET = 105000
WHERE PROJNO = 103;
|---- это появилось на экране
|
ERROR: value larger than specified precision allows for column
(ошибка: для колонки дано значение больше чем разрешено)
Мы получили сообщение об ошибке поскольку сумма нового бюд-
жета, которую мы попытались ввести, была слишком велика чтобы
поместиться в колонку BUDGET, определенную в команде CREATE
TABLE как NUMBER(7,2). Наше определение значит, что допускаются
числа не больше чем 99,999.99 (7 цифр из которых две справа от
десятичной точки). Эту проблему можно легко решить используя ко-
манду ALTER TABLE MODIFY для увеличения колонки.
я2Сделать колонку больше
ALTER TABLE PROJ MODIFU BUDGET NUMBER(8,2);
Table altered.я2 ---- я0таблица изменена
Также как в команде ALTER TABLE ADD мы назвали таблицу, ко-
торую должны изменить (PROJ) и описали нужное изменение. В дан-
ном примере нам нужно модифицировать определение колонки BUDGET,
так что мы переделаем тип данных колонки (NUMBER) и зададим но-
вую, увеличенную длину (8,2 вместо 7,2). Теперь можно обновить
таблицу снова.
.
- 26-2 -
UPDATE PROJ
SET BUDGET = 105000
WHERE PROJNO = 103
1 record updated. ----- одна запись обновлена
Давайте используем запрос для уверенности, что обновление
имело место.
SELECT *
FROM PROJ;
PROJNO PNAME BUDGET
------ ----- ----------
101 ALPHA 96,000.00
102 BETA 82,000.00
103 GAMMA 105,000.00
Никакие другие базы данных, ни обычные, ни реляционные, не
дают такой возможности добавлять колонки к существующим таблицам
или увеличивать размер существующей колонки без реорганизации
какой-либо части базы данных.
.
- 27 -
я2Альтернативные виды данных
ORACLE позволяет вам проектировать и хранить альтернативные
я2виды я0- или способы просмотра одних и тех же данных, хранящихся в
базе данных. Виды - это я2виртуальные таблицы я0- как окна через ко-
торые можно просматривать данные, хранимые в я2реальных таблицахя0.
Виды не содержат данных самих по себе, а то как они выглядят и
могут обрабатываться, как если бы они были в реальных таблицах.
Виды служат трем основным целям, они:
* упрощают доступ к данным
* обеспечивают независимость данных
* обеспечивают защиту данных
Сперва давайте посмотрим на то как создаются виды и как их
можно использовать для упрощения доступа к данным.
Давайте создадим вид таблицы ЕМР, который будет поднабором
всей таблицы: такая таблица, которая покажет нам только номера,
фамилии и работу работников, занятых в отделе 10.
CREATE VIEW EMP10 AS SELECT EMPNO,ENAME,JOB --- команда CREATE
FROM EMP VIEW
WHERE DEPTNO = 10;
View created. ---- вид создан
Поскольку результат запроса - таблица, то запрос использу-
ется для определения виртуальной таблицы или вида. Вот как SQL
использует один и тот же синтаксис для определения данных - оп-
ределение вида - такого специфического - как обработка данных и
запрос. В команде CREATE VIEW вы называете вид, а затем описыва-
ете, в форме оператора запроса SQL, что вид должен содержать.
С несколькими только ограничениями вы можете запрашивать и
модифицировать виды также как если бы они были реальными табли-
цами.
Давайте посмотрим наш вид.
я2Запрос вида
SELECT *
FROM EMP10;
EMPNO ENAME JOB
----- ------ ----------
7782 CLARK MANAGER
7839 KING PRESIDENT
7934 MILLER CLERК
.
- 28-1 -
Для некоторых пользователей проще работать с одной таблицей,
чем с несколькими. ORACLE позволяет вам создавать единственный
вид таблицы из двух или более таблиц. То есть, вы можете опреде-
лить ваш вид как ВЫБРАННЫЙ из нескольких таблиц вместо которых
можно запрашивать индивидуально сделанное соединение (ваш вид),
который можно использовать всякий раз как нужно задать запрос на
соединение каждый раз.
Давайте создадим единственный вид таблицы по имени
PERSONNEL с колонками ENAME, JOB, PNAME из таблиц ЕМР и PROJ и
затем посмотрим его.
я2Создание соединенного вида
CREATE VIEW PERSONNEL AS SELECT ENAME,JOB,PNAME
FROM EMP,PROJ
WHERE EMP.PROJNO = PROJ.PROJNO;
View created. ---- вид создан
SELECT *
FROM PERSONNEL;
ENAME JOB PNAME
------ --------- -----
SMITH CLERC ALPHA
ALLEN SALESMAN ALPHA
WARD SALESMAN ALPHA
JONES MANAGER ALPHA
MARTIN SALESMAN ALPHA
SCOTT ANALYST ALPHA
TURNER SALESMAN ALPHA
ADAMS CLERC ALPHA
FORD ANALYST ALPHA
BLAKE MANAGER BETA
CLARK MANAGER BETA
KING PRESIDENT BETA
JAMES CLERC BETA
MILLER CLERC BETA
.
- 28-2 -
После создания вида пользователь не сможет узнать, что дан-
ные, которые он просматривает, хранятся в двух таблицах. Более
того, вы можете считать, что ваши данные как бы хранятся в одной
таблице. Вот как вы можете использовать виды для упрощения дос-
тупа к данным, уменьшения длины строк и сокращения количества
ошибок.
Используя вид PERSONNEL, давайте перчислим имена менеджеров
и их проекты.
SELECT ENAME,PNAME
FROM PERSONNEL
WHERE JOB = 'MANAGER'
ENAME PNAME
----- -----
JONES ALPHA
BLAKE BETA
CLARK BETA
я2Виды упрощают доступ к данным
Отметим, как прост этот запрос вида PERSONNEL по сравнению
с запросом JOIN, требуемым для аналогичных действий - генерации
этого списка из двух таблиц ЕМР и PROJ.
.
- 29-1 -
я2Независимость данных
Виды освобождают пользователя от необходимости знать, в ка-
ких таблицах находятся данные с которыми мы работаем. Это разни-
ца в том какие данные пользователь видит и как они хранятся, но-
сит название я2независимость данных. я0Мы увидим как эта разница уп-
рощает доступ к данным в базе данных. Но вы можете использовать
эти виды и для хранения ваших запросов, программ и так далее,
предохраняя их от устаревания при изменениях базы данных.
Текущая структура базы данных позволяет присвоить несколько
работников проекту, но каждый должен работать только по одному
проекту - соотношение многие к одному. Предположим, что наши
процедуры по персоналу изменились и сейчас мы хотим присоединить
работников к нескольким проектам. Чтобы это выполнить мы дол-
жны пересмотреть структуру нашей базы данных преследуя две цели.
Первая цель - предохранить старые виды нашей базы данных так что
мы сможем продолжать работы со всеми прикладными системами, отче-
тами и программами, зависящими от исходной структуры базы данных.
Вторая цель, конечно, иметь возможность использовать наши но-
вые данные - дополнительные проекты - для построения новых прик-
ладных систем. Вот как мы построим основы, которые позволят со-
существование этих двух версий данных:
Сперва мы создаем новую таблицу, соотносящую работников с
проектами и содержащую информацию об участии конкретных работ-
ников в конкретном проекте - часы работы, как показано в примере
ниже:
я2Новая структура базы данных позволяет иметь соотношения
я2многие-к-многим
CREATE TABLE PE (EMPNO NUMBER(4),
PROJNO NUMBER(3)'
WORKHRS NUMBER(4));
Table created.я2 ----- я0таблица создана
Далее, мы пприсоединяем работников к проектам путем введе-
ния строк в таблицу РЕ. Отметим, что работник под номером 7369
имеет две строки в этой таблице: одна для проекта 101 и другая -
для 102.
.
- 29-2 -
я2Присоединение работников к проектам
INSERT INTO PE VALUES (7369,101,0);
INSERT INTO PE VALUES (7369,102,0);
INSERT INTO PE VALUES (7499,101,0);
INSERT INTO PE VALUES (7521,101,0);
INSERT INTO PE VALUES (7566,101,0);
INSERT INTO PE VALUES (7654,101,0);
INSERT INTO PE VALUES (7698,102,0);
INSERT INTO PE VALUES (7782,102,0);
INSERT INTO PE VALUES (7788,101,0);
INSERT INTO PE VALUES (7839,102,0);
INSERT INTO PE VALUES (7844,101,0);
INSERT INTO PE VALUES (7876,101,0);
INSERT INTO PE VALUES (7900,102,0);
INSERT INTO PE VALUES (7902,101,0);
INSERT INTO PE VALUES (7934,102,0);
В будущем, если работник будет присоединен ко второму или
третьему проекту, то мы просто введем дополнительную строку для
этого работника в таблицу РЕ.
.
- 30-1 -
Поскольку теперь мы используем таблицу РЕ для соотношения
работников с проектами, нам больше не нужна информация, хранимая
в колонке PROJNO таблицы ЕМР. Мы можем удалить эти данные путем
установки значений этой колонки в пустые - NULL.
UPDATE EMP
SET PROJNO = NULL;
14 records updated. - 14 записей обновлено
(Вспомним, что отсутствие клаузы WHERE приказывает ORACLE обно-
вить все строки в таблице.)
Поскольку мы установили все PROJNO в пустые, мы больше не
можем использовать вид PERSONNEL, который использует колонку
PROJNO для связи таблиц ЕМР и PROJ. Для генерации этой информа-
ции снова нам нужно соединить таблицу ЕМР с новой таблицей РЕ и
соединить таблицу РЕ с нашей таблицей PROJ. Но мы можем спрятать
это изменение от пользователей, использующих вид PERSONNEL просто
изменив определение вида. Первый шаг в изменении вида PERSONNEL
- отбросить старое определение:
я2Отбросить устаревший вид PERSONNEL
DROP VIEW PERSONNEL;
View dropped. ---- вид отброшен
Теперь мы можем создать новый вид PERSONNEL, объединяющий
таблицу ЕМР и таблицу PROJ через таблицу РЕ.
я2Создать новый вид PERSONNEL
CREATE VIEW PERSONNEL AS SELECT ENAME,JOB,PNAME
FROM EMP,PROJ,PE
WHERE EMP.EMPNO = PE.EMPNO
AND PE.PROJNO = PROJ.PROJNO;
View created. ---- вид создан
.
- 30-2 -
Теперь программы, использующие старый вид PERSONNEL, могут
продолжать работу без модификации и пользователи могут работать
со старыми данными в общем случае не боясь изменений в базе дан-
ных. Запрашивая новый вид PERSONNEL мы увидим такую же информа-
цию как для старого вида PERSONNEL я2 плюс я0 второй проект для
работника SMITH:
SELECT *
FROM PERSONNEL
ORDER BY ENAME;
ENAME JOB PNAME
------ --------- ------
ADAMS CLERK ALPHA
ALLEN SALESMAN ALPHA
BLAKE MANAGER ALPHA
CLARK MANAGER ALPHA
FORD ANALYST ALPHA
JAMES CLERK BETA
JONES MANAGER ALPHA
KING PRESIDENT BETA
MARTIN SALESMAN ALPHA
MILLER CLERK BETA
SCOTT ANALYST ALPHA
SMITH CLERK ALPHA ------|
SMITH CLERK BETA ------| SMITH работает по двум
TURNER SALESMAN ALPHA проектам
WARD SALESMAN ALPHA
.
- 31 -
я2Независимость данных ORACLE предохраняет ваши внесения в
я2программы и данные и уменьшает расходы на их поддержание
Мы только что увидели второй пример как можно легко изме-
нить структуру базы данных ORACLE без изменений ваших программ,
хранимых запросов и так далее. Это возможно, поскольку SQL явля-
ется непроцедурным зыком и ORACLE позволяет иметь разные виды
одних и тех же данных. Поскольку SQL является непроцедурным язы-
ком, ваши программы не проходят по структуре базы данных. Так
что при изменении структуры данных вам не нужно модифицировать
свои программы. Вместо этого все что вам нужно сделать - это
создать виды, которые скрывают изменения от ваших старых прог-
рамм, так что они могут работать без модификации и в то же самое
время обеспечивая преимущества расширенной структуры базы данных
для новых прикладных систем.
.
- 32-1 -
я2Разделение данных и защита
ORACLE допускает многочисленных пользователей для использо-
вания одной базы данных. Это позволяет вам разделить свои данные
с другими пользователями по вашему желанию. Но несмотря на явное
задание вами доступа других пользователей к данным, ORACLE авто-
матически сохранит ваши права на них.
В данном разделе вы узнаете как использовать SQL для:
* ГАРАНТИИ привелегий в ваших таблицах и видах для других
пользователей
* ОТМЕНЯТЬ привелегии, которые вы гарантировали другим
пользователям
Вы являетесь я2владельцем я0любой созданной вами таблицы. Толь-
ко вы, владелец, можете использовать таблицу несмотря на то, что
вы разрешили доступ к вашей таблице другим пользователям.
Команда GRANT состоит из трех базовых клауз:
GRANT - привелегия
ON - таблица или вид
TO - пользователь или группа пользователей
Предположим, что вы являетесь создателем таблицы ЕМР и хо-
тите дать пользователю по имени ADAMS право на запросы в нее. Вы
должны задать:
я2Команда GRANT
GRANT SELECT
ON EMP
TO ADAMS;
Grant succeeded. --- гарантии присвоены
.
- 32-2 -
Вы можете гарантировать любую комбинацию привелегий функци-
онирования в ваших таблицах или видах включая:
ПРИВЕЛЕГИИ ДЛЯ ТАБЛИЦ ПРИВЕЛЕГИИ ДЛЯ ВИДОВ
--------------------- --------------------
SELECT SELECT ----- ВЫБРАТЬ
INSERT INSERT ----- ВВЕСТИ
UPDATE UPDATE ----- ОБНОВИТЬ
DELETE DELETE ----- УДАЛИТЬ
ALTER ----- ИЗМЕНИТЬ
INDEX ----- ИНДЕКС
CLUSTER ----- КЛАСТЕР
В предыдущем примере мы гарантировали пользователю ADAMS
привелегию на выборку из всей таблицы ЕМР. Но что будет если вы
пожелаете обеспечивать всем пользователям доступ к этой таблице,
за исключением колонок SAL и COMM? Путем гарантирования привеле-
гий к видам, а не таблицам, вы можете ограничить доступ к задан-
ным строкам и колонкам таблицы. Сперва давайте определим вид
ЕМР, который не содержит колонки SAL и COMM.
я2Создать вид для защиты данных
CREATE VIEW EMPS
AS SELECT EMPNO,ENAME,JOB,HIREDATE,DEPTNO
FROM EMP;
Теперь вы можете гарантировать привелегии на доступ к виду
EMPS для всех при помощи ключевого слова SQL PUBLIC.
GRANT SELECT
ON EMPS
TO PUBLIC; ---- ключевое слово PUBLIC
.
- 33 -
SQL позволит вам создавать виды, возвращающие разные ре-
зультаты разным пользователям.
Давайте создадим вид таблицы ЕМР, возвращающий данные толь-
ко о тех пользователях, номера отдела которых совпадают с номе-
ром отдела персоны, использующей данный вид.
CREATE VIEW MYEMPS
AS SELECT *
FROM EMP
WHERE DEPTNO IN
(SELECT DEPTNO
FROM EMP
WHERE ENAME = USER); ---- ключевое слово USER
Ключевое слово USER (WHERE ENAME = USER), возвращает имя
текущего вошедшего в систему пользователя. Так что если это
JONES, работающий в отделе 10, то он имеет доступ к виду MYEMPS,
где перечислены работники отдела 10. Есди это BLAKE - отдел 30 -
то он увидит только работников отдела 30. Другими словами вид
становится чувствительным к тем, кто его использует.
Теперь мы гарантируем и доступ и привелегии обновления к
виду MYEMPS для всех трех менеджеров отделов для разрешения им
читать и/или изменять любые данные о работниках только их отделов.
я2Чувствительность к содержанию - защита через виды
GRANT SELECT,UPDATE
ON MYEMPS
TO JONES,BLAKE,CLARK;
Таким образом, если работник переведен в другой отдел (то
есть значение в поле DEPTNO изменилось), новый менеджер автома-
тчески получает доступ к сведениям по окладу данного работника,
а старый менеджер автоматически теряет к нему доступ. Сочетание
возможностей SQL VIEW и GRANT, а также ключевого слова USER, дает
ORACLE я2защиту, чувствительную к содержимому.
я2Команда REVOKE
После представления привелегии вы можете всегда ее отменить
при помощи команды REVOKE.
Давайте отменим привелегии ADAMS на введение значений в
таблицу DEPT.
REVOKE INSERT
ON DEPT
FROM ADAMS;
Revoke succeeded. ---- отмена свершилась
|