SQL - Structured Query Language

SQL - Structured Query Language
INT UNSIGNED
0 .. 4.294.967.295
Alle relationalen Datenbanken benutzen zur Verwaltung und Auswertung von Daten eine
Kommandosprache. Selbst MS Access speichert Abfragen in einem SQL-Dialekt (ist also nicht kompatibel
zum ANSI-Standard...).
BIGINT
-3402823e+31 .. 3402823e+31
DECIMAL(length,dec)
Kommazahl der Länge length und mit dec Dezimalstellen; die Länge beträgt:
Stellen vor dem Komma + 1 Stelle für Komma + Stellen nach dem Komma
Geschichte
1974 Chamberlin u.a. definieren die Sprache SEQUEL für die ersten relationalen Prototypen von DBSystemen.
Weiterentwicklung unter dem Namen SQL (Structured Query Language) für die Systeme R und
DB2.
1980 SQL wurde in seiner IBM-Implementierung zum Quasi-Standard.
1986 Normierung durch das American National Standard Institute (ANSI X3.135).
1987 Der ANSI - Standard wird zum ISO Standard. (International Standard Organization).
1989 Ergänzung um Sprachelemente zur Beschreibung von Integritätsbedingungen.
Sprachkonzept
SQL ist eine 4GL- Language, d. h. eine Sprache der Vierten Generation.
SQL ist wie PROLOG eine deskriptive Programmiersprache. In SQL werden nicht wie bei den
prozeduralen Programmiersprachen Sequenzen, Selektionen und Iterationen programmiert, sondern es
werden Schachtelungen aus relationalen Operationen definiert. Der SQL-Anwender muß nicht
beschreiben, wie die Ergebnisse zu generieren sind, sondern es müssen nur die gewünschten
Ergebnisse spezifiziert werden.
Man sagt auch SQL ist nicht eine How-Type-Language, sondern eine What-Type-Language.
SQL ist eine mengenorientierte und nicht satzorientierte Sprache. In SQL gilt nicht, wie beispielsweise in
COBOL, C und PASCAL "one record at one time", sondern es werden grundsätzlich Mengen verarbeitet
und Mengen ausgegeben.
SQL ist keine universelle Programmiersprache, sondern auf Datenbanken spezialisiert.
SQL besteht aus vier Kommandogruppen zur:
Datenauswertung
Kommandos für Auswahlabfragen
SELECT
Datenmanipulation
(DML, Data Manipulation Language)
INSERT,UPDATE,DELETE
Datendefinition
(DDL, Data Definition Language)
CREATE, ALTER, DROP....
Datenzugriffskontrolle (DCL, Data Control Language)
GRANT, REVOKE, LOCK..
SQL-Befehle
Tabellen erstellen
CREATE TABLE table_name (create_definition,...)
Für `create_definition` kann man folgendes einsetzen:
Feld-Name Typ [NOT NULL] [AUTO_INCREMENT] [UNIQUE] [PRIMARY KEY]
oder PRIMARY KEY (Feldname,...)
oder UNIQUE (Feldname,...)
oder FOREIGN KEY (Feldname,...) [reference_definition]
Für `reference_definition` muß man folgendes einsetzen:
REFERENCES table_name[(index_spalte,...)]
Hinweise:
NOT NULL: Eingabe notwendig
AUTO_INCREMENT: nur einmal pro Tabelle in einem Primärschlüsselfeld verwendet werden.
UNIQUE: Werte müssen eindeutig sein. (bei Primärschlüsseln automatisch).
PRIMARY KEY: Feld wird Primärschlüssel. Schließt „NOT NULL“ und „UNIQUE“ ein.
FOREIGN KEY: Fremdschlüsselbeziehung wird von. MySQL ignoriert.
Mögliche Datentypen:
Typbezeichner
Beschreibung
TINYINT
-128 .. 127
TINYINT UNSIGNED
0 .. 255
INT
-2.147.483.648 .. 2.147.483.647
CHAR(NUM)
Zeichenkette fester Länge
VARCHAR(NUM)
[BINARY]
Zeichenkette variabler Länge mit max NUM Stellen (1<= NUM <=255). Alle
Leerstellen am Ende werden gelöscht. Solange nicht BINARY angegeben
wurde, wird bei Vergleichen nicht auf Groß-/Kleinschreibung geachtet.
TEXT
Text mit einer max. Länge von 65535 Zeichen (gegenüber CHAR und
VARCHAR nur eingeschränkte Such- und Sortiermöglichkeiten)
BLOB
Binärobjekte bzw. Binärdateien (Binary Large Objects)
MEDIUMTEXT
Text mit einer max. Länge von 16.777.216 Zeichen
TIME
Zeit; Format: HH:MM:SS, HHMMSS, HHMM oder HH
DATE
Datum; Format: YYYY-MM-DD, wobei - jedes nicht numerische Zeichen sein
kann
TIMESTAMP
setzt einen Datumswert beim Einfügen/Updaten einzelner Felder automatisch
auf das Systemdatum. Format: YYYYMMDDHHMMSS. Wenn mehrere Felder
den Typ TIMESTAMP haben, wird immer nur das erste automatisch geändert!
Vorhandene Tabellen anzeigen
SHOW TABLES
Tabellenstruktur anzeigen:
SHOW COLUMNS FROM table
Indizes...
... werden für ausgewählte Felder angelegt und beschleunigen den Zugriff auf und die Sortierung von
Datensätze deutlich.
Pflichtfeld definieren: mysql> alter table daten change note note int(11) not null;
Index erstellen:
mysql> alter table daten add index notind (note);
Informationen über Indizes der Tabelle anzeigen:
mysql> show index from daten;
Tabelle löschen
DROP TABLE table_name
Datensatz einfügen
INSERT INTO table_name [ (feld_name,...) ] VALUES (werte,...)
Feldnamen können weggelassen werden, wenn in alle Felder etwas eingefügt werden soll.
Werte müssen dann in genau der Reihenfolge eingeben werden, wie die Felder in der CREATE TABLE
Anweisung definiert wurden. Zeichenketten und Datum in Hochkomma, Zahlen nicht.
mysql> INSERT INTO Mitarbeiter (Name,GebDat)
-> VALUES ('Christoph Reeg','1979-5-13');
Datenbankabfragen (SELECT)
SELECT [DISTINCT | ALL] select_expression,... FROM tables ...
[WHERE where_definition]
[GROUP BY feld,...]
[ORDER BY feld [ASC | DESC] ,...]
[LIMIT [offset,] rows];
Auch wenn die meisten Klauseln optional sind, ist ihre Reihenfolge zwingend. Ein Kommando kann
beliebig viele Zeilen enthalten. Leerzeichen, Tabulatoren (Whitespaces) sowie Groß- und Kleinschreibung
spielen keine Rolle.
Sämtliche Datensätze, alle Felder ausgeben: SELECT * FROM table
DISTINCT: identische Zeilen nur einmal ausgeben
ALL: sich wiederholenden Werte mehrmals ausgeben (Vorgabe)
ORDER BY: nach welchen Spalten soll sortiert werden?
ASC: aufsteigende Sortierung
DESC: absteigende Sortierung
Bsp.: mysql> SELECT * from Mitarbeiter ORDER BY GebDat,Name;
GROUP BY-Anweisung: Mehrere Datensätze gruppieren.
Bsp.: mysql> SELECT count(*), AbtNr from Mitarbeiter GROUP BY AbtNr;
Anzahl der Zeilen, die ausgegeben werden sollen: LIMIT [offset,] rows
select_expression
Angeben, was angezeigt werden soll. Im einfachsten Fall ist das ein `*`.
Bsp.: mysql> select Mitarbeiter.Name, Mitarbeiter.Telefon from Mitarbeiter;
Aliase...
kann man sowohl für Spalten als auch für Tabellen definieren.
Tabellen-Aliase
Bsp.:
mysql> select M.Name, M.Telefon, M.AbtNr
-> FROM Mitarbeiter M;
Spalten-Aliase
Bsp.:
mysql> SELECT count(*) AS Anzahl
-> FROM Mitarbeiter;
where_definition
Mehrere Teilbedingungen können kombiniert werden, z.B. mit AND und OR verknüpft, aber auch mit NOT
verneint werden.
Bei Vergleichen mit Strings (=VARCHAR) wird im Normalfall nicht auf die Groß-/Kleinschreibung geachtet.
Wenn man jedoch unterscheiden will, so muss beim Anlegen der Tabelle bei VARCHAR die Option BINARY
angegeben werden.
Bsp.:
mysql> select * from daten
-> where famnam='Müller' and fach='Informatik';
mysql> select * from daten where vornam like "_a%";
Daten sortiert ausgeben:
mysql> select * from daten where fach = 'Informatik' order by 'famnam','vornam';
Operator
AND
OR
NOT
LIKE
BETWEEN
IN
=
<>
>(>=)
<(<=)
Bedeutung
beide Kriterien müssen erfüllt sein
eines der beiden Kriterien muss erfüllt sein
Kriterium wird verneint
Vergleichsoperator: Platzhalter "%" für mehrere Zeichen und "_" für genau ein
Zeichen
wählt alle Spalten aus, die zwischen dem oberen und unteren Wert liegen. Kann
bei Textspalten, Datumsspalten und numerischen Spalten verwendet werden.
vergleiche mit einer Wertemenge. Kann bei Textspalten, Datumsspalten und
numerischen Spalten verwendet werden.
Bsp.:
mysql> SELECT * FROM schueler
-> WHERE Telefon IN ('123','0351/123');
beide Ausdrücke sind "gleich"
"ungleich"
"größer" ("größer gleich")
"kleiner" ("kleiner gleich")
order by ...
(Datenfeldname)...
desc (asc)
Datensätze werden absteigend (aufsteigend) sortiert ausgegeben
Bei Vergleichen mit NULL-Werten kann nicht mit den normalen Operatoren gearbeitet werden. Statt dessen
ist nur IS NULL oder, verneint, IS NOT NULL möglich.
Funktionen
Bei select_expression und where_expression können neben Konstanten und Spaltenwerten auch
Funktionen verwendet werden. Es gibt zwei Arten von Funktionen, zum einen die sog. ,,singlerow``Funktionen und zum anderen die Gruppenfunktionen. Singlerow-Funktionen werden auf jede Zeile
angewendet, während die Gruppenfunktionen immer auf eine Gruppe von Zeilen angewendet werden.
Mathematische Funktionen (Auswahl)
+-*/%
ABS()
COS()
DEGREES()
MOD()
PI()
POW(X,Y)
RAND()
ROUND()
ROUND(x,n)
SQRT()
TRUNCATE(x,n)
addieren/subtrahieren/multiplizieren/dividieren/modulo (ganzzahliger Rest)
Betrag von
Cosinus in rad
Umrechnung von rad in deg (Grad)
Modulo (ganzzahliger Rest)
die Zahl Pi
rechnet X hoch Y aus
liefert eine Zufallszahl zwischen 0 und 1
rundet Wert
rundet Wert von x auf n Stellen
Wurzel
schneidet nach n Kommastellen von x ab
Sonstige Funktionen
NOT / !
logisches NOT. Gibt 1 zurück, wenn das Argument 0 ist, sonst 0. Ausnahme: NOT
NULL gibt NULL zurück.
AND / &&
logisches UND. Gibt 0 zurück, wenn eines der Argumente 0 oder NULL ist, sonst
1.
OR / ||
logisches ODER. Gibt 1 zurück, wenn eines der Argumente nicht 0 und nicht
NULL ist, sonst 1.
|
bitweises ODER
&
bitweises UND
CONCAT(str1, str2, ...) Gibt den String zurück, der durch Zusammenführen der Argumente entstanden ist.
Sobald ein Argument NULL ist, wird NULL zurückgegeben.
LEFT(str,n)
schneidet n Buchstaben von `str` ab und gibt diese zurück
LTRIM(str)
löscht alle Leerzeichen am Anfang von `str`
PASSWORD(str)
verschlüsselt den Klartext `str`
REVERSE(str)
dreht `str` um, d.h. letzter Buchstabe ist dann am Anfang
LCASE(str)
LOWER(str)
Wandelt `str` in Kleinbuchstaben und gibt das Ergebnis zurück
UCASE(str)
UPPER(str)
Wandelt `str` in Großbuchstaben und gibt das Ergebnis zurück
DAYOFWEEK(date)
Gibt den Wochentag-Index des Datums zurück (1 = Sonntag, 2 = Montag, ..., 7 =
Samstag)
DAYOFMONTH(date) Gibt den Tag des Monats zurück
DAYOFYEAR(date)
Gibt den Tag im Jahr zurück
WEEK(date)
WEEK(date,first)
Gibt die Woche des Datums zurück. Wenn `first` nicht angegeben wird bzw. 0 ist,
fängt die Woche mit Sonntag an. Ist `first` z.B. 1, fängt die Woche mit Montag an.
MONTH(date)
Gibt den Monat zurück
YEAR(date)
Gibt das Jahr zurück
DATE_FORMAT
Formatiert das Datum entsprechend dem übergebenen format String.
(date,format)
UNIX_TIMESTAMP
Gibt den Unix-Timestamp (Sekunden seit dem 1.1.1970) des Datums date zurück.
(date)
Datums-Format-Funktionen
Mit Hilfe von DATE_FORMAT kann man Datumswerte aus Tabellen so formatieren, wie man sie gerne hätte.
Die Funktion erwarten zwei Parameter. Zum einen das Datumsfeld, zum anderen den Formatierungs-String.
Die Formatierungszeichen (Tabelle) werden durch die entsprechenden Werte ersetzt. Alle anderen Zeichen
werden so wie sie sind ausgegeben.
In PHP gibt es auch eine Datum-Formatierungsfunktion. Ob man nun mit der MySQL-Funktion das Datum
formatiert und dann mit PHP ausgibt oder mit Hilfe der PHP-Funktion das Datum formatiert, ist häufig egal.
%W
Wochentag
%w
Tag in der Woche (0 = Sonntag, ..., 6=Samstag)
%d
Tag des Monats (00 - 31)
%e
Tag des Monats (0 - 31)
%j
Tag im Jahr (001 - 366)
%U
Woche, mit Sonntag als 1. Tag der Woche (00 - 52)
%u
Woche, mit Montag als 1. Tag der Woche (00 - 52)
%M
Monatsname
%m
Monat, numerisch (01 - 12)
%c
Monat, numerisch (1 - 12)
%Y
Jahr (4stellig)
%y
Jahr (2stellig)
%T
Uhrzeit (24 Std.) (hh:mm:ss)
%S
Sekunden (00 - 59)
%s
Sekunden (00 - 59)
%i
Minuten (00 - 59)
%H
Stunde (00 - 23)
%k
Stunde (0 - 23)
%h
Stunde (00 - 12)
%I
Stunde (00 - 12)
%l
Stunde (0 - 12)
%%
%
Gruppenfunktionen (Auswahl)
COUNT(expr) zählt die Zeilen, deren Werte ungleich NULL sind
AVG(expr)
durchschnittlicher Wert
MAX(expr)
kleinster Wert
MIN(expr)
größter Wert
SUM(expr)
Summe
(`expr`: Name der Spalte, mit der die Operation erfolgen soll)
Joins
... dienen der Verknüpfung 2er Tabellen (Relationen, Master-Detail-Beziehungen etc.).
Bsp.: Eine Tabelle enthält die Bestellung, die andere die passende Kundenadresse.
mysql> kunden.vornam, kunden.famnam, bestellung.artikel, bestellung.preis
-> from kunden,bestellung
-> where kunden.nummer = bestellung.kundnu;
Equi-Join
Bsp.:
mysql> SELECT m.Name, m.AbtNr, a.Name, a.AbtNr
-> FROM Mitarbeiter m, Abteilung a
-> WHERE m.AbtNr = a.AbtNr;
Über die AbtNr entsteht die Verbindung zwischen den beiden Tabellen.
Self-Join
So, wie man mehrere Tabellen miteinander verbinden kann, ist es auch möglich, eine Tabelle mit sich selbst
zu verbinden.
Bsp.:
mysql> SELECT m.Name, m.VNr, v.Name, v.MNr
-> FROM Mitarbeiter m, Mitarbeiter v
-> WHERE m.VNr = v.MNr;
Die Tabelle `Mitarbeiter` kommt zwei Mal innerhalb der FROM-Anweisung auftauchen. Um die Spalten
eindeutig bezeichnen zu können, müssen zwei Tabellen-Aliase vergeben werden.
Outer-Join
Um beim Join alle Tupel der Haupttabelle mit den dazu passenden Tupeln der nachgeordneten Tabelle zu
bekommen, wenn nicht zu jedem Tupel der Haupttabelle ein passender Tupel existiert, wird der Outer-Join
benötigt.
Die Syntax unter mySQL lautet (wo sonst die Tabellennamen stehen):
haupttabelle LEFT JOIN tabelle2 ON bedingung
oder
haupttabelle LEFT JOIN tabelle2 USING (spalte)
Bei der unteren Möglichkeit müssen die Spaltennamen in den beiden Tabellen, über die die Verbindung
entsteht, gleich sein. Bei der oberen Möglichkeit muss an die Stelle, wo `bedingung` steht, das eingesetzt
werden, was man beim Equi-Join als `where_condition` schreiben würde.
Bsp.:
mysql> SELECT m.Name, m.VNr, v.Name, v.MNr
-> FROM Mitarbeiter m LEFT JOIN Mitarbeiter v ON m.VNr = v.MNr;
DELETE FROM - Datensätze löschen
DELETE FROM table_name [WHERE where_definition]
UPDATE - Attribute ändern
UPDATE table_name SET column=expression,... [WHERE where_definition]
ALTER TABLE - Tabellenstruktur ändern
ALTER TABLE table_name alter_spec
mit ‘alter_spec’:
ADD [COLUMN] create_definition [AFTER column_name | FIRST]
oder CHANGE old_column_name create_definition
oder ADD PRIMARY KEY (index_spalte,...)
oder ADD INDEX (index_spalte,...)
oder ADD UNIQUE (index_spalte,...)
oder DROP column_name
oder DROP PRIMARY KEY
oder RENAME new_table_name
`create_definition` funktioniert wie bei `CREATE TABLE`.
Kommentare: mit /* und */ umrahmen
MySQL
- relationales Datenbanksystem (RDBMS),
- hervorragend für die Entwicklung von Webanwendungen geeignet,
- für private Zwecke kostenlos (kein OpenSource-Projekt)
- Umgebung: LAMP (Linux...) oder WAMP (Windows - Apache - MySQL - PHP).
Benutzer- und Rechteverwaltung
Man kann natürlich auch ein grafisches Administrationstool verwenden wie phpMyAdmin...
Passwort für Root-User setzen: /usr/bin/mysqladmin -u root -h server -p password 'new-password'
Aufruf der MySQL-Kommandozeile: mysql -p{Passwort} -u{Benutzername} {DB-Name}
Einige Befehle zur Übung
MySQL-Server starten
Kommandozeile starten
Datenbank anlegen
Datenbank verbinden
Tabellen anlegen
Datenbanken freigeben
Betrifft Tabelle 'db' der Datenbank 'mysql'.
1. Datenbank 'mysql' verwenden:
mysql> use mysql;
2. Datenbank in die Tabelle 'db' eintragen:
mysql> insert into db values
-> ('%','testdb','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
3. Privilegientabelle aktualisieren:
mysql> flush privileges;
Struktur anzeigen lassen
Tabellenstruktur verändern:
... Datenfeld hinzufügen
... Datenfeld entfernen
... Datenfeldtyp ändern
... Datenfeldgröße ändern
... Datenfeldnamen ändern
... Tabellennamen ändern
neue Datensätze in
bestehende Tabelle
eintragen
Datensätze anzeigen:
... alle
... ausgewählte Felder
... gefiltert
Datensatz ändern
Die Datenbank ‘testdb’ kann nun von allen Rechnern aus und von allen MySQL-Usern verwendet werden.
Alle Rechte wurden vergeben
Datensatz löschen:
Benutzer anlegen
1. Datenbank 'mysql' verwenden:
mysql> use mysql;
2. User in die Tabelle 'user' eintragen:
mysql> insert into user values
-> ('localhost','test','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
3. Privilegientabelle aktualisieren: mysql> flush privileges;
Benutzer 'test' ist nun mit allen Rechten ausgestattet, wenn er vom Rechner 'localhost' eingeloggt ist.
Berechtigungen erteilen
Betrifft Tabelle 'user':
Privileg
Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv
mysql> describe user;
Zugehörige Berechtigung
Datensätze suchen
neue Datensätze einfügen
vorhandene Datensätze ändern
Datensätze löschen
Datenbanken und Tabellen erstellen
Datenbanken und Tabellen entfernen
MySQL-Einstellungen neu laden
MySQL-Server herunterfahren
Status-Informationen über Server abrufen
Dateien am Server lesen und schreiben
Rechte vergeben
... derzeit nicht benutzt
Index-Dateien erstellen oder entfernen
Tabellen oder Indexdateien verändern
Dateirechte setzen
Betrifft Tabelle 'db':
mysql> describe db;
Veränderte Rechte werden wirksam, nachdem die Berechtigungstabelle mit dem Befehl 'flush privileges'
aktualisiert wurde.
Mit MySQL-Server im Netz verbinden
mysql -h butler.nuss -u username -p
mysql> select user();
MySQL benutzen
Batch-Betrieb: mysql -p{Passwort} -u{Benutzername} {DB-Name} < dateiname
rcmysql start
mysql -p
mysql> create database kurs;
mysql> connect kurs;
mysql> create table daten (vornam varchar (30),
-> famnam varchar (30), fach varchar (20), note int);
mysql> describe daten;
mysql> alter table daten add adr varchar(50);
mysql> alter table daten drop adr;
mysql> alter table daten change note note char(3);
mysql> alter table daten change fach fach varchar(40);
mysql> alter table daten change fach gegenstand varchar(20);
mysql> alter table daten rename schueler;
mysql> insert into daten values ('Morty','Maus','Informatik','2');
mysql> select * from daten;
mysql> select famnam, note from daten;
mysql> select * from daten where famnam='Müller';
mysql> update daten
-> set note='1'
-> where note='3';
mysql> delete from daten
-> where vornam='Gustav';