Software Development : SQL Tutorial Part II

von Manuel Soriano

Über den Autor:

Manuel ist ein Software Consultant und seine Leidenschaft ist Linux. Er hat schon mit Computern gearbeitet als ein einziger Computer noch einen ganzen Raum ausf�llte und nur 32K Ram hatte.

Inhalt:

Einf�hrung
Erzeugen einer Tabelle
Ver�ndern einer Tabelle
Einf�gen von Daten in eine Tabelle
Daten ausw�hlen
Einzelheiten �ber WHERE
Der UPDATE Befehl
Der DELETE Befehl
Referenzen

SQL Tutorial Part II

Zusammenfassung:

In Teil II dieses Tutorials untersuchen wir einige Grundanweisungen der SQL-Sprache.

Einf�hrung

Dies ist der zweite Teil des SQL Kurses. In diesem Teil konzentrieren wir uns auf verschiedene SQL Befehle f�r die Erzeugung von Tabellen, ihrer Ver�nderung und/oder ihrer L�schung.

Au�erdem behandeln wir den SELECT Befehl, der, meiner Einsch�tzung nach, der wichtigste von allen ist.

Ich hoffe, da� dir das Lesen dieses zweiten Teils Spa� machen wird und er f�r dich lehrreich ist.

Erzeugen einer Tabelle

Wie wir im ersten Artikel gesehen haben, wird zum Erzeugen einer Tabelle der Befehl CREATE mit dem Argument TABELLEN benutzt. Der Befehl CREATE dient dazu, das folgende zu erzeugen:

Benutzer: CREATE USER
Tabellen: CREATE TABLE
Indexe: CREATE INDEX
Sichten: CREATE VIEW

Der CREATE Befehl gibt dem Manager an, da� etwas erzeugt werden soll. Sp�ter werden wir sehen, was und wie.

Was uns jetzt interessiert, ist die Erzeugung einer Tabelle:

Syntax

CREATE TABLE name ( column type [DEFAULT value] [NOT NULL], ...
      [INHERITS (inherits, ...)]
      [CONSTRAINT constraints CHECK (test), CHECK (test)] );

wobei:

`name:`	Ist der Name, der der Tabelle gegeben wurde und mit dem sie von jedem Befehl referenziert wird
`Column`:	Ist der Name der Spalte
`Type`:	Ist der Datentyp (`varchar, char, int, date, time, timestamp`), Postgres hat andere Datentypen, aber sie sind nicht mit ANSI SQL kompatibel
`Value`:	Der Wert, den er per default zugewiesen bekommt
`Inherits`:	Dies ist Postgres' eigen. Es definiert eine Vererbung von einer anderen Tabelle. Dies wird eine Entit�t erzeugen, die die Spalten der Tabelle besitzt, die wir erzeugen und die, die es geerbt hat
`Nom_cons`:	Dies definiert eine Integrit�tsregel, die jedes Mal erf�llt sein mu�, wenn eine Reihe ge�ndert wird
`Test`:	Bedingungen, die �berpr�ft werden m�ssen

Beispiel:

CREATE TABLE countries (
         cod_country integer NOT NULL,
         name varchar(30))
   CONSTRAINT cod_raro CHECK (cod_country > 0 AND cod_country < 154);

Mit diesem Beispiel haben wir eine Tabelle mit L�ndern erzeugt. Jedes Mal, wenn wir eine neue Reihe einf�gen, m�ssen diese Bedingungen erf�llt sein:

Der L�ndercode wird nicht NULL. Wenn versucht wird, einen Code zu Null zu machen, gibt der Manager diese Fehlermeldung aus:
```
ExecAppend: Fail to add null value in not null attribute cod_country
```
Der L�ndercode mu� gr��er als 0 und kleiner als 154 sein. Wenn man versucht, einen fehlerhaften L�ndercode einzugeben, gibt der Manager die folgende Fehlermeldung aus:
```
ExecAppend: rejected due to CHECK constraint cod_raro
```

Bemerkung

Was bedeutet NULL? In SQL existieren zwei Zust�nde, Daten und keine Daten. Wir k�nnen daran interessiert sein, da� ein Feld keine Daten hat, wie NULL, oder auch daran, da� die Leerr�ume (0, '') Daten sind. SQL f�hrt das Konzept der NULL ein und arbeitet damit. Ein praktisches Beispiel:

Ich habe eine Tabelle mit Rechnungen mit den folgenden Feldern: customer, value, date_issued, date_paid

Wenn ich eine Reihe erzeuge, dann f�ge ich die Daten ein: customer, value, date_issued

Ich lasse die Daten f�r paid (bezahlt) null; dann kann ich auf diese Weise mit dem folgenden Befehl feststellen, welche Rechnungen bezahlt werden m�ssen:

SELECT * FROM bills WHERE date_paid IS NULL;

Man kann einw�nden, da� eine null (0) in dem Feld date_paid dasselbe erreichen w�rde. Das ist richtig, au�er da� NULL nicht zu den Daten geh�rt und es verhindert, da� eine date_paid vom Datentyp erzeugt wird und die angemessenen Datenfunktionen darauf angewendet werden.

Beispiele f�r das Erzeugen mit NULL:

insert into countries values (15, NULL);

Oder:

insert into countries (cod_country) values (27);

Das Fehlen des Feldes "name" impliziert, da� es einen Wert erhalten hat.

Ver�ndern einer Tabelle

In PostgreSQL k�nnen �nderungen nur neue Spalten hinzuf�gen.

ALTER TABLE table ADD name type;

Wobei:

Table	Name der Tabelle, die ge�ndert werden soll
Name	Name der Spalte, die hinzugef�gt werden soll
Type	Datentyp (siehe `CREATE TABLE`)

Einf�gen von Daten in eine Tabelle

Jetzt wollen wir Daten in unsere Tabelle einf�gen:

SYNTAX:

INSERT INTO table [(column, column, ...)] VALUES (value-1, value-2,
...)

oder sonst:

INSERT INTO table [(column, column, ...)] SELECT ....

Wie wir gesehen haben, gibt es zwei Arten, Daten in eine Tabelle einzuf�gen, entweder Zeile f�r Zeile oder als Ergebnis einer Unterauswahl, die eine oder mehrere Reihen liefern kann.

Wenn wir Zeilen in eine Tabelle einf�gen, werden wir IMMER Daten in alle Spalten schreiben, einschlie�lich denen, die wir nicht erw�hnen, diese werden mit NULL Werten belegt.

Wenn wir in dem Befehl nicht spezifizieren, welche Spalten wir f�llen wollen, dann wird das so aufgefa�t, da� wir alle Spalten mit Daten f�llen wollen, Beispiel:

INSERT INTO countries VALUES (34, 'Spain');

Dies w�re falsch:

INSERT INTO countries VALUES (34);

Aber dies w�re korrekt:

INSERT INTO countries (cod_country) VALUES (34);

Ich empfehle, da� ein Befehl, der in "C" Programme oder in Datenbankfunktionen eingebettet wird, IMMER die Spalten spezifiziert, die er ber�hren wird, sonst wird, wenn wir eine neue Spalte zu der Tabelle hinzuf�gen (ALTER TABLE), und als n�chstes insert eingeben, ein Fehler auftreten, Beispiel:

INSERT INTO countries VALUES (34, 'Spain');

INSERT 18301 1

ALTER TABLE countries add population integer

INSERT INTO countries VALUES (34, 'Spain');

Angenommen, da� die Daten f�r die Bev�lkerung fehlen, dann ergibt dies einen Syntaxerror.

Bemerkung

PostgreSQL generiert keine Fehlermeldung. Es erzeugt die Zeile mit dem "population" Feld NULL. Dies ist aber nur eine Besonderheit von PostgreSQL, jeder andere SQL Manager w�rde eine Fehlermeldung ausgeben.

Wir haben noch den anderen INSERT Typ, der durch eine Unterauswahl zustande kommt.

Dieser insert Typ wird oft verwendet, um vorl�ufige Tabellen zu erzeugen, um eine konkrete Aufgabe von spekulativen Kalkulationen durchzuf�hren.

Der Teil, der ersetzt wird, ist der, der die Daten selbst ber�hrt, dies kommt von den SELECT Anweisungen, die vorher ausgef�hrt wurden und dem Einf�gen von Daten. Die Anweisung SELECT kann eine oder mehrere Reihen liefern.

Daten ausw�hlen

Ich wollte an diesem Punkt ankommen! :-))

Wir haben die erforderlichen SQL Befehle behandelt, die SQL Sprache ohne SELECT w�re wie Bohnen ohne W�rstchen.

Der SELECT Befehl erlaubt uns, auf Daten zuzugreifen, aber mit der Einschr�nkung, da� Suchen und Vereinigen (unions) von Tabellen mit den Daten funktionieren m�ssen und mit den Suchregeln ausgef�hrt werden k�nnen.

Ein Beispiel:

select * from countries;

Ein weiteres Beispiel:

SELECT a.name, SUM(population)
    FROM countries a, states b, counties c
    WHERE b.cod_country = a.cod_country
        AND (c.cod_country = b.cod_country
        AND c.state_code = b.state_code)
        AND population IS NOT NULL
        GROUP BY a.name
        ORDER BY sum ASC;

La�t es mich erkl�ren, ich forderte die Bev�lkerung von allen L�ndern geordnet nach der Bev�lkerung in aufsteigender Reihenfolge.

Hierf�r habe ich eine neue Spalte (population) in die L�ndertabelle hinzugef�gt.

Das w�re dann folgenderma�en:

create table counties (cod_country int, 
                        state_code int, 
                        county_code int,
                        county_name varchar(60),
                        population int);
insert into counties values (1, 1, 1, 'Country 1, State 1, County 1',
5435);
insert into counties values (2, 1, 1, 'Country 2, State 1, County 1',
7832);
insert into counties values (3, 1, 1, 'Country 3, State 1, County 1',
4129);
insert into counties values (1, 2, 1, 'Country 1, State 2, County 1',
76529);
insert into counties values (2, 2, 1, 'Country 2, State 2, County 1',
9782);
insert into counties values (3, 2, 1, 'Country 3, State 2, County 1',
852);
insert into counties values (1, 3, 1, 'Country 1, State 3, County 1',
3433);
insert into counties values (2, 3, 1, 'Country 2, State 3, County 1',
7622);
insert into counties values (3, 3, 1, 'Country 3, State 3, County 1',
2798);
insert into counties values (1, 1, 2, 'Country 1, State 1, County 2',
7789);
insert into counties values (2, 1, 2, 'Country 2, State 1, County 2',
76511);
insert into counties values (3, 1, 2, 'Country 3, State 1, County 2',
98);
insert into counties values (1, 2, 2, 'Country 1, State 2, County 2',
123865);
insert into counties values (2, 2, 2, 'Country 2, State 2, County 2',
886633);
insert into counties values (3, 2, 2, 'Country 3, State 2, County 2',
982345);
insert into counties values (1, 3, 2, 'Country 1, State 3, County 2',
22344);
insert into counties values (2, 3, 2, 'Country 2, State 3, County 2',
179);
insert into counties values (3, 3, 2, 'Country 3, State 3, County 2',
196813);
insert into counties values (1, 1, 3, 'Country 1, State 1, County 3',
491301);
insert into counties values (2, 1, 3, 'Country 2, State 1, County 3',
166540);
insert into counties values (3, 1, 3, 'Country 3, State 1, County 3',
165132);
insert into counties values (1, 2, 3, 'Country 1, State 2, County 3',
0640);
insert into counties values (2, 2, 3, 'Country 2, State 2, County 3',
65120);
insert into counties values (3, 2, 3, 'Country 3, State 2, County 3',
1651462);
insert into counties values (1, 3, 3, 'Country 1, State 3, County 3',
60650);
insert into counties values (2, 3, 3, 'Country 2, State 3, County 3',
651986);
insert into counties values (3, 3, 3, 'Country 3, State 3, County 3',
NULL);
commit work;

Nun, wir k�nnen es nicht mit ALTER TABLE machen, sondern sollten den Befehl UPDATE benutzen, den ich noch nicht erkl�rt habe. So benutze "cut & paste" und alle sind gl�cklich :-))

Jetzt k�nnen wir die QUERY (Abfrage) ausf�hren und die Ergebnisse sollten wie folgt sein:

name     |    sum
- ---------+-------
country 1| 705559
country 2|1212418
country 3|2804018
(3 rows)

Jetzt wollen wir dies verifizieren:

select sum(population) from counties where cod_country = 1;

Ergebnisse:

   sum
- ------
791986
(1 row)

!!!!!! Ein Unterschied !!!!!!

La�t uns die Staatentabelle betrachten, es fehlt Staat 3, wir tippen:

INSERT INTO states VALUES (3, 1, 'State 3, Country 1');
INSERT INTO states VALUES (3, 2, 'State 3, Country 2');
INSERT INTO states VALUES (3, 3, 'State 3, Country 3');

und wiederholen den Befehl mit dem Ergebnis:

name     |    sum
---------+-------
country 1| 791986
country 2|1872205
country 3|3003629

Uns fehlt von jedem Land Staat 3.

Nun f�r diejenigen, die nicht mehr mitgekommen sind, erinnere dich, da� joins (Vereinigungen) von Tabellen exakt sind, sie extrahieren nur Daten, wenn die Bedingung exakt erf�llt ist.

La�t uns den ersten Teil von WHERE: b.cod_country = a.cod_country betrachten

Dies bedeutet, da� ich die L�ndertabelle mit den Staaten vereinige, wo der L�ndercode gleich ist, jetzt erinnere dich an die L�nderdaten, die wir eingef�gt haben:

F�hr dies nicht aus, es ist nur zur Illustration.

create table countries (cod_country integer, name varchar(30));
insert into countries values (1, 'country 1');
insert into countries values (2, 'country 2');
insert into countries values (3, 'country 3');
commit work;

Jetzt die Staatendaten:

create table states (state_code int, 
                        cod_country int, 
                        state_name varchar(30));
insert into states values (1, 1, 'State 1, Country 1');
insert into states values (2, 1, 'State 2, Country 1');
insert into states values (1, 2, 'State 1, Country 2');
insert into states values (2, 2, 'State 2, Country 2');
insert into states values (1, 3, 'State 1, Country 3');
insert into states values (2, 3, 'State 2, Country 3');
commit work;

Alle Staaten 3 von jedem Land fehlen, au�er in der L�ndertabelle mit den korrespondierenden Daten zu Staat 3, deshalb ist es normal, da� wir nicht die L�nderdaten zu den Staaten mit Code 3 hinzuf�gen, die im zweiten Teil abgelegt sind, wo:

        AND (c.cod_country = b.cod_country
        AND c.state_code = b.state_code)

Der Staat existiert in den L�ndertabellen, aber NICHT in der Staatentabelle.

F�r diejenigen, die das nicht verstanden haben, nehmt eine Aspirin, f�hrt euren Hund aus (oder wenn ihr keinen Hund habt, geht ohne Hund spazieren), atmet ein bi�chen frische Luft ein und wenn ihr zur�ckkommt, fangt noch einmal mit der ersten �bung an.

Es ist sehr wichtig zu verstehen, wie das Vereinigen von Daten ausgef�hrt wird, ohne dies kann die Entwicklung, die wir machen, unvorhergesehene Ergebnisse haben.

Wechseln wir den Gang und beginnen mit der Syntax des SELECT Befehls.

SELECT [DISTINCT] expression1 [AS nom-attribute] {, expression-i [as
nom-attribute-i]} 
       [INTO TABLE classname] 
       [FROM from-list] 
       [WHERE where-clause] 
       [GROUP BY attr_name1 {, attr_name-i....}] 
       [ORDER BY attr_name1 [ASC | DESC ] [USING op1 ] {,
nom-attribute-i...}] 
       [UNION {ALL} SELECT ...]

Schritt f�r Schritt:

`DISTINCT`:	dies wird benutzt, um doppelt auftretende Reihen zu entfernen
`expression1`:	was wir als Ergebnis geliefert haben wollen, normalerweise eine Spalte aus der Tabelle der Liste FROM
`AS nom-attribute`:	ein alias f�r den Namen einer Spalte, Beispiel: manu=> select cod_country from countries; cod_country - ----------- 1 2 3 (3 rows) Jetzt mit dem alias: manu=> select cod_country as countr from countries; countr - ------ 1 2 3 (3 rows)
`INTO TABLE`:	erlaubt das direkt Einf�gen einer resultierenden Spalte in eine andere Tabelle (siehe INSERT ... SELECT...)
`FROM`:	Liste mit input Tabellen
`WHERE`:	Auswahlkriterium (Vereinigungs- und Selektionskriterien).
`GROUP BY`:	Gruppenkriterien, bestimmte Funktionen, die in Ausdr�cken benutzt werden, brauchen vielleicht eine Gruppierung
`ORDER BY`:	Ordnungskriterium der gelieferten Reihen, ASC aufsteigende Reihenfolge, DESC absteigende Ordnung, USING wenn die Spalte, die die Ordnung definiert, nicht in der Liste ist (expression)
`UNION ALL SELECT`:	Dies sagt, da� zu den Ergebnissen des ersten SELECT die des zweiten SELECT, die verschiedene Tabellen sein k�nnen, hinzugef�gt werden sollen, erzeugt aber dieselbe Anzahl von Spalten.

Wir haben gesehen, da� der SELECT Befehl nicht nur Posten von der DB liefert, sondern sie auch ver�ndern kann:

SELECT SUM(salary * 1.1) - SUM(salary) AS increment FROM employees;

Dies liefert das Zahlungsinkrement von einer Zahlungssteigerung von mehr als 10%.

La�t uns die verf�gbaren Funktionen betrachten:

COUNT():	gibt die Anzahl der Reihen an ,die nicht NULL sind
SUM():	liefert die Gesamtsumme einer Spalte aus Zahlen
AVG():	berechnet den Durchschnitt einer Spalte aus Zahlen
MIN():	liefert den kleinsten Wert einer Spalte
MAX():	liefert den gr��ten Wert einer Spalte
FLOAT(int):	liefert `FLOAT8, FLOAT(12345)`
FLOAT4(int):	liefert `FLOAT4, FLOAT4(12345)`
INT(float):	liefert ein `INT` von einem `FLOAT/4, INT(123.456)`
LOWER(text):	gibt den Text in Kleinbuchstaben aus
UPPER(text):	gibt den Text in Gro�buchstaben aus
LPAD(text, long, char):	f�llt linksb�ndig mit `char` mit der L�nge von `long` die Spalte `text`
RPAD(text, long, char):	f�llt rechtsb�ndig mit `char` mit der L�nge von `long` die Spalte `text`
LTRIM(text, char):	l�scht von links aus `text` alle Zeichen `char`
RTRIM(text, char):	l�scht von rechts aus `text` alle Zeichen `char`
POSITION(string IN text):	zieht aus `text` die Positon von `string` heraus, aber ES FUNKTIONIERT NICHT
SUBSTR(text,from[,to]):	zieht die Unterzeichenkette von `text`, von der Positon `from` bis zu der Position `to` oder dem Ende der Zeichenkette, heraus
DATETIME(date, hour):	konvertiert ein Datumsformat zum Datum (YYYY-MM-DD) und eine Stunde zu (HH:MM)

Dies waren einige wenige der Funktionen, die in SQL existieren, dies sind diejenigen, die in ANSI SQL definiert sind und auch in Postgres95 vorhanden sind.

Einzelheiten �ber WHERE

Bis jetzt haben wir gesehen, da� wir in dem Abschnitt von WHERE im SELECT Befehl Dinge wie die folgenden schreiben:

AND column = value

Dies ist ein kleines Beispiel dar�ber, was wir machen oder verbinden:

AND, OR, NOT, IN, IN ALL, =, !=, >, <, (SELECT....), LIKE auch die Klammern sind relevant, Beispiel:

WHERE 
   column IN (SELECT DISTINCT column FROM table WHERE ....)
   column IN ('value1','value2','value3',...)
   (column = 'value' and column = 'other_value' OR column != 'value')

!= ist dasselbe wie NOT EQUAL

LIKE erlaubt das Suchen nach einer Zeichenkette innerhalb einer Spalte mit wildcards:

WHERE column LIKE '%Pepito%'

Das % ist eine wildcard, in dem Beispiel ist es wahr, wenn "Pepito" in der Zeichenkette ist

WHERE column LIKE 'Pepito%'

wird es wahr, wenn "Pepito" am Anfang der Zeichenkette steht

WHERE column LIKE '%Pepito'

wird es wahr, wenn "Pepito" am Ende der Zeichenkette steht

Es ist nicht genug Zeit, um alle m�glichen Optionen von WHERE aufzuz�hlen, die Grenze liegt im Vorstellungsverm�gen des Programmierers oder in den Begrenzungen des individuellen Proze�parsers.

Jetzt k�nnen wir den SELECT Befehl verlassen und uns auf die letzten beiden konzentrieren.

Der UPDATE Befehl

Der UPDATE Befehl erlaubt die Ver�nderung von einer oder mehreren Zeilen, abh�ngig von der in WHERE definierten Bedingung

SYNTAX:

UPDATE table SET column-1 = expression-1 
                 [, column-i = expression-i] 
       [WHERE condition]

Wobei:

table:	die zu �ndernde Tabelle, nur eine Tabelle kann auf einmal ver�ndert werden
column:	Die Spalte, die ver�ndert werden soll
expression:	der Wert, den die Spalte erhalten wird. Dieser Wert kann fest sein oder das Ergebnis einer Funktion
condition:	die Bedingung, die die Ver�nderungsgrenzen definiert, die hier definierten Regeln werden auf den `SELECT` Befehl angewandt

Der DELETE Befehl

Der DELETE Befehl erlaubt ebenfalls die �nderung von einer oder mehreren Zeilen einer Tabelle.

SYNTAX

DELETE FROM table
       [WHERE condition]

Wobei:

table:	die Tabelle, in der eine Reihe gel�scht werden soll, nur aus einer Tabelle kann zu einem Zeitpunkt gel�scht werden
condition:	die Bedingung, die die Ver�nderungsgrenzen definiert, die hier definierten Regeln werden auf den `SELECT` Befehl angewandt BEACHTE: ohne das `WHERE` werden ALLE Reihen aus der Tabelle gel�scht

Referenzen

SQL: Einf�hrung zu SQL. Installation von PostgreSQL, der erste Artikel in dieser Reihe.

Authoren und Übersetzer:

fr	->	--	Manuel Soriano
fr	->	en	Dallas L. Gale
en	->	de	Katja Socher

1999-10-14, generated by lfparser version 0.7