schule:erste_schritte_in_sql
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
schule:erste_schritte_in_sql [2016-03-29 22:11] – RPi verlinkt marco.bakera | schule:erste_schritte_in_sql [2017-04-19 08:39] (aktuell) – Externe Bearbeitung 127.0.0.1 | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | ====== Erste einfache SQL-Befehle ====== | ||
+ | |||
+ | Wir verwenden eine XAMPP-Installation. Diese enthält einen MySQL-Datenbanksystem, | ||
+ | |||
+ | < | ||
+ | |||
+ | Setting environment for using XAMPP for Windows. | ||
+ | schüler@21N-04 c:\xampp | ||
+ | # mysql | ||
+ | Welcome to the MySQL monitor. | ||
+ | Your MySQL connection id is 1 | ||
+ | Server version: 5.6.11 MySQL Community Server (GPL) | ||
+ | |||
+ | Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. | ||
+ | |||
+ | Oracle is a registered trademark of Oracle Corporation and/or its | ||
+ | affiliates. Other names may be trademarks of their respective | ||
+ | owners. | ||
+ | |||
+ | Type ' | ||
+ | |||
+ | mysql> show databases; | ||
+ | +--------------------+ | ||
+ | | Database | ||
+ | +--------------------+ | ||
+ | | information_schema | | ||
+ | | test | | ||
+ | +--------------------+ | ||
+ | 2 rows in set (0.06 sec) | ||
+ | |||
+ | mysql> exit | ||
+ | Bye | ||
+ | |||
+ | schüler@21N-04 c:\xampp | ||
+ | # mysql -uroot | ||
+ | Welcome to the MySQL monitor. | ||
+ | Your MySQL connection id is 2 | ||
+ | Server version: 5.6.11 MySQL Community Server (GPL) | ||
+ | |||
+ | Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. | ||
+ | |||
+ | Oracle is a registered trademark of Oracle Corporation and/or its | ||
+ | affiliates. Other names may be trademarks of their respective | ||
+ | owners. | ||
+ | |||
+ | Type ' | ||
+ | |||
+ | mysql> show databases; | ||
+ | +--------------------+ | ||
+ | | Database | ||
+ | +--------------------+ | ||
+ | | information_schema | | ||
+ | | cdcol | | ||
+ | | data | | ||
+ | | mysql | | ||
+ | | performance_schema | | ||
+ | | phpmyadmin | ||
+ | | test | | ||
+ | | webauth | ||
+ | +--------------------+ | ||
+ | 8 rows in set (0.06 sec) | ||
+ | |||
+ | mysql> create database it_fakturierung; | ||
+ | Query OK, 1 row affected (0.05 sec) | ||
+ | |||
+ | mysql> show databases; | ||
+ | +---------------------+ | ||
+ | | Database | ||
+ | +---------------------+ | ||
+ | | information_schema | ||
+ | | cdcol | | ||
+ | | data | | ||
+ | | it_fakturierung | ||
+ | | mysql | | ||
+ | | performance_schema | ||
+ | | phpmyadmin | ||
+ | | test | | ||
+ | | webauth | ||
+ | +---------------------+ | ||
+ | 9 rows in set (0.00 sec) | ||
+ | |||
+ | mysql> use it_fakturierung; | ||
+ | Database changed | ||
+ | mysql> show tables; | ||
+ | Empty set (0.00 sec) | ||
+ | |||
+ | mysql> create table produkte | ||
+ | -> (id int primary key, | ||
+ | -> name varchar(256), | ||
+ | -> preis int); | ||
+ | Query OK, 0 rows affected (0.34 sec) | ||
+ | |||
+ | mysql> show tables; | ||
+ | +-------------------------------+ | ||
+ | | Tables_in_it_fakturierung | ||
+ | +-------------------------------+ | ||
+ | | produkte | ||
+ | +-------------------------------+ | ||
+ | 1 row in set (0.00 sec) | ||
+ | |||
+ | mysql> describe produkte; | ||
+ | +-------+--------------+------+-----+---------+-------+ | ||
+ | | Field | Type | Null | Key | Default | Extra | | ||
+ | +-------+--------------+------+-----+---------+-------+ | ||
+ | | id | int(11) | ||
+ | | name | varchar(256) | YES | | NULL | | | ||
+ | | preis | int(11) | ||
+ | +-------+--------------+------+-----+---------+-------+ | ||
+ | 3 rows in set (0.01 sec) | ||
+ | |||
+ | mysql> explain produkte; | ||
+ | +-------+--------------+------+-----+---------+-------+ | ||
+ | | Field | Type | Null | Key | Default | Extra | | ||
+ | +-------+--------------+------+-----+---------+-------+ | ||
+ | | id | int(11) | ||
+ | | name | varchar(256) | YES | | NULL | | | ||
+ | | preis | int(11) | ||
+ | +-------+--------------+------+-----+---------+-------+ | ||
+ | 3 rows in set (0.01 sec) | ||
+ | |||
+ | mysql> INSERT INTO produkte VALUES (187, '3D Graphic Power', | ||
+ | Query OK, 1 row affected (0.03 sec) | ||
+ | |||
+ | mysql> INSERT INTO produkte VALUES (243, 'Super Games', | ||
+ | Query OK, 1 row affected (0.05 sec) | ||
+ | |||
+ | mysql> SELECT * FROM produkte; | ||
+ | +-----+------------------+-------+ | ||
+ | | id | name | preis | | ||
+ | +-----+------------------+-------+ | ||
+ | | 187 | 3D Graphic Power | 9800 | | ||
+ | | 243 | Super Games | 3420 | | ||
+ | +-----+------------------+-------+ | ||
+ | 2 rows in set (0.03 sec) | ||
+ | |||
+ | mysql> SELECT id,name FROM produkte; | ||
+ | +-----+------------------+ | ||
+ | | id | name | | ||
+ | +-----+------------------+ | ||
+ | | 187 | 3D Graphic Power | | ||
+ | | 243 | Super Games | | ||
+ | +-----+------------------+ | ||
+ | 2 rows in set (0.00 sec) | ||
+ | |||
+ | mysql> exit; | ||
+ | Bye | ||
+ | |||
+ | schüler@21N-04 c:\xampp | ||
+ | # f: | ||
+ | |||
+ | schüler@21N-04 F:\ | ||
+ | # cd " | ||
+ | |||
+ | schüler@21N-04 F: | ||
+ | # mysqldump -uroot it_fakturierung > dump.sql | ||
+ | |||
+ | schüler@21N-04 F: | ||
+ | # | ||
+ | </ | ||
+ | |||
+ | ===== Übungen ===== | ||
+ | |||
+ | - Betrachte die obigen Befehle und versuche, ihre Funktionalität nachzuvollziehen. | ||
+ | - Gib die Befehle selbst ein und vergleiche die Ausgabe. | ||
+ | - Welche Befehle fehlen in der Backupdatei? | ||
+ | - Mit welcher Option können die fehlenden Befehle ergänzt werden? | ||
+ | - Lösche die angelegte Datenbank.((Du erhältst Hilfe mit dem Befehl '' | ||
+ | - Stelle die Datenbank aus der Backupdatei wieder her.((// | ||
+ | - Prüfe, ob (a) die Datenbank erstellt, (b) die Tabellen darin angelegt und < | ||