3. Wprowadzenie do języka SQL

W tej sekcji rozdziału przedstawiamy pierwsze polecenia SQL umożliwiające tworzenie i obsługę pojedynczej tabeli. Zazwyczaj podajemy ich uproszczoną wersję. Pełna składnia tych poleceń jest dostępna w przewodnikach referencyjnych Firebirda (patrz paragraf 2.2).

Z bazy danych korzystają osoby o różnym poziomie kompetencji:

administrator bazy danych to zazwyczaj osoba biegła w języku SQL i bazach danych. To on tworzy tabele, ponieważ operacja ta jest zazwyczaj wykonywana tylko raz. Z biegiem czasu może zaistnieć potrzeba zmiany ich struktury. Baza danych to zbiór tabel połączonych relacjami. To administrator bazy definiuje te relacje. To również on przyznaje uprawnienia różnym użytkownikom bazy. W ten sposób określa, że dany użytkownik ma prawo przeglądać zawartość tabeli, ale nie może jej modyfikować.
Użytkownik bazy danych to osoba, która ożywia dane. W zależności od uprawnień przyznanych przez administratora bazy będzie on dodawał, modyfikował i usuwał dane w różnych tabelach bazy. Będzie również je analizował, aby uzyskać informacje przydatne dla sprawnego funkcjonowania przedsiębiorstwa, administracji itp.

W punkcie 2.6 przedstawiliśmy edytor SQL należący do narzędzia [IB-Expert]. To właśnie tego narzędzia będziemy używać. Przypomnijmy kilka kwestii:

Edytor SQL uruchamia się za pomocą opcji menu [Tools/SQL Editor] lub klawisza [F12]

Pojawi się wtedy okno [SQL Editor], w którym możemy wpisać polecenie SQL:

Powyższy zrzut ekranu jest często przedstawiany za pomocą poniższego tekstu:

SQL> select * from BIBLIO

3.1. Typy danych w Firebirdzie

Podczas tworzenia tabeli należy określić typ danych, jakie może zawierać kolumna tabeli. Poniżej przedstawiamy najczęściej spotykane typy danych w Firebirdzie. Należy pamiętać, że typy te mogą się różnić w zależności od wersji SGBD.

`SMALLINT`	liczba całkowita w zakresie [-32768, 32767]: 4
`INTEGER`	liczba całkowita w zakresie [–2 147 483 648, 2 147 483 647]: -100
`NUMERIC(n,m) DECIMAL(n,m)`	liczba rzeczywista składająca się z n cyfr, z których m znajduje się po przecinku NUMERIC(5,2): -100,23, +027,30
`FLOAT`	liczba rzeczywista zaokrąglona do 7 cyfr znaczących: 10,4
`DOUBLE PRECISION`	liczba rzeczywista zaokrąglona do 15 cyfr znaczących: -100.89
`CHAR(N) CHARACTER(N)`	ciąg dokładnie o długości N znaków. Jeśli zapisany ciąg ma mniej niż N znaków, jest uzupełniany spacjami. CHAR(10): „ANGERS ” (4 spacje na końcu)
`VARCHAR(N) CHARACTER VARYING(N)`	ciąg o długości co najwyżej N znaków VARCHAR(10): 'ANGERS'
`DATE`	data: '2006-01-09' (format YYYY-MM-DD)
`TIME`	godzina: '16:43:00' (format HH:MM:SS)
`TIMESTAMP`	zarówno data, jak i godzina: '2006-01-09 16:43:00' (format YYYY-MM-DD HH:MM:SS)

Funkcja CAST() umożliwia konwersję między typami w razie potrzeby. Aby przekształcić wartość V zadeklarowaną jako typ T1 na typ T2, należy wpisać: CAST(V,T2). Możliwe są następujące zmiany typu:

z liczby na ciąg znaków. Ta zmiana typu odbywa się domyślnie i nie wymaga użycia funkcji CAST. Tak więc operacja 1 + '3' nie wymaga konwersji znaku '3'. Jej wynikiem jest liczba 4.
DATE, TIME, TIMESTAMP na ciągi znaków i odwrotnie. Tak więc
TIMESTAMP na TIME lub DATE i odwrotnie

W tabeli wiersz może zawierać kolumny bez wartości. Mówi się wówczas, że wartością kolumny jest stała NULL. Obecność tej wartości można sprawdzić za pomocą operatorów

IS NULL / IS NOT NULL

3.2. Tworzenie tabeli

Aby dowiedzieć się, jak utworzyć tabelę, zaczynamy od utworzenia jej w trybie [Design] z IBExpert. W tym celu postępujemy zgodnie z metodą opisaną w paragrafie 2.3. W ten sposób tworzymy następującą tabelę:

Tabela ta posłuży do rejestrowania książek zakupionych przez bibliotekę. Znaczenie pól jest następujące:

Name	Typ	Ograniczenie	Znaczenie
`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)`

Ta tabela, która została utworzona przy pomocy narzędzia IBEXPERT jako kreatora, mogła zostać utworzona bezpośrednio za pomocą poleceń SQL. Aby je poznać, wystarczy przejrzeć zakładkę [DDL] w tabeli:

Kod SQL, który umożliwił utworzenie tabeli [BIBLIO], jest następujący:

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);

wiersz 1: właściciel Firebird – wskazuje poziom dialektu SQL
wiersz 2: właściciel Firebird – wskazuje używaną rodzinę znaków
wiersze 6–14: standard SQL: tworzy tabelę BIBLIO, definiując nazwę i typ każdej z jej kolumn.
wiersz 16: standard SQL: tworzy ograniczenie wskazujące, że kolumna TITRE nie dopuszcza duplikatów
wiersz 17: standard SQL: wskazuje, że kolumna [ID] jest kluczem głównym tabeli. Oznacza to, że dwa wiersze tabeli nie mogą mieć tej samej wartości ID. Jest to zbliżone do ograniczenia [UNIQUE NOT NULL] dotyczącego kolumny [TITRE], a zatem kolumna TITRE mogłaby służyć jako klucz główny. Obecnie panuje tendencja do stosowania kluczy głównych, które nie mają znaczenia i są generowane przez SGBD.

Składnia polecenia [CREATE TABLE] jest następująca:

syntaxe

CREATE TABLE tabela (nom_colonne1 type_colonne1 contrainte_colonne1, nom_colonne2 type_colonne2 contrainte_colonne2, ..., nom_colonnen type_colonnen contrainte_colonnen, inne ograniczenia)

action

tworzy tabelę table z podanymi kolumnami

`nom_colonnei`	nazwa kolumny i, która ma zostać utworzona
`type_colonnei`	typ danych kolumny i: char(30) numeric(6,2) date timestamp ...
`contrainte_colonnei`	ograniczenia, które muszą spełniać dane w kolumnie i. Oto kilka z nich: PRIMARY KEY: kolumna jest kluczem głównym. Oznacza to, że dwa wiersze tabeli nigdy nie mają tej samej wartości w tej kolumnie, a ponadto wartość w tej kolumnie jest obowiązkowa. Klucz główny służy przede wszystkim do jednoznacznej identyfikacji wiersza. NOT NULL : w tej kolumnie nie są dozwolone wartości zerowe. UNIQUE : żadna wartość nie może pojawić się w tej kolumnie więcej niż raz. CHECK (warunek): wartość w kolumnie musi spełniać warunek.
`autres contraintes`	można tu umieścić - ograniczenia dotyczące wielu kolumn: check(col1>col2) - ograniczenia kluczy obcych

Tabela [BIBLIO] mogłaby również zostać utworzona przy użyciu następującego polecenia 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
);

Zobaczmy, jak to wygląda. Wprowadźmy tę sekwencję w edytorze SQL (F12), aby utworzyć tabelę, którą nazwiemy [BIBLIO2]:

Po wykonaniu należy zatwierdzić transakcję, aby zobaczyć wynik w bazie danych:

Po wykonaniu tej czynności tabela pojawi się w bazie danych:

Klikając dwukrotnie na jej nazwę, można uzyskać dostęp do jej struktury:

Widzimy tu definicję, którą stworzyliśmy dla tabeli [BIBLIO2]

3.3. Usunięcie tabeli

Polecenie SQL służące do usunięcia tabeli ma następującą postać:

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

Aby usunąć właśnie utworzoną tabelę [BIBLIO2], wykonujemy teraz następujące polecenie SQL:

i zatwierdzamy je za pomocą [Commit]. Tabela [BIBLIO2] zostaje usunięta:

3.4. Wypełnianie tabeli

Wstawiamy wiersz do właśnie utworzonej tabeli [BIBLIO]:

Potwierdźmy dodanie wiersza za pomocą [Commit], a następnie kliknijmy prawym przyciskiem myszy na dodanym wierszu:

i poprośmy, jak pokazano powyżej, o skopiowanie wstawionego wiersza do schowka w postaci polecenia SQL INSERT. Następnie otwórzmy dowolny edytor tekstu i wklejmy (Wklej / Paste) to, co właśnie skopiowaliśmy. Otrzymujemy następujący kod SQL:

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

Składnia polecenia SQL insert jest następująca:

`syntaxe`	insert into tabela [(colonne1, colonne2, ..)] wartości (wartość1, wartość2, ....)
`action`	dodaje wiersz (wartość1, wartość2, ..) do tabeli table. Wartości te są przypisywane do tabel colonne1, colonne2, ..., jeśli istnieją, w przeciwnym razie do kolumn tabeli w kolejności, w jakiej zostały zdefiniowane.

Aby wstawić nowe wiersze do tabeli [BIBLIO], należy wprowadzić następujące polecenia INSERT w edytorze SQL. Polecenia te należy wykonać i zatwierdzić pojedynczo za pomocą [Commit]. Aby przejść do następnego polecenia INSERT, należy użyć przycisku [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');

Po zatwierdzeniu poszczególnych zleceń SQL otrzymujemy następującą tabelę:

3.5. Przeglądanie tabeli

3.5.1. Wprowadzenie

W edytorze SQL wpiszmy następujące polecenie:

i uruchommy je. Otrzymujemy następujący wynik:

Polecenie SELECT umożliwia przeglądanie zawartości tabel bazy danych. Polecenie to charakteryzuje się bardzo rozbudowaną składnią. W tym miejscu przedstawiamy tylko tę, która pozwala na zapytanie o jedną tabelę. Kwestia jednoczesnego zapytania o wiele tabel zostanie omówiona w dalszej części. Składnia polecenia SQL [SELECT] jest następująca:

syntaxe

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

FROM table

action

wyświetla wartości z expressioni dla wszystkich wierszy tabeli. expressioni może być kolumną lub bardziej złożonym wyrażeniem. Symbol * oznacza wszystkie kolumny. Domyślnie wyświetlane są wszystkie wiersze tabeli (ALL). Jeśli występuje DISTINCT, wybrane identyczne wiersze są wyświetlane tylko raz. Wartości expressioni są wyświetlane w kolumnie o nazwie expressioni lub aliasi, jeśli została ona użyta.

Przykłady:

SQL > select titre, auteur from biblio

SQL> select titre,prix from biblio

SQL> select titre TITRE_DU_LIVRE, prix PRIX_ACHAT from biblio

Powyżej przypisaliśmy aliasy (TITRE_DU_LIVRE, PRIX_ACHAT) do żądanych kolumn.

3.5.2. Wyświetlanie wierszy spełniających określony warunek

`syntaxe`	SELECT .... WHERE condition
`action`	wyświetlane są tylko wiersze spełniające warunek condition

Przykłady

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

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

Jedna z książek ma gatunek „powieść”, a nie „Powieść”. Używamy funkcji upper, która zamienia ciąg znaków na wielkie litery, aby uzyskać wszystkie powieści.

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

Warunki można łączyć za pomocą operatorów logicznych

`AND`	ET logique
`OR`	OU logique
`NOT`	Negacja logiczna

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. Wyświetlanie wierszy w określonej kolejności

Do powyższych składni można dodać klauzulę ORDER BY określającą żądaną kolejność wyświetlania:

`syntaxe`	SELECT .... ORDER BY expression1 [asc\|desc], expression2 [asc\|dec], ...
`action`	Wiersze wynikające z selekcji są wyświetlane w kolejności 1: rosnącego (asc / ascending, co jest wartością domyślną) lub malejącego (desc / descending) porządku według expression1 2: w przypadku równości wartości expression1 wyświetlanie odbywa się zgodnie z wartościami expression2 itd.

Przykłady:

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. Usuwanie wierszy z tabeli

`syntaxe`	DELETE FROM table [WHERE condition]
`action`	usuwa wiersze z table, sprawdzając condition. Jeśli ten ostatni nie istnieje, wszystkie wiersze zostaną usunięte.

Przykłady:

SQL> select titre from biblio

Poniższe dwa polecenia są wydawane jedno po drugim:

SQL> delete from biblio where titre='Candide'

SQL> select titre from biblio

3.7. Modyfikacja zawartości tabeli

`syntaxe`	update table set kolumna1 = wyrażenie1, kolumna2 = wyrażenie2, ... [where condition]
`action`	W przypadku wierszy z tabeli table, które sprawdzają zawartość tabeli condition (wszystkie wiersze, jeśli nie ma żadnego warunku), tabela colonnei otrzymuje wartość z tabeli expressioni.

Przykłady:

SQL> select genre from biblio

Wszystkie rodzaje zapisuje się wielkimi literami:

SQL> update biblio set genre=upper(genre)

Sprawdzamy:

SQL> select genre from biblio

Wyświetlamy ceny:

SQL> select genre,prix from biblio;

Ceny powieści wzrosły o 5%:

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

Sprawdzamy:

SQL> select genre,prix from biblio

3.8. Ostateczna aktualizacja tabeli

Gdy wprowadzamy zmiany w tabeli, Firebird faktycznie generuje je na kopii tej tabeli. Zmiany te można następnie zatwierdzić lub cofnąć za pomocą poleceń COMMIT i ROLLBACK.

`syntaxe`	COMMIT
`action`	utrwala zmiany wprowadzone w tabelach od ostatniego uruchomienia COMMIT.

`syntaxe`	ROLLBACK
`action`	cofa wszystkie zmiany wprowadzone w tabelach od ostatniego uruchomienia COMMIT.

Remarque

Operacja COMMIT jest wykonywana domyślnie w następujących momentach:

a) Przy wylogowaniu z Firebirda

b) Po każdym poleceniu wpływającym na strukturę tabel: CREATE, ALTER, DROP.

Przykłady

W edytorze SQL przywraca się bazę do znanego stanu poprzez zatwierdzenie wszystkich operacji wykonanych od ostatniego COMMIT lub ROLLBACK:

SQL> commit

Żądamy listy tytułów:

SQL> select titre from biblio

Usunięcie tytułu:

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

Weryfikacja:

SQL> select titre from biblio

Tytuł został pomyślnie usunięty. Teraz unieważniamy wszystkie zmiany wprowadzone od ostatniego COMMIT / ROLLBACK:

SQL> rollback

Weryfikacja:

SQL> select titre from biblio

Tytuł został usunięty. Teraz wygenerujmy listę cen:

SQL> select prix from biblio

Załóżmy, że wszystkie ceny zostały wyzerowane.

SQL> update biblio set prix=0

Sprawdźmy ceny:

SQL> select prix from biblio

Usuńmy zmiany wprowadzone w bazie:

SQL> rollback

i ponownie sprawdźmy ceny:

SQL> select prix from biblio

Ceny powróciły do pierwotnych wartości.

3.9. Dodawanie wierszy do tabeli z innej tabeli

Można dodawać wiersze z jednej tabeli do innej, o ile ich struktury są zgodne. Aby to pokazać, zacznijmy od utworzenia tabeli [BIBLIO2] o tej samej strukturze co [BIBLIO].

W eksploratorze baz danych IBExpert kliknijmy dwukrotnie tabelę [BIBLIO], aby uzyskać dostęp do zakładki [DDL]:

W tej zakładce znajduje się lista poleceń SQL, które umożliwiają wygenerowanie tabeli [BIBLIO]. Skopiujmy cały ten kod do schowka (CTRL-A, CTRL-C). Następnie uruchomimy narzędzie o nazwie [Script Executive], które umożliwia wykonanie listy poleceń SQL:

Pojawi się edytor tekstu, w którym możemy wkleić (CTRL-V) tekst umieszczony wcześniej w schowku:

Lista poleceń SQL jest często nazywana skryptem SQL. [Script Executive] pozwoli nam na wykonanie takiego skryptu, podczas gdy edytor SQL umożliwiał wykonywanie tylko jednego polecenia na raz. Obecny skrypt SQL pozwala utworzyć tabelę o nazwie [BIBLIO]. Sprawmy, aby utworzył tabelę o nazwie [BIBLIO2]. W tym celu wystarczy zmienić [BIBLIO] na [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);

Uruchommy ten skrypt za pomocą przycisku [Run Script] poniżej:

Skrypt został uruchomiony:

i w przeglądarce baz danych widać nową tabelę:

Jeśli dwukrotnie klikniemy na [BIBLIO2], aby sprawdzić jej zawartość, okaże się, że jest pusta, co jest normalne:

Wariant polecenia SQL INSERT pozwala na wstawienie do tabeli wierszy pochodzących z innej tabeli:

`syntaxe`	INSERT INTO table1 [(colonne1, colonne2, ...)] SELECT kolumna a, kolumna b, ... FROM table2 WHERE condition
`action`	Wiersze z pliku table2, sprawdzające plik condition, są dodawane do pliku table1. Kolumny colonnea, colonneb, ... z pliku table2 są przypisywane w kolejności do kolumn colonne1, colonne2, ... w pliku table1 i dlatego muszą być typu zgodnego.

Wróćmy do edytora SQL:

i wygenerujmy następujące polecenie SQL:

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

które wstawia do pliku [BIBLIO2] wszystkie wiersze z pliku [BIBLIO] odpowiadające powieści. Po wykonaniu polecenia SQL zatwierdźmy je poleceniem [Commit]:

SQL> commit

Po wykonaniu tej czynności sprawdźmy dane w tabeli [BIBLIO2]:

SQL> select * from BIBLIO2

3.10. Usunięcie tabeli

`syntaxe`	DROP TABLE table
`action`	supprime table

Przykład: usuwamy tabelę BIBLIO2

SQL> drop table BIBLIO2

Potwierdzamy zmianę:

SQL> commit

W eksploratorze baz danych odświeżamy wyświetlanie tabel:

Okazuje się, że tabela [BIBLIO2] została usunięta:

3.11. Modyfikacja struktury tabeli

`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`	umożliwia dodawanie (ADD), modyfikowanie (ALTER) oraz usuwanie (DROP) kolumn tabeli. Składnia nom_colonnei type_colonnei contrainte_colonnei jest taka sama jak w przypadku CREATE TABLE. Można również dodawać lub usuwać ograniczenia tabeli.

Przykład: Wykonajmy kolejno dwa poniższe polecenia SQL w edytorze SQL

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

SQL> commit

W eksploratorze baz danych sprawdźmy strukturę tabeli [BIBLIO]:

Zmiany zostały uwzględnione. Zobaczmy, jak zmieniła się zawartość tabeli:

SQL> select * from biblio

Utworzono nową kolumnę [NB_PAGES], ale nie zawiera ona żadnych wartości. Usuńmy tę kolumnę:

SQL> alter table biblio drop nb_pages

SQL> commit

Sprawdźmy nową strukturę tabeli [BIBLIO]:

Kolumna [NB_PAGES] rzeczywiście zniknęła.

3.12. Widoki

Możliwe jest utworzenie widoku częściowego jednej lub kilku tabel. Widok zachowuje się jak tabela, ale nie zawiera danych. Jego dane są pobierane z innych tabel lub widoków. Widok ma kilka zalet:

Użytkownik może być zainteresowany jedynie niektórymi kolumnami i wierszami danej tabeli. Widok pozwala mu wyświetlać wyłącznie te wiersze i kolumny.
Właściciel tabeli może chcieć zezwolić innym użytkownikom jedynie na ograniczony dostęp do niej. Widok umożliwia mu to. Użytkownicy, którym udzieli uprawnień, będą mieli dostęp wyłącznie do zdefiniowanego przez niego widoku.

3.12.1. Tworzenie widoku

`syntaxe`	CREATE VIEW nom_vue AS SELECT kolumna1, kolumna2, ... FROM table WHERE condition [ WITH CHECK OPTION ]
`action`	tworzy widok nom_vue. Jest to tabela o strukturze kolumna1, kolumna2, ... z table, a wierszami są wiersze z table spełniające warunek condition (wszystkie wiersze, jeśli nie ma warunku)
`WITH CHECK OPTION`	Ta opcjonalna klauzula określa, że operacje wstawiania i aktualizacji w widoku nie mogą tworzyć wierszy, których widok nie mógłby wybrać.

Uwaga Składnia zapytania CREATE VIEW jest w rzeczywistości bardziej złożona niż ta przedstawiona powyżej i umożliwia w szczególności utworzenie widoku na podstawie kilku tabel. W tym celu wystarczy, aby zapytanie SELECT dotyczyło kilku tabel (patrz następny rozdział).

Przykłady

Tworzymy na podstawie tabeli biblio widok zawierający wyłącznie powieści (wybór wierszy) oraz kolumny tytuł, autor, cena (wybór kolumn):

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

SQL> commit

W eksploratorze baz danych odświeżamy widok (F5). Pojawia się widok:

Można sprawdzić numer porządkowy SQL powiązany z tym widokiem. W tym celu należy dwukrotnie kliknąć widok [ROMANS]:

Widok jest jak tabela. Ma następującą strukturę:

oraz zawartość:

Widok używa się tak samo jak tabelę. Można na nim wykonywać zapytania SQL. Oto kilka przykładów do wypróbowania w edytorze SQL:

SQL> select * from romans

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

Czy nowa powieść jest widoczna w widoku [ROMANS]?

SQL> select * from romans

Dodajmy do tabeli [BIBLIO] coś innego niż powieść:

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

Sprawdźmy tabelę [BIBLIO]:

SQL> select titre, auteur from BIBLIO

Sprawdźmy widok [ROMANS]:

SQL> select titre, auteur from ROMANS

Dodana książka nie znajduje się w widoku [ROMANS], ponieważ nie miała atrybutu upper(genre)='ROMAN'.

3.12.2. Aktualizacja widoku

Widok można zaktualizować tak samo, jak tabelę. Aktualizacja ta ma wpływ na wszystkie tabele, z których pobierane są dane do widoku. Oto kilka przykładów:

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

Usuwamy wiersz z widoku [ROMANS]:

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

SQL> select * from romans

SQL> select auteur, titre from BIBLIO

Wiersz usunięty z widoku [ROMANS] został również usunięty z tabeli [BIBLIO]. Teraz podnosimy ceny książek z widoku [ROMANS]:

SQL> update romans set prix=prix*1.05

Sprawdzamy w widoku [ROMANS]:

SQL> select * from romans

Jaki wpływ miało to na tabelę [BIBLIO]?

SQL> select titre, auteur, prix from biblio

Liczba powieści również wzrosła o 5% w tabeli [BIBLIO].

3.12.3. Usuń widok

`syntaxe`	DROP VIEW nom_vue
`action`	usuwa widok o nazwie

Przykład

SQL> drop view romans

SQL> commit

W eksploratorze baz danych można odświeżyć widok (F5), aby sprawdzić, czy widok [ROMANS] zniknął:

3.13. Korzystanie z funkcji grupowych

Istnieją funkcje, które zamiast działać na każdym wierszu tabeli, działają na grupach wierszy. Są to głównie funkcje statystyczne, które pozwalają nam obliczyć średnią, odchylenie standardowe itp. dla danych z danej kolumny.

`syntaxe1`	SELECT f1, f2, .., fn FROM table [ WHERE condition ]
`action`	oblicza funkcje statystyczne fi dla wszystkich wierszy tabeli, sprawdzając ewentualną wartość condition.

syntaxe2

SELECT f1, f2, .., fn FROM table

[ WHERE condition ]

[ GROUP BY expr1, expr2, ..]

action

Słowo kluczowe GROUP BY powoduje podział wierszy tabeli na grupy. Każda grupa zawiera wiersze, dla których wyrażenia expr1, expr2, ... mają tę samą wartość.

Przykład: Słowo kluczowe GROUP BY „gatunek” umieszcza w tej samej grupie książki o tym samym gatunku. Klauzula GROUP BY autor,gatunek umieściłaby w tej samej grupie książki tego samego autora i tego samego gatunku. Klauzula WHERE warunek najpierw eliminuje z tabeli wiersze, które nie spełniają warunku. Następnie tworzone są grupy za pomocą klauzuli GROUP BY. Następnie dla każdej grupy wierszy obliczane są funkcje fi.

`syntaxe3`	SELECT f1, f2, .., fn FROM table [ WHERE condition ] [ GROUP BY expression] [ HAVING condition_de_groupe]
`action`	Klauzula HAVING filtruje grupy utworzone przez klauzulę GROUP BY. Jest ona zatem zawsze powiązana z obecnością klauzuli GROUP BY. Przykład: GROUP BY rodzaj HAVING rodzaj!='ROMAN'

Dostępne funkcje statystyczne fi to:

`AVG(expression)`	średnia wyrażenia
`COUNT(expression)`	liczba wierszy, w których wyrażenie ma wartość
`COUNT(*)`	całkowita liczba wierszy w tabeli
`MAX(expression)`	maksymalna wartość wyrażenia
`MIN(expression)`	minimalna wartość wyrażenia
`SUM(expression)`	suma wyrażenia

Przykłady

SQL> select prix from biblio

Średnia cena? Maksymalna cena? Minimalna cena?

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

SQL> select titre, prix,genre from biblio

Średnia cena powieści? Cena maksymalna?

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

Ile to kosztuje BD?

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

Ile powieści kosztuje mniej niż 100 F?

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

SQL> select genre, prix from biblio

Ile jest książek i jaka jest średnia cena książki w ramach tego samego gatunku?

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

To samo pytanie, ale tylko w odniesieniu do książek, które nie są powieściami:

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

To samo pytanie, ale dotyczy wyłącznie książek poniżej 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'

To samo zapytanie, ale uwzględniamy tylko grupy, których średnia cena za książkę jest większa niż 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. Utwórz skrypt SQL dla tabeli „ ”

Język SQL jest standardowym językiem, który można stosować z wieloma skryptami SGBD. Aby móc przechodzić z jednego skryptu SGBD do innego, warto wyeksportować bazę danych lub po prostu niektóre jej elementy w postaci skryptu SQL, który po uruchomieniu w innym środowisku SGBD będzie w stanie odtworzyć elementy wyeksportowane w skrypcie.

W tym miejscu wyeksportujemy tabelę [BIBLIO]. Wybierzmy opcję [Extract Metadata]:

Jak widać powyżej, należy przejść do bazy, z której chcemy wyeksportować elementy. Opcja ta uruchamia kreatora:

`1`	gdzie wygenerować skrypt SQL: w pliku (File) do schowka (Clipboard) w narzędziu Script Executive
`2`	nazwa pliku, jeśli wybrano opcję [File]
`3`	co wyeksportować
`4`	przyciski do zaznaczania (->) lub odznaczania (<-) obiektów do eksportu

Gdybyśmy chcieli wyeksportować całą bazę, zaznaczylibyśmy powyższą opcję [Extract All]. Chcemy po prostu wyeksportować tabelę BIBLIO. W tym celu za pomocą opcji [4] zaznaczamy tabelę [BIBLIO], a za pomocą opcji [2] wskazujemy plik:

Jeśli na tym poprzestaniemy, wyeksportowana zostanie jedynie struktura tabeli [BIBLIO]. Aby wyeksportować jej zawartość, należy skorzystać z zakładki [Data Tables]:

Użyjmy [1], aby wybrać tabelę [BIBLIO]:

Użyjmy [2], aby wygenerować skrypt SQL:

Zaakceptujmy ofertę. Dzięki temu możemy zobaczyć skrypt, który został wygenerowany w pliku [biblio.sql]:

/******************************************************************************/
/****          Wygenerowane przez 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;



/******************************************************************************/
/****                                                                 Tabele ****/
/******************************************************************************/



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;



/******************************************************************************/
/****                                                     Ograniczenia unikalności ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);


/******************************************************************************/
/****                                                           Klucze główne ****/
/******************************************************************************/

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

wiersze od 1 do 3 to komentarze
wiersze od 5 do 12 to kod własnościowy SQL należący do Firebird
pozostałe wiersze to standardowy kod SQL, który powinien dać się odtworzyć w pliku SGBD zawierającym typy danych zadeklarowane w tabeli BIBLIO.

Uruchommy ponownie ten skrypt w Firebirdzie, aby utworzyć tabelę BIBLIO2, która będzie klonem tabeli BIBLIO. W tym celu użyjmy [Script Executive] (Ctrl-F12):

Załadujmy właśnie wygenerowany skrypt [biblio.sql]:

Zmodyfikujmy go tak, aby zachować tylko część dotyczącą tworzenia tabeli i wstawiania wierszy. Tabela zostaje przemianowana na [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;

Uruchommy ten skrypt:

W przeglądarce baz danych możemy sprawdzić, czy tabela [BIBLIO2] została rzeczywiście utworzona i czy ma oczekiwaną strukturę oraz zawartość: