SQL Cheatsheet

Basic Commands

Database Connection

# Connect to MySQL console
mysql -u username -p

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

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

Database Operations

-- Show all databases
SHOW DATABASES;
/*
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| my_database        |
| test_db            |
+--------------------+
*/

-- Create database
CREATE DATABASE database_name;
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Query OK, 1 row affected (0.01 sec)

-- Use database
USE database_name;
-- Database changed

-- Show current database
SELECT DATABASE();
/*
+-------------+
| DATABASE()  |
+-------------+
| my_database |
+-------------+
*/

-- Drop database
DROP DATABASE database_name;
-- Query OK, 0 rows affected (0.05 sec)

Table Management

Creating Tables

-- Basic table creation
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

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

Table Information

-- Show all tables
SHOW TABLES;
/*
+---------------------+
| Tables_in_my_database |
+---------------------+
| users               |
| posts               |
| categories          |
+---------------------+
*/

-- Describe table structure
DESCRIBE table_name;
DESC table_name;
/*
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int          | NO   | PRI | NULL              | auto_increment |
| username   | varchar(50)  | NO   | UNI | NULL              |                |
| email      | varchar(100) | NO   |     | NULL              |                |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+----------------+
*/

-- Detailed table information
SHOW CREATE TABLE table_name;

-- Show columns of a table
SHOW COLUMNS FROM table_name;

Modifying Tables

-- Add column
ALTER TABLE table_name ADD COLUMN column_name datatype;

-- Modify column
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;

-- Drop column
ALTER TABLE table_name DROP COLUMN column_name;

-- Add index
ALTER TABLE table_name ADD INDEX idx_column_name (column_name);

-- Rename table
RENAME TABLE old_name TO new_name;

-- Drop table
DROP TABLE table_name;

CRUD Operations

CREATE (Insert)

-- Insert single record
INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');

-- Insert multiple records
INSERT INTO users (username, email) VALUES 
    ('user1', '[email protected]'),
    ('user2', '[email protected]'),
    ('user3', '[email protected]');

-- With ON DUPLICATE KEY UPDATE (since MySQL 4.1)
INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]')
ON DUPLICATE KEY UPDATE email = VALUES(email);

READ (Select)

-- Select all records
SELECT * FROM users;
/*
+----+----------+-------------------+---------------------+
| id | username | email             | created_at          |
+----+----------+-------------------+---------------------+
|  1 | john_doe | [email protected]  | 2024-01-15 10:30:00 |
|  2 | jane_doe | [email protected]  | 2024-01-16 14:22:00 |
|  3 | bob_user | [email protected]   | 2024-01-17 09:15:00 |
+----+----------+-------------------+---------------------+
*/

-- Select specific columns
SELECT username, email FROM users;
/*
+----------+-------------------+
| username | email             |
+----------+-------------------+
| john_doe | [email protected]  |
| jane_doe | [email protected]  |
| bob_user | [email protected]   |
+----------+-------------------+
*/

-- With WHERE condition
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE username LIKE 'john%';

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

-- With LIMIT
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- With COUNT, SUM, AVG, MAX, MIN
SELECT COUNT(*) FROM users;
/*
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
*/

SELECT AVG(age) FROM users;
SELECT MAX(created_at) FROM users;
/*
+---------------------+
| MAX(created_at)     |
+---------------------+
| 2024-01-17 09:15:00 |
+---------------------+
*/

UPDATE (Update)

-- Update single record
UPDATE users SET email = '[email protected]' WHERE id = 1;

-- Update multiple columns
UPDATE users SET username = 'new_username', email = '[email protected]' WHERE id = 1;

-- With conditions
UPDATE users SET status = 'active' WHERE created_at > '2024-01-01';

DELETE (Delete)

-- Delete specific records
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < '2024-01-01';

-- Delete all records (structure remains)
DELETE FROM users;

-- Empty table (faster than DELETE)
TRUNCATE TABLE users;

Understanding Database Relations

1:1 Relationship (One-to-One)

Concept: One record in table A belongs to exactly one record in table B and vice versa.

Example: User and User Profile

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

-- 1:1 relationship - User profile
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE, -- UNIQUE makes the 1:1 relationship
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    address TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

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

1:n Relationship (One-to-Many)

Concept: One record in table A can have multiple records in table B, but each record in B belongs to only one in A.

Example: Users and Blog Posts

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

-- "Many" Posts (n) 
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT, -- Foreign key without UNIQUE
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Query: All posts by a user
SELECT u.username, p.title, p.created_at
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.username = 'john_doe';
/*
+----------+------------------+---------------------+
| username | title            | created_at          |
+----------+------------------+---------------------+
| john_doe | My First Post    | 2024-01-15 10:30:00 |
| john_doe | MySQL Tutorial   | 2024-01-16 14:00:00 |
| john_doe | PHP Basics       | 2024-01-17 09:15:00 |
+----------+------------------+---------------------+
*/

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

n:m Relationship (Many-to-Many)

Concept: Multiple records in table A can be associated with multiple records in table B. Requires a junction table.

Example: Posts and Tags

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

-- Second main table  
CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- Junction table for n:m relationship
CREATE TABLE post_tags (
    post_id INT,
    tag_id INT,
    PRIMARY KEY (post_id, tag_id), -- Composite Primary Key
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

-- Insert data
INSERT INTO posts (title, content) VALUES 
('MySQL Tutorial', 'MySQL basics...'),
('PHP Basics', 'Introduction to PHP...');

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

INSERT INTO post_tags (post_id, tag_id) VALUES 
(1, 1), -- MySQL Tutorial has tag "mysql"
(1, 3), -- MySQL Tutorial has tag "tutorial" 
(1, 4), -- MySQL Tutorial has tag "database"
(2, 2), -- PHP Basics has tag "php"
(2, 3); -- PHP Basics has tag "tutorial"

-- Query: All tags for a post
SELECT p.title, t.name as tag_name
FROM posts p
JOIN post_tags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE p.id = 1;
/*
+---------------+----------+
| title         | tag_name |
+---------------+----------+
| MySQL Tutorial| mysql    |
| MySQL Tutorial| tutorial |
| MySQL Tutorial| database |
+---------------+----------+
*/

-- Query: All posts for a tag
SELECT t.name as tag_name, p.title
FROM tags t
JOIN post_tags pt ON t.id = pt.tag_id
JOIN posts p ON pt.post_id = p.id
WHERE t.name = 'tutorial';
/*
+----------+----------------+
| tag_name | title          |
+----------+----------------+
| tutorial | MySQL Tutorial |
| tutorial | PHP Basics     |
+----------+----------------+
*/

-- Number of posts per tag
SELECT t.name, COUNT(pt.post_id) as post_count
FROM tags t
LEFT JOIN post_tags pt ON t.id = pt.tag_id
GROUP BY t.id, t.name;
/*
+----------+------------+
| name     | post_count |
+----------+------------+
| mysql    |          1 |
| php      |          1 |
| tutorial |          2 |
| database |          1 |
+----------+------------+
*/

Advanced Relations

Self-Referencing Relationship

Example: Employee Hierarchy (Manager-Subordinate)

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    manager_id INT NULL, -- Reference to another employee
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- Query: Employees with their managers
SELECT 
    e.name as employee_name,
    m.name as manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
/*
+---------------+--------------+
| employee_name | manager_name |
+---------------+--------------+
| John Smith    | NULL         |
| Jane Doe      | John Smith   |
| Bob Wilson    | John Smith   |
| Alice Brown   | Jane Doe     |
+---------------+--------------+
*/

Polymorphic Relationship

Example: Comments on different content types

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

-- All comments on posts
SELECT * FROM comments WHERE commentable_type = 'post' AND commentable_id = 1;

JOIN Operations

Different JOIN Types

-- INNER JOIN - Only matching records
SELECT u.username, p.title 
FROM users u 
INNER JOIN posts p ON u.id = p.user_id;
/*
+----------+------------------+
| username | title            |
+----------+------------------+
| john_doe | MySQL Tutorial   |
| john_doe | PHP Basics       |
| jane_doe | React Introduction|
+----------+------------------+
*/

-- LEFT JOIN - All from left table, even without match
SELECT u.username, p.title 
FROM users u 
LEFT JOIN posts p ON u.id = p.user_id;
/*
+----------+------------------+
| username | title            |
+----------+------------------+
| john_doe | MySQL Tutorial   |
| john_doe | PHP Basics       |
| jane_doe | React Introduction|
| bob_user | NULL             |  -- User without posts
+----------+------------------+
*/

-- RIGHT JOIN - All from right table
SELECT u.username, p.title 
FROM users u 
RIGHT JOIN posts p ON u.id = p.user_id;

-- FULL OUTER JOIN (simulated, as MySQL doesn't support it natively)
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;

Advanced Queries

Grouping and Aggregation

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

-- With multiple aggregate functions
SELECT 
    user_id, 
    COUNT(*) as total_posts,
    MAX(created_at) as latest_post,
    MIN(created_at) as first_post
FROM posts 
GROUP BY user_id;

Subqueries

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

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

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

Indexes and Performance

Index Management

-- Create index
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email_created ON users(email, created_at);

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

-- Full-text index
CREATE FULLTEXT INDEX idx_content_fulltext ON posts(content);

-- Show indexes
SHOW INDEX FROM users;

-- Drop index
DROP INDEX idx_username ON users;

Performance Analysis

-- Show query execution plan
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE username = 'john_doe';

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

Modern MySQL Features

Common Table Expressions (CTEs) - MySQL 8.0+

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

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

Window Functions - MySQL 8.0+

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

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

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

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

JSON Support - MySQL 5.7+

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

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

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

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

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

Generated Columns - MySQL 5.7+

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

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

CHECK Constraints - MySQL 8.0.16+

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

Multi-Value Indexes - MySQL 8.0.17+

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

Transactions and Locks

Transaction Management

-- Start transaction
START TRANSACTION;
-- or
BEGIN;

-- Commit changes
COMMIT;

-- Rollback changes
ROLLBACK;

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

Isolation Levels

-- Show current isolation level
SELECT @@transaction_isolation;

-- Set isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

User and Permission Management

User Management

-- Create user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username'@'%' IDENTIFIED BY 'password'; -- From anywhere

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

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

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

Permission Management

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

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

-- Revoke privileges
REVOKE INSERT ON database_name.table_name FROM 'username'@'localhost';

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

-- Apply privilege changes
FLUSH PRIVILEGES;

Backup and Recovery

Database Backup

# Complete backup
mysqldump -u username -p database_name > backup.sql

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

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

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

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

Database Restoration

# Restore backup
mysql -u username -p database_name < backup.sql

# Create new database and restore backup
mysql -u username -p -e "CREATE DATABASE new_database;"
mysql -u username -p new_database < backup.sql

Useful Commands and Functions

System Information

-- Show MySQL version
SELECT VERSION();
/*
+-----------+
| VERSION() |
+-----------+
| 8.0.35    |
+-----------+
*/

-- Current time/date
SELECT NOW(), CURDATE(), CURTIME();
/*
+---------------------+------------+-----------+
| NOW()               | CURDATE()  | CURTIME() |
+---------------------+------------+-----------+
| 2024-05-30 14:30:25 | 2024-05-30 | 14:30:25  |
+---------------------+------------+-----------+
*/

-- Show system variables
SHOW VARIABLES LIKE 'max_connections';
/*
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
*/

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

-- Show active processes
SHOW PROCESSLIST;
/*
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  5 | root | localhost | test | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+----------+------------------+
*/

-- Engine information
SHOW ENGINES;

String Functions

-- Commonly used string functions
SELECT 
    CONCAT('Hello', ' ', 'World') as concatenated,
    UPPER('hello') as uppercase,
    LOWER('HELLO') as lowercase,
    LENGTH('Hello World') as string_length,
    SUBSTRING('Hello World', 1, 5) as substring_result,
    REPLACE('Hello World', 'World', 'MySQL') as replaced;
/*
+-------------+-----------+-----------+---------------+------------------+-------------+
| concatenated| uppercase | lowercase | string_length | substring_result | replaced    |
+-------------+-----------+-----------+---------------+------------------+-------------+
| Hello World | HELLO     | hello     |            11 | Hello            | Hello MySQL |
+-------------+-----------+-----------+---------------+------------------+-------------+
*/

Date and Time Functions

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

Mathematical Functions

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

Performance Tips

Use indexes properly: Create indexes for frequently queried columns
Use LIMIT: Always use LIMIT with large datasets
**Avoid SELECT ***: Only query needed columns
WHERE before JOIN: Apply WHERE conditions before JOIN operations
Use EXPLAIN: Analyze query plans
Batch operations: Multiple INSERTs in one statement
Connection pooling: Reuse database connections
Regular maintenance: Run OPTIMIZE TABLE and ANALYZE TABLE regularly

Common Data Types

Numeric Types

TINYINT: -128 to 127
SMALLINT: -32,768 to 32,767
MEDIUMINT: -8,388,608 to 8,388,607
INT: -2,147,483,648 to 2,147,483,647
BIGINT: Very large integers
DECIMAL(M,D): Exact decimal numbers
FLOAT: Floating-point numbers
DOUBLE: Double-precision floating-point numbers

String Types

CHAR(M): Fixed length (0-255)
VARCHAR(M): Variable length (0-65,535)
TEXT: Long texts (up to 65,535 characters)
MEDIUMTEXT: Medium texts (up to 16,777,215 characters)
LONGTEXT: Very long texts (up to 4,294,967,295 characters)

Date and Time Types

DATE: YYYY-MM-DD
TIME: HH:MM:SS
DATETIME: YYYY-MM-DD HH:MM:SS
TIMESTAMP: Automatic timestamps
YEAR: Year in 2 or 4 digits

Modern Types (MySQL 5.7+)

JSON: JSON documents
GEOMETRY: Spatial data
GENERATED: Computed columns

Translated by AI from the German Version

Basic Commands#

Database Connection#

Database Operations#

Table Management#

Creating Tables#

Table Information#

Modifying Tables#

CRUD Operations#

CREATE (Insert)#

READ (Select)#

UPDATE (Update)#

DELETE (Delete)#

Understanding Database Relations#

1:1 Relationship (One-to-One)#

1:n Relationship (One-to-Many)#

n:m Relationship (Many-to-Many)#

Advanced Relations#

Self-Referencing Relationship#

Polymorphic Relationship#

JOIN Operations#

Different JOIN Types#

Advanced Queries#

Grouping and Aggregation#

Subqueries#

Indexes and Performance#

Index Management#

Performance Analysis#

Modern MySQL Features#

Common Table Expressions (CTEs) - MySQL 8.0+#

Window Functions - MySQL 8.0+#

JSON Support - MySQL 5.7+#

Generated Columns - MySQL 5.7+#

CHECK Constraints - MySQL 8.0.16+#

Multi-Value Indexes - MySQL 8.0.17+#

Transactions and Locks#

Transaction Management#

Isolation Levels#

User and Permission Management#

User Management#

Permission Management#

Backup and Recovery#

Database Backup#

Database Restoration#

Useful Commands and Functions#

System Information#

String Functions#

Date and Time Functions#

Mathematical Functions#

Performance Tips#

Common Data Types#

Numeric Types#

String Types#

Date and Time Types#

Modern Types (MySQL 5.7+)#