MySQL Cheatsheet

MySQL - The World's Most Popular Open Source Database

MySQL ist ein weit verbreitetes relationales Datenbankmanagementsystem (RDBMS), das Structured Query Language (SQL) verwendet. Es ist bekannt für seine Zuverlässigkeit, Leistung und einfache Bedienung, so dass es eine beliebte Wahl für Web-Anwendungen und Unternehmenslösungen. < p>

generieren

Inhaltsverzeichnis

[Installation](LINK_0 -%20(LINK_0)
[Datenbankbetrieb](LINK_0 -%20[Tabellen](_LINK_0 -%20Datentypen
[CRUD Operationen](_LINK_0 -%20[Indexes](LINK_0 -%20Beschränkungen
(Joins)(LINK_0)
Funktionen
Gespeicherte Verfahren
Trigger
Ansichten
[Benutzerverwaltung](_LINK_0__ -%20(__LINK_0___)
(__LINK_0___)
[Replikation](LINK_0 -%20[Sicherheit](LINK_0___ -%20(Monitoring)(_LINK_0)
Beste Praktiken

Installation

Ubuntu/Debian

# Update package index
sudo apt update

# Install MySQL Server
sudo apt install mysql-server

# Secure MySQL installation
sudo mysql_secure_installation

# Start MySQL service
sudo systemctl start mysql

# Enable MySQL to start on boot
sudo systemctl enable mysql

# Check MySQL status
sudo systemctl status mysql
```_

### CentOS/RHEL/Fedora
```bash
# Install MySQL repository
sudo dnf install mysql-server

# Start and enable MySQL
sudo systemctl start mysqld
sudo systemctl enable mysqld

# Get temporary root password
sudo grep 'temporary password' /var/log/mysqld.log

# Secure installation
sudo mysql_secure_installation
```_

### macOS
```bash
# Using Homebrew
brew install mysql

# Start MySQL
brew services start mysql

# Connect to MySQL
mysql -u root -p

# Using MySQL installer
# Download from https://dev.mysql.com/downloads/mysql/
```_

### Windows
```bash
# Download MySQL Installer from https://dev.mysql.com/downloads/installer/
# Run the installer and follow the setup wizard

# Start MySQL from Services or Command Prompt
net start mysql

# Connect to MySQL
mysql -u root -p
```_

### Docker
```bash
# Pull MySQL image
docker pull mysql:8.0

# Run MySQL container
docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=mypassword -p 3306:3306 -d mysql:8.0

# Connect to MySQL in container
docker exec -it mysql-container mysql -u root -p

# Run with persistent data
docker run --name mysql-container \
  -e MYSQL_ROOT_PASSWORD=mypassword \
  -p 3306:3306 \
  -v mysql-data:/var/lib/mysql \
  -d mysql:8.0
```_

## Grundlegende Befehle

### Verbindung mit MySQL
```sql
-- Connect to MySQL server
mysql -u username -p

-- Connect to specific database
mysql -u username -p database_name

-- Connect to remote server
mysql -h hostname -u username -p

-- Connect with specific port
mysql -h hostname -P 3306 -u username -p

-- Connect and execute command
mysql -u username -p -e "SHOW DATABASES;"
```_

### Grundlegende Informationen
```sql
-- Show MySQL version
SELECT VERSION();

-- Show current user
SELECT USER();

-- Show current database
SELECT DATABASE();

-- Show current date and time
SELECT NOW();

-- Show system variables
SHOW VARIABLES;

-- Show specific variable
SHOW VARIABLES LIKE 'version';

-- Show status
SHOW STATUS;

-- Show processes
SHOW PROCESSLIST;
```_

### Hilfe und Dokumentation
```sql
-- Get help
HELP;

-- Help for specific topic
HELP 'SELECT';

-- Show available help categories
HELP 'contents';

-- Describe table structure
DESCRIBE table_name;
DESC table_name;

-- Show table creation statement
SHOW CREATE TABLE table_name;
```_

## Datenbanken

### Datenbanken erstellen
```sql
-- Create database
CREATE DATABASE database_name;

-- Create database with character set
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS database_name;

-- Create database with specific options
CREATE DATABASE database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
ENCRYPTION = 'Y';
```_

### Datenbanken verwalten
```sql
-- Show all databases
SHOW DATABASES;

-- Use database
USE database_name;

-- Show current database
SELECT DATABASE();

-- Show database creation statement
SHOW CREATE DATABASE database_name;

-- Alter database
ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Drop database
DROP DATABASE database_name;

-- Drop database if exists
DROP DATABASE IF EXISTS database_name;
```_

### Datenbank Information
```sql
-- Show database size
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;

-- Show tables in database
SHOW TABLES;

-- Show tables with pattern
SHOW TABLES LIKE 'user%';

-- Show table status
SHOW TABLE STATUS;

-- Show table count
SELECT COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema = 'database_name';
```_

## Tabelle Operationen

### Tabellen erstellen
```sql
-- Basic table creation
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with foreign key
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Table with multiple constraints
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    category_id INT,
    stock_quantity INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Temporary table
CREATE TEMPORARY TABLE temp_data (
    id INT,
    value VARCHAR(100)
);
```_

### Alterungstabellen
```sql
-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add column with position
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50) AFTER first_name;

-- Modify column
ALTER TABLE users MODIFY COLUMN email VARCHAR(150) NOT NULL;

-- Change column name and type
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(60) NOT NULL;

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Add primary key
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- Drop primary key
ALTER TABLE table_name DROP PRIMARY KEY;

-- Add foreign key
ALTER TABLE posts ADD CONSTRAINT fk_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);

-- Drop foreign key
ALTER TABLE posts DROP FOREIGN KEY fk_user_id;

-- Add index
ALTER TABLE users ADD INDEX idx_email (email);

-- Drop index
ALTER TABLE users DROP INDEX idx_email;

-- Rename table
ALTER TABLE old_table_name RENAME TO new_table_name;
```_

### Angaben zur Tabelle
```sql
-- Describe table structure
DESCRIBE users;
DESC users;

-- Show table creation statement
SHOW CREATE TABLE users;

-- Show table columns
SHOW COLUMNS FROM users;

-- Show table indexes
SHOW INDEXES FROM users;

-- Show table status
SHOW TABLE STATUS LIKE 'users';

-- Get table size
SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'database_name'
AND table_name = 'users';
```_

### Tabellen fallen
```sql
-- Drop table
DROP TABLE table_name;

-- Drop table if exists
DROP TABLE IF EXISTS table_name;

-- Drop multiple tables
DROP TABLE table1, table2, table3;

-- Truncate table (delete all data, keep structure)
TRUNCATE TABLE table_name;
```_

## Datentypen

### Numerische Typen
```sql
-- Integer types
TINYINT     -- 1 byte, -128 to 127
SMALLINT    -- 2 bytes, -32,768 to 32,767
MEDIUMINT   -- 3 bytes, -8,388,608 to 8,388,607
INT         -- 4 bytes, -2,147,483,648 to 2,147,483,647
BIGINT      -- 8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

-- Unsigned integers
INT UNSIGNED        -- 0 to 4,294,967,295
BIGINT UNSIGNED     -- 0 to 18,446,744,073,709,551,615

-- Decimal types
DECIMAL(10,2)       -- Fixed-point number
NUMERIC(10,2)       -- Synonym for DECIMAL
FLOAT               -- Single-precision floating-point
DOUBLE              -- Double-precision floating-point

-- Boolean
BOOLEAN             -- Synonym for TINYINT(1)
BOOL                -- Synonym for BOOLEAN
```_

### Streicharten
```sql
-- Fixed-length strings
CHAR(10)            -- Fixed length, 0-255 characters

-- Variable-length strings
VARCHAR(255)        -- Variable length, 0-65,535 characters

-- Text types
TINYTEXT            -- 0-255 characters
TEXT                -- 0-65,535 characters
MEDIUMTEXT          -- 0-16,777,215 characters
LONGTEXT            -- 0-4,294,967,295 characters

-- Binary types
BINARY(10)          -- Fixed-length binary
VARBINARY(255)      -- Variable-length binary
TINYBLOB            -- 0-255 bytes
BLOB                -- 0-65,535 bytes
MEDIUMBLOB          -- 0-16,777,215 bytes
LONGBLOB            -- 0-4,294,967,295 bytes
```_

### Datum und Uhrzeit
```sql
-- Date and time types
DATE                -- YYYY-MM-DD
TIME                -- HH:MM:SS
DATETIME            -- YYYY-MM-DD HH:MM:SS
TIMESTAMP           -- YYYY-MM-DD HH:MM:SS (with timezone)
YEAR                -- YYYY

-- Examples
CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_date DATE,
    event_time TIME,
    event_datetime DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```_

### JSON Typ
```sql
-- JSON column
CREATE TABLE user_preferences (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    preferences JSON,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert JSON data
INSERT INTO user_preferences (user_id, preferences) VALUES
(1, '{"theme": "dark", "language": "en", "notifications": true}');

-- Query JSON data
SELECT preferences->'$.theme' AS theme FROM user_preferences WHERE user_id = 1;
SELECT JSON_EXTRACT(preferences, '$.language') AS language FROM user_preferences;
```_

## AUSRÜSTUNG Operationen

### INSERT Operationen
```sql
-- Basic insert
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- Insert multiple rows
INSERT INTO users (username, email, password) VALUES
('alice', 'alice@example.com', 'password1'),
('bob', 'bob@example.com', 'password2'),
('charlie', 'charlie@example.com', 'password3');

-- Insert with all columns
INSERT INTO users VALUES 
(NULL, 'dave', 'dave@example.com', 'password4', NOW());

-- Insert from another table
INSERT INTO users_backup (username, email)
SELECT username, email FROM users WHERE created_at < '2023-01-01';

-- Insert or update (ON DUPLICATE KEY UPDATE)
INSERT INTO users (id, username, email) VALUES (1, 'john_doe', 'newemail@example.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

-- Insert ignore (skip duplicates)
INSERT IGNORE INTO users (username, email, password) 
VALUES ('existing_user', 'existing@example.com', 'password');
```_

### ELEKTRIZ Operationen
```sql
-- Basic select
SELECT * FROM users;

-- Select specific columns
SELECT username, email FROM users;

-- Select with alias
SELECT username AS user_name, email AS user_email FROM users;

-- Select with WHERE clause
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE username = 'john_doe';
SELECT * FROM users WHERE created_at > '2023-01-01';

-- Select with multiple conditions
SELECT * FROM users WHERE username = 'john_doe' AND email = 'john@example.com';
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE email IS NOT NULL;

-- Select with ORDER BY
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY username ASC, created_at DESC;

-- Select with LIMIT
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT * FROM users ORDER BY id LIMIT 5;

-- Select with GROUP BY
SELECT COUNT(*) as user_count FROM users;
SELECT DATE(created_at) as date, COUNT(*) as daily_users 
FROM users GROUP BY DATE(created_at);

-- Select with HAVING
SELECT DATE(created_at) as date, COUNT(*) as daily_users 
FROM users 
GROUP BY DATE(created_at) 
HAVING COUNT(*) > 5;
```_

### UPDATE Operationen
```sql
-- Basic update
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Update multiple columns
UPDATE users SET 
    email = 'newemail@example.com',
    password = 'new_hashed_password'
WHERE id = 1;

-- Update with conditions
UPDATE users SET password = 'new_password' WHERE username = 'john_doe';

-- Update multiple rows
UPDATE users SET is_active = FALSE WHERE created_at < '2022-01-01';

-- Update with JOIN
UPDATE users u
JOIN user_profiles p ON u.id = p.user_id
SET u.email = p.contact_email
WHERE p.is_primary = TRUE;

-- Update with subquery
UPDATE users SET status = 'premium'
WHERE id IN (SELECT user_id FROM subscriptions WHERE plan = 'premium');

-- Update with CASE
UPDATE users SET 
    status = CASE 
        WHEN created_at > '2023-01-01' THEN 'new'
        WHEN created_at > '2022-01-01' THEN 'regular'
        ELSE 'old'
    END;
```_

### DELE Operationen
```sql
-- Basic delete
DELETE FROM users WHERE id = 1;

-- Delete with conditions
DELETE FROM users WHERE username = 'john_doe';
DELETE FROM users WHERE created_at < '2022-01-01';

-- Delete multiple rows
DELETE FROM users WHERE id IN (1, 2, 3);

-- Delete with JOIN
DELETE u FROM users u
JOIN user_profiles p ON u.id = p.user_id
WHERE p.is_deleted = TRUE;

-- Delete with subquery
DELETE FROM users 
WHERE id IN (SELECT user_id FROM inactive_users);

-- Delete all rows (keep table structure)
DELETE FROM users;

-- Truncate table (faster for deleting all rows)
TRUNCATE TABLE users;
```_

## Index

### Indexe erstellen
```sql
-- Create index on single column
CREATE INDEX idx_username ON users(username);

-- Create index on multiple columns
CREATE INDEX idx_name_email ON users(username, email);

-- Create unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Create index with specific length
CREATE INDEX idx_username_partial ON users(username(10));

-- Create index on expression
CREATE INDEX idx_email_domain ON users((SUBSTRING_INDEX(email, '@', -1)));

-- Create fulltext index
CREATE FULLTEXT INDEX idx_content ON posts(title, content);
```_

### Index verwalten
```sql
-- Show indexes for table
SHOW INDEXES FROM users;

-- Show index usage
SHOW INDEX FROM users;

-- Drop index
DROP INDEX idx_username ON users;

-- Add index using ALTER TABLE
ALTER TABLE users ADD INDEX idx_created_at (created_at);

-- Drop index using ALTER TABLE
ALTER TABLE users DROP INDEX idx_created_at;

-- Analyze index usage
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

-- Show index statistics
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'database_name';
```_

### Indextypen
```sql
-- B-Tree index (default)
CREATE INDEX idx_btree ON users(username) USING BTREE;

-- Hash index
CREATE INDEX idx_hash ON users(id) USING HASH;

-- Fulltext index for text search
CREATE FULLTEXT INDEX idx_fulltext ON posts(content);

-- Spatial index for geometric data
CREATE SPATIAL INDEX idx_location ON locations(coordinates);

-- Composite index
CREATE INDEX idx_composite ON orders(customer_id, order_date, status);

-- Partial index with WHERE clause (MySQL 8.0+)
CREATE INDEX idx_active_users ON users(username) WHERE is_active = TRUE;
```_

## Einschränkungen

### Primärschlüssel
```sql
-- Add primary key during table creation
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50)
);

-- Add primary key to existing table
ALTER TABLE users ADD PRIMARY KEY (id);

-- Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- Drop primary key
ALTER TABLE users DROP PRIMARY KEY;
```_

### Ausländischer Schlüssel
```sql
-- Add foreign key during table creation
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    title VARCHAR(200),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Add foreign key to existing table
ALTER TABLE posts ADD CONSTRAINT fk_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);

-- Foreign key with actions
ALTER TABLE posts ADD CONSTRAINT fk_user_id 
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

-- Drop foreign key
ALTER TABLE posts DROP FOREIGN KEY fk_user_id;

-- Show foreign keys
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'database_name';
```_

### Unique Constrain
```sql
-- Add unique constraint during table creation
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

-- Add unique constraint to existing table
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);

-- Composite unique constraint
ALTER TABLE users ADD CONSTRAINT uk_username_email UNIQUE (username, email);

-- Drop unique constraint
ALTER TABLE users DROP INDEX uk_email;
```_

### Prüfen Sie die Einschränkung
```sql
-- Add check constraint during table creation
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10,2) CHECK (price > 0),
    stock_quantity INT CHECK (stock_quantity >= 0)
);

-- Add check constraint to existing table
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);

-- Complex check constraint
ALTER TABLE users ADD CONSTRAINT chk_email 
CHECK (email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Drop check constraint
ALTER TABLE products DROP CHECK chk_price;
```_

### Nicht Null Constrain
```sql
-- Add NOT NULL during table creation
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- Add NOT NULL to existing column
ALTER TABLE users MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- Remove NOT NULL constraint
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NULL;
```_

## Mitglieder

### Inner anmelden
```sql
-- Basic inner join
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

-- Inner join with WHERE clause
SELECT u.username, p.title, p.created_at
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE p.created_at > '2023-01-01';

-- Multiple inner joins
SELECT u.username, p.title, c.name as category
FROM users u
INNER JOIN posts p ON u.id = p.user_id
INNER JOIN categories c ON p.category_id = c.id;
```_

### Links
```sql
-- Left join (all users, even without posts)
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

-- Left join with NULL check
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;

-- Left join to find users without posts
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;
```_

### Mitglied
```sql
-- Right join (all posts, even if user doesn't exist)
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

-- Right join to find orphaned posts
SELECT p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id
WHERE u.id IS NULL;
```_

### Vollständiges Outer Join
```sql
-- Full outer join (MySQL doesn't support FULL OUTER JOIN directly)
-- Use UNION of LEFT and RIGHT joins
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;
```_

### Mitmachen
```sql
-- Cross join (Cartesian product)
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c;

-- Cross join with WHERE clause
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c
WHERE u.is_active = TRUE;
```_

### Selbstvertrauen
```sql
-- Self join to find users in same city
SELECT u1.username as user1, u2.username as user2, u1.city
FROM users u1
INNER JOIN users u2 ON u1.city = u2.city AND u1.id != u2.id;

-- Self join for hierarchical data
SELECT e.name as employee, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
```_

## Funktionen

### String Funktionen
```sql
-- String manipulation
SELECT CONCAT('Hello', ' ', 'World');                    -- Hello World
SELECT CONCAT_WS('-', 'John', 'Doe', '123');            -- John-Doe-123
SELECT LENGTH('Hello World');                            -- 11
SELECT CHAR_LENGTH('Hello World');                       -- 11
SELECT UPPER('hello world');                             -- HELLO WORLD
SELECT LOWER('HELLO WORLD');                             -- hello world
SELECT SUBSTRING('Hello World', 1, 5);                  -- Hello
SELECT LEFT('Hello World', 5);                          -- Hello
SELECT RIGHT('Hello World', 5);                         -- World
SELECT TRIM('  Hello World  ');                         -- Hello World
SELECT LTRIM('  Hello World');                          -- Hello World
SELECT RTRIM('Hello World  ');                          -- Hello World
SELECT REPLACE('Hello World', 'World', 'MySQL');        -- Hello MySQL
SELECT REVERSE('Hello');                                 -- olleH
SELECT REPEAT('Ha', 3);                                  -- HaHaHa
SELECT LPAD('123', 5, '0');                             -- 00123
SELECT RPAD('123', 5, '0');                             -- 12300

-- String search
SELECT LOCATE('World', 'Hello World');                  -- 7
SELECT POSITION('World' IN 'Hello World');              -- 7
SELECT INSTR('Hello World', 'World');                   -- 7
SELECT FIND_IN_SET('b', 'a,b,c,d');                    -- 2

-- Pattern matching
SELECT 'Hello World' LIKE 'Hello%';                     -- 1 (true)
SELECT 'Hello World' REGEXP '^Hello';                   -- 1 (true)
```_

### Numerische Funktionen
```sql
-- Mathematical functions
SELECT ABS(-15);                                         -- 15
SELECT CEIL(4.3);                                        -- 5
SELECT CEILING(4.3);                                     -- 5
SELECT FLOOR(4.7);                                       -- 4
SELECT ROUND(4.567, 2);                                  -- 4.57
SELECT TRUNCATE(4.567, 2);                              -- 4.56
SELECT MOD(10, 3);                                       -- 1
SELECT POW(2, 3);                                        -- 8
SELECT POWER(2, 3);                                      -- 8
SELECT SQRT(16);                                         -- 4
SELECT RAND();                                           -- Random number 0-1
SELECT RAND(123);                                        -- Random with seed

-- Aggregate functions
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT username) FROM users;
SELECT SUM(price) FROM products;
SELECT AVG(price) FROM products;
SELECT MIN(price) FROM products;
SELECT MAX(price) FROM products;
SELECT GROUP_CONCAT(username) FROM users;
```_

### Datums- und Zeitfunktionen
```sql
-- Current date and time
SELECT NOW();                                            -- Current datetime
SELECT CURDATE();                                        -- Current date
SELECT CURRENT_DATE();                                   -- Current date
SELECT CURTIME();                                        -- Current time
SELECT CURRENT_TIME();                                   -- Current time
SELECT UNIX_TIMESTAMP();                                 -- Unix timestamp
SELECT UTC_TIMESTAMP();                                  -- UTC datetime

-- Date formatting
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');                  -- 2023-12-25
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');            -- Monday, December 25, 2023
SELECT TIME_FORMAT(NOW(), '%H:%i:%s');                  -- 14:30:45

-- Date arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);                 -- Tomorrow
SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK);                -- Last week
SELECT DATEDIFF('2023-12-25', '2023-12-01');           -- 24
SELECT TIMESTAMPDIFF(HOUR, '2023-12-01 10:00:00', '2023-12-01 15:30:00'); -- 5

-- Date extraction
SELECT YEAR(NOW());                                      -- 2023
SELECT MONTH(NOW());                                     -- 12
SELECT DAY(NOW());                                       -- 25
SELECT HOUR(NOW());                                      -- 14
SELECT MINUTE(NOW());                                    -- 30
SELECT SECOND(NOW());                                    -- 45
SELECT DAYNAME(NOW());                                   -- Monday
SELECT MONTHNAME(NOW());                                 -- December
SELECT DAYOFWEEK(NOW());                                -- 2 (Monday)
SELECT DAYOFYEAR(NOW());                                -- 359
SELECT WEEK(NOW());                                      -- 52
```_

### Bedingte Funktionen
```sql
-- IF function
SELECT IF(price > 100, 'Expensive', 'Affordable') as price_category
FROM products;

-- CASE statement
SELECT 
    name,
    price,
    CASE 
        WHEN price < 50 THEN 'Budget'
        WHEN price < 200 THEN 'Mid-range'
        ELSE 'Premium'
    END as category
FROM products;

-- IFNULL and COALESCE
SELECT IFNULL(phone, 'No phone') FROM users;
SELECT COALESCE(phone, email, 'No contact') FROM users;

-- NULLIF
SELECT NULLIF(username, '') FROM users;  -- Returns NULL if username is empty
```_

### JSON Funktionen
```sql
-- JSON creation
SELECT JSON_OBJECT('name', 'John', 'age', 30);
SELECT JSON_ARRAY('apple', 'banana', 'orange');

-- JSON extraction
SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');  -- John
SELECT '{"name": "John", "age": 30}'->'$.name';                -- John
SELECT '{"name": "John", "age": 30}'->>'$.name';               -- John (unquoted)

-- JSON modification
SELECT JSON_SET('{"name": "John"}', '$.age', 30);
SELECT JSON_INSERT('{"name": "John"}', '$.age', 30);
SELECT JSON_REPLACE('{"name": "John", "age": 25}', '$.age', 30);
SELECT JSON_REMOVE('{"name": "John", "age": 30}', '$.age');

-- JSON search
SELECT JSON_SEARCH('["apple", "banana", "orange"]', 'one', 'banana');
SELECT JSON_CONTAINS('["apple", "banana"]', '"banana"');
```_

## Gespeicherte Verfahren

### Erstellung gespeicherter Verfahren
```sql
-- Simple stored procedure
DELIMITER //
CREATE PROCEDURE GetUserCount()
BEGIN
    SELECT COUNT(*) as user_count FROM users;
END //
DELIMITER ;

-- Stored procedure with parameters
DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

-- Stored procedure with OUT parameter
DELIMITER //
CREATE PROCEDURE GetUserStats(OUT total_users INT, OUT active_users INT)
BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
    SELECT COUNT(*) INTO active_users FROM users WHERE is_active = TRUE;
END //
DELIMITER ;

-- Stored procedure with INOUT parameter
DELIMITER //
CREATE PROCEDURE UpdateUserStatus(INOUT user_id INT, IN new_status VARCHAR(20))
BEGIN
    UPDATE users SET status = new_status WHERE id = user_id;
    SELECT ROW_COUNT() INTO user_id;
END //
DELIMITER ;
```_

### Erweiterte gespeicherte Verfahren
```sql
-- Stored procedure with variables and conditions
DELIMITER //
CREATE PROCEDURE ProcessUser(IN user_id INT)
BEGIN
    DECLARE user_count INT DEFAULT 0;
    DECLARE user_status VARCHAR(20);

    SELECT COUNT(*) INTO user_count FROM users WHERE id = user_id;

    IF user_count > 0 THEN
        SELECT status INTO user_status FROM users WHERE id = user_id;

        IF user_status = 'pending' THEN
            UPDATE users SET status = 'active' WHERE id = user_id;
            SELECT 'User activated' as message;
        ELSE
            SELECT 'User already processed' as message;
        END IF;
    ELSE
        SELECT 'User not found' as message;
    END IF;
END //
DELIMITER ;

-- Stored procedure with loop
DELIMITER //
CREATE PROCEDURE CreateSampleUsers(IN count INT)
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= count DO
        INSERT INTO users (username, email) 
        VALUES (CONCAT('user', i), CONCAT('user', i, '@example.com'));
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- Stored procedure with cursor
DELIMITER //
CREATE PROCEDURE ProcessAllUsers()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_id INT;
    DECLARE user_email VARCHAR(100);

    DECLARE user_cursor CURSOR FOR 
        SELECT id, email FROM users WHERE is_active = TRUE;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN user_cursor;

    user_loop: LOOP
        FETCH user_cursor INTO user_id, user_email;

        IF done THEN
            LEAVE user_loop;
        END IF;

        -- Process each user
        UPDATE users SET last_processed = NOW() WHERE id = user_id;
    END LOOP;

    CLOSE user_cursor;
END //
DELIMITER ;
```_

### Verwaltung gespeicherter Verfahren
```sql
-- Call stored procedure
CALL GetUserCount();
CALL GetUserById(1);

-- Call procedure with OUT parameters
CALL GetUserStats(@total, @active);
SELECT @total, @active;

-- Show stored procedures
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS WHERE Db = 'database_name';

-- Show procedure definition
SHOW CREATE PROCEDURE GetUserCount;

-- Drop stored procedure
DROP PROCEDURE IF EXISTS GetUserCount;

-- Alter stored procedure (MySQL doesn't support ALTER PROCEDURE)
-- You need to DROP and CREATE again
```_

### Fehlerbehandlung in Stored Procedures
```sql
DELIMITER //
CREATE PROCEDURE SafeUpdateUser(IN user_id INT, IN new_email VARCHAR(100))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE users SET email = new_email WHERE id = user_id;

    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'User not found';
    END IF;

    COMMIT;
END //
DELIMITER ;
```_

## Auslöser

### Trigger erstellen
```sql
-- BEFORE INSERT trigger
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.username = LOWER(NEW.username);
END //
DELIMITER ;

-- AFTER INSERT trigger
DELIMITER //
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, timestamp)
    VALUES (NEW.id, 'INSERT', NOW());
END //
DELIMITER ;

-- BEFORE UPDATE trigger
DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();

    IF NEW.email != OLD.email THEN
        SET NEW.email_verified = FALSE;
    END IF;
END //
DELIMITER ;

-- AFTER UPDATE trigger
DELIMITER //
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, old_values, new_values, timestamp)
    VALUES (
        NEW.id, 
        'UPDATE', 
        JSON_OBJECT('email', OLD.email, 'status', OLD.status),
        JSON_OBJECT('email', NEW.email, 'status', NEW.status),
        NOW()
    );
END //
DELIMITER ;

-- BEFORE DELETE trigger
DELIMITER //
CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO deleted_users (original_id, username, email, deleted_at)
    VALUES (OLD.id, OLD.username, OLD.email, NOW());
END //
DELIMITER ;
```_

### Erweiterte Trigger
```sql
-- Trigger with conditions
DELIMITER //
CREATE TRIGGER update_product_stock
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    DECLARE current_stock INT;

    SELECT stock_quantity INTO current_stock 
    FROM products 
    WHERE id = NEW.product_id;

    IF current_stock >= NEW.quantity THEN
        UPDATE products 
        SET stock_quantity = stock_quantity - NEW.quantity 
        WHERE id = NEW.product_id;
    ELSE
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Insufficient stock';
    END IF;
END //
DELIMITER ;

-- Trigger for automatic calculations
DELIMITER //
CREATE TRIGGER calculate_order_total
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE total DECIMAL(10,2) DEFAULT 0;

    SELECT SUM(p.price * oi.quantity) INTO total
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    WHERE oi.order_id = NEW.id;

    SET NEW.total_amount = IFNULL(total, 0);
END //
DELIMITER ;
```_

### Verwaltung von Triggern
```sql
-- Show triggers
SHOW TRIGGERS;
SHOW TRIGGERS FROM database_name;
SHOW TRIGGERS LIKE 'user%';

-- Show trigger definition
SHOW CREATE TRIGGER before_user_insert;

-- Drop trigger
DROP TRIGGER IF EXISTS before_user_insert;

-- Get trigger information
SELECT 
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_TABLE,
    ACTION_TIMING
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'database_name';
```_

## Ansichten

### Ansichten erstellen
```sql
-- Simple view
CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE is_active = TRUE;

-- View with JOIN
CREATE VIEW user_posts AS
SELECT 
    u.username,
    u.email,
    p.title,
    p.content,
    p.created_at as post_date
FROM users u
JOIN posts p ON u.id = p.user_id;

-- View with aggregation
CREATE VIEW user_stats AS
SELECT 
    u.id,
    u.username,
    COUNT(p.id) as post_count,
    MAX(p.created_at) as last_post_date
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

-- View with complex logic
CREATE VIEW product_summary AS
SELECT 
    p.id,
    p.name,
    p.price,
    c.name as category,
    CASE 
        WHEN p.stock_quantity = 0 THEN 'Out of Stock'
        WHEN p.stock_quantity < 10 THEN 'Low Stock'
        ELSE 'In Stock'
    END as stock_status,
    (p.price * 0.9) as discounted_price
FROM products p
JOIN categories c ON p.category_id = c.id;
```_

### Auffällige Ansichten
```sql
-- Create updatable view
CREATE VIEW user_profiles AS
SELECT id, username, email, first_name, last_name
FROM users
WHERE is_active = TRUE;

-- Update through view
UPDATE user_profiles 
SET email = 'newemail@example.com' 
WHERE id = 1;

-- Insert through view
INSERT INTO user_profiles (username, email, first_name, last_name)
VALUES ('newuser', 'new@example.com', 'New', 'User');

-- Delete through view
DELETE FROM user_profiles WHERE id = 1;
```_

### Verwaltung von Ansichten
```sql
-- Show views
SHOW TABLES;  -- Views appear with tables
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Show view definition
SHOW CREATE VIEW active_users;

-- Describe view structure
DESCRIBE active_users;

-- Alter view
ALTER VIEW active_users AS
SELECT id, username, email, created_at, last_login
FROM users
WHERE is_active = TRUE;

-- Drop view
DROP VIEW IF EXISTS active_users;

-- Get view information
SELECT 
    TABLE_NAME,
    VIEW_DEFINITION,
    IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'database_name';
```_

## Benutzermanagement

### Benutzer erstellen
```sql
-- Create user with password
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- Create user for any host
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

-- Create user for specific host
CREATE USER 'username'@'192.168.1.100' IDENTIFIED BY 'password';

-- Create user with authentication plugin
CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

-- Create user with password expiration
CREATE USER 'username'@'localhost' 
IDENTIFIED BY 'password' 
PASSWORD EXPIRE INTERVAL 90 DAY;
```_

### Vorrechte gewähren
```sql
-- Grant all privileges on all databases
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

-- Grant all privileges on specific database
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'localhost';

-- Grant privileges on specific table
GRANT SELECT, UPDATE ON database_name.users TO 'username'@'localhost';

-- Grant specific column privileges
GRANT SELECT (username, email), UPDATE (email) ON database_name.users TO 'username'@'localhost';

-- Grant execute privilege for stored procedures
GRANT EXECUTE ON database_name.* TO 'username'@'localhost';

-- Grant with grant option (user can grant privileges to others)
GRANT SELECT ON database_name.* TO 'username'@'localhost' WITH GRANT OPTION;

-- Apply privilege changes
FLUSH PRIVILEGES;
```_

### Benutzerrechte verwalten
```sql
-- Show current user
SELECT USER();

-- Show user privileges
SHOW GRANTS;
SHOW GRANTS FOR 'username'@'localhost';

-- Show all users
SELECT User, Host FROM mysql.user;

-- Show user details
SELECT 
    User, 
    Host, 
    authentication_string,
    password_expired,
    password_lifetime
FROM mysql.user;

-- Revoke privileges
REVOKE INSERT, UPDATE ON database_name.* FROM 'username'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';

-- Change user password
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

-- Set password expiration
ALTER USER 'username'@'localhost' PASSWORD EXPIRE;
ALTER USER 'username'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Lock/unlock user account
ALTER USER 'username'@'localhost' ACCOUNT LOCK;
ALTER USER 'username'@'localhost' ACCOUNT UNLOCK;

-- Drop user
DROP USER 'username'@'localhost';
```_

### Roles (MySQL 8.0+)
```sql
-- Create role
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- Grant privileges to role
GRANT SELECT ON database_name.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON database_name.* TO 'app_write';
GRANT ALL PRIVILEGES ON database_name.* TO 'app_admin';

-- Grant role to user
GRANT 'app_read' TO 'username'@'localhost';
GRANT 'app_read', 'app_write' TO 'username'@'localhost';

-- Set default role
ALTER USER 'username'@'localhost' DEFAULT ROLE 'app_read';

-- Activate role in session
SET ROLE 'app_write';
SET ROLE ALL;

-- Show roles
SHOW GRANTS FOR 'username'@'localhost' USING 'app_write';

-- Drop role
DROP ROLE 'app_read';
```_

## Backup und Wiederherstellung

### mysqldump Backup
```bash
# Backup single database
mysqldump -u username -p database_name > backup.sql

# Backup multiple databases
mysqldump -u username -p --databases db1 db2 db3 > backup.sql

# Backup all databases
mysqldump -u username -p --all-databases > all_databases.sql

# Backup with additional options
mysqldump -u username -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  database_name > backup.sql

# Backup specific tables
mysqldump -u username -p database_name table1 table2 > tables_backup.sql

# Backup structure only (no data)
mysqldump -u username -p --no-data database_name > structure.sql

# Backup data only (no structure)
mysqldump -u username -p --no-create-info database_name > data.sql

# Compressed backup
mysqldump -u username -p database_name | gzip > backup.sql.gz

# Remote backup
mysqldump -h remote_host -u username -p database_name > backup.sql
```_

### Wiederherstellen von Backup
```bash
# Restore database
mysql -u username -p database_name < backup.sql

# Restore and create database
mysql -u username -p -e "CREATE DATABASE database_name;"
mysql -u username -p database_name < backup.sql

# Restore compressed backup
gunzip < backup.sql.gz | mysql -u username -p database_name

# Restore from remote
mysql -h remote_host -u username -p database_name < backup.sql

# Restore with progress (using pv)
pv backup.sql | mysql -u username -p database_name
```_

### Binary Log Backup
```sql
-- Enable binary logging (in my.cnf)
-- log-bin=mysql-bin
-- server-id=1

-- Show binary logs
SHOW BINARY LOGS;

-- Show current binary log position
SHOW MASTER STATUS;

-- Backup binary logs
-- Use mysqlbinlog utility
```_

```bash
# Backup binary logs
mysqlbinlog mysql-bin.000001 > binlog_backup.sql

# Backup multiple binary logs
mysqlbinlog mysql-bin.000001 mysql-bin.000002 > binlog_backup.sql

# Backup with date range
mysqlbinlog --start-datetime="2023-01-01 00:00:00" \
           --stop-datetime="2023-01-02 00:00:00" \
           mysql-bin.000001 > binlog_backup.sql

# Point-in-time recovery
mysqlbinlog --start-position=123456 mysql-bin.000001 | mysql -u username -p

Automatisiertes Backup-Script

```bash

!/bin/bash

backup_mysql.sh

Configuration

DB_USER="backup_user" DB_PASS="backup_password" DB_NAME="database_name" BACKUP_DIR="/backup/mysql" DATE=$(date +%Y%m%d_%H%M%S) BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$DATE.sql"

Create backup directory

mkdir -p $BACKUP_DIR

Perform backup

mysqldump -u $DB_USER -p$DB_PASS \ --single-transaction \ --routines \ --triggers \ --events \ $DB_NAME > $BACKUP_FILE

Compress backup

gzip $BACKUP_FILE

Remove old backups (keep last 7 days)

find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "Backup completed: ${BACKUP_FILE}.gz" ```_

Leistungsoptimierung

Queroptimierung

```sql -- Use EXPLAIN to analyze queries EXPLAIN SELECT * FROM users WHERE username = 'john_doe'; EXPLAIN FORMAT=JSON SELECT * FROM users WHERE username = 'john_doe';

-- Use indexes effectively -- Bad: Full table scan SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- Good: Use index SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

-- Use LIMIT for large result sets SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- Use EXISTS instead of IN for subqueries -- Bad SELECT * FROM users WHERE id IN (SELECT user_id FROM posts);

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

-- Use proper JOIN syntax -- Bad: Implicit join SELECT u.username, p.title FROM users u, posts p WHERE u.id = p.user_id;

-- Good: Explicit join SELECT u.username, p.title FROM users u INNER JOIN posts p ON u.id = p.user_id; ```_

Indexoptimierung

```sql -- Analyze table to update index statistics ANALYZE TABLE users;

-- Check index usage SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, CARDINALITY, SUB_PART FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'database_name' ORDER BY TABLE_NAME, INDEX_NAME;

-- Find unused indexes SELECT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.STATISTICS s LEFT JOIN information_schema.INDEX_STATISTICS i ON s.TABLE_SCHEMA = i.TABLE_SCHEMA AND s.TABLE_NAME = i.TABLE_NAME AND s.INDEX_NAME = i.INDEX_NAME WHERE s.TABLE_SCHEMA = 'database_name' AND i.INDEX_NAME IS NULL AND s.INDEX_NAME != 'PRIMARY';

-- Optimize table OPTIMIZE TABLE users;

-- Check table for errors CHECK TABLE users;

-- Repair table if needed REPAIR TABLE users; ```_

Konfigurationsoptimierung

```sql -- Show current configuration SHOW VARIABLES; SHOW VARIABLES LIKE 'innodb%'; SHOW VARIABLES LIKE 'query_cache%';

-- Important variables to tune SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; -- Should be 70-80% of RAM SHOW VARIABLES LIKE 'innodb_log_file_size'; -- 25% of buffer pool size SHOW VARIABLES LIKE 'max_connections'; -- Based on application needs SHOW VARIABLES LIKE 'query_cache_size'; -- For read-heavy workloads SHOW VARIABLES LIKE 'tmp_table_size'; -- For complex queries SHOW VARIABLES LIKE 'max_heap_table_size'; -- Should match tmp_table_size

-- Show status variables SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests'; SHOW STATUS LIKE 'Innodb_buffer_pool_reads'; SHOW STATUS LIKE 'Qcache_hits'; SHOW STATUS LIKE 'Qcache_inserts'; ```_

Leistungsüberwachung

```sql -- Show running processes SHOW PROCESSLIST; SHOW FULL PROCESSLIST;

-- Kill long-running query KILL QUERY process_id; KILL CONNECTION process_id;

-- Enable slow query log SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Performance Schema queries -- Top queries by execution time SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 as avg_time_sec, SUM_TIMER_WAIT/1000000000 as total_time_sec FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- Table I/O statistics SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT_READ, COUNT_WRITE, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_table WHERE OBJECT_SCHEMA = 'database_name' ORDER BY COUNT_READ + COUNT_WRITE DESC; ```_

Wiederholung

Master-Slave Replication Setup

```sql -- Master configuration (my.cnf) -- [mysqld] -- server-id = 1 -- log-bin = mysql-bin -- binlog-do-db = database_name

-- Create replication user on master CREATE USER 'replication'@'%' IDENTIFIED BY 'replication_password'; GRANT REPLICATION SLAVE ON . TO 'replication'@'%'; FLUSH PRIVILEGES;

-- Get master status SHOW MASTER STATUS; -- Note the File and Position values

-- Slave configuration (my.cnf) -- [mysqld] -- server-id = 2 -- relay-log = mysql-relay-bin

-- Configure slave CHANGE MASTER TO MASTER_HOST = 'master_ip', MASTER_USER = 'replication', MASTER_PASSWORD = 'replication_password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154;

-- Start slave START SLAVE;

-- Check slave status SHOW SLAVE STATUS\G

-- Stop slave STOP SLAVE; ```_

Master Replication

```sql -- Server 1 configuration (my.cnf) -- [mysqld] -- server-id = 1 -- log-bin = mysql-bin -- auto-increment-increment = 2 -- auto-increment-offset = 1

-- Server 2 configuration (my.cnf) -- [mysqld] -- server-id = 2 -- log-bin = mysql-bin -- auto-increment-increment = 2 -- auto-increment-offset = 2

-- Configure each server as slave to the other -- On Server 1: CHANGE MASTER TO MASTER_HOST = 'server2_ip', MASTER_USER = 'replication', MASTER_PASSWORD = 'replication_password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154;

-- On Server 2: CHANGE MASTER TO MASTER_HOST = 'server1_ip', MASTER_USER = 'replication', MASTER_PASSWORD = 'replication_password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154; ```_

Überprüfung

```sql -- Check replication status SHOW SLAVE STATUS\G

-- Important fields to monitor: -- Slave_IO_Running: Should be 'Yes' -- Slave_SQL_Running: Should be 'Yes' -- Seconds_Behind_Master: Replication lag -- Last_Error: Any replication errors

-- Check master status SHOW MASTER STATUS;

-- Show binary logs SHOW BINARY LOGS;

-- Reset slave STOP SLAVE; RESET SLAVE;

-- Skip replication error SET GLOBAL sql_slave_skip_counter = 1; START SLAVE; ```_

Sicherheit

SSL/TLS Konfiguration

```sql -- Check SSL status SHOW VARIABLES LIKE 'have_ssl'; SHOW STATUS LIKE 'Ssl_cipher';

-- Require SSL for user ALTER USER 'username'@'%' REQUIRE SSL;

-- Require specific SSL options ALTER USER 'username'@'%' REQUIRE X509; ALTER USER 'username'@'%' REQUIRE CIPHER 'AES256-SHA';

-- Connect with SSL -- mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem ```_

Datenverschlüsselung

```sql -- Enable encryption at rest (my.cnf) -- [mysqld] -- early-plugin-load=keyring_file.so -- keyring_file_data=/var/lib/mysql-keyring/keyring

-- Create encrypted table CREATE TABLE sensitive_data ( id INT PRIMARY KEY AUTO_INCREMENT, data TEXT ) ENCRYPTION='Y';

-- Encrypt existing table ALTER TABLE users ENCRYPTION='Y';

-- Check encryption status SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM information_schema.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%'; ```_

Audit Logging

```sql -- Enable audit log plugin -- INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- Configure audit log (my.cnf) -- [mysqld] -- plugin-load-add=audit_log.so -- audit_log_file=/var/log/mysql/audit.log -- audit_log_format=JSON

-- Show audit log status SHOW VARIABLES LIKE 'audit_log%';

-- Filter audit events SET GLOBAL audit_log_include_accounts = 'user1@localhost,user2@%'; SET GLOBAL audit_log_exclude_accounts = 'root@localhost'; ```_

Sicherheit Best Practices

```sql -- Remove anonymous users DELETE FROM mysql.user WHERE User='';

-- Remove test database DROP DATABASE IF EXISTS test;

-- Disable remote root login DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Set strong passwords ALTER USER 'root'@'localhost' IDENTIFIED BY 'strong_password';

-- Limit user privileges -- Don't grant ALL PRIVILEGES unless necessary GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost';

-- Use specific hosts instead of '%' CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'password';

-- Enable password validation -- INSTALL COMPONENT 'file://component_validate_password'; SHOW VARIABLES LIKE 'validate_password%';

-- Set password policy SET GLOBAL validate_password.policy = 'STRONG'; SET GLOBAL validate_password.length = 12; ```_

Überwachung

Leistungsüberwachung

```sql -- Enable Performance Schema -- [mysqld] -- performance_schema = ON

-- Monitor current connections SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_TIME, PROCESSLIST_STATE, PROCESSLIST_INFO FROM performance_schema.processlist WHERE PROCESSLIST_COMMAND != 'Sleep';

-- Monitor table locks SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS FROM performance_schema.metadata_locks WHERE OBJECT_TYPE = 'TABLE';

-- Monitor memory usage SELECT EVENT_NAME, CURRENT_COUNT_USED, CURRENT_NUMBER_OF_BYTES_USED, LOW_COUNT_USED, HIGH_COUNT_USED FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/innodb%' ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC; ```_

Systemüberwachung

```bash

Monitor MySQL processes

ps aux | grep mysql

Monitor MySQL connections

netstat -an | grep :3306

Monitor MySQL files

lsof -p $(pgrep mysqld)

Monitor disk usage

df -h /var/lib/mysql

Monitor MySQL logs

tail -f /var/log/mysql/error.log tail -f /var/log/mysql/slow.log

Monitor system resources

top -p $(pgrep mysqld) iostat -x 1 ```_

Automatisierte Überwachung Script

```bash

!/bin/bash

mysql_monitor.sh

MYSQL_USER="monitor_user" MYSQL_PASS="monitor_password" THRESHOLD_CONNECTIONS=80 THRESHOLD_SLOW_QUERIES=10

Check connection count

CONNECTIONS=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2 {print $2}')

if [ $CONNECTIONS -gt $THRESHOLD_CONNECTIONS ]; then echo "WARNING: High connection count: $CONNECTIONS" fi

Check slow queries

SLOW_QUERIES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Slow_queries';" | awk 'NR==2 {print $2}')

if [ $SLOW_QUERIES -gt $THRESHOLD_SLOW_QUERIES ]; then echo "WARNING: High slow query count: $SLOW_QUERIES" fi

Check replication lag (if slave)

| LAG=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}') |

if [ ! -z "$LAG" ] && [ $LAG -gt 60 ]; then echo "WARNING: Replication lag: $LAG seconds" fi

echo "MySQL monitoring completed at $(date)" ```_

Best Practices

Datenbank Design

```sql -- Use appropriate data types -- Bad: VARCHAR(255) for everything CREATE TABLE users ( id VARCHAR(255), age VARCHAR(255), is_active VARCHAR(255) );

-- Good: Specific data types CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, age TINYINT UNSIGNED, is_active BOOLEAN DEFAULT TRUE );

-- Normalize your database -- Avoid redundant data -- Use foreign keys to maintain referential integrity

-- Use meaningful names -- Bad CREATE TABLE u ( i INT, n VARCHAR(50), e VARCHAR(100) );

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

-- Add appropriate indexes CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_posts_user_id ON posts(user_id); CREATE INDEX idx_posts_created_at ON posts(created_at);

-- Use constraints to ensure data integrity ALTER TABLE posts ADD CONSTRAINT fk_posts_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0); ```_

Best Practices abfragen

```sql -- Use prepared statements to prevent SQL injection PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?'; SET @user_id = 1; EXECUTE stmt USING @user_id; DEALLOCATE PREPARE stmt;

-- Use LIMIT for large result sets SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- Use specific columns instead of SELECT * -- Bad SELECT * FROM users;

-- Good SELECT id, username, email FROM users;

-- Use EXISTS instead of IN for better performance -- Bad SELECT * FROM users WHERE id IN (SELECT user_id FROM posts);

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

-- Use UNION ALL instead of UNION when duplicates are acceptable SELECT username FROM active_users UNION ALL SELECT username FROM inactive_users;

-- Use proper JOIN syntax SELECT u.username, p.title FROM users u INNER JOIN posts p ON u.id = p.user_id WHERE u.is_active = TRUE; ```_

Performance Best Practices

```sql -- Regular maintenance ANALYZE TABLE users; OPTIMIZE TABLE users; CHECK TABLE users;

-- Monitor slow queries SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1;

-- Use connection pooling in applications -- Configure appropriate connection limits

-- Partition large tables CREATE TABLE sales ( id INT, sale_date DATE, amount DECIMAL(10,2) ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025) );

-- Use read replicas for read-heavy workloads -- Separate read and write operations

-- Cache frequently accessed data -- Use application-level caching (Redis, Memcached) -- Use MySQL query cache for repeated queries ```_

Sicherheit Best Practices

```sql -- Use least privilege principle -- Grant only necessary permissions GRANT SELECT, INSERT, UPDATE ON app_db.users TO 'app_user'@'localhost';

-- Use strong passwords -- Enable password validation plugin -- Set password expiration policies

-- Enable SSL/TLS -- Encrypt sensitive data -- Use audit logging

-- Regular security updates -- Monitor for suspicious activity -- Backup encryption keys securely

-- Validate input in application layer -- Use prepared statements -- Escape special characters ```_

Best Practices sichern

```bash

Regular automated backups

Full backup daily

mysqldump -u backup_user -p --single-transaction --all-databases > full_backup.sql

Incremental backup using binary logs

mysqlbinlog mysql-bin.000001 > incremental_backup.sql

Test restore procedures regularly

Store backups in multiple locations

Encrypt backup files

Document recovery procedures

Monitor backup success

Set up alerts for backup failures

Verify backup integrity

```_

--

Zusammenfassung

MySQL ist ein robustes und vielseitiges relationales Datenbankmanagementsystem, das Millionen von Anwendungen weltweit betreibt. Dieses umfassende Cheatsheet umfasst wesentliche MySQL-Operationen von der Grundinstallation bis hin zu erweiterten Themen wie Replikation und Leistungsoptimierung.

Key Strengths: - ** Zuverlässigkeit: Bewährter Rekord in Produktionsumgebungen - Leistung: Optimiert für Hochleistungsanwendungen - Skalierbarkeit: Unterstützt verschiedene Skalierstrategien einschließlich Replikation und Partitionierung - Flexibilität: Arbeitet gut mit verschiedenen Programmiersprachen und Frameworks - Gemeinschaft**: Große Gemeinschaft und umfangreiche Dokumentationen

Best Use Cases: - Webanwendungen und Content-Management-Systeme - E-Commerce-Plattformen - Datenspeicherung und Analytik - Unternehmen - Entwicklungs- und Prüfumgebungen

Importierte Überlegungen: - Regelmäßige Wartung und Überwachung sind entscheidend für optimale Leistung - Die richtige Indexierungsstrategie ist für die Abfrageleistung unerlässlich - Sicherheitskonfiguration sollte von Anfang an implementiert werden - Backup- und Recovery-Verfahren müssen regelmäßig getestet werden

Durch die in diesem Cheatsheet skizzierten Praktiken und Techniken können Sie MySQL-Datenbanken effektiv entwerfen, implementieren und pflegen, die sicher, performant und zuverlässig sind.

<= <= <= <================================================================================= Funktion copyToClipboard() {\cHFFFF} const commands = document.querySelectorAll('code'); alle Befehle = ''; Befehle. Für jede(cmd) => alle Befehle += cmd.textContent + '\n'); navigator.clipboard.writeText (allCommands); Alarm ('Alle Befehle, die in die Zwischenablage kopiert werden!'); }

Funktion generierenPDF() { Fenster.print(); }