# Connect to MySQL
mysql -u username -p
mysql -h localhost -u username -p database_name
mysql -h localhost -P 3306 -u username -p
# Execute SQL file
mysql -u username -p database_name < script.sql
# Execute query from command line
mysql -u username -p -e "SELECT * FROM users" database_name
# MySQL CLI commands
SHOW DATABASES;
USE database_name;
SHOW TABLES;
DESCRIBE table_name;
SHOW CREATE TABLE table_name;
SHOW COLUMNS FROM table_name;
SHOW INDEX FROM table_name;
SHOW PROCESSLIST;
SHOW VARIABLES LIKE '%max_connections%';
SHOW STATUS LIKE '%conn%';
\q -- Quit
-- Create database
CREATE DATABASE myapp;
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS myapp;
-- Drop database
DROP DATABASE IF EXISTS myapp;
-- Rename database (no direct command, use dump/restore)
-- Or rename tables to new database
-- Show databases
SHOW DATABASES;
-- Select database
USE myapp;
-- Show current database
SELECT DATABASE();
-- Create user
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword'; -- Any host
-- Grant privileges
GRANT ALL PRIVILEGES ON myapp.* TO 'myuser'@'localhost';
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'myuser'@'localhost';
GRANT SELECT ON myapp.users TO 'myuser'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
-- Apply privilege changes
FLUSH PRIVILEGES;
-- Revoke privileges
REVOKE ALL PRIVILEGES ON myapp.* FROM 'myuser'@'localhost';
REVOKE INSERT ON myapp.* FROM 'myuser'@'localhost';
-- Show grants
SHOW GRANTS FOR 'myuser'@'localhost';
SHOW GRANTS FOR CURRENT_USER();
-- Change password
ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'newpassword';
SET PASSWORD FOR 'myuser'@'localhost' = 'newpassword';
-- Drop user
DROP USER IF EXISTS 'myuser'@'localhost';
-- List users
SELECT user, host FROM mysql.user;
-- Numeric types
TINYINT -- 1 byte, -128 to 127
SMALLINT -- 2 bytes, -32768 to 32767
MEDIUMINT -- 3 bytes
INT / INTEGER -- 4 bytes
BIGINT -- 8 bytes
DECIMAL(M,D) -- Exact numeric, M digits, D decimals
FLOAT -- 4 bytes, floating point
DOUBLE -- 8 bytes, floating point
-- String types
CHAR(N) -- Fixed length, max 255
VARCHAR(N) -- Variable length, max 65535
TINYTEXT -- Max 255 bytes
TEXT -- Max 65535 bytes
MEDIUMTEXT -- Max 16MB
LONGTEXT -- Max 4GB
BINARY(N) -- Fixed binary
VARBINARY(N) -- Variable binary
BLOB -- Binary large object
-- Date/Time types
DATE -- 'YYYY-MM-DD'
TIME -- 'HH:MM:SS'
DATETIME -- 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP -- Unix timestamp, auto-converts timezone
YEAR -- 4-digit year
-- Other types
BOOLEAN / BOOL -- Alias for TINYINT(1)
ENUM('a','b','c') -- Enumeration
SET('a','b','c') -- Set of values
JSON -- JSON data (MySQL 5.7+)
-- Create table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
age INT UNSIGNED,
status ENUM('active', 'inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Create table with foreign key
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
total DECIMAL(10, 2) NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
-- Alter table
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;
ALTER TABLE users CHANGE COLUMN name full_name VARCHAR(200);
ALTER TABLE users RENAME TO customers;
ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE users DROP INDEX idx_email;
-- Drop table
DROP TABLE IF EXISTS users;
-- Truncate table
TRUNCATE TABLE logs;
-- Show table info
SHOW TABLE STATUS LIKE 'users';
SHOW CREATE TABLE users;
-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_name_email ON users(name, email);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
CREATE FULLTEXT INDEX idx_posts_content ON posts(title, content);
-- Primary key
ALTER TABLE users ADD PRIMARY KEY (id);
-- Drop index
DROP INDEX idx_users_email ON users;
ALTER TABLE users DROP INDEX idx_users_email;
-- Show indexes
SHOW INDEX FROM users;
-- Index hints
SELECT * FROM users USE INDEX (idx_email) WHERE email = 'test@example.com';
SELECT * FROM users FORCE INDEX (idx_email) WHERE email = 'test@example.com';
SELECT * FROM users IGNORE INDEX (idx_email) WHERE email = 'test@example.com';
-- INSERT
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
-- Multiple INSERT
INSERT INTO users (name, email) VALUES
('John', 'john@example.com'),
('Jane', 'jane@example.com');
-- INSERT IGNORE (skip on duplicate key)
INSERT IGNORE INTO users (id, name, email) VALUES (1, 'John', 'john@example.com');
-- INSERT ... ON DUPLICATE KEY UPDATE (upsert)
INSERT INTO users (id, name, email)
VALUES (1, 'John', 'john@example.com')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
email = VALUES(email);
-- REPLACE (delete + insert)
REPLACE INTO users (id, name, email) VALUES (1, 'John', 'john@example.com');
-- INSERT from SELECT
INSERT INTO archive_users SELECT * FROM users WHERE created_at < '2023-01-01';
-- UPDATE
UPDATE users SET name = 'Johnny' WHERE id = 1;
UPDATE users SET name = 'Johnny', updated_at = NOW() WHERE id = 1;
UPDATE users SET age = age + 1 WHERE id = 1;
-- UPDATE with JOIN
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.status = 'vip'
WHERE u.membership = 'premium';
-- UPDATE with LIMIT
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01' LIMIT 100;
-- DELETE
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < '2020-01-01';
-- DELETE with JOIN
DELETE o FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'deleted';
-- DELETE with LIMIT
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;
-- Create table with JSON
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
attributes JSON
);
-- Insert JSON data
INSERT INTO products (name, attributes) VALUES
('Laptop', '{"brand": "Dell", "ram": 16, "tags": ["electronics", "computers"]}');
-- Query JSON fields
SELECT JSON_EXTRACT(attributes, '$.brand') FROM products;
SELECT attributes->'$.brand' FROM products; -- Returns JSON
SELECT attributes->>'$.brand' FROM products; -- Returns text (MySQL 5.7.13+)
SELECT JSON_EXTRACT(attributes, '$.tags[0]') FROM products;
-- JSON functions
JSON_EXTRACT(json, path) -- Extract value
JSON_UNQUOTE(json) -- Remove quotes
JSON_SET(json, path, value) -- Set value (update or insert)
JSON_INSERT(json, path, value) -- Insert if not exists
JSON_REPLACE(json, path, value) -- Replace existing value
JSON_REMOVE(json, path) -- Remove key
JSON_CONTAINS(json, value) -- Check if contains
JSON_KEYS(json) -- Get keys
JSON_LENGTH(json) -- Get length
JSON_TYPE(json) -- Get type
-- Update JSON
UPDATE products
SET attributes = JSON_SET(attributes, '$.color', 'silver')
WHERE id = 1;
-- Remove key from JSON
UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.color')
WHERE id = 1;
-- Search in JSON
SELECT * FROM products
WHERE JSON_CONTAINS(attributes, '"Dell"', '$.brand');
SELECT * FROM products
WHERE JSON_EXTRACT(attributes, '$.ram') > 8;
-- Create index on JSON (generated column)
ALTER TABLE products
ADD COLUMN brand VARCHAR(100) GENERATED ALWAYS AS (attributes->>'$.brand') STORED,
ADD INDEX idx_brand (brand);
-- Create fulltext index
CREATE FULLTEXT INDEX idx_articles_content ON articles(title, content);
-- Or in CREATE TABLE
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT(title, content)
) ENGINE=InnoDB;
-- Natural language search
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database tutorial');
-- Boolean mode search
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+mysql -postgres' IN BOOLEAN MODE);
-- Boolean operators
-- +word : Must contain
-- -word : Must not contain
-- word* : Wildcard
-- "phrase" : Exact phrase
-- >word : Increase relevance
-- <word : Decrease relevance
-- With relevance score
SELECT *, MATCH(title, content) AGAINST('database') as score
FROM articles
WHERE MATCH(title, content) AGAINST('database')
ORDER BY score DESC;
-- Query expansion
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database' WITH QUERY EXPANSION);
-- Basic transaction
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Rollback
START TRANSACTION;
DELETE FROM users WHERE id = 1;
ROLLBACK;
-- Savepoints
START TRANSACTION;
INSERT INTO users (name) VALUES ('John');
SAVEPOINT my_savepoint;
INSERT INTO users (name) VALUES ('Jane');
ROLLBACK TO my_savepoint;
COMMIT;
-- Isolation levels
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- Default
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Check current isolation level
SELECT @@transaction_isolation;
-- Row locking
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;
-- Table locking
LOCK TABLES users READ, orders WRITE;
UNLOCK TABLES;
-- Explain query plan
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com'; -- MySQL 8.0.18+
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';
-- Analyze table
ANALYZE TABLE users;
-- Optimize table (defragment)
OPTIMIZE TABLE users;
-- Check table
CHECK TABLE users;
-- Repair table
REPAIR TABLE users;
-- Table size
SELECT
table_name,
ROUND(data_length / 1024 / 1024, 2) as data_mb,
ROUND(index_length / 1024 / 1024, 2) as index_mb
FROM information_schema.tables
WHERE table_schema = 'myapp';
-- Database size
SELECT
table_schema as database_name,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size_mb
FROM information_schema.tables
GROUP BY table_schema;
-- Show running queries
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
-- Kill query
KILL process_id;
KILL QUERY process_id; -- Kill query only, keep connection
-- Slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SHOW VARIABLES LIKE 'slow_query%';
-- Create procedure
DELIMITER //
CREATE PROCEDURE get_user_orders(IN user_id INT)
BEGIN
SELECT * FROM orders WHERE orders.user_id = user_id;
END //
DELIMITER ;
-- Call procedure
CALL get_user_orders(1);
-- Procedure with OUT parameter
DELIMITER //
CREATE PROCEDURE get_order_count(IN user_id INT, OUT order_count INT)
BEGIN
SELECT COUNT(*) INTO order_count FROM orders WHERE orders.user_id = user_id;
END //
DELIMITER ;
CALL get_order_count(1, @count);
SELECT @count;
-- Create function
DELIMITER //
CREATE FUNCTION calculate_tax(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN amount * 0.1;
END //
DELIMITER ;
-- Use function
SELECT calculate_tax(100);
-- Drop procedure/function
DROP PROCEDURE IF EXISTS get_user_orders;
DROP FUNCTION IF EXISTS calculate_tax;
-- Show procedures/functions
SHOW PROCEDURE STATUS WHERE Db = 'myapp';
SHOW FUNCTION STATUS WHERE Db = 'myapp';
-- Create trigger (BEFORE INSERT)
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
SET NEW.email = LOWER(NEW.email);
END //
DELIMITER ;
-- Create trigger (AFTER INSERT)
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE users SET order_count = order_count + 1 WHERE id = NEW.user_id;
END //
DELIMITER ;
-- Create trigger (BEFORE UPDATE)
DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
SET NEW.updated_at = NOW();
END //
DELIMITER ;
-- Drop trigger
DROP TRIGGER IF EXISTS before_user_insert;
-- Show triggers
SHOW TRIGGERS;
SHOW TRIGGERS LIKE 'users';
-- Create view
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = 'active';
-- Create or replace view
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, created_at FROM users WHERE status = 'active';
-- Query view
SELECT * FROM active_users;
-- Drop view
DROP VIEW IF EXISTS active_users;
-- Show views
SHOW FULL TABLES WHERE Table_type = 'VIEW';
# Dump database
mysqldump -u username -p database_name > backup.sql
mysqldump -u username -p --databases db1 db2 > backup.sql
mysqldump -u username -p --all-databases > all_backup.sql
# Dump specific table
mysqldump -u username -p database_name table_name > table_backup.sql
# Dump with options
mysqldump -u username -p --single-transaction --quick database_name > backup.sql
mysqldump -u username -p --no-data database_name > schema.sql # Schema only
mysqldump -u username -p --no-create-info database_name > data.sql # Data only
# Compressed backup
mysqldump -u username -p database_name | gzip > backup.sql.gz
# Restore
mysql -u username -p database_name < backup.sql
# Restore compressed
gunzip < backup.sql.gz | mysql -u username -p database_name
-- String functions
CONCAT(first_name, ' ', last_name)
CONCAT_WS(' ', first_name, middle_name, last_name) -- With separator
UPPER(name)
LOWER(name)
LENGTH(name) -- Bytes
CHAR_LENGTH(name) -- Characters
TRIM(name)
LTRIM(name)
RTRIM(name)
SUBSTRING(name, 1, 3)
LEFT(name, 5)
RIGHT(name, 5)
REPLACE(name, 'old', 'new')
REVERSE(name)
LPAD(id, 5, '0') -- Pad left
RPAD(id, 5, '0') -- Pad right
-- Date functions
NOW()
CURDATE()
CURTIME()
DATE(datetime_col)
TIME(datetime_col)
YEAR(date_col)
MONTH(date_col)
DAY(date_col)
HOUR(datetime_col)
DATE_ADD(date, INTERVAL 1 DAY)
DATE_SUB(date, INTERVAL 1 MONTH)
DATEDIFF(date1, date2)
DATE_FORMAT(date, '%Y-%m-%d')
STR_TO_DATE('2024-01-15', '%Y-%m-%d')
UNIX_TIMESTAMP()
FROM_UNIXTIME(timestamp)
-- Numeric functions
ROUND(number, 2)
CEIL(number)
FLOOR(number)
ABS(number)
MOD(number, divisor)
RAND() -- Random 0-1
FLOOR(RAND() * 100) -- Random 0-99
-- NULL handling
COALESCE(col1, col2, 'default')
IFNULL(col, 'default')
NULLIF(col1, col2)
-- Conditional
IF(condition, true_val, false_val)
CASE
WHEN age < 18 THEN 'Minor'
WHEN age < 65 THEN 'Adult'
ELSE 'Senior'
END
-- Aggregation
GROUP_CONCAT(name SEPARATOR ', ')
GROUP_CONCAT(DISTINCT name ORDER BY name SEPARATOR ', ')
-- Generate UUID
SELECT UUID();
-- Get auto increment value
SELECT LAST_INSERT_ID();
-- Find duplicates
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Delete duplicates (keep lowest id)
DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.email = u2.email;
-- Pagination
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
-- Get nth row
SELECT * FROM users ORDER BY id LIMIT 1 OFFSET 4; -- 5th row
-- Random row
SELECT * FROM users ORDER BY RAND() LIMIT 1;
-- Copy table structure
CREATE TABLE users_backup LIKE users;
-- Copy table with data
CREATE TABLE users_backup AS SELECT * FROM users;
-- Swap column values
UPDATE users SET
first_name = (@temp := first_name),
first_name = last_name,
last_name = @temp;
-- Show table columns
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'myapp' AND TABLE_NAME = 'users';