MySQL einrichten

Download bibliothek.sql

Für root wurde das Passwort xxxxx vergeben.

MySQL starten

H:\mysql>mysql -u root -pxxxxx
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 76 to server version: 5.0.27-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

MySQL-root richtet die Datenbank bibliothek mit den Tabellen buch, leser, leiht ein.

Nach Export durch
    mysqldump  -hlocalhost  -uroot  -pxxxxx  bibliothek > bibliothek.txt
ist der Import mit
    mysql  -hlocalhost  -uroot  -pxxxxx  bibliothek < bibliothek.txt
möglich.

bibliothek.txt

Datenbank erstellen

mysql> create database bibliothek;
Query OK, 1 row affected (0.00 sec)

mysql> use bibliothek;
Database changed

Tabellen erstellen

mysql> create table buch (
-> BuchNr integer primary key,
-> BuchAutor char(15),
-> BuchTitel char(50),
-> BuchPreis decimal(6,2));
Query OK, 0 rows affected (0.02 sec)

mysql> create table leiht (
-> BuchNr integer,
-> LeserNr integer,
-> Datum date);
Query OK, 0 rows affected (0.03 sec)

mysql> create table leser (
-> LeserNr integer,
-> LeserName char(15),
-> LeserAlter tinyint,
-> LeserGeschlecht char(1),
-> LeserBetreuer integer);
Query OK, 0 rows affected (0.00 sec)

Daten eingeben

mysql> insert into buch values (23,'Schiller','Kabale und Liebe',29.00);
Query OK, 1 row affected (0.00 sec)
mysql> insert into buch values (45,'Frisch','Andorra',34.00);
Query OK, 1 row affected (0.00 sec)
mysql> insert into buch values (56,'Goethe','Faust',24.00);
Query OK, 1 row affected (0.00 sec)
mysql> insert into buch values (57,'Schiller','Die Räuber',32.00);
Query OK, 1 row affected (0.00 sec)

mysql> insert into leser values (23,'Kurt',18,'m',45);
Query OK, 1 row affected (0.00 sec)
mysql> insert into leser values (33,'Else',13,'w',33);
Query OK, 1 row affected (0.00 sec)
mysql> insert into leser values (45,'Paul',18,'m',45);
Query OK, 1 row affected (0.00 sec)
mysql> insert into leser values (65,'Lisa',14,'w',33);
Query OK, 1 row affected (0.00 sec)
mysql> insert into leser values (67,'Erna',13,'w',33);
Query OK, 1 row affected (0.00 sec)

mysql> insert into leiht values (57,45,'2002-04-02');
Query OK, 1 row affected (0.00 sec)
mysql> insert into leiht values (45,33,'2002-04-05');
Query OK, 1 row affected (0.00 sec)
mysql> insert into leiht values (23,45,'2002-04-05');
Query OK, 1 row affected (0.00 sec)
mysql> insert into leiht values (57,45,'2002-05-12');
Query OK, 1 row affected (0.00 sec)

MySQL-root richtet einen Benutzer gast ein, der bezogen auf die Datenbank bibliothek Leserechte bekommt.

Es wird vorausgesetzt, dass sich der Benutzer gast am Server localhost anmeldet (direkt oder über telnet/ssh).

Benutzer anlegen

mysql> grant usage on bibliothek.* to gast@localhost identified by 'xgast';
Query OK, 0 rows affected (0.09 sec)

mysql> grant select on bibliothek.* to gast@localhost;
Query OK, 0 rows affected (0.03 sec)

Eine differenzierte Aufgabenverteilung für verschiedene Benutzer wird unten angegeben.

Datenbank schliessen

mysql> quit;
Bye

H:\>

Jetzt kann gast , wenn er am Server localhost direkt oder über telnet/ssh angemeldet ist, die Datenbank öffnen und Abfragen starten.

Datenbank öffnen

H:\mysql>mysql -u gast -pgast bibliothek
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 76 to server version: 5.0.27-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Abfragen

einfachste Form:

mysql> select * from leser;
+---------+-----------+------------+-----------------+---------------+
| LeserNr | LeserName | LeserAlter | LeserGeschlecht | LeserBetreuer |
+---------+-----------+------------+-----------------+---------------+
|      23 | Kurt      |         18 | m               |            45 |
|      33 | Else      |         13 | w               |            33 |
|      45 | Paul      |         18 | m               |            45 |
|      65 | Lisa      |         14 | w               |            33 |
|      67 | Erna      |         13 | w               |            33 |
+---------+-----------+------------+-----------------+---------------+
5 rows in set (0.00 sec)

Abfrage mit Kriterium:

(1) Vergleichsoperatoren =, <, >, <=, >=, <> bzw. !=

mysql> select * from leser where LeserAlter<18;
+---------+-----------+------------+-----------------+---------------+
| LeserNr | LeserName | LeserAlter | LeserGeschlecht | LeserBetreuer |
+---------+-----------+------------+-----------------+---------------+
|      33 | Else      |         13 | w               |            33 |
|      65 | Lisa      |         14 | w               |            33 |
|      67 | Erna      |         13 | w               |            33 |
+---------+-----------+------------+-----------------+---------------+
3 rows in set (0.00 sec)

(2) Mengenoperatoren IN, =ANY, <ANY, ..., =ALL, ...

mysql> select * from leser where LeserAlter in (14,16,18);
+---------+-----------+------------+-----------------+---------------+
| LeserNr | LeserName | LeserAlter | LeserGeschlecht | LeserBetreuer |
+---------+-----------+------------+-----------------+---------------+
|      23 | Kurt      |         18 | m               |            45 |
|      45 | Paul      |         18 | m               |            45 |
|      65 | Lisa      |         14 | w               |            33 |
+---------+-----------+------------+-----------------+---------------+
3 rows in set (0.00 sec)

(3) Bereichsabgleich

mysql> select * from leser where LeserAlter between 14 and 18;
+---------+-----------+------------+-----------------+---------------+
| LeserNr | LeserName | LeserAlter | LeserGeschlecht | LeserBetreuer |
+---------+-----------+------------+-----------------+---------------+
|      23 | Kurt      |         18 | m               |            45 |
|      45 | Paul      |         18 | m               |            45 |
|      65 | Lisa      |         14 | w               |            33 |
+---------+-----------+------------+-----------------+---------------+
3 rows in set (0.00 sec)

(4) Existenzabgleich

mysql> select * from leser where exists
-> (select * from leiht where leiht.LeserNr=Leser.LeserNr);
+---------+-----------+------------+-----------------+---------------+
| LeserNr | LeserName | LeserAlter | LeserGeschlecht | LeserBetreuer |
+---------+-----------+------------+-----------------+---------------+
|      33 | Else      |         13 | w               |            33 |
|      45 | Paul      |         18 | m               |            45 |
+---------+-----------+------------+-----------------+---------------+
2 rows in set (0.00 sec)

(x) Die Bedingungen sind mit den Booleschen Operatoren NOT, AND, OR erweiterbar.

mysql> select * from leser
-> where LeserAlter not in (13,14)
-> and (LeserGeschlecht='m' or LeserName='Erna');
+---------+-----------+------------+-----------------+---------------+
| LeserNr | LeserName | LeserAlter | LeserGeschlecht | LeserBetreuer |
+---------+-----------+------------+-----------------+---------------+
|      23 | Kurt      |         18 | m               |            45 |
|      45 | Paul      |         18 | m               |            45 |
+---------+-----------+------------+-----------------+---------------+
2 rows in set (0.02 sec)

Projektion

mysql> select LeserAlter, LeserGeschlecht from leser;
+------------+-----------------+
| LeserAlter | LeserGeschlecht |
+------------+-----------------+
|         18 | m               |
|         13 | w               |
|         18 | m               |
|         14 | w               |
|         13 | w               |
+------------+-----------------+
5 rows in set (0.00 sec)

mysql> select distinct LeserAlter, LeserGeschlecht from leser;
+------------+-----------------+
| LeserAlter | LeserGeschlecht |
+------------+-----------------+
|         18 | m               |
|         13 | w               |
|         14 | w               |
+------------+-----------------+
3 rows in set (0.00 sec)

Join

mysql> select leser.LeserName, leiht.BuchNr
-> from leser,leiht
-> where leser.LeserNr=leiht.LeserNr;
+-----------+--------+
| LeserName | BuchNr |
+-----------+--------+
| Paul      |     57 |
| Else      |     45 |
| Paul      |     23 |
| Paul      |     57 |
+-----------+--------+
4 rows in set (0.00 sec)

mysql> select leser.LeserName, leiht.BuchNr
-> from leser inner join leiht on leser.LeserNr=leiht.LeserNr;
+-----------+--------+
| LeserName | BuchNr |
+-----------+--------+
| Paul      |     57 |
| Else      |     45 |
| Paul      |     23 |
| Paul      |     57 |
+-----------+--------+
4 rows in set (0.00 sec)

mysql> select x.LeserName, y.LeserName
-> from leser x, leser y
-> where x.LeserBetreuer=y.LeserNr;
+-----------+-----------+
| LeserName | LeserName |
+-----------+-----------+
| Else      | Else      |
| Lisa      | Else      |
| Erna      | Else      |
| Kurt      | Paul      |
| Paul      | Paul      |
+-----------+-----------+
5 rows in set (0.00 sec)

mysql> select leser.LeserName, Leser_1.LeserName
-> from leser, leser as leser_1
-> where leser.LeserBetreuer=leser_1.LeserNr;
+-----------+-----------+
| LeserName | LeserName |
+-----------+-----------+
| Else      | Else      |
| Lisa      | Else      |
| Erna      | Else      |
| Kurt      | Paul      |
| Paul      | Paul      |
+-----------+-----------+
5 rows in set (0.00 sec)

Spaltenüberschrift und Ergebnisspalte

mysql> select BuchAutor as Autor from buch;
+----------+
| Autor    |
+----------+
| Schiller |
| Frisch   |
| Goethe   |
| Schiller |
+----------+
4 rows in set (0.00 sec)

mysql> select BuchPreis/1.07 as NettoPreis from buch;
+------------+
| NettoPreis |
+------------+
|    27.1028 |
|    31.7757 |
|    22.4299 |
|    29.9065 |
+------------+
4 rows in set (0.00 sec)

Gruppenverarbeitung

mysql> select LeserNr, count(*) as Anzahl
-> from leiht
-> group by LeserNr;
+---------+--------+
| LeserNr | Anzahl |
+---------+--------+
|      33 |      1 |
|      45 |      3 |
+---------+--------+
2 rows in set (0.00 sec)

mysql> select LeserNr, count(distinct BuchNr) as Anzahl
-> from leiht
-> group by LeserNr;
+---------+--------+
| LeserNr | Anzahl |
+---------+--------+
|      33 |      1 |
|      45 |      2 |
+---------+--------+
2 rows in set (0.00 sec)

mysql> select LeserNr, count(*) as Anzahl
-> from leiht
-> group by LeserNr
-> having min(Datum)<'2002-04-05';
+---------+--------+
| LeserNr | Anzahl |
+---------+--------+
|      45 |      3 |
+---------+--------+
1 row in set (0.00 sec)

Gruppen-Funktionen:

AVG(Attribut), COUNT(*), COUNT(Attribut), MAX(Attribut), MIN(Attribut), SUM(Attribut)

mysql> select LeserNr, count(BuchNr) as Anzahl
-> from leiht
-> where Datum>='2002-04-05'
-> group by LeserNr;
+---------+--------+
| LeserNr | Anzahl |
+---------+--------+
|      33 |      1 |
|      45 |      2 |
+---------+--------+
2 rows in set (0.00 sec)

Sortierte Ergebnisse

mysql> select *
-> from leser
-> order by LeserAlter desc, LeserName asc;
+---------+-----------+------------+-----------------+---------------+
| LeserNr | LeserName | LeserAlter | LeserGeschlecht | LeserBetreuer |
+---------+-----------+------------+-----------------+---------------+
|      23 | Kurt      |         18 | m               |            45 |
|      45 | Paul      |         18 | m               |            45 |
|      65 | Lisa      |         14 | w               |            33 |
|      33 | Else      |         13 | w               |            33 |
|      67 | Erna      |         13 | w               |            33 |
+---------+-----------+------------+-----------------+---------------+
5 rows in set (0.00 sec)

Subquery

Eine SELECT-Anweisung innerhalb einer andren SQL-Anweisung wird als Subquery bezeichnet. Bei einer geschachtelten SELECT-Anweisung wird mit der FROM-Klausel der äußeren SELECT-Anweisung eine Ausgangsrelation vorgegeben. Die Tupel dieser Relation werden mit der WHERE-Klausel überprüft. Dazu wird die innere SELECT-Anweisung ausgeführt und der Wahrheitswert ermittelt.

mysql> select LeserName
-> from leser, leiht
-> where leser.LeserNr=leiht.LeserNr and leiht.BuchNr in
-> (select BuchNr from buch where BuchAutor in ('Goethe','Schiller'));
+-----------+
| LeserName |
+-----------+
| Paul      |
| Paul      |
| Paul      |
+-----------+
3 rows in set (0.00 sec)

Verwendete Tabellen

mysql> show tables;
+----------------------+
| Tables_in_bibliothek |
+----------------------+
| buch                 |
| leiht                |
| leser                |
+----------------------+
3 rows in set (0.00 sec)

mysql> show columns from buch;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| BuchNr    | int(11)      |      | PRI | 0       |       |
| BuchAutor | char(15)     | YES  |     | NULL    |       |
| BuchTitel | char(50)     | YES  |     | NULL    |       |
| BuchPreis | decimal(6,2) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show columns from leiht;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| BuchNr  | int(11) | YES  |     | NULL    |       |
| LeserNr | int(11) | YES  |     | NULL    |       |
| Datum   | date    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show columns from leser;
+-----------------+------------+------+-----+---------+-------+
| Field           | Type       | Null | Key | Default | Extra |
+-----------------+------------+------+-----+---------+-------+
| LeserNr         | int(11)    | YES  |     | NULL    |       |
| LeserName       | char(15)   | YES  |     | NULL    |       |
| LeserAlter      | tinyint(4) | YES  |     | NULL    |       |
| LeserGeschlecht | char(1)    | YES  |     | NULL    |       |
| LeserBetreuer   | int(11)    | YES  |     | NULL    |       |
+-----------------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select * from buch;
+--------+-----------+------------------+-----------+
| BuchNr | BuchAutor | BuchTitel        | BuchPreis |
+--------+-----------+------------------+-----------+
|     23 | Schiller  | Kabale und Liebe |     29.00 |
|     45 | Frisch    | Andorra          |     34.00 |
|     56 | Goethe    | Faust            |     24.00 |
|     57 | Schiller  | Die Räuber       |     32.00 |
+--------+-----------+------------------+-----------+
4 rows in set (0.00 sec)

mysql> select * from leiht;
+--------+---------+------------+
| BuchNr | LeserNr | Datum      |
+--------+---------+------------+
|     57 |      45 | 2002-04-02 |
|     45 |      33 | 2002-04-05 |
|     23 |      45 | 2002-04-05 |
|     57 |      45 | 2002-05-12 |
+--------+---------+------------+
4 rows in set (0.02 sec)

mysql> select * from leser;
+---------+-----------+------------+-----------------+---------------+
| LeserNr | LeserName | LeserAlter | LeserGeschlecht | LeserBetreuer |
+---------+-----------+------------+-----------------+---------------+
|      23 | Kurt      |         18 | m               |            45 |
|      33 | Else      |         13 | w               |            33 |
|      45 | Paul      |         18 | m               |            45 |
|      65 | Lisa      |         14 | w               |            33 |
|      67 | Erna      |         13 | w               |            33 |
+---------+-----------+------------+-----------------+---------------+
5 rows in set (0.00 sec)

Differenzierte Benutzerverwaltung

MySQL-root richtet einen Benutzer bibliothek ein, der bezogen auf die Datenbank bibliothek alle Rechte bekommt.

Jedoch kann Benutzer bibliothek keine neuen Benutzer anlegen; deshalb führt root drei weitere Benutzer buch, leser, leiht ohne weitere Rechte ein.

Für Aufsicht über diese drei Benutzer übernimmt der Benutzer bibliothek, der Ihnen das Recht zum Lesen, Schreiben usw. bezogen auf einzelne Tabellen erteilt.

H:\mysql>mysql -u root -pxxxxx
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76 to server version: 5.0.27-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all privileges on bibliothek.* to bibliothek@localhost
-> identified by 'xbibliothek' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on bibliothek.* to buch@localhost
-> identified by 'xbuch' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on bibliothek.* to leiht@localhost
-> identified by 'xleiht' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on bibliothek.* to leser@localhost
-> identified by 'xleser' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

Benutzer bibliothek übernimmt die Kontrolle und teilt den Benutzern buch, leser und leiht Rechte zu.

Dem Benutzer leiht sollte später auch das Lesen (select) der Tabellenspalten buch.bnr, buch.autor, buch.titel, leser.lnr und leser.name ermöglicht werden.

H:\>mysql -u bibliothek -pxbibliothek bibliothek
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1 to server version: 5.0.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant select,insert,update,delete on bibliothek.buch
-> to buch@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert,update,delete on bibliothek.leser
-> to leser@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert,update,delete on bibliothek.leiht
-> to leiht@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

Jetzt kann Benutzer buch die Daten der Tabelle bibliothek.buch betreuen.

Die anderen Tabellen der Datenbank sieht er nicht!

Die Buchnummer bnr wird automatisch erhöht (Eingabe NULL), kann aber auch gezielt angegeben werden. Die bei der automatischern Erhöhung gewählte Zahl wird durch last_insert_id() abgefragt.

H:\>mysql -u buch -pxbuch bibliothek
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
+----------------------+
| Tables_in_bibliothek |
+----------------------+
| buch                 |
+----------------------+
1 row in set (0.00 sec)
 
mysql> show fields from buch;
+--------+------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra          |
+--------+------------+------+-----+---------+----------------+
| bnr    | int(11)    | NO   | PRI | NULL    | auto_increment |
| bautor | char(20)   | YES  |     | NULL    |                |
| btitel | char(50)   | YES  |     | NULL    |                |
| bpreis | float(5,2) | YES  |     | NULL    |                |
+--------+------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)
 
mysql> insert into buch values(5,'frisch','andorra',12);
Query OK, 1 row affected (0.02 sec)
 
mysql> select * from buch;
+-----+--------+---------+--------+
| bnr | bautor | btitel  | bpreis |
+-----+--------+---------+--------+
|   5 | frisch | andorra |  12.00 |
+-----+--------+---------+--------+
1 row in set (0.00 sec)
 
mysql> insert into buch values(NULL,'geothe','faust',9);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into buch values(NULL,'dostojewski','der spieler',10.5);
Query OK, 1 row affected (0.05 sec)
 
mysql> insert into buch values(NULL,'schiller','die räuber',10.5);
Query OK, 1 row affected (0.05 sec)
 
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                8 |
+------------------+
1 row in set (0.00 sec)
 
mysql> select * from buch;
+-----+-------------+-------------+--------+
| bnr | bautor      | btitel      | bpreis |
+-----+-------------+-------------+--------+
|   5 | frisch      | andorra     |  12.00 |
|   6 | geothe      | faust       |   9.00 |
|   7 | dostojewski | der spieler |  10.50 |
|   8 | schiller    | die räuber  |  12.20 |
+-----+-------------+-------------+--------+
5 rows in set (0.00 sec)
 
mysql> delete from buch where bnr=7;
Query OK, 1 row affected (0.00 sec)
 
mysql> update buch SET bautor='goethe' where bautor='geothe';
Query OK, 1 row affected (0.01 sec)
 
mysql> replace into buch values(6,'goethe','wahlverwandschaften',14.6);
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from buch;
+-----+-------------+---------------------+--------+
| bnr | bautor      | btitel              | bpreis |
+-----+-------------+---------------------+--------+
|   5 | frisch      | andorra             |  12.00 |
|   6 | goethe      | wahlverwandschaften |  14.60 |
|   8 | schiller    | die räuber          |  12.20 |
+-----+-------------+---------------------+--------+
4 rows in set (0.00 sec)
 
mysql> quit;
Bye