tech
Defaultdeesfrzh

MySQL

Populare Open-Source relationale Datenbank - ACID-Transaktionen, Replikation, breit unterstutzt

TL;DR

Was: Die weltweit beliebteste relationale Open-Source-Datenbank.

Warum: Zuverlässig, schnell, weit verbreitet, ideal für Webanwendungen.

Quick Start

Installation:

macOS:

brew install mysql
brew services start mysql

Linux (Ubuntu):

sudo apt install mysql-server
sudo systemctl start mysql

Docker:

docker run --name mysql -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 -d mysql:8

Verbinden:

mysql -u root -p

Datenbank und Tabelle erstellen:

CREATE DATABASE myapp;
USE myapp;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Cheatsheet

BefehlBeschreibung
SHOW DATABASES;Datenbanken auflisten
USE dbname;Datenbank auswählen
SHOW TABLES;Tabellen auflisten
DESCRIBE table;Tabellenstruktur anzeigen
SELECT * FROM table;Alle Zeilen abfragen
INSERT INTO table (...) VALUES (...);Zeile einfügen
UPDATE table SET ... WHERE ...;Zeilen aktualisieren
DELETE FROM table WHERE ...;Zeilen löschen

Gotchas

Basic CRUD

-- Insert
INSERT INTO users (name, email) VALUES ('John', '[email protected]');

-- Select
SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT name, email FROM users ORDER BY created_at DESC LIMIT 10;

-- Update
UPDATE users SET name = 'Jane' WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 1;

Joins

SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE orders.total > 100;

Indexes

-- Create index for faster queries
CREATE INDEX idx_email ON users(email);

-- Show indexes
SHOW INDEX FROM users;

User management

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

Next Steps