Рассмотрим наиболее часто используемые группы функций языка SQL.
Числовые функции.Предназначены для вычисления степени числа, абсолютного значения, округления и усечения числа с заданной точностью, вычисления тригонометрических значений. Опишем некоторые числовые функции.
Функция ABS(n) возвращает абсолютное значение аргумента n, имеющего числовой тип.
Функция ROUND(n, [r]) осуществляет округление значения аргумента n, имеющего числовой тип, с точностью до количества указанных знаков r. При этом если значение r положительно, то округление производится до указанного количества знаков после запятой, если значение r отрицательно, то округление производится до указанного количества знаков до запятой. При r = 0 функция возвращает округленную целую часть аргумента n.
Функция MOD(m, n) возвращает остаток от деления целочисленного аргумента m на целочисленный аргумент n.
Функция POWER(m, n) возвращает аргумент m, имеющий числовой тип, возведенный в степень, заданную аргументом n, имеющим также числовой тип.
Функция SQRT(m) возвращает квадратный корень из аргумента n, имеющего числовой тип.
Символьные функции. Предназначены для работы со строками. Они могут возвращать либо строку, либо целое значение.Ниже приводится описание некоторых символьных функций.
Функция UPPER(str) возвращает строку str, все символы которой преобразованы в верхний регистр.
Функция LENGTH(str) возвращает длину строки str в символах.
Функция SUBSTR(str, n, m) выделяет из строки str подстроку длины n, начиная с символа в позиции m.
Функция LPAD(str, n, chr) возвращает строку str, дополненную слева указанным символом chr до указанной длины n.
Функция RPAD(str, n, chr) возвращает строку str, дополненную справа указанным символом chr до указанной длины n.
Функции работы с датами. Предназначены для работы с данными типа DATE. Ниже описываются некоторые функции этой группы.
Функция ADD_MONTHS(data, n) добавляет к указанной дате data или вычитает из нее n месяцев.
Функция MONTHS_BETWEEN(data1, data2) возвращает количество месяцев, находящихся между указанными датами data1 и data2.
Функция LAST_DAY(data) возвращает последний день месяца, указанного датой data.
Функции преобразования типа. В основном используются для преобразования данных символьного типа в числовой или в тип DATE и, наоборот, для преобразования данных числового типа или типа DATE в символьный тип. Преобразование осуществляется в соответствии с задаваемым форматом. Формат преобразования имеет вид символьной строки, где каждый символ или группа символов имеет определенное назначение.
Функция TO_CHAR(d1, [fmt]) преобразует значение d1 типа DATE в значение типа VARCHAR2 по формату fmt.
Функция TO_CHAR(n1, [fmt]) преобразует значение n1 типа NUMBER в значение типа VARCHAR2 по формату fmt.
Функция TO_DATE(char, [fmt]) преобразует значение char типа CHAR или VARCHAR2 в значение типа DATE по формату fmt.
Функция TO_NUMBER(char, [fmt]) преобразует значение char типа CHAR или VARCHAR2 в значение типа NUMBER по формату fmt.
В форматах для даты используются следующие группы символов:
DD – задает номер дня месяца в диапазоне от 1 до 31;
DAY – задает полное название дня недели;
MON – задает краткое название месяца;
MONTH – задает полное название месяца;
YY – задает две последние цифры номера календарного года;
YYYY – задает полный номер календарного года.
В форматах для чисел используются следующие символы:
цифра 9 – задает цифру;
символ точка (.) – задает десятичную точку;
цифра 0 – задает обязательный ноль;
буква s – задает обязательное наличие знака {+; –};
символ $ – задает знак доллара, проставляемый в начале числа.
Групповые функции.Выполняют операции над группами строк.
Функция COUNT({*}) – возвращает количество строк в группе.
Функция COUNT([DISTINCT] выражение) – возвращает количество строк в группе, игнорируя значение NULL.
Функция SUM([DISTINCT] выражение) – возвращает сумму значений указанного выражения для группы строк или списка значений, игнорируя значение NULL.
Функция AVG([DISTINCT] выражение) – возвращает среднее значение указанного выражения для группы строк или списка значений, игнорируя значение NULL.
Функция MIN([DISTINCT] выражение) – возвращает минимальное из значений указанного выражения для группы строк или списка значений, игнорируя значение NULL.
Функция MAX([DISTINCT] выражение) – возвращает максимальное из значений указанного выражения для группы строк или списка значений, игнорируя значение NULL.
Фраза DISTINCT предписывает групповым функциям рассматривать только различные значения выражения.
Другие функции.Функция NVL(выражение1, выражение2) обрабатывает пустое значение NULL. Если значение выражения1 равно NULL, то функция возвращает значение выражения2; если же значение выражения1 не равно NULL, то функция возвращает значение выражения1.
12.6. Создание, модификация и удаление таблиц.
Рассмотрим основные функции, выполняемые над таблицами.
Создание таблицы выполняется с помощью оператора CREATE, упрощенный синтаксис которого имеет следующий вид:
Таблица может быть создана либо стандартным образом через описание ее компонент, либо в результате выполнения некоторого подзапроса. Подзапрос – это обычный запрос на выборку информации, реализуемый оператором 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 до 1027 с шагом единица. Конструкция INCREMENT BY позволяет указать шаг изменения значений последовательности. Конструкция START WITH позволяет задать начальное значение генерируемой последовательности, которое при ее отсутствии устанавливается равным единице. Для вставки в столбец текущего значения последовательности нужно указать имя_последовательности.СURRVAL, а для вставки в столбец измененного по правилам формирования последовательности следующего значения используется имя_последовательности.NEXTVAL. Последовательности являются самостоятельными объектами базы данных, одна и та же последовательность может быть использована для задания уникальных значений столбцов нескольких таблиц, при удалении или модификации последовательности значения, созданные ею, сохраняются в таблицах базы данных.
Вставка строк в таблицу осуществляется с помощью оператора INSERT, который имеет следующий синтаксис:
Если список столбцов не указывается, то список значений в конструкции 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 воспользуемся оператором CREATE TABLE. Столбец КОД_КНИГИ, содержащий уникальный код книги, является ключевым, и по отношению к его значениям устанавливаем ограничение PRIMARY KEY. За значениями для этого столбца пользователь должен следить сам. Ограничение именуется как PK_KN. Поскольку столбец НАЗВАНИЕ не может иметь пустые значения, на него накладываем ограничение NOT NULL. На значения столбца ЦЕНА накладываем ограничение, связанное со стоимостью книги, она должна быть не менее 100 рублей. Ограничение получает имя CEN_KN. Если таблица KNIGA была уже создана ранее, то перед повторным созданием ее следует удалить следующим оператором:
DROP TABLE KNIGA;
Следующий оператор CREATE языка SQL создает таблицу KNIGA с необходимыми ограничениями целостности данных:
CREATE TABLE KNIGA (
КОД_КНИГИ NUMBER(5) CONSTRAINT PK_KN PRIMARY KEY,
НАЗВАНИЕ VARCHAR2(25) NOT NULL, АВТОР VARCHAR2(20),
ЦЕНА NUMBER(7) CONSTRAINT CEN_KN CHECK(ЦЕНА>100),
ИЗДАТЕЛЬСТВО VARCHAR2(15), ЖАНР VARCHAR2(15));
Вставка строк в таблицу KNIGA осуществляется следующей совокупностью операторов:
INSERT INTO KNIGA VALUES(3,'Гибель Богов','Перумов Н.', 345, 'Аст', 'Фантастика');
INSERT INTO KNIGA VALUES(5,'Казаки','Толстой Л.', 5568, 'Нова', 'Роман');
INSERT INTO KNIGA VALUES(8,'Ярость','Перумов Н.',1385,'Аст', 'Детектив');
INSERT INTO KNIGA VALUES(11,'Дюна', 'Герберт Ф.', 2668, 'Нова', 'Фантастика');
INSERT INTO KNIGA VALUES(13,'Гибель Титана', 'Кристи А.', 2345, 'Аст', 'Роман');
Просмотр введенных значений можно выполнить с помощью следующего оператора SQL:
SELECT * FROM KNIGA;
Для создания таблицы 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 с необходимыми ограничениями целостности данных:
CREATE TABLE KNIGA_POSTAVKA (
КОД_ОПЕРАЦИИ NUMBER(10)
CONSTRAINT POST_PR PRIMARY KEY,
КОД_КНИГИ NUMBER(5),
ПРОДАВЕЦ VARCHAR2(20),
КОЛ_ЕДИНИЦ NUMBER(4),
ДАТА_ПОСТУПЛЕНИЯ DATE DEFAULT SYSDATE,
CONSTRAINT POST_FK FOREIGN KEY(КОД_КНИГИ)
REFERENCES KNIGA(КОД_КНИГИ) ON DELETE CASCADE);
Вставка строк в таблицу KNIGA_POSTAVKA осуществляется использованием следующей совокупности операторов INSERT:
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 1, 'Иванов', 5, '20-JAN-2006');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 2, 'Иванов', 5, '20-JAN-2006');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 6, 'Петров', 4, '25-JAN-2006');
INSERT INTO KNIGA_POSTAVKA VALUES(KOD_OP.NEXTVAL, 7, 'Петров', 4, '25-JAN-2006');
Просмотр введенных значений можно выполнить с помощью следующего оператора SQL:
SELECT * FROM KNIGA_POSTAVKA;
2. Создать таблицу KNIGA1, структура которой идентична структуре таблицы KNIGA, и перенести в нее строки с информацией о книгах жанра “Фантастика” и “Роман” из таблицы KNIGA.
Следующий набор операторов SQL создает таблицу KNIGA1 и переносит в нее строки из таблицы KNIGA:
INSERT INTO KNIGA1 SELECT КОД_КНИГИ, НАЗВАНИЕ, АВТОР, ЦЕНА, ИЗДАТЕЛЬСТВО, ЖАНР FROM KNIGA WHERE
ЖАНР = 'Фантастика' OR ЖАНР = 'Роман';
Аналогичные действия выполняются следующим оператором:
CREATE TABLE KNIGA1 AS
SELECT КОД_КНИГИ, НАЗВАНИЕ, АВТОР, ЦЕНА,
ИЗДАТЕЛЬСТВО, ЖАНР FROM KNIGA WHERE
ЖАНР = 'Фантастика' OR ЖАНР = 'Роман';
Просмотр введенных значений можно выполнить с помощью следующего оператора SQL:
SELECT * FROM KNIGA1;
3. Выполнить с помощью оператора UPDATE следующие изменения в таблице KNIGA:
а) Заменить в таблице KNIGA в строке с фамилией автора “Кристи” в столбце АВТОР фамилию “Кристи” на “Агата Кристи” и в столбце ЖАНР жанр “Роман” на жанр “Детектив”;
б) Заменить в таблице KNIGA цену книги “Гибель Титана” на цену книги “Казаки”.
Следующий набор операторов выполнит указанные действия:
UPDATE KNIGA SET ЖАНР='Детектив', АВТОР='Агата Кристи'
WHERE АВТОР='Кристи';
UPDATE KNIGA SET ЦЕНА=
(SELECT ЦЕНА FROM KNIGA1 WHERE НАЗВАНИЕ='Казаки')
WHERE НАЗВАНИЕ='Гибель Титана';
4. Удалить из таблицы KNIGA все строки, содержащие информацию о книгах в жанре “Роман”. Используем следующий оператор:
DELETE KNIGA WHERE ЖАНР='Роман';
5. Добавить в таблицу KNIGA новый столбец ЦЕНА_В_У_Е с типом данных NUMBER(7,2) и пересчитать цену книг в условных единицах. Следующие операторы выполнят сначала добавление нового столбца в таблицу, а затем заполнят его соответствующими значениями:
ALTER TABLE KNIGA ADD ЦЕНА_В_У_Е NUMBER(7,2);
UPDATE KNIGA SET ЦЕНА_В_У_Е=ЦЕНА/2155;
Отметим, что при добавлении нескольких новых столбцов в таблицу они заключаются в скобки:
ALTER TABLE KNIGA ADD (ГОД_ИЗДАНИЯ CHAR(4), МЕСТО_ИЗДАНИЯ CHAR(15));
Для изменения характеристик столбца указывается ключевое слово MODIFY, имя столбца и новые характеристики:
ALTER TABLE KNIGA MODIFY (ГОД_ИЗДАНИЯ NUMBER(4),
МЕСТО_ИЗДАНИЯ CHAR(25));
Для добавления ограничения целостности данных необходимо указать ключевое слово CONSTRAINT, имя ограничения и само ограничение. При этом необходимо помнить, что добавляемое ограничение целостности данных не должно противоречить данным, находящимся в таблице.
ALTER TABLE KNIGA ADD CONSTRAINT ZZ CHECK(ЦЕНА>200);
Для временного отключения проверки ограничения целостности с именем ZZ необходимо использовать команду
ALTER TABLE KNIGA DISABLE CONSTRAINT ZZ;
Для включения проверки этого ограничения целостности можно использовать команду
ALTER TABLE KNIGA ENABLE CONSTRAINT ZZ;
Для удаления ограничения целостности с именем ZZ необходимо указать ключевые слова DROP и CONSTRAINT и имя удаляемого ограничения
