Глушаков С.В., Ломотько Д.В. Базы данных

Подождите немного. Документ загружается.

180

Часть И. Структурированный язык запросов SQL

больше, чем 1, делает предикат среднего запроса верным. Это. в

свою очередь, делает EXISTS-предикат внешнего запроса верным

для текущей строки таблицы.

Таким образом, несмотря на кажущуюся простоту, EXISTS

может оказаться одним из самых трудных с точки зрения исполь-

зования операторов в SQL. Существуют еще три специальных

оператора, ориентируемых на подзапросы - это ANY, ALL и

SOME, напоминающие EXISTS, которые воспринимают подза-

просы, как аргументы. Однако эти операторы отличаются от

EXISTS тем, что используются совместно с реляционными опе-

раторами, аналогично IN в подзапросах

Операторы SOME и ANY достаточно часто взаимозаменяемы,

и в наших примерах будут работать одинаково. Различие в тер-

минологии состоит в том, чтобы позволить пользователям упот-

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

сказанное относительно ANY в равной степени относится и к

SOME.

Рассмотрим следующий способ нахождения студентов, кото-

рые получали оценки по разным учебным предметам:

SELECT *

FROM STUDENTS

WHERE SNUM = ANY

(SELECT SNUM

FROM USP);

Вывод этого запроса приведен ниже:

SNUM SEAM SEMA.

SOTCH

STIP

3412 Поляков

3413 Старова

3414 Гриценко

3416 Нагорный

Анатолий

Любовь

Владимир

Евгений

Алексеевич 25.50

Михайловна 17.00

Николаевич 0.00

Васильевич 25.50

Оператор ANY берет все значения, выведенные подзапросом,

т.е. в примере - все значения поля SNUM, и оценивает их как

верные, если любое из их равняется номеру студенческого билета

текущей строки внешнего запроса. Отсюда следует, что подза-

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

сравниваются в основном предикате. В этом его отличие от

EXISTS, который просто определяет, производит ли вывод под-

запрос или нет, фактически не используя эти результаты.

Глава 2.2. Выборка, или чтение данных

181

Вообще говоря, в ряде случаев допускается использование

операторов IN или EXISTS вместо оператора ANY. Например,

используя оператор IN, можно создать запрос, аналогичный

предыдущему:

SELECT *

FROM STUDENTS

WHERE SNUM IN

(SELECT SNUM

FROM USP);

Вывод этого запроса такой, как в предыдущем примере, по

этому приводить его не будем.

Интересен тот факт, что оператор ANY может использовать

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

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

IN. Например, можно выполнить следующий запрос:

SELECT PNAME

FROM PREDMET

WHERE HOURS > ANY

(SELECT HOURS

FROM PREDMET);

Результат этого запроса следующий:

PNAME

Физика

Химия

Математика

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

существует хотя бы одна учебная дисциплина с количеством ча-

сов, меньшим, чем у текущей.

В принципе, этот же запрос может быть реализован с исполь-

зованием EXISTS, следующим образом:

SELECT PNAME

FROM PREDMET FIRST

WHERE EXISTS

(SELECT *

FROM PREDMET SECOND

WHERE FIRST.HOURS > SECOND.HOURS);

182

Часть И. Структурированный язык запросов SQL

В любом случае запрос, который может быть реализован с

ANY и ALL, может быть также сформулирован с EXISTS, но не

наоборот. Правда в ряде случаев, из-за различий в обработке

NULL значений, вариант с EXISTS не абсолютно идентичен, чем

при использовании ANY, поэтому необходимо в таких случаях

применять команду IS NULL.

Основное преимущество в использовании ANY и ALL по срав-

нению с EXISTS заключается в том. что последний требует соотне-

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

подзапросы с ANY или ALL могут выполняться один раз и иметь

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

основного запроса, a EXISTS требует, чтобы весь подзапрос повтор-

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

Оператор ALL работает таким образом, что предикат является

верным, если каждое значение, выбранное подзапросом, удовле-

творяет условию в предикате внешнего запроса.

Например, если необходимо вывести только тех студентов,

чьи оценки выше или равны полученным 10/06/1999, то можно

воспользоваться следующим запросом:

SELECT *

FROM USP

WHERE OCENKA >= ALL

(SELECT OCENKA

FROM USP

WHERE UDATE = 10/06/1999);

Вывод этого запроса такой:

UNUM OCENKA UDATE SNUM PNUM

1001 5 10/06/1999 3412 2001

1005 5 12/06/1999 3416 2004

Запрос работает так: подзапрос проверяет значения оценок за

10/06/1999 для всех студентов. Затем он находит студентов с

оценкой большей или равной по сравнению с оценками за

10/06Л999. Поскольку самая высокая оценка в этот день - отлич-

но, то выбираются только записи с оценкой 5.

Как и в случае с ANY, допускается использовать EXISTS для

альтернативной формулировки такого же запроса:

Глава 22. Выборка, или чтение данных

183

SELECT *

FROM USP FIRST

WHERE EXISTS

(SELECT *

FROM USP SECOND .

WHERE FIRST.OCENKA >= SECOND,OCENKA

AND SECOND.UDATE = 10/06/1999);

Основное применение ALL находит со знаком неравенства,

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

равно для всех, т.е. все записи, фактически, идентичны. Напри-

мер, запрос

SELECT *

FROM USP

WHERE OCENKA = ALL

(SELECT OCENKA

FROM USP

WHERE UDATE = 10/06/1999);

является допустимым, но вывод будет только в случае, если

все оценки за 10/06/1999 окажутся идентичными. Вероятно, не

очень хорошо использовать запросы, которые работают только в

определенных ситуациях.

Однако ALL гораздо более эффективно используется со зна-

ком неравенства, т.е. с оператором < >. При этом обязательно

надо учитывать, что если подзапрос возвращает много различных

значений, то это означает неравенство любому результату. Иначе

говоря, предикат верен, если данное значение не найдено среди

результатов подзапроса. Рассмотрим такой запрос:

SELECT *

FROM USP

WHERE OCENKA < > ALL

(SELECT OCENKA

FROM USP

WHERE UDATE = 10/06/1999);

Вывод этого запроса таков:

UNUM OCENKA UDATE

SNUM PNUM

1003

11/06/1999 3414 2005

184

Часть II. Структурированный язык запросов SQL

• Рассматриваемый запрос сделает следующее: подзапрос вы-

берет все оценки за 10/06/1999 - это будут 5 и 4. После этого, ос-

новной запрос выведет все записи с оценкой, не совпадающей ни

с одной из них. Аналогичный запрос можно сформулировать с

использование оператора NOT IN:

SELECT *

FROM USP

WHERE OCENKA NOT IN

(SELECT OCENKA

FROM USP

WHERE UDATE = 10/06/1999);

или используя оператор ANY:

SELECT *

FROM USP

WHERE NOT OCENKA = ANY

(SELECT OCENKA

FROM USP

WHERE UDATE = 10/06/1999) ;

Вывод этих запросов такой же, как и первого.

Таким образом, в SQL дать запрос на сравнение с использова-

нием команды ANY для набора значений - то же самое, что про-

извести сравнение с любым отдельным значением из набора. Ес-

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

бору значений, то это то же самое, что определить факт отсутст-

вия этого значения во всем наборе.

Как уже было сказано, имеются некоторые различия между

EXISTS и операторами ANY и ALL с точки зрения работы с не-

известными и отсутствующими данными. Кроме того, важное

различие между ALL и ANY - это способ действия в ситуации,

когда подзапрос не возвращает никаких значений. Вообще гово-

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

да, ALL автоматически возвращает значение ИСТИНА, a ANY -

ЛОЖЬ. Например, это означает, что запрос:

SELECT *

FROM USP

' WHERE OCENKA >= ANY

(SELECT OCENKA

FROM USP

WHERE UDATE = 10/06/2000);

Глава 2.2. Выборка, или чтение данных

185

не произведет никакого вывода, в то время как запрос

SELECT *

FROM USP

WHERE OCENKA >= ALL

(SELECT OCENKA

FROM USP

WHERE UDATE = 10/06/2000);

выведет всю таблицу USP. Действительно, если нет никаких оце-

нок за 10/06/2000 то, естественно, ни одно из этих сравнений не

имеет значения.

NULL значения имеют свои особенности при их обработке

этими операторами. Когда SQL сравнивает два значения в

предикате, одно из которых NULL, очевидно, результат этого

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

является причиной того, что запись не выбирается, однако ра-

ботать он будет несколько иначе, в зависимости от того, ис-

пользуется ALL или ANY. Рассмотрим предыдущий пример с

ANY и такой запрос:

SELECT *

FROM USP FIRST

WHERE EXISTS

(SELECT *

FROM USP SECOND

WHERE FIRST.OCENKA > = SECOND.OCENKA

AND SECOND.UDATE = 10/06/1999);

Пока NULL значений в таблице нет, оба запроса будут вести

себя одинаково. Но в случае появления NULL значения в поле

OCENKA таблицы успеваемости, в варианте с ANY значение

предиката при обработке этой строки станет неизвестным и вы-

вода для нее не будет в любом случае, однако другие строки бу-

дут обрабатываться обычным образом. В варианте с EXISTS,

значение NULL используется в предикате подзапроса, делая его

неизвестным в каждом случае, а это означает, что подзапрос не

будет производить никаких значений, и запрос не произведет

вывода вообще. Это является важным фактором в пользу исполь-

зования варианта с ANY.

Таким образом, подзапросы не самое простое, что имеется в

SQL, однако они являются одним из мощнейших средств этого

языка. Как правило, допускается выполнять тот же запрос с под-

186

Часть II. Структурированный язык запросов SQL

запросами несколькими способами, и, в конечном счете, пользо-

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

Глава 2.3

Внесение изменений в базу данных

При работе с SQL исключительно важно не только уметь вы-

бирать данные, но и пользоваться средствами, которые управля-

ют значениями в таблице. Значения могут быть помещены и уда-

лены из полей тремя командами языка DML (Язык Манипулиро-

вания Данными), а именно:

• INSERT - вставить;

• UPDATE - модифицировать;

• DELETE - уд&ЧИТЬ.

Добавление информации в базу данных

Все записи в SQL вводятся с использованием команды моди-

фикации INSERT. В самой простой форме эта команда имеет

следующий синтаксис:

INSERT INTO <table name>

VALUES «value>, <value> . . .);

Так, например, для добавления записи в таблицу преподавателей

TEACHERS, можно воспользоваться следующим выражением:

INSERT INTO TEACHERS

VALUE S (4006, 'Федченко', 'Светлана',

'Геннадиевна', 01/09/1999);

Команда INSERT не производит никакого вывода, но жела-

тельно, чтобы СУБД давала некоторое подтверждение того, что

данные были успешно внесены. Кроме того, следует помнить, что

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

предварительно определено, а каждое значение в списке встав-

ляемых данных должно совпадать с типом данных столбца, в ко-

торый оно вставляется. Значения в этом списке вводятся в табли-

цу в том порядке, в котором они записаны в команде, поэтому

Глава 2.3. Внесение изменений в базу данных

187

первое значение автоматически попадает в первый столбец, вто-

рое - во второй столбец и т. д.

Если требуется ввести в таблицу NULL значение, то оно вво-

дится точно так же, как и обычное. Например, следующая коман-

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

вателя, вполне допустима:

INSERT INTO TEACHERS

VALUES (NULL, 'Федченко', 'Светлана',

'Геннадиевна', 01/09/1999);

Так как значение NULL - специальное служебное слово, то за-

ключать его в одиночные кавычки не требуется.

Также допускается указывать столбцы, куда необходимо осу-

ществить вставку значения, что позволяет делать это в любом

порядке. Например, команда:

INSERT INTO TEACHERS (TDATE, TFAM, TIMA)

VALUES (01/09/1999, 'Федченко',

'Светлана');

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

TDATE, TFAM, TIMA, причем столбцы TNUM и ТОТСН отсут-

ствуют. Это означает, что для этих полей автоматически устанав-

ливается значение по умолчанию. Значение по умолчанию может

быть введено заранее или, в противном случае, это будет NULL

значение. Если ограничение запрещает использование значения

NULL в данном поле, то обязательно надо позаботиться об обес-

печении столбца содержательным значением для любой команды

INSERT.

Можно использовать команду INSERT для того, чтобы полу-

чать или выбирать значения из одной таблицы и помещать их в

другую вместе с запросом. Для этого предложение VALUES за-

меняется на соответствующий запрос:

INSERT INTO EXCELLENT

SELECT *

FROM USP

WHERE OCENKA = 5;

В результате будет сформирована таблица с данными, приве-

денными в табл. 2.5 (напомним, что сама INSERT команда ника-

кого вывода не производит).

188

Часть II, Структурированный язык запросов SQL

Таблица 2.5

Таблица EXCELLENT

UNUM

1001

1005

OCENKA

EXCELLENT

UDATE

10/06/1999

12/06/1999

SNUM

3412

3416

PNUM

2001

2004

Следовательно, будет сделано следующее: все значения, вы-

данные запросом (информация о студентах, имеющих только от-

личные оценки), помещаются в таблицу, названную

EXCELLENT. Для того чтобы не произошло ошибки, таблица

EXCELLENT должна уже быть создана командой CREATE

TABLE (о ней речь ниже) и иметь пять столбцов, которые совпа-

дают с таблицей USP по типу данных.

Таким образом, будет получена независимая таблица с неко-

торыми данными из таблицы успеваемости USP. При изменении

значений в таблице USP это ни в коем случае не отразится на

таблице EXCELLENT.

В принципе, имеется возможность указывать столбцы по име-

ни, как это уже было продемонстрировано выше, а значит - пере-

упорядочивать добавляемую информацию.

Например, при помощи нижеприведенной команды можно

вставить информацию о среднем балле каждого студента:

INSERT INTO AVGRAITING {SNUM, AVGOCENKA)

SELECT SNUM, AVG (OCENKA)

FROM USP

GROUP BY SNUM;

Обратите внимание на то, что указаны имена столбцов табли-

цы AVGRAITING, а значит, последовательность данных во

вставляемом списке (т.е. порядок следования полей в предложе-

нии SELECT) должна с этим порядком совпадать

В INSERT можно использовать подзапросы внутри любого

запроса, который генерирует значения для этой команды анало-

гично тому, как это уже делалось при рассмотрении соответст-

вующего материала. Например, для вставки в уже имеющуюся

таблицу STO фамилий, имен и отчеств студентов, у которых есть

Глава 2.3. Внесение изменений в базу данных

189

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

щей командой:

INSERT INTO STO (SFAM, SIMA, SOTCH)

SELECT SFAM, SIMA, SOTCH

FROM STUDENTS

WHERE SNUM = ANY

(SELECT SNUM

FROM USP

WHERE OCENKA = 5);

Оба запроса в этой команде функционируют так же, как если

бы они не являлись частью выражения INSERT. Подзапрос нахо-

дит все строки для студентов, имеющих отличные оценки, и

формирует набор значений SNUVL Внешний запрос выбирает

строки из таблицы STUDENTS, где эти значения SNUM найдены,

a INSERT вставляет найденные данные в таблицу STO.

В команде INSERT допускается использовать соотнесенные

подзапросы. Предположим, что имеется таблица МАХО-

CENKA, в которой хранится информация о студенте, имею-

щем максимальную оценку за определенную дату (скажем, для

начисления именной стипендии). Тогда, для отслеживания из-

менения данных в таблице успеваемости и модификации соот-

ветствующей информации о претенденте на именную стипен-

дию, необходимо воспользоваться следующей командой с со-

отнесенным подзапросом:

INSERT INTO MAXOCENKA (SNUM, OCENKA)

SELECT SNUM, OCENKA

FROM USP FIRST

WHERE OCENKA =

(SELECT MAX (OCENKA)

FROM USP SECOND

WHERE FIRST.UDATE = SECOND.UDATE) ;

При этом рассматриваемая команда имеет подзапрос, который

базируется на той же самой таблице, что и внешний запрос, но не

ссылается на таблицу MAXOCENKA, на которую воздействует

команда, поэтому такая конструкция является допустимой.

Удаление данных

Удаление строк из таблицы можно осуществить командой

модификации DELETE. Стоит учитывать, что она может уда-

190

Часть II. Структурированный язык запросов SQL

лять только целые записи таблицы, а не индивидуальные зна-

чения того или иного поля. По этой причине для данного опе-

ратора параметр поля является недоступным. Например, для

удаления всего содержимого таблицы STUDENTS, можно вос-

пользоваться следующим:

DELETE FROM STUDENTS;

В процессе работы чаще необходимо удалять не все данные, а

только некоторые определенные строки из таблицы. Для того

чтобы определить, какие строки будут удалены, используют пре-

дикат, аналогично тому, как это делается для запросов. Напри-

мер, чтобы удалить информацию о студенте Нагорный, можно

использовать следующую команду:

DELETE FROM STUDENTS

WHERE SNUM = 3416;

Здесь в качестве предиката использован номер студенческого

билета: действительно, это поле фактически является первичным

ключом таблицы, что дает гарантию удаления только одной запи-

си. Использование поля SFAM, вообще говоря, приводит к уда-

лению нескольких записей, т.к. в таблице могла храниться ин-

формация об однофамильцах.

В команде DELETE допускается использовать предикат, вы-

бирающий целую группу строк. Например, следующая команда

удаляет из таблицы USP все данные, относящиеся к оценкам, по-

лученным 10/06/1999:

DELETE FROM USP

WHERE UDATE = 10/06/1999;

Наконец, так же, как и в случае с командой INSERT, допус-

кается в предикате использовать вложенный запрос. Чаще все-

го это необходимо, когда критерий, по которому выбираются

данные для удаления, базируется на другой таблице. Напри-

мер, если возникает необходимость в удалении информации о

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

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

нужно выполнить следующее:

DELETE FROM STUDENTS

WHERE SNUM =

Глава 2.3. Внесение изменений в базу данных

191

(SELECT SNUM

FROM USP

WHERE OCENKA = 3);

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

тройки, из таблицы успеваемости, и в предикат основной коман-

ды вернет номера их студенческих билетов.

Допускается в предикате команды DELETE использовать и

подзапросы, что дает возможность установить довольно сложные

критерии того, какие строки будут удаляться. Кроме того, очень

эффективно вьшолнять стачала вторичные действия (проверки и

т.п.), после чего выполнять само удаление. Хотя нельзя ссылаться

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

FROM подзапроса, в предикате допускается ссылка на текущую

строку этой таблицы, т.е. можно использовать соотнесенные под-

запросы. Например:

DELETE FROM STUDENTS

WHERE EXISTS

(SELECT *

FROM USP

WHERE OCENKA = 3

AND STUDENTS.SNUM = USP.SNUM);

Собственно вся команда удаляет информацию, аналогично

предыдущему примеру. Обратите внимание на то, что часть пре-

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

означает, что весь подзапрос будет выполняться отдельно для

каждой строки данной таблицы.

В целом, структура команды DELETE достаточно проста для

понимания, поэтому более подробно на ней останавливаться не

будем.

Изменение существующих данных

Гораздо более серьезным вопросом является возможность из-

менения некоторых или всех значений в существующей строке

таблицы, что реализуется при помощи команды UPDATE.

Эта команда содержит ключевое слово UPDATE, где указыва-

ется имя используемой таблицы, и предложение SET, которое

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

192

Часть II. Структурированный язык запросов SQL

Например, чтобы изменить оценки всех студентов на 5, необхо-

димо использовать команду:

UPDATE USP

SET OCENKA

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

определенные строки таблицы для изменения единственного зна-

чения, и с этой целью вместе с UPDATE можно использовать

предикаты. Например, изменить оценки на 5 по предмету с кодом

2003, можно выполнив такую команду:

UPDATE USP

SET OCENKA = 5

WHERE PNUM = 2003;

При помощи команды UPDATE можно модифицировать дан-

ные из нескольких полей - предложение SET может назначать

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

значения могут быть сделаны для любой табличной строки, но

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

преподаватель Викулина ушла на пенсию, и вместо нее занятия

должна вести преподаватель Федченко. Это можно реализовать

такой командой:

UPDATE TEACHERS

SET TFAM = 'Федченко', TNAME

TOTCH = 'Геннадиевна', TDATE =

'Светлана',

01/09/1999

WHERE TNUM = 4001;

Эта команда передаст новому преподавателю Федченко все

текущие учебные предметы из таблицы PREDMET - в нашем

примере это будет физика. Однако имейте в виду, что модифици-

ровать сразу много таблиц в одной команде UPDATE нельзя, а

следовательно, нельзя и использовать название (префикс) табли-

цы с именем поля для этой команды. Т.е.. например, предложение

SET TEACHERS . TFAM = 'Федченко' вызовет ошибку.

В предложении SET команды UPDATE можно использовать

выражения, располагая их в списке для того поля, которое необ-

Глаеа 2.3. Внесение изменений в базу данных

193

холимо изменить (напомним, что в предложении VALUES ко-

манды INSERT выражения использовать нельзя). Например, для

того, чтобы увеличить стипендию в 2 раза, можно использовать

следующую конструкцию:

UPDATE STUDENTS

SET STIP = STIP*2;

При этом каждый раз, когда команда ссылается к указанному

значению поля в предложении SET, действие производится, ра-

зумеется, над еще не модифицированными данными текущей

записи.

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

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

удвоить только стипендию размером 25.50, то команда будет

следующей:

UPDATE STUDENTS

SET STIP = STIP*2

WHERE STIP = 25.50;

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

Кстати говоря, команда UPDATE может работать с NULL

значениями. Т.к. предложение SET не является предикатом, то

можно вводить NULL значения так же, как вводятся обыкновен-

ные данные. Так что, если необходимо изменить все оценки сту-

дентов по учебному предмету с кодом 2003 на NULL, можно вос-

пользоваться следующей командой:

UPDATE USP

SET OCENKA = NULL

WHERE PNUM = 2003;

Мощным средством модификации данных является использо-

вание подзапросов в команде модификации UPDATE. Важный

принцип, который надо соблюдать при работе с командами мо-

дификации и подзапросами, состоит в том, что нельзя в предло-

жении FROM любого подзапроса модифицировать таблицу, к

которой ссылается основная команда.

Обратите внимание на следующий важный момент - в ко-

манде модификации UPDATE (кстати, так же, как и в команде

7 «Базы данных»

194

Часть II. Структурированный язык запросов SQL

INSERT) может возникнуть проблематичная ситуация, связан-

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

тате вложенного запроса. В этом случае, если в модифицируе-

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

ния быть уникальными, команда модификации или вставки

потерпит неудачу. Поэтому рекомендуется каким-либо обра-

зом выяснить то, что эти значения могли были уже быть ис-

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

дифицировать запись. Это можно реализовать с помощью до-

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

операторы EXISTS, IN, < > или аналогичные.

С другой стороны, не стоит забывать о запрещении ссылки во

вложенных запросах к таблице, которая модифицируется коман-

дой UPDATE. По этой причине запросы и подзапросы в командах

модификации имеют порой достаточно сложную структуру. Кро-

ме того, внутри необязательного предиката этой команды можно

использовать соотнесенные подзапросы, аналогично тому как это

делается для DELETE.

Например, следующий запрос увеличивает размер стипендии

в 2 раза студентам, у которых имеются оценки, по крайней мере,

по двум учебным предметам:

UPDATE STUDENTS

SET STIP = STIP*2

WHERE 2 <=

(SELECT COUNT (SNUM)

FROM USP

WHERE STUDENTS.SNUM = USP.SNUM);

Здесь внутренний запрос подсчитывает количество записей в

таблице успеваемости для каждого студента, и, если оно 2 или

больше, предикат основной функции становится истинным, а

размер стипендии модифицируется.

Рассмотрим еще один, достаточно сложный, пример с соотне-

сенным подзапросом. Здесь будем модифицировать размер сти-

пендии для студентов, имеющих минимальный балл в тот или

иной день:

UPDATE STUDENTS

SET STIP = STIP - 1

WHERE SNUM IN

Глава 2.З. Внесение изменений в базу данных

195

(SELECT SNUM

FROM USP FIRST

WHERE OCENKA =

(SELECT MIN (OCENKA)

FROM USP SECOND

WHERE FIRST.UDATE = SECOND.UDATE));

Как уже говорилось, к существенному недостатку UPDATE

стоит отнести невозможность сослаться на таблицу, задейство-

ванную в любом подзапросе из команды модификации. Напри-

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

модификация оценок для студентов, у которых оценки ниже

средней. Для выполнения этого действия сначала придется вы-

полнить поиск средней оценки

SELECT AVG (OCENKA)

FROM USP;

а затем результат этого запроса (4.2) использовать для моди-

фикации

UPDATE USP

SET OCENKA = OCENKA - 1

WHERE OCENKA < 4.2

Таким образом, команда UPDATE, управляющая содержани-

ем записи, является одной из ключевых в языке SQL. Она приме-

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

кат, определяющий модифицируемые записи, так и к конкретным

строкам при наличии предиката, который, в свою очередь, может

иметь достаточно сложную структуру.

Глава 2.4

Способы создания баз данных

Создание баз данных

Говоря о DML, было установлено, что эти операторы могут

манипулировать с данными, хранимыми в БД, однако не могут

196

Часть II. Структурированный язык запросов SQL

изменить ее структуру. Для изменения структуры БД в SQL пре-

дусмотрен язык определения данных, или DDL.

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

БД или таблицы внутри нее. Традиционно он работает с уже го-

товой структурой, которая уже разработана и реализована адми-

нистратором БД. Тем не менее, для полного понимания особен-

ностей работы SQL на операторах DDL стоит остановиться дос-

таточно подробно. С помощью этих операторов можно:

• создать новую БД;

• определить структуру новой таблицы и создать эту таб-

лицу;

• удалить существующую таблицу;

• изменить определение существующей таблицы;

• определить представление данных:

• обеспечить условия безопасности БД;

• создать индексы для доступа к таблицам;

• управлять размещением данных на устройствах хранения.

Операторы DDL позволяют пользователю не вникать в детали

хранения информации в БД на физическом уровне, т.к. опериру-

ют, например, такими понятиями, как таблицы или поля. В то же

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

физической памятью.

Собственно DDL базируется на трех командах SQL:

• CREATE - создать, позволяющий определить и создать

объект БД;

• DROP - удалить, применяемый для удаления сущест-

вующего объекта данных;

• ALTER - изменить, с помощью которого можно изменить

определение объекта БД.

Использование команд DDL во время работы позволяет

сделать структуру реляционной БД динамической. Другими

словами, в СУБД можно создавать, удалять или изменять таб-

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

к данным. В свою очередь, это означает, что с течением вре-

мени БД может расти и изменяться, а ее эксплуатация может

продолжаться в то время, когда в БД добавляются все новые

таблицы и приложения.

Операторы DDL в СУБД можно использовать как в интерак-

тивном, так и в программном SQL. Например, если программе

Глава 2.4. Способы создания баз данных

197

или пользователю требуется таблица для временного хранения

результатов, то допускается создать эту таблицу, заполнить ее

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

и затем удалить ее. В серьезных СУБД за создание новых БД от-

вечает только администратор, хотя не исключена возможность

того, что и отдельным пользователям это может быть разрешено.

Методы создания БД, применяемые в ведущих реляционных

СУБД, имеют ряд различий. Например, в Microsoft SQL Server

существует оператор CREATE DATABASE, который является

частью языка определения данных и служит для создания БД.

Соответственно, оператор DROP DATABASE удаляет сущест-

вующие БД. Эти операторы можно использовать как в интерак-

тивном, так и в программном SQL.

Большинство многопользовательских БД имеют достаточно

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

повышение ее производительности. Например, в Microsoft SQL

Server администратор БД может с помощью оператора CREATE

DATABASE задать один или несколько именованных файлов;

CREATE DATABASE <NAME_DATABASE>

ON <FILE1>, <FILE2>, ...

Подход, используемый в SQL Server, позволяет распределять

содержимое БД по нескольким дисковым томам, о чем уже гово-

рилось выше. Следующим шагом, вслед за созданием пустой БД,

является заполнение ее таблицами.

Создание таблиц

Итак, после создания БД необходимо осуществить создание, из-

менение, а если нужно - то и удаление таблиц. Эти действия отно-

сятся к самим таблицам, а не к данным, которые в них содержатся.

Таблицы создаются командой CREATE TABLE. Эта команда

создает пустую таблицу, т.е. не содержащую записей. Очевидно,

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

INSERT. Главное в команде CREATE TABLE - это определение

имени таблицы и описания набора имен полей, которые указы-

ваются в соответствующем порядке. Кроме того, этой командой

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

198

Часть II. Структурированный язык запросов SQL

Очевидно, что в каждой таблице должно быть, по крайней мере,

одно поле.

Синтаксис команды CREATE TABLE следующий:

CREATE TABLE <TABLE NAME>

(<COLUMN NAME> <DATA TYPE> [(<SIZE>)],

<COLUMN NAME> <DATA TYPE> [ «SIZE» ]

... ) ;

Имейте в виду: в силу того, что пробелы используются для

разделения элементов команды SQL, они не могут быть частью

имени таблицы или любого другого создаваемого объекта. По-

этому символ подчеркивания обычно используется для разделе-

ния слов в именах таблиц.

Значение аргумента размера зависит от типа данных. Если его

не указывать, то СУБД сама будет назначать значение автомати-

чески. Надо сказать, что для числовых значений это часто бывает

лучшим выходом, т.к. в этом случае все поля такого типа получат

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

совместимости. Кроме того, использование аргумента размера с

некоторыми числовым данными не совсем простой вопрос - если

требуется хранить большие числа, то необходимо убедиться в

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

В то же время, тип данных CHAR требует обязательного ука-

зания размера. Аргумент размера - это целое число, определяю-

щее максимальное количество символов, которое может вместить

поле. Фактически, количество символов такого поля может быть

от нуля (если поле имеет значение NULL) до этого числа. По

умолчанию аргумент размера равен 1, а это означает, что поле

может содержать только один символ.

Кроме того, таблицы принадлежат пользователю, который их

создал, а имена всех таблиц, принадлежащих данному пользова-

телю, должны отличаться друга от друга точно так же, как и име-

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

могут использовать одинаковые имена полей, даже если они при-

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

именем SNUM присутствует в таблицах STUDENTS и USP, ни-

чуть не мешая друг другу.

Как уже было сказано, пользователи, не являющиеся владель-

цами таблиц, могут ссылаться к этим таблицам с помощью имени

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

SA. STUDENTS

Глава 2.4. Способы создания баз данных

199

подразумевает, что обращение идет к таблице STUDENTS,

созданной пользователем с идентификатором разрешения (ID)

SA.

Приведем пример команды, которая создаст структуру табли-

цы STUDENTS:

CREATE TABLE STUDENTS

(SNUM INTEGER,

SFAM CHAR (20),

SIMA CHAR (10),

SOTCH CHAR (15)

STIP DECIMAL);

Порядок расположения полей в таблице определяется тем. в ка-

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

После того, как таблица была создана, ее можно изменить.

Команда ALTER TABLE является широко доступным средством

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

Чаще всего с ее помощью добавляют поля к таблице, хотя она

может удалять или изменять их размеры. Типичный синтаксис

этой команды для добавления столбца к таблице, такой:

ALTER TABLE <TABLE NAME>

ADD <COLUMN NAME> <DATA TYPE> <SIZE>;

Стоит помнить, что поле будет добавлено с NULL значениями

для всех записей таблицы. Кроме того, новое поле станет послед-

ним по порядку в таблице. Допускается добавление сразу не-

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

Например, для добавления к таблице STUDENTS двух полей

для хранения информации о курсе и специальности студента,

можно воспользоваться следующей командой:

ALTER TABLE STUDENTS

ADD COURS INTEGER,

SPEC CHAR (10);

С использованием этой команды имеется возможность уда-

лять или изменять поля, причем наиболее часто изменением бы-

вает просто увеличение его размера. Обязательно нужно убедить-

ся, что любые вносимые изменения не противоречат существую-