Таблица может быть создана либо стандартным образом через описа-ние ее компонент, либо в результате выполнения некоторого подзапроса.
Подзапрос– это обычный запрос на выборку информации, реализуемый операторомSELECT. При создании таблицы задаваемые имена таблиц и имена столбцов должны удовлетворять правилам, предписываемые идентификаторам. При этом естественно, что имена, присваиваемые таб-лицам, должны быть уникальными в схеме пользователя, а имена столб-цов должны быть уникальными в рамках одной таблицы. Для каждого столбца указываются тип данных и, если необходимо, значение, встав-ляемое в столбец по умолчанию(DEFAULT значение). Важным элемен-том при создании таблиц является задание ограничений целостности данных, которые позволяют отслеживать правильность модификации имеющихся данных или вставляемых в таблицу новых данных. Ограни-чения целостности данных делятся на два типа: ограничения столбца и ограничения таблицы. Ограничения столбца позволяют определить усло-вия, которым должны удовлетворять значения соответствующего столб-ца; ограничения целостности данных, накладываемые на таблицу, позво-ляют проверить правильность всех добавляемых или модифицируемых строк таблицы. Ограничение может быть именованным или безымян-ным. Удобнее использовать именованные ограничения, поскольку при выдаче информации, связанной с возникшим нарушением одного из ог-раничений, выдается и имя этого ограничения, что весьма полезно для исправления ошибок в дальнейшем. Задание ограничений на столбец или ограничений на таблицу осуществляется по следующему синтаксису:
[CONSTRAINT имя_ограничения] тип_ограничения
Имеются следующие типы ограничений, накладываемых на столбец:
PRIMARY KEY – это ограничение требует, чтобы вводимые в стол-бец значения были уникальными и отличными от пустых, поскольку они будут использоваться в качестве первичного ключа, однозначно идентифицирующего запись; первичный ключ определяется для таблицы только единожды;
UNIQUE – это ограничение требует, чтобы вводимые в столбец зна-чения в рамках одной таблицы были уникальными;
NOT NULL – это ограничение требует обязательного присутствия в столбце некоторого значения;
CHECK(выражение) – это ограничение позволяет подвергнуть опре-деленной проверке вставляемое в столбец значение; если условия, нало-женные на вставляемые значения, не выполняются, то значения в стол-бец не помещаются;
REFERENCES – это ограничение позволяет установить взаимосвязь
значений данного столбца со значениями столбца другой таблицы. Взаи-мосвязь обеспечивается использованием следующей конструкции:
REFERENCES имя_таблицы[(имя_столбца)][ ON DELETE CASCADE]
При внесении значения в столбец создаваемой таблицы система будет автоматически проверять наличие аналогичного значения в указанном столбце указанной таблицы. При этом естественно, что для обеспечения однозначности устанавливаемой взаимосвязи все значения, находящиеся в указанном столбце, на которые производится ссылка, должны иметь ограничениеUNIQUE илиPRIMARY KEY. Если в качестве имени столбца указанной таблицы используется первичный ключ, то имя столбца можно не указывать. Таблица, на чей столбец ссылается другая таблица, называется главной, а таблица, ссылающаяся на нее, – подчи-ненной. КонструкцииON DELETE CASCADЕ указывает, что при удале-нии строк в главной таблице автоматически осуществляется удаление соответствующих строк и в подчиненной таблице.
Ограничение на таблицу во многом напоминают ограничения столб-ца, но при этом обычно задействуют, как правило, несколько столбцов.
Например, можно задать ограничениеPRIMARY KEY, указав список имен столбцов, тем самым, определив составной первичный ключ. При этом для столбцов, указываемых в списке, должны быть заданы ограни-ченияUNIQUE иNOT NULL.
Используя следующую форму записи:
FOREIGN KEY (список_имен_столбцов)
REFERENCES имя_таблицы(список_имен_столбцов)
[ON DELETE CASCADE]
можно определить составной внешний ключ для таблицы. Естественно,что в случае составного внешнего ключа перечень столбцов в подчиненной таблице и перечень столбцов в главной таблице должны совпадать по количеству, типу данных и порядку следования. КонструкцияON DELETE CASCADE позволяет обеспечить целостность и непротиворе-чивость данных при изменениях, которые затрагивают значения столб-цов главной таблицы, являющихся внешним ключом по отношению к подчиненной таблице.
Если ограничениеCHECK затрагивает значения нескольких столбцов, увязывая их в некоторое достаточно сложное условие, то такое ограни-чение также удобно определить как ограничение на таблицу.
Для генерации и вставки в столбец таблицы уникальных значений можно создать специально предназначенный для этих целей объект– по-следовательность. Создание последовательности выполняется с помо-щью оператораCREATE по следующему упрощенному синтаксису:
CREATE SEQUENCE имя_последовательности
[START WITH начальное_значение] [INCREMENT BY шаг];
В самом простейшем случае генерируется последовательность целых чисел от1 до10 с шагом единица. Конструкция INCREMENT BY по-зволяет указать шаг изменения значений последовательности. Конструк-цияSTART WITH позволяет задать начальное значение генерируемой последовательности, которое при ее отсутствии устанавливается равным единице. Для вставки в столбец текущего значения последовательности нужно указать имя_последовательности.СURRVAL, а для вставки в столбец измененного по правилам формирования последовательности следующего значения используется имя_последовательности.NEXTVAL.
Последовательности являются самостоятельными объектами базы дан-ных, одна и та же последовательность может быть использована для за-дания уникальных значений столбцов нескольких таблиц, при удалении или модификации последовательности значения, созданные ею, сохра-няются в таблицах базы данных.
Вставка строк в таблицу осуществляется с помощью оператора
Если список столбцов не указывается, то список значений в конст-рукцииVALUES должен содержать значения для всех столбцов таблицы, причем порядок их следования должен однозначно соответствовать по-рядку их следования в строке. Использование подзапроса позволяет пе-ренести строки из некоторой таблицы в создаваемую таблицу.
Удаление строк из таблицы осуществляется с помощью оператора DELETE, который имеет следующий синтаксис:
DELETE [FROM] имя_таблицы[WHERE условие];
При отсутствии ключевого словаWHERE из таблицы удаляются все строки, но сама таблица остается.
Модификация строк таблицы реализуется с помощью оператора UPDATE, форма записи которого приведена ниже. При отсутствии усло-вия модифицируются все строки таблицы для указанного списка столб-цов.
UPDATE имя_таблицы
SET {(имя_столбца1 [, имя_столбца2]...) = (подзапрос) |
Для изменения структуры таблицы используется операторALTER TABLE, с помощью которого можно осуществить добавление одного или несколько новых столбцов в таблицу, изменение характеристик у одного или нескольких столбцов, добавление ограничения столбца или таблицы или удаление ограничений столбца или таблицы. Примеры его использо-вания приведены в следующем пункте.
Удаление таблицы можно выполнить с помощью следующего опе-ратора:
DROP TABLE имя_таблицы[CASCADE CONSTRAINTS];
При наличии конструкцииCASCADE CONSTRAINTS вместе с уда-лением таблицы уничтожаются ограничения внешнего ключа в других таблицах.
Демонстрационные примеры
З а д а н и е«Книжный магазин».
Постоянно работающий магазин напрямую контактирует с издатель-ствами и имеет постоянный штат продавцов, постоянных и случайных покупателей. Постоянные покупатели регулярно оставляют заявки на книги. Директор магазина должен иметь сведения:
о поступивших книгах: название книги, фамилия автора, цена, изда-тельство, жанр;
о распределении книг среди продавцов: название книги, фамилия про-давца, количество экземпляров, дата поступления;
о продаже книг: название книги, фамилия продавца, фамилия поку-пателя, дата продажи.
1. Необходимо создать таблицы: KNIGA, KNIGA_POSTAVKA, указав все необходимые ограничения целостности данных.
Перечень, названия и тип данных столбцов таблицыKNIGA:
Информация таблицыKNIGA:
Перечень, название и тип данных столбцов таблицыKNIGA_POS-TAVKA:
Создание таблицыKNIGA выполняется с помощью оператораCREATE TABLE. Столбец КОД_КНИГИ, содержащий уни-кальный код книги, является ключевым, и по отношению к его значени-ям устанавливается ограничениеPRIMARY KEY. За значениями для это-го столбца пользователь должен следить сам. Ограничение именуется как PK_KN. Поскольку столбец НАЗВАНИЕ не может иметь пустые значе-ния, на него накладывается ограничениеNOT NULL. На значения столб-ца ЦЕНА накладывается ограничение, связанное со стоимостью книги, она должна быть не менее100 рублей. Ограничение получает имя CEN_KN. Если таблицаKNIGA была уже создана ранее, то перед по-вторным созданием ее следует удалить следующим оператором:
DROP TABLE KNIGA;
Следующий операторCREATE языкаSQL создает таблицуKNIGA с необходимыми ограничениями целостности данных:
в) Вводится строка со значением для столбца ЦЕНА меньше100 –
INSERT INTO KNIGA VALUES(1, 'Дюна', 'Герберт',68, 'Аст', 'Фантастика');
П о я с н е н и е. Создание таблицыKNIGA_POSTAVKA выполняется с помощью оператора CREATE TABLE. Столбец КОД_ОПЕРАЦИИ, со-держащий уникальный код операции, является ключевым и по отноше-нию к его значениям устанавливается ограничениеPRIMARY KEY. Это ограничение получает имяPOST_PR. Для генерации уникальных значе-ний этого столбца используется предварительно созданная с помощью оператораCREATE SEQUENCE последовательностьKOD_OP:
CREATE SEQUENCE KOD_OP;
Поскольку столбец КОД_КНИГИ должен содержать только те зна-чения, которые присутствуют в соответствующем столбце таблицы KNIGA, необходимо задать соответствующее ограничение на значения столбца КОД_КНИГИ. Это ограничение можно сделать как ограничени-ем столбца, так и ограничением таблицы, задав ему имяPOST_FK. Кон-струкцияON DELETE CASCADE обеспечит при удалении из таблицы KNIGA строк, содержащих значения внешнего ключа, автоматическое удаление строк и из таблицыKNIGA_POSTAVKA. Для столбца ДАТА_ПОСТУПЛЕНИЯ значением по умолчанию устанавливается те-кущая дата(SYSDATE). Если таблицаKNIGA_POSTAVKA была уже создана ранее, то перед повторным созданием ее следует удалить сле-дующим оператором:
DROP TABLE KNIGA_POSTAVKA;
Следующий оператор создает таблицуKNIGA_POSTAVKA с необхо-димыми ограничениями целостности данных:
Вставка строк в таблицуKNIGA_POSTAVKA осуществляется ис-пользованием следующей совокупности операторовINSERT:
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 1, 'Иванов',5, '20-JAN-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 2, 'Иванов',5, '20-JAN-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 3, 'Иванов',5, '10-FEB-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 4, 'Иванов',5, '10-FEB-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 5, 'Иванов',5, '10-FEB-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 6, 'Петров',4, '25-JAN-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 7, 'Петров',4, '25-JAN-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 8, 'Петров',4, '20-FEB-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 9, 'Петров',4, '20-FEB-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 10, 'Петров',4, '20-FEB-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 11, 'Сидоров',3, '25-JAN-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 12, 'Сидоров',3, '25-JAN-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 13, 'Сидоров',3, '25-JAN-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 7, 'Сидоров',3, '20-FEB-2004');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 4, 'Сидоров',3, '20-FEB-2004');
Просмотр введенных значений можно выполнить с помощью сле-дующего оператораSQL:
SELECT * FROM KNIGA_POSTAVKA;
З а м е ч а н и е. Пример строк, вызывающих нарушение ограничений целостности данных для таблицыKNIGA_POSTAVKA:
a) Вводится строка с повторением значения первичного ключа– 12:
INSERT INTO KNIGA_POSTAVKA VALUES(12, 12, 'Сидоров',3, '25-JAN-2004');
б) Вводится строка, в которой указан отсутствующий в таблице KNIGA код книги– 15:
INSERT INTO KNIGA_POSTAVKA VALUES(16, 15, 'Сидоров',3, '25-JAN-2004');
2. Создать новую таблицуKNIGA1, структура которой идентична структуре таблицыKNIGA, и перенести в нее строки с информацией о книгах жанра«Фантастика» и«Роман» из таблицыKNIGA.
Следующий набор операторовSQL создает таблицуKNIGA1 и пере-носит в нее строки из таблицыKNIGA:
Аналогичные действия выполняются следующим оператором:
CREATE TABLE KNIGA1 AS
SELECT КОД_КНИГИ, НАЗВАНИЕ, АВТОР, ЦЕНА,
ИЗДАТЕЛЬСТВО, ЖАНРFROM KNIGA WHERE
ЖАНР= 'Фантастика' OR ЖАНР= 'Роман';
Просмотр введенных значений можно выполнить с помощью сле-дующего оператораSQL:
SELECT * FROM KNIGA1;
3. Выполнить с помощью оператораUPDATE следующие изменения в таблицеKNIGA1:
а) Заменить в таблицеKNIGA1 в строке с фамилией автора«Кристи» в столбце АВТОР фамилию«Кристи» на«Агата Кристи» и в столбце ЖАНР жанр«Роман» на жанр«Детектив»;
б) Заменить в таблицеKNIGA1 цену книги«Гибель Титана» на цену книги«Казаки».
Следующий набор операторов выполнит указанные действия:
UPDATE KNIGA1 SET ЖАНР='Детектив', АВТОР='Агата Кристи' WHERE АВТОР='Кристи';
UPDATE KNIGA1 SET ЦЕНА= (SELECT ЦЕНАFROM KNIGA1 WHERE НАЗВАНИЕ='Казаки') WHERE НАЗВАНИЕ='Гибель Титана';
4. Удалить из таблицыKNIGA1 все строки, содержащие информацию о книгах в жанре«Роман». Используем следующий оператор:
DELETE KNIGA1 WHERE ЖАНР='Роман';
5. Добавить в таблицуKNIGA1 новый столбец ЦЕНА_В_У_Е с типом данныхNUMBER(7,2) и пересчитать цену книг в условных единицах.
Следующие операторы выполнят сначала добавление нового столбца в таблицу, а затем заполнят его соответствующими значениями:
ALTER TABLE KNIGA1 ADD ЦЕНА_В_У_ЕNUMBER(7,2);
UPDATE KNIGA1 SET ЦЕНА_В_У_Е=ЦЕНА/2155;
З а м е ч а н и е. При добавлении нескольких новых столбцов в табли-цу они заключаются в скобки:
ALTER TABLE KNIGA1 ADD (ГОД_ИЗДАНИЯCHAR(4),
МЕСТО_ИЗДАНИЯCHAR(15));
Для изменения характеристик столбца указывается ключевое слово MODIFY, имя столбца и новые характеристики:
ALTER TABLE KNIGA1 MODIFY (ГОД_ИЗДАНИЯNUMBER(4),
МЕСТО_ИЗДАНИЯCHAR(25));
Для добавления ограничения целостности данных необходимо ука-зать ключевое словоCONSTRAINT, имя ограничения и само ограниче-ние. При этом необходимо помнить, что добавляемое ограничение цело-стности данных не должно противоречить данным, находящимся в таб-лице.
ALTER TABLE KNIGA1 ADD CONSTRAINT ZZ CHECK(ЦЕНА>200);
Для временного отключения проверки ограничения целостности с именемZZ необходимо использовать команду ALTER TABLE KNIGA1 DISABLE CONSTRAINT ZZ;
Для включения проверки этого ограничения целостности можно ис-пользовать команду
ALTER TABLE KNIGA1 ENABLE CONSTRAINT ZZ;
Для удаления ограничения целостности с именемZZ необходимо ука-зать ключевые словаDROP иCONSTRAINT и имя удаляемого ограниче-ния
