mysql -u root -p | Connect as root |
mysql -u user -p -h hostname | Connect to remote host |
mysql -u user -p database_name | Connect to specific database |
exit; or quit; | Exit MySQL |
mysql -u user -p < script.sql | Execute SQL file |
SHOW DATABASES; | List all databases |
CREATE DATABASE dbname; | Create database |
CREATE DATABASE dbname CHARACTER SET utf8mb4; | Create with charset |
DROP DATABASE dbname; | Delete database |
USE dbname; | Select database |
SELECT DATABASE(); | Show current database |
SHOW TABLES; | List tables |
DESCRIBE tablename; | Show table structure |
SHOW CREATE TABLE tablename; | Show CREATE statement |
DROP TABLE tablename; | Delete table |
TRUNCATE TABLE tablename; | Empty table (fast) |
RENAME TABLE old TO new; | Rename table |
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100)); | Basic table |
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE); | With constraints |
CREATE TABLE orders (id INT PRIMARY KEY, user_id INT, FOREIGN KEY (user_id) REFERENCES users(id)); | With foreign key |
CREATE TABLE IF NOT EXISTS tablename (...); | Create if not exists |
INT, BIGINT, SMALLINT, TINYINT | Integer types |
DECIMAL(10,2), FLOAT, DOUBLE | Decimal/float types |
VARCHAR(255), CHAR(10), TEXT | String types |
DATE, DATETIME, TIMESTAMP, TIME | Date/time types |
BOOLEAN, BOOL | Boolean (alias for TINYINT(1)) |
BLOB, MEDIUMBLOB, LONGBLOB | Binary data |
JSON | JSON data type |
ENUM('a','b','c') | Enumeration |
ALTER TABLE t ADD column_name VARCHAR(100); | Add column |
ALTER TABLE t DROP COLUMN column_name; | Drop column |
ALTER TABLE t MODIFY column_name INT NOT NULL; | Modify column |
ALTER TABLE t CHANGE old_name new_name INT; | Rename column |
ALTER TABLE t ADD INDEX idx_name (column); | Add index |
ALTER TABLE t ADD PRIMARY KEY (id); | Add primary key |
ALTER TABLE t DROP INDEX idx_name; | Drop index |
SELECT * FROM users; | Select all columns |
SELECT id, name FROM users; | Select specific columns |
SELECT * FROM users WHERE age > 18; | With condition |
SELECT * FROM users ORDER BY name ASC; | Order by |
SELECT * FROM users LIMIT 10; | Limit results |
SELECT * FROM users LIMIT 10 OFFSET 20; | Pagination |
SELECT DISTINCT category FROM products; | Distinct values |
SELECT * FROM users WHERE name LIKE "J%"; | Pattern matching |
INSERT INTO users (name, email) VALUES ('John', 'john@email.com'); | Insert single row |
INSERT INTO users (name) VALUES ('A'), ('B'), ('C'); | Insert multiple rows |
INSERT INTO t2 SELECT * FROM t1 WHERE condition; | Insert from select |
INSERT INTO users SET name='John', email='j@e.com'; | Insert with SET |
INSERT IGNORE INTO users ...; | Ignore duplicates |
UPDATE users SET name='Jane' WHERE id=1; | Update single row |
UPDATE users SET age=age+1 WHERE birthday=CURDATE(); | Update with expression |
UPDATE users SET status='active' WHERE status='pending' LIMIT 10; | Update with limit |
DELETE FROM users WHERE id=1; | Delete single row |
DELETE FROM users WHERE age < 18; | Delete with condition |
DELETE FROM users; | Delete all rows |
SELECT * FROM a INNER JOIN b ON a.id = b.a_id; | Inner join |
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id; | Left join |
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id; | Right join |
SELECT * FROM a CROSS JOIN b; | Cross join |
SELECT * FROM a JOIN b USING (column); | Join using column |
SELECT * FROM a NATURAL JOIN b; | Natural join |
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); | Subquery in WHERE |
SELECT *, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count FROM users; | Subquery in SELECT |
SELECT * FROM (SELECT * FROM users LIMIT 10) AS subquery; | Subquery in FROM |
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id); | EXISTS subquery |
SELECT COUNT(*) FROM users; | Count rows |
SELECT COUNT(DISTINCT category) FROM products; | Count distinct |
SELECT SUM(amount) FROM orders; | Sum |
SELECT AVG(price) FROM products; | Average |
SELECT MIN(price), MAX(price) FROM products; | Min/Max |
SELECT GROUP_CONCAT(name) FROM users; | Concatenate values |
SELECT category, COUNT(*) FROM products GROUP BY category; | Group by |
SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 5; | Having clause |
SELECT YEAR(created_at), COUNT(*) FROM orders GROUP BY YEAR(created_at); | Group by year |
SELECT category, SUM(price) as total FROM products GROUP BY category WITH ROLLUP; | With rollup |
CREATE INDEX idx_name ON table (column); | Create index |
CREATE UNIQUE INDEX idx_name ON table (column); | Create unique index |
CREATE INDEX idx ON table (col1, col2); | Composite index |
CREATE FULLTEXT INDEX idx ON table (column); | Fulltext index |
SHOW INDEX FROM tablename; | Show indexes |
DROP INDEX idx_name ON tablename; | Drop index |
EXPLAIN SELECT * FROM users WHERE id=1; | Explain query |
EXPLAIN ANALYZE SELECT ...; | Explain with analyze |
SHOW PROCESSLIST; | Show running queries |
OPTIMIZE TABLE tablename; | Optimize table |
ANALYZE TABLE tablename; | Update statistics |
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password'; | Create user |
DROP USER 'user'@'localhost'; | Delete user |
ALTER USER 'user'@'localhost' IDENTIFIED BY 'newpass'; | Change password |
SELECT user, host FROM mysql.user; | List users |
GRANT ALL PRIVILEGES ON db.* TO 'user'@'localhost'; | Grant all on database |
GRANT SELECT, INSERT ON db.table TO 'user'@'localhost'; | Grant specific privileges |
REVOKE ALL PRIVILEGES ON db.* FROM 'user'@'localhost'; | Revoke privileges |
SHOW GRANTS FOR 'user'@'localhost'; | Show user grants |
FLUSH PRIVILEGES; | Reload privileges |