MySQL Cheat Sheet
By: Mike Burns
SELECT
Purpose: Retrieves data from one or more tables.
Syntax: SELECT column1, column2 FROM table_name;
INSERT INTO
Purpose: Inserts new data into a table.
Syntax: INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE
Purpose: Modifies existing data in a table.
Syntax: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
DELETE
Purpose: Removes existing data from a table.
Syntax: DELETE FROM table_name WHERE condition;
CREATE DATABASE
Purpose: Creates a new database.
Syntax: CREATE DATABASE database_name;
DROP DATABASE
Purpose: Deletes an existing database.
Syntax: DROP DATABASE database_name;
USE
Purpose: Selects a database to work with.
Syntax: USE database_name;
CREATE TABLE
Purpose: Creates a new table in the database.
Syntax: CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
DROP TABLE
Purpose: Deletes an existing table in the database.
Syntax: DROP TABLE table_name;
ALTER TABLE
Purpose: Modifies the structure of an existing table.
Syntax: ALTER TABLE table_name ADD column_name datatype;
INDEX
Purpose: Creates or deletes an index.
Syntax: CREATE INDEX index_name ON table_name (column1, column2);
Syntax: DROP INDEX index_name ON table_name;
JOIN
Purpose: Combines rows from two or more tables based on a related column.
Syntax: SELECT columns FROM table1 JOIN table2 ON table1.column_name =
table2.column_name;
GROUP BY
Purpose: Groups rows that have the same values in specified columns.
Syntax: SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
HAVING
Purpose: Used with GROUP BY to filter groups based on a condition.
Syntax: SELECT column_name FROM table_name GROUP BY column_name HAVING condition;
ORDER BY
Purpose: Sorts the result set in ascending or descending order.
Syntax: SELECT column1, column2 FROM table_name ORDER BY column1 ASC|DESC;
LIMIT
Purpose: Specifies the maximum number of records to return in a query result.
Syntax: SELECT columns FROM table_name LIMIT number;
SHOW DATABASES
Purpose: Lists all databases on the MySQL server.
Syntax: SHOW DATABASES;
SHOW TABLES
Purpose: Lists all tables in the current database.
Syntax: SHOW TABLES;
DESCRIBE
Purpose: Displays the structure of a table.
Syntax: DESCRIBE table_name;
TRUNCATE TABLE
Purpose: Deletes all data in a table without deleting the table itself.
Syntax: TRUNCATE TABLE table_name;
RENAME TABLE
Purpose: Renames an existing table.
Syntax: RENAME TABLE old_table_name TO new_table_name;
BACKUP DATABASE
Purpose: Creates a backup of a database.
Syntax: mysqldump -u username -p database_name > backup.sql
RESTORE DATABASE
Purpose:
Restores a database from a backup file.
- Syntax: mysql -u username -p database_name < backup.sql
LOCK TABLES and UNLOCK TABLES
Purpose: Locks one or more tables for writing and reading.
Syntax: LOCK TABLES table_name READ|WRITE;
Syntax: UNLOCK TABLES;
GRANT
Purpose: Gives a user permissions to access and manipulate databases and tables.
Syntax: GRANT privilege ON database.table TO 'username'@'host';
REVOKE
Purpose: Removes user access rights to the database.
Syntax: REVOKE privilege ON database.table FROM 'username'@'host';
SHOW GRANTS
Purpose: Displays the granted permissions for a user.
Syntax: SHOW GRANTS FOR 'username'@'host';
BEGIN, COMMIT, ROLLBACK
Purpose: Manages transactions in a database.
Syntax: BEGIN; (starts a transaction)
Syntax: COMMIT; (commits the current transaction)
Syntax: ROLLBACK; (reverts the current transaction)
EXPLAIN
Purpose: Provides information about how MySQL executes a query.
Syntax: EXPLAIN SELECT columns FROM table_name WHERE condition;
SET
Purpose: Sets a variable to the specified value.
Syntax: SET @variable_name = value;
