Використання операторів INSERT, UPDATE і DELETE

Іначітельная частина роботи багатьох адміністраторів баз даних і адміністраторів SQL Server складається в забезпеченні поточної роботи бази даних. Так само як додавання, зміна та видалення даних з таблиць. Запити на T-SQL дозволяють легко виконувати ці завдання. Потрібно тільки використовувати пропозиції INSERT, UPDATE і DELETE. Перед тим як я покажу, як застосовувати ці пропозиції, згадаємо кілька речей, які потрібно зробити до початку роботи.

попередні умови

Приклади пропозицій INSERT, UPDATE і DELETE, які я використовую в цій статті, запускаються на простий базі даних MyDB, що містить кілька тимчасових таблиць і таблицю MyTable. Таблиця MyTable використовується для зберігання назв різних часових таблиць. Єдина мета тимчасових таблиць - заповнити простір. Просто коли вони присутні, вони забезпечують наявність даних в таблиці sysobjects, яка використовується в якості джерела даних для вставки даних в MyTable.

Припускаючи наявність повноважень, необхідних для створення баз даних і таблиць, для запуску пропозицій, що модифікують дані, можна створити MyDB, MyTable і тимчасові таблиці за допомогою файлів MyDB.sql і MyTable.sql. Ці запити можна побачити в лістингу 1 і 2.

Для запуску запиту відкрийте вікно запиту або в SQL Server 2005 SQL Server Management Studio (далі SSMS), або в SQL Server 2000 Query Analyzer і скопіюйте код MyDB.sql в вікно. У двох записах FILENAME замініть C: Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAMyDB.mdf на шлях на наявному сервері. Виконайте запит для створення MyDB. Для розробки MyTable і тимчасових таблиць скопіюйте код в MyTable.sql в вікно запиту і виконайте його.

Перед тим як приступати до роботи з даними будь-якої таблиці, потрібно ознайомитися з її структурою. Для цього відкриваємо таблицю MyTable і виконуємо наступні дії.

Додавання запису

Стандартна пропозиція INSERT, яке додає одиночну запис (або рядок) в таблицю, складається з трьох частин і має вигляд

INSERT INTO Part1 (Part2) VALUES (Part3)

У частині 1 визначена цільова таблиця, яка буде містити новий запис. У частині 2 перераховуються назви полів в цільовій таблиці, для якої є дані. Потрібно укласти перелік в круглі дужки і використовувати коми для відділення назв полів. У частині 3 забезпечується додавання даних. Коли треба буде встановити фактичні значення даних, використовується параметр VALUES і наступні за ним значення. Потрібно укласти перелік значень даних в круглі дужки і відокремлювати значення комами. Якщо це значення символьне, на кшталт рядка або дати, його також потрібно укласти в одинарні лапки ( ''). Наприклад, виконання наступного речення вставляє значення даних TestTable і 2007-09-22 в поля ObjectName і Creation-Date таблиці MyTable, відповідно:

INSERT INTO MyTable (ObjectName, CreationDate) VALUES ( 'TestTable', '007-09-22')

Ті, хто вже знає мої прийоми роботи з таблицею MyTable, можливо, помітили, що я просто встановив два або три поля в пропозицію INSERT. Перше поле - це поле ідентифікатора, яке має спеціальне призначення. Дані цього поля додаються автоматично, і його значення збільшуються з кожною новою вставленої записом. Це важлива властивість SQL Server, про який потрібно знати. Щоб дізнатися більше про типи ідентифікаційних даних, загляньте в SQL Server Books Online (BOL) в розділ IDENTITY (Властивість) на msdn2.microsoft.com/en-us/library/ms186775.aspx.

Щоб переглянути знову вставлену запис, виконайте наступну пропозицію:

SELECT * FROM MyTable

На малюнку 1 показано, як виглядає результат.

Додавання результатів пропозицією SELECT

Я вже показував, як отримати дані з бази даних, використовуючи пропозицію SELECT. Можна задіяти оператор INSERT для збереження даних, витягнутих пропозицією SELECT, в таблицю. Як і оператор INSERT для окремого запису, оператор INSERT, який зберігає результати пропозиції SELECT, має три частини: задана база даних (частина 1), назви полів (частина 2) і дані (частина 3). Проте замість параметра VALUES для визначення актуальних значень даних в частині 3 використовується пропозицію SELECT, яке отримує дані з іншого джерела. Наприклад, коли виконується до бази даних MyDB, запит

INSERT INTO MyTable (ObjectName, CreationDate) SELECT name, crdate FROM sysobjects WHERE type = 'U' ORDER BY name

вставляє в нову таблицю MyTable записи, що містять назву і дату розробки всіх призначених для користувача таблиць в MyDB, упорядкованих в алфавітному порядку відповідно до назв. Таким чином, припускаючи, що виконано пропозицію INSERT з одиночної записом, описане раніше, результати цього запиту повинні виглядати так само, як і результати, показані на малюнку 2, з двома винятками. Значення CreationDate для MyTable і тимчасових таблиць будуть містити дату і час запуску MyTable.sql. Друге поле ObjectName буде ширше. Я скоротив його через нестачу місця.

На малюнку 2 TestTable - тільки перший запис. Ім'я умови ORDER BY тільки звертається до нових записів, які вставляє в даний момент оператор SELECT. Таблиця TestTable була попередньо доповнена за допомогою оператора, що складається з одиничною записи оператора INSERT.

Можна використовувати будь-який оператор SELECT, до тих пір поки типи даних в полях, перерахованих в частині 2 оператора INSERT, відповідають полях, визначених у реченні SELECT. Це дає можливість зібрати всі типи даних. Проте використовуйте нове знання з обережністю, щоб не вставити мільйони записів на зайнятий сервер або на сервер з обмеженим дисковим простором.

модернізація даних

Тепер, коли ми вміємо додавати дані, давайте подивимося, як працює оператор UPDATE. Простий оператор UPDATE зазвичай складається з трьох частин:

UPDATE Part1 SET Part2 WHERE Part3

У частині 1 визначена цільова таблиця. У частині 2 визначені поля, які повинні бути змінені, разом з новими даними для кожного поля. Частина 3 не є обов'язковою, але в більшості випадків вона дуже важлива. Тут встановлюється фільтр, який використовує оператор WHERE. Якщо оператор WHERE не встановлено, буде змінена кожна одинична запис в таблиці. Наприклад, запит

UPDATE MyTable SET CreationDate = '2007-09-23'

змінює значення CreationDate для кожного запису в таблиці MyTable, як показано на малюнку 3. На малюнку видно, що значення CreationDate - це 2007-09-23 00: 00: 00.000, а не 2007-09-23, як зазначено в запиті. Так як тип даних поля CreationDate визначено як дата і запит не вказує тимчасову частину, SQL Server допускає, що мається на увазі опівночі, і додає 00: 00: 00.000.

Тепер використовуємо оператор WHERE для зміни полів ObjectName і CreationDate обраної записи:

UPDATE MyTable SET ObjectName = 'PartyTime', CreationDate = '1999-12-31 23:00:00' WHERE TableID = 1

Як показано на малюнку 4, тільки перший запис (встановлена ​​за допомогою WHERE TableID = 1) змінена з новою назвою таблиці PartyTime і новою датою розробки 1999-12-31 23:00:00.

видалення даних

Кожен адміністратор баз даних побоюється, що пропозиція DELETE потрапить не в ті руки. Хоча воно може бути використано неправильно, воно відіграє важливу роль, коли запити застосовуються для зміни даних в таблицях. Типове пропозиція DELETE зазвичай складається з двох частин:

DELETE Part1
WHERE Part2

У частині 1 визначається цільова таблиця. У частині 2 визначається фільтр, який використовує оператор WHERE. Як і оператор WHERE в реченні UPDATE, умова WHERE в вираженні DELETE необов'язково, але в більшості випадків необхідно. Якщо не буде включений оператор WHERE і фільтр, будуть видалені всі записи в заданій таблиці.

Наприклад, потрібно видалити з таблиці MyTables всі записи з датою розробки раніше 22 вересня 2007 року.

запит

DELETE MyTable WHERE CreationDate> '2007-09-22'

Приведе до результату, показаному на малюнку 5. Потрібно бути уважним під час зміни даних за допомогою пропозицій DELETE або UPDATE. Оператор WHERE використовується завжди, крім випадку, коли треба видалити з таблиці всі дані.

Якщо ви не впевнені, скільки записів потрібно видалити, виконується пропозицію SELECT з тим же оператором WHERE. Замість визначення полів в реченні SELECT задається оператор COUNT (*), який повертає кількість рядків, які повинні бути видалені. Наприклад, щоб перевірити результат останнього виразу з пропозицією DELETE, можна виконати:

SELECT COUNT (*) FROM MyTable WHERE CreationDate> '2007-09-22'

Якщо результуючий ряд обчислюється в мільйонах, подумайте, чи треба деталізувати оператор WHERE. Я розповім про використання функції COUNT в наступній статті, тому не варто турбуватися, якщо не вийде застосувати це в роботі.

Вивчіть свої дані

Якщо ви впевнені в тому, що тепер зможете змінювати дані в MyTable, спробуйте виконати оператори INSERT, UPDATE і DELETE в існуючій базі даних на тестовому сервері. Для цього скопіюйте код із запиту ExistingDatabaseQuery.sql (див. Лістинг 3) в вікно запиту, помістіть MyDB в першому рядку до назви існуючої бази даних і виконайте запит.

Потім можна переробити приклади з пропозицій INSERT, UPDATE і DELETE до наявної конфігурації і виконати їх. Таким чином ви дізнаєтеся, як краще використовувати ці пропозиції. Можливо, виявляться дані, про існування яких раніше не було відомо.

Білл Макевой ( [email protected] ) - керівник і адміністратор бази даних сайту Cooking with SQL Web