Файл: Методическое пособие к выполнению самостоятельной и лабораторных работ и курсового проекта по дисциплине Организация баз данных для студентов направления подготовки бакалавров.pdf

40
Предикат EXISTS (с необязательным зарезервированным словом
NOT) используется в логическом выражении для определения того, должен ли подчиненный запрос возвращать какие-либо записи.
В подчиненном запросе можно использовать псевдонимы таблиц для ссылки на таблицы, перечисленные в предложении FROM, распо- ложенном вне подчиненного запроса. В следующем примере отбира- ются фамилии и имена студентов, чья стипендия равна или больше средней стипендии студентов, обучающихся в той же группе. В дан- ном примере таблица СТУДЕНТЫ получает псевдоним С1:
SELECT Фамилия,
Имя, Номер_группы, Стипендия
FROM СТУДЕНТЫ AS С1
WHERE Стипендия >=
(SELECT Avg(Стипендия)
FROM СТУДЕНТЫ
WHERE С1.Номер_группы = СТУДЕНТЫ.Номер_группы) Order by Номер_группы;
В последнем примере зарезервированное слово AS не является обязательным.
Некоторые подчиненные запросы можно использовать в пере- крестных запросах как предикаты (в предложении WHERE). Подчи- ненные запросы, используемые для вывода результатов (в списке
SELECT), нельзя использовать в перекрестных запросах.
Создание новой таблицы
Инструкция CREATE TABLE создает новую таблицу и исполь- зуется для описания ее полей и индексов. Если для поля добавлено ограничение NOT NULL, то при добавлении новых записей это поле должно содержать допустимые данные. Синтаксис:
CREATE TABLE таблица (поле_1 тип [(размер)]
[NOT NULL] [индекс_1] [, поле_2 тип [(размер)]
[NOT NULL] [индекс_2] [, ...]] [, CONSTRAINT составной Индекс
[, ...]]), где таблица — имя создаваемой таблицы; поле_1, поле_2 — имена одного или нескольких полей, создавае- мых в новой таблице. Таблица должна содержать хотя бы одно поле; тип — тип данных поля в новой таблице;

41
размер — размер поля в символах (только для текстовых и двоич- ных полей); индекс_1, индекс_2 — предложение CONSTRAINT, предназна- ченное для создания простого индекса; составной Индекс — предложение CONSTRAINT, предназначен- ное для создания составного индекса. следующем примере создается новая таблица с двумя полями:
CREATE TABLE Студенты (Номер_зачетной_книжки integer
PRIMARY KEY, ФИО_студента TEXT (50), Место_рождения TEXT
(50));
В результате выполнения этого запроса будет создана таблица со следующей схемой (Рисунок 22):
Рисунок 22. Схема таблицы СТУДЕНТЫ
Предложение CONSTRAINT используется в инструкциях ALTER
TABLE и CREATE TABLE для создания или удаления индексов. Су- ществуют два типа предложений CONSTRAINT: для создания просто- го индекса (по одному полю) и для создания составного индекса (по нескольким полям). Синтаксис: простой индекс:
CONSTRAINT имя {PRIMARY KEY|UNIQUE | NOT NULL]} составной индекс:
CONSTRAINT имя
{PRIMARY KEY (ключевое_1[, ключевое_2 [, ...]]) |
UNIQUE (уникальное_1[, уникальное_2 [, ...]]) |
NOT NULL (непустое_1[, непустое_2 [, ...]]) |
FOREIGN KEY (ссылка_1[, ссылка_2 [, ...]])
REFERENCES внешняя Таблица [(внешнее Поле_1 [, внешнее
Поле_2 [, ...]])]}, где имя — имя индекса, который следует создать; ключевое_1, ключевое_2 — имена одного или нескольких полей, которые следует назначить ключевыми;

42
уникальное_1, уникальное_2 — имена одного или нескольких по- лей, которые следует включить в уникальный индекс; непустое_1, непустое_2 — имена одного или нескольких полей, в которых запрещаются значения Null; ссылка_1, ссылка_2 — имена одного или нескольких полей, включенных во внешний ключ, которые содержат ссылки на поля в другой таблице; внешняя Таблица — имя внешней таблицы, которая содержит по- ля, указанные с помощью аргумента внешнееПоле; внешнее Поле_1, внешнее Поле_2 — имена одного или несколь- ких полей во внешней Таблице, на которые ссылаются поля, указан- ные с помощью аргумента ссылка_1, ссылка_2. Это предложение можно опустить, если данное поле является ключом внешней Табли- цы.
Предложение CONSTRAINT позволяет создать для поля индекс одного из двух описанных ниже типов:
1) уникальный индекс, использующий для создания зарезервиро- ванное слово UNIQUE. Это означает, что в таблице не может быть двух записей, имеющих одно и то же значение в этом поле. Уникаль- ный индекс создается для любого поля или любой группы полей. Если в таблице определен составной уникальный индекс, то комбинация значений включенных в него полей должна быть уникальной для каж- дой записи таблицы, хотя отдельные поля и могут иметь совпадающие значения;
2) ключ таблицы, состоящий из одного или нескольких полей, использующий зарезервированные слова PRIMARY KEY. Все значения ключа таблицы должны быть уникальными и не значениями Null. Кроме того, в таблице может быть только один ключ.
Для создания внешнего ключа можно использовать зарезервиро- ванную конструкцию FOREIGN KEY. Если ключ внешней таблицы состоит из нескольких полей, необходимо использовать предложение
CONSTRAINT. При этом следует перечислить все поля, содержащие ссылки на поля во внешней таблице, а также указать имя внешней таб- лицы и имена полей внешней таблицы, на которые ссылаются поля, перечисленные выше, причем в том же порядке. Однако, если послед- ние поля являются ключом внешней таблицы, то указывать их необя- зательно, поскольку ядро базы данных считает, что в качестве этих по- лей следует использовать поля, составляющие ключ внешней таблицы.

43
В следующем примере создается таблица
ЗАДОЛЖЕН-
НОСТЬ_ЗА_ОБУЧЕНИЕ с единственным полем
НО-
МЕР_ЗАЧЕТНОЙ_КНИЖКИ и внешним ключом f1_i, связанным с полем
НОМЕР_ЗАЧЕТНОЙ_КНИЖКИ в таблице СТУДЕНТЫ:
CREATE TABLE Задолженность_за_обучение
(Код_задолженности integer
PRIMARY
KEY,
Но- мер_зачетной_книжки integer, CONSTRAINT f1_i FOREIGN KEY
(Номер_зачетной_книжки)
REFERENCES
Студенты
(Но- мер_зачетной_книжки));
Внешний вид схемы БД, состоящей из таблиц СТУДЕНТЫ и ЗА-
ДОЛЖЕННОСТЬ_ЗА_ОБУЧЕНИЕ, представлен на рисунке 23.
Рисунок 23 – Схема данных
Изменение структуры таблицы
Инструкция ALTER TABLE изменяет структуру таблицы, со- зданной с помощью инструкции CREATE TABLE.
Синтаксис:
ALTER TABLE таблица {ADD {COLUMN поле тип[(размер)]
[NOT NULL]
[CONSTRAINT индекс] | CONSTRAINT составной Индекс} |
DROP {COLUMN поле I CONSTRAINT имя Индекса} } где таблица — имя изменяемой таблицы; поле — имя поля, добавляемого в таблицу или удаляемого из нее; тип — тип данных поля; размер — размер поля; индекс — индекс для поля; составной Индекс — описание составного индекса, добавляемого к таблице; имя Индекса — имя составного индекса, который следует уда- лить.
С помощью инструкции ALTER TABLE существующую таблицу можно изменить несколькими способами:

44 1) добавить новое поле в таблицу с помощью предложения ADD
COLUMN. В этом случае необходимо указать имя поля, его тип и раз- мер. Например, следующая инструкция добавляет в таблицу СТУ-
ДЕНТЫ текстовое поле ПРИМЕЧАНИЯ длиной 50 символов:
ALTER TABLE Студенты ADD COLUMN Примечания TEXT(50)
Если для поля добавлено ограничение NOT NULL, то при добав- лении новых записей это поле должно содержать допустимые данные;
2) добавить составной индекс с помощью зарезервированных слов ADD CONSTRAINT;
3) удалить поле с помощью зарезервированных слов DROP
COLUMN. В этом случае необходимо указать только имя поля;
4) удалить составной индекс с помощью зарезервированных слов
DROP CONSTRAINT. В этом случае указывается только имя составного индекса, следующее за зарезервированным словом CONSTRAINT.
Создание индекса с помощью инструкции CREATE INDEX
CREATE INDEX создает новый индекс для существующей таб- лицы. Синтаксис команды:
CREATE [UNIQUE] INDEX индекс
ON таблица (поле [ASC|DESC][, поле [ASC|DESC], ...])
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }] где индекс — имя создаваемого индекса; таблица — имя существующей таблицы, для которой создается индекс; поле — имена одного или нескольких полей, включаемых в ин- декс. Для создания простого индекса, состоящего из одного поля, вво- дится имя поля в круглых скобках сразу после имени таблицы. Для со- здания составного индекса, состоящего из нескольких полей, перечисля- ются имена всех этих полей. Для расположения элементов индекса в убывающем порядке используется зарезервированное слово DESC; в противном случае будет принят порядок по возрастанию.
Чтобы запретить совпадение значений индексированных полей в разных записях, используется зарезервированное слово UNIQUE. Не- обязательное предложение WITH позволяет задать условия на значе- ния. Например:

с помощью параметра DISALLOW NULL можно запретить значения Null в индексированных полях новых записей;

параметр IGNORE NULL позволяет запретить включение в индекс записей, имеющих значения Null в индексированных полях;

45

зарезервированное слово PRIMARY позволяет назначить индек- сированные поля ключом. Такой индекс по умолчанию является уникаль- ным, следовательно, зарезервированное слово UNIQUE можно опустить.
Удаление таблицы/индекса
Инструкция DROP удаляет существующую таблицу из базы дан- ных или удаляет существующий индекс из таблицы. Синтаксис:
DROP {TABLE таблица | INDEX индекс ON таблица} где таблица — имя таблицы, которую следует удалить или из которой следует удалить индекс; индекс — имя индекса, удаляемого из таблицы.
Прежде чем удалить таблицу или удалить из нее индекс, необхо- димо ее закрыть. Следует отметить, что таблица удаляется из базы данных безвозвратно.
Удаление записей
Инструкция DELETE создает запрос на удаление записей из од- ной или нескольких таблиц, перечисленных в предложении FROM и удовлетворяющих предложению WHERE.
Синтаксис команды:
DELETE [Таблица.*]
FROM таблица
WHERE условие Отбора где Таблица — необязательное имя таблицы, из которой удаляются записи; таблица — имя таблицы, из которой удаляются записи; условие Отбора — выражение, определяющее удаляемые записи.
С помощью инструкция DELETE можно осуществлять удаление большого количества записей. Данные из таблицы также можно уда- лить и с помощью инструкции DROP, однако при таком удалении те- ряется структура таблицы. Если же применить инструкцию DELETE, удаляются только данные. При этом сохраняются структура таблицы и все остальные ее свойства, такие, как атрибуты полей и индексы.
Запрос на удаление удаляет записи целиком, а не только содержи- мое указанных полей. Нельзя восстановить записи, удаленные с помо- щью запроса на удаление. Чтобы узнать, какие записи будут удалены, необходимо посмотреть результаты запроса на выборку, использующего те же самые условие отбора в предложении Where, а затем выполнить запрос на удаление.

46
Добавление записей
Инструкция INSERT INTO добавляет запись или записи в табли- цу.
Синтаксис команды: а) запрос на добавление нескольких записей:
INSERT INTO назначение [(поле_1[, поле_2[, ...]])]
SELECT [источник.]поле_1[, поле_2[, ...]
FROM выражение б) запрос на добавление одной записи:
INSERT INTO назначение [(поле_1[, поле_2[, ...]])]
VALUES (значение_1[, значение_2[, ...]) где назначение — имя таблицы или запроса, в который добавляются записи; источник — имя таблицы или запроса, откуда копируются записи; поле_1, поле_2 — имена полей для добавления данных, если они следуют за аргументом «Назначение»; имена полей, из которых берутся данные, если они следуют за аргументом источник; выражение — имена таблицы или таблиц, откуда вставляются данные. Это выражение может быть именем отдельной таблицы или результатом операции INNER JOIN, LEFT JOIN или RIGHT JOIN, а также сохраненным запросом; значение_1, значение_2 — значения, добавляемые в указанные по- ля новой записи. Каждое значение будет вставлено в поле, занимающее то же положение в списке: значение_1 вставляется в поле_1 в новой записи, значение_2 — в поле_2 и т.д. Каждое значение текстового поля следует заключать в кавычки (' '), для разделения значений использу- ются запятые.
Инструкцию INSERT INTO можно использовать для добавления одной записи в таблицу с помощью запроса на добавление одной запи- си, описанного выше. В этом случае инструкция должна содержать имя и значение каждого поля записи. Нужно определить все поля записи, в которые будет помещено значение, и значения для этих полей. Если поля не определены, в недостающие столбцы будет вставлено значение по умолчанию или значение Null. Записи добавляются в конец таблицы.
Инструкцию INSERT INTO можно также использовать для до- бавления набора записей из другой таблицы или запроса с помощью предложения SELECT ... FROM, как показано выше в запросе на до- бавление нескольких записей. В этом случае предложение SELECT определяет поля, добавляемые в указанную таблицу НАЗНАЧЕНИЕ.

47
Инструкция INSERT INTO является необязательной, однако, если она присутствует, то должна находиться перед инструкцией SELECT.
Запрос на добавление записей копирует записи из одной или не- скольких таблиц в другую таблицу. Таблицы, которые содержат до- бавляемые записи, не изменяются.
Вместо добавления существующих записей из другой таблицы, можно указать значения полей одной новой записи с помощью пред- ложения VALUES. Если список полей опущен, предложение VALUES должно содержать значение для каждого поля таблицы; в противном случае инструкция INSERT не будет выполнена. Можно использовать дополнительную инструкцию INSERT INTO с предложением VALUES для каждой добавляемой новой записи.
Обновление данных
Инструкция UPDATE создает запрос на обновление, который из- меняет значения полей указанной таблицы на основе заданного усло- вия отбора.
Синтаксис команды:
UPDATE таблица
SET новое Значение
WHERE условие Отбора; где таблица — имя таблицы, данные в которой следует изменить; новое Значение — выражение, определяющее значение, которое должно быть вставлено в указанное поле обновленных записей; условие Отбора — выражение, отбирающее записи, которые долж- ны быть изменены.
При выполнении этой инструкции будут изменены только записи, удовлетворяющие указанному условию. Инструкцию UPDATE осо- бенно удобно использовать для изменения сразу нескольких записей или в том случае, если записи, подлежащие изменению, находятся в разных таблицах. Одновременно можно изменить значения нескольких полей. Следующая инструкция SQL увеличивает стипендию студентов группы 422-1 на 10 %:
UPDATE Студенты
SET Стипендия = стипендия * 1.1
WHERE Номер_группы = '422-1';
Запрос на объединение
Операция UNION создает запрос на объединение, который объ- единяет результаты нескольких независимых запросов или таблиц.

48
Синтаксис команды:
[TABLE] запрос_1 UNION [ALL] [TABLE] запрос_2 [UN-
ION[ALL] [TABLE] запрос_n [...]] где запрос_1-n — инструкция SELECT или имя сохраненной таблицы, перед которым стоит зарезервированное слово TABLE.
В одной операции UNION можно объединить в любом наборе ре- зультаты нескольких запросов, таблиц и инструкций SELECT. В сле- дующем примере объединяется существующая таблица СТУДЕНТЫ и инструкции SELECT:
TABLE Студенты UNION ALL
SELECT *
FROM Абитуриенты
WHERE Общий_балл > 22;
По умолчанию повторяющиеся записи не возвращаются при ис- пользовании операции UNION, однако в нее можно добавить предикат
ALL, чтобы гарантировать возврат всех записей. Кроме того, такие запросы выполняются несколько быстрее.
Все запросы, включенные в операцию UNION, должны отбирать одинаковое число полей; при этом типы данных и размеры полей не обязаны совпадать. Псевдонимы необходимо использовать только в первом предложении SELECT, в остальных они пропускаются.
В каждом аргументе «Запрос» допускается использование пред- ложения GROUP BY или HAVING для группировки возвращаемых данных. В конец последнего аргумента «Запрос» можно включить предложение ORDER BY, чтобы отсортировать возвращенные данные.