Programming63 entries
MySQL Reference
MySQL queries, joins, indexes, user management, and database administration
1Connection & Basics
mysql -u root -p | Connect to MySQL as root |
mysql -h host -u user -p dbname | Connect to remote database |
mysql -u user -p < dump.sql | Import SQL file |
mysqldump -u user -p dbname > dump.sql | Export database to SQL file |
mysqldump -u user -p --all-databases > all.sql | Export all databases |
SHOW DATABASES; | List all databases |
USE database_name; | Switch to a database |
SHOW TABLES; | List tables in current database |
DESCRIBE table_name; | Show table structure and columns |
SHOW CREATE TABLE table_name; | Show table creation SQL |
2Database & Table Management
CREATE DATABASE dbname CHARACTER SET utf8mb4; | Create database with UTF-8 support |
DROP DATABASE dbname; | Delete a database |
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255)); | Create a table |
DROP TABLE table_name; | Delete a table |
TRUNCATE TABLE table_name; | Delete all rows (faster than DELETE) |
RENAME TABLE old_name TO new_name; | Rename a table |
ALTER TABLE users ADD email VARCHAR(255) AFTER name; | Add column after specific column |
ALTER TABLE users DROP COLUMN email; | Remove a column |
ALTER TABLE users MODIFY name VARCHAR(500); | Change column type |
3SELECT Queries
SELECT * FROM users; | Select all columns and rows |
SELECT name, email FROM users WHERE id = 1; | Select specific columns with filter |
SELECT DISTINCT status FROM orders; | Select unique values only |
SELECT * FROM users ORDER BY created_at DESC; | Sort results descending |
SELECT * FROM users LIMIT 10 OFFSET 20; | Paginate results (page 3) |
SELECT * FROM users WHERE name LIKE "%john%"; | Pattern matching search |
SELECT * FROM users WHERE id IN (1, 2, 3); | Filter by list of values |
SELECT * FROM users WHERE age BETWEEN 18 AND 30; | Filter by range |
SELECT * FROM users WHERE email IS NOT NULL; | Filter for non-null values |
4INSERT, UPDATE & DELETE
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]'); | Insert a single row |
INSERT INTO users (name) VALUES ('A'), ('B'), ('C'); | Insert multiple rows |
INSERT INTO target SELECT * FROM source; | Insert from another table |
UPDATE users SET name = 'Bob' WHERE id = 1; | Update specific rows |
UPDATE users SET status = "active" WHERE last_login > NOW() - INTERVAL 30 DAY; | Update with date condition |
DELETE FROM users WHERE id = 1; | Delete specific rows |
DELETE FROM logs WHERE created_at < "2025-01-01"; | Delete old records |
INSERT INTO ... ON DUPLICATE KEY UPDATE name = VALUES(name); | Upsert (insert or update) |
5JOINs
SELECT * FROM orders INNER JOIN users ON orders.user_id = users.id; | Inner join (matching rows only) |
SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id; | Left join (all from left table) |
SELECT * FROM orders RIGHT JOIN users ON orders.user_id = users.id; | Right join (all from right table) |
SELECT * FROM t1 CROSS JOIN t2; | Cross join (cartesian product) |
SELECT * FROM users u JOIN orders o ON u.id = o.user_id JOIN items i ON o.id = i.order_id; | Multi-table join |
SELECT * FROM employees e JOIN employees m ON e.manager_id = m.id; | Self join |
6Aggregation & Grouping
SELECT COUNT(*) FROM users; | Count all rows |
SELECT status, COUNT(*) FROM users GROUP BY status; | Count by group |
SELECT AVG(price), SUM(price) FROM orders; | Average and sum |
SELECT MAX(salary), MIN(salary) FROM employees; | Max and min values |
SELECT status, COUNT(*) FROM orders GROUP BY status HAVING COUNT(*) > 10; | Filter groups with HAVING |
SELECT YEAR(created_at), COUNT(*) FROM users GROUP BY YEAR(created_at); | Group by date part |
7Indexes & Performance
CREATE INDEX idx_email ON users(email); | Create an index on column |
CREATE UNIQUE INDEX idx_email ON users(email); | Create unique index |
CREATE INDEX idx_name_email ON users(name, email); | Create composite index |
DROP INDEX idx_email ON users; | Delete an index |
SHOW INDEX FROM users; | List indexes on a table |
EXPLAIN SELECT * FROM users WHERE email = "[email protected]"; | Analyze query execution plan |
EXPLAIN ANALYZE SELECT * FROM users; | Execute and show actual timing |
SHOW PROCESSLIST; | Show active queries and connections |
8User & Access Management
CREATE USER 'app'@'%' IDENTIFIED BY 'pass'; | Create user with password |
GRANT ALL ON dbname.* TO 'app'@'%'; | Grant all privileges on database |
GRANT SELECT, INSERT ON dbname.* TO 'reader'@'%'; | Grant specific privileges |
REVOKE ALL ON dbname.* FROM 'app'@'%'; | Revoke all privileges |
DROP USER 'app'@'%'; | Delete a user |
FLUSH PRIVILEGES; | Reload privilege tables |
SHOW GRANTS FOR 'app'@'%'; | Show user privileges |
Related Cheatsheets
JavaScript ES6+
Modern JavaScript syntax: destructuring, arrow functions, promises, modules, and built-in methods
Python Essentials
Python syntax, data structures, comprehensions, built-in functions, and common patterns
Regular Expressions
Regex syntax, character classes, quantifiers, groups, lookaheads, and common patterns