Home

MySQL Cheatsheet

📘 MySQL Basics

-- Login to MySQL
mysql -u root -p

-- Show all databases
SHOW DATABASES;

-- Create new database
CREATE DATABASE mydb;

-- Use a database
USE mydb;

-- Show tables
SHOW TABLES;

-- Describe table structure
DESCRIBE users;

📦 Tables & Data

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

-- Insert data
INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');

-- Select data
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;

-- Update data
UPDATE users SET name = 'Alice Smith' WHERE id = 1;

-- Delete data
DELETE FROM users WHERE id = 2;

🔗 Joins

-- Inner Join
SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- Left Join
SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- Right Join
SELECT users.name, orders.product
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- Full Outer Join (not directly supported, use UNION)
SELECT users.name, orders.product
FROM users LEFT JOIN orders ON users.id = orders.user_id
UNION
SELECT users.name, orders.product
FROM users RIGHT JOIN orders ON users.id = orders.user_id;

🛡️ Constraints

-- Primary Key
CREATE TABLE categories (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

-- Foreign Key
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  category_id INT,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Unique Constraint
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE(email);

-- Default Value
ALTER TABLE users ADD COLUMN status VARCHAR(10) DEFAULT 'active';

⚡ Indexes & Performance

-- Create Index
CREATE INDEX idx_email ON users(email);

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

-- Show Indexes
SHOW INDEX FROM users;

-- Drop Index
DROP INDEX idx_email ON users;

👀 Views

-- Create View
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

-- Use View
SELECT * FROM active_users;

-- Drop View
DROP VIEW active_users;

💰 Transactions

-- Start transaction
START TRANSACTION;

-- Example
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit (save changes)
COMMIT;

-- Rollback (undo changes)
ROLLBACK;

🧠 Advanced MySQL

-- Stored Procedure
DELIMITER //
CREATE PROCEDURE GetUsers()
BEGIN
  SELECT * FROM users;
END //
DELIMITER ;

CALL GetUsers();

-- Function
DELIMITER //
CREATE FUNCTION getUserCount()
RETURNS INT DETERMINISTIC
BEGIN
  DECLARE total INT;
  SELECT COUNT(*) INTO total FROM users;
  RETURN total;
END //
DELIMITER ;

SELECT getUserCount();

-- Trigger
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

💡 Pro Tips

-- Import SQL file
mysql -u root -p mydb < backup.sql

-- Export SQL file
mysqldump -u root -p mydb > backup.sql

-- Optimize Table
OPTIMIZE TABLE users;

-- Check slow queries
SHOW FULL PROCESSLIST;

Post a Comment