Максимов Е.М. Структурированный язык запросов - SQL
Подождите немного. Документ загружается.
Структурированный язык запросов – SQL, страница 51
1001 5 10/06/1999 3412 2001
1005 5 12/06/1999 3416 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 фамилий, имен и отчеств студентов,
у которых есть хотя бы одна отличная оценка, можно воспользоваться
следующей командой:
INSERT INTO STO (SFAM, SIMA, SOTCH) SELECT
SFAM, SIMA, SOTCH FROM STUDENTS WHERE SNUM =
ANY (SELECT SNUM FROM USP
WHERE OCENKA = 5);
Оба запроса в этой команде функционируют так же, как если бы они не
являлись частью выражения INSERT. Подзапрос находит все строки для
студентов, имеющих отличные оценки, и формирует набор значений SNUM.
Внешний запрос выбирает строки из таблицы STUDENTS, где эти значения
SNUM найдены, a INSERT вставляет найденные данные в таблицу STO.
В команде INSERT допускается использовать соотнесенные
подзапросы. Предположим, что имеется таблица МАХОCENKA. в которой
Структурированный язык запросов – SQL, страница 52
хранится информация о студенте, имеющем максимальную оценку за
определенную дату (скажем, для начисления именной стипендии). Тогда, для
отслеживания изменения данных в таблице успеваемости и модификации
соответствующей информации о претенденте на именную стипендию,
необходимо воспользоваться следующей командой с соотнесенным
подзапросом:
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. Стоит учитывать, что она может удалять только
целые записи таблицы, а не индивидуальные значения того или иного поля.
По этой причине для данного оператора параметр поля является
недоступным. Например, для удаления всего содержимого таблицы
STUDENTS, можно воспользоваться следующим:
DELETE FROM STUDENTS;
В процессе работы чаще необходимо удалять не все данные, а только
некоторые определенные строки из таблицы. Для того, чтобы определить,
какие строки будут удалены, используют предикат, аналогично тому, как это
делается для запросов. Например, чтобы удалить информацию о студенте
Нагорный, можно использовать следующую команду:
DELETE FROM STUDENTS WHERE SNUM = 3416;
Здесь в качестве предиката использован номер студенческого билета.
Действительно, это поле фактически является первичным ключом таблицы,
что дает гарантию удаления только одной записи. Использование поля
SFAM, вообще говоря, приводит к удалению нескольких записей, т.к. в
таблице могла храниться информация об однофамильцах.
В команде DELETE допускается использовать предикат, выбирающий
целую группу строк. Например, следующая команда удаляет из таблицы USP
все данные, относящиеся к оценкам, полученным 10/06/1999:
Структурированный язык запросов – SQL, страница 53
DELETE FROM USP
WHERE UDATE = 10/06/1999;
Наконец, так же, как и в случае с командой INSERT, допускается в
предикате использовать вложенный запрос. Чаще всего это необходимо,
когда критерий, по которому выбираются данные для удаления, базируется
на другой таблице. Например, если возникает необходимость в удалении
информации о студентах из таблицы STUDENTS, причем для таких, у кото-
рых имеются тройки по любому из учебных предметов, то нужно выполнить
следующее:
DELETE FROM STUDENTS WHERE SNUM =
(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. Это означает,
что весь подзапрос будет выполняться отдельно для каждой строки данной
таблицы.
Изменение существующих данных
Изменение некоторых или всех значений в существующей строке
таблицы реализуется при помощи команды UPDATE.
Структурированный язык запросов – SQL, страница 54
Эта команда содержит ключевое слово UPDATE, где указывается имя
используемой таблицы, и предложение SET, которое определяет вносимое
изменение для требуемого поля таблицы.
Например, чтобы изменить оценки всех студентов на 5, необходимо
использовать команду:
UPDATE USP
SET OCENKA = 5;
Конечно, гораздо чаще приходится указывать не все, а только
определенные строки таблицы для изменения единственного значения, и с
этой целью вместе с UPDATE можно использовать предикаты. Например,
изменить оценки на 5 по предмету с кодом 2003, можно выполнив такую
команду:
UPDATE USP
SET OCENKA = 5 WHERE PNUM = 2003;
При помощи команды UPDATE можно модифицировать данные из
нескольких полей - предложение SET может назначать любое число
столбцов, отделяемых запятыми. Все указанные назначения могут быть
сделаны для любой табличной строки, но только для одной в каждый момент
времени. Предположим, что преподаватель Викулина ушла на пенсию, и
вместо нее занятия должна вести преподаватель Федченко. Это можно
реализовать такой командой:
UPDATE TEACHERS
SET TFAM = 'Федченко', TNAME
TOTCH = 'Геннадиевна', TDATE =
'Светлана',
WHERE TNUM = 4001;
Эта команда передаст новому преподавателю Федченко все текущие
учебные предметы из таблицы PREDMET - в нашем примере это будет
физика. Однако имейте в виду, что модифицировать сразу много таблиц в
одной команде UPDATE нельзя, а, следовательно, нельзя и использовать
название (префикс) таблицы с именем поля для этой команды. Т.е.,
например, предложение
SET TEACHERS.TFAM = 'Федченко' ;
вызовет ошибку.
В предложении SET команды UPDATE можно использовать
выражения, располагая их в списке для того поля, которое необходимо
изменить (напомним, что в предложении VALUES команды INSERT
Структурированный язык запросов – SQL, страница 55
выражения использовать нельзя). Например, для того, чтобы увеличить
стипендию в 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 (кстати, так же, как и в команде INSERT) может возникнуть
проблематичная ситуация, связанная с возможными дубликатами строк,
получаемыми в результате вложенного запроса. В этом случае, если в
модифицируемой таблице есть ограничения, которые вынуждают ее значе-
ния быть уникальными, команда модификации или вставки потерпит
неудачу. Поэтому рекомендуется каким-либо образом выяснить это, прежде
чем пытаться вставить или модифицировать запись. Это можно реализовать с
помощью добавления вложенного подзапроса, использующего в предикате
операторы EXISTS. IN, < > или аналогичные.
С другой стороны, не стоит забывать о запрещении ссылки во
вложенных запросах к таблице, которая модифицируется командой UPDATE.
По этой причине запросы и подзапросы в командах модификации имеют
Структурированный язык запросов – SQL, страница 56
порой достаточно сложную структуру. Кроме того, внутри необязательного
предиката этой команды можно использовать соотнесенные подзапросы,
аналогично тому, как это делается для 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
(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. Она применима как ко всем
строкам таблицы, если не используется предикат, определяющий
модифицируемые записи, так и к конкретным строкам при наличии
Структурированный язык запросов – SQL, страница 57
предиката, который, в свою очередь, может иметь достаточно сложную
структуру.