Comprehensive MySQL Cheat Sheet: Commands, Syntax, and Best Practices

Service Management (Windows)

Start MySQL as a Windows service and create the service manually:

net start mysql
sc create mysql binPath= "C:\path\to\mysqld.exe"

Connecting to MySQL

Typical client connection syntax (replace placeholders with actual values):

mysql -h <strong>host</strong> -P <strong>port</strong> -u <strong>user</strong> -p<strong>password</strong>

Useful diagnostics after connecting: SHOW PROCESSLIST; – view active threads. SHOW VARIABLES; – view server variables.

Database Operations

-- Show current database
SELECT DATABASE();
-- Show server time, current user and version
SELECT NOW(), USER(), VERSION();
-- Create a database (optional IF NOT EXISTS)
CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- List databases (pattern optional)
SHOW DATABASES LIKE 'pattern%';
-- Show the CREATE statement for a database
SHOW CREATE DATABASE db_name;
-- Alter database options (e.g., default charset)
ALTER DATABASE db_name CHARACTER SET utf8mb4;
-- Drop a database
DROP DATABASE IF EXISTS db_name;

Table Operations

-- Create a table (IF NOT EXISTS, optional TEMPORARY)
CREATE TABLE IF NOT EXISTS db_name.table_name (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'PK',
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Show tables
SHOW TABLES LIKE 'tbl_%';
-- Show CREATE statement for a table
SHOW CREATE TABLE table_name;
-- Describe table structure
DESCRIBE table_name;
-- Add a column after an existing column
ALTER TABLE table_name ADD COLUMN age TINYINT AFTER name;
-- Modify a column (full definition required)
ALTER TABLE table_name MODIFY COLUMN name VARCHAR(200) NOT NULL;
-- Rename a column
ALTER TABLE table_name CHANGE old_name new_name VARCHAR(200);
-- Drop a column
ALTER TABLE table_name DROP COLUMN obsolete_col;
-- Add constraints
ALTER TABLE table_name ADD PRIMARY KEY (id);
ALTER TABLE table_name ADD UNIQUE (email);
ALTER TABLE table_name ADD CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE SET NULL;
-- Truncate (fast delete, resets AUTO_INCREMENT)
TRUNCATE TABLE table_name;
-- Drop table
DROP TABLE IF EXISTS table_name;

Data Manipulation (DML)

-- Insert single or multiple rows
INSERT INTO table_name (col1, col2) VALUES (v1, v2), (v3, v4);
-- INSERT … SET syntax (alternative)
INSERT INTO table_name SET col1=v1, col2=v2;
-- Replace (INSERT or UPDATE on duplicate key)
REPLACE INTO table_name (id, col) VALUES (1, 'val');
-- Select with optional clauses
SELECT col1, col2 FROM table_name WHERE col1>10 GROUP BY col2 HAVING COUNT(*)>1 ORDER BY col1 DESC LIMIT 10;
-- Delete rows
DELETE FROM table_name WHERE created_at < '2023-01-01' ORDER BY id LIMIT 100;
-- Update rows
UPDATE table_name SET status='archived' WHERE updated_at < NOW() - INTERVAL 1 YEAR;

Character Set and Collation

SHOW VARIABLES LIKE 'character_set_%';
SET character_set_client = 'utf8mb4';
SET character_set_results = 'utf8mb4';
SET character_set_connection = 'utf8mb4';
SET NAMES utf8mb4;   -- shortcut for the three statements above
SHOW CHARACTER SET;   -- list all character sets
SHOW COLLATION;       -- list all collations

Data Types Overview

Numeric : TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT (add UNSIGNED for non‑negative values). INT(M) defines display width; Zerofill pads with leading zeros. FLOAT(M,D), DOUBLE(M,D) for approximate values; DECIMAL(M,D) for exact fixed‑point numbers.

String : CHAR(N) (fixed length), VARCHAR(N) (variable length, up to 65 535 bytes depending on charset), TEXT / BLOB families, and binary equivalents BINARY / VARBINARY.

Date/Time : DATETIME, DATE, TIMESTAMP, TIME, YEAR.

Enumerations : ENUM('a','b',...) (max 65 535 values, stored as SMALLINT), SET('a','b',...) (max 64 members, stored as BIGINT bitmask).

Choosing Column Types (Practical Tips)

Prefer the smallest type that satisfies range and precision.

Use UNSIGNED for values that cannot be negative.

Store IPv4 addresses as INT UNSIGNED using INET_ATON() / INET_NTOA() or PHP ip2long() with sprintf('%u', …).

Normalization Guidelines

1NF – atomic columns, no repeating groups.

2NF – no partial dependencies on a composite primary key.

3NF – no transitive dependencies; non‑key columns depend only on the primary key.

SELECT Statement Structure

SELECT [ALL|DISTINCT] select_expr
FROM table_expression
[WHERE condition]
[GROUP BY col1, col2 ...]
[HAVING agg_condition]
[ORDER BY col1 ASC|DESC, ...]
[LIMIT offset, row_count];

Use column aliases with AS. Index hints ( USE INDEX, IGNORE INDEX, FORCE INDEX) can improve performance.

UNION

SELECT col1, col2 FROM t1
UNION [ALL|DISTINCT]
SELECT col1, col2 FROM t2;
-- Enclose each SELECT in parentheses when combined with ORDER BY/LIMIT.

Subqueries

Derived table (FROM subquery) – must have an alias.

Scalar subquery (WHERE) – returns a single value.

IN / NOT IN – column subquery.

EXISTS / NOT EXISTS – tests for row existence.

Row‑value subquery – e.g., (col1, col2) IN (SELECT a, b FROM …).

JOIN Types

-- Inner join (default)
SELECT * FROM a INNER JOIN b ON a.id = b.id;
-- Cross join (no condition)
SELECT * FROM a CROSS JOIN b;
-- Left outer join
SELECT * FROM a LEFT JOIN b ON a.id = b.id;
-- Right outer join
SELECT * FROM a RIGHT JOIN b ON a.id = b.id;
-- Natural join (auto‑matches columns with same name)
SELECT * FROM a NATURAL JOIN b;

Export / Import

-- Export table to a CSV‑like file
SELECT * INTO OUTFILE '/tmp/table.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '
'
FROM table_name;

-- Import from a file (LOCAL if the file is on the client)
LOAD DATA [LOCAL] INFILE '/tmp/table.txt' REPLACE INTO TABLE table_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '
';

Backup & Restore (mysqldump)

# Export a single table
mysqldump -uuser -p password db_name table_name > table.sql
# Export multiple tables
mysqldump -uuser -p password db_name table1 table2 > multi.sql
# Export an entire database
mysqldump -uuser -p password db_name > db.sql
# Export all databases
mysqldump -uuser -p password --all-databases > all.sql
# Import
mysql -uuser -p password db_name < db.sql
# Or from within the MySQL client: source /path/file.sql;

Views

CREATE OR REPLACE VIEW view_name (col1, col2) AS
SELECT a, b FROM source_table WHERE status='active';
SHOW CREATE VIEW view_name;
DROP VIEW IF EXISTS view_name;

Transactions (InnoDB)

START TRANSACTION;   -- or BEGIN
-- DML statements …
COMMIT;             -- persist changes
ROLLBACK;           -- revert to start point

-- Savepoints
SAVEPOINT sp1;
-- … statements …
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;

-- Disable autocommit for the session
SET autocommit = 0;   -- must COMMIT or ROLLBACK manually

MySQL provides ACID guarantees with InnoDB. DDL statements (e.g., CREATE/DROP) cannot be rolled back.

Table Locks

LOCK TABLES tbl_name READ;   -- shared read lock
LOCK TABLES tbl_name WRITE;  -- exclusive write lock
UNLOCK TABLES;

Triggers

CREATE TRIGGER trg_name BEFORE INSERT ON tbl_name
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END;

DROP TRIGGER IF EXISTS trg_name;

String Functions (selected)

CONCAT(str1, str2, ...)
CONCAT_WS(separator, str1, str2, ...)
LCASE(str), UCASE(str)
SUBSTRING(str, pos, len)
REPLACE(str, from, to)
INSTR(str, substr)
LOCATE(substr, str [, start])
LENGTH(str)          -- bytes
CHAR_LENGTH(str)     -- characters
TRIM(str), LTRIM(str), RTRIM(str)

Numeric Functions (selected)

ABS(x), CEIL(x), FLOOR(x), ROUND(x), MOD(m,n), POW(m,n), SQRT(x), RAND()
TRUNCATE(x, d)   -- d decimal places
PI()

Date/Time Functions (selected)

NOW(), CURRENT_TIMESTAMP(), CURRENT_DATE(), CURRENT_TIME()
DATE_FORMAT(dt, format)
UNIX_TIMESTAMP(), FROM_UNIXTIME(ts)
DATE(dt), TIME(dt)

Control Flow in Stored Programs

-- Variable declaration
DECLARE var_name datatype [DEFAULT value];
-- Assignment
SET var_name = expr;
SELECT expr INTO var_name;
-- IF … THEN … ELSEIF … THEN … ELSE … END IF;
-- CASE … WHEN … THEN … ELSE … END CASE;
-- WHILE loop
[label:] WHILE condition DO
    statements
END WHILE [label];
-- LOOP, REPEAT, LEAVE, ITERATE are also available.

-- Stored function template
CREATE FUNCTION func_name(arg1 TYPE, ...) RETURNS ret_type
BEGIN
    DECLARE local_var TYPE;
    -- statements
    RETURN result;
END;

-- Stored procedure template
CREATE PROCEDURE proc_name(IN p1 TYPE, OUT p2 TYPE, INOUT p3 TYPE)
BEGIN
    -- statements
END;

User and Privilege Management

-- Reset root password (skip grant tables)
mysqld --skip-grant-tables &
USE mysql;
UPDATE `user` SET PASSWORD=PASSWORD('new_pass') WHERE `user`='root';
FLUSH PRIVILEGES;

-- Create a new user
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
-- Grant privileges
GRANT SELECT, INSERT, UPDATE ON db_name.* TO 'username'@'host';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
-- Show privileges
SHOW GRANTS FOR 'username'@'host';
-- Revoke
REVOKE INSERT, UPDATE ON db_name.* FROM 'username'@'host';
-- Drop user
DROP USER 'username'@'host';

Table Maintenance

ANALYZE TABLE tbl_name;   -- update index statistics
CHECK TABLE tbl_name;      -- verify integrity
OPTIMIZE TABLE tbl_name;   -- defragment and reclaim space

Miscellaneous Tips

Enclose identifiers with backticks ( `) to avoid conflicts with reserved words; Unicode identifiers are allowed.

Comments: # single line, -- space‑prefixed single line, /* block */.

Wildcard patterns: _ matches one character, % matches any number of characters.

SQL keywords are case‑insensitive; string literals are case‑sensitive.

Terminate statements with ;. Use DELIMITER to change the terminator when defining stored routines.