SELECT Name, UnitPrice
FROM tblItem
WHERE (UnitPrice > (SELECT AVG(UnitPrice) FROM tblItem)
В этом случае оказывается, что запрос и подзапрос обращаются к одной и той же таблице, но это не принципиально. Подзапросы могут делать запросы к любой таблице в базе данных, главное – чтобы они возвращали одиночное значение.
Приведенная выше инструкция SQL возвращает следующий результирующий набор:
Name UnitPrice Rubber Chicken 5.99 Disappearing Penny Magic Trick 3.99 Loaded Dice 3.49 Whoopee Cushion 5.99
Манипулирование данными с помощью SQL
Команда манипулирования данными (data manipulation command) — это команда SQL, которая изменяет записи. Такие команды создаются на языке манипулирования данными DML, который является подмножеством языка SQL. Эти команды не возвращают записи, а только изменяют их в базе данных.
DML-команды SQL обычно применяются для изменения большого объема данных на основе заданного критерия. Например, для повышения на 10% цены всех товаров следует использовать запрос на обновление, который автоматически выполнит такие изменения для всех товаров.
В среде Visual Studio .NET предусмотрен очень мощный интерфейс для выполнения DML-команд. Действительно, инструменты среды Visual Studio .NET могут пре
доставить полезную информацию (например, правильную строку подключения для соединения с базой данных) или генерировать в окне конструктора основные DML-команды при извлечении данных из таблицы или изменении типа запроса.
НА ЗАМЕТКУ
Примеры в этом разделе демонстрируют способы изменения данных в базе данных Novelty. Если после многочисленных попыток изменить данные вы хотите вернуть базу данных Noveltу в ее прежнее состояние, то ее можно переустановить, запуская описанный во введении к этой книге сценарий.
На низком уровне (т.е. не на уровне графического интерфейса пользователя) DML-команды SQL можно использовать с помощью следующих двух инструментов:
• Microsoft SQL Server Query Analyzer (или просто Query Analyzer) — инструмент с графическим интерфейсом пользователя для создания запросов и команд для SQL Server;
• osql — используемый в режиме командной строки процессор запросов.
Вы можете использовать любой из этих инструментов, а в данной главе применяется Query Analyzer, который обладает более широкими возможностями и более удобен в употреблении, чем процессор запросов osql. В настоящей главе основное внимание сосредоточено на фактически выполняемых командах, а не на методах использования графического интерфейса Query Analyzer. Инструмент Query Analyzer находится в группе программ Microsoft SQL Server. (В главе 7, "ADO.NET: дополнительные компоненты", более подробно рассматриваются способы применения DML-команд в среде Visual Studio.NET.)
Запросы на обновление
Запрос на обновление может изменить сразу целую группу записей. Этот запрос состоит из трех частей:
• предложение UPDATE, которое указывает на обновляемую таблицу;
• предложение SET, задающее данные для обновления;
• необязательный критерий WHERE, ограничивающий число записей, на которые воздействует запрос на обновление.
Например, чтобы увеличить цену на все товары, воспользуйтесь запросом на обновление, код которого приведен ниже.
UPDATE tblItem
SET Price = Price * 1.1
SELECT * FROM tblItem
Команда SELECT, которая располагается вслед за предложением UPDATE, не обязательна и предназначена для просмотра результатов обновления.
Ниже приведены значения полей после выполнения данного запроса на обновление.
ID Name Description UnitPrice Price 1 Rubber Chicken A classic laugh getter 2.0300 6.5890 2 Hand Buzzer Shock your friends .8600 1.5290 3 Stink Bomb Perfect for ending boring meetings .3400 1.4190 4 Invisible Ink Write down your most intimate thoughts 1.4500 2.5190 5 Loaded Dice Not for gambling purposes 1.4600 3.8390 6 Whoopee Cushion The ultimate family gag 2.0300 6.5890
Для ограничения числа записей, подвергаемых воздействию запроса на обновление, достаточно добавить в запрос SQL предложение WHERE. Например, чтобы применить повышение цен только к дорогим товарам, стоимость которых больше $100, откорректируйте запрос так, как показано ниже.
UPDATE
SET Price = Price * 1.1
WHERE Price > 100
Эта команда увеличивает на 10% цену на товары, текущая цена которых больше $100.
Запросы на удаление
С помощью запроса на удаление (delete query) можно одним махом удалить одну или несколько записей. Например, чтобы удалить все заказы, которые были оформлены до (но не во время) последнего празднования Дня всех святых, воспользуйтесь запросом SQL, код которого приведен ниже.
DELETE *
FROM tblOrder
WHERE OrderDate < '10/31/2002'
Запрос на добавление записей
Запрос на добавление (append query) используется в двух случаях:
• при добавлении одиночной записи в таблицу;
• при копировании одной или нескольких записей из одной таблицы в другую.
Для создания запроса на добавление используйте предложение SQL INSERT. Точный синтаксис запроса зависит от того, добавляете ли вы одну запись или копируете несколько. Например, для добавления одной новой записи в tblOrder можно использовать приведенный ниже запрос.
INSERT INTO tblOrder(CustomerID, OrderDate)
VALUES (119, '6/16/2001')
При выполнении этого запроса в таблице tblOrder создается новый заказ для клиента с идентификационным номером 119 и датой 16 июня 2001 года.
НА ЗАМЕТКУ
В этом запросе на добавление для поля ID таблицы tblOrder не предлагается никакого значения, так как это поле идентификации. Попытка пользователя присвоить значение этому полю приведет к возникновению ошибки, поскольку его может сгенерировать только сам процессор баз данных.
Для создания разновидности запроса на добавление, который копирует записи из одной таблицы в другую, используйте предложение INSERT вместе с предложением SELECT. Предположим, вместо удаления старых заказов вы архивируете их, периодически копируя в архивную таблицу tblOrderArchive, которая имеет ту же структуру, что и tblOrder. Для выполнения этой работы необходимо сначала создать таблицу tblOrderArchive со структурой, аналогичной tblOrder.
CREATE TABLE tblOrderArchive (
ID [int] NOT NULL,
CustomerID [int] NULL,
OrderDate [datetime] NULL)
НА ЗАМЕТКУ
Как уже сообщалось выше, команды SQL для создания и управления структурой базы данных называются командами манипулирования данными или DML-командами. Более подробно они рассматриваются далее в главе.
Ниже приведена инструкция SQL для копирования старых записей из tblOrder в tblOrderArchive.
INSERT INTO tblOrderArchive
SELECT * FROM tblOrder
WHERE OrderDate < '6/1/2001'
При выполнении этой инструкции SQL в таблицу tblOrderArchive копируются все записи, содержащие заказы, которые были оформлены до 1 июня 2001 года.
Запросы на основе команды SELECT INTO
Запрос на основе команды SELECT INTO аналогичен запросу на добавление, за исключением того, что он создает новую таблицу и сразу же копирует в нее записи. В Microsoft Access он называется запросом на создание таблиц (make-table query). Так, в предыдущем примере все записи из таблицы tblOrder копировались в таблицу tblOrderArchive, исходя из предположения, что таблица tblOrderArchive уже существует. Вместо этого запроса для копирования тех же записей в новую таблицу с такой же структурой, как и у оригинала, воспользуйтесь приведенным ниже запросом SQL.
SELECT * INTO tblOrderArchive
FROM tblOrder
НА ЗАМЕТКУ
Этот запрос копирует все записи из tblOrder в новую таблицу с именем tblOrderArchive. Однако если такая таблица уже существует, эта команда не будет выполнена. Это отличается от результата выполнения данного запроса в Microsoft Access.
