Woche 5, 9. Mai
Folien.
MySQL-Kommandoen
(base) oeide@vintereple2 ~ % mysql --user=root --password
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 443
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 |
| TiereK |
+--------------------+
6 rows in set (0,00 sec)
mysql> USE TiereK;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
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 | 1 |
| 5 | Maus | Kattie | 2 |
+--------+---------+--------+------------+
5 rows in set (0,00 sec)
mysql> SELECT * FROM tiernamen;
+----------+----------------------+-------------+
| tiername | bedeutung | sprache |
+----------+----------------------+-------------+
| Foxie | Vom Tanz | English |
| Mausie | Ironisch | Deutsch |
| Muzte | Unklar | Nordsamisch |
| Lurifaks | Auf Märchen basiert | Norwegisch |
+----------+----------------------+-------------+
4 rows in set (0,00 sec)
mysql> SELECT * FROM personen CROSS JOIN tiere;
+----------+--------------+-------------+--------+---------+--------+------------+
| personID | name | telefonnr | tierID | tier | name | besitzerID |
+----------+--------------+-------------+--------+---------+--------+------------+
| 3 | Anna Kurz | 0121 121 12 | 1 | Fuchs | Foxie | 1 |
| 2 | Øyvind Eide | 075 43 25 | 1 | Fuchs | Foxie | 1 |
| 1 | Petra Knoch | 0171 12 14 | 1 | Fuchs | Foxie | 1 |
| 3 | Anna Kurz | 0121 121 12 | 2 | Lachs | Salmie | 2 |
| 2 | Øyvind Eide | 075 43 25 | 2 | Lachs | Salmie | 2 |
| 1 | Petra Knoch | 0171 12 14 | 2 | Lachs | Salmie | 2 |
| 3 | Anna Kurz | 0121 121 12 | 3 | Katze | Mausie | 3 |
| 2 | Øyvind Eide | 075 43 25 | 3 | Katze | Mausie | 3 |
| 1 | Petra Knoch | 0171 12 14 | 3 | Katze | Mausie | 3 |
| 3 | Anna Kurz | 0121 121 12 | 4 | Rentier | Muzte | 1 |
| 2 | Øyvind Eide | 075 43 25 | 4 | Rentier | Muzte | 1 |
| 1 | Petra Knoch | 0171 12 14 | 4 | Rentier | Muzte | 1 |
| 3 | Anna Kurz | 0121 121 12 | 5 | Maus | Kattie | 2 |
| 2 | Øyvind Eide | 075 43 25 | 5 | Maus | Kattie | 2 |
| 1 | Petra Knoch | 0171 12 14 | 5 | Maus | Kattie | 2 |
+----------+--------------+-------------+--------+---------+--------+------------+
15 rows in set (0,00 sec)
mysql> SELECT * FROM tiere
-> INNER JOIN tiernamen
-> ON tiere.name = tiernamen.tiername;
+--------+---------+--------+------------+----------+-----------+-------------+
| tierID | tier | name | besitzerID | tiername | bedeutung | sprache |
+--------+---------+--------+------------+----------+-----------+-------------+
| 1 | Fuchs | Foxie | 1 | Foxie | Vom Tanz | English |
| 3 | Katze | Mausie | 3 | Mausie | Ironisch | Deutsch |
| 4 | Rentier | Muzte | 1 | Muzte | Unklar | Nordsamisch |
+--------+---------+--------+------------+----------+-----------+-------------+
3 rows in set (0,00 sec)
mysql> SELECT * FROM tiere
-> LEFT JOIN tiernamen
-> ON tiere.name = tiernamen.tiername;
+--------+---------+--------+------------+----------+-----------+-------------+
| tierID | tier | name | besitzerID | tiername | bedeutung | sprache |
+--------+---------+--------+------------+----------+-----------+-------------+
| 1 | Fuchs | Foxie | 1 | Foxie | Vom Tanz | English |
| 2 | Lachs | Salmie | 2 | NULL | NULL | NULL |
| 3 | Katze | Mausie | 3 | Mausie | Ironisch | Deutsch |
| 4 | Rentier | Muzte | 1 | Muzte | Unklar | Nordsamisch |
| 5 | Maus | Kattie | 2 | NULL | NULL | NULL |
+--------+---------+--------+------------+----------+-----------+-------------+
5 rows in set (0,00 sec)
mysql> SELECT * FROM tiere
-> RIGHT JOIN tiernamen
-> ON tiere.name = tiernamen.tiername;
+--------+---------+--------+------------+----------+---------------------+-------------+
| tierID | tier | name | besitzerID | tiername | bedeutung | sprache |
+--------+---------+--------+------------+----------+---------------------+-------------+
| 1 | Fuchs | Foxie | 1 | Foxie | Vom Tanz | English |
| 3 | Katze | Mausie | 3 | Mausie | Ironisch | Deutsch |
| 4 | Rentier | Muzte | 1 | Muzte | Unklar | Nordsamisch |
| NULL | NULL | NULL | NULL | Lurifaks | Auf Märchen basiert | Norwegisch |
+--------+---------+--------+------------+----------+---------------------+-------------+
4 rows in set (0,01 sec)
mysql> SELECT * FROM tiere
-> FULL JOIN tiernamen
-> ON tiere.name = tiernamen.tiername;
ERROR 1054 (42S22): Unknown column 'tiere.name' in 'on clause'
mysql> SELECT * FROM tiere
-> LEFT JOIN tiernamen
-> ON tiere.name = tiernamen.tiername
-> UNION
-> SELECT * FROM tiere
-> RIGHT JOIN tiernamen
-> ON tiere.name = tiernamen.tiername;
+--------+---------+--------+------------+----------+---------------------+-------------+
| tierID | tier | name | besitzerID | tiername | bedeutung | sprache |
+--------+---------+--------+------------+----------+---------------------+-------------+
| 1 | Fuchs | Foxie | 1 | Foxie | Vom Tanz | English |
| 2 | Lachs | Salmie | 2 | NULL | NULL | NULL |
| 3 | Katze | Mausie | 3 | Mausie | Ironisch | Deutsch |
| 4 | Rentier | Muzte | 1 | Muzte | Unklar | Nordsamisch |
| 5 | Maus | Kattie | 2 | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | Lurifaks | Auf Märchen basiert | Norwegisch |
+--------+---------+--------+------------+----------+---------------------+-------------+
6 rows in set (0,01 sec)
mysql>