Введение в язык SQL

Исполняющая система использует это значение для динамического построения условия поиска 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

Подзапросы могут тоже быть составными, как и основные запросы - они могут содержать условия поиска и запросы JOIN. Например, можно использовать их в командах INSERT, UPDATE и DELETE (рассматриваемых в следующих секциях) наряду с командами SELECT.

Контрольные вопросы

В чем заключается удобство использования подзапросов в SQL-клаузе SELECT?

Каков порядок выполнения подзапросов и каким образом его можно изменить?

Составьте SQL-предписание для уничтожения строк с информацией о работниках, имеющих оклад не выше 25% от среднего. Каковы `подводные камни', могущие возникнуть при выполнении этой операции?

1.12 Модификация хранимых данных

Как видно из вышеизложенного, одна команда SELECT позволяет выбрать набор строк из одной или нескольких таблиц. SQL позволяет добавлять или модифицировать строки достаточно просто при помощи следующих трех команд:

команда UPDATE изменяет значения, хранимые в полях

команда INSERT добавляет строки в таблицу

команда DELETE удаляет строки из таблицы

1.12.1 Команда UPDATE

Используя имеющийся пример базы данных, для повышения окладов всех клерков на 100 долларов потребуется обновить или изменить значения некоторых данных (поля SAL) в некоторых строках (тех, в которых значение для JOB = CLERK) в таблице EMP. Команда UPDATE состоит из клаузы UPDATE вместе с клаузой SET и опционной (необязательной) клаузой WHERE:

UPDATE EMP

SET SAL = SAL + 100

WHERE JOB = 'CLERK';

Клауза UPDATE определяет таблицу, которую нужно модифицировать (EMP). Клауза SET задает поле, которое нужно модифицировать в некоторое другое значение (SAL=SAL+100). В клаузе WHERE задается условие выбора строк для обновления (условие JOB='CLERK'). Вывод на дисплей подтверждает, что обновление действительно имело место:

SELECT ENAME,JOB,SAL

FROM EMP

WHERE JOB ='CLERK';

ENAME	JOB	SAL
SMITH	CLERK	900.00
ADAMS	CLERK	1,200.00
JAMES	CLERK	1,050.00
MILLER	CLERK	1,400.00

1.12.2 Команда INSERT

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

Предположим, что в ранее описанном примере базы данных имеется третья таблицу по имени PROMOTION. Эта таблица содержит многие колонки такие же как в EMP - ENAME, JOB, SAL, СОММ. Задача введения в эту таблицу данных для всех продавцов, имеющих комиссионные больше 25% от оклада путем копирования информации из таблицы ЕМР решается с помощью следующей SQL-инструкции:

INSERT INTO PROMOTION (ENAME,JOB,SAL,COMM)

SELECT ENAME,JOB,SAL,COMM --- подзапрос

FROM EMP

WHERE COMM > 0.25 * SAL;

2 records created. --- возможное сообщение системы

Команда INSERT в данном примере использует подзапрос вместо списка значений данных. Исполняющая система вводит в таблицу PROMOTION строки и колонки, выбранные из ЕМР в подзапросе.

1.12.3 Команда DELETE

Поскольку в отделе 40 нет работников, удалим его из таблицы DEPT при помощи команды DELETE:

DELETE FROM DEPT

WHERE DEPTNO = 40;

Используем запрос SELECT * , чтобы убедиться, что удаление реально имело место:

SELECT *

FROM DEPT;

DEPTNO	DNAME	LOC
10	ACCOUNTIG	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO

Клауза DELETE FROM определяет таблицу, из которой необходимо удалить строку или набор строк (в данном случае таблицу DEPT). Клауза WHERE должна иметь опции (клауза DELETE FROM без нее будет означать, что пользователь желает удалить все строки из таблицы); она задает условия, при соблюдении которых нужно удалять строки. В этом примере использовалась клауза WHERE, чтобы сообщить исполняющей системе следующее `УДАЛИТЬ строки таблицы DEPT, у которых значение в колонке DEPTNO равно 40'.

Клаузы WHERE во всех командах демонстрируют, как SQL использует одинаковый синтаксис для обработки данных и для запросов. В командах SELECT, UPDATE, INSERT, DELETE функционирование клаузы WHERE идентично: она всегда опционна и используется для задания условий, при которых нужно уничтожать, модифицировать или обновлять данные. Долее будет ясно, как SQL использует одинаковый синтаксис запросов для определения данных.

Контрольные вопросы

Каков формат SQL-предписаний UPDATE, INSERT и DELETE?

Каким образом команда INSERT используется для переноса (по заданному условию) данных из одной таблицы в другую?

1.13 Динамические изменения структуры базы данных

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

ALTER TABLE ADD - добавления новой колонки в существующую таблицу

ALTER TABLE MODIFY - изменение размера существующей колонки

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

CREATE TABLE DEPT	(PROGNO	NUMBER (3) NOT NULL,
	PNAME	CHAR (5),
	BUDGET	NUMBER(7,2));

Далее используем команду INSERT для помещения некоторых данных в таблицу 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	BUDJET
101	ALPHA	96,000.00
102	BETA	82,000.00
103	GAMMA	15,000.00

И в третьих, добавим колонку номеров проектов в таблицу ЕМР, так что две таблицы разделят общую колонку. Используем команду ALTER TABLE для добавления новой колонки в существующую таблицу :

ALTER TABLE ЕМР ADD (PROJNO NUMBER(3));

В данной команде названа таблица, которую нужно изменить (в данном случае ЕМР), описана функция добавления (ADD), колонка, которую нужно добавить в нее (PROJNO), новый тип данных колонки и максимальная длина новой колонки - NUMBER(3).

Просмотрим таблицу ЕМР и заметим, что добавлено в каждую строку новое поле PROJNO с пустым начальным значением:

SELECT *

FROM ЕМР;

После введения новой колонки в таблицу ЕМР применим команду UPDATE для соотнесения работников с проектами. Соотнесем всех работников отдела 20 и всех продавцов проекту 101:

UPDATE ЕМР SET PROJNO = 101

WHERE DEPTNO = 20

OR JOB = 'SALESMAN';

Выведем на экран полученную таблицу ЕМР для уверенности во введенных изменениях:

SELECT *

FROM ЕМР;

Теперь поместим всех, кто не отнесен ни к какому проекту (то есть WHERE PROJNO IS NULL), в проект 102:

UPDATE EMP SET PROJNO = 102

WHERE PROJNO IS NULL;

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

SELECT *

FROM EMP;

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

Присоединение таблицы EMP к таблице PROJ:

SELECT ENAME,JOB,DEPTNO,PNAME

FROM EMP,PROJ

WHERE EMP.PROJNO = PROJ.PROJNO;

ENAME	JOB	DEPTNO	PNAME
SMITH	CLERK	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	CLERK	20	ALPHA
FORD	ANALYST	20	ALPHA
BLAKE	MANAGER	20	BETA
CLARK	MANAGER	10	BETA
KING	PRESIDENT	10	BETA
JAMES	CLERK	30	BETA
MILLER	CLERK	10	BETA

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

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

Команда ALTER TABLE

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

Например, что было введено значение 15000 для бюджета в проекте 103. Предположим, что произошла ошибка и правильное значение в действительности должно быть равно 105000. Необходимо обновить строку проект 103 с правильным значением бюджета:

UPDATE PROJ

SET BUDGET = 105000

WHERE PROJNO = 103;

ERROR: для колонки задано значение больше допустимого

Получено сообщение об ошибке, поскольку сумма нового бюджета, которую попытались ввести, была слишком велика, чтобы поместиться в колонку BUDGET, определенную в команде CREATE TABLE как NUMBER(7,2). Это определение значит, что допускаются числа не больше чем 99,999.99 (7 цифр, из которых две справа от десятичной точки). Проблему можно решить, используя команду ALTER TABLE для увеличения колонки:

ALTER TABLE PROJ

MODIFY BUDGET NUMBER(8,2);

Также как в команде ALTER TABLE ADD, указывается таблица PROG, которую надо изменить. В данном примере необходимо модифицировать определение колонки BUDGET (определить ее размер как NUMBER(8,2) вместо текущей.

Теперь можно попытаться обновить таблицу снова:

UPDATE PROJ

SET BUDGET = 105000

WHERE PROJNO = 103;

Используем запрос SELECT для уверенности, что обновление действительно имело место:

SELECT *

FROM PROJ;

Контрольные вопросы

В каких случаях необходимо динамически изменять структуру таблицы?

Каков формат SQL-предписаний, осуществляющих динамическое изменение структуры данных?

Каким образом пользователь может отслеживать возможные ошибки, возникающие вследствие непродуманного изменения структуры таблиц?

Насколько, по Вашему мнению, динамическое изменение структуры таблиц с помощью SQL эффективнее традиционного подхода (создание новой таблицы c заданной структурой копирования в нее содержимого имеющейся таблицы добавление необходимых данных уничтожение `старой' таблицы)?

1.14Альтернативные виды данных

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

Также любое изменение данных в таблицах сразу же вызывает соответственное изменение связанного с данными таблицами вида; образно говоря, изменения в 'окружающем пространстве' (таблицах) тут же проявляются в 'окне' (виде).

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

Обычно изменения в виде сразу же сказывается на содержании породивших его таблиц (в этом случае говорят, что `вид обновляем'), однако существуют случаи, когда вид существует `только для чтения'. Вопрос определения критериев `обновляемости' видов до сих пор дискуссируется в теории баз данных (некоторые практические примеры можно найти в [1]).

Виды служат трем основным целям

упрощают доступ к данным

обеспечивают независимость данных

обеспечивают защиту данных

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

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

CREATE VIEW EMPN0 AS

SELECT EMPNO,ENAME,JOB

FROM ЕМР

WHERE DEPTNO = 10;

Поскольку результат запроса - таблица, то запрос используется для определения виртуальной таблицы или вида. В команде CREATE VIEW называется вид, а затем описывается в форме оператора запроса SQL, что вид должен содержать. С некоторыми ограничениями можно запрашивать и модифицировать виды так же, как если бы они были реальными таблицами.

Просмотрим созданный вид:

SELECT *

FROM EMPNO;

EMPNO	ENAME	JOB
7782	CLARK	MANAGER
7839	KING	PRESIDENT
7934	MILLER	CLERK

Для некоторых пользователей проще работать с одной таблицей, чем с несколькими. Поддерживающие SQL системы позволяют создавать единственный вид из двух или более таблиц. Таким образом, возможно определить вид как выборку из нескольких таблиц, вместо которых можно запрашивать индивидуально сделанное соединение (это и есть вид).

Несложно создать единственный вид таблицы по имени PERSONNEL с колонками ENAME, JOB, PNAME из таблиц ЕМР и PROJ и затем проанализировать его содержимое.

Создание соединенного вида:

CREATE VIEW PERSONNEL AS

SELECT ENAME,JOB,PNAME

FROM EMP,PROJ

WHERE EMP.PROJNO = PROJ.PROJNO;

Просмотр созданного вида:

SELECT *

FROM PERSONNEL;

PROJNO	JOB	PNAME
SMITH	CLERK	ALPHA
ALLEN	SALESMAN	ALPHA
WARD	SALESMAN	ALPHA
JONES	MANAGER	ALPHA
MARTIN	SALESMAN	ALPHA
SCOTT	ANALYST	ALPHA
TURNER	SALESMAN	ALPHA
ADAMS	CLERK	ALPHA
FORD	ANALYST	ALPHA
BLAKE	MANAGER	BETA
CLARK	MANAGER	BETA
KING	PRESIDENT	BETA
JAMES	CLERK	BETA
MILLER	CLERC	BETA

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

Таким образом можно использовать виды для упрощения доступа к данным, уменьшения длины строк и сокращения количества ошибок.

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

SELECT ENAME,PNAME

FROM PERSONNEL

WHERE JOB = 'MANAGER';

ENAME	PNAME
JONES	ALPHA
BLAKE	BETA
CLARK	BETA

Отметим, насколько прост этот запрос вида PERSONNEL по сравнению с запросом типа JOIN, требуемым для выполнения аналогичных действий - генерации этого списка из двух таблиц ЕМР и PROJ.

Контрольные вопросы

Что такое представления (виды) и чем они функционально отличаются от таблиц?

Каким целям служат представления?

Каким образом достигается актуальность содержимого представления?

Должно ли представление иметь одинаковое имя с таблицей, от которой порождено?

Возможно ли создание представления, включающего информацию из нескольких таблиц одновременно?

1.15 Независимость данных

Виды освобождают пользователя от необходимости знать, в каких таблицах находятся данные, c которыми он работает. Это разница в том, какие данные пользователь видит и как они хранятся, носит название независимость данных; эта разница упрощает доступ к данным в БД. Но можно использовать виды и для хранения пользовательских запросов, программ и так далее, предохраняя их от устаревания при изменениях базы данных.

Текущая структура БД позволяет связать несколько работников с проектом, однако каждый должен работать только по одному проекту - это известное соотношение 'многие к одному'. Предположим, что процедуры по персоналу изменились и сейчас есть необходимость связать работников с несколькими проектам. Чтобы это выполнить, необходимо пересмотреть структуру базы данных, преследуя две цели.

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

Сначала создадим новую таблицу, соотносящую работников с проектами и содержащую информацию об участии конкретных работников в конкретном проекте (например, введем новый столбец - часы работы WORKHPS. Новая структура базы данных будет поддерживать соотношения типа 'многие-к-многим'):

Далее присоединим работников к проектам путем введения строк в таблицу РЕ. Отметим, что работник под номером 7369 имеет две строки в этой таблице: одна для проекта 101 и другая - для 102. Присоединение работников к проектам выполняется многократным использованием INSERT:

INSERT INTO PE VALUES (7369, 101, 0);

INSERT INTO PE VALUES (7369, 102, 0);

INSERT INTO PE VALUES (7399, 101, 0);

INSERT INTO PE VALUES (7521, 101, 0);

INSERT INTO PE VALUES (7366, 101, 0);

INSERT INTO PE VALUES (7554, 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);

В будущем, если работник будет присоединен ко второму или третьему проекту, достаточно ввести дополнительную строку для этого работника в таблицу РЕ.

Поскольку теперь используется таблица РЕ для соотношения работников с проектами, больше не нужна информация, хранимая в колонке PROJNO таблицы ЕМР. Можно удалить эти данные путем установки значений этой колонки в пустые:

UPDATE EMP

SET PROJNO = NULL;

Вспомним, что отсутствие клаузы WHERE приказывает обновить все строки в таблице.

Поскольку все PROJNO установлены в NULL, больше невозможно использовать вид PERSONNEL, который использует колонку PROJNO для связи таблиц ЕМР и PROJ. Для генерации этой информации снова нужно соединить таблицу ЕМР с новой таблицей РЕ и соединить таблицу РЕ с нашей таблицей PROJ. Но возможно спрятать это изменение от пользователей, использующих вид PERSONNEL, просто изменив определение вида. Первый шаг в изменении вида PERSONNEL - уничтожить старый вид:

DROP VIEW PERSONNEL;

Теперь создадим новый вид PERSONNEL, объединяющий таблицу ЕМР и таблицу PROJ через таблицу РЕ :

CREATE VIEW PERSONNEL AS

SELECT ENAME,JOB,PNAME

FROM EMP,PROJ,PE

WHERE EMP.EMPNO = PE.EMPNO

AND PE.PROJNO = PROJ.PROJNO;

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

Запрашивая новый вид PERSONNEL, увидим такую же информацию, как для старого вида PERSONNEL плюс второй проект для работника SMITH:

SELECT *

FROM PERSONNEL

ORDER BY ENAME;

Только что был продемонстрирован пример, как можно легко изменить структуру базы данных без изменений пользовательских программ, хранимых запросов и др. Это возможно именно потому, что SQL является непроцедурным языком и позволяет иметь разные виды (представления) одних и тех же данных.

Контрольные вопросы

В чем заключается независимость данных?

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

Каким образом с помощью представлений можно обеспечить функционирование старых программ без их модификации?

1.16 Разделение данных и защита

Поддерживающие язык SQL системы дают возможность многочисленным пользователям иметь доступ к одной базе данных. Это позволяет разделять (по желанию) свои данные с другими пользователями.

В данном разделе обсуждается, как использовать SQL для:

ГАРАНТИИ привилегий в таблицах и видах для других пользователей

ОТМЕНЯТЬ привилегии, которые ранее были гарантированы другим пользователям

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

Команда GRANT состоит из трех базовых клауз:

GRANT привилегия

ON таблица или вид

ТО пользователь или группа пользователей;

Предположим, что текущий пользователь является создателем таблицы ЕМР и желает дать пользователю по имени ADAMS право на запросы к ней. Для этого следует задать команду:

GRANT SELECT

ON EMP

TO ADAMS;

Возможно гарантировать любую комбинацию привилегий функционирования в таблицах или видах, включая:

Привилегии для таблиц	Привилегии для видов
SELECT	выбрать	SELECT	выбрать
INSERT	ввести	INSERT	ввести
UPDATE	обновить	UPDATE	обновить
DELETE	удалить	DELETE	удалить
ALTER	изменить
INDEX	индексировать
CLUSTER	кластер

В предыдущем примере пользователю ADAMS гарантированы привилегии на выборку из всей таблицы EMP. Но что будет, если необходимо обеспечить всем пользователям доступ ко всей таблице, за исключением колонок SAL и СОММ?

Вопрос может быть решен путем гарантирования привилегий к видам, а не таблицам. Можно ограничить доступ к заданным строкам и колонкам таблицы. Сначала определим вид EMP, который не содержит колонки SAL и СОММ:

CREATE VIEW EMPS AS

SELECT EMPNO,ENAME,JOB,HIREDATE,DEPTNO

FROM EMP;

Теперь легко гарантировать привилегии на доступ к виду EMPS для всех пользователей при помощи ключевого слова PUBLIC:

GRANT SELECT

ON EMPS

TO PUBLIC; --- PUBLIC значит `ДЛЯ ВСЕХ'

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

CREATE VIEW MYEMPS AS

SELECT *

FROM EMP

WHERE DEPTNO IN

(SELECT DEPTNO

FROM EMP

WHERE ENAME = USER);

Ключевое слово USER (используемое в конструкции WHERE ENAME=USER) возвращает имя текущего вошедшего в систему пользователя. Если это JONES, работающий в отделе 10, то он имеет доступ к виду MYEMPS, где перечислены только работники отдела 10. Если это BLAKE (из отдела 30), то он увидит только работников отдела 30. Другими словами, вид становится чувствительным к тем, кто его использует.

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

GRANT SELECT,UPDATE

ON MYEMPS

ТО JONES, BLAKE, CLARK;

Таким образом, если работник переведен в другой отдел (то есть значение в поле DEPTNO изменилось), новый менеджер автоматически получает доступ к сведениям по окладу данного работника, а старый менеджер отдела автоматически теряет к нему доступ. Сочетание возможностей VIEW и GRANT, а также ключевого слова USER, дает поддерживающим SQL системам способность осуществлять защиту, чувствительную к содержимому БД.

Команда REVOKE

После предоставления привилегии всегда можно ее отменить при помощи команды REVOKE.

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

REVOKE INSERT

ON DEPT

FROM ADAMS;

Контрольные вопросы

Каким образом подтверждаются и отменяются привилегии пользователей на доступ к конкретной таблице БД?

Какими привилегиями обладает пользователь по отношению к созданной по его требованию таблице?

Каким образом осуществляется разделение привилегий на доступ к отдельным столбцам таблицы?

В чем заключается принцип защиты информации, чувствительный к содержимому БД?

1.17 Понятие транзакции

Транзакция - логическая единица работы, определяемая пользователем. Важнейшим свойством транзакции является ее атомарность - транзакция должна или завершиться полностью и успешно, или не завершиться совсем. В качестве логической единицы может выступать любое количество операторов SQL (однако целесообразно включать в единичную транзакцию логически связанные запросы). Смысл реализации транзакций - повышение уровня целостности информации в БД в критических ситуациях (например, в случае потери связи с удаленной БД, при сбое питания и др.).

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

Транзакция начинается выдачей команды BEGIN TRANSACTION (т.е. начать вести журнал транзакции, в который записываются все необходимые изменения в БД). Далее следуют команды изменения БД (`тело' транзакции). Транзакция может завершиться успешно (для чего выдается команда COMMIT) или может быть проведен 'откат' данной транзакции (по команде ROLLBACK) к исходному (до момента начала транзакции) состоянию БД. Команды COMMIT или ROLLBACK могут являться условными; по умолчанию COMMIT выполняется в случае успешного выполнения тела транзакции (что определяется согласно записям в журнале транзакций), в противном случае выполняется ROLLBACK. Естественно, пользователь (обычно привилегированный) имеет возможность `откатить' все зарегистрированные в журнале транзакции.

Контрольные вопросы

Дать определение транзакции. Для применяются транзакции?

В чем заключается основное свойство транзакции?

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

1.18 Понятие триггера

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

Различают триггеры уровня блоков (активируются при выполнении запросов) и триггеры уровня записей (активируются в моменты изменений в полях БД). Триггеры также разделяются на пред-триггеры (активируются перед выполнением некоего действия) и пост-триггеры (активируются после выполнения действия)

Тело триггера может содержать любое число SQL-операторов, возвращающих значение ИСТИНА при выполнении заложенного в теле триггера условия или ЛОЖЬ в противоположном случае.

Простейший случай использования триггера - обеспечение ссылочной целостности БД. Например, в случае попытки удаления записи в MASTER-таблице триггер должен проверить наличие соответствующих ссылок в DETAIL-таблице (вернув значение ИСТИНА при их наличии и ЛОЖЬ в случае отсутствия); программа пользователя при получении значении ИСТИНА от данного триггера обязана отменить удаление записи.

Триггеры являются интуитивно понятным и мощнейшим средством обеспечения корректности функционирования СУРБД.

Контрольные вопросы

Что такое триггер (в понятиях SQL)?

Триггеры каких уровней и типов существуют?

Привести пример использования триггера при работе с БД.

2. ПРАКТИЧЕСКОЕ ИСПОЛЬЗОВАНИЕ SQL НА ПЭВМ

2.1 Создание приложений класса баз данных в Delphi / С++Builder

Создание приложений класса баз данных в Delphi/С++Builder производится быстро и легко, в то же время достигается большая гибкость в применении. Освоение фирмой Borland операционной системы Linux (RAD-система Kylix, см. http://www.borland.ru/kylix/index.html) еще более повышает ценность накопленных при работе с Delphi / С++Builder навыков программирования.

В основном применяются следующие компоненты Delphi / С++Builder (находятся на страницах DataAceess и DataControls стандартной линейки компонентов):

компонент ТТаblе для доступа к БД без использования SQL или ориентированный на SQL компонент TQuery доступа к БД; эти компоненты осуществляют физический доступ к БД на диске;

компонент TDataSource для связи ТTаblе/TQuery с (видимыми) компонентами представления информации БД;

компонент TDBGrid для визуального представления информации в виде стандартной таблицы (строки коей соответствуют записям, а столбцы - полям БД).

Часто применяют компонент TFieldList, обеспечивающий задание и визуализацию пользовательских заголовков полей в таблицах, компоненты TDBEdit, TDBImage для доступа/редактирования полей таблиц, компонент TDBNavigator для удобного перемещения по записям БД и др.

Для реализации систем класса 'клиент/сервер' обычно дополнительно применяются компоненты TStoredProc и TBatchMove

Таким образом, схема простейшей машины БД в Delphi / С++Builder может быть представлена на рис.2.1.

Для создания простейшей системы управления БД разработчик должен поместить на форму компоненты TTable/TQuery, TDataSource, TDBNavigator / TDBGrid / TDBEdit / TDBImage и настроить их соответствующим образом с помощью Object Inspector'a.

Экран приложения на Delphi / C++Builder

TDBGrid

TTable или TQuery

TFieldList

IndexNames

TDataSource

IndexFieldNames

Оперативная память

Дисковая память

Таблица индексов

Физическая таблица на диске (файл)

Рис.2.1.Простейшая машина баз данных (схема)

Настройка компонент TDBNavigator/TDBGrid и TDBEdit / TDBMemo / TDBImage заключается в установке свойства DataSource на имя реального компонента TDataSource (а для компонентов TDBEdit / TDBMemo / TDBImage - свойства DataFleld на имя отображаемого поля).

Настройка компонента TDataSource заключается в установке свойства DataSet на имя реального компонента ТТаblе или TQuery

Настройка компонента ТТаblе заключается в установке свойства DatabaseName на имя алиаса нужной таблицы, свойств IndexFieldName или IndexName - на имя ключа, свойства TableName - на имя содержащего таблицу файла, свойства ТаblеТуре - на тип файла БД (dBase, Paradox etc).

Настройка компонента TQuery заключается в установке свойства DatabaseName как указано выше, (необязательных) установках свойств SQL (задать строку SQL-запроса) и Params (задать фактические значения используемых в SQL-запросе параметров). Часто свойства SQL и Params не устанавливаются в Object Inspector'e, a прямо присваиваются в RunTime (как показано ниже).

Установка свойства Active в TRUE (даже в DesignTime) компонентов ТТаblе и TQuery инициирует немедленное открытие нужной таблицы (весьма рекомендуется при отладке). Практические советы по настройке компонентов можно найти в [6,7].

Существенно, что о каждой (физической) таблицей могут быть связаны несколько компонентов TTable/TQuery и др., что бывает необходимо для открытия таблицы в разных режимах.

Сложности может вызвать настройка алиасов (псевдонимов) полных путей к файлам БД в компонентах ТTаblе/TQuery. Пользователь должен задать имя алиаса для каждой физической таблицы или для всех используемых в данном приложении таблиц (или разбить файлы на группы по месту их расположения на диске).

Компонент TQuery использует SQL-выражения для доступа к БД, причем возможны три пути определения SQL-предложения:

статическое SQL-выражение (задается во время DesignTime в свойстве SQL и в простейшем случае не изменяется во время выполнения скомпилированного приложения);

SQL-выражение с параметрами (текст SQL-запроса может содержать начинающиеся с двоеточия формальные параметры, изменяемые во время выполнения скомпилированного приложения);

динамическое задание SQL-запроса; в этом случае текст (строка) SQL-запроса генерируется в RunTime и передается компоненту TQuery для исполнения.

Простейшая схема использования возможностей компонента TQuery, обеспечивающая функционирование SQL-запросов к БД приведен ниже (C-текст дан применительно к системе C++Builder, предполагается, что текст SQL-запроса вводится пользователем в компонент Edit1):

void __fastcall TForm1::RunSQL(TObject *Sender)

{ // функция вызывается при нажатии

// на кнопку `RunSQL' (`Выполнить SQL')

Query1->Close(); // закрыть БД

Query1->SQL->Clear(); // очистить буфер SQL-предписаний

Query1->SQL->Add(Edit1->Text); // копировать в буфер из Edit1

Query1->Open(); // заново открыть БД (c выполнением SQL)

} // конец функции RunSQL

Приближенный к практике Раsса1-текст (для Delphi) в этом случае будет следующим (полагаем, что имя TQuery-компонента суть Query_l, a строки SQL_string_l, SQL_string__2 и др. содержат ранее сгенерированные тексты частей SQL-запроса - например, отдельно SELECT-часть, WHERE-часть и др.):

try { включить режим отслеживание исключительных ситуаций }

Screen.Cursor:=crSQLWait;{ форма курсора - песочные часы }

Query_1.Close;

Query_1.SQL.Clear; { очистить текст SQL-запроса }

Qnery_1.SQL.Add(SQL_string_1); { 1-я часть SQL-запроса }

Query_1.SQL.Add(SQL_string_2); { 2-я часть SQL-запроса }

{ и так далее - текст запроса может быть достаточно длинным... }

Query_1.Prepare; { ...часто повышает эффективность }

Query_1.Open; { выполнение SELECT - запроса}

Screen.Cursor:=crDefault; { форма курсора - по умолчанию }

except { ...какая-то ошибка при выполнении OPEN }

on E:EDatabaseError do

begin

if E.Message=LoadStr(SHandleError) then { ...это не SELECT !}

begin

Query1.ExecSQL; { попытка выполнения не SELECT - запроса }

Screen.Cursor:=crDefault; { форма курсора - по умолчанию }

end

else

begin { ошибка выполнение не SELECT - запроса }

Sсreen.Cursor:=crDefault; { форма курсора - по умолчанию }

{ raise; возбудить исключительное состояние }

MessageDlg('Ошибка выполнения UPDATE,DELETE или ' +

`INSERT', mtError, [mbOk], 0);

end;

end

else

begin { непонятная ошибка... }

Screen.Cursor:=crDefault; { форма курсора - по умолчанию }

{ raise; возбудить исключительное состояние }

MessageDlg('Нераспознанная ошибка SQL-запроса к БД',

mtError,[mbOk],0);

end;

end; { конец блока TRY }

Именно при выполнении метода Open происходит (физическое) соединение с БД, выполнение SQL-запроса и дальнейшая визуализация полученных данных. При наличии в SQL-предложении клауз INSERT, UPDATE, DELETE следует вместо метода Open использовать метод ExecSQL (метод Open используется только тогда, когда запрос предполагает возвращение результата - т.е. используется клауза SELECT).

Для успешной работы следует корректно настроить соответствие алиаса физическому пути к файлу БД и параметры соединения с помощью BDE (Borland Database Engine), в основе которого лежит технология IDAPI (Integrated Database API), см. [6,7] и др.

Контрольные вопросы

Какие компоненты интегрированных сред Delphi / C++Builder применяются при создании приложений класса БД и какие функции они выполняют?

В чем разница использования методов Open и ExecSQL и в каких случаях они применяются?

С помощью каких программных средств устанавливается соответствие алиаса и пути к файлам БД и параметры соединения?

2.2 Использование тренажера локального SQL

Конструирование корректных SQL-предложений на начальном этапе работы с языком SQL может вызвать затруднения, причем отладка SQL-запросов совместно с разрабатываемым Windows-приложением неэффективна из-за больших затрат на компиляцию (интерпретацию) базового приложения.

В связи с огромными возможностями языка SQL конечный результат запроса может быть достигнут различными способами (например, с использование или без использования вложенных запросов); эффективность (время выполнения и требуемый объем оперативной памяти) SQL-запроса при этом может изменяться в значительной степени, поэтому рационально использовать специальные программы-тренажеры (имитаторы выполнения) языка SQL.

При таком подходе SQL-запросы сначала конструируются и оптимизируются пользователем с использованием тренажера и только потом переносятся в приложение. Особенно эффективен такой подход для создания сложных хранимых SQL-процедур.

2.2.1Тренажер SQL-запросов к одной таблице

Для освоения (локального) SQL разработан простой тренажер в применении SQL, исполняемый файл которого называется SQL_1. ЕХЕ.

Тренажер позволяет исполнять SQL-предложения при работе с локальной БД, название файла БД - ЕМР (по строению совпадает с приведенным выше), имя алиаса - ЕМР. Текст SQL-запроса вводится пользователем и исполняется, результат исполнения визуализируется тут же в таблице, ошибка индицируется. Само SQL-выражение запоминается в дисковом файле с расширением SQL_1.SQL и восстанавливается при перестартовке тренажера.

При работе с тренажером имеется возможность пользоваться системой контекстного HELP'a (используется файл LOCALSQL.HLP из поставки Delphi); для вызова системы помощи следует пользоваться клавишей F1 (или Ctrl+Fl) или щелкнуть правой кнопкой 'мыши'.

Для перемещения по записям БД служит компонент TDBNavigator, представленный в виде линейки с кнопками в левой нижней части окна; он же используется для удаления записей, их изменения/добавления (в случае, если в результате последнего выполненного SQL-предписания БД не была открыта в режиме ReadOnly).

При работе тренажера ведется и выдается на экран статистика выполнения SQL-предписаний, протоколирование в файл не предусмотрено.

Общий вид экрана при работе с тренажером SQL_1 приведен ниже на рис.2.2.



Рис.2.2.Копия экрана дисплея при функционировании модуля SQL_1

Работающие на тренажере могут убедиться в наличии в текущем каталоге тренажера временных (начинающихся символом `подчеркивание') файлов, являющимися результатом последнего выполненного SQL-запроса; при штатном окончании Windows-приложения эти файлы стираются.

Пользователь вводит текст SQL-директивы в соответствующем окне и нажимает кнопку ВЫПОЛНИТЬ, после чего в таблице индицируется результат SQL-запроса или выдается предупреждение о невозможности выполнения. Кнопка ПОКАЗАТЬ ВСЮ ТАБЛИЦУ служит для вывода всех записей таблицы ЕМР, при этом (неявно) выполняется SQL-предписание SELECT * FROM 'emp'. Заметим, что в данной реализации SQL имена таблиц следует заключать в кавычки, для наименований полей (вместе с конкретизирующим данное поле именем таблицы) кавычки не требуются.

Пользуясь линейкой управления, можно дополнять / изменять / уничтожать записи (если до этого таблица не была открыта в режиме ReadOnly). Пользуйтесь выдаваемыми системой всплывающими подсказками (инициируются при нахождении указателя 'мыши' над отдельными элементами экрана в течение нескольких секунд).

2.2.2 Тренажер SQL-запросов к двум таблицам

Исполняемый файл данного тренажера имеет название SQL_2.EXE, при работе используется ранее описанная таблица ЕМР и таблица DEPT (строение таблицы DEPT приведено выше), алиас DEPT.

Общий вид экрана при работе с тренажером SQL_2 приведен ниже на рис.2.3. Условно экран разбит на 2 области - в верхней вводятся SQL-директивы и просматривается результат их выполнения (в окне 'РЕЗУЛЬТИРУЮЩАЯ ТАБЛИЦА), в нижней показаны исходные таблицы DEPT и ЕМР (допускается их редактирование с помощью линеек управления).

Пользователь вводит текст SQL-директивы в соответствующем окне и нажимает кнопку ВЫПОЛНИТЬ, после чего в окне РЕЗУЛЬТИРУЮЩАЯ ТАБЛИЦА индицируется результат SQL-запроса или выдается предупреждение о невозможности выполнения. SQL-выражение запоминается в дисковом файле с расширением SQL_2.SQL и восстанавливается при перестартовке тренажера.

Примеры SQL-запросов к таблицам DEP и ЕМР приведен выше. Заметим, что при использовании поля HIREDATE следует применять встроенные функции DAY(), MONTH(), YEAR(); подробнее см. контекстный HELP. Точка с запятой в конце SQL-строки необязательна. Локальный SQL поддерживает псевдонимы внутри SQL-выражений (см. ниже задаваемые в клаузе FROM псевдонимы D и Е для таблиц DEPT и ЕМР соответственно).

SELECT D.deptno,D.dname,D.loc,E.ename,E.job,E.hiredate,E.sal,E.comm

FROM 'dept' D, 'emp' E

WHERE D.deptno = E.deptno

ORDER BY E.sal



Рис.2.3.Копия экрана дисплея при функционировании модуля SQL_2

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

2.2.3Утилита dbEXPLORER разработки АО ROSNO

В комплект программного обеспечения для исследования БД входит разработанная и бесплатно распространяемая АО ROSNO утилита dbEXPLORER ('исследователь' БД). Утилита позволяет подключаться к таблице (файлу) БД по известному алиасу, просматривать содержимое БД (и выполнять допустимые SQL-запросы), получать информацию о полях и их типах в выбранной БД.

Утилита dbEXPLORER по функциональности приближается к (штатной) утилите SQL Explorer (см. ниже) и приводится здесь в качестве иллюстрации возможностей создания системного ПО с помощью интегрированных сред Delphi / C++Builder. Принцип взаимодействия пользователя с dbEXPLORER является стандартным для Windows, ввод и выполнение SQL-запросов практически не отличается от вышеописанного.



Рис.2.3.Копия экрана дисплея при функционировании утилиты

dbEXPLORER (русификация FREEWARE-продукта АО ROSNO)

Автор данного пособия заочно благодарен разработчикам dbEXPLORER'a и обязан сообщить о внесенных им несущественных изменениях в исходные тексты dbEXPLORER'а (в основном оформительского характера), не затрагивающих сущности утилиты.

2.3Дополнительные инструментальные средства пакетов Delphi / C++Builder

Для операций с БД в составе систем Delphi / C++Builder штатно поставляется набор утилит, приведенных ниже:

Database Desktop - программа создания и редактирования таблиц, SQL-запросов и запросов типа QBE (Query By Example, запросов по образцу).

BorlandDatabase Engine(BDE) - набор динамических библиотек и драйверов, предназначенных для организации доступа к БД.

BDE Administrator - утилита конфигурации BDE, позволяющая настраивать различные параметры BDE.

SQLExplorer - утилита для просмотра и редактирования БД и словарей данных.

SQLMonitor - программа-монитор для отслеживания порядка выполнения SQL-запросов к удаленным БД.

SQLBuilder - программа визуального конструирования SQL-запросов.

Data Pump - программа для переноса данных между БД различного формата.

LocalInterBaseServer - локальная версия SQL-сервера Borland InterBase.

InterBaseServerforWindows - многопользовательская версия SQL-сервера Borland InterBase.

InterBaseServerManager - программа-администратор управления удаленным сервером.

SQLLinks - набор драйверов для обращения к удаленным промышленным СУБД (Oracle, Microsoft SQL Server). Для доступа к серверу InterBase достаточно возможностей вышеописанного BDE.

ЗАКЛЮЧЕНИЕ

Приведенное учебное пособие по применению языка запросов SQL позволяет обучаемому начать изучение системы и создавать не слишком сложные программы. Для углубления знаний и навыков следует практиковаться при работе с конкретной системой (например, C++Builder, Delphi, Oracle etc) и пользоваться дополнительной литературой.

СПИСОК ЛИТЕРАТУРЫ

1. Грабер M. SQL. -M.: Лори, 2001. -643 C.

2. Пейдж В. Использование ORACLE 8/8i (специальное издание). -М.: Диалектика, 1999. -464 C.

3. Мещеряков Е.В., Хомоненко А.Д. Публикация баз данных в Интернете. -CПб.: BHV-Петербург, 2001. -560 С.

4. Codd E.F. Normalized database structure: A brief tutorial. ACM SIGFIDET Workshop on datadescription, access and control. Nov. 1971.

5. Мaier D. The Theory of Relational Databases. ComputerScience Press, 1983.

6. Гофман В., Хомоненко А. Delphi 5. -CПб.: BHV-Петербург, 2000. -800 С.

7. Елманова Н.З.Borland С++Builder. -М.: Диалог-МИФИ, 1998. - 240 C.

8. TUXEDO System: разработка систем клиент-сервер. Ладыженский Г.М.. В сб. 'Системы управления базами данных', 1996. № 1,2.