Woche 3, 25. April
Für nächste Woche
Bitte installieren Sie MySQL, Community-Version (GPL): https://www.mysql.com/downloads/
OSX, Windows und viele Linux-Varianten sind zugänglich: https://dev.mysql.com/downloads/mysql/
Falls Probleme machen wir ein kleines Install Fest.
MySQL-Kommandoen
% mysql --user=root --password
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 347
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0,00 sec)
mysql> CREATE DATABASE TiereK;
Query OK, 1 row affected (0,00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| TiereK |
+--------------------+
6 rows in set (0,00 sec)
mysql> USE TiereK;
Database changed
mysql> SHOW TABLES;
Empty set (0,00 sec)
mysql> CREATE TABLE tiere
-> (tierID int, tier varchar(20), name varchar(20), besitzerID int);
Query OK, 0 rows affected (0,01 sec)
mysql> SHOW TABLES;
+------------------+
| Tables_in_tierek |
+------------------+
| tiere |
+------------------+
1 row in set (0,00 sec)
mysql> DROP TABLE tiere;
Query OK, 0 rows affected (0,01 sec)
mysql> CREATE TABLE tiere
-> (tierID int NOT NULL, tier varchar(20), name varchar(20), besitzerID int);
Query OK, 0 rows affected (0,00 sec)
mysql> CREATE TABLE personen
-> (personID INT NOT NULL, name varchar(30), telefonnr varchar(15));
Query OK, 0 rows affected (0,00 sec)
mysql> SHOW TABLES;
+------------------+
| Tables_in_tierek |
+------------------+
| personen |
| tiere |
+------------------+
2 rows in set (0,00 sec)
mysql> DESCRIBE personen;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| personID | int | NO | | NULL | |
| name | varchar(30) | YES | | NULL | |
| telefonnr | varchar(15) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0,00 sec)
mysql> DESCRIBE tiere;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| tierID | int | NO | | NULL | |
| tier | varchar(20) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| besitzerID | int | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0,00 sec)
mysql> CREATE UNIQUE INDEX tierIDidx ON tiere(tierID);
Query OK, 0 rows affected (0,01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE tiere;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| tierID | int | NO | PRI | NULL | |
| tier | varchar(20) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| besitzerID | int | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0,00 sec)
mysql> DROP INDEX tierIDidx ON tiere;
Query OK, 0 rows affected (0,02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE tiere;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| tierID | int | NO | | NULL | |
| tier | varchar(20) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| besitzerID | int | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0,00 sec)
mysql> CREATE UNIQUE INDEX tierIDidx ON tiere(tierID);
Query OK, 0 rows affected (0,01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE tiere;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| tierID | int | NO | PRI | NULL | |
| tier | varchar(20) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| besitzerID | int | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0,00 sec)
mysql> CREATE UNIQUE INDEX personIDidx ON personen(personID);
Query OK, 0 rows affected (0,01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE personen;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| personID | int | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| telefonnr | varchar(15) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0,00 sec)
mysql> ALTER TABLE tiere
-> ADD CONSTRAINT tiereFK1 FOREIGN KEY (besitzerID)
-> REFERNCES personen (personID)
-> ON DELETE RESTRICT ON UPDATE RESTRICT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERNCES personen (personID)
ON DELETE RESTRICT ON UPDATE RESTRICT at line 3
mysql> ALTER TABLE tiere
-> ADD CONSTRAINT tiereFK1 FOREIGN KEY (besitzerID)
-> REFERENCES personen (personID)
-> ON DELETE RESTRICT ON UPDATE RESTRICT;
Query OK, 0 rows affected (0,02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE personen;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| personID | int | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| telefonnr | varchar(15) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0,01 sec)
mysql> DESCRIBE tiere;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| tierID | int | NO | PRI | NULL | |
| tier | varchar(20) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| besitzerID | int | YES | MUL | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0,00 sec)
mysql> INSERT INTO tiere(tierID, tier, name, besitzerID) VALUES (1, "Fuchs", "Foxie", 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tierek`.`tiere`, CONSTRAINT `tiereFK1` FOREIGN KEY (`besitzerID`) REFERENCES `personen` (`personID`) ON DELETE RESTRICT ON UPDATE RESTRICT)
mysql> INSERT INTO tiere(tierID, tier, name) VALUES (1, "Fuchs", "Foxie");
Query OK, 1 row affected (0,00 sec)
mysql> SELECT * FROM tiere;
+--------+-------+-------+------------+
| tierID | tier | name | besitzerID |
+--------+-------+-------+------------+
| 1 | Fuchs | Foxie | NULL |
+--------+-------+-------+------------+
1 row in set (0,00 sec)
mysql> INSERT INTO personen (personID, name, telefonnr) VALUES (1, "Petra Knoch", "0171 12 14");
Query OK, 1 row affected (0,00 sec)
mysql> INSERT INTO personen (personID, name, telefonnr) VALUES (1, "Øyvind Eide", "075 43 25");
ERROR 1062 (23000): Duplicate entry '1' for key 'personen.personIDidx'
mysql> INSERT INTO personen (personID, name, telefonnr) VALUES (2, "Øyvind Eide", "075 43 25");
Query OK, 1 row affected (0,00 sec)
mysql> INSERT INTO personen (personID, name, telefonnr) VALUES (3, "Anna Kurz", "0121 121 12");
Query OK, 1 row affected (0,00 sec)
mysql> select * from personen;
+----------+--------------+-------------+
| personID | name | telefonnr |
+----------+--------------+-------------+
| 1 | Petra Knoch | 0171 12 14 |
| 2 | Øyvind Eide | 075 43 25 |
| 3 | Anna Kurz | 0121 121 12 |
+----------+--------------+-------------+
3 rows in set (0,00 sec)
mysql> select * from tiere;
+--------+-------+-------+------------+
| tierID | tier | name | besitzerID |
+--------+-------+-------+------------+
| 1 | Fuchs | Foxie | NULL |
+--------+-------+-------+------------+
1 row in set (0,00 sec)
mysql> UPDATE tiere SET besitzerID = 1 where tierID = 1;
Query OK, 1 row affected (0,00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tiere;
+--------+-------+-------+------------+
| tierID | tier | name | besitzerID |
+--------+-------+-------+------------+
| 1 | Fuchs | Foxie | 1 |
+--------+-------+-------+------------+
1 row in set (0,00 sec)
mysql> delete from personen where personID = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`tierek`.`tiere`, CONSTRAINT `tiereFK1` FOREIGN KEY (`besitzerID`) REFERENCES `personen` (`personID`) ON DELETE RESTRICT ON UPDATE RESTRICT)
mysql> delete from personen where personID = 2;
Query OK, 1 row affected (0,00 sec)
mysql> select * from personen;
+----------+-------------+-------------+
| personID | name | telefonnr |
+----------+-------------+-------------+
| 1 | Petra Knoch | 0171 12 14 |
| 3 | Anna Kurz | 0121 121 12 |
+----------+-------------+-------------+
2 rows in set (0,00 sec)
mysql> INSERT INTO personen (personID, name, telefonnr) VALUES (2, "Øyvind Eide", "075 43 25");
Query OK, 1 row affected (0,00 sec)
mysql> select * from personen;
+----------+--------------+-------------+
| personID | name | telefonnr |
+----------+--------------+-------------+
| 1 | Petra Knoch | 0171 12 14 |
| 2 | Øyvind Eide | 075 43 25 |
| 3 | Anna Kurz | 0121 121 12 |
+----------+--------------+-------------+
3 rows in set (0,00 sec)
mysql> INSERT INTO tiere(tierID, tier, name, besitzerID) VALUES (2, "Lachs", "Salmie", 2);
Query OK, 1 row affected (0,00 sec)
mysql> INSERT INTO tiere(tierID, tier, name, besitzerID) VALUES (3, "Katze", "Mausie", 3);
Query OK, 1 row affected (0,00 sec)
mysql> INSERT INTO tiere(tierID, tier, name, besitzerID) VALUES (4, "Rentier", "Muzte", 3);
Query OK, 1 row affected (0,00 sec)
mysql> select * from personen;
+----------+--------------+-------------+
| personID | name | telefonnr |
+----------+--------------+-------------+
| 1 | Petra Knoch | 0171 12 14 |
| 2 | Øyvind Eide | 075 43 25 |
| 3 | Anna Kurz | 0121 121 12 |
+----------+--------------+-------------+
3 rows in set (0,00 sec)
mysql> select * from tiere;
+--------+---------+--------+------------+
| tierID | tier | name | besitzerID |
+--------+---------+--------+------------+
| 1 | Fuchs | Foxie | 1 |
| 2 | Lachs | Salmie | 2 |
| 3 | Katze | Mausie | 3 |
| 4 | Rentier | Muzte | 3 |
+--------+---------+--------+------------+
4 rows in set (0,00 sec)
mysql> INSERT INTO tiere(tierID, tier, name, besitzerID) VALUES (5, "Maus", "Kattie", 2);
Query OK, 1 row affected (0,00 sec)
mysql> select * from tiere;
+--------+---------+--------+------------+
| tierID | tier | name | besitzerID |
+--------+---------+--------+------------+
| 1 | Fuchs | Foxie | 1 |
| 2 | Lachs | Salmie | 2 |
| 3 | Katze | Mausie | 3 |
| 4 | Rentier | Muzte | 3 |
| 5 | Maus | Kattie | 2 |
+--------+---------+--------+------------+
5 rows in set (0,00 sec)
mysql> UPDATE tiere SET besitzerID = 1 WHERE tierID = 4;
Query OK, 0 rows affected (0,00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from tiere;
+--------+---------+--------+------------+
| tierID | tier | name | besitzerID |
+--------+---------+--------+------------+
| 1 | Fuchs | Foxie | 1 |
| 2 | Lachs | Salmie | 2 |
| 3 | Katze | Mausie | 3 |
| 4 | Rentier | Muzte | 1 |
| 5 | Maus | Kattie | 2 |
+--------+---------+--------+------------+
5 rows in set (0,00 sec)
mysql>