|
|
Глава 6. Манипуляции данными из базы В этой главе...
В главах 3 и 4 вы узнали, что для обеспечения целостности информации, которая хранится в базе данных, очень важно создать хорошую структуру этой базы. Впрочем, для пользователя интерес представляет не структура базы данных, а ее содержимое, т.е. сами данные. С данными можно выполнять четыре следующих действия: добавлять в таблицы, получать и выводить, изменять, а также удалять из таблиц. В принципе, манипуляции данными выполнять достаточно просто. Легко разобраться, каким образом можно добавить в таблицу одну или сразу несколько строк данных. Изменение, удаление и получение строк из таблиц баз данных также не представляют особого труда. Главная трудность манипуляций с базами данных состоит в выборе строк, которые требуется изменить, удалить или получить. Иногда получение данных напоминает складывание мозаики из ее фрагментов, которые перемешаны с фрагментами сотен других таких мозаик. Нужные данные обычно перемешаны с ненужными, причем последних во много раз больше. К счастью, вам требуется лишь точно указать, что именно вам нужно сделать с помощью оператора SELECT, а весь поиск выполнит компьютер. SQL во встроенных инструментах Получение данных Задача, которую выполняют пользователи, манипулируя данными чаще всего состоит в том, чтобы получить из базы выбранную информацию. Допустим, вы хотите получить содержимое одной определенной строки, находящейся в таблице среди тысяч других. Или, возможно, требуется получить все строки, удовлетворяющие какому-либо условию или комбинации условий. А может быть, вы хотите получить из таблицы все ее строки. Для решения всех этих задач предназначен оператор SQL SELECT. Проще всего с помощью оператора SELECT получить все данные, хранящиеся во всех строках определенной таблицы. Для этого используется следующий синтаксис: SELECT * FROM CUSTOMER ; Помни: Звездочка (*)— это символ-маска, который означает "все". В данном примере этот символ стоит вместо перечня всех имен столбцов из таблицы CUSTOMER. В результате выполнения этого оператора на экран выводятся все данные, находящиеся во всех строках и столбцах этой таблицы. Операторы SELECT могут быть намного сложнее, чем тот, который приведен в примере. Некоторые из них могут быть настолько сложными, что в них становится почти невозможно разобраться. Это связанно с тем, что есть возможность к основному оператору присоединять еще и множество уточняющих предложений. Подробно об уточняющих предложениях рассказывается в главе 9. В этой же главе кратко говорится о предложении WHERE — самом распространенном способе ограничить количество строк, возвращаемых оператором Оператор SELECT с предложением WHERE имеет такой общий вид: SELECT список_столбцов FROM имя_таблицы WHERE условие ; Список столбцов указывает, какие столбцы таблицы следует отобразить при выводе. Этот оператор отобразит только те столбцы, которые вы запросите. Предложение FROM определяет имя той таблицы, столбцы которой требуется отобразить. А предложение WHERE исключает те строки, которые не удовлетворяют указанному условию. Условие может быть простым (например, WHERE CUSTOMER_STATB= 'NH', где CUSTOMER_STATE означает "штат, где проживает клиент", a NH — "штат Нью-Хэмпшир") или составным (например, WHERE CUSTOMER_STATE= 'NH' AND STATUS='Active', где STATUS означает "статус", a Active — "активный"). Следующий пример показывает, как выглядит составное условие внутри оператора SELECT: SELECT FirstName, LastName, Phone FROM CUSTOMER WHERE State= 'NH' AND Status='Active' ; Этот оператор возвращает фамилии и телефонные номера всех активных клиентов, живущих в штате Нью-Хэмпшир. Ключевое слово AND означает следующее: чтобы строка была возвращена, она должна соответствовать сразу двум условиям, State= 'NH' и Status='Active'. Создание представлений Структура базы данных, спроектированной в соответствии с разумными принципами — включая и подходящую нормализацию, — обеспечивает максимальную целостность данных. Однако такая структура часто не позволяет обеспечить лучший способ их просмотра. Одни и те же данные могут использоваться разными приложениями, и у каждого из них может быть своя специализация. Одним из самых сильных качеств SQL является возможность выводить данные в виде представлений, чья структура отличается от структуры тех таблиц базы, в которых реально хранятся эти данные. Таблицы, столбцы и строки которых используются при создании представления, называются базовыми. В главе 3 говорилось о представлениях как о части языка определения данных (Data Definition Language, DDL). А в этом разделе представления рассматриваются как одно из средств, предназначенных для получения данных и манипуляции ими. Оператор SELECT всегда возвращает результат в виде виртуальной таблицы. Представление же принадлежит к особой разновидности этих таблиц. От остальных виртуальных таблиц оно отличается тем, что в метаданных базы хранится его определение. Представление других виртуальных таблиц похоже на реальную таблицу базы данных. Работать с представлением можно так же, как и с настоящей таблицей базы данных. Разница здесь в том, что совокупность данных, находящихся в представлении, не является физически независимой частью базы данных. Представление извлекает их из одной или множества таблиц, из которых и были выбраны его столбцы. В каждом приложении могут быть свои собственные, непохожие друг на друга представления, но созданные на основе одних и тех же данных. Обратите внимание на базу данных VetLab (см. главу 5). Эта база состоит из пяти таблиц: CLIENT (фирма-клиент), TESTS (анализы), EMPLOYEE (сотрудник), ORDERS (заказы) и RESULTS (результаты). Предположим, что главному менеджеру по маркетингу компании VetLab необходимо посмотреть, из каких штатов в эту компанию приходят заказы. Часть этой информации находится в таблице CLIENT, а часть — в ORDERS. А чиновнику из службы контроля качества требуется сравнить дату оформления заказа на один из анализов и дату получения его окончательного результата. Для этого сравнения требуются некоторые данные из таблицы ORDERS и RESULTS. В каждом конкретном случае можно создать представления, предоставляющие в точности те данные, которые требуются. Создание представлений из таблиц Для менеджера по маркетингу можно создать представление ORDERS_BY_STATE (заказы по штатам), приведенное на рис. 6.1.
Рис. 6.1. Представление ORDERS_BY_STATE, предназначенное для менеджера по маркетингу Представление для менеджера по маркетингу создается с помощью следующего оператора: CREATE VIEW ORDERS_BY_STATE (ClientName, State, OrderNumber) AS SELECT CLIENT. ClientName, State, OrderNumber FROM CLIENT, ORDERS WHERE CLIENT.ClientName = ORDERS.ClientName; В новом представлении имеются три столбца: ClientName (название фирмы-клиента), State (штат) и OrderNumber (номер заказа). ClientName находится как в CLIENT, так и в ORDERS и используется для связи между этими двумя таблицами. Новое представление получает информацию из столбца State таблицы CLIENT и берет для каждого заказа значение из столбца OrderNumber таблицы ORDERS. В приведенном примере имена столбцов нового представления объявляются явно. Впрочем, если имена точно такие же, как и у соответствующих столбцов исходных таблиц, то такое объявление не обязательно. Пример, приведенный в следующем разделе, демонстрирует похожий оператор CREATE VIEW, в котором имена столбцов для представления явно не указываются, а только подразумеваются. Создание представления с условием выборки Как видно в примере на рис. 6.2, для чиновника из службы контроля качества требуется представление, отличающееся от того, которое использует менеджер по маркетингу.
Рис. 6.2. Представление REPORTINGJAG (задержка результатов), предназначенное для чиновника из службы контроля качества Ниже приведен код, с помощью которого создается представление, приведенное на рис. 6.2. CREATE VIEW REPORTING_LAG AS SELECT ORDERS.OrderNumber, OrderDate, DateReported FROM ORDERS, RESULTS WHERE ORDERS. OrderNumber = RESULTS. OrderNumber AND RESULTS.PreliminaryFinal = 'F' ; В представлении REPORTTNG_LAG содержится информация из таблицы ORDERS по датам заказов и из таблицы RESULTS по датам окончательных результатов. В этом представлении появляются только строки, у которых в столбце PRELIMJFINAL (предварительный-окончательный), взятом из таблицы RESULTS, находится значение 'F (от слова "final" — окончательный). Создание представления с модифицированным атрибутом В примерах из двух предыдущих разделов предложения SELECT содержат только имена столбцов. Впрочем, в любом предложении SELECT может находиться не только имя, но и выражение. Предположим, что владелец VetLab отмечает свой день рождения и хочет в честь этого события предоставить всем своим клиентам 10-процентную скидку. Он может на основе двух таблиц, ORDERS и TESTS, создать представление BIRTHDAY (день рождения). Вполне возможно, что оно будет создано так, как показано в следующем примере: CREATE VIEW BIRTHDAY (ClientName, Test, OrderDate, BirthdayCharge) AS SELECT ClientName, TestOrdered, OrderDate, StandardCharge * .9 FROM ORDERS, TESTS WHERE TestOrdered = TestName ; Обратите внимание, что в представлении BIRTHDAY второй столбец — Test (анализ) — соответствует столбцу TestOrdered (заказанный анализ) из таблицы ORDERS, который также соответствует столбцу TestName (название анализа) из таблицы TESTS. Как создать это представление, можно увидеть на рис. 6.3.
Рис. 6.З. Представление, созданное, чтобы показать скидки в честь дня рождения Представления можно создавать как на основе множества таблиц, что и делалось в предыдущих примерах, так и на основе всего лишь одной таблицы. Если вам не нужны определенные столбцы и строки какой-либо таблицы, то создайте представление, в котором нет этих строк и столбцов, а затем работайте уже не с таблицей, а с представлением. Этот подход защищает пользователя от путаницы и не отвлекает внимание, которые бывают при просмотре участков таблицы, не относящихся к делу. Совет: Еще одной причиной создания представлений является обеспечение безопасности исходных таблиц базы данных. Одни столбцы ваших таблиц следует открыть, а другие, наоборот, скрыть. В таком случае можно создать представление только с теми столбцами, которые вы хотите сделать доступными, затем предоставить к нему широкий доступ, а доступ к исходным таблицам этого представления— ограничить. В главе 13 объясняется, каким образом обеспечивается безопасность баз данных, и описывается, как предоставлять полномочия на доступ к данным и лишать их. Обновление представлений Созданные таблицы автоматически поддерживают возможности вставки, обновления И удаления данных. А вот к представлениям это относится не всегда. Обновляя представление, вы на самом деле обновляете исходную таблицу. Вот две потенциальные проблемы, возникающие при обновлении представлений.
Предположим, что вы создаете представление СОМР из таблицы EMPLOYEE (сотрудник), используя се поля EmpName (фамилия), SALARY (оклад) и Comm (комиссионные). Для этого вы используете следующий оператор: CREATE VIEW COMP AS SELECT EmpName, Salary+Comm AS Pay FROM EMPLOYEE ; Можно ли в представлении обновить столбец PAY (оплата), используя следующий оператор? UPDATE COMP SET Pay = Pay + 100 ; Нет, этот подход не сработает потому, что в таблице EMPLOYEE нет столбца Pay, и в ней он обновлен не будет, а следовательно, не будет обновлен и в представлении. Представление не может показать того, чего нет в исходной таблице. Помни: Когда вы думаете об обновлении представлений, не забывайте следующее правило. Столбец представления обновлять нельзя, если он не соответствует столбцу таблицы этого представления. Добавление новых данных В базе данных каждая таблица появляется на свет пустой, т.е. сразу после своего создания (или с помощью DDL из SQL, или с помощью RAD-инструмента) такая таблица является не чем иным, как структурной оболочкой, не содержащей данных. Чтобы таблица стала полезной, в нее необходимо поместить некоторые данные. Эти данные могут быть уже в цифровом виде, т.е. введенными в компьютер, или еще нет.
В зависимости от того состояния, в котором находятся данные, их или сразу можно перевести в базу с помощью одной операции, или, возможно, потребуется вводить в виде отдельных записей одну за другой. Каждая такая запись будет соответствовать одной строке из какой-либо таблицы базы данных. Добавление данных в виде отдельных записей В большинстве СУБД поддерживается ввод данных с помощью форм. Такая возможность позволяет создать экранную форму, в которой для каждого табличного столбца из базы данных имеется свое поле. Находящиеся в форме метки полей позволяют легко определить, какие данные следует вводить в каждое поле. Оператор вручную вводит в форму все данные, предназначенные для одной строки. После того как СУБД примет новую строку, она очищает форму, чтобы в нее можно было вводить данные для следующей строки. Таким образом можно легко добавить в таблицу данные в виде отдельных строк одну за другой. Ввод данных с помощью форм легко использовать, кроме того, при таком вводе допускается меньше ошибок, чем при вводе списков значений, отделяемых друг от друга запятыми. Основная трудность ввода данных с помощью форм состоит в том, что он не является стандартным. В различных системах СУБД имеются собственные методы создания форм. Для оператора, занятого вводом данных, не имеет значения, каким образом создана форма ввода. Можно сделать та, что форма в любой СУБД будет выглядеть в основном одинаково. А вот разработчику приложений при переходе к новым инструментам разработки приходится учиться с самого начала. При вводе данных с помощью форм встречается и другая трудность. Она состоит в том, что в некоторых реализациях нельзя выполнять полную проверку правильности вводимых данных. Самый лучший способ поддерживать на высоком уровне целостность данных в базе — это не вводить неправильные данные. Предотвратить неправильный ввод можно, применяя ограничения к полям формы ввода. Это гарантирует, что в базу данных попадут только те значения, которые имеют правильный тип данных и являются частью заранее определенного диапазона. Конечно, с помощью такого ограничения нельзя предотвратить все возможные ошибки, но "отловить" некоторые все же можно. Если в таблицу из базы данных вводятся значения, предназначенные для отдельной строки, то в команде INSERT используется следующий синтаксис: INSERT INTO таблица_1 [(столбец_1, столбвц_2, ..., столбец_n)] VALUES (значение_1, значение_2, ..., значение_n) ; Совет: Бывает так, что средства проектирования форм вашей СУБД не могут осуществить все нужные проверки, гарантирующие целостность данных. В таком случае вам придется создавать собственные процедуры ввода, в которых вводимые значения присваиваются переменным и проверяются с помощью кода самой прикладной программы. Затем, убедившись, что все значения, введенные для табличной строки, являются правильными, программа может добавить эту строку в таблицу с помощью команды SQL INSERT (вставить). Квадратные скобки ([]) означают, что список имен столбцов не является обязательным. По умолчанию порядок расположения столбцов в списке является таким же, как и в таблице. Если расположить значения, находящиеся после ключевого слова VALUES (значения), в том же порядке, в каком столбцы находятся в таблице, то эти элементы попадут в нужные столбцы — неважно, указаны при этом столбцы явно или нет. А если требуется расположить эти значения в порядке, который не совпадает с расположением столбцов в таблице, то тогда имена столбцов необходимо перечислить в требуемом порядке. Чтобы ввести, например, запись в таблицу CUSTOMER (покупатель), используйте следующий синтаксис: INSERT INTO CUSTOMER (CustomerlD, FirstName, LastName, Street, City, State, Zipcode, Phone) VALUES (:vcustid, 'David1, 'Taylor', '235 Nutley Ave.', 'Nutley', 'NJ', '07110', '(201) 555-1963') ; После ключевого слова VALUES первым стоит vcustid — базовая переменная-счетчик, значение которой с помощью программного кода увеличивается на единицу, как только в таблицу введена новая строка. Это дает гарантию, что не будет дублирования значений в столбце CustomerlD (идентификатор покупателя). CustomerlD является первичным ключом для этой таблицы и поэтому должен оставаться уникальным. Остальные значения в операторе являются не переменными с элементами данных, а самими элементами данных. Конечно, элементы данных для этих столбцов также можно, если хотите, поместить в переменные. Оператор INSERT работает одинаково хорошо с аргументами ключевого слова VALUES, выраженными как в форме переменных, так и в форме значений. Добавление данных только в выбранные столбцы Иногда нужно где-то отметить, что объект существует, даже если по нему еще нет всех данных. Если у вас для таких объектов есть таблица базы данных, то строку по новому объекту можно вставить в нее, не заполняя значениями все столбцы этой строки. И если вы хотите, чтобы таблица была в первой нормальной форме, то необходимо вставить столько данных, чтобы можно было отличать новую строку от всех остальных строк этой таблицы. (О первой нормальной форме см. в главе 5.) Для этого в новой строке достаточно вставить первичный ключ. Кроме этого ключа вставляйте все остальные данные, которые известны об этом объекте. В тех столбцах, куда данные не вводятся, остаются значения NULL. Ниже приведен пример такого частичного ввода строки. INSERT INTO CUSTOMER (CustomerlD, FirstName, LastName) VALUES (:vcustid, 'Tyson1, 'Tylor') ; Вы вставляете только уникальный идентификационный номер клиента, а также его имя и фамилию. А в остальных столбцах этой строки будут находиться значения NULL. Добавление в таблицу группы строк Добавлять в таблицу строки одну за другой, используя для этого оператор INSERT, становится ужасно скучным — особенно если это растягивается на целый день. Даже вводить данные в экранную форму, тщательно продуманную с точки зрения эргономики, через некоторое время становится утомительным. Ясно, что если у вас есть надежный способ автоматического ввода данных, вы будете стараться использовать его вместо ввода вручную везде, где это возможно. Автоматический ввод, например, можно использовать тогда, когда данные уже представлены в электронном виде, благодаря тому, что кто-то уже ввел вручную эти данные в компьютер. Незачем повторять эту рутинную работу. Перенести данные из одного файла в другой можно с минимальным участием человека. Если вам известны характеристики исходных данных и нужная форма таблицы, в которую должны быть перенесены данные, то компьютер может в принципе выполнить такой перенос данных автоматически. Копирование из внешнего файла данных Предположим, что вы создаете базу данных для нового приложения. Некоторые из нужных вам данных уже имеются в каком-либо файле. Это может быть плоский файл или таблица базы данных, работающей в СУБД, отличающейся от той, которую используете вы. Данные могут быть в коде ASCII, EBCDIC или в каком-нибудь другом закрытом внутреннем формате. Так что же делать? Прежде всего — это надеяться и молиться, чтобы нужные вам данные были представлены в каком-нибудь широко используемом формате. Если это достаточно популярный формат, то у вас имеется хороший шанс достать утилиту преобразования формата, которая может преобразовать данные в один или несколько других популярных форматов. А затем как минимум один из этих форматов можно импортировать в вашу среду разработки. А если повезет, то можно будет преобразовать текущий формат данных с помощью встроенных средств среды. Вероятно, самыми распространенными на персональных компьютерах форматами являются Access, dBASE и Paradox. Если нужные вам данные находятся в одном из этих форматов, преобразование должно пройти легко. Ну а если формат данных является не таким распространенным, то, видимо, преобразование все-таки придется провести в два этапа. И с последней надеждой можно всегда обратиться к специальным профессиональным службам преобразования данных. Они специализируются на оказании услуг по преобразованию компьютерных данных из одного формата в другой. У них есть возможность обрабатывать сотни разных форматов — о большинстве из которых никто никогда и не слышал. Передайте одной из этих служб ленту или диск с данными в первоначальном формате, и вы получите назад те желанные, но преобразованные в любой из форматов, который только укажете. Перенос всех строк из одной таблицы в другую Много проще, чем импортировать внешние данные, извлекать данные, уже находящиеся водной из таблиц вашей базы, и комбинировать их с данными из другой таблицы. В самом простом случае структура второй таблицы идентична структуре первой. Это означает, что каждый столбец первой таблицы имеет соответствующий столбец во второй, а типы данных соответствующих столбцов совпадают. В таком случае содержимое двух таблиц можно комбинировать с помощью реляционного оператора UNION (объединение). В результате получается виртуальная таблица, в которой содержатся данные исходных таблиц. О реляционных операторах, в том числе о UNION, рассказывается в главе 10. Перенос выбранных столбцов и строк из одной таблицы в другую Часто бывает так, что данные исходной таблицы не соответствуют в точности структуре той таблицы, в которую вы собираетесь их поместить. Возможно, соответствуют друг другу только некоторые из столбцов — и это как раз те столбцы, которые вы хотите перенести. Комбинируя операторы SELECT с помощью оператора UNION, можно указать, какие столбцы из исходных таблиц должны войти в полученную в результате виртуальную таблицу. Используя в операторах SELECT предложения WHERE, можно помещать в виртуальную таблицу только те строки, которые удовлетворяют определенным условиям. Предложения WHERE достаточно подробно описываются в главе 9. Предположим, у вас имеются две таблицы, PROSPECT (потенциальный клиент) и CUSTOMER (покупатель), и вам нужно составить список всех жителей штата Мэн, данные о которых находятся в обеих таблицах. Тогда можете создать виртуальную таблицу с нужной информацией, используя следующую команду: SELECT FirstName, LastName FROM PROSPECT WHERE State = 'ME' UNION SELECT FirstName, LastName FROM CUSTOMER WHERE State = 'ME' В этом коде заключено следующее:
Другой способ копировать данные в базе из одной ее таблицы в другую состоит в том, чтобы разместить оператор SELECT в операторе INSERT. Такой метод (подвыборка) виртуальной таблицы не создает, а просто дублирует выбранные данные. Например, вы можете взять все строки из таблицы CUSTOMER и вставить их в таблицу PROSPECT. Конечно, эта операция удастся только в том случае, если у обеих этих таблиц одинаковая структура. Далее, если нужно отобрать только тех покупателей, которые живут в штате Мэн, то достаточно простого оператора SELECT, имеющего в предложении WHERE всего лишь одно условие. Соответствующий код показан в следующем примере: INSERT INTO PROSPECT SELECT * FROM CUSTOMER WHERE 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
Время от времени списки покупателей изменяются, по мере того как эти люди переезжают, изменяются номера телефонов и т.д. Предположим, что Эйб Эйбелсон (Abe Abelson) переехал из Спрингфилда в Канкаки. Тогда запись этого покупателя, находящуюся в таблице CUSTOMER, можно обновить с помощью следующего оператора UPDATE: UPDATE CUSTOMER SET City = 'Kankakee1, Telephone = '666-6666' WHERE Name = 'Abe Abelson1 ; В результате его выполнения в записи произошли изменения, которые показаны в табл. 6.2. Таблица 6.2. Таблица CUSTOMER после обновления одной строки оператором update
Похожий оператор можно использовать, чтобы обновить сразу множество строк. Предположим, что город Файло переживает резкий рост населения и ему теперь требуется собственный телефонный код региона. Все строки покупателей, проживающих в этом городе, можно сразу изменить с помощью одного оператора UPDATE: UPDATE CUSTOMER SET AreaCode = '(619)' WHERE City = 'Philo' ; Теперь таблица CUSTOMER выглядит так, как показано в табл. 6.3. Таблица 6.3. Таблица CUSTOMER после обновления нескольких строк оператором update
Обновить в таблице все строки даже легче, чем только некоторые из них. Ведь в таком случае не надо использовать ограничивающее предложение WHERE. Представьте, что город Рантул значительно увеличился в размерах и в его состав вошли не только Канкаки, Декейтер и Файло, но и все остальные города и городки, упомянутые в базе данных. Тогда все строки можно сразу изменить с помощью одного оператора: UPDATE CUSTOMER SET City = 'Rantoul' ; Результат показан в табл. 6.4. Таблица 6.4. Таблица CUSTOMER после обновления всех строк оператором update
В предложении 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.
INSERT INTO BONUS (EmployeeName) (SELECT EmployeeName FROM EMPLOYEE, ORDERS WHERE EMPLOYEE.EmployeeName = ORDERS.Salesperson GROUP BY EMPLOYEE.EmployeeName) ; Теперь сделаем запрос для таблицы BONUS и посмотрим, что она содержит.
Затем выполним команду 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 дает следующее:
Первые четыре записи, созданные с помощью команды INSERT, располагаются в алфавитном порядке по именам работников. Остальные записи, добавленные с помощью команды MERGE, располагаются в том порядке, в котором они были в таблице EMPLOYEE. Удаление устаревших данных С течением времени данные могут устаревать и становиться бесполезными. Ненужные данные, находясь в таблице, только замедляют работу системы, расходуют память и путают пользователей. Возможно, лучше перенести старые данные в архивную таблицу, а затем поместить архив с этой таблицей вне системы. Таким образом, в случае такого маловероятного события, когда потребуется снова взглянуть на эти данные, их можно будет восстановить. А в остальное время они не будут замедлять ежедневную обработку данных. Впрочем, независимо от того, было ли решено архивировать устаревшие данные или нет, все же наступит время, когда их надо будет удалить. Для удаления строк из таблицы, находящейся в базе данных, в языке SQL предназначен оператор DELETE (удалить). С помощью единственного оператора DELETE можно удалить как все строки таблицы, так и некоторые из них. Строки, предназначенные для удаления, можно выбрать, используя в операторе DELETE необязательное предложение WHERE. Синтаксис оператора DELETE такой же, как и в операторе SELECT, за исключением того, что столбцы указывать не надо. При удалении строки удаляются все данные, находящиеся в ее столбцах. Предположим, например, что ваш клиент Дэвид Тейлор переехал на Таити и больше ничего у вас покупать не собирается. Вы можете удалить его данные из таблицы CUSTOMER, используя следующий оператор: DELETE FROM CUSTOMER WHERE FirstName = 'David' AND LastName = 'Taylor' ; Если у вас только один покупатель, которого зовут Дэвид Тейлор, то этот оператор будет выполнен безупречно. А если существует вероятность, что Дэвидом Тейлором зовут как минимум двух ваших покупателей? Чтобы удалить данные именно того из них, к кому вы потеряли интерес, добавьте в предложение WHERE дополнительные условия (для таких столбцов, как, например. Street, Phone или CustomerlD). |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
| На правах рекламы: |