Компьютерные технологии подготовки текстовых документов, обработки экономической информации на основе табличных процессоров, использования систем управления базами данных (СУБД), интегрированных программных пакетов.
Обработка информации средствами структурированного языка запросов SQL.
1. Добавление данных
2. Удаление данных
3. Обновление данных
1. Добавление данных
SQL представляет собой полноценный язык, предназначенный для работы с данными и позволяющий не только извлекать информацию из базы данных, выполняя запросы на выборку, но и изменять содержащуюся в ней информацию с помощью запросов на добавление, удаление и обновление. По сравнению с инструкцией SELECT, с помощью которой выполняются запросы на выборку, инструкции SQL, изменяющие содержимое базы данных, являются более простыми. Однако при изменении содержимого базы данных к СУБД предъявляется ряд дополнительных требований. При внесении изменений СУБД должна сохранять целостность данных и разрешать ввод в базу данных только допустимых значений, а также обеспечивать непротиворечивость базы данных даже в случае системной ошибки. Помимо этого, СУБД должна обеспечивать возможность одновременного изменения базы данных несколькими пользователями таким образом, чтобы они не мешали друг другу.
Добавление новой строки в реляционную базу данных происходит тогда, когда во "внешнем мире" появляется новый объект, представляемый этой строкой. На примере учебной базы данных это выглядит следующим образом:
• если принимается на работу новый служащий, в таблицу salesreps необходимо добавить новую строку с данными о нем;
• если служащий заключает договор с новым клиентом, в таблицу customers должна быть добавлена новая строка, представляющая этого клиента;
• если клиент делает заказ, в таблицу orders требуется добавить новую строку, содержащую информацию об этом заказе.
Во всех приведенных примерах новая строка добавляется для того, чтобы база данных оставалась точной моделью реального мира. Наименьшей единицей информации, которую можно добавить в реляционную базу данных, является одна строка. В реляционной СУБД существует три способа добавления новых строк в базу данных:
• Однострочная инструкция INSERT позволяет добавить в таблицу одну новую строку;
• Многострочная инструкция INSERT обеспечивает извлечение строк из одной части базы данных и добавление их в другую таблицу. Она обычно используется в конце месяца или года, когда "старые" строки таблицы пересылаются в неиспользуемую таблицу для сохранения;
• Утилита пакетной загрузки служит для добавления в таблицу данных из внешнего файла. Эта утилита обычно используется для первоначального наполнения базы данных, а также для загрузки данных, либо содержащихся в другой компьютерной системе, либо собранных из различных источников.
Однострочная инструкция INSERT добавляет в таблицу новую строку. В предложении INTO указывается таблица (целевая таблица), в которую добавляется новая строка, а в предложении VALUES содержатся значения данных для новой строки. Список столбцов определяет какие значения в какой столбец заносятся.
Пример.
|
Имя Возраст Идентификатор Должность Офис Дата приема Личный план Объем продаж на текущую дату |
Генри Якобсен 36 111 Менеджер по продажам Атланта (идентификатор офиса 13) 25 июля 1990 года Еще не установлен $0.00
|
Добавить информацию о новом служащем Генри Якобсене.
INSERT INTO salesreps (name, age, empl_num, sales, title,
hire_date, rep_office)
VALUES ('Henry Jacobsen', 36, 111, 0.00, 'Sales Mgr', '25-JUL-90', 13)
Добавление информации в БД для служащего Якобсена – самостоятельно
Служащий Якобсен получает свой первый заказ от компании Inter Corp, нового клиента, которому присвоен идентификатор 2126. Это заказ на 20 изделий ACI-41004 общей стоимостью $2340, и ему присваивается номер 113069.
INSERT INTO CUSTOMERS (COMPANY, CUST_NUM, CREDIT_LIMIT, CUST_REP) VALUES ('InterCorp', 2126, 15000.00, 111)
INSERT INTO ORDERS (AMOUNT, MFR, PRODUCT, QTY, ORDER_DATE,
ORDER_NUM, CUST, REP)
VALUES (2340.00, 'ACI', '41004', 20, CURRENT DATE, 113069, 2126, 111)
Current date ® date ( ) – для ACCESS.
В инструкции INSERT в предложении VALUES можно использовать системную константу current date, что обеспечивает ввод текущей даты в качестве даты получения заказа. Эта системная константа определена в стандарте SQL2.
Инструкцию INSERT можно использовать в интерактивном режиме для добавления строк в таблицы, которые изменяются очень редко. Однако на практике данные о новом клиенте, заказе или служащем почти всегда добавляются в базу данных с помощью программ ввода данных, использующих специальные формы. После окончания ввода данных в форму приложение добавляет новую строку данных с помощью программного SQL. Независимо от того, какой SQL используется, интерактивный или программный, инструкция INSERT имеет один и тот же вид.
Как правило, в инструкции INSERT указывается простое имя таблицы, определяющее собственную таблицу пользователя. Чтобы вставить данные в таблицу, созданную другим пользователем, необходимо указать ее полное имя. Кроме того, необходимо иметь разрешение на ввод данных в эту таблицу.
Список столбцов в инструкции INSERT служит для того, чтобы установить соответствие между значениями данных, содержащимися в предложении VALUES, и столбцами, для: которых эти данные предназначены. Списки значений и столбцов должны содержать одинаковое число элементов, а тип данных каждого значения должен быть совместимым с типом соответствующего столбца, иначе произойдет ошибка. Стандарт ANSI/ISO требует, чтобы в списке столбцов использовались простые имена, но во многих СУБД допускаются и полные. Ясно, что в именах столбцов в любом случае не может быть неоднозначности, потому что все они являются ссылками на столбцы целевой таблицы.
При добавлении в таблицу новой строки всем столбцам, имена которых отсутствуют в списке столбцов инструкции INSERT, автоматически присваивается значение NULL.
Для удобства в SQL разрешается не включать список столбцов в инструкцию INSERT. Если список столбцов опущен, он генерируется автоматически и в нем слева направо перечисляются все столбцы таблицы.
Многострочная инструкция INSERT добавляет в целевую таблицу несколько строк (более одной). В этой разновидности инструкции INSERT значения для новых строк явно не задаются. Источником новых строк служит запрос на выборку, содержащийся внутри инструкции INSERT.
Процедура добавления строк со значениями, взятыми из той же базы данных, может сперва показаться странной, но иногда оказывается необходимой. Предположим, что нам требуется скопировать номера, даты и стоимости всех заказов, сделанных до 1 января 1990 года, из таблицы orders в другую таблицу с именем oldorders. Многострочная инструкция INSERT позволяет скопировать данные компактно и быстро:
Скопировать старые заказы в таблицу oldorders.
INSERT INTO oldorders (order_num, order_date, amount) SELECT order_num, order_date, amount
FROM orders
WHERE order_date < '01-jan-90'
Хотя многострочная инструкция INSERT выглядит сложнее однострочной, в действительности она является очень простой. В ней, как и в однострочной инструкции INSERT, задаются таблица и столбцы, в которые заносятся новые элементы данных. Оставшаяся часть инструкции представляет собой запрос, извлекающий данные из таблицы с помощью инструкции SELECT.
Многострочную инструкцию INSERT можно также использовать, если, например, требуется проанализировать, что именно приобретают клиенты, и для этого необходимо просмотреть информацию о клиентах и служащих, имеющих большие заказы – стоимостью свыше $15000. Запросы, которые необходимо для этого выполнить, объединяют информацию из таблиц customers, salesreps и orders. В учебной базе данных эти трехтабличные запросы будут выполняться довольно быстро, но в реальной корпоративной базе данных, содержащей тысячи строк информации, выполнение таких запросов заняло бы длительное время.
Вместо выполнения нескольких длинных трехтабличных запросов лучше создать для требуемых данных новую таблицу с новым именем и имеющую новую структуру, соответствующую запросу. Следует отметить, что запрос с использованием инструкции INSERT будет выполняться намного быстрее, чем при использовании объединения инструкций SELECT.
На запрос, содержащийся внутри многострочной инструкции INSERT, стандарт SQL1 накладывает несколько логических ограничений:
§ в запрос нельзя включать предложение ORDER BY. He имеет смысла сортировать таблицу результатов запроса, поскольку она добавляется в таблицу, которая, как и все остальные, не упорядочена;
§ таблица результатов запроса должна содержать количество столбцов, равное длине списка столбцов в инструкции INSERT (или полностью всю целевую таблицу, если список столбцов опущен), а типы данных соответствующих столбцов таблицы результатов запроса и целевой таблицы должны быть совместимыми;
§ запрос не может быть запросом на объединение (UNION) нескольких различных инструкций SELECT.
§ имя целевой таблицы инструкции INSERT не может присутствовать в предложении FROM запроса на выборку или любого запроса, вложенного в него. Тем самым запрещается добавление таблицы самой к себе.
Первые два ограничения очевидны, а последние два были добавлены только для того, чтобы избежать излишней сложности инструкции INSERT. Как результат, в стандарте SQL2 они были ослаблены, и в запросе допускаются операции UNION и JOIN; разрешается также "самодобавление".
Утилиты пакетной загрузки.
Часто возникает необходимость загрузить в базу данных информацию из другого компьютера или из файла, в который она была собрана из различных источников. Для загрузки данных в таблицу можно было бы написать программу с циклом, в котором из файла считывается одна запись, а затем с помощью однострочной инструкции INSERT эта запись добавляется в таблицу. Однако «накладные расходы», связанные с циклическим выполнением однострочной инструкции INSERT, могут оказаться очень высокими. Если принять, что в типичном случае ввод одной строки занимает полсекунды, то для интерактивного режима это, по-видимому, допустимое быстродействие. Но если необходимо загрузить 50000 строк данных, то такое быстродействие неприемлемо. Загрузка данных в этом случае заняла бы свыше шести часов. По этой причине во всех коммерческих СУБД имеются средства пакетной загрузки, которые с высокой скоростью загружают данные из файла в таблицу. В стандарте SQL этот тип загрузки не упоминается, и обычно она осуществляется автономными служебными утилитами без участия SQL. Утилиты различных поставщиков СУБД немного отличаются набором функций, команд и свойств.
2. Удаление данных
Удалять ту или иную строку из реляционной базы данных приходится тогда, когда объект, представляемый этой строкой, исчезает из "внешнего мира". Скажем, в случае учебной базы возможны следующие варианты:
• если клиент отменяет заказ, необходимо удалить соответствующую строку из таблицы orders;
• если служащий увольняется из компании, должна быть удалена соответствующая строка из таблицы salesreps;
• если ликвидируется офис, необходимо удалить соответствующую строку из таблицы offices; в случае, когда служащие этого офиса увольняются, их строки в таблице salesreps также должны быть удалены; если служащие переводятся в другой офис, то соответствующие значения в столбце office необходимо обновить.
Наименьшей единицей информации, которую можно удалить из реляционной базы данных, является одна строка.
Инструкция DELETE предназначена для удаления выбранной строки из одной таблицы.
В предложении FROM указывается таблица, содержащая строки, которые требуется удалить. В предложении WHERE указывается критерий отбора строк, которые должны быть удалены.
Пример.
Удалить информацию о Генри Якобсоне
DELETE FROM salesreps
WHERE name = 'Henry Jacobsen'
Задание условия удаления строки аналогично заданию условия отбора строк в инструкции SELECT. Причем также как и в инструкции SELECT в инструкции DELETE возможно удаление нескольких строк, соответствующих критерию отбора.
Хотя предложение WHERE в инструкции DELETE является необязательным, оно присутствует почти всегда. Если же оно отсутствует, то удаляются все строки целевой таблицы. Например:
Удалить все заказы.
DELETE FROM orders
Хотя в результате выполнения приведенной инструкции DELETE таблица ORDERS становится пустой, из базы данных она не удаляется. Определение таблицы ORDERS и ее столбцов остается в базе данных. Таблица по-прежнему существует, и в нее по-прежнему можно добавлять новые строки с помощью инструкции INSERT. Чтобы удалить из базы данных определение таблицы, необходимо использовать инструкцию DROP TABLE.
Инструкция DELETE несет в себе потенциальную угрозу удаления необходимых строк, поэтому всегда следует задавать условие отбора и обращать внимание на то, отбирает ли оно действительно ненужные строки. Желательно вначале проверить предложение WHERE в интерактивном режиме в составе инструкции SELECT и отобразить выбранные строки на экране. Убедившись, что это именно те строки, которые требуется удалить, можно использовать предложение WHERE в инструкции DELETE.
Инструкции DELETE с простыми условиями отбора отбирают строки для удаления исключительно на основании содержимого этих строк. Но иногда отбор строк необходимо производить, опираясь на данные из других таблиц. Предположим, необходимо удалить все заказы, принятые служащей Сью Смит. Не зная ее идентификатора, невозможно найти ее заказы, пользуясь одной только таблицей ORDERS. Чтобы найти эти заказы, можно было бы воспользоваться запросом к двум таблицам:
Найти заказы, принятые Сью Смит.
SELECT order_num, amount
FROM orders, salesreps
WHERE rep = empl_num AND name = 'Sue Smith'
Однако в инструкции DELETE запрещено использовать объединение таблиц.
Чтобы выполнить поставленную задачу, необходимо использовать условие отбора с подчиненным запросом.
DELETE FROM orders
WHERE rep = (SELECT empl_num
FROM salesreps
WHERE name = `Sue Smith`)
Подчиненный запрос находит идентификатор Сью Смит, а затем предложение WHERE отбирает заказы с данным идентификатором. Как видно из этого примера, подчиненные запросы в инструкции DELETE играют важную роль, поскольку они позволяют удалять строки, основываясь на информации, содержащейся в других таблицах.
Подчиненные запросы в предложении WHERE могут иметь несколько уровней вложенности. Они могут также содержать внешние ссылки на целевую таблицу инструкции DELETE. В этом случае предложение FROM инструкции DELETE играет такую же роль, как и предложение FROM инструкции SELECT.
3. Обновление данных
Инструкция UPDATE обновляет значения одного или нескольких столбцов в выбранных строках одной таблицы. В инструкции указывается целевая таблица, которая должна быть модифицирована, при этом пользователь должен иметь разрешение на обновление таблицы и каждого конкретного столбца. Предложение WHERE отбирает строки таблицы подлежащие обновлению. В предложении SET указывается, какие столбцы должны быть обновлены, и для них задаются новые значения.
Пример.
Увеличить лимит кредита для компании Acme Manufacturing до $60000 и закрепить ее за Мэри Джонс (идентификатор 109).
UPDATE customers
SET credit_limit = 60000.00, cust_rep = 109 WHERE company = 'Acme Mfg'.
Инструкция UPDATE выполняется таким образом: все строки указанной таблицы по очереди проверяются на соответствие условию отбора. Строки, для которых условие отбора выполняется (результат проверки имеет значение TRUE), обновляются, а строки, для которых условие не выполняется (результат проверки имеет значение FALSE или NULL), не обновляются. Поскольку инструкция UPDATE данного типа производит в таблице поиск строк, она иногда называется поисковой. Инструкция update другого типа, всегда обновляющая одну строку, называется позиционной. Позиционная инструкция update применяется только в программном SQL.
Предложение set в инструкции update представляет собой список операций присваивания, отделяемых друг от друга запятыми. В каждой операции идентифицируется целевой столбец, который должен обновляться, и определяется новое значение для этого столбца. Каждый целевой столбец должен встречаться в списке только один раз; не должно быть двух операций присваивания для одного и того же целевого столбца. Согласно стандарту ANSI/ISO, для целевых столбцов необходимо использовать простые имена, но некоторые СУБД допускают использование полных имен столбцов. Так как эти имена являются ссылками на столбцы целевой таблицы, то в любом случае неоднозначность невозможна.
Выражение в операции присваивания может быть любым допустимым SQL-выражением, результирующее значение которого имеет тип данных, соответствующий целевому столбцу. Необходимо, чтобы значение выражения вычислялось на основе значений строки, которая в данный момент обновляется в целевой таблице. Оно не может включать в себя какие-либо статистические функции или подчиненные запросы.
Если выражение в операции присваивания содержит ссылку на один из столбцов целевой таблицы, то для вычисления выражения используется значение этого столбца в текущей строке, которое было перед обновлением. То же самое справедливо для ссылок на столбцы в предложении WHERE.
Предложение WHERE в инструкции UPDATE является необязательным. Если оно опущено, то обновляются все строки целевой таблицы. Например:
Увеличить все личные планы на пять процентов.
UPDATE salesreps
SET quota = 1.05 * quota
В отличие от инструкции DELETE, в которой предложение WHERE практически никогда не опускается, инструкция UPDATE и без предложения WHERE выполняет полезную функцию. Она применяется в основном для обновления всей таблицы.
В инструкции UPDATE, так же как и в инструкции DELETE, подчиненные запросы могут играть важную роль, поскольку они дают возможность отбирать строки для обновления, опираясь на информацию из других таблиц. Например, использование инструкции UPDATE, в которой используются подчиненные запросы:
Увеличить на $5000 лимит кредита для тех клиентов, которые сделали заказ на сумму более $25000.
UPDATE customers
SET credit_limit = credit_limit + 5000.00
WHERE cust_num IN (SELECT DISTINCT cust
FROM orders
WHERE amount > 25000.00)
Подчиненные запросы в предложении WHERE инструкции UPDATE, так же как и в инструкции DELETE, могут иметь любой уровень вложенности и содержать внешние ссылки на целевую таблицу инструкции UPDATE. Имя столбца empl_num в подчиненном запросе предыдущего примера является такой внешней ссылкой; она относится к столбцу empl_num той строки таблицы salesreps, которая проверяется в настоящий момент инструкцией UPDATE. Подчиненный запрос в этом примере является коррелированным.
Внешние ссылки часто встречаются в подчиненных запросах инструкции UPDATE, поскольку они реализуют "объединение" таблиц (таблицы) подчиненного запроса и целевой таблицы инструкции UPDATE. Для инструкции UPDATE справедливо то же самое ограничение стандарта SQL1, что и для инструкции DELETE: имя целевой таблицы не может присутствовать в предложении FROM подчиненного запроса на любом уровне вложенности. Это предотвращает ссылки из подчиненных запросов на целевую таблицу (часть строк которой уже может быть модифицирована). Таким образом, все ссылки в подчиненных запросах на целевую таблицу являются внешними ссылками на ту строку целевой таблицы, которая проверяется в данный момент предложением WHERE инструкции UPDATE. В стандарте SQL2 это ограничение также устраняется и дается определение, что ссылка на целевую таблицу в подчиненном запросе считается ссылкой на исходную целевую таблицу, в которой еще не были сделаны какие-либо обновления