Thomas Ohlhauser: MySQL in Einzelschritten

Thomas Ohlhauser: MySQL in Einzelschritten
1. Installation und erste Schritte
- Mysql installieren unter c:\mysql (im Raum 10 bereits auf allen Rechnern erfolgt)
- # NT neu starten
- DOS-Box : Ins Verzeichnis C:\mysql\bin wechseln und mysql eingeben. Mysql Monitor
erscheint
- MySQL unter Linux erstmals starten: /sbin/init.d/mysql start
- Linux: /usr/local/mysql bzw. bei einem symbolischen Link direkt mysql am Prompt
eingeben.
- Wichtig: Jeden Befehl mit einem ; beenden!
- Show databases; # zwei Datenbanken sind vorhanden: Mysql und test.
- Show tables from mysql;
- Show columns from user; # Befehl gibt die Beschreibung der Felder in der
Datenbanktabelle an. Sie gibt den Typ, den Standardwert, ob Null oder nicht Null und
sämtliche Schlüsselfelder an.
- Select * from user; # Befehl zeigt alle Datensätze der Tabelle.
- Die mysql Datenbank ist die Standarddatenbank. Diese Datenbank speichert alle
Berechtigungen, alle Benutzer, Host-Privilegien usw.
2. Entwerfen der ersten Datenbank
• Konsole öffnen (MS-DOS-Eingabeaufforderung unter Windows bzw. eine Konsole ihrer
Wahl unter Linux).
• CREATE DATABASE nachname_db; # erstellt eine neue Datenbank mit eurem
Nachnamen
• DROP DATABASE nachname_db; # löscht die Datenbank mit eurem Nachnamen
3. Benutzer hinzufügen (Wichtig bei Remote Login)
Um einen Benutzer von ihrem lokalen Rechner aus – im Folgenden als localhost bezeichnet
– Zugang zu ihrer Datenbank zu ermöglichen, muss dieser Benutzer an mehreren Orten
eingetragen sein. Die MySQL RDBMS beinhaltet eine Datenbank namens mysql. Diese
verfügt über sämtliche Berechtigungen für alle MySQL Datenbanken
a) Um die mysql-Datenbank zu einer aktiven Datenbank zu machen, geben sie folgendes
ein: use mysql;
b) Fügen Sie der host-Tabelle den Host –Name und die Datenbank hinzu:
INSERT INTO host VALUES(‘localhost‘,‘nachname_db‘, ‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘);
Beachten Sie, dass der MySQL-Monitor Ihre Anweisung in der folgenden Zeile
weiterführt, wenn sie kein Semikolon (;) eingeben. Mit der Pfeilnachoben-Taste (history
key) könne sie alle Befehle Revue passieren lassen. Y bedeutet der Benutzer ist
berechtigt eine bestimmte Aktion auszuführen. N bedeutet keine Berechtigung.
c) Fügen Sie der user Tabelle einen Benutzer, indem sie folgende Anweisung eingeben:
INSERT INTO user VALUES(‘localhost‘,‘gast‘,
PASSWORD(‘gast‘),‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘;Y‘,‘Y‘,‘Y‘,‘Y‘);
d) Sie sind nun so weit, der mysql-Datenbank Ihre Datenbank sowie Benutzer
hinzuzufügen. Geben Sie dazu folgendes ein:
INSERT INTO db
VALUES(‘localhost‘,‘nachname_db‘,‘gast‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘,‘Y‘);
Rückblick:
Benutzer Gast hat nun die Berechtigung die Datenbank nachname_db auf dem gleichen
Rechner (localhost) zu gebrauchen. Wenn Sie von ihrem Rechner aus mit einer anderen
MySQL-Datenbank auf einem anderen Rechner in Verbindung treten, muss der Name
ihres Rechners sich in dieser Datenbank befinden.
4. Tabellen erstellen
a) Datenbank auswählen
mysql> USE nachname_db;
b) Anlegen einer Tabelle
mysql> CREATE TABLE schueler (name CHAR(20), vorname CHAR(20),
-> geschlecht CHAR(1), geboren DATE, betrieb CHAR(30));
c) Anschauen der Tabelle mit dem Befehl DESCRIBE:
mysql> DESCRIBE schueler; # bzw. show columns from schueler;
d) Datensätze einfügen
mysql> INSERT INTO schueler
-> VALUES ('Oettl','Christoph',,'m','1975 03-30',‘Edeka‘);
Übungsaufgabe: Mindestens vier weitere Schüler einfügen!!!
e) Abfragen der Informationen aus der Tabelle
Allgemein:
SELECT was
FROM tabelle
WHERE bedingungen;
mysql> SELECT * FROM schueler;
f) Geburtsdatum beim Schüler Oettl ändern
mysql> UPDATE schueler SET geboren = "1960-08-31" WHERE name = "Oettl";
g) Auswahl spezieller Einträge (Selektion)
Wir möchten uns gerne nur die Zeile von Oettl ausgeben lassen:
mysql> SELECT * FROM schueler WHERE name = "Oettl";
Ein weiteres Beispiel mit Datumsvergleich:
mysql> SELECT * FROM schueler WHERE geboren >= "1960-05-01";
Es können auch Bedingungen mit einander verbunden werden:
mysql> SELECT * FROM schueler WHERE geboren > = "1960-05-01" AND geschlecht =
"m";
Die vorhergehende Abfrage benutzt einen logischen Operator: AND. Hier nun die
Anwendung für den Operator OR:
mysql> SELECT * FROM schueler WHERE geboren > = "1960-05-01" OR geschlecht =
"m";
h) Auswählen von Spalten
mysql> SELECT name, geburtstag FROM schueler;
i) Sortieren von Reihen
Bisher haben wir in den vorhergehenden Beispielen die Ausgabe in keiner besonderen
Reihenfolge ausgeben lassen. Die Datenbank hat die Daten geliefert, wie sie
gespeichert waren.
Um ein Ergebnis zu sortieren, kann der Befehl ORDER BY verwendet werden:
Hier nun die Geburtstage aller Schueler, sortiert nach Datum:
mysql> SELECT name, geboren FROM schueler ORDER BY geboren ;
Um in absteigender Reichenfolge zu sortieren, müssen wir einen weiteren
Ausdruck verwenden, DESC. Ohne diese Angabe wird immer aufsteigend sortiert,
daher erübrigt sich auch der Ausdruck ASC (für ASCEND):
mysql> SELECT name, geboren FROM schueler ORDER BY geboren DESC;
Man kann auch nach mehreren Spalten sortieren. Wenn mehrere Treffer in einer
Spalte gefunden und eingeordnet sind, dann werden diese noch nachsortiert.
Ein Beispiel:
mysql> SELECT name, geschlecht, geboren FROM schueler ORDER BY geschlecht,
geboren DESC;
j) Abfrage von Suchmustern (Patterns)
MySQL kennt alle Standards von ANSI SQL 92. Ein Beispiel, in welchem alle Namen
gefunden werden sollen, die mit B anfangen:
mysql> SELECT * FROM schueler WHERE name LIKE "O%";
oder alle, die mit er enden.
mysql> SELECT * FROM schueler WHERE name LIKE "%fy";
Für alle, die ein "w" enthalten:
mysql> SELECT * FROM tier WHERE name LIKE "%w%";
Eine Besonderheit ist die Möglichkeit zur Angabe von genauen Wortlängen:
mysql> SELECT * FROM schueler WHERE name LIKE "_____";
k) Zählen in Spalten
In der Praxis stellen sich oft immer wieder dieselben fragen. Grund können statistische
Erhebungen sein. Auf unserer schueler Datenbank bezogen, könnte man die
Fragen stellen:
Wie oft kommt ein bestimmter Typ von Einträgen in einer Tabelle vor ? Wir fragen z.B.
nach der Zahl der Schüler, der Zahl von Schülern je Betrieb, oder der Zahl der
weiblichen Schüler.....
Um diese Fragen alle beantworten zu können, müssen wir einige wichtige Informationen
zu vor wissen. Z.B. wieviele Spalten es überhaupt in unserer Tabelle gibt.
Hierfür benutzen wir den Operator COUNT():
mysql> SELECT COUNT(*) FROM schueler;
Ok, soweit so gut, der Operator COUNT(*) zählt also alle Spalten. Wir möchten aber z.B.
wissen, wieviele Schüler ein Betrieb hat:
mysql> SELECT betrieb, COUNT(*) FROM schueler GROUP BY betrieb;
Die Zahl der Schüler, nach Geschlecht geordnet:
mysql> SELECT geschlecht, COUNT(*) FROM schueler GROUP BY geschlecht;
i) Aggregat Funktionen
1.COUNT
2.SUM
3.AVG
4.MIN
5.MAX
In einer Select-Anweisung können verschiedene Aggregatfunktionen verwendet werden.
COUNT
Diese Aggregatfunktion liefert die Anzahl Datensätze zurück, die mit der SQL-Anweisung
eingeschränkt wurden. Beispiel:
Select Count(Ident) from Adressen
Das Ergebnis ist eine Zahl, die die Anzahl aller Ident bzw. aller Personen in der Tabelle
Adressen darstellt.
SUM
Diese Funktion liefert die Summe aller Werte in der SQL-Ergebnismenge zurück. Beispiel:
Select sum(alter) from adressen
Das Ergebnis ist das Gesamtalter, die Summe aller Personen in der Tabelle Adressen
AVG
Mit dieser Funktion wird der Durchschnittswert zurückgegeben. Beispiel:
Select avg(alter) from adressen
Das Ergebnis besteht nur aus einem Wert, nämlich dem Durchschnittsalter aller Personen
aus der Tabelle Adressen.
MIN
Es wird der kleinste Wert in der Datenmenge ermittelt und als Ergebnis zurückgegeben.
Beispiel:
Select min(alter) from adressen
Das Ergebnis ist das jüngste Alter aller Personen in der Tabelle Adressen.
MAX
Es wird der grösste Wert in der Datenmenge ermittelt. Beispiel:
Select max(alter) from adressen
Das Ergebnis ist das grösste Alter aller Personen in der Tabelle Adressen.
5. Hinzufügen von Tabellen und Spalten
a) Ändern eines Spaltennamens
Alter table schueler
Change Vorname First_Name Char(20);
In der schueler Tabelle wird der Namen der Spalte Vorname in First_Name geändert.
Describe schueler;
Um die Änderung zu prüfen!
b) Ändern eines Spaltentyps
Alter table schueler
Modify Nachname Char(50);
Describe schueler;
c) Umbenennung einer Tabelle
Alter table schueler Rename IT-Schueler;
Show tables from ohlhauser_db;
d) Löschen von Tabellen
Drop schueler;
e) Löschen von Spalten
Alter table schueler Drop betrieb;
f) Hinzufügen von Spalten
Alter table schueler Add Klasse Char(6);
g) Update von Spalten
UPDATE schueler SET Alter=Alter+1;
6. MySQL – Datentypen
a) Nummerische Typen
TINYINT
SMALLINT
MEDIUMINT
INT
BIGINT
DOUBLE(M,D)
DECIMAL(M,D)
-128 bis 127
-32768 bis 32767
-8388608 bis 8388607
-2147483648 bis 2147483647
.......
abhängig von den Werten
abhängig von den Werten
b) Zeichenketten
CHAR(X)
VARCHAR(X)
TEXT
X Byte
X+1 Byte
X+2 Byte
c) Vermische Typen
SET
Werteliste
CREATE TABLE TEST(
Medien SET(‘Web Page‘,‘Television’,‘Newspaper‘));
Die anhand dieser Anweisung erstellte Spalte kann folgende Werte beinhalten:
Web Page, Television, Newspaper.
7. Das Füllen der Datenbank mit Daten
Nun da Sie Ihre Datenbank entworfen und erstellt haben, sind sie soweit
weitere Daten einzufügen. Sie könnten z.B. weiterhin alle Daten auf
Kommandozeilenebene eingeben, dies wäre aber ordentliche
Zeitverschwendung, denn MySQL bietet Ihnen zahlreiche Möglichkeiten zum
Daten importieren bzw. exportieren.
a) Load Data Infile
Stellen Sie sich vor, Sie müssten eine durch Komma abgegrenzte Textdatei,
die Sie mit einem Editor generiert haben, importieren. Jede Spalte mit Daten
wird durch ein Komma getrennt. Dies sieht folgendermaßen aus:
Müller, Max, m, 1975-01-01, hws
Speichern Sie die Datei und importieren Sie sie mit folgendem Befehl:
mysql> Load Data Infile “C:/home/schueler.txt“ into table schueler
¾ Fields
Terminated By ‘,‘
# Die Felder(fields) der Textdatei sind durch ein Komma getrennt!
;
¾
mysql>Select* from schueler;
b)
1.
2.
3.
4.
5.
# Betrachten Sie ihr Werk!
Datenimport aus Microsoft ACCESS
Öffnen Sie die ACCESS-Datenbank EURO02
Öffnen Sie die Mitarbeiter Tabelle.
Datei / Speichern unter/Exportieren
Dateiname mitarbeiter.txt (Speichern als Textdatei)
Beantworten Sie die folgende Fragen und klicken Sie Weiter bis die Datei
exportiert ist.
6. Erstellen Sie eine MySQL Datenbank euro02.
7. Erstellen Sie eine MySQL Tabelle Mitarbeiter:
Create table mitarbeiter (mnr Int, name Char(20), ............);
8. Importieren Sie die Daten der Textdatei in ihre MySQL Datenbank
Load Data Infile „C:/home/mitarbeiter.txt“ into table mitarbeiter
Fields
Terminated by ‘;‘
# Felder der Textdatei sind durch Semikolon getrennt.
;
9. Select* from mitarbeiter;
10. Importieren Sie die Tabellen Projekt, Kind, ProjektMitarbeiter.
8. Verknüpfen von Tabellen
:LU KDEHQ QXQ PHKUHUH 7DEHOOHQ %HLGH P|FKWHQ ZLU ]XVDPPHQ
EHQXW]HQ+LHUDOVRHLQIDFKHLQPDO]ZHL%HLVSLHOHIUHLQH$EIUDJH
EHU]ZHL7DEHOOHQKLQZHJ
Beispiel 1:
Welche Mitarbeiter (Name, Vorname) leiten die einzelnen Projekte? Das Dynaset soll
aufsteigend nach Name und Vorname der Leitenden geordnet sein.
SQL:
SELECT Bezeichnung, Name AS Leiter, Vorname
From Projekt, Mitarbeiter
WHERE Mitarbeiter.MNr = Projekt.Leiter
ORDER BY Name, Vorname;
Beispiel 2:
Mit welchen Stundenanteilen arbeiten welche Mitarbeiter in den einzelnen Projekten?
Das Dynaset (bestehend aus den Attributen Name und Vorname) soll aufsteigend nach
Name und Vorname der Mitarbeiter geordnet sein.
SQL:
SELECT Name, Vorname, Bezeichnung, Zeitanteil
FROM Mitarbeiter, ProjektMitarbeiter, Projekt
WHERE Mitarbeiter.MNr = ProjektMitarbeiter.MNr
AND Projekt.ProjNr = ProjektMitarbeiter.ProjNr
ORDER BY Mitarbeiter.Name, Mitarbeiter.Vorname;
(UNOlUXQJ
•
•
!#"%$"'&()%+*,-.-/"
0 UP1V2W 243(
'
@RCTXSd6QlQ5X2Z2 m
X%YNZ5X 27689Z1;XV:<2X =?>@A[3B\5Z^CD]`89_>a 212Eb c%5d FGH[f3Be1_Ig ]`@X1Z%@Z1_2KU(J,X5Z L1MNhiM/1X24dkjOX>Z3Pl%LQX8N] 2891j
nopqrNr/q,sut^vor nq
8P
7DEHOOHQPLWHLQDQGHU]XYHUNQSIHQEHQXW]WPDQJHZ|KQOLFK
GHQ -2,1 %HIHKO 0DQ
PX‰ DEHU QLFKW ]ZDQJVOlXILJ ]ZHL
YHUVFKLHGHQH 7DEHOOHQ QXW]HQ XP
HLQHQ -2,1 DXV]XIKUHQ
0DQFKPDONDQQHVDXFKVLQQYROOVHLQ(LQWUlJHLQHLQHU7DEHOOHPLW
DQGHUHQ(LQWUlJHQLQGHUVHOEHQ7DEHOOHPLWHLQDQGHU]XYHUJOHLFKHQ
'DV6FKOVVHOZRUW-2,1 PX‰LQGLHVHP
%HLVSLHODXFKQLFKWH[SOL]LW
DQJHJHEHQZHUGHQ