Лекція 12. Оператори модифікації та SQL-сценарії

1.   Оператори для модифікації даних

Оператор INSERT

1.1.     Додавання однієї повної стрічки у таблицю

INSERT INTO Client VALUES

(1, 'Павельчак', 'Україна', 'Львів', 'bilyj@ukr.net')

 

ID

Surname

Country

City

Email

1

Павельчак

Україна

Львів

bilyj@ukr.net

 

У переліку значення повинні слідувати у тому порядку, в якому стовпці є визначені у таблиці.

У багатьох реалізаціях СКБД ключове слово INTO може бути опущене.

 

INSERT INTO Client (ID, Surname, Country, City, Email)

VALUES (2, 'Ющенко', 'Україна', 'Київ', NULL)

 

ID

Surname

Country

City

Email

1

Павельчак

Україна

Львів

bilyj@ukr.net

2

Ющенко

Україна

Київ

NULL

 

Перелік імен стовпців вказується у дужках після назви таблиці.

Якщо структура стовпця дозволяє не вводити конкретне значення, тоді замість значення можемо ввести null-значення.

 

INSERT INTO Client (Surname, City, Country, ID, Email)

VALUES ('Лукашенко', 'Мінськ', 'Білорусія', 3, NULL)

ID

Surname

Country

City

Email

1

Павельчак

Україна

Львів

bilyj@ukr.net

2

Ющенко

Україна

Київ

NULL

3

Лукашенко

Мінськ

Білорусія

NULL

 

Порядок введення значень для стовпців може бути довільним, при умові, що порядок значень у переліку співпадає з порядком слідування стовпців.

 

1.2.     Додавання частини стрічки у таблицю

INSERT INTO Client (ID, Surname)

VALUES (4, 'Мажуга')

 

ID

Surname

Country

City

Email

1

Павельчак

Україна

Львів

bilyj@ukr.net

2

Ющенко

Україна

Київ

NULL

3

Лукашенко

Мінськ

Білорусія

NULL

4

Мажуга

Україна

Львів

NULL

 

У переліку стовпців може бути вказана лише частина стовпців. Тоді у переліку значень мають бути вказані лише відповідні для них значення. Решта значень визначаються за замовчуванням, тобто, якщо для стовпця визначені значення за замовчуванням, тоді присвоюються ці значення, інакше встановлюються null-значення (якщо null-значення дозволені).

 

INSERT INTO Client

VALUES (5, 'Шніцар', DEFAULT, DEFAULT, DEFAULT)

 

ID

Surname

Country

City

Email

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

Email

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

Email

 

 

 

 

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        Змінна надає інформацію про імя локального сервера, на якому був запущений сценарій.