3. Einführung in die Sprache SQL

In diesem Abschnitt stellen wir die ersten Befehle SQL vor, mit denen eine einzelne Tabelle erstellt und verwaltet werden kann. Wir geben in der Regel eine vereinfachte Version wieder. Die vollständige Syntax finden Sie in den Firebird-Referenzhandbüchern (siehe Abschnitt 2.2).

Eine Datenbank wird von Personen mit unterschiedlichen Kompetenzen genutzt:

Der Datenbankadministrator ist in der Regel jemand, der die Sprache SQL und Datenbanken beherrscht. Er ist derjenige, der die Tabellen anlegt, da dieser Vorgang in der Regel nur einmal durchgeführt wird. Im Laufe der Zeit kann es vorkommen, dass er deren Struktur ändern muss. Eine Datenbank ist eine Sammlung von Tabellen, die durch Beziehungen miteinander verknüpft sind. Der Datenbankadministrator definiert diese Beziehungen. Er ist es auch, der den verschiedenen Benutzern der Datenbank Berechtigungen erteilt. So legt er fest, dass ein bestimmter Benutzer das Recht hat, den Inhalt einer Tabelle einzusehen, diese jedoch nicht zu ändern.
Der Datenbankbenutzer ist derjenige, der die Daten mit Leben erfüllt. Je nach den vom Datenbankadministrator gewährten Rechten fügt er Daten in den verschiedenen Tabellen der Datenbank hinzu, ändert sie oder löscht sie. Er wertet sie auch aus, um daraus Informationen zu gewinnen, die für den reibungslosen Ablauf des Unternehmens, der Verwaltung usw. nützlich sind.

In Abschnitt 2.6 haben wir den Editor SQL des Tools [IB-Expert] vorgestellt. Dieses Tool werden wir nun verwenden. Hier noch einmal einige wichtige Punkte:

Der Editor SQL wird über die Menüoption [Tools/SQL Editor] oder über die Taste [F12] aufgerufen

Daraufhin erscheint ein Fenster mit der Bezeichnung [SQL Editor], in das wir den Befehl SQL eingeben können:

Der obige Screenshot wird häufig durch den folgenden Text dargestellt:

SQL> select * from BIBLIO

3.1. Die Datentypen von Firebird

Beim Anlegen einer Tabelle muss angegeben werden, welchen Datentyp eine Tabellenspalte enthalten darf. Hier stellen wir die gängigsten Firebird-Datentypen vor. Es sei darauf hingewiesen, dass diese Datentypen von einer SGBD zur anderen variieren können.

`SMALLINT`	Ganzzahl im Bereich [-32768, 32767]: 4
`INTEGER`	Ganzzahl im Bereich [–2 147 483 648, 2 147 483 647]: -100
`NUMERIC(n,m) DECIMAL(n,m)`	Reelle Zahl mit n Ziffern, davon m nach dem Komma NUMERIC(5,2): -100,23, +027,30
`FLOAT`	Reelle Zahl, gerundet auf 7 signifikante Stellen: 10,4
`DOUBLE PRECISION`	Reelle Zahl, gerundet auf 15 signifikante Stellen: -100.89
`CHAR(N) CHARACTER(N)`	Zeichenkette mit genau N Zeichen. Wenn die gespeicherte Zeichenkette weniger als N Zeichen enthält, wird sie mit Leerzeichen aufgefüllt. CHAR(10): 'ANGERS ' (4 Leerzeichen am Ende)
`VARCHAR(N) CHARACTER VARYING(N)`	Zeichenkette mit höchstens N Zeichen VARCHAR(10): 'ANGERS'
`DATE`	ein Datum: '2006-01-09' (Format YYYY-MM-DD)
`TIME`	eine Uhrzeit: '16:43:00' (Format HH:MM:SS)
`TIMESTAMP`	Datum und Uhrzeit zusammen: '2006-01-09 16:43:00' (Format YYYY-MM-DD HH:MM:SS)

Die Funktion CAST() ermöglicht es, bei Bedarf von einem Typ zum anderen zu wechseln. Um einen Wert V, der als Typ T1 deklariert ist, in den Typ T2 umzuwandeln, schreibt man: CAST(V,T2). Folgende Typumwandlungen sind möglich:

Zahl in Zeichenkette. Diese Typumwandlung erfolgt implizit und erfordert nicht die Verwendung der Funktion CAST. So erfordert die Operation 1 + '3' keine Konvertierung des Zeichens '3'. Das Ergebnis ist die Zahl 4.
DATE, TIME, TIMESTAMP in Zeichenfolgen und umgekehrt. So
TIMESTAMP in TIME oder DATE und umgekehrt

In einer Tabelle kann eine Zeile Spalten ohne Wert enthalten. Man sagt, der Wert der Spalte sei die Konstante NULL. Das Vorhandensein dieses Werts lässt sich mit Hilfe der Operatoren prüfen

IS NULL / IS NOT NULL

3.2. Erstellen einer Tabelle

Um zu erfahren, wie man eine Tabelle anlegt, erstellen wir zunächst eine im Modus [Design] mit IBExpert. Dazu folgen wir der in Abschnitt 2.3 beschriebenen Vorgehensweise. So erstellen wir die folgende Tabelle:

Diese Tabelle dient dazu, die von einer Bibliothek erworbenen Bücher zu erfassen. Die Felder haben folgende Bedeutung:

Name	Typ	Einschränkung	Bedeutung
`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)`

Diese Tabelle, die mit dem Tool IBEXPERT als Assistent erstellt wurde, hätte auch direkt über die Befehle SQL erstellt werden können. Um diese zu erfahren, genügt es, die Registerkarte [DDL] der Tabelle aufzurufen:

Der Code SQL, mit dem die Tabelle [BIBLIO] angelegt wurde, lautet wie folgt:

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

Zeile 1: Eigentümer Firebird – gibt die verwendete Dialektstufe SQL an
Zeile 2: Firebird-Eigentümer – gibt die verwendete Zeichenfamilie an
Zeilen 6–14: SQL-Standard: Erstellt die Tabelle BIBLIO, indem der Name und der Typ jeder ihrer Spalten definiert werden.
Zeile 16: Standard SQL: Erstellt eine Einschränkung, die angibt, dass die Spalte TITRE keine Duplikate zulässt
Zeile 17: Standard SQL: Gibt an, dass die Spalte [ID] der Primärschlüssel der Tabelle ist. Das bedeutet, dass zwei Zeilen der Tabelle nicht denselben Wert für ID haben dürfen. Dies ähnelt der Einschränkung [UNIQUE NOT NULL] für die Spalte [TITRE], und tatsächlich hätte die Spalte TITRE als Primärschlüssel dienen können. Der aktuelle Trend geht dahin, Primärschlüssel zu verwenden, die keine Bedeutung haben und vom SGBD generiert werden.

Die Syntax des Befehls [CREATE TABLE] lautet wie folgt:

syntaxe

CREATE TABLE Tabelle (nom_colonne1 type_colonne1 contrainte_colonne1, nom_colonne2 type_colonne2 contrainte_colonne2, ..., nom_colonnen type_colonnen contrainte_colonnen, weitere Einschränkungen)

action

legt die Tabelle table mit den angegebenen Spalten an

`nom_colonnei`	Name der zu erstellenden Spalte i
`type_colonnei`	Datentyp der Spalte i: char(30) numeric(6,2) date timestamp ...
`contrainte_colonnei`	Einschränkungen, denen die Daten der Spalte i entsprechen müssen. Hier sind einige davon: PRIMARY KEY: Die Spalte ist ein Primärschlüssel. Das bedeutet, dass zwei Zeilen der Tabelle niemals denselben Wert in dieser Spalte haben dürfen und dass ein Wert in dieser Spalte obligatorisch ist. Ein Primärschlüssel dient in erster Linie dazu, eine Zeile eindeutig zu identifizieren. NOT NULL : In dieser Spalte sind keine Nullwerte zulässig. UNIQUE : Kein Wert darf in der Spalte mehrfach vorkommen. CHECK (Bedingung): Der Wert der Spalte muss die Bedingung erfüllen.
`autres contraintes`	Hier können - Einschränkungen über mehrere Spalten hinweg festlegen: check(col1>col2) - Fremdschlüsselbedingungen

Die Tabelle [BIBLIO] hätte auch mit dem folgenden Befehl SQL erstellt werden können:

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

Lassen Sie uns das veranschaulichen. Nehmen wir diese Reihenfolge in einem Editor SQL (F12) auf, um eine Tabelle zu erstellen, die wir [BIBLIO2] nennen werden:

Nach der Ausführung muss die Transaktion bestätigt werden, um das Ergebnis in der Datenbank zu sehen:

Danach erscheint die Tabelle in der Datenbank:

Durch Doppelklick auf den Namen kann man die Struktur der Tabelle einsehen:

Dort findet man tatsächlich die Definition, die wir für die Tabelle [BIBLIO2] erstellt haben

3.3. Löschen einer Tabelle

Der Befehl SQL zum Löschen einer Tabelle lautet wie folgt:

`syntaxe`	DROP TABLE Tabelle
`action`	Löscht [table]

Um die soeben erstellte Tabelle [BIBLIO2] zu löschen, führen wir nun den folgenden Befehl SQL aus:

und bestätigen ihn mit [Commit]. Die Tabelle [BIBLIO2] wird gelöscht:

3.4. Eine Tabelle füllen

Fügen wir eine Zeile in die soeben erstellte Tabelle [BIBLIO] ein:

Bestätigen wir das Hinzufügen der Zeile mit [Commit] und klicken wir dann mit der rechten Maustaste auf die hinzugefügte Zeile:

und fordern wir, wie oben gezeigt, an, dass die eingefügte Zeile in Form eines Befehls SQL INSERT in die Zwischenablage kopiert wird. Öffnen wir anschließend einen beliebigen Texteditor und fügen wir den soeben kopierten Text ein (Einfügen / Paste). Wir erhalten den folgenden Code SQL:

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

Die Syntax eines Befehls „SQL insert“ lautet wie folgt:

`syntaxe`	INSERT INTO TABELLE [(colonne1, colonne2, ..)] VALUES (Wert1, Wert2, ....)
`action`	fügt eine Zeile (Wert1, Wert2, ..) zur Tabelle table hinzu. Diese Werte werden den Spalten colonne1, colonne2, ... zugewiesen, sofern diese vorhanden sind; andernfalls werden sie den Spalten der Tabelle in der Reihenfolge zugewiesen, in der sie definiert wurden.

Um neue Zeilen in die Tabelle [BIBLIO] einzufügen, geben Sie die folgenden Befehle INSERT in den Editor SQL ein. Diese Befehle werden nacheinander ausgeführt und bestätigt. Mit der Schaltfläche [New Query] wechselt man zum nächsten Befehl INSERT.

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

Nachdem die verschiedenen Aufträge SQL bestätigt wurden, erhalten wir die folgende Tabelle:

3.5. Tabelle anzeigen

3.5.1. Einleitung

Geben Sie im Editor SQL den folgenden Befehl ein:

und führen wir ihn aus. Wir erhalten folgendes Ergebnis:

Mit dem Befehl SELECT können wir den Inhalt von Datenbanktabellen abfragen. Dieser Befehl verfügt über eine sehr umfangreiche Syntax. Wir stellen hier nur die Syntax vor, mit der eine einzelne Tabelle abgefragt werden kann. Auf die gleichzeitige Abfrage mehrerer Tabellen werden wir später eingehen. Die Syntax des Befehls SQL [SELECT] lautet wie folgt:

syntaxe

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

FROM table

action

zeigt die Werte von expressioni für alle Tabellenzeilen an. expressioni kann eine Spalte oder ein komplexerer Ausdruck sein. Das Symbol * bezeichnet die Gesamtheit aller Spalten. Standardmäßig werden alle Tabellenzeilen (ALL) angezeigt. Ist DISTINCT vorhanden, werden identische Zeilen nur einmal angezeigt. Die Werte von expressioni werden in einer Spalte mit der Überschrift expressioni oder aliasi angezeigt, sofern diese verwendet wurde.

Beispiele:

SQL > select titre, auteur from biblio

SQL> select titre,prix from biblio

SQL> select titre TITRE_DU_LIVRE, prix PRIX_ACHAT from biblio

Oben haben wir den angeforderten Spalten Aliase (TITRE_DU_LIVRE, PRIX_ACHAT) zugewiesen.

3.5.2. Anzeige der Zeilen, die eine Bedingung erfüllen

`syntaxe`	SELECT .... WHERE condition
`action`	Es werden nur die Zeilen angezeigt, die die Bedingung condition erfüllen

Beispiele

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

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

Eines der Bücher hat das Genre „Roman“ und nicht „Roman“. Wir verwenden die Funktion upper, die eine Zeichenfolge in Großbuchstaben umwandelt, um alle Romane zu erhalten.

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

Wir können Bedingungen mithilfe logischer Operatoren verknüpfen

`AND`	ET logisch
`OR`	OU Logik
`NOT`	Logische Negation

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. Anzeige der Zeilen in einer bestimmten Reihenfolge

Zu den oben genannten Syntaxen kann eine Klausel ORDER BY hinzugefügt werden, die die gewünschte Anzeigereihenfolge angibt:

`syntaxe`	SELECT .... ORDER BY expression1 [asc\|desc], expression2 [asc\|dec], ...
`action`	Die Zeilen, die aus der Auswahl resultieren, werden in der Reihenfolge von 1: in aufsteigender Reihenfolge (asc / ascending, dies ist der Standardwert) oder in absteigender Reihenfolge (desc / descending) von expression1 2: Bei Gleichheit von expression1 erfolgt die Anzeige entsprechend den Werten von expression2 usw.

Beispiele:

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. Zeilen aus einer Tabelle löschen

`syntaxe`	DELETE FROM table [WHERE condition]
`action`	löscht die Zeilen von table, indem es condition überprüft. Fehlt letzteres, werden alle Zeilen gelöscht.

Beispiele:

SQL> select titre from biblio

Die beiden folgenden Befehle werden nacheinander ausgeführt:

SQL> delete from biblio where titre='Candide'

SQL> select titre from biblio

3.7. Änderung des Inhalts einer Tabelle

`syntaxe`	update table set Spalte1 = Ausdruck1, Spalte2 = Ausdruck2, ... [where condition]
`action`	Für die Zeilen von table, die condition prüfen (alle Zeilen, wenn keine Bedingung vorliegt), erhält colonnei den Wert expressioni.

Beispiele:

SQL> select genre from biblio

Alle Gattungen werden großgeschrieben:

SQL> update biblio set genre=upper(genre)

Überprüfung:

SQL> select genre from biblio

Die Preise werden angezeigt:

SQL> select genre,prix from biblio;

Die Preise für Romane steigen um 5 %:

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

Wir überprüfen:

SQL> select genre,prix from biblio

3.8. Endgültige Aktualisierung einer Tabelle

Wenn Änderungen an einer Tabelle vorgenommen werden, führt Firebird diese tatsächlich an einer Kopie der Tabelle durch. Sie können dann mit den Befehlen COMMIT und ROLLBACK endgültig übernommen oder rückgängig gemacht werden.

`syntaxe`	COMMIT
`action`	macht die seit dem letzten COMMIT an den Tabellen vorgenommenen Aktualisierungen endgültig.

`syntaxe`	ROLLBACK
`action`	macht alle Änderungen rückgängig, die seit dem letzten COMMIT an den Tabellen vorgenommen wurden.

Remarque

Ein COMMIT wird implizit zu folgenden Zeitpunkten ausgeführt:

a) Beim Abmelden von Firebird

b) Nach jedem Befehl, der die Struktur der Tabellen beeinflusst: CREATE, ALTER, DROP.

Beispiele

Im Editor SQL wird die Datenbank in einen bekannten Zustand versetzt, indem alle seit dem letzten COMMIT oder ROLLBACK durchgeführten Operationen validiert werden:

SQL> commit

Man fordert die Liste der Titel an:

SQL> select titre from biblio

Löschen eines Titels:

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

Überprüfung:

SQL> select titre from biblio

Der Titel wurde erfolgreich gelöscht. Nun machen wir alle Änderungen rückgängig, die seit dem letzten COMMIT / ROLLBACK vorgenommen wurden:

SQL> rollback

Überprüfung:

SQL> select titre from biblio

Der Titel ist wieder vorhanden. Fordern wir nun die Preisliste an:

SQL> select prix from biblio

Nehmen wir an, alle Preise wurden auf Null gesetzt.

SQL> update biblio set prix=0

Überprüfen wir die Preise:

SQL> select prix from biblio

Lassen Sie uns die an der Datenbank vorgenommenen Änderungen rückgängig machen:

SQL> rollback

und überprüfen wir die Preise erneut:

SQL> select prix from biblio

Wir haben die ursprünglichen Preise wiederhergestellt.

3.9. Zeilen aus einer Tabelle in eine andere Tabelle einfügen

Es ist möglich, Zeilen aus einer Tabelle in eine andere Tabelle hinzuzufügen, sofern deren Strukturen kompatibel sind. Um dies zu veranschaulichen, erstellen wir zunächst eine Tabelle [BIBLIO2] mit derselben Struktur wie [BIBLIO].

Doppelklicken Sie im Datenbank-Explorer von IBExpert auf die Tabelle [BIBLIO], um die Registerkarte [DDL] aufzurufen:

Auf dieser Registerkarte finden Sie die Liste der Befehle SQL, mit denen die Tabelle [BIBLIO] generiert werden kann. Kopieren Sie den gesamten Code in die Zwischenablage (CTRL-A, CTRL-C). Rufen wir anschließend ein Tool namens [Script Executive] auf, mit dem eine Liste von Befehlen SQL ausgeführt werden kann:

Es erscheint ein Texteditor, in den wir den zuvor in die Zwischenablage kopierten Text (CTRL-V) einfügen können:

Eine Liste von Befehlen SQL wird oft als Skript SQL bezeichnet. Mit [Script Executive] können wir ein solches Skript ausführen, während der Editor SQL jeweils nur die Ausführung eines einzigen Befehls erlaubte. Mit dem aktuellen Skript SQL lässt sich die Tabelle [BIBLIO] anlegen. Sorgen wir dafür, dass es eine Tabelle namens [BIBLIO2] erstellt. Dazu muss lediglich [BIBLIO] in [BIBLIO2] geändert werden:

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

Führen wir dieses Skript über die Schaltfläche [Run Script] unten aus:

Das Skript wird ausgeführt:

und die neue Tabelle ist im Datenbank-Explorer zu sehen:

Wenn man auf [BIBLIO2] doppelklickt, um den Inhalt zu überprüfen, stellt man fest, dass sie leer ist, was normal ist:

Eine Variante des Befehls SQL INSERT ermöglicht es, Zeilen aus einer anderen Tabelle in eine Tabelle einzufügen:

`syntaxe`	INSERT INTO table1 [(colonne1, colonne2, ...)] SELECT Spalte a, Spalte b, ... FROM table2 WHERE condition
`action`	Die Zeilen von table2, die condition überprüfen, werden zu table1 hinzugefügt. Die Spalten „colonnea“, „colonneb“, … von table2 werden der Reihe nach den Spalten „colonne1“, „colonne2“, … von table1 zugeordnet und müssen daher vom kompatiblen Typ sein.

Kehren wir zum Editor SQL zurück:

und geben wir den folgenden Befehl SQL ein:

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

der in [BIBLIO2] alle Zeilen aus [BIBLIO] einfügt, die einem Roman entsprechen. Nach Ausführung des Befehls SQL bestätigen wir diesen mit einem [Commit]:

SQL> commit

Anschließend rufen wir die Daten der Tabelle [BIBLIO2] ab:

SQL> select * from BIBLIO2

3.10. Löschen einer Tabelle

`syntaxe`	DROP TABLE table
`action`	löscht table

Beispiel: Die Tabelle BIBLIO2 wird gelöscht

SQL> drop table BIBLIO2

Die Änderung wird bestätigt:

SQL> commit

Im Datenbank-Explorer wird die Tabellenansicht aktualisiert:

Man stellt fest, dass die Tabelle [BIBLIO2] gelöscht wurde:

3.11. Änderung der Tabellenstruktur

`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`	ermöglicht das Hinzufügen (ADD), Ändern (ALTER) und Löschen (DROP) von Tabellenspalten. Die Syntax nom_colonnei type_colonnei contrainte_colonnei entspricht der von CREATE TABLE. Es können auch Tabelleneinschränkungen hinzugefügt bzw. gelöscht werden.

Beispiel: Führen wir nacheinander die beiden folgenden Befehle SQL im Editor SQL aus

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

SQL> commit

Überprüfen wir im Datenbank-Explorer die Struktur der Tabelle [BIBLIO]:

Die Änderungen wurden übernommen. Sehen wir uns an, wie sich der Inhalt der Tabelle verändert hat:

SQL> select * from biblio

Die neue Spalte [NB_PAGES] wurde angelegt, enthält jedoch keine Werte. Löschen wir diese Spalte:

SQL> alter table biblio drop nb_pages

SQL> commit

Überprüfen wir die neue Struktur der Tabelle [BIBLIO]:

Die Spalte [NB_PAGES] ist tatsächlich verschwunden.

3.12. Sichten

Es ist möglich, eine Teilansicht einer Tabelle oder mehrerer Tabellen zu erstellen. Eine Ansicht verhält sich wie eine Tabelle, enthält jedoch keine Daten. Ihre Daten werden aus anderen Tabellen oder Ansichten extrahiert. Eine Ansicht bietet mehrere Vorteile:

Ein Benutzer ist möglicherweise nur an bestimmten Spalten und Zeilen einer bestimmten Tabelle interessiert. Die Ansicht ermöglicht es ihm, nur diese Zeilen und Spalten anzuzeigen.
Der Eigentümer einer Tabelle möchte möglicherweise anderen Benutzern nur einen eingeschränkten Zugriff gewähren. Die Ansicht ermöglicht ihm dies. Die von ihm autorisierten Benutzer haben nur Zugriff auf die von ihm definierte Ansicht.

3.12.1. Erstellen einer Ansicht

`syntaxe`	CREATE VIEW nom_vue AS SELECT Spalte1, Spalte2, ... FROM table WHERE condition [ WITH CHECK OPTION ]
`action`	legt die Ansicht nom_vue an. Diese ist eine Tabelle mit der Struktur „Spalte1, Spalte2, …“ aus table und mit den Zeilen aus table, die die Bedingung condition erfüllen (alle Zeilen, wenn keine Bedingung vorliegt)
`WITH CHECK OPTION`	Diese optionale Klausel legt fest, dass Einfügungen und Aktualisierungen in der Ansicht keine Zeilen erzeugen dürfen, die die Ansicht nicht auswählen könnte.

Anmerkung Die Syntax von CREATE VIEW ist tatsächlich komplexer als die oben dargestellte und ermöglicht insbesondere die Erstellung einer Ansicht aus mehreren Tabellen. Dazu muss lediglich die Abfrage SELECT mehrere Tabellen betreffen (siehe nächstes Kapitel).

Beispiele

Ausgehend von der Tabelle „biblio“ wird eine Ansicht erstellt, die nur die Romane (Zeilenauswahl) und nur die Spalten „Titel“, „Autor“ und „Preis“ (Spaltenauswahl) enthält:

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

SQL> commit

Im Datenbank-Explorer aktualisieren wir die Ansicht (F5). Es erscheint eine Ansicht:

Man kann den zur Ansicht gehörenden Auftrag SQL ermitteln. Dazu doppelklicken wir auf die Ansicht [ROMANS]:

Eine Ansicht ist wie eine Tabelle. Sie hat folgende Struktur:

und einen Inhalt:

Eine Ansicht wird wie eine Tabelle verwendet. Man kann Abfragen SQL darauf ausführen. Hier sind einige Beispiele, die Sie im Editor SQL ausprobieren können:

SQL> select * from romans

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

Ist der neue Roman in der Ansicht [ROMANS] sichtbar?

SQL> select * from romans

Fügen wir der Tabelle [BIBLIO] noch etwas anderes als einen Roman hinzu:

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

Überprüfen wir die Tabelle [BIBLIO]:

SQL> select titre, auteur from BIBLIO

Überprüfen wir die Ansicht [ROMANS]:

SQL> select titre, auteur from ROMANS

Das hinzugefügte Buch ist nicht in der Ansicht [ROMANS] enthalten, da es nicht über den Eintrag „upper(genre)='ROMAN'“ verfügte.

3.12.2. Aktualisierung einer Ansicht

Eine Ansicht kann genauso aktualisiert werden wie eine Tabelle. Alle Tabellen, aus denen die Daten der Ansicht extrahiert werden, sind von dieser Aktualisierung betroffen. Hier einige Beispiele:

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

Eine Zeile wird aus der Ansicht [ROMANS] gelöscht:

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

SQL> select * from romans

SQL> select auteur, titre from BIBLIO

Die gelöschte Zeile aus der Ansicht [ROMANS] wurde ebenfalls in der Tabelle [BIBLIO] gelöscht. Nun wird der Preis der Bücher aus der Ansicht [ROMANS] erhöht:

SQL> update romans set prix=prix*1.05

Wir überprüfen dies in [ROMANS]:

SQL> select * from romans

Welche Auswirkungen hatte dies auf die Tabelle [BIBLIO]?

SQL> select titre, auteur, prix from biblio

Auch in der Tabelle [BIBLIO] wurden die Romanzahlen um 5 % erhöht.

3.12.3. Eine Ansicht löschen

`syntaxe`	DROP VIEW nom_vue
`action`	löscht die Ansicht mit dem Namen

Beispiel

SQL> drop view romans

SQL> commit

Im Datenbank-Explorer kann man die Ansicht (F5) aktualisieren, um festzustellen, dass die Ansicht [ROMANS] verschwunden ist:

3.13. Verwendung von Gruppenfunktionen

Es gibt Funktionen, die nicht jede Zeile einer Tabelle einzeln bearbeiten, sondern auf Zeilengruppen anwenden. Dabei handelt es sich im Wesentlichen um statistische Funktionen, mit denen wir den Mittelwert, die Standardabweichung usw. der Daten einer Spalte ermitteln können.

`syntaxe1`	SELECT f1, f2, .., fn FROM table [ WHERE condition ]
`action`	berechnet die statistischen Funktionen fi über alle Tabellenzeilen und prüft dabei auf das Vorhandensein von condition.

syntaxe2

SELECT f1, f2, .., fn FROM table

[ WHERE condition ]

[ GROUP BY expr1, expr2, ..]

action

Das Schlüsselwort GROUP BY bewirkt, dass die Tabellenzeilen in Gruppen unterteilt werden. Jede Gruppe enthält die Zeilen, für die die Ausdrücke expr1, expr2, … denselben Wert haben.

Beispiel: GROUP BY genre fasst Bücher desselben Genres in einer Gruppe zusammen. Die Klausel GROUP BY „Autor,Genre“ würde Bücher mit demselben Autor und demselben Genre in dieselbe Gruppe einordnen. Die Klausel WHERE „Bedingung“ entfernt zunächst alle Zeilen aus der Tabelle, die die Bedingung nicht erfüllen. Anschließend werden die Gruppen durch die Klausel GROUP BY gebildet. Die Funktionen fi werden dann für jede Zeilengruppe berechnet.

`syntaxe3`	SELECT f1, f2, .., fn FROM table [ WHERE condition ] [ GROUP BY expression] [ HAVING condition_de_groupe]
`action`	Die Klausel HAVING filtert die durch die Klausel GROUP BY gebildeten Gruppen. Sie ist daher immer an das Vorhandensein dieser Klausel GROUP BY gebunden. Beispiel: GROUP BY Gattung HAVING Gattung!='ROMAN'

Die folgenden statistischen Funktionen fi stehen zur Verfügung:

`AVG(expression)`	Mittelwert des Ausdrucks
`COUNT(expression)`	Anzahl der Zeilen, in denen der Ausdruck einen Wert hat
`COUNT(*)`	Gesamtzahl der Zeilen in der Tabelle
`MAX(expression)`	Maximalwert des Ausdrucks
`MIN(expression)`	Minimaler Ausdruck
`SUM(expression)`	Summe des Ausdrucks

Beispiele

SQL> select prix from biblio

Durchschnittspreis? Höchstpreis? Mindestpreis?

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

SQL> select titre, prix,genre from biblio

Durchschnittspreis eines Romans? Höchstpreis?

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

Wie viele BD?

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

Wie viele Romane kosten weniger als 100 F?

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

SQL> select genre, prix from biblio

Anzahl der Bücher und Durchschnittspreis pro Buch für Bücher desselben Genres?

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

Dieselbe Frage, jedoch nur für Bücher, die keine Romane sind:

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

Dieselbe Frage, jedoch nur für Bücher unter 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'

Gleiche Frage, aber es werden nur die Gruppen berücksichtigt, deren durchschnittlicher Buchpreis >100 F beträgt

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

3.14. Das Skript „ “ SQL für eine Tabelle erstellen

Die Sprache SQL ist eine Standardsprache, die mit zahlreichen SGBD verwendet werden kann. Um von einem SGBD zu einem anderen wechseln zu können, ist es sinnvoll, eine Datenbank oder auch nur bestimmte Elemente daraus in Form eines SQL-Skripts zu exportieren, das, wenn es in einem anderen SGBD ausgeführt wird, die im Skript exportierten Elemente wiederherstellen kann.

Wir werden hier die Tabelle [BIBLIO] exportieren. Nehmen wir die Option [Extract Metadata]:

Wie oben zu sehen ist, muss man sich auf der Datenbank befinden, aus der man Elemente exportieren möchte. Die Option startet einen Assistenten:

`1`	Wo soll das Skript „SQL“ generiert werden: in einer Datei (File) in die Zwischenablage (Clipboard) im Tool „Script Executive“
`2`	Dateiname, wenn die Option [File] ausgewählt ist
`3`	Was soll exportiert werden?
`4`	Schaltflächen zum Auswählen (->) oder Abwählen (<-) der zu exportierenden Objekte

Wenn wir die gesamte Datenbank exportieren wollten, würden wir oben die Option [Extract All] aktivieren. Wir möchten lediglich die Tabelle BIBLIO exportieren. Dazu wählen wir mit [4] die Tabelle [BIBLIO] aus und geben mit [2] eine Datei an:

Wenn wir es dabei belassen, wird nur die Struktur der Tabelle [BIBLIO] exportiert. Um deren Inhalt zu exportieren, müssen wir die Registerkarte [Data Tables] verwenden:

Verwenden wir [1], um die Tabelle [BIBLIO] auszuwählen:

Verwenden wir [2], um das Skript SQL zu generieren:

Nehmen wir das Angebot an. Dadurch können wir das generierte Skript in der Datei [biblio.sql] einsehen:

/******************************************************************************/
/****          Generiert von 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;



/******************************************************************************/
/****                                                                 Tabellen ****/
/******************************************************************************/



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;



/******************************************************************************/
/****                                                     Eindeutigkeitsbeschränkungen ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);


/******************************************************************************/
/****                                                           Primärschlüssel ****/
/******************************************************************************/

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

Die Zeilen 1 bis 3 sind Kommentare
Die Zeilen 5 bis 12 sind SQL-Code von Firebird
Die übrigen Zeilen stammen aus dem Standard-SQL und sollten in einem SGBD wiederhergestellt werden können, in dem die Datentypen in der Tabelle BIBLIO deklariert sind.

Führen wir dieses Skript nun in Firebird aus, um eine Tabelle BIBLIO2 zu erstellen, die ein Klon der Tabelle BIBLIO sein wird. Verwenden wir dazu [Script Executive] (Strg-F12):

Laden wir nun das soeben generierte Skript [biblio.sql]:

Passen wir es so an, dass nur der Teil zum Anlegen der Tabelle und zum Einfügen von Zeilen erhalten bleibt. Die Tabelle wird in [BIBLIO2] umbenannt:

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;

Führen wir dieses Skript aus:

Im Datenbank-Explorer können wir überprüfen, ob die Tabelle [BIBLIO2] tatsächlich angelegt wurde und die erwartete Struktur sowie den erwarteten Inhalt aufweist: