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.

Beispiel: Benutzer und Benutzerprofil

-- Haupttabelle
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- 1:1 Beziehung - Benutzerprofil
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE, -- UNIQUE macht die 1:1 Beziehung aus
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    address TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Abfrage mit 1:1 JOIN
SELECT u.username, p.first_name, p.last_name, p.phone
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id;
/*
+----------+------------+-----------+-------------+
| username | first_name | last_name | phone       |
+----------+------------+-----------+-------------+
| john_doe | John       | Doe       | 555-0123    |
| jane_doe | Jane       | Doe       | 555-0124    |
+----------+------------+-----------+-------------+
*/

1:n Beziehung (One-to-Many)

Konzept: Ein Datensatz in Tabelle A kann mehrere Datensätze in Tabelle B haben, aber jeder Datensatz in B gehört nur zu einem in A.

Beispiel: Benutzer und Blog-Posts

-- "Ein" Benutzer (1)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- "Viele" Posts (n) 
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT, -- Fremdschlüssel ohne UNIQUE
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Abfrage: Alle Posts eines Benutzers
SELECT u.username, p.title, p.created_at
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.username = 'john_doe';
/*
+----------+------------------+---------------------+
| username | title            | created_at          |
+----------+------------------+---------------------+
| john_doe | Mein erster Post | 2024-01-15 10:30:00 |
| john_doe | MySQL Tutorial   | 2024-01-16 14:00:00 |
| john_doe | PHP Grundlagen   | 2024-01-17 09:15:00 |
+----------+------------------+---------------------+
*/

-- Anzahl Posts pro Benutzer
SELECT u.username, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;
/*
+----------+------------+
| username | post_count |
+----------+------------+
| john_doe |          3 |
| jane_doe |          1 |
| bob_user |          0 |
+----------+------------+
*/

n:m Beziehung (Many-to-Many)

Konzept: Mehrere Datensätze in Tabelle A können mehrere Datensätze in Tabelle B zugeordnet sein. Benötigt eine Zwischentabelle.

Beispiel: Posts und Tags

-- Erste Haupttabelle
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT
);

-- Zweite Haupttabelle  
CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- Zwischentabelle für n:m Beziehung
CREATE TABLE post_tags (
    post_id INT,
    tag_id INT,
    PRIMARY KEY (post_id, tag_id), -- Composite Primary Key
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

-- Daten einfügen
INSERT INTO posts (title, content) VALUES 
('MySQL Tutorial', 'Grundlagen zu MySQL...'),
('PHP Grundlagen', 'Einführung in PHP...');

INSERT INTO tags (name) VALUES ('mysql'), ('php'), ('tutorial'), ('database');

INSERT INTO post_tags (post_id, tag_id) VALUES 
(1, 1), -- MySQL Tutorial hat Tag "mysql"
(1, 3), -- MySQL Tutorial hat Tag "tutorial" 
(1, 4), -- MySQL Tutorial hat Tag "database"
(2, 2), -- PHP Grundlagen hat Tag "php"
(2, 3); -- PHP Grundlagen hat Tag "tutorial"

-- Abfrage: Alle Tags zu einem Post
SELECT p.title, t.name as tag_name
FROM posts p
JOIN post_tags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE p.id = 1;
/*
+---------------+----------+
| title         | tag_name |
+---------------+----------+
| MySQL Tutorial| mysql    |
| MySQL Tutorial| tutorial |
| MySQL Tutorial| database |
+---------------+----------+
*/

-- Abfrage: Alle Posts zu einem Tag
SELECT t.name as tag_name, p.title
FROM tags t
JOIN post_tags pt ON t.id = pt.tag_id
JOIN posts p ON pt.post_id = p.id
WHERE t.name = 'tutorial';
/*
+----------+----------------+
| tag_name | title          |
+----------+----------------+
| tutorial | MySQL Tutorial |
| tutorial | PHP Grundlagen |
+----------+----------------+
*/

-- Anzahl Posts pro Tag
SELECT t.name, COUNT(pt.post_id) as post_count
FROM tags t
LEFT JOIN post_tags pt ON t.id = pt.tag_id
GROUP BY t.id, t.name;
/*
+----------+------------+
| name     | post_count |
+----------+------------+
| mysql    |          1 |
| php      |          1 |
| tutorial |          2 |
| database |          1 |
+----------+------------+
*/

Erweiterte Relationen

Selbstreferenzierende Beziehung

Beispiel: Mitarbeiter-Hierarchie (Manager-Untergebene)

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    manager_id INT NULL, -- Verweis auf anderen Mitarbeiter
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- Abfrage: Mitarbeiter mit ihren Managern
SELECT 
    e.name as employee_name,
    m.name as manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
/*
+---------------+--------------+
| employee_name | manager_name |
+---------------+--------------+
| John Smith    | NULL         |
| Jane Doe      | John Smith   |
| Bob Wilson    | John Smith   |
| Alice Brown   | Jane Doe     |
+---------------+--------------+
*/

Polymorphe Beziehung

Beispiel: Kommentare zu verschiedenen Inhaltstypen

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT NOT NULL,
    commentable_type VARCHAR(50) NOT NULL, -- 'post', 'product', etc.
    commentable_id INT NOT NULL,          -- ID des kommentierten Objects
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_commentable (commentable_type, commentable_id)
);

-- Alle Kommentare zu Posts
SELECT * FROM comments WHERE commentable_type = 'post' AND commentable_id = 1;

JOIN-Operationen

Verschiedene JOIN-Typen

-- INNER JOIN - Nur übereinstimmende Datensätze
SELECT u.username, p.title 
FROM users u 
INNER JOIN posts p ON u.id = p.user_id;
/*
+----------+------------------+
| username | title            |
+----------+------------------+
| john_doe | MySQL Tutorial   |
| john_doe | PHP Grundlagen   |
| jane_doe | React Einführung |
+----------+------------------+
*/

-- LEFT JOIN - Alle aus linker Tabelle, auch ohne Match
SELECT u.username, p.title 
FROM users u 
LEFT JOIN posts p ON u.id = p.user_id;
/*
+----------+------------------+
| username | title            |
+----------+------------------+
| john_doe | MySQL Tutorial   |
| john_doe | PHP Grundlagen   |
| jane_doe | React Einführung |
| bob_user | NULL             |  -- Benutzer ohne Posts
+----------+------------------+
*/

-- RIGHT JOIN - Alle aus rechter Tabelle
SELECT u.username, p.title 
FROM users u 
RIGHT JOIN posts p ON u.id = p.user_id;

-- FULL OUTER JOIN (simuliert, da MySQL es nicht nativ unterstützt)
SELECT u.username, p.title 
FROM users u 
LEFT JOIN posts p ON u.id = p.user_id
UNION
SELECT u.username, p.title 
FROM users u 
RIGHT JOIN posts p ON u.id = p.user_id;

Erweiterte Abfragen

Gruppierung und Aggregation

-- GROUP BY mit HAVING
SELECT user_id, COUNT(*) as post_count 
FROM posts 
GROUP BY user_id 
HAVING COUNT(*) > 5;

-- Mit mehreren Aggregatfunktionen
SELECT 
    user_id, 
    COUNT(*) as total_posts,
    MAX(created_at) as latest_post,
    MIN(created_at) as first_post
FROM posts 
GROUP BY user_id;

Subqueries (Unterabfragen)

-- WHERE Subquery
SELECT * FROM users 
WHERE id IN (SELECT DISTINCT user_id FROM posts);

-- EXISTS Subquery
SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);

-- Correlated Subquery
SELECT u.*, 
    (SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) as post_count
FROM users u;

Indizes und Performance

Index-Management

-- Index erstellen
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email_created ON users(email, created_at);

-- Unique Index
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Volltext-Index
CREATE FULLTEXT INDEX idx_content_fulltext ON posts(content);

-- Index anzeigen
SHOW INDEX FROM users;

-- Index löschen
DROP INDEX idx_username ON users;

Performance-Analyse

-- Query-Ausführungsplan anzeigen
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE username = 'john_doe';

-- Performance Schema verwenden (MySQL 5.5+)
SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY sum_timer_wait DESC LIMIT 10;

Moderne MySQL-Features

Common Table Expressions (CTEs) - MySQL 8.0+

-- Einfache CTE
WITH user_stats AS (
    SELECT user_id, COUNT(*) as post_count
    FROM posts
    GROUP BY user_id
)
SELECT u.username, us.post_count
FROM users u
JOIN user_stats us ON u.id = us.user_id;

-- Rekursive CTE
WITH RECURSIVE number_series AS (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1 FROM number_series WHERE n < 10
)
SELECT * FROM number_series;

Window Functions - MySQL 8.0+

-- ROW_NUMBER()
SELECT 
    username,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) as row_num
FROM users;

-- RANK() und DENSE_RANK()
SELECT 
    username,
    score,
    RANK() OVER (ORDER BY score DESC) as rank_pos,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_pos
FROM users;

-- PARTITION BY
SELECT 
    username,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM employees;

-- LAG() und LEAD()
SELECT 
    date,
    sales,
    LAG(sales, 1) OVER (ORDER BY date) as previous_sales,
    LEAD(sales, 1) OVER (ORDER BY date) as next_sales
FROM daily_sales;

JSON-Unterstützung - MySQL 5.7+

-- JSON-Spalte erstellen
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

-- JSON-Daten einfügen
INSERT INTO products (name, attributes) VALUES 
('Laptop', '{"brand": "Dell", "ram": "16GB", "storage": "512GB SSD"}');

-- JSON-Daten abfragen
SELECT name, JSON_EXTRACT(attributes, '$.brand') as brand FROM products;
SELECT name, attributes->>'$.brand' as brand FROM products; -- MySQL 5.7.13+

-- JSON-Funktionen
SELECT 
    name,
    JSON_KEYS(attributes) as available_keys,
    JSON_LENGTH(attributes) as attr_count
FROM products;

-- JSON-Index erstellen
ALTER TABLE products ADD INDEX idx_brand ((CAST(attributes->>'$.brand' AS CHAR(50))));

Generated Columns - MySQL 5.7+

-- Virtual Generated Column
ALTER TABLE users ADD COLUMN full_name VARCHAR(100) 
GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL;

-- Stored Generated Column
ALTER TABLE orders ADD COLUMN total_price DECIMAL(10,2) 
GENERATED ALWAYS AS (quantity * unit_price) STORED;

CHECK Constraints - MySQL 8.0.16+

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    category ENUM('electronics', 'clothing', 'books') NOT NULL,
    stock_quantity INT CHECK (stock_quantity >= 0)
);

Multi-Value Indexes - MySQL 8.0.17+

-- Für JSON-Arrays
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    tags JSON,
    INDEX idx_tags ((CAST(tags->'$[*]' AS CHAR(50) ARRAY)))
);

Transaktionen und Locks

Transaktions-Management

-- Transaktion starten
START TRANSACTION;
-- oder
BEGIN;

-- Änderungen bestätigen
COMMIT;

-- Änderungen rückgängig machen
ROLLBACK;

-- Savepoint verwenden
START TRANSACTION;
INSERT INTO users (username, email) VALUES ('user1', '[email protected]');
SAVEPOINT sp1;
INSERT INTO users (username, email) VALUES ('user2', '[email protected]');
ROLLBACK TO sp1;
COMMIT;

Isolationslevel

-- Aktuelles Isolationslevel anzeigen
SELECT @@transaction_isolation;

-- Isolationslevel setzen
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Benutzer- und Rechteverwaltung

Benutzer-Management

-- Benutzer erstellen
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username'@'%' IDENTIFIED BY 'password'; -- Von überall

-- Passwort ändern
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

-- Benutzer löschen
DROP USER 'username'@'localhost';

-- Alle Benutzer anzeigen
SELECT User, Host FROM mysql.user;

Rechtevergabe

-- Alle Rechte auf Datenbank vergeben
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

-- Spezifische Rechte vergeben
GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'username'@'localhost';

-- Rechte entziehen
REVOKE INSERT ON database_name.table_name FROM 'username'@'localhost';

-- Rechte anzeigen
SHOW GRANTS FOR 'username'@'localhost';

-- Rechte-Änderungen aktivieren
FLUSH PRIVILEGES;

Backup und Recovery

Datenbank-Backup

# Vollständiges Backup
mysqldump -u username -p database_name > backup.sql

# Mehrere Datenbanken
mysqldump -u username -p --databases db1 db2 > backup.sql

# Alle Datenbanken
mysqldump -u username -p --all-databases > backup.sql

# Nur Struktur (ohne Daten)
mysqldump -u username -p --no-data database_name > structure.sql

# Nur Daten (ohne Struktur)
mysqldump -u username -p --no-create-info database_name > data.sql

Datenbank wiederherstellen

# Backup einspielen
mysql -u username -p database_name < backup.sql

# Neue Datenbank erstellen und Backup einspielen
mysql -u username -p -e "CREATE DATABASE new_database;"
mysql -u username -p new_database < backup.sql

Nützliche Befehle und Funktionen

System-Informationen

-- MySQL-Version anzeigen
SELECT VERSION();
/*
+-----------+
| VERSION() |
+-----------+
| 8.0.35    |
+-----------+
*/

-- Aktuelle Zeit/Datum
SELECT NOW(), CURDATE(), CURTIME();
/*
+---------------------+------------+-----------+
| NOW()               | CURDATE()  | CURTIME() |
+---------------------+------------+-----------+
| 2024-05-30 14:30:25 | 2024-05-30 | 14:30:25  |
+---------------------+------------+-----------+
*/

-- Systemvariablen anzeigen
SHOW VARIABLES LIKE 'max_connections';
/*
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
*/

SHOW STATUS LIKE 'Threads_connected';
/*
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 3     |
+-------------------+-------+
*/

-- Aktive Prozesse anzeigen
SHOW PROCESSLIST;
/*
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  5 | root | localhost | test | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+----------+------------------+
*/

-- Engine-Informationen
SHOW ENGINES;

String-Funktionen

-- Häufig verwendete String-Funktionen
SELECT 
    CONCAT('Hello', ' ', 'World') as concatenated,
    UPPER('hello') as uppercase,
    LOWER('HELLO') as lowercase,
    LENGTH('Hello World') as string_length,
    SUBSTRING('Hello World', 1, 5) as substring_result,
    REPLACE('Hello World', 'World', 'MySQL') as replaced;
/*
+-------------+-----------+-----------+---------------+------------------+-------------+
| concatenated| uppercase | lowercase | string_length | substring_result | replaced    |
+-------------+-----------+-----------+---------------+------------------+-------------+
| Hello World | HELLO     | hello     |            11 | Hello            | Hello MySQL |
+-------------+-----------+-----------+---------------+------------------+-------------+
*/

Datum- und Zeit-Funktionen

-- Datum-Funktionen
SELECT 
    DATE_ADD(NOW(), INTERVAL 30 DAY) as thirty_days_later,
    DATE_SUB(NOW(), INTERVAL 1 MONTH) as one_month_ago,
    DATEDIFF('2024-12-31', '2024-01-01') as days_difference,
    DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') as formatted_date;

Mathematische Funktionen

-- Math-Funktionen
SELECT 
    ROUND(3.14159, 2) as rounded,
    CEIL(3.2) as ceiling,
    FLOOR(3.8) as floor,
    ABS(-5) as absolute_value,
    RAND() as random_number;

Performance-Tipps

  1. Indizes richtig verwenden: Erstelle Indizes für häufig abgefragte Spalten
  2. LIMIT verwenden: Bei großen Datensätzen immer LIMIT einsetzen
  3. SELECT * vermeiden: Nur benötigte Spalten abfragen
  4. WHERE vor JOIN: WHERE-Bedingungen vor JOIN-Operationen anwenden
  5. EXPLAIN verwenden: Query-Pläne analysieren
  6. Batch-Operationen: Mehrere INSERTs in einer Anweisung
  7. Connection Pooling: Datenbankverbindungen wiederverwenden
  8. Regular Maintenance: OPTIMIZE TABLE und ANALYZE TABLE regelmäßig ausführen

Häufige Datentypen

Numerische Typen

  • TINYINT: -128 bis 127
  • SMALLINT: -32,768 bis 32,767
  • MEDIUMINT: -8,388,608 bis 8,388,607
  • INT: -2,147,483,648 bis 2,147,483,647
  • BIGINT: Sehr große Ganzzahlen
  • DECIMAL(M,D): Exakte Dezimalzahlen
  • FLOAT: Gleitkommazahlen
  • DOUBLE: Doppelt genaue Gleitkommazahlen

String-Typen

  • CHAR(M): Feste Länge (0-255)
  • VARCHAR(M): Variable Länge (0-65,535)
  • TEXT: Lange Texte (bis 65,535 Zeichen)
  • MEDIUMTEXT: Mittlere Texte (bis 16,777,215 Zeichen)
  • LONGTEXT: Sehr lange Texte (bis 4,294,967,295 Zeichen)

Datum- und Zeit-Typen

  • DATE: YYYY-MM-DD
  • TIME: HH:MM:SS
  • DATETIME: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP: Automatische Zeitstempel
  • YEAR: Jahr in 2 oder 4 Stellen

Moderne Typen (MySQL 5.7+)

  • JSON: JSON-Dokumente
  • GEOMETRY: Räumliche Daten
  • GENERATED: Berechnete Spalten