DBeaver Cheatsheet¶

DBeaver - Universal Datenbank Tool

DBeaver ist ein kostenloses, universelles Datenbanktool für Entwickler, Datenbankadministratoren, Analysten und alle, die mit Datenbanken arbeiten müssen. Es unterstützt alle wichtigen Datenbanken wie MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto und mehr. < p>

generieren

Inhaltsverzeichnis¶

[Installation](LINK_0 -%20Datenbankverbindungen
[SQL Editor](LINK_0_ -%20[Data%20Viewer](LINK_0__ -%20[Schema%20Browser](LINK_0__ -%20(LINK_0_)
[Datenexport/Import](LINK_0 -%20[Datenbankverwaltung](LINK_0__ -%20Visual%20Query%20Builder
[Datentransfer](LINK_0_ -%20Erweiterungen%20und%20Plugins
Beste Praktiken

Installation¶

Windows Installation¶

# Download from official website
# https://dbeaver.io/download/

# Install via Chocolatey
choco install dbeaver

# Install via winget
winget install dbeaver.dbeaver

# Install specific version
choco install dbeaver --version 23.2.5

# Silent installation
dbeaver-ce-23.2.5-x86_64-setup.exe /S

# Portable version
# Download portable ZIP and extract
# No installation required
```_

### macOS Installation
```bash
# Download from official website
# https://dbeaver.io/download/

# Install via Homebrew
brew install --cask dbeaver-community

# Install specific version
brew install --cask dbeaver-community@23.2.5

# Manual installation
curl -O https://dbeaver.io/files/dbeaver-ce-23.2.5-macos-x86_64.dmg
open dbeaver-ce-23.2.5-macos-x86_64.dmg
```_

### Linux Installation
```bash
# Ubuntu/Debian
wget https://dbeaver.io/files/dbeaver-ce_23.2.5_amd64.deb
sudo dpkg -i dbeaver-ce_23.2.5_amd64.deb

# Fix dependencies if needed
sudo apt-get install -f

# CentOS/RHEL/Fedora
wget https://dbeaver.io/files/dbeaver-ce-23.2.5.x86_64.rpm
sudo rpm -i dbeaver-ce-23.2.5.x86_64.rpm

# Or using yum/dnf
sudo yum install dbeaver-ce-23.2.5.x86_64.rpm
sudo dnf install dbeaver-ce-23.2.5.x86_64.rpm

# Snap package
sudo snap install dbeaver-ce

# Flatpak
flatpak install flathub io.dbeaver.DBeaverCommunity

# AppImage
wget https://dbeaver.io/files/dbeaver-ce-23.2.5-linux.gtk.x86_64.tar.gz
tar -xzf dbeaver-ce-23.2.5-linux.gtk.x86_64.tar.gz
cd dbeaver
./dbeaver
```_

### Docker Installation
```bash
# Run DBeaver in Docker (with X11 forwarding)
docker run -it --rm \
  -e DISPLAY=$DISPLAY \
  -v /tmp/.X11-unix:/tmp/.X11-unix \
  -v $HOME/.dbeaver:/opt/dbeaver-data \
  --net=host \
  dbeaver/cloudbeaver

# CloudBeaver (web-based version)
docker run -d --name cloudbeaver \
  -p 8978:8978 \
  -v cloudbeaver-data:/opt/cloudbeaver/workspace \
  dbeaver/cloudbeaver:latest

# Access CloudBeaver at http://localhost:8978
```_

### Systemanforderungen

Minimum Requirements: - OS: Windows 7+, macOS 10.12+, Linux (any modern distribution) - RAM: 512 MB - Disk Space: 200 MB - Java: 11+ (bundled with installer)

Recommended: - RAM: 2 GB or more - Disk Space: 1 GB or more - Java: 17+ for best performance - SSD for better performance with large databases ```_

Datenbankverbindungen¶

Verbindungskonfiguration¶

```sql -- Connection parameters for different databases

-- MySQL Host: localhost Port: 3306 Database: myapp Username: dbuser Password: password Driver: MySQL JDBC Driver

-- PostgreSQL Host: localhost Port: 5432 Database: myapp Username: postgres Password: password Driver: PostgreSQL JDBC Driver

-- SQL Server Host: localhost Port: 1433 Database: myapp Username: sa Password: password Driver: Microsoft SQL Server JDBC Driver

-- Oracle Host: localhost Port: 1521 SID/Service: XE Username: hr Password: password Driver: Oracle JDBC Driver

-- SQLite Database Path: /path/to/database.db Driver: SQLite JDBC Driver

-- MongoDB Host: localhost Port: 27017 Database: myapp Username: user Password: password Authentication Database: admin Driver: MongoDB JDBC Driver ```_

Erweiterte Verbindungseinstellungen¶

```properties

Connection properties file¶

MySQL example¶

driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/myapp user=dbuser password=password

Connection pool settings¶

initialSize=5 maxActive=20 maxIdle=10 minIdle=2 maxWait=10000

SSL settings¶

useSSL=true requireSSL=true verifyServerCertificate=true trustCertificateKeyStoreUrl=/path/to/truststore.jks trustCertificateKeyStorePassword=password

Connection timeout settings¶

connectTimeout=30000 socketTimeout=60000 loginTimeout=30

Additional parameters¶

characterEncoding=UTF-8 useUnicode=true allowMultiQueries=true rewriteBatchedStatements=true ```_

SSH Tunnel Konfiguration¶

```bash

SSH tunnel settings in DBeaver¶

SSH Host: ssh-server.example.com SSH Port: 22 SSH Username: ssh-user SSH Authentication: Password/Key

Local port forwarding¶

Local Host: localhost Local Port: 3307 Remote Host: database-server Remote Port: 3306

SSH key authentication¶

Private Key: /path/to/private-key.pem Passphrase: key-passphrase

SSH tunnel command equivalent¶

ssh -L 3307:database-server:3306 ssh-user@ssh-server.example.com

Test SSH connection¶

ssh -T ssh-user@ssh-server.example.com ```_

Verbindungsvorlagen¶

json { "templates": [ { "name": "Local MySQL Development", "driver": "mysql8", "host": "localhost", "port": 3306, "database": "dev_db", "properties": { "useSSL": "false", "allowPublicKeyRetrieval": "true" } }, { "name": "Production PostgreSQL", "driver": "postgresql", "host": "prod-db.example.com", "port": 5432, "database": "production", "properties": { "ssl": "true", "sslmode": "require" } }, { "name": "AWS RDS MySQL", "driver": "mysql8", "host": "mydb.cluster-xyz.us-east-1.rds.amazonaws.com", "port": 3306, "database": "myapp", "properties": { "useSSL": "true", "requireSSL": "true" } } ] }_

SQL Editor¶

Basic SQL Operationen¶

```sql -- Data Definition Language (DDL) CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(100) NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_created_at ON users(created_at);

-- Data Manipulation Language (DML) INSERT INTO users (username, email, password_hash) VALUES ('john_doe', 'john@example.com', 'hashed_password_1'), ('jane_smith', 'jane@example.com', 'hashed_password_2'), ('bob_wilson', 'bob@example.com', 'hashed_password_3');

UPDATE users SET email = 'john.doe@example.com' WHERE username = 'john_doe';

DELETE FROM users WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Data Query Language (DQL) SELECT u.username, u.email, u.created_at FROM users u WHERE u.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY u.created_at DESC LIMIT 10;

-- Complex queries with joins SELECT u.username, u.email, COUNT(o.id) as order_count, SUM(o.total_amount) as total_spent, AVG(o.total_amount) as avg_order_value FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at >= '2023-01-01' GROUP BY u.id, u.username, u.email HAVING COUNT(o.id) > 0 ORDER BY total_spent DESC; ```_

SQL Editor Funktionen¶

```sql -- Auto-completion and syntax highlighting -- DBeaver provides intelligent code completion

-- Code formatting (Ctrl+Shift+F) SELECT u.username,u.email,o.total FROM users u JOIN orders o ON u.id=o.user_id;

-- Formatted result: SELECT u.username, u.email, o.total FROM users u JOIN orders o ON u.id = o.user_id;

-- Code templates and snippets -- Type 'sel' and press Tab for SELECT template -- Type 'ins' and press Tab for INSERT template -- Type 'upd' and press Tab for UPDATE template

-- SQL validation and error highlighting -- DBeaver highlights syntax errors in real-time

-- Multiple query execution -- Separate queries with semicolons SELECT COUNT() FROM users; SELECT COUNT() FROM orders; SELECT COUNT(*) FROM products;

-- Execute specific query (Ctrl+Enter) -- Execute all queries (Ctrl+Alt+Shift+X) -- Execute current statement (Alt+X) ```_

Query History und Bookmarks¶

```sql -- Query history is automatically saved -- Access via Window -> Show View -> Query History

-- Bookmark frequently used queries -- Right-click in SQL editor -> Add Bookmark

-- Saved queries examples: -- Name: "Active Users Count" SELECT COUNT(*) as active_users FROM users WHERE last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Name: "Top Products by Sales" SELECT p.name, SUM(oi.quantity) as total_sold, SUM(oi.quantity * oi.price) as total_revenue FROM products p JOIN order_items oi ON p.id = oi.product_id JOIN orders o ON oi.order_id = o.id WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH) GROUP BY p.id, p.name ORDER BY total_revenue DESC LIMIT 10;

-- Name: "Database Size Analysis" SELECT table_name, ROUND(((data_length + index_length) / 1024 / 1024), 2) as size_mb, table_rows FROM information_schema.tables WHERE table_schema = DATABASE() ORDER BY (data_length + index_length) DESC; ```_

SQL-Script Ausführung¶

```sql -- Execute SQL scripts from files -- File -> Open SQL Script -- Or drag and drop .sql files

-- Script execution options: -- 1. Execute entire script -- 2. Execute selected statements -- 3. Execute statement at cursor -- 4. Execute with transaction control

-- Transaction control in scripts START TRANSACTION;

INSERT INTO users (username, email, password_hash) VALUES ('test_user', 'test@example.com', 'hash');

UPDATE users SET email = 'updated@example.com' WHERE username = 'test_user';

-- Commit or rollback based on results COMMIT; -- ROLLBACK;

-- Script with error handling DELIMITER // CREATE PROCEDURE safe_user_update( IN p_user_id INT, IN p_email VARCHAR(100) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;

START TRANSACTION;

UPDATE users 
SET email = p_email, updated_at = NOW()
WHERE id = p_user_id;

COMMIT;

END // DELIMITER ; ```_

Datenansicht¶

Grid View Funktionen¶

```sql -- Data viewer provides multiple view modes: -- 1. Grid view (default) -- 2. Form view (record-by-record) -- 3. JSON view (for JSON data) -- 4. XML view (for XML data) -- 5. Image view (for binary image data)

-- Grid view features: -- - Sort by clicking column headers -- - Filter data using column filters -- - Resize columns -- - Freeze columns -- - Show/hide columns -- - Export selected data

-- Column operations: -- Right-click column header for options: -- - Sort Ascending/Descending -- - Filter -- - Hide Column -- - Resize to Fit -- - Copy Column Name -- - Set as Primary Key (for editing)

-- Row operations: -- Right-click row for options: -- - Edit Row -- - Duplicate Row -- - Delete Row -- - Copy Row -- - Export Row ```_

Datenfilterung¶

```sql -- Column filters (click filter icon in column header) -- Text filters: -- - Contains -- - Starts with -- - Ends with -- - Equals -- - Not equals -- - Regular expression

-- Numeric filters: -- - Equals -- - Not equals -- - Greater than -- - Less than -- - Between -- - Is null -- - Is not null

-- Date filters: -- - Equals -- - Before -- - After -- - Between -- - Today -- - This week -- - This month -- - This year

-- Advanced filtering with SQL WHERE clause -- Use the filter panel at bottom of data viewer WHERE created_at >= '2023-01-01' AND status = 'active' AND email LIKE '%@gmail.com'

-- Multiple column filters are combined with AND -- Use custom WHERE clause for OR conditions WHERE (status = 'active' OR status = 'pending') AND created_at >= '2023-01-01' ```_

Datenbearbeitung¶

```sql -- Enable data editing: -- 1. Ensure table has primary key -- 2. Enable edit mode (pencil icon in toolbar) -- 3. Double-click cell to edit

-- Inline editing features: -- - Edit cells directly in grid -- - Tab to move between cells -- - Enter to confirm changes -- - Escape to cancel changes -- - Auto-save or manual save options

-- Bulk operations: -- - Select multiple rows -- - Delete selected rows -- - Copy/paste between tables -- - Duplicate rows

-- Data validation: -- - Type checking (numeric, date, etc.) -- - Length validation -- - Foreign key constraints -- - Custom validation rules

-- Transaction control: -- - Auto-commit mode -- - Manual transaction mode -- - Rollback changes -- - Save changes

-- Example: Bulk update using SQL UPDATE users SET status = 'inactive' WHERE last_login < DATE_SUB(NOW(), INTERVAL 6 MONTH);

-- Example: Safe delete with confirmation DELETE FROM temp_data WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY) LIMIT 1000; ```_

```sql -- Navigation features: -- - Page through large result sets -- - Jump to specific page -- - Configure page size (100, 500, 1000, etc.) -- - Fetch all data (for smaller result sets)

-- Keyboard shortcuts: -- Ctrl+Home: Go to first row -- Ctrl+End: Go to last row -- Page Up/Down: Navigate pages -- Ctrl+F: Find in data -- F3: Find next -- Shift+F3: Find previous

-- Search and find: -- - Find text in current column -- - Find text in all columns -- - Case-sensitive search -- - Regular expression search -- - Replace functionality

-- Bookmarks and favorites: -- - Bookmark specific rows -- - Save filtered views -- - Quick access to bookmarked data

-- Data refresh: -- - Manual refresh (F5) -- - Auto-refresh intervals -- - Refresh on focus -- - Show refresh timestamp ```_

Browser¶

```sql -- Schema browser shows database structure: -- - Databases/Schemas -- - Tables -- - Views -- - Stored Procedures -- - Functions -- - Triggers -- - Indexes -- - Sequences -- - User-defined types

-- Table information: -- - Column definitions -- - Primary keys -- - Foreign keys -- - Indexes -- - Triggers -- - Constraints -- - Table statistics

-- View table DDL: -- Right-click table -> View DDL CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_email (email), INDEX idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- View table properties: -- - Row count -- - Table size -- - Index size -- - Creation date -- - Last modified date -- - Engine type (MySQL) -- - Character set -- - Collation ```_

Aufgabenbereich¶

```sql -- Table operations (right-click table): -- - Open in new tab -- - View data -- - Edit data -- - Generate SQL -- - Export data -- - Import data -- - Truncate table -- - Drop table -- - Rename table -- - Copy table structure

-- Generate SQL scripts: -- - CREATE TABLE statement -- - INSERT statements (with data) -- - SELECT statement -- - UPDATE statement template -- - DELETE statement template

-- Example generated INSERT: INSERT INTO users (username, email, password_hash) VALUES ('john_doe', 'john@example.com', 'hash1'), ('jane_smith', 'jane@example.com', 'hash2');

-- Column operations: -- - Add column -- - Modify column -- - Drop column -- - Rename column -- - Set as primary key -- - Create index

-- Index operations: -- - Create index -- - Drop index -- - Rebuild index -- - View index usage statistics

CREATE INDEX idx_users_status_created ON users(status, created_at);

-- Constraint operations: -- - Add foreign key -- - Drop constraint -- - Check constraint -- - Unique constraint

ALTER TABLE orders ADD CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id); ```_

Vergleich¶

```sql -- Compare database schemas: -- Tools -> Compare -> Schemas

-- Comparison features: -- - Structure differences -- - Data differences -- - Missing objects -- - Different object definitions

-- Generate synchronization script: -- - CREATE statements for missing objects -- - ALTER statements for differences -- - DROP statements for extra objects

-- Example sync script: -- Add missing table CREATE TABLE new_feature ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

-- Modify existing table ALTER TABLE users ADD COLUMN phone VARCHAR(20), ADD INDEX idx_phone (phone);

-- Update stored procedure DROP PROCEDURE IF EXISTS get_user_stats; DELIMITER // CREATE PROCEDURE get_user_stats() BEGIN SELECT COUNT(*) as total_users, COUNT(CASE WHEN status = 'active' THEN 1 END) as active_users, COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as new_users FROM users; END // DELIMITER ; ```_

ER-Diagramme¶

```sql -- Generate Entity-Relationship diagrams: -- Right-click database -> Generate ER Diagram

-- ER diagram features: -- - Visual table relationships -- - Primary/foreign key connections -- - Column data types -- - Index visualization -- - Constraint display

-- Diagram customization: -- - Layout algorithms (hierarchical, circular, etc.) -- - Show/hide columns -- - Show/hide data types -- - Color coding -- - Export as image (PNG, SVG, PDF)

-- Reverse engineering: -- - Generate diagrams from existing database -- - Include/exclude specific tables -- - Filter by schema -- - Group related tables

-- Forward engineering: -- - Design database visually -- - Generate DDL from diagram -- - Validate relationships -- - Export schema script ```_

Querausführung¶

Ausführungspläne¶

```sql -- View query execution plans: -- Enable "Show execution plan" in SQL editor -- Or use EXPLAIN statement

-- MySQL execution plan: EXPLAIN SELECT u.username, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at >= '2023-01-01' GROUP BY u.id, u.username;

-- PostgreSQL execution plan: EXPLAIN (ANALYZE, BUFFERS) SELECT u.username, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at >= '2023-01-01' GROUP BY u.id, u.username;

-- SQL Server execution plan: SET SHOWPLAN_ALL ON; SELECT u.username, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at >= '2023-01-01' GROUP BY u.id, u.username;

-- Execution plan analysis: -- - Table scans vs index seeks -- - Join algorithms (nested loop, hash, merge) -- - Sort operations -- - Filter operations -- - Cost estimates -- - Row count estimates ```_

Leistungsüberwachung¶

```sql -- Query performance metrics: -- - Execution time -- - Rows affected -- - Rows fetched -- - Memory usage -- - CPU usage

-- Enable query profiling: -- Preferences -> Database -> SQL Editor -> Show query info

-- Performance optimization tips: -- 1. Use appropriate indexes CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 2. Limit result sets SELECT * FROM large_table LIMIT 1000;

-- 3. Use EXISTS instead of IN for subqueries SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id );

-- 4. Avoid SELECT * SELECT id, username, email FROM users;

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

-- Query timeout settings: -- Set query timeout in connection properties -- Default: 20 seconds -- Long-running queries: 300+ seconds -- Batch operations: 3600+ seconds ```_

Batch Operationen¶

```sql -- Execute multiple statements: -- Separate with semicolons -- Use transaction control

START TRANSACTION;

-- Batch insert INSERT INTO users (username, email, password_hash) VALUES ('user1', 'user1@example.com', 'hash1'), ('user2', 'user2@example.com', 'hash2'), ('user3', 'user3@example.com', 'hash3');

-- Batch update UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

-- Batch delete DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

COMMIT;

-- Bulk data operations: -- Use LOAD DATA for large imports (MySQL) LOAD DATA INFILE '/path/to/data.csv' INTO TABLE temp_import FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

-- Use COPY for bulk operations (PostgreSQL) COPY users(username, email, password_hash) FROM '/path/to/users.csv' DELIMITER ',' CSV HEADER;

-- Use BULK INSERT for SQL Server BULK INSERT temp_import FROM '/path/to/data.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2 ); ```_

Query Debugging¶

```sql -- Debug complex queries step by step:

-- Step 1: Test base query SELECT COUNT(*) FROM users WHERE created_at >= '2023-01-01';

-- Step 2: Add joins one by one SELECT COUNT(*) FROM users u JOIN orders o ON u.id = o.user_id WHERE u.created_at >= '2023-01-01';

-- Step 3: Add grouping and aggregation SELECT u.username, COUNT(o.id) as order_count FROM users u JOIN orders o ON u.id = o.user_id WHERE u.created_at >= '2023-01-01' GROUP BY u.id, u.username;

-- Step 4: Add filtering and sorting SELECT u.username, COUNT(o.id) as order_count, SUM(o.total_amount) as total_spent FROM users u JOIN orders o ON u.id = o.user_id WHERE u.created_at >= '2023-01-01' GROUP BY u.id, u.username HAVING COUNT(o.id) >= 5 ORDER BY total_spent DESC;

-- Use LIMIT for testing large queries SELECT * FROM large_table LIMIT 10;

-- Check intermediate results SELECT DISTINCT status FROM orders; -- Check available values SELECT MIN(created_at), MAX(created_at) FROM orders; -- Check date range ```_

Datenexport/Import¶

Exportformate¶

```sql -- Supported export formats: -- - CSV (Comma-separated values) -- - TSV (Tab-separated values) -- - Excel (XLSX) -- - JSON -- - XML -- - SQL INSERT statements -- - HTML -- - PDF (via print)

-- Export configuration: -- File -> Export Data -- Or right-click table -> Export Data

-- CSV export options: { "delimiter": ",", "quote_char": "\"", "escape_char": "\", "line_separator": "\n", "encoding": "UTF-8", "include_header": true, "quote_all_values": false, "null_value": "", "date_format": "yyyy-MM-dd", "timestamp_format": "yyyy-MM-dd HHss" }

-- JSON export options: { "format": "array", // array or object "pretty_print": true, "include_metadata": false, "date_format": "ISO", "encoding": "UTF-8" }

-- SQL export options: { "include_ddl": true, "include_data": true, "batch_size": 1000, "use_qualified_names": true, "include_comments": true } ```_

Daten für die Einfuhr¶

```sql -- Import data from various sources: -- File -> Import Data -- Or right-click table -> Import Data

-- CSV import configuration: { "file_path": "/path/to/data.csv", "delimiter": ",", "quote_char": "\"", "escape_char": "\", "encoding": "UTF-8", "header_row": 1, "skip_rows": 0, "null_values": ["", "NULL", "null"], "trim_values": true, "empty_string_as_null": true }

-- Column mapping: { "mappings": [ {"source": "user_name", "target": "username"}, {"source": "email_address", "target": "email"}, {"source": "registration_date", "target": "created_at"} ], "type_conversions": [ {"column": "created_at", "type": "timestamp", "format": "MM/dd/yyyy"}, {"column": "age", "type": "integer"}, {"column": "salary", "type": "decimal", "precision": 10, "scale": 2} ] }

-- Import modes: -- - INSERT: Add new records -- - UPDATE: Update existing records -- - UPSERT: Insert or update based on key -- - REPLACE: Replace existing records

-- Error handling: { "on_error": "skip", // skip, stop, or log "max_errors": 100, "log_errors": true, "error_log_file": "/path/to/errors.log" } ```_

Bulk Data Operationen¶

```sql -- Large dataset import strategies:

-- 1. Batch processing { "batch_size": 1000, "commit_frequency": 10000, "use_transactions": true, "parallel_threads": 4 }

-- 2. Disable constraints during import ALTER TABLE target_table DISABLE KEYS; -- Import data ALTER TABLE target_table ENABLE KEYS;

-- 3. Use staging tables CREATE TABLE staging_users LIKE users; -- Import to staging table first -- Validate and clean data -- Transfer to production table INSERT INTO users SELECT * FROM staging_users WHERE validation_check();

-- 4. Optimize for bulk operations SET autocommit = 0; SET unique_checks = 0; SET foreign_key_checks = 0; -- Perform bulk operations SET foreign_key_checks = 1; SET unique_checks = 1; SET autocommit = 1;

-- Progress monitoring: -- DBeaver shows import progress -- - Records processed -- - Processing speed (records/second) -- - Estimated time remaining -- - Error count -- - Success rate ```_

Datensynchronisation¶

```sql -- Synchronize data between databases: -- Tools -> Database -> Compare/Synchronize

-- Sync configuration: { "source": { "connection": "source_db", "schema": "production", "tables": ["users", "orders", "products"] }, "target": { "connection": "target_db", "schema": "staging", "tables": ["users", "orders", "products"] }, "options": { "compare_data": true, "compare_structure": true, "generate_script": true, "execute_immediately": false } }

-- Sync strategies: -- 1. Full synchronization -- 2. Incremental synchronization -- 3. Bidirectional synchronization -- 4. One-way synchronization

-- Generated sync script example: -- Update existing records UPDATE target.users t JOIN source.users s ON t.id = s.id SET t.email = s.email, t.updated_at = s.updated_at WHERE t.email != s.email;

-- Insert new records INSERT INTO target.users (id, username, email, created_at) SELECT s.id, s.username, s.email, s.created_at FROM source.users s LEFT JOIN target.users t ON s.id = t.id WHERE t.id IS NULL;

-- Delete removed records DELETE t FROM target.users t LEFT JOIN source.users s ON t.id = s.id WHERE s.id IS NULL; ```_

Datenbankverwaltung¶

Benutzermanagement¶

```sql -- User and privilege management: -- Navigate to Security -> Users in schema browser

-- Create new user (MySQL): CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password'; CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';

-- Grant privileges: GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'localhost'; GRANT ALL PRIVILEGES ON myapp.* TO 'admin_user'@'localhost';

-- Create role and assign to user: CREATE ROLE 'app_role'; GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_role'; GRANT 'app_role' TO 'app_user'@'localhost';

-- PostgreSQL user management: CREATE USER app_user WITH PASSWORD 'secure_password'; CREATE ROLE app_role; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role; GRANT app_role TO app_user;

-- SQL Server user management: CREATE LOGIN app_user WITH PASSWORD = 'SecurePassword123!'; USE myapp; CREATE USER app_user FOR LOGIN app_user; ALTER ROLE db_datareader ADD MEMBER app_user; ALTER ROLE db_datawriter ADD MEMBER app_user;

-- View user privileges: SHOW GRANTS FOR 'app_user'@'localhost';

-- Revoke privileges: REVOKE INSERT, UPDATE ON myapp.* FROM 'app_user'@'localhost';

-- Drop user: DROP USER 'app_user'@'localhost'; ```_

Backup und Wiederherstellung¶

```sql -- Database backup using DBeaver: -- Right-click database -> Tools -> Backup

-- Backup options: { "backup_type": "full", // full, incremental, differential "include_data": true, "include_structure": true, "include_procedures": true, "include_triggers": true, "include_views": true, "compression": "gzip", "output_format": "sql" // sql, binary, custom }

-- MySQL backup (mysqldump): mysqldump -u username -p --single-transaction --routines --triggers myapp > backup.sql

-- PostgreSQL backup (pg_dump): pg_dump -U username -h localhost -d myapp -f backup.sql

-- SQL Server backup: BACKUP DATABASE myapp TO DISK = 'C:\Backups\myapp_backup.bak' WITH FORMAT, COMPRESSION;

-- Restore database: -- Right-click database -> Tools -> Restore

-- MySQL restore: mysql -u username -p myapp < backup.sql

-- PostgreSQL restore: psql -U username -h localhost -d myapp -f backup.sql

-- SQL Server restore: RESTORE DATABASE myapp FROM DISK = 'C:\Backups\myapp_backup.bak' WITH REPLACE;

-- Automated backup script:

!/bin/bash¶

DATE=$(date +%Y%m%d_%H%M%S) BACKUP_DIR="/backups" DB_NAME="myapp"

mysqldump -u backup_user -p$BACKUP_PASSWORD \ --single-transaction \ --routines \ --triggers \ $DB_NAME | gzip > \(BACKUP_DIR/$_\).sql.gz

Keep only last 7 days of backups¶

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

Leistungsüberwachung¶

```sql -- Monitor database performance: -- Window -> Show View -> Database Monitor

-- Key performance metrics: -- - Active connections -- - Query execution time -- - Lock waits -- - Buffer cache hit ratio -- - Disk I/O statistics -- - Memory usage

-- MySQL performance queries: -- Show processlist SHOW PROCESSLIST;

-- Show status variables SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Slow_queries'; SHOW STATUS LIKE 'Questions';

-- Show engine status SHOW ENGINE INNODB STATUS;

-- Performance schema queries SELECT event_name, count_star, sum_timer_wait/1000000000 as total_time_sec, avg_timer_wait/1000000000 as avg_time_sec FROM performance_schema.events_statements_summary_global_by_event_name WHERE count_star > 0 ORDER BY sum_timer_wait DESC LIMIT 10;

-- PostgreSQL performance queries: -- Active queries SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- Database statistics SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted FROM pg_stat_database;

-- Table statistics SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables ORDER BY seq_scan DESC; ```_

Index Management¶

```sql -- Index analysis and optimization: -- Navigate to table -> Indexes tab

-- Create indexes: CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_orders_user_date ON orders(user_id, created_at); CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite indexes: CREATE INDEX idx_products_category_price ON products(category, price); CREATE INDEX idx_orders_status_date ON orders(status, created_at DESC);

-- Partial indexes (PostgreSQL): CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Functional indexes: CREATE INDEX idx_users_lower_email ON users(LOWER(email)); CREATE INDEX idx_orders_year ON orders(YEAR(created_at));

-- Index usage analysis (MySQL): SELECT t.table_name, s.index_name, s.column_name, s.cardinality, ROUND(((s.cardinality / t.table_rows) * 100), 2) AS selectivity FROM information_schema.statistics s JOIN information_schema.tables t ON s.table_name = t.table_name WHERE s.table_schema = DATABASE() AND t.table_schema = DATABASE() ORDER BY selectivity DESC;

-- Unused indexes (PostgreSQL): SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY schemaname, tablename;

-- Index size analysis: SELECT table_name, index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb FROM mysql.innodb_index_stats WHERE stat_name = 'size' ORDER BY stat_value DESC;

-- Drop unused indexes: DROP INDEX idx_unused_index ON table_name;

-- Rebuild indexes: ALTER TABLE table_name ENGINE=InnoDB; -- MySQL REINDEX TABLE table_name; -- PostgreSQL ```_

Visual Query Build¶

Das ist nicht möglich. Schnittstelle¶

```sql -- Access Visual Query Builder: -- SQL Editor -> Open Visual Query Builder -- Or click "Design" tab in SQL Editor

-- Query builder components: -- 1. Table/View selector -- 2. Join configuration -- 3. Column selection -- 4. WHERE conditions -- 5. GROUP BY clauses -- 6. HAVING conditions -- 7. ORDER BY specification -- 8. LIMIT/OFFSET settings

-- Example query built visually: -- Tables: users, orders, order_items, products -- Joins: -- users.id = orders.user_id -- orders.id = order_items.order_id -- order_items.product_id = products.id

-- Generated SQL: SELECT u.username, u.email, COUNT(DISTINCT o.id) as order_count, SUM(oi.quantity * oi.price) as total_spent, AVG(oi.price) as avg_item_price FROM users u INNER JOIN orders o ON u.id = o.user_id INNER JOIN order_items oi ON o.id = oi.order_id INNER JOIN products p ON oi.product_id = p.id WHERE o.created_at >= '2023-01-01' AND o.status = 'completed' GROUP BY u.id, u.username, u.email HAVING COUNT(DISTINCT o.id) >= 5 ORDER BY total_spent DESC LIMIT 100; ```_

Konfigurieren beitreten¶

```sql -- Join types available in visual builder: -- 1. INNER JOIN -- 2. LEFT JOIN (LEFT OUTER JOIN) -- 3. RIGHT JOIN (RIGHT OUTER JOIN) -- 4. FULL JOIN (FULL OUTER JOIN) -- 5. CROSS JOIN

-- Join condition types: -- 1. Equality (=) -- 2. Inequality (!=, <>, <, >, <=, >=) -- 3. LIKE pattern matching -- 4. IN/NOT IN -- 5. EXISTS/NOT EXISTS

-- Complex join example: SELECT u.username, p.name as product_name, oi.quantity, oi.price FROM users u INNER JOIN orders o ON u.id = o.user_id INNER JOIN order_items oi ON o.id = oi.order_id INNER JOIN products p ON oi.product_id = p.id LEFT JOIN product_reviews pr ON p.id = pr.product_id AND pr.user_id = u.id WHERE o.status = 'completed' AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Self-join example: SELECT e1.name as employee, e2.name as manager FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- Multiple table join with aggregation: SELECT c.name as category, COUNT(p.id) as product_count, AVG(p.price) as avg_price, SUM(oi.quantity) as total_sold FROM categories c LEFT JOIN products p ON c.id = p.category_id LEFT JOIN order_items oi ON p.id = oi.product_id LEFT JOIN orders o ON oi.order_id = o.id WHERE o.status = 'completed' OR o.status IS NULL GROUP BY c.id, c.name ORDER BY total_sold DESC NULLS LAST; ```_

Zustandsbau¶

```sql -- WHERE condition builder supports: -- 1. Simple conditions -- 2. Complex conditions with AND/OR -- 3. Nested conditions with parentheses -- 4. Subqueries -- 5. Functions and expressions

-- Simple conditions: WHERE users.status = 'active' AND users.created_at >= '2023-01-01' AND users.email LIKE '%@gmail.com'

-- Complex conditions with grouping: WHERE (users.status = 'active' OR users.status = 'premium') AND users.created_at >= '2023-01-01' AND (users.country = 'US' OR users.country = 'CA')

-- Subquery conditions: WHERE users.id IN ( SELECT DISTINCT user_id FROM orders WHERE total_amount > 1000 )

-- EXISTS conditions: WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = users.id AND o.status = 'completed' )

-- Function-based conditions: WHERE YEAR(users.created_at) = 2023 AND MONTH(users.created_at) >= 6 AND LENGTH(users.username) > 5

-- NULL handling: WHERE users.phone IS NOT NULL AND users.last_login IS NOT NULL AND COALESCE(users.middle_name, '') != ''

-- Date range conditions: WHERE orders.created_at BETWEEN '2023-01-01' AND '2023-12-31' AND orders.updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)

-- Pattern matching: WHERE products.name LIKE '%laptop%' AND products.description REGEXP 'gaming|performance' AND products.sku NOT LIKE 'DISC-%' ```_

Aggregation und Gruppierung¶

```sql -- GROUP BY builder supports: -- 1. Single column grouping -- 2. Multiple column grouping -- 3. Expression-based grouping -- 4. Date/time grouping functions

-- Basic grouping: SELECT category, COUNT(*) as product_count, AVG(price) as avg_price, MIN(price) as min_price, MAX(price) as max_price FROM products GROUP BY category;

-- Multiple column grouping: SELECT category, brand, COUNT(*) as product_count, SUM(stock_quantity) as total_stock FROM products GROUP BY category, brand ORDER BY category, brand;

-- Date-based grouping: SELECT YEAR(created_at) as year, MONTH(created_at) as month, COUNT(*) as order_count, SUM(total_amount) as monthly_revenue FROM orders WHERE created_at >= '2023-01-01' GROUP BY YEAR(created_at), MONTH(created_at) ORDER BY year, month;

-- Expression-based grouping: SELECT CASE WHEN price < 100 THEN 'Budget' WHEN price < 500 THEN 'Mid-range' ELSE 'Premium' END as price_category, COUNT(*) as product_count, AVG(price) as avg_price FROM products GROUP BY CASE WHEN price < 100 THEN 'Budget' WHEN price < 500 THEN 'Mid-range' ELSE 'Premium' END;

-- HAVING conditions: SELECT user_id, COUNT() as order_count, SUM(total_amount) as total_spent FROM orders WHERE status = 'completed' GROUP BY user_id HAVING COUNT() >= 5 AND SUM(total_amount) > 1000 ORDER BY total_spent DESC;

-- Window functions (advanced): SELECT user_id, order_date, total_amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) as order_sequence, SUM(total_amount) OVER (PARTITION BY user_id ORDER BY order_date) as running_total, LAG(total_amount) OVER (PARTITION BY user_id ORDER BY order_date) as previous_order_amount FROM orders WHERE status = 'completed' ORDER BY user_id, order_date; ```_

Datentransfer¶

Datenbank Migration¶

```sql -- Database-to-database transfer: -- Tools -> Database -> Transfer Data

-- Transfer configuration: { "source": { "connection": "source_mysql", "database": "production", "tables": ["users", "orders", "products"] }, "target": { "connection": "target_postgresql", "database": "staging", "create_tables": true, "drop_existing": false }, "options": { "transfer_data": true, "transfer_structure": true, "batch_size": 1000, "use_transactions": true, "parallel_jobs": 4 } }

-- Data type mapping (MySQL to PostgreSQL): { "type_mappings": [ {"source": "INT AUTO_INCREMENT", "target": "SERIAL"}, {"source": "VARCHAR(255)", "target": "VARCHAR(255)"}, {"source": "TEXT", "target": "TEXT"}, {"source": "DATETIME", "target": "TIMESTAMP"}, {"source": "TINYINT(1)", "target": "BOOLEAN"}, {"source": "DECIMAL(10,2)", "target": "NUMERIC(10,2)"} ] }

-- Generated migration script: -- Create target tables CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

-- Transfer data with transformation INSERT INTO target.users (username, email, created_at) SELECT username, email, created_at FROM source.users WHERE status = 'active';

-- Handle data conflicts: INSERT INTO target.users (username, email, created_at) SELECT CASE WHEN EXISTS (SELECT 1 FROM target.users WHERE username = s.username) THEN CONCAT(s.username, 'migrated') ELSE s.username END, s.email, s.created_at FROM source.users s; ```

Cross-Platform Datentransfer¶

```sql -- Transfer between different database systems:

-- Oracle to MySQL: { "source": "Oracle 19c", "target": "MySQL 8.0", "challenges": [ "Date format differences", "Sequence to AUTO_INCREMENT", "PL/SQL to MySQL procedures", "Data type compatibility" ], "solutions": [ "Use DATE_FORMAT() for date conversion", "Convert sequences to AUTO_INCREMENT", "Rewrite stored procedures", "Map Oracle types to MySQL equivalents" ] }

-- SQL Server to PostgreSQL: { "source": "SQL Server 2019", "target": "PostgreSQL 15", "type_mappings": [ {"source": "NVARCHAR(MAX)", "target": "TEXT"}, {"source": "UNIQUEIDENTIFIER", "target": "UUID"}, {"source": "DATETIME2", "target": "TIMESTAMP"}, {"source": "BIT", "target": "BOOLEAN"}, {"source": "MONEY", "target": "NUMERIC(19,4)"} ] }

-- Data transformation during transfer: -- Source (SQL Server): SELECT Id, FirstName + ' ' + LastName as FullName, CONVERT(VARCHAR, BirthDate, 120) as BirthDate, CASE WHEN IsActive = 1 THEN 'true' ELSE 'false' END as IsActive FROM Users;

-- Target (PostgreSQL): INSERT INTO users (id, full_name, birth_date, is_active) VALUES (?, ?, ?::timestamp, ?::boolean);

-- Handle encoding issues: { "encoding": { "source": "latin1", "target": "utf8", "conversion": "iconv -f latin1 -t utf8" } } ```_

ETL Operationen¶

```sql -- Extract, Transform, Load operations:

-- Extract phase: SELECT customer_id, order_date, product_id, quantity, unit_price, quantity * unit_price as line_total FROM raw_orders WHERE order_date >= '2023-01-01';

-- Transform phase: SELECT o.customer_id, c.customer_name, o.order_date, p.product_name, p.category, o.quantity, o.unit_price, o.line_total, CASE WHEN o.line_total > 1000 THEN 'High Value' WHEN o.line_total > 100 THEN 'Medium Value' ELSE 'Low Value' END as order_category, ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date) as order_sequence FROM ( SELECT customer_id, order_date, product_id, quantity, unit_price, quantity * unit_price as line_total FROM raw_orders WHERE order_date >= '2023-01-01' ) o JOIN customers c ON o.customer_id = c.id JOIN products p ON o.product_id = p.id;

-- Load phase: INSERT INTO analytics.order_facts ( customer_id, customer_name, order_date, product_name, category, quantity, unit_price, line_total, order_category, order_sequence ) SELECT customer_id, customer_name, order_date, product_name, category, quantity, unit_price, line_total, order_category, order_sequence FROM transformed_orders;

-- Data quality checks: -- Check for duplicates SELECT customer_id, order_date, product_id, COUNT() FROM order_facts GROUP BY customer_id, order_date, product_id HAVING COUNT() > 1;

-- Check for missing references SELECT DISTINCT customer_id FROM order_facts WHERE customer_id NOT IN (SELECT id FROM customers);

-- Check data ranges SELECT MIN(order_date) as earliest_date, MAX(order_date) as latest_date, MIN(line_total) as min_total, MAX(line_total) as max_total, COUNT(*) as total_records FROM order_facts; ```_

Erweiterungen und Plugins¶

Datenbank Driver Management¶

```sql -- DBeaver supports numerous database drivers: -- - MySQL/MariaDB -- - PostgreSQL -- - SQLite -- - Oracle -- - SQL Server -- - DB2 -- - Sybase -- - Firebird -- - H2 -- - HSQLDB -- - Derby -- - MongoDB -- - Cassandra -- - Redis -- - ClickHouse -- - Snowflake -- - BigQuery -- - And many more...

-- Driver installation: -- Database -> Driver Manager -- Download/Update drivers automatically -- Or manually add JAR files

-- Custom driver configuration: { "driver_name": "Custom MySQL", "driver_class": "com.mysql.cj.jdbc.Driver", "jar_files": [ "/path/to/mysql-connector-java-8.0.33.jar" ], "url_template": "jdbc:mysql://{host}[:{port}]/[{database}]", "default_port": 3306, "supports_transactions": true, "supports_multiple_results": true } ```_

Plugin Ecosystem¶

```sql -- Available DBeaver plugins: -- 1. Git integration -- 2. ERD (Entity Relationship Diagrams) -- 3. Data visualization -- 4. Query profiling -- 5. Database documentation -- 6. Code formatting -- 7. Schema comparison -- 8. Data masking -- 9. Backup/restore tools -- 10. Performance monitoring

-- Install plugins: -- Help -> Install New Software -- Or download from DBeaver marketplace

-- Popular plugins:

-- 1. DBeaver PRO features: -- - NoSQL databases support -- - Cloud databases integration -- - Advanced data visualization -- - Query profiling and optimization -- - Data masking and anonymization -- - Advanced export/import formats

-- 2. Git integration: -- - Version control for SQL scripts -- - Collaborative development -- - Change tracking -- - Branch management

-- 3. ERD plugin: -- - Visual database design -- - Automatic diagram generation -- - Relationship visualization -- - Export to various formats

-- 4. Documentation generator: -- - Automatic schema documentation -- - HTML/PDF reports -- - Table and column descriptions -- - Relationship documentation ```_

Kundenspezifische Erweiterungen¶

```java // Create custom DBeaver extension: // 1. Set up Eclipse plugin development environment // 2. Create new plugin project // 3. Implement DBeaver extension points

// Example: Custom data export extension public class CustomExportExtension implements IDataExporter {

@Override
public void exportData(IDataExportSite site) throws DBException {
    // Custom export logic
    IResultSet resultSet = site.getResultSet();

    while (resultSet.nextRow()) {
        Object[] row = resultSet.getRowData();
        // Process row data
        processRow(row);
    }
}

private void processRow(Object[] row) {
    // Custom row processing
    for (Object value : row) {
        // Transform and export value
        String transformedValue = transformValue(value);
        writeToOutput(transformedValue);
    }
}

}

// Plugin manifest (plugin.xml):

```_

Konfiguration und Anpassung¶

```sql -- DBeaver configuration files: -- Windows: %APPDATA%\DBeaverData\workspace6.metadata -- macOS: ~/Library/DBeaverData/workspace6/.metadata
-- Linux: ~/.local/share/DBeaverData/workspace6/.metadata

-- Key configuration files: -- - .metadata/.plugins/org.eclipse.core.runtime/.settings/ -- - connections.xml (connection configurations) -- - drivers.xml (driver configurations) -- - preferences.properties (user preferences)

-- Workspace customization: { "sql_editor": { "auto_completion": true, "syntax_highlighting": true, "line_numbers": true, "word_wrap": false, "tab_size": 4, "font_size": 12, "theme": "dark" }, "data_viewer": { "max_rows": 200, "auto_refresh": false, "show_row_numbers": true, "null_value_display": "", "date_format": "yyyy-MM-dd HHss" }, "performance": { "result_set_max_size": 100000, "query_timeout": 20000, "connection_pool_size": 10, "memory_limit": "2G" } }

-- Export/import configuration: -- File -> Export -> General -> Preferences -- File -> Import -> General -> Preferences

-- Team configuration sharing: -- Export workspace settings -- Share connection templates -- Standardize formatting rules -- Common driver configurations ```_

Best Practices¶

Leistungsoptimierung¶

```sql -- 1. Connection management: -- Use connection pooling -- Close unused connections -- Configure appropriate timeouts -- Monitor connection usage

-- Connection pool configuration: { "initial_size": 2, "max_active": 10, "max_idle": 5, "min_idle": 1, "max_wait": 10000, "validation_query": "SELECT 1", "test_on_borrow": true, "test_while_idle": true }

-- 2. Query optimization: -- Use LIMIT for large result sets SELECT * FROM large_table LIMIT 1000;

-- Use appropriate indexes CREATE INDEX idx_orders_date_status ON orders(order_date, status);

-- Avoid SELECT * in production queries SELECT id, name, email FROM users WHERE status = 'active';

-- Use EXISTS instead of IN for subqueries SELECT * FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'completed' );

-- 3. Memory management: -- Configure appropriate result set limits -- Use streaming for large exports -- Monitor memory usage -- Close result sets properly

-- 4. Network optimization: -- Use compression for remote connections -- Minimize data transfer -- Use appropriate fetch sizes -- Enable connection keep-alive ```_

Sicherheit Best Practices¶

```sql -- 1. Connection security: -- Use SSL/TLS for remote connections -- Implement proper authentication -- Use least privilege principle -- Regular password rotation

-- SSL connection configuration: { "ssl_mode": "require", "ssl_cert": "/path/to/client-cert.pem", "ssl_key": "/path/to/client-key.pem", "ssl_ca": "/path/to/ca-cert.pem", "ssl_verify_server_cert": true }

-- 2. Access control: -- Create application-specific users -- Grant minimal required privileges -- Use roles for permission management -- Regular access reviews

-- Example secure user setup: CREATE USER 'app_readonly'@'%' IDENTIFIED BY 'SecurePassword123!'; GRANT SELECT ON myapp.* TO 'app_readonly'@'%';

CREATE USER 'app_writer'@'%' IDENTIFIED BY 'SecurePassword456!'; GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_writer'@'%';

-- 3. Data protection: -- Mask sensitive data in development -- Use parameterized queries -- Implement audit logging -- Regular security updates

-- Data masking example: SELECT id, username, CONCAT(LEFT(email, 3), '@', SUBSTRING_INDEX(email, '@', -1)) as masked_email, '-***-' + RIGHT(phone, 4) as masked_phone FROM users;

-- 4. Backup security: -- Encrypt backup files -- Secure backup storage -- Test restore procedures -- Document recovery processes ```_

Entwicklungs-Workflow¶

```sql -- 1. Environment management: -- Separate development, staging, production -- Use consistent naming conventions -- Version control for database schemas -- Automated deployment processes

-- Environment configuration: { "development": { "host": "dev-db.internal", "database": "myapp_dev", "auto_commit": true, "query_timeout": 30 }, "staging": { "host": "staging-db.internal", "database": "myapp_staging", "auto_commit": false, "query_timeout": 60 }, "production": { "host": "prod-db.internal", "database": "myapp_prod", "auto_commit": false, "query_timeout": 120, "read_only": true } }

-- 2. Code organization: -- Use folders for script organization -- Consistent naming conventions -- Document complex queries -- Version control integration

-- Script organization: /sql-scripts/ /migrations/ 001_create_users_table.sql 002_add_email_index.sql 003_create_orders_table.sql /queries/ /reports/ monthly_sales_report.sql user_activity_report.sql /maintenance/ cleanup_old_logs.sql rebuild_indexes.sql /procedures/ calculate_user_stats.sql process_orders.sql

-- 3. Testing practices: -- Test queries on sample data -- Validate performance on production-size data -- Use transactions for testing -- Document test cases

-- Testing template: START TRANSACTION;

-- Test data setup INSERT INTO test_users (username, email) VALUES ('test_user_1', 'test1@example.com'), ('test_user_2', 'test2@example.com');

-- Execute test query SELECT COUNT(*) FROM test_users WHERE email LIKE '%@example.com';

-- Verify results -- Expected: 2 rows

-- Cleanup ROLLBACK;

-- 4. Documentation: -- Document database schema -- Explain complex business logic -- Maintain change logs -- Create user guides

-- Schema documentation template: /* Table: users Purpose: Store user account information Created: 2023-01-01 Last Modified: 2023-12-01

Columns: - id: Primary key, auto-increment - username: Unique username, 3-50 characters - email: User email address, must be valid format - password_hash: Bcrypt hashed password - status: Account status (active, inactive, suspended) - created_at: Account creation timestamp - updated_at: Last modification timestamp

Indexes: - PRIMARY KEY (id) - UNIQUE KEY uk_username (username) - KEY idx_email (email) - KEY idx_status_created (status, created_at)

Related Tables: - orders (user_id references users.id) - user_profiles (user_id references users.id) */ ```_

Wartung und Überwachung¶

```sql -- 1. Regular maintenance tasks: -- Monitor database growth -- Analyze query performance -- Update statistics -- Rebuild indexes when needed

-- Database size monitoring: SELECT table_schema as database_name, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size_mb FROM information_schema.tables GROUP BY table_schema ORDER BY size_mb DESC;

-- Table growth monitoring: SELECT table_name, table_rows, ROUND(((data_length + index_length) / 1024 / 1024), 2) as size_mb, ROUND((data_length / 1024 / 1024), 2) as data_mb, ROUND((index_length / 1024 / 1024), 2) as index_mb FROM information_schema.tables WHERE table_schema = DATABASE() ORDER BY (data_length + index_length) DESC;

-- 2. Performance monitoring: -- Track slow queries -- Monitor connection usage -- Analyze index effectiveness -- Review execution plans

-- Slow query analysis: SELECT query_time, lock_time, rows_sent, rows_examined, sql_text FROM mysql.slow_log WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY query_time DESC LIMIT 10;

-- 3. Backup verification: -- Test restore procedures -- Verify backup integrity -- Monitor backup completion -- Document recovery procedures

-- Backup verification script:

!/bin/bash¶

BACKUP_FILE="/backups/myapp_$(date +%Y%m%d).sql" TEST_DB="myapp_restore_test"

Create test database¶

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS $TEST_DB;"

Restore backup to test database¶

mysql -u root -p $TEST_DB < $BACKUP_FILE

Verify restore¶

TABLES=$(mysql -u root -p -e "USE $TEST_DB; SHOW TABLES;" | wc -l) echo "Restored $TABLES tables"

Cleanup¶

mysql -u root -p -e "DROP DATABASE $TEST_DB;"

-- 4. Capacity planning: -- Monitor growth trends -- Plan for scaling -- Optimize resource usage -- Prepare for peak loads

-- Growth trend analysis: SELECT DATE(created_at) as date, COUNT() as daily_records, SUM(COUNT()) OVER (ORDER BY DATE(created_at)) as cumulative_records FROM large_table WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY DATE(created_at) ORDER BY date; ```_

--

Zusammenfassung¶

DBeaver ist ein umfassendes Datenbankmanagement-Tool, das eine einheitliche Schnittstelle für die Arbeit mit mehreren Datenbanksystemen bietet. Dieses Cheatsheet umfasst die wesentlichen Merkmale und Best Practices für eine effektive Datenbankentwicklung und -verwaltung.

Key Strengths: - Universal Support*: Arbeitet mit nahezu allen großen Datenbanksystemen - **Rich Feature Set: SQL-Bearbeitung, Datenvisualisierung, Schemaverwaltung - Cross-Platform*: Verfügbar unter Windows, macOS und Linux - **Extensible*: Plugin-Architektur für zusätzliche Funktionalität - ** Gratis und Open Source: Gemeinschaftsausgabe ohne Kosten

Best Use Cases: - Datenbankentwicklung und Verwaltung - Datenanalyse und Berichterstattung - Schemagestaltung und Migration - Multi-Datenbank Umweltmanagement - Teamkooperation für Datenbankprojekte

Importierte Überlegungen: - Leistung kann mit sehr großen Ergebnissätzen beeinflusst werden - Einige erweiterte Funktionen erfordern DBeaver PRO Lizenz - Speichernutzung sollte für große Operationen überwacht werden - Regelmäßige Updates für die neueste Datenbank-Treiberunterstützung empfohlen - Richtige Sicherheitskonfiguration für den Produktionseinsatz

Durch die in diesem Cheatsheet skizzierten Praktiken und Techniken können Sie DBeaver effektiv nutzen, um Datenbanken zu verwalten und gleichzeitig die Leistung, Sicherheit und Produktivität in Ihren Datenbankbetrieben zu erhalten.

<= <= <= <================================================================================= 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(); }

DBeaver Cheatsheet¶

DBeaver - Universal Datenbank Tool

Inhaltsverzeichnis¶

Installation¶

Windows Installation¶

Datenbankverbindungen¶

Verbindungskonfiguration¶

Erweiterte Verbindungseinstellungen¶

Connection properties file¶

MySQL example¶

Connection pool settings¶

SSL settings¶

Connection timeout settings¶

Additional parameters¶

SSH Tunnel Konfiguration¶

SSH tunnel settings in DBeaver¶

Local port forwarding¶

SSH key authentication¶

SSH tunnel command equivalent¶

Test SSH connection¶

Verbindungsvorlagen¶

SQL Editor¶

Basic SQL Operationen¶

SQL Editor Funktionen¶

Query History und Bookmarks¶

SQL-Script Ausführung¶

Datenansicht¶

Grid View Funktionen¶

Datenfilterung¶

Datenbearbeitung¶

Daten Navigation¶

Browser¶

Datenbank Objekt Navigation¶

Aufgabenbereich¶

Vergleich¶

ER-Diagramme¶

Querausführung¶

Ausführungspläne¶

Leistungsüberwachung¶

Batch Operationen¶

Query Debugging¶

Datenexport/Import¶

Exportformate¶

Daten für die Einfuhr¶

Bulk Data Operationen¶

Datensynchronisation¶

Datenbankverwaltung¶

Benutzermanagement¶

Backup und Wiederherstellung¶

!/bin/bash¶

Keep only last 7 days of backups¶

Leistungsüberwachung¶

Index Management¶

Visual Query Build¶

Das ist nicht möglich. Schnittstelle¶

Konfigurieren beitreten¶

Zustandsbau¶

Aggregation und Gruppierung¶

Datentransfer¶

Datenbank Migration¶

Cross-Platform Datentransfer¶

ETL Operationen¶

Erweiterungen und Plugins¶

Datenbank Driver Management¶

Plugin Ecosystem¶

Kundenspezifische Erweiterungen¶

Konfiguration und Anpassung¶

Best Practices¶

Leistungsoptimierung¶

Sicherheit Best Practices¶

Entwicklungs-Workflow¶

Wartung und Überwachung¶

!/bin/bash¶

Create test database¶

Restore backup to test database¶

Verify restore¶

Cleanup¶

Zusammenfassung¶