3. Вступ до мови SQL

У цьому розділі ми представляємо перші команди SQL, що дозволяють створювати та використовувати окрему таблицю. Зазвичай ми наводимо їх спрощену версію. Повний синтаксис наведено в довідниках Firebird (див. параграф 2.2).

Базою даних користуються люди з різним рівнем кваліфікації:

адміністратор бази даних, як правило, є фахівцем, який досконало володіє мовою SQL та базами даних. Саме він створює таблиці, оскільки ця операція зазвичай виконується лише один раз. Згодом йому може знадобитися змінити їхню структуру. База даних — це сукупність таблиць, пов’язаних між собою відношеннями. Саме адміністратор бази визначає ці відношення. Також саме він надає права різним користувачам бази. Таким чином, він вказує, що певний користувач має право переглядати вміст таблиці, але не має права її змінювати.
Користувач бази даних — це особа, яка забезпечує функціонування даних. Відповідно до прав, наданих адміністратором бази, він додаватиме, змінюватиме та видалятиме дані в різних таблицях бази. Він також використовуватиме їх для отримання інформації, корисної для належного функціонування підприємства, адміністрації тощо.

У розділі 2.6 ми представили редактор SQL інструменту [IB-Expert]. Саме цей інструмент ми й будемо використовувати. Нагадаємо кілька моментів:

Редактор SQL відкривається за допомогою пункту меню [Tools/SQL Editor] або за допомогою клавіші [F12]

Після цього з’явиться вікно [SQL Editor], у якому ми можемо ввести команду SQL:

Наведений вище знімок екрана часто відображається у вигляді такого тексту:

SQL> select * from BIBLIO

3.1. Типи даних у Firebird

Під час створення таблиці необхідно вказати тип даних, які може містити стовпець таблиці. Тут ми наводимо найпоширеніші типи даних у Firebird. Зазначимо, що ці типи даних можуть відрізнятися в різних версіях SGBD.

`SMALLINT`	ціле число в діапазоні [-32768, 32767]: 4
`INTEGER`	ціле число в діапазоні [–2 147 483 648, 2 147 483 647]: -100
`NUMERIC(n,m) DECIMAL(n,m)`	дійсне число з n цифр, з яких m — після коми NUMERIC(5,2): -100,23, +027,30
`FLOAT`	наближене дійсне число з 7 значущими цифрами: 10,4
`DOUBLE PRECISION`	наближене дійсне число з 15 значущими цифрами: -100.89
`CHAR(N) CHARACTER(N)`	рядок, що містить рівно N символів. Якщо збережений рядок містить менше N символів, він доповнюється пробілами. CHAR(10): 'ANGERS ' (4 пробіли наприкінці)
`VARCHAR(N) CHARACTER VARYING(N)`	рядок, що містить не більше N символів VARCHAR(10): 'ANGERS'
`DATE`	дата: '2006-01-09' (формат YYYY-MM-DD)
`TIME`	час: '16:43:00' (формат HH:MM:SS)
`TIMESTAMP`	дата та час одночасно: '2006-01-09 16:43:00' (формат YYYY-MM-DD HH:MM:SS)

Функція CAST() дозволяє переходити з одного типу в інший, коли це необхідно. Щоб перетворити значення V, оголошене як тип T1, на тип T2, потрібно написати: CAST(V,T2). Можна здійснювати такі перетворення типів:

число в рядок символів. Ця зміна типу відбувається неявно і не вимагає використання функції CAST. Таким чином, операція 1 + '3' не вимагає перетворення символу '3'. Її результатом є число 4.
DATE, TIME, TIMESTAMP у символьні рядки та навпаки. Таким чином,
TIMESTAMP у TIME або DATE і навпаки

У таблиці рядок може містити стовпці без значень. Кажуть, що значенням стовпця є константа NULL. Наявність цього значення можна перевірити за допомогою операторів

IS NULL / IS NOT NULL

3.2. Створення таблиці

Щоб дізнатися, як створити таблицю, спочатку створимо її в режимі [Design] за допомогою IBExpert. Для цього дотримуємося методу, описаного в розділі 2.3. Таким чином створюємо таку таблицю:

Ця таблиця слугуватиме для обліку книг, придбаних бібліотекою. Значення полів такі:

Name	Тип	Обмеження	Значення
`ID`	`INTEGER`	`Primary Key`	`Identifiant du livre`
`TITRE`	`VARCHAR(30)`	`NOT NULL UNIQUE`	`Titre du livre`
`AUTEUR`	`VARCHAR(20)`	`NOT NULL`	`Son auteur`
`GENRE`	`VARCHAR(30)`	`NOT NULL`	`Son genre (Roman, Poésie, Policier, BD, ..)`
`ACHAT`	`DATE`	`NOT NULL`	`Date d'achat du livre`
`PRIX`	`NUMERIC6,2)`	`NOT NULL`	`Son prix`
`DISPONIBLE`	`CHAR(1)`	`NOT NULL`	`Est-il disponible ? O (oui), N (non)`

Цю таблицю, створену за допомогою інструменту IBEXPERT як майстра, можна було б створити безпосередньо за допомогою команд SQL. Щоб дізнатися про них, достатньо переглянути вкладку [DDL] таблиці:

Код SQL, за допомогою якого було створено таблицю [BIBLIO], має такий вигляд:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;


CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL,
    TITRE VARCHAR(30) NOT NULL,
    AUTEUR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   ACHAT DATE NOT NULL,
   PRIX NUMERIC(6,2) NOT NULL,
   DISPONIBLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);
ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

рядок 1: власник Firebird — вказує рівень діалекту SQL, що використовується
рядок 2: власник Firebird — вказує на використовуване сімейство символів
рядки 6–14: стандарт SQL: створює таблицю BIBLIO, визначаючи ім’я та тип кожного з її стовпців.
рядок 16: стандарт SQL: створює обмеження, яке вказує, що стовпець TITRE не допускає дублікатів
рядок 17: стандарт SQL: вказує, що стовпець [ID] є первинним ключем таблиці. Це означає, що два рядки таблиці не можуть мати однаковий ID. Це схоже на обмеження [UNIQUE NOT NULL] для стовпця [TITRE], і, фактично, стовпець TITRE міг би слугувати первинним ключем. Сучасна тенденція полягає у використанні первинних ключів, які не мають значення і генеруються за допомогою SGBD.

Синтаксис команди [CREATE TABLE] такий:

syntaxe

CREATE TABLE таблиця (nom_colonne1 type_colonne1 contrainte_colonne1, nom_colonne2 type_colonne2 contrainte_colonne2, ..., nom_colonnen type_colonnen contrainte_colonnen, інші обмеження)

action

створює таблицю table із зазначеними стовпцями

`nom_colonnei`	назва стовпця i, який потрібно створити
`type_colonnei`	тип даних стовпця i: char(30) numeric(6,2) date timestamp ...
`contrainte_colonnei`	обмеження, яким повинні відповідати дані стовпця i. Ось деякі з них: PRIMARY KEY: стовпець є первинним ключем. Це означає, що два записи таблиці ніколи не можуть мати однакове значення в цьому стовпці, а також те, що значення в цьому стовпці є обов’язковим. Первинний ключ слугує, насамперед, для однозначної ідентифікації запису. NOT NULL : у цьому стовпці не допускаються нульові значення. UNIQUE : жодне значення не може повторюватися у стовпці. CHECK (умова): значення стовпця має відповідати умові.
`autres contraintes`	тут можна вказати - обмеження на кілька стовпців: check(col1>col2) - обмеження на зовнішні ключі

Таблицю [BIBLIO] також можна було б побудувати за таким порядком SQL:

CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL PRIMARY KEY,
    TITRE VARCHAR(30) NOT NULL UNIQUE,
    AUTEUR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   ACHAT DATE NOT NULL,
   PRIX NUMERIC(6,2) NOT NULL,
   DISPONIBLE  CHAR(1) NOT NULL
);

Давайте це продемонструємо. Візьмемо цей порядок у редакторі SQL (F12), щоб створити таблицю, яку ми назвемо [BIBLIO2]:

Після виконання потрібно підтвердити транзакцію, щоб побачити результат у базі даних:

Після цього таблиця з’являється в базі даних:

Двічі клацнувши на її назві, можна переглянути її структуру:

Тут ми бачимо саме те визначення, яке ми створили для таблиці [BIBLIO2]

3.3. Видалення таблиці

Команда SQL для видалення таблиці виглядає так:

`syntaxe`	DROP TABLE table
`action`	Supprime [table]

Щоб видалити таблицю [BIBLIO2], яку ми щойно створили, тепер виконаємо таку команду SQL:

і підтверджуємо її за допомогою [Commit]. Таблиця [BIBLIO2] видалена:

3.4. Заповнення таблиці

Вставимо рядок у таблицю [BIBLIO], яку щойно створили:

Підтвердимо додавання рядка за допомогою [Commit], а потім клацнімо правою кнопкою миші на доданому рядку:

і, як показано вище, скопіюємо вставлений рядок у буфер обміну у вигляді команди SQL INSERT. Далі відкриємо будь-який текстовий редактор і вставимо (Вставити / Paste) те, що щойно скопіювали. Отримаємо такий код SQL:

INSERT INTO BIBLIO (ID,TITRE,AUTEUR,GENRE,ACHAT,PRIX,DISPONIBLE) VALUES (1,'Candide','Voltaire','Essai','18-OCT-1985',140,'o');

Синтаксис команди вставки SQL такий:

`syntaxe`	insert into table [(colonne1, colonne2, ..)] values (значення1, значення2, ....)
`action`	додає рядок (значення1, значення2, ..) до таблиці table. Ці значення присвоюються стовпцям таблиць colonne1, colonne2,... якщо вони існують, інакше — стовпцям таблиці в тому порядку, в якому вони були визначені.

Щоб вставити нові рядки в таблицю [BIBLIO], у редакторі SQL слід ввести такі команди INSERT. Ці команди потрібно виконувати та підтверджувати [Commit] по черзі. Для переходу до наступної команди INSERT слід використовувати кнопку [New Query].

insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (2,'Les fleurs du mal','Baudelaire','Poème','01-jan-78',120,'n');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (3,'Tintin au Tibet','Hergé','BD','10-nov-90',70,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (4,'Du côté de chez Swann','Proust','Roman','08-dec-78',200,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (5,'La terre','Zola','roman','12-jun-90',50,'n');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (6,'Madame Bovary','Flaubert','Roman','12-mar-88',130,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (7,'Manhattan transfer','Dos Passos','Roman','30-aug-87',320,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (8,'Tintin en Amérique','Hergé','BD','15-may-91',70,'o');

Після підтвердження [Commit] різних команд SQL ми отримуємо таку таблицю:

3.5. Перегляд таблиці

3.5.1. Вступ

У редакторі SQL введімо таку команду:

і виконаємо її. Отримаємо такий результат:

Команда SELECT дозволяє переглянути вміст таблиць бази даних. Ця команда має дуже розгалужений синтаксис. Тут ми розглянемо лише ту частину, яка дозволяє здійснювати запит до однієї таблиці. Пізніше ми розглянемо одночасний запит до декількох таблиць. Синтаксис команди SQL [SELECT] такий:

syntaxe

SELECT [ALL|DISTINCT] [*|expression1 alias1, expression2 alias2, ...]

FROM table

action

відображає значення expressioni для всіх рядків таблиці. expressioni може бути стовпцем або більш складним виразом. Символ * позначає сукупність стовпців. За замовчуванням відображаються всі рядки таблиці (ALL). Якщо присутній DISTINCT, вибрані однакові рядки відображаються лише один раз. Значення expressioni відображаються у стовпці з заголовком expressioni або aliasi, якщо такий заголовок було використано.

Приклади:

SQL > select titre, auteur from biblio

SQL> select titre,prix from biblio

SQL> select titre TITRE_DU_LIVRE, prix PRIX_ACHAT from biblio

Вище ми прив’язали псевдоніми (TITRE_DU_LIVRE, PRIX_ACHAT) до запитуваних стовпців.

3.5.2. Відображення рядків, що відповідають певній умові

`syntaxe`	SELECT .... WHERE condition
`action`	відображаються лише рядки, що відповідають умові condition

Приклади

SQL> select titre,prix from biblio where prix>100

SQL> select titre,prix,genre from biblio where genre='Roman'

Одна з книг має жанр «roman», а не «Roman». Ми використовуємо функцію upper, яка перетворює рядок на великі літери, щоб отримати всі романи.

SQL> select titre,prix,genre from biblio where upper(genre)='ROMAN'

Ми можемо об’єднувати умови за допомогою логічних операторів

`AND`	ET logique
`OR`	OU logique
`NOT`	Логічне заперечення

SQL> select titre,prix,genre from biblio where upper(genre)='ROMAN' and prix<100

SQL> select titre,genre from biblio

SQL> select titre,genre from biblio where upper(genre)='ROMAN' or upper(genre)='BD'

SQL> select titre,genre from biblio where not( upper(genre)='ROMAN' or upper(genre)='BD')

SQL> select titre,achat from biblio

SQL>select titre,achat from biblio where achat>'31-dec-1987'

SQL> select titre,prix from biblio where prix between 100 and 150

3.5.3. Відображення рядків у певному порядку

До наведених вище синтаксисів можна додати клаузулу ORDER BY, що вказує бажаний порядок відображення:

syntaxe

SELECT ....

ORDER BY expression1 [asc|desc], expression2 [asc|dec], ...

action

Рядки, отримані в результаті відбору, відображаються в порядку

1: у порядку зростання (asc / ascending — значення за замовчуванням) або спадання (desc / descending) за expression1

2: у разі рівності значень expression1 відображення здійснюється відповідно до значень expression2

тощо...

Приклади:

SQL>select titre, genre,prix,achat from biblio order by achat desc

SQL>select titre, genre,prix,achat from biblio order by prix

SQL>select titre, genre,prix,achat from biblio order by genre desc

SQL >select titre, genre,prix,achat from biblio order by genre desc, prix

SQL>select titre, genre,prix,achat from biblio order by genre desc, prix desc

3.6. Видалення рядків у таблиці

`syntaxe`	DELETE FROM table [WHERE condition]
`action`	видаляє рядки з table, перевіряючи наявність condition. Якщо останнього немає, видаляються всі рядки.

Приклади:

SQL> select titre from biblio

Дві наведені нижче команди виконуються одна за одною:

SQL> delete from biblio where titre='Candide'

SQL> select titre from biblio

3.7. Зміна вмісту таблиці

`syntaxe`	update table set стовпець1 = вираз1, стовпець2 = вираз2, ... [where condition]
`action`	Для рядків table, що перевіряють condition (усі рядки, якщо немає умови), colonnei отримує значення expressioni.

Приклади:

SQL> select genre from biblio

Усі родові форми пишуться з великої літери:

SQL> update biblio set genre=upper(genre)

Перевіряємо:

SQL> select genre from biblio

Виводимо ціни:

SQL> select genre,prix from biblio;

Ціни на романи зростають на 5 %:

SQL> update biblio set prix=prix*1.05 where genre='ROMAN';

Перевіряємо:

SQL> select genre,prix from biblio

3.8. Остаточне оновлення таблиці

Коли в таблицю вносяться зміни, Firebird фактично генерує їх на копії таблиці. Потім ці зміни можна зробити остаточними або скасувати за допомогою команд COMMIT та ROLLBACK.

`syntaxe`	COMMIT
`action`	зафіксує зміни, внесені до таблиць з моменту останнього запуску COMMIT.

`syntaxe`	ROLLBACK
`action`	скасовує всі зміни, внесені до таблиць з моменту останнього запуску COMMIT.

Remarque

COMMIT виконується неявно в таких випадках:

а) під час виходу з Firebird

б) Після кожного запиту, що впливає на структуру таблиць: CREATE, ALTER, DROP.

Приклади

У редакторі SQL базу даних приводять до відомого стану, підтверджуючи всі операції, виконані з моменту останнього виконання COMMIT або ROLLBACK:

SQL> commit

Запитуємо список титулів:

SQL> select titre from biblio

Видалення заголовка:

SQL> delete from biblio where titre='La terre'

Перевірка:

SQL> select titre from biblio

Заголовок було успішно видалено. Тепер ми скасовуємо всі зміни, внесені з моменту останнього виконання COMMIT / ROLLBACK:

SQL> rollback

Перевірка:

SQL> select titre from biblio

Ми бачимо, що заголовок видалено. Тепер запитаємо список цін:

SQL> select prix from biblio

Припустимо, що всі ціни обнулені.

SQL> update biblio set prix=0

Перевіримо ціни:

SQL> select prix from biblio

Видаляємо зміни, внесені до бази:

SQL> rollback

і ще раз перевіримо ціни:

SQL> select prix from biblio

Ми відновили початкові ціни.

3.9. Додавання рядків з однієї таблиці до іншої

Можна додавати рядки з однієї таблиці до іншої, якщо їхні структури сумісні. Щоб продемонструвати це, спочатку створимо таблицю [BIBLIO2], яка має таку саму структуру, як і [BIBLIO].

У браузері баз даних IBExpert двічі клацніть на таблиці [BIBLIO], щоб відкрити вкладку [DDL]:

На цій вкладці міститься список команд SQL, які дозволяють згенерувати таблицю [BIBLIO]. Скопіюємо весь цей код у буфер обміну (CTRL-A, CTRL-C). Потім запустимо інструмент під назвою [Script Executive], який дозволяє виконати список команд SQL:

З’явиться текстовий редактор, у який ми можемо вставити (CTRL-V) текст, попередньо скопійований у буфер обміну:

Список команд SQL часто називають скриптом SQL. [Script Executive] дозволить нам виконати такий скрипт, тоді як редактор SQL дозволяв виконувати лише одну команду за раз. Поточний скрипт SQL дозволяє створити таблицю [BIBLIO]. Зробимо так, щоб він створив таблицю з назвою [BIBLIO2]. Для цього достатньо змінити [BIBLIO] на [BIBLIO2]:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITRE       VARCHAR(30) NOT NULL,
    AUTEUR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(20) NOT NULL,
    ACHAT       DATE NOT NULL,
    PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    DISPONIBLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO2 ADD CONSTRAINT UNQ1_BIBLIIO2 UNIQUE (TITRE);

ALTER TABLE BIBLIO2 ADD CONSTRAINT PK_BIBLIIO2 PRIMARY KEY (ID);

Запустимо цей скрипт за допомогою кнопки [Run Script] нижче:

Скрипт виконано:

і ми можемо побачити нову таблицю в оглядачі баз даних:

Якщо двічі клацнути на [BIBLIO2], щоб перевірити її вміст, можна побачити, що вона порожня, що є нормальним:

Варіант команди SQL INSERT дозволяє вставляти в таблицю рядки з іншої таблиці:

`syntaxe`	INSERT INTO table1 [(colonne1, colonne2, ...)] SELECT стовпець a, стовпець b, ... FROM table2 WHERE condition
`action`	Рядки з table2, що перевіряють condition, додаються до table1. Стовпці colonnea, colonneb, ... з table2 присвоюються в порядку стовпцю1, стовпцю2, ... з table1 і тому повинні бути сумісного типу.

Повернемося до редактора SQL:

і відправимо таку команду SQL:

SQL> insert into BIBLIO2 select * from BIBLIO where upper(genre)='ROMAN'

яка вставляє в [BIBLIO2] усі рядки з [BIBLIO], що відповідають роману. Після виконання команди SQL підтвердимо її командою [Commit]:

SQL> commit

Зробивши це, переглянемо дані таблиці [BIBLIO2]:

SQL> select * from BIBLIO2

3.10. Видалення таблиці

`syntaxe`	DROP TABLE table
`action`	supprime table

Приклад: видаляємо таблицю BIBLIO2

SQL> drop table BIBLIO2

Підтверджуємо зміну:

SQL> commit

У браузері баз даних оновлюємо відображення таблиць:

Виявляється, що таблиця [BIBLIO2] була видалена:

3.11. Зміна структури таблиці

`syntaxe`	ALTER TABLE table [ ADD nom_colonne1 type_colonne1 contrainte_colonne1] [ALTER nom_colonne2 TYPE type_colonne2] [DROP nom_colonne3] [ADD contrainte] [DROP CONSTRAINT nom_contrainte]
`action`	дозволяє додавати (ADD), змінювати (ALTER) та видаляти (DROP) стовпці таблиці. Синтаксис nom_colonnei type_colonnei contrainte_colonnei відповідає синтаксису CREATE TABLE. Також можна додавати/видаляти обмеження таблиці.

Приклад: Послідовно виконаємо дві наступні команди SQL у редакторі SQL

SQL > alter table biblio add nb_pages numeric(4), alter genre type varchar(30)

SQL> commit

У браузері баз даних перевіримо структуру таблиці [BIBLIO]:

Зміни було враховано. Давайте подивимося, як змінився вміст таблиці:

SQL> select * from biblio

Новий стовпець [NB_PAGES] було створено, але він не містить жодних значень. Видамо цей стовпець:

SQL> alter table biblio drop nb_pages

SQL> commit

Перевіримо нову структуру таблиці [BIBLIO]:

Стовпець [NB_PAGES] дійсно зник.

3.12. Види

Можна створити частковий вигляд однієї або декількох таблиць. Вигляд працює як таблиця, але не містить даних. Його дані витягуються з інших таблиць або виглядів. Вигляд має кілька переваг:

Користувач може бути зацікавлений лише в певних стовпцях та рядках певної таблиці. Вигляд дозволяє йому бачити лише ці рядки та стовпці.
Власник таблиці може бажати надати іншим користувачам лише обмежений доступ до неї. Вигляд дозволяє йому це зробити. Користувачі, яким він надасть дозвіл, матимуть доступ лише до вигляду, який він визначить.

3.12.1. Створення подання

`syntaxe`	CREATE VIEW nom_vue AS SELECT стовпець1, стовпець2, ... FROM table WHERE condition [ WITH CHECK OPTION ]
`action`	створює погляд nom_vue. Це таблиця, структура якої складається зі стовпців 1, 2, ... з table, а рядки — це рядки з table, що відповідають condition (усі рядки, якщо умови немає)
`WITH CHECK OPTION`	Цей необов’язковий пункт вказує, що операції вставки та оновлення у поданні не повинні створювати рядків, які подання не зможе вибрати.

Примітка Синтаксис запитів CREATE та VIEW насправді є складнішим, ніж наведений вище, і, зокрема, дозволяє створювати представлення на основі декількох таблиць. Для цього достатньо, щоб запит SELECT стосувався декількох таблиць (див. наступний розділ).

Приклади

На основі таблиці biblio створюємо представлення, що містить лише романи (вибір рядків) та стовпці «назва», «автор», «ціна» (вибір стовпців):

SQL> create view romans as select titre,auteur,prix from biblio where upper(genre)='ROMAN';

SQL> commit

У браузері баз даних оновимо представлення (F5). З’явиться таке представлення:

Можна дізнатися порядок SQL, пов'язаний із цим поглядом. Для цього двічі клацніть на погляді [ROMANS]:

Вигляд — це щось на зразок таблиці. Він має таку структуру:

та вміст:

З видом працюють так само, як і з таблицею. До нього можна надсилати запити SQL. Ось кілька прикладів, з якими можна попрактикуватися в редакторі SQL:

SQL> select * from romans

SQL> insert into biblio values (10,'Le père Goriot','Balzac','Roman','01-sep-91',200,'o')

Чи видно новий роман у вікні [ROMANS]?

SQL> select * from romans

Додамо до таблиці [BIBLIO] ще щось, окрім роману:

SQL> insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (11,'Poèmes saturniens','Verlaine','Poème','02-sep-92',200,'o');

Перевіримо таблицю [BIBLIO]:

SQL> select titre, auteur from BIBLIO

Перевіримо представлення [ROMANS]:

SQL> select titre, auteur from ROMANS

Додана книга відсутня у перегляді [ROMANS], оскільки у неї не було upper(genre)='ROMAN'.

3.12.2. Оновлення подання

Огляд можна оновити так само, як і таблицю. Це оновлення впливає на всі таблиці, з яких витягуються дані для огляду. Ось кілька прикладів:

SQL> insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (13,'Le Rouge et le Noir','Stendhal','Roman','03-oct-92',110,'o')

SQL> select * from romans

SQL> select titre, auteur from biblio

Видаляємо рядок з подання [ROMANS]:

SQL> delete from ROMANS where titre='Le Rouge et le Noir'

SQL> select * from romans

SQL> select auteur, titre from BIBLIO

Рядок, видалений з подання [ROMANS], також було видалено з таблиці [BIBLIO]. Тепер підвищуємо ціну на книги з подання [ROMANS]:

SQL> update romans set prix=prix*1.05

Перевіряємо в [ROMANS]:

SQL> select * from romans

Як це вплинуло на таблицю [BIBLIO]?

SQL> select titre, auteur, prix from biblio

Кількість романів у таблиці [BIBLIO] також збільшилася на 5%.

3.12.3. Видалити представлення

`syntaxe`	DROP VIEW nom_vue
`action`	видаляє подання з назвою

Приклад

SQL> drop view romans

SQL> commit

У браузері баз даних можна оновити представлення (F5), щоб переконатися, що представлення [ROMANS] зникло:

3.13. Використання групових функцій

Існують функції, які замість обробки кожного рядка таблиці працюють із групами рядків. Це, в основному, статистичні функції, що дозволяють обчислювати середнє значення, стандартне відхилення тощо для даних стовпця.

`syntaxe1`	SELECT f1, f2, .., fn FROM table [ WHERE condition ]
`action`	обчислює статистичні функції fi для всіх рядків таблиці, перевіряючи можливе значення condition.

syntaxe2

SELECT f1, f2, .., fn FROM table

[ WHERE condition ]

[ GROUP BY expr1, expr2, ..]

action

Ключове слово GROUP BY дозволяє розділити рядки таблиці на групи. Кожна група містить рядки, для яких вирази expr1, expr2, ... мають однакове значення.

Приклад: GROUP BY genre об’єднує в одну групу книги одного жанру. Умова GROUP BY «автор, жанр» об’єднає в одну групу книги, що мають одного й того самого автора та належать до одного й того самого жанру. Умова WHERE спочатку виключає з таблиці рядки, що не відповідають цій умові. Потім за допомогою умови GROUP BY формуються групи. Далі для кожної групи рядків обчислюються функції fi.

`syntaxe3`	SELECT f1, f2, .., fn FROM table [ WHERE condition ] [ GROUP BY expression] [ HAVING condition_de_groupe]
`action`	Умова HAVING фільтрує групи, утворені умовою GROUP BY. Тому вона завжди пов’язана з наявністю таких умов GROUP та BY. Приклад: GROUP BY тип HAVING тип!='ROMAN'

Доступні статистичні функції fi:

`AVG(expression)`	середнє значення виразу
`COUNT(expression)`	кількість рядків, для яких вираз має значення
`COUNT(*)`	загальна кількість рядків у таблиці
`MAX(expression)`	максимальне значення виразу
`MIN(expression)`	мінімальне значення виразу
`SUM(expression)`	сума виразу

Приклади

SQL> select prix from biblio

Середня ціна? Максимальна ціна? Мінімальна ціна?

SQL> select avg(prix), max(prix), min (prix) from biblio

SQL> select titre, prix,genre from biblio

Середня ціна роману? Максимальна ціна?

SQL> select avg(prix) moyenne, max(prix) prix_maxi from biblio where upper(genre)='ROMAN'

Скільки BD?

SQL> select count(*) from biblio where upper(genre)='BD'

Скільки романів коштують менше 100 F?

SQL> select count(*) from biblio where upper(genre)='ROMAN' and prix<100

SQL> select genre, prix from biblio

Кількість книг та середня ціна книги для книг одного жанру?

SQL> select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE from biblio group by upper(genre)

Те саме питання, але лише для книг, що не є романами:

SQL>
select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
group by upper(genre)
having upper(GENRE)!='ROMAN'

Те саме питання, але тільки для книг вартістю менше 150 F:

SQL> 
select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
where prix<150
group by upper(genre)
having upper(GENRE)!='ROMAN'

Те саме запит, але відбираються лише групи, середня ціна книги в яких перевищує 100 F

SQL> 
select upper(genre) GENRE, avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
group by upper(genre)
having avg(prix)>100

3.14. Створити скрипт SQL для таблиці « »

Мова SQL є стандартною мовою, яку можна використовувати з багатьма SGBD. Щоб мати можливість переходити від одного SGBD до іншого, доцільно експортувати базу даних або лише окремі її елементи у вигляді скрипта SQL, який, запущений в іншому SGBD, зможе відтворити елементи, експортовані у скрипті.

Тут ми експортуємо таблицю [BIBLIO]. Виберемо опцію [Extract Metadata]:

Зверніть увагу, що потрібно вибрати базу даних, з якої потрібно експортувати елементи. Ця опція запускає майстер:

`1`	де створити скрипт SQL: у файл (File) у буфер обміну (Clipboard) у програмі Script Executive
`2`	ім'я файлу, якщо обрано опцію [File]
`3`	що експортувати
`4`	кнопки для вибору (->) або скасування вибору (<-) об’єктів для експорту

Якщо ми хочемо експортувати всю базу даних, ми повинні встановити прапорець біля опції [Extract All] вище. Ми хочемо експортувати лише таблицю BIBLIO. Для цього за допомогою [4] ми вибираємо таблицю [BIBLIO], а за допомогою [2] вказуємо файл:

Якщо зупинитися на цьому, буде експортовано лише структуру таблиці [BIBLIO]. Щоб експортувати її вміст, потрібно скористатися вкладкою [Data Tables]:

Використаємо [1], щоб вибрати таблицю [BIBLIO]:

Використаємо [2] для генерації скрипта SQL:

Приймемо пропозицію. Це дозволить нам переглянути скрипт, який було згенеровано у файлі [biblio.sql]:

/******************************************************************************/
/****          Створено за допомогою IBExpert 17.06.2004 22.        01.2006 15:06:13 ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE DATABASE 'D:\data\serge\travail\2005-2006\polys\sql\DBBIBLIO.GDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET ISO8859_1;



/******************************************************************************/
/****                                                                 Таблиці ****/
/******************************************************************************/



CREATE TABLE BIBLIO (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      VARCHAR(20) NOT NULL,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POèME', '1978-01-01', 120, 'n');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 'Hergé', 'BD', '1990-11-10', 70, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 'Proust', 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 'Zola', 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 'Flaubert', 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 'Dos Passos', 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 'Hergé', 'BD', '1991-05-15', 70, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 'Balzac', 'Roman', '1991-09-01', 210, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 'Verlaine', 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                                                     Унікальні обмеження ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);


/******************************************************************************/
/****                                                           Первинні ключі ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

рядки з 1 по 3 — це коментарі
рядки з 5 по 12 — це власний код SQL для Firebird
решта рядків — це стандартний код SQL, який можна виконати у файлі SGBD, де типи даних оголошені в таблиці BIBLIO.

Виконаємо цей скрипт у Firebird, щоб створити таблицю BIBLIO2, яка буде клоном таблиці BIBLIO. Для цього скористаємося [Script Executive] (Ctrl-F12):

Завантажимо скрипт [biblio.sql], який ми щойно згенерували:

Змінимо його, щоб залишити лише частину, що стосується створення таблиці та вставки рядків. Таблицю перейменовано на [BIBLIO2]:

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITRE       VARCHAR(30) NOT NULL,
    AUTEUR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(30) NOT NULL,
    ACHAT       DATE NOT NULL,
    PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO2 (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POèME', '1978-01-01', 120, 'n');
...

COMMIT WORK;

Запустимо цей скрипт:

У базі даних ми можемо перевірити, що таблиця [BIBLIO2] була успішно створена і має очікувану структуру та вміст: