Разработка при помощи Apache Derby -- тройной выигрыш: Разработка баз данных при помощи Apache Derby, Часть 6

Изменение данных и схем

Наряду с созданием схемы базы данных и заполнения таблиц данными, возможность избирательного изменения данных является самым важным навыком, необходимым для разработчика баз данных. В данной статье показано, как избирательно удалять или обновлять данные в существующей таблице и как изменять структуру существующих таблиц. Для изменения данных в более сложных схемах баз данных необходимо освоить использование вложенных подзапросов, скалярных и табличных, и операции обновления и вставки данных. В статье будет показано, как удалять и изменять данные в сложных схемах с помощью базы данных Apache Derby.

Роберт Бруннер , ученый-исследователь NCSA, старший преподаватель астрономии, Университет штата Иллинойс, г. Урбана-Шампейн

Роберт Дж. Бруннер (Robert J. Brunner) занимается научными исследованиями в Национальном центре по приложениям для суперкомпьютеров и является старшим преподавателем астрономии в университете штата Иллинойс, город Урбана-Шампейн. Автор нескольких книг и множества статей и практических руководств на различные темы.



10.04.2007

Введение

Предыдущие статьи данной серии:

  • Знакомство с базой данных Apache Derby;
  • Представление инструмента ij;
  • Показано, как создавать схему базы данных, проектировать таблицы реляционных баз данных и вставлять данные в таблицы;
  • Показано, как извлекать данные с помощью SQL-запросов.

Не затронута еще одна важная задача - изменение существующих данных. В данной статье представлены операторы SQL DELETE и UPDATE, которые можно использовать для избирательного удаления или изменения существующих данных в базе данных Apache Derby.

Для выполнения упражнений данной статьи требуется следующее:

  1. Установленная рабочая база данных Apache Derby, рассматриваемая в первой статье данной серии;
  2. Знакомство с инструментом командной строки Apache Derby ij, который рассматривается во второй статье данной серии;
  3. Правильно инициализированный пример базы данных Bigdog's Surf Shop, подробно рассматриваемый в четвертой и пятой статьях данной серии;
  4. Знакомство с основами оператора SQL SELECT, который рассматривался в пятой статье данной серии.

Если этого не сделано, перед продолжением работы с данной статьей убедитесь, что выполнены шаги, перечисленные выше, что можно легко сделать, просмотрев предыдущие статьи данной серии.


Удаление данных

Первый рассматриваемый в этой статье метод изменения данных - удаление. Для удаления данных в базе данных Apache Derby используется оператор SQL DELETE , позволяющий удалить все строки в таблице или определенный набор строк. Формальный синтаксис оператора SQL DELETE, применяемый для базы данных Apache Derby, удивительно прост, как это показано ниже:

DELETE FROM tableName
[WHERE clause]

Оператор DELETE удаляет все строки в заданной таблице, удовлетворяющие дополнительному предложению WHERE. Если предложение WHERE отсутствует, удаляются все строки в таблице. Для демонстрации использования оператора DELETE создадим временную таблицу, вставим в нее несколько строк и удалим их, как это показано в листинге 1.

Листинг 1. Удаление строк
ij> CREATE TABLE bigdog.temp (aValue INT) ;
0 rows inserted/updated/deleted
ij> INSERT INTO bigdog.temp VALUES(0), (1), (2), (3) ;
4 rows inserted/updated/deleted
ij> SELECT COUNT(*) AS COUNT FROM bigdog.temp ; 
COUNT      
-----------
4          

1 row selected
ij> DELETE FROM bigdog.temp ;
4 rows inserted/updated/deleted
ij> SELECT COUNT(*) AS COUNT FROM bigdog.temp ; 
COUNT           
-----------
0          

1 row selected
ij> DROP TABLE bigdog.temp ;
0 rows inserted/updated/deleted

В данном примере создается временная таблица с одним столбцом, содержащим целое значение. В базу данных вставляется четыре строки и вызывается оператор SELECT для проверки содержания в новой таблице четырех строк. Оператор DELETE без ограничений удаляет все четыре строки из временной таблицы, что подтверждается сообщением Apache Derby -- 4 rows inserted/updated/deleted -- и вторым оператором SELECT, указывающим, что временная таблица содержит ноль строк. Наконец, оператор DROP TABLE удаляет из схемы пустую таблицу.

Как правило, удалять все строки в таблице не требуется, нужно удалить только некоторые. Для этого создадим соответствующее предложение WHERE для определения нужных строк. Синтаксисы предложения WHERE, которое можно использовать с оператором DELETE , и предложения, рассмотреного в части 4, представляющего собой синтаксис оператора SQL SELECT, идентичны. Основные стандартные блоки для построения логических выражений в предложении WHERE представлены в таблице 1 данной статьи и показаны в листинге 2, где удаляются все строки, удовлетворяющие хотя бы одному из двух условий.

Листинг 2. Удаление выбранных строк
ij> DELETE FROM bigdog.products 
    WHERE description LIKE '%towel%' OR itemNumber <= 3 ;
5 rows inserted/updated/deleted
ij> SELECT itemNumber, description FROM bigdog.products ;
ITEMNUMBER |DESCRIPTION                             
----------------------------------------------------
4          |Male bathing suit, blue                 
5          |Female bathing suit, one piece, aqua    
6          |Child sand toy set                      
9          |Flip-flop                               
10         |Open-toed sandal                        

5 rows selected

В данном примере оператор DELETE включает предложение WHERE для определения пяти строк, которые можно просмотреть в информационном сообщении 5 rows inserted/updated/deleted, возвращаемом Apache Derby с помощью инструмента ij. Предложение WHERE содержит два выражения, объединенных оператором OR, то есть если одно из выражений для определенной строки соответствует TRUE, эта строка будет удалена.

Первое выражение служит для определения всех строк, содержащих слово "towel" в описании товара. Если вспомнить предыдущие статьи данной серии (или вызвать оператор SELECT до оператора DELETE), то в таблице bigdog.products имеются два полотенца со значениями столбца itemNumber 7 и 8. Другое выражение служит для выбора всех строк со значениями столбца itemNumber меньше или равными 3. В заключение содержимое таблицы bigdog.products отображается с помощью простого оператора SELECT, показывая, что в таблице остались только 5 строк из 10.

Хотя в данном примере не показано явное использование SQL-функций, можно включить SQL-функции, описанные в части 5 для обеспечения большего контроля при выборе строк для удаления. Функции и другие операторы, которые можно использовать в предложении WHERE оператора DELETE, также можно использовать с оператором UPDATE для избирательного изменения значений строк в таблице, как это описано в следующем разделе.


Обновление данных

Последняя нерассмотренная SQL-задача обработки данных представляет собой обновление значений в определенных столбцах для выбранных строк в таблице. На некотором уровне оператор SQL UPDATE представляет собой объединение операторов SQL INSERT и DELETE, поскольку необходимо выбрать строки для изменения, а также определить способ их изменения. Формально синтаксис оператора UPDATE является довольно простым, так как необходимо определить для обновления несколько значений столбцов для набора строк, как это показано в листинге 3.

Листинг 3. Синтаксис оператора SQL UPDATE
UPDATE tableName
        SET columnName = Value
        [ , columnName = Value} ]*
        [WHERE clause]

Как видно в этом примере SQL-синтаксиса, в операторе SQL UPDATE должен находиться хотя бы один компонент SET для обновления одного столбца вместе с одним или несколькими компонентами SET и предложением WHERE, которые являются дополнительными. Если предложение WHERE не включено, оператор UPDATE изменяет указанные столбцы для всех строк в таблице.

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

Листинг 4. Обновление выбранных строк
ij> SELECT itemNumber, price, stockDate FROM bigdog.products WHERE itemNumber = 6 ;
ITEMNUMBER |PRICE   |STOCKDATE 
-------------------------------
6          |9.95    |2006-01-15

1 row selected
ij> UPDATE bigdog.products
    SET price = price * 1.25, stockDate = CURRENT_DATE
    WHERE itemNumber = 6 ;
1 row inserted/updated/deleted
ij> SELECT itemNumber, price, stockDate FROM bigdog.products WHERE itemNumber = 6 ;
ITEMNUMBER |PRICE   |STOCKDATE 
-------------------------------
6          |12.43   |2006-06-20

1 row selected

В этом примере оператор UPDATE с операторами SELECT используется для демонстрации изменения в указанной строке. Операторы SELECT служат для выбора трех столбцов в таблице bigdog.products для одной строки (строка со значением 6 в столбце itemNumber). Оператор UPDATE изменяет столбцы price и stockDate в данной строке. Значение в строке price увеличивается на 25% (например, вследствие популярности товара), а столбец stockDate изменяется для включения текущей даты, которую в Apache Derby легко получить с помощью встроенной функции CURRENT_DATE в SQL-запросе.

Apache Derby включает несколько встроенных функций, которые можно использовать для получения данных, связанных с текущей подключенной базой данных. Полный список встроенных функций представлен в таблице 1.

Таблица 1. SQL-функции в Apache Derby
ФункцияОписание
CURRENT_DATEВозвращает текущую дату в подходящем формате Apache Derby DATE
CURRENT_ISOLATIONВозвращает текущий уровень изоляции транзакции, который подробнее будет рассмотрен в следующей статье, в виде двухсимвольной строки
CURRENT_SCHEMAВозвращает имя схемы в качестве строки длиной до 128 символов, используемой для классификации неклассифицированных имен объектов базы данных
CURRENT_TIMEВозвращает текущее время в подходящем формате Apache Derby TIME
CURRENT_TIMESTAMPВозвращает текущую метку времени в подходящем формате Apache Derby TIMESTAMP
CURRENT_USERВозвращает авторизованный идентификатор текущего пользователя в виде строки длиной до 128 символов или APP в случае отсутствия текущего пользовтеля

В предыдущем примере было показано, как изменить значения в нескольких столбцах для выбранной строки в отдельной таблице. Но иногда логика обновления выбранных строк является более сложной. Например, предположим, необходимо изменить цену всех объектов в таблице bigdog.products, поставляемых компанией Quiet Beach Industries и имеющих значение 3 в столбце vendorNumber таблицы bigdog.vendors . Для этого необходимо использовать встроенный запрос, как это показано в листинге 5.

Листинг 5. Обновление строк с помощью встроенного оператора SELECT
ij> UPDATE bigdog.products
    SET price = price * 1.10, description = 'NEW: ' || description
    WHERE itemNumber IN 
        ( SELECT v.itemNumber 
          FROM bigdog.products as p, bigdog.vendors as v 
          WHERE p.itemNumber = v.itemNumber AND v.vendorNumber = 3 ) ;
2 rows inserted/updated/deleted
ij> SELECT * FROM bigdog.products ;
ITEMNUMBER |PRICE   |STOCKDATE |DESCRIPTION                             
------------------------------------------------------------------------
4          |29.95   |2006-02-10|Male bathing suit, blue                 
5          |49.95   |2006-02-20|Female bathing suit, one piece, aqua    
6          |12.43   |2006-06-20|Child sand toy set                      
9          |14.24   |2006-03-12|NEW: Flip-flop                          
10         |38.44   |2006-01-24|NEW: Open-toed sandal                   

5 rows selected

В этом примере оператор UPDATE изменяет столбцы price и description для всех товаров, поставляемых поставщиком со значением 3 в столбце vendorNumber таблицы bigdog.vendors . Поскольку в операторе UPDATE нельзя выполнить простое объединение, необходимо в предложение WHERE включить подзапрос для извлечения строк itemNumber, соответствующих товарам от Quiet Beach Industries. В предложении WHERE в операторе UPDATE оператор IN используется для выбора строк со значением в столбце itemNumber, соответствующим набору значений, выбранных встроенным запросом.

В предложении WHERE оператора UPDATE можно использовать два типа запросов: скалярный подзапрос и табличный подзапрос. Скалярный подзапрос представляет собой встроенный запрос, возвращающий отдельную строку, содержащую один столбец, по существу, одно значение, известное как скаляр. Скалярный подзапрос можно использовать для выбора определенного значения, которое будет использоваться в выражении предложения WHERE. Например, выражение itemNumber = (Scalar Subquery) обновляет все строки со значением в столбце itemNumber, соответствующим результату скалярного подзапроса.

Табличный подзапрос, с другой стороны, может возвращать несколько строк, которые обычно имеют только один столбец. В некоторых случаях табличный подзапрос может содержать несколько столбцов. Для использования табличного подзапроса необходимо применить SQL-оператор для объединения встроенного запроса с логическим выражением. Это показано, например, в коде предыдущего листинга, где оператор IN служит для выбора всех строк из таблицы bigdog.products, товары в которых поставляются Quiet Beach Industries. Оператор IN представляет собой один из четырех SQL-операторов, которые можно использовать в табличных подзапросах. Все четыре оператора описаны в таблице 2.

Таблица 2. SQL-операторы и табличные подзапросы Apache Derby
ОператорПримерОписание
INitemNumber IN (табличный подзапрос)Возвращает TRUE, если значение выражения находится в табличном подзапросе, который может возвращать только отдельный столбец. Можно включить оператор NOT, например, NOT IN, для выбора строк, не находящихся в табличном подзапросе.
EXISTSEXISTS (табличный подзапрос)Возвращает TRUE, если табличный подзапрос возвращает любые строки, или FALSE, если строки не выбраны. Это означает, что изменены либо все строки, либо ни одна из строк, в зависимости от количества строк, выбранных в табличном запросе. Можно включить оператор NOT для инверсии этого правила.
ALLitemNumber = ALL (табличный подзапрос)Называют классифицированным сравнением, поскольку ключевое слово ALL изменяет оператор сравнения (один из =, <, >, <=, >= или <>), поэтому результат будет TRUE , только если условие истинно для всех строк. Табличный подзапрос может возвращать несколько строк, но у них должен быть только один столбец.
ANYitemNumber = ANY (табличный подзапрос)Другое классифицированное сравнение, но в этом случае результат TRUE, если условие истинно для любого из столбцов. SOME можно использовать в качестве синонима ANY. Табличный подзапрос может возвращать несколько строк, но у них должен быть только один столбец.

Используя информацию из таблицы 2, можно заметить, что если повторно записать предложение WHERE в операторе UPDATE, представленное в листинге 4, для использования классифицированного сравнения, и использовать тот же табличный подзапрос WHERE itemNumber = ANY (...), то результат будет таким же. Если использовать оператор ALL и тот же табличный подзапрос, строки не будут обновляться, поскольку все значения itemNumber в таблице bigdog.products не находятся в табличном подзапросе. С другой стороны, если использовать оператор EXISTS, изменяются все строки, поскольку хотя бы одно из значений itemNumber имеется в табличном подзапросе.


Изменение схемы таблиц

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

Листинг 6. Обновление таблицы
ij> CREATE TABLE bigdog.newProducts (
    itemNumber INT NOT NULL,
    price DECIMAL(5, 2),
    stockDate DATE,
    count INT NOT NULL DEFAULT 0,
    description VARCHAR(40)
) ;
0 rows inserted/updated/deleted
ij> INSERT INTO bigdog.newProducts(itemNumber, price, stockDate, description) 
    SELECT itemNumber, price, stockDate, description FROM bigdog.products ;
5 rows inserted/updated/deleted
ij> DROP TABLE bigdog.products ;
0 rows inserted/updated/deleted
ij> RENAME TABLE bigdog.newProducts TO products ;
0 rows inserted/updated/deleted
ij> SELECT * FROM bigdog.products ;
ITEMNUMBER |PRICE   |STOCKDATE |COUNT      |DESCRIPTION                             
------------------------------------------------------------------------------------
4          |29.95   |2006-02-10|0          |Male bathing suit, blue                 
5          |49.95   |2006-02-20|0          |Female bathing suit, one piece, aqua    
6          |12.43   |2006-06-20|0          |Child sand toy set                      
9          |14.24   |2006-03-12|0          |NEW: Flip-flop                          
10         |38.44   |2006-01-24|0          |NEW: Open-toed sandal                   

5 rows selected

Как показано в этом примере, для изменения таблицы (в данном случае для добавления нового столбца count к таблице bigdog.products), сначала создается таблица с требуемой схемой. В данном примере требуется, чтобы всегда имелось допустимое значение. Для этого включено ограничение столбца NOT NULL и назначено значение по умолчанию 0 для столбца count. Это выполняется с помощью ограничения столбца DEFAULT 0. Обратите внимание, как можно объединить несколько ограничений столбцов с помощью последовательного перечисления.

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

После создания новой таблицы и копирования в нее соответствующих данных, удалите старую таблицу с помощью оператора SQL DROP TABLE и переименуйте новую таблицу по имени исходной с помощью оператора SQL RENAME TABLE. Операция переименования довольно проста: Переименуйте таблицу oldTableName в newTableName, но не задавайте имя схемы для новой таблицы, поскольку операция RENAME не может перемещать таблицу в другую схему базы данных. В данном примере в завершении оператор SELECT используется для отображения схемы и содержимого новой таблицы bigdog.products. Как можно видеть, в новой таблице пять столбцов, значение в столбце count всегда равно нулю. В данный момент реальное приложение может изменить столбец count с помощью соответствующих операторов SQL UPDATE.


Заключение

В данной статье рассматривается исключительно изменение данных в базе данных Apache Derby. Первый рассмотренный метод изменения данных заключался в удалении данных и выполнялся с помощью оператора SQL DELETE. Затем оператор SQL UPDATE использовался для изменения значений столбцов в выбранных строках таблицы. Наконец, для изменения структуры данных существующей таблицы использовалась временная таблица. В статье также показано, как изменять сложную схему базы данных с помощью встроенных подзапросов. В следующей статье описывается несколько оставшихся дополнительных тем по базам данных, затем будет показано, как подключиться к базе данных Apache Derby из Java-приложения.

Ресурсы

Научиться

Получить продукты и технологии

  • Загрузите последнюю версию Apache Derby;
  • Добавьте новизны в ваши следующие проекты разработки при помощи пробного ПО IBM, доступного в виде файлов для загрузки и на DVD.

Обсудить

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Open source, Information Management
ArticleID=208464
ArticleTitle=Разработка при помощи Apache Derby -- тройной выигрыш: Разработка баз данных при помощи Apache Derby, Часть 6
publish-date=04102007