Перенос всех строк из одной таблицы в другую

Много проще, чем импортировать внешние данные, извлекать данные, уже находящиеся водной из таблиц вашей базы, и комбинировать их с данными из другой таблицы. В самом простом случае структура второй таблицы идентична структуре первой. Это означает, что каждый столбец первой таблицы имеет соответствующий столбец во второй, а типы данных соответствующих столбцов совпадают. В таком случае содержимое двух таблиц можно комбинировать с помощью реляционного оператора UNION (объединение). В результате получается виртуальная таблица, в которой содержатся данные исходных таблиц. О реляционных операторах, в том числе о UNION, рассказывается в главе 10.

Перенос выбранных столбцов и строк из одной таблицы в другую

Часто бывает так, что данные исходной таблицы не соответствуют в точности структуре той таблицы, в которую вы собираетесь их поместить. Возможно, соответствуют друг другу только некоторые из столбцов – и это как раз те столбцы, которые вы хотите перенести. Комбинируя операторы SELECT с помощью оператора UNION, можно указать, какие столбцы из исходных таблиц должны войти в полученную в результате виртуальную таблицу. Используя в операторах SELECT предложения WHERE, можно помещать в виртуальную таблицу только те строки, которые удовлетворяют определенным условиям. Предложения WHERE достаточно подробно описываются в главе 9.

Предположим, у вас имеются две таблицы, PROSPECT (потенциальный клиент) и CUSTOMER (покупатель), и вам нужно составить список всех жителей штата Мэн, данные о которых находятся в обеих таблицах. Тогда можете создать виртуальную таблицу с нужной информацией, используя следующую команду:

SELECT FirstName, LastNameFROM PROSPECTWHERE State = 'ME'UNIONSELECT FirstName, LastNameFROM CUSTOMERWHERE State = 'ME'

В этом коде заключено следующее:

· Операторы SELECT говорят о том, что у созданной таблицы будут столбцы FirstName (имя) и LastName (фамилия).

· Предложения WHERE ограничивают количество строк в этой таблице, выбирая лишь те, у которых в столбце State (штат) находится значение 'ME' (штат Мэн).

· Столбца State в созданной таблице не будет, но он находится в двух исходных таблицах: в PROSPECT и CUSTOMER.

· Оператор UNION объединяет результаты, полученные при выполнении SELECT, отдельно с PROSPECT и отдельно с CUSTOMER, удаляет все дублированные строки, а затем выводит окончательный результат на экран.

Другой способ копировать данные в базе из одной ее таблицы в другую состоит в том, чтобы разместить оператор SELECT в операторе INSERT. Такой метод (подвыборка) виртуальной таблицы не создает, а просто дублирует выбранные данные. Например, вы можете взять все строки из таблицы CUSTOMER и вставить их в таблицу PROSPECT. Конечно, эта операция удастся только в том случае, если у обеих этих таблиц одинаковая структура. Далее, если нужно отобрать только тех покупателей, которые живут в штате Мэн, то достаточно простого оператора SELECT, имеющего в предложении WHERE всего лишь одно условие. Соответствующий код показан в следующем примере:

INSERT INTO PROSPECTSELECT * FROM CUSTOMERWHERE State = 'ME';

Внимание:
Даже если эта операция и создает избыточные данные – данные о покупателях теперь хранятся в обеих таблицах, в PROSPECT и CUSTOMER, – но зато увеличивается производительность выборок. Чтобы избежать избыточности и поддерживать согласованность данных, делайте так, чтобы строки в одной таблице не вставлялись, не изменялись и не удалялись без вставки, изменения и удаления соответствующих строк в другой таблице. Может возникнуть еще одна проблема. Возможно, что оператор INCERT продублирует первичные ключи. Если существует один-единственный потенциальный клиент, имеющий ключ ProspectID, который совпадает с соответствующим первичным ключом CustomerlD покупателя, введенного в таблицу PROSPECT, тогда операция вставки будет неудачной.

Обновление имеющихся данных

Все течет, все изменяется. Если вам не нравится нынешнее положение дел, то надо немного подождать. Через некоторое время существующее положение изменится.

И так как мир постоянно преобразуется, то надо также обновлять и базы данных, с помощью которых моделируются его компоненты. Покупатель, возможно, поменял адрес. Может измениться количество какого-либо товара на складе, будем надеяться не в результате воровства, а потому что товар хорошо расходится. Это типичные примеры тех событий, из-за которых приходится обновлять базу данных.

В языке SQL для изменения данных, хранящихся в таблице, имеется оператор UPDATE (обновить). С помощью одного такого оператора можно изменить в таблице одну строку, несколько или все ее строки. В операторе UPDATE используется следующий синтаксис:

UPDATE имя_таблицы

SET столбец_1 – выражение_1, столбец_2 = выражение_2,

…, столбец_n = выражение__n

[WHERE предикаты];

Предложение WHERE (где) не является обязательным. Оно указывает, какие строки должны обновляться. Если это предложение не используется, то будут обновляться все строки таблицы. В свою очередь предложение SET (установить) указывает новые значения изменяемых столбцов.

Проанализируйте с помощью табл. 6.1 таблицу CUSTOMER (покупатель), имеющую столбцы Name (имя и фамилия), City (город), Area-Code (телефонный код региона) и Telephone (телефон).

Таблица 6.1. Таблица CUSTOMER.

Name	City	AreaCode	Telephone
Abe Abelson	Springfield	(714)	555-1111
Bill Bailey	Decatur	(714)	555-2222
Chuck Wood	Philo	(714)	555-3333
Don Stetson	Philo	(714)	555-4444
Dolph Stetson	Philo	(714)	555-5555

Время от времени списки покупателей изменяются, по мере того как эти люди переезжают, изменяются номера телефонов и т.д. Предположим, что Эйб Эйбелсон (Abe Abelson) переехал из Спрингфилда в Канкаки. Тогда запись этого покупателя, находящуюся в таблице CUSTOMER, можно обновить с помощью следующего оператора UPDATE:

UPDATE CUSTOMER

SET City = 'Kankakee1, Telephone = '666-6666'

WHERE Name = 'Abe Abelson1;

В результате его выполнения в записи произошли изменения, которые показаны в табл. 6.2.

Таблица 6.2. Таблица CUSTOMER после обновления одной строки оператором update.

Name	City	AreaCode	Telephone
Abe Abelson	Kankakee	(714)	666-6666
Bill Bailey	Decatur	(714)	555-2222
Chuck Wood	Philo	(714)	555-3333
Don Stetson	Philo	(714)	555-4444
Dolph Stetson	Philo	(714)	555-5555

Похожий оператор можно использовать, чтобы обновить сразу множество строк. Предположим, что город Файло переживает резкий рост населения и ему теперь требуется собственный телефонный код региона. Все строки покупателей, проживающих в этом городе, можно сразу изменить с помощью одного оператора UPDATE:

UPDATE CUSTOMER

SET AreaCode = '(619)'

WHERE City = 'Philo';

Теперь таблица CUSTOMER выглядит так, как показано в табл. 6.3.

Таблица 6.3. Таблица CUSTOMER после обновления нескольких строк оператором update.

Name	City	Area-Code	Telephone
Abe Abelson	Kankakee	(714)	666-6666
Bill Bailey	Decatur	(714)	555-2222
Chuck Wood	Philo	(619)	555-3333
Don Stetson	Philo	(619)	555-4444
Dolph Stetson	Philo	(619)	555-5555

Обновить в таблице все строки даже легче, чем только некоторые из них. Ведь в таком случае не надо использовать ограничивающее предложение WHERE. Представьте, что город Рантул значительно увеличился в размерах и в его состав вошли не только Канкаки, Декейтер и Файло, но и все остальные города и городки, упомянутые в базе данных. Тогда все строки можно сразу изменить с помощью одного оператора:

UPDATE CUSTOMER

SET City = 'Rantoul';

Результат показан в табл. 6.4.

Таблица 6.4. Таблица CUSTOMER после обновления всех строк оператором update.

Name	City	Area-Code	Telephone
Abe Abelson	Rantoul	(714)	666-6666
Bill Bailey	Rantoul	(714)	555-2222
Chuck Wood	Rantoul	(619)	555-3333
Don Stetson	Rantoul	(619)	555-4444
Dolph Stetson	Rantoul	(619)	555-5555

В предложении WHERE, используемом для ограничения тех строк, к которым применяется оператор UPDATE, может находиться подвыборка. Подвыборка дает возможность обновлять строки одной таблицы на основе содержимого другой.

Предположим, что вы оптовый продавец и в вашей базе данных находится таблица VENDOR (поставщик) с названиями всех фирм-производителей, у которых вы покупаете товары. У вас также есть таблица PRODUCT (товар) с названиями всех продаваемых вами товаров и ценами, которые вы за них назначаете. В таблице VENDOR имеются столбцы VendorlD (идентификатор поставщика), VendorName (название поставщика), Street (улица), City (город), State (штаг) и Zip (почтовый код). А в таблице PRODUCT имеются столбцы ProductID (идентификатор товара), ProductName (название товара), VendorlD (идентификатор поставщика) и SalePrice (цена при продаже).

Предположим, поставщик Cumulonimbus Corporation принял решение поднять цены на все виды товаров на 10%. И для того чтобы поддержать планку своей прибыли, вам также придется поднять на 10% цены продажи продуктов, получаемых от этого поставщика. Это можно сделать с помощью следующего оператора UPDATE:

UPDATE PRODUCT

SET SalePrice = (SalePrice * 1.1)

WHERE VendorlD IN

(SELECT VendorlD FROM VENDOR

WHERE VendorName = 'Cumulonimbus Corporation');

Подстрока находит то значение из столбца VendorlD, которое соответствует Cumulonimbus Corporation. Затем полученное значение можно использовать для поиска в таблице PRODUCT тех строк, которые следует обновить. Цены всех товаров, полученных от Cumulonimbus Corporation, повышаются на 10%, а цены остальных остаются прежними. О подвыборках более подробно рассказывается в главе 11.

Перемещение данных

Помимо команд INSERT и UPDATE, можно воспользоваться командой MERGE (слияние), чтобы добавить данные в таблицу или представление. Команда MERGE позволяет производить "слияние" данных исходных таблиц, представления – в нужные таблицы или сами представления. Эта же команда позволяет вставить новые строки в нужную таблицу или обновить существующие строки. Таким образом, команда MERGE представляет собой весьма удобный способ копирования уже существующих данных из одного местоположения в новое, необходимое пользователю.

Возьмем, к примеру, базу данных VetLab (см. главу 5). Предположим, что некоторые работники, занесенные в таблицу EMPLOYEE, – это продавцы, которые уже приняли заказы, а другие – это работники, не связанные напрямую с продажами, или продавцы, которые еще не взяли заказы. Только что закончившийся год был прибыльным, поэтому вы решили дать премии по 100 долларов каждому, кто принял по крайней мере один заказ, и по 50 долларов всем остальным. Для начала давайте создадим таблицу BONUS (бонус) и вставим в нее записи для каждого работника, который появляется хотя бы однажды в таблице ORDERS, задавая каждой записи значение премии по умолчанию 100 долларов.

Затем воспользуемся командой MERGE, чтобы вставить новые записи для тех работников, которые не имеют заказов, давая им премии 50 долларов. Ниже приведен программный код, который позволяет создать и заполнить таблицу BONUS.

СREARE TABLE BONUS (
EmployeeName	CHARACTER (30)	PRIMARY KEY
Bonus	NUMERIC	DEFAULT 100);

INSERT INTO BONUS (EmployeeName)

(SELECT EmployeeName FROM EMPLOYEE, ORDERS

WHERE EMPLOYEE.EmployeeName = ORDERS.Salesperson

GROUP BY EMPLOYEE.EmployeeName);

Теперь сделаем запрос для таблицы BONUS и посмотрим, что она содержит.

SELECT * FROM BONUS;
EmployeeName	BONUS
----------------	-------
Brynna Jones	100
Chris Bancroft	100
Greg Bosser	100
Kyle Weeks	100

Затем выполним команду MERGE, чтобы назначить премии по 50 долларов для всех остальных работников.

MERGE INTO BONUS

USING EMPLOYEE

ON (BONUS.EmployeeName = EMPLOYEE.EmployeeName)

WHEN NOT MATCHED THEN INSERT

(BONUS.EmployeeName, BONUS,bonus)

VALUES (EMPLOYEE.EmployeeName, 50);

Записи для людей в таблице EMPLOYEE, которые не соответствуют записям для тех же людей, но уже в таблице BONUS, будут вставлены в таблицу BONUS. Теперь запрос таблицы BONUS дает следующее:

SELECT * FROM BONUS;
EmployeeName	BONUS
----------------	-------
Brynna Jones	100
Chris Bancroft	100
Greg Bosser	100
Kyle Weeks	100
Neth Doze	50
Matt Bak	50
Sam Saylor	50
Nic Foster	50

Первые четыре записи, созданные с помощью команды INSERT, располагаются в алфавитном порядке по именам работников. Остальные записи, добавленные с помощью команды MERGE, располагаются в том порядке, в котором они были в таблице EMPLOYEE.

Удаление устаревших данных

С течением времени данные могут устаревать и становиться бесполезными. Ненужные данные, находясь в таблице, только замедляют работу системы, расходуют память и путают пользователей. Возможно, лучше перенести старые данные в архивную таблицу, а затем поместить архив с этой таблицей вне системы. Таким образом, в случае такого маловероятного события, когда потребуется снова взглянуть на эти данные, их можно будет восстановить. А в остальное время они не будут замедлять ежедневную обработку данных. Впрочем, независимо от того, было ли решено архивировать устаревшие данные или нет, все же наступит время, когда их надо будет удалить. Для удаления строк из таблицы, находящейся в базе данных, в языке SQL предназначен оператор DELETE (удалить).

С помощью единственного оператора DELETE можно удалить как все строки таблицы, так и некоторые из них. Строки, предназначенные для удаления, можно выбрать, используя в операторе DELETE необязательное предложение WHERE. Синтаксис оператора DELETE такой же, как и в операторе SELECT, за исключением того, что столбцы указывать не надо. При удалении строки удаляются все данные, находящиеся в ее столбцах.

Предположим, например, что ваш клиент Дэвид Тейлор переехал на Таити и больше ничего у вас покупать не собирается. Вы можете удалить его данные из таблицы CUSTOMER, используя следующий оператор:

DELETE FROM CUSTOMER

WHERE FirstName = 'David' AND LastName = 'Taylor';

Если у вас только один покупатель, которого зовут Дэвид Тейлор, то этот оператор будет выполнен безупречно. А если существует вероятность, что Дэвидом Тейлором зовут как минимум двух ваших покупателей? Чтобы удалить данные именно того из них, к кому вы потеряли интерес, добавьте в предложение WHERE дополнительные условия (для таких столбцов, как, например. Street, Phone или CustomerlD).

 

Определение значений

В этой главе…

· Использование переменных для уменьшения избыточного кодирования

· Получение часто запрашиваемой информации, находящейся в поле таблицы базы данных

· Комбинирование простых значений для создания составных выражений

В этой книге постоянно подчеркивается, насколько важной для поддержания целостности базы данных является структура этой базы. Впрочем, хотя значение структуры базы данных часто недооценивается, но не надо забывать, что наибольшую важность все же представляют сами данные. В конце концов, значения, хранящиеся на пересечении строк и столбцов в таблице базы данных, являются "сырьем", из которого можно получать ценную информацию об имеющихся взаимоотношениях и тенденциях.

Значения можно получать несколькими способами – непосредственно или с помощью функций или выражений. В этой главе описываются разные виды значений, функций и выражений.

Помни:
Функция принимает данные и на их основе вычисляет значение. Выражение является комбинацией элементов данных, из которой SQL в результате вычислений получает единственное значение.

Значения

В SQL имеется несколько видов значений:

· значения типа записи;

· литеральные значения;

· переменные;

· специальные переменные;

· ссылки к столбцам.