MySQL Command & Syntax Cheat Sheet

Database Basics

CREATE DATABASE dbname;
SHOW DATABASES;
USE dbname;
DROP DATABASE dbname;

Table Operations

CREATE TABLE tablename (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
SHOW TABLES;
DESCRIBE tablename;
SHOW COLUMNS FROM tablename;
RENAME TABLE oldname TO newname;
DROP TABLE tablename;
ALTER TABLE tablename ADD column_name datatype;
ALTER TABLE tablename DROP column column_name;
ALTER TABLE tablename MODIFY column_name new_datatype;

Insert, Update, Delete

INSERT INTO tablename (col1, col2) VALUES (‘value1’, ‘value2’);
INSERT INTO tablename (col1, col2) VALUES
(‘value1’, ‘value2’),
(‘value3’, ‘value4’);
UPDATE tablename SET col1 = ‘newvalue’ WHERE id = 1;
DELETE FROM tablename WHERE id = 1;
TRUNCATE TABLE tablename;

Querying Data

SELECT * FROM tablename;
SELECT col1, col2 FROM tablename;
SELECT * FROM tablename WHERE col1 = ‘value’;
SELECT * FROM tablename WHERE col1 = ‘value’ AND col2 > 10;
SELECT * FROM tablename ORDER BY col1 ASC;
SELECT * FROM tablename ORDER BY col1 DESC;
SELECT * FROM tablename LIMIT 10;
SELECT * FROM tablename LIMIT 5 OFFSET 10;
SELECT DISTINCT col1 FROM tablename;

Joins

SELECT a.col1, b.col2
FROM tableA a
INNER JOIN tableB b ON a.id = b.a_id;
SELECT a.col1, b.col2
FROM tableA a
LEFT JOIN tableB b ON a.id = b.a_id;
SELECT a.col1, b.col2
FROM tableA a
RIGHT JOIN tableB b ON a.id = b.a_id;
SELECT a.col1, b.col2
FROM tableA a
LEFT JOIN tableB b ON a.id = b.a_id
UNION
SELECT a.col1, b.col2
FROM tableA a
RIGHT JOIN tableB b ON a.id = b.a_id;

Aggregates & Grouping

SELECT COUNT(), MAX(col1), MIN(col1), AVG(col1), SUM(col1) FROM tablename; SELECT col1, COUNT()
FROM tablename
GROUP BY col1;
SELECT col1, COUNT() FROM tablename GROUP BY col1 HAVING COUNT() > 1;

Indexes & Keys

CREATE INDEX idx_col1 ON tablename(col1);
SHOW INDEXES FROM tablename;
DROP INDEX idx_col1 ON tablename;
ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_col)
REFERENCES parent_table(parent_col)
ON DELETE CASCADE;
User ManagementCREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;
GRANT ALL PRIVILEGES ON dbname.* TO ‘username’@’localhost’;
FLUSH PRIVILEGES;
SELECT user, host FROM mysql.user;
DROP USER ‘username’@’localhost’;

Backup & Restore

Backup

databasemysqldump -u username -p dbname > backup.sql

Restore database

mysql -u username -p dbname < backup.sql