Sql

Grundlegende Befehle Datenbankverbindung # Verbindung zur MySQL-Konsole mysql -u username -p # Verbindung zu spezifischer Datenbank mysql -u username -p database_name # Verbindung zu Remote-Server mysql -h hostname -u username -p database_name Datenbank-Operationen -- Alle Datenbanken anzeigen SHOW DATABASES; /* +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | my_database | | test_db | +--------------------+ */ -- Datenbank erstellen CREATE DATABASE database_name; CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Query OK, 1 row affected (0.01 sec) -- Datenbank verwenden USE database_name; -- Database changed -- Aktuelle Datenbank anzeigen SELECT DATABASE(); /* +-------------+ | DATABASE() | +-------------+ | my_database | +-------------+ */ -- Datenbank löschen DROP DATABASE database_name; -- Query OK, 0 rows affected (0.05 sec) Tabellen-Management Tabellen erstellen -- Grundlegende Tabellenerstellung CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- Tabelle mit Fremdschlüssel CREATE TABLE posts ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT, title VARCHAR(200) NOT NULL, content TEXT, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ); Tabellen-Informationen -- Alle Tabellen anzeigen SHOW TABLES; /* +---------------------+ | Tables_in_my_database | +---------------------+ | users | | posts | | categories | +---------------------+ */ -- Tabellenstruktur beschreiben DESCRIBE table_name; DESC table_name; /* +------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+-------------------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | username | varchar(50) | NO | UNI | NULL | | | email | varchar(100) | NO | | NULL | | | created_at | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | +------------+--------------+------+-----+-------------------+----------------+ */ -- Detaillierte Tabelleninformationen SHOW CREATE TABLE table_name; -- Spalten einer Tabelle anzeigen SHOW COLUMNS FROM table_name; Tabellen ändern -- Spalte hinzufügen ALTER TABLE table_name ADD COLUMN column_name datatype; -- Spalte ändern ALTER TABLE table_name MODIFY COLUMN column_name new_datatype; ALTER TABLE table_name CHANGE old_column_name new_column_name datatype; -- Spalte löschen ALTER TABLE table_name DROP COLUMN column_name; -- Index hinzufügen ALTER TABLE table_name ADD INDEX idx_column_name (column_name); -- Tabelle umbenennen RENAME TABLE old_name TO new_name; -- Tabelle löschen DROP TABLE table_name; CRUD-Operationen CREATE (Einfügen) -- Einzelnen Datensatz einfügen INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]'); -- Mehrere Datensätze einfügen INSERT INTO users (username, email) VALUES ('user1', '[email protected]'), ('user2', '[email protected]'), ('user3', '[email protected]'); -- Mit ON DUPLICATE KEY UPDATE (seit MySQL 4.1) INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]') ON DUPLICATE KEY UPDATE email = VALUES(email); READ (Abfragen) -- Alle Datensätze auswählen SELECT * FROM users; /* +----+----------+-------------------+---------------------+ | id | username | email | created_at | +----+----------+-------------------+---------------------+ | 1 | john_doe | [email protected] | 2024-01-15 10:30:00 | | 2 | jane_doe | [email protected] | 2024-01-16 14:22:00 | | 3 | bob_user | [email protected] | 2024-01-17 09:15:00 | +----+----------+-------------------+---------------------+ */ -- Spezifische Spalten auswählen SELECT username, email FROM users; /* +----------+-------------------+ | username | email | +----------+-------------------+ | john_doe | [email protected] | | jane_doe | [email protected] | | bob_user | [email protected] | +----------+-------------------+ */ -- Mit WHERE-Bedingung SELECT * FROM users WHERE id = 1; SELECT * FROM users WHERE username LIKE 'john%'; -- Mit ORDER BY SELECT * FROM users ORDER BY created_at DESC; SELECT * FROM users ORDER BY username ASC, created_at DESC; -- Mit LIMIT SELECT * FROM users LIMIT 10; SELECT * FROM users LIMIT 10 OFFSET 20; -- Mit COUNT, SUM, AVG, MAX, MIN SELECT COUNT(*) FROM users; /* +----------+ | COUNT(*) | +----------+ | 3 | +----------+ */ SELECT AVG(age) FROM users; SELECT MAX(created_at) FROM users; /* +---------------------+ | MAX(created_at) | +---------------------+ | 2024-01-17 09:15:00 | +---------------------+ */ UPDATE (Aktualisieren) -- Einzelnen Datensatz aktualisieren UPDATE users SET email = '[email protected]' WHERE id = 1; -- Mehrere Spalten aktualisieren UPDATE users SET username = 'new_username', email = '[email protected]' WHERE id = 1; -- Mit Bedingungen UPDATE users SET status = 'active' WHERE created_at > '2024-01-01'; DELETE (Löschen) -- Spezifische Datensätze löschen DELETE FROM users WHERE id = 1; DELETE FROM users WHERE created_at < '2024-01-01'; -- Alle Datensätze löschen (Struktur bleibt) DELETE FROM users; -- Tabelle leeren (schneller als DELETE) TRUNCATE TABLE users; Datenbank-Relationen verstehen 1:1 Beziehung (One-to-One) Konzept: Ein Datensatz in Tabelle A gehört zu genau einem Datensatz in Tabelle B und umgekehrt. ...