🐬 MySQL / MariaDB

mysql -u root -p

# Connect to MySQL as root

mysql -u user -p -h host db_name

# Connect to remote database

SELECT VERSION();

# Show MySQL version

SELECT DATABASE();

# Show current database

SELECT USER();

# Show current user

SHOW STATUS;

# Show server status

SHOW PROCESSLIST;

# Show running queries

SHOW DATABASES;

# List all databases

CREATE DATABASE db_name;

# Create a new database

CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Create database with UTF-8

USE db_name;

# Switch to a database

DROP DATABASE db_name;

# Delete a database

ALTER DATABASE db_name CHARACTER SET utf8mb4;

# Change database charset

SHOW TABLES;

# List all tables in current DB

DESCRIBE table_name;

# Show table structure / columns

SHOW CREATE TABLE table_name;

# Show CREATE TABLE statement

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# Create a table

ALTER TABLE table_name ADD column_name INT;

# Add a column

ALTER TABLE table_name DROP COLUMN column_name;

# Remove a column

ALTER TABLE table_name MODIFY column_name TEXT;

# Change column type

ALTER TABLE table_name RENAME TO new_name;

# Rename a table

DROP TABLE table_name;

# Delete a table

TRUNCATE TABLE table_name;

# Delete all rows (fast)

CREATE INDEX idx_name ON table_name (column);

# Create an index

CREATE UNIQUE INDEX idx_email ON users (email);

# Create unique index

CREATE FULLTEXT INDEX idx_content ON posts (body);

# Create full-text index

SHOW INDEX FROM table_name;

# List indexes on a table

DROP INDEX idx_name ON table_name;

# Remove an index

SELECT * FROM table_name;

# Select all rows

SELECT col1, col2 FROM table_name WHERE condition;

# Select with condition

SELECT * FROM table_name ORDER BY col DESC LIMIT 10;

# Ordered & limited select

SELECT COUNT(*), col FROM table GROUP BY col HAVING COUNT(*) > 1;

# Group by with having

SELECT DISTINCT col FROM table_name;

# Select unique values

SELECT * FROM t1 JOIN t2 ON t1.id = t2.fk_id;

# INNER JOIN two tables

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.fk_id;

# LEFT JOIN

SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.fk_id;

# RIGHT JOIN

SELECT col FROM table WHERE col LIKE '%pattern%';

# Pattern search with LIKE

SELECT col FROM table WHERE col IN ('a', 'b', 'c');

# Filter by value list

SELECT col FROM table WHERE col BETWEEN 10 AND 20;

# Range filter

SELECT col, ROW_NUMBER() OVER (PARTITION BY cat ORDER BY score DESC) AS rnk FROM t;

# Window function

INSERT INTO table (col1, col2) VALUES ('val1', 'val2');

# Insert a row

INSERT INTO table (col1, col2) VALUES ('a',1),('b',2),('c',3);

# Insert multiple rows

UPDATE table SET col = 'value' WHERE condition;

# Update rows

DELETE FROM table WHERE condition;

# Delete specific rows

REPLACE INTO table (id, col) VALUES (1, 'val');

# Insert or replace

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

# Create a new user

ALTER USER 'user'@'localhost' IDENTIFIED BY 'newpass';

# Change user password

GRANT ALL PRIVILEGES ON db.* TO 'user'@'localhost';

# Grant all privileges on a DB

GRANT SELECT, INSERT ON db.* TO 'user'@'%';

# Grant specific privileges

SHOW GRANTS FOR 'user'@'localhost';

# Show user privileges

REVOKE ALL PRIVILEGES ON db.* FROM 'user'@'localhost';

# Revoke privileges

DROP USER 'user'@'localhost';

# Delete a user

FLUSH PRIVILEGES;

# Reload privilege tables

SELECT NOW();

# Current date & time

SELECT CURDATE();

# Current date

SELECT DATE_FORMAT(date, '%Y-%m-%d') FROM table;

# Format date

SELECT DATEDIFF('2025-01-01', CURDATE());

# Date difference in days

SELECT CONCAT(first, ' ', last) AS full_name;

# Concatenate strings

SELECT LENGTH(text), UPPER(text), LOWER(text);

# String functions

SELECT COALESCE(col, 'default') FROM table;

# Default value if NULL

SELECT CAST('123' AS UNSIGNED);

# Cast string to number

SELECT JSON_EXTRACT(data, '$.key') FROM table;

# Extract JSON value

SELECT AES_ENCRYPT('plaintext', 'key');

# Encrypt with AES

mysqldump -u root -p db_name > backup.sql

# Backup a single database

mysqldump -u root -p --all-databases > all.sql

# Backup all databases

mysqldump -u root -p db_name table_name > table.sql

# Backup a single table

mysql -u root -p db_name < backup.sql

# Restore a database from dump

mysqldump -u root -p --no-data db_name > schema.sql

# Backup schema only (no data)

mysqlcheck -u root -p --all-databases

# Check & repair all tables