Лекція 12. Оператори модифікації та SQL-сценарії
1. Оператори для модифікації даних
Оператор INSERT
1.1. Додавання однієї повної стрічки у таблицю
INSERT INTO Client VALUES
(1, 'Павельчак', 'Україна', 'Львів', 'bilyj@ukr.net')
ID |
Surname |
Country |
City |
|
1 |
Павельчак |
Україна |
Львів |
bilyj@ukr.net |
У переліку значення повинні слідувати у тому порядку, в якому стовпці є визначені у таблиці.
У багатьох реалізаціях СКБД ключове слово INTO може бути опущене.
INSERT INTO Client (ID, Surname, Country, City, Email)
VALUES (2, 'Ющенко', 'Україна', 'Київ', NULL)
ID |
Surname |
Country |
City |
|
1 |
Павельчак |
Україна |
Львів |
bilyj@ukr.net |
2 |
Ющенко |
Україна |
Київ |
NULL |
Перелік імен стовпців вказується у дужках після назви таблиці.
Якщо структура стовпця дозволяє не вводити конкретне значення, тоді замість значення можемо ввести null-значення.
INSERT INTO Client (Surname, City, Country, ID, Email)
VALUES ('Лукашенко', 'Мінськ', 'Білорусія', 3, NULL)
ID |
Surname |
Country |
City |
|
1 |
Павельчак |
Україна |
Львів |
bilyj@ukr.net |
2 |
Ющенко |
Україна |
Київ |
NULL |
3 |
Лукашенко |
Мінськ |
Білорусія |
NULL |
Порядок введення значень для стовпців може бути довільним, при умові, що порядок значень у переліку співпадає з порядком слідування стовпців.
1.2. Додавання частини стрічки у таблицю
INSERT INTO Client (ID, Surname)
VALUES (4, 'Мажуга')
ID |
Surname |
Country |
City |
|
1 |
Павельчак |
Україна |
Львів |
bilyj@ukr.net |
2 |
Ющенко |
Україна |
Київ |
NULL |
3 |
Лукашенко |
Мінськ |
Білорусія |
NULL |
4 |
Мажуга |
Україна |
Львів |
NULL |
У переліку стовпців може бути вказана лише частина стовпців. Тоді у переліку значень мають бути вказані лише відповідні для них значення. Решта значень визначаються за замовчуванням, тобто, якщо для стовпця визначені значення за замовчуванням, тоді присвоюються ці значення, інакше встановлюються null-значення (якщо null-значення дозволені).
INSERT INTO Client
VALUES (5, 'Шніцар', DEFAULT, DEFAULT, DEFAULT)
ID |
Surname |
Country |
City |
|
1 |
Павельчак |
Україна |
Львів |
bilyj@ukr.net |
2 |
Ющенко |
Україна |
Київ |
NULL |
3 |
Лукашенко |
Мінськ |
Білорусія |
NULL |
4 |
Мажуга |
Україна |
Львів |
NULL |
5 |
Шніцар |
Україна |
Львів |
NULL |
Якщо у стрічку таблиці вставляється неповний набір даних, і при цьому відсутній список з іменами стовпців, тоді на місцях з пропущеними даними вписується ключове слово DEFAULT.
1.3. Додавання стрічки у таблицю, що має автоінкрементне поле
INSERT INTO Student VALUES ('Цюпа', 'Роман')
ID |
Surname |
Name |
1 |
Цюпа |
Роман |
При вставці даних значення для автоінкрементного поля встановлюється автоматично, і тому такий стовпець у конструкції INSERT пропускається, незалежно від того, де він розташовується у переліку стовпців. При спробі явно вказати для нього значення сервер видасть помилку.
1.4. Додавання у таблицю результатів запиту SELECT
Нехай ми маємо таблицю Client2, аналогічну за структурою до таблиці Client.
INSERT INTO Client2 SELECT * FROM Client
Результат: дані з таблиці Client повністю скопіюються у таблицю Client2.
INSERT INTO Client2(Surname, ID)
SELECT Surname, ID AS Num FROM Client
WHERE City = 'Львів'
Client2
ID |
Surname |
Country |
City |
|
1 |
Павельчак |
Україна |
Львів |
bilyj@ukr.net |
4 |
Мажуга |
Україна |
Львів |
NULL |
5 |
Шніцар |
Україна |
Львів |
NULL |
У цьому запиті копіюються дані, що відповідають зазначеній умові. При цьому порядок слідування стовпців повинен співпадати як у конструкції INTO, так і у SELECT. Імена стовпців у SELECT не мають ніякого значення, значення має лише їхній тип даних.
INSERT INTO Client2(ID, Surname)
SELECT 6, 'Сало'
UNION ALL
SELECT 7, 'Кмита'
UNION ALL
SELECT 8, 'Галів'
Client2
ID |
Surname |
Country |
City |
|
… |
|
|
|
|
6 |
Сало |
Україна |
Львів |
NULL |
7 |
Кмита |
Україна |
Львів |
NULL |
8 |
Галів |
Україна |
Львів |
NULL |
Такий «хитрий» підхід дає можливість за один раз вставити декілька стрічок з даними.
1.5. Експортування даних у нову таблицю
SELECT ID, Surname INTO Client3 FROM Client
WHERE City = 'Київ'
Конструкція SELECT INTO копіює дані із зазначеної таблиці у нову (створену «на льоту») таблицю.
Одна з основних відмінностей між операторами SELECT INTO та INSERT SELECT полягає у тому, що перший оператор експортує дані, а другий імпортує.
У СКБД MySQL та Oracle дещо інший синтаксис:
CREATE TABLE Client3 AS
SELECT ID, Surname FROM Client
WHERE City = 'Київ'
Оператор UPDATE
UPDATE <ім’я таблиці>
SET { <ім’я стовпця>={<значення> | NULL | DEFAULT} [,{…} …] }
[ WHERE <умова> ]
Приклад:
UPDATE Client
SET ID = ID + 1, City = DEFAULT, Email = NULL
У результаті в таблиці Client усі значення стовпця ID будуть збільшені на 1, поля City приймуть значення 'Львів', а поля стовпця Email будуть встановлені у NULL.
UPDATE Client
SET City = 'Тернопіль'
WHERE Surname = 'Шніцар'
У результаті виконання цієї конструкції буде видозмінена лише стрічка, що має прізвище 'Шніцар'.
Оператор DELETE
DELETE FROM <ім’я таблиці>
[WHERE <умова>]
Приклад:
DELETE FROM Client
WHERE Surname = 'Павельчак'
У результаті виконання цього запиту з таблиці будуть видалені усі стрічки з прізвищем 'Павельчак'.
Якщо конструкція WHERE відсутня, тоді будуть видалені усі стрічки.
TRUNCATE TABLE <ім’я таблиці>
Також видаляє усі стрічки з таблиці. Але у порівнянні з оператором DELETE має такі відмінності:
1) Значення лічильника автоінкрементного поля скидається у початкове значення.
2) Не спрацьовують тригери, тобто не спрацьовує каскадне видалення.
3) Не вноситься у журнал інформація про окремо видалені стрічки. Зауваження: ключове слово FROM для багатьох СКБД може бути опущене.
2. Створення SQL-сценаріїв
SQL-сценарій – це програмоподібний набір коду, що складається зі сукупності SQL-операторів. Сценарій зберігається у вигляді окремого txt-файла, який може бути викликаний на виконання, а також повторно використовуватися.
Приклади сценаріїв:
1) Створення бази даних.
2) Резервне копіювання.
3) Відновлення бази даних.
Сценарії призначені для виконання будь-яких дій, що потребують сумісного використання декількох SQL-операторів.
Сценарії переважно розглядаються як окрема одиниця роботи, яка або виконується повністю, або взагалі не виконується.
USE master
CREATE DATABASE KSA
1. Оператор USE.
Вказує, яка база даних повинна бути поточною. Як правило, оператор USE необхідний, якщо в сценарію використовуються таблиці якоїсь конкретної БД, особливо при модифікації конкретної БД.
2. Пакети.
Пакет – це засіб групування операторів T-SQL у вигляді однієї логічної одиниці. Сценарій представляє собою також пакет, однак, він може бути розбитий на ряд пакетів за допомогою оператора GO.
· Оператор GO повинен знаходитися на окремій стрічці (на цій ж стрічці не повинно бути більше нічого, за виключенням коментаріїв).
· Оператор GO викликає виконання всіх SQL-операторів від початку сценарію чи від попереднього оператора GO (у залежності, що ближче).
Помилки в пакетах бувають:
- синтаксичні помилки (блокують виконання усього пакета);
- помилки, що виявлені на етапі прогону програми.
У вищенаведеному прикладі помилка CREATE TABLES викличе відміну виконання «свого» пакета, однак попередній пакет виконається успішно, та буде створена пуста БД.
CREATE DATABASE KSA
GO
CREATE TABLES St ← помилка
GO
PRINT 'Все OK'
1) Створиться БД KSA.
2) Сервер видасть помилку в другому пакеті.
3) Надрукується повідомлення 'Все OK'.
3. Оголошення змінних.
DECLARE @<ім’я змінної> <тип змінної>
[@<ім’я змінної> <тип змінної>, … ]
За допомогою оператора DECLARE оголошується одна або декілька змінних. На практиці люблять для кожної окремої змінної використовувати свій DECLARE.
Після оголошення змінна має значення NULL.
Приклад.
DECLARE @value1 int
DECLARE @value2 datetime
DECLARE @value3 int
4. Присвоєння значень змінним.
SET @value1 = 10
SET @value3 = @value1 * 2 - 3
Оператор SET присвоює змінній або константні значення, або значення, отримані на основі виразу з використанням інших змінних.
5. Присвоєння значень на основі результатів запитів.
а)
USE Labor_SQL
DECLARE @value int
SET @value = (SELECT MAX(speed) FROM PC)
Виконається успішно, повернеться результат 900, а також повідомлення 900.
б)
USE Labor_SQL
DECLARE @value int
SELECT @value = MAX(speed) FROM PC
SELECT @value
Виконається успішно та повернеться результат 900.
Рекомендують використовувати саме другий варіант!
За допомогою SELECT можна одразу присвоювати значення декільком змінним.
Приклад2.
USE Labor_SQL
DECLARE @value int
DECLARE @value2 money
SELECT @value = MAX(speed), @value2 = MIN(price) FROM PC
SELECT @value, @value2
Приклад3. Знайдіть виробників найдешевших кольорових принтерів.
USE Labor_SQL
DECLARE @value money
SELECT @value = MIN(price) FROM Printer WHERE color = 'y' SELECT maker FROM Product P, Printer Pr
WHERE P.model = Pr.model AND price = @value AND color = 'y'
6. Системні змінні.
У СКБД SQL Server передбачено понад 30 системних змінних.
Деякі з них:
@@ERROR Містить номер помилки, що виникла при виконанні останнього оператора T-SQL у поточному з’єднанні. Якщо помилка не виявлена результат дорівнює 0. Якщо помилка виникла на початку пакету, то тоді, щоб побачити її у кінці пакету, слід використовувати локальну змінну.
@@IDENTITY Містить останнє автоінкрементне значення, що було встановлене в БД у результаті виконання останнього оператора INSERT чи INSERT INTO.
@@ROWCOUNT Повертає інформацію про кількість стрічок, що були задіяні останнім оператором.
@@SERVERNAME Змінна надає інформацію про ім’я локального сервера, на якому був запущений сценарій.