 |
Раздел 10. Множественные операции
|
|
|
|
В языке SQL существуют несколько бинарных операций, которые позволяют оперировать с результирующими множествами нескольких независимыхSELECT-запросов. Такие операции, приведенные в таблице 4, называются множественными.
Таблица 4. Множественные операции
| UNION | слияние строк обоих результирующих множеств, при этом дубликаты строк удаляются |
| UNION ALL | слияние строк с сохранением дубликатов |
| INTERSECT | слияние строк при условии, что каждая строка присутствует в каждом объединяемом результирующем множестве |
| MINUS | в результирующем множестве остаются только строки, которые присутствуют в первой выборке, но отсутствуют во второй; эта операция несимметричная |
Рассмотрим несколько формальных примеров использования множественных операций:
SELECT result FROM CourseResult
UNION SELECT result FROM CourseResult (10.1)
| RESULT |
| - |
SELECT result FROM CourseResult
INTERSECT SELECT result FROM CourseResult (10.2)
| RESULT |
| - |
SELECT result FROM CourseResult
UNION ALL SELECT result FROM CourseResult (10.3)
| RESULT |
| - |
| - |
| - |
| - |
SELECT result FROM CourseResult
MINUS SELECT result FROM CourseResult (10.4)
no data found
При использовании множественных операций обе выборки должны иметь одинаковое количество полей и типы полей должны быть совместимыми (т.е. либо быть одного типа, либо допускать неявное преобразование). Рекомендуется использовать явное преобразование, если сливаются данные из разных таблиц, как в следующем примере:
SELECT name, 'an academic staff' "status", position FROM staff
UNION
SELECT name, 'a student ' "status", TO_CHAR(class) || ' year'
FROM student
ORDER BY "status", 3 (10.5)
| NAME | status | POSITION |
| S.Abiteboul | an academic staff | Full Professor |
| Won Kim | an academic staff | Full Professor |
| G.Anderson | an academic staff | Senior Lector |
| Gill | a student | 1 year |
| Polie | a student | 2 year |
| Bill | a student | 3 year |
| Jane | a student | 3 year |
|
|
|
Поскольку поле class имеет тип INT, перед объединением необходимо преобразовать это значение в тип CHAR для соответствия с типом значений поля position из первого SELECT-запроса.
В этом запросе 10.5 применяется упорядочивание результирующего множества после выполнения операции UNION. Используемая здесь конструкция ORDER BY относится не к последнему SELECT-запросу, а к значению UNION-операции; использование этой конструкции в первом SELECT-запросе запрещено правилами языка. Идентификатор "status" введен в обеих выборках для того, чтобы это поле в итоговом результирующем множестве получило именно это имя (по умолчанию при множественных операциях это поле получило бы имя поля первой выборки, т.е. “an academic staff”, что не очень удачно). Упорядочивание проводится по второму и третьему полям; вместо идентификатора третьего поля используется его порядковый номер, что разрешается в SQL Oracle.
Следующие два примера иллюстрируют различие группировки выборок до (10.7) и после (10.6) применения множественных операций.
SELECT result, COUNT(*) FROM CourseResult GROUP BY result
UNION ALL
SELECT result, COUNT(*) FROM CourseResult
GROUP BY result (10.6)
| RESULT | COUNT(*) |
| - | |
| - | |
SELECT result, COUNT(*) FROM
(SELECT result FROM CourseResult
UNION ALL
SELECT result FROM CourseResult)
GROUP BY result (10.7)
| RESULT | COUNT(*) |
| - | |
Наконец, ниже приведен еще один вариант вычисления антисоединения, эквивалентного запросу 7.15, реализованный при помощи операции MINUS. Впрочем, подобный вариант является наименее эффективным и представляет скорее формальный, чем практический интерес.
SELECT DISTINCT title "Курс", staff "Преподавателя"
FROM Attends RIGHT JOIN Course USING (title)
MINUS
SELECT DISTINCT title "Курс", staff "Преподавателя"
FROM Attends JOIN Course USING (title) (10.8)
Раздел 11. Команды манипулирования данными
Команды манипулирования данными предназначены для добавления новых записей, удаления записей и изменения существующих записей в SQL-таблицах.
|
|
|
Добавление записей
Команда INSERT предназначена для добавления записей в существующую таблицу. В тексте команды указывается имя таблицы, в которую будет добавлена одна или несколько строк, и значения, которые будут присвоены полям созданный строк.
Ниже приведена схема базы данных, которая будет использоваться в последующих примерах. Данные этих таблиц аналогичны данным из таблицы acceptedorders, использованной в разделе 2, однако использует более правильный вариант структурирования данных – сведения о клиентах и товарах теперь выделены в отдельные таблицы.
CREATE TABLE Customer -- таблица клиентов
(name CHAR(40), -- имя клиента
address CHAR(50), -- его адрес
gender CHAR(1)); -- пол
CREATE TABLE Item -- таблица товаров
(item CHAR(40), -- название товара
description CHAR(1000), -- детальное описание
price NUMBER); -- цена единицы товара
CREATE TABLE "Order" -- заказы товаров
(customer CHAR(40), -- имя клиента
orderdate DATE, -- дата заказа
item CHAR(40), -- название товара
quantity INTEGER, -- количество единиц в заказе
totalsum NUMBER, -- общая цена заказа
shipping_address CHAR(50), -- адрес для доставки
shipping_date DATE); -- дата доставки заказа
Простейший вариант команды INSERT приведен ниже:
INSERT INTO Customer
VALUES ('Mr.Pundleberry', 'Green str. 2', 'F'); (11.1)
При исполнении данной команды в таблицу Customer будет добавлена строка, полям которой будут присвоены указанные значения вычисленных выражений. Типы, количество и порядок значений должны соответствовать определению таблицы. Таким образом, первому полю новой строки будет присвоено значение первого выражения из списка и т.д. В качестве выражений можно использовать NULL и DEFAULT, в первом случае значению поля будет присвоено пустое значение, а во втором – так называемое значение по умолчанию (подробнее это будет описано во 2 части издания).
INSERT INTO Item VALUES ('DVD Disk', NULL, 6.35); (11.2)
INSERT INTO Item VALUES ('Book', DEFAULT, 12.50); (11.3)
Здесь в обоих случаях в созданных записях полю description будет присвоено значение NULL; во втором случае – поскольку значение NULL является значением по умолчанию, если явно не указано другое.
Следует иметь в виду, что не все команды манипулирования данными, в том числе и команда INSERT, являясь синтаксически корректными, могут быть успешно выполнены. Это связано с тем, в том числе, что при определении SQL-таблиц обычно определяются различные ограничения на значения, которые могут храниться в полях, и которые, следовательно, ограничивают возможные модификации данных.
|
|
|
При добавлении новой строки значения можно присваивать не всем полям, как, например, в следующей команде:
INSERT INTO Item (item, price) VALUES ('Watch', 199.99)(11.4)
В этом варианте команды INSERT в скобках перечисляются имена полей, которым должны быть присвоены значения; при этом порядок выражений должен соответствовать порядку указанных полей.
Теперь рассмотрим внимательнее структуру таблицы "Order". Обратите внимание, что в ней, в отличие от таблицы acceptedorders, теперь хранятся: поле totalsum, значение которой вычисляется как произведение цены единицы товара и количества заказанных единиц и поле shipping_address (адрес доставки), значение которого дублируется из поля address таблицы Customer. Например, если необходимо добавить строку, соответствующую данным «'Mr.O''Raily' заказал '11-08-2008' товар 'DVD Disk' в количестве две штуки», то одним из вариантов команды для добавления новой записи в таблицу "Order" является следующий:
INSERT INTO "Order"
VALUES ('Mr.O''Raily', TO_DATE('11-08-2008', 'DD-MM-YYYY'),
'DVD Disk', 2, 6.35 * 2, 'Docks', NULL); (11.5)
Однако у этого варианта есть следующие недостатки. Во-первых, необходимо знать цену товара, которая хранится в таблице Item в строке, соответствующей товару 'DVD Disk', и подставить это значение в текст запроса в выражение для вычисления общей стоимости заказа. Во-вторых, необходимо получить значение адреса для доставки, которая хранится в таблице Customer, и так же подставить это значение в INSERT-команду.
Для устранения, в том числе, подобных неудобств, можно использовать вариант команды INSERT с подзапросом. Например, эквивалентная для команды 11.5 будет следующий вариант:
INSERT INTO "Order"
SELECT C.name, TO_DATE('11-08-2008', 'DD-MM-YYYY'), I.item, 2, 2*I.price, C.address FROM Customer C,
Item I, NULL
WHERE C.name = 'Mr.O''Raily' AND I.item = 'DVD Disk'; (11.6)
Здесь вместо конструкции VALUE указан текст SELECT-запроса, который исполняется, и сформированное результирующее множество построчно добавляется в таблицу "Order"; при этом, естественно, поля выборки подзапроса должны соответствовать полям таблицы, в которую строки добавляются. Если выборка подзапроса – пустая, то и строки добавлены не будут. Обычно интерактивный интерпретатор команд языка SQL после выполнения команды INSERT и других команд манипулирования данными печатает сообщение о количестве добавленных, удаленных или измененных строк таблицы.
|
|
|
Следует обратить внимание, что SELECT-запрос в последнем примере являются двухтабличным и с формальной точки зрения является запросом с соединением (точнее говоря, кросс-соединением). Однако никакой взаимосвязи между записями обеих таблиц не существует и не подразумевается, что, в том числе, следует из текста запроса – отсутствует условие эквисоединения. Этот пример иллюстрирует «механическое» использование двухтабличного запроса с целью извлечения данных из двух различных таблиц. Однако при работе с подобными запросами необходимо контролировать, сколько в действительности записей окажется в результирующем множестве. В нашем примере запись одна, и это следует из соглашения, что в таблицах Customer и Item должно быть в точности по одной записи для каждого клиента и товара соответственно.
Команды INSERT с подзапросами удобно использовать для заполнения данными новых таблиц на основе существующих строк других таблиц. Типичным примером является следующая команда, которая копирует в таблицу CourseResult имена студентов и названия посещаемых курсов, оставляя поле result незаполненным.
INSERT INTO CourseResult
SELECT student, title, NULL FROM Attends (11.7)
Удаление строк
Команда DELETE предназначена для удаления одной или нескольких строк из SQL-таблицы. Ниже приведен простейший вариант использования этой команды:
DELETE FROM Courseresult (11.8)
Служебное слово FROM является необязательным.
Исполнение данной команды приведет к удалению всех строк в указанной таблице, поэтому исполнять эту команду необходимо с осторожностью, так как восстановление удаленных записей возможно не во всех случаях и может потребовать применения соответствующих административно-технических средств. Кроме того, в некоторых случаях команды DELETE не будут выполнены из-за наложенных ограничений.
Если действительно необходимо удалить все строки таблицы, рекомендуется использовать команду TRUNCATE, которая выполняется более эффективно:
TRUNCATE TABLE Courseresult (11.9)
Команда DELETE обычно используется в комбинации с конструкцией WHERE для спецификации условия селекции удаляемых записей, это логическое условие в основном аналогично логическим условиям, используемым в SELECT-запросах, и может включать, в том числе, коррелированные подзапросы. Например, команда 11.10 удаляет сведения о заказах из таблицы "Order", с более ранней датой, чем '12-08-2008'.
|
|
|
DELETE "Order" WHERE orderdate <
TO_DATE('12-08-2008', 'DD-MM-YYYY') (11.10)
В следующем примере 11.11 команда удаляет сведения о посещаемых студентами курсах, если больше нет либо такого студента, либо такого курса:
DELETE Attends A WHERE
NOT EXISTS (SELECT * FROM Student S
WHERE S.name = A.student)
OR A.title NOT IN (SELECT title FROM course) (11.11)
Обратите внимание, что в командах DELETE также можно использовать синонимы таблиц (Attends A).
Изменение строк
Команда UPDATE предназначена для изменения набора строк SQL таблицы. Текст UPDATE-команды содержит имя таблицы, набор выражений, которые определяют новые значения изменяемых полей, и логическое условие, которому должны удовлетворять данные каждой строки перед ее изменением; логическое условие может отсутствовать, в этом случае изменения затронут все строки таблицы.
Следующий пример изменяет значения поля result таблицы Courseresult, устанавливая в каждой записи значение NULL:
UPDATE CourseResult SET result = NULL (11.12)
В следующей команде изменения ограничивается только теми строками, которые удовлетворяют специфицированному условию; в данном случае удаляется общая сумма для неотправленных заказов.
UPDATE "Order" O SET totalsum = NULL
WHERE SHIPPING_DATE IS NULL (11.13)
В одной UPDATE-команде можно изменять значения нескольких полей, причем можно изменять значения полей, по которым выполняется селекция строк. В присваиваемых выражениях можно использовать подзапросы, которые, однако, должны возвращать одну строку с атомарным значением. Например, следующая команда вычисляет для незавершенных заказов новые значение общей стоимости заказа, исходя из цены за единицу, извлекаемую с помощью коррелированного подзапроса из таблицы с описанием товаров, и устанавливает дату отгрузки заказа текущей датой, которая также определяется через подзапрос, реализующий вызов системной функции CURRENT_DATE.
UPDATE "Order" O SET totalsum = quantity *
(SELECT price FROM Item WHERE item = O.item),
SHIPPING_DATE =
(SELECT CURRENT_DATE FROM DUAL)
WHERE SHIPPING_DATE IS NULL (11.14)
Впрочем, команду 11.14 можно переписать и без использования второго подзапроса:
UPDATE "Order" O SET totalsum = quantity *
(SELECT price FROM Item WHERE item = O.item),
SHIPPING_DATE = CURRENT_DATE
WHERE SHIPPING_DATE IS NULL (11.15)
В выражениях присваивания можно использовать и оператор CASE, который полезен для изменения значений полей строк в зависимости от выполнения условий. Например, следующая команда устанавливает дату отгрузки заказа либо такую же, как дату приема заказа, либо последним днем месяца, в зависимости от даты оформления заказа.
UPDATE "Order" SET SHIPPING_DATE =
(CASE WHEN orderdata <
TO_DATE('15-08-2008', 'DD-MM-YYYY')
THEN orderdata
ELSE LAST_DAY(CURRENT_DATE)
END) (11.16)
Заключение
В данном издании изложены синтаксис и основные функциональные возможности языка баз данных SQL в системе Oracle SQL Server и способы их практического применения. Представленный в нем материал полностью соответствует материалу, изучаемому в курсе «Системы управления базами данных» студентами четвертого курса специальности 080115 «Таможенное дело» специализации «Информационные таможенные технологии» и включает достаточное количество дополнительного материала, который может быть адресован студентам для самостоятельного изучения. Данное издание также может использоваться в качестве краткого справочника по системе Oracle при практической работе.
Некоторые функциональные возможности языка Oracle SQL остались за рамками данной работы, например, детальное описание встроенных функций, и некоторые другие аспекты, которые существенно выходят за рамки изучаемого курса. Для ознакомления с ними рекомендуется обращаться к системной документации и приводимой дополнительной литературе.
В издание не вошло описание команд создания объектов баз данных (таблиц, индексов, триггеров), описание процедурного расширения PL/SQL, команд управления транзакциями, и некоторых других. Эти команды будут изложены во второй части.
Библиографический список
1. Андон Ф., Резниченко В. Язык запросов SQL: учебный курс. – СПб.: Питер, 2006.
2. Малыхина М.П. Базы данных: основы, проектирование, использование. – СПб.: БХВ-Петербург, 2006.
3. Моисеенко С. SQL. Задачи и решения. – СПб.: Питер, 2006.
4. Гектор Гарсия-Молина, Дж.Ульман, Дж.Уидом. Системы баз данных. – М.: Вильямс, 2002.
5. Oracle11 Server SQL Reference Manual. Oracle Corp. 2006.
6. Дж. Перри, Дж. Пост. Введение в Oracle 10 g. –М.: Вильямс, 2006.
Указатели
Команды
| CREATE TABLE | |
| DELETE | |
| DESCR | |
| INSERT | |
| SELECT | |
| TRUNCATE | |
| UPDATE |
Конструкции
| AS | |
| FROM | |
| HAVING | |
| GROUP BY | |
| ORDER BY | |
| WHERE |
Логические предикаты
| ALL | |
| ANY | |
| BETWEEN | |
| EXISTS | |
| IN | |
| IS NULL, IS NOT NULL | |
| LIKE |
Множественные операции
| INTERSECT | |
| MINUS | |
| UNION | |
| UNION ALL |
| Оператор CASE |
Операции соединения
| CROSS JOIN | |
| FULL JOIN | |
| INNER JOIN | |
| JOIN USING | |
| LEFT JOIN, RIGHT JOIN | |
| NATURAL JOIN |
Функции
| CAST | |
| CEIL | |
| COS | |
| COUNT | |
| DECODE | |
| FLOOR | |
| INITCAP | |
| LAST_DAY | |
| LENGTH | |
| LOWER | |
| LPAD | |
| LTRIM | |
| REPLACE | |
| RPAD | |
| RTRIM | |
| SIN | |
| SUBSTR | |
| SYSDATE | |
| TO_CHAR | |
| TO_DATE | |
| TO_NUMBER | |
| TRUNC | |
| UPPER |
Язык баз данных Oracle SQL
Запросы и команды манипулирования данными. Ч.1.
материалы к лекциям
Автор
Дмитрий Александрович Заставной
Сдано в производство 17.12.2008. Подписано в печать 18.12.2008.
Формат 60х84/16. Ксерокопия. Таймс. Усл.п.л.4,8.
Тираж 30 экз. Заказ №2009.
РИО Ростовского филиала РТА,
344002 г. Ростов-на-Дону, пр. Буденновский, 20.
|
|
|
