Lernen Sie MySQL-Tabellenstrukturen und Datentypen | Interaktives Lab

Einführung

In diesem Lab werden wir die Grundlagen von MySQL-Tabellenstrukturen und Datentypen erkunden. Das Verständnis, wie man Tabellen erstellt, modifiziert und verwaltet, ist für jeden, der mit Datenbanken arbeitet, von entscheidender Bedeutung. Wir werden über verschiedene Datentypen in MySQL lernen, wie man den richtigen Datentyp für seine Bedürfnisse auswählt und wie man grundlegende Tabellenoperationen durchführt. Am Ende dieses Labs haben Sie praktische Erfahrungen in der Erstellung von Tabellen, der Definition von Spalten mit geeigneten Datentypen und der Modifikation von Tabellenstrukturen gesammelt.

Ziele

Durch die Absolvierung dieses Labs können Sie:

Die Kern-Datentypen von MySQL verstehen und wissen, wann man sie einsetzen sollte
Tabellen mit geeigneten Spaltendefinitionen erstellen
Bestehende Tabellenstrukturen modifizieren
Tabellen entfernen, wenn sie nicht mehr benötigt werden
Tabellenmetadaten anzeigen und verstehen

Das Verständnis von MySQL-Datentypen

In diesem Schritt werden wir die am häufigsten verwendeten MySQL-Datentypen untersuchen. Das Verständnis von Datentypen ist von entscheidender Bedeutung, da die Wahl des richtigen Datentyps für Ihre Spalten sowohl die Datenintegrität als auch die Leistung der Datenbank beeinflusst.

Da in diesem Schritt viele SQL-Befehle verwendet werden, empfehlen wir die Verwendung des Web-Terminals. Klicken Sie auf die Registerkarte "Terminal", um es zu öffnen; es funktioniert genauso wie das Desktop-Terminal.

Beginnen wir damit, uns mit MySQL zu verbinden:

sudo mysql -u root

Nachdem wir uns verbunden haben, erstellen wir eine neue Datenbank für unsere Experimente:

CREATE DATABASE store;
USE store;

Lassen Sie uns die Hauptkategorien der MySQL-Datentypen betrachten:

Numerische Typen:
- INT: Für ganze Zahlen
- DECIMAL: Für genaue Dezimalzahlen
- FLOAT/DOUBLE: Für ungefähre Dezimalzahlen
Zeichenketten-Typen:
- VARCHAR: Für Zeichenketten variabler Länge
- CHAR: Für Zeichenketten fester Länge
- TEXT: Für lange Texte
Datums- und Zeit-Typen:
- DATE: Für Datumsangaben (YYYY-MM-DD)
- TIME: Für Zeitangaben (HH:MM:SS)
- DATETIME: Für sowohl Datum als auch Zeit

Erstellen wir eine einfache Tabelle, die diese verschiedenen Datentypen demonstriert:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    weight FLOAT,
    in_stock BOOLEAN,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Lassen Sie uns diese Tabellenstruktur analysieren:

id: Eine automatisch inkrementierende Ganzzahl, die als Primärschlüssel dient
name: Eine Zeichenkette variabler Länge, die nicht NULL sein darf
price: Eine genaue Dezimalzahl mit insgesamt 10 Ziffern und 2 Dezimalstellen
description: Ein Textfeld für längere Beschreibungen
weight: Eine Gleitkommazahl für ungefähre Dezimalwerte
in_stock: Ein boolesches Feld (TRUE/FALSE)
created_at: Speichert automatisch den Erstellungszeitstempel
last_updated: Wird automatisch aktualisiert, wenn der Datensatz geändert wird

Um die Struktur unserer Tabelle anzuzeigen:

DESCRIBE products;

Sie sollten eine Ausgabe wie diese sehen:

+--------------+---------------+------+-----+---------------------+-------------------------------+
| Field        | Type          | Null | Key | Default             | Extra                         |
+--------------+---------------+------+-----+---------------------+-------------------------------+
| id           | int(11)       | NO   | PRI | NULL                | auto_increment                |
| name         | varchar(100)  | NO   |     | NULL                |                               |
| price        | decimal(10,2) | NO   |     | NULL                |                               |
| description  | text          | YES  |     | NULL                |                               |
| weight       | float         | YES  |     | NULL                |                               |
| in_stock     | tinyint(1)    | YES  |     | NULL                |                               |
| created_at   | datetime      | YES  |     | current_timestamp() |                               |
| last_updated | timestamp     | NO   |     | current_timestamp() | on update current_timestamp() |
+--------------+---------------+------+-----+---------------------+-------------------------------+
8 rows in set (0.001 sec)

Das Erstellen von Tabellen mit Einschränkungen

In diesem Schritt werden wir uns mit Tabelleneinschränkungen (Constraints) befassen und lernen, wie sie zur Aufrechterhaltung der Datenintegrität beitragen. Wir werden eine komplexere Tabellenstruktur erstellen, die verschiedene Arten von Einschränkungen zeigt.

Erstellen wir zwei miteinander verknüpfte Tabellen, um Beziehungen und Einschränkungen zu verstehen:

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200),
    active BOOLEAN DEFAULT TRUE
);

CREATE TABLE inventory_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    sku VARCHAR(20) NOT NULL UNIQUE,
    item_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL CHECK (quantity >= 0),
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Lassen Sie uns die von uns verwendeten Einschränkungen untersuchen:

PRIMARY KEY: Stellt sicher, dass jeder Datensatz eindeutig identifiziert werden kann
UNIQUE: Verhindert doppelte Werte in einer Spalte
NOT NULL: Stellt sicher, dass eine Spalte keine NULL-Werte enthalten kann
CHECK: Validiert die Daten, bevor sie eingefügt werden
FOREIGN KEY: Stellt die referentielle Integrität zwischen Tabellen sicher
DEFAULT: Liefert einen Standardwert, wenn keiner angegeben wird

Um die Tabellenstruktur mit Einschränkungen anzuzeigen:

SHOW CREATE TABLE inventory_items;

Dies zeigt die vollständige CREATE TABLE-Anweisung einschließlich aller Einschränkungen:

MariaDB [store]> SHOW CREATE TABLE inventory_items;

<!-- Sample output -->

CREATE TABLE `inventory_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `sku` varchar(20) NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `quantity` int(11) NOT NULL CHECK (`quantity` >= 0),
  `unit_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `inventory_items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

Das Ändern von Tabellenstrukturen

In diesem Schritt werden wir lernen, wie man bestehende Tabellen mit ALTER TABLE-Befehlen ändert. Dies ist eine häufige Aufgabe, wenn sich Ihre Datenanforderungen im Laufe der Zeit ändern.

Ändern wir unsere products-Tabelle, um einige neue Spalten hinzuzufügen und bestehende zu ändern:

Fügen Sie eine neue Spalte hinzu:

ALTER TABLE products
ADD COLUMN manufacturer VARCHAR(100) AFTER name;

Dieser Befehl fügt eine neue Spalte namens manufacturer hinter der name-Spalte hinzu.

Ändern Sie eine bestehende Spalte:

ALTER TABLE products
MODIFY COLUMN description VARCHAR(500) NOT NULL DEFAULT 'No description available';

Dieser Befehl ändert die description-Spalte in eine Zeichenkette variabler Länge mit einer maximalen Länge von 500 Zeichen. Er legt auch einen Standardwert von 'No description available' für neue Zeilen fest.

Benennen Sie eine Spalte um:

ALTER TABLE products
CHANGE COLUMN weight product_weight DECIMAL(8,2);

Dieser Befehl benennt die weight-Spalte in product_weight um und ändert ihren Datentyp in eine Dezimalzahl mit insgesamt 8 Ziffern und 2 Dezimalstellen.

Löschen Sie eine Spalte:

ALTER TABLE products
DROP COLUMN in_stock;

Dieser Befehl entfernt die in_stock-Spalte aus der Tabelle.

Fügen wir einen zusammengesetzten Index (composite index) hinzu, um die Abfrageleistung zu verbessern:

ALTER TABLE products
ADD INDEX idx_name_manufacturer (name, manufacturer);

Dieser Befehl erstellt einen zusammengesetzten Index auf den Spalten name und manufacturer.

Um alle vorgenommenen Änderungen anzuzeigen:

DESCRIBE products;
SHOW INDEX FROM products;

Sie sollten die aktualisierte Tabellenstruktur und die Indizes sehen:

MariaDB [store]> DESCRIBE products;
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| Field          | Type          | Null | Key | Default                  | Extra                         |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| id             | int(11)       | NO   | PRI | NULL                     | auto_increment                |
| name           | varchar(100)  | NO   | MUL | NULL                     |                               |
| manufacturer   | varchar(100)  | YES  |     | NULL                     |                               |
| price          | decimal(10,2) | NO   |     | NULL                     |                               |
| description    | varchar(500)  | NO   |     | No description available |                               |
| product_weight | decimal(8,2)  | YES  |     | NULL                     |                               |
| created_at     | datetime      | YES  |     | current_timestamp()      |                               |
| last_updated   | timestamp     | NO   |     | current_timestamp()      | on update current_timestamp() |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
8 rows in set (0.001 sec)

MariaDB [store]> SHOW INDEX FROM products;
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| products |          0 | PRIMARY               |            1 | id           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            1 | name         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            2 | manufacturer | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.000 sec)

Arbeiten mit Tabelleninformationen

In diesem Schritt werden wir verschiedene Methoden erkunden, um Informationen über unsere Tabellen und deren Struktur abzurufen.

Zunächst schauen wir uns die Statusinformationen der Tabellen an:

SHOW TABLE STATUS FROM store;

Dieser Befehl zeigt Informationen über jede Tabelle in der Datenbank an, darunter:

Speicher-Engine (Storage engine)
Zeilenformat (Row format)
Anzahl der Zeilen (Number of rows)
Durchschnittliche Zeilenlänge (Average row length)
Datengröße (Data length)
Indexgröße (Index length)

Um alle Tabellen in unserer Datenbank anzuzeigen:

SHOW TABLES;

Um detaillierte Informationen über eine bestimmte Spalte anzuzeigen:

SHOW FULL COLUMNS FROM products;

Dies liefert zusätzliche Informationen über jede Spalte, darunter:

Spaltentyp (Column type)
Sortierreihenfolge (Collation)
Berechtigungen (Privileges)
Kommentare (Comments)

Um alle Indizes einer Tabelle anzuzeigen:

SHOW INDEX FROM products;

Wir können auch Informationen über unsere Tabellen aus der INFORMATION_SCHEMA-Datenbank abrufen:

SELECT
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    INDEX_LENGTH
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'store';

Das Löschen von Tabellen und die Aufräumarbeiten

In diesem letzten Schritt werden wir lernen, wie man Tabellen sicher entfernt, wenn sie nicht mehr benötigt werden. Dies ist eine wichtige Fähigkeit, aber sie sollte mit Vorsicht angewendet werden, da das Löschen einer Tabelle alle ihre Daten dauerhaft entfernt.

Bevor Sie eine Tabelle löschen, ist es eine gute Praxis:

Zu überprüfen, ob Sie sich in der richtigen Datenbank befinden
Zu prüfen, ob die Tabelle existiert
Sicherzustellen, dass Sie bei Bedarf eine Sicherungskopie haben

Beginnen wir damit, unsere aktuelle Datenbank und die Tabellen zu überprüfen:

SELECT DATABASE();
SHOW TABLES;

Um eine Tabelle sicher zu löschen, können wir die IF EXISTS-Klausel verwenden:

DROP TABLE IF EXISTS inventory_items;

Beachten Sie, dass wir zuerst Tabellen mit Fremdschlüssel-Einschränkungen (foreign key constraints) löschen müssen. Wenn wir versuchen würden, zuerst die categories-Tabelle zu löschen, würden wir einen Fehler erhalten, da die inventory_items-Tabelle darauf verweist.

Jetzt können wir die verbleibenden Tabellen löschen:

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;

Um zu überprüfen, ob die Tabellen gelöscht wurden:

SHOW TABLES;

Sie sollten eine leere Ergebnismenge sehen, was darauf hinweist, dass alle Tabellen gelöscht wurden.

Zusammenfassung

In diesem Lab haben wir die wesentlichen Aspekte der Arbeit mit MySQL-Tabellen und Datentypen behandelt:

Das Verstehen und Verwenden verschiedener MySQL-Datentypen
Das Erstellen von Tabellen mit geeigneten Einschränkungen (Constraints)
Das Ändern von Tabellenstrukturen mit ALTER TABLE
Das Abrufen von Tabellenmetadaten und -informationen
Das sichere Löschen von Tabellen, wenn sie nicht mehr benötigt werden

Diese Fähigkeiten bilden die Grundlage für die effektive Arbeit mit MySQL-Datenbanken.

MySQL-Tabellenstruktur und Datentypen