MySQL Syntax Cheatsheet

Cheatsheet Content

### Basic Commands - **Connect to MySQL:** `mysql -u [username] -p` - **Show Databases:** `SHOW DATABASES;` - **Use a Database:** `USE [database_name];` - **Show Tables:** `SHOW TABLES;` - **Describe Table Structure:** `DESCRIBE [table_name];` or `EXPLAIN [table_name];` - **Exit MySQL:** `EXIT;` or `QUIT;` ### Database Operations - **Create Database:** `CREATE DATABASE [db_name];` - **Drop Database:** `DROP DATABASE [db_name];` - **Alter Database (e.g., character set):** `ALTER DATABASE [db_name] CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;` ### Table Operations #### Create Table ```sql CREATE TABLE [table_name] ( column1_name DATATYPE [CONSTRAINTS], column2_name DATATYPE [CONSTRAINTS], ... PRIMARY KEY (column1_name) ); ``` **Example:** ```sql CREATE TABLE Users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` #### Alter Table - **Add Column:** `ALTER TABLE [table_name] ADD COLUMN [new_column_name] DATATYPE [CONSTRAINTS];` - **Drop Column:** `ALTER TABLE [table_name] DROP COLUMN [column_name];` - **Modify Column:** `ALTER TABLE [table_name] MODIFY COLUMN [column_name] NEW_DATATYPE [NEW_CONSTRAINTS];` - **Rename Column:** `ALTER TABLE [table_name] RENAME COLUMN [old_name] TO [new_name];` (MySQL 8.0+) - **Rename Table:** `ALTER TABLE [old_table_name] RENAME TO [new_table_name];` #### Drop & Truncate - **Drop Table:** `DROP TABLE [table_name];` (Deletes table and all data) - **Truncate Table:** `TRUNCATE TABLE [table_name];` (Deletes all rows, resets AUTO_INCREMENT, keeps table structure) ### Common Data Types - **Numeric:** - `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `BIGINT` - `FLOAT`, `DOUBLE` (floating-point) - `DECIMAL(M,D)` (exact fixed-point) - **String:** - `CHAR(M)` (fixed-length, 0-255) - `VARCHAR(M)` (variable-length, 0-65535) - `TINYTEXT`, `TEXT`, `MEDIUMTEXT`, `LONGTEXT` - `ENUM('val1', 'val2', ...)` - `SET('val1', 'val2', ...)` - **Date and Time:** - `DATE` (YYYY-MM-DD) - `TIME` (HH:MM:SS) - `DATETIME` (YYYY-MM-DD HH:MM:SS) - `TIMESTAMP` (YYYY-MM-DD HH:MM:SS, range 1970-2038) - `YEAR` (YYYY) - **Binary:** - `TINYBLOB`, `BLOB`, `MEDIUMBLOB`, `LONGBLOB` (for binary data like images) - `BINARY(M)`, `VARBINARY(M)` ### Constraints - `NOT NULL`: Column cannot store NULL values. - `UNIQUE`: All values in a column are different. - `PRIMARY KEY`: NOT NULL and UNIQUE, unique identifier for each row. - `FOREIGN KEY`: Links two tables, ensures referential integrity. - `DEFAULT [value]`: Sets a default value for a column when no value is specified. - `AUTO_INCREMENT`: Automatically generates a unique number for new records (typically for PRIMARY KEY). ### Insert Data - **Single Row:** ```sql INSERT INTO [table_name] (column1, column2, ...) VALUES (value1, value2, ...); ``` - **Multiple Rows:** ```sql INSERT INTO [table_name] (column1, column2, ...) VALUES (value1a, value2a, ...), (value1b, value2b, ...), ...; ``` - **Insert from another table:** ```sql INSERT INTO [table_name] (column1, column2, ...) SELECT column_a, column_b, ... FROM [another_table] WHERE condition; ``` ### Update Data ```sql UPDATE [table_name] SET column1 = new_value1, column2 = new_value2, ... WHERE condition; ``` **Example:** ```sql UPDATE Products SET price = 29.99, stock = 100 WHERE product_id = 101; ``` ### Delete Data - **Delete specific rows:** ```sql DELETE FROM [table_name] WHERE condition; ``` **Example:** ```sql DELETE FROM Orders WHERE order_date ### Select Data #### Basic Select - **Select all columns:** `SELECT * FROM [table_name];` - **Select specific columns:** `SELECT column1, column2 FROM [table_name];` - **Select distinct values:** `SELECT DISTINCT column_name FROM [table_name];` - **Alias columns:** `SELECT column_name AS alias_name FROM [table_name];` #### WHERE Clause (Filtering) ```sql SELECT column1, column2 FROM [table_name] WHERE condition; ``` **Operators:** - **Comparison:** `=`, `!=` (or `<>`), `>`, ` =`, ` 50 AND category = 'Electronics'; ``` #### ORDER BY & LIMIT (Sorting & Limiting) - **Order results:** ```sql SELECT column1, column2 FROM [table_name] ORDER BY column_name [ASC|DESC], another_column [ASC|DESC]; ``` - **Limit results:** ```sql SELECT column1, column2 FROM [table_name] LIMIT [offset], [count]; ``` **Example:** ```sql SELECT username, created_at FROM Users ORDER BY created_at DESC LIMIT 5 OFFSET 0; -- Get the 5 most recent users ``` #### Aggregate Functions - `COUNT([column]|*)`: Number of rows. - `SUM(column)`: Sum of values in a column. - `AVG(column)`: Average of values in a column. - `MIN(column)`: Minimum value in a column. - `MAX(column)`: Maximum value in a column. #### GROUP BY & HAVING - **Group rows with same values:** ```sql SELECT column1, AGGREGATE_FUNCTION(column2) FROM [table_name] GROUP BY column1; ``` - **Filter groups:** ```sql SELECT column1, COUNT(*) FROM [table_name] GROUP BY column1 HAVING COUNT(*) > [number]; ``` **Example:** ```sql SELECT category, AVG(price) AS average_price FROM Products GROUP BY category HAVING AVG(price) > 100; ``` ### Joins - **INNER JOIN:** Returns rows when there is a match in both tables. ```sql SELECT T1.column, T2.column FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.common_column = T2.common_column; ``` - **LEFT JOIN (or LEFT OUTER JOIN):** Returns all rows from the left table, and the matched rows from the right table. NULL for no match. ```sql SELECT T1.column, T2.column FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.common_column = T2.common_column; ``` - **RIGHT JOIN (or RIGHT OUTER JOIN):** Returns all rows from the right table, and the matched rows from the left table. NULL for no match. ```sql SELECT T1.column, T2.column FROM Table1 AS T1 RIGHT JOIN Table2 AS T2 ON T1.common_column = T2.common_column; ``` - **FULL JOIN (or FULL OUTER JOIN):** Returns all rows when there is a match in one of the tables. (MySQL does not directly support FULL JOIN, use `UNION` of `LEFT JOIN` and `RIGHT JOIN`). ```sql -- Emulating FULL JOIN in MySQL SELECT T1.column, T2.column FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.common_column = T2.common_column UNION SELECT T1.column, T2.column FROM Table1 AS T1 RIGHT JOIN Table2 AS T2 ON T1.common_column = T2.common_column WHERE T1.common_column IS NULL; -- Exclude duplicates from LEFT JOIN ``` - **CROSS JOIN:** Returns the Cartesian product of the rows in the joined tables. ```sql SELECT T1.column, T2.column FROM Table1 AS T1 CROSS JOIN Table2 AS T2; ``` ### Subqueries - A query nested inside another query. ```sql SELECT column1 FROM Table1 WHERE column2 IN (SELECT column_id FROM Table2 WHERE condition); ``` **Example:** ```sql SELECT product_name FROM Products WHERE category_id IN (SELECT category_id FROM Categories WHERE category_name = 'Electronics'); ``` ### Views - A virtual table based on the result-set of a SQL query. - **Create View:** ```sql CREATE VIEW [view_name] AS SELECT column1, column2 FROM [table_name] WHERE condition; ``` - **Drop View:** `DROP VIEW [view_name];` ### Indexes - Used to retrieve data from the database more quickly. - **Create Index:** ```sql CREATE INDEX [index_name] ON [table_name] (column1, column2, ...); ``` - **Create Unique Index:** ```sql CREATE UNIQUE INDEX [index_name] ON [table_name] (column1); ``` - **Drop Index:** `DROP INDEX [index_name] ON [table_name];` ### Users & Permissions - **Create User:** `CREATE USER '[username]'@'[hostname]' IDENTIFIED BY 'password';` - `hostname` can be `localhost` or `%` for any host. - **Grant Permissions:** ```sql GRANT [permission1], [permission2], ... ON [database_name].[table_name] TO '[username]'@'[hostname]'; ``` - `ALL PRIVILEGES` for all permissions. - `*.*` for all databases and tables. - **Revoke Permissions:** ```sql REVOKE [permission1], [permission2], ... ON [database_name].[table_name] FROM '[username]'@'[hostname]'; ``` - **Show Grants:** `SHOW GRANTS FOR '[username]'@'[hostname]';` - **Drop User:** `DROP USER '[username]'@'[hostname]';` - **Flush Privileges (apply changes):** `FLUSH PRIVILEGES;` ### Transactions - A sequence of operations performed as a single logical unit of work. - **Start Transaction:** `START TRANSACTION;` or `BEGIN;` - **Commit Transaction:** `COMMIT;` (Saves changes) - **Rollback Transaction:** `ROLLBACK;` (Undoes changes) ```sql START TRANSACTION; UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1; UPDATE Accounts SET balance = balance + 100 WHERE account_id = 2; COMMIT; -- or ROLLBACK; on error ``` ### Stored Procedures - A prepared SQL code that you can save and reuse. - **Create Procedure:** ```sql DELIMITER // CREATE PROCEDURE GetProductDetails(IN product_id INT) BEGIN SELECT product_name, price, stock FROM Products WHERE id = product_id; END // DELIMITER ; ``` - **Call Procedure:** `CALL GetProductDetails(101);` - **Drop Procedure:** `DROP PROCEDURE GetProductDetails;` ### Common Functions - **String Functions:** - `CONCAT(str1, str2, ...)` - `LENGTH(str)` - `SUBSTRING(str, start, length)` - `UPPER(str)`, `LOWER(str)` - `TRIM(str)` - **Numeric Functions:** - `ABS(num)` - `ROUND(num, decimals)` - `CEIL(num)`, `FLOOR(num)` - `MOD(num, divisor)` - **Date/Time Functions:** - `NOW()`: Current date and time. - `CURDATE()`: Current date. - `CURTIME()`: Current time. - `DATE_ADD(date, INTERVAL value unit)` - `DATEDIFF(date1, date2)` - `DATE_FORMAT(date, format)` - **Control Flow Functions:** - `IF(condition, value_if_true, value_if_false)` - `CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END`