Übungen und Lösungen(Stand:15.7.02):

1)Datenbank anlegen/anzeigen/löschen/anlegen
mysql> create database xyz;
Query OK, 1 row affected (0.10 sec)

mysql> show databases;
+----------+
| Database |
+----------+
| flo_test |
| mysql    |
| test     |
| xyz      |
+----------+
4 rows in set (0.12 sec)

mysql> drop database xyz;
Query OK, 0 rows affected (0.42 sec)

mysql> show databases;
+----------+
| Database |
+----------+
| flo_test |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

mysql> create database xyz;
Query OK, 1 row affected (0.42 sec)

2) Tabelle anlegen, anzeigen, Struktur anzeigen, löschen
mysql> use xyz;
Database changed
mysql> create table abc (ID int not null auto_increment, name char(20), geb_jahr
 year, primary key(ID));
Query OK, 0 rows affected (0.50 sec)

mysql> show tables;
+---------------+
| Tables_in_xyz |
+---------------+
| abc           |
+---------------+
1 row in set (0.00 sec)

mysql> explain abc;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| ID       | int(11)  |      | PRI | NULL    | auto_increment |
| name     | char(20) | YES  |     | NULL    |                |
| geb_jahr | year(4)  | YES  |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
3 rows in set (0.43 sec)

mysql> drop table abc;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

3) Tabelle Buch erstellen und umbenennen:
mysql> create table buch(ID int not null auto_increment, autor char(20), titel c
har(20), preis float, jahr year, primary key(ID));
Query OK, 0 rows affected (0.41 sec)

mysql> explain buch;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| ID    | int(11)  |      | PRI | NULL    | auto_increment |
| autor | char(20) | YES  |     | NULL    |                |
| titel | char(20) | YES  |     | NULL    |                |
| preis | float    | YES  |     | NULL    |                |
| jahr  | year(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table buch rename buchliste;
Query OK, 0 rows affected (0.43 sec)

mysql> show tables;
+---------------+
| Tables_in_xyz |
+---------------+
| buchliste     |
+---------------+
1 row in set (0.01 sec)

4) Spalte hinzufügen etc.
mysql> alter table buchliste add (verlag char(15));
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain buchliste;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| ID     | int(11)  |      | PRI | NULL    | auto_increment |
| autor  | char(20) | YES  |     | NULL    |                |
| titel  | char(20) | YES  |     | NULL    |                |
| preis  | float    | YES  |     | NULL    |                |
| jahr   | year(4)  | YES  |     | NULL    |                |
| verlag | char(15) | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> alter table buchliste drop verlag;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain buchliste;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| ID    | int(11)  |      | PRI | NULL    | auto_increment |
| autor | char(20) | YES  |     | NULL    |                |
| titel | char(20) | YES  |     | NULL    |                |
| preis | float    | YES  |     | NULL    |                |
| jahr  | year(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table buchliste drop primary key;
ERROR 1075: Incorrect table definition; There can only be one auto column and it
 must be defined as a key
 
mysql> alter table buchliste modify ID int not null;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain buchliste;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ID    | int(11)  |      | PRI | 0       |       |
| autor | char(20) | YES  |     | NULL    |       |
| titel | char(20) | YES  |     | NULL    |       |
| preis | float    | YES  |     | NULL    |       |
| jahr  | year(4)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table buchliste drop primary key;
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain buchliste;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ID    | int(11)  |      |     | 0       |       |
| autor | char(20) | YES  |     | NULL    |       |
| titel | char(20) | YES  |     | NULL    |       |
| preis | float    | YES  |     | NULL    |       |
| jahr  | year(4)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table buchliste add primary key(ID);
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table buchliste modify ID int not null auto_increment;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain buchliste;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| ID    | int(11)  |      | PRI | NULL    | auto_increment |
| autor | char(20) | YES  |     | NULL    |                |
| titel | char(20) | YES  |     | NULL    |                |
| preis | float    | YES  |     | NULL    |                |
| jahr  | year(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


mysql> alter table buchliste change titel buchtitel char(21);
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain buchliste;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| ID        | int(11)  |      | PRI | NULL    | auto_increment |
| autor     | char(20) | YES  |     | NULL    |                |
| buchtitel | char(21) | YES  |     | NULL    |                |
| preis     | float    | YES  |     | NULL    |                |
| jahr      | year(4)  | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> alter table buchliste alter preis SET DEFAULT 10;
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain buchliste;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| ID        | int(11)  |      | PRI | NULL    | auto_increment |
| autor     | char(20) | YES  |     | NULL    |                |
| buchtitel | char(21) | YES  |     | NULL    |                |
| preis     | float    | YES  |     | 10      |                |
| jahr      | year(4)  | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


5) Daten einfügen:
mysql> insert into buchliste values (" ","Muster","SQL","12",2000);
Query OK, 1 row affected (0.43 sec)

mysql> select * from buchliste;
+----+--------+-----------+-------+------+
| ID | autor  | buchtitel | preis | jahr |
+----+--------+-----------+-------+------+
|  1 | Huber  | MySQL     |     0 | 1999 |
|  2 | Muster | SQL       |    12 | 2000 |
+----+--------+-----------+-------+------+
2 rows in set (0.00 sec)

mysql> insert into buchliste(autor, buchtitel, jahr) values ("Test", "Linux",200
2), ("Maxl", "OpenOffice", 2002);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from buchliste;
+----+--------+------------+-------+------+
| ID | autor  | buchtitel  | preis | jahr |
+----+--------+------------+-------+------+
|  1 | Huber  | MySQL      |     0 | 1999 |
|  2 | Muster | SQL        |    12 | 2000 |
|  3 | Maier  | Win98SE    |     0 | 1999 |
|  4 | Test   | Linux      |    10 | 2002 |
|  5 | Maxl   | OpenOffice |    10 | 2002 |
+----+--------+------------+-------+------+
5 rows in set (0.00 sec)

6) SELECT:
mysql> select * from buchliste;
+----+--------+------------+-------+------+
| ID | autor  | buchtitel  | preis | jahr |
+----+--------+------------+-------+------+
|  1 | Huber  | MySQL      |     0 | 1999 |
|  2 | Muster | SQL        |    12 | 2000 |
|  3 | Maier  | Win98SE    |     0 | 1999 |
|  4 | Test   | Linux      |    10 | 2002 |
|  5 | Maxl   | OpenOffice |    10 | 2002 |
|  6 | Tom    | Jerry      |    20 | 1997 |
+----+--------+------------+-------+------+
6 rows in set (0.00 sec)

mysql> select autor, buchtitel from buchliste;
+--------+------------+
| autor  | buchtitel  |
+--------+------------+
| Huber  | MySQL      |
| Muster | SQL        |
| Maier  | Win98SE    |
| Test   | Linux      |
| Maxl   | OpenOffice |
| Tom    | Jerry      |
| Huber  | PHP        |
+--------+------------+
7 rows in set (0.00 sec)

mysql> select * from buchliste order by autor asc;
+----+--------+------------+-------+------+
| ID | autor  | buchtitel  | preis | jahr |
+----+--------+------------+-------+------+
|  1 | Huber  | MySQL      |     0 | 1999 |
|  7 | Huber  | PHP        |    11 | 2001 |
|  3 | Maier  | Win98SE    |     0 | 1999 |
|  5 | Maxl   | OpenOffice |    10 | 2002 |
|  2 | Muster | SQL        |    12 | 2000 |
|  4 | Test   | Linux      |    10 | 2002 |
|  6 | Tom    | Jerry      |    20 | 1997 |
+----+--------+------------+-------+------+
7 rows in set (0.00 sec)

mysql> select * from buchliste order by autor desc;
+----+--------+------------+-------+------+
| ID | autor  | buchtitel  | preis | jahr |
+----+--------+------------+-------+------+
|  6 | Tom    | Jerry      |    20 | 1997 |
|  4 | Test   | Linux      |    10 | 2002 |
|  2 | Muster | SQL        |    12 | 2000 |
|  5 | Maxl   | OpenOffice |    10 | 2002 |
|  3 | Maier  | Win98SE    |     0 | 1999 |
|  1 | Huber  | MySQL      |     0 | 1999 |
|  7 | Huber  | PHP        |    11 | 2001 |
+----+--------+------------+-------+------+
7 rows in set (0.00 sec)

mysql> select buchtitel, preis*13.76 from buchliste;
+------------+-------------+
| buchtitel  | preis*13.76 |
+------------+-------------+
| MySQL      |           0 |
| SQL        |      165.12 |
| Win98SE    |           0 |
| Linux      |       137.6 |
| OpenOffice |       137.6 |
| Jerry      |       275.2 |
| PHP        |      151.36 |
+------------+-------------+
7 rows in set (0.45 sec)


mysql> select AVG(preis), MIN(preis), MAX(preis) from buchliste;
+------------+------------+------------+
| AVG(preis) | MIN(preis) | MAX(preis) |
+------------+------------+------------+
|          9 |          0 |         20 |
+------------+------------+------------+
1 row in set (0.00 sec)

mysql> select count(*) from buchliste;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> select * from buchliste where autor="Huber";
+----+-------+-----------+-------+------+
| ID | autor | buchtitel | preis | jahr |
+----+-------+-----------+-------+------+
|  1 | Huber | MySQL     |     0 | 1999 |
|  7 | Huber | PHP       |    11 | 2001 |
+----+-------+-----------+-------+------+
2 rows in set (0.00 sec)

mysql> select count(*) from buchliste where autor="Huber";
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)


mysql> select buchtitel from buchliste where preis < 10;
+-----------+
| buchtitel |
+-----------+
| MySQL     |
| Win98SE   |
+-----------+
2 rows in set (0.01 sec)


mysql> select * from buchliste where jahr=1999;
+----+-------+-----------+-------+------+
| ID | autor | buchtitel | preis | jahr |
+----+-------+-----------+-------+------+
|  1 | Huber | MySQL     |     0 | 1999 |
|  3 | Maier | Win98SE   |     0 | 1999 |
+----+-------+-----------+-------+------+
2 rows in set (0.01 sec)

mysql> select * from buchliste where jahr=1999 and autor="Huber";
+----+-------+-----------+-------+------+
| ID | autor | buchtitel | preis | jahr |
+----+-------+-----------+-------+------+
|  1 | Huber | MySQL     |     0 | 1999 |
+----+-------+-----------+-------+------+
1 row in set (0.41 sec)

mysql> select * from buchliste where autor<>"Huber";
+----+--------+------------+-------+------+
| ID | autor  | buchtitel  | preis | jahr |
+----+--------+------------+-------+------+
|  2 | Muster | SQL        |    12 | 2000 |
|  3 | Maier  | Win98SE    |     0 | 1999 |
|  4 | Test   | Linux      |    10 | 2002 |
|  5 | Maxl   | OpenOffice |    10 | 2002 |
|  6 | Tom    | Jerry      |    20 | 1997 |
+----+--------+------------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from buchliste where autor="Huber" or autor="Maxl";
+----+-------+------------+-------+------+
| ID | autor | buchtitel  | preis | jahr |
+----+-------+------------+-------+------+
|  1 | Huber | MySQL      |     0 | 1999 |
|  5 | Maxl  | OpenOffice |    10 | 2002 |
|  7 | Huber | PHP        |    11 | 2001 |
+----+-------+------------+-------+------+
3 rows in set (0.01 sec)

mysql> select * from buchliste where autor IN("Huber","Maxl");
+----+-------+------------+-------+------+
| ID | autor | buchtitel  | preis | jahr |
+----+-------+------------+-------+------+
|  1 | Huber | MySQL      |     0 | 1999 |
|  5 | Maxl  | OpenOffice |    10 | 2002 |
|  7 | Huber | PHP        |    11 | 2001 |
+----+-------+------------+-------+------+
3 rows in set (0.00 sec)


mysql> select buchtitel from buchliste where buchtitel like "M%";
+-----------+
| buchtitel |
+-----------+
| MySQL     |
+-----------+
1 row in set (0.00 sec)

mysql> select * from buchliste where jahr>=1999 and jahr<=2001;
+----+--------+-----------+-------+------+
| ID | autor  | buchtitel | preis | jahr |
+----+--------+-----------+-------+------+
|  1 | Huber  | MySQL     |     0 | 1999 |
|  2 | Muster | SQL       |    12 | 2000 |
|  3 | Maier  | Win98SE   |     0 | 1999 |
|  7 | Huber  | PHP       |    11 | 2001 |
+----+--------+-----------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from buchliste where jahr between 1999 and 2001;
+----+--------+-----------+-------+------+
| ID | autor  | buchtitel | preis | jahr |
+----+--------+-----------+-------+------+
|  1 | Huber  | MySQL     |     0 | 1999 |
|  2 | Muster | SQL       |    12 | 2000 |
|  3 | Maier  | Win98SE   |     0 | 1999 |
|  7 | Huber  | PHP       |    11 | 2001 |
+----+--------+-----------+-------+------+
4 rows in set (0.01 sec)


mysql> select autor,AVG(preis) from buchliste group by autor;
+--------+------------+
| autor  | AVG(preis) |
+--------+------------+
| Huber  |        5.5 |
| Maier  |          0 |
| Maxl   |         10 |
| Muster |         12 |
| Test   |         10 |
| Tom    |         20 |
+--------+------------+
6 rows in set (0.00 sec)


7) UPDATE:
mysql> update buchliste set preis=15 where autor="Huber";
Query OK, 2 rows affected (0.42 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from buchliste;
+----+--------+------------+-------+------+
| ID | autor  | buchtitel  | preis | jahr |
+----+--------+------------+-------+------+
|  1 | Huber  | MySQL      |    15 | 1999 |
|  2 | Muster | SQL        |    12 | 2000 |
|  3 | Maier  | Win98SE    |     0 | 1999 |
|  4 | Test   | Linux      |    10 | 2002 |
|  5 | Maxl   | OpenOffice |    10 | 2002 |
|  6 | Tom    | Jerry      |    20 | 1997 |
|  7 | Huber  | PHP        |    15 | 2001 |
+----+--------+------------+-------+------+
7 rows in set (0.00 sec)


mysql> alter table buchliste add preis2 float;
Query OK, 7 rows affected (0.43 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> explain buchliste;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| ID        | int(11)  |      | PRI | NULL    | auto_increment |
| autor     | char(20) | YES  |     | NULL    |                |
| buchtitel | char(21) | YES  |     | NULL    |                |
| preis     | float    | YES  |     | 10      |                |
| jahr      | year(4)  | YES  |     | NULL    |                |
| preis2    | float    | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> update buchliste set preis2=preis*2;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> select * from buchliste;
+----+--------+------------+-------+------+--------+
| ID | autor  | buchtitel  | preis | jahr | preis2 |
+----+--------+------------+-------+------+--------+
|  1 | Huber  | MySQL      |    15 | 1999 |     30 |
|  2 | Muster | SQL        |    12 | 2000 |     24 |
|  3 | Maier  | Win98SE    |     0 | 1999 |      0 |
|  4 | Test   | Linux      |    10 | 2002 |     20 |
|  5 | Maxl   | OpenOffice |    10 | 2002 |     20 |
|  6 | Tom    | Jerry      |    20 | 1997 |     40 |
|  7 | Huber  | PHP        |    15 | 2001 |     30 |
+----+--------+------------+-------+------+--------+
7 rows in set (0.00 sec)